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.常用命令
登录、切换用户
conn scott/tiger
conn sys/sys as sysdba
加锁解锁账户
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.三大范式
范式是指导数据设计的规范化理论，可以保证数据库设计质量
第一范式：字段不能再分
第二范式：不存在局部依赖
第三范式：不含传递依赖（间接依赖）
使用范式可以减少冗余，但是会降低性能
特定表的的设计可以违反第三范式，增加冗余提高性能
