MySql命令清单

约定不等于承诺〃 2022-05-25 01:26 175阅读 0赞

一直以来都是使用 MySql 的可视化工具来创建表与数据库，为了增强一下基础知识，花了一点时间把 MySql 又复习了一遍。把一些命令总结了一下，留作以后的查阅，也希望能够为大家提供一些帮助。

#### 一、数据库 ####

# 显示数据库管理系统中所有数据库
    SHOW DATABASES;
    
    # 创建数据库
    DROP DATABASE IF EXITS [库名];
    CREATE DATABASE [数据库名];
    
    # 选择数据库
    USE [数据库名];
    
    # 查看当前正在使用的数据库
    SELECT DATABASE();
    
    # 查看数据库创建语句
    SHOW CREATE DATABASE [数据库名]; 
    
    # 更改数据库编码
    ALTER DATABASE [数据库名] CHARACTER SET [编码格式];
    
    # 删除一个数据库
    DROP DATABASE IF EXITS [数据库名];

#### 二、数据表 ####

# 查看某个数据库中的所有表，通用命令(即：没有选择数据库时也可以使用)
    SHOW TABLES FROM [库名];
    
    # 创建数据表
    DROP TABLE IF EXITS [表名];
    CREATE TABLE test(
        id INT PRIMARY KEY AUTO_INCREMENT ,   
        name VARCHAR(20) NOT NULL ,
        password VARCHAR(20) NOT NULL
    )ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    # 查看数据表结构
    DESC [表名]
    
    # 查看数据表创建语句
    SHOW CREATE TABLE [表名]
    
    # 添加数据列在指定的位置，默认加在最后一列
    ALTER TABLE [表名] ADD COLUMN [列名] [类型] [约束] FIRST | [AFRET] [列名];
    
    # 删除数据列
    ALTER TABLE [表名] DROP COLUMN [列名];
    
    # 更改数据列属性值
    ALTER TABLE [表名] MODIFY [列名] [类型] [约束];
    
    # 修改数据表的名字
    RENAME TABLE [原名] TO [新名];
    
    # 修改数据列的名字
    ALTER TABLE [表名] CHANGE COLUMN [原列名] [更改列名] [类型] [约束];
    
    # 修改数据表字符编码
    ALTER TABLE [表名]  CHARACTER SET [编码];
    
    # 添加外键约束
    ALTER TABLE [表名] ADD CONSTRAINT [外键名] FOREIGN KEY([外键列]) REFERENCES [关联表名]([列名]);
    
    # 表的复制，仅复制表的结构
    CREATE TABLE [复制表名] LIKE [被复制的表名]; 
    
    # 复制表与表中的数据
    # 也可以添加筛选条件，只复制部分列与数据 
    # 也可以用于复制表的结果，只要查询的数据为 'false' 即可
    CREATE TABLE [复制表名] SELECT * FROM [被复制表名];
    
    # 删除数据表
    DROP TABLE [表名];
    
    # 删除主键、唯一、外键
    ALTER TABLE [表名] DROP PRIMARY KEY | INDEX [名称] | FOREIGN KEY [外键名];

#### 三、函数 ####

##### 字符函数 #####

# 获取参数字节数
    LENGTH([参数])
    
    # 字符拼接
    CONCAT ([列名1], [列名2], ...)
    
    # 大小写转换，UPPER 大写,LOWER 小写
    UPPER([参数])          LOWER([参数])
    
    # 字符截取，注意 MySql 的索引是从 1 开始的
    SUBSTR([参数], startIndex)
    SUNSTR([参数], startIndex, subCount)
    
    # 返回自串第一次出现的索引，找不到返回0
    INSTR([原字符], [自串])
    
    # 格式化，默认去除首和尾的空格
    TRIM('[参数]')
    
    # 指定格式化，指定去除参数的首尾字符
    TRIM('[字符]' FROM '[参数]')
    
    # 左右字符填充，使用指定的字符填充原参数，直到指定的长度为止
    LPAD([参数], [长度], [字符])
    
    # 字符替换
    REPLACE([原字符], [被替换的字符], [替换字符])

##### 数学函数 #####

# 四舍五入
    ROUND([浮点数], [保留位数])
    
    # 向上取整
    CEIL([浮点数])
    
    # 向下取整
    FLOOR([浮点数])
    
    # 截取，小数点后保留几位
    TRUNCATE([浮点数], [位数])
    
    # 取余,[a % b = a - a / b * b]
    MOD([a], [b])

##### 日期函数 #####

# 返回系统当前时间：日期 + 时间
    NOW()
    
    # 只返回系统当前日期
    CURDATE()
    
    # 只返回系统当前时间
    CURTIME()
    
    # 返回系统当前月份(数字显示)，其他同理
    MONTH(NOW())
    
    # 返回系统当前是星期几(英文显示)
    DAYNAME(NOW())
    
    # 日期格式转换，可以将字符串转换为对应的日期格式
    STR_TO_DATE('3-4 2000', '%m-%d %Y') -> 2000-03-04
    
    # 日期格式转换，将日期格式转换成字符串
    DATA_FORMAT([日期], [转换格式])

##### 流程控制函数 #####

# IF 条件判断，类似于三目运算符
    IF(10 > 1, 'TRUE', 'FALSE') -> TRUE
    
    # CASE 语句，相当于 switch-case 语句
    CASE [判断的列名或表达式]
    WHEN [常量1] THEN [自定义输出]
    WHEN [常量2] THEN [自定义输出]
    ELSE [自定义输出]
    END;

##### 分组函数 #####

分组函数支持和 `DISTINCT` 一起使用，表示去重后再计算结果。使用方式是直接在列名前面加上 `DISTINCT` 。

# 求和
    SUM([列名])
    
    # 平均值
    AVG([列名])
    
    # 最大最小值
    MIN([列名])
    
    # 非空列名的行数
    COUNT([列名])
    
    # 统计所有有效行数
    COUNT(*)

关于更多的函数请参考：[https://www.w3schools.com/sql/sql\_ref\_mysql.asp][https_www.w3schools.com_sql_sql_ref_mysql.asp]

#### 四、约束 ####

# 非空约束
    NOT NULL
    
    # 默认约束
    DEFAULT
    
    # 唯一约束，允许为空，但只允许一个为空，可以使用多个列组合成唯一约束
    UNIQUE
    
    # 主键约束，不允许为空，一个表中至多有一个
    PRIMARY KEY
    
    # 外键约束
    FOREIGN KEY
    
    # 通用写法
    CREATE TABLE [表名] (
        [列1] [类型] PRIMARY KEY [其他约束 ...],
        [列2] [类型] NOT NULL，
        [列3] [类型] UNIQUE，
        [列4] [类型] DEFAULT [默认值],
        CONSRTAINT [外键名] FOREIGN KEY([当前表的列名]) REFERENCES [关联表]([关联表中的列名])
    );

#### 五、数据查询 ####

# 查询数据表中的总记录数
    SELECT COUNT(*) FROM [表名];
    
    # 查询结果去重
    SELECT DISTINCT [列名] FROM [表名];
    
    # 查询结果拼接
    SELECT CONCAT([列名1], [列名2] ...) FROM [表名];
    
    # 查询结果为空特殊处理
    SELECT  IFNULL ([列名], [处理]) FROM [表名]; 
    
    # 模糊查询,'%' 表示任意多个字符，包括 0 个字符，'_' 表示单个字符
    SELECT * FROM [表名] WHERE [列名] LIKE '%[关键字]%';
    
    # 'BETWEEN' 'AND' 区间查询，相当于 ">=" "<="
    SELECT * FROM [表名] WHERE [列名] BETWEEN [范围1] AND [范围2];
    
    # 'IN' 关键字范围查询，相当于多个 'OR' 连接，注意类型匹配，
    SELECT * FROM [表名] WHERE [列名] IN ([值1], [值2]...);
    
    # 空处理， MySql 不支持运算符处理空，可以使用 'IS NULL' 和 'IS NOT NULL'
    SELECT * FROM [表名] WHERE [列名] IS NULL;
    
    # 查询结果排序，'ASC' 升序，'DESC' 降序，默认是升序，支持别名、表达式函数与多字段排序
    SELECT * FROM [表名] ORDER BY [列名] [排序方式];
    
    # 分组查询，'WHERE'用于在分组前筛选， 'HAVING'用于分组后筛选，
    # 也可以使用表达式和函数，通常与分组函数结合使用
    SELECT [列名], [函数]
    FROM [表名]
    WHERE [列名] [条件] [值]
    GROUP BY [列名]
    HAVING [列名] [条件] [值]
    ORDER BY [列名] [升序或降序]
    
    # 范围等值查询
    SELECT * FROM [表名] WHERE [列名] iN([值1], [值2] ...);
    
    # 分页查询，注意索引是从 0 开始的，LIMIT 一般在 SQL 语句最后面
    # 分页查询算法：index = (page - 1) * size；
    SELECT * FROM [表名] LIMIT [索引], [显示的行数];

MySql连接查询与联合查询：[https://blog.csdn.net/codejas/article/details/80227404][https_blog.csdn.net_codejas_article_details_80227404]

#### 六、数据插入 ####

# 选择列名插入数据
    # 第一种方式支持多行数据插入与子查询
    INSERT INTO [表名]([列名1], [列名2] ...) VALUES([值1], [值2] ...); 
    INSERT INTO [表名] SET [列名1] = [值1], [列名22] = [值2] ...
    
    # 为所有列插入数据，值的顺序要和数据表中列的顺序一致
    INSERT INTO [表名] VALUES([值1], [值2] ...); 
    
    # 插入多行数据
    INSERT INTO [表名] VALUES([值1], [值2] ...)， ([值1], [值2] ...)， ([值1], [值2] ...);

#### 七、数据更新 ####

# 更新数据库中所有的行数
    UPDATE [表名] SET [列名1] = [值1], [列名2] = [值2] ...;
    
    # 按条件更新数据
    UPDATE [表名] SET [列名1] = [值1], [列名2] = [值2] ... WHERE [列名] [条件] [条件值];
    
    # 多表连接更新
    UPDATE [表名1] [连接类型] JOIN [表名2] ON [连接条件] SET [列名1] = [值1], ... WHERE [筛选条件];

#### 八、数据删除 ####

# 删除表中所有的数据
    # TRUNCATE 后不可以加 'WHERE' 筛选条件，并且不支持事务回滚
    DELETE FROM [表名];
    TRUNCATE TABLE [表名];
    
    # 按条件删除
    DELETE FROM [表名] WHERE [删除条件];
    
    # 多表连接删除
    DELETE [表名1], ... FROM [表名1] [连接类型] JOIN [表名2] ON [连接条件] WHERE [筛选条件];

#### 九、视图 ####

# 创建试图或修改视图名，试图存在修改，不存在修改
    CREATE OR REPLACE VIEW [视图名] AS [SQL语句];
    
    # 查看试图结构
    DESC [视图名];
    
    # 使用试图
    SELECT * FROM [视图名] WHERE [查询条件]; 
    
    # 删除试图
    DROP VIEW [视图名1], ...;

#### 十、变量 ####

#查看系统全局变量或会话变量
    SHOW GLOBAL|SESSION VARIABLES;
    
    # 模糊查询匹配系统变量
    SHOW GLOBAL|SESSION VARIABLES LIKE '[条件]';
    
    # 查看某个具体的系统变量值
    SHOW @@GLOBAL|SESSION.[系统变量名];
    
    # 修改系统变量值
    SET GLOBAL|SESSION [变量名] = [变量值];
    
    # 自定义用户变量并初始化，只在当前的会话有效
    SET @[变量名]=[变量值];
    SET @[变量名]:=[变量值];
    SELECT @[变量名]:=[变量值];
    
    # 查看自定义用户变量
    SELECT @[变量名];
    
    # 自定义局部变量，只在 BEGIN END 中有效，并且需要声明在第一行
    DECLARE [变量名] [类型] DEFAULT [默认值];
    
    # 设置局部变量值
    SET @[变量名]=[变量值];
    SET @[变量名]:=[变量值];
    SELECT @[变量名]:=[变量值];
    
    # 获取局部变量值
    SELECT [变量值];

#### 十一、其他 ####

# 查看事务的隔离级别
    SELECT @@TX_ISOLATION;
    
    # 设置事务的隔离级别
    SET SESSION|GLOBAL TRANSACTION ISOLACTION LEVEL [隔离级别];

#### 十二、更新 ####

按月份查询数据并对某一字段求和

SELECT MONTH (time_column), SUM(column) FROM table GROUP BY MONTH (time_column)

区分年份

SELECT MONTH (time_column), sum(column) FROM table GROUP BY extract(YEAR_MONTH FROM time_column)

区分年份，指定查询几个月份并对字段求和

SELECT SUM(column) AS 总数量 MONTH (time_column) AS 月份, YEAR (time_column) AS 年份 FROM table WHERE time_column > DATE_SUB(CURDATE(), INTERVAL 6 MONTH) GROUP BY extract(YEAR_MONTH FROM gmt_create) ORDER BY time_column DESC

按照时间获取每个分组最新的数据

SELECT * FROM ( SELECT * FROM [表名] ORDER BY [日期字段] DESC ) AS u GROUP BY u.account ORDER BY u.[日期字段]DESC

[https_www.w3schools.com_sql_sql_ref_mysql.asp]: https://www.w3schools.com/sql/sql_ref_mysql.asp
[https_blog.csdn.net_codejas_article_details_80227404]: https://blog.csdn.net/codejas/article/details/80227404