--************************************************ --2010.9.26 oracle第2天2课
--建表语句
create table student( id number(4), name varchar(20), sex char(2), birthday date, sal number(7,2) );
--修改表的信息
--（1）添加字段
alter table student add (score number); --(2)修改字段长度
alter table student modify (score number(3,1)); --(3)删除一个字段
alter table student drop column score;
--查看表的结构（注意，此命令只有用在命令窗口才能够显示表的结构信息） desc student
--打开显示时间的操作开关（注意，此命令只有用在命令窗口才能够显示表的结构信息, --此命会将后面的查询操作执行时间显示出来） set timing on
--查询
select *from student
--如果查询某个字段为空的信息使用 is关键字 select *from student where birthday is null
--插入（注意：时间的插入写法）
insert into student values(1,'yoyo1','男','1-2月-1988','123.11') --转换时间插入的格式输入法，为中国人习惯型的输入格式 alter session set nls_date_format='yyyy-mm-dd' --转换时间输入格式后的插入写法
insert into student values(1,'yoyo1','男','1988-3-2','123.11')
--修改
update 表名 set 字段名1=值1，字段名2=值2... where 条件
--设置一个保存点 savepoint a --删除表信息
delete from student --回滚 rollback
--回滚后可以查询刚刚删除的信息 select *from student
--删除
--(1)删除表的内容，不会删除自动增长列已经增长到的某个值的记录 delete from student --（2）删除表结构 drop table student
--（3）删除表的内容，速度快，删除后没有日志记录,自动增长列会从1重新开始记录 truncate table student
--********************************************** --2010.9.26 oracle第2天3课
select *from emp select *from dept --查询速度的区别
--使用 * 的速度比指明 字段名 的速度 慢
--查询smith的工作，薪水和部门（注意：SMITH 内容的大小写，关键字不区分大小写） select deptno,job,sal from emp where ename='SMITH'
--查询职工一年的工资（sal*13）
--查询结果一样，问题(如果查询字段中有一个为null值，那么该字段跟其他字段合起来查询就为null值)
select (sal*13)+(comm*13) 年工资 from emp select sal*13+comm*13 年工资 from emp
--解决( nvl(comm,0)意思：如果comm字段为空，那么该字段值就为0，如果不为空那么就以该字段值为准进行下一步运算 )
select sal*13+nvl(comm,0)*13 年工资 from emp
--如何显示工资高于3000的员工
select ename,sal from emp where sal>3000
--如何显示工资在800到1600的员工(注意：使用between 800 and 1600，查询出的结果包含800，和1600的值的)
select ename,sal from emp where sal between 800 and 1600
--如何查找1982.1.1后入职的员工(注意： 时间输入 及使用 大于 符合) select ename,hiredate from emp where hiredate>'1-1月-1982' --或者
alter session set nls_date_format='yyyy-mm-dd'
select ename,hiredate from emp where hiredate>'1982-1-1'
--如何使用like操作符
-- %表示：任意0-多个字符，_表示：任意单个字符，?表示：所有 select ename,sal from emp where ename like 'S%' --查询员工名字第3个字符是O的员工
select ename,sal from emp where ename like '__O%'--此处是2条_(下划线)
--where条件中使用in
--查询员工号为：7369,7499,7521
select ename,empno from emp where empno in(7369,7499,7521) --查询没有上级的员工
select *from emp where mgr is null
--查询工资大于500或者职位为MANAGER，并且名字是以S开头的员工 select *from emp where (sal>500 or job='MANAGER') and ename like 'S%'
--order by (默认asc：低到高，高到底：desc) select *from emp sal order by sal desc
--按照部门号升序而雇员的工资降序排列 select *from emp order by empno asc , sal desc --按照部门号升序而雇员的入职降序排列
select *from emp order by empno asc , hiredate desc
--使用列的别名排序，使用年薪排名
select ename,sal*12 年薪 from emp order by 年薪 asc
select ename,sal*12+nvl(comm,0)*12 年薪 from emp order by 年薪 asc
--分页查询
--复杂查询
--如何显示所有员工的最高，最低工资 select MAX(sal) from emp select MIN(sal) from emp
--查询最高工资，最低工资员工的详细信息(注意:使用子查询--把查询出的结果看做一个结果集，来筛选)
select *from emp where sal=(select max(sal) from emp) --查询工资高于平均工资的员工信息
select *from emp where sal>(select avg(sal) from emp)
--grop by 和 having子句
--如何显示每个部门的最高工资，最低工资,并按照部门编号降序排列
select avg(sal),max(sal),deptno from emp group by deptno order by deptno desc
--显示每个部门的每种岗位的平均工资和最低工资
select avg(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子句中 --顺序：group by,having,order by
--笛卡尔集：多表查询的条件是，至少不能少于表的个数减1
--查询员工的姓名，工资，工资级别 select *from salgrade select *from emp select *from dept
select a.ename,a.sal,b.grade from emp a,salgrade b where a.sal between b.losal and b.hisal
--显示雇员名，工资，及所在部门名字，并按部门排序
select a.ename,a.sal,b.dname from emp a,dept b where a.deptno=b.deptno order by b.deptno
--自连接(单独一个表多次利用自己)
--如何显示某个员工的上级领导的名字(难点!!! 把一张表当2张表使用) select a.ename,b.ename from emp a,emp b where a.mgr=b.empno
--子查询又称嵌套查询（一个查询语句里面出现多个select关键字，也就是一个以查询结果为条件的语句）
--如何显示与smith同一部门的所有员工
--数据库在执行sql语句时，是从左到右扫描执行，而写sql语句又是从右往左写的
--写sql语句时，如果条件是多个，那么把第一次查询出来结果最多的那个放在左边，提高查询性能。最好从多的开始查询再到少的查询
select ename 名字,deptno 同一部门编号 from emp where deptno=(select deptno from emp where ename='SMITH')
--多行子查询
--如何查询和部门10相同的雇员的名字、岗位、工资、部门号
--返回的是多行的语句，那么条件使用in关键字，而不用 ‘ = ’ select *from emp where job in (select job from emp where deptno=10)
--使用all关键字
--如何查询工资比部门30的所有员工的工资高的员工的姓名、工资、部门号 --方法一
select ename,sal,deptno from emp where sal >all (select sal from emp where deptno=30) --方法二(查询快点:原因是在第二次筛选结果集中他的筛选的范围要小店)
select ename,sal,deptno from emp where sal >all (select max(sal) from emp where deptno=30)
--any关键字的使用
--如何显示工资比部门30号的任意一个员工工资高的员工姓名，工资，部门号 --方法一
select ename,sal,deptno from emp where sal >any (select sal from emp where deptno=30) --方法二(查询快点:原因是在第二次筛选结果集中他的筛选的范围要小店)
select ename,sal,deptno from emp where sal >any (select max(sal) from emp where deptno=30)
--多列子查询(如果根据查询的结果集里面包含2列，而不是1列) --查询与smith的部门和岗位完全相同的所有雇员 --注意：列匹配顺序要一样
select *from emp where (deptno,job)=(select deptno,job from emp where ename='SMITH')
--如何显示高于自己部门平均工资的员工的信息(--难点 --)
--1.首先查询各个部门的平均工资和部门号（其中 a 代表平均工资的 别名） select deptno,avg(sal) a from emp group by deptno
--2.把上面的查询结果看做是一张 子表 ,并给他取个别名(其中 a1 代表上面所查询的结果的表的别名)
--给表取别名不能加as，给列取别名可以加as
select ename,a2.sal,a2.deptno,a1.a from emp a2,(select deptno,avg(sal) a from emp group by deptno) a1
where a2.deptno=a1.deptno and a2.sal>a1.a order by deptno
--分页查询（oracle分页一共有3种方式） --按雇员的id号升序查询 --方法一：rownum分页
select a1.*,rownum rn from (select *from emp) a1
--问题：如何显示第6条到10条的记录？使用分页显示 --1.rownum分页( select *from emp )
--2.显示 rownum[oracle分配的] 行号( select a1.*,rownum rn from (select *from emp) a1 ) --3.显示某一部分的信息（rownum只能用一次，不能用多次，使用多次试不会查询出数据结果的）
select a1.*,rownum rn from (select *from emp) a1 where rownum<=10
--不能写成如下这样：select a1.*,rownum rn from (select *from emp) a1 where rownum<=10 and rownum>=6