百木园-与人分享,
就是让自己快乐。

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

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;
    
  • 使用多列作为排序条件:当第一个排序条件相同时,根据第二列排序条件进行排序(第二列如果还相同,.....)

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

举例:

创建一张用户表,id,username,password。

几乎所有的表都会有两个字段,create_time,update_time。

几乎所有的查询都会按照update_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 该分组列名;

分组要使用关键词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条记录。

  • 如果两个参数,说明从第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两张表数据的乘积。

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

多表连接的方式有四种:

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

SQL92语法

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;

SQL99语法

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

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

需求2

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




来源:https://www.cnblogs.com/YQuicksilver/p/16610933.html
本站部分图文来源于网络,如有侵权请联系删除。

未经允许不得转载:百木园 » 2022-08-16 数据库查询语言之-------DQL

相关推荐

  • 暂无文章