9,885
社区成员
MySQL性能强劲,是目前使用最广泛的数据库之一,以 MySQL为学习原型也方便之后掌握其他数据库,下面就给大家全面讲解下MySQL8.0的新特性,从零基础到高阶一站式学习,结合实际案例让大家有所收获!
▼ MySQL8.0入门-高阶学习笔记:(汇总)
目录
实际开发中,一个项目通常需要很多张表才能完成。例如:一个商城项目就需要分类表(category)、商品表(products)、订单表(orders)等多张表。且这些表的数据之间存在一定的关系,接下来我们将在单表的基础上,一起学习多表方面的知识。
MySQL多表之间的关系可以概括为:一对一、一对多/多对一关系,多对多。
MySQL 外键约束(FOREIGN KEY)是表的一个特殊字段,经常与主键约束一起使用。对于两个具有关联关系的表而言,相关联字段中主键所在的表就是主表(父表),外键所在的表就是从表(子表)。
外键用来建立主表与从表的关联关系,为两个表的数据建立连接,约束两个表中数据的一致性和完整性。比如,一个水果摊,只有苹果、桃子、李子、西瓜等 4 种水果,那么,你来到水果摊要买水果就只能选择苹果、桃子、李子和西瓜,其它的水果都是不能购买的。
特点:
定义一个外键时,需要遵守下列规则:
在 create table 语句中,通过 foreign key 关键字来指定外键,具体的语法格式如下:
[constraint <外键名>] foreign key 字段名 [,字段名2,…] references <主表名> 主键列1 [,主键列2,…]
实现:
create database mydb3;
use mydb3;
-- 创建部门表
create table if not exists dept(
deptno varchar(20) primary key , -- 部门号
name varchar(20) -- 部门名字
);
create table if not exists emp(
eid varchar(20) primary key , -- 员工编号
ename varchar(20), -- 员工名字
age int, -- 员工年龄
dept_id varchar(20), -- 员工所属部门
constraint emp_fk foreign key (dept_id) references dept (deptno) –- 外键约束
);
外键约束也可以在修改表时添加,但是添加外键约束的前提是:从表中外键列中的数据必须与主表中主键列中的数据一致或者是没有数据。
alter table <数据表名> add constraint <外键名> foreign key(<列名>) references
<主表名> (<列名>);
1、数据插入
-- 1、添加主表数据
-- 注意必须先给主表添加数据
insert into dept values('1001','研发部');
insert into dept values('1002','销售部');
insert into dept values('1003','财务部');
insert into dept values('1004','人事部’);
-- 2、添加从表数据
-- 注意给从表添加数据时,外键列的值不能随便写,必须依赖主表的主键列
insert into emp values('1','乔峰',20, '1001');
insert into emp values('2','段誉',21, '1001');
insert into emp values('3','虚竹',23, '1001');
insert into emp values('4','阿紫',18, '1002');
insert into emp values('5','扫地僧',35, '1002');
insert into emp values('6','李秋水',33, '1003');
insert into emp values('7','鸠摩智',50, '1003');
insert into emp values('8','天山童姥',60, '1005'); -- 不可以
2、删除数据
-- 3、删除数据
/*
注意:
1:主表的数据被从表依赖时,不能删除,否则可以删除
2: 从表的数据可以随便删除
*/
delete from dept where deptno = '1001'; -- 不可以删除
delete from dept where deptno = '1004'; -- 可以删除
delete from emp where eid = '7'; -- 可以删除
当一个表中不需要外键约束时,就需要从表中将其删除。外键一旦删除,就会解除主表和从表间的关联关系
格式:
alter table <表名> drop foreign key <外键约束名>;
实现:
alter table emp2 drop foreign key dept_id_fk;
在多对多关系中,A表的一行对应B的多行,B表的一行对应A表的多行,我们要新增加一个中间表,来建立多对多关系。
操作
-- 学生表和课程表(多对多)
-- 1 创建学生表student(左侧主表)
create table if not exists student(
sid int primary key auto_increment,
name varchar(20),
age int,
gender varchar(20)
);
-- 2 创建课程表course(右侧主表)
create table course(
cid int primary key auto_increment,
cidname varchar(20)
);
-- 3创建中间表student_course/score(从表)
create table score(
sid int,
cid int,
score double
);
-- 4建立外键约束(2次)
alter table score add foreign key(sid) references student(sid);
alter table score add foreign key(cid) references course(cid);
-- 5给学生表添加数据
insert into student values(1,'小龙女',18,'女'),(2,'阿紫',19,'女'),(3,'周芷若',20,'男');
-- 6给课程表添加数据
insert into course values(1,'语文'),(2,'数学'),(3,'英语');
-- 7给中间表添加数据
insert into score values(1,1),(1,2),(2,1),(2,3),(3,2),(3,3);
介绍:
多表查询就是同时查询两个或两个以上的表,因为有的时候用户在查看数据的时候,需要显示的数据来自多张表.
多表查询有以下分类:
语法:select * from A,B;
隐式内连接(SQL92标准):select * from A,B where 条件;
显示内连接(SQL99标准):select * from A inner join B on 条件;
左外连接:left outer join
select * from A left outer join B on 条件;
右外连接:right outer join
select * from A right outer join B on 条件;
满外连接: full outer join
select * from A full outer join B on 条件;
select的嵌套
表自关联:
将一张表当成多张表来用
接下来准备多表查询需要数据,注意,外键约束对多表查询并无影响。
use mydb3;
-- 创建部门表
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) -- 员工所属部门
);
-- 给dept3表添加数据
insert into dept3 values('1001','研发部');
insert into dept3 values('1002','销售部');
insert into dept3 values('1003','财务部');
insert into dept3 values('1004','人事部');
-- 给emp表添加数据
insert into emp3 values('1','乔峰',20, '1001');
insert into emp3 values('2','段誉',21, '1001');
insert into emp3 values('3','虚竹',23, '1001');
insert into emp3 values('4','阿紫',18, '1001');
insert into emp3 values('5','扫地僧',85, '1002');
insert into emp3 values('6','李秋水',33, '1002');
insert into emp3 values('7','鸠摩智',50, '1002');
insert into emp3 values('8','天山童姥',60, '1003');
insert into emp3 values('9','慕容博',58, '1003');
insert into emp3 values('10','丁春秋',71, '1005');
格式:
select * from 表1,表2,表3….;
实现:
-- 交叉连接查询
select * from dept3,emp3;
结果
内连接查询求多张表的交集。
格式:
隐式内连接(SQL92标准):select * from A,B where 条件;
显示内连接(SQL99标准):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;
-- 查询研发部和销售部的所属员工
select * from dept3,emp3 where dept3.deptno = emp3.dept_id and name in( '研发部','销售部');
select * from dept3 join emp3 on dept3.deptno = emp3.dept_id and name in( '研发部','销售部');
-- 查询每个部门的员工数,并升序排序
select deptno,count(1) as total_cnt from dept3,emp3 where dept3.deptno = emp3.dept_id group by deptno order by total_cnt;
select deptno,count(1) as total_cnt from dept3 join emp3 on dept3.deptno = emp3.dept_id group by deptno order by total_cnt;
-- 查询人数大于等于3的部门,并按照人数降序排序
select deptno,count(1) as total_cnt from dept3,emp3 where dept3.deptno = emp3.dept_id group by deptno having total_cnt >= 3 order by total_cnt desc;
select deptno,count(1) as total_cnt from dept3 join emp3 on dept3.deptno = emp3.dept_id group by deptno having total_cnt >= 3 order by total_cnt desc;
外连接分为左外连接(left outer join)、右外连接(right outer join),满外连接(full outer join)。
注意:oracle里面有full join,可是在mysql对full join支持的不好。我们可以使用union来达到目的。
格式:
左外连接:left outer join
select * from A left outer join B on 条件;
右外连接:right outer join
select * from A right outer join B on 条件;
满外连接: full outer join
select * from A full outer join B on 条件;
操作:
-- 外连接查询
-- 查询哪些部门有员工,哪些部门没有员工
use mydb3;
select * from dept3 left outer join emp3 on dept3.deptno = emp3.dept_id;
-- 查询哪些员工有对应的部门,哪些没有
select * from dept3 right outer join emp3 on dept3.deptno = emp3.dept_id;
-- 使用union关键字实现左外连接和右外连接的并集
select * from dept3 left outer join emp3 on dept3.deptno = emp3.dept_id
union
select * from dept3 right outer join emp3 on dept3.deptno = emp3.dept_id;
介绍:
子查询就是指的在一个完整的查询语句之中,嵌套若干个不同功能的小查询,从而一起完成复杂查询的一种编写形式,通俗一点就是包含select嵌套的查询。
特点:
子查询可以返回的数据类型一共分为四种:
操作
-- 查询年龄最大的员工信息,显示信息包含员工号、员工名字,员工年龄
select eid,ename,age from emp3 where age = (select max(age) from emp3);
-- 查询年研发部和销售部的员工信息,包含员工号、员工名字
select eid,ename,t.name from emp3 where dept_id in (select deptno,name from dept3 where name = '研发部' or name = '销售部') ;
-- 查询研发部20岁以下的员工信息,包括员工号、员工名字,部门名字
select eid,age,ename,name from (select * from dept where name = '研发部 ')t1,(select * from emp3 where age <20)t2
在子查询中,有一些常用的逻辑关键字,这些关键字可以给我们提供更丰富的查询功能,主要关键字如下:
格式:
select …from …where c > all(查询语句)
--等价于:
select ...from ... where c > result1 and c > result2 and c > result3
特点:
ALL: 与子查询返回的所有值比较为true 则返回true
ALL可以与=、>、>=、<、<=、<>结合是来使用,分别表示等于、大于、大于等于、小于、小于等于、不等于其中的其中的所有数据。
ALL表示指定列中的值必须要大于子查询集的每一个值,即必须要大于子查询集的最大值;如果是小于号即小于子查询集的最小值。同理可以推出其它的比较运算符的情况。
操作:
-- 查询年龄大于‘1003’部门所有年龄的员工信息
select * from emp3 where age > all(select age from emp3 where dept_id = '1003’);
-- 查询不属于任何一个部门的员工信息
select * from emp3 where dept_id != all(select deptno from dept3);
格式:
select …from …where c > any(查询语句)
--等价于:
select ...from ... where c > result1 or c > result2 or c > result3
特点:
操作:
-- 查询年龄大于‘1003’部门任意一个员工年龄的员工信息
select * from emp3 where age > all(select age from emp3 where dept_id = '1003’);
格式:
select …from …where c in(查询语句)
--等价于:
select ...from ... where c = result1 or c = result2 or c = result3
特点:
IN关键字,用于判断某个记录的值,是否在指定的集合中
在IN关键字前边加上not可以将条件反过来
操作:
-- 查询研发部和销售部的员工信息,包含员工号、员工名字
select eid,ename,t.name from emp3 where dept_id in (select deptno from dept3 where name = '研发部' or name = '销售部') ;
格式:
select …from …where exists(查询语句)
特点:
操作
-- 查询公司是否有大于60岁的员工,有则输出
select * from emp3 a where exists(select * from emp3 b where a.age > 60);
-- 查询有所属部门的员工信息
select * from emp3 a where exists(select * from dept3 b where a.dept_id = b.deptno);
概念:
MySQL有时在信息查询时需要进行对表自身进行关联查询,即一张表自己和自己关联,一张表当成多张表来用。注意自关联时表必须给表起别名。
格式:
select 字段列表 from 表1 a , 表1 b where 条件;
或者
select 字段列表 from 表1 a [left] join 表1 b on 条件;
操作:
-- 创建表,并建立自关联约束
create table t_sanguo(
eid int primary key ,
ename varchar(20),
manager_id int,
foreign key (manager_id) references t_sanguo (eid) -- 添加自关联约束
);
操作:
-- 添加数据
insert into t_sanguo values(1,'刘协',NULL);
insert into t_sanguo values(2,'刘备',1);
insert into t_sanguo values(3,'关羽',2);
insert into t_sanguo values(4,'张飞',2);
insert into t_sanguo values(5,'曹操',1);
insert into t_sanguo values(6,'许褚',5);
insert into t_sanguo values(7,'典韦',5);
insert into t_sanguo values(8,'孙权',1);
insert into t_sanguo values(9,'周瑜',8);
insert into t_sanguo values(10,'鲁肃',8);
-- 进行关联查询
-- 1.查询每个三国人物及他的上级信息,如: 关羽 刘备
select * from t_sanguo a, t_sanguo b where a.manager_id = b.eid;