MySQL2️⃣SQL 语句（❗）

简介

SQL（Structured Query Language）

操作 RDBMS 的编程语言

类型

作用 作用对象
DDL
(Data Definition Language)		 定义数据库对象：数据库、表、字段 结构
DML
(Data Manipulation Language)		 操作数据库表的记录 数据（记录）
DQL（🔥）
(Data QueryLanguage)		 查询数据库表的记录 数据（记录）
DCL
(Data Control Language)		 控制用户及权限 用户权限

书写规范

  1. 单行/多行书写，分号代表结尾。

  2. 大小写不敏感，关键字建议大写。

  3. 使用空格/缩进，以增强语句的可读性。

  4. 使用反引号，避免变量名与关键字冲突。

  5. 注释：

    -- 单行注释
# 单行注释

/*
	多行注释
*/

1、DDL

Data Definition Language（数据定义语言）

定义数据库对象：数据库、表、字段

1.1、操作（❗）

1.1.1、数据库

  1. 查：

    # 所有数据库
SHOW DATABASES;

# 当前数据库
SELECT DATABASE();

  2. 使用（切换）：

    USE 数据库名;

  3. 增（创建）：

    • 存在性：不存在才创建。

    • 字符集：

      • utf8：存储长度 3 字节
      • utf8mb4：存储长度 4 字节，支持特殊符号（👍）

    • 排序规则：

      CREATE DATABASE [IF NOT EXISTS] 数据库名
[DEFAULT CHARSET 字符集]
[COLLATE 排序规则];

  4. 删：

    • 存在性：存在才删除。

      DROP DATABASE [IF EXISTS] 数据库名;

1.1.2、表

  1. 查询：

    # 当前数据库所有表
SHOW TABLES;

# 指定表的结构
DESC 表名;

# 指定表的建表语句
SHOW CREATE TABLE 表名;

  2. 创建：建议在约束条件处统一管理约束。

    CREATE TABLE [IF NOT EXISTS] 表名(
    字段1 类型(长度) [COMMENT 注释],
    ...
    字段n 类型(长度) [COMMENT 注释],
    约束条件
)[COMMENT 表注释];

  3. 删除：

    # 删除
DROP TABLE [IF EXISTS] 表名;

# 清空数据
TRUNCATE TABLE 表名;

  4. 改名：

    ALTER TABLE 表名
RENAME TO 新表名;

1.1.3、字段

从纵向的角度，即表的“列”

  1. 添加：

    ALTER TABLE 表名
ADD 字段名 类型(长度) [COMMENT 注释] [约束];

  2. 删除：

    ALTER TABLE 表名
DROP 字段名;

  3. 修改：

    # 仅修改类型
ALTER TABLE 表名
MODIFY 字段名 新类型(长度);
 
# 修改字段
ALTER TABLE 表名
CHANGE 旧字段名 新字段名 新类型(长度) [COMMENT 注释] [约束];

1.2、数据类型

1.2.1、数值

类型：整型，浮点型。

范围：有符号范围（SIGNED），无符号范围（UNSIGNED）

大小（Byte）
TINYINT 1
SMALLINT 2
MEDIUMINT 3
INT (INTEGER) 4
BIGINT 8
FLOAT 4
DOUBLE 8
DECIMAL (M, D) 取决于精度和标度 (M, D)

1.2.2、字符串

L 表示字符个数

L 范围（byte）
CHAR(L) 0 - 255
VARCHAR(L) 0 - 65,535
TINYTEXT 0 - 255
TEXT 0 - 65,535
MEDIUMTEXT 0 - 16,777,215
LONGTEXT 0 - 4,296,967,295

1.2.3、二进制

M 表示精度

M 范围（Byte）
BIT(M) 约 (M + 7) / 8
BINARY(M) M
VARBINARY (M) M + 1
TINYBLOB (M) 0 - 255
BLOB (M) 0 - 65,535
MEDIUMBLOB (M) 0 - 16,777,215
LONGBLOB (M) 0 - 4,294,967,295

1.2.4、日期时间

日期格式 范围 大小（Byte）
YEAR YYYY 1901 - 2155 1
TIME HH:MM:SS -838:59:59 - 838:59:59 3
DATE YYYY-MM-DD 1000-01-01 - 9999-12-3 3
DATETIME YYYY-MM-DD HH:MM:SS 1000-01-01 00:00:00 - 9999-12-31 23:59:59 8
TIMESTAMP YYYY-MM-DD HH:MM:SS 1980-01-01 00:00:01 UTC - 2040-01-19 03:14:07 UTC 4

1.3、约束

1.3.1、简介

约束：作用于字段的规则，保证数据库中数据的正确性、有效性和完整性。

含义 描述
PRIMARY KEY 主键 一条记录的唯一标识，主键非空且唯一
UNIQUE 唯一 保证该字段的所有值都是唯一、不重复的
FOREIGN KEY 物理外键 建立两张表之间的连接，保证数据的一致性和完整性
NOT NULL 非空 限制该字段值不能为 null
DEFAULT 默认 若保存数据时未指定该字段值，则采用默认值
CHECK 检查（8.0.16+） 保证字段值满足某个条件

示例

  • 表结构：

    类型 含义 约束
    id INT - 主键，自增
    name VARCHAR(10) 姓名 非空，唯一
    age INT 年龄 (0, 120]
    gender CHAR(1) 性别 默认 0

  • 对应建表语句：

    • 一般约束：定义字段时添加（e.g. 非空、默认值）

    • 重要约束：定义字段后统一添加（e.g. 主键，唯一，外键，检查）

      DROP TABLE IF EXISTS t_user;

CREATE TABLE IF NOT EXISTS t_user(
    id INT AUTO_INCREMENT,
    name VARCHAR(10) NOT NULL COMMENT '姓名',
    age INT COMMENT '年龄',
    gender CHAR(1) DEFAULT '0' COMMENT '性别',

    PRIMARY KEY(id),
    UNIQUE KEY `t_user_uni_name` (`name`),
    CONSTRAINT `t_user_chk_1` CHECK ((`age` > 0) and (`age` <= 120))
);

1.3.2、外键

① 类型

外键：建立两张表之间的连接，保证数据的一致性和完整性。

  • 逻辑外键（logically）
    • 含义：逻辑上的关联关系，无实际限制。
    • 特点：在代码层面维护多表关系（👍）。
      1. 增删：先操作主表，再操作从表。
      2. 改：主表更新，从表先删后增。
  • 物理外键（actually）
    • 含义：数据库层面的约束，限制主表记录的删除和更新。
    • 特点：影响性能，不建议使用（👎）。
      1. 尝试变更主表数据时，MySQL 会检查该记录是否存在对应外键。
      2. 根据设定策略进行约束。

② 物理外键

在从表中定义外键。

  • 设置：建表时设置外键约束。

    CREATE TABLE 表名(
    字段定义,
    CONSTRAINT 外键名称 FOREIGN KEY(外键字段) REFERENCES 主表(主表字段)
);

  • 更改：建表后添加/删除外键约束。

    # 添加
ALTER TABLE 表名
ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段) REFERENCES 主表(主表字段);

# 删除
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

③ 外键策略

尝试变更（删除/更新）主表数据时，MySQL 会检查该记录是否存在对应外键。

若存在物理外键，MySQL 会对主表进行约束。

主表变更 存在外键
NOT ACTION
（默认）		 删除，更新 限制（拒绝）操作
RESTRICT 删除，更新 同上
CASCADE 删除，更新 级联变更子表记录
SET NULL 删除 将从表的外键值设为 null
（前提：外键字段允许 null）
SET DEFAULT 更新 将从表的外键值设为指定默认值
（InnoDB 不支持）

语法

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

2、DML

Data Manipulate Language（数据操纵语言）

增删改：从横向的角度，即表的行（记录）

2.1、增删改（❗）

  1. 增：

    • 建议在表名后指定要赋值的字段，提高可读性。

    • 字符串和日期时间值，需要包围在引号中。

    • 可一次性插入多条记录，但大批量数据不推荐使用（👉SQL 优化）

      # 指定字段
INSERT INTO 表名 (字段名1, 字段名2, ...)
VALUES (值1, 值2, ...), 
	(值1, 值2, ...), 
	(值1, 值2, ...);

# 全部字段
INSERT INTO 表名
VALUES
	(值1, 值2, ...),
	(值1, 值2, ...),
	(值1, 值2, ...);

  2. 删：若没有指定 WHERE 条件，会对数据库表的所有记录生效。

    DELETE FROM 表名
[WHERE 条件];

  3. 改：若没有指定 WHERE 条件，会对数据库表的所有记录生效。

    UPDATE 表名
SET 字段名1 = 值1,
	字段名2 = 值2,
	... 
[WHERE 条件];

2.2、对比

区分 DDL 和 DML 对字段/记录的操作

DDL DML
重点 （列）字段，纵向的 （行）记录，横向的
增 ALTER ... ADD INSERT INTO ... VALUES
删 ALTER ... DROP DELETE FROM ... WHERE
改 ALTER ... MODIFY UPDATE ... SET

3、DQL

Data Query Language（数据查询语言）

查询数据库表的记录

👉DQL

4、DCL

Data Control Language（数据控制语言）

用户管理，权限控制

4.1、用户管理

说明

  1. Host：可访问当前数据库的主机，% 表示任意主机。
  2. MySQL 用户：以 用户名@主机名 唯一标识用户。

操作

  1. 查询：切换到 mysql 库下查看。

    USER mysql;
SELECT * FROM user;

  2. 增：创建用户并指定密码。

    CREATE USER '用户名'@'主机名'
IDENTIFIED BY '密码';

  3. 删：

    DROP USER '用户名'@'主机名';

  4. 改密：

    ALTER USER '用户名'@'主机名'
IDENTIFIED WITH mysql_native_password BY '新密码';

4.2、权限控制

4.2.1、常用权限

官方文档：Privileges Provided by MySQL

含义
ALL（ALL PRIVILEGES） 所有权限
SELECT 查询数据
INSERT 插入数据
UPDATE 修改数据
DELETE 删除数据
ALTER 修改表
DROP 删除数据库/表/视图
CREATE 创建数据库/表

4.2.2、控制

说明

  1. 多个权限用逗号 , 分隔。
  2. 数据库名、表名可用 * 表示通配。

操作

  1. 查：用户具有的权限。

    SHOW GRANTS FOR '用户名'@'主机名';

  2. 授予：

    GRANT 权限列表
ON 数据库名.表名 TO
'用户名'@'主机名';

  3. 撤销：

    REVOKE 权限列表
ON 数据库名.表名
FROM '用户名'@'主机名';
posted @ 2022-03-13 12:47  Jaywee  阅读(206)  评论(0)  编辑  收藏  举报

☝

👇