Mysql数据库基础知识（2）

Mysql数据库基础知识（2）

（包括约束，多表查询，事务，索引等知识）

一.约束

1. 概述

（1）概念:约束是作用于表中字段上的规则，用于限制存储在表中的数据

（2）目的:保证数据库中数据的正确、有效性和完整性。

（3）分类：

1）非空约束 限制该字段的数据不能为null NOT NULL

2）唯一约束 保证该字段的所有数据都是唯一、不重复的 UNIQUE（关键字）

3）主键约束 主键是一行数据的唯一标识，要求非空且唯一 PRIMARY KEY（关键字）

4）默认约束 保存数据时，如果未指定该字段的值，则采用默认值 DEFAULT（关键字）

5）检查约束(8.0.16版本之后) 保证字段值满足某一个条件 CHECK（关键字）

6）外键约束 用来让两张表的数据之间建立连接，保证数据的一致性和完整性 FOREIGN KEY（关键字）

7）注意:约束是作用于表中字段上的，可以在创建表/修改表的时候添加约束。

8）自动增长： AUTO INCREMENT（关键字）

9）如：create table user(

id int primary key auto_increment comment'主键"

name varchar(10) not null unique comment'姓名'，

age int check(age>0&& age <= 120 )comment"年龄"，

status char(1)default '1'comment"状态'，

gender char(1)comment性别"

）comment'用户表"；

2. 外键约束

（1）概念：外键用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性。有外键的为子表，外键关联的为父表。

（2）语法：

1）添加外键

··CREATE TABLE 表名(

字段名 数据类型

…

[CONSTRAINT][外键名称]FOREIGN KEY(外键字段名) REFERENCES 主表 (主表列名)

）；

··ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名);

2）删除外键

··ALTER TABLE 表名 DROP FOREIGN KEY 外键名称

3）删除/更新行为

··NO ACTION 当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。(与 RESTRICT 一致)

··RESTRICT 当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/吏新。(与NO ACTION 一致)

··CASCADE当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有，则也删除/更新外键在子表中的记录。

··SET NULL 当在父表中删除对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为nul(这就要求该外键允许取nu)。

··SET DEFAULT 父表有变更时，子表将外键列设置成一个默认的值(Innodb不支持)

··格式：ALTERTABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段) REFERENCES 主表名(主表字段名) ON UPDATE *（选项） ON DELETE *（选项）

二.多表查询

1. 概述：

项目开发中，在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以各个表结构之间也存在着各种联系，基本上分为三种:一对多(多对一)，多对多，一对一。

2. 一对多(多对一)

·案例: 部门 与 员工的关系

·关系:一个部门对应多个员工，一个员工对应一个部门

·实现:在多的一方建立外键，指向一的一方的主键

3. 多对多

·案例: 学生 与 课程的关系

·关系:一个学生可以选修多门课程，一门课程也可以供多个学生选择

·实现:建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

4.一对一

（1）案例: 用户 与 用户详情的关系

（2）关系:一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以

提升操作效率

（3）实现:在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的(UNIQUE)

5. 笛卡尔积

笛卡尔乘积是指在数学中，两个集合A集合 和 B集合的所有组合情况。(在多表查询时，需要消除无效的笛卡尔积)（A*B）

6. 多表查询分类

（1）连接查询

1）内连接:相当于查询A、B交集部分数据

2）外连接:左外连接:查询左表所有数据，以及两张表交集部分数据

右外连接:查询右表所有数据，以及两张表交集部分数据

3）自连接:当前表与自身的连接查询，自连接必须使用表别名

（2）子查询

7. 连接查询-内连接

（1）内连接查询语法：

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

2)显式内连接:SELECT 字段列表 FROM 表1 [INNER]JOIN 表2 ON 连接条件 …;

(2)注意：内连接查询的是两张表交集的部分

8. 连接查询-外连接

（1）外连接查询语法:

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

（相当于查询表1(左表)的所有数据 包含 表1和表2交集部分的数据）

2）右外连接：SELECT 字段列表 FROM 表1 RIGHT 「OUTER]JOIN 表2 ON 条件。

（相当于查询表2(右表)的所有数据 包含 表1和表2交集部分的数据）

9. 连接查询-自连接

（1）自连接查询语法 : SELECT 字段列表 FR  OM 表A 别名A JOIN 表A 别名B ON 条件.

（自连接查询，可以是内连接查询，也可以是外连接查询。）

如：select a.name ,b.name from emp a emp b where a.managerid = b.id;

select a.name '员工',b.name '领导'from emp a left join emp b on a.managerid = b.id;（注意：必须起别名！！！）

10. 联合查询-union,union all

（1）对于union查询，就是把多次查询的结果合并起来，形成一个新的查询结果集。

SELECT 字段列表 FROM 表A …

UNION [ ALL ]

SELECT 字段列表 FROM 表B …;

（2）对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致，

union al 会将全部的数据直接合并在一起，union 会对合并之后的数据去重

11. 子查询

（1）概念:SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询，

SELECT *FROM t1 WHERE column1=(SELECT column1 FROM t2 );

子查询外部的语句可以是INSERT/UPDATE/DELETE/SELECT的任何一个.

（2）根据子查询结果不同，分为:

1）标量子查询(子查询结果为单个值)

2）列子查询(子查询结果为一列)

3）行子查询(子查询结果为一行)

4）表子查询(子查询结果为多行多列)

（3）根据子查询位置，分为:WHERE之后、FROM之后、SELECT之后。

（4）标量子查询

1）子查询返回的结果是单个值(数字、字符串、日期等)，最简单的形式，这种子查询成为标量子查询

2）常用的操作符:=<，<>，>，>=，<，<=

3）如：select * from emp where dept_id =(select id from dept where name ='销售部');

（5）列子查询

1)子查询返回的结果是一列(可以是多行)，这种子查询称为列子查询

2)常用的操作符:IN、NOTIN、ANY、SOME、ALL

IN 在指定的集合范围之内，多选一

NOT IN 不在指定的集合范围之内

ANY 子查询返回列表中，有任意一个满足即可

SOME 与ANY等同，使用SOME的地方都可以使用ANY

ALL 子查询返回列表的所有值都必须满足。

3）如：select salary from emp where dept_id =(select id from dept where name ='财务部');

(6) 行子查询

1)子查询返回的结果是一行(可以是多列)，这种子查询称为行子查询

2)常用的操作符:=、<>、IN、NOT IN   

3)如：select * from emp where(salary,managerid):(select salary，managerid from emo where name ='张无忌");

（7）表子查询

1）子查询返回的结果是多行多列，这种子查询称为表子查询

2）常用的操作符:IN

3）select * from  emp where (job,salary)in(select job,salary from

emp where name ='鹿杖客"or name ='宋远桥');

三.事务

1. 事务简介

事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。

2. 事务操作

（1）方式一

1）查看/设置事务提交方式

SELECT @@autocommit;（返回值为1，表明为自动提交）

SET @@autocommit=0；（取消自动提交，手动提交）

2）提交事务

COMMIT;

3）回滚事务

ROLLBACK;

（2）方式二

1）开启事务

START TRANSACTON 或 BEGIN;

2）提交事务

COMMIT;

3）回滚事务

ROLLBACK;

3. 事务四大特性

（1）原子性(Atomicity):事务是不可分割的最小操作单元，要么全部成功，要么全部失败。

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

（3）隔离性(lsolation):数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。

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

4. 并发事务问题

（1）脏读 -个事务读到另外一个事务还没有提交的数据。

（2）不可重复读 一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读

（3）幻读 一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了“幻影”。

5. 事务隔离级别

隔离级别	赃读	不可重复读	幻读
Read uncommitted	√	√	√
Read committed	×	√	√
Repeatable Read(默认)	×	×	√
Serializable	×	×	×

（1）-查看事务隔离级别

SELECT @@TRANSACTION ISOLATON,;

（2）--设置事务隔离级别

SET [ SESSION | GIOBAL] TRANSACTION ISOLATON LEVEL { READ UNCOMIMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}

（SESSION：当前窗口有效，GIOBAL：所以窗口有效）

（从上到下级别越来越高，但级别越高效率越低）

（3）例子

set session transaction isolation level repeatable read

四. 索引

1.概念：

索引(index)是帮助数据库 高效获取数据 的 数据结构

2. 优缺点

(1)优点

1)提高数据查询的效率，降低数据库的I0成本。

2)通过索引列对数据进行排序，降低数据排序的成本，降低CPU消耗。

(2) 缺点

1)索引会占用存储空间。

2)索引大大提高了查询效率，同时却也降低了insert、update、delete的效率。

(对优点来说缺点可以忽略不计)

3. 结构

MySQL数据库支持的索引结构有很多，如:Hash索引、B+Tree索引、Full-Text索引等。我们平常所说的索引，如果没有特别指明，都是指默认的 B+Tree (多路平衡搜索树）结构组织的索引。

4. 语法

（1）创建索引

create [unique] index 索引名 on 表名(字段名,.);

（2）查看索引

show index from 表名；

（3）删除索引

drop index 索引名 on 表名；

（4）注意事项

1）主键字段，在建表时，会自动创建主键索引。

2）添加唯一约束时，数据库实际上会添加唯一索引。