MySQL高级
1、约束
非空约束
唯一约束
主键约束
默认约束
外键约束
2、数据库设计
2.1 数据设计简介
表关系
2.2 表关系之一对多
2.3 表关系之多对多
2.4 表关系之一对一
2.5 数据库设计案例
3、多表查询
内连接
外连接
子查询
案例
4、事务
4.1 事务简介
4.2 事务操作
4.3 事务四大特征
1、约束
1. 约束概念
-
- 约束是作用于表中列上的规则，用于限制加入表的数据
- 约束的存在保证了数据库中的正确性、有效性和完整性
2. 约束的分类
| 约束名称 | 描述 | 关键字 |
| 非空约束 | 保证列中所有数据不能有null值 | not null |
| 唯一约束 | 保证列中所有数据各不相同 | unique |
| 主键约束 | 主键是一行数据的唯一标识符，要求非空且唯一 | primary key |
| 检查约束 | 抱枕列中的值满足某一条件 | Check |
| 默认约束 | 保存数据时，未指定值则采用默认值 | Default |
| 外键约束 | 外键用来让两个表的数据之间建立连接，保证数据的一致性和完整性 | foreign key |
Tips：MySQL不支持检查约束
非空约束
1. 概念
-
- 非空约束用于保证列中所有数据不能有null值
2. 语法
（1）添加约束
-- 创建表时添加非空约束
create table 表名(
列名 数据类型 not null,
...
);
-- 建表完成后添加非空约束
alter table 表名 modify 字段名 数据类型 not null;（2）删除约束
alter table 表名 modify 列名 数据类型;唯一约束
1. 概念
-
- 唯一约束用于保证列中所有数据各不相同
2. 语法
（1）添加约束
-- 创建表时添加唯一约束
create table 表名(
列名 数据类型 unique,
...
);
create table 表名(
列名 数据类型,
...
[constraint] [约束名称] unique(列名)
);
-- 建完表后添加依约束
alter table 表名 modify 字段名 数据类型 unique;（2）删除约束
alter table 表名 modify 列名 数据类型;主键约束
1. 概念
-
- 主键是一行数据的唯一标识，要求非空且唯一
- 一张表只能有一个主键
2. 语法
（1）添加约束
-- 建表时添加主键约束
create table 表名(
列名 数据类型 primary key,
...
);
-- 建完表后添加主键约束
alter table 表名 add primary key(字段名);（2）删除约束
alter table 表名 modify 列名 数据类型;默认约束
1. 概念
保存数据时，为指定值则采用默认值
2. 语法
（1）添加约束
-- 创建表时添加默认约束
create table 表名(
列名 数据类型 default 默认值,
...
);
-- 建完表后添加默认约束
alter table 表名 modify 列名 数据类型 default 默认值;（2）删除约束
alter table 表名 modify 列名 数据类型3. 案例：根据需求，为表添加合适的约束
-- 员工表 create table emp( id int, -- 员工id，主键且自增长 ename varchar(50), -- 员工姓名，非空并且唯一 joindate DATE, -- 入职日期，非空 salary double(7,2), -- 工资，非空 bonus double(7,2) -- 奖金，如果没有奖金默认为0 );
create table emp(
id int primary key auto_increment, -- 员工id，主键且自增长
ename varchar(50) not null unique, -- 员工姓名，非空并且唯一
joindate DATE not null, -- 入职日期，非空
salary double(7,2) not null, -- 工资，非空
bonus double(7,2) default 0-- 奖金，如果没有奖金默认为0
);外键约束
1. 概念
-
- 外键用来让两个表的数据之间建立联系，保证数据的一致性和完整性
2. 语法
（1）添加约束
-- 创建表时添加外键约束
create table 表名(
列名 数据类型,
...
[constraint] 外键名称 foreign key(外键列名) references 主表(主表列名)
);
-- 建完表后添加外键约束
alter table 表名 add constraint 外键名称 foreign key(外键列名) references 主表(主表列名);（2）删除约束
alter table 表名 drop foreign key 外键名称;# 创建部门表
CREATE TABLE dept(
did INT PRIMARY KEY AUTO_INCREMENT,
dname VARCHAR(20)
);
drop table if exists emp;
drop table if exists dept;
# 创建员工表
CREATE TABLE emp (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10),
gender CHAR(1), -- 性别
salary DOUBLE, -- 工资
join_date DATE, -- 入职日期
dep_id INT,
constraint fk_emp_dept 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);
-- 删除约束
alter table emp drop foreign key fk_emp_dept;2、数据库设计
2.1 数据设计简介
1. 软件的研发步骤
2. 数据库设计概念
- 数据库设计就是根据业务系统的具体需求，结合我们所选用的DBMS，为这个业务系统构造出最优的数据存储模
型。 - 建立数据库中的表结构以及表与表之间的关联关系的过程。
- 有哪些表？表里有哪些字段？表和表之间有什么关系？
3.数据库设计的步骤
- 需求分析（数据是什么? 数据具有哪些属性? 数据与属性的特点是什么）
- 逻辑分析（通过ER图对数据库进行逻辑建模，不需要考虑我们所选用的数据库管理系统）
如下图就是ER(Entity/Relation)图：
- 物理设计（根据数据库自身的特点把逻辑设计转换为物理设计）
- 维护设计（1.对新的需求进行建表；2.表优化
表关系
- 一对一：
- 一对多（多对一）：
- 如部门和员工
- 一个部门对应多个员工，一个员工对应一个部门
- 多对多：
- 如商品和订单
- 一个商品对应多个订单，一个订单包含多个商品
2.2 表关系之一对多
- 一对多（多对一）：
- 如：部门表和员工表
- 一个部门对应多个员工，一个员工对应一个部门
- 实现方式：在多的一方建立外键，指向一的一方的主键
建表语句如下：
-- 删除表
DROP TABLE IF EXISTS tb_emp;
DROP TABLE IF EXISTS tb_dept;
-- 部门表
CREATE TABLE tb_dept(
id int primary key auto_increment,
dep_name varchar(20),
addr varchar(20)
);
-- 员工表
CREATE TABLE tb_emp(
id int primary key auto_increment,
name varchar(20),
age int,
dep_id int,
-- 添加外键 dep_id,关联 dept 表的id主键
CONSTRAINT fk_emp_dept FOREIGN KEY(dep_id) REFERENCES tb_dept(id)
);2.3 表关系之多对多
- 多对多
- 如：订单和商品
- 一个商品对应多个订单，一个订单包含多个商品
- 实现方式：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键
建表语句如下：
-- 删除表
DROP TABLE IF EXISTS tb_order_goods;
DROP TABLE IF EXISTS tb_order;
DROP TABLE IF EXISTS tb_goods;
-- 订单表
CREATE TABLE tb_order(
id int primary key auto_increment,
payment double(10,2),
payment_type TINYINT,
status TINYINT
);
-- 商品表
CREATE TABLE tb_goods(
id int primary key auto_increment,
title varchar(100),
price double(10,2)
);
-- 订单商品中间表
CREATE TABLE tb_order_goods(
id int primary key auto_increment,
order_id int,
goods_id int,
count int
);
-- 建完表后，添加外键
alter table tb_order_goods add constraint foreign key fk_order_id(order_id) references tb_order(id);
alter table tb_order_goods add constraint foreign key fk_goods_id(goods_id) references tb_goods(id);
2.4 表关系之一对一
- 一对一
- 如：用户和用户详情
- 一对一关系多用于表拆分，将一个实体中经常使用的字段放一张表，不经常使用的字段放在另一张表，用于提升查询性能
- 实现方式
- 在任意一方加入外键，关联另一方主键，并且设置外键为唯一（unique）
- 案例
我们以用户表举例：
而在真正使用过程中发现 id、photo、nickname、age、gender 字段比较常用，此时就可以将这张表查分成两张表。
建表语句如下：
create table tb_user_desc (
id int primary key auto_increment,
city varchar(20),
edu varchar(10),
income int,
status char(2),
des varchar(100)
);
create table tb_user (
id int primary key auto_increment,
photo varchar(100),
nickname varchar(50),
age int,
gender char(1),
desc_id int unique,
-- 添加外键
CONSTRAINT fk_user_desc FOREIGN KEY(desc_id) REFERENCES tb_user_desc(id)
);
2.5 数据库设计案例
根据下图设计表及表和表之间的关系：
经过分析，我们分为专辑表、曲目表、短评表、用户表4张表。
3、多表查询
- 笛卡尔积：取A、B集合所有组合情况
- 多表查询：从多张表查询数据
- 连接查询
- 内连接：相当于查询A B交集数据
- 外连接：
- 左外连接：相当于查询A表所有数据和交集部分数据
- 右外连接：相当于查询B表所有数据和交集部分数据
- 子查询
- 连接查询
内连接
1. 内连接查询语法
-- 隐式内连接
select 字段列表 from 表1,表2 where 条件;
-- 显示内连接
select 字段列表 from 表1 inner join 表2 on 条件;内连接相当于查询AB交集数据
# 创建部门表
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,
constraint fk_emp_dept 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);
-- 多表查询
-- 隐式内连接
select * from emp,dept where emp.dep_id = dept.did;
-- 查询emp的name,gender,dept表的dname
select emp.name,emp.gender,dept.dname from emp,dept where emp.dep_id = dept.did;
-- 显示内连接
select * from emp inner join dept on emp.dep_id = dept.did;外连接
1. 外连接查询语法
-- 左外连接
select 字段列表 from 表1 left [outer] join 表2 on 条件;
-- 右外连接
select 字段列表 from 表1 right [outer] join 表2 on 条件;- 左外连接：相当于查询A表所有数据和交集部分数据
- 右外连接：相当于查询B表所有数据和交集部分数据
-- 左外连接
-- 查询emp表所有数据和对应的部门信息
select * from emp left outer join dept on emp.dep_id = dept.did;
-- 右外连接
-- 查询dept表的所有数据和对应的员工数据
select * from emp right outer join dept on emp.dep_id = dept.did;
子查询
1. 子查询概念：
-
- 查询中嵌套查询，称嵌套查询为子查询
2. 子查询根据检查结果不同，作用不同：
-
- 单行单列：作为条件，使用 =、!=、>、<等进行条件判断
select 字段列表 from 表 where 字段名 = (子查询);
-
- 单行多列：作为条件，使用in等关键字进行条件判断
select 字段列表 from 表 where 字段名 in (子查询);
-
- 多行多列：作为虚拟表
select 字段列表 from (子查询) where 条件;
-- 查询工资高于猪八戒的员工信息
select * from emp;
-- 1.查询猪八戒的工资
select salary from emp where name = '猪八戒';
-- 2. 查询工资高于猪八戒的员工信息
select * from emp where salary > (select salary from emp where name = '猪八戒');
-- 查询'财务部'和'市场部'所有的员工信息
select * from emp where dep_id in (select did from dept where dname in ('财务部','市场部'));
-- 查询入职日期是'2011-11-11'之后的员工信息和部门信息
select * from emp inner join dept on emp.dep_id = dept.did where emp.join_date > '2011-11-11';案例：
DROP TABLE IF EXISTS emp;
DROP TABLE IF EXISTS dept;
DROP TABLE IF EXISTS job;
DROP TABLE IF EXISTS salarygrade;
-- 部门表
CREATE TABLE dept (
id INT PRIMARY KEY PRIMARY KEY, -- 部门id
dname VARCHAR(50), -- 部门名称
loc VARCHAR(50) -- 部门所在地
);
-- 职务表，职务名称，职务描述
CREATE TABLE job (
id 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 (id),
CONSTRAINT emp_deptid_ref_dept_id_fk FOREIGN KEY (dept_id) REFERENCES dept (id)
);
-- 工资等级表
CREATE TABLE salarygrade (
grade INT PRIMARY KEY, -- 级别
losalary INT, -- 最低工资
hisalary INT -- 最高工资
);
-- 添加4个部门
INSERT INTO dept(id,dname,loc) VALUES
(10,'教研部','北京'),
(20,'学工部','上海'),
(30,'销售部','广州'),
(40,'财务部','深圳');
-- 添加4个职务
INSERT INTO job (id, 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);
-- 1.查询所有员工信息。查询员工编号，员工姓名，工资，职务名称，职务描述
select e.id,e.ename,e.salary,j.jname,j.description from emp e inner join job j where e.job_id = j.id;
-- 2.查询员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置
select ej.id,ej.ename,ej.salary,ej.description,d.dname,d.loc from (select e.id,e.ename,e.salary,e.dept_id,j.description from emp e inner join job j on e.job_id = j.id) ej
inner join dept d where ej.dept_id = d.id;
-- 3.查询员工姓名，工资，工资等级
select e.ename,e.salary,s.grade from emp e inner join salarygrade s on e.salary >s.losalary and e.salary < s.hisalary;
-- 4.查询员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级
-- 5.查询出部门编号、部门名称、部门位置、部门人数
4、事务
4.1 事务简介
- 数据库的事务（Transaction）是一种机制、一个操作序列，包含了一组数据操作命令。
- 事务把所有的命令作为一个整体一起向系统提交或撤销操作请求，记者一组数据库命令要么同时成功，要么同时失败
- 事务是一个不可分割的工作逻辑单元
-- 开启事务
start transaction;
或者 begin;
-- 提交事务
commit;
-- 回滚事务
rollback;4.2 事务操作
drop table if exists account;
-- 创建账户表
create table account(
id int primary key auto_increment,
name varchar(10),
money double(10,2)
);
-- 添加数据
insert into account(name,money) values('张三',1000),('李四',1000);
-- 转账操作
begin;
-- 1. 查询李四的余额
-- 2. 李四金额 -500
update account set money = money -500 where name = '李四';
出错了
-- 3. 张三金额 +500
update account set money = money + 500 where name = '张三';
-- 提交事务
commit;
-- 回滚事务
rollback;
-- 1.查询事务的默认提交方式
select @@autocommit;
-- 2.修改事务的提交方式：手动提交
set @@autocommit = 0;4.3 事务四大特征
- 原子性（Atomicity）：事务是不可能分隔的最小单元，要么同时成功，要是同时失败
- 一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态
- 隔离性（Isolation）：多个事务之间，操作的可见性
- 持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。
MySQL事务默认自动提交
-- 查看事务的默认自动提价
select @@autocommit;
-- 1 自动提交 0 手动提价
-- 修改事务提交方式
set @@autocommit = 0;
浙公网安备 33010602011771号