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

柔情只为你懂 2021-11-14 12:08 303阅读 0赞

## 子查询： ##

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

**子查询的作用：**

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);