DBMS，SQL，DB三者的关系

DBMS：数据库管理系统
SQL：用于访问和处理数据库的标准的计算机语言
DB：数据库

三者的关系：DBMS数据库管理系统执行SQL语句，来操作DB数据库当中的数据的

关于in与or的建议

条件查询 in(a,b,c,...)
可以替代or，提高检索效率
例：

select id,name,age from student where name = '张三' or name = '李四'
替换为：
select id,name,age from student where name in ('张三' , '李四')

模糊查询like

%代表任意多个字符
_代表任意1个字符

例1：查询姓张的同学
select id,name,age from student where name like '张%'

例2：查询姓李且只有两个字的同学
select id,name,age from student where name like '李_'

ps：CSDN遵循Markdown语法，不能打出下划线，所以需要转义字符\，如\_

排序 order by

asc 升序（默认）
desc 降序
排序 order by sal 薪资 asc/desc ;

例：按薪水为公司的员工排序降序
select name,age,sal from emp order by sal desc

SQL完整的语句系统的执行顺序（重要）

select 
       *             5
from
     tableName       1    
where
     条件(join)       2
group by
     ...             3
having
     ...             4
order by
     ...             6
limit
     ...             7

分组函数

分组函数：多行处理函数

• 分组函数一共5个
• 输入多行，输出结果总是1行
• 分组函数自动忽略NULL
• NULL参与运算时，结果为NULL，解决办法是：ifnull(comm,0)
• 分组函数不可直接使用在where子句中，因为分组函数是在group by执行之后才会执行
• count(*) 统计总记录的条数，count(字段名) 统计某个字段不为NULL的数量

聚合函数 group by

group by ：按照某个字段或者某些字段进行分组
having ：对分组之后的数据进行再次过滤

例1：按公司员工的工作岗位进行分组
select name,job from emp group by job;

例2：找出每个部门的最高薪资，要求显示薪资大于2900的数据

select max(sal),deptno from emp group by deptno having max(sal)>2900; 效率低
select max(sal),deptno from emp where sal>2900 group by deptno; 
效率高，建议能够使用where过滤的尽量使用能够在分组之前过滤的，就先进行where过滤  

例3：找出每个部门的平均薪资，要求显示薪资>2000的数据

步骤1.找出每个部门的平均薪资
 select deptno,avg(sal) from emp group by deptno;

步骤2.要求显示薪资>2000的数据
 select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;

去重 distinct

select distinct job from emp;
distinct 只能出现在所有字段的最前边，表示字段的联合去重

连接查询

在实际开发中，大部分的情况下都不是从单表中查询数据，一般都是多张表联合查询取出最终的数据
一般一个业务都会对应多张表，比如：学生和班级，起码两张表

连接查询的分类

根据语法出现的年代来划分

• SQL92（一些老的DBA可能还在使用这种语法。DBA：数据库管理员）
• SQL99（比较新的语法）

根据表的连接方式来划分

内连接：

• 等值连接
• 非等值连接 between and
• 自连接

外连接

• 左外连接（左连接）
• 右外连接（右连接）

全连接（很少用）

注：在表的连接查询方面有一种现象被称为：笛卡尔积现象
在数据库中，两张表连接查询默认显示的结果条数是两张表记录条数的乘积

案例：找出每一个员工的部门名称，要求显示员工名和部门名
  select ename,dname from emp,dept;

如何避免笛卡尔积现象？
避免了笛卡尔积现象，不会减少记录的匹配次数，只不过显示的是有效记录

等值连接

select                //SQL92 旧语法
  e.ename,d.dname
from 
  emp e,dept d
where 
  e.deptno = d.deptno;  
  
**内连接之等值连接：**

特点：条件是等量关系
select            //SQL99 新语法,常用的
  e.ename,d.dname
from 
  emp e
join
  dept d
on
  e.deptno = d.deptno;   

语法：
  ...
    A
 (inner) join        //inner 可读性更强，但可以省略
    B
 on
  连接条件
 where
  ...
SQL99语法结构更清晰，表的连接条件和后来的where条件分离了

内连接之非等值连接：

特点：连接条件中的关系是非等量的关系

案例：找出每个员工的工资等级，要求显示员工名、工资、工资等级
  select
    e.ename,e.sal,s.grade
  from
    emp e
  join
    salgrade s
  on
    e.sal between s.losal and hisal;

内连接之自连接

特点：一张表看做两张表，自己连接自己

案例：找出每个员工的上级领导，要求显示员工名和对应的领导名
  select 
    a.ename,b.ename
  from
    emp a
  inner join
    emp b
  on
    a.mgr = b.empno;  

外连接

内连接：
假设A表和B表进行连接，使用内连接，凡是A表和B表能够匹配上的记录查询出来
AB两张表没有主副之分，两张表是平等的
外连接：
假设A表和B表进行连接，AB两张表中有一张表是主表，一张表是副表，主要查询主表中的
数据，捎带着查询副表，当副表的数据没有和主表数据匹配上，副表自动模拟出NULL与之匹配
外连接的分类
左外连接（左连接）：表示左边的这张表是主表
右外连接（右连接）：表示右边的这张表是主表

案例：找出每个员工的上级领导
   select 
     a.ename,b.ename
   from
     emp a
   left (outer) join  //left 左边是主表 outer可以省略
     emp b
   on
     a.mgr = b.empno;   

在实际开发中，大部分是外连接

外连接最重要的特点：主表的数据无条件的全部查询出来

案例：找出哪个部门没有员工？
 select
  d.deptno,d.dname
 from
    emp e
 right outer join
    dept d
 on 
    e.deptno = d.deptno
 where
    e.empno is null;

三张表连接查询

案例：找出每一个员工的部门名称以及工资等级
  select
   e.ename,d.dname,s.grade
  from 
    emp e
  join  
	dept d
  on 
    e.deptno=d.deptno
  join	
	salgrade s
  on
    e.sal between s.losal and hisal;

案例：找出每一个员工的部门名称以及工资等级以及上级领导
  select
   e.ename '员工',d.dname '部门',s.grade '工资等级',em.ename '领导'
  from 
    emp e
  join  
	dept d
  on 
    e.deptno=d.deptno
  join	
	salgrade s
  on
    e.sal between s.losal and hisal
  left join
    emp em
  on
    e.mgr = em.empno;

子查询

select语句当中嵌套select语句，被嵌套的select语句是子查询

  select
    ..(select)    在select中嵌套
  from
    ..(select)    在from中嵌套
  where
    ..(select)    在where中嵌套

• 在where子句中使用子查询

  案例：找出高于平均薪资的员工信息
  select
    *
  from
    emp
  where
    sal > (select avg(sal) from emp);
  

• from后面嵌套子查询

案例：找出每个部门平均薪水的薪资等级
  select
    t.*,s.grade
  from
    (select deptno,avg(sal) avgsal from emp group by deptno) t
  join
    salgrade s
  on
    t.avgsal between s.losal and hisal;

案例：找出每个部门平均的薪水等级
   select
     e.ename,e.sal,e.deptno,s.grade,avg(s.grade)
   from
     emp e
   join
     salgrade s
   on
     e.sal between s.losal and s.hisal
   group by
     e.deptno

• 在select后面嵌套子查询

案例：找出每个员工所在的部门名称，要求显示员工名和部门名
  select
    emp.ename,dept.dname
  from
    emp
  join
    dept
  on
    dept.deptno = emp.deptno;

将查询结果集相加 union

案例：找出工作岗位是SALESMAN和MANAGER的员工？
  select
    ename,job
  from
    emp
  where
//    job = 'salesman' or job = 'manager'; 
// or改成in
    job in ('salesman','manager');

方法二：
  select ename,job from emp where job = 'salesman';
    union
  select ename,job from emp where job = 'manager';

limit （重点，分页查询）

特点：mysql特有的，其他数据库没有。
作用：limit取结果集中的部分数据
语法：limit startIndex,length
startIndex 表示起始位置，length 表示取几个

案例：取出工资前5名的员工
   select
     ename,sal
   from
     emp
   order by
     sal desc
   limit   
     0,5;

案例：找出工资排名在第4到第9名的员工
  select
    ename,sal
  from
    emp
  order by
    sal desc
  limit
    3,6;

			** 通用的标准分页sql **

  每页显示3条记录
  第1页：0，3
  第2页：3，3
  第3页：6，3
  
  每页显示pageSize条记录（如上述的pageSize=3）
  pageNo表示第几页，如上述的第1页、第2页、第3页，pageNO=1，2，3，....
  
  公式：第pageNo页：(pageNo-1)*pageSize,pageSize

创建表 create

  create table 表名(
     字段名1 数据类型 约束，
     字段名2 数据类型 约束，
     字段名3 数据类型 约束，
     ...
  );
  create table t_student(
	no bigint,
	name varchar(255),
	sex char(1),
	classno varchar(255),
	birth char(10)
  );
  
主键值自增： auto_increment 从1开始递增

插入表 insert

  insert into 
  	表名(字段名1,字段名2,字段名3,....) 
  values
  	(值1,值2,值3,...)
  	
  insert into 
  	t_student(no,name,sex,classno,birth) 
  values
  	(1,'zhangsan','1','gaosan1ban','1999-07-06');

复制一张表

  create table emp1 as select * from emp;
  创建一张表emp1，将emp表的查询结果放到emp1中

修改数据 update

update 表名 set 字段名1=值1,字段名2=值2,... where 条件;

删除数据 delete

  delete from 表名 where 条件;
  
  删除大表 truncate table emp;  //表被截断，不可回滚，永久丢失

关于MySQL从删除到跑路的方法 : )

1.删除数据库删除后可能会遗留日志，一些数据还是可以通过日志恢复的，所以索性把日志也一起删了吧
drop database databasename
purge binary logs to '日志名字';
2.直接删除mysql的服务和数据
find / -name mysql
3.删除找到的关于mysql的一切
rm -rf /var/lib/mysql
rm -rf /var/lib/mysql
rm -rf /usr/lib64/mysql
rm -rf /etc/my.cnf

(*￣︶￣) (*￣︶￣) (*￣︶￣) (*￣︶￣) (*￣︶￣) (*￣︶￣) (*￣︶￣) (*￣︶￣) (*￣︶￣)
上面仅仅是个玩笑，但是多学习一点总归是好的
(*￣︶￣) (*￣︶￣) (*￣︶￣) (*￣︶￣) (*￣︶￣) (*￣︶￣) (*￣︶￣) (*￣︶￣) (*￣︶￣)

约束

1. 非空约束 not null ：约束的字段不能为NULL
2. 唯一数据 unique : 约束的字段不能重复
3. 主键约束 primary key : 约束的字段既不能为NULL，也不能重复
4. 外键约束 foreign key : oreign key(classno) references t_class(cno)
   classno的外键引用t_class表中的cno字段
5. 检查约束 check ： Oracle有，mysql没有

存储引擎 engines

查看当前mysql支持的存储引擎
  show engines \G

6. MyISAM： 读 咪塞姆
   不支持事务，mysql最常用的存储引擎，但不是默认的
   使用3个文件表示一张表，表结构.frm，表数据.MYD，索引.MYI
   灵活的AUTO_INCREMENT字段处理
   可被转换为压缩、只读表来节省空间

7. InnoDB：
   支持事务、外键、行级锁
   mysql默认存储引擎
   每个InnoDB表在数据库目录中以.frm格式文件表示
   InnoDB表空间tablespace被用于存储表的内容（逻辑概念），无法被压缩，无法转换成只读
   提供一组用来记录事务性活动的日志文件
   在mysql服务器崩溃后提供自动恢复

8. MEMORY：
   不支持事务，数据容易丢失，因为所有数据和索引都是存储在内存当中
   查询速度最快，以前被称为HEAP引擎
   在数据库目录中，每个表以.frm格式文件表示
   表数据及索引被存储在内存中
   表级锁机制
   不能包含TEXT(CLOB)或BLOB字段

事务 transaction

一个事务是一个完整的业务逻辑单元，不可再分

  比如：银行账户转账，从A账户向B账户转账1000，需要执行两条update语句
  update t_act set balance = balance -1000 where actno='act-001';
  update t_act set balance = balance +1000 where actno='act-002';

以上两条DML语句必须同时成功，或者同时失败，不允许出现一条成功，一条失败
事务的存在是为了保证数据的完整性、安全性（insert delete update）

假设一个业务，需要先执行一条Insert，再执行update，最后执行delete

1. 首先开启事务机制
2. 执行insert语句-->这个执行成功之后，把执行记录到数据库的操作历史中，并不会向文件中保存一条数据，不会真正的修改硬盘上的数据
3. 执行update语句-->这个执行也是记录一下历史操作，不会真正的修改硬盘上的数据
4. 执行delete语句-->这个执行也是记录一下历史操作，记录到缓存，不会真正的修改硬盘上的数据
5. 提交事务commit或者回滚事务rollback（结束）

事务的特性：
ACID
A 原子性：事务是最小的工作单元，不可再分
C 一致性：事务必须保证多条DML语句同时成功或者同时失败
I 隔离性：事务A与事务B之间具有隔离
D 持久性：最终数据必须持久化到硬盘文件，事务才算成功的结束

事务之间的隔离性：事务隔离性存在隔离级别

• 第一级别：读未提交（read uncommitted）
  对方事务还没有提交，我们当前事务可以读取到对方未提交的数据
  读未提交存在脏读（Dirty Read）现象，表示读到了脏的数据
  未提交的事务，另一个线程用户也能够读取到未提交的数据

• 第二级别：读已提交（read committed）
  对方事务提交之后的数据我方可以读取到
  解决了脏读现象
  读已提交存在的问题是：不可重复读（做不到从头到尾读取到的数据一样的）
  只有提交了事务，另一个线程的用户才能读取到提交后的数据

• 第三级别：可重复读（repeatable read）
  解决了不可重复读的问题
  但存在的问题是：读取到的数据是幻象
  数据可以一直读，读取到的是备份数据，即使数据已经被另一个线程的用户删除了

• 第四级别：序列化读/串行化读（serializable）
  解决了所有问题
  效率低，需要事务排队
  两个线程的用户不可同时进行事务操作，用户A进行操作事务，用户A必须提交事务，用户B才能进行事务操作，排队

  oracle数据库默认的隔离级别是：第二级别
  mysql数据库默认的隔离级别是：第三级别

mysql事务默认情况下是自动提交的（只要执行任意一条DML语句则提交一次）

关闭自动提交 start transaction;(即开启事务)
 set global transaction isolation level read uncommitted;
 
设置全局的事务隔离级别 读未提交
 select @@global.tx isolation; 查看全局事务隔离级别

索引 index

1. 什么是索引，有什么作用
   索引就相当于一本书的目录，通过目录可以快速的找到对应的资源
   在数据库方面，查询一张表的时候有两种检索方式：
   1)全表扫描
   2)根据索引检索（效率很高）
2. 索引为什么可以提高检索效率？
   原因是缩小了扫描的范围
   索引虽然可以提高检索效率，但是不能随意的添加索引，因为索引也是数据库当中的对象，也需要数据库不断的维护，维护是有成本的。
   比如，表中的数据经常被修改，这样就不适合添加索引，因为数据一旦修改，索引需要重新排序，进行维护

   添加索引是给某一个字段，或者说某些字段添加索引
     select ename,sal from emp where ename = 'SMITH';
   

   当ename字段上没有添加索引的时候，以上sql语句会进行全表扫描，扫描ename字段中所有的值
   当ename字段上添加索引的时候，以上sql语句会进行全表扫描，扫描ename字段中所有的值

3. 怎么创建索引对象？怎么删除索引对象？

创建索引对象
create index 索引名称 on 表名（字段名）;

删除索引对象
drop index 索引名称 on 表名;

4. 什么时候考虑给字段添加索引？
   * 数据量庞大 （根据客户的需求，根据线上的环境）
   * 该字段很少的DML操作 （因为字段进行修改操作，索引也需要维护）
   * 该字段经常出现在where子句中 （经常根据哪个字段查询）

主键和具有unique约束的字段自动会添加索引
根据主键查询效率较高，
explain 查看该语句的解释

索引底层采用的数据结构是：B + Tree

5. 索引的实现原理
   通过B Tree缩小扫描范围，底层索引进行了排序，分区，索引会携带数据在表中的“物理地址”，最终通过索引检索到数据之后，获取到关联的物理地址，通过物理地址定位表中的数据，效率最高

	select ename from emp where ename = 'SMITH';
	通过索引转换为：
	select ename from emp where 物理地址 = '0x001';

6. 索引的分类
   单一索引：给单个字段添加索引
   复合索引：给多个字段联合起来添加1个索引
   主键索引：主键上自动添加索引
   唯一索引：有unique约束的字段上会自动添加索引
7. 索引什么时候失效？

select ename from emp where ename like '%A%';
模糊查询的时候，第一个通配符使用的是%，这个时候索引是失效的

视图 view

站在不同的角度去看待数据（同一张表，通过不同的角度去看待）

创建视图 create view myview as select empno,ename from emp;
删除视图 drop view myview;

对视图进行增删改查，会影响到原表数据。（通过视图影响原表的数据的，不是直接操作的原表）
只有DQL语句才能以视图对象的方式创建出来
视图的作用

• 视图可以隐藏表的实现细节
• 保密级别较高的系统，数据库只对外提供相关的视图，java程序员只对视图对象进行CRUD

DBA命令

将数据库中的数据导出
  mysqldump 数据库名>E:\test.sql -uroot -proot  （DOS命令）
导出数据库中的指定表
  mysqldump 数据库名 emp>E:\test.sql -uroot -proot
导入数据
  create database 数据库名;
  user 数据库名;
  source E:\test.sql   （source，文件拖进来）

数据库设计三范式

设计范式：设计表的依据，按照三范式设计的表不会出现数据冗余
三范式：

• 第一范式：任何一张表都应该有主键，并且每一个字段原子性不可再分
• 第二范式：所有非主键字段完全依赖主键，不能产生部分依赖
• 第三范式：所有非主键字段直接依赖主键，不能产生传递依赖
  口诀：
  多对多，三张表，关系表两个外键
  一对多，两张表，多的表加外键

在实际的开发中，以满足客户的需求为主，有的时候会拿冗余换执行速度

rank函数的三种用法

• rank() over(业务逻辑)

select name,score, rank() over(order by score desc) 'rank' from student
作用：查出指定条件后的进行排名，条件相同排名相同，排名间断不连续。
说明：例如学生排名，使用这个函数，成绩相同的两名是并列，
	下一位同学空出所占的名次。即：1 1 3 4 5 5 7

• dense_rank() over(业务逻辑)

select name,score, dense_rank() over(order by score desc) 'rank' 
作用：查出指定条件后的进行排名，条件相同排名相同，排名间断不连续。
说明：和rank() over 的作用相同，区别在于dense_rank() over 排名是密集连续的。
	例如学生排名，使用这个函数，成绩相同的两名是并列，下一位同学接着下一个名次。即：1 1 2 3 4 5 5 6

• row_number() over(业务逻辑)

select name,score, row_number() over(order by score desc) 'rank' 
作用：查出指定条件后的进行排名，条件相同排名也不相同，排名间断不连续。
说明：这个函数不需要考虑是否并列，即使根据条件查询出来的数值相同也会进行连续排序。
	即：1 2 3 4 5 6

关于面试中遇到的问题

• 面试官说一下你的项目干啥了
  答：我的这个项目有某模块..我负责哪些模块，这个模块用到了哪些技术，当时我在做这个的时候遇到了哪些困难,这个困难是如何解决的
• 你项目的哪一块用到索引了
  答：当时的业务是怎么回事，为什么要考虑用索引，用了索引项目的效率有什么提升

MySQL试题

1.取得每个部门最高薪水的人员名称
select
  e.ename,t.deptno,t.maxsal
from
  (select
    deptno,max(sal) maxsal
  from 
    emp 
  group by 
    deptno) t
join
  emp e
on
  t.maxsal=e.sal and t.deptno=e.deptno;  
   
2.哪些人的薪水在部门的平均薪水之上
  select
    e.ename,e.sal,t.*
  from
    (select
       deptno,avg(sal) avgsal
     from
       emp e
     group by
       deptno) t
   join
     emp e
   on
     e.sal>avgsal and e.deptno = t.deptno;
   
3.取得部门中所有人的平均的薪水等级
  select
    e.deptno,avg(s.grade)
  from
    emp e
  join
    salgrade s
  on
    e.sal betweeen s.losal and s.hisal
  group by
    deptno;
   
4.不用max函数，取得最高薪水
  select
    sal
  from
    emp
  order by
    sal desc
  limit
    0,1
   
5.取得平均薪水最高的部门的部门编号
  select
    deptno,avg(sal) avgsal,
  from
    emp
  group by
    deptno
  order by
    avgsal desc
  limit
    1;
   
6.取得平均薪水最高的部门的部门名称
  select
    d.dname,avg(e.sal) avgsal
  from
    emp e
  join
    dept d
  on
    d.deptno = e.deptno
  group by
    d.dname
  order by
    avgsal desc
  limit
	1;