目录
（3）列子查询
（6）总结
一、构建数据库
1、概念
重点：DQL是我们每天都要接触编写最多也是最难的SQL，该语言用来查询记录，它不会修改数据库和表结构。
2、新建四张表
（1）student表
DROP TABLE IF EXISTS student;/*如果存在student表就删除*/ CREATE TABLE `student` ( `id` INT ( 10 ) PRIMARY KEY,/*主键*/ `name` VARCHAR ( 10 ), `age` INT ( 10 ) NOT NULL, `gender`VARCHAR(2) );
（2）course表
DROP TABLE IF EXISTS course; CREATE TABLE `course` ( `id` INT ( 10 ) PRIMARY KEY,/*主键*/ `name` VARCHAR ( 10 ), `t_id` INT(10) );
（3）teacher表
DROP TABLE IF EXISTS teacher; CREATE TABLE `teacher` ( `id` INT ( 10 ) PRIMARY KEY,/*主键*/ `name` VARCHAR ( 10 ) );
（4）scores表
DROP TABLE IF EXISTS scores; CREATE TABLE `scores` ( `s_id` INT ( 10 ), `score` INT ( 10 ), `c_id`int(10), PRIMARY KEY(s_id,c_id) );
（5）给四张表插入数据
insert into student (id,name,age,gender)VALUES(1,'张小明',19,'男'),(2,'李小红',19,'男'),(3,'小刚',24,'男'),(4,'小龙',11,'男'),(5,'小丽',18,'男'),(6,'张小军',18,'女'),(7,'小航',16,'男'),(8,'小亮',23,'男'),(9,'小杰',22,'女'),(10,'李小虎',21,'男'); insert into course (id,name,t_id)VALUES(1,'数学',1),(2,'语文',2),(3,'c++',3),(4,'java',4),(5,'php',null); insert into teacher (id,name)VALUES(1,'Tom'),(2,'Jerry'),(3,'Tony'),(4,'Jack'),(5,'Rose'); insert into scores (s_id,score,c_id)VALUES(1,80,1); insert into scores (s_id,score,c_id)VALUES(1,56,2); insert into scores (s_id,score,c_id)VALUES(1,95,3); insert into scores (s_id,score,c_id)VALUES(1,30,4); insert into scores (s_id,score,c_id)VALUES(1,76,5); insert into scores (s_id,score,c_id)VALUES(2,35,1); insert into scores (s_id,score,c_id)VALUES(2,86,2); insert into scores (s_id,score,c_id)VALUES(2,45,3); insert into scores (s_id,score,c_id)VALUES(2,94,4); insert into scores (s_id,score,c_id)VALUES(2,79,5); insert into scores (s_id,score,c_id)VALUES(3,65,2); insert into scores (s_id,score,c_id)VALUES(3,85,3); insert into scores (s_id,score,c_id)VALUES(3,37,4); insert into scores (s_id,score,c_id)VALUES(3,79,5); insert into scores (s_id,score,c_id)VALUES(4,66,1); insert into scores (s_id,score,c_id)VALUES(4,39,2); insert into scores (s_id,score,c_id)VALUES(4,85,3); insert into scores (s_id,score,c_id)VALUES(5,66,2); insert into scores (s_id,score,c_id)VALUES(5,89,3); insert into scores (s_id,score,c_id)VALUES(5,74,4); insert into scores (s_id,score,c_id)VALUES(6,80,1); insert into scores (s_id,score,c_id)VALUES(6,56,2); insert into scores (s_id,score,c_id)VALUES(6,95,3); insert into scores (s_id,score,c_id)VALUES(6,30,4); insert into scores (s_id,score,c_id)VALUES(6,76,5); insert into scores (s_id,score,c_id)VALUES(7,35,1); insert into scores (s_id,score,c_id)VALUES(7,86,2); insert into scores (s_id,score,c_id)VALUES(7,45,3); insert into scores (s_id,score,c_id)VALUES(7,94,4); insert into scores (s_id,score,c_id)VALUES(7,79,5); insert into scores (s_id,score,c_id)VALUES(8,65,2); insert into scores (s_id,score,c_id)VALUES(8,85,3); insert into scores (s_id,score,c_id)VALUES(8,37,4); insert into scores (s_id,score,c_id)VALUES(8,79,5); insert into scores (s_id,score,c_id)VALUES(9,66,1); insert into scores (s_id,score,c_id)VALUES(9,39,2); insert into scores (s_id,score,c_id)VALUES(9,85,3); insert into scores (s_id,score,c_id)VALUES(9,79,5); insert into scores (s_id,score,c_id)VALUES(10,66,2); insert into scores (s_id,score,c_id)VALUES(10,89,3); insert into scores (s_id,score,c_id)VALUES(10,74,4); insert into scores (s_id,score,c_id)VALUES(10,79,5);
二、单表查询
1、查询所有记录
基本语法（注意：在开发中，严禁使用select * from）
select * from 表名;select * from student;
2、查询指定的列
select `id`, `name`,`age`, `gender` from student; select `id`, `name`, `age` from student;
3、去重
如果表中有完全重复的记录只显示一次，在查询的列之前加上distinct。
select DISTINCT `字段名` from `表名`;
4、列运算
select `id`,`name`,age/10 from student;
注意:
①我们写的所有的查询语句,最终执行的结果,都是生成一张虚拟表。
②null值和任何值做计算都为null ，null不意味着0。比如在员工表中，给工资都加1000，有工资的加减，有的员工工资初始值，设置了空值。
select id, `name`,sa1+1000 from emp emloyee;null值和任何值做计算都为null ,需要用到函数ifnull()函数。如果薪资是空,则为0。将字符串做加减乘除运算,会把字符串当0处理。
select IFNULL(sal,0) + 1000 from employee;5、别名
我们可以给列起别名， 因为我们在查询过程中,列名很可能重复,可能名字不够简洁,或者列的名字不能满足我们的要求。
select `id`as `编号`, `name` as`姓名^ ,`age`as `年龄` ,`gender`as `性别` from student ;除了使用关键字 as 来给表或是 列起别名外，还可以直接使用空格字符达到同样的效果
select `id` `编号`, `name` `姓名^ ,`age` `年龄` ,`gender` `性别` from student ;
6、条件控制
（1）条件查询
在后面添加where指定条件
select * from student where id = 3;/*查询student表中所有id=3的信息*/ select * from student where id in (1,3,5);/*查询student表中所有id为1，3，5的信息*/ select * from student where id > 2;/*查询student表中所有id>2的信息*/ select * from student where id BETWEEN 3 and 5;/*查询student表中id为在3到5之间的信息*/ select * from student where id BETWEEN 6 and 7 or age > 20; /*查询student表中id为6到7，age>20的信息*/（2）模糊查询
通过通配符的限制条件查询到想要的信息。用到【like】关键字
通配符：_下划线代表一个字符，%百分号代表任意字符。
select * from `student` where `name` like '张%' ;/*姓张的所有同学*/ select * from `student` where `name` like '张_' ;/*姓张的两个字同学*/ select * from `student` where `name` like '%明%';/*随便多少个字，中间有明*/ select * from `student` where `name` like '_明_';/*三个字，中间有明*/7、排序
（1）升序---ASC(ASC是可以省略)
select * from student ORDER BY age ASC ;（2）降序---DESC
select * from student ORDER BY age DESC;（3）使用多列作为排序条件
当第一个排序条件相同时,根据第二列排序条件进行排序(第二列如果还相同, 以此类推…… )
select * from student ORDER BY age asc,id desc;8、聚合函数
（1）count
-- count() 统计表中记录数量，count()用于行数的统计。
SELECT COUNT(列名) FROM student; -- count(字段),忽略所有null值 SELECT COUNT(*) FROM student;-- count(*) ,不会忽略null值, 本质计算行数 SELECT COUNT(1) FROM student;-- count(1) ,不会忽略null值, 本质计算行数查询满足条件的记录行数，后边可以跟where条件。如果满足条件的行数为空，不会进行统计。如果我们要统计真实有效的记录数，最好不要用可以为空列。
count(*) ：推荐使用count(主键) ：推荐使用
count(1) ：不推荐使用
select count(id) from student where gender='男';参考连接：
（2）max
查询满足条件的记录中的最大值，后面可以跟where条件。
select max(age) from student where gender='女';（3）min
查询满足条件的记录中的最小值，后面可以跟where条件。
select MIN(age) from student where gender='男';（4）sum
查询满足条件的记录的和，后面可以跟where条件。
select sum(age) from student where gender='男';（5）avg
查询满足条件的记录的平均数,后面可以跟where条件。
select avg(score) from scores where c_id = 3;SQL中sum()和count()的区别
https://blog.csdn.net/banjw_129/article/details/81136966
5、分组查询（有难度）
（1）概念
顾名思义:分组查询就是将原有数据进行分组统计。
举例:
将班级的同学按照性别分组，统计男生和女生的平均年龄。select 分组列名,聚合函数1,聚合函数2... from 表名 group by 该分组列名;分组要使用关键词group by ,后面可以是一列 ,也可以是多个列,分组后查询的列只能是分组的
列,或者是使用了聚合函数的其他的列,剩余列不能单独使用。
--根据性别分组，查看每一组的平均年龄和最大年龄select gender,avg(age),max(age) from student group by gender ;--根据专业号分组，查看每一个专业的平均分
select C_id,avg(score) from scores group by C_id;我们可以这样理解: 一旦发生了分组,我们查询的结果只能是所有男生的年龄平均值、最大值，而不能是某一个男生的数据。
（2）分组查询前
分组查询前，可以通过关键字[where]先把满足条件的人分出来，再分组。
select 分组列,聚合函数1... from 表名 where 条件 group by 分组列;select C_ id,avg(score) from scores where C_id in (1,2,3) group by C_id;（3）分组查询后
分组查询后,也可以通过关键字[having]把组信息中满足条件的组再细分出来。
select 分组列,聚合函数1... from 表名 where 条件 group by 分组列 having 聚合函数或列 名(条件) ;select gender,avg(age) ,sum(age)`sum_age` from student GROUP BY gender HAVING `sum_ age` > 50;（4）面试题: where和having的区别?
①where是写在group by之前的筛选,在分组前筛选; having是写在group by之后,分组
后再筛选。②where只能使用分组的列作为筛选条件; having既可以使用分组的列,也可以使用聚合函数列作为筛选条件。
6、分页查询
（1）limit字句
用来限定查询结果的起始行，以及总行数。limit是MySQL独有的语法。
●如果只有一个参数，说明从起始位置查找4条记录。
●如果两个参数,说明从第4行下一行，向后查找3条记录。select * from student limit 4,3; select * from student limit 4;（2）面试题：不同数据库的特有查询语法
●MySQL : limit
●Oracle : rownum
●SqlServer : top（3）分页查询案例分析
student表中有10条数据，如果每页显示4条,分几页?3页
3页怎么来的? (int)(Math.cil(10 / 4));
显示第一页的数据:select * from student limit 0,4;显示第二页的数据:
select * from student limit 4,4;显示第三页的数据:
select * from student limit 8,4;7、案例
需求：想要判断在student表中有没有叫“小红”的这个人
1.0版本
select * from student where name ='小红' ; select id from student where name ='小红' ;2.0版本
select count(id) from student where name = '小红';3.0版本
select id from student where name = '小红' limit 1;注意：Limit字句永远是在整个的sql语句的最后。
https://blog.csdn.net/wangruoao/article/details/107483638三、多表查询
1、笛卡尔乘积
select * from 表名1,表名2;如果两个表没有任何关联关系,我们也不会连接这两张表。如果按上面的语法连接就会出现笛卡尔乘积，会生成一张虚拟表，这张虚拟表的数据就是表1和表2两张表数据的乘积。
注意：开发中，一定要避免出现笛卡儿积。
select * from student,teacher;
2、多表连接的方式
（1）内连接
（2）外连接
（3）全连接
（4）子查询
2、SQL92语法
1992年的语法。
（1）二表查询
-- 查询学号，姓名，年龄，分数，通过多表连接查询，student和scores通过id和s_id连接
SELECT stu.id 学号, stu.name 姓名, stu.age 年龄, sc.score 分数 FROM student stu, scores sc WHERE stu.id = sc.s_id;（2）三表查询
-- 查询学号，姓名，年龄，分数，科目名称，通过多表查询，student和scores，course
SELECT stu.`id` 学号, stu.`name` 姓名, stu.`age` 年龄, sc.`score` 分数, c.`name` 科目 FROM /*给表取个别名*/ student stu, scores sc, course c WHERE stu.id = sc.s_id AND c.id = sc.c_id;（3）四表查询
-- 查询学号，姓名，年龄，分数，科目名称，老师名称，通过多表查询，student和scores，course，teacher
SELECT stu.`id` 学号, stu.`name` 姓名, stu.`age` 年龄, sc.`score` 分数, c.`name` 科目, t.`name` 老师 FROM student stu, scores sc, course c, teacher t WHERE stu.id = sc.s_id AND c.id = sc.c_id AND c.t_id = t.id;（4）综合查询
-- 查询老师的信息以及对应教的课程
SELECT t.id 教师号, t.NAME 教师姓名, c.NAME 科目名 FROM teacher t, course c WHERE t.id = c.t_id;SQL92语法,多表查询,如果有数据为null ,会过滤掉，SQL92也存在笛卡儿积问题，所以这个语法现在淘汰了。
（5）案例示例
①--查询学号，姓名，年龄，分数，科目名称，通过多表查询，student和scores, course
--在查询的基础上，进一步筛选，筛选小红和张小军的成绩。SELECT stu.`id` 学号, stu.`name` 姓名, stu.`age` 年龄, sc.`score` 分数, c.`name` 科目 FROM student stu, scores sc, course c WHERE stu.id = sc.s_id AND c.id = sc.c_id AND stu.`name` in ('小红','张小军');②--查询学号，姓名，年龄，分数，科目名称，通过多表查询，student和scores, course
--在查询的基础上，进一步筛选， 筛选小红和张小军的成绩
--在小红和张小军成绩的基础上进一步再筛选， 筛选他们的java成绩SELECT stu.`id` 学号, stu.`name` 姓名, stu.`age` 年龄, sc.`score` 分数, c.`name` 科目 FROM student stu, scores sc, course c WHERE stu.id = sc.s_id AND c.id = sc.c_id AND stu.`name` in ('小红','张小军') AND c.`name` = 'java';③--查询学号，姓名，年龄，分数，科目名称，通过多表查询，student和scores, course
--找出最低分和最高分，按照科目分组，每一科
SELECT sc.c_id, max( score ), min( score ), c.`name` FROM scores sc, course c WHERE sc.c_id = c.id GROUP BY sc.c_id;3、SQL99语法
1999年的语法。
（1）内连接
在我们刚才的sq|当中,使用逗号分隔两张表进行查询，mysq|进行优化默认就等效于内连接。
使用[join] 关键字,使用[on]来确定连接条件。[where] 只做筛选条件。SELECT t.*, c.*, sc.* FROM teacher t JOIN course c on c.t_id = t.id JOIN scores sc on sc.c_id = c.id;（2）外连接（常用）
①左连接
以左侧的表为基表，显示基表的所有行、列，外表如果条件不匹配则外表中所有字段显示为null
SELECT t.*, c.* FROM teacher t LEFT JOIN course c ON t.id = c.t_id;②右连接
以右侧的表为基表，显示基表的所有行、列，外表如果条件不匹配则外表中所有字段显示为null
SELECT t.*, c.* FROM course c RIGHT JOIN teacher t ON t.id = c.t_id;（3）内连接和外连接的区别
●对于[内连接]的两个表,如果[驱动表]在[被驱动表]找不到与之匹配的记录,则最终的
记录不会出现在结果集中。
●对于[外连接]中的两个表,即使[驱动表]中的记录在[被驱动表]中找不到与之匹配的记
录,也要将该记录加入到最后的结果集中。针对不同的[驱动表]的位置,有分为[左外连
接]和[右外连接]
a.对于左连接,左边的表为主,左边的表的记录会完整的出现在结果集里。
b.对于右连接,右边的表为主,左边的表的记录会完整的出现在结果集里。
外连接的关键字[outter join] , 也可以省略outter ,连接条件同样使用[ on]关键字（4）全连接
MySQL不支持全连接，Oracle支持全连接。
SELECT * FROM teacher t FULL JOIN course c ON c.t_id = t.id;我们可以通过一些手段来实现全连接的效果
SELECT t.*, c.* FROM teacher t LEFT JOIN course c ON t.id = c.t_id UNION SELECT t.*, c.* FROM teacher t RIGHT JOIN course c ON t.id = c.t_idSQL92语法和SQL99语法的优缺点参考连接：https://zhuanlan.zhihu.com/p/62901648
4、子查询
（1）分类
按照结果集的行列数不同，子查询可以分为以下几类：
标量子查询：结果集只有一行一列（单行子查询）
列子查询：结果集有一列多行
行子查询：结果集有一行多列
表子查询：结果集多行多列
（2）标子量查询
-- 查询比李小虎年龄大的所有学生
SELECT * FROM student WHERE age > (SELECT age FROM student WHERE name = '李小虎');
（3）列子查询
-- 查询有一门学科分数大于90分的学生信息
SELECT * FROM student WHERE id IN( SELECT s_id FROM scores WHERE score>90 )
（4）行子查询
-- 查询男生且年龄最大的学生
SELECT * FROM student WHERE age = ( SELECT MAX(age) FROM student GROUP BY gender HAVING gender = '男' )-- 优化（这种方式不常用）
SELECT * FROM student WHERE (age,gender)=( SELECT MAX(age), gender FROM student GROUP BY gender HAVING gender = '男' )
（5）表子查询
-- 取排名数学成绩前五的学生，正序排列
SELECT * FROM ( SELECT s.*, sc.score 成绩, c.NAME 科目 FROM student s LEFT JOIN scores sc ON s.id = sc.s_id LEFT JOIN course c ON c.id = sc.c_id WHERE c.NAME = '数学' ORDER BY score DESC LIMIT 5 ) t
-- 取排名数学成绩前五的学生中是男生的学生信息，正序排列
SELECT * FROM ( SELECT s.*, sc.score 成绩, c.NAME 科目 FROM student s LEFT JOIN scores sc ON s.id = sc.s_id LEFT JOIN course c ON c.id = sc.c_id WHERE c.NAME = '数学' ORDER BY score DESC LIMIT 5 ) t WHERE t.gender = '男';
（6）总结
where型子查询，如果是where 列 = （内层sql），则内层的sql返回的必须是单行单列，单个值。
where型子查询，如果是where (列1,列2) = （内层sql），内层的sql返回的必须是单列，可以是多行。
写子查询的方法：分析需求------拆步骤------分步写sql-------整合拼装sql。
如果一个需求可以不用子查询，尽量不使用，因为子查询的SQL可读性太低。
