MySQL指令大全

 说明：

• 大写的表示关键字
• [] 是可选项
• CHARACTER SET: 指定数据库采用的字符集
• COLLATE: 指定数据库字符集的校验规则

一、数据库操作

1、显示当前数据库

SHOW DATABASES;

2、创建数据库

CREATE DATABASE IF NOT EXISTS db_test CHARACTER SET utf8mb4;
-- 如果系统没有 db_test 的数据库，则创建一个使用 utf8mb4 字符集的 db_test 数据库，如果有则不创建

3、使用数据库

use 数据库名;

4、删除数据库

DROP DATABASE [IF EXISTS] db_name;

二、常用数据类型

数据类型	大小	说明
INT	4字节	对应Java中Integer
BIGINT	8字节	对应Java中Long
DOUBLE(M,D)	8字节	对应Java中Double，M指定长度，D指定小数位数，会发生精度丢失
DECIMAL(M,D)	M/D最大值+2	精确数值
VARCHAR(SIZE)	0-65,535字节	可变长度字符串，对应Java中String
DATETIME	8字节	范围从 1000 到 9999 年，不会进行时区的 检索及转换。

三、表操作

        需要操作数据库中的某个表时，需要先使用该数据库。

1、查看数据库中的表

show tables;

2、查看表结构

desc 表名;

Field	Type	Null	Key	Default	Extra
字段名字	字段类型	是否允许为空	索引类型	默认值	扩充

3、创建表 

CREATE TABLE table_name (
    field1 datatype,
    field2 datatype,
    field3 datatype
);

        可以使用comment增加字段说明。 

示例：

create table stu_test (
   id int,
   name varchar(20) comment '姓名',
   password varchar(50) comment '密码',
   age int,
   sex varchar(1),
   birthday timestamp,
   amout decimal(13,2),
   resume text
);

表的关系：

1. 一对一
2. 一对多
3. 多对多：需要创建中间表来映射两张表的关系

4、删除表

DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name] ...

示例：

-- 删除 stu_test 表
drop table stu_test;

-- 如果存在 stu_test 表，则删除 stu_test 表
drop table if exists stu_test;

四、表的增删改查

        CRUD：增加(Create)、查询(Retrieve)、更新(Update)、删除(Delete)四个单词的首字母缩写。

1、增加（Create）

1.1 单行数据+全列插入

-- 插入两条记录，value_list 数量必须和定义表的列的数量及顺序一致
INSERT INTO student VALUES (100, 10000, '唐三藏', NULL);
INSERT INTO student VALUES (101, 10001, '孙悟空', '11111');

1.2 多行数据+指定列插入

-- 插入两条记录，value_list 数量必须和指定列数量及顺序一致
INSERT INTO student (id, sn, name) VALUES
     (102, 20001, '曹孟德'),
     (103, 20002, '孙仲谋');

1.3 插入查询结果

-- 将学生表中的所有数据复制到用户表
INSERT INTO test_user(name, email) SELECT name, qq_mail FROM student;

2、查询（Retrieve）

selsct条件查询的执行顺序：

1. 遍历表中的每条记录
2. 把当前记录的值，带入条件，根据条件进行筛选
3. 如果这个条件成立，就保留，进行列上的表达式的计算

2.1 全列查询

-- 通常情况下不建议使用 *进行全列查询
-- 1.查询的列越多，意味着需要传输的数据量越大;
-- 2.可能会影响到索引的使用。
SELECT * FROM exam_result;

2.2 指定列查询

-- 指定列的顺序不需要按照定义表的顺序来
SELECT id, name, english FROM exam_result;

2.3 查询字段为表达式

-- 表达式不包含字段
SELECT id, name, 10 FROM exam_result;
-- 表达式包含一个字段
SELECT id, name, english + 10 FROM exam_result;
-- 表达式包含多个字段
SELECT id, name, chinese + math + english FROM exam_result;

2.4 别名

2.4.1 空格

SELECT id, name, chinese + math + english 总分 FROM exam_result;
-- 将列名chinese + math + english替换成别名 总分

2.4.2 as（alias (别名) 的缩写）

SELECT id, name, chinese + math + english as 总分 FROM exam_result;
-- 将列名chinese + math + english替换成别名 总分
-- as可省略（但是最好不要省略，列名和别名混淆），省略后和使用空格效果一致

2.4.3 双引号

SELECT id, name, chinese + math + english "总分 语数英" FROM exam_result;
-- 将列名chinese + math + english替换成别名 总分 语数英
-- 当别名中存在空格时，需要用双引号将别名引起来，否则会报错

2.5 去重（DISTINCT）

SELECT DISTINCT math FROM exam_result;

2.6 排序（ORDER BY）

-- ASC 为升序（从小到大，默认）
-- DESC 为降序（从大到小）

-- 查询同学姓名和 qq_mail，按 qq_mail 排序显示
SELECT name, qq_mail FROM student ORDER BY qq_mail;
SELECT name, qq_mail FROM student  ORDER BY qq_mail DESC;


-- 查询同学及总分，由高到低，使用表达式及别名排序
SELECT name, chinese + english + math FROM exam_result 
    ORDER BY chinese + english + math DESC;

SELECT name, chinese + english + math total FROM exam_result 
    ORDER BY total DESC;


-- 查询同学各门成绩，依次按 数学降序，英语升序，语文升序的方式显示
SELECT name, math, english, chinese FROM exam_result 
    ORDER BY math DESC, english, chinese;

注意：

1. 没有 ORDER BY 子句的查询，返回的顺序是未定义的，永远不要依赖这个顺序。
2. NULL 数据排序，视为比任何值都小，升序出现在最上面，降序出现在最下面。
3. 对多个字段进行排序，排序优先级随书写顺序 。

2.7 条件查询（WHERE）

2.7.1 比较运算符

运算符	说明
>,>=,<,<=
=	NULL不安全，例如NULL=NULL的结果是NULL
<=>	NULL安全，例如NULL<=>NULL的结果是TRUE(1)
!=,<>	不等于
BETWEEN a0 AND a1	如果value在范围[a0,a1]，返回TRUE(1)
IN(option,…)	如果是option中的任何一个，返回TRUE(1)
IS NULL	是NULL
IS NOT NULL	不是NULL
LIKE	模糊匹配。%表示任意多个任意字符，_表示任意一个字符

2.7.2 逻辑运算符

运算符	说明
AND	全部条件为TRUE(1),结果才为TRUE(1)
OR	任意一个条件为TRUE(1)，结果为TRUE(1)
NOT	条件为 TRUE(1)，结果为 FALSE(0)

2.7.3 案例

-- 查询英语不及格的同学及英语成绩 ( < 60 )
SELECT name, english FROM exam_result WHERE english < 60;

-- 查询语文成绩好于英语成绩的同学
SELECT name, chinese, english FROM exam_result WHERE chinese > english;

-- 查询总分在 200 分以下的同学
SELECT name, chinese + math + english 总分 FROM exam_result 
    WHERE chinese + math + english < 200;


-- 查询语文成绩大于80分，且英语成绩大于80分的同学
SELECT * FROM exam_result WHERE chinese > 80 and english > 80;

-- 查询语文成绩大于80分，或英语成绩大于80分的同学
SELECT * FROM exam_result WHERE chinese > 80 or english > 80;

-- AND 的优先级高于 OR ，在同时使用时，需要使用小括号()包裹优先执行的部分
SELECT * FROM exam_result WHERE chinese > 80 or math>70 and english > 70;
SELECT * FROM exam_result WHERE (chinese > 80 or math>70) and english > 70;


-- 查询语文成绩在 [80, 90] 分的同学及语文成绩
SELECT name, chinese FROM exam_result WHERE chinese BETWEEN 80 AND 90;

-- 使用 AND 也可以实现
SELECT name, chinese FROM exam_result WHERE chinese >= 80 AND chinese <= 90;


-- 查询数学成绩是 58 或者 59 或者 98 或者 99 分的同学及数学成绩
SELECT name, math FROM exam_result WHERE math IN (58, 59, 98, 99);

-- 使用 OR 也可以实现
SELECT name, math FROM exam_result WHERE math = 58 OR math = 59 OR math = 98 OR math = 99;


-- % 匹配任意多个（包括 0 个）字符
SELECT name FROM exam_result WHERE name LIKE '孙%';-- 匹配到孙悟空、孙权

-- _ 匹配严格的一个任意字符
SELECT name FROM exam_result WHERE name LIKE '孙_';-- 匹配到孙权


-- 查询 qq_mail 已知的同学姓名
SELECT name, qq_mail FROM student WHERE qq_mail IS NOT NULL;

-- 查询 qq_mail 未知的同学姓名
SELECT name, qq_mail FROM student WHERE qq_mail IS NULL;

 2.8 分页查询（LIMIT）

-- 起始下标为 0
-- 从 0 开始，筛选 3 条结果
SELECT id,name,math,english,chinese FROM exam_result ORDER BY id LIMIT 3;

-- 从 0 开始，筛选 3 条结果
SELECT id,name,math,english,chinese FROM exam_result ORDER BY id LIMIT 0,3;

-- 从 0 开始，筛选 3 条结果
SELECT id,name,math,english,chinese FROM exam_result ORDER BY id LIMIT 3 OFFSET 0;


-- 建议使用第三种方法

2.9 聚合查询

2.9.1聚合函数

函数	说明
COUNT([DISTINCT] expr)	返回查询到的数据的 数量
SUM([DISTINCT] expr)	返回查询到的数据的 总和，不是数字没有意义
AVG([DISTINCT] expr)	返回查询到的数据的 平均值，不是数字没有意义
MAX([DISTINCT] expr)	返回查询到的数据的 最大值，不是数字没有意义
MIN([DISTINCT] expr)	返回查询到的数据的 最小值，不是数字没有意义

注意：NULL不会被统计。

2.9.2 GROUP BY：分组

-- 查询每个角色的最高工资、最低工资和平均工资
SELECT role,max(salary),min(salary),avg(salary) FROM emp GROUP BY role;

-- GROUP BY 要搭配聚合函数使用，否则查询结果可能没有意义

2.9.3 HAVING

        GROUP BY 子句进行分组以后，需要对分组结果再进行条件过滤时，不能使用 WHERE 语句，而需要用 HAVING。（注意区分分组前和分组后）

-- 显示平均工资低于1500的角色和它的平均工资
SELECT role,max(salary),min(salary),avg(salary) FROM emp GROUP BY role HAVING avg(salary)<1500;

2.10 联合查询（多表查询）

基本步骤：

1. 笛卡尔积
2. 连接条件
3. 指定更多的业务上的条件
4. 针对列进行筛选
5. 聚合函数

2.10.1 笛卡尔积（联合查询的关键思路）

        其实就是简单的排列组合。

 2.10.2 内连接

SELECT 字段 FROM 表1 别名1 [INNER] JOIN 表2 别名2 ON 连接条件 AND 其他条件;
SELECT 字段 FROM 表1 别名1,表2 别名2 WHERE 连接条件 AND 其他条件;

-- 查询“许仙”同学的 成绩
SELECT sco.score FROM student stu, score sco WHERE stu.id=sco.student_id AND stu.name='许仙';

-- 查询所有同学的总成绩
SELECT stu.name, sum( sco.score ) FROM student stu, score sco WHERE stu.id = sco.student_id GROUP BY name;

-- 查询所有同学的所有成绩
SELECT student.name as stuName, course.name as couName, score.score FROM student, score, course WHERE student.id = score.student_id AND course.id = score.course_id;

2.10.3 外连接

        外连接分为左外连接和右外连接。如果联合查询，左侧的表完全显示我们就说是左外连接；右侧的表完全显示我们就说是右外连接。

-- 左外连接，表1完全显示
SELECT 字段名 FROM 表名1 LEFT JOIN 表名2 ON 连接条件;

-- 右外连接，表2完全显示
SELECT 字段名 FROM 表名1 RIGHT JOIN 表名2 ON 连接条件;

2.10.4 自连接 

        自连接是指在同一张表连接自身进行查询。

-- 显示所有“计算机原理”成绩比“Java”成绩高的成绩信息
SELECT
    s1.* 
FROM
    score s1
    JOIN score s2 ON s1.student_id = s2.student_id
    AND s1.score < s2.score
    AND s1.course_id = 1
    AND s2.course_id = 3;

2.10.5 子查询 

        子查询是指嵌入在其他sql 语句中的 select 语句，也叫嵌套查询。由于是将几个简单的sql语句合成一个复杂的sql语句，因此不建议使用。

-- 单行子查询：返回一条记录的子查询
-- 查询“不想毕业”同学的同班同学
SELECT * FORM student WHERE classes_id=(SELECT classes_id FORM student WHERE name='不想毕业');


-- 单行子查询：返回一条记录的子查询
-- 查询“语文”或“英文”课程的成绩信息

-- 1. [NOT] IN关键字
-- 使用 IN
SELECT * FROM score WHERE course_id IN (SELECT id FROM course WHERE name='语文' OR name='英文');
-- 使用 NOT IN
SELECT * FROM score WHERE course_id NOT IN (SELECT id FROM course WHERE name!='语文' and name!='英文');

-- 2. [NOT] EXISTS关键字
-- 使用 EXISTS
SELECT * FROM score sco WHERE EXISTS (SELECT sco.id FROM course cou WHERE (name='语文' or name='英文') AND cou.id = sco.course_id);
-- 使用 NOT EXISTS
SELECT * FROM score sco WHERE NOT EXISTS (SELECT sco.id FROM course cou WHERE (name!='语文' and name!='英文') AND cou.id = sco.course_id);

2.10.6 合并查询

        合并多个select的执行结果。

-- UNION
-- 用于取得两个结果集的并集。自动去掉结果集中的重复行。
-- 查询id小于3，或者名字为“英文”的课程
SELECT * FROM course WHERE id<3 UNION SELECT * FROM course WHERE name='英文';
-- 或者使用or来实现
SELECT * FROM course WHERE id<3 OR name='英文';

-- UNION ALL
-- 用于取得两个结果集的并集。不会去掉结果集中的重复行。
-- 查询id小于3，或者名字为“Java”的课程
SELECT * FROM course WHERE id<3 UNION ALL SELECT * FROM course WHERE name='英文';

3、修改（Update）

-- 将孙悟空同学的数学成绩变更为 80 分
UPDATE exam_result SET math = 80 WHERE name = '孙悟空';

-- 将曹孟德同学的数学成绩变更为 60 分，语文成绩变更为 70 分
UPDATE exam_result SET math = 60, chinese = 70 WHERE name = '曹孟德';

-- 将总成绩倒数前三的 3 位同学的数学成绩加上 30 分
UPDATE exam_result SET math = math + 30 ORDER BY chinese + math + english LIMIT 3;

-- 将所有同学的语文成绩更新为原来的 2 倍
UPDATE exam_result SET chinese = chinese * 2;

4、删除（Delete）

-- 删除孙悟空同学的考试成绩
DELETE FROM exam_result WHERE name = '孙悟空';

-- 删除整表数据
DELETE FROM for_delete;

五、数据库的约束

1、NULL约束

CREATE TABLE student (
    id INT NOT NULL,    -- id不能为NULL
    sn INT,
    name VARCHAR(20),
    qq_mail VARCHAR(20)
);

2、UNIQUE：唯一约束

DROP TABLE IF EXISTS student;
CREATE TABLE student (
   id INT NOT NULL,
   sn INT UNIQUE,    -- 指定sn列为唯一的、不重复的
   name VARCHAR(20),
   qq_mail VARCHAR(20)
);

3、DEFAULT：默认值约束

DROP TABLE IF EXISTS student;
CREATE TABLE student (
   id INT NOT NULL,
   sn INT UNIQUE,
   name VARCHAR(20) DEFAULT 'unkown',    -- 指定插入数据时，若name列为空，默认值unkown
   qq_mail VARCHAR(20)
);

4、PRIMARY KEY：主键约束

CREATE TABLE student (
   id INT PRIMARY KEY,    -- 主键是 NOT NULL 和 UNIQUE 的结合，可以不用 NOT NULL
   sn INT UNIQUE,
   name VARCHAR(20) DEFAULT 'unkown',
   qq_mail VARCHAR(20)
);

-- 对于整数类型的主键，常配搭自增长auto_increment来使用。
-- 插入数据对应字段不给值时，使用最大值+1。
id INT PRIMARY KEY auto_increment,

5、FOREIGN KEY：外键约束

FOREIGN KEY (字段名) REFERENCES 主表(列)
-- 外键用于关联其他表的主键或唯一键，即被关联的列必须是被 UNIQUE 或 PRIMARY KEY 修饰的

六、索引

        数据库的索引类似于书籍的目录。可以提高数据库的查询效率，从而提高数据库的性能。创建主键约束（PRIMARY KEY）、唯一约束（UNIQUE）、外键约束（FOREIGN KEY）时，会自动创建对应列的索引。

1、查看索引

SHOW INDEX FROM 表名;

2、创建索引

CREATE INDEX 索引名 ON 表名(字段名);

3、删除索引

DROP INDEX 索引名 ON 表名;

注意：删除操作无法删除自动创建的索引。

4、原理

        索引保存的数据结构主要为B+树。