MYSQL数据库基础（五 子查询 事务 索引 视图 存储过程）

子查询：

  将一个查询语句的结果，运用到另一个查询语句中，这样的查询我们叫做子查询。

子查询的作用：

  1）我们可以将查询出来的结果当作一种条件，运用到另一种查询中去。当作删改的条件进行使用。

-- 如何查得所有比“CLARK”工资高的员工的信息
  --(1)先查询 CLARK的工资
   select sal from emp where ename='CLARK';  --查询结果为单行单列  2450
  --(2)查询薪水比2450高的员工信息
   select * from emp where sal>2450;
  --一个问题如何使用一条SQL语句完成  ，将(1)的查询结果作为（2）的条件使用
  select * from emp where sal>(select sal from emp where ename='CLARK');
-- 查询工资高于平均工资的雇员名字和工资。
  --(1)查询平均工资
    select avg(sal) from emp;
  --(2) 将(1)的查询结查作为条件使用
   select ename,sal from emp where sal>(select avg(sal) from emp);
--查询和SCOTT同一部门且比他工资低的雇员名字和工资。
 --(1)查询SCOTT所在的部门
  select deptno from emp where ename='SCOTT';
 -- (2)查询SCOTT的薪水 
  select sal from emp where ename='SCOTT';
 
  --(3) ,将(1)和(2)作为查询条件使用
  select ename,sal from emp where deptno=(select deptno from emp where ename='SCOTT')
  and sal<( select sal from emp where ename='SCOTT');

  2）子查询也可以当作修改的条件进行使用

 --将与‘SCOTT’所在的部门的员工的薪水都增加500
 --(1)SCOTT所在的部门
 select deptno from emp where ename='SCOTT';
 update emp set sal=sal+500 
 where deptno=(select deptno from emp where ename='SCOTT');

 3）在oracle数据库中，我们可以将子查询的值，当作新增的值加入到表中

 --(1)JACK今天入职到SCOTT部门
 insert into emp (empno,ename,hiredate,deptno) values 
 (9988,'JACK',NOW(),( select deptno from emp where ename='SCOTT'))

4）子查询的结果还能够当作一张表来进行使用

--列出每个部门的平均工资，最高工资，最低工资，部门总人数，部门编号和部门名称
  --(1)  查询结果是一张“虚拟表”
select deptno,floor(avg(sal)) as avgsal,max(sal) as masxal,min(sal)as minsal,count(*) as num from emp
where deptno is not null group by deptno;
-- (2)  将(1)查询的虚拟表，作为一张临时表使用
select d.deptno,dname,loc ,avgsal,masxal,minsal,num from dept d,
(select deptno,floor(avg(sal)) as avgsal,max(sal) as masxal,min(sal)as minsal,count(*) as num from emp
where deptno is not null group by deptno) 
t where d.deptno=t.deptno

多行查询中，我们可以用到几个关键词：

  1）any  任意一个     2）all    全部        3）in  等于之中的一个

-- 查询工资低于任何一个“CLERK”的工资的雇员信息。  (任何any)

   --(1)查询‘CLERK’的薪水
 select sal from emp where job='CLERK';  --多行单列
 
select * from emp where sal<any( select sal from emp where job='CLERK') 
order by sal;
select * from emp where sal<(select max(sal) from emp  where job='CLERK');

-- 查询工资比所有的“SALESMAN”都高的雇员的编号、名字和工资。 (所有all)
  --（1）查询所有销售的薪水
select sal from emp where job='SALESMAN';
  select empno,ename,sal from emp where sal>all(select sal from emp where job='SALESMAN');

-- 查询部门20中职务同部门10的雇员一样的雇员信息。  ( 同.. 一样  in)
 --(1) 查询10号部门的职位
select DISTINCT job  from emp where deptno=10;
select * from emp where deptno=20 
and job in(select DISTINCT job  from emp where deptno=10);

相关子查询/不相关子查询：

  不相关子查询 ：我们可以通过 括号里的查询条件能够直接允许sql语句，不受外部的影响。

  相关子查询： 依赖于整个的查询语句，括号里的语句不能单独运行。

 什么时候使用相关子查询语句：当外查询的条件和子查询的条件一致，把两个条件放到子查询之中写一个就好了。

--  --查询本部门最高工资的员工
 select max(sal) from emp where deptno=10;  --10号部门的最高工资
 select * from emp where deptno=10 and sal=(select max(sal) from emp where deptno=10);
 select max(sal) from emp where deptno=20;  --20号部门的最高工资
select * from emp where deptno=20 and sal=(select max(sal) from emp where deptno=20)
 select max(sal) from emp where deptno=30 ;--30号部门的最高工资
select * from emp where deptno=30 and sal=(select max(sal) from emp where deptno=30)


--这个表只有14条数据，还有一个没有部门，就剩13个员工了，就是用手去查，也能查出来13个员工
分别在哪个部门， 如果有10万条数据呢？你还能知道这10万个员工都被分配到了哪个部门呢？
select * from emp e1
 where sal=(select max(sal) from emp e2 where e1.deptno=e2.deptno);

索引：

索引创建的方式： 

   1）自动创建：在创建唯一约束时，会自动创建一个唯一索引。创建主键的时候也会创建一个索引。

create table student(
 stuid int(4) PRIMARY key auto_increment,
 stuname varchar(20),
 identifiy varchar(18)unique 
);

2）手动创建

-- 创建索引  create index   索引名称 on  表面（需要创建索引的字段）
create index  indddex  on  student(stuid);

-- 索引是自动使用的，当我们开始检索的时候，这个索引便能够自己使用了

-- 删除索引  alter table  表名 drop index  对应索引字段
alter table student drop index indddex;

索引有什么用？是索引越多查询越快吗？

    索引只是增加了对数据查找的效率，但是降低了增删改的的效率

     并且索引数量越多，磁盘空间占据的数量越大

     只有经常加where 中的条件列  或者经常排序的列需要增加索引

 

事务：

    事务概念：事务是指一个操作的序列，该序列中的多个操作序列要么都做，要么都不做，是一个不可分割的工作单位。

   事务的作用：保证数据库中数据的完整性和一致性
   事务的特性:
   (1)原子性 ：操作要么都执行，要么都不执行， 不允许再拆分
   (2)一致性:  数据库中的数据由一个一致状态，变到另外一个一致状态
             转账之前 A账户与B账户的余额相加一共为800,      A 账户 300   B账户500 
              转账之后A账户与B账户的余额相加一共为800,       A 账户 100   ，B账户700
  (3)隔离性：  一个事务看不到另外事务的操作
  (4)持久性：  一旦提交，就永久性的存储到磁盘

-- mysql添加手动事务
--格式： strat transaction
--        需要一起执行的条件
--        提交就  commit ； /  回滚就 rollback；
--例如微信转账 一方转账，一方的账户必须同时增加  有点像java中的线程

start transaction
update account set sal=sal-100 where id=1;
update account set sal=sal+100 where id=2;
--提交  /  回滚
commit ;
rollback;

    事务隔离级别：

-- 查看默认的事务隔离级别  MySQL默认的是repeatable read
select @@tx_isolation
-- 设置事务的隔离级别
set session transaction isolation level read uncommitted;
set session transaction isolation level read committed;
set session transaction isolation level repeatable read;
set session transaction isolation level serializable;

 

视图：

  视图是若干个表 和（或）其他表构造出来的表。

  视图中存放的是sql语句，而不是数据，我们在使用的时候调用的是视图中存放的sql语句，来进行数据的查询，而不是表中的数据。

  只有在用户需要使用的时候才会调用其中的数据，查询出来的是一张虚拟表。

 视图的作用：

 打个比方，工资是公司中比较敏感的信息，我们可以把整个员工信息包括工资做成一张表，为了方便同事联系，我们可以把姓名，住址、电话 展示给同事看，我们就可以把这几个信息做成视图， 发工资的时候，可以把姓名和工资给财务看，不包含其他的杂乱信息，就可以把这两项做成一个视图只给财务看，当然对外可以把其他的信息除去工资做成一张视图，当作基本信息表。

 

-- 创建视图
create view  view_mess
as
-- 限定表的查询权限 比如说view_mess视图 只展示了姓名，电话年龄等信息
select ename,tel,age  from message; 

--视图如何使用，基本表怎么使用，视图就怎么使用
select * from vi_emp; --视图是一个“虚表”
insert into emp (empno,ename,hiredate )values (8888,'mary',now());
--作用： 用于存储复杂的SQL语句
--查询每个部门的总人数，平均薪水，最高薪水，最低薪水，及部门名称
--(1)查询 
select deptno ,count(*) as num,floor(avg(sal)) as avgsal,max(sal) as maxsal,min(sal) as minsal
 from emp
where deptno is not null group by deptno;
 --(2)将(1)的查询作为一张表来使用
select t.*,d.dname from dept d,
(select deptno ,count(*) as num,floor(avg(sal)) as avgsal,max(sal) as maxsal,min(sal) as minsal
 from emp
where deptno is not null group by deptno)t where d.deptno=t.deptno
--将2的查询的SQL语句存储到视图 mysql中不支持，oracle支持
create view vi_emp3
as 
select t.*,d.dname from dept d,
(select deptno ,count(*) as num,floor(avg(sal)) as avgsal,max(sal) as maxsal,min(sal) as minsal
 from emp
where deptno is not null group by deptno)t where d.deptno=t.deptno

我们也可以对视图进行修改删除操作

select * from vi_emp;   --（1）把视图当作表来查询
insert into vi_emp values (7777,'rose',9900);  --(2)对视图进行插入数据，实际上就是对基本表进行insert
update vi_emp set sal=900 where empno=7777;  --(3)对视图中的数据的修改,............
delete from vi_emp where empno=7777;  --(3)对视图中的数据进行删除，。。。。。。。。。。。。。
select * from vi_emp4;
insert into vi_emp4 values (90,'财务部',5,2000,900,870);  --报错，执行不了，因为这个视图中的数据是通过统计得出，而且还是两张表

 

数据库对象：

就好比java中的方法，存储过程

主要是针对增删改统计的查询

 

为什么需要使用存储过程？

     数据库客户端的操作和得到的反馈是源于 客户端 和 服务端，倘若 有人在这两者之中进行“截断”，是不是就可以对我们的数据，或者数据库做一些操作，导致我们的数据库泄密？比如我们登录数据库 发送账号密码，这个时候如果发生了截断，那么我们的账号密码就会被窃取，或者 “坏人”还能够对我们发送的增加或者删除指令做出篡改，或者删库。很是可怕，所以使用存储过程，可以把我们发送的sql语句进行一个“包装”，就好像快递盒，完全不知道里面是什么东西。提升程序的安全性。

 

如何使用数据库对象？

dilimiter // -- 修改结束的标志
-- 格式 create procedure 方法名(out/in 变量名  类型)
create procedure myselect(out s int)  --创建一个方法myselect out是往外输出 s是变量 int类型 
begin -- 这就好比方法中的方括号
 select count(*) into s from student --将查询的结果封装到s中去
end --方法中的结束方括号
//

dilimiter;   -- 将标志修改回来
set @s=0;    --声明变量
call myselect(@s);   --调用“方法”
select @s;  --输出变量值


--新增的存储过程
delimiter //
create PROCEDURE myinsert ( in dno int,in ddname varchar(20) ,in dloc varchar(20) )
BEGIN
          insert into dept values (dno,ddname,dloc);
END 
//
delimiter ;
--调用新增的存储过程
call myinsert(90,'财务部','北京');
call myinsert(80,'财务部2','上海');
--修改的存储过程
delimiter //
create PROCEDURE myupdate ( in dno int ,in ddname varchar(20))
BEGIN
         update dept set dname=ddname where deptno=dno;
END 
//
delimiter ;
--调用修改的存储过程
call myupdate(80,'开发部');
--删除的存储过程
delimiter //
create PROCEDURE mydelete ( in dno int )
BEGIN
        delete from dept where deptno=dno;
END 
//
delimiter ;
--调用删除的存储过程
call mydelete(80);