# DDL
# 一、数据定义
# 1、database 定义
数据库的名称通常是固定的,一旦创建了数据库,就不能直接修改其名称
-- 1、创建 -- 同时设置字符集、校对集 CREATE DATABASE IF NOT EXISTS `db_1` CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; -- 2、查看 -- 查看所有 SHOW DATABASES; -- 查看正在use的数据库 USE db_1; SELECT DATABASE(); -- 3、删除 DROP DATABASE db_1;
Copied!
# 2、table 定义
# -- 创建
USE db_1; -- ========== CREATE TABLE IF NOT EXISTS t1_user ( `user_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '用户ID', `dept_id` bigint(20) DEFAULT NULL COMMENT '部门ID', `user_name` varchar(30) NOT NULL COMMENT '用户账号', `email` varchar(50) DEFAULT '' COMMENT '用户邮箱', `password` varchar(100) DEFAULT '' COMMENT '密码', PRIMARY KEY (`user_id`) ) COMMENT = '用户信息表';
Copied!
# -- 查看
-- 查看数据库中所有表 SHOW TABLES FROM db_1; -- 查看某个表的结构 DESC t1_user; -- 查看某个表的数据 SELECT * FROM t1_user;
Copied!
# -- 修改
-- 更改表名(rename) ALTER TABLE t1_user RENAME TO t2_user; -- 新增字段(add) ALTER TABLE t2_user ADD `sex` char(1) DEFAULT '0' COMMENT '用户性别(0男 1女 2未知)'; -- 更改字段(modify、change) ALTER TABLE t2_user MODIFY `sex` char(1) DEFAULT '1' COMMENT '用户性别(0男 1女 2未知)'; ALTER TABLE t2_user CHANGE `sex` `user_sex` char(1) DEFAULT '0' COMMENT '用户性别(0男 1女 2未知)'; -- 删除字段(drop) ALTER TABLE t2_user DROP `user_sex`; -- ========== DESC t2_user;
Copied!
# -- 删除
DROP TABLE IF EXISTS t1_calendar;
Copied!
# 3、其他对象定义
创建 | 修改 | 删除 | |
---|---|---|---|
模式 | CREATE SCHEME | DROP SCHEME | |
表 | CREATE TABLE | ALTER TABLE | DROP TABLE |
视图 | CREATE VIEW | DROP VIEW | |
索引 | CREATE INDEX | ALTER INDEX | CREATE INDEX |
# 二、数据类型
mysql 官方文档:DataTypes (opens new window)
# 1、数字类型
# -- 整数
经常使用的是 1 个字节的 TINYINT 和 4 个字节的 INT
类型 | 存储(Btyes) | 有符号(SIGNED) | 无符号(UNSIGNED) | |
---|---|---|---|---|
TINYINT | 1 | -128 ~ 127 | 0 ~ 2^8 - 1(255) | |
SMALLINT | 2 | -32768 ~ 32767 | 0 ~ 2^16 - 1(65535) | 6 万多 |
MEDIUMINT | 3 | -8388608 ~ 8388607 | 0 ~ 2^24 - 1(16777215) | 1000 万多 |
INT | 4 | -2147483648 ~ 2147483647 | 0 ~ 2^32 - 1(4294967295) | 4 亿多 |
BIGINT | 8 | -2^63 ~ 2^63 - 1 | 0 ~ 2^64 - 1 | |
MySQL 默认采用 SIGNED 类型 |
CREATE TABLE billionaire_ranking ( billionaire_id INT PRIMARY KEY, full_name VARCHAR(255) NOT NULL, age TINYINT UNSIGNED COMMENT '正常年龄不可能超过255', ) COMMENT = '富豪榜表';
Copied!
# -- 浮点数
由于浮点值是近似值,而不是存储为精确值,因此尝试在比较中将它们视为精确值可能会导致问题。
提示
浮点数超过精度范围 ✨ 会自动进行四舍五入
由于指数位的存在,理论上 float 和 double 是可以表示无穷大/小的数字。
CREATE TABLE t_goods ( goods_name VARCHAR(255) NOT NULL, goods_price FLOAT ) COMMENT = '商品价格表'; INSERT INTO t_goods values('Nike',1999.8889); # 实际存储结果:1999.89 (7位) INSERT INTO t_goods values('Li Ning',3.4e38); # float的最大值为 3.4e38
Copied!
float 的有效数字个数(7)
float 使用 4 个字节存储,IEEE 754 单精度浮点数标准定义了单精度浮点数的位分布:
符号位:1 位;指数位:8 位,用于表示数的指数部分;尾数位:23 位,用于表示数的有效数字
尾数有 23 位(约 3 个字节),可以表示的数字范围 2^23 === 8,388,608,即最多可以 ✨ 7 位的有效数字
根据其未分布,我们可以得到其最大/小值的范围为:
0 11111111 11111111111111111111111 -3.4E38 1 11111111 11111111111111111111111 3.4E38
Copied!
double 的有效数字个数(15-16)
double 使用 8 个字节存储,IEEE 754 双精度浮点数标准定义了双精度浮点数的位分布:
符号位:1 位;指数位:11 位,用于表示数的指数部分;尾数位:52 位,用于表示数的有效数字
尾数有 52(约 7 个字节) 位,可以表示的数字范围 2^52 === 4,503,599,627,370,496,即最多可以 ✨ 16 位的有效数字
和 float 一样,我们根据 double 的位分别也可以得到其最大值的范围为:1.8E308
# -- 定点数
DECIMAL 数据类型是用于存储精确数值的一种数据类型,适用于需要保留小数位数精度的场景,如货币金额或精确计算。
DECIMAL、NUMERIC (DECIMAL 是 NUMERIC 的实现形式)精度控制
CREATE TABLE shareholder_table ( shareholder_id INT PRIMARY KEY, shareholder_name VARCHAR(255), ownership_percentage DECIMAL(4, 2) UNSIGNED COMMENT '数值🎈范围为: 0.00 ~ 99.99' ) COMMENT = '股份占比表'; INSERT INTO shareholder_table values(1, '股东1', 30); # 实际存储结果:30.00 INSERT INTO shareholder_table values(2, '股东3', 9.2567); # 实际存储结果:9.26
Copied!
# 2、日期/时间类型
- DATA
YYYY-MM-DD
格式,范围为'1000-01-01' to '9999-12-31'
- DATATIME
YYYY-MM-DD hh:mm:ss
格式,范围为'1000-01-01 00:00:00.000000' to '9999-12-31 23:59:59.499999'
- TIMESTAMP
YYYY-MM-DD hh:mm:ss
格式,范围为 '1970-01-01 00:00:01.000000' UTC to '2038-01-19 03:14:07.499999' UTC
提示
下表中,created_time
和 updated_time
是开发中一个非常有用的字段,它可以自动记录数据被操作时(DML)的最早和最新时间。
CREATE TABLE event_schedule ( event_id INT AUTO_INCREMENT PRIMARY KEY, event_name VARCHAR(255) NOT NULL, event_date DATE, event_start_time DATETIME, event_end_time DATETIME, created_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP );
Copied!
# 3、字符串类型
CHAT(n):使用固定存储空间/字符长度( n 的取值范围为 0 ~ 255,1 个字节)
VARCHAR(n):指定最大的存储空间/字符长度( n 的取值范围为 0 ~ 65535,2 个字节)
TEXT:一般在文本超过 255 个字符时使用
tinytext(2 ^ 8 -1)、text(2 ^ 16 - 1)、mediumtext(2 ^ 24 - 1)、longtext(2 ^ 32 - 1)
# 三、数据约束
# 1、约束汇总
提示
使用 AUTO_INCREMENT
好处是可以保证其值的唯一性。
但缺点也要注意:
- 不能显示的为
AUTO_INCREMEN
列指定值(数据库会自动为这一列分配下一个可用的唯一值) - 被删除的值不会再次使用,而是继续递增。
如果我们想要显示的修改列指定值,还要让值保持唯一性,可以使用 UNIQUE
。
如果我们为某个字段设置了 PRIMARY KEY,会隐式地表明其值是唯一的,这是主键的主要作用之一
-- 值约束 NOT NULL DEFAULT '' -- 内容约束 UNIQUE AUTO_INCREMENT -- 逻辑约束 PRIMARY KEY FOREIGN KEY -- 其他 CREATE TABLE t_goods ( -- …… UNIQUE KEY(,) ) ENGINE=InnoDB CHARSET=utf8 COMMENT = 'xxx';
Copied!
# 2、主/外键设置
工号(id) | 姓名(name) | 入职时间 entry_time | 部门编号 depart_id |
---|---|---|---|
0001 | 杨戬 | 0001-01-01 | 1 |
0002 | 李白 | 1500-12-12 | 2 |
0003 | 典韦 | 1000-12-12 | 1 |
ALTER TABLE employee ADD depart_id INT; ALTER TABLE employee ADD FOREIGN KEY (depart_id) REFERENCES department(id);
Copied!
部门编号(id) | 部门名称 |
---|---|
1 | 武装部 |
2 | 书院部 |
3 | 后勤部 |
使用外键的作用是确保 depart_id
的值可以在 department 表中的 id
中能够找到。
-- 先建department表 CREATE TABLE department ( id INT PRIMARY KEY, # 设置主键 department_name VARCHAR(255) ) COMMENT = '部门表'; INSERT INTO department (id, department_name) VALUES (1, '武装部'), (2, '书院部'), (3, '后勤部');
Copied!
-- 后建employee表 CREATE TABLE employee ( id CHAR(4) PRIMARY KEY, # 设置主键 name VARCHAR(255), entry_time DATE, depart_id INT, FOREIGN KEY (depart_id) REFERENCES department(id) # 设置外键(建立表关联) ) COMMENT = '员工表'; INSERT INTO employee (id, name, entry_time, depart_id) VALUES ('0001', '杨戬', '0001-01-01', 1), ('0002', '李白', '1500-12-12', 2), ('0003', '典韦', '1000-12-12', 1);
Copied!
# 3、外键更新/删除
mysql 官方文档:FOREIGN KEY Constraints (opens new window)
外键
[CONSTRAINT [symbol]] FOREIGN KEY [index_name] (col_name, ...) REFERENCES tbl_name (col_name,...) [ON DELETE reference_option] [ON UPDATE reference_option] reference_option: RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT
Copied!
值 | 更新操作(检查到有关联的外键记录)时 | 删除操作(检查到有关联的外键记录)时 |
---|---|---|
CASCADE | 更新 | 删除 |
SET NULL | 设置为 NULL | 设置为 NULL |
RESTRICT | 报错 | 报错 |
NO ACTION |
当我执行下列操作时,默认情况下是会报错的:
-- department表 的 id 在 employee 表中用到 -- 1、更新 UPDATE department set id = 4 WHERE id = 1; -- 2、删除 DELETE FROM department WHERE id = 2;
Copied!
解决方案
- 方案 1:
先更新 employee 表,再修改 department 表
UPDATE employee set depart_id = 4 WHERE depart_id = 1; UPDATE department set id = 4 WHERE id = 1; DELETE FROM employee WHERE depart_id = 2; DELETE FROM department WHERE id = 2;
Copied!
- 方案 2:
-- 1、查看外键名称 SHOW CREATE TABLE employee; -- 2、删除设置的外键 ALTER TABLE employee DROP FOREIGN KEY employee_ibfk_1; -- 3、更新外键设置方式 ALTER TABLE employee ADD FOREIGN KEY (depart_id) REFERENCES department(id) on update CASCADE on delete CASCADE;
Copied!
提示
其他案例:https://www.yiibai.com/mysql/on-delete-cascade.html
# 代码
案例完整 sql
CREATE DATABASE IF NOT EXISTS db_foreing CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; USE db_foreing; -- 先建department表 CREATE TABLE department ( id INT PRIMARY KEY, # 设置主键 department_name VARCHAR(255) ) COMMENT = '部门表'; INSERT INTO department (id, department_name) VALUES (1, '武装部'), (2, '书院部'), (3, '后勤部'); SELECT * FROM department; -- 后建employee表 CREATE TABLE employee ( id CHAR(4) PRIMARY KEY, # 设置主键 name VARCHAR(255), entry_time DATE, depart_id INT, FOREIGN KEY (depart_id) REFERENCES department(id) # 设置外键(建立表关联) ) COMMENT = '员工表'; INSERT INTO employee (id, name, entry_time, depart_id) VALUES ('0001', '杨戬', '0001-01-01', 1), ('0002', '李白', '1500-12-12', 2), ('0003', '典韦', '1000-12-12', 1); SELECT * FROM employee; -- 报错 UPDATE department set id = 4 WHERE id = 1; DELETE FROM department WHERE id = 2; -- 解决方案 SHOW CREATE TABLE employee; ALTER TABLE employee DROP FOREIGN KEY employee_ibfk_1; ALTER TABLE employee ADD FOREIGN KEY (depart_id) REFERENCES department(id) on update CASCADE on delete CASCADE; -- 测试 UPDATE department set id = 4 WHERE id = 1; DELETE FROM department WHERE id = 2; SELECT * FROM department; SELECT * FROM employee;
Copied!