码农进化之Oracle学习篇

亦凉 2022-06-09 06:15 172阅读 0赞

## 码农进化之Oracle学习篇 ##

1.oracle-c/s

C/S结构：  
client/server 客户端 服务器  
特点：客户端程序比较大。  
优点：服务器的压力比较小。很多计算都是在客户端本地计算的。运行客户端加载会比较快。客户端运行的效果比较好。有单独的客户端运行的程序。

大版本的更新可能需要重新安装客户端。

B/S结构：Browser/server 浏览器 服务器  
特点：客户端很小。服务器压力比较大。很多资源都需要及时的从服务器下载更新。

2.oracle概念

全局数据库名

唯一标识Oracle数据库的名称  
数据库实例名SID  
和操作系统交互，Oracle数据库的一个引用  
每个启动的数据库都对应一个数据库实例，由这个实例来访问和控制数据库  
为了运行数据库，Oracle系统所运行的所有进程和分配的内存结构的组合体

3.LAMP

业内的免费的黄金组合  
LAMP：  
L:  
Linux：服务器的操作系统  
A：  
apache：旗下有非常多的产品 tomcat  
M：  
mysql  
P：  
php：做web开发的。

4.常用命令

加锁解锁账户  
alter user scott account lock;  
alter user scott account unlock;

显示当前用户  
show user

清屏  
host cls

5.SQL-1

--最简单的sql语句
    select * from emp;
    select * from dept;
    select empno,ename,job,sal from emp;
    
    --使用算术运算符
    --需求：将员工的年薪求出
    select sal from emp;
    select ename ,sal from emp;
    select ename, sal*12 from emp;
    select ename,sal*12,comm from emp;
    select ename,sal*12 + comm from emp;--如果comm 是空 则当作0来处理
    
    
    --使用字段别名，增加可读性
    --别名可以使用双引号 as 以及不添加任何内容 如果别名中包含特殊字符 关键字 空格需要使用双引号 表示该部分是一个整体
    select * from emp;
    select empno,ename,job,sal,deptno from emp;
    select empno "员工编号", ename 姓名,job as 工种,sal as 工资,deptno as "部门 编号" from emp;
    
    --|| 连接符
    --需求：XXX的年薪是XXX，入职时间是 XXX
    select ename,sal*12,hiredate from emp;
    select ename || '的年薪是,' || sal * 12 ||',入职时间是' || hiredate from emp;
    
    --去除重复行 distinct
    select deptno from emp;
    select distinct deptno from emp;
    select deptno ,job from emp;
    --把distinct 后面的所有的字段的重复的组合去掉
    select distinct deptno ,job from emp;
    
    --按照部门编号的大小排序 order by 默认asc 升序 降序 desc
    select deptno from emp order by deptno;
    select distinct deptno from emp order by deptno desc;
    
    select distinct deptno,job from emp order by deptno desc;
    select distinct deptno, job from emp order by deptno desc, job;--多个字段进行先后的排序，使用逗号分割
    
    --按照员工的年薪排序
    select empno,ename,sal*12 from emp;
    select empno, ename, sal*12 from emp order by sal*12 desc;--可以使用算术表达式排序
    select empno, ename,sal*12 年薪 from emp order by 年薪;--还可以使用别名排序

6.SQL-2

--where 子句 条件查询
    --职位是MANAGER 全部显示出来
    select * from emp where job='MANAGER';
    select empno, ename,job,sal from emp where job='MANAGER';
    select * from emp where job='manager';--数据的内容大小写敏感
    SELECT * FROM emp WHERE job = 'MANAGER';--关键字大小写不敏感
    select * from EMP WHERE JOB='MANAGER';--字段名称大小写不敏感
    --把部门编号大于20的员工信息列出
    select * from emp where deptno > 20;
    select * from emp where deptno < 20;
    select * from emp where deptno >= 20;
    select * from emp where deptno <= 20;
    select * from emp where deptno != 20;
    select * from emp where deptno <> 20;--建议使用这样的形式
    --将入职时间为1981/9/8之后的员工的信息全部打印
    --日期比较两种解决方案，一种 使用类型转换 to_date 还有使用日期字符串的默认格式 'DD-MON-RR'
    select * from emp where hiredate>'1981/9/8';--类型不匹配 字符串转化为 date to_date() 然后再比较
    select * from emp where hiredate > '8-9月-81'--使用日期的默认格式比较 --查询工资在2000-3000见的员工的信息 select * from emp where sal >=2000 and sal<=3000--and 并且的意思 select empno,ename,job,sal from emp where sal >2000 and sal < 3000 select empno,ename,job,sal from emp where sal between 2000 and 3000--闭区间[] --岗位是分析师ANALYST或者是MANAGER 经理的员工的信息 select * from emp where job='ANALYST' or job='MANAGER' or job='SALESMAN' select * from emp where job in ('ANALYST','MANAGER','SALESMAN') --模糊查询 % 通配符 _任意一个字符 --员工的名字中包含了字母S的员工的信息 select * from emp where ename like '%S%';--%代表任意个字符 %通配符
    --首字符为S
    select * from emp where ename like 'S%' --第一个字符为任意字符，第二个字符为S的员工的信息 select * from emp where ename like '_C%';
    --第二个字符为下划线的员工的信息 使用escape 指定转义字符
    select * from emp where ename like '__\_%' escape '\';

7.SQL-3

--需求 将奖金为 空的员工的信息打印 is null 
    select * from emp where comm is null --将将近为非空的员工信息打印 is not null select * from emp where comm is not null;
    
    --运算符的优先级 可以通过使用小括号()来达到先计算某一个部分的目的
    select * from emp where job='SALESMAN' or job='CLERK' and sal>=1280;
    select * from emp where (job='SALESMAN' or job='CLERK') and sal>=1280;
    select * from emp where job='SALESMAN' or (job='CLERK' and sal>=1280);
    
    --伪表 字符函数的测试
    select * from dual;
    select lower('ASD') from dual;
    select sysdate ,user from dual select INITCAP (ename),lower(job) from emp;
    
    --数值函数测试（自测）
    
    --日期函数测试
    --如何得到系统日期
    select sysdate from dual --查询所有员工的名字和入职的天数 select ename ,sysdate-hiredate from emp;
    --查询所有员工的名字和入职的月数
    select ename, months_between(sysdate,hiredate) from emp;
    --对某个日期加减月份
    select sysdate , add_months(sysdate,-1) from dual;
    select sysdate , add_months(sysdate,12) from dual;
    --下周一的日期
    select sysdate,next_day(sysdate,'星期一') from dual;
    --本月的最后一天的日期
    select sysdate ,last_day(sysdate) from dual;
    --函数的嵌套调用 当前时间的下一个星期一的后面的星期二的日期
    select sysdate,next_day(next_day(sysdate,'星期一'),'星期二') from dual;
    --查询所有员工入职的 星期数 和年数 使用别名增加可读性
    select ename 姓名, sysdate 当前日期 , sysdate-hiredate 入职天数, (sysdate-hiredate)/7 入职星期数, months_between(sysdate,hiredate) 入职月数, months_between(sysdate,hiredate)/12 入职年数 from emp;
    
    
    --自动类型转换
    select '12.5'+30 from dual;--->42.5
    select '12.5'||30 from dual;---->12.530
    
    --转换函数测试 to_char() to_date() to_number()
    --java 中的结果 51
    select 1+'2' from dual;
    --数值 --->字符串 输出的结果保持一个固定的位数 代表一位数字，如果该位没有数字则强制显示0
    select to_char(1234.123, 'L000,000.0000') from dual;
    --代表一位数字，如果该位没有数字则不进行显示，但对于小数点后面的部分仍会强制显示
    select to_char(123456.789 , '$999,999,999.9999') from dual --字符串到数值 必须提供数值型字符串 不能包含非法字符 select to_number('123') from dual;
    --回忆：日期格式化 “yyyy-MM-dd HH:mm:ss”
    --日期<--->字符串
    
    --将当前日期按照指定的格式输出 HH 默认12小时制
    select sysdate , to_char(sysdate , 'YYYY-MON-DD HH24:MI:SS') from dual;
    select sysdate, to_char(sysdate , 'YY-MM-DD HH12:MI:SS') from dual;
    select sysdate , to_char(sysdate,'YYYY-MM-DD') from dual;
    
    --to_date
    --需求：求指定日子和当前时间的月份差
    --将指定的字符串按照指定的模式转化为日期
    select sysdate, months_between(sysdate , to_date('2017-07-07','YYYY-MM-DD')) from dual;
    
    --求出指定是时间点之后入职的员工的信息？？
    select * from emp where hiredate>to_date('1981/12/1','YYYY/MM/DD');
    --求出指定时间区间内入职的员工的信息？？
    select * from emp where hiredate between to_date('1980/12/1','YYYY/MM/DD') and to_date('1981/12/1','YYYY/MM/DD') select * from emp where hiredate >=to_date('1980/12/1','YYYY/MM/DD') and hiredate <=to_date('1981/12/1','YYYY/MM/DD')

8.常用SQL

--1:查询每个月倒数第三天入职的员工的所有信息
    select * from emp where last_day(hiredate)-2=hiredate --2:找出早于30年前入职的员工的信息 select * from emp where months_between(sysdate,hiredate)/12>30 select * from emp where (to_char(sysdate,'YYYY')-to_char(hiredate,'YYYY'))>30 --3:以首字母都是大写的方式显示所有员工的姓名 select ename , initcap(ename) from emp --4:显示不带有字符E的员工的姓名 select ename from emp where ename not like '%E%' select ename from emp where ename like '%E%' --5 ：显示名字长度为5的员工的名字 select ename from emp where length(ename)=5 --6:显示所有员工的名字的前三个字符 select ename, substr(ename,0,3) sub_name from emp;
    --7:显示所有的员工的姓名 把所有的‘A’替换为 'a'
    select ename ,replace(ename,'A','a') re_name from emp --8:显示员工的所有的信息，按照姓名排序 select * from emp order by ename asc;
    select * from emp order by ename desc;
    --9:显示员工的姓名，加入公司的年份，加入公司的月份，按照受雇日期排序，若月份相同按照年份排序
    select ename ,hiredate ,to_char(hiredate,'YYYY') year,to_char(hiredate,'MM') month from emp order by month,year;
    --10:显示有所员工的姓名，和受雇日期，根据服务的年限，将最老的员工排在前面
    select ename,hiredate from emp order by hiredate --11:显示所有的员工的姓名，工作，薪金，按照工作的降序排序，若工作相同，按照薪金排序 select ename,job,sal from emp order by job desc,sal asc;
    --12:找出所有在2月份受雇的员工的信息
    select * from emp where to_char(hiredate,'MM')=2 --13 将所有的员工加入公司的天数显示 select ename,round(sysdate-hiredate) days from emp --14 将“￥123”字符串，显示为number select to_number('￥123','L999.99') from dual select to_number('￥123','L999.00') from dual select to_number('￥123.12','L999.999') from dual select to_number('￥123.12','L999.000') from dual select to_char('123.123','L999.9999') from dual select to_char('123.123','L999.0000') from dual

9.通用函数

--通用函数 其他函数
    --查询所有员工的姓名，职位，工资，奖金，总收入（工资+奖金）。
    --任何值和null做运算结果都是null
    select ename,job,sal,comm,sal+comm income from emp;
    --处理null 的 函数 nvl nvl2
    --nvl：两个参数，如果第一个参数为null，则返回第二个参数，如果不为null 返回第一个参数
    select ename,job,sal ,comm,sal+nvl(comm,0) income from emp --nvl2:三个参数 如果第一个参数为null 则返回第三个参数，如果第一个参数不为null 返回第二个参数 --nvl2和java 中的三目运算符 ? : 相似 select ename,job,sal,comm,sal+nvl2(comm,comm,0) income from emp --注意事项 nvl nvl2 中的参数的类型必须要一致。 select comm,nvl(comm,'没有奖金') from emp --decode --将所有的职位显示，不能重复 select distinct job from emp --需求：将员工的所有的职位用中文表示 --类似于java 中的switch case 语句。 select distinct job, decode(job,'SALESMAN','销售员','PRESIDENT','CEO','MANAGER','经理','ANALYST','分析师','CLERK','职员') from emp;
    select distinct job, decode(job,'SALESMAN','销售员','PRESIDENT','CEO','MANAGER','经理','ANALYST','分析师','职员') from emp;

10.多行函数

--多行函数：每次可以处理一组 （多条）数据 然后返回一条数据。也成为分组函数。
    --一共就5个函数
    --sum() avg() max() min() count()
    --需求：查询公司最高工资，最低工资，总工资，平均工资，公司的总人数
    select max(sal),min(sal),sum(sal),round(avg(sal)),count(ename) from emp; 
    --5个函数可以分为两类
    --第一类：max,min,count 可以处理任意类型的数据。
    select max(ename),min(sal) ,count(hiredate) from emp;
    --第二类 sum avg 只能处理数值类型。其他类型不可以
    select sum(sal),avg(sal) from emp --针对null 多行函数如何处理 --多行函数处理null 时会直接忽略掉 select ename ,comm from emp where comm is not null select sum(comm),avg(comm),count(comm),max(comm),min(comm) from emp --求平均奖金，按照总人数计算 select sum(comm),avg(nvl(comm,0)),count(comm),max(comm),min(comm) from emp --count()一般是用来统计数据的条数，如果某一个属性的字段有 null 将不能正确的统计真实的数据的条数。 --通常使用count(*)来统计数据的条数。 --如果一个表中没有任何的数据，那么count(*)返回0 count 函数永远返回的是一个>=零的值，不会返回null。 select * from bonus select count(*) from bonus --统计10部门的员工总数 --表中的字段和单行函数不能和分组函数一起使用，除非以该字段分组。 select deptno,count(*) from emp where deptno=10--报错

11.分组查询

---分组查询 group by
    --需求：统计每个部门的人数
    select count(*) from emp where deptno=10 or deptno=20 or deptno=30 --group by 为分组查询的关键字，后面跟要按照分组的字段 select deptno, count(*) from emp group by deptno --需求：统计各个部门的总人数，总工资，平均工资，最大工资，最小工资 select deptno,count(*),sum(sal),avg(sal),max(sal),min(sal) from emp group by deptno order by deptno --需求：统计各个部门的总人数，总工资，，平均工资，最大工资，最小工资，除了10部门 select deptno,count(*),sum(sal),avg(sal),max(sal),min(sal) from emp where deptno <> 10 group by deptno order by deptno --sql 中的关键字的顺序是有严格要求的，不能随意排放 --顺序：from-->where(行过滤)-->group by(根据什么字段分组)--having(分组条件)--select(显示哪些字段)--order by(对显示的字段进行排序) --order by 必须放在sql 语句的最后面 --统计每个部门的人数，平均工资，排除平均工资小于2000的。 --where 后不能使用分组函数。 where 是进行行过滤，不能进行分组过滤，having 是进行分组过滤的。 --where 子句在 分组之前进行行过滤。然后在进行分组 group by ，分组之后在进行 分组过滤 having --where 后可以使用的所有的条件判断，在having 后都可以使用。 select deptno,count(*)，avg(sal) from emp group by deptno having avg(sal)>=2000 select deptno,count(*)，avg(sal) avg_sal from emp group by deptno having avg_sal>=2000--标识符无效 ，having 子句先与select 执行。 --统计每个部门的人数和平均工资，排除10部门 和 平局工资小于1000 的部门。 select deptno,count(*),avg(sal) from emp where deptno<>10 group by deptno having avg(sal)>=1000 order by deptno --列出工资最小值小于2000的职位 --select 后的字段，必须是分组的字段才可以使用。 select job,min(sal) from emp group by job having min(sal)<2000 --需求，将所有的职员和销售人员的工资列出 select job, sal from emp where job='CLERK' or job='SALESMAN' --列出平均工资大于1200的部门和工作组合 对多个字段进行分组 select deptno,job,avg(sal) from emp group by deptno,job having avg(sal)>1200 --统计人数小于4的部门的平均工资 select deptno,count(*),avg(sal) from emp group by deptno having count(*)<6 --统计各部门的最高工资，排除最高工资小于3000的部门？？？ select deptno,max(sal) from emp group by deptno having max(sal)>=3000

12.DML

--DML 增加 修改 删除 
    --创建一个临时表 使用查询的结果创建一个表
    create table temp as select * from emp;
    
    create table temp1 as select * from emp where deptno=10 select * from temp1 --drop table temp1 --创建表结构 create table temp1 as select * from emp where 1=2 --插入数据 insert into temp1 (empno,ename,job,mgr,hiredate,sal,comm,deptno) values(1234,'张三丰','掌门',null,sysdate,5000,null,10) insert into temp1 (empno,ename,job,mgr,hiredate,sal,comm,deptno) values(2234,'张无忌','徒孙',null,sysdate,1000,null,10) --使用字段名 顺序 无所谓。只要求values 中的值的顺序和 字段的顺序一致即可。 insert into temp1 (ename,empno,job,mgr,hiredate,sal,comm,deptno) values('周芷若',3343,'徒孙对象',null,sysdate,1000,null,10) --插入的数据的列 不一定要求是全部的列，插入的列的数量和 values 中的提供的值的数量和顺序一致即可。 insert into temp1 (ename,empno,job,hiredate,sal,deptno) values('赵敏',1343,'徒孙媳妇',sysdate,1000,10) -- 如果想省略列需要插入数据的字段，那么在values 中，必须提供全部的表的字段的值，并且顺序和表中字段的顺序一致。 insert into temp1 values(1235,'金毛狮王','',null,sysdate,5000,null,20) --将查询的结果集插入表中 insert into temp1 (select * from temp) commit--提交 rollback--回滚 --update --将10部门有的员工的工资都增加20% update temp1 set sal = sal*1.2 where deptno=10 --可以同时修改多个字段，使用逗号分割 --需求，将comm 为null 的值修改为0，然后将mgr 为null 的值修改为指定的值。 update temp1 set mgr=nvl(mgr,7839),comm=nvl(comm,0) --删除 delete 删除表中 的数据。 from 可以省略 delete from temp1 where empno<4000 --不加任何的条件，就是删除所有的数据 delete temp1 --如果把表中的数据都删除，一般不用delete --truncate table 效率更高，而且自带提交功能。 truncate table temp1;
    
    select * from temp1

13.多表查询

--多表查询
    --笛卡尔集（积）
    select * from emp;
    select * from dept;
    --两张表的笛卡尔集 就是一个乘积的关系
    select * from emp,dept
    --消除笛卡尔集
    --等值连接 
    --需求：查询员工的编号，姓名，部门编号，部门名称。  sql 92标准中，将多个表的连接条件写在where子句中。
    select empno,ename,emp.deptno,dname
    from emp,dept
    where emp.deptno=dept.deptno
    --给表起别名  一旦给表起了别名，那么只能使用表的别名， 表的原始名称将失效。表的别名不能使用as
    select e.empno,e.ename,e.deptno,d.dname
    from emp e,dept d
    where e.deptno=d.deptno
    --建议，把所有的需要展示的字段，都使用表名为前缀，表示该字段所在哪个表，可读性更强，效率更高，只需要在指定的表内查找该值即可。
    --如果多个表中存在相同的字段，那么该字段如果展示，必须使用表名指明是哪个表中的字段。
    
    --需求：查询所有员工的姓名，职位 薪水，薪水等级
    --非等值连接
    select e.ename,e.job,e.sal,s.grade
    from emp e,salgrade s
    where e.sal>=s.losal and e.sal<=s.hisal
    --首先求出笛卡尔集，然后根据条件判断那些数据是有效数据，这个条件就是where 中需要写入的条件。
    select e.ename,e.job,e.sal,s.grade
    from emp e,salgrade s
    where e.sal between s.losal and s.hisal
    
    --自连接  一张表看作两张表
    --将员工的编号，名字，员工的上级的编号 名字 求出
    select * from emp
    select e1.empno,e1.ename,e1.mgr,e2.ename
    from emp e1,emp e2
    where e1.mgr=e2.empno
    
    select e1.empno,e1.ename,e2.empno,e2.ename
    from emp e1,emp e2
    where e1.mgr=e2.empno

14.外连接

--使用外连接可以看到参与连接的某一方不满足连接条件的记录，而不仅仅是满足连接条件的数据。
    --如果没有领导那就只显示员工的信息即可
    --左外连接 ，将(+) 放到连接条件的右边那个表的后面
    --右外连接，将(+) 放到连接条件的左边那个表的后面
    select e1.empno,e1.ename,e1.mgr,e2.ename
    from emp e1,emp e2
    where e1.mgr=e2.empno(+)
    
    --查询所有部门的详细信息，以及每个部门的平均工资，包含没有员工的部门
    --右外连接
    select d.* ,round(nvl(avg(e.sal),0)) avg_sal
    from emp e, dept d
    where e.deptno(+)=d.deptno
    group by d.deptno,d.dname,d.loc--此处不可以使用d.*
    order by d.deptno
    
    --练习三表查询
    --查询20号部门的员工的编号，姓名，薪水，部门名称，薪水等级
    select e.deptno,e.empno,e.ename,e.sal,d.dname,s.grade
    from emp e,dept d,salgrade s
    where e.deptno=d.deptno and e.sal between s.losal and s.hisal and d.deptno=20
    --思考：n张表，至少需要几个连接条件
    n-1个连接条件

15.多表连接-99

--sql99 多表连接
    --1：cross join 和 92标准中的笛卡尔集是一样的效果
    select * from emp,dept; --92
    select * from emp cross join dept --99 --2：natural join 自然连接 类似于92标准中等值连接 select emp.deptno,dept.deptno --92 --相同的列前必须使用所属表的限定词 from emp,dept where emp.deptno=dept.deptno;
    --99 如果想进行自然连接，那么两个表需要有相同的字段。然后连接时会根据两个字段自动进行等值连接。
    --两个表中相同的字段，进行自然连接的时候，相同的字段前不能再有所属于某个表的限定词
    --两个表的连接条件在自然连接中 省略了。
    select deptno from emp natural join dept;
    --两个表没有相同的字段，等同于cross join
    select * from emp natural join salgrade --using 子句后(连接的字段) 用来指定两个表连接的时候使用哪几个字段。用于等值连接 --[inner] join 内连接 select * from emp join dept using(deptno) --on 子句 后跟连接条件 既可以是等值连接，也可以是非等值连接 --查询30部门员工的 编号 姓名 部门名称 select e.empno,e.ename,d.dname --92 标准 from emp e,dept d where e.deptno=d.deptno and d.deptno=30;
    select e.empno,e.ename,d.dname--sql99 标准 from emp e join dept d on e.deptno=d.deptno--连接条件使用on 子句 where d.deptno=30--过滤条件仍然使用where --查看10部门员工的姓名，薪水 和薪水等级 select e.deptno, e.ename,e.sal,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal where e.deptno=10 --自连接 --需求：员工编号，员工的名称，领导的编号，领导的名称 select e1.empno,e1.ename,e2.empno,e2.ename from emp e1 join emp e2 on e1.mgr=e2.empno --外连接 --左外连接 left [outer] join --右外连接 right [outer] join --全外连接 full [outer] join select e1.empno,e1.ename,e2.empno,e2.ename from emp e1 left outer join emp e2 on e1.mgr=e2.empno select e1.empno,e1.ename,e2.empno,e2.ename from emp e1 right outer join emp e2 on e1.mgr=e2.empno select e1.empno,e1.ename,e2.empno,e2.ename from emp e1 full outer join emp e2 on e1.mgr=e2.empno ---查询所有部门的详细信息，以及每个部门的平均工资 包含没有员工的部门 select d.* ,round(avg(sal)) avg_sal from emp e right join dept d on e.deptno=d.deptno group by d.deptno,d.dname,d.loc order by d.deptno --查询20号部门的员工的编号、姓名，薪水、部门名称、薪水等级 select d.deptno,e.empno,e.ename,e.sal,d.dname,s.grade from emp e join dept d on e.deptno=d.deptno join salgrade s on e.sal between s.losal and s.hisal where d.deptno=20 select deptno,e.empno,e.ename,e.sal,d.dname,s.grade from emp e join dept d using(deptno) join salgrade s on e.sal between s.losal and s.hisal where deptno=20 select deptno,e.empno,e.ename,e.sal,d.dname,s.grade from emp e natural join dept d join salgrade s on e.sal between s.losal and s.hisal where deptno=20 1、内连接(自然连接): 只有两个表相匹配的行才能在结果集中出现 2、外连接: 包括 （1）左外连接(左边的表不加限制) （2）右外连接(右边的表不加限制) （3）全外连接(左右两表都不加限制) 3、自连接(连接发生在一张基表内)

16.子查询

--子查询--单行子查询
    --如何查得所有比“CLARK”工资高的员工的信息
    --先查clark 的工资
    select sal from emp where ename='CLARK';
    select * from emp where sal > 2450 --子查询，必须放置到一对小括号中。 select * from emp where sal > (select sal from emp where ename='CLARK');
    
    --查询工资高于平均工资的员工的名字 和工资
    select avg(sal) from emp select ename,sal from emp where sal > (select avg(sal) from emp) --查询和scott 同一个部门而且比他工资低的员工的姓名和工资 select deptno from emp where ename='SCOTT' select sal from emp where ename='SCOTT' select ename,sal from emp where deptno=(select deptno from emp where ename='SCOTT') and sal<(select sal from emp where ename='SCOTT') --查询 职务 和scott 相同 比scott 雇佣时间早的员工的信息 select job from emp where ename='SCOTT' select hiredate from emp where ename ='SCOTT' select * from emp where job=(select job from emp where ename='SCOTT') and hiredate<(select hiredate from emp where ename ='SCOTT') -- 查询工资比scott 高或者雇佣时间比scott 早的员工的编号和名字 select sal from emp where ename='SCOTT' select hiredate from emp where ename ='SCOTT' select empno,ename from emp where sal>(select sal from emp where ename='SCOTT') or hiredate <(select hiredate from emp where ename ='SCOTT') --多行子查询 --查询工资低于任意一个 ‘clerk’ 的工资的员工信息 --ALL：和所有的查询的数据都比较 --ANY:和结果中的任意一个数据比较 --IN：等于结果集中某一个数据 select sal from emp where job='CLERK' select * from emp where sal < any(select sal from emp where job='CLERK') --查询工资比所有的‘SALESMAN’都高的雇员的编号，姓名 工资 select sal from emp where job='SALESMAN' select empno,ename,sal from emp where sal > all(select sal from emp where job='SALESMAN') --查询部门编号为20 中的职务同部门 10 的员工一样的 员工的信息 select job from emp where deptno=10 select * from emp where deptno=20 and job in(select job from emp where deptno=10) --查询在员工中那些人是领导 select distinct mgr from emp where mgr is not null;
    select * from emp where empno in(select distinct mgr from emp where mgr is not null) select * from emp where empno = any(select distinct mgr from emp where mgr is not null) --找出部门编号为20 的所有员工中收入最高的职员 select max(sal) from emp where deptno=20 select * from emp where deptno=20 and sal=(select max(sal) from emp where deptno=20) -- 查询每个部门的平均薪水的等级（可以将子查询作为一张表格，实现多表关联查询） --部门平均薪水 作为一张表 select deptno,avg(sal) avg_sal from emp group by deptno --最终结果 select T.deptno,avg_sal,s.grade from (select deptno,avg(sal) avg_sal from emp group by deptno) T join salgrade s on avg_sal between s.losal and s.hisal -- 查询部门的详细信息，以及部门平均工资和等级 --部门平均薪水 作为一张表 select deptno,avg(sal) avg_sal from emp group by deptno --最终结果 select d.*,avg_sal,s.grade from (select deptno,avg(sal) avg_sal from emp group by deptno) T join dept d on T.deptno=d.deptno join salgrade s on avg_sal between s.losal and s.hisal

17.练习

-- 1 、列出所有员工的年工资，按年薪从低到高排序。
    select (sal+nvl(comm,0))*12 income from emp order by income;
    -- 2 、列出薪金比“ SMITH ”多的所有员工。
    select * from emp where sal > (select sal from emp where ename='SMITH');
    -- 3 、列出所有员工的姓名及其直接上级的姓名。
    select e1.ename,e2.ename --sql99 from emp e1 join emp e2 on e1.mgr= e2.empno select e1.ename,e2.ename --sql92 from emp e1 , emp e2 where e1.mgr= e2.empno -- 4 、列出受雇日期早于其直接上级的所有员工。 select e1.* --sql99 from emp e1 join emp e2 on e1.mgr= e2.empno where e1.hiredate<e2.hiredate -- 5 、列出部门名称和这些部门的员工信息，包括那些没有员工的部门。 select d.dname,e.* from dept d left join emp e on d.deptno=e.deptno -- 6 、列出所有job 为“ CLERK ”（办事员）的姓名及其部门名称。 select e.ename,d.dname from emp e natural join dept d where e.job='CLERK' -- 7 、列出最低薪金大于1500 的各种工作。 select job,min(sal) from emp group by job having min(sal)>1500 -- 8 、列出在部门“ SALES ”（销售部）工作的员工的姓名，假定不知道销售部的部门编号。 select e.ename from dept d join emp e on d.deptno=e.deptno where d.dname='SALES' -- 9 、列出薪金高于公司平均薪金的所有员工。 select * from emp where sal > (select avg(sal) from emp) -- 10 、列出与“ SCOTT ”从事相同工作的所有员工。 select * from emp where job=(select job from emp where ename='SCOTT') and ename <> 'SCOTT' -- 11 、列出薪金高于在部门 30 工作的所有员工的薪金的员工姓名和薪金。 select ename,sal from emp where sal > all(select sal from emp where deptno=30) -- 12 、列出在每个部门工作的员工数量、平均工资和平均服务期限（年）。 select count(*),avg(sal),avg(to_char(sysdate,'YYYY')-to_char(hiredate,'YYYY')) from emp group by deptno -- 13 、列出所有员工的姓名、部门名称和工资。 select e.ename,d.dname,e.sal from emp e join dept d on e.deptno=d.deptno -- 14 、列出从事同一种工作但属于不同部门的员工的一种组合。 select e1.ename,e1.deptno,e1.job,e2.ename,e2.deptno from emp e1 join emp e2 on e1.job=e2.job and e1.deptno <> e2.deptno and e1.ename<e2.ename --保证同一个组合不会出现两次， -- 15 、列出所有部门的详细信息和部门人数。 select deptno,d.dname,d.loc ,count(*) from dept d join emp e using(deptno) group by deptno,d.dname,d.loc order by deptno -- 16 、列出各种工作的最低工资。 select job,min(sal) from emp group by job order by min(sal) -- 17 、列出各个部门的 MANAGER （经理）的最低薪金。 select deptno, min(sal) from emp where job='MANAGER' group by deptno -- 18 、列出至少有一个员工的所有部门。 select deptno,d.dname,d.loc from emp join dept d using(deptno) group by deptno,d.loc,d.dname having count(*) > 0

18.关于用户的操作

--创建用户
    --同等权限的用户不能相互创建
    --需要有管理员的权限
    create user bfmzdx1 identified by bfmzdx1;
    --授予权限
    grant connect , resource to bfmzdx;
    grant dba to bfmzdx;
    --撤销用户权限
    revoke dba from bfmzdx;
    --修改用户的密码
    alter user bfmzdx identified by 123456;
    --删除用户
    drop user bfmzdx1;
    --建立一张用来存储学生信息的表
    --字段包含学号、姓名、性别，年龄、入学日期、班级，email等信息
    --学号是主键
    --姓名不能为空
    --性别默认值是男
    --年龄范围18---30岁
    --Email唯一
    create table student( sno varchar2(10), sname varchar2(15), gender char(2) default '女', age number(2), sdate date, clazz varchar2(20), email varchar2(30) );
    insert into student values('100001',null,'男',60,sysdate,'406','zhangsanfeng@123.com');
    select * from student;

19.关于表的操作

--关于表的操作 修改表
    --添加字段
    alter table student add (score number(5,2));
    --删除字段
    alter table student drop column score;
    --修改字段的名称
    alter table student rename column newage to age;
    --修改字段的数据类型 需要修改的字段的数据必须为空
    alter table student modify (score varchar2(10)) --重命名表--stu rename s to student;
    rename stu to student;
    --删除表 表会被放到回收站
    drop table student;
    create table student( sno varchar2(10), sname varchar2(15), gender char(2) default '女', age number(2), sdate date, clazz varchar2(20), email varchar2(30) );
    --查看回收站
    select * from recyclebin;
    --还原表格--把回收站的表还原
    flashback table student to before drop;
    --把回收站的表还原 表格并重命名
    flashback table student to before drop rename to s;
    select * from student;
    --直接不进回收站，直接删除 shift +del
    drop table stu purge;
    --将回收站内的某一张表删除 
    purge table stu;
    --清空回收站
    purge recyclebin;

20.约束

---约束
    select * from emp insert into emp values(7878,'','',7369,sysdate,null,null,40);
    select * from dept 【1】--主键约束 唯一 非空 drop table student purge;
    create table student( --sno varchar2(10) constraints pk_student primary key, sno varchar2(10),-- primary key,--列级主键约束 sname varchar2(15), gender char(2) default '女', age number(2), sdate date, clazz varchar2(20), email varchar2(30), constraints pk_student primary key (sno)--表级别设置主键约束 --primary key (sno)--简化的表级别 使用系统提供的名字 );
    select * from student;
    insert into student values('100001','张三丰','',60,sysdate,'406','zhangsanfeng@123.com');
    --如果多列作为主键 --联合主键 只能使用表级约束。
    drop table student purge;
    create table student( --sno varchar2(10) constraints pk_student primary key, sno varchar2(10),-- primary key,--列级主键约束 sname varchar2(15),-- primary key, gender char(2) default '女', age number(2), sdate date, clazz varchar2(20), email varchar2(30), constraints pk_student primary key (sno,sname)--表级别设置主键约束 --primary key (sno,sname)--简化的表级别 使用系统提供的名字 );
    --联合主键，主键的组合唯一即可 任意一个主键的字段都不能为null
    insert into student values('100001','张三丰','',60,sysdate,'406','zhangsanfeng@123.com');
    insert into student values(null,'张无忌','',60,sysdate,'406','zhangsanfeng@123.com');
    
    【2】--非空约束
    drop table student purge;
    create table student( sno varchar2(10), sname varchar2(15) not null,--列级约束 gender char(2) default '女', age number(2), sdate date, clazz varchar2(20), email varchar2(30), constraints pk_student primary key (sno) --constraints nn_student_sname not null---非空约束只有列级约束，没有表级约束 );
    select * from student;
    insert into student values('100001','张三丰','',60,sysdate,'406','zhangsanfeng@123.com');
    insert into student values('100002',null,'',60,sysdate,'406','zhangsanfeng@123.com');
    
    --【3】唯一约束 唯一约束的列如果不为空，则值必须唯一，该字段的值可以为null， 多行数据，该字段可以有多个null值。 
    drop table student purge;
    create table student( sno varchar2(10), sname varchar2(15) not null,--列级约束 gender char(2) default '女', age number(2), sdate date, clazz varchar2(20), email varchar2(30) ,--unique, --列级唯一约束 constraints pk_student primary key (sno), constraints uk_student_email unique (email) );
    --【4】 检查约束 check 学生的年龄为18-30
    drop table student purge;
    create table student( sno varchar2(10), sname varchar2(15) not null,--列级约束 gender char(2) default '女' check (gender in ('男','女')), age number(2) ,--check (age between 18 and 30),--check (age >=18 and age <=30), 列级检查约束 sdate date, clazz varchar2(20), email varchar2(30) ,--unique, --列级唯一约束 constraints pk_student primary key (sno), constraints uk_student_email unique (email), constraints ck_student_age check (age between 18 and 30)--表级检查约束 );
    select * from student;
    insert into student values('100002','张三丰','男',18,sysdate,'406','zhangsanfeng1@123.com');
    --【5】 外键约束 表的外键只能是主表的 主键列或者是 唯一约束的列。
    create table clazz(--主表 cno varchar2(10) primary key, cname varchar2(20) not null, loc varchar2(40) );
    insert into clazz values('101','20140405','银川西夏区');
    insert into clazz values('102','20140405','北京西三旗');
    insert into clazz values('103','20140405','银川金凤区');
    insert into clazz values('104','20140405','北京亦庄');
    insert into clazz values('105','20140405','北京东三旗');
    select * from clazz;
    drop table student purge;
    create table student(--子表 、从表 sno varchar2(10), sname varchar2(15) not null,--列级约束 gender char(2) default '女' check (gender in ('男','女')), age number(2) ,--check (age between 18 and 30),--check (age >=18 and age <=30), 列级检查约束 sdate date, clazz varchar2(20), email varchar2(30) ,--unique, --列级唯一约束 cno varchar2(10) ,--references clazz(cno),--外键的列级约束 constraints pk_student primary key (sno), constraints uk_student_email unique (email), constraints ck_student_age check (age between 18 and 30),--表级检查约束 constraints fk_student_cno foreign key (cno) references clazz (cno) );
    select * from student;
    insert into student values('100001','张三丰','男',18,sysdate,'406','zhangsanfeng@123.com','105');
    insert into student values('100003','张无忌','男',18,sysdate,'406','zhangswuji@123.com','101');

21.级联删除

--级联删除 三种情况 默认情况 RESTRICT
    delete from clazz where cno='105';
    delete from student where cno='105';
    select * from clazz;
    --级联删除 CASCADE
    drop table student purge;
    create table student(--子表 、从表 sno varchar2(10), sname varchar2(15) not null,--列级约束 gender char(2) default '女' check (gender in ('男','女')), age number(2) ,--check (age between 18 and 30),--check (age >=18 and age <=30), 列级检查约束 sdate date, clazz varchar2(20), email varchar2(30) ,--unique, --列级唯一约束 cno varchar2(10) ,--references clazz(cno),--外键的列级约束 constraints pk_student primary key (sno), constraints uk_student_email unique (email), constraints ck_student_age check (age between 18 and 30),--表级检查约束 constraints fk_student_cno foreign key (cno) references clazz (cno) on delete cascade );
    delete from clazz where cno='102';
    ----级联删除 set null
    drop table student purge;
    create table student(--子表 、从表 sno varchar2(10), sname varchar2(15) not null,--列级约束 gender char(2) default '女' check (gender in ('男','女')), age number(2) ,--check (age between 18 and 30),--check (age >=18 and age <=30), 列级检查约束 sdate date, clazz varchar2(20), email varchar2(30) ,--unique, --列级唯一约束 cno varchar2(10) ,--references clazz(cno),--外键的列级约束 constraints pk_student primary key (sno), constraints uk_student_email unique (email), constraints ck_student_age check (age between 18 and 30),--表级检查约束 constraints fk_student_cno foreign key (cno) references clazz (cno) on delete set null );
    delete from clazz where cno='103';

22.表格约束-2

---先创建表，然后再去添加约束
    drop table student purge;
    create table student( sno varchar2(10), sname varchar2(15), gender char(2) default '女', age number(2), sdate date, clazz varchar2(20), email varchar2(30), cno varchar2(20) );
    alter table student add constraints pk_student primary key (sno);
    alter table student add constraints uk_student_email unique (email);
    alter table student add constraints ck_student_age check (age between 18 and 30);
    alter table student add constraints ck_student_gender check (gender in('男','女'));
    alter table student add constraints fk_student_cno foreign key (cno) references clazz (cno);
    --删除约束
    alter table student drop constraints fk_student_cno;
    --修改 约束不能修改，如果想修改某个约束，只能先删除 然后再添加
    select * from clazz;
    select * from student;
    --删除某一张表 不能直接删除 可以先删除掉外键约束，然后再删除主表
    drop table clazz;
    flashback table clazz to before drop;
    --强制删除 其实走的也是先删除约束，然后再删除相关的表
    drop table clazz cascade constraints;

23.序列-索引

-序列 Sequence oracle 专有的数据库对象
    --希望主键的列是递增的。
    --创建默认属性的一个序列 从1开始 递增 1 不循环 
    create sequence seq_clazz_cno;
    --创建自定义属性的序列
    create sequence seq_clazz_cno increment by 1 start with 100;
    --maxvalue 9999999999999999
    insert into clazz values(seq_clazz_cno.nextval,'20170202',null);
    
    --删除序列
    drop sequence seq_clazz_cno;
    
    --获得当前序列的值 currval 必须在nextvalue 使用之后才能生效
    select seq_student_sno.nextval from dual;--每次访问nextvalue 都会根据自增的数值进行递增。
    select seq_student_sno.currval from dual;
    
    insert into student values(seq_student_sno.nextval,'张翠山','男',28,sysdate,'406','zhangcuishan2@123.com','103');
    
    select * from student;
    select * from clazz;
    
    --索引
    --索引的作用：在数据库中用来加速对表的查询,通过使用快速路径访问方法快速定位数据,减少了磁盘的I/O
    --索引创建以后，如果根据创建索引的字段进行数据的检索，那么该字段对应的索引会被被动使用。
    --索引创建
    --自动: 当在表上定义一个PRIMARY KEY 或者UNIQUE 约束条件时,Oracle数据库自动创建一个对应的唯一索引.
    --手动: 用户可以创建索引以加速查询
    
    --手动创建
    --删除索引
    drop index index_student_sname;
    select sname from student;
    create index index_student_sname on student (sname desc);
    select sname from student;
    
    select * from student where sname='张三丰';
    --删除索引
    drop index index_student_sname;
    -- 授予权限
    grant dba to bfmzdx;

24.视图

--视图 view
    --视图 view 之所以被称之为虚表 并没有真实的存储任何的数据。其实就是一条sql 语句，是访问的另外的一张表 或者是 视图。
    --创建视图
    create or replace view view_student as (select * from student);
    --访问视图
    select * from view_student ;
    --操作视图 DML 操作
    --将 sno =10 的数据的日期修改 通过视图 可以修改基表的数据
    update view_student set sdate=to_date('2017-10-10','YYYY-MM-DD') where sno=10 --设置视图只读 设置只读之后，视图只能被查询 不能被DML 操作。 select sno,sname,gender,age,sdate from student;
    create or replace view view_stu as (select sno,sname,gender,age,sdate from student) with read only;
    --对只有部分字段的视图进行DML 操作
    insert into view_stu values('100003','卿易富网','男',29,sysdate);
    --删除视图
    drop view view_stu;
    --需求：查询所有班级的详细信息，并统计班级的人数和平均年龄
    select c.*,count(*),avg(s.age)--92 from student s,clazz c where s.cno(+) = c.cno group by c.cno,c.cname,c.loc --复杂的视图查询操作，一般都设置为只读 create or replace view view_sc as(--92 select c.*,count(*) count_clazz ,avg(s.age) avg_age from student s,clazz c where s.cno(+) = c.cno group by c.cno,c.cname,c.loc ) with read only;
    select c.*,count(*) count_sc,avg(s.age) avg_age--SQL99 from student s right join clazz c on(s.cno=c.cno) group by c.cno,c.cname,c.loc select * from view_sc;
    select count_clazz,avg_age from view_sc;
    --通过视图创建视图
    create or replace view view_1 as (select * from view_sc);
    select * from view_1;
    --作用：
    --1：保护数据 通过设置视图可以访问的字段，和 设置视图为只读 with read only 来达到保护数据的目的。
    --2：简化查询 如果一个查询是一个比较复杂的查询，可以将查询的结果作为视图使用。下次在进行相应的查询，只需要查询该视图即可
    --如果视图定义的字段中，缺少了基本表中非空的字段，是否可以进行插入的操作？？？？？

25.分页查询

--rowid rownum
    --访问rowid
    select s.* ,s.rowid from student s ;
    --rowid 作用：
    --rowid 程序员是不能操作。rowid 都是由数据库软件自己维护。查找数据都是通过rowid 来完成。直接指向了磁盘上的物理地址。
    --rownum 并不是真实存在的数据。 是需要显示的时候，将需要的结果查询出来之后，显示的时候，
    --根据查询的结果对每一条数据赋值一个rownum
    --rownum 特别指定显示rownum
    select rownum,s.* from student s;
    ---其主要的用处是控制查询返回的行数
    --delete from emp where empno=7878;
    select * from emp;
    --需求 把emp 中的前5条数据显示
    select rownum,e.* from emp e;
    select rownum ,e.* from emp e where rownum <=5;
    --需求：把emp 中6-10的数据显示--rownum 从1开始，所有查询到的数据给rownum 赋值，然后判断是否满足条件。满足条件的第一条数据rownum 是1
    --只能使用：<,<=
    select rownum ,e.* from emp e where rownum between 5 and 11;
    -- select 时对rownub 赋值 order by 是最后的
    select rownum,e.* from emp e order by sal desc;
    --需求：拿按照工资排序的6-10条数据
    --总结rownun 赋值的时机
    select rownum,e.* from emp e order by sal desc;
    --求的工资中最高的前五数据
    select rownum,t.* from (select e.* from emp e order by sal desc) t where rownum<=5;
    --需求：拿按照工资排序的6-10条数据 
    select rownum,R.* from ( --拿到前10条 select rownum r,t.* from (select e.* from emp e order by sal desc) t where rownum<=5--page * count ) R where R.r>0 -- and R.r<11;  (page-1)*count
    -- 一页显示 5条数据 count=5 page 当前页

26.三大范式

范式是指导数据设计的规范化理论，可以保证数据库设计质量
    
    第一范式：字段不能再分
    第二范式：不存在局部依赖
    第三范式：不含传递依赖（间接依赖）
    
    使用范式可以减少冗余，但是会降低性能
    
    特定表的的设计可以违反第三范式，增加冗余提高性能