公告

View Post

20220820 第六组张嘉源学习笔记二

DQL数据库查询语言

用来查询记录，不会修改数据库和表结构

构建数据库

创建一张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.查询每个同学的最高成绩和科目名称
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 stu.*,co.`name` coname,sc.score
FROM student stu
LEFT JOIN scores sc ON sc.s_id = stu.id
LEFT JOIN course co ON sc.c_id = co.id
WHERE stu.`name` LIKE '%张%' OR stu.`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 stu.`name`,SUM(sc.score)
FROM student stu
LEFT JOIN scores sc ON sc.s_id = stu.id
LEFT JOIN course co ON sc.c_id = co.id
GROUP BY stu.`name`
ORDER BY SUM(sc.score) DESC;
-- 9.查询数学成绩的最高分、最低分、平均分。
SELECT co.`name` coname,MAX(sc.score),MIN(sc.score),AVG(sc.score)
FROM student stu
LEFT JOIN scores sc ON sc.s_id = stu.id
LEFT JOIN course co ON sc.c_id = co.id
WHERE co.`name` = '数学';
-- 10.将各科目按照平均分排序。
SELECT co.`name` coname,AVG(sc.score)
FROM student stu
LEFT JOIN scores sc ON sc.s_id = stu.id
LEFT JOIN course co ON sc.c_id = co.id
GROUP BY co.id
ORDER BY sc.score DESC;

需求3

-- 11.查询老师的信息和他所带的科目的平均分
SELECT t.*,co.`name` coname,AVG(sc.score)
FROM teacher t
LEFT JOIN course co ON co.t_id = t.id
LEFT JOIN scores sc ON co.id = sc.c_id
GROUP BY t.id
-- 12.查询被"Tom"和"Jerry"教的课程的最高分和最低分
SELECT t.`name`,co.`name` coname,MAX(sc.score),MIN(sc.score)
FROM teacher t
LEFT JOIN course co ON co.t_id = t.id
LEFT JOIN scores sc ON co.id = sc.c_id
WHERE t.`name` = 'Tom'
OR t.`name` = 'Jerry'
GROUP BY t.`name`
-- 13.查询每个学生的最好成绩的科目名称（子查询）
SELECT stu.`name`,co.`name`,sc.score
FROM scores sc
LEFT JOIN student stu ON stu.id = sc.s_id
LEFT JOIN course co ON sc.c_id = co.id
WHERE sc.score IN 
(SELECT MAX(score)
FROM scores
GROUP BY scores.s_id);
-- 14.查询所有学生的课程及分数
SELECT stu.id,stu.`name`,co.`name` coname,sc.score
FROM student stu
LEFT JOIN scores sc ON sc.s_id = stu.id
LEFT JOIN course co ON sc.c_id = co.id
ORDER BY stu.id 
-- 15.查询课程编号为1且课程成绩在60分以上的学生的学号和姓名（子查询）
SELECT stu.`name`,co.id,co.`name` coname,sc.score
FROM student stu
LEFT JOIN scores sc ON sc.s_id = stu.id
LEFT JOIN course co ON sc.c_id = co.id
WHERE (co.id,sc.score)
IN (SELECT course.id,scores.score
FROM course
LEFT JOIN scores ON scores.c_id = course.id
WHERE id = '1' AND score >= '60');

需求4

-- 16. 查询平均成绩大于等于65的所有学生学号、姓名和平均成绩
SELECT stu.id,stu.`name`,AVG(sc.score)
FROM student stu
LEFT JOIN scores sc ON sc.s_id = stu.id
GROUP BY stu.id
HAVING AVG(sc.score) >= '65';
-- 17.查询有不及格课程的学生信息
SELECT stu.*
FROM student stu
LEFT JOIN scores sc ON sc.s_id = stu.id
WHERE sc.score < '60'
GROUP BY stu.id
-- 18.查询每门课程有成绩的学生人数
SELECT sc.c_id,count(*)
FROM scores sc
GROUP BY sc.c_id
-- 19.查询每门课程的平均成绩，结果按照平均成绩降序排列，如果平均成绩相同，再按照课程编号升序排列
SELECT AVG(score)
FROM course co
LEFT JOIN scores sc ON co.id = sc.c_id
GROUP BY co.id,co.`name`
ORDER BY score DESC,co.id ASC
-- 20.查询平均成绩大于60分的同学的学生编号和学生姓名和平均成绩
SELECT stu.id,stu.`name`,AVG(sc.score)
FROM student stu
LEFT JOIN scores sc ON sc.s_id = stu.id
GROUP BY stu.id
HAVING AVG(sc.score) >= '60';

需求5

-- 21.查询有且仅有一门课程成绩在80分以上的学生信息
SELECT stu.*
FROM scores sc
LEFT JOIN student stu ON sc.s_id = stu.id
WHERE sc.score > '80'
GROUP BY stu.id
HAVING count(sc.s_id) = 1
-- 22.查询出只有三门课程的学生的学号和姓名
SELECT stu.*
FROM scores sc
LEFT JOIN student stu ON sc.s_id = stu.id
GROUP BY stu.id
HAVING count(sc.s_id) = 3
-- 23.查询有不及格课程的课程信息
SELECT co.*
FROM course co
LEFT JOIN scores sc ON co.id = sc.c_id
WHERE sc.score < '60'
GROUP BY co.id
-- 24.查询至少选择4门课程的学生信息
SELECT stu.*
FROM student stu
LEFT JOIN scores sc ON stu.id = sc.s_id
GROUP BY stu.id
HAVING count(sc.c_id) >= 4
-- 25.查询没有学全所有课程的同学的信息
SELECT stu.*
FROM student stu
LEFT JOIN scores sc ON stu.id = sc.s_id
GROUP BY stu.id
HAVING count(sc.c_id) != 
(SELECT count(course.id)
FROM course);

需求6

-- 26.查询选全所有课程的同学的信息
SELECT stu.*
FROM student stu
LEFT JOIN scores sc ON stu.id = sc.s_id
GROUP BY stu.id
HAVING count(sc.c_id) = 
(SELECT count(course.id)
FROM course);
-- 27.查询各学生都选了多少门课
SELECT stu.`name`,count(*)
FROM scores sc
LEFT JOIN student stu ON sc.s_id = stu.id
GROUP BY stu.id
-- 28.查询课程名称为"java"，且分数低于60分的学生姓名和分数
SELECT stu.`name`,sc.score
FROM student stu
LEFT JOIN scores sc ON sc.s_id = stu.id
LEFT JOIN course co ON sc.c_id = co.id
WHERE co.`name` = 'Java'
AND sc.score < '60'
GROUP BY stu.id
-- 29.查询学过"Tony"老师授课的同学的信息
SELECT stu.*
FROM student stu
LEFT JOIN scores sc ON stu.id = sc.s_id
LEFT JOIN course co ON sc.c_id = co.id
LEFT JOIN teacher t ON co.t_id = t.id
WHERE t.`name` = 'Tom'
GROUP BY stu.id
-- 30.查询没学过"Tony"老师授课的学生信息
SELECT *
FROM student 
WHERE id NOT IN(
SELECT stu.id
FROM student stu
LEFT JOIN scores sc ON stu.id = sc.s_id 
LEFT JOIN course co ON sc.c_id = co.id
LEFT JOIN teacher t ON co.t_id = t.id
WHERE t.`name` = 'Tom'
GROUP BY stu.id);

MySQL常用函数

聚合函数

count：计数。count（*）=count（1）> count（主键）

count（*）
count（1）
count（主键）
count（字段）

min

max

sum

avg

数值型函数

主要是对数值型进行处理

ceiling(x)：向上取整

floor(x)：向下取整

round(x)：四舍五入

truncate(x,y)：返回数字x截断为y位小数的结果

PI：圆周率，π

rand：返回0到1的随机数

abs：绝对值

-- 绝对值
select ABS(-4) 4的绝对值,ABS(-1.1);
-- 向下取整，向上取整，四舍五入
select CEILING(4.1),FLOOR(1.1),ROUND(-4.4)
-- 取余
select MOD(60,11);
-- 随机数
select RAND(),RAND(),RAND()
-- 截断
select TRUNCATE(2.33999999,2);

字符串型函数

对字符串进行处理

length(s)：字符串的长度

concat(s1,s2,.....sn)：合并字符串

lower(str)：将字母转成小写

upper(str)：将字母转成大写

left(str,x)：返回字符串str的左边的x个字符

right(str,x)：返回字符串str右边的x个字符

trim：去掉左右两边的空格

replace：替换

substring：截取

reverse：反转

select LEFT('abcdefg',2);
select RIGHT('abcdefg',2);
select REVERSE('hijklmn');
select REPLACE('abcdefg','abc','x');

日期和时间函数

date,time,datetime,timestamp,year

获取时间和日期

【curdate】和【current_date】，返回当前的系统日期。

【curtime】和【current_time】，返回当前的系统时间。

【now】和【sysdate】，返回当前的系统时间和日期。

select CURRENT_DATE();
select CURTIME();
select now();

时间戳和日期转换函数

【UNIX_TIMESTAMP】获取unix时间戳函数

【FROM_UNIXTIME】将时间戳转换为时间格式

select UNIX_TIMESTAMP();
select FROM_UNIXTIME(1660785720);

根据日期获取年月日的数值

select MONTH(SYSDATE());
select MONTHNAME(SYSDATE());
select DAYNAME(SYSDATE());
select DAYOFWEEK(SYSDATE());
select WEEK(SYSDATE());
select DAYOFMONTH(SYSDATE());
select YEAR(SYSDATE());

时间日期的计算

-- 日期加法
select DATE_ADD(SYSDATE(),INTERVAL 70 DAY);
-- 日期减法
select DATE_SUB(SYSDATE(),INTERVAL 10 DAY);
-- 时间间隔
select DATEDIFF('2023-01-01',SYSDATE());
-- 日期格式化
select DATE_FORMAT(SYSDATE(),'%W %M %D %Y');

加密函数

-- 把传入的参数的字符串按照md5算法进行加密，得到一个32位的16进制的字符串
select MD5('123456');

Md5算法是不可逆的

流程控制函数

可以进行条件判断，用来实现SQL语句的逻辑。

if(test,t,f)：如果test是真，则返回t，否则返回f
ifnull(arg1,arg2)：如果arg1不是空，返回arg1，否则返回arg2
nullif(arg1,arg2)：如果arg1=arg2返回null，否则返回arg1

select IF(2 > 1,'a','b');
select IFNULL(sal,0);
select NULLIF(age,0);

对一系列的值进行判断：

-- 输出学生的各科的成绩，以及评级，60以下D，60-70是C，71-80是B，80以上是A
SELECT
	*,
CASE
		
		WHEN score < 60 THEN 'D' WHEN score >= 60 
		AND score < 70 THEN 'C' WHEN score >= 70 
			AND score < 80 THEN 'B' WHEN score >= 80 THEN
				'A' 
			END AS '评级' 
	FROM
	mystudent;

-- 行转列
SELECT
	user_name,
	max( CASE course WHEN '数学' THEN score ELSE 0 END ) '数学',
	max( CASE course WHEN '语文' THEN score ELSE 0 END ) '语文',
	max( CASE course WHEN '英语' THEN score ELSE 0 END ) '英语' 
FROM
	mystudent 
GROUP BY
	user_name

数据库设计

三范式

第一范式：要求有主键，并且要求每一个字段的原子性不能再分。
第二范式：要求所有的非主键字段完全依赖主键，不能产生部分依赖
第三范式：所有非主键字段和主键字段之间不能产生传递依赖。

第一范式

不符合第一范式表结构：

id	name	联系方式
1001	aaa、	[aaa@163.com , 13314569878](mailto:aaa@163.com , 13314569878)
1002	bbb	[bbb@163.com , 13245678945](mailto:bbb@163.com , 13245678945)
1003	ccc	[ccc@163.com , 15000456987](mailto:ccc@163.com , 15000456987)

符合第一范式的表结构：

id	name	邮箱	手机号
1001	aaa	aaa@163.com	12321321321
1002	bbb	bbb@163.com	32132654654
1003	ccc	ccc@163.com	45654654654

必须有主键，这是数据库设计的基本要求，一般情况下我们采用数值型或定长字符串，列不能再分，比如：联系方式。

关于第一范式，保证每一行的数据是唯一，每个表必须有主键。

第二范式

建立在第一范式的基础上，要求所有非主键字段完全依赖于主键，不能产生部分依赖。

学号	性别	姓名	课程编号	课程名称	教室	成绩
1001	男	a	2001	java	301	89
1002	女	b	2002	mysql	302	90
1003	男	c	2003	html	303	91
1004	男	d	2004	python	304	52
1005	女	e	2005	c++	305	67
1006	男	f	2006	c#	306	84

解决方案：

学生表：学号是主键

学号	性别	姓名
1001	男	a
1002	女	b
1003	男	c
1004	男	d
1005	女	e
1006	男	f

课程表：课程编号是主键

课程编号	课程名称	教室
2001	java	301
2002	mysql	302
2003	html	303
2004	python	304
2005	c++	305
2006	c#	306

成绩表：学号和课程编号为联合主键

学号	课程编号	成绩
1001	2001	89
1002	2002	90
1003	2003	91
1004	2004	52
1005	2005	67
1006	2006	84

第三范式

建立在第二范式基础上，非主键字段不能传递依赖于主键字段。

不满足第三范式：

学号	姓名	课程编号	课程名称
1001	a	2001	java
1002	b	2002	mysql
1003	c	2003	html
1004	d	2004	python
1005	e	2005	c++
1006	f	2006	c#

解决方案：

学生表：学号是主键

学号	姓名	课程编号
1001	a	2001
1002	b	2002
1003	c	2003
1004	d	2004
1005	e	2005
1006	f	2006

课程表：课程编号是主键

课程编号	课程名称
2001	java
2002	mysql
2003	html
2004	python
2005	c++
2006	c#

常见的表关系

一对一

学生信息表分为基本信息表和信息信息表。

分为两张表，共享主键。
分两张表，用外键连接。

一对多

两张表，外键在多的一方。

分两张表存储，在多的一方加外键
这个外键字段引用是一的一方的主键

多对多

分三张表存储，在学生表存储学生信息，在课程表存储课程信息。
在成绩表中存储学生和课程的对应关系。

posted on 2022-08-20 14:40 林钟朔一阅读(19) 评论(0) 编辑收藏举报

刷新页面返回顶部

lzsykal

公告

View Post

20220820 第六组 张嘉源 学习笔记二

DQL数据库查询语言

构建数据库

单表查询

基本查询

基本语法

查询所有列

查询指定的列

列运算

别名

条件控制

排序

升序

降序

聚合函数

count

max

min

sum

avg

分组查询

where和having的区别

分页查询

多表查询

笛卡尔积

多表连接的方式

SQL92语法

SQL99语法

内连接

外连接（常用）

左连接

右连接

全连接

需求1

需求2

需求3

需求4

需求5

需求6

MySQL常用函数

聚合函数

数值型函数

字符串型函数

日期和时间函数

获取时间和日期

时间戳和日期转换函数

根据日期获取年月日的数值

时间日期的计算

加密函数

流程控制函数

数据库设计

三范式

第一范式

第二范式

第三范式

常见的表关系

一对一

一对多

多对多

20220820 第六组张嘉源学习笔记二