MySQL进阶

g-yli

已于 2022-08-17 22:01:34 修改

阅读量164

点赞数

分类专栏： JavaWeb 文章标签： mysql 数据库

于 2022-08-17 21:38:57 首次发布

本文链接：https://blog.csdn.net/qq_53563793/article/details/126206796

版权

JavaWeb 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

本文接MySQL基础，介绍数据库的约束、数据库的设计、多表查询以及数据库的事务。包括对应案例的分析及结果。

一. 约束

1. 概念

数据库是作用于表中列上的规则，用于限制加入表的数据。比如给某一列加上非空约束，则该数据表对应列的数据不能为空值（null）。
约束的存在保证了数据库中数据的正确性、有效性和完整性。

2. 分类

◆ 非空约束（NOT NULL）

概念：保证列中所有的数据不能为null
语法：

建表时添加非空约束
create table 表名(
列名数据类型 not null,
…
);
建完表后添加非空约束
alter table 表名 modify 字段名数据类型 not null;
删除非空约束
alter table 表名 modify 字段名数据类型;

◆ 唯一约束（UNIQUE）

概念：保证列中的所有数据各不相同
语法：

创建表时添加唯一约束
create table 表名(
列名数据类型 unique,
…
);
建完表后添加唯一约束
alter table 表名 modify 字段名数据类型 unique;
删除唯一约束
alter table 表名 drop index 字段名;

◆ 主键约束（PRIMARY KEY）

概念：主键是一行数据的唯一标识，要求非空且唯一，一张表只能有一个主键。主键用来唯一标识数据，比如学生信息表的学号id就可以作为主键，来标识每一位学生。
语法：

创建表时添加主键约束
create table 表名(
列名数据类型 primary key,
…
);
创建完表之后添加主键约束
alter table 表名 add primary key(字段名);
删除主键约束
alter table 表名 drop primary key;

◆ 检查约束（CHECK）

概念：保证列中的值满足某一条件。
注意：MySQL不支持检查约束。但Oracle及Microsoft推出的SQL Server等数据库管理系统支持检查约束。

◆ 默认约束（DEFAULT）

概念：指保存数据时，未指定值则采用默认约束。比如某一列的数据类型为int，在该列未指定非空约束时，默认值是0，添加数据时没有指定值则该位置会使用默认值0。
语法：

创建表时添加默认约束
create table 表名(
列名数据类型 dedault 默认值,
…
);
创建完表之后添加默认约束
alter table 表名 alter 列名 set default 默认值;
删除默认约束
alter table 表名 alter 列名 drop default;

◆ 外键约束（FOREIGN KEY）

概念：外键用来让两个表的数据之间建立链接，保证数据的一致性和完整性。多用于多表联合查询。
语法：

创建表时添加外键约束
create table 表名(
列名数据类型,
…
[constraint] [外键名称] foreign key(外键列名) references 主表(主表列名)
);
创建完表之后添加外键约束
alter table 表名 add constraint 外键名称 foreign key (外键字段名称) references 主表名称(主表列名称);
删除外键约束
alter table 表名 drop foreign key 外键名称;
外键约束添加在从表中

二. 数据库设计

1. 数据库设计概念

数据库设计就是根据业务系统的具体需求，结合所选用的数据库管理系统，为业务系统构造出最优的数据存储模型。
数据库设计即建立数据库中的表结构以及表与表之间的关联关系的过程。
有哪些表？表中需要哪些字段？表与表之间有什么关系？

2. 数据库设计步骤

需求分析： 即业务系统的数据是什么？数据具有哪些属性？数据与属性的特点是什么？
逻辑分析： 通过ER图对数据库进行逻辑建模，即综合整个业务系统，使用ER实体图来整体展现数据之间的关联以及表之间的关联
物理设计： 根据数据库自身特点吧逻辑设计转换为物理设计，即使用数据库管理系统来实现的过程。
维护设计： 对表进行优化等

3. 表关系

数据表之间的关系可分为三种：一对一、一对多、多对多。

一对多
如部门和员工，一个部门对应多个员工，一个员工对应一个部门
实现方式：在多的一方建立外键，指向一的一方的主键
多对多
如商品和订单，一个商品对应多个订单，一个订单包含多个商品
实现方式：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键
一对一
如用户和用户信息
一对一关系多用于表拆分，将一个实体中经常使用的字段放一张表，不经常使用的字段放另一张表，用于提升查询性能。
实现方式：在任意一方加入外键，关联另一方主键，并且设置外键为唯一（UNIQUE）

三. 多表查询

1. 数据准备

DROP TABLE IF EXISTS emp;
DROP TABLE IF EXISTS dept;


# 创建部门表
CREATE TABLE dept(
       did INT PRIMARY KEY AUTO_INCREMENT,
       dname VARCHAR(20)
   );

# 创建员工表
CREATE TABLE emp (
       id INT PRIMARY KEY AUTO_INCREMENT,
       NAME VARCHAR(10),
       gender CHAR(1), -- 性别
       salary DOUBLE, -- 工资
       join_date DATE, -- 入职日期
       dep_id INT,
       FOREIGN KEY (dep_id) REFERENCES dept(did) -- 外键，关联部门表(部门表的主键)
   );
-- 添加部门数据
INSERT INTO dept (dNAME) VALUES ('研发部'),('市场部'),('财务部'),('销售部');
-- 添加员工数据
INSERT INTO emp(NAME,gender,salary,join_date,dep_id) VALUES
('孙悟空','男',7200,'2013-02-24',1),
('猪八戒','男',3600,'2010-12-02',2),
('唐僧','男',9000,'2008-08-08',2),
('白骨精','女',5000,'2015-10-07',3),
('蜘蛛精','女',4500,'2011-03-14',1),
('小白龙','男',2500,'2011-02-14',null);

2. 概念引入

查询emp表和dept表中的所有字段数据。

select * from emp,dept;

从查询结果可以看出，同时查询两张表不加任何限制会出现很多无用的数据，因此就需要通过条件来消除无效的数据。

select * from emp,dept where dep_id = did;

查询结果如下，可以看到所有的无效数据都已被消除。

上面的查询就属于多表查询。

多表查询分为 连接查询 和 子查询
连接查询又分为 内连接查询 和 外连接查询
外连接查询又分为 左外连接查询(相当于查询A表所有数据和B表交集部分数据)和 右外连接查询(相当于查询B表所有数据和交际部分数据)。

3. 内连接查询

3.1 语法

隐式内连接

select 字段列表 from 表1,表2... where 条件;

显式内连接

select 字段列表 from 表1 [inner] join 表2 on 条件;

3.2 举例

3.2.1 隐式内连接

1.查询emp和dept两张表的全部数据

-- 隐式内连接
SELECT
	*
FROM
	emp,
	dept
WHERE
	emp.dep_id = dept.did;

2.查询emp表的name、gender，dept表的dname并起别名

-- 隐式内连接
select
	emp.name as "姓名",
	emp.gender as "性别",
	dept.dname as "所属部门"
from
	emp,
	dept
where
	emp.dep_id = dept.did;

在这里插入图片描述
3.查询部分字段名时使用表名.字段名的方式可能有点麻烦，sql也支持给表指定别名，来简化书写
如下sql查询出来的结果与上相同。（as关键字可以省略）

select
	t1.name as "姓名",
	t1.gender as "性别",
	t2.dname as "所属部门"
from
	emp as t1,
	dept as t2
where
	t1.dep_id = t2.did;

3.2.2 显式内连接

查询两张表的所有数据

-- 以下两种方式查询结果一样，只是字段名排序位置不一样，哪张表在前哪张表在后无硬性要求。
-- 其中inner关键字可以省略。
select 
	* 
from 
	emp inner join dept
on 
	emp.dep_id = dept.did;
--------------------------
select 
	* 
from 
	dept inner join emp
on 
	emp.dep_id = dept.did;

4. 外连接查询

4.1 语法

-- 左外连接
select 字段列表 from 表1 left [outer] join 表2 on 条件;
-- 右外连接
select 字段列表 from 表1 right [outer] join 表2 on 条件;

左外连接：相当于查询A表所有数据和交集部分数据
右外连接：相当于查询B表所有数据和交集部分数据

4.2 案例

1.查询emp表所有数据和对应的部门信息（左外连接）

select 
	*
from 
	emp left outer join dept 
on
	emp.dep_id = dept.did;

-- 或(右外连接，字段顺序不一样)
select 
	*
from 
	dept right outer join emp
on
	emp.dep_id = dept.did;

2.查询dept表所有数据和对应的员工信息（右外连接）

select 
	*
from 
	emp right outer join dept 
on
	emp.dep_id = dept.did;	

-- 同(左外连接，字段顺序不一样)
select 
	*
from 
	dept left outer join emp 
on
	emp.dep_id = dept.did;

5. 子查询

5.1 概念

查询中嵌套查询，称嵌套查询为子查询

5.2 举例

查询工资高于猪八戒的员工信息。

-- 实现该需求，可以分两步实现，第一步：先查询出来猪八戒的工资，（3600）
select salary from emp where name = '猪八戒';
-- 再查询工资高于猪八戒的员工信息
select * from emp where salary > 3600;
-- 第二步中的3600可以通过第一步的sql查询出来，所以将3600用第一步的sql语句进行替换
select 
	* 
from 
	emp 
where 
	salary > (select salary from emp where name = '猪八戒');

5.3 子查询的作用范围

子查询根据查询结果不同，作用不同。

5.3.1 单行单列

子查询语句结果是单行单列，子查询语句作为条件值，使用 = != > < <>等进行条件判断。

例：上述查询比猪八戒工资高的员工即是。

5.3.2 多行单列

子查询语句结果是多行单列，子查询语句作为条件值，使用in等关键字进行条件判断。

例：查询“财务部”和“市场部”所有的员工信息

-- 分析：先从部门表中查出 财务部 和 市场部 的did
-- 如下所示，查询出的结果是两行单列
select did from dept where dname in('财务部','市场部');
-- 将改子查询语句作为条件值，使用in进行条件判断
select 
	*
from
	emp
where dep_id in (select did from dept where dname in('财务部','市场部'));

在这里插入图片描述

5.3.3 多行多列

子查询语句结果是多行多列，子查询语句作为虚拟表。

例：查询入职日期是’2011-11-11‘之后的员工信息和部门信息。

-- 查询入职日期在’2011-11-11’之后的员工信息‘。
select * from emp where join_date > '2011-11-11';
-- 将上面语句的结果作为虚拟表和dept表进行内连接查询。
select
	*
from
	(select * from emp where join_date > '2011-11-11') 
	as t1 
	inner join dept
where
	t1. dep_id = dept.did;

在这里插入图片描述

6. 综合案例

6.1 数据准备

DROP TABLE IF EXISTS emp;
DROP TABLE IF EXISTS dept;
DROP TABLE IF EXISTS job;
DROP TABLE IF EXISTS salarygrade;

-- 部门表
CREATE TABLE dept (
  did INT PRIMARY KEY PRIMARY KEY, -- 部门id
  dname VARCHAR(50), -- 部门名称
  loc VARCHAR(50) -- 部门所在地
);

-- 职务表，职务名称，职务描述
CREATE TABLE job (
  jid INT PRIMARY KEY,
  jname VARCHAR(20),
  description VARCHAR(50)
);

-- 员工表
CREATE TABLE emp (
  id INT PRIMARY KEY, -- 员工id
  ename VARCHAR(50), -- 员工姓名
  job_id INT, -- 职务id
  mgr INT , -- 上级领导
  joindate DATE, -- 入职日期
  salary DECIMAL(7,2), -- 工资
  bonus DECIMAL(7,2), -- 奖金
  dept_id INT, -- 所在部门编号
  CONSTRAINT emp_jobid_ref_job_id_fk FOREIGN KEY (job_id) REFERENCES job (jid), -- 建立员工表与部门表之间的外键
  CONSTRAINT emp_deptid_ref_dept_id_fk FOREIGN KEY (dept_id) REFERENCES dept (did) -- 建立员工表与职务表之间的外键
);
-- 工资等级表
CREATE TABLE salarygrade (
  grade INT PRIMARY KEY,   -- 级别
  losalary INT,  -- 最低工资
  hisalary INT -- 最高工资
);
				
-- 添加4个部门
INSERT INTO dept(did,dname,loc) VALUES 
(10,'教研部','北京'),
(20,'学工部','上海'),
(30,'销售部','广州'),
(40,'财务部','深圳');

-- 添加4个职务
INSERT INTO job (jid, jname, description) VALUES
(1, '董事长', '管理整个公司，接单'),
(2, '经理', '管理部门员工'),
(3, '销售员', '向客人推销产品'),
(4, '文员', '使用办公软件');

-- 添加员工
INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES 
(1001,'孙悟空',4,1004,'2000-12-17','8000.00',NULL,20),
(1002,'卢俊义',3,1006,'2001-02-20','16000.00','3000.00',30),
(1003,'林冲',3,1006,'2001-02-22','12500.00','5000.00',30),
(1004,'唐僧',2,1009,'2001-04-02','29750.00',NULL,20),
(1005,'李逵',4,1006,'2001-09-28','12500.00','14000.00',30),
(1006,'宋江',2,1009,'2001-05-01','28500.00',NULL,30),
(1007,'刘备',2,1009,'2001-09-01','24500.00',NULL,10),
(1008,'猪八戒',4,1004,'2007-04-19','30000.00',NULL,20),
(1009,'罗贯中',1,NULL,'2001-11-17','50000.00',NULL,10),
(1010,'吴用',3,1006,'2001-09-08','15000.00','0.00',30),
(1011,'沙僧',4,1004,'2007-05-23','11000.00',NULL,20),
(1012,'李逵',4,1006,'2001-12-03','9500.00',NULL,30),
(1013,'小白龙',4,1004,'2001-12-03','30000.00',NULL,20),
(1014,'关羽',4,1007,'2002-01-23','13000.00',NULL,10);

-- 添加5个工资等级
INSERT INTO salarygrade(grade,losalary,hisalary) VALUES 
(1,7000,12000),
(2,12010,14000),
(3,14010,20000),
(4,20010,30000),
(5,30010,99990);

6.2 需求及结果分析

6.2.1 需求1

查询所有员工信息。查询员工编号，员工姓名，工资，职务名称，职务描述

# 分析
-- 1.员工编号，员工姓名，工资在emp表中，职务名称，职务描述在job表中，因此需要使用多表查询
-- 2.经分析可知，需要拿到emp表和job表的交集部分数据，因此使用内连接查询
-- 方法1-隐式内连接
select
	emp.id '员工编号', -- 起别名
	emp.ename '员工姓名',
	emp.salary '工资',
	job.jname '职务名称',
	job.description '职务描述'
from
	emp, job
where
	emp.job_id = job.jid
order by 
	emp.id; -- 将查询到的数据通过id进行升序排列

-- 显式内连接
select
	emp.id '员工编号', -- 起别名
	emp.ename '员工姓名',
	emp.salary '工资',
	job.jname '职务名称',
	job.description '职务描述'
from
	emp 
inner join job
	on emp.job_id = job.jid
order by 
	emp.id; -- 将查询到的数据通过id进行升序排列
	
-- (左)外连接
select
	emp.id '员工编号', -- 起别名
	emp.ename '员工姓名',
	emp.salary '工资',
	job.jname '职务名称',
	job.description '职务描述'
from
	emp 
left outer join job -- 左外连接
	on emp.job_id = job.jid
order by 
	emp.id; -- 将查询到的数据通过id进行升序排列

6.2.2 需求2

查询员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置

# 分析
-- 1.员工编号，员工姓名，工资 在emp表，职务名称，职务描述在job表，部门名称，部门位置在dept表
-- 2.使用多表查询 内连接查询外连接查询都可
-- 隐式内连接
select
	emp.id '员工编号', -- 起别名
	emp.ename '员工姓名',
	emp.salary '工资',
	job.jname '职务名称',
	job.description '职务描述',
	dept.dname '部门名称',
	dept.loc '部门位置'
from
	emp, job, dept
where
	emp.job_id = job.jid and emp.dept_id = dept.did
order by 
	emp.id; -- 将查询到的数据通过id进行升序排列

-- 显式内连接参照需求1

-- 外连接查询
select
	emp.id '员工编号', -- 起别名
	emp.ename '员工姓名',
	emp.salary '工资',
	job.jname '职务名称',
	job.description '职务描述',
	dept.dname '部门名称',
	dept.loc '部门位置'
from 
	emp
	left outer join job 
		on emp.job_id = job.jid
	left outer join dept 
		on emp.dept_id = dept.did
order by 
	emp.id; -- 将查询到的数据通过id进行升序排列

6.2.3 需求3

查询员工姓名，工资，工资等级

# 分析(同上)
-- 隐式内连接
select
	emp.ename '员工姓名',
	emp.salary '工资',
	t1.grade '工资等级'
from
	emp, salarygrade as t1
where
	emp.salary >= t1.losalary
	and emp.salary <= t1.hisalary
order by
	t1.grade;
	
-- 显式内连接
select
	emp.ename '员工姓名',
	emp.salary '工资',
	t1.grade '工资等级'
from
	emp 
	inner join salarygrade as t1
		on emp.salary >= t1.losalary and emp.salary <= t1.hisalary
order by
	t1.grade;

在这里插入图片描述

6.2.4 需求4

查询员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级

-- 显式内连接
select
	emp.ename '员工姓名',
	emp.salary '工资',
	job.jname '职务名称',
	job.description '职务描述',
	dept.dname '部门名称',
	dept.loc '部门位置',
	t1.grade '工资等级'
from
	emp
	inner join job
		on emp.job_id = job.jid
	inner join dept
		on emp.dept_id = dept.did
	inner join salarygrade as t1
		on emp.salary >= t1.losalary and emp.salary <= t1.hisalary
order by 
	t1.grade; -- 将查询到的数据通过id进行升序排列

6.2.5 需求5

查询出部门编号、部门名称、部门位置、部门人数

# 分析
-- 1.将员工表按部门编号分组，查询部门编号和部门人数
-- 2.将分组后的表作为虚拟表并入后续查询
select
	dept_id,
	count(*) as count
from
	emp
group by
	dept_id;
-- 方式1.隐式内连接，该方式查不出部门编号为40的全部信息，如下图所示，因此并不完善(同显示内连接，因此需使用外连接才能将部门表全部信息显示出来)
select
	dept.did '部门编号',
	dept.dname '部门名称',
	dept.loc '部门位置',
	t1.count '部门人数'
from
	dept,
	(
		select
			dept_id,
			count(*) as count 
		from
			emp
		group by
			dept_id
	) as t1
where
	dept.did = t1.dept_id;

在这里插入图片描述

-- 方法2 外连接，该sql有一个缺点，部门编号为40的部门人数为0，但该行该列显示的数据为null，因此需要给count加上默认值，当其为null时，设其初值为0，即下面第三种方式。
select
	dept.did '部门编号',
	dept.dname '部门名称',
	dept.loc '部门位置',
	t1.count '部门人数'
from
	dept
	left outer join
	(
		select
			dept_id,
			count(*) as count 
		from
			emp
		group by
			dept_id
	) as t1
on
	dept.did = t1.dept_id;

在这里插入图片描述

-- 方式3 完善的sql
select
	dept.did '部门编号',
	dept.dname '部门名称',
	dept.loc '部门位置',
	ifnull(t1.count, 0) '部门人数'
from
	dept
	left outer join
	(
		select
			dept_id,
			count(*) as count 
		from
			emp
		group by
			dept_id
	) as t1
on
	dept.did = t1.dept_id;

在这里插入图片描述

四. 数据库事务

1. 概述

数据库的事务（Transaction）是一种机制、一个操作序列，包含了一组数据库操作命令。
事务把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令要么同时成功，要么同时失败。
事务是一个不可分割的工作逻辑单元。

2. 语法

2.1 开启事务

START TRANSACTION;
或者  
BEGIN;

2.2 提交事务

commit;

2.3 回滚事务

rollback;

3. 事务的四大特征

原子性(Atomicity)：事务是不可分割的最小操作单位，要么同时成功，要么同时失败。
一致性(Consistency)：事务完成时，必须使所有的数据都保持一致状态。
隔离性(Isolation)：多个事务之间，操作的可见性。
持久性(Durability)：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。