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 id,`name`,age from 表名;

开发中严禁使用select * 。

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

select distinct id from 表名;

列运算：

select id,`name`,age/10 from 表名;

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

select id,`name`,age+1000 from 表名;

注意：

1、null与任何值做运算结果都为null，需要用到函数IFNULL()，如果薪资是空，则为0.

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

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

别名：

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

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

max：

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

select max(列名) from 表名 where 条件;

min：

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

select min(列名) from 表名 where 条件;

sum：

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

select sum(列名) from 表名 where 条件;

avg：

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

select avg(列名) from 表名 where 条件;

分组查询：

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

举例：

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

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字句，用来限定查询结果的起始行以及总行数。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页，(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;

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

出现笛卡尔乘积，会生成一张虚拟表，这张虚拟表的数据就是表一和表二数据的乘积。

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

多表连接的方式有四种：

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

SQL92语法：

1992年的语法：

select stu.id,stu.name,stu.age,sc.score from student stu,scores sc where stu.id = sc.s_id;

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;

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('小红','小军');

SELECT
	sc.c_id,
	MAX( score ),
	MIN( score ),
	c.NAME 
FROM
	student stu,
	scores sc,
	course c 
WHERE
	sc.c_id = c.id 
GROUP BY
	sc.c_id;

SQL92语法，多表查询，如果数据为空，会自动过滤掉。

SQL99语法：

1999年的语法：

内连接：

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

select
t.*,c.*,sc.*
from
teacher t,
join course c on c.t_id = t.id
join scores sc on sc.c_id = c.id;

外连接（常用）：

内连接与外连接的区别：

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

左连接：

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

心得体会

今天继续学习了数据库的操作语言，受益匪浅，通过今天的学习，我觉得自己学到了很多知识，老师给的题也很快做完了。

posted @ 2022-08-16 22:04 jinjidecainiao 阅读(26) 评论(0) 收藏举报

刷新页面返回顶部

cd01106118

2022-08-16 第三组陈迪学习笔记

DQL(数据库查询语言)：

构建数据库：

单表查询：

基本查询：

基本语法：

列运算：

别名：

条件控制：

排序：

聚合函数：

count：

max：

min：

sum：

avg：

分组查询：

分页查询：

多表查询：

多表连接的方式有四种：

SQL92语法：

SQL99语法：

内连接：

外连接（常用）：

全连接：

需求：

心得体会

公告

cd01106118

2022-08-16 第三组 陈迪 学习笔记

DQL(数据库查询语言)：

构建数据库：

单表查询：

基本查询：

基本语法：

列运算：

别名：

条件控制：

排序：

聚合函数：

count：

max：

min：

sum：

avg：

分组查询：

分页查询：

多表查询：

多表连接的方式有四种：

SQL92语法：

SQL99语法：

内连接：

外连接（常用）：

全连接：

需求：

心得体会

公告

2022-08-16 第三组陈迪学习笔记