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

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

构建数据库

创建一张student表：

DROP TABLE IF EXISTS student;
CREATE TABLE student (
    id INT(10) PRIMARY KEY,
    name VARCHAR(10),
    age INT(10) NOT NULL,
    gender VARCHAR(2)
);

构建一张course表：

DROP TABLE IF EXISTS course;
CREATE TABLE course(
    id INT(10) PRIMARY KEY,
    name VARCHAR(10),
    t_id INT(10)
);

构建一张teacher表：

DROP TABLE IF EXISTS teacher;
CREATE TABLE teacher(
    id INT(10) PRIMARY KEY,
    name VARCHAR(10)
);

构建一个score表：

DROP TABLE IF EXISTS score;
CREATE TABLE scores(
    s_id INT(10),
    score INT(10),
    c_id INT(10),
    PRIMARY KEY(s_id,c_id)
);

表格填充数据：

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);

单表查询

查询所有列：

select * from 表名;
select * from student;

查询指定的列：

select id,name,age,gender from student;
select id,name,age from student;

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

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

select DISTINCT name from book;

select id,name,age/10 from student;

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

select id,name,sal+1000 from employee;

注意：

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

我们可以为该列指定别名，因为我们可能会在查询过程中重复该列的名称，可能该名称不够简洁，或者该列名称不符合我们的要求。

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

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

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

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

select * from student where name like '张%';
select * from student where name like '张_';
select * from student where name like '%明%';
select * from student where name like '_明_';

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

• 升序

select * from student ORDER BY age ASC;
-- ASC是可以省略

• 降序

select * from student ORDER BY age DESC;

• 使用多列作为排序条件：当第一个排序条件相同时，根据第二列排序条件进行排序(如果第二列仍然相同，。)
  
  [En]

  use multiple columns as the sort condition: when the first sort condition is the same, sort according to the second column sort condition (if the second column is still the same,.)*

select * from student ORDER BY age asc,id desc;

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

查询满足条件的记录行数，后边可以跟where条件。

如果满足条件的列值为空，则不进行统计。

如果我们想要计算真实和有效的记录的数量，最好不要使用空列。

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

select count(列名) from 表名;

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

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

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

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

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

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

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

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

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

顾名思义：分组查询就是对原始数据进行分组。

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

[En]

The students in the class are divided into groups according to gender, and the average ages of boys and girls are counted.

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;

我们可以这样理解：一旦分组发生，我们查询的结果只能是所有男孩的平均年龄和最大值，而不是特定男孩的数据。

分组查询前，可以通过关键字【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;

分组查询后，也可以通过关键字【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;

面试题：where和having的区别？

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

limit字句，用来限定查询结果的起始行，以及总行数。

limit是 mysql 独有的语法。

select * from student limit 4,3;
select * from student limit 4;

• 如果只有一个参数，则表示从起始位置查找4条记录。
  
  [En]

  if there is only one parameter, it means to find 4 records from the starting position.*

• 如果两个参数，说明从第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版本

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语句的最后。

多表查询

select * from student,teacher;

如果这两个表没有任何关联，我们将不会连接这两个表。

在一个 select * from 表名1，表名2;，就会出现笛卡尔乘积，会生成一张虚拟表，这张虚拟表的数据就是表1和表2两张表数据的乘积。

注意：开发中，一定要避免出现笛卡尔积。

多表连接的方式有四种：

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

1992年的语法。

-- 查询学号，姓名，年龄，分数，通过多表连接查询，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;

-- 查询学号，姓名，年龄，分数，科目名称，通过多表查询，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;

-- 查询学号，姓名，年龄，分数，科目名称，老师名称，通过多表查询，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;

-- 查询老师的信息以及对应教的课程
SELECT
    t.id 教师号,
    t.NAME 教师姓名,
    c.NAME 科目名
FROM
    teacher t,
    course c
WHERE
    t.id = c.t_id;

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

-- 查询学号，姓名，年龄，分数，科目名称，通过多表查询，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;

1999年的语法。

在我们刚才的sql当中，使用逗号分隔两张表进行查询，mysql进行优化默认就等效于内连接。

使用【join】关键字，使用【on】来确定连接条件。【where】只做筛选条件。

SELECT
    t.*,
    c.* ,
    sc.*
FROM
    teacher t
    INNER JOIN course c ON c.t_id = t.id
    INNER JOIN scores sc ON sc.c_id = c.id;

内连接和外连接的区别：

• 对于[NER JOIN]的两个表，如果[Driven TABLE]在[Driven TABLE]中找不到匹配的记录，则最终记录将不会出现在结果集中。
  
  [En]

  for the two tables of [inner join], if the [driven table] cannot find a matching record in the [driven table], the final record will not appear in the result set.*

• 对于外部联接中的两个表，即使驱动表中的记录在驱动表中找不到匹配的记录，也应该将该记录添加到最终结果集中。根据[驱动器表]的位置不同，可分为[左外接]和[右外接]。
  
  [En]

  for the two tables in the external join, even if the record in the driven table cannot find a matching record in the driven table, the record should be added to the final result set. According to the location of different [driver table], it can be divided into [left outer connection] and [right outer connection].*

• 对于LEFT JOIN，左侧的表占主导地位，左侧的表的记录完全出现在结果集中。
  
  [En]

  for left joins, the table on the left is dominant, and the record of the table on the left appears completely in the result set.*

• 对于Right Join，右边的表占主导地位，左边的表的记录完全出现在结果集中。
  
  [En]

  for right join, the table on the right is dominant, and the record of the table on the left appears completely in the result set.*

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

SELECT
    t.*,
    c.*
FROM
    teacher t
    LEFT JOIN course c ON t.id = c.t_id;

SELECT
    t.*,
    c.*
FROM
    course c
    RIGHT JOIN teacher t ON t.id = c.t_id;

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_id

-- 1.查询'01'号学生的姓名和各科成绩 **
SELECT
    s.id sid,
    s.name sname,
    c.name cname,
    sc.score
FROM
    student s
    LEFT JOIN scores sc ON s.id = sc.s_id
    LEFT JOIN course c ON c.id = sc.c_id
WHERE
    s.id = 1;
-- 2.查询各个学科的平均成绩和最高成绩**
SELECT
    c.id,
    c.name,
    AVG( sc.score ),
    max( sc.score )
FROM
    course c
    LEFT JOIN scores sc ON c.id = sc.c_id
GROUP BY
    c.id,
    c.name;
-- 3.查询每个同学的最高成绩和科目名称****
SELECT
    t.id,
    t.NAME,
    c.id,
    c.NAME,
    r.score
FROM
    (
    SELECT
        s.id,
        s.NAME,(
        SELECT
            max( score )
        FROM
            scores r
        WHERE
            r.s_id = s.id
        ) score
    FROM
        student s
    ) t
    LEFT JOIN scores r ON r.s_id = t.id
    AND r.score = t.score
    LEFT JOIN course c ON r.c_id = c.id;
-- 4.查询所有姓张的同学的各科成绩**
SELECT
    s.id,
    s.name,
    c.name cname,
    sc.score
FROM
SELECT
    s.id,
    s.name,
    c.name cname,
    sc.score
FROM
    student s
    LEFT JOIN scores sc ON sc.s_id = s.id
    LEFT JOIN course c ON c.id = sc.c_id
WHERE
    s.name LIKE '张%';
-- 5.查询每个课程的最高分的学生信息*****
SELECT
    *
FROM
    student s
WHERE
    id IN (
    SELECT DISTINCT
        r.s_id
    FROM
        (
        SELECT
            c.id,
            c.NAME,
            max( score ) score
        FROM
            student s
            LEFT JOIN scores r ON r.s_id = s.id
            LEFT JOIN course c ON c.id = r.c_id
        GROUP BY
            c.id,
            c.NAME
        ) t
        LEFT JOIN scores r ON r.c_id = t.id
    AND t.score = r.score
    )

-- 6.查询名字中含有'张'或'李'字的学生的信息和各科成绩。
SELECT
    s.id,
    s.NAME sname,
    sc.score,
    c.NAME
FROM
    student s
    LEFT JOIN scores sc ON s.id = sc.s_id
    LEFT JOIN course c ON sc.c_id = c.id
WHERE
    s.NAME LIKE '%张%'
    OR s.NAME LIKE '%李%';
-- 7.查询平均成绩及格的同学的信息。（子查询）
SELECT
    *
FROM
    student
WHERE
    id IN (
    SELECT
        sc.s_id
    FROM
        scores sc
    GROUP BY
        sc.s_id
    HAVING
    avg( sc.score ) >= 70
    )
-- 8.将学生按照总分数进行排名。（从高到低）
SELECT
    s.id,
    s.NAME,
    sum( sc.score ) score
FROM
    student s
    LEFT JOIN scores sc ON s.id = sc.s_id
GROUP BY
    s.id,
    s.NAME
ORDER BY
    score DESC,
    s.id ASC;
-- 9.查询数学成绩的最高分、最低分、平均分。
SELECT
    c.NAME,
    max( sc.score ),
    min( sc.score ),
    avg( sc.score )
FROM
    course c
    LEFT JOIN scores sc ON c.id = sc.c_id
WHERE
    c.NAME = '数学';
-- 10.将各科目按照平均分排序。
SELECT
    c.id,
    c.NAME,
    avg( sc.score ) score
FROM
    course c
    LEFT JOIN scores sc ON c.id = sc.c_id
GROUP BY
    c.id,
    c.NAME
ORDER BY
    score DESC;

Original: https://www.cnblogs.com/YQuicksilver/p/16610933.html
Author: QuickSilver
Title: 2022-08-16 数据库查询语言之——-DQL