【MySQL】DML，DQL

3、MySQL数据管理

3.1.外键（了解）

1. 在创建表的时候，增加约束

-- 学生表的gradeid 字段要去引用年纪表的gradeid
-- 定义外键key
-- 给这个外键添加约束（执行引用）
CREATE TABLE IF NOT EXISTS `student` (
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
`sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
`gradeid` INT(10) NOT NULL COMMENT '年纪id',

PRIMARY KEY(`id`),
KEY `FK_gradeid` (`gradeid`),
CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

删除有外键关系的表，要先删除引用别人的表，在删除被引用的表
方式二：

-- 创建表的时候没有外键关系
ALTER TABLE `student` ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`);

-- alter TABLE 表 ADD CONSTRAINT 约束名 FOREIGN KEY(作为外键的列）REFERENCES 原表（原表的字段）

最佳实践：
数据库就是单纯的表，行代表数据，列表示字段。
如果想使用多张表的数据，使用外键用程序实现。

3.2.DML语言（熟悉）

DML语言：数据操作语言

insert

语法：

-- 插入数据
-- insert into 表名（字段名） values （‘值’）
INSERT INTO `grade`(`gradename`) VALUES ('大四')

-- 由于主键自增我们可以省略（如果不写表的字段，就会一一匹配）
INSERT INTO `grade` VALUES ('2','大四')

-- 一般写插入语句，要一一对应
INSERT INTO `grade`(`gradename`) VALUES ('大二'),('大一')

INSERT INTO `student`(`name`) VALUES ('张三')
INSERT INTO `student`(`name`,`pwd`,`sex`) VALUES ('玛丽','835','女')
INSERT INTO `student` VALUES ('3','小如','822','女','2001-07-23','3')

注：1.字段和字段用英文隔开
2.字段可以省略，但是后面的值要一一对应
3.可以同时插入多条数据，但是values后面的值要用逗号隔开。

update修改

语法：
UPDATE 表明 set 列名=值 WHERE 条件

-- 修改学员名字,指定条件
UPDATE `student` SET `name`='小马' WHERE id=1

-- 不指定条件，全部修改
UPDATE `student` SET `name`='小马' 

-- 修改多个属性
UPDATE `student` SET `name`='小马',`sex`='男人' WHERE id=1

-- 通过多个条件定位数据
UPDATE `student` SET `name`='阿耶莎' WHERE `sex`='女' AND `name`='匿名'

-- value的值设成变量
UPDATE `student` SET birthday=CURRENT_TIME WHERE `name`='玛丽'

操作符	含义	范围	结果
=	等于	5==6	false
！=或者<>	等于	5<>6	true
>
<
>=
<=
BETWEEN…AND…	在某个范围内
AND
OR

注意：
条件：筛选的条件，如果没有指定，则会修改所有的列
value是一个具体的值，也可以是变量（一般是时间）
多个设置的属性之间用逗号隔开。

delete 删除

-- 删除数据
DELETE FROM `student` WHERE id=1

TRUNCATE 命令
区别：
相同点：不会删除表结构
不同：
TRUNCATE：不会影响事务，重新设置自增列，计数器会归零

DELETE FROM `test` -- 不影响自增
TRUNCATE TABLE `test`  -- 自增会归零

4. DQL查询数据（熟悉）

4.1 DQL数据查询语言（data query language）

数据库最核心语言
建库：

CREATE DATABASE IF NOT EXISTS `school`;
-- 创建一个school数据库
USE `school`;-- 创建学生表
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`(
	`studentno` INT(4) NOT NULL COMMENT '学号',
    `loginpwd` VARCHAR(20) DEFAULT NULL,
    `studentname` VARCHAR(20) DEFAULT NULL COMMENT '学生姓名',
    `sex` TINYINT(1) DEFAULT NULL COMMENT '性别，0或1',
    `gradeid` INT(11) DEFAULT NULL COMMENT '年级编号',
    `phone` VARCHAR(50) NOT NULL COMMENT '联系电话，允许为空',
    `address` VARCHAR(255) NOT NULL COMMENT '地址，允许为空',
    `borndate` DATETIME DEFAULT NULL COMMENT '出生时间',
    `email` VARCHAR (50) NOT NULL COMMENT '邮箱账号允许为空',
    `identitycard` VARCHAR(18) DEFAULT NULL COMMENT '身份证号',
    PRIMARY KEY (`studentno`),
    UNIQUE KEY `identitycard`(`identitycard`),
    KEY `email` (`email`)
)ENGINE=MYISAM DEFAULT CHARSET=utf8;
INSERT INTO `student` (`studentno`,`loginpwd`,`studentname`,`sex`,`gradeid`,`phone`,`address`,`borndate`,`email`,`identitycard`)
VALUES
(1000,'123456','张伟',0,2,'13800001234','北京朝阳','1980-1-1','text123@qq.com','123456198001011234'),
(1001,'123456','赵强',1,3,'13800002222','广东深圳','1990-1-1','text111@qq.com','123456199001011233');

DROP TABLE IF EXISTS `subject`;
CREATE TABLE `subject`(
	`subjectno`INT(11) NOT NULL AUTO_INCREMENT COMMENT '课程编号',
    `subjectname` VARCHAR(50) DEFAULT NULL COMMENT '课程名称',
    `classhour` INT(4) DEFAULT NULL COMMENT '学时',
    `gradeid` INT(4) DEFAULT NULL COMMENT '年级编号',
    PRIMARY KEY (`subjectno`)
)ENGINE = INNODB AUTO_INCREMENT = 19 DEFAULT CHARSET = utf8;

INSERT INTO `subject`(`subjectno`,`subjectname`,`classhour`,`gradeid`)VALUES
(1,'高等数学-1',110,1),
(2,'高等数学-2',110,2),
(3,'高等数学-3',100,3),
(4,'高等数学-4',130,4),
(5,'C语言-1',110,1),
(6,'C语言-2',110,2),
(7,'C语言-3',100,3),
(8,'C语言-4',130,4),
(9,'Java程序设计-1',110,1),
(10,'Java程序设计-2',110,2),
(11,'Java程序设计-3',100,3),
(12,'Java程序设计-4',130,4),
(13,'数据库结构-1',110,1),
(14,'数据库结构-2',110,2),
(15,'数据库结构-3',100,3),
(16,'数据库结构-4',130,4),
(17,'C#基础',130,1);

-- 创建年级表
DROP TABLE IF EXISTS `grade`;
CREATE TABLE `grade`(
	`gradeid` INT(11) NOT NULL AUTO_INCREMENT COMMENT '年级编号',
  `gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
    PRIMARY KEY (`gradeid`)
) ENGINE=INNODB AUTO_INCREMENT = 6 DEFAULT CHARSET = utf8;

-- 插入年级数据
INSERT INTO `grade` (`gradeid`,`gradename`) VALUES(1,'大一'),(2,'大二'),(3,'大三'),(4,'大四'),(5,'预科班');


-- 创建成绩表
DROP TABLE IF EXISTS `result`;
CREATE TABLE `result`(
	`studentno` INT(4) NOT NULL COMMENT '学号',
    `subjectno` INT(4) NOT NULL COMMENT '课程编号',
    `examdate` DATETIME NOT NULL COMMENT '考试日期',
    `studentresult` INT (4) NOT NULL COMMENT '考试成绩',
    KEY `subjectno` (`subjectno`)
)ENGINE = INNODB DEFAULT CHARSET = utf8;

-- 插入成绩数据  这里仅插入了一组，其余自行添加
INSERT INTO `result`(`studentno`,`subjectno`,`examdate`,`studentresult`)
VALUES
(1000,1,'2013-11-11 16:00:00',85),
(1000,2,'2013-11-12 16:00:00',70),
(1000,3,'2013-11-11 09:00:00',68),
(1000,4,'2013-11-13 16:00:00',98),`grade`
(1000,5,'2013-11-14 16:00:00',58);

4.2 指定查询字段

简单查询（单表）

-- 查询全部的学生
SELECT * FROM result

-- 查询指定字段
SELECT `studentno`,`studentname` FROM student

-- 给结果起个别名，可以给字段起别名，也可以给表起别名。
SELECT `studentno` AS 学号,`studentname` AS 学生姓名 FROM student AS s

-- 函数 Concat（a,b)
SELECT CONCAT('姓名：',studentname) AS 新名字 FROM student

有的时候，列名可以起别名 AS 字段名 as 别名 表明 as 别名

去重

-- 去重
-- 查询有哪些同学参加了考试
SELECT * FROM result -- 查询全部的考试成绩
-- 查询有哪些同学参加了考试
SELECT `studentno` FROM result 	
-- 发现重复数据，去重
SELECT DISTINCT `studentno` FROM result 

表达式

-- 查询系统版本
SELECT VERSION()	
SELECT @@auto_increment_increment -- 查询自增的步长

-- 学员考试成绩加一
SELECT `studentno`,`studentresult`+1 AS '提分后' FROM result

4.3 where条件子句

作用：检索数据中符合条件的值

逻辑运算符

运算符	语法	描述
and &&	a and b a&&b	逻辑与
or	a or b	逻辑或
not ！	not a ！a	逻辑非

比较运算符（模糊查询）

运算符	语法	描述
IS NULL
IS NOT NULL
BETWEEN…AND…	a between b and c	若a在b和c之间，则结果为真
LIKE	a like b	SQL匹配，如果a匹配b，则结果为真
IN	a in（a1,a2,a3…)	包含

-- 模糊查询
SELECT studentno,`studentresult` FROM result
WHERE `studentresult` BETWEEN 80 AND 100
-- 查询姓张的学生
-- like % 表示任意字符，_表示一个字符
SELECT studentno,studentname FROM student
WHERE studentname LIKE '张%'

-- 查询姓张，名字一共为两个字的学生
SELECT studentno,studentname FROM student
WHERE studentname LIKE '张_'

-- 查询名字中带伟的学生
SELECT studentno,studentname FROM student
WHERE studentname LIKE '%强%'


-- ========in===========
-- 查询1001，1002，1003号学员
SELECT studentno,studentname FROM student
WHERE studentno IN (1001,1002,1003);

-- 查询在北京的学生
SELECT studentno,studentname FROM student
WHERE address IN ('北京');-- IN里面不能用%

-- ======null 和 not nuoll=========
-- 查询email为空的学生
SELECT studentno,studentname FROM student
WHERE email='' OR email IS NULL;

-- 查询出生日期不为空的学生
SELECT studentno,studentname FROM student
WHERE borndate IS NOT NULL

4.4 联表查询

JOIN 对比

-- ===========联表查询 join==========
-- 查询参加了考试的同学（学号，姓名，科目编号，分数）
SELECT * FROM student
SELECT * FROM result

/*
思路：
1.看看用到那个表
2.确定使用哪种连接查询。
确定交叉列
判断条件：学生表中的studentno = 成绩表中的studentno

*/
SELECT s.studentno,studentname,subjectno,studentresult
FROM student AS s
INNER JOIN result AS r
WHERE s.studentno=r.studentno 

-- RIGHT JOIN
SELECT s.studentno,studentname,subjectno,studentresult
FROM student AS s
RIGHT JOIN result AS r
ON s.studentno=r.studentno

-- LEFT JOIN
SELECT s.studentno,studentname,subjectno,studentresult
FROM student AS s
LEFT JOIN result AS r
ON s.studentno=r.studentno

操作	描述
INNER JOIN	如果如果表中至少有一个匹配，就返回行
LEFT JOIN	会从左表中返回所有的值，即使右表没有匹配
RIGHT JOIN	会从右表中返回所有的值，即使右表没有匹配

-- 查询缺考的同学
SELECT s.studentno,studentname,subjectno,studentresult
FROM student AS s
LEFT JOIN result AS r
ON s.studentno=r.studentno
WHERE studentresult IS NULL 

-- 思考：查询了参加考试的同学的信息。（学号，姓名，科目名称，分数）
/*
思路：
1.看看用到那个表 student ，result,subject
2.确定使用哪种连接查询。
确定交叉列
判断条件：学生表中的studentno = 成绩表中的studentno
join on 连接查询
where 等值查询

*/

SELECT s.studentno,studentname,subjectname,studentresult
FROM student s
RIGHT JOIN result r
ON r.studentno=s.studentno
INNER JOIN `subject` AS sub
ON r.subjectno=sub.subjectno

-- 我要查询哪些数据 selcet
-- 从哪几个表中查 from 表 xxx join 链接的表 on 交叉条件
-- 假设存在多张表查询，多次连接

-- from a left join b
-- from b right join b以右表为基准。

自连接（了解）

一张表拆为两张一样的表
父类：

categoryid	categoryName
2	信息技术
3	软件开发
5	美术设计

子类：

pid	categoryid	categoryName
3	4	数据库
2	8	办公信息
3	6	web开发
5	7	美术设计

操作：
查询父类对应的子类

父类	子类
信息技术	办公信息
软件开发	数据库
软件开发	web开发
美术设计	美术设计

-- 查询父子信息:把一张表看为两个一模一样的表
SELECT a.`categoryname` AS '父栏目',b.`categoryname` AS '子栏目'
FROM `category` AS a,`category` AS b
WHERE a.`categoryid` = b.`pig`

联表查询练习：

-- 查询学员所属的年级（学号，学生的姓名，年纪名称）
SELECT studentno,studentname,gradename
FROM student s
RIGHT JOIN `grade` g
ON s.gradeid=g.gradeid

-- 查询科目所属的年级（科目名称，年纪名称）
SELECT subjectname,gradename
FROM `subject` AS s
INNER JOIN grade AS g
ON s.gradeid=g.gradeid


-- 查询参加了数据库结构-2考试人的所有信息（学号，姓名，科目名，分数）
SELECT	s.studentno,studentname,subjectname,studentresult
FROM student s
INNER JOIN `subject` sub
ON s.gradeid=sub.gradeid
INNER JOIN result
ON s.studentno=result.`studentno`
WHERE sub.subjectname='数据库结构-2'

4.5 分页和排序

升序和降序

-- 排序：升序asc ，降序desc
-- oreder by 字段 升降序


-- 查询参加了数据库结构-2考试人的所有信息（学号，姓名，科目名，分数）
-- 根据成绩排序
SELECT	s.studentno,studentname,subjectname,studentresult
FROM student s
INNER JOIN `subject` sub
ON s.gradeid=sub.gradeid
INNER JOIN result
ON s.studentno=result.`studentno`
WHERE sub.subjectname='数据库结构-2'
ORDER BY studentresult DESC

分页

-- 为什么要分页？缓解数据库压力，体验更好，图片会使用瀑布流

-- 分页，每页只显示5条数据
-- 语法：limit 起始页,页面大小(注意逗号不要省）
SELECT	s.studentno,studentname,subjectname,studentresult
FROM student s
INNER JOIN `subject` sub
ON s.gradeid=sub.gradeid
INNER JOIN result
ON s.studentno=result.`studentno`
WHERE sub.subjectname='数据库结构-2'
ORDER BY studentresult DESC
LIMIT 2,2

-- 第一页 limit 0，5
-- 第二页 limit 5，5
-- 第三页 limit 10，5
-- 第n页 limit pageSize（n-1），pageSize

练习

-- 查询java第一学年 课程成绩排名前10的学生，并且分数要大于80的学生信息（学号，姓名，科目，成绩）
SELECT s.studentno,studentname,subjectname,studentresult
FROM student s
INNER JOIN `subject` sub
ON s.gradeid=sub.gradeid
INNER JOIN result r
ON r.studentno=s.studentno
WHERE subjectname='C语言-2' AND studentresult>80
ORDER BY studentresult DESC
LIMIT 0,10

4.6 子查询

where（这个值是计算出来的）
本质：在where语句中嵌套一个子查询

-- 1. 查询数据库结构-1 的所有考试结果（学号，科目，成绩），降序排列
-- 方式1 ：使用连接查询
SELECT r.studentno,r.subjectno,studentresult
FROM result r
INNER JOIN `subject` sub
ON r.`subjectno`=sub.`subjectno`
WHERE subjectname='C语言-1'
ORDER BY studentresult DESC

-- 使用子查询(由里及外）(嵌套）
SELECT studentno,subjectno,studentresult
FROM result
WHERE subjectno =(
SELECT subjectno FROM `subject` WHERE subjectname='C语言-1'
)

-- 查询所有 C语言-1 的学生的学号
SELECT subjectno FROM subjectno WHERE subname='C语言-1'

-- 分数不少于80分的学生的学号和姓名
SELECT s.studentno,studentname
FROM student s
INNER JOIN result r
ON r.studentno=s.studentno
WHERE r.studentresult>80

-- 第二种方法
SELECT s.studentno,studentname
FROM student s
INNER JOIN result r
ON r.studentno=s.studentno
WHERE studentresult IN(
SELECT studentresult 
FROM result 
WHERE studentresult>80

)

4.7 分组和过滤

-- 查询不同课程的平均分，最高分，最低分
-- 核心：（根据不同的课程分组）
SELECT subjectname,AVG(studentresult),MAX(studentresult),MIN(studentresult)
FROM result r
INNER JOIN `subject` s
ON r.subjectno=s.subjectno
GROUP BY r.subjectno -- 通过什么字段来分组
HAVING AVG(studentresult)>80  -- 分组后的过滤条件

小结

-- ================== select =================

SELECT 去重 要查询的字段 FROM 表
xxx JOIN 要连接的表 ON 等值判断
where（具体的值，子查询语句）
GROUP by（通过哪个字段分组）
HAVING （过滤分组后的信息）
ORDER by（通过哪个字段排序）【升序还是降序】
LIMIT startindex,pagesize

业务层面：
查询：跨表，跨数据库，