目录

数据库

表

 视图

数据

查询

索引

事务

---

数据库

创建数据库

可选参数： CHARACTER SET （编码格式）、COLLATE（校验规则）

CREATE DATABASE 数据库名 CHARACTER SET 编码格式(不写默认utf8) COLLATE 校验规则(不写默认utf8_general_ci不区分大小写)

删除数据库

DROP DATABASE 数据库名

显示所有的数据库

SHOW DATABASES

查看已创建的数据库的定义信息

SHOW CREATE DATABASE 数据库名

表

创建表

可选参数：CHARACTER SET （编码格式）、COLLATE（校验规则）、ENGINE（引擎）

CREATE TABLE '表名'(
    '列名' 数据类型,
    '列名' 数据类型,
    ...
    '列名' 数据类型
)CHARACTER SET 编码格式(不写默认跟随数据库) COLLATE 校验规则(不写默认跟随数据库) ENGINE INNODB

常用数据类型：

复制表，把复制表的结构（列）复制到该表

CREATE TABLE '表名' LIKE '复制表名'

设置约束

CREATE TABLE '表名'(
    '列名' 数据类型 约束,
    '列名' 数据类型 约束,
    ...
    '列名' 数据类型 约束
)

复合主键
CREATE TABLE '表名'(
    '列名' 数据类型,
    '列名' 数据类型,
    ...
    '列名' 数据类型,
    PRIMARY KEY ('列名','列名',...)
)

外键
CREATE TABLE '表名'(
    '列名' 数据类型,
    '列名' 数据类型,
    ...
    '列名' 数据类型,
    FOREIGN KEY ('列名') REFERENCES '主表名'('列名')
)

• PRIMARY KEY：主键，一个表只能有一个，不能重复，不能为空
• NOT NULL：非空，不能为空
• UNIQUE：唯一，不能重复，可以有多个空
• FOREIGN KEY：外键，定义在从表上，主表必须有主键约束或唯一约束。从表不能添加主表没有的数据，但是可以添加空值（前提是主表字段允许为空）。需要先删从表的数据，再删主表的数据
• CHECK (条件)：控制这列的数值符合一定的条件
• AUTO_INCREMENT：自增长，默认从1开始，每次添加数据就自己增长，一般和主键配合使用

修改表名

RENAME TABLE '表名' TO '新表名'

查看表的所有列

DESC '表名'

添加列

ALTER TABLE '表名' ADD '列名' 数据类型

修改列

ALTER TABLE '表名' MODIFY '列名' 数据类型

删除列

ALTER TABLE '表名' DROP '列名'

 视图

基表修改视图也会修改，修改视图，基表也会改变

创建视图

CREATE VIEW '视图名' 
AS 
SELECT语句

更新成新的视图

ALTER VIEW '视图名' 
AS 
SELECT语句

删除视图

DROP VIEW '视图名','视图名',...

数据

添加数据

INSERT INTO '表名' ('列名','列名',...,'列名')
VALUES(第一列数据,第二列数据,...,第n列数据),(第一列数据,...),...()

复制数据

INSERT INTO '表名' ('列名','列名',...,'列名')
    SELECT '列名','列名',...,'列名'
    FROM '复制表名'

修改数据

UPDATE '表名' SET '列名'='新值','列名'='新值',... WHERE '条件列名'='值'

删除数据

DELETE FROM '表名' WHERE '条件列名'='值'

查询

可选参数：DISTINCT（显示结果时去掉重复的数据）

普通查询，查询所有列时，可以将列名替换成*

SELECT DISTINCT '列名','列名',...,'列名' | *
FROM '表名'

操作查询，括号里还可以进行一些其他的数学运算

SELECT '列0',('列1'+'列2'+'列3') AS '别名'
FROM '表名'

 带条件查询，WHERE后面可以写各种数学表达式，表达式之间可以用AND、OR连接

SELECT '列名','列名',...,'列名' 
FROM '表名'
WHERE '列名'>数值 OR '列名'<'列名'...

pattern：正则表达式   

• %：0到多个任意字符
• _：单个任意字符

排序查询结果，列名也可以用别名或数学表达式

可选参数：ASC （升序）DESC（降序），默认asc

SELECT '列名','列名',...,'列名' 
FROM '表名'
ORDER BY '列名' DESC

函数查询

SELECT 函数名('列名','列名',...,'列名' | * )
FROM '表名'
WHERE '列名'>数值 OR '列名'<'列名' ...

函数	作用
COUNT()	返回符合条件行的总数
SUM()	返回符合条件行的和
AVG()	返回符合条件行的平均数
MAX()/MIN()	返回符合条件行的最大值/最小值
CONCAT()	拼接字符串，将多列拼接成一列
LENGTH()	返回字节长度
TRIM()	去除前端和后端的空格
ABS()	返回绝对值
MOD()	求余数
FORMAT(数值,n)	保留n位小数，四舍五入
RAND()	返回随机数，范围[0,1]
CURRENT_DATE()	返回当前日期
CURRENT_TIME()	返回当前时间
CURRENT_TIMESTAMP()	返回当前日期和时间
NOW()	返回当前日期和时间
DATE_ADD(date,INTERVAL 数字 类型)	在date的基础上加上日期或者时间 类型：MINUTE、YEAR、DAY...
DATE_SUB(date,INTERVAL 数字 类型)	在date的基础上减去日期或者时间 类型：MINUTE、YEAR、DAY...
DATEDIFF(date1,date2)	返回两个日期的差（单位：天）

分组统计查询，按指定列分组显示查询结果，并且按数学表达式过滤。HAVING用来指定组的条件

SELECT 函数名('列名','列名',...,'列名' | * )
FROM '表名'
GROUP BY '列名','列名'...
HAVING 数学表达式

• WHERE是分组之前进行过滤，不满足条件的不参与分组，HAVING是分组之后对结果进行过滤。
• WHERE不能对聚合函数进行判断，而HAVING可以。

多表查询

SELECT '表名'.'列名' AS '别名', ...
FROM '表名','表名'
WHERE '表名'.'列名'='表名'.'列名'...

单表自连接查询

SELECT '别名1'.'列名' AS '别名3', ...
FROM '表名' '别名1','表名' '别名2'
WHERE '别名1'.'列名'='别名2'.'列名'...

嵌套查询（子查询），IN：表示在这个集合里面，ALL：集合中所有，ANY：集合中任意

SELECT '列名','列名',...
FROM '表名'
WHERE '列名' IN (
        SELECT '列名'
        FROM '表名'
        WHERE '列名'=数值)

SELECT '列名','列名',...
FROM '表名'
WHERE ('列名','列名',...) = (
        SELECT '列名','列名',...
        FROM '表名'
        WHERE '列名'=数值)

SELECT '列名','列名',...
FROM '表名'
WHERE '列名' > ALL | ANY (
        SELECT '列名'
        FROM '表名'
        WHERE '列名'=数值)

子表查询（临时表查询）

SELECT '列名','表名'.'列名',...
FROM (
    SELECT '列名',函数('列名') AS '别名',...
    FROM '表名'
    GROUP BY '列名'
) '表别名','表名',...
WHERE '表名'.'列名'='表别名'.'列名',...

左右外连接查询

SELECT '列名','列名' ...
FROM '表名' LEFT | RIGHT JOIN '表名'
ON '表名'.'列名'='表名'.'列名'

索引

索引类型：

• 主键索引，主键自动为主索引
• 唯一索引，UNIQUE INDEX，满足唯一约束的同时也有唯一索引
• 普通索引，INDEX
• 全文索引，FULLTEXT INDEX，（一般不用SQL自带的全文索引，而是用Solr和ElasticSearch）

创建索引

CREATE 索引类型 '索引名' ON '表名' ('列名')

ALTER TABLE '表名' ADD 索引类型 '索引名' (列名)

查看索引

SHOW INDEX FROM '表名'

删除索引

DROP INDEX '索引名' ON '表名'

删除主键索引

ALTER TABLE '表名' DROP PRIMARY KEY

事务

开始一个事务

START TRANSACTION

设置保存点

SAVEPOINT 保存点名

回退事务

ROLLBACK TO 保存点名

提交事务

COMMIT

事务隔离级别

隔离级别	脏读	不可重复读	幻读	锁
读未提交 （READ UNCOMMITTED）	√	√	√	不加锁
读已提交 （READ COMMITTED）	×	√	√	不加锁
可重复读 （REPEATABLE READ）默认	×	×	×	不加锁
可串行化 （SERIALIZABLE）	×	×	×	加锁

查看当前会话隔离级别

SELECT @@tx_isolation

设置当前对话隔离级别

SET SESSION TRANSACTION ISOLATION LEVEL 隔离级别