前言

均为个人总结，难免有失误，如有请指正！

正文

数据定义

创建数据库

CREATE DATABASE IF NOT EXISTS database_name;

可以使用关键字来指定要创建的数据库的字符集或排序方式

CHARACTER SET 字符集;
COLLATE 排序规则;

查看数据库

SHOW DATABASES;

查看当前正在使用的库

SELECT DATABASE();

查看指定库下的所有表

SELECT TABLES FROM 数据库名;

查看创建库的信息

SHOW CREATE DATABASE 数据库名;

选中库

USE 数据库名;

注意

数据库不能改名，改名操作是通过建立新数据库，将所有表复制到新数据库来实现的

删除库

DROP DATABASE IF EXISTS  数据库名称;

注释

COMMENT '注释内容'

创建表

CREATE TABLE [IF NOT EXISTS] table_name (
    列名 类型 [列可选约束] [COMMENT '注释'],
    列名 类型 [列可选约束] [COMMENT '注释']
)[表可选约束][COMMENT '注释']

数据类型

整数

存在有五种类型，其中标准是SMALLINT和INT，而TINYINT，MEDIUMINT和BIGINT是mysql的拓展

添加unsigned符号用来声明无符号类型

user_age tinyint unsigned COMMENT 'tinyint类型，从0开始'

类型	字节
TINYINT	1
SMALLINT	2
MEDIUMINT	3
INT	4
BIGINT	8

浮点类型

不推荐使用浮点类型

类型	字节数	M（整数+小数位数）	D（小位数）
FLOAT(M, D)	4	max 24	max 8
DOUBLE(M, D)	8	max 53	max 30

定点数

DECIMAL(M, D)

M最大位为65，D最大为30

存储字节是动态计算的

字符类型

CHAR(M),固定长度，不足长度会补全到M，取出的时候不会受到补全影响

VARCHAR(M)，可变长度，一行数据不能超过65535字节

TEXT 大文本类型，不需要指定长度，一行最长65535，但是不受一行的限制

不建议使用TEXT，因为性能很烂，建议存为文本文件，使用varchar记录文件地址

时间类型

类型	字节	格式	示例
year	1	yyyy	‘2009’
time	3	hh:mm:ss	’10:10:10′
date	3	yy-mm-dd	2000:10:10
datetime	8	yy-mm-dd	hh:mm:ss

插入默认添加时间

datetime default current_timestamp

修改默认更改时间

datetime default current_timestamp on update current_timestamp

修改表

修改表中列

添加列

ALTER TABLE table_name ADD 字段名 字段类型 [FIRST|AFTER 字段名]

修改列名

ALTER TABLE 表名 CHANGE 原字段名 新字段名 新字段类型 [FIRST|AFTER 字段名]

修改列类型

ALTER TABLE 表名 MODIFY 字段名 新字段类型 [FIRST|AFTER 字段名]

删除列

ALTER TABLE 表名 DROP 字段名

修改表名

ALTER TABLE 表名 RENAME TO 新表名

删除表

删除表

DROP TABLE 表1

清空表数据

TRUNCATE TABLE 表名

DML数据操作语言

插入数据

插入一行所有字段的数据

INSERT INTO 表名 VALUES(value1, value2...) //有多少字段就对应多少个值

插入一行指定字段的数据

INSERT INTO 表名(列名1, 列名2...) VALUES(对应值1,对应值2...)

修改数据

修改表中全部的数据

UPDATE table_name
SET column1 = value, column2 = value, ..., columnn = valuen

修改符合条件的数据

UPDATE table_name
SET ... 
WHERE condition

删除数据

删除表中全部的数据

DELETE FROM TABLE

删除表中指定行的数据

DELETE FROM TABLE WHERE condition

DQL数据查询语句

关键字

SELECT

单表查询

指定表

SELECT 列名1, 列名2, 列名3 FROM 表名;
SELECT 表名.列名, 表名.* FROM 表名;

查询列起别名

SELECT 列名1 AS `别名1` FROM 表名;

去除重复行

SELECT DISTINCT 列名1 FROM table_name

查询常数

SELECT '常量' as `指定列名`, 列 FROM 表;

显示表结构

使用命令查看表中的列和列的特征

语法：

DESCRIBE
DESC

字段介绍

Field：字段名
Type：类型
Null：是否可以为空
Key：索引
Default：默认值
Extra：额外描述

条件查询

SELECT xx FROM xx WHERE condition

条件判断语句可以用and or xor来连接不同条件

运算符

算数运算符

% MOD模运算符

*

–

+

/ 浮点除法

DIV 整数除法

比较运算符

特殊的在此处写出

<> 不等于

<=> NULL安全等于运算符。不推荐使用

IS NULL

IS NOT NULL

BETWEEN … AND … 是否在值之间

NOT BETWEEN … AND …是否不在值之间

IN(1, 5) 是否是1或者5

NOT IN(1, 5) 是否不是1/5

LIKE 模糊匹配

NOT LIKE 模糊匹配取反

% 匹配0个或者多个字符

_只能匹配一个字符

FIND_IN_SET(要查询的内容,要搜寻的集合)

函数

ABS 返回绝对值

SIGN返回符号，正数1，负数-1，0返回0

CEL 返回向上取整的整数

FLOOR返回向下取整的整数

LEAST返回列表中的最小值

GREATEST返回列表中的最大值

RAND 返回零到一的随机值

ROUND(x)四舍五入取整

ROUND(x, y)四舍五入取整，y保留小数位数

SQRT x的平方

字符串函数

mysql中，字符串的位置从1开始

CHAR_LENGTH(s) 返回s的字符数

CONCAT(s1,…,sn) 链接内部参数为一个字符串

FIND_IN_SET 返回字符串s1，在s2中出现的位置

时间函数

CURDATE() 返回当前日期

NOW()返回当前时间

DATE_ADD(datetime, INTERVAL exprtype)返回给定日期和interval时间段的相差的时间

DATE_SUB 返回相隔的日期

ADDTIME(t1, t2)t2为秒，返回t1+t2的日期

DATEDIFF，返回日期间隔天数

流程控制语句

IF

条件函数，IF(condition, true_value, false_value)

如果表达式成立，则返回正确值，否则返回错误值

IFNULL

IFNULL(column, null_value)

当指定列column值为NULL，取null_value值作为结果

CASE表达式

CASE
    WHEN condition THEN result
    ...
    ELSE default_result
END

格式2

CASE expr
    WHEN value1 THEN result
    ...
    ELSE default_result
END

聚合函数

AVG(列名) 平均值

SUM() 和

MIN() 最小值

MAX() 最大值

COUNT() 非空值出现的次数，参数为1是行的数量

分组查询

概念

将数据行按照某一或者多特性进行分组，最后查询每组的特性，分组查询的结果只能是分组特性列或者聚合函数

格式

SELECT 分组列, 分组列, 聚合函数
FROM table_name
[WHERE condition]
[GROUP BY 分组列, 分组列... HAVING 分组后条件]

排序

SELECT 列,列, 函数
FROM table_name
[WHERE condition]
[ORDER BY 排序列1 asc, 排序列2 desc]

asc表示正序，从小到大，desc表示倒序，从大到小，默认值是asc

分页查询(MySQL方言)

SELECT c1, c2, func
FROM table
WHERE condition
LIMIT[index] 行数

位置偏移量代表从哪里开始查，比如LIMIT[1,2]表示从第二行开始查，查两行

关键词顺序

SELECT FROM WHERE AND GROUP BY HAVING ORDER BY LIMIT

约束

表级别的规定，数据的限制语法

确保数据的准确性，可靠性，正确性

添加时机：创建表时直接添加，创建表之后，通过ALTER TABLE语句添加

分类

域（列）级约束

NOT NULL 非空约束

DEFAULT 默认值约束

CHECK 检查约束

实体（行）约束

PRIMARY KEY 主键唯一且不为空

UNIQUE 限制某一列值中唯一

AUTO_INCREMENT数字类型字段插入数据自增长约束

引用（多表）约束

FOREIGN KEY 限定表中某一列正确引用其他表的数据值

NOT NULL

所有类型都可以为NULL，非空约束只可以添加到列上

可以建表时添加，也可以使用MODIFY更改

ALTER TABLE table_name MODIFY xxx NOT NULL;

DEFAULT

默认值约束不能添加到唯一或者主键上

使用关键字DEFAULT声明

CHECK

CHECK 表达式

不推荐使用check约束进行数据检查，建议用程序来实现

check实现简单约束

UNIQUE

CREATE TABLE table_name(
    字段名 数据类,
    [constraint 约束名] UNIQUE KEY(字段名)
)

主键约束

在任何情况下，确保表中的数据至少有一列是不重复的，避免整行数据的不重复

永远不重复，且不为空的列就可以称为主键列

分为自定义主键和自然主键

每个表中只能有一个主键

使用PRIMARY来创建主键，可以提高查询速度，建议使用

为了确保主键不重复且不为空，可以添加主键约束

PRIMARY KEY

自增长约束

AUTO_INCREMENT

只能添加到键列，每一张表只能有一个自增约束，数据类型必须为整数，设置为0和null会自增赋值，设置的是非零或者非空会真实设置值

外键

外键约束可以确保外键的值正确引用值

每个表可以有多个外键，被引用的是主表，外键表是子表

多表查询

垂直合并

UNION / UNION ALL

不要求主外键，将多个表结果汇总

UNION 会在合并记录的同时去掉重复数据

UNION ALL 合并记录，并且不去除重复数据

只要求合并的结果之间的列数和对应列的类型相同即可

一行之中所有列值都认定为相同，认定为重复行

连接查询，要求有主外键

SELECT * FROM a
UNION
SELECT * FROM b

水平合并

内连接

标准语法：

SELECT * FROM 表1 [inner] join 表2 on 表1.主键 = 表2.外键 

外链接

语法：

SELECT * FROM 表1 LEFT OUTER JOIN 表2 ON 表1.主键 = 表2.外键
SELECT * FROM 表1 RIGHT OUTER JOIN 表2 ON 表1.主键 = 表2.外键

和内连接不同的事，可以通过左和右指定一个逻辑主表，逻辑主表数据一定会查询到

新特性和其他

事务

事务内部成功才会成功，事务内部失败会回滚

具有ACID特性，原子性，一致性，隔离性，持久性

A 不可被分割，要么都发生，要么都不发生

C 数据一致，要么都成功，要么都失败

I 不受影响，隔离开来

D 一旦修改，永久改变

使用SET AUTOCOMMIT = FALSE来取消自动提交事务
从而使用COMMIT手动提交 或者使用ROLLBACK回滚

建议方法：

#在自动提交模式下开启一个事务
start transaction

#sql命令

commit / rollback

隔离

隔离级别	概述	脏读	不可重复读	幻读
read-uncommitted	读未提交事务数据	是	是	是
read-committed	读已提交事务数据	否	是	是
repeatable-read	可重复读	否	否	是（小概率）
serializable	序列化	否	否	否

脏读：一个事务读取了另一个事务的未提交数据

不可重复读：一个事务读取了另一个事务提交的修改数据

幻读：一个事务读取了另一个事务提交的新增，删除的记录情况，记录数不一样

MySQL默认是第三级别，建议修改第二级别

权限

MySQL使用权限来限制用户对数据库和表的访问，权限可以授予到数据库级别，表级别或特定操作上

权限	权限功能
SELECT	允许用户查询
INSERT	允许用户插入新数据
UPDATE	允许用户更新表中现有数据
DELETE	允许用户删除数据
CREATE	允许用户创建新的数据库或表
DROP	允许用户删除库或者表
GRANT OPTION	允许用户将自己拥有的权限授予其他用户
ALL PRIVILEGES	允许用户执行所有操作

用户权限

创建用户：

CREATE USER 'username'@'localhost' IDENTIFIED BY 'password'

赋予权限：

GRANT ALL PRIVILEGES ON database_name.table_name TO 'username@localhost'

指定权限：

GRANT SELECT ON …….

撤销权限：

REVOKE ALL PRIVILEGES ON …..

查看权限：

SHOW GRANTS FOR ‘username’@’localhost’

删除用户

DROP USER ‘用户名’

数据备份还原

全量备份恢复

备份单库和单表

mysqldump -u username -p password database_name table_name > backup.sql

备份单库和多表

mysqldump -u username -p passsword database_name table_name1 ... table_namen > backup.sql

备份单库的所有表

mysqldump -u username -p password database_name > backup.sql

如

mysqldump -uroot -p123456 database_name > C:/backup.sql

还原

mysql -u username -p database_name < backup.sql

Binlog

MySQL的二进制日志记录，binlog记录数据库所有的增删改查的操作，同时也包括操作的执行时间，利用日志文件实现：误删数据恢复，增量复制，主从同步

查看：

SHOW BINARY LOGS;

查看某个日志文件的命令清单：

SHOW BINLOG EVENTS;