本系列文章：
Mysql（一）三大范式、数据类型、常用函数、事务
Mysql（二）Mysql SQL练习题
Mysql（三）索引、视图、存储过程、触发器、分区表
Mysql（四）存储引擎、锁
Mysql（五）Mysql架构、数据库优化、主从复制
Mysql（六）慢查询、执行计划、SQL语句优化
SQL（Structured Query Languange），结构化查询语言。
1）SQL语句可以单行或多行书写，以分号结尾。
2）可使用空格和缩进来增强语句的可读性。
3）MySQL数据库的SQL语言不区分大小写，关键字建议使用大写，对所有列和表名使用小写，这样做使代码更容易阅读和调试。
4）3种注释：
单行注释： – 注释内容
单行注释：# 注释内容（mysql特有）
多行注释：/*注释*/
Mysql中SQL语句的分类可以分成3类：数据定义语言DDL、数据操纵语言DML、数据控制语言DCL。
DDL：数据定义语言，这些语句主要用来创建、修改、删除数据库的逻辑结构，其中包括表结构，视图和索引等。常用的关键字有主要包括create、drop、alter等，truncate也是DDL关键字。
DML：数据操纵语言，这些语句用于添加、删除、更新和查询数据库中的数据，并检查数据完整性。常用的关键字有insert、delete、update、select等。
DCL：数据控制语言，这些语句主要用来控制数据库的访问权限。常用的关键字有主要包括grant、revoke、commit、rollback等。DCL主要用来控制数据库的权限。
DDL主要操作数据库对象。
简单来说，DDL就是对数据库内部的对象进行创建、删除、修改等操作的语言。示例：
#创建数据库
CREATE DATABASE dbname
#查看系统中都有哪些数据库
SHOW DATABASES
以Mysql为例，有几个内置的数据库，较重要的有：
information_schema：主要存储系统中的一些数据库对象信息，比如用户表信息、列信息、权限信息、分区信息等；
mysql：存储系统的用户权限信息。
#选择某个数据库
USE dbname
#查看当前数据库中的表
SHOW TABLES
#删除数据库
DROP DATABASE dbname
创建表：
CREATE TABLE tablename(
column_name_1 column_type_1 constraints,
column_name_2 column_type_2 constraints,
...
column_name_n column_type_n constraints
)
column_name_1是列名，column_type_1是列字段的数据类型，constraints是列的约束条件。
#查看表定义
DESC tablename
#查看表的创建语句
SHOW CREATE TABLE tablename
#删除表
DROP TABLE tablename
#删除表更常见的用法
DROP TABLE IF EXISTS tablename
#修改字段类型
ALTER TABLE tablename MODIFY [COLUMN] column_definition
#修改字段类型示例
ALTER TABLE emp MODIFY name VARCHAR(20)
#增加字段
ALTER TABLE tablename ADD [column] column_definition
#增加字段示例
ALTER TABLE emp ADD COLUMN age INT(3)
#删除字段
ALTER TABLE tablename DROP [column] col_name
#修改字段名
ALTER TABLE tablename CHANGE [column] old_col_name column_definition
#示例：将age改名为age1，并修改字段类型
ALTER TABLE emp CHANGE age age1 INT(4)
使用上面命令增加的字段默认都在表的最后位置，而CHANGE/MODIFY不会修改字段的位置。如果要修改字段的位置，要用AFTER/FIRST等关键字：
#将birth字段添加到ename之后
ALTER TABLE emp ADD birth DATE AFTER ename
#将age字段放在最前面
ALTER TABLE emp MODIFY age INT(3) FIRST
更改表名：
ALTER TABLE tablename RENAME [TO] new_table
#示例
ALTER TABLE emp RENAME emp1
DML主要操作数据库中的数据。
简单来说，DML操作指对数据库中数据的操作，主要包括增删改查。
插入数据：
#可以不指定字段名称，但value后面的顺序应该和字段的排列顺序一致
INSERT INTO tablename (field1,field2,...,fieldn) VALUES (value1,value2,...valuen)
使用INSERT时，可以一次性插入多条数据：
INSERT INTO tablename (field1,field2,...,fieldn)
VALUES
(record1_value1,record1_value2,...record1_valuen)
(record2_value1,record2_value2,...record2_valuen)
...
(recordn_value1,recordn_value2,...recordn_valuen)
更新数据：
UPDATE tablename SET field1=value1,field2=value2,...,fieldn=valuen
#示例
UPDATE emp SET sal=4000 WHERE ename='lisa'
删除数据：
DELETE FROM tablename [WHERE CONDITION]
#示例
DELETE FROM emp WHERE ename='tony'
如果使用DELETE关键字时，后面没加条件，就会把整张表的数据删掉。
：
#查询数据
SELECT * FROM tablename [WHERE CONDITION]
#数据去重
SELECT DISTINCT deptno FROM emp
WHERE后面跟的条件中，除了可以使用=，还可以使用>、<、>=、<=、！=等比较运算符，多个条件之间可以用OR、AND等逻辑运算符。
数据排序：
SELECT * FROM tablename [WHERE CONDITION] [ORDER BY field1 [DESC|ASC],field2 [DESC|ASC],...,fieldn [DESC|ASC]]
DESC表示按字段进行降序排列，ASC表示升序排列，默认是升序排列。
显示一部分数据：
SELECT ... [LIMIT offset_start,row_count]
offset_start表示其实偏移量，从0开始；row_count表示要显示的行数。示例：
#显示前3条数据
SELECT * FROM emp ORDER BY sal LIMIT 3
#显示从第2条数据开始的3条数据
SELECT * FROM emp ORDER BY sal LIMIT 1,3
LIMIT经常和ORDER BY一起使用，进行数据的分页显示。
聚合语句：
SELECT [filed1,field2,...,fieldn] fun_name
FROM tablename
[WHERE where_condition]
[GROUP BY field1,field2,...,fieldn]
[HAVING where_condition]
fun_name表示聚合函数，常用的有sum、count(*)、avg、max、min等。GROUP BY后面跟的是要进行分类聚合的字段。HAVING表示对分类后的结果在进行条件过滤。
Group BY用来创建分组，如果分组中有NULL值，将NULL作为一个分组返回。如果列中有多行NULL值，它们将分为一组。
HAVING和WHERE的区别：
HAVING是对聚合后的结果进行条件的过滤，而WHERE是在聚合前就对记录进行过滤。如果逻辑允许，尽可能用WHERE先过滤条件，因为这样结果集减小，聚合的效率将大大提高，最后再根据逻辑看是否用HAVING进行过滤。
表的连接分为内查询和外查询，他们最主要的差别是：内连接仅仅选出两张表中互相匹配的数据，而外连接会选出一些不匹配的数据。外连接又分为左连接和右连接：
- 左连接：包含左边表中的所有数据和右边表中未匹配的数据；
- 右连接：包含右边表中的所有数据和左边表中未匹配的数据。
某些情况下，当进行查询时，需要的条件是另外一个SELECT语句的结果，这时就要用到子查询。用于子查询的关键字主要包括in、not in、=、！=、exists、not exists等。示例：
SELECT * FROM emp WHERE deptno IN(SELECT deptno FROM dept)
如果子查询数据唯一，可以用=替代in，示例：
SELECT * FROM emp WHERE deptno = (SELECT deptno FROM dept LIMIT 1)
有时，子查询可以转化为表连接，示例：
SELECT * FROM emp WHERE deptno IN(SELECT deptno FROM dept)
SELECT emp.* FROM emp,dept WHERE emp.deptno=dept.deptno
表连接很多时候用于优化子查询。
将多个查询的数据合并用到的关键字是UNION/UNION ALL：
SELECT * FROM t1
UNION|UNION ALL
SELECT * FROM t2
...
UNION|UNION ALL
SELECT * FROM tn
UNION和UNION ALL的区别：
- UNION ALL：把结果集直接合并在一起；
- UNION：将UNION ALL的结果进行去重。
因为UNION有个去重的过程，所以UNION ALL的效率较高。
where可以使用索引，having不能使用索引，只能在临时结果集操作；where 后面不能使用聚集函数，having是专门使用聚集函数的。一个聚集函数从多个输入行中计算出一个结果，比如count（数目）， sum（和），avg（ 均值 ）， max（最大值）和min（最小值）。
后者不需要数据字典。后者可以建立索引进行优化，前者无法优化。后者的可读性比前者要高。 数据库的三大范式的设计目的是减少数据冗余。
第一范式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值（列不可再分），就说明该数据库表满足了第一范式。
第一范式的合理遵循需要根据系统的实际需求来定。比如某些数据库系统中需要用到“地址”这个属性，本来直接将“地址”属性设计成一个数据库表的字段就行。但是如果系统经常会访问“地址”属性中的“城市”部分，那么就非要将“地址”这个属性重新拆分为省份、城市、详细地址等多个部分进行存储，这样在对地址中某一部分操作的时候将非常方便。
这样设计才算满足了数据库的第一范式，示例：
第二范式：确保表中的每列都和主键相关。
第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。
比如这样的表是不合适的，可以拆分成学生表、教师表和中间表：
满足第三范式（3NF）必须先满足第二范式（2NF）。简而言之，第三范式（3NF）要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。例如，存在一个部门信息表，其中每个部门有部门编号（dept_id）、部门名称、部门简介等信息。那么员工信息表中列出部门编号后就不能再将部门名称、部门简介等与部门有关的信息再加入员工信息表中。如果不存在部门信息表，则根据第三范式（3NF）也应该构建它，否则就会有大量的数据
冗余。
简而言之，第三范式就是属性不依赖于其它非主属性。第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。
比如在设计一个订单数据表的时候，可以将客户编号作为一个外键和订单表建立相应的关系。而不可以在订单表中添加关于客户其它信息（比如姓名、所属公司等）的字段。如下面这两个表所示的设计就是一个满足第三范式的数据库表。
禁止非主键列和其他非主键列一对多关系。在同一张表中，非主键的列与非主键列存在一对多的关系会造成存储大量的冗余字段，当出现这种状况时，应进行合理拆表。
在考试分数等级表中，Grade和Subject存在一对多的关系，浪费了存储空间，且会降低查询效率，拆分后业务的可扩展性更大，也节省了不必要的冗余信息存储所浪费的空间。
列不可分。列必须直接依赖主键。表里面的列不能出现其它表的非主键字段。三者都可以删除表中的数据，三者的差别：
| Delete | Truncate | Drop | |
|---|---|---|---|
| 回滚 | 可回滚 |
不可回滚 | 不可回滚 |
| 删除内容 | 表结构还在，删除表的全部或者一部分数据行 |
表结构还在，删除表中的所有数据 |
从数据库中删除表结果和所有的数据行，索引和权限也会被删除 |
| 删除速度 | 删除速度慢，需要逐行删除 | 删除速度快 | 删除速度最快 |
在想删除部分数据行时候，用delete；
在保留表结构而删除所有数据的时候用truncate；
在不再需要一张表的时候，用drop。
执行速度：DROP>TRUNCATE>DELETE。TRUNCATE和DELETE只删除数据，而DROP则删除整个表（结构和数据）。exists用于对外表记录做筛选。 exists会遍历外表，将外查询表的每一行，代入内查询进行判断。当exists里的条件语句能够返回记录行时，条件就为真，返回外表当前记录。反之如果exists里的条件语句不能返回记录行，条件为假，则外表当前记录被丢弃。exists使用示例：
select a.* from A a where exists(select 1 from B b where a.id=b.id)
in是先把后边的语句查出来放到临时表中，然后遍历临时表，将临时表的每一行，代入外查询去查找。in使用示例：
select * from A where id in(select id from B)
子查询的表比较大的时候，使用exists可以有效减少总的循环次数来提升速度； 当外查询的表比较大的时候，使用in可以有效减少对外查询表循环遍历来提升速度。
not in和not exists：如果查询语句使用了not in，那么内外表都进行全表扫描，没有用到索引；而not exists的子查询依然能用到表上的索引。所以无论那个表大，not exists都比not in要快。
创建表的时候，同时可以指定所插入数据的一些规则，比如说某个字段不能为空值，某个字段的值（比如年龄）不能小于零等等，这些规则称为约束。约束是在表上强制执行的数据校验规则。
数据库中的五大约束：
五大约束示例：
--添加主键约束
Alter table 表名 add Constraint 主键名 primary key(字段)
--添加唯一约束
Alter table 表名 add Constraint 约束名 unique(字段)
--添加默认约束
Alter table 表名 add Constraint 约束名 default（默认内容） for 字段名
--添加检查约束
Alter table 表名 add Constraint 约束名 check (字段表达)
--添加外键约束
Alter table 表名 add Constraint 约束名 foreign key(字段) references 表名（字段名）
Mysql中常用的关联查询有左外连接、右外连接、全连接、自连接等。
创建两个表：t_employee(员工表)和t_dept(部门表)。建表及插入测试数据示例：
DROP TABLE t_dept;
CREATE TABLE t_employee(
id INT,
empName VARCHAR(32),
dept VARCHAR(32),
bossId VARCHAR(32)
)CHARSET UTF8;
DROP TABLE t_dept;
CREATE TABLE t_dept(
id INT,
deptName VARCHAR(32)
)CHARSET UTF8;
INSERT INTO t_employee(id,empName,dept,bossId) VALUES (1,'张三','1',null);
INSERT INTO t_employee(id,empName,dept,bossId) VALUES (2,'李四','2','1');
INSERT INTO t_employee(id,empName,dept,bossId) VALUES (3,'王五','2','2');
INSERT INTO t_employee(id,empName,dept,bossId) VALUES (4,'陈六','3','3');
INSERT INTO t_employee(id,empName,dept,bossId) VALUES (5,'赵七',null,'4');
INSERT INTO t_dept(id,deptName) VALUES (1,'软件开发部');
INSERT INTO t_dept(id,deptName) VALUES (2,'软件测试部');
INSERT INTO t_dept(id,deptName) VALUES (3,'市场运维部');
INSERT INTO t_dept(id,deptName) VALUES (4,'人力资源部');
此时表中的数据：
语法为"INNER JOIN … ON …"，INNER可以省略，内连接查出来的数据是两张表里都有的数据：
示例：
SELECT
e.empName,d.deptName
from t_employee e
JOIN t_dept d
ON e.dept = d.id;
结果：
语法为"LEFT JOIN … ON …"，左外连接查出来的数据是左边表中全部数据和右边表中匹配上的数据，右边表中未匹配的数据以null显示：
示例：
SELECT e.empName,d.deptName
FROM t_employee e
LEFT JOIN t_dept d
ON d.id = e.dept;
结果：
语法为"RIGHT JOIN … ON …"，右外连接查出来的数据是右边表中全部数据和左边表中匹配上的数据，左边表中未匹配的数据以null显示：
示例：
SELECT e.empName,d.deptName
FROM t_employee e
RIGHT JOIN t_dept d
ON d.id = e.dept;
结果：
语法为UNION"，UNION操作符用于合并两个或多个SELECT语句的结果集。UNION内部的每个SELECT语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每个SELECT语句中的列的顺序必须相同。
示例：
SELECT e.empName,d.deptName
FROM t_employee e
LEFT JOIN t_dept d
ON e.dept = d.id
UNION
SELECT e.empName,d.deptName
FROM t_employee e
RIGHT JOIN t_dept d
ON e.dept = d.id;
结果：
Mysql的一般书写顺写为：
select <要返回的数据列>
from <表名>
<join, left join, right join...> join <join表>
on <join条件>
where <where条件>
group by <分组条件>
having <分组后的筛选条件>
order by <排序条件>
limit <行数限制>
一个查询语句同时出现了where、group by、having、order by的时候，执行顺序和编写顺序是：
- 执行where xx
对全表数据做筛选，返回第1个结果集。- 针对第1个结果集使用group by分组，返回第2个结果集。
- 针对第2个结果集中的每1组数据执行select xx，有几组就执行几次，返回第3个结果集。
- 针对第3个结集执行having xx进行筛选，返回第4个结果集。
- 针对第4个结果集排序。
按照执行顺序的关键词首字母分别是W（where）->G（Group）->S（Select）->H（Having）->O（Order），对应汉语首字母可以编成容易记忆的顺口溜：我(W)哥(G)是(SH)偶(O)像。
from
<表名> # 笛卡尔积
on
<筛选条件> #对笛卡尔积的虚表进行筛选
<join, left join, right join...> join
<join表> #指定join，用于添加数据到on之后的虚表中，例如left join会将左表的剩余数据添加到虚表中
where
<where条件> #对上述虚表进行筛选
group by
<分组条件> #分组
<sum()等聚合函数> #用于having子句进行判断，在书写上这类聚合函数是写在having判断里面的
having
<分组筛选> #对分组后的结果进行聚合筛选
select
<返回数据列表> #返回的单列必须在group by子句中，聚合函数除外
distinct
order by
<排序条件> #排序
limit
<行数限制>
执行流程：
select * from table_1, table_2; 与 select * from table_1 join table_2; 的结果一致，都是表示求笛卡尔积； 主键：数据库表中对储存数据对象予以唯一和完整标识的数据列或属性的组合。一个数据列只能有一个主键，且主键的取值不能缺失，即不能为空值（Null）。简单理解：唯一+非空。
主键的最好习惯：
- 不更新主键列中的值；
- 不重用主键列中的值；
- 不在主键列中使用可能会更改的值。
外键：在一个表中存在的另一个表的主键称此表的外键。
LIMIT子句可以被用于强制SELECT语句返回指定的记录数。LIMIT接受一个或两个数字参数。参数必须是一个整数常量。如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。初始记录行的偏移量是0(而不是 1)。
--检索记录行 6-15
SELECT * FROM table LIMIT 5,10;
为了检索从某一个偏移量到记录集的结束所有的记录行，可以指定第二个参数为 -1：
--检索记录行 96-last
SELECT * FROM table LIMIT 95,-1;
如果只给定一个参数，它表示返回最大的记录行数目：
--检索前 5 个记录行
SELECT * FROM table LIMIT 5;
换句话说，LIMIT n等价于LIMIT 0,n。
MySQL服务器通过权限表来控制用户对数据库的访问，权限表存放在mysql数据库里，由mysql_install_db脚本初始化。这些权限表分别是：user、db、table_priv、columns_priv和host。下面分别介绍一下这些表的结构和内容：
user权限表：记录允许连接到服务器的用户帐号信息，里面的权限是全局级的。
db权限表：记录各个帐号在各个数据库上的操作权限。
table_priv权限表：记录数据表级的操作权限。
columns_priv权限表：记录数据列级的操作权限。
host权限表：配合db权限表对给定主机上数据库级操作权限作更细致的控制。这个权限表不受GRANT和REVOKE语句的影响。
Mysql是一个关系型数据库管理系统。在Web应用方面，Mysql是最好的RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一。
1、RDBMS的一些术语
数据库：数据库是一些关联表的集合。
表：表是数据的矩阵。
列：一列(数据元素) 包含了相同的数据, 例如邮政编码的数据。
行：一行（元组，或记录）是一组相关的数据，例如一条用户订阅的数据。
冗余：存储两倍数据，冗余可以使系统速度更快。
主键：主键是唯一的。一个数据表中只能包含一个主键，可以使用主键来查询数据。
外键：外键用于关联两个表。
复合键：复合键（组合键）将多个列作为一个索引键，一般用于复合索引。
索引：使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构，类似于书籍的目录。
2、Mysql的特点
- Mysql是一个数据库管理系统；
- Mysql数据库是关系型的；
一个关系型数据库将数据存储在不同的表中，而不是将所有的数据存储在一个大的存储区域中。- Mysql软件是开源的；
- Mysql数据库服务器速度快、可靠性高，扩展性强，且易于使用。
Mysql提供了多种数据类型，主要包括数值型、字符串类型、日期和时间类型。接下来以Mysql5.0为例，详细介绍Mysql中的各种数据类型。
| 整数类型 | 字节 | 最小值 | 最大值 | 备注 |
|---|---|---|---|---|
| TINYINT | 1 | 有符号-128(-27) 无符号0 |
有符号127(27-1) 无符号255(28-1) |
很小的整数 |
| SMALLINT | 2 | 有符号-32768(-215) 无符号0 |
有符号32767(215-1) 无符号65535(216) |
小的整数 |
| MEDIUMINT | 3 | 有符号- 8388608(-223) 无符号0 |
有符号8388607(223-1) 无符号1677215(224-1) |
中等大小的整数 |
| INT、INTEGER | 4 | 有符号- 2147483648(-231) 无符号0 |
有符号2147483647(231-1) 无符号4294967295(232-1) |
普通大小的整数 |
| BIGINT | 8 | 有符号-9223372036854775808(-263) 无符号0 |
有符号9223372036854775807(263-1) 无符号18446744073709551615(264-1) |
大的整数 |
| 浮点数类型 | 字节 | 最小值 | 最大值 | |
| FLOAT | 4 | ±1.175494351E-38 | ±3.402823466E+38 | 单精度浮点型，m总个数，d小数位 |
| DOUBLE | 8 | ±2.2250738585072014E-308 | ±1.7976931348623157E+308 | 双精度浮点型， m总个数，d小数位 |
| 定点数类型 | 字节 | 描述 | |
|---|---|---|---|
| DEC(M,D) DECIMAL(M,D) |
M+2 | 最大取值范围与DOUBLE相同，给定DECIMAL的有效取值范围由M和D决定 | 压缩严格的定点数 |
对于整型数据，Mysql支持在类型名称后面的小括号内指定显示宽度。例如int(5)表示当数值宽度小于5位的时候在数字前面，用空格填满宽度，如果不显示指定宽度则默认为int(11)。
对于小数的表示，MySQL分为两种方式：浮点数和定点数。浮点数包括float（单精度）和double（双精度），而定点数则只有decimal一种表示。定点数在MySQL内部以字符串形式存放，比浮点数更精确，适合用来表示货币等精度高的数据。
浮点数和定点数都可以用类型名称后加“(M,D)”的方式来进行表示，“(M,D)”表示该值一共显示M位数字（整数位+小数位），其中D位于小数点后面。
float和double在不指定精度时，默认会按照实际的精度来显示，而DECIMAL在不指定精度时，默认整数为10，小数为0。
decimal采用的是四舍五入。
float和double采用的是四舍六入五成双。四舍六入五成双：就是5以下舍弃5以上进位，如果需要处理数字为5的时候，需要看5后面是否还有不为0的任何数字，如果有，则直接进位，如果没有，需要看5前面的数字，若是奇数则进位，若是偶数则将5舍掉。
| 日期和时间类型 | 字节 | 最小值 | 最大值 | 说明 |
|---|---|---|---|---|
| DATE | 4 | 1000-01-01 | 9999-12-31 | YYYY-MM-DD |
| DATETIME | 8 | 1000-01-01 00:00:00 | 9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS |
| TIMESTAMP | 4 | 19700101080001 | 2038-01-19 03:14:07UTC（格林尼治时间） | YYYY-MM-DD HH:MM:SS |
| TIME | 3 | -838:59:59 | 838:59:59 | HH:MM:SS |
| YEAR | 1 | 1901 | 2155 | YYYY |
如果要用来表示年月日，通常用DATE来表示。
如果要用来表示年月日时分秒，通常用DATETIME表示。
如果只用来表示时分秒，通常用TIME来表示。
如果需要经常插入或者更新日期为当前系统时间，则通常使用TIMESTAMP来表示。
如果只是表示年份，可以用YEAR来表示，它比DATE占用更少的空间。YEAR有2位或4位格式的年。默认是4位格式。在4位格式中，允许的值是1901～2155和0000。在2位格式中，允许的值是70～69，表示从1970～2069年。
DATETIME是DATE和TIME的组合。
TIMESTAMP还有一个重要特点，就是和时区相关。当插入日期时，会先转换为本地时区后存放；而从数据库里面取出时，也同样需要将日期转换为本地时区后显示。这样，两个不同时区的用户看到的同一个日期可能是不一样的。
尽量使用timestamp，空间效率高于datetime（datetime占用8字节，timestamp占用4字节）。
如果需要存储微秒，可以使用bigint存储。
DATETIME表示的时间范围较大，TIMESTAMP表示的时间范围较小。
| 类型 | 范围 | 存储所需字节 | 说明 |
|---|---|---|---|
| char(M) | [0,m]，m的范围[0, 28-1] | m | 定长字符串 |
| varchar(M) | [0,m]，m的范围[0, 216-1] | m | 0-65535 字节 |
| tinyblob | 0-255(28 -1)字节 | L+1 | 不超过 255 个字符的二进制字符串 |
| blob | 0-65535( 216-1)字节 | L+2 | 二进制形式的长文本数据 |
| mediumblob | 0-16777215(224 -1)字节 | L+3 | 二进制形式的中等长度文本数据 |
| longblob | 0-4294967295( 232-1)字节 | L+4 | 二进制形式的极大文本数据 |
| tinytext | 0-255( 28-1)字节 | L+1 | 短文本字符串 |
| text | 0-65535(216 -1)字节 | L+2 | 长文本数据 |
| mediumtext | 0-16777215(224 -1)字节 | L+3 | 中等长度文本数据 |
| longtext | 0-4294967295( 232-1)字节 | L+4 | 极大文本数据 |
char类型占用固定长度，如果存放的数据为固定长度的建议使用char类型，如：手机号码、身份证等固定长度的信息。
表格中的L表示存储的数据本身占用的字节，L 以外所需的额外字节为存放该值的长度所需的字节数。
CHAR和VARCHAR很类似，都用来保存MySQL中较短的字符串。二者的主要区别在于存储方式的不同：CHAR列的长度固定为创建表时声明的长度，长度可以为从0～255的任何值；而VARCHAR列中的值为可变长字符串。
在检索的时候，CHAR删除了尾部的空格，而VARCHAR则保留这些空格。示例：
CREATE TABLE vc (v VARCHAR(4), c CHAR(4));
INSERT INTO vc VALUES ('ab ', 'ab ');
SELECT length(v),length(c) FROM vc;
结果为：
BINARY和VARBINARY类似于CHAR和VARCHAR，不同的是它们包含二进制字符串而不包含非二进制字符串。
varchar和char的使用策略：
对于经常变更的数据来说，CHAR比VARCHAR更好，因为CHAR不容易产生碎片。- 对于非常短的列，CHAR比VARCHAR在存储空间上更有效率。
- 使用时要注意只分配需要的空间。
尽量避免使用TEXT/BLOB类型，查询时会使用临时表，导致严重的性能开销。
由于utf8的每个字符最多占用3个字节。而MySQL定义行的长度不能超过65535，因此N的最大值计算方法为：(65535-1-2)/3。
减去1的原因是实际存储从第二个字节开始，减去2的原因是因为要在列表长度存储实际的字符长度，除以3是因为utf8限制：每个字符最多占用3个字节。
MyISAM：建议使用固定长度的数据列代替可变长度的数据列。MEMORY：目前都使用固定长度的数据行存储，因此无论使用CHAR或VARCHAR列都没有关系。两者都是作为CHAR类型处理。InnoDB：建议使用VARCHAR类型。对于InnoDB数据表，内部的行存储格式没有区分固定长度和可变长度列（所有数据行都使用指向数据列值的头指针），因此在OPTIMIZE TABLE table_name。可以使用合成的（Synthetic）索引来提高大文本字段（BLOB 或 TEXT）的查询性能。简单来说，合成索引就是根据大文本字段的内容建立一个散列值，并把这个值存储在单独的数据列中，接下来就可以通过检索散列值找到数据行了。但是，要注意这种技术只能用于精确匹配的查询（散列值对于类似<或>=等范围搜索操作符是没有用处的）。可以使用MD5()函数生成散列值，也可以使用SHA1()或CRC32()，或者使用自己的应用程序逻辑来计算散列值。请记住数值型散列值可以很高效率地存储。同样，如果散列算法生成的字符串带有尾部空格，就不要把它们存储在CHAR或VARCHAR 列中，它们会受到尾部空格去除的影响。合成的散列索引对于那些BLOB或TEXT数据列特别有用。用散列标识符值查找的速度比搜索BLOB列本身的速度快很多。在不必要的时候避免检索大型的BLOB或TEXT值。把BLOB或TEXT列分离到单独的表中。
- 浮点数存在误差问题；
- 对货币等对精度敏感的数据，应该用定点数表示或存储；
- 在编程中，如果用到浮点数，要特别注意误差问题，并尽量避免做浮点数比较；
- 要注意浮点数中一些特殊值的处理。
- 根据实际需要选择能够满足应用的最小存储的日期类型。如果应用只需要记录“年份”，那么用1个字节来存储的YEAR类型完全可以满足，而不需要用4个字节来存储的DATE类型。这样不仅仅能节约存储，更能够提高表的操作效率。
- 如果要记录年月日时分秒，并且记录的年份比较久远，那么最好使用DATETIME，
而不要使用TIMESTAMP。因为TIMESTAMP表示的日期范围比DATETIME要短得多。- 如果记录的日期需要让不同时区的用户使用，那么最好使用TIMESTAMP，因为日期类型中只有它能够和实际时区相对应。
Null值会占用更多的字节，且会在程序中造成很多与预期不符的情况。
密码散列，盐，用户身份证号等固定长度的字符串应该使用char而不是varchar来存储，这样可以节省空间且提高检索效率。
很多开发者都会创建一个VARCHAR(15)字段来存放字符串形式的IP而不是整形的IP。如果用整形来存放，只需要4个字节，并且你可以有定长的字段。而且，这会为你带来查询上的优势，尤其是当你需要使用这样的WHERE条件：IP between ip1 and ip2。
我们必需要使用UNSIGNED INT，因为IP地址会使用整个32位的无符号整形。
varchar的10代表了申请的空间长度,也是可以存储的数据的最大长度，而int的10只是代表了展示的长度，不足10位以0填充。也就是说，int(1)和int(10)所能存储的数字大小以及占用的空间都是相同的，只是在展示时按照长度展示。
varchar(10)：10位可变字符串，不足补空格,多10个字符。
char(10)表示存储定长的10个字符，不足10个就用空格补齐，占用更多的存储空间。
varchar(10)表示存储10个变长的字符，存储多少个就是多少个，空格也按一个字符存储，这一点是和char(10)的空格不同的，char(10)的空格表示占位不算一个字符。
FLOAT类型数据可以存储至多8位十进制数，并在内存中占4字节。
DOUBLE类型数据可以存储至多18位十进制数，并在内存中占8字节。
数字类型：尽量不要使用double。
字符类型：尽量不要使用text类型。
时间类型：尽量使用timestamp类型，因为其存储空间只需要datetime类型的一半。对于只需要精确到某一天的数据类型，建议使用date类型，因为其存储空间只需要3个字节，比timestamp还少。
| 运算符 | 作用 |
|---|---|
| + | 加法 |
| - | 减法 |
| * | 乘法 |
| /，DIV | 除法，返回商 |
| %，MOD | 除法，返回余数 |
除法运算和模运算中，如果除数为0，将是非法除数，返回结果为NULL。示例：
| 运算符 | 作用 |
|---|---|
| = | 等于 |
| != | 不等于 |
| < | 小于 |
| <= | 小于等于 |
| > | 大于 |
| >= | 大于等于 |
| BETWEEN | 存在于指定范围 |
| IS NULL | 为NULL |
| IS NOT NULL | 不为NULL |
| LIKE | 通配符匹配 |
| REGEXP或RLIKE | 正则表达式匹配 |
BETWEEN的用法格式：a BETWEEN min AND max。
| 运算符 | 作用 |
|---|---|
| NOT 或！ | 逻辑非 |
| AND 或&& | 逻辑与 |
| OR | 逻辑或 |
| XOR |
简单地说，字符集就是一套文字符号及其编码、比较规则的集合。
Mysql常用字符集：
| 字符集 | 是否定长 | 编码方式 | 说明 |
|---|---|---|---|
| ACSII | 是 | 单字节 7 位编码 | 最早的奠基性字符集 |
| GBK | 是 | 双字节编码 | |
| UTF-32 | 是 | 4 字节编码 | 目前很少采用 |
| UTF-16 | 否 | 2 字节或 4 字节编码 | Java和Windows XP/NT等内部使用UTF-16 |
| UTF-8 | 否 | 1 至 4 字节编码 | 互联网和UNIX/Linux 广泛支持的Unicode字符集；MySQLServer也使用UTF-8 |
选择字符集时，要考虑的因素：
MySQL服务器可以支持多种字符集，在同一台服务器、同一个数据库、甚至同一个表的不同字段都可以指定使用不同的字符集。
常用字符串函数：
| 函数 | 功能 |
|---|---|
| CANCAT(S1,S2,…Sn) | 连接 S1,S2,…Sn 为一个字符串 |
| LOWER(str) | 将字符串str中所有字符变为小写 |
| UPPER(str) | 将字符串str中所有字符变为大写 |
| LTRIM(str) | 去掉字符串str左侧的空格 |
| RTRIM(str) | 去掉字符串str行尾的空格 |
| REPLACE(str,a,b) | 用字符串b替换字符串str中所有出现的字符串a |
| TRIM(str) | 去掉字符串行尾和行头的空格 |
常用数值函数：
| 函数 | 功能 |
|---|---|
| ABS(x) | 返回 x 的绝对值 |
| CEIL(x) | 返回大于 x 的最小整数值 |
| FLOOR(x) | 返回小于 x 的最大整数值 |
| MOD(x，y) | 返回 x/y 的模 |
| RAND() | 返回 0 到 1 内的随机值 |
| ROUND(x,y) | 返回参数 x 的四舍五入的有 y 位小数的值 |
FLOOR、CEIL使用示例：
日期时间函数：
| 函数 | 功能 |
|---|---|
| CURDATE() | 当前日期 |
| CURTIME() | 当前时间 |
| NOW() | 当前的日期和时间 |
| UNIX_TIMESTAMP(date) | 日期 date 的 UNIX 时间戳 |
| FROM_UNIXTIME | UNIX 时间戳的日期值 |
| WEEK(date) | 日期 date 为一年中的第几周 |
| YEAR(date) | 日期 date 的年份 |
| HOUR(time) | time 的小时值 |
| MINUTE(time) | time 的分钟值 |
| DATE_FORMAT(date,fmt) | 按字符串 fmt 格式化日期 date 值 |
| DATE_ADD(date,INTERVAL expr type) | 一个日期或时间值加上一个时间间隔的时间值 |
| DATEDIFF(expr,expr2) | 起始时间 expr 和结束时间 expr2 之间的天数 |
上面函数使用示例：
距离2030-08-08的天数：
主要指case…when…函数：
| 函数 | 功能 |
|---|---|
| CASE WHEN [value1] THEN [result1]… ELSE [default] END |
如果value1是真，返回result1，否则返回default |
| CASE [expr] WHEN [value1] THEN [result1]… ELSE [default] END |
如果expr等于value1，返回result1，否则返回default |
窗口可以理解为数据集合，窗口函数是在满足某种条件的数据集合上执行的特殊函数。有的函数，随着数据不同，窗口大小都是固定的，这种属于静态窗口；有的函数则相反，不同的记录对应着不同的窗口，这种称为滑动窗口。
看个常用的row_number使用的例子。如果要查询每个用户最新的一笔订单数据，此时就可以使用ROW_NUMBER()函数按照用户进行分组并按照订单日期进行由大到小排序，最后查找每组序号中为1的数据，示例：
select * from
(
select row_number() over(partition by user_no orderby create_date desc) as row_num,order_id,user_no,amount,create_date
from order_tab
)t where row_num = 1;
临时表：MySQL用于存储一些中间结果集的表，临时表只在当前连接可见，当关闭连接时，Mysql会自动删除表并释放所有空间。
为什么会产生临时表：一般是由于复杂的SQL导致临时表被大量创建。
下列操作会使用到临时表：
- union查询
- 对于视图的操作，比如使用union
- 子查询
- join，包括not in、exist等
- 复杂的group by和order by
- Insert select 同一个表，Mysql会产生一个临时表缓存select的行
- 多个表更新
- COUNT(DISTINCT) 语句
select * from table_name order by rand() limit 1;
计算机中存储的最小单元是一个字节即8bit，所能表示的字符范围是255个，而人类要表示的符号太多，无法用一个字节来完全表示，固需要将符号编码，将各种语言翻译成计算机能懂的语言。
ASCII码：总共128个，用一个字节的低7位表示，0〜31控制字符如换回车删除等；32~126是打印字符，可通过键盘输入并显示出来。
ISO-8859-1：用来扩展ASCII编码，256个字符，涵盖了大多数西欧语言字符。
GB2312：双字节编码，总编码范围是A1-A7，A1-A9是符号区，包含682个字符，B0-B7是汉字区，包含6763个汉字。
GBK：为了扩展GB2312，加入了更多的汉字，编码范围是8140~FEFE，有23940个码位，能表示21003个汉字。
UTF-16：ISO试图想创建一个全新的超语言字典，世界上所有语言都可通过这本字典Unicode来相互翻译，而UTF-16定义了Unicode字符在计算机中存取方法，用两个字节来表示Unicode转化格式。不论什么字符都可用两字节表示，即16bit，固叫UTF-16。
UTF-8：UTF-16统一采用两字节表示一个字符，但有些字符只用一个字节就可表示，浪费存储空间，而UTF-8采用一种变长技术，每个编码区域有不同的字码长度。 不同类型的字符可以由1~6个字节组成。
utf-8是一种变长编码技术，utf-8编码中的中文占用的字节不确定，可能2个、3个、4个。
事务是数据处理的最小操作单元，是一组不可再分割的操作集合，这个操作单元里的一系列操作要么都成功，要么都失败。
事务（Transaction）是一个操作序列。这些操作要么都做，要么都不做，是一个不可分割的工作单位。
事务最主要的目的是为了数据一致性。
可以通过set session autoCommit = on/off 来设置mysql事务是否自动开启。如果我们设置autoCommit为off的时候，需要手动开启mysql事务。
事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态。拿转账来说，假设用户A和用户B两者的钱加起来一共是5000，那么不管A和B之间如何转账，转几次账，事务结束后两个用户的钱相加起来应该还得是5000，这就是事务的一致性。
各并发事务之间数据库是相互隔离的；一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。已知有两个事务A和B, A读取了已经被B更新但还没有被提交的数据。之后，B回滚事务，A读取的数据就是脏数据。此处的"脏"主要指由于回滚，导致了数据的无效性。
已知有两个事务A和B，A多次读取同一数据，B在A多次读取的过程中对数据作了修改并提交，导致A多次读取同一数据时，结果不一致。此处的"重复"指的是由于修改，某个事务重复读取的某个值发生了变化。
有两个事务A和B，A从一个表中读取了数据，然后B在该表中插入了一些新数据，导致A再次读取同一个表, 就会多出几行，简单地说，一个事务中先后读取一个范围的记录，但每次读取的纪录数不同。此处的"幻"指的是查到的数据个数（范围）发生了变化。
为了达到事务的四大特性，数据库定义了4种不同的事务隔离级别，由低到高依次为Read uncommitted、Read committed、Repeatable read、Serializable，这四个级别可以逐个解决脏读、不可重复读、幻读这几类问题。
SQL 标准定义了四个隔离级别：
读未提交)允许一个事务可以读取另外一个事务未提交的事务。可能会导致脏读、幻读或不可重复读
读已提交)允许一个事务读取另一个并发事务已经提交的数据。可以阻止脏读，但是幻读或不可重复读仍有可能发生。
可重复读)可以阻止脏读和不可重复读，但幻读仍有可能发生。
可串行化)该级别可以防止脏读、不可重复读以及幻读。
用表格表示四种隔离级别和能解决问题情况：
| 事务隔离级别 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|
| 读未提交 | 不能解决 | 不能解决 | 不能解决 |
| 不可重复读 | 能解决 | 不能解决 | 不能解决 |
| 可重复读 | 能解决 | 能解决 | 不能解决 |
| 串行化 | 能解决 | 能解决 | 能解决 |
Mysql默认采用的REPEATABLE_READ（可重复读）隔离级别。
事务隔离机制的实现基于锁机制和并发调度。
因为隔离级别越低，事务请求的锁越少，所以大部分数据库系统的隔离级别都是READ-COMMITTED(读已提交)，但是InnoDB存储引擎默认使用REPEATABLE-READ（可重读），并不会有任何性能损失。
InnoDB存储引擎在分布式事务的情况下一般会用到SERIALIZABLE(可串行化)隔离级别。
事务日志是通过redo和innodb的存储引擎日志缓冲（Innodb log buffer）来实现的，当开始一个事务的时候，会记录该事务的lsn(log sequence number)号。当事务执行时，会往InnoDB存储引擎的日志的日志缓存里面插入事务日志；当事务提交时，必须将存储引擎的日志缓冲写入磁盘（通过innodb_flush_log_at_trx_commit来控制），也就是写数据前，需要先写日志。这种方式称为“预写日志方式”。
正则表达式用来描述或者匹配符合规则的字符串。正则表达式需要使用REGEXP命令，匹配上返回"1"匹配不上返回"0"，默认不加条件REGEXP相当于like ‘%%’。在前面加上NOT相当于NOT LIKE。
| 符号 | 说明 |
|---|---|
| ^ | 在字符的首部进行匹配 |
| $ | 在字符的末尾处进行匹配 |
| . | 匹配任何字符（包括回车和新行） |
| [….] | 匹配括号内的任意单个字符 |
| [m-n] | 匹配m到n之间的任意单个字符，例如[0-9],[a-z],[A-Z] |
| [^…] | 不能匹配括号内的任意单个字符 |
| a* | 匹配0个或多个a,包括空,可以作为占位符使用 |
| a+ | 匹配一个或多个a,不包括空 |
| a? | 匹配一个或0个a |
| `a1 | a2` |
| a{m} | 匹配m个a |
| a{m,} | 匹配m个或者更多个a |
| a{m,n} | 匹配m到n个a |
| a{,n} | 匹配0到n个a |
| (….) | 将模式元素组成单一元素，例如（do）*意思是匹配0个多或多个do |
^：在字符串开始处进行匹配 # 1
SELECT 'abc' REGEXP '^a';
$：在字符串末尾开始匹配 # 0
SELECT 'abc' REGEXP 'a$';
# 1
SELECT 'abc' REGEXP 'c$';
.：匹配任意字符 # 0
SELECT 'abc' REGEXP '.a';
# 1
SELECT 'abc' REGEXP '.b';
# 1
SELECT 'abc' REGEXP '.c';
# 1
SELECT 'abc' REGEXP 'a.';
[...]：匹配括号内的任意单个字符 # 0
SELECT 'abc' REGEXP '[xyz]';
# 1
SELECT 'abc' REGEXP '[xaz]';
[^...]：^只有在[ ]内才是取反的意思，在别的地方都是表示开始处匹配 # 0
SELECT 'a' REGEXP '[^abc]';
# 1
SELECT 'x' REGEXP '[^abc]';
# 1
SELECT 'abc' REGEXP '[^a]';
a*：匹配0个或多个a,包括空字符串。 可以作为占位符使用 # 1
SELECT 'stab' REGEXP '.ta*b';
# 1
SELECT 'stb' REGEXP '.ta*b';
# 1
SELECT '' REGEXP 'a*';
a+：匹配1个或者多个a,但是不包括空字符 # 1
SELECT 'stab' REGEXP '.ta+b';
# 0
SELECT 'stb' REGEXP '.ta+b';
a?：匹配0个或者1个a # 1
SELECT 'stb' REGEXP '.ta?b';
# 1
SELECT 'stab' REGEXP '.ta?b';
# 0
SELECT 'staab' REGEXP '.ta?b';
a1|a2：匹配a1或者a2 # 1
SELECT 'a' REGEXP 'a|b';
# 1
SELECT 'b' REGEXP 'a|b';
# 1
SELECT 'b' REGEXP '^(a|b)';
# 1
SELECT 'a' REGEXP '^(a|b)';
# 0
SELECT 'c' REGEXP '^(a|b)';
a{m}：匹配m个a # 1
SELECT 'auuuuc' REGEXP 'au{4}c';
# 0
SELECT 'auuuuc' REGEXP 'au{3}c';
a{m,}：匹配m个或者更多个a # 1
SELECT 'auuuuc' REGEXP 'au{3,}c';
# 1
SELECT 'auuuuc' REGEXP 'au{4,}c';
# 0
SELECT 'auuuuc' REGEXP 'au{5,}c';
a{m,n}：匹配m到n个a,包含m和n # 1
SELECT 'auuuuc' REGEXP 'au{3,5}c';
# 1
SELECT 'auuuuc' REGEXP 'au{4,5}c';
# 0
SELECT 'auuuuc' REGEXP 'au{5,10}c';
(abc)：将abc作为一个序列匹配，不用括号括起来都是用单个字符去匹配，如果要把多个字符作为一个整体去匹配就需要用到括号 # 1
SELECT 'xababy' REGEXP 'x(abab)y';
# 1
SELECT 'xababy' REGEXP 'x(ab)*y';
# 1
SELECT 'xababy' REGEXP 'x(ab)*y';