MySQL 高级

1、约束

1.1、约束介绍

什么是约束
- 对表中的数据进行限定，保证数据的正确性、有效性、完整性
约束类型
- 约束说明
  
  PRIMARY KEY 主键约束
  
  UNIQUE 唯一约束
  
  NOT NULL 非空约束
  
  DEFAULT 默认值约束
  
  FOREIGN KEY 外键约束
  
  CHECK 检查约束（MySQL并不支持）
注意事项
- MySQL不支持检查约束
- 约束通常是在创建表结构的时候创建
  - 如果在创建表结构的时候没增加约束，后续再添加约束的话，有可能会导致垃圾数据的进入

约束	说明
PRIMARY KEY	主键约束
UNIQUE	唯一约束
NOT NULL	非空约束
DEFAULT	默认值约束
FOREIGN KEY	外键约束
CHECK	检查约束（MySQL并不支持）

1.2、主键约束

主键的作用
- 用来区分表中的数据
主键的特点
- 主键必须是唯一不重复的值
- 主键不能包含NULL值

建表的时候添加主键约束

CREATE TABLE 表名 (
	字段名 字段类型 PRIMARY KEY,
	字段名 字段类型
);

CREATE TABLE 表名(
   列名 数据类型,
   [CONSTRAINT] [约束名称] PRIMARY KEY(列名)
);

删除主键约束

ALTER TABLE 表名 DROP PAIMARY KEY;	-- 非空主键不会随着主键约束的删除而消失，在MySQL中会保存下来

注意事项
- 非空主键不会随着主键约束的删除而消失，在MySQL中会保存下来

建表后单独添加主键约束
- ```
ALTER TABLE 表名 ADD PRIMARY KEY (字段名);
```
- 注意事项
  - 当添加主键约束的时候，字段的值如果在表中存在有重复值，那么建表后单独添加主键约束会报错
主键自增
- 主键如果让我们自己添加很有可能重复，我们通常希望在每次插入新纪录时，数据库自动生成主键字段的值
- 主键设置为自增后，允许插入的主键为NULL值，自增的主键会自动把NULL值改为自增后的数据
- 格式
  - 字段名字段类型 PRIMARY KEY AUTO_INCREMENT
  - 注意事项：AUTO_INCREMENT 的字段必须是数值类型

面试题：修改自动增长的开始值

ALTER TABLE st2 AUTO_INCREMENT = 1000;
INSERT INTO st2 (NAME, age) VALUES (\'校长\', 22);

ALTER TABLE st2 AUTO_INCREMENT = 500;
INSERT INTO st2 (NAME, age) VALUES (\'coolman\', 23);

注意事项
- 自增以出现过的最大值为基准而+1

1.3、非空约束

非空约束的作用
- 让字段的值不能为NULL

非空约束的格式

CREATE TABLE 表名 (
	字段名 字段类型 NOT NULL,
    字段名  字段类型
);

1.4、唯一约束

唯一约束的作用
- 让字段的值唯一，不能重复

唯一约束的格式

CREATE TABLE 表名 (
	字段名 字段类型 UNIQUE,
  	字段名 字段类型
);

1.5、默认约束

默认约束的作用
- 如果这个字段不设置值，就使用默认值

默认约束的格式

CREATE TABLE 表名(
  	字段名 数据类型 DEFAULT 值,
  	字段名 字段类型
);

1.6、外键约束

1.6.1、使用外键约束的意义

当我们在employee的dep_id里面输入不存在的部门，数据依然可以添加，但是并没有对应的部门，不能出现在这种情况。employee的dep_id的内容只能是department表中存在的id
解决方式
- 需要约束dep_id只能是department表中已经存在id
- 可以使用外键约束来解决这类问题
外键约束的作用
- 1.限制表中的数据只能使用另外一张表的数据
- 2.保证数据的一致性、完整性

1.6.2、外键约束的概念

什么是外键
- A1表中的字段C1，引用了A2表中字段C2，那么C1字段叫做外键，A2表交主表，A1表叫从表（也叫副表）
- 主表：将数据给别人用的表
- 副表：使用别人数据的表

1.6.3、外键约束的使用

新建表的时候增加外键约束
- ```
CREATE TABLE 表名 (
	字段名 字段类型,
  	字段名 字段类型,
  	-- 添加外键约束
  	[CONSTRAINT 外键约束名] FOREIGN KEY (外键字段名) REFERENCES 主表(主表字段名)
);
```
- 关键字解释
  - CONSTRAINT
    - 表示约束外键约束名：给外键取个名字，将来通过约束名可以删除这个约束
  - FOREIGN KEY(外键字段名)
    - 指定某个字段左外外键
  - REFERENCES 主表(主键字段名)
    - 引用主表的主键的值
删除外键约束
- ```
ALTER TABLE 表名 DROP FOREGIN KEY 外键约束名;
```
- 注意事项
  - 删除外键的时候，外键名不需要添加单引号（外键名等同于其他字段名）

已有表增加外键约束

ALTER TABLE 从表 ADD [CONSTRAINT 外键约束名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主键字段名);

2、数据库设计（范式）

2.1、数据库设计简介

1.软件的研发步骤
2.数据库设计概念
- 数据设计就是根据业务系统的具体需求，结合我们所选用的DBMS，为这个业务系统构造出最优的数据存储模型
- 建立数据库中的表结构以及表与表之间的关联关系的过程
- 有哪些表？表里有哪些字段？表和表之间有什么关系？
3.数据库设计的步骤
- 需求分析（数据是什么，数据具有哪些属性，数据与属性的特点是什么）
- 逻辑分析（通过ER图对数据库进行逻辑建模，不需要考虑我们所选用的数据库管理系统）
- 物理设计（根据数据库自身的特点把逻辑设计转换为物理设计）
- 维护设计（对新的需求进行建模；表优化）
论坛系统设计案例

2.2、表关系

2.2.1、表关系之一对多

一对多（多对一）
- 部门表和员工表
- 一个部门对应多个员工，一个员工对应一个部门
实现方式
- 在多的一方建立外键，指向一的一方的主键

2.2.2、表关系之多对多

多对多
- 订单表和商品表
- 一个商品对应多个订单，一个订单包含多个商品
实现方式
- 建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

2.2.3、表关系之一对一

一对一
- 用户表和用户详情表
- 一对一关系多用于表拆分，将一个实体中经常使用的字段放在一张表，不经常使用的字段放另一张表，用于提升查询性能
实现方式
- 在任意一方加入外键，关联另一方主键，并且设置外键为唯一（UNIQUE）

2.3、数据库设计案例

3、MySQL多表查询

3.1、MySQL多表查询介绍

为什么要有多表查询
- 例如要查询某员工的名字和他所在的部门名字（这里假设数据库中员工表和部门表是关联的）
- 需要查询多张表才能得到我们想要的数据
多表查询的分类
- 表连接查询（同时查询多张表）
  - 内连接
  - 外连接
- 子查询

3.2、表连接笛卡尔积现象

查询孙悟空员工的信息，包括所在的部门名称
- 左表的每条数据和右表的每条数据组合，这种效果称为笛卡尔乘积
- 我们发现不是所有的数据组合都是游泳的，只有员工表.dept_id = 部门表.id的数据才是游泳的。所以需要通过条件过滤掉没用的数据。
- 过滤掉没用数据的条件称为表连接条件

3.3、表连接查询--内连接

隐式内连接
- ```
SELECT 字段列表 FROM 表1, 表2,... WHERE 条件;
```
- 看不到 JOIN关键字，条件使用WHERE指定
显式内连接
- ```
SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 条件；
```
- 使用INNER JOIN ... ON 条件，可以省略INNER
内连接效果

3.4、表连接查询--外连接

3.4.1、左外查询

SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件;

使用LEFT OUTER JOIN ... ON，OUTER可以省略
左外连接效果
- 左外连接可以理解为：将满足要求的数据显示，左表不满足要求的数据也显示

3.4.2、右外查询

SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件;

使用RIGHT OUTER JOIN ... ON，OUTER可以省略
右外连接效果
- 右外连接可以理解为：满足要求的数据显示，并且右表不满足要求的也显示

3.5、多表查询之子查询

什么是子查询
- 一个查询语句的结果作为另一个查询语句的一部分

Demo

SELECT 查询字段 FROM 表 WHERE 条件;

SELECT * FROM employee WHERE salary=(SELECT MAX(salary) FROM employee);

子查询要放在()中
先执行子查询，将子查询的结果作为父查询的一部分

子查询结果的三种情况
- 子查询结果是单行单列
  - ```
  SELECT 查询字段 FROM 表 WHERE 字段=(子查询);
```
- 子查询结果是单行单列，在WHERE后面作为条件，WHERE后面使用的是比较运算符：=，>, <=, <>
- 子查询结果是多行单列
  - ```
  SELECT 查询字段 FROM 表 WHERE 字段 IN (子查询);
```
- 子查询结果是多行单列，结果集类似于一个数组，在WHERE后面作为条件，父类使用IN/ANY/ALL运算符
- 子查询结果是多行多列
  - ```
  SELECT 查询字段 FROM (子查询) 表别名 WHERE 条件;
```
- 子查询结果是多行多列，在FROM后面作为虚拟表

3.6、多表查询案例

我们在公司开发中，根据不同的业务需求往往需要通过2张及以上的表中去查询需要的数据。所以我们有必要学习2张及以上的表的查询。其实不管是几张表的查询，都是有规律可循的。
- 准备数据在备注中
- 练习1:查询所有员工信息。显示员工编号, 员工姓名, 工资, 职务名称, 职务描述
- 练习2:查询所有员工信息。显示员工编号, 员工姓名, 工资, 职务名称, 职务描述, 部门名称, 部门位置
- 练习3:查询经理的信息。显示员工姓名, 工资, 职务名称, 职务描述, 部门名称, 部门位置, 工资等级
- 练习4:查询出部门编号、部门名称、部门位置、部门人数
- 练习5:列出所有员工的姓名及其直接上级的姓名, 没有上级领导的员工也需要显示,显示自己的名字和领导的名字
- 练习6:查询出所有的普通员工
- 练习7:查询工资高于公司平均工资的所有员工信息。显示员工id, 员工姓名, 员工工资, 部门名称, 工资等级

4、数据库事务

4.1、事务简介

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

事务的使用

-- 开启事务
START TRANSACTION; -- 或者 BEGIN; 效果一样
-- 提交事务
COMMIT;
-- 回滚事务
ROLLBACK;

事务Demo

4.2、转账事务案例

演示手动提交事务
- 模拟张三给李四转500元钱（成功）
  - 在DOS命令行执行以下SQL语句： 1.开启事务 2.张三账号-500 3.李四账号+500
  - 在DOS命令行执行commit：提交事务
  - 使用SQLYog查看数据库：发现数据改变
- 模拟张三给李四转500元钱（失败）
  - 在DOS命令行执行以下SQL语句：1.开启事务， 2.张三账号-500
  - 在DOS命令行执行rollback回滚事务
  - 使用SQLYog查看数据库：发现数据没有改变

4.3、事务的四大特性

原子性（Atomicity）
- 事务是不可分割的最小操作单位，要么同时成功，要么同时失败
一致性（Consistency）
- 事务前后数据的完整性必须保持一致
隔离性（Isolation）
- 指多个事务并发访问数据库时，一个事务不能被其他的事务所干扰，多个并发事务之间数据要相互隔离，不能互相影响
持久性（Durability）
- 事务一旦提交或回滚，它对数据中的数据的改变就是永久的

4.4、自动提交事务

在没有手动开启的情况下，每条增删改语句执行完毕自动提交事务，MySQL默认开始自动提交事务
查看MySQL是否开启自动提交事务
- SELECT @@autocommit;
  - 0：关闭自动提交
  - 1：开启自动提交
关闭自动提交事务
- set autocommit = 0;
关闭事务后的案例
- 在控制台执行以下SQL语句：张三-500
- 使用SQLYog查看数据库，发现数据并没有改变
- 在控制台执行commit提交任务
- 使用SQLYog查看数据库，发现数据改变

来源：https://www.cnblogs.com/OnlyOnYourself-lzw/p/16471573.html
本站部分图文来源于网络，如有侵权请联系删除。

02-MySQL高级

MySQL 高级

1、约束

1.1、约束介绍

1.2、主键约束

1.3、非空约束

1.4、唯一约束

1.5、默认约束

1.6、外键约束

1.6.1、使用外键约束的意义

1.6.2、外键约束的概念

1.6.3、外键约束的使用

2、数据库设计（范式）

2.1、数据库设计简介

2.2、表关系

2.2.1、表关系之一对多

2.2.2、表关系之多对多

2.2.3、表关系之一对一

2.3、数据库设计案例

3、MySQL多表查询

3.1、MySQL多表查询介绍

3.2、表连接笛卡尔积现象

3.3、表连接查询--内连接

3.4、表连接查询--外连接

3.4.1、左外查询

3.4.2、右外查询

3.5、多表查询之子查询

3.6、多表查询案例

4、数据库事务

4.1、事务简介

4.2、转账事务案例

4.3、事务的四大特性

4.4、自动提交事务

相关推荐

热门文章