百木园MySQL 高级
1、约束
1.1、约束介绍
-
什么是约束
- 对表中的数据进行限定，保证数据的正确性、有效性、完整性
-
约束类型
-
约束 说明 PRIMARY KEY 主键约束 UNIQUE 唯一约束 NOT NULL 非空约束 DEFAULT 默认值约束 FOREIGN KEY 外键约束 CHECK 检查约束（MySQL并不支持）
-
-
注意事项
- 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 主表(主键字段名)
- 引用主表的主键的值
- CONSTRAINT
-
-
删除外键约束
-
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查看数据库：发现数据没有改变
- 模拟张三给李四转500元钱（成功）
4.3、事务的四大特性
- 原子性（Atomicity）
- 事务是不可分割的最小操作单位，要么同时成功，要么同时失败
- 一致性（Consistency）
- 事务前后数据的完整性必须保持一致
- 隔离性（Isolation）
- 指多个事务并发访问数据库时，一个事务不能被其他的事务所干扰，多个并发事务之间数据要相互隔离，不能互相影响
- 持久性（Durability）
- 事务一旦提交或回滚，它对数据中的数据的改变就是永久的
4.4、自动提交事务
- 在没有手动开启的情况下，每条增删改语句执行完毕自动提交事务，MySQL默认开始自动提交事务
- 查看MySQL是否开启自动提交事务
- SELECT @@autocommit;
- 0：关闭自动提交
- 1：开启自动提交
- SELECT @@autocommit;
- 关闭自动提交事务
- set autocommit = 0;
- 关闭事务后的案例
- 在控制台执行以下SQL语句：张三-500
- 使用SQLYog查看数据库，发现数据并没有改变
- 在控制台执行
commit提交任务 - 使用SQLYog查看数据库，发现数据改变
来源：https://www.cnblogs.com/OnlyOnYourself-lzw/p/16471573.html
本站部分图文来源于网络，如有侵权请联系删除。