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);
添加UNIQUE(唯一索引)
alter table table_name add unique(column);
添加普通索引
alter table table_name add index index_name(column);
添加多列索引
alter table table_name add index index_name(column1,column2,column3);
2.Sql语句
2.1基础函数
lower
SELECT 'ABC',LOWER('ABC') from dept; --数据转小写
upper
select upper(dname) from dept --数据转大写
length
select length(dname) from dept --数据的长度
substr
SELECT dname,SUBSTR(dname,1,3) FROM dept; --截取[1,3]
concat
select dname,concat(dname,'123') X from dept --拼接数据
replace
select dname,replace(dname,'a','666') X from dept --把a字符替换成666
ifnull
select ifnull(comm,10) comm from dept2 #判断,如果comm是null,用10替换
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向下取整
now
select now() -- 年与日 时分秒
select curdate() --年与日
select curtime() --时分秒
2.2分组
group by
SELECT deptno,job,MAX(sal),AVG(sal) FROM emp
GROUP BY deptno,job #deptno和job都满足的
having
select deptno, AVG(sal) from emp
group by deptno #按部门分组
having AVG(sal)<8000 #查询条件,类似where,但是group by只能配合having
2.3聚合
count
select count(*) from emp --底层优化了
select count(1) from emp --效果和*一样
select count(comm) from emp --慢,只统计非NULL的
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 --分组
sum / avg
select count(*) from emp --总记录数
select sum(sal) from emp --求和
select avg(sal) from emp --平均数
2.4排序
order by
SELECT * FROM emp order by sal #默认升序
SELECT * FROM emp order by sal desc #降序
2.5链接
left join : 左连接,返回左表中所有的记录以及右表中连接字段相等的记录。
right join : 右连接,返回右表中所有的记录以及左表中连接字段相等的记录。
inner join : 内连接,又叫等值连接,只返回两个表中连接字段相等的行。
2.6多表联查
1.子查询
语法:
select 字段名
from 表1,表2 …
where 表1.字段 = 表2.字段
and 其它查询条件
例:以学生表student和班级表class为例
Select student.sid, student.sname, student.classid, class.classid, class.classname
from student,class
where student.classid = class.classid
2.join连接
内连接 inner join
语法:
from 表1
inner join 表2
on 表1.字段 = 表2.字段
外连接:
- left join
语法:
select 字段名
from 表1
left join 表2
on 表1.字段 = 表2.字段
2.right join
语法:
select 字段名
from 表1
right join 表2
on 表1.字段 = 表2.字段
- left join union right join
语法:
select 字段名
from 表1
left join 表2
on 表1.字段 = 表2.字段
union
select 字段名
from 表1
right join 表2
on 表1.字段 = 表2.字段
例:
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
3.笛卡尔积
select * from t_user, t_address;
2.7条件查询
distinct
使用distinct关键字,去除重复的记录行
SELECT DISTINCT loc FROM dept;
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后面有两个字符的 _代表一个字符位置
null
select * from emp where mgr is null --过滤字段值为空的
select * from emp where mgr is not null --过滤字段值不为空的
between and
select * from emp where sal between 3000 and 10000
limit
在mysql中,通过limit进行分页查询:
select * from emp limit 0,3 --从第一条开始,展示3条记录--前三条
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(约束列名称)
2.唯一约束(Unique ):
保证数据的唯一性,唯一约束的列可以为空(唯一性,可以空,但只能有一个)。
alter table 表名
add constraint 唯一约束名 unique(字段)
3.检查约束(Check ):
保证数据的有效性,让值在有效范围内取值,对该列数据的范围、格式的限制(如:年龄、性别等)
alter table 表名
add constraint 检查约束名 check(约束列名称 between 10 and 40)
4.默认约束(Default ):
保证数据的完整性,如果没有入信息时,会使用默认信息填入,保证数据的完整性。
alter table 表名
add constraint 默认约束名 default(默认值) for 约束列名称
5.外键约束(Foreign Key ):
需要建立两表间的关系并引用主表的列,保证数据的完整性,互相依赖的数据不能缺失。
alter table 子表名
add constraint 约束名称 foreign key(外键列名称) references 主表名称(主键列字段)
6.非空约束
数据列不能为空
ALTER TABLE 表名
MODIFY 非空约束名 字段类型 NOT NULL;
其他操作
1.删除约束
alter table 表名
drop constraint 约束名
2.关闭约束
alter table 表名
disable constraint 约束名 cascade
//如果没有被引用则不需CASCADE关键字
3.打开约束
alter table 表名
enable constraint 约束名
4. MySQL 问题排查都有哪些手段?
使用 show processlist 命令查看当前所有连接信息。
使用 explain 命令查询 SQL 语句执行计划。
开启慢查询日志,查看慢查询的 SQL。