SQL高级（约束、多表查询、事务操作等）

一、约束

1.1 约束概念

• 约束是作用于表中列上的规则，用于限制加入表的数据
• 约束的存在保证了数据库中数据的正确性、有效性和完整性

1.2 约束分类

• 非空约束： 关键字是 NOT NULL
  保证列中所有的数据不能有null值。
• 唯一约束：关键字是 UNIQUE
  保证列中所有数据各不相同。
• 主键约束： 关键字是 PRIMARY KEY
  主键是一行数据的唯一标识，要求非空且唯一。一般我们都会给没张表添加一个主键列用来唯一标识数据。
• 检查约束： 关键字是 CHECK
  保证列中的值满足某一条件。
  例如：我们可以给age列添加一个范围，最低年龄可以设置为1，最大年龄就可以设置为300，这样的数据才更合理些。

  注意：MySQL不支持检查约束。
  这样是不是就没办法保证年龄在指定的范围内了？从数据库层面不能保证，以后可以在java代码中进行限制，一样也可以实现要求。

• 默认约束： 关键字是 DEFAULT
  保存数据时，未指定值则采用默认值。
  例如：我们在给english列添加该约束，指定默认值是0，这样在添加数据时没有指定具体值时就会采用默认给定的0。
• 外键约束： 关键字是 FOREIGN KEY
  外键用来让两个表的数据之间建立链接，保证数据的一致性和完整性。
  外键约束现在可能还不太好理解，后面我们会重点进行讲解。

  补充：属性值自动增加在数据库应用中，经常希望在每次插入新记录时，系统自动生成字段的主键值。可以通过为表主键添加AUTO_INCREMENT关键字来实现。默认的，在MySQL中AUTO_INCREMENT的初始值是1，每新增一条记录，字段值自动加1。一个表只能有一个字段使用AUTO_INCREMENT约束，且该字段必须为主键的一部分。AUTO_INCREMENT约束的字段可以是任何整数类型（TINYINT、SMALLIN、INT、BIGINT等）。
  字段名 数据类型 AUTO_INCREMENT

1.3 创建和删除约束

• 查看约束条件

SELECT * FROM information_schema.`TABLE_CONSTRAINTS` where table_name = '表名';

• 使约束生效和失效

--使约束条件失效：
ALTER TABLE 表名 DISABLE CONSTRANT 约束名;

--使约束条件生效：
ALTER TABLE 表名 ENABLE CONSTRANT 约束名;

• 添加约束,也就是添加一列

-- 用添加表级的方式
Alter Table 表名   
Add [Constraint 约束名] 约束类型 具体的约束类型(字段名)

--修改的方式添加表、列级约束
alter table 表名
modify 列名 新数据类型 新约束;

• 删除约束

--删除主键
alter table 表名 drop primary key;

--删除非空约束
alter table 表名 modify  字段名  null;

--删除外键
alter table 表名 drop foreign key fk_name;

--删除唯一键
alter table 表名 drop index index_name;

--删除默认约束
ALTER TABLE 表名 ALTER 列名 DROP DEFAULT;

1.3 非空约束

• 概念
  非空约束用于保证列中所有数据不能有NULL值。
• 语法

-- 创建表时添加非空约束
CREATE TABLE 表名(
   列名 数据类型 NOT NULL,
   …
);

--建完表后添加非空约束
ALTER TABLE 表名 MODIFY 字段名 数据类型 NOT NULL;
--建完表后删除非空约束
ALTER TABLE 表名 MODIFY 字段名 数据类型;

1.4 唯一约束

• 概念
  唯一性约束（Unique Constraint）要求该列唯一，允许为空，但只能出现一个空值。唯一约束可以确保一列或者几列不出现重复值。
• 语法

-- 创建表时添加唯一约束
CREATE TABLE 表名(
   列名 数据类型 UNIQUE [AUTO_INCREMENT],
   -- AUTO_INCREMENT: 当不指定值时自动增长
   …
); 
CREATE TABLE 表名(
   列名 数据类型,
   …
   [CONSTRAINT] [约束名称] UNIQUE(列名)
);

--建完表后添加唯一约束
ALTER TABLE 表名 MODIFY 字段名 数据类型 UNIQUE;
--删除约束
ALTER TABLE 表名 DROP INDEX 字段名;

如若指定了NOT NULL和UNIQUE，就相当于指定了PRIMARY KEY

1.5 主键约束

• 概念
  主键，又称主码，是表中一列或多列的组合。主键约束（Primary Key Constraint）要求主键列的数据唯一，并且不允许为空。主键分为两种类型：单字段主键和多字段联合主键
  1.单字段主键
  主键由一个字段组成，SQL语句格式分为以下两种情况

  --在定义列的同时指定主键，语法规则如下
   CREATE TABLE 表名(
  列名 数据类型 PRIMARY KEY [AUTO_INCREMENT],
   …
  );

--在定义完所有列之后指定主键
CREATE TABLE 表名(
   列名 数据类型,
   [CONSTRAINT] [约束名称] PRIMARY KEY(列名)
); 

2. 多字段联合主键

--主键由多个字段联合组成
 CREATE TABLE test_tb1
(
  列名1 数据类型1,
  列名2 数据类型2,
  列名3 数据类型3,
  PRIMARY KEY(列名1,列名2)
);

-- 建完表后添加主键约束
ALTER TABLE 表名 ADD PRIMARY KEY(字段名);
--删除约束
ALTER TABLE 表名 DROP PRIMARY KEY;

1.6 默认约束

• 概念
  默认约束（Default Constraint）指定某列的默认值。如男性同学较多，性别就可以默认为‘男’。如果插入一条新的记录时没有为这个字段赋值，那么系统会自动为这个字段赋值为‘男’。
• 语法

-- 创建表时添加默认约束
CREATE TABLE 表名(
   列名 数据类型 DEFAULT 默认值,
   …
);

-- 建完表后添加默认约束
ALTER TABLE 表名 ALTER 列名 SET DEFAULT 默认值;
--删除约束
ALTER TABLE 表名 ALTER 列名 DROP DEFAULT;

1.7 外键约束

1.7.1 概念

外键用来在两个表的数据之间建立连接，可以是一列或者多列。一个表可以有一个或多个外键。外键对应的是参照完整性，一个表的外键可以为空值，若不为空值，则每一个外键值必须等于另一个表中主键的某个值。

外键：首先它是表中的一个字段，虽可以不是本表的主键，但要对应另外一个表的主键。外键的主要作用是保证数据引用的完整性，定义外键后，不允许删除在另一个表中具有关联关系的行。外键的作用是保持数据的一致性、完整性。
主表（父表）：对于两个具有关联关系的表而言，相关联字段中主键所在的那个表即是主表。
从表（子表）：对于两个具有关联关系的表而言，相关联字段中外键所在的那个表即是从表。

1.7.2 语法

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

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

1.7.3 举例

-- 部门表
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)	
);

二、多表查询

多表查询前先根据目标厘清如下思路：

1. 确定表（从哪些表中查？）；from 表1，表2
2. 确定连接条件：where/on
3. 确定筛选出哪些列：select 列

2.1 多表查询类型

• 连接查询
  

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

• 子查询

• 下方举例都基于这个表

# 创建部门表
	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.2 内连接查询

2.2.1 语法

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

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

内连接相当于查询 A B 交集数据

2.2.2 举例

• 隐式内连接

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

• 显式内连接

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

inner join 意思是连接就是把join in后面的表加入 前面的emp表

两者区别

• 连接方式不同：隐式内连接使用"FROM"和"WHERE"关键字，显式内连接使用"JOIN"和"ON"关键字。
• 连接结果不同：内连接只返回匹配的数据，左外连接返回左表所有数据和右表匹配的数据，右外连接返回右表所有数据和左表匹配的数据。
• 执行效率不同：内连接和显式连接效率相对较高，左右外连接和子查询效率较低。
• 使用场景不同：内连接用于获取两个表中匹配的数据，左右外连接用于获取两个表中的所有数据和匹配的数据，子查询用于需要嵌套查询的情况。

2.3 外连接查询

2.3.1 语法

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

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

左外连接：相当于查询A表所有数据和交集部分数据
右外连接：相当于查询B表所有数据和交集部分数据
注意：左外连接左表显示全部，右表只显示满足on后条件数据
右外连接右表显示全部，左表只显示满足on后条件数据

2.3.2 举例

--（左外连接）left join on / left outer join on
select * from a_table  a left join b_table b on a.id = b.id;

说明：
left join 是left outer join的简写，它的全称是左外连接，是外连接中的一种。
左(外)连接，左表(a_table)的记录将会全部表示出来，而右表(b_table)只会显示符合搜索条件的记录。右表记录不足的地方均为NULL。

--（右外连接）right join on / right outer join on
select * from a_table  a left join b_table b on a.id = b.id;

说明：
right join是right outer join的简写，它的全称是右外连接，是外连接中的一种。
与左(外)连接相反，右(外)连接，左表(a_table)只会显示符合搜索条件的记录，而右表(b_table)的记录将会全部表示出来。左表记录不足的地方均为NULL。

2.3 子查询

2.3.1 概念

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

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

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

2.3.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 = '猪八戒');  --这就是查询语句中嵌套查询语句。

2.3.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 = '市场部');

--查询入职日期是 '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;

三、事务

3.1 概述

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

3.2 语法

-- 开启事务
START TRANSACTION;
或者  
BEGIN;
-- 提交事务
commit;

-- 回滚事务
rollback;

3.3 举例

3.3.1 理解

• 如下图有一张表
  
  张三和李四账户中各有100块钱，现李四需要转换500块钱给张三，具体的转账操作为

• 第一步：查询李四账户余额
• 第二步：从李四账户金额 -500
• 第三步：给张三账户金额 +500
  现在假设在转账过程中第二步完成后出现了异常第三步没有执行，就会造成李四账户金额少了500，而张三金额并没有多500；这样的系统是有问题的。如果解决呢？使用事务可以解决上述问题
  
  从上图可以看到在转账前开启事务，如果出现了异常回滚事务，三步正常执行就提交事务，这样就可以完美解决问题。

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

• 不加事务演示问题

-- 转账操作
-- 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;

上面sql中的执行成功进选择执行提交事务，而出现问题则执行回滚事务的语句。以后我们肯定不可能这样操作，而是在java中进行操作，在java中可以抓取异常，没出现异常提交事务，出现异常回滚事务。

3.4 事务的四大特征

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

==说明：==
mysql中事务是自动提交的。
也就是说我们不添加事务执行sql语句，语句执行完毕会自动的提交事务。
可以通过下面语句查询默认提交方式：
SELECT @@autocommit;

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