- Enum数据类型，enum('a','b','abc')
Enum类型允许输入指定集合中的一个值，输入内容不区分大小写，最多允许65535个成员
- Set类型， set('a','b','c')
Set类型允许输入指定集合中的一个或多个值，多个值之间用逗号分隔，最多允许64个成员。
set类型会自动去掉重复的数据，比如插入 ‘a,b,a,b,a’,库中只会存储 'a,b'
- 安全的等于运算符 <=>
使用安全等于时 NULL<=>NULL 的值为1，效果等同于is null，而NULL=NULL的值为null
- 正则表达式运算符 regexp
-- 查询名字为aeiou中任意一个字母开头，ok结尾的数据
SELECT * FROM t WHERE name REGEXP '^[aeiou]|ok$';
-- 查询名字中包含mar的数据
SELECT name FROM t WHERE name REGEXP 'mar';
-- 查询名字以ok结尾的数据
SELECT name FROM person_tbl WHERE name REGEXP 'ok$';
-- 查询名字以'st'为开头的数据
SELECT name FROM person_tbl WHERE name REGEXP '^st';
- if函数, if(value, v1, v2) 如果value为真，则返回v1，否则返回v2
ifnull函数， ifnull(v1,v2) 如果v1不为null就返回v1，否则返回v2
-- 如果年龄大于20就返回man,否则返回boy
select if(age>20,'man','boy') from t
-- 如果年龄字段为null，就返回0
select ifnull(age, 0) from t
- 其他常用函数
| 函数 | 功能 | 使用举例 |
|---|---|---|
| DATABASE() | 返回当前数据库名 | select database(); |
| VERSION() | 返回当前数据库版本 | select version(); |
| USER() | 返回当前登录用户名 | select user(); |
| INET_ATON(ip) | 返回ip地址的数字表示 | select inet_aton('192.168.1.1'); 可以用来比较两个IP地址，比如想要知道两个IP段之间有多少个地址 |
| INET_NTOA(num) | 返回数字num代表的IP地址 | select inet_ntoa(3232235777); |
| PASSWORD(str) | 返回字符串str的加密版本 | select password('aaa'); |
| MD5(str) | 返回字符串str的md5值 | select md5('aaa') |
- 各种数据库存储引擎的特性
| 特点 | MyISAM | InnoDB | MEMORY | MERGE | NDB |
|---|---|---|---|---|---|
| 存储限制 | 有 | 64TB | 有 | 没有 | 有 |
| 事物安全 | 支持 | ||||
| 锁机制 | 表锁 | 行锁 | 表锁 | 表锁 | 行锁 |
| B树索引 | 支持 | 支持 | 支持 | 支持 | 支持 |
| 哈希索引 | 支持 | 支持 | |||
| 全文索引 | 支持 | ||||
| 集群索引 | 支持 | ||||
| 数据缓存 | 支持 | 支持 | 支持 | ||
| 索引缓存 | 支持 | 支持 | 支持 | 支持 | 支持 |
| 数据可压缩 | 支持 | ||||
| 空间使用 | 低 | 高 | N/A | 低 | 低 |
| 内存使用 | 低 | 高 | 中等 | 低 | 低 |
| 批量插入速度 | 高 | 低 | 高 | 高 | 高 |
| 支持外键 | 支持 |
- 浮点数和定点数
| 名称 | 数据类型 | 特点 | 使用场景 |
|---|---|---|---|
| 浮点数 | float/double | 存在误差，不能直接比较两个浮点数，可对数据自动进行四舍五入 | 对数据精度有一定的容忍度的数据 |
| 定点数 | decimal | 没有误差，对于超出精度的数据可以根据SQLMode进行警告或禁止插入 | 对货币等精度敏感的数据，应使用定点数 |
- MySql的字符集
对于客户端和服务端交互，MySql提供了三个参数 character_set_client(客户端)、character_set_connection(连接)、character_set_results(返回结果)标示字符集，只有当这三个字符集完全相同的时候，才能确保用户写入的数据正确度出，一般在 my.cnf 中用[mysql] default-character-set=UTF-8来设置默认字符集
- 查看执行计划
-- 使用explain命令查看执行计划，分析索引使用情况，找出优化方案
explain select * from t where c1='a' and c2 like '%b%' \G;
- 视图的可更新性
在视图中可以进行update和delete操作,操作的结果会反应在视图的源表中.例如:update了视图的一个字段,对应的源表中的字段也会自动修改,delete操作同理。但是，包含以下条件的视图是不能进行更新操作的：
1）视图的SQL中包含聚合函数（sum，count等）、DISTINCT、GROUP BY、HAVING、UNION或UNION ALL
2）常量视图
3）select中包含子查询
4）join
5）from一个不能更新的视图
6）where子句的子查询引用了from子句中的表
- 锁表和解锁
--加读锁，当前session和其他session还可以继续读取表数据，但是都不能更新表数据
lock tables tableName read;
--加写锁，其他session不能读写表数据，只有当前session可读写表数据
lock tables tableName write;
--解锁，会释放当前线程获得的任何锁定，当与服务器的链接断开时，当前线程的锁定也会被隐含解锁
unlock tables
- 事务
-- 开始一个事务，开启事务时，unlock tables会被隐含执行
start transaction 或 begin
-- 提交事务 / 提交事务后立即启动一个新的同一隔离级别的事务 / 提交事务后断开客户端连接
commit / commit and chain / commit and release
-- 回滚整个事务
rollback
-- 在事务执行过程过（start transation 后 commit或rollback前）定义一个名字为pointName的point
savepoint pointName
-- 回滚到名字为pointName的point，在point之后的sql会被回滚
rollback to savepoint pointName
-- 设置事务自动提交/不自动提交
set autocommit=1或0
- 分布式事务（直到MySQL-5.7.7才完美支持分布式事务）
目前Mysql分布式事务的缺点：1）性能太低，与一个数据库的事务相比差了10倍 2）对版本有要求，只有5.7.7以上的版本才有完美的分布式事务解决方案
- 对于特别大的表，可以使用分区功能（分区对应用来说完全透明，不影响应用业务逻辑）
分区的优点：1）可以存储更多数据 2）优化查询，where子句包含分区字段时，可以只扫描一个或多个分区 3）可以快速删除过期的数据 4）跨多个磁盘来分散查询，能获得更大的吞吐量
| 分区类型 | 说明 | 接收数据类型 | 多字段 |
|---|---|---|---|
| RANGE分区 | 基于一个给定的连续区间范围，把数据分配到不同的分区 | 除blob和txt以外的类型 | 支持 |
| LIST分区 | 类似RANGE分区，区别在于LIST分区是基于枚举值进行分区，RANGE分区是基于连续的值 | INT | 不支持 |
| HASH分区 | 基于给定的分区个数，把数据分配到不同的分区 ，支持自定义表达式 | INT | 不支持 |
| KEY分区 | 类似HASH分区，不支持自定义表达式 | 除blob和txt以为的类型 | 支持 |
HASH和KEY分区一般搭配linear关键字强制使用线性分区方式，可以防止重新调整分区的时候导致大量计算的问题。
--查看当前数据库是否支持分区
show plugins
--如果结果里有名称为partition，状态为ACTIVE的数据，表名当前数据库支持分区
-- 1.Hash分区方式（如果有主键，只能使用主键作为分区字段）,Hash分区只支持int。
create table emp (id int, salary decimal(7,2), birth_date DATE)
engine=innodb
partition by hash(MONTH(birth_date))
partitions 6
--2. Key分区方式，支持使用字符串进行分区。
create table emp (id int, a varchar(10))
engine=innodb
partition by key(a)
partitions 4
--2.5 Hash和Key分区默认使用取膜的方式进行分区，导致的问题是新增或删除分区的时候，之前的分区都要进行重新计算，所以MySQL提供了一种线性Hash和线性key分区，使用2的幂的运算法则来解决这个问题。
create table emp (id int, a varchar(10))
engine=innodb
partition by linear key(a)
partitions 4
--3.Range分区方式，按数字大小分段
create table emp1 (id int, salary decimal(7,2), birth_date DATE, primary key(id))
engine=innodb
partition by range(id)(
partition p0 values less than (10)
INDEX DIRECTORY = '/data/emp'
DATA DIRECTORY = '/data/emp',
partition p1 values less than (20)
INDEX DIRECTORY = '/data1/emp'
DATA DIRECTORY = '/data1/emp',
partition p2 values less than (30)
INDEX DIRECTORY = '/data2/emp'
DATA DIRECTORY = '/data2/emp',
partition p3 values less than maxvalue
INDEX DIRECTORY = '/data3/emp'
DATA DIRECTORY = '/data3/emp'
)
--4. list分区，分区字段只能在有限集合内
create table emp3 (id int, salary decimal(7,2), birth_date DATE, category int)
engine=innodb
partition by list(id)(
partition p0 values in (1,3),
partition p1 values in (2,4),
partition p2 values in (5,7,9),
partition p3 values in (10,11)
)
--5. 新增/删除/拆分/合并Range和List分区
alter table emp3 drop partition p2;
alter table emp3 add partition (partition p4 values less than (2030));
alter table emp3 reorganize partition p3 into (
partition p2 values less than (1000),
partition p4 values less than (2000)
);
alter table emp3 reorganize partition p2,p3,p4 into( partition p5 values less than (5000));
--6. 新增/删除Hash和Key分区
alter table emp add partition 2; (减少到2个分区)
alter table emp coalesce partition 8; (新增8个分区)
