数据库知识点

1索引

介绍

索引本质上是一张表，保存了主键与索引字段，在对数据做频繁的查询或排序时，可在某些字段上添加索引，提高检索的的效率，降低IO成本，并可以使用索引列，对数据进行排序，降低排序的成本。盲目的添加索引是不正确的。索引表保存数据占用空间，在对数据进行增删改操作时，降低了操作的速度，在更新表的同时不仅要保存操作数据还需要更新索引表中的数据，因此我们可以在频繁的作为查询条件的字段上添加索引，查询中与其他表关联的字段，外检关系建立索引，在高并发下创建组合索引等恰当的使用索引事务场景。索引分为四类有唯一索引，复合索引，主键索引，单值索引。唯一索引的索引列必须是唯一的，但允许有空值，复合索引指一个索引包含多个列。例如用户表需要用户名以及手机号确定一个信息，就需要添加复合索引了。主键索引是一个特殊的唯一索引，非空且默认存在，单值索引，一个索引值包含单个列，一个表可以有多个单列索引。

1.2失效原因

1.索引失效的原因

1.模糊查询like以%开头，
2.数据类型错误
3.对索引字段使用内部函数
4.索引列是null
5.索引列运行四则运算
6.复合索引不按索引列最左特性开始查找

1.3.索引的类型

复合(最左特性)
主键
单值
唯一

1.4 为什么添加了索引查询快了

本质上是因为数据库会根据索引的数据结构（如B+树）构建一个索引树。索引树的结构使得数据库可以快速定位到满足查询条件的数据，而不需要扫描整个表。通过索引树的搜索，数据库可以快速确定需要读取的数据页，并直接读取对应的数据行，从而大大减少了查询所需的读取和比较操作。

1.4如何创建索引

创建主键索引：

alter table table_name add primary key(column);
1

添加UNIQUE(唯一索引)

alter table table_name add unique(column);
1

添加普通索引

alter table table_name add index index_name(column);
1

添加多列索引

alter table table_name add index index_name(column1,column2,column3);
1

2.Sql语句

2.1基础函数

lower

SELECT 'ABC',LOWER('ABC') from dept; --数据转小写
1

upper

select upper(dname) from dept --数据转大写
1

length

select length(dname) from dept --数据的长度
1

substr

SELECT dname,SUBSTR(dname,1,3) FROM dept; --截取[1,3]
1

concat

select dname,concat(dname,'123') X from dept --拼接数据
1

replace

select dname,replace(dname,'a','666') X from dept --把a字符替换成666
1

ifnull

select ifnull(comm,10) comm from dept2 #判断,如果comm是null,用10替换
1

round四舍五入，ceil向上取整，floor向下取整

select comm,round(comm) from emp–直接四舍五入取整
select comm,round(comm,1) from emp–四舍五入并保留一位小数
select comm,ceil(comm) ,floor(comm) from emp–ceil向上取整,floor向下取整
1
2
3

now

select now() -- 年与日 时分秒
select curdate() --年与日
select curtime() --时分秒
1
2
3

2.2分组

group by

SELECT deptno,job,MAX(sal),AVG(sal) FROM emp
GROUP BY deptno,job #deptno和job都满足的
1
2

having

select deptno, AVG(sal) from emp
group by deptno #按部门分组
having AVG(sal)<8000 #查询条件,类似where,但是group by只能配合having
1
2
3

2.3聚合

count

select count(*) from emp --底层优化了
select count(1) from emp --效果和*一样
select count(comm) from emp --慢,只统计非NULL的
1
2
3

max / min

select max(sal) from emp --求字段的最大值
select max(sal) sal,max(comm) comm from emp
select min(sal) min from emp --获取最小值
select min(sal) min,max(sal) max from emp --最小值最大值
SELECT ename,MAX(sal) FROM emp group by ename --分组
1
2
3
4
5

sum / avg

select count(*) from emp --总记录数
select sum(sal) from emp --求和
select avg(sal) from emp --平均数
1
2
3

2.4排序

order by

SELECT * FROM emp order by sal #默认升序
SELECT * FROM emp order by sal desc #降序
1
2

2.5链接

left join : 左连接，返回左表中所有的记录以及右表中连接字段相等的记录。
right join : 右连接，返回右表中所有的记录以及左表中连接字段相等的记录。
inner join : 内连接，又叫等值连接，只返回两个表中连接字段相等的行。

2.6多表联查

1.子查询
语法：

select  字段名  
from 表1，表2 …
where 表1.字段 = 表2.字段
and 其它查询条件
1
2
3
4

例：以学生表student和班级表class为例

Select   student.sid,  student.sname,  student.classid, class.classid,  class.classname
from student，class
where student.classid = class.classid
1
2
3

2.join连接
内连接 inner join
语法：

from 表1
inner join 表2
on 表1.字段 = 表2.字段
1
2
3

外连接:

1. left join
   语法：

select  字段名  
from 表1
left join 表2
on 表1.字段 = 表2.字段
1
2
3
4

2.right join
语法：

select  字段名  
from 表1
right  join 表2
on 表1.字段 = 表2.字段
1
2
3
4

3. left join union right join
   语法：

select  字段名  
from 表1
left join 表2
on 表1.字段 = 表2.字段
union
select  字段名  
from 表1
right  join 表2
​​​​​​​on 表1.字段 = 表2.字段
1
2
3
4
5
6
7
8
9

例：

select   student.*  , class.*from student
left join class
on student.classid = class.classid
union
select  student.*  , class.*from student
right join class
on student.classid = class.classid
1
2
3
4
5
6
7

3.笛卡尔积

select * from t_user, t_address;
1

2.7条件查询

distinct
使用distinct关键字，去除重复的记录行

SELECT DISTINCT loc FROM dept;
1

like模糊查询

select * from emp where ename like 'l%' --以l开头的
select * from emp where ename like '%a' --以a结束的
select * from emp where ename like '%a%' --中间包含a的
select * from emp where ename like 'l__' --l后面有两个字符的 _代表一个字符位置
1
2
3
4

null

select * from emp where mgr is null --过滤字段值为空的
select * from emp where mgr is not null --过滤字段值不为空的
1
2

between and

select * from emp where sal between 3000 and 10000
1

limit
在mysql中，通过limit进行分页查询：

select * from emp limit 0,3 --从第一条开始,展示3条记录--前三条
1

3.事务

3.1 特性（ACID）

原子性：一个事务要么全部成功，全部失败，不会停在某个阶段
一致性 ：事务的前后处于一致状态
隔离性：多个事务同时操作相同数据库的同一个数据时，一个事务的执行不受另外一个事务的干扰
持久性：一个事务一旦提交，则数据将持久化到本地，除非其他事务对其进行修改

3.2隔离级别

1.读未提交，最低隔离级别、事务未提交前，就可被其他事务读取（会出现幻读、脏读、不可重复读）；
2.提交读，一个事务提交后才能被其他事务读取到（会造成幻读、不可重复读），SQL server 的默认级别；
3.可重复读，保证多次读取同一个数据时，其值都和事务开始时候的内容是一致，禁止读取到别的事务未提交的数据（会造成幻读），
MySQL 的默认级别；
4.序列化，代价最高最可靠的隔离级别，该隔离级别能防止脏读、不可重复读、幻读。

3.3三大范式

第一范式：强调的是列的原子性，即数据库表的每一列都是不可分割的原子数据项。
第二范式：要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性。
第三范式：任何非主属性不依赖于其它非主属性。

3.4约束

1.主键约束（Primary Key ）
保证数据的唯一性，并且主键列数据不能为空（唯一性，非空性）。

alter table 表名
add constraint 主键名 primary key(约束列名称)
1
2

2.唯一约束（Unique ）：
保证数据的唯一性，唯一约束的列可以为空（唯一性，可以空，但只能有一个）。

alter table 表名
add constraint 唯一约束名 unique(字段)
1
2

3.检查约束（Check ）：
保证数据的有效性，让值在有效范围内取值，对该列数据的范围、格式的限制（如：年龄、性别等）

alter table 表名
add constraint 检查约束名 check(约束列名称 between 10 and 40)
1
2

4.默认约束（Default ）：
保证数据的完整性，如果没有入信息时，会使用默认信息填入，保证数据的完整性。

alter table 表名
add constraint 默认约束名 default(默认值) for 约束列名称
1
2

5.外键约束（Foreign Key ）：
需要建立两表间的关系并引用主表的列，保证数据的完整性，互相依赖的数据不能缺失。

alter table 子表名
add constraint 约束名称 foreign key(外键列名称) references 主表名称(主键列字段)
1
2

6.非空约束
数据列不能为空

ALTER TABLE 表名 
MODIFY 非空约束名 字段类型 NOT NULL;

1
2
3

其他操作
1.删除约束

alter table 表名
drop constraint 约束名
1
2

2.关闭约束

alter table 表名
disable constraint 约束名 cascade
//如果没有被引用则不需CASCADE关键字
1
2
3

3.打开约束

alter table 表名
enable constraint 约束名
1
2

4. MySQL 问题排查都有哪些手段？

使用 show processlist 命令查看当前所有连接信息。
使用 explain 命令查询 SQL 语句执行计划。
开启慢查询日志，查看慢查询的 SQL。

5.Sql优化