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

2023年6月14日上午8:48 • 数据库 • 阅读 90

重点，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;

注意：

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

使用多列作为排序条件：当第一个排序条件相同时，根据第二列排序条件进行排序（第二列如果还相同，…..）

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;

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

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

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的区别？

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

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

limit是 mysql 独有的语法。

select * from student limit 4,3;
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版本

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;

内连接和外连接的区别：

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

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

外连接的关键字【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

原创文章受到原创版权保护。转载请注明出处：https://www.johngo689.com/611095/

转载文章受原作者版权保护。转载请注明原作者出处！

数据库

【自取】最近整理的，有需要可以领取学习：

Linux核心资料大放送~

全栈面试题汇总（持续更新&可下载）

一个提高学习100%效率的工具！

【超详细】深度学习面试题目！

LeetCode Python刷题答案下载！

LeetCode Java版刷题答案下载！

LeetCode C++ 版本，抓紧保存！

LeetCode GO语言刷题答案下载！

Dockerfile 构建镜像

从 Dockerfile 构建镜像涉及三个步骤创建工作目录编写 Dockerfile 规格使用 docker build 命令构建镜像 1. 创建工作目录这个根据应用实际情…

数据库 2023年6月14日
0066
必应咋想的

首页里弄了个阴森的图片，下面有个山洞，里面有白衣女鬼飘过，还有背景音乐。看右下角的介绍里有名字叫：万圣节之夜在黑暗树篱 Original: https://www.cnblogs…

数据库 2023年6月9日
0098
如何实现跨域？

https://blog.csdn.net/meism5/article/details/90414283 Original: https://www.cnblogs.com/cr…

数据库 2023年6月16日
0087
vue项目发布到docker nginx的方法总结

我总结了以下两种方法可用一、通过制作镜像，使用镜像生成docker 这个是比较常用的方法，制作镜像(images)后，如果还要生成docker可以直接使用镜像比较方便以下是操作步…

数据库 2023年6月9日
0090
Linux 系统安装RocketMQ

准备工作 1.去官网下载一个安装包 1.解压 unzip rocketmq-all-4.9.0-bin-release.zip -d /download/compress/ 2.进…

数据库 2023年6月6日
0088
机器学习模型太慢？来看看英特尔(R) 扩展加速 ⛵

💡 作者：韩信子@ShowMeAI📘 机器学习实战系列：https://www.showmeai.tech/tutorials/41📘 本文地址：https://www.showm…

数据库 2023年6月14日
0084
简单！代码原来是这样被CPU跑起来的

CPU对我们来说既熟悉又陌生，熟悉的是我们知道代码是被CPU执行的，当我们的线上服务出现问题时可能首先会查看CPU负载情况。陌生的是我们并不知道CPU是如何执行代码的，它对我们的代…

数据库 2023年6月6日
0088
maven配置本地仓库

https://www.cnblogs.com/javajetty/p/10051156.html maven配置本地仓库 Original: https://www.cnblog…

数据库 2023年6月9日
0081
Python–Queue

线程队列：先入先出队列、后进先出队列、优先级队列先入先出：后进先出：优先级队列：设置队列空间大小生产者消费者模型示例： Original: https://www.cn…

数据库 2023年6月9日
0091
Intellij IDEA个人常用快捷键

分享一下个人常用快捷键。说明：字母排序规则遵循字母表(a->z) 快捷键介绍 ctrl+b 快速打开当前光标处的类或方法 ctrl+d 复制当前光标所在行至下一行 ctr…

数据库 2023年6月14日
0084
MySQL数据库-数据表（下）

SELECT定义： SQL的SELECT语句可以实现对表的选择、投影及连接操作。即SELECT语句可以从一个或多个表中根据用户的需要从数据库中选出匹配的行和列，结果通常是生成一个临…

数据库 2023年6月11日
0080
Linux指令_入门基础

2.pwd指令：用法：#pwd （print working directory ，打印当前工作目录） 3.cd指令：命令：# cd （change directory，改…

数据库 2023年6月11日
00103
在使用amoeba连接数据库时，报错java.lang.Exception: poolName=slaves, no valid pools

搭建3台MySQL服务器，完成主从复制，搭建一台amoeba服务器，完成MySQL的读写分离问题描述：问题1、在服务搭建完毕后，利用客户机连接amoeba服务器登录数据库，无…

数据库 2023年6月14日
00159
Linux_连接工具_SecureCRT的使用教程

什么是SecureCRT? SecureCRT是一款支持 SSH2、SSH1、Telnet、Telnet/SSH、Relogin、Serial、TAPI、RAW 等协议的终端仿真程…

数据库 2023年6月11日
00119
Question04-查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩

* SELECT stu.SID, stu.Sname, IFNULL(CAST(AVG(sc.score) AS DECIMAL(18,2)), 0) 平均成绩 FROM Stu…

数据库 2023年6月16日
0086
eclipse反编译插件

1、在eclipse的help—》Install New Software…中添加新软件开发，添加它的源： undefined name ： jd – ec…

数据库 2023年6月11日
0082

2024 年 5 月
一	二	三	四	五	六	日
		1	2	3	4	5
6	7	8	9	10	11	12
13	14	15	16	17	18	19
20	21	22	23	24	25	26
27	28	29	30	31

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

构建数据库

单表查询

多表查询

大家都在看