2022-8-16 mysql 第二天约束

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;

注意：

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降序排列。

聚合函数

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

多表查询

笛卡尔积

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.查询每个同学的最高成绩和科目名称****（明天说，子查询）
-- 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.查询每个课程的最高分的学生信息*****（明天说，子查询）

需求2

-- 6.查询名字中含有'张'或'李'字的学生的信息和各科成绩。
-- 7.查询平均成绩及格的同学的信息。（子查询）
-- 8.将学生按照总分数进行排名。（从高到低）
-- 9.查询数学成绩的最高分、最低分、平均分。
-- 10.将各科目按照平均分排序。

posted @ 2022-08-17 08:55 阿萨德菩提子阅读(50) 评论(0) 编辑收藏举报

刷新页面返回顶部

ychptz

2022-8-16 mysql 第二天约束

DQL数据库查询语言

构建数据库

单表查询

基本查询

基本语法

列运算

别名

条件控制

排序

聚合函数

count

max

min

sum

avg

分组查询

分页查询

多表查询

笛卡尔积

SQL92语法

SQL99语法

内连接

外连接（常用）

左连接

右连接

全连接

需求1

需求2

公告

ychptz

2022-8-16 mysql 第二天 约束

DQL数据库查询语言

构建数据库

单表查询

基本查询

基本语法

列运算

别名

条件控制

排序

聚合函数

count

max

min

sum

avg

分组查询

分页查询

多表查询

笛卡尔积

SQL92语法

SQL99语法

内连接

外连接（常用）

左连接

右连接

全连接

需求1

需求2

公告

2022-8-16 mysql 第二天约束