[TOC]
## 字段类型
### 整数类型
`TINYINT、 SMALLINT、 MEDIUMINT、INT、 BIGINT`
属性: `UNSIGNED`
长度:可以为整数类型指定宽度,例如:INT(11)、对大多数应用
是没有意义的,它不会限制值的合法范围,只会影响显示字符的个
数
> int(3) 可以存入值为1234567 ,且不会切断,
> 设置长度是为了 如果设置了 `Zerofill` 存入123 会变为 0123,
> 所以 int(0) 也是具有意义的
### 实数类型
`FLOAT、 DOUBLE、 DECIMAL`
DECIMAL可存储比 BIGINT还大的整数;可以用于存储精确的小数,相当于存为字符串
FLOAT和 DOUBLE类型支持使用标准的浮点进行近似计算
### 字符串类型
`VARCHAR、CHAR、TEXT、BLOB`
VARCHAR类型用于存储可变长字符串,它比定长类型更节省空间
VARCHAR使用1或2个额外字节记录字符串的长度,列长度小于
255字节,使用1个字节表示,否则用2个
> `char` 定长 ,如 `char(10)` 不管你存入多长, 都占用10 个字节,用于存储定长的字符串
> `varchar` 与 `char` 会切断长度长度的字符串 ,超过255字节的只能用`varchar`或者`text`；
> 如果存储值很短或定长,可以选择` char`
> 能用`varchar`的地方不用`text`；
### 枚举
`create table user_sex( sex enum('M','F'));`
有时可以使用枚举代替常用的字符串类型
把不重复的集合存储成一个预定义的集合
非常紧凑,把列表值压缩到一个或两个字节
部存储的是整数
### 日期和时间类型
尽量使用 `TIMESTAMP`,比 `DATETIME`空间效率高
用整数保存时间戳的格式通常不方便处理
如果需要存储微秒,可以使用 bigint存储
```
date 1000-01-01 ~ 9999-12-31
datetime 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
timestamp 1970-01-01 00:00:01 ~ 2038-01-19 03:14:07
```
## 数据表引擎
### Innodb表引擎
默认事务型引擎,最重要最广泛的存儲引擎,性能非常优秀
数据存储在共享表空间,可以通过配置分开
对主键查询的性能高于其他类型的存储引擎
内部做了很多优化,从磁盘读取数据时自动在内存构建hash索引
插入数据时自动构建插入缓冲区
通过一些机制和工具支持真正的热备份
支持崩溃后的安全恢复
支持行级锁
支持外键
### MYISAM表引擎
51版本前, MYISAM是默认的存储引擎
拥有全文索引l、压缩、空间函数
表锁,不支持事务和行级锁,不支持崩溃后的安全恢复
表存储在两个文件,MYD和MYI
设计简单,某些场景下性能很好
### 其他表引擎
Archive、 Blackhole、csV、 Memory
## 锁机制
表锁是日常开发当中常见的问题,当多个查询同一时刻进行数据修改时,就会产生并发控制的问题
### 共享锁(读锁)和排他锁(写锁)
#### 读锁
共享的,不堵塞,多个用户可以同时读一个资源,互不干扰
#### 写锁
排他的,一个写锁会阻塞其他的写锁和读锁,这样可以只允许一人进行写入,防止其他用户读取正在写入的资源。
### 锁粒度
- 表锁,系统性能开销最小,会锁定整张表, MYISAM使用表锁
- 行锁,最大程度地支持并发处理,但是也带来了最大的锁开销,Innodb实现行级锁
## 事务处理
MYSQL提供事务处理的表引|擎, Inno DB
服务器层不管理事务,由下层的引擎实现,所以同一个事务中,使
用多种存储引擎不靠谱
在非事务的表上执行事务操作 MYSQL不会发出提醒,也不会报错
## 存储过程
为以后的使用而保存的一条或多条 MYSQL语句的集合
存储过程就是有业务逻辑和流程的集合
可以在存储过程中创建表,更新数据,删除等等
## 触发器
提供给程序员和数据分析员来保证数据完整性的一种方法,它是与
表事件相关的特殊的存储过程
### 使用场景
可通过数据库中的相关表实现级联更改
实时监控某张表中的某个字段的更改而需要做出相应的处理
某些业务编号的生成等
滥用会造成数据库及应用程序的维护困难
> [MySQL 视图、函数、存储过程和触发器 简书 详解](https://www.jianshu.com/p/814d8aee700a)
## 索引的基础和类型
### 索引对性能的影响
大大减少服务器需要扫描的数据量
帮助服务器避免排序和临时表
将随机I/O变顺序I/O
大大提高查询速度,降低写的速度、占用磁盘
### 索引的使用场景
对于非常小的表,大部分情况下全表扫描效率更高
特大型的表,建立和使用索引的代价将随之增长,可以使用分区技术来解决
### 索引的类型
索引|有很多种类型,都是实现在存储引擎层的
- 普通索引:最基本的索引,没有任何约束限制
- 唯一索引:与普通索引类似,但是具有唯一性约束
- 主键索引:主键可以与外键构成参照完整性约束,防止数据不一致
- 组合索引:将多个列组合在一起创建索引,可以覆盖多个列
- 外键索引:只有 Innodb类型的表才可以使用外键索引,保证数据的一致性、完整性和实现级联操作
- 全文索引: MYSQL自带的全文索引只能用于 MYISAM,并且只能对英文进行全文检索
### 创建索引的原则
1. 最适合索引的列是出现在 WHERE子句中的列,或连接子句中的列而不是出现在 SELECT关键字后的列
2. 索引列的基数越大,索引的效果越好
3. 对字符串进行索引,应该制定一个前缀长度,可以节省大量的索引空间
4. 根据情况创建复合索引,复合索引可以提高查询效率
5. 避免创建过多索引,索引|会额外占用磁盘空间,降低写操作效率
6. 主键尽可能选择较短的数据类型,可以有效减少索引的磁盘占用提高查询效率
### 索引的注意事项
1. 复合索引遵循前缀原则
```
创建的索引有顺序,非顺序的索引不生效
KEY(a, b, c) //创建 a b c 索引
WHERE a=l and b=2 and c= 3 //生效
WHERE a=1 and b=2 //生效
WHERE a= 1 //生效
WHERE b=2 andc= 3 //无效
WHERE a= 1 and c=3 //无效
```
2. like查询,%在前,则素以失效,可以使用全文索引
3. column is null可以使用索引
`where name=null //也是可以使用索引`
4. 如果 MYSQL估计使用索引比全表扫描更慢,会放弃使用索引
`如果只有100 条数据 where id 1>and id<100 会自动转为全表索引`
5. 如果or前的条件中的列有索引,后面的没有,索引都不会被用到
`where name='cpj' or age='12'`
7. 列类型是字符串,查询时一定要给值加引|号,否则索引失效
```
name varchar(16)
存了 "100"
Where name =100 //虽然能得到值 但是没有使用索引
```
## 六种关联查询
### 交叉连接 CROSS JOIN
```
SELECT* FROM A,B(C)或者
SELECT* FROM A CROSS JOIN B(CROSS JOIN C
//没有任何关联条件,结果是笛卡尔积,结果集会很大,没有意义,很少使用
```
### 内连接 INNER JOIN
```
SELECT* FROM A, B WHERE A id=Bd或者
SELECT FROM A INNER JOIN B ON A.id=Bid
```
#### 内连接分为三类
- 等值连接: ON Aid=Bid
- 不等值连接: ON A id>B.d
- 自连接: `SELECT* FROM A T1 INNER JOIN A T2 on T1.id=T2. pid`
-
### 外连接 LEFT JOIN/ RIGHT JOIN
#### 左外连接
LEFT OUTER JOIN,以左表为主,先查询出左表,按照ON后的关联条件匹配右表,没有匹配到的用NULL填充,可以简写成 LEFT JOIN
#### 右外连接
RIGHT OUTER JOIN,以右表为主,先查询出右表,按照ON后的关联条件匹配左表,没有匹配到的用NULL填充,可以简写成 RIGHT JOIN
### 联合查询 UNION与 UNION ALL
`SELECT* FROM A UNION SELECT* FROM B UNION··`
就是把多个结果集集中在一起, UNION前的结果为基准,需要注
意的是联合查询的列数要相等,相同的记录行会合并
UNION ALL不会合并重复的结果集
### 嵌套语句
用一条SQL语句的结果作为另外一条SQL语句的条件
`SELECT FROM A WHERE id IN(SELECT id FROM B)`
## 真题
1. 更新 b 表 的 c1 c2 到 A 表的 c1 c2
```
A(id, sex, par, cl, c2)
B(id, age, cl, c2)
//方法一
update a, b set A.cl= B.cl, A.c2=B.c2
where A.id = B.id and B.age >50
//方法二
update a inner join B on Aid B id
set A.c1=B.c1,A.c2= B.c2
where Bage >50
```
