Mysql约束、设计、多表查询、事务

1.约束

1.1概念

• 约束是作用于表中列上的规则，用于限制加入表的数据

  例如：不能为null值，不能不填。

• 约束的存在保证了数据库中数据的正确性、有效性和完整性

  例如：不能瞎填数据，岁数为负。

1.2分类

很简单字面意思。

1.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，主键且自增长
    name VARCHAR(50) not NULL UNIQUE, -- 员工姓名，非空且唯一
    joindate DATE not NULL,  -- 入职日期，非空
    salary DOUBLE(7,2) not NULL,  -- 工资，非空
    bonus DOUBLE(7,2) DEFAULT 0-- 奖金，如果没有将近默认为0
);

• auto_increment

  自动增长，当列是数字类型，并且唯一约束。

1.4外键约束

外键用来让两个表的数据直接建立链接，保证数据的一致性和完整性。（就是让两个表有关系）

• 添加外键约束

-- 创建表时添加外键约束
CREATE TABLE 表名(
   列名 数据类型,
   …
   [CONSTRAINT] [外键名称] FOREIGN KEY(外键列名) REFERENCES 主表(主表列名) 
); (常用这个 )

-- 建完表后添加外键约束
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);

1.4.1案例：

1.根据上述语法创建员工表和部门表，并添加上外键约束：

-- 删除表
DROP TABLE IF EXISTS emp;
DROP TABLE IF EXISTS dept;

-- 部门表
CREATE TABLE dept(
	id int primary key auto_increment,
	dep_name varchar(20),
	addr varchar(20)
);
-- 员工表 
CREATE TABLE 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 dept(id)	

);

2.添加数据

-- 添加 2 个部门
insert into dept(dep_name,addr) values
('研发部','广州'),('销售部', '深圳');

-- 添加员工,dep_id 表示员工所在的部门
INSERT INTO emp (NAME, age, dep_id) VALUES 
('张三', 20, 1),
('李四', 20, 1),
('王五', 20, 1),
('赵六', 20, 2),
('孙七', 22, 2),
('周八', 18, 2);

此时删除 研发部 这条数据，会发现无法删除。

删除外键

alter table emp drop FOREIGN key fk_emp_dept;

重新添加外键

alter table emp add CONSTRAINT fk_emp_dept FOREIGN key(dep_id) REFERENCES dept(id);

navicat可以查看它们的关联。

2.数据库设计

2.1数据库设计简介

2.1.1软件的研发步骤

2.1.2数据库设计概念

数据库设计就是根据业务系统的具体需求，结合我们所选用的DBMS，为这个业务系统构造出最优的数据存储模型。建立数据库中的==表结构==以及==表与表之间的关联关系==的过程。

简单来说就是有哪些表？表里有哪些字段？表和表之间有什么关系？

2.1.1数据库设计的步骤

2.2表关系（三种）

• 一对一：在任意一方建立外键，关联对方主键，并设置外键唯一。
• 一对多（多对一）:在多的一方建立外键，关联一的一方主键。
• 多对多:1.建立第三张中间表–2.中间表至少包含2个外键，分别关联双方主键。

2.2.1一对一【用的少】

如：人 和 身份证。一个人对应一个身份证。

作用：一对一关系多用于表拆分，将一个实体中经常使用的字段放一张表，不经常使用的字段放另一张表，用于提升查询性能****。

• 实现方式：

==在任意一方加入外键，关联另一方主键，并且设置外键为唯一(UNIQUE)==

• 案例：

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.2.2一对多（多对一）【常见】

如：部门 和 员工。一个部门对应多个员工，一个员工对应一个部门。

• 实现方式：

直接添加一个外键就可以了。

经过分析发现，员工表属于多的一方，而部门表属于一的一方，此时我们会在员工表中添加一列（dep_id），指向于部门表的主键（id）：

会在多的一方添加外键，用来指定主键。

• 案例：

-- 删除表
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.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 fk_order_id FOREIGN key(order_id) REFERENCES tb_order(id);
alter table tb_order_goods add CONSTRAINT fk_goods_id FOREIGN key(goods_id) REFERENCES tb_goods(id);

3.多表查询：

案例：

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);	

执行下面的多表查询语句

select * from emp , dept;  -- 从emp和dept表中查询所有的字段数据

从表中看有24条记录，那么明显是有问题的。取了笛卡尔积：有A、B两个集合，取A、B所有的组合情况。（所以数据大量重复）

select * from emp , dept where emp.dep_id = dept.did;

可以通过这个语法来去除重复。

3.1连接查询

• 内连接查询 ：相当于查询AB交集数据

• 外连接查询

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

• 子查询

3.2内连接查询

3.2.1隐式内连接

• 概念：

-- 隐式内连接
SELECT 字段列表 FROM 表1,表2… WHERE 条件;

-- 显示内连接
SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 条件;

• 案例

1.查询表的信息(所有的交集)

SELECT
	*
FROM
	emp,
	dept
WHERE
	emp.dep_id = dept.did;

2.查询表的部门信息(部分部门的信息交集)

SELECT
	emp.NAME,
	emp.gender,
	dept.dname
FROM
	emp,
	dept
WHERE
	emp.dep_id = dept.did;

3.上面语句中使用表名指定字段所属有点麻烦，sql也支持给表指别名，上述语句可以改进为

SELECT
	t1. NAME,
	t1.gender,
	t2.dname
FROM
	emp t1,
	dept t2
WHERE
	t1.dep_id = t2.did;

3.2.2显式内连接

select * from emp inner join dept on emp.dep_id = dept.did;
-- 上面语句中的inner可以省略，可以书写为如下语句
select * from emp join dept on emp.dep_id = dept.did;

区别：当只是两张表时，显示和隐式都可以使用，而当需要多张表进行内链接时，就必须使用显示内连接的格式了，如果使用隐式则会报错。。。

3.3外连接查询

3.3.1语法

-- 左外连接
SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件;

-- 右外连接
SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件;

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

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

3.3.2左外连接案例（常用）

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

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

3.3.3右外连接案例

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

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

3.3.4通用连接

一般我们用左外连接比较多，其实是因为用左外连接都可以实现，只需要将表位置进行互换：

--左外连接
select * from emp left join dept on emp.dep_id = dept.did;

--右外连接
select * from dept left join emp on emp.dep_id = dept.did;

--两者只是把表进行了互换。也可以实现。

3.4子查询

概念：子查询类似于复合查询，意思是本来可能需要两个表查询下，现在只需要整合用一个就可以了。

3.4.1分类

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

• 子查询语句结果是单行单列，子查询语句作为条件值，使用 = != > < 等进行条件判断
• 子查询语句结果是多行单列，子查询语句作为条件值，使用 in 等关键字进行条件判断
• 子查询语句结果是多行多列，子查询语句作为虚拟表

3.4.2子查询单行单列

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

案例：

来实现这个需求，我们就可以通过二步实现，第一步：先查询出来 猪八戒的工资

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 = '猪八戒');

3.4.3子查询多行单列

案例

• 查询 ‘财务部’ 和 ‘市场部’ 所有的员工信息

  -- 查询 '财务部' 或者 '市场部' 所有的员工的部门did
  select did from dept where dname = '财务部' or dname = '市场部';
  
  select * from emp where dep_id in (select did from dept where dname = '财务部' or dname = '市场部');

3.4.5子查询多行多列

• 查询入职日期是 ‘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' ) t1, dept where t1.dep_id = dept.did;

意思是虽然可以多个表查询，但是每个表名称太长了。我们可以用个简称比如说“t1”简化一下。这样就方便多了。不至于显得太乱。

4.事务

4.1概述

数据库的事务（Transaction）是一种机制、一个操作序列，包含了==一组数据库操作命令==。

事务把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令

• 要么同时成功，要么同时失败

事务是一个不可分割的工作逻辑单元。

4.2语法

• 开启事务

  START TRANSACTION;
  或者  
  BEGIN;

• 提交事务

  commit;

• 回滚事务

  rollback;

4.3 代码验证

• 环境准备

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);

• 不加事务演示问题

-- 转账操作
-- 1. 查询李四账户金额是否大于500

-- 2. 李四账户 -500
UPDATE account set money = money - 500 where name = '李四';

出现异常了...  -- 此处不是注释，在整体执行时会出问题，后面的sql则不执行
-- 3. 张三账户 +500
UPDATE account set money = money + 500 where name = '张三';

整体执行结果肯定会出问题，我们查询账户表中数据，发现李四账户少了500。

• 添加事务sql如下：

-- 开启事务
BEGIN;
-- 转账操作
-- 1. 查询李四账户金额是否大于500

-- 2. 李四账户 -500
UPDATE account set money = money - 500 where name = '李四';

出现异常了...  -- 此处不是注释，在整体执行时会出问题，后面的sql则不执行
-- 3. 张三账户 +500
UPDATE account set money = money + 500 where name = '张三';

-- 提交事务
COMMIT;

-- 回滚事务
ROLLBACK;

4.4 事务的四大特征

• 原子性（Atomicity）: 事务是不可分割的最小操作单位，要么同时成功，要么同时失败

• 一致性（Consistency） :事务完成时，必须使所有的数据都保持一致状态

• 隔离性（Isolation） :多个事务之间，操作的可见性

• 持久性（Durability） :事务一旦提交或回滚，它对数据库中的数据的改变就是永久的

==说明：==

mysql中事务是自动提交的。

也就是说我们不添加事务执行sql语句，语句执行完毕会自动的提交事务。

可以通过下面语句查询默认提交方式：

SELECT @@autocommit;

查询到的结果是1 则表示自动提交，结果是0表示手动提交。当然也可以通过下面语句修改提交方式

set @@autocommit = 0;