2022-08-16 第五组张明敏学习笔记

最新推荐文章于 2024-07-12 14:17:28 发布

小张同学的小张

最新推荐文章于 2024-07-12 14:17:28 发布

阅读量200

点赞数

分类专栏：数据库文章标签：学习数据库 sql

本文链接：https://blog.csdn.net/qq_50392643/article/details/126401518

版权

数据库专栏收录该内容

6 篇文章 0 订阅

订阅专栏

一、DQL数据库查询语言

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

1. 构建数据库

创建一张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);

2. 单表查询

基本查询

（1）基本语法

查询所有列：

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;

（2）列运算

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处理。

（3）别名

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

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

（4）条件控制

条件查询：在后面添加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的区别？

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

3. 查询练习

-- 1.查询'01'号学生的姓名和各科成绩 **
SELECT
stu.id ,stu.`name` ,sc.score ,c.`name` 
FROM
	students stu
	LEFT JOIN scores sc ON stu.id = sc.s_id
	LEFT JOIN courses c ON c.id = sc.c_id;
	WHILE
	stu.id = 1;

	
-- 2.查询各个学科的平均成绩和最高成绩**	
SELECT
c.id,c.`name`,AVG(sc.score),MAX(sc.score)
FROM
courses 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 students s 
	) t
	LEFT JOIN scores r ON r.s_id = t.id 
	AND r.score = t.score
	LEFT JOIN courses c ON r.c_id = c.id;



-- 4.查询所有姓张的同学的各科成绩**
SELECT
stu.id,stu.`name`,c.`name`,sc.score
FROM
students stu
Left JOIN scores sc ON sc.s_id = stu.id
Left JOIN courses c ON c.id = sc.c_id
WHERE
stu.`name` LIKE '张%';


-- 5.查询每个课程的最高分的学生信息*****
SELECT s.id,s.`name`,s.age,s.gender,scores.score,scores.c_id
FROM students s
LEFT JOIN scores ON s.id = scores.s_id
AND scores.score IN
(SELECT MAX( t1.score ) 
			FROM scores t1
			LEFT JOIN courses co ON co.id = t1.c_id 
			GROUP BY co.id,co.`name`
			) 
ORDER BY scores.c_id ;
	


-- 6.查询名字中含有'张'或'李'字的学生的信息和各科成绩。
SELECT
*
FROM
students s
LEFT JOIN courses c ON s.id = c.id
LEFT JOIN scores sc ON s.id = sc.s_id
WHERE
s.`name` LIKE '%张%'
OR s.`name` LIKE '%李%';


-- 7.查询平均成绩及格的同学的信息。（子查询）
SELECT
*
FROM
students s
WHERE s.id IN
(SELECT sc.s_id FROM scores sc GROUP BY sc.s_id
HAVING AVG(sc.score) > 60
);

-- 8.将学生按照总分数进行排名。（从高到低）
SELECT
s.id,s.`name`,SUM(sc.score)
FROM
students s 
LEFT JOIN scores sc ON s.id = sc.s_id
GROUP BY sc.s_id,s.`name`
ORDER BY SUM(sc.score) DESC,s_id ;


-- 9.查询数学成绩的最高分、最低分、平均分。
SELECT
c.`name`,MAX(sc.score),MIN(sc.score),AVG(sc.score)
FROM
courses c 
LEFT JOIN scores sc ON c.id = sc.c_id
WHERE c.`name` = '数学';


-- 10.将各科目按照平均分排序。
SELECT
c.id,c.`name`,AVG(sc.score)
FROM
courses c 
LEFT JOIN scores sc ON c.id = sc.c_id
GROUP BY c.`name`
ORDER BY AVG(sc.score);

3：结果展示