mysql03_(2)

1. 高级查询

a) 关联查询(连接查询)

1. 内连接

select ename,dname 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);

 通用列字段名称必须一致;去除重复字段

关联表中都出现的字段值最终才能出现在结果集中；

    内连接与连接顺序无关，没有主从表之分。

1. 外连接

有主从表之分，与连接顺序有关。

依次遍历主表中记录，与从表中记录进行匹配；如果匹配到则连接展示，否则以null填充。

1. 左外连接 left/right [outer] join …on

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

1. 自然连接(自然连接肯定是等值连接，但等值连接不一定是自然连接)

select * from emp NATURAL join dept;

 

1. 自连接

b) 子查询(嵌套查询)

嵌套查询,将一个查询结果当做另一个查询的条件或者结果集。

子查询最接近思考方式，最自然的查询。

1. 分类

a) 单行子查询:子查询的返回结果只有一条记录。

select * from emp where sal > (select sal from emp where ename = 'scott');

b) 多行子查询:子查询返回结果有多条记录

select * from emp where sal in ( select distinct sal from emp where deptno = 20) and deptno <>20;

any  all

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

>all:大于最大值   <all小于最小值

 

c) 联合查询(索引:or导致索引失效)

union/union all

union:去重

联合的结果集必须一致。

1. 事务

a) 存储引擎

数据库底层软件组织,dbms通过存储引擎实现对数据库的操作,mysql核心就是存储引擎。

 mysql中可以设置多种存储引擎，不同的存储引擎在索引，存储，以及索策略上是不同的。

  mysql 5.5之前,采用myisam存储引擎,支持全文搜索,不支持事务。

  mysql 5.5之后，默认采用innodb存储引擎,支持事务以及行级锁。

 

b) 什么是事务

事务保证数据一致性，一组DML操作要么同时成功，要么同时失败.

例如:转账

1. 事务的acid特性
   1. 原子性: 放在同一事务中的一组操作是不可分割的。
   2. 一致性: 事务执行前后整体的状态保持不变。
   3. 隔离性: 并发事务之间互相不能干扰
   4. 持久性: 事务执行之后将永久化到数据库。
   5. 事务语法(数据库中)

mysql数据库默认采用自动事务提交。

# 查看mysql的事务自动提交show variables like 'autocommit';

 

#修改自动提交

set autocommit = 0;

 

#显式开启事务(begin)

start transaction;  

 

#在同一个事务

update account set money = money + 100 where name = 'zs';

update account set money = money - 100 where name = 'ls';

 

#手动提交或回滚事务

#commit;

rollback;

 

1. 并发事务产生问题

脏读:

   一个事务执行范围内读到了另一事务未提交的数据。

不可重复读:

一个事务在只读范围内,被另一事务修改并提交事务，导致多次读取事务不一致的问题。

幻读(虚读)::

一个事务只读范围内,被另一事务删除或者添加数据，导致读取数据不一致问题。

1. 事务隔离级别

读未提交: 不能处理任何问题

读已提交: 解决脏读问题

可重复读:解决脏读和不可重复读问题

串行化:解决所有问题

1. 存储程序

a) 运行于服务器端程序。

b) 优点

1. 简化开发
2. 执行效率较高

c) 缺点

1. 程序保存在服务器端,占用服务器资源
2. 数据迁移
3. 调试编写程序不方便

d) 分类

1. 存储过程

 

#创建

delimiter //;create procedure sel_emp()

begin

 select dname,ename from emp,dept where dept.deptno=emp.deptno;

end;

 #调用

call sel_emp();

 

参数的模式:

  In:传入模式  默认

  Out:传出模式

  Inout:传入传出模式

 

#根据员工姓名查询员工职位

delimiter //;

create procedure findJob(inout name_job varchar(20))

begin

  select job into name_job from emp where ename = name_job;

end;

set @name_job='smith';

call findJob(@name_job);

select @name_job;

 

if语法:

delimiter //;

create procedure score_level(score int)begin

  #变量声明

  declare v_level varchar(20);

  if score >= 80 then

  #变量赋值

  set v_level='A';

elseif score>=60 then

    set v_level='B';

else

    set v_level='C';

end if;

select v_level;

end;

 

 

#循环(1+2+...+100)

delimiter //;

create procedure calc()

begin

  declare i int;

declare sum int;

set i=1;

set sum=0;

 

while i<=100 do

  set sum = sum + i;

set i = i + 1;

end while;

 

  select sum;

end;

 

call calc();

 

 

delimiter //;

create procedure calc1()

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>100 then

  leave lip;

end if;

end loop;

  select sum;

end;

 

call calc1();

 

 

 

delimiter //;

create procedure calc2()

begin

  declare i int;

declare sum int;

set i=1;

set sum=0;

 

repeat

  set sum = sum + i;

set  i = i + 1;

  until i > 100

end repeat;

 

  select sum;

end;

 

call calc2();

 

1. 存储函数

 

#存储函数(函数) #存储在服务器端,有返回值,函数作为sql一部分使用。

 #根据用户编号查询姓名

 

delimiter //;

create function findNameByNo(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 findNameByNo(7788);

函数和存储过程区别:

 关键字不同

 存储过程三种参数模式实现数据输入输出;函数有返回值返回数据；

 存储过程可以作为独立个体执行,函数只能作为sql的一部分执行。

 

1. 触发器