数据库知识归纳整理

昨天面试通话中一个问题把我问住了，自己的数据库知识丢了不少，这几天抓紧拾起来
------------------------------------------分割线-------------------------------------------------
1、select * from temp group by ab;问我这个sql语句对不对
当时知道它是不对的，面试官这么问他就不会对，但是我想不出是哪里不对，还是知识点细节
引入Group by的语句查询操作中，在select语句汇总出现的列，必须在group by的子句中

2、内外链接
显示内连接：关键词inner join on；inner join用于连接两个表，on是限定条件
隐式内连接：这个是我常用的，就是不加inner join on 直接用表名引导
左外链接：LEFT JOIN 指定左表为参照，若右表无对应的行，则为空
右外连接原理同左外
完整外连接：关键词FULL JOIN，就是左右的整合

3、有关子查询：子查询的限定条件包括单行运算符（>=<>=<><=）还包括多行运算符（IN、ANY、ALL）

4、整形
tinyint、smallint、mediumint、int、bigint
属性：unsigned（非负）
长度：限制指定宽度，例如：int（4）加入zerofill属性后，输入“123456”，数据库中存入为“123456”；输入“123”数据库中存入为“000123”

5、字符型
varchar、char、text、blob
varchar用于存储可变长字符串，它比定长类型更节省空间
varchar会有一或两个额外字节记录字符串的长度，列长度小于255字节，用一个字节表示，否则用两个
varchar超出指定长度会被截断
char会根据需要采用空格进行填充，适合存储很短的字符串，或者所有都是接近同一个长度，密码常用char，定长定长定长

对于经常变更的数据，char优于varchar，char不容易产生碎片
对于非常短的列，char比varchar在存储空间上更有效率
尽量避免使用text和blob，查询会使用临时表，导致严重的性能开销

6、日期和时间类型
尽量使用timestamp，比datetime空间效率更高，用整数保存时间戳的格式不方便操作，如需存储微秒，可以用bigint存储（存入的时候*一个数，取出的时候再/回来）

7、列属性
auto_increment、default、not null、zerofill

8、索引（类似于书的目录）
索引的使用场景：
对于非常小的表，全表扫描往往效率更高
中到大型表，索引非常有效
特大型的表，建立和使用索引的代价将随之增长，可以使用分区技术来解决

索引的影响：
大大提高查询速度，降低写的速度，占用磁盘

索引分类：
普通索引，没有约束条件
唯一索引：与普通索引类似，但具有唯一性约束
主键索引：特殊的唯一索引，不允许为空
一个表只能有一个主键索引，可以有多个唯一索引
主键索引一定是唯一索引，唯一索引不一定是主键索引
主键可以与外键构成参照完整性约束，防止数据不一致
组合索引：将多个列组合在一起创建索引，可以覆盖多个列

创建原则：
最适合索引的列是出现在where子句中的列，或链接子句总的列而不是出现在select关键字后的列
索引的基数越大，效果越好
对字符串进行索引，应制定一个前缀长度，可以省大量的索引空间
根据情况创建复合索引，提高查询效率
主键尽可能选择短的数据类型

复合索引注意事项：
复合索引遵循前缀原则

KEY(a,b,c)
where a=1 and b=2 and c=3
where a=1 and b=2
where a=1
//可以
where b=2 and c=3
where a=1 and c=3
//不可以