【总结】数据库基础

一.sql分类及基本语法

1.sql分类

（1）DDL：数据库定义语言，用于定义表，列，索引等数据库对象. create,drop,alter等
（2）DML：数据库操纵语言，增删改查数据库的记录insert，delete，update，select等
（3）DCL：数据库控制语言，定义了数据库的表，字段，用户的访问权限和安全级别 grant，revoke等

2.DDL语句

（1）创建数据库:create database test1;
（2）删除数据库：drop database test1；
（3）创建表：create table user(name varchar(10),age int(2));
（4）删除表：drop table user;
（5）修改表：
alter table user modify name varchar(20);
alter table user add column sex varchar(10);
alter table user drop column age;
Alter table user change age age1 int(4);
Alter table user rename user1;

3.DML语句

（1）插入记录insert into user(name,age)values(‘zhangsan’,18);
（2）删除记录：delete from user where name=’张三’
（3）更新记录：update user set age=1 where name=’ 张三’
（4）查询记录：select * from user;
（5）查询不重复的记录j：select distinct name from user;
（6）排序查询：select from user order by age desc;
（7）限制排序：select * from user order by age limit 1,30;
（8）聚合：select name,age from user group by age;
（9）聚合后条件：select name,age from user group by age having age > 18;
（having和where的区别：having是聚合后条件，where是聚合前条件）

3.DML语句

（1）插入记录insert into user(name,age)values(‘zhangsan’,18);
（2）删除记录：delete from user where name=’张三’
（3）更新记录：update user set age=1 where name=’ 张三’
（4）查询记录：select * from user;
（5）查询不重复的记录j：select distinct name from user;
（6）排序查询：select from user order by age desc;
（7）限制排序：select * from user order by age limit 0,30;
 # LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。初始记录行的偏移量是 0(而不是 1)
 # LIMIT n 等价于 LIMIT 0,n
 # limit 0 会发生什么呢？这条语句数据库会进行优化而跳过而不是执行全表扫描
（8）聚合：select name,age from user group by age;
（9）聚合后条件：select name,age from user group by age having age > 18;
（having和where的区别：having是聚合后条件，where是聚合前条件）

1.group by详解

（1）单字段分组（去重）

 select  grade  from  student   group   by   grade       查出学生等级的种类（按照等级划分，去除重复的）

（2）多字段分组（一般配合聚合函数使用）
常用的聚合函数：count() , sum() , avg() , max() , min()

select  name , sum(salary)    from   student    group  by   name , grade      按照名字和等级划分，查看相同名字下的工资总和

2.内连接 外连接

（1）内连接：返回两个表的交集部分

select * from a_table a inner join b_table bon a.a_id = b.b_id;

（2）左外连接：左表的全部数据和右表符合条件的数据

select * from a_table a left join b_table bon a.a_id = b.b_id;

（3）右外连接：右表中全部数据和左表中符合条件的数据

select * from a_table a right outer join b_table b on a.a_id = b.b_id;

（4）全外连接：相当于左外连接+右外连接。mysql不支持全外连接，可以使用union连接左外连接和右外连接

3.union union all

select * from t1 union select * from t2
select * from t1 union all select * from t2

union和union all的区别：union是将union all查询出来的结果进行了一次distinct，去除重复后的结果

3.DCL语句

（1）授权：创建一个数据库用户 z1，具有对 sakila 数据库中所有表的 SELECT/INSERT 权限：
grant select,insert on sakila.* to 'z1'@'localhost' identified by '123';

（2）收回权限：由于权限变更，需要将 z1 的权限变更，收回 INSERT，只能对数据进行 SELECT 操作：
revoke insert on sakila.* from 'z1'@'localhost';

二.索引

1.什么是索引

1.系统根据某种算法，将已有的数据（和未来新增的数据）单独建立一个文件，文件能够实现快速的匹配数据，并能够快速的找到对应表中的记录
2.每种存储引擎（innodb，myidsam等）对每个表至少支持16个索引，myisam和innodb默认创建的都是BTREE索引，memory存储引擎默认使用hash索引
没有索引的情况下访问数据：

使用B+索引访问数据：

2.索引分类

1.结构上

聚簇索引：聚簇索引是指索引的结构和排列规则是和实际数据的存储结构和排列规则是一样的。每个表至多只能创建一个聚簇索引（主键）
非聚簇索引：非聚簇索引则可能是通过其他算法规则构成的一种索引结构，索引的结构和实际数据存储的结构是不同的

2.功能上

（1）主键索引：设定为主键后数据库会自动建立索引
（2）唯一索引：索引列的值必须唯一，但允许有空值

CREATE UNIQUE INDEX idx_customer_no ON customer(customer_no);

（3）单值索引：即一个索引只包含单个列，一个表可以有多个单列索引

CREATE INDEX idx_customer_name ON customer(customer_name); 

（4）复合索引：即一个索引包含多个列。当表的行数远大于索引列的数目时可以使用复合索引

CREATE INDEX idx_no_name ON customer(customer_no,customer_name); 

3.哪些情况需要创建索引

（1）主键自动建立唯一索引
（2）频繁作为查询条件的字段应创建索引（where后面的语句）
（3）经常要用于排序(order by)，分组(group by)的列，因为索引已经排好序了

4.哪些情况不需要创建索引

（1）表记录太少
（2）经常增删改的表（重建索引）

5.索引失效的情况

（1）or连接的条件不是每一个列都有索引，这时有索引的列也会失效
（2）复合索引遵守“最左前缀”原则，即在查询条件中使用了复合索引的第一个字段，索引才会被使用
（3）like查询是以%开头（以%结尾，索引可以使用）
（4）存在索引列的数据类型隐形转换，则用不上索引（比如列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引）
（5）where 子句里对索引列上有数学运算，用不上索引
（6）where 子句里对有索引列使用函数，用不上索引
（7）如果mysql估计使用全表扫描要比使用索引快,则不使用索引（比如数据量极少的表）

6.索引实现原理

MySQL支持诸多存储引擎，而各种存储引擎对索引的支持也各不相同，因此MySQL数据库支持多种索引类型，如BTree索引，B+Tree索引，哈希索引，全文索引等等
1.哈希索引：只有memory（内存）存储引擎支持哈希索引
2.BTree索引
BTree是平衡搜索多叉树，设树的度为2d（d>1），高度为h，那么BTree要满足以以下条件：

（1）每个叶子结点的高度一样，等于h；
（2）每个非叶子结点由n-1个key和n个指针point组成，其中d<=n<=2d,key和point相互间隔，结点两端一定是key；
（3）叶子结点指针都为null；
（4）非叶子结点的key都是[key,data]二元组，其中key表示作为索引的键，data为键值所在行的数据；

在BTree的机构下，就可以使用二分查找的查找方式，查找复杂度为h*log(n)，一般来说树的高度是很小的，一般为3左右，因此BTree是一个非常高效的查找结构
3.B+Tree索引
B+Tree是BTree的一个变种，设d为树的度数，h为树的高度，B+Tree和BTree的不同主要在于：
（1）B+Tree中的非叶子结点不存储数据，只存储键值；
（2）B+Tree的叶子结点没有指针，所有键值都会出现在叶子结点上，且key存储的键值对应data数据的物理地址；
（3）B+Tree的每个非叶子节点由n个键值key和n个指针point组成；

7.B+Tree索引相对BTree索引的优势

（1）速度更快。B+Tree的非叶节点中不存储data，就可以存储更多的key。一次性读入内存中的关键字也就越多。相对来说IO读写次数也就降低了
（2）查询效率更稳定。任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当

三.存储引擎

1.myisam和innodb

（1）innodb是事务优先，它加的是行锁。适合高并发操作 。myisam是性能优先，加的是表锁
（2）innodb支持事务，外键。myisam不支持
（3）5.5之前默认myisam，5.5之后默认innodb

四.三范式

1.第一范式（1NF）

原子性，字段不可分，否则就不是关系数据库

2.第二范式（2NF）

唯一性，要求数据库表中的每个行必须可以被惟一地区分。为实现区分通常需要我们设计一个主键来实现

3.第三范式（3NF）

不冗余，一个数据库表中不包含已在其它表中已包含的非主键字段

4.反三范式

反三范式
没有冗余的数据库未必是最好的数据库，有时为了提高运行效率，提高读性能，就必须降低范式标准，适当保留冗余数据。具体做法是： 在概念数据模型设计时遵守第三范式，降低范式标准的工作放到物理数据模型设计时考虑。降低范式就是增加字段，减少了查询时的关联，提高查询效率，因为在数据库的操作中查询的比例要远远大于DML的比例。但是反范式化一定要适度，并且在原本已满足三范式的基础上再做调整的