Mysql

1.什么是mysql

mysql其实是关系型数据库管理系统RDBMS，但人们习惯称他为数据库。

2. SQL的分类

DDL数据库定义语言Data Definition Language

        例如create drop alter

DQL数据库查询语言Data Query Language

        例如select

DML数据库操作语言Data Manipulation Language

        例如insert delete update

TCL事务操作语言Transactional Control Language

DCL数据控制语言Data Control Language

3.mysql常用命令

创建数据库：

create datebase 数据库名;

查询当前所用数据库：

select database();

查看现有的数据库：

show databases;

使用数据库:

use 数据库名;

查看当前数据库的表：

show tables;

查看表结构：

decs 表名;

查看建表语句：

show create table 表名;

4.select语句中可以进行算术运算

如select empno, ename, sal*12 from emp;

5.给字段起别名：

格式：字段名 as 'xxx'

as可以省略 

6.运算符

运算符	说明
=	等于
<>或!=	不等于
<	小于
<=	小于等于
>	大于
>=	大于等于
between … and ….	两个值之间,等同于 >= and <=
is null	为null（is not null 不为空）
and	并且
or	或者
in	包含，相当于多个or（not in不在这个范围中）
not	not可以取非，主要用在is 或in中
like	like称为模糊查询，支持%或下划线匹配 %匹配任意个字符 下划线，一个下划线只匹配一个字符

7.判断某个值为null

用is null。如select * from emp where comm is null;

！！！！切记不可以用等号判断是否为null！！！

8.当null参与算术运算结果一定是null

如(null+12)*10 结果为null。

9.ifnull(字段名,给定值)

当字段对应的值为null时，将null改为给定的值返回。

所以当记录的字段含null时，应该这样写(ifnull(字段名,0)+12)*10

 10.order by排序

默认asc升序。可指定排序方式，在order by后面加 asc或者desc降序。

多字段排序：靠前面的起决定性作用越大。靠后面的影响越小。

如select * from emp order by job desc, sal asc; 

先按工作进行降序排序，再将工作相同的记录按工资进行升序排。

11.group by分组 having分组后过滤

1.若没有指定分组，整张表自成一组

2.若按某个字段进行分组，select后接的字段只能是被分组的字段或者分组函数。

3.where后面不能有分组函数。因为先from再where再groupby[再having]再select。所以where是在groupby之前执行的，还没有分组何来分组函数。

4.having是对分组后的记录进行再次过滤，having可以使用分组函数。

5.having过滤的效率较低，尽量在where中完成过滤。必要时再用having。

12.分组函数

sum求和

avg求平均

count求总数

max求最大值

min求最小值

分组函数自动忽略null值。所以用分组函数不需要在where后面排除字段为null的记录。

13.连接查询

 内连接：两张表是平等的，没有主副之分。匹配不到就忽略

           等值连接

           非等值连接

           自连接：自己连接自己

 外连接：两张表有主副之分，必须保证主表的数据全部查出来，若匹配不到自动补null。

            左外连接：左边为主表

            右外连接：右边为主表

 全连接（几乎不用）：左右都为主表

14.子查询

select中嵌套（基本不用）

from中嵌套子查询：from后面接select语句作为临时表t。join后面也可。

如：select e.empno, e.ename from emp e join (select distinct mgr from emp where mgr is not null) m on e.empno=m.mgr;

where中嵌套子查询：where后面接查询语句作为判断的标准。

如：select empno, ename from emp where empno in(select mgr from emp where mgr is not null);

15.union

两张表相加，字段的数量必须相同，且字段相同才有意义。

16.分页limit pageNum,pageSize

 select * from emp  limit 0,5;

第pageNum页 limit (pageNum-1)*pageSize,pageSize  

17. DDL数据定义语言 建表

create table tableName(

   columnName dataType(length),

   ………………..

   columnName dataType(length)

);

表名建议是t_xxxx或者tbl_xxxx

dataType有：int,bigint,char定长字符串,varchar可变长字符串,float,double,Date日期,BLOB二进制流媒体大对象,CLOB文本大对象。

18.约束条件 

not null非空约束：字段不能为null。

unique唯一性约束：值不能重复。

primary key主键约束：不能重复且不能为null，主键最好与业务无关，只起区分每个记录的作用。

auto_increment：主键自增从1开始。

foreign key外键约束：来源于其他表。理解父表子表的概念。

not null是列级约束。

primary key是列级，表级约束（可以是联合主键）。

unique是列级，表级约束（可以是多个字段联合是唯一的）。

foreign key 表级约束foreign key(xxx) references t_aa(xxxx)  。

19.DML数据库操作语言 insert delete update

insert into 表名  (字段,字段......) values ('a','b'.....);

可不写字段名，要求values后面给全部字段赋值。

可连续插入多条values(),values().....

delete from 表名 where ...

update 表名 set 字段=a,字段=b.... where

20.存储引擎

mysql常用的存储引擎有MyISAM，InnoDB，Memory。

mysql自建表默认的存储引擎是InnoDB。

MyISAM是mysql内部最常用的存储引擎。不支持事务，用三个文件表示每一张表。表结构文件，数据文件，索引文件。

InnoDB存储引擎支持事务机制，有行级锁，比较安全。java开发通常用的都是这个引擎。在mysql崩溃后提供自动恢复。可级联删除，级联更新，父表（被引用代表）删除一条记录，级联删除子表中对应的记录。父表更新一条记录，记录更新子表中的记录。级联删除级联更新比较危险，谨慎使用。

MEMORY存储引擎不支持事务，数据存储在内存中，读取速度很快，但数据容易丢失。

21. 事务

事务的四个特征ACID Atomicity Consistency Isolation Durability

Atomicity原子性：事务中的DML语句是一个整体，要么全部执行，要么全部不执行。

Consistency一致性：在事务开始前和结束之后，数据库都保持一致的状态。

Isolation隔离性：事务之间互不影响。

Durability持久性：在事务完成之后，该事务对数据库所做的更改会持久地保存在数据库之中，并不会被回滚。

22.事务的隔离级别

读未提交read uncommitted：在A事务并未提交的情况下，B事务读取到了A事务对表修改的数据称为读未提交。也称为脏读dirty read。因为A事务可能会回滚，所以会导致B读到不正确的数据。

读已提交read committed：B事务在A事务提交之前读到的数据和B事务在A事务提交后读到的数据不一致。缺点:不可重复读。

可持续读repeatable read：B事务在A事务提交之前读到的数据和在A事务提交之后得到的数据保持一致。缺点：会出现幻读。读到了不存在的数据，只是之前的一个备份。java中默认的隔离级别

串行化serializable：解决了上述全部问题，但效率最低。事务对同一表的DML操作排队执行。

 23.索引是什么？有什么用？

索引相当于一本书的目录，根据索引能快速定位，查找到所需数据。索引被用来快速在一个列上找到特定值的行。如果不使用索引，将从表中第一条记录一条一条找，效率较低。索引底层采用b+tree数据结构。

24.什么时候要用到索引？

1.表中该字段数据量庞大

2.某字段在where后出现的频率高

3.经常被DML（更新，修改，删除）的字段不建议添加索引

25.索引的操作

创建索引 create index 索引名 on 表名 (字段名);

删除索引 drop index 索引名 on 表名;

26.索引的细粒度分类：（主键索引，单列索引，复合索引，唯一索引）InnoDB，（全文索引）MyISAM。

主键索引：在InnoDB中主键自动被添加索引。

单列索引：给非主键字段添加索引。

复合索引：给多个非主键字段添加索引。

唯一索引：给唯一的字段添加索引，值可以为NULL。

全文索引：类似于ES的分词机制。

27.索引的粗粒度分类：聚簇索引和非聚簇索引

聚簇索引：叶子结点存储索引和数据。

非聚簇索引：叶子节点不存放数据，叶子节点存放索引和指向数据对应的位置（一般为主键）。

28.MyISAM索引的原理：

给表中的某一字段加上索引，数据库就会为该字段的数据先排序，然后分页建立一个B+树。并且携带数据在内存或硬盘的物理地址。例如当select * from emp where ename='smith'。查询该语句时会先找ename字段上的索引，找到索引之后根据's'去s区找，再定位m...返回物理地址。此时直接根据物理地址找到所求记录。MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。

29.InnoDB中索引的原理：

1）主键会默认被加上索引，而且是聚簇索引。按主键字段进行排序，每个记录被封装成【主键 +一条记录+指针指向下一条记录】。每页的默认大小为16KB，当插入一条新的记录，且当前16KB的页不够装时，就会分页，新开一页继续放。上层页保存下层每页的首索引和指针。

在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

 (图inndb主键索引）是InnoDB主索引（同时也是数据文件）的示意图，可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

2）InnoDB的辅助索引，非主键字段加的索引。

 InnoDB的所有辅助索引都引用主键作为data域。

InnoDB 表是基于聚簇索引建立的。因此InnoDB 的索引能提供一种非常快速的主键查找性能。不过，它的辅助索引（Secondary Index， 也就是非主键索引）也会包含主键列，所以，如果主键定义的比较大，其他索引也将很大。如果想在表上定义 、很多索引，则争取尽量把主键定义得小一些。InnoDB 不会压缩索引。

3）InnoDB使用的是聚簇索引，将主键组织到一棵B+树中，而行数据就储存在叶子节点上，若使用"where id = 14"这样的条件查找主键，则按照B+树的检索算法即可查找到对应的叶节点，之后获得行数据。若对Name列进行条件搜索，则需要两个步骤：第一步在辅助索引B+树中检索Name，到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树种再执行一次B+树检索操作，最终到达叶子节点即可获取整行数据。

30.使用主键索引时有什么建议？

不建议使用uuid作为主键：因为uuid很难排序，容易往树中间插入数据，导致树结构改变。耗费大量数据库资源。而且uuid不是自增的。

建议使用int类型作为主键必要时用bigInt：因为int类型可以排序，而且可以自增。自增的话插入数据可以直接在末尾插入，几乎不影响前面的页。

建议主键值不要过大。因为辅助索引存储的是主键值，而主键值过大会加大辅助索引存储需要的空间。

31.索引不是万能的也会失效。

1.当模糊查询时name='%s%' ,第一个通配符为%索引就会失效。

2.不符合最左前缀原则。

     例如给name age bir三个字段加上复合索引

     根据where name and age and bir  ：使用索引

     根据where name and age ：使用索引

     根据where name：使用索引

     根据where age：不使用索引

     根据where bir：不使用索引

     根据where age and bir：不使用索引

3.用or。

     例如给name age 字段加上复合索引

     根据where name and age ：使用索引

     根据where name or age  ：不使用索引

32.索引有什么缺点：

给字段添加索引会耗费数据库资源。

当对表进行DML时也会对索引树进行修改，耗费数据库资源。

33.视图是什么？有什么用？

视图是一种根据查询（也就是SELECT表达式）定义的数据库对象，用于获取想要看到和使用的局部数据。可以隐藏表中的某些字段。对不同用户显示不同的内容等。

对视图进行CRUD实际上是对原表的CRUD。

34. 创建视图

例子：create view v_dept_emp as select ename,dname,sal,hiredate,e.deptno from emp e,dept d where e.deptno= e.deptno and e.deptno = 10;

创建视图时select语句不能嵌套子查询。

35.数据库三范式 

第一范式：每个表必须有主键，表中的所有字段原子上不可再分。

第二范式：基于第一范式，主键外的字段完全依赖于主键，不能产生部分依赖。复合主键大概率产生部分依赖。

                  多对多，三张表，中间表为关系表。

第三范式：基于第二范式，主键外的字段不能传递依赖于主键。

                  一对多，多的表加外键。

在真实开发中，以满足客户需求为主。有时需要拿数据冗余来换取效率。因为连接查询效率会降低。

36.一对一怎么表示关系

主键复用：A表的主键作为B表的主键和外键。

唯一外键 ：B表中添加一个唯一unique字段，指向A表的主键。