2022-08-17 第八组常竞文 DQL-CSDN博客

本文链接：https://blog.csdn.net/cjwtsl/article/details/126358424

构建数据库

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

构建数据库

DROP TABLE IF EXISTS student;

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

单表查询

基本查询

基本语法

查询指定的列；

select id ,`name`,age from student;

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

select distinct `name` from book;

列运算

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

null值和任何值运算都为null，需要用到函数ifnull（）;如果工资为空，则为零

select IFNULL(sal,0) + 1000 from employee;

将字符串做加减乘除运算，会把字符串当0处理

别名

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

条件控制

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

排序

升序

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

降序

select* from studentORDER BY age DESC;

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

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

举例：

创建一张用户表，id，username，password。几乎所有的表都会有两个字段，creat_time,

update_time. 几乎所有的查询都会按照updata_time降序排列。

聚合函数

count

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

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

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

max

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

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

min

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

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

sum

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

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

avg

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

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

分组查询

将原有数据进行分组统计

举例：

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

--语法
select 分组列名，聚合函数1，聚合函数2···· from 表名 GROUP BY 该分组列名;
--根据性别分组，查看每一组的平均年龄和最大年龄
select gender,ave(age),max(age) from student group by gender;
--一旦发生了分组，我们查询的结果只能是所有男生的年龄平均值、最大值，而不是某一个男生的数据

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

分组查询前，可以通过关键字【where】先把满足条件的的人分出来，在分组

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

分组查询后，也可以通过关键字【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条记录。
如果两个参数，说明从第4行下一行，向后查找3条记录

面试题：

MySQL：limit

Oracle：rownum

SqlServer ：top

判断在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;

如果两个表没有任何关联关系，我们也不会连接这两张表

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

多表连接的方式有四种：

内连接

外连接***

全连接

子查询

SQL92语法（了解）

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

SQL99语法

内连接

在我们刚才的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
	student 
WHERE
	age > ( SELECT age FROM student WHERE NAME = '小虎' );

列子查询

结果集有一列多行

SELECT
	* 
FROM
	student 
WHERE
	id IN (
	SELECT
		s_id 
	FROM
		scores 
WHERE
	score > 90);

行子查询

结果集有一行多列

SELECT
	* 
FROM
	student 
WHERE
	id IN (
	SELECT
		s_id 
	FROM
		scores 
WHERE
	score > 90);
-- 优化
SELECT
	* 
FROM
	student 
WHERE
	( age, gender ) = (
	SELECT
		max( age ),
		gender 
	FROM
		student 
	GROUP BY
		gender 
	HAVING
	gender = '男' 
	)

总结：