cgb2105-day04

Bertha 。 2021-09-11 02:28 167阅读 0赞

### 文章目录 ###

*   *  一，复合索引的练习
     *   *  \--1，测试
     *  二，视图
     *   *  \--1，概述
         *  \--2，测试
     *  三，多表联查
     *   *  \--1，概述
         *  \--2，方式
         *  \--3，笛卡尔积测试
         *  \--4，join测试
         *  \--5，子查询
     *  四，Oracle
     *   *  \--1，概述
         *  \--2，安装
     *  五，使用
     *   *  \--1，创建用户：文件--新建--用户
         *  \--2，创建表
         *  \--3，特殊语法

## 一，复合索引的练习 ##

### –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，创建用户：文件–新建–用户 ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTI5MzI4NzY_size_16_color_FFFFFF_t_70]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTI5MzI4NzY_size_16_color_FFFFFF_t_70 1]  
![在这里插入图片描述][20210701165409486.png]

### –2，创建表 ###

![在这里插入图片描述][20210701171231344.png]

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTI5MzI4NzY_size_16_color_FFFFFF_t_70 2]

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTI5MzI4NzY_size_16_color_FFFFFF_t_70 3]

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTI5MzI4NzY_size_16_color_FFFFFF_t_70 4]  
![在这里插入图片描述][20210701171308142.png]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTI5MzI4NzY_size_16_color_FFFFFF_t_70 5]

### –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

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTI5MzI4NzY_size_16_color_FFFFFF_t_70]: /images/20210911/274fbf65bd0b4823bae4e77cf5ff8f01.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTI5MzI4NzY_size_16_color_FFFFFF_t_70 1]: /images/20210911/41724695cc7c40b2ac9427c178e8c21d.png
[20210701165409486.png]: /images/20210911/4a98fbb4abcc4a8d87aebc7416efbdcd.png
[20210701171231344.png]: /images/20210911/383a551d5447407a82a48b5b1d0fea3a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTI5MzI4NzY_size_16_color_FFFFFF_t_70 2]: /images/20210911/ff0bc53b84fa4844bad9d97aa4d4d4b7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTI5MzI4NzY_size_16_color_FFFFFF_t_70 3]: /images/20210911/110e91abecd74bccba45f8d3a7895f0b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTI5MzI4NzY_size_16_color_FFFFFF_t_70 4]: /images/20210911/8053607989dd4d73974159be00a0e576.png
[20210701171308142.png]: /images/20210911/2b98d763ed634e9daa83dae46d233f54.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTI5MzI4NzY_size_16_color_FFFFFF_t_70 5]: /images/20210911/ccfe01154fdb4e2c8b5f5097eb3421f6.png