MYSQL语句。-阿里云开发者社区

一些常用的操作内容，非常重要。首先从数据的增删改查谈起。

#增加一条数据
INSERT INTO `studentdb`.`book`(`name`, `maker`, `price`, `num`, `time`, `autor`) VALUES ('Sping', '中国邮政', '500元', 456, '2021/11/30', 'spingsping')
#查询表单结构
DESC book
#查询表的内容
SELECT * FROM book
#查询一条记录
SELECT *FROM book WHERE name='Sping'and maker='中国邮政'
#查询多个条件
#且 and
#或 or 
INSERT INTO `studentdb`.`book`(`name`, `maker`, `price`, `num`, `time`, `autor`) VALUES ('Sping', '中国邮政', '400元', 456, '2021/11/30', 'spingsping')
SELECT *FROM book WHERE price='400元'or price='500元'
#修改数据内容
UPDATE `studentdb`.`book` SET `maker` = 'kut' WHERE `id` = 1005
#修改大量内容
SELECT * FROM book
#创建一个数据
INSERT INTO `studentdb`.`book`(`name`, `maker`, `price`, `num`, `time`, `autor`) VALUES ('SpingBoot', '中国人名出版社', '300元', 356, '2021/12/30', 'spingbook')
#修改大量的数据内容
INSERT INTO `studentdb`.`book`(`id`, `name`, `maker`, `price`, `num`, `time`, `autor`) VALUES (NULL, 'jquery', 'sum', '123', 345, '2020/1/2', 'gh')
UPDATE `studentdb`.`book` SET `name` = 'html', `maker` = 'sumdt', `price` = '678', `num` = 340, `time` = '2021/1/2', `autor` = 'ghj' WHERE `id` = 1008
#删除表的数据
DELETE FROM `studentdb`.book WHERE ` id` = 1007
DELETE FROM `studentdb`.`book` WHERE `id` = 1004
-- 练习题
#增一条语句
INSERT INTO `studentdb`.`book`(`id`, `name`, `maker`, `price`, `num`, `time`, `autor`) VALUES (1013, 'Html5', 'hellowhtml5', '200元', 678, '2020/1/2', '你哈')
#改一条语句的多个条件
UPDATE `studentdb`.`book` SET `name` = 'php', `maker` = 'as', `price` = '344元', `num` = 2334, `time` = '2021/2/4', `autor` = 'nees' WHERE `id` = 10014 AND `name` = Cast('ps' AS Binary(2))
#查表的结构
DESC book
DESC tb_student
#查表的内容
SELECT * FROM book
#删除语句
INSERT INTO `studentdb`.`book`(`id`, `name`, `maker`, `price`, `num`, `time`, `autor`) VALUES (1015, 'Html5', 'maysquery', '210元', 678, '2020/11/30', '增加的一条语句')
#删除上面增加的语句
DELETE FROM `studentdb`.`book` WHERE `id` = 1015 AND `name` = Cast('Html5' AS Binary(5))
UPDATE `studentdb`.`book` SET `maker` = '', `price` = '' WHERE `id` = 10014 AND `name` = Cast('php' AS Binary(3))

这里是基本的数据操作内容以及语法

代码

use student;
set @num=0;
SET @name:= "电子工业出版社";
SET @num =(
SELECT
    Count(*) 
  FROM
    `图书信息` 
  WHERE
    `出版社ID` =(SELECT  `出版社ID`
    from `出版社`
    where `出版社名称`=@name );
SELECT  @name,@num;
#!
Delimiter $$
use student;
Create Procedure proc0501()
BEGIN
  DECLARE name VARCHAR(40);
  DECLARE id int;
  Declare num int;
  Set name="电子工业出版社";
  Set ID=(SELECT `出版社ID` from `出版社` where `出版社名称`=name);
  Set Counr(*) INTO num from `图书信息`where `出版社ID`=ID；
  SELECT name ,ID ,num;
END $$
Delimiter;
#2
Delimiter $$
use student;
Create Procedure proc0502( In strName varchar(50))
BEGIN
  DECLARE id int;
  Declare num int;
  if(strName Is Not Null ) Then 
  Set id=(SELECT `出版社ID` from `出版社` where `出版社名称`=strName);
  Set Counr(*) INTO num from `图书信息`where `出版社ID`=id；
  END if;
  SELECT name ,ID ,num;
END $$
Delimiter;
#3
DROP PROCEDURE IF EXISTS `student`.`proc0503`;
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc0503`(IN `strName` varchar(50))
BEGIN
  #Routine body goes here..
  Declare id int ;
  Declare num int ;
  if(strName Is Not Null ). Then
      Set id=(SELECT `出版社ID` from `出版社` where `出版社名称`=strName);
  Set Counr(*) INTO num from `图书信息`where `出版社ID`=id；
  END IF;
  select strName ,id ,num;
END;
#4
DROP PROCEDURE IF EXISTS `student`.`proc0503`;
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc0503`(IN `strName` varchar(50))
BEGIN
  #Routine body goes here..
  Declare id int ;
  Declare num int ;
  if(strName Is Not Null ). Then
      Set id=(SELECT `出版社ID` from `出版社` where `出版社名称`=strName);
  Set Counr(*) INTO num from `图书信息`where `出版社ID`=id；
  END IF;
  SELECT strName as `出版社名称`, id as `出版社ID`, intNum as `图书类型`;
END;
#5
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc0503`(IN `strName` varchar(50))
BEGIN
  #Routine body goes here..
  Declare id int ;
  Declare  maxprice declimal;
  if(strName Is Not Null ). Then
      Set id=(SELECT `出版社ID` from `出版社` where `出版社名称`=strName);
      Set MAX(`价格`) INTO maxprice FROM `图书信息` where `出版社ID`=id;
      SELECT `图书名称` into strName from `图书信息` where 价格 =maxprice AND `出版社ID`=ID;
  Set Counr(*) INTO num from `图书信息`where `出版社ID`=id；
  END IF;
  SELECT strName as `出版社名称`, id as `出版社ID`, maxprice as `价格`;
END
use student;
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_null`()
DROP PROCEDURE IF EXISTS 'proc_null';
CREATE PROCEDURE'proc_null'()
BEGIN
ELETE i int;
DECLARE s int ;
set s=1;
set i=1;
while i<100 DO
set i=i+1;
set s+1;
end while;
select s;
END$$
call 'proc_null'();
use student;
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_null`()
DROP PROCEDURE IF EXISTS 'proc_null';
CREATE PROCEDURE'proc_null'()
BEGIN
DECLARE i int;
DECLARE s int ;
set s=1;
set i=1;
while i<100 DO
set i=i+1;
set s+1;
end while;
select s;
END$$
call 'proc_null'();
use student;
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_null`()
DROP PROCEDURE IF EXISTS 'proc_null';
CREATE PROCEDURE'proc_null'()
BEGIN
ELETE i int;
DECLARE s int ;
set s=1;
set i=1;
while i<100 DO
set i=i+1;
set s+1;
end while;
select s;
END$$
call 'proc_null'();
use student;
SELECT
  `出版社`.`出版社ID`, 
  `出版社`.`出版社名称`, 
  `出版社`.`出版社简称`, 
  `出版社`.`出版社地址`, 
  `出版社`.`邮政编码`, 
  `图书信息`.`ISBN编号`, 
  `图书信息`.`图书名称`, 
  `图书信息`.`作者`, 
  `图书信息`.`出版社ID`, 
  `图书信息`.`价格`, 
  `图书信息`.`出版日期`, 
  `图书信息`.`图书类型`
FROM
  `图书信息`
  INNER JOIN
  `出版社`
  ON 
    `图书信息`.`出版社ID` = `出版社`.`出版社ID`

use student ;
select *from `藏书信息`;
select `读者类型名称`, `限借数量`,`限借期限` from `读者类型`;
select  `ISBN编号`,`图书名称`,`作者`,`出版日期`
From  `图书信息`
where `作者`='王青青';
/*
select  `ISBN编号`,`图书名称`,`作者`,`出版日期`
From  `图书信息`
where YEAR(`出版日期`)>2014；
*/
select  `ISBN编号`,`图书名称`
From  `图书信息`
Limit 7;
select  `ISBN编号`,`图书名称`
From  `图书信息`
Limit 7,8;
select COUNT(*)AS  `图书名称`
from  `图书信息`
where `价格` Between 30 And 50;
select SUM(`总藏书量`) AS `总藏书量` from `藏书信息`;
USE student;#子查询
/*
#1
SELECT
  `图书名称`,
  `图书简介`,
  `图书类型`,
  `作者`,
  `出版社ID` 
FROM
  `图书信息` 
WHERE
  `作者` = '陈丽丽刘国良';#2创建子查询对关键字为in
SELECT DISTINCT
  `出版社名称`,
  `出版社简称` 
FROM
  `出版社` 
WHERE
  `出版社ID` IN ( SELECT `出版社ID` FROM `图书信息` WHERE `作者` = "王青青" );#3 用exists
SELECT
  * 
FROM
  `借书证` 
WHERE
  EXISTS ( SELECT * FROM `借阅者信息` WHERE `借阅者信息`.`借阅者编号` = `借书证`.`借阅者编号` );#4 ang 关键字
SELECT
  `ISBN编号`,
  `出版社ID`,
  `价格` 
FROM
  `图书信息` 
WHERE
  `价格` <ANY ( SELECT `价格` FROM `图书信息` WHERE `价格`='45' )；
  */
#5 DELETE
DELETE FROM asd;
USE student;#加元素
CREATE TABLE 出版社 2 (
  出版社 ID INT AUTO_INCREMENT NOT NULL,
  出版社名称 VARCHAR ( 50 ) UNIQUE NOT NULL,
  出版社简称 VARCHAR ( 16 ) UNIQUE NULL,
  出版社地址 VARCHAR ( 50 ) NULL,
  邮政编码 CHAR ( 6 ) NULL,
  PRIMARY KEY (出版社 ID ) 
);
INSERT INTO `student`.`出版社2` ( `出版社ID`, `出版社名称`, `出版社简称`, `出版社地址`, `邮政编码` )
VALUES
  ( '2', '人民邮电出版社', '人邮', '北京市38号', '100061' );
INSERT INTO `student`.`出版社2` ( `出版社ID`, `出版社名称`, `出版社简称`, `出版社地址`, `邮政编码` )
VALUES
  ( '4', '电子工业出版社', '电子工业', '北京市150号', '100006' );
INSERT INTO `student`.`出版社2` ( `出版社ID`, `出版社名称`, `出版社简称`, `出版社地址`, `邮政编码` )
VALUES
  ( '3', '清华大学出版社', '清华大学', '北京市1250号', '100084' );
INSERT INTO `student`.`出版社2` ( `出版社ID`, `出版社名称`, `出版社简称`, `出版社地址`, `邮政编码` )
VALUES
  ( '5', '机械工业出版社', '机械工业 ', '北京市750号', '100008' );
INSERT INTO `student`.`出版社2` ( `出版社ID`, `出版社名称`, `出版社简称`, `出版社地址`, `邮政编码` )
VALUES
  ( '6', '人民邮电出版社', '人邮', '北京市38号', '100061' );
INSERT INTO `student`.`出版社2` ( `出版社ID`, `出版社名称`, `出版社简称`, `出版社地址`, `邮政编码` )
VALUES
  ( '7', '电子工业出版社', '电子工业', '北京市150号', '100006' );

use student;
select *from `出版社`;
select *from `出版社`;
select  count (*) as  图书统计数量 , `出版社ID`
from 图书信息
GROUP BY  `出版社ID`;
select `出版社ID`; AVG(`价格`) 平局定价,COUNT(*)  AS  `图书名称`
from  `图书信息`
GROUP BY `出版社ID`;
--
select `出版社ID`; AVG(`价格`) 平局定价,COUNT(*)  AS  `图书名称`
from  `图书信息`
where `价格`>=30;    分组前的条件--
GROUP BY `出版社ID`;
select `出版社ID`; AVG(`价格`) 平局定价,COUNT(*)  AS  `图书名称`
from  `图书信息`
where `价格`>=30 A    分组后的条件--
GROUP BY `出版社ID`;
having count (*)>=3;  *分组后进行--
select `出版社ID`; AVG(`价格`) 平局定价,COUNT(*)  AS  `图书名称`
from  `图书信息`
having avg (`价格`）30  ;
GROUP BY `出版社ID`;
use studemt ;
update `图书信息`
SET `价格`=(
case
WHEN `价格` BETWEEN 0 and 20 then `价格` * 1.5
WHEN `价格` BETWEEN 21 and 30 then `价格` * 1.3
WHEN `价格` BETWEEN 31 and 40 then `价格` * 1.2
WHEN `价格` BETWEEN 41 and 50 then `价格` * 1.1
when `价格`>50 then `价格`*1
end case;)
select *from `图书信息`;
USE student;
/*
ALTER TABLE `student`.`藏书信息` MODIFY COLUMN `图书编号` CHAR ( 255 ) CHARACTER 
SET utf8 COLLATE utf8_general_ci NOT NULL FIRST;
ALTER TABLE `student`.`藏书信息` MODIFY COLUMN `图书编号` CHAR ( 255 ) CHARACTER 
SET utf8 COLLATE utf8_general_ci NOT NULL FIRST,
ADD CONSTRAINT `booktre` FOREIGN KEY ( `ISBN编号` ) REFERENCES `student`.`图书信息` ( `ISBN编号` ) ON DELETE CASCADE ON UPDATE RESTRICT;
SHOW INDEX 
ALTER TABLE `student`.`藏书信息` 
MODIFY COLUMN `图书编号` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL FIRST,
MODIFY COLUMN `总藏书量` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT A-1-1-2 AFTER `ISBN编号`,
ADD CONSTRAINT `booktre` FOREIGN KEY (`ISBN编号`) REFERENCES `student`.`图书信息` (`ISBN编号`) ON DELETE CASCADE ON UPDATE RESTRICT;
ALTER TABLE`藏书信息` 
MODIFY COLUMN `图书编号` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL FIRST,
MODIFY COLUMN `总藏书量` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT A-1-1-2 AFTER `ISBN编号`,
ADD UNIQUE INDEX `idx_ISBN`(`ISBN编号`) USING BTREE,
ADD CONSTRAINT `booktre` FOREIGN KEY (`ISBN编号`) REFERENCES `student`.`图书信息` (`ISBN编号`) ON DELETE CASCADE ON UPDATE RESTRICT;
  */
  SHOW INDEX FROM `藏书信息`;
use student;
/*
CREATE TABLE `student`.`Untitled`  (
  `职工编号` varchar(255) NOT NULL,
  `姓名` varchar(255) NOT NULL,
  `性别` varchar(255) NULL,
  `部门名称` varchar(255) NOT NULL,
  PRIMARY KEY (`职工编号`, `姓名`),
  CONSTRAINT `sdf` FOREIGN KEY (`职工编号`) REFERENCES `student`.`职工表` (`职工编号`) ON DELETE SET NULL ON UPDATE SET NULL
);
*/
#插入记录
INSERT INTO `student`.`职工表`(`职工编号`, `姓名`, `性别`, `部门名称`) VALUES ('A234', '你就', '男', '网络信息');
INSERT INTO `student`.`职工表`(`职工编号`, `姓名`, `性别`, `部门名称`) VALUES ('A235', '小吗', '男', '网络信息xx');
INSERT INTO `student`.`职工表`(`职工编号`, `姓名`, `性别`, `部门名称`) VALUES ('A237', '咯破', '男', '网络java');
INSERT INTO `student`.`职工表`(`职工编号`, `姓名`, `性别`, `部门名称`) VALUES ('A237', '咯破', '男', '计算机');
INSERT INTO `student`.`职工表`(`职工编号`, `姓名`, `性别`, `部门名称`) VALUES ('A237', '咯破', '男', '计算机se');
/*
SELECT* 
FROM
  `职工表`
/*  
  ALTER TABLE `student`.`职工表` 
ADD UNIQUE INDEX `gh`(`职工编号`) USING HASH;
*/
#查看
SHOW INDEX   FROM 职工表 ;
DROP index `姓名` ON 职工;
ALTER TABLE 职工 DROP PRIMARY KEY;
use student ;
select `图书编号总`,`图书名称`,`作者`, `总藏书量` ,`馆内剩余`
from `藏书信息`,`图书信息`
where `藏书位置`.`ISBN编号`=`图书信息`.`ISBN编号`and `价格`>30;
select `图书编号总`,`图书名称`,`作者`, `总藏书量` ,`馆内剩余`
from `藏书信息`
join `图书信息`
ON `藏书位置`.`ISBN编号`=`图书信息`.`ISBN编号`and 
where `价格`>30;
select  `图书信息`, `出版社`,`出版社ID` ,`作者`
from `图书信息`,`出版社`
where `图书信息`.`出版社ID`=`出版社`.`出版社ID`and `价格`>30；
select  `图书信息`, `出版社`,`出版社ID` ,`作者`
from `图书信息`
join`出版社`
ON `图书信息`.`出版社ID`=`出版社`.`出版社ID`
where `价格`>30;
#查询   图书编号总`,`图书名称`,`作者`, `总藏书量` ,`馆内剩余` `图书信息`, `出版社`,`出版社ID` ,`作者
#内连接
select `图书编号`,`图书名称`,`作者`, `总藏书量` ,`馆内剩余``出版社名称`,`出版社`
from `藏书信息``图书信息``出版社`
where `藏书位置`.`ISBN编号`=`图书信息`.`ISBN编号` And `图书信息`.`出版社ID`=`出版社`.`出版社ID` 
where `价格`>30;
select `图书编号`,`图书名称`,`作者`, `总藏书量` ,`馆内剩余``出版社名称`,`出版社`
from `藏书信息`
 inner JOIN`图书信息`
inner JOIN`出版社`
ON `藏书位置`.`ISBN编号`=`图书信息`.`ISBN编号` And `图书信息`.`出版社ID`=`出版社`.`出版社ID` 
where `价格`>30;
#三表中必有二个表字段重复 二次   `藏书信息`,`图书信息`,`出版社`
select 借书证.`姓名`,`图书借阅`.`借阅者编号`,`图书借阅``续借次数`
from 借书证  INNER JOIN `图书借阅`
ON `借书证状态`.`借书证编号`=`图书借阅`.`借阅者编号`;

use student;
SELECT * 
FROM 课程;
CREATE TABLE `student`.`Untitled`  (
  `学号` varchar(21) NOT NULL,
  `姓名` char(25) NOT NULL,
  `性别` varchar(2) NOT NULL,
  `专业课` varchar(20) NOT NULL,
  `java` varchar(25) NOT NULL,
  `mysql` varchar(21) NOT NULL,
  PRIMARY KEY (`学号`, `姓名`, `专业课`, `mysql`)
);
INSERT INTO `student`.`课程`(`学号`, `姓名`, `性别`, `专业课`, `java`, `mysql`) VALUES ('A2020653', '归还', '男', '67', '89', '100');
INSERT INTO `student`.`课程`(`学号`, `姓名`, `性别`, `专业课`, `java`, `mysql`) VALUES ('A2020654', '星期', '女', '67', '34', '45');
use student ;
#左连接查看出版社ID,`出版社名称` ,`图书名称` ,`ISBN编号` 
Select `出版社`.`出版社ID`,`出版社名称`,`ISBN编号`,`图书名称`
from `出版社`
LEFT  OUTER `出版社`JOIN `图书信息`
ON `出版社`.`出版社ID`=`图书信息`.`出版社ID`;
#右表连接
Select ``图书信息`.`出版社ID`,`出版社名称`,`ISBN编号`,`图书名称`
from   `出版社`
RIGHT  OUTER  JOIN `图书信息`
ON  `出版社`.`出版社ID`=`图书信息`.`出版社ID`;
use student;
/*
SELECT now ();
SELECT WEEK(now ());
SELECT WEEKOFYEAR(NOW());
SELECT WEEKDAY(NOW());
*/
SELECT WEEKOFYEAR(NOW());
SELECT WEEKDAY(NOW());
select DATE_ADD('2020-01-02',INTERVAL -12 day);
select DATE_ADD('2020-01-02',INTERVAL -11 day);
select DATE_ADD('2020-01-02',INTERVAL -12 week);
select DATEDIFF('2020-01-02','2020-o3-01');
SELECT DATE_FORMAT('2020-01-04','%d/%m/%y');
select DATEDIFF('2012-01-02','2020-o3-01');
SELECT DATE_FORMAT('2000-01-04','%d/%m/%y');
select DATE_ADD('2020-01-02',INTERVAL -2 day);
select DATE_ADD('2020-01-02',INTERVAL -1 day);
select DATE_ADD('2020-01-02',INTERVAL -1 week);
USE student;#select *from `藏书信息`;
/*SELECT
  `ISBN编号`，`图书名称`,
  `图书编号`,
  `ISBN编号`,
  `总藏书量`,
  `藏书位置` 
FROM
  `藏书信息`
  LEFT JOIN `图书信息` ON `ISBN编号`.`藏书信息` = `ISBN编号`.`图书信息`;*/
SELECT
  `ISBN编号`，`图书名称`,
  `图书编号`,
  `ISBN编号`,
  `总藏书量`,
  `藏书位置` 
FROM
  `图书信息`
  RIGHT JOIN `藏书信息` ON `ISBN编号`.`藏书信息` = `ISBN编号`.`图书信息`;
/*SELECT
  `藏书信息`.`ISBN编号`,
  `图书名称`,
  `图书编号`,
  `ISBN编号`,
  `总藏书量`,
  `藏书位置` 
FROM
  `藏书信息`
  LEFT JOIN `图书信息` ON `藏书信息`.`ISBN编号` = `图书信息`.`ISBN编号`;*/
USE student;
delimiter $$
DROP PROCEDURE IF
  EXISTS 'proc_in_out';
CREATE PROCEDURE 'proc_in_out' (IN 'n' int,out s INT) 
BEGIN
  DECLARE
    i INT;
  SET i= 0;
  SET s= 0;
  WHILE
      i<=n DO
      SET s=s+1;
    SET i=i+1;
  END WHILE;
END $$
delimiter;
set @num=100;
set @sum=0;
CALL proc_in_out (@num,@sum);
set @sum;
USE student;
delimiter $$
DROP PROCEDURE IF
  EXISTS 'proc_inout';
CREATE PROCEDURE 'proc_inout' (IN 'n' int,out s INT,INT,INOUT i INT) 
BEGIN
  SET s= 0;
  WHILE
      i<=n DO
      SET s=s+1;
    SET i=i+1;
  END WHILE;
END $$
delimiter;
set @num=100;
set @sum=0;
set @innum=1;
CALL proc_in_out (@num,@sum,@innum);
set @sum;@innum;
USE student;
delimiter $$
DROP PROCEDURE
IF
  EXISTS 'proc_null';
CREATE PROCEDURE 'proc_null' () BEGIN
  DECLARE
    i INT;
  DECLARE
  proc_in_out s INT;
  SET s = 1;
  SET i = 1;
  WHILE
      i < 100 DO
      SET i = i + 1;
    SET s + 1;
  END WHILE;
  SELECT
    s;
END $$
delimiter;
CALL 'proc_null' ();
USE student;
delimiter $$
DROP FUNCTION
IF
  EXISTS fuc_abc ();
CREATE FUNCTION fuc_abc ( INT, B INT ) RETURNS INT BEGIN
  DECLARE
    c INT;
  .0 `
  set c=a+b;
  RETURN c;
  end $$
delimiter;
jd
Delimiter $$
use student;
Create Procedure proc0501()
BEGIN
  DECLARE name VARCHAR(40);
  DECLARE id int;
  Declare num int;
  Set name="电子工业出版社";
  Set ID=(SELECT `出版社ID` from `出版社` where `出版社名称`=name);
  Set Counr(*) INTO num from `图书信息`where `出版社ID`=ID；
  SELECT name ,ID ,num;
END $$
Delimiter;
use student;
/*
SELECT now ();
SELECT WEEK(now ());
SELECT WEEKOFYEAR(NOW());
SELECT WEEKDAY(NOW());
*/
SELECT WEEKOFYEAR(NOW());
SELECT WEEKDAY(NOW());
select DATE_ADD('2020-01-02',INTERVAL -12 day);
select DATE_ADD('2020-01-02',INTERVAL -11 day);
select DATE_ADD('2020-01-02',INTERVAL -12 week);
select DATEDIFF('2020-01-02','2020-o3-01');
SELECT DATE_FORMAT('2020-01-04','%d/%m/%y');
select DATEDIFF('2012-01-02','2020-o3-01');
SELECT DATE_FORMAT('2000-01-04','%d/%m/%y');
select DATE_ADD('2020-01-02',INTERVAL -2 day);
select DATE_ADD('2020-01-02',INTERVAL -1 day);
select DATE_ADD('2020-01-02',INTERVAL -1 week);

USE student;
DROP TABLE
IF
  EXISTS sc;
CREATE TABLE sc (
  sno CHAR ( 6 ) NOT NULL FOREIGN KEY ( sno ) REFERENCES student ( sno ),
  cno CHAR ( 5 ) NOT NULL,
  FOREIGN KEY ( cno ) REFERENCES course ( cno ),
  score TINYINT,
  PRIMARY KEY ( sno, cno ) 
);
INSERT INTO SC ALTER TABLE student ALTER ssex 
SET DEL ETE '男'； ALTER TABLE student ADD CONSTRAINT CHK ssex CHECK (
  ssex = '男' 
  OR ssex = '女'； INSERT INTO 
  VALUES
    ( '10010I', 'AAAAAAA', 'NV', 89 );
  INSERT INTO SC
  VALUES+
    ( '100102', 'AAAAAAA', 'NV', 89 );
  INSERT INTO SC
  VALUES
    ( '100103', 'AAAAAAA', 'NV', 89 );
  INSERT INTO SC
  VALUES
    ( '100104', 'AAAAAAA', 'NV', 89 );
  INSERT INTO SC
  VALUES
    ( '100105', 'AAAAAAA', 'NV', 89 );
  INSERT INTO SC
  VALUES
    ( '100106', 'AAAAAAA', 'NV', 89 );
  INSERT INTO SC
VALUES
  ( '10010I', 'AAAAAAA', 'NV', 89 );
use student ;
#表一
drop table  if EXISTS course ;
create table course(
cno char (5) not null primary key,
cnam VALUES (10) UNIQUE,
credit tinyint default 4
);
insert into value ('0001'，'DB'，4）；
insert into value ('0001'，'os'，4）；
insert into value ('0001'，'java'，4）；
insert into value ('0001'，'c++'，4）；
insert into value ('0001'，'ko'，4）；
SELECT *
FROM course ;
#表二
drop table if EXISTS sc;
create table sc(
sno char (6) not null FOREIGN key (sno) references student(sno) ,
cno char (5)not null ,FOREIGN key (cno) references course (cno),
score tinyint ,
primary key (sno,cno)
);
INSERT INTO SC VALUES('10010I','AAAAAAA','NV',89);
INSERT INTO SC VALUES('10010I','AAAAAAA','NV',89);
INSERT INTO SC VALUES('10010I','AAAAAAA','NV',89);
INSERT INTO SC VALUES('10010I','AAAAAAA','NV',89);
INSERT INTO SC VALUES('10010I','AAAAAAA','NV',89);
INSERT INTO SC VALUES('10010I','AAAAAAA','NV',89);
INSERT INTO SC VALUES('10010I','AAAAAAA','NV',89);
use student 
#表一
drop table  if EXISTS course ;
create table course(
cno char (5) not null primary key,
cnam VALUES (10) UNIQUE,
credit tinyint default 4
constraint CHK_credit  check (credit<=6);
);
alter table student4  add constraint  Pk_cno PAIMARY key (cno);
alter table student4   add constraint UNQ_cnname  UNIOUE (CNNAME);
alter table student4  add constraint  alter credit set  DELFAULT 4;
(
alter table student4 add constraint  CHK_credit (credit >=1) and credit<=6);
insert into value ('0001'，'DB'，4）；
insert into value ('0001'，'os'，4）；
insert into value ('0001'，'java'，4）；
insert into value ('0001'，'c++'，4）；
insert into value ('0001'，'ko'，4）；
USE syudent ;
#DELETE stu_info;
#修改表的结构
ALTER TABLE student ADD student VARCHAR (
  10） defaul 'ca' FIRST;#增加内容
  ALTER TABLE student ADD adderss VARCHAR ( 50 ) defaul NULL;
  #DELETE stu_info;
  SELECT
    * 
FROM
  student;
  alter table student add sdept varchar (10) default 'cs' first;
  alter table student add addess  varchar (50) default null;
  ALTER TABLE student modify score SMALLINT;
  ALTER TABLE student change  sno s_id char(6);
  ALTER TABLE student rename student_1007;
  ALTER ALTER student_1007 drop addess;
  select  *
  use student ；
select *
from 藏书信息 ;
/*
select *
from 出版社
order by `出版社` Desc ;
/*
//2
select  `出版社ISBN`, `出版社名称`  
from  `出版社`;
LIMIT 3;
 //3
select  `出版社ISBN`, `出版社名称`  
from  `出版社`;
LIMIT 1,3;
//4
select `ISBN编号`,`总藏数量`，`馆内剩余`，`总藏数量`-`馆内剩余` AS
 `借出数量`
 From `藏书信息`
 //5
 select `ISBN编号`,`图书类型`，，`总藏数量`-`馆内剩余` AS
 `借出数量`
 From `藏书信息``出版日期` From `图书信息` where  YEAR(`出版日期`)>2015；
 //6
 Select COUNT(*) AS `图书类型` From `图书信息` where `价格` Between 20 And
 45;
 Select SUM(`总藏数量`) AS `总藏数量` From `藏书信息`；
 //7
 select `ISBN编号`,  图书名称, name，`价格`
 from `图书信息`
 where 价格 in (25,33,36,40);
 //8
 select `ISBN编号`,  图书名称, name，`价格`
 from `图书信息`
 where 价格   between  25 and  36;
 //9
 select `ISBN编号`,  图书名称, name，`价格` `出版日期`
 from `图书信息`
 where  name='程程华' or year (`出版日期`) >2015 
 //10
 select `ISBN编号`,  图书名称, name，`价格` `出版日期`
 from `图书信息`
 where  `图书状态` is null ;
 //11
 select `ISBN编号`,  图书名称, `出版日期` 
 From `图书信息`
 where  `出版日期`  
 Between '2015-10-2' And   '2016-9-2'
 //12
select `ISBN编号`,  图书名称, `作者`
 From `图书信息`
 where  作者  
in ('成成胡','成趋势','程海林',);
//13
SELECT count(*) as `图书信息`
from  `图书信息`
 //12
select `ISBN编号`,  图书名称, `作者`
 From `图书信息`
 where  作者  
in ('成成胡','成趋势','程海林',);
//13
SELECT count(*) as `图书信息`
from  `图书信息`
//14
SELECT sum  as(价格), avg (`价格`) as 平均值
from `图书信息2`
where 出版社=3
//15
SELECT `ISBN编号`as `图书编号` 图书名称 ，价格as单价 60 人图书manay
from  `图书信息`
//16
Select COUNT(*) AS `图书类型` From `图书信息` where `价格` Between 20 And
 45;
 //17
 Select SUM(`总藏数量`) AS `总藏数量` From `藏书信息`；
 //18
 Select Count (Distinct (``藏书位置``)) AS  `藏书位置数量` from `藏书信息`；
 //19
 Select MAX(`价格`) AS 最高价， MIN(`价格`) AS  最低价， AVG (`价格`) AS 平均价
 From `图书信息`；
 */

use student;
select  `ISBN编号`, `出版社ID`,`价格`
From `图书信息`
where 价格>All (select 价格 from `图书信息`where `出版社ID`='4');
# 查询 `藏书信息`A-1-1的图书的出版设ID 出版社ID 出版jiecheng
# 藏书是 内表  为条件  =====图书信息 中间    目标 `出版社`
SELECT
  `出版社ID`,
  `出版社简称` 
FROM
  `出版社` 
WHERE
  `出版社ID` IN (
  SELECT
    `出版社ID` 
  FROM
    `图书信息` 
WHERE
  `ISBN编号` IN ( SELECT `ISBN编号` FROM `藏书信息` WHERE `藏书位置` = 'A-1-1' );
USE student;
CREATE TABLE users2
(  
    ID           INT(4)      NOT NULL ,
    ListNum      VARCHAR(10)   NULL ,
    NAME         VARCHAR(30)   NULL ,
    UserPassword  VARCHAR(15)  NULL,
    PRIMARY KEY( ID ) 
) ;
CREATE TABLE 出版社2
(
  出版社ID INT AUTO_INCREMENT NOT NULL,
  出版社名称 VARCHAR(50) UNIQUE NOT NULL,
  出版社简称 VARCHAR(16) UNIQUE NULL,
  出版社地址 VARCHAR(50) NULL,
  邮政编码 CHAR(6) NULL,
   PRIMARY KEY(出版社ID)
) ; 
INSERT INTO `student`.`出版社2`(`出版社ID`,`出版社名称`,`出版社简称`,`出版社地址`,`邮政编码`) VALUES ( '2','人民邮电出版社','人邮','北京市38号','100061');
INSERT INTO `student`.`出版社2`(`出版社ID`,`出版社名称`,`出版社简称`,`出版社地址`,`邮政编码`) VALUES ( '4','电子工业出版社','电子工业','北京市150号','100006');
INSERT INTO `student`.`出版社2`(`出版社ID`,`出版社名称`,`出版社简称`,`出版社地址`,`邮政编码`) VALUES ( '3','清华大学出版社','清华大学','北京市1250号','100084');
INSERT INTO `student`.`出版社2`(`出版社ID`,`出版社名称`,`出版社简称`,`出版社地址`,`邮政编码`) VALUES ( '5','机械工业出版社','机械工业 ','北京市750号','100008');
INSERT INTO `student`.`出版社2`(`出版社ID`,`出版社名称`,`出版社简称`,`出版社地址`,`邮政编码`) VALUES ( '6','人民邮电出版社','人邮','北京市38号','100061');
INSERT INTO `student`.`出版社2`(`出版社ID`,`出版社名称`,`出版社简称`,`出版社地址`,`邮政编码`) VALUES ( '7','电子工业出版社','电子工业','北京市150号','100006');
CREATE TABLE 图书信息2
(
  ISBN编号 VARCHAR(20) PRIMARY KEY NOT NULL,
  图书名称 VARCHAR(100) NOT NULL,
  作者 VARCHAR(40) NULL,
  价格 DECIMAL NOT NULL,
  出版社ID INT NOT NULL,
  出版日期 DATE NULL,
  图书类型 VARCHAR(2) NOT NULL,
  封面图片 BLOB,
  图书简介 TEXT
) ;
INSERT INTO `student`.`图书信息2`VALUES ( '9787121121201478','软件工程基础','陈承欢','32','4','2014/7/1','T ',NULL,NULL);
INSERT INTO `student`.`图书信息2`VALUES ( '9787121201693','实用工具软件','陈丽丽','31','2','2016/7/1','T',NULL,NULL);
INSERT INTO `student`.`图书信息2`VALUES ( '9787125201556','数据库基础软件实例教程','刘国良','18','4','2015/5/6','T',NULL,NULL);
INSERT INTO `student`.`图书信息2`VALUES ( '9787125201632','网页设计','陈欢欢','36','2','2016/9/8','T',NULL,NULL);
INSERT INTO `student`.`图书信息2`VALUES ( '9787125201735','web 前端基础','王青青','26','2','2017/3/5','T',NULL,NULL);
use student 
CREATE TABLE 藏书信息
(
  图书编号 VARCHAR NOT NULL,
  ISBN编号  VARCHAR(50) UNIQUE NOT NULL,
  总藏书量 VARCHAR(16)  UNIQUE NULL,
  馆内剩余VARCHAR(50) NULL,
  藏书位置 CHAR(6) NULL,
  入库时间  VARCHAR(67 NULL  NULL,
   PRIMARY KEY(出版社ID)
) ; 
USE student;#查询  出版社名称`,`出版社简称`
/*select   DISTINCT`出版社名称`,`出版社简称`
from `出版社`,`图书信息`
where `出版社`.`出版社ID`=`图书信息`.`出版社ID` and `价格`>30;
#条件在一张表，jie kou zailingyizhangbiao 
select   DISTINCT`出版社名称`,`出版社简称`
from `出版社`
inner join`图书信息`
on  `出版社`.`出版社ID`=`图书信息`.`出版社ID` 
where `价格`>30;
*/
SELECT DISTINCT
  `出版社名称`,
  `出版社简称` 
FROM
  `出版社` 
WHERE
  `出版社ID` IN ( SELECT `出版社ID` FROM `图书信息` WHERE `价格` < 30; );# 查询 `藏书信息`A-1-1的图书的出版设ID 出版社ID 出版jiecheng
# 藏书是 内表  为条件  =====图书信息 中间    目标 `出版社`
SELECT
  `出版社ID`,
  `出版社简称` 
FROM
  `出版社` 
WHERE
  `出版社ID` IN (
  SELECT
    `出版社ID` 
  FROM
    `图书信息` 
  WHERE
    `ISBN编号` IN ( SELECT `ISBN编号` FROM `藏书信息` WHERE `藏书位置` = 'A-1-1' );
  SELECT DISTINCT
    `出版社名称`,
    `出版社简称` 
  FROM
    `出版社`,
    `图书信息` 
  WHERE
    `出版社`.`出版社ID` = `图书信息`.`出版社ID` 
    AND `价格` > 30;#条件在一张表，jie kou zailingyizhangbiao
  SELECT DISTINCT
    `出版社名称`,
    `出版社简称` 
  FROM
    `出版社`
    INNER JOIN `图书信息` ON `出版社`.`出版社ID` = `图书信息`.`出版社ID` 
  WHERE
    `价格` > 30;
  SELECT DISTINCT
    `出版社名称`,
    `出版社简称` 
  FROM
    `出版社` 
  WHERE
    `出版社ID` IN ( SELECT `出版社ID` FROM `图书信息` WHERE `价格` >= 30; );#查询作者性王青青的图书的出版社ID 出版社简称  图书名称
  SELECT DISTINCT
    `出版社名称`,
    `出版社简称` 
  FROM
    `出版社` 
  WHERE
    `出版社ID` IN ( SELECT `出版社ID` FROM `图书信息` WHERE `作者` = "王青青" );
  SELECT DISTINCT
    `出版社名称`,
    `出版社简称` 
  FROM
    `出版社` 
WHERE
  `出版社ID` IN ( SELECT `出版社ID` FROM `图书信息` WHERE ( SELECT `藏书位置` FROM `藏书信息` ) WHERE `藏书位置` = 'A-1-1' );
USE student;
select  `ISBN编号`,`价格`,`图书名称`
from `图书信息`
where `价格`>ALL(select `价格`
            from `图书信息`
            where `出版社ID`=4;
select  `ISBN编号`,`价格`,`图书名称`
from `图书信息`
where `价格`>ALL(select  MAX(`价格`)
            from `图书信息`
            where `出版社ID`=2;
select  `ISBN编号`,`价格`,`图书名称`
from `图书信息`
where `出版社ID`!=4; in  `价格`>ALL(SELECT `价格`
from  `图书信息` `出版社`);
select `出版社ID`,`出版社名称`,`出版社简称`
from `出版社`
where `出版社ID` in (SELECT `出版社ID`FROM `图书信息`);
SELECT
  `出版社ID`,
  `出版社名称`,
  `出版社简称` 
FROM
  `出版社` 
WHERE
  not EXISTS `出版社ID`  ( SELECT `出版社ID` FROM `图书信息` WHERE `出版社`.`出版社ID` = `图书信息`.`出版社ID` );
  #查出比4号出版社的图书都要高的图书  
SELECT
  `ISBN编号`,
  `价格`,
  `图书名称` 
FROM
  `图书信息` 
WHERE
  `价格` > ALL (
  SELECT
    `价格` 
  FROM
    `图书信息` 
WHERE
  `出版社ID`=3
  );
SELECT
  `ISBN编号`,
  `价格`,
  `图书名称` 
FROM
  `图书信息` 
WHERE
  `价格` > ALL (
  SELECT
    MAX(`价格`)
  FROM
    `图书信息` 
WHERE
  `出版社ID`=2
  );
  SELECT
  `ISBN编号`,
  `价格`,
  `图书名称` 
FROM
  `图书信息` 
WHERE>
`出版社ID`!=4 AND `价格`<ANY(SELECT `价格` FROM `图书信息` WHERE `出版社ID`=2)
#查出其他出版社的图书 出比4号出版设图书价格高
SELECT
  `ISBN编号`,
  `价格`,
  `图书名称` 
FROM
  `图书信息` 
WHERE
  `价格` >  (
  SELECT
    MIN(`价格`)
  FROM
    `图书信息` 
WHERE
  `出版社ID`=3
  );  
  #查询图书信息表中出版社的出版设ID
  select `出版社ID`,`出版社简称`,`出版社名称`
  FROM `出版社`
  where `出版社ID`IN(select `出版社ID`FROM `图书信息`);
USE student;
/*
INSERT INTO `出版社2`
VALUES
  ( '6', '上海', '上海教读取', '上海是128号', '34567' ) ,( '7', '上海', '上海教读', '上海是1283号', '345673' );
  DELETE
  FROM `出版社`
  WHERE `出版社简称`='上海交大';
  SELECT *
  from `出版社`
  update `图书信息`
  set  `价格`=`价格`+20,`图书类型`='f'
  where  `价格`>=40;
  select *
  from `出版社2`;
  update `图书信息`
  SET `价格`=`价格`+10
  WHERE  `ISBN编号`in (select `ISBN编号`);
  UPDATE `图书信息`
  set `价格`=`价格`-10 
  where  `ISBN编号` in (SELECT `ISBN编号`
  from `藏书信息`
  WHERE ``藏书信息`= 'A-1-1'
  );

USE student;
SET @a := 0;
SET @a := @a + 10;
SELECT
  @a;
SET @v := 0;
SET @v := @v + 130;
SELECT
  @v;
SET @f = 'fghjjkk';
SELECT
  @f;
SELECT
  @u := 34;
SELECT
  @g = 67;
SELECT
  @u = 34;
SELECT
  @g = 56;#方式一
SET @price = 0.0;
SELECT
  `价格` INTO @price 
FROM
  `图书信息` 
WHERE
  `作者` = '陈欢欢';
SELECT
  @price;#方式二
SET @price = 0.0;
SET @price =(
  SELECT
    `价格` 
  FROM
    `图书信息` 
  WHERE
    `作者` = '刘国良';
);
SELECT
  @price;
USE student;
/*
SET @price = 0.0;
SELECT
  `价格` INTO @price 
FROM
  `图书信息` 
WHERE
  `作者` = '陈欢欢';
SELECT
  @price;
/*
#方式二
SET @price = 0.0; SET @price =( SELECT `价格` FROM `图书信息` WHERE `作者` = '刘国良'; );
select
  @price;
SET @bcount (*),
@price_avg = 0.0;
SELECT
  count(*) avg( `价格` ) INTO @bcount,
  @price_avg 
FROM
  `图书信息`； SELECT
  @bcount,
  @price_avg;
  */
/*
set @name = "电子工业出版社";
SET @id =(
  SELECT
    `出版社ID` 
  FROM
    `出版社` 
  WHERE
    `出版社名称` = "电子工业出版社";
);
SET @num =(
  SELECT
    Count(*) 
  FROM
    `图书信息` 
  WHERE
    `出版社ID` = @id 
  );
SELECT
  @name,
  @id,
  @num;
*/  
set @num=0;
SET @name:= "电子工业出版社";
SET @num =(
SELECT
    Count(*) 
  FROM
    `图书信息` 
  WHERE
    `出版社ID` =(SELECT  `出版社ID`
    from `出版社`
    where `出版社名称`=@name );
SELECT  @name,@num;
  USE student;
/
CREATE 
  OR REPLACE ALGORITHM = UNDEFINED DEFINER = `root` @`localhost` SQL SECURITY DEFINER VIEW `student`.`view2` AS SELECT
  `出版社`.`出版社ID` AS `出版社ID`,
  `出版社`.`出版社名称` AS `出版社名称`,
  `出版社`.`出版社简称` AS `出版社简称`,
  `图书信息`.`ISBN编号` AS `ISBN编号`,
  `图书信息`.`图书名称` AS `图书名称`,
  `图书信息`.`作者` AS `作者`,
  `图书信息`.`价格` AS `价格` 
FROM
  (
    `出版社`
    JOIN `图书信息` ON ((
      `出版社`.`出版社ID` = `图书信息`.`出版社ID` 
  )));
  */#多表
CREATE VIEW view2 AS SELECT
`出版社`.`出版社ID`;
`出版社`.`出版社名称`;
`图书信息`.`ISBN编号`;
`图书信息`.`作者`;
`图书信息`.`价格` 
FROM
  `出版社`
  INNER JOIN `图书信息` ON `出版社`.`出版社ID` = `图书信息`.`出版社ID`;
*/  
  #INSERT INTO view2('78','上海交大','786455464433','c++','hu吗'43)；
  UPDATE view2
  SET `价格`=35
  WHERE `作者`='陈承欢';
  */
  UPDATE view2
  SET `价格`=35,`出版社ID`.`出版社ID`+12
  WHERE `作者`='陈承欢';
  DELETE
  FROM view2
  WHERE `作者`='陈承欢';
SELECT *
FROM view2;
USE student;#查询  出版社名称`,`出版社简称`
SELECT DISTINCT
  `出版社名称`,
  `出版社简称` 
FROM
  `出版社`,
  `图书信息` 
WHERE
  `出版社`.`出版社ID` = `图书信息`.`出版社ID` 
  AND `价格` > 30;#条件在一张表，jie kou zailingyizhangbiao
SELECT DISTINCT
  `出版社名称`,
  `出版社简称` 
FROM
  `出版社`
  INNER JOIN `图书信息` ON `出版社`.`出版社ID` = `图书信息`.`出版社ID` 
WHERE
  `价格` > 30;
SELECT DISTINCT
  `出版社名称`,
  `出版社简称` 
FROM
  `出版社` 
WHERE
  `出版社ID` IN ( SELECT `出版社ID` FROM `图书信息` WHERE `价格` >= 30; );#查询作者性王青青的图书的出版社ID 出版社简称  图书名称
SELECT DISTINCT
  `出版社名称`,
  `出版社简称` 
FROM
  `出版社` 
WHERE
  `出版社ID` IN ( SELECT `出版社ID` FROM `图书信息` WHERE `作者` = "王青青" );
/*
SELECT DISTINCT
  `出版社名称`,
  `出版社简称` 
FROM
  `出版社` 
WHERE
  `出版社ID` IN ( SELECT `出版社ID` FROM `图书信息` WHERE ( SELECT `藏书位置` FROM `藏书信息` ) WHERE `藏书位置` = 'A-1-1' );
  */

学习的道路还有很长呢!!!!

MYSQL语句。

一些常用的操作内容，非常重要。首先从数据的增删改查谈起。

这里是基本的数据操作内容以及语法

代码

学习的道路还有很长呢!!!!

热门文章

最新文章

相关课程

相关电子书

相关实验场景

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

MYSQL语句。

一些常用的操作内容，非常重要。首先从数据的增删改查谈起。

这里是基本的数据操作内容以及语法

代码

学习的道路还有很长呢!!!!

热门文章

最新文章

相关课程

相关电子书

相关实验场景