二、MySQL操作命令汇总

二、MySQL操作命令汇总

1.数据库操作

• 查看当前连接的所有数据库：

  show databases;
  

• 创建数据库：

  create database 数据库名;
  

• 切换数据库：

  use 数据库名;
  

• 删除数据库：

  drop database 数据库名;
  

• 查询数据库默认的字符集和比较规则：

  SHOW VARIABLES LIKE 'character_set_database';
  SHOW VARIABLES LIKE 'collation_database';
  

• 查看通用查询日志是否开启：（ON 为开启，OFF 为关闭。如果未开启，可以通过 SET GLOBAL general_log = 'ON'; 来开启通用查询日志。）

  SHOW VARIABLES LIKE 'general_log'; 
  

• 查看通用查询日志的文件路径：

  SHOW VARIABLES LIKE 'general_log_file';
  

2.表的增删改查

2.1 查表

show tables;

假如没有指定数据库，可以通过from指定要操作的表：

show tables from table_name;

2.2 建表

实际工作中，一般不会直接通过 SQL 语句来创建表，而是通过 Navicat、DataGrip 这样的数据库工具实现。

create table 表名(列名1 数据类型1,列名2 数据类型2,...列名n 数据类型n
);

create table article(id int primary key auto_increment,title varchar(100) not null,content text not null,author varchar(20) not null,create_time datetime not null,read_count int default 0
);

• article 是表名；
• id 是主键，类型为 int，自增长；
• title 是标题，类型为 varchar，长度为 100，不允许为空；
• content 是内容，类型为 text，不允许为空；
• author 是作者，类型为 varchar，长度为 20，不允许为空；
• create_time 是发布时间，类型为 datetime，不允许为空；
• read_count 是阅读量，类型为 int，默认值为 0。

给表添加注释

通过给表添加注释，方便我们在后期维护的时候，能够更好的理解表的含义。

create table 表名(列名1 数据类型1 comment '注释1',列名2 数据类型2 comment '注释2',...列名n 数据类型n comment '注释n'
) comment '表注释';

create table article(id int primary key auto_increment comment '主键',title varchar(100) not null comment '标题',content text not null comment '内容',author varchar(20) not null comment '作者',create_time datetime not null comment '发布时间',read_count int default 0 comment '阅读量'
) comment '文章表';

假如表已经存在

如果在创建表时，该表已经存在，就会报错 Table 'article' already exists。为了避免这种情况，我们可以在建表的时候，先判断表是否存在，如果不存在，再创建表，语法如下：

create table if not exists 表名(列名1 数据类型1,列名2 数据类型2,...列名n 数据类型n
);

create table if not exists article(id int primary key auto_increment comment '主键',title varchar(100) not null comment '标题',content text not null comment '内容',author varchar(20) not null comment '作者',create_time datetime not null comment '发布时间',read_count int default 0 comment '阅读量'
) comment '文章表';

2.3 删表

drop table 表名;

在删除表的时候也可以加上 if exists，防止表不存在的时候，报错。

drop table if exists 表名;

2.4 查看表结构

desc 表名;
describe 表名;
explain 表名;
show columns from 表名;
show fields from 表名;

查看建表语句：

show create table 表名;

查看索引信息：

show index from 表名;

查看字段的属性：

show columns from 表名 like 字段名;

2.5 改表

• 增加字段

  alter table 表名 add 列名 数据类型;alter table article add update_time datetime;
  

• 增加字段，并指定位置到某个字段的后面。

  alter table article add update_time datetime after create_time;
  

• 删除字段

  alter table 表明 drop 列名;alter table article drop update_time;
  

• 修改字段

  alter table 表明 modify 列名 数据类型;alter table article modify title varchar(200);
  

• 修改字段名

  alter table 表名 change 原列名 新列名 数据类型;alter table article change title article_title varchar(100);
  

• 修改表名

  alter table 原表名 rename 新表名;alter table article rename article_info;
  

3.简单查询

3.1 查询单个字段

• 查看某个表的某个字段：

  SELECT 字段名 FROM 表名;
  

• 使用 AS 关键字，给查询出来的字段起一个别名：

  SELECT 字段名 AS 别名 FROM 表名;
  

3.2 查询多个字段

SELECT 字段1, 字段2, 字段3 FROM 表名;

3.3 查询所有字段

SELECT * FROM 表名;

3.4 查询结果去重

当结果需要去重时，可以使用 DISTINCT 关键字进行查询：

SELECT DISTINCT 字段名 FROM 表名;

3.5 查询结果排序

当结果需要排序时，可以使用 ORDER BY 关键字进行查询，ASC 是升序，DESC 是降序，默认是升序：

SELECT 字段名 FROM 表名 ORDER BY 字段名 [ASC | DESC]; 

如果需要按照多个字段排序，可以使用逗号 , 分隔字段进行排序：

SELECT 字段1, 字段2 FROM 表名 ORDER BY 字段1 [ASC|DESC], 字段2 [ASC|DESC];

3.6 查询结果限制条数

当结果集中需要限制返回的行数时，可以使用 LIMIT 关键字进行查询：

SELECT 字段名 FROM 表名 LIMIT 开始行,行数;

开始行也叫偏移量（OFFSET），默认是 0，可以缺省。

3.7 查询分组结果

GROUP BY 子句用于将结果集根据指定的字段或者表达式进行分组。

SELECT column1[, column2, ...], aggregate_function(ci)
FROM table
[WHERE clause]
GROUP BY column1[, column2, ...];
[HAVING clause]

经常使用的聚合函数主要有：SUM(),AVG(),MAX(),MIN(),COUNT()。

HAVING 子句用来过滤 GROUP BY 分组的数据，需要使用逻辑表达式作为条件，其中逻辑表达式中的字段或表达式只能使用分组使用的字段和聚合函数。

note：WHERE 用来过滤结果集中的数据，HAVING 子句用来过滤分组数据。

SELECT customer_id, SUM(amount) total
FROM payment
GROUP BY customer_id
HAVING total > 180
ORDER BY total DESC;

4.条件查询

通过 WHERE 设置查询条件：

SELECT * FROM 表名 WHERE 查询条件;
SELECT * FROM `user` WHERE `name` = 'xiaoming';

---

为了更好理解条件查询，下面将创建一个 student 表，配合语法进行演示：

CREATE TABLE `student` (`id` INT (10) NOT NULL AUTO_INCREMENT,`name` VARCHAR (255) NOT NULL,`gender` VARCHAR (10) NOT NULL,`age` INT (10) NOT NULL,`class` VARCHAR (10) DEFAULT '三班',PRIMARY KEY (`id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;INSERT INTO `student` (`id`, `name`, `gender`, `age`) VALUES (1, 'zhangsan', '男', 18);
INSERT INTO `student` (`name`, `gender`, `age`) VALUES ('lisi', '男', 18);
INSERT INTO `student` (`name`, `gender`, `age`) VALUES ('wangwu', '男', 18);
INSERT INTO `student` (`name`, `gender`, `age`) VALUES ('xiaomei', '女', 19);
INSERT INTO `student` (`name`, `gender`, `age`) VALUES ('pengyuyan', '男', 18);
INSERT INTO `student` (`name`, `gender`, `age`) VALUES ('xiaoli', '女', 18);
INSERT INTO `student` (`name`, `gender`, `age`) VALUES ('dabao', '男', 19);
INSERT INTO `student` (`name`, `gender`, `age`) VALUES ('zhaoliying', '女', 18);
INSERT INTO `student` (`name`, `gender`, `age`) VALUES ('fanbingbing', '女', 19);
INSERT INTO `student` (`name`, `gender`, `age`) VALUES ('lihua', '男', 18);SELECT * FROM `student`;

4.1 区间查询

查询 id 在 1 到 5 之间的学生，可以这么写：

SELECT * FROM student WHERE id >= 1 and id <= 5;

还可以使用 BETWEEN 关键字，来更简洁地实现区间查询：

SELECT * FROM student WHERE id BETWEEN 1 and 5;

配合 NOT 关键字，可以实现区间查询的取反操作：

SELECT * FROM student WHERE id NOT BETWEEN 1 and 5;

4.2 枚举查询

查询 id 是 1,3,5 的学生：

SELECT * FROM student WHERE id IN (1, 3, 5);

查询 id 不是 1,3,5 的学生：

SELECT * FROM student WHERE id NOT IN (1, 3, 5);

4.3 NULL查询

NULL 是一个特殊的值，表示表示一个未知值或缺失值，它不等于空字符串、零或任何其他值。没办法直接通过 = 或者 != 来查询 NULL 值，而是要使用 IS NULL 或者 IS NOT NULL 来查询。

SELECT * FROM student WHERE gender IS NULL;
SELECT * FROM student WHERE gender IS NOT NULL;

4.4 逻辑操作符

• AND 操作符用于组合多个查询条件，只有当所有的条件都满足时，才会返回结果。

  SELECT * FROM student WHERE id >= 1 AND gender = '男';
  

• OR 操作符用于组合多个查询条件，但只要有一个条件满足，就会返回结果。

  SELECT * FROM student WHERE id >= 3 OR age = 19;
  

• 小括号 () 操作符用于改变查询条件的优先级。

  SELECT * FROM student WHERE id >= 1 AND (gender = '男' OR age = 19);
  

4.5 通配符查询

通配符查询使用在模糊查询的场景下。LIKE 关键字用于模糊查询，= 属于精确查询。

MySQL 支持两种通配符，% 和 _，其中 % 用于匹配任意长度的字符串，_ 用于匹配单个字符。

SELECT * FROM student WHERE name LIKE '%xiao%'

note：% 通配符可以连续出现，但是它们的含义是一样的，都是匹配任意长度的字符序列。

-- 结果一致
SELECT * FROM student WHERE name LIKE '%%%xiao'
SELECT * FROM student WHERE name LIKE '%%xiao'
SELECT * FROM student WHERE name LIKE '%xiao'

note：如果查询的内容本身就带有 % 或 _，这个时候可以只用 \ 作为转义字符。

4.6 集合查询

SQL 标准中定义了 3 个集合操作符： UNION, INTERSECT 和 MINUS。目前 MySQL 只支持 UNION。它用于合并 2 个结果集中的所有的行。

SELECT statement
UNION [DISTINCT | ALL]
SELECT statement

• UNION 双目操作符，需要两个 SELECT 语句作为操作数。
• UNION 中的 SELECT 语句中的列数、列顺序必须相同。
• UNION 运算包括 UNION DISTINCT 和 UNION ALL 两种算法，其中 UNION DISTINCT 可以简写为 UNION。
• UNION DISTINCT 或 UNION 将过滤掉结果集中重复记录。
• UNION ALL 将返回结果集中的所有记录。
• UNION 运算取第一个参与运算的结果集的列名作为最终的列名。

SELECT * FROM a
UNION
SELECT * FROM b
UNION
SELECT * FROM c;

5.数据的增删改查

5.1 增数据

在 MySQL 中，INSERT 语句用于将一行或者多行数据插入到数据表中。可以使用一个 INSERT 语句插入一行或多行数据。

• INSERT 语句的插入单行数据语法：

INSERT INTO table_name (column_1, column_2, ...) VALUES (value_1, value_2, ...);

• INSERT 语句的插入多行数据语法：

INSERT INTO table_name (column_1, column_2, ...)
VALUES (value_11, value_12, ...),(value_21, value_22, ...)...;

---

除了使用 INSERT 语句，还可以使用 REPLACE 语句。REPLACE 语句和 INSERT 语句很像，它们的不同之处在于，当插入过程中出现了重复的主键或者重复的唯一索引的时候，INSERT 语句会产生一个错误，而 REPLACE 语句则先删除旧的行，再插入新的行。（REPLACE 语句不在标准 SQL 的范畴）

REPLACE [INTO] table_name (column_1, column_2, ...)
VALUES (value_11, value_12, ...),(value_21, value_22, ...)...;

• REPLACE INTO 和 VALUES 都是关键字。INTO 可省略。

REPLACE 语句还可以使用 SET 关键词，这只适用于操作单行。语法如下：

REPLACE [INTO] table_name
SET column1 = value1,column2 = value2,...;

这种用法与 UPDATE 语句的相似，但也是不同的。 UPDATE 只更新符合条件的行的指定字段的值，未指定的字段保留原值。REPLACE 则会删掉旧行，再插入新行，REPLACE 语句中未指定的字段则为默认值或者 NULL。

5.2 删数据

在 MySQL 中，DELETE 语句用于从表中删除满足条件的记录行。

DELETE FROM table_name
[WHERE clause]
[ORDER BY ...]
[LIMIT row_count]

• DELETE FROM 后跟的是要从中删除数据的表。
• WHERE 子句用来过滤需要删除的行。满足条件的行会被删除。
• WHERE 子句是可选的。没有 WHERE 子句时，DELETE 语句将删除表中的所有行。
• ORDER BY 子句用来指定删除行的顺序。它是可选的。
• LIMIT 子句用来指定删除的最大行数。它是可选的。
• DELETE 语句返回删除的行数。

5.3 改数据

如果想要更新表中的已有的数据行，需要使用到 UPDATE 语句。

UPDATE [IGNORE] table_name
SETcolumn_name1 = value1,column_name2 = value2,...
[WHERE clause];

• UPDATE 关键字后指定要更新数据的表名。
• 使用 SET 子句设置字段的新值。多个字段使用逗号分隔。字段的值可以是普通的字面值，也可以是表达式运算，还可以是子查询。
• 使用 WHERE 子句指定要更新的行。只有符合 WHERE 条件的行才会被更新。
• WHERE 子句是可选的。如果不指定 WHERE 子句，则更新表中的所有行。

6.MySQL子查询

MySQL 子查询是嵌套一个语句中的查询语句，也被称为内部查询。子查询经常用在 WHERE 子句中。接下来使用来自 Sakila 示例数据库中的 language 和 film 表作为演示。

6.1 EXISTS子查询

在 MySQL 中，EXISTS 操作符用来判断一个子查询是否返回数据行。如果一个子查询返回了至少一个数据行，则 EXISTS 的计算结果为 TRUE，否则计算结果为 FALSE。

SELECT column_name
FROM table_name
WHERE EXISTS(subquery);

• EXISTS 一般用在 WHERE 子句中。

• EXISTS 是一个单目操作符，它需要一个子查询 subquery 作为参数。

• EXISTS 运算时，一旦子查询找到一个匹配的行，EXISTS 运算就会返回。

• NOT EXISTS 则是 EXISTS 的否定操作。

• EXISTS 不关心子查询中的列的数量或者名称，它只在乎子查询是否返回数据行。所以在 EXISTS 的子查询中，无论你是使用 SELECT 1 还是 SELECT *，亦或是 SELECT column_list，都不影响 EXISTS 运算的结果。

SELECT *
FROM language
WHERE EXISTS(SELECT *FROM filmWHERE film.language_id = language.language_id);

6.2 子查询与IN

上面的 EXISTS 语句可以使用 IN 来实现，但是大多数情况下，使用 EXISTS 的语句的性能比对应的使用 IN 的语句要好。

SELECT *
FROM language
WHERE language_id IN (SELECT DISTINCT language_idFROM film);

6.3 派生表

位于 FORM 子句中的子查询被称为派生表。派生表必须具有别名。因为 MySQL 规定，任何 FORM 子句中的表必须具有一个名字。

SELECT *
FROM (SELECT last_name,COUNT(*) countFROM actorGROUP BY last_name) t
WHERE t.last_name LIKE 'A%';

note：派生表不是临时表。派生表必须具有别名，并且列名必须唯一。

7.MySQL别名

在 MySQL 中，可以使用的别名包括：列别名和表别名。可以通过 AS 关键字指定别名，但是 AS 关键字是可选的。

7.1 列别名

SELECT column_name AS `alias`
FROM table_name;

• AS 关键字后面跟的是列的别名 alias。AS 关键字是可选的。
• 当别名中包含空格时，必须使用`将别名引起来。

7.2 表别名

table_name AS alias

AS 关键字同样是可选的，可以省略它。

SELECT *
FROM language l
WHERE EXISTS(SELECT *FROM film fWHERE f.language_id = l.language_id);

8.MySQL连接类型

在 MySQL 中，JOIN 语句用于将数据库中的两个表或者多个表组合起来。比如在一个学校系统中，有一个学生信息表和一个学生成绩表。这两个表通过学生 ID 字段关联起来。当我们要查询学生的成绩的时候，就需要连接两个表以查询学生信息和成绩。

MySQL 支持以下类型的连接：

• 内部联接 (INNER JOIN)
• 左连接 (LEFT JOIN)
• 右连接 (RIGHT JOIN)
• 交叉连接 (CROSS JOIN)

为了更好的理解，MySQL连接类型，首先创建创建表 student 和 student_score。

CREATE TABLE `student` (`student_id` int NOT NULL,`name` varchar(45) NOT NULL,PRIMARY KEY (`student_id`)
);CREATE TABLE `student_score` (`student_id` int NOT NULL,`subject` varchar(45) NOT NULL,`score` int NOT NULL
);INSERT INTO `student` (`student_id`, `name`)
VALUES (1,'Tim'),(2,'Jim'),(3,'Lucy');INSERT INTO `student_score` (`student_id`, `subject`, `score`)
VALUES (1,'English',90),(1,'Math',80),(2,'English',85),(2,'Math',88),(5,'English',92);SELECT * FROM student;SELECT * FROM student_score;

8.1 交叉连接

交叉连接(CROSS JOIN)返回两个集合的笛卡尔积。也就是两个表中的所有的行的所有可能的组合。这相当于内连接没有连接条件或者连接条件永远为真。如果一个有 m 行的表和另一个有 n 行的表，它们交叉连接将返回 m * n 行数据。

显式的交叉连接 student 和 student_score 表：

SELECTstudent.*,student_score.*
FROMstudent CROSS JOIN student_score;

隐式的交叉连接 student 和 student_score 表：

SELECTstudent.*,student_score.*
FROMstudent, student_score;

8.2 内连接

内连接(INNER JOIN)基于连接条件组合两个表中的数据。内连接相当于加了过滤条件的交叉连接。内连接将第一个表的每一行与第二个表的每一行进行比较，如果满足给定的连接条件，则将两个表的行组合在一起作为结果集中的一行。

内连接 student 和 student_score 表：

SELECTstudent.*,student_score.*
FROMstudentINNER JOIN student_scoreON student.student_id = student_score.student_id;

等价于：

SELECTstudent.*,student_score.*
FROMstudent, student_scoreWHERE student.student_id = student_score.student_id;

8.3 左连接

两个表左连接时，第一个表称为左表，第二表称为右表。例如 A LEFT JOIN B，A 是左表，B 是右表。

左连接以左表的数据行为基础，根据连接匹配右表的每一行，如果匹配成功则将左表和右表的行组合成新的数据行返回；如果匹配不成功则将左表的行和 NULL 值组合成新的数据行返回。

将 student 表和 student_score 表左连接：

SELECTstudent.*,student_score.*
FROMstudentLEFT JOIN student_scoreON student.student_id = student_score.student_id;

8.4 右连接

右连接与左连接处理逻辑相反，右连接以右表的数据行为基础，根据条件匹配左表中的数据。如果匹配不到左表中的数据，则左表中的列为 NULL 值。

将 student 表和 student_score 表右连接：

SELECTstudent.*,student_score.*
FROMstudentRIGHT JOIN student_scoreON student.student_id = student_score.student_id;

🤗🤗🤗