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将表中的数据存储在内存中，速度较快。

什么是事物

保证数据的一致性，一系列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/6668.html