oracle学习笔记（二）表的查询

--oracle表的管理

--创建表
create table users(userName varchar2(20));
--删除表
drop table users;
--创建表
create table student(xh number(4),xm varchar2(20),sex char(2),birthday date,sal number(7,2));
create table classes(classId number(2),cnmae varchar2(40));
--添加一个字段
alter table  student add (classId number(2));
--修改字段长度
alter table student modify(xm varchar2(30));
--修改字段类型/或者名字（不能有数据）
alter table student modify(xm char(30));
--删除一个字段 不要删字段
alter table student drop column sal;
--修改表的名字
rename student to stu;
--删除表
drop table student;
--oracle中迷人的日期格式'DD-MON-YY' DD代表日，MON代表月 YY代表年
--变更日期的输入默认格式 临时生效
alter session set nls_date_format='DD-MM-YYYY'
--插入空值
insert into student(xh,xm,sex,birthday) values(1,'aa','女'，null);
--查询数据为空的
select * from student where birthday is null;
--查询数据非空
select * from student where birthday is not null;
--把所有的男员工的工资减为原来的一半
update student set sal=sal/2 where sex='男';
--删除数据
--删除所有的记录，表结构还在，写日志,还可以恢复，速度慢
delect from student;
--删除表的结构和数据
drop table student;
--删除表中的所有记录，表结构还在，不写日志，无法找回删除的记录，速度快
truncate table student;
--设置保存点
savepoint a;
--回滚到a点
rollback to a;
--///////////////////////////////
commit
--表的查询
--查询表结构
sesc dept;
select * from emp;
select * from dept;
--疯狂复制
create table users(userId varchar2(20),userName varchar2(20),userpasswd varchar2(20));
insert into users values('001','aaa','12aaa');
insert into users(userId,userName,userpasswd) select * from users;
--取消重复行 distinct
select distinct  deptno,job from emp;
--查询SMITH雇员的deptno，job，sal信息
select deptno,job,sal from emp where ename='SMITH'
--使用算术表达式
--显示所有雇员的年工资
select ename,sal*13 "年工资" from emp; 
--加上奖金的
--处理null志使用 nvl判断是否为空值
--如果查询出comm查询出为空值就用0替代，不为零则使用comm值
select ename,sal*13+nvl(comm,0)*13 "年工资" from emp;
--显示工资高于3000的员工
select * from emp where sal>3000;
--查找1982年1.1后入职的员工
select ename,hiredate from emp where hiredate>'1-1-1982';
--显示工资在2000到2500之间的
select * from emp where sal>2000 and sal<2500;
--like操作符号
--%代表任意的0到多个字符
-- _表示任意单个字符
--显示首字母是大写字母人的工资
select ename ,sal from emp where ename like 'S%'
--显示第三个字母为大写O的人的工资
select ename ,sal from emp where ename like '__O%'
--使用in
select * from emp where  empno in (7844,234,345);
--显示没有上级的人
select * from emp where mgr is null;

--逻辑操作符号
select * from emp where (sal>500 or job='MANAGER') and ename like 'J%'
--薪水从低到高排
select * from emp  order by sal;
--工资从高到底
select * from emp order by sal desc;
--按照部门编号排序升序，并按照薪水从高到底（降序）
select * from emp order by deptno ,sal desc;
--按照部门和升序，入职时间降序
select * from emp order by deptno,hiredate ;
--使用列的别名排序 按照年薪升序排列
select ename,sal*12+nvl(comm,0)*12 "年薪" from emp order by "年薪";
---////////////////////////
--表的复杂查询
--分组函数和非分组函数不能放在一起
--查询出最高工资和最低工资
select  max(sal),min(sal) from emp;
--查询出最高工资的人名
select ename,sal from emp where sal =(select max(sal) ab from emp);
--显示工资高于平均工资的员工
select * from emp where sal<( select avg(sal) from emp);
--group by 分组
--having 过滤
--显示各个部门的平均工资和最高工资
select avg(sal),max(sal),min(sal),deptno ,job from emp group by deptno,job;
--显示平均工资低于2000的部门号和它的平均工资
select avg(sal),max(sal),deptno from emp group by deptno having avg(sal)>2000;
--总结
--分组函数只能出现在选择列表，having,order by子句中
--在select中使用的顺序group by>having>order by
select avg(sal),max(sal),deptno from emp group by deptno having avg(sal)>2000 order by avg(sal) desc;
--//////////////////////////////////
--多表查询 
--显示雇员名，雇员工资，及所在部门名称,要用到emp表和dept表
select ab.ename ,ab.sal ,ac.dname from emp ab,dept ac where ab.deptno=ac.deptno
--如何显示部门号为10的部门名，员工名和工资
select a1.ename,a2.dname,a1.sal from emp a1,dept a2 where a1.deptno=a2.deptno and a1.deptno=10
--显示雇员名，雇员工资，及所在部门的名字，并按照部门排序
select a1.ename,a1.sal,a2.dname from emp a1,dept a2 where a1.deptno=a2.deptno order by a1.deptno;
--自连接
--显示某个员工的上级 'FORD'
select worker.ename "员工",boss.ename "上级" from emp worker ,emp boss where worker.mgr=boss.empno and worker.ename='FORD';
--子查询
--多列子查询
---查询和smith部门号，工作职位一样的员工
select * from emp where (deptno,job)=(select deptno,job from emp where ename='SMITH')
--如何显示高于自己部门平均工资的员工的信息
--1.各个部门的平均工资
select avg(sal) mysal ,deptno from emp group by deptno
--2.把上面的查询看做是一张子表
select * from emp a2,(select avg(sal) mysal ,deptno from emp group by deptno) a1 where a2.deptno=a1.deptno and a2.sal>a1.mysal;
---////////////////////////////////////////
--oracle分页，一共有三种
--1.rownum分页
select * from emp;
--2.显示rownum
select a1.* ,rownum rn from (select * from emp) a1 ;
--3.分页 
--a,指定查询列，只需改变最里面的
--b,排序,也只需修改最里面的查询
select a2.* from (select a1.* ,rownum rn from (select ename,sal from emp order by sal) a1 where rownum<9)a2 where rn>=6;
--/////////////////////
--用查询结果创建一张新表
create table myemp2(id,ename,sal)as select empno,ename,sal from emp;
desc myemp2;
select * from myemp2;
--合并查询
--为了合并多个select语句的结果，可以使用集合操作符号 union ,union all,minus,intersect
--union 改操作符号用于取得两个结果的的并集，当使用该操作符号时会自动去掉结果集合中的重复行
--union 改操作符号和union相似，但不会取消重复行，而且不会排序
--minus  改操作符号，用于取得两个结果集的差集，只会显示在第一个集合的，而不存在第二个集合中的数据