cgb2105-day04

一，复合索引的练习

–1，测试

select * from emp
show index from emp
alter table emp add index fuhe_index(job,ename) #添加复合索引
explain
select * from emp where job='员工' and ename='jack' #索引生效
explain
select * from emp where ename='jack' and job='员工'  #索引生效
explain
select * from emp where job ='员工'  #索引生效
explain
select * from emp where ename='jack'  #不匹配最左特性，索引失效
explain
select * from emp where job like '%员工%'  #模糊查询，索引失效
explain
select * from emp where job='员工' or ename='jack' #or条件查询，索引失效

二，视图

–1，概述

本质上就是缓存了 查询的结果，作为一个表体现

–2，测试

#视图：把一个查询语句的结果缓存起来存入视图中
#好处：提高查询效率
#坏处：占用了内存，无法进行SQL优化，当更新了数据时视图也需要时间更新
#1. 创建视图 create view 视图名 as SQL语句 
create view empView as 
select * from emp where ename like '%a%' #把SQL执行的结果存入视图

#2. 使用视图,就当做一张表来用
select * from empView

三，多表联查

–1，概述

是指联合查询多张表里的数据

–2，方式

笛卡尔积：通过逗号隔开多个表，写在from后面。通过where 连接表之间的关联关系
表连接：通过join连接多个表，分为内连接和外连接。通过on描述表关系，通过where添加过滤条件
子查询：把上一次的查询结果，作为条件继续使用

–3，笛卡尔积测试

#多表联查1：笛卡尔积，把多个表用逗号隔开 
#使用指定的字段deptno来描述两个表之间的关系
select * from dept , emp 
#表名.字段名
where dept.deptno=emp.deptno 
#要明确的表明使用哪个表里的哪个字段
and dept.deptno=1  

#使用别名
select * from dept a, emp b
where a.deptno=b.deptno 
and a.deptno=1 

#查询1号部门的员工姓名
select ename from dept a,emp b
where a.deptno=b.deptno#描述两个表的关系
and a.deptno=1 #查询1号部门的

#查询tony所在的办公地址
select b.loc from emp a , dept b
where a.deptno=b.deptno #描述表关系
and a.ename='tony' #业务条件

#查询accounting部门的员工信息
select b.* from dept a ,emp b
where a.deptno=b.deptno #描述表关系
and a.dname='accounting' #业务条件

#练习：查询二区员工的平均薪资
select avg(b.sal) from dept a ,emp b
where a.deptno=b.deptno #描述表关系
and a.loc='二区' #业务条件

–4，join测试

#多表联查2：表连接 join，把多个表用join连接
select * from dept join emp 
on dept.deptno=emp.deptno #描述两个表之间的关系

#查询Jack的部门名称
select a.dname from dept a join emp b
on a.deptno = b.deptno  #描述表间的关系
where b.ename='jack' #过滤条件

#查询二区办公的员工名字
select b.ename from dept a join emp b
on a.deptno = b.deptno  #描述表关系
where a.loc='二区' #业务条件

#查询二区里的最高薪
select max(b.sal) from dept a join emp b
on a.deptno = b.deptno  #描述表关系
where a.loc='二区' #业务条件

#查询二区部门的部门信息和员工信息
#join分为内连接(inner join)  外连接(left join/right join)
#inner join：取左右两个表中，都满足了条件的记录
#left join：取左表里的所有数据，右边中满足了条件的记录不满足的用null填充
#right join：取右表里的所有数据，左边中满足了条件的记录不满足的用null填充

#select * from dept a inner join emp b #取交集
#select * from dept a left join emp b #取左表的所有和右边满足条件的不满足条件都是null
select * from dept a right join emp b #取左表的所有和右边满足条件的不满足条件都是null
on a.deptno=b.deptno
where a.loc='二区'

–5，子查询

#列出research部门的所有员工的信息
#笛卡尔积
select b.* from dept a,emp b 
where a.deptno=b.deptno #表关系
and a.dname='research' #业务条件

#join连接
select b.* from dept a left join emp b 
on a.deptno=b.deptno #表关系
where a.dname='research' #业务条件

#子查询：把第一次的查询结果作为第二次查询的条件 
#练习1：列出research部门的所有员工的信息
#1.根据部门名称查部门编号
select deptno from dept where dname='research'  
#2.回员工表,根据查到的编号查员工信息
#select * from emp where deptno=2
select * from emp where deptno=(
	select deptno from dept where dname='research' )
#练习2：查询tony的部门信息
select * from dept where deptno=  #再查部门的信息
(select deptno from emp where ename='tony') #先查tony的部门
#练习3：查询二区员工的姓名
#select ename from emp where deptno in(2,3)#指编号可以是2或者3
select ename from emp where deptno in 
(select deptno from dept where loc='二区')

#练习4：查询高于平均工资的员工姓名
#select ename from emp where sal > avg(sal)#where里不能有聚合函数
select ename from emp where sal > (
 select avg(sal) from emp
)

四，Oracle

–1，概述

和MySQL一样，可以存储管理数据。Oracle是收费的，MySQL是免费的。

–2，安装

服务器端：简版 的软件，下一步的操作。注意：密码设置好。
客户端：PLSQL，连接服务器，操作服务器里的数据。

五，使用

MySQL：数据库—表----字段/记录
Oracle： 用户—表----字段/记录
登录PLSQL：使用system和安装的密码

–1，创建用户：文件–新建–用户

–2，创建表

–3，特殊语法

select t.*  from stu t where sclass='java'
--日期类型，默认格式是 日-月-年
insert into stu values('rose',0,'1/7月/2021','java',3)
--改成习惯的年-月-日
alter session set nls_date_format='yyyy-mm-dd'
--遵循新规则插入新数据
insert into stu values('tom',0,'2021-7-1','java',4)

--decode(1,2,3,4,5)-1是字段名 2是判断条件 3要执行的内容 4相当于2判断失败 5是要执行的内容
select t.*, decode(ssex,0,'男',1,'女')  from stu t