2022年02年09日

存储引擎,事务隔离性limit的内容

1.索引

什么是索引是在数据库的字段上添加,为了提高查询效率而添加的是为了提高查询效率的一种机制,一张表的一个字段,当然可以多个字段俩he起来可以添加索引,索引相当于一本书的目录,是为缩小扫描范围而存在的一种机制对于一本字典,一页一页找,第二种是定位到一个大概位置在缩小范围快速查找,如果没有给name字段创建索引,mysql会全扫描,会将所有值都比对一遍,mysql主要是两种方式,主要是全表扫描,或检索.索引是目录,一种是全表扫,如果添加索引根据索引扫描.目录是否需要排序,需要,汉语字典的目录是需要排序的,只有排序才有区间查找这一说,在mysql数据库中索引是需要排序的和treeset数据结构相同,是一个自平衡的二叉树,索引是一个b-tree,遵循左小右大的原则存放,采用中序遍历取数据,t_user,id()主线,nameid, name,在任何数据库中,主键都会自动添加索引,如果有unique约束的话也会串讲索引对象,在任何数据库中,任何一张表的任何一条记录在硬盘上都有一个硬盘物理存储编号.索引是一个单独的对象,不同的引擎以不停的形式存在,在myisan中,索引在myi中,在innodb中索引在一个逻辑名称叫tablespace中, 都是以树的形式存在的自平衡二叉树,b-tree,索引的实现原理,首先存起来,100,二叉树,idindex(id字段的索引对象)mysql发现id上有索引对象,就会通过idindex进行查找,通过idindex索引对象定位到101快速定位,二叉树,通过101得出物理编号直接转换,mysql形成树的结构缩小范围形成物理编号,缩小扫描范围避免全表扫,索引会在主键中添加索引,数据量庞大,因为每个硬件的环境不同,一个字典在数据量庞大,该字段经常出现在where的后面以条件的形式存在,该字段总是被扫描,该字段很少dml操作,因为需要重新排序,优化是很重要的东西,不要随意添加索引,因为是需要维护的,太多容易降低,通过unique查询效率高些,索引的创建和删除,两个索引没有关系,

mysql> create index emp_ename_index on emp(ename);
Query OK, 0 rows affected, 1 warning (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> drop index emp_ename_index on emp;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

如何检查是否使用了索引??explain

以%开始用where会导致索引失效

数据量大的情况下会添加索引,必须要知道第一个字母是啥才能比较,

失效的第二种情况,使用or,要求两边的条件字段有索引才会走索引,如果其中仪表没有索引那么另一个字段上的索引也会失效,采用unique放回寺失效的第三种情况,复核索引的时候没有使用左侧的列查找,索引失效,什么是复核索引两个字段或者索引,叫做复核索引,用模糊查询,or,复核索引,在where中索引列参加了数学运算,在where中索引列使用了函数,失效的第六种情况,参加了运算,索引是mysql数据库中进行优化的重要手段,说出来连三个,优化的时候,有限考虑的因素,在数据库中分了很

2.索引的分类

单一索引:一个字段上添加索引

复核索引:两个以上

主键索引:primary key上加

唯一性索引:unique加

唯一性比较弱的情况下,可能会出现大量重复性.越唯一越高

3.视图

站在不同的角度去看待同一份数据,怎么创建,create view dept2_view as select * from dept2;只有dql语句才能以view形式创建,可以对视图对象进行增删改查,会导致有原表被操作,视图关联的原表数据会受影响,面向视图查询,都会影响到原表,创建视图对象,更新,面向视图更新,查询视图对象,视图对象在实际开发中的作用:视图是用来简化sql的,把复杂的一块东西做成视图利于维护 假设有一条很长的sql语句需要在不停位置反复使用每次很麻烦怎么办,可以把复杂的sql语句以视图对象的形式新建咋编写sql语句的位置直接使用视图对象,直接修改映射的sql语句,以文件的状态存在的,面向视图开发可以像使用表一样,可以对视图进行增删改查等操作,视图也是存储在硬盘上,不会消失,视图对应的语句只能是sql,但是完成后可进行crud,一般共同的术语,create,retruve,update,delete,retrive,

4.DBA常用命令

数据管理员

增加用户 mysql> create user tom identified by "123456";

导入导出:

重点是导入和导出,其他了解一下,数据的导出,数据导入用

导出 mysqldump bjpowernode > D:\bjpowernode.sql -u root -p123456

导入 source D:\bjpowernode.sql (创建,使用,导入)

导出要在dos窗口.导入要登录到mysql,先创建再使用然后初始化数据库

5.alter

alter table 表名称 add 列名 数据类型（add表示添加一列）
alter table 表名称 drop column 列名称（column表示列 ),

6.数据库的三范式

数据库设计三范式,什么数据库设计范式教你怎么进行数据库的设计,1.要求必须有主键,每一个字段原子性不可再分,要求苏欧欧非主键字段完全依赖不要产生部分依赖,第二范式要求直接一栏主键,不能传递,按照以上进行可以避免数据的冗余和空间的浪费,第一范式是最核心最重要的范式,所有表的设计都必须满足,必须有主键并且不可再分,学生编号姓名和练习方式,联系方式,拆成两列,每个第一范式是,所哟非主键字段必须完全依赖主键不能产生部分依赖,描述了学生和老师关系,怎么满足第一范式?修改,学生编号和教师编号联合做主键,两个字段联合做主键,复合组件,产生了部分依赖数据冗余,为了让以上的表满足第二范式需要三张表表示多对多的关系,背口诀多对多,三张表,关系表,两个外键,要求非主键字段必须直接依赖主键不能产生传递依赖,第一范式就是变成原子最小分割单元,第二范式是表再分成多个要完全依赖主键的一个关系表和多个外键表,第三范式是纯净化第二范式,为了让每个都直接依赖且不重复,拆分成两张表, 第一范式一对一,第二范式一对多,第三范式多对多

一对多,两张表,多的表加外键,一对一放到一张表上,因为太庞大了要拆分表,一对一设计,没哟拆分表之前一张表,loginname,login_pwd,realname,庞大的表拆分成两张,一对一,可以加外键,加unique约束外键加约束,外键加唯一性约束,口诀:一对一,外键为一!!数据库设计三范式是理论上的时间和理论有偏差,最终的目的是为了满足用户的需求,有时候会那冗余换速度,因为在sql中,表合表的连接换写在一张表中查询效率高,有时候会冗余但是为了减少连接次数也是合理的,对开发中sql语句的编写难度也会降低,主要为了满足客户需求

-- select select max(sal) as maxsal from emp group by deptno;
select e.ename, t.* from emp e join 
(select deptno, max(sal) as maxsal from emp group by deptno) t
on e.deptno = t.deptno and t.maxsal = e.sal;

select e.deptno ,e.ename, e.sal from emp e join 
(select deptno, avg(sal) as avgsal from emp group by deptno) t
on e.deptno = t.deptno and e.sal > t.avgsal order by e.deptno;

SELECT e.deptno, e.ename, e.sal, s.grade, round(avg(s.GRADE), 2) as avggrade
from emp e join salgrade s
on e.sal BETWEEN s.losal and s.hisal
group by e.DEPTNO;

-- select e.deptno ,s.grade from emp e left join salgrade s on 
-- select t.*,s.grade from salgrade s join 
-- (select ename, ROUND(avg(sal), 2) as avgsal from emp group by deptno) t 
-- on avgsal between s.losal and s.hisal order BY s.GRADE;
-- select t.*,s.grade from salgrade s join 
-- (select ename, ROUND(avg(sal), 2) as avgsal from emp group by deptno) t 
-- on avgsal between s.losal and s.hisal order BY s.GRADE;

-- select t.*, round(avg(t.grade), 2) as avggrade from (SELECT e.deptno, e.ename, e.sal,s.grade from emp e join salgrade s
-- on e.sal BETWEEN s.losal and s.hisal) t 
-- group by deptno;
SELECT e.deptno, e.ename, e.sal, s.grade, round(avg(s.GRADE), 2) as avggrade
from emp e join salgrade s
on e.sal BETWEEN s.losal and s.hisal
group by e.DEPTNO;
select ename, sal from emp order by sal desc limit 1;

SELECT sal FROM emp where sal NOT in (select DISTINCT a.sal from emp a join emp b where a.SAL < b.SAL);

-- select deptno, avg(sal) AS avgsal from emp group BY deptno 
-- ORDER BY avgsal LIMIT 1;
-- select deptno, max(avgsal) from(
-- select deptno, avg(sal) AS avgsal from emp group BY deptno 
-- ORDER BY avgsal) t;
select deptno, avg(sal) AS avgsal from emp group BY deptno 
having max(avgsal) ;

select d.dname, avg(e.sal) as avgsal from emp e join dept d on e.DEPTNO=d.DEPTNO group by e.deptno 
ORDER BY avgsal desc limit 1;

-- select t.*, s.grade from 
-- (select deptno, avg(sal) as avgsal from emp 
-- group BY DEPTNO) t
-- join salgrade s on t.avgsal between s.losal and s.HISAL
-- ORDER BY GRADE
-- limit 1;

select t.DEPTNO, s.grade from salgrade s JOIN
(select DEPTNO, avg(sal) as avgsal from emp 
group BY deptno
ORDER by avgsal LIMIT 1) t on
t.avgsal between s.losal and s.hisal ;

SELECT e.ename, e.sal, e.MGR, a.ename from emp e join  
 (select ename, sal from emp 
 where EMPNO NOT in(select mgr from emp where mgr is not NULL)
 order BY sal desc LIMIT 1) t join emp a on e.MGR = a.EMPNO
where e.EMPNO in(select mgr from emp where mgr is not NULL)
and e.sal > t.sal;
select ename, sal from emp 
order by sal desc LIMIT 0,5;

select ename, sal from emp 
order by sal desc LIMIT 5,5;
-- 
-- 

select ename, HIREDATE from emp 
order by HIREDATE desc LIMISELECT t.grade, count(grade) from
(select s.grade from emp e join salgrade s 
on e.sal between s.losal AND s.hisal) t 
group by grade;T 0,5; 

select a.ename, b.ename as boss from 
emp a left join emp b on a.mgr = b.empno; 

-- select e.empno, e.ename, d.dname from emp e join 
-- (select a.ename,b.HIREDATE from emp a right join emp b on a.mgr = b.empno) t
-- on e.ENAME = t.ename join dept d on e.deptno = d.deptno
-- where e.HIREDATE < t.HIREDATE;
select a.empno, a.ename, d.dname from 
emp a join emp b on a.mgr = b.empno
join dept d on a.deptno = d.DEPTNO
where a.HIREDATE < b.HIREDATE;

-- select e.empno, e.ename, d.dname from emp e join 
-- (select a.ename,b.HIREDATE from emp a right join emp b on a.mgr = b.empno) t
-- on e.ENAME = t.ename join dept d on e.deptno = d.deptno
-- where e.HIREDATE < t.HIREDATE;
select a.ename "员工",a.HIREDATE, b.ename "领导", b.HIREDATE, d.dname from 
emp a join emp b on a.mgr = b.empno
join dept d on a.deptno = d.DEPTNO
where a.HIREDATE < b.HIREDATE
order by dname;

select e.ename, d.dname from emp e RIGHT JOIn dept d 
on e.deptno = d.deptno; 
-- 

select deptno,count(deptno) as count_deptno from emp 
GROUP BY DEPTNO having count_deptno >=5 
;select * from emp where sal >(
select sal from emp where ename = "smith")

select e.ename, e.job, d.dname, d.deptno, t.deptcount from emp e 
join dept d on e.deptno = d.deptno
join (select deptno, count(*) as deptcount from emp group by deptno) t
on d.DEPTNO = t.deptno
where e.job = "clerk";

select count(*) from emp group by deptno;

select job, min(sal), count(ename) from emp
group by job having min(sal) > 1500;

SELECT ename from emp where 
DEPTNO = (select deptno from dept where dname = "sales"); 

select a.ename, d.dname, b.ename, s.grade from emp a
join emp b on a.MGR = b.EMPNO 
JOIN dept d on a.DEPTNO = d.DEPTNO
join salgrade s on a.sal between s.losal and s.HISAL
where a.sal > (select avg(sal) from emp);

select e.ename, d.dname from emp e 
join dept d
on e.deptno = d.deptno
where e.job = (select job from emp where ename = "SCOTT")

and e.ename != "scott"; 

select ename, sal, deptno from emp
where sal in (select distinct sal from emp where deptno = 30)
 

select e.ename, e.sal, d.dname from emp e
join dept d on e.deptno = d.DEPTNO
where sal > 
(select max(sal) from emp where deptno = 30);
select count(e.ename)"人数", avg(e.sal) "平均工资", 
TIMESTAMPDIFF(year,e.hiredate,NOW()) as "工作年限"
from emp e RIGHT join dept d on e.DEPTNO = d.DEPTNO
group by d.DEPTNO
;select e.ename, d.dname, e.sal FROM
emp e join dept d on e.DEPTNO = d.DEPTNO;

right join dept d on e.DEPTNO = d.DEPTNO
group by d.DNAME; 

select t.*, e.ename from emp e
left join (select job, min(sal) as min_sal from emp 
group by job)t  
on e.job = t.job
where e.sal = min_sal 

;select deptno, min(sal) from emp 
WHERE ename in (select ename from emp where job = "manager")
group by DEPTNO
;
select ename, sal*12 as yearsal FROM emp order BY yearsal;

select a.ename, b.ename from emp a join emp b
on a.mgr = b.EMPNO where b.sal > 3000;

select d.deptno, d.dname, d.loc, sum(e.sal), count(e.ename) from emp e right join dept d
on e.DEPTNO = d.deptno where d.dname like "%s%"
group BY d.deptno, d.dname, d.loc
;