Back to VNotes

Database / SQL

MySQL 与 SQL 基础（五）

记录 MySQL 启动方式、SQL 分类、DDL、DML、DQL、DCL 和表结构操作。

数值函数

常见函数：

函数功能
CEIL(x)向上取整
FLOOR(X)向下取整
MOD(x, y)返回x % y
RAND()返回0~1内的随机数
ROUND(x, y)求x的四舍五入值，保留y位小数

日期函数

常见函数：

函数功能
CURDATE()返回当前日期
CURTIME()返回当前时间
NOW()返回当前日期和时间
YEAR()获取指定date的年份
MONTH()获取指定date的月份
DAY()获取指定date的日期
DATE_ADD(date, INTERVAL expr type)返回一个日期/时间值加上一个时间间隔expr后的时间值
DATEDIFF(date1, date2)返回起始时间date1和结束时间date2之间的天数

流程函数

常见函数：

函数功能
IF(value, t, f)如果value为true，则返回t，否则返回f
IFNULL(value1, value2)如果value1不为NULL，返回value1，否则返回value2
CASE WHEN [val1] THEN [res1] ... ELSE [DEFAULT] END如果val1为true，返回res1, ... 否则返回DEFAULT
CASE [expr] WHEN [val1] THEN [res1] ... ELSE [DEFAULT] END如果expr的值等于val1，返回res1, ... 否则返回DEFAULT

例：

SELECT (case 2 WHEN 2 THEN 'Y' WHEN 3 THEN 'N' ELSE 'D' END);

约束

概述

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

目的：保证数据库中数据的正确、有效性和完整性

分类：

约束描述关键字
非空约束限制该字段的数据不能为NULLNOT NULL
唯一约束保证该字段的所有数据都是唯一，不重复的UNIQUE
主键约束主键是一行数据的唯一标识，要求非空且唯一PRIMARY KEY
默认约束保存数据时，如果未指定该字段的值，则采用默认值DEFAULT
检查约束（8.0.16）版本之后保证字段值满足某一个条件CHECK
外键约束用来让两张表的数据之间建立连接，保证数据的一致性和完整性FOREIGN KEY
自增数据会从1开始，每多一条数据自动+1，由数据库维护，无需传入数据，但需要注意的是某些情况下就算插入失败也会自增，导致“空洞”AUTO_INCREMENT

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

多个约束之间直接用空格分隔

外键约束

让两张表的数据之间建立联系

具有外键的表称为子表/从表，被外键所关联的表称为父表/主表

主表需已建立索引（普通索引、唯一索引或主键）才能添加外键

当存在外键时，若主表的数据在从表中也存在，则不能删除/更新主表的该数据

添加外键

CREATE TABLE 表名(
		字段名 字段类型,
		...
		[CONSTRAINT] [外键名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主表列名)
);
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表(主表列名);

删除外键

ALTER TABLE 表名 DROP FOREIGN KEY 外键名;

外键名是自己起的，没有明确要求

删除/更新行为

行为说明
NO ACTION当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新（与RESTRICT一致）
RESTRICT当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新（与NO ACTION一致）
CASCADE当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则也删除/更新外键在子表中的记录
SET NULL当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为NULL（要求该外键允许为NULL）
SET DEFAULT父表有变更时，子表将外键设为一个默认值（Innodb不支持）

更改删除/更新行为

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES 主表名(主表字段名) ON UPDATE 行为 ON DELETE 行为;

多表查询

多表关系

  • 多对一（一对多）
  • 多对多
  • 一对一

多对一：

案例：一个部门对应多个员工

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

多对多：

案例：学生可以选多门课程，课程也可以被多个学生选择

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

一对一：

案例：用户和用户详情的关系，有时将一张表拆分成两张表来存放，以提升性能

实现：在任意一方加入外键，关联另一方的主键，并设置外键为UNIQUE