快速跳转✅
1.数据库的约束
1.1 约束类型
- NOT NULL - 指示某列不能存储 NULL 值。
- UNIQUE - 保证某列的每行必须有唯一的值。
- DEFAULT - 规定没有给列赋值时的默认值。
- PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列(或两个列多个列的结合)有唯一标
识,有助于更容易更快速地找到表中的一个特定的记录。 - FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。
- CHECK - 保证列中的值符合指定的条件。对于MySQL数据库,对CHECK子句进行分析,但是忽略
CHECK子句
1.2 NULL约束
创建表时,可以指定某列不为空:
-- 重新设置学生表结构
DROP TABLE IF EXISTS student;
CREATE TABLE student (
id INT NOT NULL,
sn INT,
name VARCHAR(20),
qq_mail VARCHAR(20)
);
1.3 UNIQUE:唯一约束
指定sn列为唯一的 不重复的
DROP TABLE IF EXISTS student;
CREATE TABLE student (
id INT NOT NULL,
sn INT UNIQUE,
name VARCHAR(20),
qq_mail VARCHAR(20)
);
1.4 DEFAULT:默认值约束
指定插入数据时,name列为空,默认值unkown:
-- 重新设置学生表结构
DROP TABLE IF EXISTS student;
CREATE TABLE student (
id INT NOT NULL,
sn INT UNIQUE,
name VARCHAR(20) DEFAULT 'unkown',
qq_mail VARCHAR(20)
);
1.5 PRIMARY KEY:主键约束
主键约束也就是 : 设置的那一列主键值不能重复,也不能为null
指定id列为主键:
-- 重新设置学生表结构
DROP TABLE IF EXISTS student;
CREATE TABLE student (
id INT NOT NULL PRIMARY KEY,
sn INT UNIQUE,
name VARCHAR(20) DEFAULT 'unkown',
qq_mail VARCHAR(20)
);
对于整数类型的主键,常配搭自增长auto_increment
来使用。插入数据对应字段不给值时,使用最大
值+1。
-- 主键是 NOT NULL 和 UNIQUE 的结合,可以不用 NOT NULL
id INT PRIMARY KEY auto_increment ;
1.6 FOREIGN KEY:外键约束
外键用于关联其他表的主键或唯一键,语法:
foreign key (字段名) references 主表(列)
案例:
- 创建班级表classes,id为主键:
//创建班级表,有使用MySQL关键字作为字段时,需要使用``来标识
DROP TABLE IF EXISTS classes;
CREATE TABLE classes (
id INT PRIMARY KEY auto_increment,
name VARCHAR(20),
`desc` VARCHAR(100)
);
创建学生表student,一个学生对应一个班级,一个班级对应多个学生。使用id为主键,classes_id为外键,关联班级表id
-- 重新设置学生表结构
DROP TABLE IF EXISTS student;
CREATE TABLE student (
id INT PRIMARY KEY auto_increment,
sn INT UNIQUE,
name VARCHAR(20) DEFAULT 'unkown',
qq_mail VARCHAR(20),
classes_id int,
FOREIGN KEY (classes_id) REFERENCES classes(id)
);
讲解:
如图我们设置了班级表classes,班级ID为自增主键,以及学生表student,学生ID为自增主键;
要实现的逻辑是一个学生只能去一个班,一个班可以有多个学生;
也就在学生表里面的class_id设置外键约束自classes表中的id;
也就是说插入学生数据时候学生班级ID需要在班级表中存在才可以插入成功,否则不成功
如图插入1-3班的学生数据都可以成功,因为班级表中就有这三个班级,而插入6班时候就因为外键约束而插入失败了;
没有班级ID为6的同学 ;
1.7 CHECK约束(了解)
MySQL使用时不报错,但忽略该约束:
drop table if exists test_user;
create table test_user (
id int,
name varchar(20),
sex varchar(1),
check (sex ='男' or sex='女')
);
2.表的设计
三大方式:
- 示例1:
设计一个学校食堂管理系统,学校食堂管理系统,包含食堂表,食堂仓口表,仓口收费记录表
-- 一个食堂有多个仓口卖饭,关系为1:m,每个仓口卖饭可以有很多次,仓口与收费记录也是1:m
// m:多
create table hall(
id int primary key,
name varchar(20)
);
create table hall_opening(
id int primary key,
name varchar(20),
hall_id int,
foreign key (hall_id) references hall(id)
);
create table info(
id int primary key,
price int,
info_date timestamp,
hall_opening_id int,
foreign key (hall_opening_id) references hall_opening(id)
);
- 示例2:
设计一个车辆违章系统,车辆违章系统,包含用户表,车辆表,违章信息表。违章信息表中包含用户和车辆的违章信息
-- 用户可以拥有多辆车,关系为1:m,题目已经说明违章信息包括用户和车辆,说明违章信息表中要记录用户和车辆,一个用户可以有多次违章记录,用户与违章记录关系为1:m,一辆车也可以有多次违章记录,车辆与违章记录关系也为1:m
create table user(
id int primary key,
name varchar(20)
);
create table cars(
id int primary key,
name varchar(20),
user_id int,
foreign key (user_id) references user(id)
);
create table info(
id int primary key,
user_id int,
cars_id int,
foreign key (user_id) references user(id),
foreign key (cars_id) references cars(id)
);
- 示例3:
设计一个学校宿舍管理系统,学校宿舍管理系统,要求包含宿舍信息,学生信息,每日的宿舍查房记录。
-- 主要考虑学生与宿舍的关系:m:1,宿舍的查房记录是根据宿舍来查的,与宿舍有关系,一个宿舍可以多次查房,宿舍与查房记录是1:m的关系
create table dormitory(
id int primary key,
number varchar(20)
);
create table student(
id int primary key,
name varchar(20),
dormitory_id int,
foreign key (dormitory_id) references dormitory(id)
);
create table info(
id int primary key,
dormitory_id int,
status bit,
info_date timestamp,
foreign key (dormitory_id) references dormitory(id)
);
- 示例4:
设计一个考勤系统,考勤系统,包含员工表,考勤记录表
-- 主要考虑记录表中的记录信息,是如何关联到员工表,员工与记录关系为1:m。
create table emp(
id int primary key,
name varchar(20)
);
create table info(
id int primary key,
emp_id int,
info_date timestamp,
foreign key (emp_id) references emp(id)
);
- 示例5:
设计课程表,课程包包含学生信息
-- 创建课程表
DROP TABLE IF EXISTS course;
CREATE TABLE course (
id INT PRIMARY KEY auto_increment,
name VARCHAR(20)
);
-- 创建课程学生中间表:考试成绩表
DROP TABLE IF EXISTS score;
CREATE TABLE score (
id INT PRIMARY KEY auto_increment,
score DECIMAL(3, 1),
student_id int,
course_id int,
FOREIGN KEY (student_id) REFERENCES student(id),
FOREIGN KEY (course_id) REFERENCES course(id)
);
3. 新增
插入查询结果:
语法:
INSERT INTO table_name [(column [, column ...])] SELECT ...
案例:创建一张用户表,设计有name姓名、email邮箱、sex性别、mobile手机号字段。需要把已有的
学生数据复制进来,可以复制的字段为name、qq_mail
-- 创建用户表
DROP TABLE IF EXISTS test_user;
CREATE TABLE test_user (
id INT primary key auto_increment,
name VARCHAR(20) comment '姓名',
age INT comment '年龄',
email VARCHAR(20) comment '邮箱',
sex varchar(1) comment '性别',
mobile varchar(20) comment '手机号'
);
-- 将学生表中的所有数据复制到用户表
insert into test_user(name, email) select name, qq_mail from student;
这个SQL语句就要把test2查询到的数据相应的插入到test1表中
4. 查询
4.1 聚合查询
4.1.1 聚合函数
常见的统计总数、计算平局值等操作,可以使用聚合函数来实现,常见的聚合函数有:
案例:
- count
-- 统计班级共有多少同学
SELECT COUNT(*) FROM student;
SELECT COUNT(0) FROM student;
-- 统计班级收集的 qq_mail 有多少个,qq_mail 为 NULL 的数据不会计入结果
SELECT COUNT(qq_mail) FROM student;
注意:
count(*) 跟 count(0) 的结果一样,都包括对NULL的统计,表示的是当前表中一共有几条记录,而count(column) 是不包括NULL的统计.
- sum
-- 统计数学成绩总分
SELECT SUM(math) FROM exam_result;
-- 不及格 < 60 的总分,没有结果,返回 NULL
SELECT SUM(math) FROM exam_result WHERE math < 60;
如果查询的不是数字,则结果为0 没有意义
- AVG
-- 统计平均总分
SELECT AVG(chinese + math + english) 平均总分 FROM exam_result;
如果表中有null,则那一列不计入除数 ;
- MAX
-- 返回英语最高分
SELECT MAX(english) FROM exam_result;
- MIN
-- 返回 > 70 分以上的数学最低分
SELECT MIN(math) FROM exam_result WHERE math > 70;
注意:聚合查询里面如果有where条件判断的话,先执行where条件判断,判断出来的临时表,再执行聚合查询;
4.1.2 GROUP BY子句
SELECT 中使用 GROUP BY 子句可以对指定列进行分组查询。需要满足:使用 GROUP BY 进行分组查询时,SELECT 指定的字段必须是“分组依据字段”,其他字段若想出现在SELECT 中则必须包含在聚合函数中。
select column1, sum(column2), .. from table group by column1,column3;
案例:
- 准备测试表及数据:职员表,有id(主键)、name(姓名)、role(角色)、salary(薪水)
create table emp(
id int primary key auto_increment,
name varchar(20) not null,
role varchar(20) not null,
salary numeric(11,2)
);
insert into emp(name, role, salary) values
('马云','服务员', 1000.20),
('马化腾','游戏陪玩', 2000.99),
('孙悟空','游戏角色', 999.11),
('猪无能','游戏角色', 333.5),
('沙和尚','游戏角色', 700.33),
('隔壁老王','董事长', 12000.66);
- 查询每个角色的最高工资、最低工资和平均工资
select role,max(salary),min(salary),avg(salary) from emp group by role;
也就是先根据role分组,role一样的记录为一组;分好组之后再根据不同的组进行相应的聚合查询
先分组,后查询;
是在不同的行之间进行的聚合查询;
4.1.3 HAVING
GROUP BY 子句进行分组以后,需要对分组结果再进行条件过滤时,不能使用 WHERE 语句,而需要用HAVING
- 显示平均工资低于1500的角色和它的平均工资
原 :
筛选后:
4.1.4 聚合查询案例
有一张员工表emp,字段:姓名name,性别sex,部门depart,工资salary。查询以下数据:
1、查询男女员工的平均工资
2、查询各部门的总薪水
3、查询总薪水排名第二的部门
4、查询姓名重复的员工信息
5、查询各部门薪水大于10000的男性员工的平均薪水
答案:
1:select sex,avg(salary) from emp group by sex;
说明:平均值使用聚合函数avg,并且按照性别男女分组,group by 性别字段
2:select depart,sum(salary) from emp group by depart;
说明:总薪水使用聚合函数sum取薪水字段求和,并且按照部门字段分组,group by 部门字段
3:select depart,sum(salary) from emp group by depart order by sum(salary) desc limit 1,1;
说明:order by语句先按照总薪水排序,之后取第二条数据,可以使用分页,每一页1条数据,第二页就是该结果
4:select name from emp group by name having count(name)>1;
说明:名字重复,说明同一个名字有多条数据,可以先按照名字分组,分组之后再过滤行数大于1的,就表示同一个名字至少有2条记录,重复了
5:select depart,avg(salary) from emp where salary>10000 and sex=‘男’ group by depart;
说明:这里需要注意题目要求是查询薪水大于10000的男性员工,这个是在按部门分组前就过滤,在过滤后的结果集中再查询各个部门的平均薪水
4.2 联合查询
实际开发中往往数据来自不同的表,所以需要多表联合查询。多表查询是对多张表的数据取笛卡尔积:
笛卡尔积类似于排列组合,也就是两个表(一个长M,一个长N(数据表的有多少列就对应的有多少行))进行笛卡尔积后,笛卡尔积表记录为M*N条,字段(列)为M+N个
注意:关联查询可以对关联表使用别名。
初始化测试数据:
drop table if exists classes;
drop table if exists student;
drop table if exists course;
drop table if exists score;
create table classes (id int primary key auto_increment, name varchar(20), `desc` varchar(100));
create table student (id int primary key auto_increment, sn varchar(20), name varchar(20), qq_mail varchar(20) ,
classes_id int);
create table course(id int primary key auto_increment, name varchar(20));
create table score(score decimal(3, 1), student_id int, course_id int);
insert into classes(name, `desc`) values
('计算机系2019级1班', '学习了计算机原理、C和Java语言、数据结构和算法'),
('中文系2019级3班','学习了中国传统文学'),
('自动化2019级5班','学习了机械自动化');
insert into student(sn, name, qq_mail, classes_id) values
('09982','黑旋风李逵','xuanfeng@qq.com',1),
('00835','菩提老祖',null,1),
('00391','白素贞',null,1),
('00031','许仙','xuxian@qq.com',1),
('00054','不想毕业',null,1),
('51234','好好说话','say@qq.com',2),
('83223','tellme',null,2),
('09527','老外学中文','foreigner@qq.com',2);
insert into course(name) values
('Java'),('中国传统文化'),('计算机原理'),('语文'),('高阶数学'),('英文');
insert into score(score, student_id, course_id) values
-- 黑旋风李逵
(70.5, 1, 1),(98.5, 1, 3),(33, 1, 5),(98, 1, 6),
-- 菩提老祖
(60, 2, 1),(59.5, 2, 5),
-- 白素贞
(33, 3, 1),(68, 3, 3),(99, 3, 5),
-- 许仙
(67, 4, 1),(23, 4, 3),(56, 4, 5),(72, 4, 6),
-- 不想毕业
(81, 5, 1),(37, 5, 5),
-- 好好说话
(56, 6, 2),(43, 6, 4),(79, 6, 6),
-- tellme
(80, 7, 2),(92, 7, 6);
4.2.1 内连接
select 字段 from 表1 别名1 [inner] join 表2 别名2 on 连接条件 and 其他条件;
select 字段 from 表1 别名1,表2 别名2 where 连接条件 and 其他条件; (推荐使用)
案例:
- (1)查询“许仙”同学的 成绩
- 先将学生表以及成绩表进行笛卡尔积:
- 过滤无效数据
- 定位许仙,过滤其他行,只留下姓名以及成绩行
或者:
select student.name,score.score from student (inner)join score on student.id=score.student_id and name='许仙';
-- from+表1+join+表2+on+条件
-- 或者
select sco.score from student stu, score sco where stu.id=sco.student_id and
stu.name='许仙';
- (2)查询所有同学的总成绩,及同学的个人信息:
这个案例要在多表查询的基础上再加一个聚合查询;
- 这个操作就显示出每个同学每个科目的分数,可以看出,同学的分数,是按照行的方式来排列的;
- 加上group by id 之后,可以看到,记录的行数明显变少了.每个同学只有一行数据了
分组之后,可以看到,当前这里的score列并不是总成绩,而是每个分组中的第一条记录
- 此处需要得到总成绩,就需要进行聚合查询sum操作
或者 :
-- 成绩表对学生表是多对1关系,查询总成绩是根据成绩表的同学id来进行分组的
SELECT
stu.sn,
stu.NAME,
stu.qq_mail,
sum( sco.score )
FROM
student stu
JOIN score sco ON stu.id = sco.student_id
GROUP BY
sco.student_id;
- (3) 查询所有同学的成绩,及同学的个人信息:
这里需要查询 同学的成绩,就需要学生表以及成绩表,还要查询个人信息,就需要课程表
所以这题就是涉及三个表的多表查询
- 先按照三个表直接的连接条件将其多表查询出来:
当前这个表就列出了每个同学的每个课程拿到的分数.同时带有课程的名字
- 去掉不必要的列,只保留关注的关键列
(join on 写法):
select student.name,course.name,score.score from student join score on student.id=score.student_id join course on score.course_id=course.id;
或者:
-- 查询出来的都是有成绩的同学,“老外学中文”同学 没有显示
select * from student stu join score sco on stu.id=sco.student_id;
-- 学生表、成绩表、课程表3张表关联查询
SELECT
stu.id,
stu.sn,
stu.NAME,
stu.qq_mail,
sco.score,
sco.course_id,
cou.NAME
FROM
student stu
JOIN score sco ON stu.id = sco.student_id
JOIN course cou ON sco.course_id = cou.id
ORDER BY
stu.id;
4.2.2 外连接
外连接分为左外连接和右外连接。如果联合查询,左侧的表完全显示我们就说是左外连接;右侧的表完
全显示我们就说是右外连接。
语法:
-- 左外连接,表1完全显示
select 字段名 from 表名1 left join 表名2 on 连接条件;
-- 右外连接,表2完全显示
select 字段 from 表名1 right join 表名2 on 连接条件;
案例:
创建两个数据表,一个学生表,一个成绩表,如图所示:
- 先看内连接:
图示:
select * from student inner join score on student.id=score.student_id;
内连接里的记录就只是包含两个表中同时拥有的记录
- 再看外连接:
- 左外连接
图示:
select * from student left join score on student.id=score.student_id;
左外连接就是以左侧表为主,左侧表中的每个记录都在左外连接中有体现,数据不存在的话就直接填成null;
- 右外连接
图示:
select * from student right join score on student.id=score.student_id;
右外连接就是以右侧表为主,右侧表中的每个记录都在左外连接中有体现,数据不存在的话就直接填成null;
了解:
4.2.3 联合查询案例
有员工表、部门表和薪资表,根据查询条件写出对应的sql【同程艺龙2020届校招笔试题】
现在有员工表、部门表和薪资表。部门表depart的字段有depart_id, name;员工表 staff 的字段有 staff_id, name, age, depart_id;薪资表salary 的字段有 salary_id,staff_id,salary,month。
(问题a):求每个部门’2016-09’月份的部门薪水总额
说明:
mysql中年和月的函数分别是year(字段),month(字段)
查询要求的是每个部门的信息,所以要按照部门进行分组,部门和员工为1:m,员工与薪水为1:m,查询要求有部门,有薪水,所以必须关联3张表查询
(问题b):求每个部门的部门人数,要求输出部门名称和人数
说明:查询要求的信息有部门,有人数,人数只能从员工表中获取,所以关联表为部门表/员工表。按照部门id分组查询员工id的行数
(问题c):求公司每个部门的月支出薪资数,要求输出月份和本月薪资总数
说明:按照题目要求,查询信息有部门/月/每个部门每月薪资总额,其中薪水信息再薪水表中,每个员工可以有多条薪资记录(不同时间)。所以需要按照部门分组,再按照月份分组,查询分组的部门在不同月份下的总薪水
-- 1.
select depart.name,sum(salary.salary) from salary join staff on staff.staff_id=salary_staff_id join depart on
staff.depart_id=depart.depart_id where year=2016 and month=9 group by depart.depart_id;
-- 2.
select depart.name,count(staff.staff_id) from staff join depart on staff.depart_id=depart.depart_id
group by depart.depart_id;
-- 3.
select depart.name,salary.month,sum(salary.salary) from depart join staff on depart.depart_id=staff.depart_id
join salary on staff.staff_id = salary.staff_id group by depart.depart_id,salary.month;
- over ~