【MYSQL8.0从入门到精通】

内部静态类

已于 2023-09-21 11:10:12 修改

阅读量478

点赞数

分类专栏： mysql 文章标签： mysql

于 2023-08-26 16:03:00 首次发布

本文链接：https://blog.csdn.net/qq_52421292/article/details/132512546

版权

mysql 专栏收录该内容

1 篇文章 1 订阅

订阅专栏

文章目录

MySQL 8.0
- 一.MySQL的多表操作

MySQL 8.0

一.MySQL的多表操作

1.外键约束（一对多）

方式1 在创建表的同时创建外键约束

-- 1.创建主表
create table if not exists dept(
	deptno varchar(20) primary key, -- 部门编号 主键
	name varchar(20)  -- 部门名称
)

-- 2.创建从表 
create table if not exists emp(
	eid varchar(20) primary key, -- 员工编号 主键
	ename varchar(20), -- 员工姓名
	eage int(2),  -- 员工年龄
	dept_id varchar(20), -- 部门编号
	constraint emp_fk foreign key (dept_id) references dept (deptno) -- 添加外键约束 
)

方式2 创建完表之后再创建外键约束

alter table <表名> add constraint <外键名> foreign key (列名) references <主表>(主键列名)
alter table emp2 add constraint emp_fk2 foreign key(dept_id) references dept2(deptno);

查看表之间的关联（先打开主表）

在这里插入图片描述

注：删除数据主表的数据被从表依赖时不能删除,从表数据可以任意删除
先删除从表再删除主表,外键约束对多表查询没有影响

删除数据

删除外键约束:

alter table <表名> drop foreign key <外键约束名>;
alter table emp2 drop foreign key emp_fk2 ;

2.外键约束（多对多）

多对多约束需要增加一个中间表

在这里插入图片描述

-- 1.创建学生主表
create table if not exists student(
	sid int primary key auto_increment,
	name varchar(20),
	age int,
	gender varchar(20)
);
-- 2.创建课程主表
create table if not exists course(
	cid int primary key auto_increment,
	cname varchar(20)
);
-- 3.创建中间表
create table if not exists score(
 sid int,
 cid int,
 score double
);
-- 4.创建外键约束
alter table score add constraint score_fk1 foreign key (sid) references student(sid);
alter table score add constraint score_fk2 foreign key (cid) references course(cid);

3.多表联合查询

外键约束对多表查询并无影响

在这里插入图片描述

create table if not exists dept3(
	deptno varchar(20) primary key,
	name varchar(20)
);

create table if not exists emp3(
	eid varchar(20) primary key,
	ename varchar(20),
	age int,
	dept_id varchar(20)
);

-- 添加外键约束
alter table emp3 add constraint foreign key (dept_id) references dept3(deptno);

ctrl + r navicat中执行sql语句快捷键

3.1 交叉查询(做笛卡尔积)

select * from dept3,emp3;

3.2 内连接查询(求交集)

内连接查询是求多表的交集对象，共性的地方
格式：

-- 隐式内连接：SQL92标准
select * from A,B where 条件; 
-- 显式内连接：SQL99标准（inner可以省略）
select * from A inner join B on 条件;

操作：

-- 查询每个部门的所属 
select * from dept3,emp3 where dept3.deptno=emp3.dept_id;  -- 隐式内连接
select * from dept3 inner join emp3 on dept3.deptno=emp3.dept_id； -- 显式内连接

实例：

同一字段or需要用括号放一起或者用in
能加name的原因：同一组中name相同
对分组进行筛选用having

-- 1.查询每个部门的所属员工 
-- 隐式内连接
select * from emp3,dept3 where emp3.dept_id=dept3.deptno;
select * from emp3,dept3 where deptno=dept_id;
-- 显式内连接 , 变成 inner join  where 变成 on  inner 可省略
select * from emp3 inner join dept3 on emp3.dept_id=dept3.deptno;
select * from dept3 inner join emp3 on dept3.deptno=emp3.dept_id;
select * from dept3 join emp3 on deptno=dept_id;
-- 2.查询研发部门所属员工
select * from dept3,emp3 where dept_id=deptno and dept_id='1001';
select * from dept3 inner join emp3 on deptno=dept_id and dept_id='1001';
-- 3.查询研发部门和销售部所属员工
select * from emp3,dept3 where dept_id=deptno and dept_id='1001' or dept_id='1002';
select * from emp3,dept3 where dept_id=deptno and (dept_id='1001' or dept_id='1002'); --  需要用括号放一起
select * from emp3 inner join dept3 on dept_id=deptno and (dept_id='1001' or dept_id='1002');
select * from emp3 inner join dept3 on dept_id=deptno and dept_id in ('1001','1002');
-- 4.查询每个部门的人数并升序排列 
-- 能加name的原因：同一组中name相同
select name,count(eid) from emp3 inner join dept3 on deptno=dept_id group by dept_id order by count(eid) asc;
-- 5.查询人数大于等于3的部门并降序排序
-- 对分组进行筛选用having
select name,count(eid) from emp3 inner join dept3 on deptno=dept_id group by dept_id having count(eid)>=3 order by count(eid) desc;

3.3 外连接（求并集）

在这里插入图片描述

-- 外连接查询 outer 可省略
-- 查询哪些部门有员工,哪些部门没有员工
select * from dept3 left outer join emp3 on deptno=dept_id;
-- 查询哪些员工有部门,哪些员工没部门
select * from dept3 right outer join emp3 on dept_id=deptno;
-- 实现满外连接 full join union
select * from dept3 full join emp3 on dept_id=deptno; -- 有问题
select * from dept3 left join emp3 on dept_id=deptno 
union 
select * from dept3 right join emp3 on deptno=dept_id;
-- union 去重 union all 不去重
select * from dept3 left join emp3 on dept_id=deptno 
union all 
select * from dept3 right join emp3 on deptno=dept_id;

3.4 子查询

-- 子查询
-- 1.查询年龄最大的员工信息
select max(age) from emp3; 
select * from emp3 where age=85;
select * from emp3 where age = (select max(age) from emp3);
-- 2. 查询销售部和研发部的员工信息
select * from emp3 where dept_id in (select deptno from dept3 where name in ('研发部','销售部'));
-- 3.查询研发部20岁以下的员工信息
-- 3.1 关联查询
select * from emp3 inner join dept3 on deptno=dept_id and name='研发部' and age <20;
-- 3.2 子查询
select * from emp3 where dept_id = (select deptno from dept3 where name='研发部' and age <20);
-- 3.3 将两个子查询关联起来
select * from (select * from  emp3  where age < 20) t1  inner join (select * from dept3 where name='研发部' )  t2 on t1.dept_id=t2.deptno;

3.4.1 子查询关键字

ALL 所有 select * from emp3 where age > all (select age from emp3 where dept_id='1003');
ANY 任一 select * from emp3 where age > any (select age from emp3 where dept_id='1003');
SOME some可以理解为any的别名
IN select * from emp3 where dept_id in (select deptno from dept3 where name in('研发部','销售部'));
EXISTS exists()结果为true或者false,false的话外层查询语句不执行

-- 查询公司是否有大于60岁的员工
select * from emp3 where exists (select * from emp3 where age > 60); -- 全表查询
select * from emp3 t where exists (select * from emp3 where t.age > 60); -- 查询年龄大于60岁的

-- 查询没有所属部门的员工信息
select * from emp3 t where not exists (select * from dept3 k where  t.dept_id=k.deptno);
select * from emp3 t where exists (select * from dept3 k where t.dept_id=k.deptno);

在这里插入图片描述

3.5 自关联查询

在这里插入图片描述

-- 自关联查询
create table if not exists t_sanguo(
		eid int primary key,
		ename varchar(20),
		manager_id int,
		foreign key (manager_id) references t_sanguo(eid) -- 添加自关联约束
);
insert into t_sanguo value(1,'刘协',NULL);
insert into t_sanguo value(2,'刘备',1);
insert into t_sanguo value(3,'关羽',2);
insert into t_sanguo value(4,'张飞',2);
insert into t_sanguo value(5,'曹操',1);
insert into t_sanguo value(6,'许褚',5);
insert into t_sanguo value(7,'典韦',5);
insert into t_sanguo value(8,'孙权',1);
insert into t_sanguo value(9,'周瑜',8);
insert into t_sanguo value(10,'鲁肃',8);

select b.ename as 臣子,a.ename as 主公 from t_sanguo a,t_sanguo b where a.eid=b.manager_id;
-- 查询所有人物及其上级
select a.ename,b.ename from t_sanguo a left join t_sanguo b on a.manager_id=b.eid;
-- 查询所有人物 自己 上级 上上级
SELECT
	a.ename,
	b.ename,
	c.ename 
FROM
	t_sanguo a
	LEFT JOIN t_sanguo b ON a.manager_id = b.eid
	LEFT JOIN t_sanguo c ON b.manager_id = c.eid;

3.6 多表查询综合练习

-- 创建数据库
create database if not exists test1;

-- 使用数据库
use test1;

-- 创建部门表
create table dept(
	deptno int primary key, -- 部门编号
	dname varchar(14),  -- 部门名称
	loc varchar(13)  -- 部门地址
);

-- 创建员工表
create table emp(
	empno int primary key,  -- 员工编号
	ename varchar(10),  -- 员工姓名
	job varchar(9),  -- 员工工作
	mgr int,  -- 员工直属领导编号
	hiredate date, -- 入职时间
	sal double, -- 工资
	comm double, -- 奖金
	deptno int,-- 部门编号 外键
  constraint fk_emp1 foreign key (deptno) references dept(deptno)
);

-- 创建工资等级表
create table salgrade(
	grade int, -- 等级
	losal double, -- 最低工资
	hisal double  -- 最高工资
);


insert into dept values(10,'accounting','new york');
insert into dept values(20,'research','dallas');
insert into dept values(30,'sales','chicago');
insert into dept values(40,'operations','boston');

insert into emp values(7369,'smith','clerk',7902,'1980-12-17',800,null,20); 
insert into emp values(7499,'allen','salesman',7698,'1981-02-20',1600,300,30); 
insert into emp values(7521,'ward','salesman',7698,'1981-02-22',1250,500,30);
insert into emp values(7566,'jones','manager',7839,'1981-04-02',2975,null,20); 
insert into emp values(7654,'martin','salesman',7698,'1981-09-28',1250,1400,30);
insert into emp values(7698,'blake','manager',7839,'1981-05-01',2850,null,30); 
insert into emp values(7782,'clark','manager',7839,'1981-06-09',2450,null,10); 
insert into emp values(7788,'scott','analyst',7566,'1987-07-03',3000,null,20); 
insert into emp values(7839,'king','president',null,'1981-11-17',5000,null,10);
insert into emp values(7844,'turner','salesman',7698,'1981-09-08',1500,0,30); 
insert into emp values(7876,'adams','clerk',7788,'1987-07-13',1100,null,20); 
insert into emp values(7900,'james','clerk',7698,'1981-12-03',950,null,30); 
insert into emp values(7902,'ford','analyst',7566,'1981-12-03',3000,null,20);
insert into emp values(7934,'miller','clerk',7782,'1981-01-23',1300,null,10); 

insert into salgrade values(1,700,1200);
insert into salgrade values(2,1201,1400);
insert into salgrade values(3,1401,2000);
insert into salgrade values(4,2001,3000);
insert into salgrade values(5,3001,9999);

distinct去重，后面如果跟多个字段的话则是将这多个字段合起来进行去重

-- 1.返回拥有员工的部门名，部门号
select distinct dname,dept.deptno from dept inner join emp on dept.deptno=emp.deptno;
-- 2.工资水平多于smith的员工信息
select * from emp where sal>(select sal from emp where ename='smith');
-- 3.返回员工和所属经理的姓名 员工的上级领导编号等于领导的员工编号
select a.ename,b.ename from emp a,emp b where a.mgr=b.empno;
-- 4.返回雇员的雇佣日期早于其经理雇佣日期的员工及其经理姓名
select a.ename,a.hiredate,b.ename,b.hiredate from emp a,emp b where a.mgr=b.empno and a.hiredate<b.hiredate;
-- 5.返回员工姓名及其所在的部门名称
select ename,dname from emp inner join dept on emp.deptno=dept.deptno;
-- 6.返回从事clerk工作的员工姓名和所在部门名称
select ename,dname from emp inner join dept on emp.deptno=dept.deptno and job='clerk';
-- 7.返回部门号及其本部门最低工资
select emp.deptno,min(sal) from emp group by deptno;
-- 8.返回销售部(sales)所有员工的姓名
select ename from emp inner join dept on emp.deptno=dept.deptno and dname='sales';
-- 9.返回工资多于平均工资的员工 使用子查询方便
select * from emp where sal > (select avg(sal) from emp);
-- 10.查询和scott从事同样工作的员工
select * from emp where job = (select job from emp where ename='scott') and ename <> 'scott';
-- 11.查询工资高于30部门所有员工工资水平的员工信息
select * from emp where sal > all (select sal from emp where deptno=30);
-- 12.返回员工工作和从事此工作的最低工资
select job,min(sal) from emp group by job;
-- 13.计算出员工的年薪,并且按年薪排序
select ename,sal*12+ifnull(comm,0) from emp order by sal*12+ifnull(comm,0) desc;
-- 14.返回工资处于第四级别的员工的姓名
select ename from emp where sal <= (select hisal from salgrade where grade=4) and sal >= (select losal from salgrade where grade = 4);

select ename from emp where sal between (select losal from salgrade where grade=4) and (select hisal from salgrade where grade=4);
-- 15.返回工资为二等级的职工姓名和部门所在地
select ename,dname from emp inner join dept on emp.deptno=dept.deptno and sal <= (select hisal from salgrade where grade =2) and sal >= (select losal from salgrade where grade = 2);

select ename,dname from emp inner join dept on emp.deptno=dept.deptno and sal between (select losal from salgrade where grade =2)
and (select hisal from salgrade where grade =2);

select ename,dname from emp inner join dept on emp.deptno=dept.deptno inner join salgrade on grade = 2 and emp.sal >= losal and emp.sal <= hisal;

4.MySQL的函数

聚合函数
数学函数
字符串函数
日期函数
控制流函数
窗口函数

4.1 聚合函数

在这里插入图片描述

create table if not exists emp(
	emp_id int primary key auto_increment comment '编号',
	emp_name char(20) not null default '' comment '姓名',
	salary decimal(10,2) not null default 0 comment '工资',
	department char(20) not null default '' comment '部门'
);

insert into emp(emp_name,salary,department) values('张晶晶',5000,'财务部'),('王飞飞',5800,'财务部'),('赵刚',6200,'财务部'),('刘小贝',5700,'人事部'),('王大鹏',6700,'人事部'),('张小斐',5200,'人事部'),('刘云云',7500,'销售部'),('刘云鹏',7200,'销售部'),('刘云鹏',5700,'销售部');