3.数据库高级

目录:

• 1、模糊查询

• 2、自身连接 自然连接 交叉连接

• 3、子查询

• 4、视图 索引

---

一.模糊查询

_ 下划线 代表任意一个字符
% 代表任意个字符

语法:

select * from emp where ename  like '谷%'
select * from emp where ename like'张%'
select * from emp where ename like'_树%'
select * from emp where ename like '%t%'
select * from emp where ename like '%t'

PS:补充之前的:

-- com不等于空的行
select * from emp where com is not null or deptno='20' order by sal desc

---

二.多表连接查询

等值连接：把两个表中都符合连接条件的数据给查询出来(常用)
内部连接：把两个表中都符合连接条件的数据给查询出来(常用)
左外连接： 以左表为主，把坐标的数据都会查询出，那么右表按条件进行连接，无连接数据的使用null(常用)
右外连接：同理(常用)
全外连接：左外联+右外联 mysql不支持
非等值连接：
自然连接：根据进行连接的两个表中的名字相同的列自动进行连接. 要求：自动连接的列的数据类型、长度要一致

1.自然连接

select ename,dname
from emp
natural join dept 

2.自身连接(表和自身进行连接)

等值连接方式

-- 员工编号  员工姓名  领导姓名
select e.empno as 员工编号,
       e.ename as 员工姓名,
       m.ename as 领导姓名
from emp as e,emp as m
where e.mgrid=m.empno

左外连接方式

select e.empno as 员工编号,
       e.ename as 员工姓名,
       m.ename as 领导姓名
from emp as e
left join emp as m on e.mgrid=m.empno

3.交叉连接：会产生笛卡尔积

SELECT * from emp 
CROSS JOIN dept

等同于

 select * from emp,dept

---

三.子查询(子查询效率比表连接慢)

在一个操作（select,update,delete,insert）的基础上，先进行的查询, 称之为子查询
特点：子查询要先执行

案例:

• 查询比许洪宝的薪资高的员工

select * from emp 
where sal>(select sal from emp where ename='许洪宝')

• 查询比许洪宝薪资高 且和张弛是同一个部门的员工

select * from emp 
where 
sal>(select sal from emp where ename='许洪宝')
AND
deptno=(select deptno from emp where ename='张弛')

• 更新的值需要查询得到

update emp set 
sal=(select sal from emp where ename='许洪宝')
WHERE empno='1001'

• 更新的值通过子查询得到

update emp as e1
inner join
(select sal from emp where ename='崔宸') as e2
set e1.sal=e2.sal
where e1.empno='1001'

• 插入的值通过子查询得到

insert into emp(empno,ename,sal,job,joindate,deptno)
select '1080','谷丰',3000,'SE','2019-8-15',deptno
from emp where ename='scott'

---

在子查询中使用6个比较运算符的子查询称为简单子查询(>,>=,<,<=,=,<>)
要求：简单子查询的结果不能多于一条

如果子查询的结果多于一条，考虑使用复杂子查询

1、 in /not in (重要)

在范围里

select * from emp
where deptno in
(select deptno from emp where ename='scott' or ename='marry')

不在范围里

select * from emp
where deptno not in
(select deptno from emp where ename='scott' or ename='marry')

2、

any

• =any 相当于 in
• >any 大于子查询结果中的最小值
  查询比40部门最低薪资高的员工都有哪些
• <any 小于子查询结果中的最大值

案例:
比40部门最低薪资高的人:

select * from emp
where sal>any
(select sal from emp where deptno='40')

3、

all

• >all 大于子查询结果中的最大值
• <all 小于子查询结果中的最小值

案例:
高于40部门最高薪资的员工

select * from emp
where sal>all
(select sal from emp where deptno='40')

---

四.视图

存放在数据库中的一些编译好的Select语句

作用:

• 1、简化数据库操作
• 2、提高了运行效率（预编译）
• 3、提供了安全保障 为不同权限的人创建不同的视图

注意:

视图的select语句中不能包含子查询
视图不存放数据，数据依然存放在对应的表中
通过视图可以改变(insert update)表中的数据，不建议

案例:

查询：
比40部门平均薪资高的员工信息：编号 姓名 部门名称 薪资

select e.ename,sal,d.dname
from 
(SELECT * from emp 
where sal>
(SELECT avg(sal) from emp where deptno='40')) e
INNER JOIN dept d on e.deptno=d.deptno

创建视图:

CREATE view v_querysal
AS
select empno as 员工编号,ename as 员工姓名, sal as 薪资,dname as 部门名称
from emp 
inner join dept on emp.deptno=dept.deptno

通过视图改变表中的数据(不建议)

update v_empinfo4 set job='测试'
where empno='1007'

删掉视图

DROP view v_empinfo4

创建 或 替换 视图

CREATE or REPLACE  view v_empinfo4
AS
SELECT * from emp
where deptno='40'

---

五.索引

相当于字典中的目录，提升查询效率
当创建主键、唯一键后，系统默认按照主键或唯一键创建索引

语法:
create index 索引名 on 表名(列名)

   CREATE INDEX index_emp1902 on emp(ename)

索引的创建原则：

• 1、索引并不是越多越好，需要占用空间
• 2、在upate delete时，需要更多的时间来更新索引
• 3、当值的数量多于百分之五时，才考虑创建索引

---

六.函数

有些业务处理可以直接在数据库端直接执行即可

函数：存放在数据库中的代码块
好处：直接在数据库端执行，无需在Java程序和数据库之间传输SQL语句
提升效率，保证安全。

案例
编写无参数函数 统计40部门的员工数量

无参:

CREATE FUNCTION funCountNum()
RETURNS INT
BEGIN
  -- 声明变量
	DECLARE totalCount INT;
	-- 给变量赋值有两种方式
  -- 方式1： 赋固定的值
  -- set totalSal=10;
	-- 方式2：把查询到的值赋值给变量
  select COUNT(empno) INTO totalCount 
  from emp where deptno='40';
	-- 返回值
  RETURN totalCount;
END;

有参

CREATE FUNCTION  funCountNum2(dno VARCHAR(20))
RETURNS INT
BEGIN
	DECLARE totalCount int;
	SELECT COUNT(empno) into totalCount
  from emp
  where deptno=dno;
  return totalCount;
END;

---

如何获取自动生成的主键值?

1.select max(id) from 表
2.select last_insert_id() from 表