投稿
  1. 云图网首页
  2. 技术专区
  3. 数据库

Day3 MySql高级查询详解数据库

• 数据库

DQL高级查询

多表查询（关联查询、连接查询）

1.笛卡尔积

emp表15条记录,dept表4条记录。

连接查询的笛卡尔积为60条记录。

2.内连接

不区分主从表，与连接顺序无关。两张表均满足条件则出现结果集中。

--where子句 
select * from emp,dept 
where emp.deptno = dept.deptno 
--inner join…on… 
select * from emp 
inner join dept 
on emp.deptno = dept.deptno 
--inner join…using… 
select * from emp 
INNER JOIN dept 
using(deptno)

3.自然连接

寻找两表中字段名称相等的字段进行连接，会自动去重重复列。

--自然连接(等值连接,表的字段名称必须相同,去除重复行) 
select * from emp NATURAL JOIN dept;

4.外连接

有主从表之分，与连接顺序有关。以驱动表为依据，匹配表依次进行查询；匹配表中找不到数据，则以null填充。

--左外连接 left [outer] join  .... ON... 
select * from emp 
 left join dept 
 on emp.deptno = dept.deptno;

5.自连接

同一个表里面的数据相关联

--查询所有的员工的姓名和上级领导的姓名  emp(内连接) 
select e1.ename ename ,e2.ename mgrname from emp e1,emp e2 
where e1.mgr=e2.empno 
 
select e1.ename ename ,e2.ename mgrname from emp e1 
left join emp e2 
on e1.mgr=e2.empno

子查询（嵌套查询）

1.单行子查询

子查询的结果返回一行

select dname from dept where deptno = (select deptno from emp where empno=7788);

2.多行子查询

查询的结果返回一个集合

--查询工资大于2000的员工的部门名称 
  select dname from dept where deptno =any(select deptno from emp where sal > 2000); 
  ANY   ALL 
  =ANY 含义就是in   >any  大于最小值   <any  小于最大值 
  >all 大于最大值    <all 小于最小值

案例

--查询大于所在部门的平均工资的员工信息。 
  --关联查询 
   1.分组每个部门平均工资 
   select * from emp e,(select deptno,avg(sal) avg from emp group by deptno) e1 
   where e.deptno = e1.deptno and e.sal > e1.avg 
  --子查询(主查询可以将数据传递给子查询) 
   select * from emp e where sal > (select avg(sal) from emp e1 where e1.deptno = e.deptno) 
   1.先执行主查询,将deptno传给子查询 
   2.子查询拿到deptno，计算查询的结果,返回给主查询 
   3.主查询拿到子查询返回的结果执行查询 
 
--查询薪水大于2000  emp 的部门名称  dept 
 
   select dname from dept where deptno in( 
   select deptno from emp where sal > 2000); 
    
   select dname from dept d where EXISTS( 
   select * from emp e where sal > 2000 and d.deptno = e.deptno)

in和exists的区别

1.IN 
     主查询的条件字段和子查询返回字段必须一致。 
     先执行子查询，将返回子查询的结果给主查询，再执行主查询 
2.EXISTS 
     主查询不需要出现条件字段 
     先执行主查询，将主查询的表的字段传给子查询，如果在子查询找到相应结果， 
     返回true,将该结果显示在结果集中。否则返回false

联合查询

1.UNION

并集，所有的内容都查询，重复的显示一次

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

2.UNION ALL

并集，所有的内容都显示，包括重复的

事物

存储引擎

Mysql的核心就是存储引擎，DBMS借助于引擎实现增删改查操作。

Mysql有多种存储引擎，不同引擎在事务的支持，查询的效率，索引策略等方面有不同。

InnoDB是事务型数据库的首选，执行安全性数据库，行锁定和外键。mysql5.5之后默认使用。

MyISAM插入速度和查询效率较高，但不支持事务。

MEMORY将表中的数据存储在内存中，速度较快。

Day3 MySql高级查询详解数据库

什么是事物

保证数据的一致性，一系列DML操作，要么同时成功，要么同时失败。

事物的ACID特性

a)  原子性Atomicity: 一系列的DML操作不可分割。

b)  一致性Consistency:

　　　　数据一致性:事务执行前后整体的状态不变。

　　　　约束:事务执行前后约束信息不变。

c)  隔离性(独立性) Isolation:

　　　　并发事务是互相隔离的。

d)  持久性Durability:事务提交之后数据将持久化到数据库。

事物的实现  tcl   commit   rollback

a) mysql数据库默认是自动提交

set autocommit=0; 不自动提交

set autocomiit=1;自动提交

b) 手动开启事务

start transaction/begin;

c) 手动提交或者回滚

commit;

rollback;

savepoint;保存点，恢复必须在事务提交之前。事务一旦提交，所有的保存点全部失效。

-- 关闭自动提交 
set autocommit=0; 
-- 显式开始事务 
start TRANSACTION;  
-- DML 
update account set money = money - 20 where name = 'ls'; 
-- 保存点 
-- SAVEPOINT a; 
delete from aa; 
update account set money = money + 20 where name = 'zs'; 
-- 提交 
-- commit; 
-- 回滚 
commit; 
-- 不起效 
-- ROLLBACK to a;

注意：DDL操作会隐式事物提交

存储程序

概念

存储程序指的一组存储和执行在数据库服务器端的程序。

分类

1.存储过程

2.存储函数

3.触发器

存储过程

1.基本语法

CREATE PROCEDURE sel_emp(参数列表) 
    BEGIN 
     --操作 
  END;

2.使用

无参的存储过程: 
CREATE PROCEDURE sel_emp() 
    BEGIN 
     select * from emp where deptno = 10; 
  END; 
--存储过程的调用 
call sel_emp(); 
 
有参的存储过程: 
--根据部门编号查询员工信息 
  CREATE PROCEDURE sel_emp2(dno int) 
  BEGIN 
    select * from emp where deptno=dno; 
  END; 
 --调用存储过程 
  call sel_emp2(30); 
 
--根据员工编号查询员工的名称 
  CREATE PROCEDURE sel_emp3(eno int,OUT name varchar(20)) 
  BEGIN 
    select ename into name from emp where empno=eno; 
  end; 
  --调用 
  call sel_emp3(7788,@name); 
  select @name; 
 
--根据员工编号查询所在部门的编号 
  CREATE PROCEDURE sel_emp4(INOUT eno int) 
  BEGIN 
    select deptno into eno from emp where empno = eno; 
  END; 
     
  --调用 
  set @eno = 7788; 
  call sel_emp4(@eno); 
  select @eno; 
 
--分支语句 
CREATE PROCEDURE cal_score(score int) 
        BEGIN 
        -- 声明变量 
        DECLARE levels varchar(20); 
        -- 分支 
                IF score >= 90 THEN 
          -- 赋值 
          set levels = '优秀'; 
        ELSEIF score >= 80  THEN 
          set levels = '良好'; 
        ELSE 
          set levels = '不通过'; 
        END IF; 
        -- 输出 
        select levels; 
END; 
 
--while循环 
create PROCEDURE calc() 
  BEGIN 
    -- 声明两个变量 
    DECLARE sum int; 
    DECLARE count int; 
    -- 初始化 
    set sum = 0; 
    set count = 1; 
    -- 循环 
    while count <=100 DO 
      set sum = sum + count; 
      set count = count + 1; 
    END WHILE; 
 
    SELECT sum; 
  END; 
 
  --LOOP 
  create PROCEDURE calc1() 
  BEGIN 
    -- 声明两个变量 
    DECLARE sum int; 
    DECLARE count int; 
    -- 初始化 
    set sum = 0; 
    set count = 1; 
    -- 循环 
   lip:LOOP 
      set sum = sum + count; 
      set count = count + 1; 
      IF count > 100 THEN 
        LEAVE lip; 
      END IF; 
   END LOOP; 
    SELECT sum; 
  END; 
     
call calc1(); 
 
create PROCEDURE calc3() 
  BEGIN 
    -- 声明两个变量 
    DECLARE sum int; 
    DECLARE count int; 
    -- 初始化 
    set sum = 0; 
    set count = 1; 
    -- 循环 
   REPEAT 
      set sum = sum + count; 
      set count = count + 1; 
      UNTIL count > 100 
   END REPEAT; 
    SELECT sum; 
  END;

3.参数模式

in:外部传进存储过程

out:传出

inout:传进传出

4.游标

--查询所有员工的姓名 
  create PROCEDURE emp_cursor4() 
  BEGIN 
    DECLARE name varchar(20); 
    DECLARE DONE boolean default 0; 
    -- 声明游标类型变量存储所有员工的名称 
    DECLARE emp_cursor CURSOR for select ename from emp;   
    -- 结束设置状态码为1   
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET DONE = 1;   
    -- 打开游标 
    open emp_cursor; 
    -- 获取游标中维护的值 
    lip:LOOP 
      FETCH emp_cursor into name; 
      IF DONE THEN 
        leave lip; 
      END IF; 
      select name; 
    END LOOP; 
    -- 关闭游标 
    close emp_cursor; 
  END;

存储函数

--函数(确定的不变的 DETERMINISTIC  Not ) 
  create FUNCTION emp_func() 
  RETURNS VARCHAR(20) 
DETERMINISTIC 
  BEGIN 
     DECLARE name varchar(20); 
     select ename into name from emp where empno = 7788; 
     RETURN name; 
  END; 
 
  select emp_func(); 
 
函数有返回值  return 
存储过程可以单独使用；但是函数只能作为语句的一部分。

 

原创文章，作者：ItWorker，如若转载，请注明出处：https://blog.ytso.com/tech/database/6668.html

赞 (0)
0 0
上一篇 2021年7月17日 01:27
下一篇 2021年7月17日 01:27

相关推荐

发表回复

登录后才能评论