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表的主键。