跳至主要內容

02.SQL命令行基础语句与基本类型

12a2024/10/3大约 43 分钟数据库后端sql

02.SQL命令行基础语句与基本类型

2.1 数据类型

  • 不同数据库可使用的类型不相同，但经常使用的类型还是基本相同的
整数
  • 如果希望使用无符号数
    • MySQL、SQLServer、Oracle可以在声明时使用UNSIGNED关键字
    • PostgreSQL不提供无符号关键字，所有的数字都是有符号的，如果有必要使用无符号数，最便捷的方法是使用BIGINT，或使用数位存储，并在读取时转换
类型声明描述长度与限制可使用此类型的数据库
INT/INTEGER标准整数类型, 存储整数通常占4B, 32位有符号整数MySQL, PostgreSQL, SQLServer, Oracle
SMALLINT较小范围的整数类型通常占2B,[-32768,32767]MySQL, PostgreSQL, SQLServer, Oracle
BIGINT大范围的整数类型通常占8B, 64位有符号整数MySQL, PostgreSQL, SQLServer, Oracle
BOOLEAN布尔值, 表示TRUE或FALSE通常占1BPostgreSQL
TINYINT(1)模拟布尔值的整数类型(取值范围为0和1)最多占1B, 范围：0到255(无符号)或-128到127(有符号)MySQL
BIT模拟布尔值的位类型占1位或更多(最多64位)SQLServer,MySQL

  • 在PostgreSQL中支持定义域，修改限制原有的基本类型

    # 当尝试将负值赋给uint4类型的列时，PostgreSQL会抛出错误
CREATE DOMAIN uint4 AS integer CHECK (VALUE >= 0);

2.2 数据定义语言DDL

  • 大部分数据库定义语言没有简单的办法回滚到操作之前，最好是对被影响表进行备份，如果没有提前备份恢复起来可能很困难

2.2.1 数据库操作

  • 数据库操作主要包括以下方面

    • 查询
    MySQL
    -- 查询所有数据库，返回所有数据库名称
SHOW DATABASES;

-- 查询当前所在数据库，返回一行一列数据，对应数据库名称
SELECT DATABASE();
    • 创建和删除
    MySQL
    -- 数据库名称可以自定义，如果和关键字相同需要使用``包裹
-- 字符集：定义文字的编码方式，一般使用utf8mb4，支持特殊字符
-- 具体支持的字符集可以通过SHOW CHARACTER SET;查看
-- 排序规则：设置字符串的排序规则，具体支持的排序规则可以通过SHOW COLLATION;查看
-- 排序规则最后的ci表示大小写不敏感，cs表示大小写敏感，bin表示是以二进制存储，区分大小写排序，ai表示不区分重音，as表示区分重音
-- 排序规则中间一般有一个比较的规则名称，不同国家可能有不同的比较方法
CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集] [COLLATE 排序规则];

-- 字符集：MySQL中有四种级别的字符集设置
-- 1. 服务器级别：character_set_server，通过配置文件设置或通过SET语句修改
--    - 还有其他用于处理请求的字符集通过SET NAMES 字符集;修改
-- 2. 数据库级别：character_set_database，默认使用服务器级别字符集
-- 3. 表级别：默认使用数据库级别的字符集
-- 4. 列级别：默认使用表级别的字符集

-- 例子
CREATE DATABASE IF NOT EXISTS db1 DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

-- 删除数据库
DROP DATABASE [IF EXISTS] 数据库名;
    • 修改
    MySQL
    -- 修改数据库定义的信息包括字符集和排序规则
ALTER DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集] [COLLATE 排序规则];

-- 修改完数据库默认字符集无法修改已创建表的字符集，需要对每张表进行转换
-- CHARACTER SET 是 CHARSET 的别名
-- 仅转换表的字符集
ALTER TABLE 表名 DEFAULT CHARACTER SET 字符集 COLLATE 排序规则;
-- 不仅转换表的字符集，还修改数据使用的字符集
ALTER TABLE 表名 CONVERT TO CHARACTER SET 新字符集;
-- 仅修改某列的字符集和单例的数据
ALTER TABLE 表名 CHANGE 列名 VARCHAR(32) CHARACTER SET 字符集 COLLATE 排序规则;

    一般情况下，同数据库中表的字段字符集和排序规则都是相同的，不同的话，编写查询时需要指定查询的排序规则，同样在末尾使用COLLATE，不然速度会受影响。注意！至少要保证需要关联查询的不同表的字段排序规则要相同，否则会关联查询报错

    • 使用
    MySQL
    -- 使用数据库
use 数据库名;

2.2.2 表操作

  • 表是数据的逻辑存储结构，由行和列组成，可以通过以下命令查询表和表的结构

    MySQL
    -- 查询所有表名
SHOW TABLES;

-- 查询某个表信息，包括字段、类型、能否为空、是否为主键、默认值和额外标识
DESC 表名;
DESCRIBE 表名;

-- 查询指定表的建表语句
SHOW CREATE TABLE 表名;

-- 查询表的其他信息
SHOW TABLE STATUS [FROM 数据库名] [LIKE '表名']\G

创建一个表

  • 最简单的方法就是为一些需要的字段创建表，常见字段类型可在2.1数据类型查看

    MySQL
    -- 描述将保留在查询到的建表语句中
CREATE TABLE [IF NOT EXISTS ]表名 (
  字段名1 字段类型 [字段约束] [额外标识] [COMMENT '字段描述信息'],
  字段名2 字段类型 [字段约束] [额外标识] [COMMENT '字段描述信息'],
  字段名3 字段类型 [字段约束] [额外标识] [COMMENT '字段描述信息']
  [,字段约束]
)[可选配置项] [COMMENT '表描述'];
-- 可选配置项：对于一张表可以配置一些可选项，都有默认值，一般不需要更改，配置项之间用逗号隔开
-- * 数据库引擎：默认为ENGINE=InnoDB
-- * 表字符集：默认为数据库级别字符集DEFAULT CHARSET=utf8mb4
-- * 表排序规则：默认为数据库级别排序规则COLLATE=utf8mb4_0900_ai_ci
-- * 行格式：默认为ROW_FORMAT=DYNAMIC
-- * 自增起始值：默认为AUTO_INCREMENT=1

-- 简单的表创建语句
CREATE TABLE student (
  id INT COMMENT '学号',
  `name` VARCHAR(32),
  age INT UNSIGNED
);

  • 字段约束：所有数据库都支持在创建表时添加字段约束，作用在表中字段的规则，用于限制表中的数据

    • 主键约束PRIMARY KEY：主键是数据行的唯一标识，相当于非空且唯一约束，并且在绝大多数时候要求主键不改变且不包含隐私信息
    • 外键约束FOREIGN KEY：设置外键，保证数据完整性和一致性，可以针对删除主表ON DELETE和更新主表ON UPDATE操作设置级联行为(级联策略)

    外键对表操作和性能有影响，可以考虑在服务器应用层设计验证机制，不完全依赖数据库的约束

    级联行为说明
    NO ACTION当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新
    RESTRICT与NO ACTION一致，默认策略
    CASCADE当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有，则也删除/更新外键在子表中的记录
    SET NULL当在父表中删除对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为null(这要求外键允许取null)
    SET DEFAULT父表有变更时，子表将外键列设置成一个默认的值(Innodb不支持)
    • 唯一约束UNIQUE：限制字段的所有数据值必须唯一、不重复的
    • 非空约束NOT NULL：限制字段不能为空
    • 默认约束DEFAULT：设置保存数据时如果未指定使用的默认值
    • 检查约束CHECK：保证字段满足某一条件，MySQL 8.0.16版本才添加CHECK约束，早期版本请使用触发器
    MySQL
    create table IF NOT EXISTS `users` (
    id INTEGER PRIMARY KEY AUTO_INCREMENT COMMENT 'id，主键，自增',
    name VARCHAR(55) NOT NULL COMMENT '姓名，不为空',
    -- 可使用判断条件，在check的括号中可读取行信息
    email VARCHAR(50) check ( email like '%@%' ) COMMENT '邮箱，必须包含@',
    phone VARCHAR(50) UNIQUE NOT NULL COMMENT '电话号码，唯一且不为空',
    status CHAR(1) DEFAULT '1' COMMENT '状态，默认为1', 
    gender CHAR(1) COMMENT '性别',
    -- 时间类型，支持设置插入行时间为默认值
    entry_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '加入时间，默认为插入行的时间',
    department_id INTEGER COMMENT '外键，连接部门表',
    -- 格式为FOREIGN KEY (字段名) REFERENCES 主表名(主表列名) [ON DELETE 级联策略] [ON UPDATE 级联策略]
    FOREIGN KEY (department_id) REFERENCES `departments` (id) ON DELETE SET NULL ON UPDATE CASCADE
);

-- 可以临时关闭外键约束，之后再设置为1，对数据库大规模表数据导入有帮助
SET foreign_key_checks = 0;

-- 可以为除NOT NULL以外的约束取名，上方表定义可转换为
CREATE TABLE IF NOT EXISTS `users` (
    id INTEGER AUTO_INCREMENT COMMENT 'id，主键，自增',
    name VARCHAR(55) NOT NULL COMMENT '姓名，不为空',
    email VARCHAR(50) COMMENT '邮箱，必须包含@',
    phone VARCHAR(50) NOT NULL COMMENT '电话号码，唯一且不为空',
    status CHAR(1) DEFAULT '1' COMMENT '状态，默认为1',
    gender CHAR(1) COMMENT '性别',
    entry_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '加入时间，默认为插入行的时间',
    department_id INTEGER COMMENT '外键，连接部门表',
    
    -- 约束部分，[CPMSTRAINT 约束名称]是可选的，可以不提供名称，使用默认命名规则
    CONSTRAINT `pk_users_id` PRIMARY KEY (id),  -- 主键约束命名
    CONSTRAINT `chk_email_format` CHECK (email LIKE '%@%'), -- 邮箱必须包含@
    CONSTRAINT `uc_phone` UNIQUE (phone), -- 电话号码唯一
    CONSTRAINT `fk_department` FOREIGN KEY (department_id) REFERENCES `departments` (id) ON DELETE SET NULL ON UPDATE CASCADE -- 外键约束
);

  • 额外标识

    • 数字自增AUTO_INCREMENT：可用于数字类型，包括浮点数，常用于主键，表示每次提供一个会自动增加1的默认值
    • 生成列GENERATED ALWAYS：表示它的值会根据列定义中的表达式自动计算得出。并且，你不能直接写入或更新生成列的值
    MySQL
    create table IF NOT EXISTS `fruit` (
    id INTEGER PRIMARY KEY AUTO_INCREMENT COMMENT 'id，主键，自增',
    name VARCHAR(255) NOT NULL COMMENT '水果名称',
    price FLOAT NOT NULL CHECK ( price > cost ) COMMENT '水果价格，不为空，大于成本',
    cost FLOAT NOT NULL CHECK ( cost > 0 ) COMMENT '成本，不为空，大于0',
    -- 利润列自动计算得到
    -- 格式为[GENERATED ALWAYS] AS (expr) [VIRTUAL | STORED]
    -- * VIRTUAL意思是此列不存储在实际的表中，而是在查询时生成
    -- * STORED的意思是此列存储在实际的表中，在插入和修改时储存
    -- 如果数据经常发生变动，请考虑使用虚拟生成列
    -- 如果数据在创建后不经常变动，请考虑使用存储生成列
    profit FLOAT GENERATED ALWAYS AS (price - cost) STORED COMMENT '利润'
);

  • 从查询创建表

    MySQL
    -- 创建来自查询的表，类型自动推断，列名称继承自查询
-- 之后需要手动添加主键和外键等字段约束信息
CREATE TABLE new_table AS
SELECT * FROM old_table;

-- 也支持显式定义列
CREATE TABLE employee_summary (
  emp_id INT,
  full_name VARCHAR(200),
  annual_salary DECIMAL(10,2)
) AS 
SELECT 
    employee_id,
    CONCAT(first_name, ' ', last_name),
    salary * 12
FROM employees;

-- 拷贝表结构
-- 拷贝一模一样的表结构，不拷贝数据
-- 也不保留外键和 check 约束
CREATE TABLE new_table LIKE old_table;

修改表信息

  • 在使用表时，如果希望对表的定义进行修改，可以使用修改表操作ALTER，需要修改的表信息可以是：

    • ADD关键字可用来添加列、索引、约束等，包括：

      • ADD [COLUMN]: 添加列
      • ADD INDEX: 添加索引
      • ADD PRIMARY KEY: 添加主键
      • ADD FOREIGN KEY: 添加外键
      • ADD UNIQUE INDEX: 添加唯一索引
      • ADD CHECK: 添加检查约束
      -- ADD语法，MySQL/PostgreSQL/Oracle/SQLServer都支持
-- * 添加列
ALTER TABLE table_name ADD column_name data_type;
-- * 添加索引
ALTER TABLE table_name ADD INDEX index_name (column_name);
-- * 添加主键 CONSTRAINT 主键名可省略
ALTER TABLE table_name ADD CONSTRAINT primary_key_name PRIMARY KEY (column_name);
-- * 添加外键 CONSTRAINT 外键名可省略
ALTER TABLE table_name ADD CONSTRAINT foreign_key_name FOREIGN KEY (column_name) REFERENCES referenced_table_name (referenced_column_name);
-- * 添加check约束
ALTER TABLE table_name ADD CHECK (condition);

    • DROP关键字可用来删除列、索引、约束等，包括：

      • DROP [COLUMN]: 删除列
      • DROP INDEX: 删除索引
      • DROP PRIMARY KEY: 删除主键
      • DROP FOREIGN KEY: 删除外键
      • DROP CHECK: 删除检查约束
      -- DROP语法，MySQL/PostgreSQL/Oracle/SQLServer都支持
-- * 删除列
ALTER TABLE table_name DROP COLUMN column_name;
-- * 删除索引
ALTER TABLE table_name DROP INDEX index_name;
-- * 删除主键
ALTER TABLE table_name DROP PRIMARY KEY;
-- * 删除外键
ALTER TABLE table_name DROP FOREIGN KEY foreign_key_name;
-- * 删除check约束
ALTER TABLE table_name DROP CONSTRAINT constraint_name;

    • MODIFY/ALTER关键字修改列的定义。与CHANGE关键字不同，它不能重命名列

    MySQL
    -- 修改列定义
ALTER TABLE table_name MODIFY column_name new_data_type [约束] [额外标识] [COMMENT '注释信息'];
-- 比如:
ALTER TABLE table_name MODIFY column_name int primary key COMMENT '注释信息';
    • CHANGE关键字用来修改列的定义。与MODIFY关键字不同，它可以重命名列，是MySQL的特有语法
    -- 使用时必须附带修改列名
ALTER TABLE table_name CHANGE old_column_name new_column_name new_data_type [约束];
    • RENAME关键字可以重命名列、索引和表。包括：
      • RENAME COLUMN: 重命名列
      • RENAME INDEX: 重命名索引
      • RENAME TABLE: 重命名表
    MySQL
    -- 重命名列
ALTER TABLE table_name RENAME COLUMN column_name TO new_column_name;
-- 重命名索引
ALTER TABLE table_name rename INDEX old_index_name TO new_index_name;
-- 重命名表
ALTER TABLE old_table_name RENAME TO new_table_name;
    • 直接修改某些表配置
    MySQL
    -- 直接使用 key=value 修改表配置
ALTER TABLE table_name ENGINE = engine_name;

删除表

  • DROP：如果某一张表不需要了，可以选择将表删除

    删除一张表将永久删除表和表中的数据，请谨慎操作

    MySQL
    -- 支持判断是否存在再删除，防止报错，也支持删除多张表
DROP TABLE [IF EXISTS] table_name [, table_name] ...;
-- 删除多张表操作在MySQL8以下版本不是原子的，会依次删除这些表，直到结束或出错

其他数据库定义语言

  • 重命名表：有时，可能要求修改数据库的表名，数据库提供了重命名的语句，但是要注意，这会对使用旧表名的语句产生影响，可能对以下内容产生影响，根据数据库和版本可能不同，需要进行确认

    • 使用了此表的视图
    • 使用了此表的函数
    • 使用了此表的触发器
    • 使用了此表的外键约束
    • 使用了此表的存储过程
    • 使用了此表的应用程序
    MySQL
    -- 支持多表同时重命名，但不可对临时表重命名
RENAME TABLE old_table_name TO new_table_name, old_table_name2 TO new_table_name2;

-- 针对单个表也可以使用ALTER，支持临时表
ALTER TABLE old_table_name RENAME TO new_table_name;

-- 临时表是使用CREATE TAMPORARY TABLE定义的表
-- 只在创建它的会话中可见，其他会话无法访问该临时表
-- 当会话结束时，临时表会自动被删除，不需要手动删除
-- 通常存储在内存中(可配置)，与普通表命名空间独立
-- 可以避免不同会话污染
-- 临时表也支持事务和索引，但不支持外键

  • 清空表：清空表操作在大部分数据库中有专门的语句完成，要求有删除权限，因为他们都直接重置表的存储空间，只产生非常少的日志，速度非常快，相当于先将此表删除掉，再创建一个新表，无法回滚，需要谨慎操作，和删除所有条目语句相比：

    • 属于DDL，而不是DML，无法被回滚
    • 如果表被其他表通过外键引用，无法执行清空操作
    • 不会触发DELETE触发器，且不返回删除的行数
    • 会重置表的自增值
    MySQL
    TRUNCATE [TABLE] table_name;

2.3 数据操作语言DML

  • 数据操作语言是指用来增加、删除、修改表中数据的语句，也就是INSERT、DELETE、UPDATE操作

    • INSERT操作：向一张表中添加数据
    MySQL
    -- 基本语法，VALUES也可以写成VALUE，但规范语法是VALUES
--如果提供的值比声明要填充的列少，将填充NULL
INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);

-- 示例：
INSERT INTO department(name, level) VALUES ('research department', 1);

-- 完整语法
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...)[ ON DUPLICATE KEY UPDATE ...];

-- 支持插入多行，效率更高
INSERT INTO table_name (column_1, column_2, ...)
VALUES (value_11, value_12, ...),
       (value_21, value_22, ...)
       ...;

-- 支持插入查询结果
-- 需要确保字段类型可存入，也就是要保证被插入表的字段类型范围比查询的原表对应字段大
-- 插入的值和声明要填充的列是一一对应关系
INSERT INTO table_name (column1, column2, ...) 
-- 直接写查询语句
SELECT c1, c2, ...
FROM table_name2 ...;

-- 如果是插入所有字段，也可以不声明字段名，根据建表时的顺序依次填入
INSERT INTO table_name VALUES (value1, value2, ...);

-- 支持修饰符
-- LOW_PRIORITY：服务器将延迟执行 INSERT 操作直到没有客户端对表进行读操作
-- HIGH_PRIORITY：覆盖掉服务器启动时的 --low-priority-updates 选项
-- LOW_PRIORITY 和 HIGH_PRIORITY 对那些只支持表级锁的存储引擎的执行速度影响较大
-- 默认情况下插入、修改、删除的优先级比查询的优先级更高
-- IGNORE：忽略那些可忽略的插入错误，忽略了的错误将以warning的形式显示
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);

-- 提供了没有对应主键插入，有对应主键就更新的操作(显式提供主键)
-- ON DUPLICATE KEY UPDATE
-- 比如：在水果表中id为主键
INSERT INTO fruit(`id`, `name`) VALUES (5, 'apple') ON DUPLICATE KEY UPDATE name='apple';
-- 如果存在id为5的行，修改name为apple
-- 如果不存在，添加行(5, 'apple')

-- REPLACE 语句：是MySQL中的一个关键字，语法与INSERT相同
-- 可以实现和ON DUPLICATE KEY UPDATE类似的效果
-- 如果有重复主键造成添加失败时会先删除旧行，再插入新行
-- REPLACE 要求有插入和删除的权限
REPLACE INTO user (id, name, age) VALUES (1, "李四", 18);
    • UPDATE操作：对筛选到的满足条件的行统一修改
    MySQL
    -- 基本语法
-- where条件是可选的，如果不筛选是对所有行做处理
-- 可以设置SET sql_safe_updates = 1;要求必须使用条件判断筛选修改
UPDATE table_name SET column_name1 = value1, ..., [WHERE 条件子句];

-- 示例：
UPDATE users SET name = '李四', price = price * 1.2 WHERE id = 1;

-- 完整语法
UPDATE [LOW_PRIORITY] [IGNORE] table_name
SET column_name1 = value1, column_name2 = value2, ...
[WHERE 条件子句] [ORDER BY ...] [LIMIT 最大影响行数];

-- 支持修饰符
-- LOW_PRIORITY：延迟更新操作，直到没有客户端对表进行读操作，对那些只支持表级锁的存储引擎的执行速度影响较大
-- IGNORE：忽略那些可忽略的更新错误，这些错误将以warning的形式显示
UPDATE LOW_PRIORITY IGNORE users SET create_time = '2008-01-01'
WHERE status = '1' ORDER BY id LIMIT 10;

-- 支持限制影响的条数
UPDATE users SET status = '1' WHERE status = '0' LIMIT 10;
    • DELETE操作：删除所有筛选到的满足条件的行
    MySQL
    -- 基本语句
DELETE FROM table_name [WHERE 条件子句];

-- 示例：
DELETE FROM student WHERE status <> '1';

-- 完整语法
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM table_name
[WHERE 条件子句] [ORDER BY ...] [LIMIT 最大影响行数];

-- 支持多表同时删除
-- 此时ORDER BY和LIMIT无法使用
-- 同时删除两个表的满足条件的行
DELETE t1, t2
FROM t1 INNER JOIN t2
WHERE t1.id = t2.id;

-- 支持修饰符
-- LOW_PRIORITY：延迟更新操作，直到没有客户端对表进行读操作，对那些只支持表级锁的存储引擎的执行速度影响较大
-- QUICK：MyISAM 存储引擎不会在 DELETE 操作期间合并索引。这在某种程度上会加快 DELETE 操作
-- IGNORE：忽略那些可忽略的更新错误，这些错误将以warning的形式显示
DELETE LOW_PRIORITY QUICK IGNORE FROM student
WHERE id = 1;

2.4 数据查询语言DQL

  • 数据查询语言一般指SELECT查询语句

2.4.1 查询语句的基本结构

  • 查询语句的基本结构如下

    MySQL
    -- 按次序写下
SELECT [ALL|DISTINCT] 查询项列表 -- ALL指不去重复查询结果(默认)，DISTINCT是去重复
FROM 表名、子查询或视图名列表 -- 可以添加多个表、查询、视图进行连接
WHERE 条件表达式 -- 支持各种查询谓词，不能含有聚合函数
-- GROUP BY只有用于分组的属性和函数有意义,即查询的属性列表只有这些列和聚合函数才有意义
GROUP BY 列名组 [WITH ROLLUP|CUBE] -- 按某些列的取值分组,有相同取值的为一组
HAVING 条件表达式 -- 对数据筛选,一般只写聚合函数
ORDER BY 列名 [ASC|DESC], ... -- 默认升序(ASC),DESC降序
LIMIT 条数 OFFSET 起始行; -- 行的索引从0开始

  • 查询语句的逻辑执行顺序如下，正常的执行顺序会受查询优化器的优化策略影响，有可能是先排序再筛选，有可能先筛选再连接，也可能修改部分筛选的顺序

    • FROM：指明查询哪些表，可以取表别名，但取了别名后必须使用别名访问。别名可用于之后执行的部分，在这里是所有语句都可以使用表别名
    • 多表连接过滤条件ON语句：进行第一次过滤
    • 连接方式JOIN：根据连接方式补充需要保留表的被过滤条件过滤掉的数据，非保留表中的数据被赋予NULL值
    • 筛选器WHERE：根据指定的筛选条件进行筛选
    • GROUP BY：将相同的查询结果行合并为一组，如果使用此语句，一般需要搭配聚合函数使用，聚合函数结果在这步之后立即计算，如果还有WITH子句，在聚合函数计算完成后计算，在这一步开始，后面的语句可以使用SELECT中的别名
    • HAVING：对聚合完成的数据进一步筛选，在这一步才能对聚合结果进行过滤，一般也只在使用GROUP BY的情况下使用
    • SELECT：对查询列进行筛选，选出指定列
    • DISTINCT：筛选结果进一步去重
    • ORDER BY：修改结果行的排序
    • LIMIT/OFFSET：从某个位置开始返回指定数量的行，部分数据库不使用专门的LIMIT语法，但执行顺序相同

    每一步操作都是得到一张虚拟的表

2.4.2 基本查询

  • 一个最基本的查询：SELECT 字段1, 字段2, ... FROM 表名

    • 这个查询可以不依赖任何表的数据，只完成简单的数据库配置、函数或表达式计算
    -- 在Oracle中最早使用了DUAL表，它是一个只有一行一列的伪表
-- 可以用在返回不依赖表的查询中，MySQL也支持这种写法
SELECT 1 + 1, 2 * 3, 'abc' FROM DUAL;

-- PostgreSQL和SQLServer中没有这个表，但支持直接查询，MySQL也支持这种写法
SELECT 1 + 1， 2 * 3, 'abc';
    • 可以使用*代替字段名声明，表示查询一个表的所有字段
    • 可以对字段指定别名alias，最终查找结果集中对应列使用别名命名
    -- 别名可以使用as标识，也可以省略标识
-- 别名可以使用""指定，这样别名就可以使用空格等字符了
SELECT name as student_name, id "student id" FROM student;
    • 可以使用SELECT DISTINCT去重复结果
    • 可以使用UNION将两个查询结果合并在一起，默认去重，如果希望保留重复项使用UNION ALL
    SELECT * FROM a
UNION
SELECT * FROM b;

-- 保留重复
SELECT * FROM a
UNION ALL
SELECT * FROM b;

  • 使用WHERE过滤数据：WHERE子句必须紧跟在FROM子句的后面，并且不应该包含聚合函数，可以使用查询谓词和关键字。常用的查询谓词和关键字如下，需要注意的是：

    • 规范写明使用<>表示不等于，部分数据库支持使用!=表示不等于
    • 如果有NULL值参与算术运算，结果为NULL；NULL值不参与数值比较，如果使用<等过滤，NULL会被过滤掉
    • LIKE支持的通配符匹配规则包括
      • %匹配0或多个任意字符
      • _匹配单个任意字符
      • []匹配其中任意单个字符，比如匹配AKM字符中的单个字符[AKM]
      • [^]或[!]匹配除提供的字符外的单个字符，比如匹配除CDE外的其他单个字符[!CDE]
    • 逻辑运算符优先级从高到低排序为NOT(!), AND(&&), XOR, OR(||)，如果希望改变优先级顺序，可以使用()包裹提高优先级
    • EXISTS用于根据之后跟着的子查询进行判断，当该行在子查询中找到配对结果则返回TRUE，否则返回FALSE
      • IN和EXISTS的区别：如果子查询得出的结果集记录较少，主查询中的表较大且又有索引时应该用IN；反之如果外层的主查询记录较少，子查询中的表大且又有索引时使用EXISTS。
      • 两种方法驱动顺序的不一样：如果是EXISTS，那么以外层表为驱动表，先被访问；如果是IN，那么先执行子查询
    • 对于多行子查询，除了IN，还支持使用ALL、ANY(SOME)表示比其中任意一个都、比其中至少某一个，比如salary > ANY (100, 200)
    常用的查询谓词, 关键字描述示例
    =, <>, >, <, >=, <=比较运算符id=10
    +, -, *, /, MOD算术运算符price=price*0.9
    [NOT] BETWEEN ... AND位于两值之间, 相当于同时使用>=, <=salary BETWEEN 3000 AND 5000
    [NOT] IN判断是否在集合其中, 集合可以通过查询创建level IN (1, 2, 3)
    [NOT] LIKE使用通配符进行字符匹配, 支持 ESCAPE 设置转义符name LIKE '[ALZ]%_\%%' ESCAPE '\'
    IS [NOT] NULL判断值是否为空deparement_id IS NULL
    OR, AND, NOT, XOR交并反异或逻辑运算符id=1 AND name='李明'
    &, |, ^, ~, <<, >>代表与, 或, 异或, 非, 移位的位运算符SELECT 12&5, 12|5, ^5;
    [NOT] EXISTS根据子查询结果对单行返回true或falseSELECT * FROM a WHERE EXISTS(SELECT 1 FROM b WHERE a.b_id = b.id);

2.4.3 多表查询

  • 多表查询是通过在查询中添加多个表名实现的，有以下几种情况

    • 交叉连接：对所有表的行进行拼接
    • 等值连接和非等值连接：在交叉连接基础上增加筛选，只展示想要的结果
    • 自连接：查询同一张表多次，拼接在一起
    • 内连接和外连接：基于列的外键关系完成筛选，只对有关系的行进行连接
    • 自然连接：SQL99中引入的连接方式，自动找到两个表中字段名称相同的列，以这些字段都相等作为条件进行等值连接
    • USING连接：SQL99中引入的连接方式，指定表中的同名字段进行等值连接

    进行多表查询时，表数量不宜过多，连接表的数量对查询效率有很大影响，关联表的字段类型要相同，最好保证被关联查询的字段有索引

  • 通过笛卡尔积交叉连接CROSS JOIN：这是直接罗列不同的表时使用的连接方式，比如对a, b两个表进行查询

    • 会对a表的每一行和b表的每一行做笛卡尔积，形成一个新的表
    • 笛卡尔积是一个数学运算，在这里为a表的行组成的集合A和b表的行组成的集合B做笛卡尔积，A中行和B中行的所有拼接组合即为结果，新集合的元素数量为两个集合的数量乘积
    笛卡尔积
    笛卡尔积
    SELECT name, department.id from employees, department;

  • 等值连接与非等值连接：通过值的判断关系，对笛卡尔积结果进行筛选，得到筛选后的表

    -- 等值连接：展示每个员工所在部门的名称
SELECT e.name, d.name
FROM employees e, department d
WHERE e.department_id = d.id;

-- 非等值连接：根据工资等级范围表展示每个员工的工资等级
SELECT e.name, j.level
FROM employees e, job_level j
WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal;

  • 自连接：对同一张表进行多表查询，连接在一起

    -- 查询员工和员工的上司信息
SELECT e1.id employee_id, e1.name employee_name, e2.id manager_id, e2.name manager_name
FROM employees e1, employees e2
WHERE e1.manager_id = e2.id;

  • 内连接INNER JOIN与外连接OUTER JOIN：合并具有同一列相同的值的两个以上表的行，几种内外连接区别如下

    • 外连接：主表中不满足条件的行会展示，对应行没有匹配的部分为null

      一般，在左外连接中在连接条件左边的表称为主表(驱动表)，另一边的表称为从表(被驱动表)；同理，在右外连接中在连接条件右边的表称为主表，另一边的表称为从表。同时，优化器可能修改外连接方式

    • 内连接：结果集中不包含两个表中没有匹配上的行，内连接中主表由优化器决定
    • 全连接：两个表不满足条件的行都会展示，没有匹配的部分为null

    关联查询原理

    在数据库发展的早期，比如MySQL 5.5之前，只支持嵌套查询Nested Loop Join

    • 嵌套查询虽然有很多变种，但核心都是从主表中取出一条数据，查看从表中是否有匹配的行，可能是直接查询、索引查询和主表批量对从表的存储块查询
    • hash join是现在许多数据库首选的进行等值连接查询的查询方法，MySQL 8.0.18加入，通过将驱动表数据缓存到内存中，使用哈希映射快速判断被驱动表中每一行在驱动表中是否存在对应的行，从而提高查询效率
    • 一般数据规模小的表作为从表，比如数据规模较大、有索引的表，减少查询成本
    内外连接示意图
    内外连接示意图
    -- SQL92标准，不推荐使用
-- * 内连接，通过等值连接完成
SELECT e.name, d.name
FROM employees e, department d
WHERE e.department_id = d.id;
-- * 外连接使用(+)标识哪个表是从表
-- Oracle等数据库还支持，但包括MySQL在内的一些数据库不支持此写法
SELECT e.name, d.name
FROM employees e, department d
WHERE e.department_id(+) = d.id;
-- * 全连接：在SQL92中不被支持

-- SQL99标准
-- * 内连接 [INNER] JOIN ... ON
SELECT e.name, d.name, l.city
FROM employees e JOIN department d ON e.department_id = d.id
JOIN `location` l ON d.location_id = l.id;
-- * 左外连接 LEFT [OUTER] JOIN ... ON
SELECT e.name, d.name
FROM employees e LEFT JOIN department d ON e.department_id = d.id;
-- * 右外连接 RIGHT [OUTER] JOIN ... ON
SELECT e.name, d.name
FROM employees e RIGHT JOIN department d ON e.department_id = d.id;
-- * 全连接 FULL OUTER JOIN ... ON
-- MySQL不支持SQL99全连接，考虑使用UNION并上左右外连接的查询实现
SELECT e.name, d.name
FROM employees e FULL JOIN department d ON e.department_id = d.id;

  • 自然连接和USING连接：通过自动查询或给出两个表的相同名称字段的方式，快速创建等值连接，一般不使用

    -- 等值内连接
SELECT employee_id, name, deparement_name
FROM employees e JOIN deparement d
ON e.deparement_id = d.deparement_id
AND e.manager_id = d.manager_id;

-- 自动寻找deparement_id和manager_id两个相同名称的字段
SELECT employee_id, name, deparement_name
FROM employees e NATURAL JOIN deparement d;

-- 给出deparement_id和manager_id，以此创建等值连接
SELECT employee_id, name, deparement_name
FROM employees e JOIN deparement d USING(deparement_id, manager_id);

2.4.4 聚合查询

  • 聚合查询：通过分组对数据进行汇总

  • 聚合函数：作用于一组数据，并对一组数据返回一个值，比如统计平均值、最大值、最小值、总和，甚至是结果行数

    MySQL
    -- 查询salary平均值、最大值、最小值、总和，只能用于数值类型上
SELECT AVG(salary), MAX(salary), MIN(salary), SUM(salary)
FROM employees;

-- 查询行数
-- 可以使用常数或字段名, 针对单独字段名的查询可能得到字段为非空的行数
-- 如果需要所有行数，使用COUNT(1)或COUNT(*)，如果能使用二级索引统计，在InnoDB速度会更快，优化器也回自动优化
SELECT COUNT(*) FROM employees;

-- 其他聚合函数
-- * 按位运算：与BIT_ADD(expr), 或BIT_OR(expr), 异或BIT_exprOR(expr)
-- * 拼接字符串：expr是要拼接的字段名, 可选去重, 连接内容排序方法, 分隔符(默认为,)
-- GROUP_CONCAT([DISTINCT] expr1 [, expr2 ...] [ORDER BY ...][SEPARATOR separator])
-- * JSON结果生成：生成数组JSON_ARRAYAGG(expr), 生成对象JSON_OBJECTAGG(k_expr, v_expr)
-- * 数学：总体标准差和样本标准差的公式不同
-- 总体标准差STDDEV_POP(expr), 样本标准差STDDEV_SAMP(expr), 样本应该是指
-- 总体方差VAR_POP(expr), 样本方差VAR_SAMP(expr)

    聚合函数和聚合函数的返回值

    聚合函数支持使用表达式作为参数，可以不处理每行的数据，而是通过流程控制等方法根据每行的情况添加不同的数据项，查询结果一般会出现下列情况

    • 如果没查到数据，函数的值为null
    • 如果查到数据或输入正确表达式，那么函数的值默认为所有输入非空值的统计结果

  • 分组GROUP BY：对数据进行分组，分组之后，默认展示的是分组第一行的数据，一般是聚合函数结果

    • 一般情况下，SELECT中声明的非聚合函数字段必须出现在GROUP BY中
    • 可以通过多个字段进行多级分组，但过多级分组效率不高
    • 分组可以进行汇总统计WITH ROLLUP，但汇总统计可能和ORDER BY子句冲突(与数据库和版本有关)
    -- 基本分组
SELECT department_id, job_id, AVG(salary) avg_sal
FROM users
GROUP BY deparement_id, job_id DESC;

-- 结果汇总统计, 在最后会出现一个汇总行, 统计了所有行的结果
-- 与ORDER BY互斥, 不能一起使用
-- MySQL、PostgreSQL、Oracle、SQLServer都支持
SELECT department_id, job_id, AVG(salary) avg_sal
FROM users
GROUP BY deparement_id, job_id DESC WITH ROLLUP;

-- PostgreSQL、Oracle、SQLServer支持额外汇总, MySQL暂不支持
-- 比如对department_id进行汇总, 对job_id进行汇总, 对所有结果进行汇总
SELECT department_id, job_id, AVG(salary) avg_sal
FROM users
GROUP BY deparement_id, job_id DESC WITH ROLLUP;

  • 分组后筛选HAVING：在分组之后，内容已经聚合在一起,可以进一步筛选

    • HAVING的适用范围更广，WHERE只能使用字段名筛选，HAVING中还能使用聚合函数
    • WHERE筛选更靠前，查询效率更高，在HAVING一般只写对聚合函数的筛选，其他筛选一般都可以通过WHERE完成
    SELECT department_id, job_id, AVG(salary) avg_sal
FROM users
GROUP BY deparement_id, job_id DESC
HAVING avg_sal >= 1000;

2.4.5 分页查询

  • 排序：默认情况下，查询得到的数据是按添加数据的顺序显示的，如果希望查询排序，需要使用ORDER BY子句，有两种排序方式，升序ASC和降序DESC，分别是ascend和descend的缩写

    -- 按 salary 进行排序
SELECT salary, deparement_id
FROM employees
ORDER BY salary ASC;

-- 多级排序：按多列排序，优先对第一个列进行排序，之后处理之后的每一列排序
SELECT e.id, e.name, salary, deparement_id, d.name
FROM employees AS e, JOIN deparement AS d ON e.deparement_id = d.id
ORDER BY salary, deparement_id DESC;

  • 分页：可以使用LIMIT实现分页查询，LIMIT 本页条数 OFFSET 前置条数

    • 一般情况下，页面条数是相同的，因此假如需要返回第page_num页每页page_size条数据，可以写成LIMIT page_size OFFSET (page_num - 1)*page_size
    • 不同数据库的LIMIT实现关键字和放置位置可能不同，MySQL、PostgreSQL、SQLite都可以使用LIMIT/OFFSET的写法，Oracle和SQLServer不支持这种写法
    MySQL
    SELECT id, name, salary, deparement_id
FROM employees
-- 默认索引 OFFSET 从 0 开始
-- MySQL还支持LIMIT 10 OFFSET 20写法
LIMIT 20, 10;

SELECT id, name, salary, deparement_id
FROM employees
-- 可以不写OFFSET，也就是从 0 开始
-- 返回10条数据
LIMIT 10;

    分页可以帮助DBMS知道查询最终需要多少条数据，减少数据表的网络传输量，也可以提升查询效率

2.5 数据控制语言DCL

2.5.1 用户管理

  • 用户是数据库认证的基本元素，用于连接数据库，每个用户可以分别授予不同的权限

  • 创建用户

    MySQL
    -- 用户名参数表示新建用户的账户，由用户名User和主机名Host构成，中间用 @ 连接
-- 如果username和hostname中包含空格和-等特殊字符，则需要'username'@'hostname'
-- 比如: admin@127.0.0.1，主机名可以省略，等效于%，表示任意主机都可以
-- 此语句只创建没有任何权限的用户，支持一次创建多个用户
-- 用户@%的优先级低于用户名@特定ip，不允许有相同用户名@主机名的用户
CREATE USER [IF NOT EXISTS] 用户
  [IDENTIFIED [WITH 身份验证插件] BY '密码'];

-- 身份验证(AUTHENTICATION)插件一般有：
-- * caching_sha2_password：
-- MySQL 8的默认身份验证插件，使用SHA-256算法，并在服务器端缓存验证信息提高性能
-- * mysql_native_password：
-- MySQL 5.7及之前的默认插件，使用SHA-1算法，在8.0.34已被弃用
-- * auth_socket：
-- 通过Unix套接字文件验证从本地连接的客户端用户身份，无需输入密码，无法远程登录
-- * authentication_webauthn：
-- MySQL 8.2.0引入的新插件，允许使用FIDO U2F/FIDO2兼容设备进行多因素身份验证

  • 修改用户

    MySQL
    -- 修改用户名为王五的条目，修改为李四
UPDATE mysql.user SET User='li4' WHERE User='wang5';

-- 修改密码,支持用USER()指定当前用户
ALTER USER 用户 IDENTIFIED BY 'new_password';
-- 也可以，如果是MySQL5.7版本需要使用PASSWORD()加密
SET PASSWORD = 'Db123654';
SET PASSWORD FOR 'li4' = 'Db123654';
-- 也可以(不推荐),PASSWORD()函数在MySQL8.0已经弃用
UPDATE mysql.user
SET password = PASSWORD('Db123654')
WHERE User = 'li4' AND
      Host = '%';

-- 刷新权限
-- 刷新不会改变已连接的用户连接，修改用户名、密码，这些连接还可以使用
FLUSH PRIVILEGES;

  • 删除用户

    MySQL
    -- 根据用户名或username@hostname删除用户
-- 默认删除用户名@%，表示删除所有主机上对应用户
DROP USER user[,user];
-- 也可以，但会有其他表残留信息
DELETE FROM mysql.user WHERE Host='hostname' AND User='username';

FLUSH PRIVILEGES;

  • 设置密码更新策略

    MySQL
    -- 支持设置密码更新策略
ALTER USER 用户 PASSWORD EXPIRE [NEVER | INTERVAL 时间 | DEFAULT]; -- 过期时间
ALTER USER 用户 PASSWORD HISTORY 数字; -- 不能选择最近使用过的多少个密码
ALTER USER 用户 PASSWORD REUSE 数字 DAY; -- 不能选择最近多长时间内的密码

-- 也可以设置全局更新策略，其中PERSIST表示持久设置，默认GLOBAL系统重启后失效
SET PERSIST default_password_lifetime = 180; -- 建立全局策略，设置密码每隔180天过期
SET PERSIST password_history = 6; -- 设置不能选择最近使用过的6个密码
SET PERSIST password_reuse_interval = 365; -- 设置不能选择最近一年内的密码

2.5.2 权限管理

  • 查看权限

    MySQL
    -- 展示当前用户的权限
SHOW grants\G
SHOW GRANTS FOR CURRENT_USER;

-- 展示某个用户的权限
SHOW GRANTS FOR 用户;

-- 查看所有的权限，也可以通过mysql.user有哪些权限字段，知道支持的权限
show privileges;
-- CREATE和DROP权限 可以创建新的数据库和表，或删除已有的数据库和表
-- SELECT、INSERT、UPDATE和DELETE权限 允许在一个数据库现有的表上实施操作
-- SELECT权限 只有在它们真正从一个表中检索行时才被用到
-- INDEX权限 允许创建或删除索引(用于已有的表)
-- ALTER权限 可以使用ALTER TABLE来更改表的结构和重新命名表
-- CREATE ROUTINE权限 用来创建保存的程序，ALTER ROUTINE权限用来修改程序，EXECUTE权限 用来执行程序
-- GRANT权限 允许授权给其他用户
-- FILE权限 使用户可以使用LOAD DATA INFILE和SELECT ... INTO OUTFILE语句读或写数据库中的任何文件

  • 授予权限

    MySQL
    -- 授予权限
GRANT 权限1,权限2,...,权限n ON 数据库名称.表名称 TO 用户 [IDENTIFIED BY '密码'] [WITH CHECK OPTION];

-- 如果不存在用户将进行创建
-- 支持授予所有权限
-- 支持使用*表示权限用于所有表
GRANT ALL PRIVILEGES ON *.* TO joe@'%' IDENTIFIED BY '123';

  • 收回权限

    MySQL
    REVOKE 权限1,权限2,…权限n ON 数据库名称.表名称 FROM 用户;

  • 授权检查的过程

    MySQL
    • 要了解如何进行授权检查，首先要了解的是这些权限是怎么储存的，在MySQL系统表中，有
      • user表：记录用户的账号和权限信息，比如：范围列(用户列)存储用户和密码信息；权限列，包含Grant_priv字段(能否授权)、Shutdown_priv字段(能否关闭数据库服务)、Super_priv字段(是否是超级管理员)、Execute_priv字段(能否执行存储过程和函数)和其他全局字段；安全列用于ssh加密和用户验证身份；资源控制列控制用户的最大操作次数
      • db表：通过三个字段user、host、Db表示从某个用户连接能否使用指定数据库，还有一些权限字段，比如Create_routine_priv和Alter_routine_priv表示能否创建和修改存储过程
      • table_priv表：记录不同用户的表操作权限
      • column_priv表：记录不同用户的列操作权限
      • process_priv表：记录不同用户的存储过程或函数操作权限
    • 授权检查过程
      • 连接核实阶段：用户会在连接请求中提供用户名、主机地址、用户密码，MySQL服务器接收到用户请求后，会使用user表中的host、user和authentication_string这3个字段匹配客户端提供信息。如果连接核实没有通过，服务器就完全拒绝访问；否则，服务器接受连接，然后进入阶段2等待用户请求
      • 请求核实阶段：对连接上的所有请求，判断请求要执行什么操作、是否有足够的权限，首先将检查user表，如果对应权限有被授予则执行，否则检查db表，如果找到权限则执行，如果找不到对应权限，再依次检查table_priv表，column_priv表是否能找到权限，决定请求能否执行，如果未在这些表内找到对应权限，拒绝执行，返回错误信息

2.5.3 角色管理

  • 引入角色的目的是方便管理拥有相同权限的用户。恰当的权限设定，可以确保数据的安全性，这是至关重要的

  • 创建、授权、撤销权限和删除用户角色的语法如下

    MySQL
    -- 创建角色
CREATE ROLE 'role_name'[@'host_name'] [,'role_name'[@'host_name']];

-- 授权
GRANT 权限1,权限2,...,权限n ON 数据库名称.表名称 TO 'role_name'[@'host_name'];

-- 设置强制角色，强制角色对所有用户有效，且无法撤销权限和删除
-- 可以在服务启动前设置
-- [mysqld]
-- mandatory_roles='role1,role2@localhost,r3@%.atguigu.com'
-- 也可以通过命令，PERSIST表示永久有效，可以改为GLOBAL表示重新启动前有效
SET PERSIST mandatory_roles = 'role1,role2@localhost,r3@%.example.com';


-- 撤销权限
REVOKE 权限1,权限2,…权限n ON 数据库名称.表名称 FROM 'role_name'[@'host_name'];

-- 删除角色
DROP ROLE 'role_name'[@'host_name'] [,'role_name'[@'host_name']];

  • 为用户分配、撤销角色

    MySQL
    -- 分配角色
GRANT 'role_name'[@'host_name'] [,'role_name'[@'host_name']]
TO 'user_name'[@'host_name'] [,'user_name'[@'host_name']];

-- 撤销角色
REVOKE 'role_name'[@'host_name'] [,'role_name'[@'host_name']]
FROM 'user_name'[@'host_name'] [,'user_name'[@'host_name']];

-- 激活角色
-- 默认创建的角色是未激活的，需要激活才能生效，支持ALL指代所有角色
SET DEFAULT ROLE 'role_name'[@'host_name'] TO 'user_name'[@'host_name'];
-- 设置所有角色永久激活变量
SET GLOBAL activate_all_roles_on_login=ON;

-- 查看目前用户激活了的角色
SELECT CURRENT_ROLES();

2.6 其他

2.6.1 表维护

  • 一些数据库附带表维护语句，定期的维护数据表是一个很好的习惯。对提高数据库的性能很有帮助

    MySQL
    -- 分析表
ANALYZE TABLE table_name, ...;
-- 将展示这些表的状态

-- 优化表，对大量更新或删除过的表有效果，缓解空间碎片化，如果没有效果会有提示
OPTIMIZE TABLE table_name;

-- 检查表，如果操作在执行完成前，连接异常关闭，可能影响数据完整性，可以使用此命令检查
CHECK TABLE table_name;

-- 修复表，只支持MyISAM引擎，可以尝试修复检查出来的错误
REPAIR TABLE table_name;
最近更新