一 数据约束

数据类型决定字段需要存储什么样的数据，而约束告诉我们这些数据具体需要满足的规则

1 默认值 (DEFAULT)

设置默认值后，插入数据时，如果没有给该字段插入数据，该字段的值使用设置的默认值，设置非NULL的默认值后，插入数据时，该值也可以设为NULL

CREATE TABLE person (
  did  int ,
  dname  varchar(10)  DEFAULT ‘hello’ 
);

2 非空 （是否为NULL）

设置非空属性：

student表的name字段设置成不为NULL，插入数据时，如果不给name字段赋值，报错

mysql> insert into student(sid) values(14);
1364 - Field 'name' doesn't have a default value

插入值时，如果name为NULL，报错

mysql> insert into student(sid,name) values(14,NULL);
1048 - Column 'name' cannot be null

3 唯一 (Unique)

CREATE TABLE person(
  id int UNIQUE,
  name VARCHAR(10)
)

字段设置为unique，会自动建立唯一性索引

比如学号设为为唯一：

-- 如果插入两条stu_num值相同数据：
mysql> insert into student(sid,stu_no) values(3,'1234');
mysql> insert into student(sid,stu_no) values(13,'1234');
1062 - Duplicate entry '1234' for key 'index_id'

注意：唯一性约束的字段，可以设置为NULL，而且多条记录中，该字段都可以为NULL，且不会报错

4 主键 (PRIMARY KEY)

主键用来标识一个唯一记录，大部分情况下，每个表都会设置主键；

主键可以有一个，也可以有多个，如果有多个主键，称为联合主键；

主键相当于 非空+唯一

CREATE TABLE person (
  id INT PRIMARY KEY,
  name VARCHAR(20)
)

勾选主键项，会自动勾选非空项

如果插入数据时主键值相同

INSERT INTO student(sid,name,age) values(22,'zhaosi',12)
[Err] 1062 - Duplicate entry '22' for key 'PRIMARY'

很多时候，可以在表中设置某个字段为自增约束(AUTO_INCREMENT)，并且用该字段作为主键

5 自增(AUTO INCREMENT)

设置自增操作：

插入数据时，不用给自增的字段赋值，mysql中记录了表的自增字段的最大值，如果删除数据，再次插入数据时，自增字段的值根据mysql记录的最大值进行处理

INSERT INTO student(name,age) values('zhaosi',12)

当然，也可以在插入语句中给自增的字段直接赋值

INSERT INTO student(sid,name,age) values(20,'zhaosi',12)

注：自增属性不能单独设置，需要配合主键或唯一索引使用

二 数据库设计

1 范式（了解）

第一范式：数据表中的字段要保持原子性，即每个字段都是不可再分的最小数据单元

不符合第一范式的例子：

编号	名字	联系方式
1	zhangsan	123456/1312345678/aa@aa.com

可以改为

编号	名字	手机号	邮箱	QQ
1	zhangsan	1312345678	aa@aa.com	123456

第二范式：满足第一范式的基础上，表中其他字段要和主键字段有关联，主要针对联合主键情况

（一个表表达一个事物或一件事）

不符号第二范式的例子：

快递单号	商品编号	商品名称	价格	数量	收件人	地址
10002000	s001	变形金刚	200	1	hehe	XXX
10002000	s002	Mysql从入门到删库跑路	5	1	hehe	XXX
10003000	s001	变相金刚	200	3	haha	houhouhoui

本例中快递单号和商品编号可以作为联合主键，表示唯一记录

商品价格和商品有关系，和快递没有直接关系

地址和快递有关系，和商品没有直接关系

可以改为：

快递单号	收件人	地址
10002000	hehe	XXX
10003000	haha	houhoui

商品编号	商品名称	价格
s001	变形金刚	200
s002	Mysql从入门到删库跑路	5

快递单号	商品编号	数量
10002000	s001	1
10002000	s002	1
10003000	s001	3

**第三范式：**满足第二范式的基础上，表中其他字段都和主键有直接关联

不符合第三范式的例子：

学生学号	学生姓名	所属系名	系主任
J001	张三	计算机系	马老师
J002	李四	计算机系	马老师
S001	王五	数学系	丘老师

学生属于哪个系，和学生有关联，但是系主任和学生没有直接关联，可以改为：

学生学号	学生姓名	系编号
J001	张三	001

编号	系名	系主任
001	计算机系	马老师
002	数学系	丘老师

2 表之间关系

1）一对一

比如 一个人对应一个身份证

2）一对多/多对一

一个部门下有多个员工，多个员工属于一个部门（一个员工只会属于一个部门）

3）多对多

一个学生可以选多门课，一个课程可以多个学生选

可以使用学生表、课程表、选课表， 体现课程和学生的关系

注意：在数据表设计规范化的同时，要综合考虑数据库的性能

4） 外键 (FOREIGN KEY) 理解

思考：有部门表和员工表，员工表中有个字段，用来表示部门表的id。插入数据时，在员工表中的部门id可以随意插入数据，并且部门表中不存在的数据也可以插入。这样做是否合适？如何解决？

解决方案：通过外键约束

建立外键sql语句

ALTER TABLE `employee` ADD FOREIGN KEY (`deptId`) REFERENCES `department` (`did`);

3 多表查询（重点）

比如，有员工表和部门表，需要根据员工名查询员工所属的部门名称。

常规思路，可以两步走：

-- 根据员工名查询部门id
SELECT deptId from employee WHERE ename='张三'

-- 根据部门id找到部门名称
SELECT dname from department WHERE did=1

(1)内连接

inner join，通过内连接可以在多个表中找出符合条件的共有记录

基本语法： select .... from 表1 inner join 表2 on 表1.字段名=表2.字段名

inner可以省略不写

select d.dname, e.ename
from department d 
inner join employee e
on d.did = e.deptId

-- on 后可以有多个条件
select d.dname, e.ename
from department d inner join employee e
on d.did = e.deptId and e.ename='张三'

使用了多表连接查询，后面依旧可以使用其他的关键字，比如where

select d.dname, e.ename
from department d inner join employee e
on d.did = e.deptId
where e.ename='张三'

注意：有关联关系的表才可以使用连接查询。本例中，员工表的deptId字段关联部门表的did字段，使用关联查询时，需要在on后给出两个表的关联条件，即 d.did=e.deptId

(2)左外连接

left join

满足条件的左表中的数据都会查询出来，如果右表中没匹配的数据，显示null

select d.*,e.ename
from department d 
left join employee e
on d.did=e.deptId

select d.*,e.ename
from employee e 
left join department d
on d.did=e.deptId

(3)右外连接

right join

右表中的数据都会查询出来，如果左表中没有匹配的数据，显示null

select d.*,e.ename
from employee e right join department d
on d.did=e.deptId

(4)交叉连接 了解

cross join

通过交叉连接，可以得到所有可能的组合，查询到的记录数 = 表1的行数 X 表2的行数

通过交叉连接返回被连接的两个表的笛卡尔积，即返回结果的行数等于两个表行数的乘积。

select * from department cross join employee;

-- 交叉连接还可以使用下面的方式表示
select * from department,employee;

(5)自连接

进行连接的表是同一张表，使用自连接查询的表，一般是数据中存在上下级关系的表，比如表示菜单关系的表

表中数据：

-- 把同一张表逻辑上看作两张表，一个作为父菜单表，一个作为子菜单表
select m1.menu_name, m2.menu_name from menu p 
inner join menu c 
on p.id=c.pid 

select m1.menu_name, m2.menu_name from menu p 
inner join menu c 
on p.id=c.pid 
where p.menu_name='文件'

4 子查询

子查询也称“内部查询”或者“嵌套查询”，是指将一个 SELECT 查询（子查询）的结果作为另一个 SQL 语句（主查询）的数据来源或者判断条件。 子查询可以嵌入 SELECT、INSERT、UPDATE 和 DELETE 语句中，也可以和 =、<、>、in等运算符一起使用。

子查询常用在 WHERE 子句和 FROM 子句后边：

当用于 WHERE 子句时，根据不同的运算符，子查询可以返回单行单列、多行单列、单行多列数据。子查询就是要返回能够作为 WHERE 子句查询条件的值。

当用于 FROM 子句时，一般返回多行多列数据，相当于返回一张临时表，这样才符合 FROM 后面是表的规则。这种做法能够实现多表联合查询。

(1) insert 中使用子查询

insert into new_student (sid, sname) select sid,sname from student;

(2) from 后使用子查询

select t.dname, t.ename from (
  select d.*,e.ename
  from department d 
  left join employee e
  on d.did=e.deptId
) t

(3) where 后使用子查询

结合=、>、< 等运算符

select * from employee WHERE salary > (select salary from employee where ename='wang')

in

in表示是否在某个范围内，作用上等效于多个条件使用or连接

-- 查询10楼的部门有哪些员工
select * from employee where dept_id in (select did from department where floor=10)
-- 不属于10楼部门的员工
select * from employee where dept_id not in (select did from department where floor=10)

any

any是表示子查询结果中任意一个

-- 查询比篮球事业部其中任意一个员工年龄都大的员工记录（最小的大）
--  > Any 比最小的大
select * from employee where age > Any (
	select e.age from department d
	inner join employee e
	on e.dept_id=d.did
	where d.dept_name='篮球事业部'
)

all

all 表示子查询结果中的所有

--   > All 比最大的大
select * from employee where age > All (
	select e.age from department d
	inner join employee e
	on e.dept_id=d.did
	where d.dept_name='篮球事业部'
)

将运算符与any、all结合使用时，关系如下：

	=	<>或!=	<	<=	>	>=
ANY	IN		<MAX	<=MAX	>MIN	>=MIN
ALL		NOT IN	<MIN	<=MIN	>MAX	>=MAX

5 union和union all

UNION相关操作符用于合并两个或多个 SELECT 语句的结果集。

UNION 操作符选取不同的值。如果允许重复的值，使用 UNION ALL

语法：

SELECT column_name(s) FROM table_name1
UNION [ALL]
SELECT column_name(s) FROM table_name2

store_sales表：实体店铺销售额

store_name	sales
storeA	1500
storeB	250
storeC	300

online_sales 表：线上销售额

store_name	sales
scoreA	250
scoreC	535

三 事务(重点)

事务指逻辑上的一组操作，组成这组操作的各个单元，要不全部成功，要不全部不成功。

比如A向B转账，数据库中A的钱要减少，B的钱要增加，如果A的钱减少了，但是B的钱没有增加，那后果不堪设想

针对这种情况，该怎么进行处理呢？-- 数据库事务

基本解释：

数据库事务可以包含一个或多个数据库操作,但这些操作构成一个逻辑上的整体。

构成逻辑整体的这些数据库操作,要么全部执行成功,要么全部不执行。

构成事务的所有操作,要么全都对数据库产生影响,要么全都不产生影响,即不管事务是否执行成功,数据库总能保持一致性状态。

1 事务相关命令

针对mysql数据库，默认情况下，没有开启事务，进行增删改操作后，会直接对数据表中的数据进行修改。

为了演示，需要使用命令开启事务。mysql数据库中与事务相关的命令：

开启事务：start transaction

提交事务：commit

回滚事务：rollback

正常提交事务演示：

-- 开启事务
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

-- 执行一条记录的更新操作
mysql> update money set money=money-1000 where name='zhangsan';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

-- 查询
mysql> select * from money;
+----+-------+----------+
| id | money | name     |
+----+-------+----------+
|  1 |  7000 | zhangsan |
|  2 |  9000 | lisi     |
+----+-------+----------+
2 rows in set (0.00 sec)

-- 针对另外的用户执行一条更新操作
mysql> update money set money=money+1000 where name='lisi';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

-- 查询
mysql> select * from money;
+----+-------+----------+
| id | money | name     |
+----+-------+----------+
|  1 |  7000 | zhangsan |
|  2 | 10000 | lisi     |
+----+-------+----------+
2 rows in set (0.00 sec)

-- 提交，所有的sql语句执行正确，提交数据。提交数据后，其他的客户端才可以看到数据的变化
mysql> commit;
Query OK, 0 rows affected (0.01 sec)

异常回滚演示

-- 开启事务
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

-- 更新一条记录
mysql> update money set money=money-1000 where name='zhangsan';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

-- 人为制造一个sql语句异常
mysql> update1 money set money=money+1000 where name='lisi';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'updat
e1 money set money=money+1000 where name='lisi'' at line 1

-- 查询
mysql> select * from money;
+----+-------+----------+
| id | money | name     |
+----+-------+----------+
|  1 |  6000 | zhangsan |
|  2 | 10000 | lisi     |
+----+-------+----------+
2 rows in set (0.00 sec)

-- 如果某条sql语句执行失败，我们要进行数据回滚，回滚到本次操作之前的状态，防止数据的不一致
mysql> rollback; 
Query OK, 0 rows affected (0.01 sec)

-- 查询
mysql> select * from money;
+----+-------+----------+
| id | money | name     |
+----+-------+----------+
|  1 |  7000 | zhangsan |
|  2 | 10000 | lisi     |
+----+-------+----------+
2 rows in set (0.00 sec)

2 事务的特性

ACID

原子性（Atomicity）

原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。

一致性（Consistency）

事务必须使数据库从一个一致性状态变换到另外一个一致性状态。

隔离性（Isolation）

事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。

持久性（Durability）

持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响。

四 索引

1 什么是索引

索引是一种快速查询表中内容的机制，使用索引可以提高查询效率，相当于字典的目录

可以将查询过程中经常使用的条件设为索引

2 mysql中索引的类型

1）普通索引

这是最基本的索引类型，而且它没有唯一性之类的限制。普通索引可以通过以下几种方式创建：

创建索引，例如CREATE INDEX <索引的名字> ON tablename (列的列表);

修改表，例如ALTER TABLE tablename ADD INDEX [索引的名字] (列的列表);

创建表的时候指定索引，例如CREATE TABLE tablename ( [...], INDEX [索引的名字] (列的列表) );

2）唯一性索引

这种索引和前面的“普通索引”基本相同，但有一个区别：索引列的所有值都只能出现一次，即必须唯一。

唯一性索引可以用以下几种方式创建：

创建索引，例如CREATE UNIQUE INDEX <索引的名字> ON tablename (列的列表);

修改表，例如ALTER TABLE tablename ADD UNIQUE [索引的名字] (列的列表);

创建表的时候指定索引，例如CREATE TABLE tablename ( [...], UNIQUE [索引的名字] (列的列表) );

3）主键索引

主键是一种唯一性索引，但它必须指定为“PRIMARY KEY”。

主键一般在创建表的时候指定，例如“CREATE TABLE tablename ( [...], PRIMARY KEY (列的列表) ); ”。

但是，我们也可以通过修改表的方式加入主键，例如“ALTER TABLE tablename ADD PRIMARY KEY (列的列表); ”

4）全文索引

MySQL从3.23.23版开始支持全文索引和全文检索。

在MySQL中，全文索引的索引类型为FULLTEXT。

全文索引可以在VARCHAR或者TEXT类型的列上创建。

它可以通过CREATE TABLE命令创建，也可以通过ALTER TABLE或CREATE INDEX命令创建。

对于大规模的数据集，通过ALTER TABLE（或者CREATE INDEX）命令创建全文索引要比把记录插入带有全文索引的空表更快。

3 什么情况下创建索引

1）表经常进行 SELECT 操作

2）表很大(记录超多)，记录内容分布范围很广

3）列名经常在 WHERE 子句或连接条件中出现

注意：符合上述某一条要求，都可创建索引，创建索引是一个优化问题，同样也是一个策略问题

-- 100万条数据，查询时有无索引，时间差距明显--
select * from user where username='zhang234567';

插入数据的存储过程：了解

CREATE DEFINER=`root`@`localhost` PROCEDURE `pro1`(IN n INT)
BEGIN
	DECLARE num int;
	set num = 1;
	set autocommit = 0;
	while n - num >= 0 do
		insert into index_test values(num, concat('zhang', num));
		set num = num + 1;
	end while;
	set autocommit = 1;

END

4 索引的缺点

首先，索引要占用磁盘空间。表中建的索引越多，占的磁盘空间也就越多。

第二，对于需要写入数据的操作，比如DELETE、UPDATE以及INSERT操作，索引会降低它们的速度。这是因为MySQL不仅要把改动数据写入数据文件，而且它还要把这些改动写入索引文件。

五 其他

数据库中还包括很多其他知识点，比如视图、触发器、自定义函数、存储过程等。

一般情况下，不建议在实际开发中，通过这些语法实现本该由业务逻辑实现的功能。

附录

MySql数据库的存储引擎

数据库存储引擎是数据库底层软件组织，数据库管理系统（DBMS）使用数据引擎进行创建、查询、更新和删除数据。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎，还可以获得特定的功能。

MySql支持的存储引擎：

其中：

Transactions：是够支持事务。

XA：是否支持XA事务（XA 事务通常用于分布式事务处理当中。比如在分库分表的场景下，当遇到一个用户事务跨了多个分区，需要使用XA事务 来完成整个事务的正确的提交和回滚，即保证全局事务的一致性）

Savepoints：是否支持保存savepoints之间的内容

对比：

B+树的索引

B+Tree是在B-Tree基础上的一种优化，使其更适合实现外存储索引结构，InnoDB存储引擎就是用B+Tree实现其索引结构。在B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度。

B+树索引简图

注：红色的值表示页的值

聚簇索引、非聚簇索引、联合索引

聚簇索引 InnerDB会自动根据主键建立聚簇索引，其叶子节点存储表中每一条完整的记录信息 聚簇索引以主键大小作为排序依据

非聚簇索引 也称为二级索引，其叶子节点总存储的记录包括索引列和主键对应的值 二级索引以索引列的大小作为排序规则

注：如果通过二级索引查找完整的记录，需要执行回表，即先根据索引找到主键，然后根据主键在聚簇索引中找到完整记录

联合索引 根据多个列建立索引，表示联合索引。联合索引也属于二级索引。 使用联合索引时，按照设置的顺序，先以第一个索引列大小进行排序；在第一个索引大小相同的情况下，再以第二个索引列的大小进行排序，以此类推

注：了解了联合索引的表示形式，也就理解了联合索引的最左匹配原则（SQL语句中用到了联合索引中最左边的索引，那么该SQL语句就可以使用联合索引进行匹配）