视图单行子查询返回mysql,Day3 MySql高级查询详解

DQL高级查询

多表查询(关联查询、连接查询)

1.笛卡尔积

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

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

2.内连接

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

--where子句

select * fromemp,deptwhere emp.deptno =dept.deptno--inner join…on…

select * fromempinner joindepton emp.deptno =dept.deptno--inner join…using…

select * fromempINNER JOINdept

using(deptno)

3.自然连接

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

--自然连接(等值连接,表的字段名称必须相同,去除重复行)

select * from emp NATURAL JOIN dept;

4.外连接

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

--左外连接 left [outer] join .... ON...

select * fromempleft joindepton emp.deptno = dept.deptno;

5.自连接

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

--查询所有的员工的姓名和上级领导的姓名 emp(内连接)

select e1.ename ename ,e2.ename mgrname fromemp e1,emp e2where e1.mgr=e2.empnoselect e1.ename ename ,e2.ename mgrname fromemp e1left joinemp e2on 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 大于最小值 all 大于最大值

案例

--查询大于所在部门的平均工资的员工信息。

--关联查询

1.分组每个部门平均工资select * from emp e,(select deptno,avg(sal) avg from emp group bydeptno) e1where 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 fromaa;update account set money = money + 20 where name = 'zs';--提交--commit;--回滚

commit;--不起效--ROLLBACK to a;

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

存储程序

概念

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

分类

1.存储过程

2.存储函数

3.触发器

存储过程

1.基本语法

CREATE PROCEDUREsel_emp(参数列表)BEGIN

--操作

END;

2.使用

无参的存储过程:CREATE PROCEDUREsel_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;--输出

selectlevels;END;--while循环

create PROCEDUREcalc()BEGIN

--声明两个变量

DECLARE sum int;DECLARE count int;--初始化

set sum = 0;set count = 1;--循环

while count <=100DOset sum = sum + count;set count = count + 1;END WHILE;SELECT sum;END;--LOOP

create PROCEDUREcalc1()BEGIN

--声明两个变量

DECLARE sum int;DECLARE count int;--初始化

set sum = 0;set count = 1;--循环

lip:LOOPset sum = sum + count;set count = count + 1;IF count > 100 THENLEAVE lip;END IF;ENDLOOP;SELECT sum;END;

call calc1();create PROCEDUREcalc3()BEGIN

--声明两个变量

DECLARE sum int;DECLARE count int;--初始化

set sum = 0;set count = 1;--循环

REPEATset sum = sum + count;set count = count + 1;

UNTILcount > 100

ENDREPEAT;SELECT sum;END;

3.参数模式

in:外部传进存储过程

out:传出

inout:传进传出

4.游标

--查询所有员工的姓名

create PROCEDUREemp_cursor4()BEGIN

DECLARE name varchar(20);DECLARE DONE boolean default 0;--声明游标类型变量存储所有员工的名称

DECLARE emp_cursor CURSOR for select ename fromemp;--结束设置状态码为1

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET DONE = 1;--打开游标

openemp_cursor;--获取游标中维护的值

lip:LOOPFETCH emp_cursor intoname;IF DONE THENleave lip;END IF;selectname;ENDLOOP;--关闭游标

closeemp_cursor;END;

存储函数

--函数(确定的不变的 DETERMINISTIC Not )

create FUNCTIONemp_func()RETURNS VARCHAR(20)

DETERMINISTICBEGIN

DECLARE name varchar(20);select ename into name from emp where empno = 7788;RETURNname;END;selectemp_func();

函数有返回值return存储过程可以单独使用；但是函数只能作为语句的一部分。