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; on也可以换位using()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 > 20002.UNION ALL
并集，所有的内容都显示，包括重复的
事务
- 存储引擎
Mysql的核心就是存储引擎，DBMS借助于引擎实现增删改查操作。
Mysql有多种存储引擎，不同引擎在事务的支持，查询的效率，索引策略等方面有不同。
InnoDB是事务型数据库的首选，执行安全性数据库，行锁定和外键。mysql5.5之后默认使用。
MyISAM插入速度和查询效率较高，但不支持事务。
MEMORY将表中的数据存储在内存中，速度较快。
- 什么是事务
事务指逻辑上的一组操作，组成这组操作的各个单元，要么全成功，要么全不成功。
- 事务的ACID特性
原子性：指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
一致性：事务必须使数据库从一个一致性状态变换到另外一个一致性状态。转账前和转账后的总金额不变。
隔离性：事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。
持久性：指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响。
- 事务的实现 tcl commit rollback
查看事务 show variables like '%autocommit%'
a)mysql数据库默认是自动提交
set autocommit=0; 不自动提交
set autocomiit=1;自动提交
b)手动开启事务
start transaction/begin;
c)手动提交或则回滚
commit;
rollback;
savepoint;保存点，恢复必须在事务提交之前。事务一旦提交，所有的保存点全部失效。
注意：DDL操作会隐式事务提交
-- 关闭自动提交 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;
- JDBC如何控制事务
- 事物的隔离级别
赃读：指一个事务读取了另一个事务未提交的数据。
不可重复读：在一个事务内读取表中的某一行数据，多次读取结果不同。一个事务读取到了另一个事务提交后的数据。（update）
虚读（幻读）：是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。 (insert)
数据库通过设置事务的隔离级别防止以上情况的发生：
* 1、READ UNCOMMITTED: 赃读、不可重复读、虚读都有可能发生。
* 2、READ COMMITTED: 避免赃读。不可重复读、虚读都有可能发生。（oracle默认的）
* 4、REPEATABLE READ:避免赃读、不可重复读。虚读有可能发生。（mysql默认）
* 8、SERIALIZABLE: 避免赃读、不可重复读、虚读。
级别越高，性能越低，数据越安全
mysql中：
查看当前的事务隔离级别：SELECT @@TX_ISOLATION;
更改当前的事务隔离级别：SET TRANSACTION ISOLATION LEVEL 四个级别之一。
设置隔离级别必须在事务之前
- JDBC控制事务的隔离级别
Connection接口：
设置隔离级别：必须在开启事务之前。
Connection.setTransactionIsolation(int level);
存储程序
- 概念：存储程序指的一组存储和执行在数据库服务器端的程序。
- 分类 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 存储过程可以单独使用；但是函数只能作为语句的一部分。
273
评论 1
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
查看更多评论
添加红包
