# 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;

# 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 = '用户信息表';

# -- 查看

-- 查看数据库中所有表
SHOW TABLES FROM db_1;

-- 查看某个表的结构
DESC t1_user;

-- 查看某个表的数据
SELECT * FROM t1_user;

# -- 修改

-- 更改表名(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;

# -- 删除

DROP TABLE IF EXISTS t1_calendar;

# 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 = '富豪榜表';

# -- 浮点数

  由于浮点值是近似值,而不是存储为精确值,因此尝试在比较中将它们视为精确值可能会导致问题。

提示

  浮点数超过精度范围 ✨ 会自动进行四舍五入

  由于指数位的存在,理论上 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
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
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

# 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_timeupdated_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
);

# 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';

# 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);
部门编号(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, '后勤部');
-- 后建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);

# 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
更新操作(检查到有关联的外键记录)时 删除操作(检查到有关联的外键记录)时
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;

解决方案

  • 方案 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;
  • 方案 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;

提示

其他案例: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;
更新于 : 7/8/2024, 10:21:14 AM