任先生的笔记库

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

# 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;
更新于 : 8/7/2024, 2:16:31 PM

← 数据操作-DML →

Apache License 2.0 | Copyright © 2022 lencamo All Rights Reserved | 蜀ICP备2022012538号-2