本文章是菜鸟在学习 MySQL数据库时做的记录，从基础 到 进阶 到 运维，实现轻松掌握MySQL数据库技能知识。
本文围绕以下展开介绍： 1.外键约束、2.多表查询 DQL、3.联合查询-union,union all、 4.子查询
1.约束
-- 约束 概念:约束是作用于表中字段上的规则，用于限制存储在表中的数据。 目的:保证数据库中数据的正确、有效性和完整性。 种类： 1)非空约束NOT NULL、 2)唯一约束UNIQUE、 3)主键约束PRIMARY KEY、 4)默认约束DEFAULT、 5)检查约束(8.0.16版本之后)CHECK、 6）外键约束FOREIGN KEY;
(1）非空约束
(2）唯一约束
（3）主键约束
（4）默认约束
（5）检查约束
先创建两个表 emp_1、dept
# 创建部门表dept
# （1）非空约束name用‘not null’限制非空，name必须有"数据"
# （3）主键约束id用 ‘primary key’,id 表示主键;
create table dept(
id int auto_increment comment 'ID' primary key ,
name varchar(50) not null comment '部门名称'
)comment '部门表';
insert into yfdb.dept values (1,'研发部'),
(2,'市场部'),
(3,'财务部'),
(4,'销售部'),
(5,'总经办');
# 创建员工表 emp_1
# (2)员工id 用‘unique’限制唯一，id不能重复
# (5) age用 ‘check’限制，检查年龄是否在范围内
create table emp_1(
id int unique comment '编号',
name varchar(10) comment '姓名',
age tinyint unsigned check ( age>0 && age<=120 ) comment '年龄',
job varchar(20) comment '职位',
salary int comment '薪资',
entrydate date comment '入职时间',
managerid int comment '直属领导',
dept_id int comment '部门ID'
) comment '员工表';
# 插入数据
insert into
emp_1(id,name,age,job,salary,entrydate,managerid,dept_id)
values (1,'金庸',66,'总裁',20000,'2000-01-01',null,5),
(2,'张无忌',18,'项目经理',18000,'2005-09-01',1,1),
(3, '韦一笑', 38, '开发',11000,'2002-08-01',2,1),
(5,'小昭',16,'程序员鼓励员',6600,'2007-07-01',2,1),
(6,'杨道',33,'开发',8400,'2000-11-01',2,1),
(7,'范瑶',40,'开发',10550,'2004-05-01',2,1);(6）外键约束FOREIGN KEY;
# 建立外界关联,添加外键，将dept_id、id关联
alter table yfdb.emp_1 add constraint fk_emp1_dept_id foreign key (dept_id) references yfdb.dept(id);在datagrip 中可以只管的看到两表的关系，
# 删除外键
alter table yfdb.emp_1 drop foreign key fk_emp1_dept_id;外键约束的 删除/更新行为
no action 当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。(与RESTRICT一致) -
restric 当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。(与NOACTION一致)
casecade 当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有，则也删除/更新外键在子表中的记录。
# 用 cascade 约束 创建外键
alter table yfdb.emp_1 add constraint fk_emp1_dept_id foreign key (dept_id) references yfdb.dept(id) on update cascade on delete cascade ;set null 当在父表中删除对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为nul(前提要求该外键允许取null)
alter table yfdb.emp_1 add constraint fk_emp1_dept_id foreign key (dept_id) references yfdb.dept(id) on update set null on delete set null;set default 父表有变更时，子表将外键列设置成一个默认的值(Innodb不支持)
2.多表查询 DQL
（1）多表关系： 一对多（多对一）、多对多 解决方法：建立中间表
创建表，学生表：student、课程表：course、中间表：student_course
#创建学生表
create table student(
id int primary key auto_increment comment '主键ID',
name varchar(10) comment '姓名',
no varchar(10) unique comment '学号'
)comment '学生表';
insert into yfdb.student values (1,'黛丽丝',20240101),
(2,'Jack',20240102),
(3,'欧文',20240103);
#创建课程表
create table course(
id int primary key auto_increment comment '主键ID',
name varchar(50) unique comment '课程名称'
)comment '课程表';
insert into yfdb.course values (null,'java'),
(null,'C++'),
(null,'MySQL'),
(null,'Python');通过 ”constraint 外键名 foreign key(本表列) references 关联表的列 “语句，创建外键
#创建中间表
# constraint 外键名 foreign key(本表列) references 关联表的列
create table student_course(
id int auto_increment comment '主键' primary key ,
studentid int not null comment '学生ID',
courseid int not null comment '科目ID',
constraint fk_courseid foreign key (courseid) references yfdb.course(id),
constraint fk_studentid foreign key (studentid) references yfdb.student(id)
) comment '学生课程中间表';
insert into yfdb.student_course values(null,1,1),
(null,1,2),
(null,1,3),
(null,2,2),
(null,2,3),
(null,3,4);datagrip工具展示三表的关系如图所示：
例子：展示学生所选的课程（多对多的关系），运行下面指令，结果如下图所示
select s.*,c.name from yfdb.student s,yfdb.course c,yfdb.student_course sc where s.id = sc.studentid and sc.courseid = c.id;
（2）一对一 表关系
创建表 员工表tb_user、学历表 tb_user_edu、
create table tb_user(
id int primary key auto_increment comment '主键ID',
name varchar(10) comment '姓名',
age int comment '年龄',
gender char(1) comment '性别',
phone char(11) comment '电话号码'
)comment '用户基本信息表';
create table tb_user_edu(
id int primary key auto_increment comment '主键ID',
degree varchar(20) comment '学历',
major varchar(50) comment '专业',
primaryschool varchar(50) comment '小学',
middleschool varchar(50) comment '中学',
university varchar(50) comment '大学',
userid int unique comment '用户ID',
constraint fk_userid foreign key(userid) references yfdb.tb_user(id)
)comment '用户基本信息表'
insert into yfdb.tb_user(id, name, age, gender, phone)
values (null,'Ros',30,'男','13888888888'),
(null,'Hipy',30,'男','13999999999'),
(null,'Tom',30,'男','13800000000'),
(null,'Jack',30,'男','13811111111');
insert into yfdb.tb_user_edu(id, degree, major, primaryschool, middleschool, university, userid)
values (null,'硕士','舞蹈','1小学','第一中学','第1学院',1),
(null,'本科','表演','1小学','第二中学','第2学院',2),
(null,'本科','美声','1小学','第三中学','第3学院',3),
(null,'本科','播音','1小学','第四中学','4学院',4);
连接方式：内连接、外连接、自连接
（1）内连接
-- 查询每一个员工的姓名，及教育背景（隐式内连接）
-- 表结构tb_user,tb_user_edu
-- 连接条件tb_user.id= tb_user_edu.userid;
隐式内连接： select 字段列表 from 表1，表2 where 条件;
-- 2.多表查询 查询时消除无效的笛卡尔积
select * from yfdb.tb_user u,yfdb.tb_user_edu ue where u.id = ue.userid;
显式内连接 "select 字段列表 from 表1，[inner] jion 表2 on 连接条件"， inner 可以省略
-- 查询每一个员工的姓名，及关联的部门名称（显式内连接）
select * from yfdb.tb_user u inner join yfdb.tb_user_edu ue where u.id = ue.userid;（2）外连接 emp_1表、dept表在第一篇mysql学习有创建
-- 查询emp_1表的所有数据，和对应的部门信息（左外连接,完全包含左表的数据，左表指from后的前表）
-- 1.查询emp_1表的所有数据，和对应的部门信息（左外连接,完全包含左表的数据）
-- 表结构emp_1,dept
-- 连接条件emp_1.dept_id= dept.id;
select e.*,d.name from emp_1 e left outer join dept d on e.dept_id = d.id;
select e.*,d.name from emp_1 e left join dept d on e.dept_id = d.id;
-- 查询dept表的所有数据，和对应的员工信息（右外连接，包含右表的数据）
-- 2.查询dept表的所有数据，和对应的员工信息（右外连接，包含右表的数据）
-- 表结构emp_1,dept
-- 连接条件emp_1.dept_id= dept.id;
select d.*,e.* from emp_1 e right outer join dept d on e.dept_id = d.id;
（3）自连接
-- 1.查询员工 及其所属领导的名字
-- 表结构emp_1,dept
select a.name,b.name from yfdb.emp_1 a, yfdb.emp_1 b where a.managerid = b.id;
-- 2.查询员工 及其所属领导的名字，如果员工没有领导也要查询出来
-- 表结构emp_1,dept
select a.name '员工', b.name '领导' from yfdb.emp_1 a left join yfdb.emp_1 b on a.managerid = b.id;
3.联合查询 -union, -union all
-- 对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。
-- union all 会将全部的数据直接合并在一起，存在重复项
select * from yfdb.emp_1 where salary < 10000
union all
select *from emp_1 where age > 50;
--union 会对合并之后的数据去重。union查询就是把多次查询的结果合并起来，形成一个新的查询结果集。不存在重复项
select * from yfdb.emp_1 where salary < 10000
union
select *from emp_1 where age > 50;
4.子查询
概念:SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询
子查询外部的语句可以是插入/更新/删除/选择的任何一个
SELECT * FROM t1 WHERE column1=(SELECT column1 FROM t2 );
-- 分类： 标量子查询(子查询结果为单个值) 查询研发部的所有员工信息（研发部dept_id=1）
select * from yfdb.emp_1 where dept_id =(select id from yfdb.dept where name ='研发部');-- 查询在“张无忌”后入职的所有员工
select * from yfdb.emp_1 where entrydate > (select entrydate from yfdb.emp_1 where name ='张无忌'); -- 列子查询(子查询结果为一列)
-- 常用的操作符:
-- IN(在指定的集合范围之内，多选一)、-- 查询研发部、财务部的所有员工信息
select * from yfdb.emp_1 where dept_id in (select id from dept where name ='研发部' or name ='财务部');
-- NOTIN(不在指定的集合范围之内)、 -- 查询研发部、财务部的所有员工信息
select * from yfdb.emp_1 where dept_id not in (select id from dept where name ='总经办');
-- ANY(子查询返回列表中，有任意一个满足即可)、
-- SOME(与ANY等同，使用SOME的地方都可以使用ANY)、
-- ALL(子查询返回列表的所有值都必须满足
-- 行子查询(子查询结果为一行) 常用的操作符:=、<>、IN、NOT IN
select * from emp_1 where (salary, managerid) = (select salary, managerid from yfdb.emp_1 where name='张无忌');
-- 表子查询(子查询结果为多行多列) 常用的操作符:IN
select a.name,a.age,a.job,d.name from yfdb.emp_1 a join yfdb.dept d on d.id = a.dept_id;
select a.name,a.age,a.job,d.name from yfdb.emp_1 a join yfdb.dept d on d.id = a.dept_id where a.age<30;
select * from yfdb.emp_1 e join yfdb.dept p on p.id=e.dept_id where (not isnull(managerid)) and (not isnull(p.name));
select * from yfdb.emp_1 e left join yfdb.dept p on p.id=e.dept_id where e.age>35;
select avg(e.salary) from yfdb.emp_1 e where dept_id =(select id from yfdb.dept where name ='研发部');
select * from yfdb.emp_1 e where salary>(select salary from yfdb.emp_1 where name ='灭绝');
select * from yfdb.emp_1 e where salary>(select avg(e.salary) from yfdb.emp_1 e);
select * from yfdb.emp_1 e where salary<(select avg(e.salary) from yfdb.emp_1 e);
select p.name,count(e.dept_id) from yfdb.dept p left join yfdb.emp_1 e on p.id = e.dept_id group by p.name;
select s.name,s.no,c.name from student s , student_course sc , course c where s.id = sc.studentid and sc.courseid = c.id;以上指令需要自己动手实践，笨鸟只是在这里记个笔记，供以后查看
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
