【图文解析 】MySQL 查询及存储

MySQL的查询存储

**MySQL查询**
1.高级查询
1)多表查询(关联查询，连接查询)
a)内连接
没有主从表之分。
与连接顺序无关
内连接出现在结果集中的数据必须出现在每一个关联表中。   

select * from emp,dept where emp.deptno = dept.deptno;   

    select * from emp
        inner join dept
        on emp.deptno = dept.deptno;
    select * from emp
    inner join dept
    using (deptno);   

不通用，必须通用列字段名称一样，会去除重复列。
b)外连接(左外连接 右外连接)
          有主从表之分，与连接顺序有关。
以主表为基准,依次在从表中查找与主表记录相关联记录，如果找到则关联并显示，否则以null填充。

   

 select * from emp
    left/right join dept
    on emp.deptno = dept.deptno;

2)子查询(嵌套查询)
一次查询的结果作为另一查询的条件或者结果集，称为子查询。
1.单行子查询
子查询返回结果一条记录

   

 select * from dept where deptno = (select deptno from emp where empno=7369);

2.多行子查询
子查询返回结果多条记录

   

select * from dept where deptno in (
       select deptno from emp where sal > 2000);
    any/all

=any: 相当于in      >any:大于最小值    <any:小于最大值
<>all: 相当于not in   >all:大于最大值   <all:小于最小值

查询超过所在部门的平均工资的员工信息。
  关联
      

    select * from emp,
            (select deptno,avg(sal) avg from emp group by deptno) e
            where e.deptno = emp.deptno and sal > e.avg;

    #子查询
      #1.主查询将deptno传给子查询
        #2.子查询根据主查询传的部门编号查询该部门的平均工资
        #3.子查询将结果返回给主查询，主查询执行。

         select * from emp e1 where sal >(
       select avg(sal) avg from emp e2 where e2.deptno = e1.deptno);

查询工资>2000的员工所在部门的名称
  

 

   select dname from dept where deptno in
      (select deptno from emp where sal > 2000);

    
  

  #exists
    select dname from dept where exists(
    select * from emp where sal > 2000 and dept.deptno = emp.deptno);

    
    exits和in对比
      in：先执行子查询，将子查询结果返回给主查询,主查询根据返回的结果后续处理。
exits：先执行主查询,子查询根据主查询传输的数据，依次在子查询中匹配,如果能够找到与主查询相匹配的记录，则返回true,显示主查询的结果;如果不能匹配，则返回false，则主查询该记录不会显示在结果集中。

3)联合查询 union 去重、union all 不去重

    select * from emp where deptno =20 
    union all
    select * from emp where sal >2000;

**2.事务**
1)存储引擎
Mysql核心存储引擎。
Mysql5.5 默认采用innoDB。(my.ini)

2)什么是事务
事务用于保证数据的一致性，由一组DML操作组成，该组SQL语句要么同时成功，要么同时失败。例如转账。
3)事务的四大特性(ACID)
原子性: dml作为整体不可分割。
一致性: 事务执行前后整体的状态不变。
隔离性: 并发事务之间互相独立，不能相互干扰。
持久性: 事务执行完毕，则数据将持久化到数据库。
4)并发事务产生的问题
a)脏读:   一个事务读到另一个事务未提交的数据。
b)不可重复读:   在一个事务t1执行过程中,由另一个事务t2对该数据进行修改并且提交了事务；t1再次进行处理时发现数据已经改变。
c)幻读(虚读):   在一个事务t1的执行过程中,另一个事务t2对该数据进行增加删除操作并且提交事务;t1再次读取时发现数据不一致。
5)事务隔离级别
读未提交: 无法解决问题
读已提交: 解决脏读问题。    Oracle
可重复读: 解决脏读和不可重复读问题。  Mysql
串行化: 解决所有问题。
6)数据库中事务语法

   

 set autocommit=0;
    start TRANSACTION;
    update account set money=money-100 where aid = 1;
    update account set money=money+100 where aid = 2;
    #commit;
    rollback;

**存储程序**
1)什么是存储程序
存储程序指的一组存储和执行在数据库服务器端的程序。存储程序总是在服务器的进程或者线程的内存中执行的。
优点:
1.简化sql开发
2.效率提高
        缺点:
1.数据迁移麻烦
2.占用服务器资源
3.对数据库影响较大
2)[存储]过程
有输入和输出参数,存储一组sql操作,独立调用。

 

       #声明
       

delimiter //;
        create procedure pro_emp()
        begin
          select * from emp;
        end;
        //

    
        #调用
       

call pro_emp();
        
        delimiter //;
        create procedure pro_emp_no(eno int)
        begin
          select ename from emp where empno=eno;
        end;
    
        call pro_emp_no(7369);

    
    参数三种模式:
      IN:输入模式(默认)
      OUT:输出模式
      INOUT:输入输出模式
    
       

 elimiter //;
        create procedure sel_emp(eno int,out en varchar(20))
        begin
           select ename into en from emp where empno = eno;
        end;

    
    调用
    
       

 call sel_emp(7788,@name);
        select @name;

    
    根据员工姓名查询职位
    
     

  delimiter //;
        create procedure sel_emp2(inout en_job varchar(20))
        begin
          select job into en_job from emp where ename = en_job;
        end;

    
    调用
    
       

 set @name_job='scott';
        call sel_emp2(@name_job);
        select @name_job;

    
    分支:  if
     
    
       

  delimiter //;
        create procedure score(score int,out level varchar(20))
        begin
         if score>=80 then
           set level='A';
         elseif score>=60 then
           set level ='B';
         else
           set level = 'C';
         end if;     
        end;

    
    循环:
    
       

 #while  1+2+...+10
         delimiter //;
         create procedure calc1()
         begin
         #声明变量
         declare i int;
         declare sum int;

         #赋值
        

set i=1;
         set sum=0;

         #循环
        

 while i<=10 do
           set sum=sum+i;
             set i=i+1;
         end while;
         select sum;
         end;
        
            call calc1();
        
            loop
            delimiter //;
             create procedure calc2()
             begin

             声明变量
            

declare i int;
             declare sum int;

             赋值
            

set i=1;
             set sum=0;

             循环
            

lip:loop
               set sum = sum + i;
                 set i = i+1;
                 if i>10 then
                   leave lip;
                 end if;      
             end loop;
             select sum;
         end;
        
        call calc2();
    
        repeat
        delimiter //;
         create procedure calc3()
         begin

             声明变量
            

 declare i int;
             declare sum int;

             赋值
             
    
 

       set i=1;
             set sum=0;

         循环
        
    
       

 repeat
               set sum = sum + i;
                 set i = i+1;
                 until i>10
             end repeat;
             select sum;
         end;
        call calc3();

3)[存储]函数
有返回值。

   

 delimiter //;
    create function sel_name(eno int)
    returns varchar(20)  #返回值类型
    DETERMINISTIC #确定的
    begin
      declare v_name varchar(20); 
      select ename into v_name from emp where empno = eno;
        return v_name;
    end;
    
    select sel_name(7788);

4)触发器
由事件驱动(不能手动调用)