2022-08-16 数据库查询语言之-------DQL

DQL数据库查询语言

重点，DQL是我们每天都要接触编写最多也是最难的SQL，该语言用来查询记录，不会修改数据库和表结构。
构建数据库

创建一张student表：

1. DROP TABLE IF EXISTS student;
2. CREATE TABLE student (
3.         id INT(10) PRIMARY KEY,
4.         `name` VARCHAR(10),
5.         age INT(10) NOT NULL,
6.         gender VARCHAR(2)
7. );

复制代码

构建一张course表：

1. DROP TABLE IF EXISTS course;
2. CREATE TABLE course(
3.         id INT(10) PRIMARY KEY,
4.         `name` VARCHAR(10),
5.         t_id INT(10)
6. );

复制代码

构建一张teacher表：

1. DROP TABLE IF EXISTS teacher;
2. CREATE TABLE teacher(
3.         id INT(10) PRIMARY KEY,
4.         `name` VARCHAR(10)
5. );

复制代码

构建一个score表：

1. DROP TABLE IF EXISTS score;
2. CREATE TABLE scores(
3.         s_id INT(10),
4.         score INT(10),
5.         c_id INT(10),
6.         PRIMARY KEY(s_id,c_id)
7. );

复制代码

表格填充数据：

1. 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,'男');
3. insert into  course (id,name,t_id)VALUES(1,'数学',1),(2,'语文',2),(3,'c++',3),(4,'java',4),(5,'php',null);
6. insert into  teacher (id,name)VALUES(1,'Tom'),(2,'Jerry'),(3,'Tony'),(4,'Jack'),(5,'Rose');
9. insert into  scores (s_id,score,c_id)VALUES(1,80,1);
10. insert into  scores (s_id,score,c_id)VALUES(1,56,2);
11. insert into  scores (s_id,score,c_id)VALUES(1,95,3);
12. insert into  scores (s_id,score,c_id)VALUES(1,30,4);
13. insert into  scores (s_id,score,c_id)VALUES(1,76,5);
15. insert into  scores (s_id,score,c_id)VALUES(2,35,1);
16. insert into  scores (s_id,score,c_id)VALUES(2,86,2);
17. insert into  scores (s_id,score,c_id)VALUES(2,45,3);
18. insert into  scores (s_id,score,c_id)VALUES(2,94,4);
19. insert into  scores (s_id,score,c_id)VALUES(2,79,5);
21. insert into  scores (s_id,score,c_id)VALUES(3,65,2);
22. insert into  scores (s_id,score,c_id)VALUES(3,85,3);
23. insert into  scores (s_id,score,c_id)VALUES(3,37,4);
24. insert into  scores (s_id,score,c_id)VALUES(3,79,5);
26. insert into  scores (s_id,score,c_id)VALUES(4,66,1);
27. insert into  scores (s_id,score,c_id)VALUES(4,39,2);
28. insert into  scores (s_id,score,c_id)VALUES(4,85,3);
30. insert into  scores (s_id,score,c_id)VALUES(5,66,2);
31. insert into  scores (s_id,score,c_id)VALUES(5,89,3);
32. insert into  scores (s_id,score,c_id)VALUES(5,74,4);
35. insert into  scores (s_id,score,c_id)VALUES(6,80,1);
36. insert into  scores (s_id,score,c_id)VALUES(6,56,2);
37. insert into  scores (s_id,score,c_id)VALUES(6,95,3);
38. insert into  scores (s_id,score,c_id)VALUES(6,30,4);
39. insert into  scores (s_id,score,c_id)VALUES(6,76,5);
41. insert into  scores (s_id,score,c_id)VALUES(7,35,1);
42. insert into  scores (s_id,score,c_id)VALUES(7,86,2);
43. insert into  scores (s_id,score,c_id)VALUES(7,45,3);
44. insert into  scores (s_id,score,c_id)VALUES(7,94,4);
45. insert into  scores (s_id,score,c_id)VALUES(7,79,5);
47. insert into  scores (s_id,score,c_id)VALUES(8,65,2);
48. insert into  scores (s_id,score,c_id)VALUES(8,85,3);
49. insert into  scores (s_id,score,c_id)VALUES(8,37,4);
50. insert into  scores (s_id,score,c_id)VALUES(8,79,5);
52. insert into  scores (s_id,score,c_id)VALUES(9,66,1);
53. insert into  scores (s_id,score,c_id)VALUES(9,39,2);
54. insert into  scores (s_id,score,c_id)VALUES(9,85,3);
55. insert into  scores (s_id,score,c_id)VALUES(9,79,5);
57. insert into  scores (s_id,score,c_id)VALUES(10,66,2);
58. insert into  scores (s_id,score,c_id)VALUES(10,89,3);
59. insert into  scores (s_id,score,c_id)VALUES(10,74,4);
60. insert into  scores (s_id,score,c_id)VALUES(10,79,5);

复制代码

单表查询

基本查询

基本语法

查询所有列：

1. select * from 表名;
2. select * from student;

复制代码

查询指定的列：

1. select id,`name`,age,gender from student;
2. select id,`name`,age from student;

复制代码

补充：开发中，严禁使用select *。

如果表中有完全重复的记录只显示一次，在查询的列之前加上distinct。

1. select DISTINCT `name` from book;

复制代码

列运算

1. select id,`name`,age/10 from student;

复制代码

注意：我们写的所有的查询语句，最终执行的结果，都是生成一张虚拟表。

1. select id,`name`,sal+1000 from employee;

复制代码

注意：

• null值和任何值做计算都为null，需要用到函数ifnull()函数。select IFNULL(sal,0) + 1000 from employee;如果薪资是空，则为0。
  
• 将字符串做加减乘除运算，会把字符串当0处理。
  

别名

我们可以给列起【别名】，因为我们在查询过程中，列名很可能重复，可能名字不够简洁，或者列的名字不能满足我们的要求。

1. select id `编号`,`name` `姓名`,age `年龄`,gender `性别` from student;
2. select id as `编号`,`name` as `姓名`,age as `年龄`,gender as `性别` from student;

复制代码

条件控制

条件查询：在后面添加where指定条件

1. select * from student where id = 3;
2. select * from student where id in (1,3,5);
3. select * from student where id > 2;
4. select * from student where id BETWEEN 3 and 5;
5. select * from student where id BETWEEN 6 and 7 or age > 20;

复制代码

模糊查询：我想查询所有姓张的。

1. select * from student where `name` like '张%';
2. select * from student where `name` like '张_';
3. select * from student where `name` like '%明%';
4. select * from student where `name` like '_明_';

复制代码

通配符：_下划线代表一个字符，%百分号代表任意个字符。

排序

• 升序
  1. select * from student ORDER BY age ASC;
  2. -- ASC是可以省略

  复制代码
• 降序
  1. select * from student ORDER BY age DESC;

  复制代码
• 使用多列作为排序条件：当第一个排序条件相同时，根据第二列排序条件进行排序（第二列如果还相同，.....）
  1. select * from student ORDER BY age asc,id desc;

  复制代码

举例：
创建一张用户表，id，username，password。
几乎所有的表都会有两个字段，create_time，update_time。
几乎所有的查询都会按照update_time降序排列。

聚合函数

count

查询满足条件的记录行数，后边可以跟where条件。
如果满足条件的列值为空，不会进行统计。
如果我们要统计真实有效的记录数，最好不要用可以为空列。

• count(*)
  
• count(主键)（推荐）
  
• count(1)（不推荐）
  

1. select count(列名) from 表名;

复制代码

1. select count(id) from student where gender='男';

复制代码

max

查询满足条件的记录中的最大值，后面可以跟where条件。

1. select max(age) from student where gender='女';

复制代码

min

查询满足条件的记录中的最小值，后面可以跟where条件。

1. select MIN(age) from student where gender='男';

复制代码

sum

查询满足条件的记录的和，后面可以跟where条件。

1. select sum(age) from student where gender='男';

复制代码

avg

查询满足条件的记录的平均数，后面可以跟where条件。

1. select avg(score) from scores where c_id = 3;

复制代码

分组查询

顾名思义：分组查询就是将原有数据进行分组统计。

举例：
将班级的同学按照性别分组，统计男生和女生的平均年龄。

1. select 分组列名,聚合函数1,聚合函数2... from 表名 group by 该分组列名;

复制代码

分组要使用关键词group by，后面可以是一列，也可以是多个列，分组后查询的列只能是分组的列，或者是使用了聚合函数的其他的列，剩余列不能单独使用。

1. -- 根据性别分组，查看每一组的平均年龄和最大年龄
2. select gender,avg(age),max(age) from student group by gender;
3. -- 根据专业号分组，查看每一个专业的平均分
4. select c_id,avg(score) from scores group by c_id;

复制代码

我们可以这样理解：一旦发生了分组，我们查询的结果只能是所有男生的年龄平均值、最大值，而不能是某一个男生的数据。
分组查询前，可以通过关键字【where】先把满足条件的人分出来，再分组。

1. select 分组列,聚合函数1... from 表名 where 条件 group by 分组列;

复制代码

1. select c_id,avg(score) from scores where c_id in (1,2,3) group by c_id;

复制代码

分组查询后，也可以通过关键字【having】把组信息中满足条件的组再细分出来。

1. select 分组列,聚合函数1... from 表名 where 条件 group by 分组列 having 聚合函数或列名（条件）;

复制代码

1. select gender,avg(age),sum(age) `sum_age` from student GROUP BY gender HAVING `sum_age` > 50;

复制代码

面试题：where和having的区别？

• where是写在group by之前的筛选，在分组前筛选；having是写在group by之后，分组后再筛选。
  
• where只能使用分组的列作为筛选条件；having既可以使用分组的列，也可以使用聚合函数列作为筛选条件。
  

分页查询

limit字句，用来限定查询结果的起始行，以及总行数。
limit是mysql独有的语法。

1. select * from student limit 4,3;
2. select * from student limit 4;

复制代码

• 如果只有一个参数，说明从起始位置查找4条记录。
  
• 如果两个参数，说明从第4行下一行，向后查找3条记录。
  

面试题：

• MySQL：limit
  
• Oracle：rownum
  
• SqlServer：top
  

分析：
student表中有10条数据，如果每页显示4条，分几页？3页
3页怎么来的？(int)(Math.ceil(10 / 4));
显示第一页的数据：select * from student limit 0,4;
第二页：select * from student limit 4,4;
第三页：select * from student limit 8,4;

一个问题：我想要判断在student表中有没有叫"小红"的这个人？
1.0版本

1. select * from student where name = '小红';
2. select id from student where name = '小红';

复制代码

2.0版本

1. select count(id) from student where name = '小红';

复制代码

3.0版本

1. select id from student where name = '小红' limit 1;

复制代码

注意：Limit子句永远是在整个的sql语句的最后。

多表查询

笛卡尔积

1. select * from student,teacher;

复制代码

如果两个表没有任何关联关系，我们也不会连接这两张表。
在一个select * from 表名1，表名2;，就会出现笛卡尔乘积，会生成一张虚拟表，这张虚拟表的数据就是表1和表2两张表数据的乘积。
注意：开发中，一定要避免出现笛卡尔积。
多表连接的方式有四种：

• 内连接
  
• 外连接**
  
• 全连接
  
• 子查询
  

SQL92语法

1992年的语法。

1. -- 查询学号，姓名，年龄，分数，通过多表连接查询，student和scores通过id和s_id连接
2. SELECT
3.         stu.id 学号,
4.         stu.name 姓名,
5.         stu.age 年龄,
6.         sc.score 分数
7. FROM
8.         student stu,
9.         scores sc
10. WHERE
11.         stu.id = sc.s_id;

复制代码

1. -- 查询学号，姓名，年龄，分数，科目名称，通过多表查询，student和scores，course
2. SELECT
3.         stu.`id` 学号,
4.         stu.`name` 姓名,
5.         stu.`age` 年龄,
6.         sc.`score` 分数,
7.         c.`name` 科目
8. FROM
9.         student stu,
10.         scores sc,
11.         course c
12. WHERE
13.         stu.id = sc.s_id
14. AND
15.         c.id = sc.c_id;

复制代码

1. -- 查询学号，姓名，年龄，分数，科目名称，老师名称，通过多表查询，student和scores，course，teacher
2. SELECT
3.         stu.`id` 学号,
4.         stu.`name` 姓名,
5.         stu.`age` 年龄,
6.         sc.`score` 分数,
7.         c.`name` 科目,
8.         t.`name` 老师
9. FROM
10.         student stu,
11.         scores sc,
12.         course c,
13.         teacher t
14. WHERE
15.         stu.id = sc.s_id
16. AND
17.         c.id = sc.c_id
18. AND
19.         c.t_id = t.id;

复制代码

1. -- 查询老师的信息以及对应教的课程
2. SELECT
3.         t.id 教师号,
4.         t.NAME 教师姓名,
5.         c.NAME 科目名
6. FROM
7.         teacher t,
8.         course c
9. WHERE
10.         t.id = c.t_id;

复制代码

SQL92语法，多表查询，如果有数据为null，会过滤掉。

1. -- 查询学号，姓名，年龄，分数，科目名称，通过多表查询，student和scores，course
2. -- 在查询的基础上，进一步筛选，筛选小红和张小军的成绩
3. SELECT
4.         stu.`id` 学号,
5.         stu.`name` 姓名,
6.         stu.`age` 年龄,
7.         sc.`score` 分数,
8.         c.`name` 科目
9. FROM
10.         student stu,
11.         scores sc,
12.         course c
13. WHERE
14.         stu.id = sc.s_id
15. AND
16.         c.id = sc.c_id
17. AND
18.         stu.`name` in ('小红','张小军');

复制代码

1. -- 查询学号，姓名，年龄，分数，科目名称，通过多表查询，student和scores，course
2. -- 在查询的基础上，进一步筛选，筛选小红和张小军的成绩
3. -- 在小红和张小军成绩的基础上进一步再筛选，筛选他们的java成绩
4. SELECT
5.         stu.`id` 学号,
6.         stu.`name` 姓名,
7.         stu.`age` 年龄,
8.         sc.`score` 分数,
9.         c.`name` 科目
10. FROM
11.         student stu,
12.         scores sc,
13.         course c
14. WHERE
15.         stu.id = sc.s_id
16. AND
17.         c.id = sc.c_id
18. AND
19.         stu.`name` in ('小红','张小军')
20. AND
21.         c.`name` = 'java';

复制代码

1. -- 查询学号，姓名，年龄，分数，科目名称，通过多表查询，student和scores，course
2. -- 找出最低分和最高分，按照科目分组，每一科
3. SELECT
4.         sc.c_id,
5.         max( score ),
6.         min( score ),
7.         c.`name`
8. FROM
9.         scores sc,
10.         course c
11. WHERE
12.         sc.c_id = c.id
13. GROUP BY
14.         sc.c_id;

复制代码

SQL99语法

1999年的语法。
内连接

在我们刚才的sql当中，使用逗号分隔两张表进行查询，mysql进行优化默认就等效于内连接。
使用【join】关键字，使用【on】来确定连接条件。【where】只做筛选条件。

1. SELECT
2.         t.*,
3.         c.* ,
4.         sc.*
5. FROM
6.         teacher t
7.         INNER JOIN course c ON c.t_id = t.id
8.         INNER JOIN scores sc ON sc.c_id = c.id;

复制代码

外连接（常用）

内连接和外连接的区别：

• 对于【内连接】的两个表，如果【驱动表】在【被驱动表】找不到与之匹配的记录，则最终的记录不会出现在结果集中。
  
• 对于【外连接】中的两个表，即使【驱动表】中的记录在【被驱动表】中找不到与之匹配的记录，也要将该记录加入到最后的结果集中。针对不同的【驱动表】的位置，有分为【左外连接】和【右外连接】。
  

• 对于左连接，左边的表为主，左边的表的记录会完整的出现在结果集里。
  
• 对于右连接，右边的表为主，左边的表的记录会完整的出现在结果集里。
  

外连接的关键字【outter join】，也可以省略outter，连接条件同样使用【on】关键字。
左连接

1. SELECT
2.         t.*,
3.         c.*
4. FROM
5.         teacher t
6.         LEFT JOIN course c ON t.id = c.t_id;

复制代码

右连接

1. SELECT
2.         t.*,
3.         c.*
4. FROM
5.         course c
6.         RIGHT JOIN teacher t ON t.id = c.t_id;

复制代码

全连接

mysql不支持全连接。oracle支持全连接。

1. SELECT
2.         *
3. FROM
4.         teacher t
5.         FULL JOIN course c ON c.t_id = t.id;

复制代码

我们可以通过一些手段来实现全连接的效果

1. SELECT
2.         t.*,
3.         c.*
4. FROM
5.         teacher t
6.         LEFT JOIN course c ON t.id = c.t_id
7. UNION
8. SELECT
9.         t.*,
10.         c.*
11. FROM
12.         teacher t
13.         RIGHT JOIN course c ON t.id = c.t_id

复制代码

需求1

1. -- 1.查询'01'号学生的姓名和各科成绩 **
2. SELECT
3.         s.id sid,
4.         s.`name` sname,
5.         c.`name` cname,
6.         sc.score
7. FROM
8.         student s
9.         LEFT JOIN scores sc ON s.id = sc.s_id
10.         LEFT JOIN course c ON c.id = sc.c_id
11. WHERE
12.         s.id = 1;
13. -- 2.查询各个学科的平均成绩和最高成绩**
14. SELECT
15.         c.id,
16.         c.`name`,
17.         AVG( sc.score ),
18.         max( sc.score )
19. FROM
20.         course c
21.         LEFT JOIN scores sc ON c.id = sc.c_id
22. GROUP BY
23.         c.id,
24.         c.`name`;
25. -- 3.查询每个同学的最高成绩和科目名称****
26. SELECT
27.         t.id,
28.         t.NAME,
29.         c.id,
30.         c.NAME,
31.         r.score
32. FROM
33.         (
34.         SELECT
35.                 s.id,
36.                 s.NAME,(
37.                 SELECT
38.                         max( score )
39.                 FROM
40.                         scores r
41.                 WHERE
42.                         r.s_id = s.id
43.                 ) score
44.         FROM
45.                 student s
46.         ) t
47.         LEFT JOIN scores r ON r.s_id = t.id
48.         AND r.score = t.score
49.         LEFT JOIN course c ON r.c_id = c.id;
50. -- 4.查询所有姓张的同学的各科成绩**
51. SELECT
52.         s.id,
53.         s.`name`,
54.         c.`name` cname,
55.         sc.score
56. FROM
57. SELECT
58.         s.id,
59.         s.`name`,
60.         c.`name` cname,
61.         sc.score
62. FROM
63.         student s
64.         LEFT JOIN scores sc ON sc.s_id = s.id
65.         LEFT JOIN course c ON c.id = sc.c_id
66. WHERE
67.         s.`name` LIKE '张%';
68. -- 5.查询每个课程的最高分的学生信息*****
69. SELECT
70.         *
71. FROM
72.         student s
73. WHERE
74.         id IN (
75.         SELECT DISTINCT
76.                 r.s_id
77.         FROM
78.                 (
79.                 SELECT
80.                         c.id,
81.                         c.NAME,
82.                         max( score ) score
83.                 FROM
84.                         student s
85.                         LEFT JOIN scores r ON r.s_id = s.id
86.                         LEFT JOIN course c ON c.id = r.c_id
87.                 GROUP BY
88.                         c.id,
89.                         c.NAME
90.                 ) t
91.                 LEFT JOIN scores r ON r.c_id = t.id
92.         AND t.score = r.score
93.         )

复制代码

需求2

1. -- 6.查询名字中含有'张'或'李'字的学生的信息和各科成绩。
2. SELECT
3.         s.id,
4.         s.NAME sname,
5.         sc.score,
6.         c.NAME
7. FROM
8.         student s
9.         LEFT JOIN scores sc ON s.id = sc.s_id
10.         LEFT JOIN course c ON sc.c_id = c.id
11. WHERE
12.         s.NAME LIKE '%张%'
13.         OR s.NAME LIKE '%李%';
14. -- 7.查询平均成绩及格的同学的信息。（子查询）
15. SELECT
16.         *
17. FROM
18.         student
19. WHERE
20.         id IN (
21.         SELECT
22.                 sc.s_id
23.         FROM
24.                 scores sc
25.         GROUP BY
26.                 sc.s_id
27.         HAVING
28.         avg( sc.score ) >= 70
29.         )
30. -- 8.将学生按照总分数进行排名。（从高到低）
31. SELECT
32.         s.id,
33.         s.NAME,
34.         sum( sc.score ) score
35. FROM
36.         student s
37.         LEFT JOIN scores sc ON s.id = sc.s_id
38. GROUP BY
39.         s.id,
40.         s.NAME
41. ORDER BY
42.         score DESC,
43.         s.id ASC;
44. -- 9.查询数学成绩的最高分、最低分、平均分。
45. SELECT
46.         c.NAME,
47.         max( sc.score ),
48.         min( sc.score ),
49.         avg( sc.score )
50. FROM
51.         course c
52.         LEFT JOIN scores sc ON c.id = sc.c_id
53. WHERE
54.         c.NAME = '数学';
55. -- 10.将各科目按照平均分排序。
56. SELECT
57.         c.id,
58.         c.NAME,
59.         avg( sc.score ) score
60. FROM
61.         course c
62.         LEFT JOIN scores sc ON c.id = sc.c_id
63. GROUP BY
64.         c.id,
65.         c.NAME
66. ORDER BY
67.         score DESC;

复制代码

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！