Oracle表的创建和基本查询

创建一张表
create table users(
 id number ,
 name varchar2(32),
 password varchar2(32),
 birthday date
);

Oracle 数据类型

{ character_datatypes
| number_datatypes
| long_and_raw_datatypes
| datetime_datatypes
| large_object_datatypes
| rowid_datatypes
}

① char(size):存放字符串，最大2000个字符，是定常
   查看一个列的详情
 select name,dump(name) from test1;
② varchar2(size) 变长 ,最大可以存放4000个字符
③ nchar(n) Unicode数据类型，定常最大2000个字符
   nvarchar2(n)Unicode数据类型，定常最大4000个字符
 
  一个汉字，占用nchar的一个字符空间。一个汉字，占用char的两个字符空间
④ clob 字符型大对象，最大8tb
   blob 二进制数据，可以存放图片/声音8tb

⑤number(p,s) 数值类型，p为整数位，s为小数位 1<=p<=38,  -84<=s<=127
⑥date timestamp 时间类型，包含年月日时分秒，默认格式day-mon-yyyy

字段                   字段类型
Id          整形
name   字符型
sex   字符型
birthday  日期类型
fellowship  小数型
resume   大文本型 

--建立班级表
create table student
(
       id number,
       name varchar2(64),
       sex char(2),
       birthday date,
       fellowship number(10,2),
       resume clob
      
)

--建立班级表
create table classInfo(
       id number,
       name varchar2(32)
)

--添加一个字段
alter table tablename add (columnname datatype);
--修改一个字段
alter table tablename modify(columnname datatype);
--删除一个字段
alter table tablename drop column (columnname);
--修改表的名称
rename 表名 to 新表名

--查看表的结构
desc tableName

--删除表的数据有两种方式
① delete from tablename
② truncate table tablename --速度快。不能回滚回复的

查询出emp表中每个员工的年薪（基本工资+奖金）

SQL> select ename  ,(sal+nvl(comm,0))*12 as 年薪 from emp;
 
ENAME         年薪
---------- ----------
SMITH       9600
ALLEN       22800
WARD       21000
JONES      35700
MARTIN      31800
BLAKE       34200
CLARK      29400
KING        60000
TURNER      18000
JAMES      11400
FORD        36000
MILLER      15600
 
nvl(comm,0)函数 表示如果奖金为空则取0

--添加自动增长列
create sequence seq_test
 minvalue 1
 maxvalue 999999999
 start with 1
 increment by 1
 cache 20
 cycle;

insert into table1 values(seq_test.nextval, '2222');

设置当前会话的日期表现格式
alter session set nls_date_format='YYYY-MM-DD';
查看当前日期
select sysdate from dual;

向表中添加日期格式
create table test3
(
       id number primary key,
       name varchar2(32),
       birthday date
)

insert into test3(id,name,birthday) values(1001,'tanwei','20-8月-92');
insert into test3(id,name,birthday) values(1002,'martin',TO_DATE('1992-08-20','YYYY-MM-DD'));

select * from test3;

---按照指定格式查看日期
select to_char(sysdate,'YYYY-MM-DD')as 当前日期 from dual;
select to_char(sysdate,'YYYY-MM-DD hh24:mi:ss')as 当前日期 from dual;

select ename,sal from emp where sal<= all(select sal from emp where deptno=10);

--根据已有的表创建备份的表(含有原始表中所有的数据)
create table empBak as select * from emp;
--如果只需要表的结构不需要数据只需在创建时
create table empBak as select * from emp where 1=2;

--一个树状结构的查询显示，并在查询中显示层级
CREATE TABLE ORG
(
 ORGID NUMBER PRIMARY KEY,
 PARENTID NUMBER,
 ORGNAME VARCHAR2(32)
);
SELECT * FROM org

INSERT INTO ORG VALUES (1,NULL,'徐东校区');
INSERT INTO ORG VALUES (2,1,'学术部');
INSERT INTO ORG VALUES (3,2,'一学期学术部');
INSERT INTO ORG VALUES (4,2,'二学期学术部');
INSERT INTO ORG VALUES (5,2,'三学期学术部');

SELECT LEVEL ,ORGID,PARENTID,ORGNAME FROM ORG
CONNECT BY PRIOR ORGID=PARENTID
START WITH ORGID=1

1 1  徐东校区
2 2 1 学术部
3 3 2 一学期学术部
3 4 2 二学期学术部
3 5 2 三学期学术部
-------------------------------------------------

集合查询

并集查询
--将两张表的记录联合起来查询去除重复
union
--将两张表联合起来查询并记录重复记录
union all

select * from table_a
union all
select *from table_b

交集查询  会在两个查询结果中找到相同的记录

select * from table_a
intersect
select * from table_b

--割集 从第一个结果集合中减去第二个结果集合中存在的记录
select * from table_a
minus
select * from table_b

---------------------------------------------------------
  函数

①单行函数 见word 《oracle+110个常用函数和错误代码.doc》
②排序函数
 row_number(顺序排序函数)
 rank(排序存在相同值给出相同的序号，同时空留出预留序号)
 dense_rank(排序存在相同值给出相同的序号，但不空留出预留序号)

③窗口函数
 如果要对一个序列排序，首先要知道在哪个范围内排序，还要知道根据哪个字段排序，是升序还是降序

窗口函数的语法
over (partition by 分组字段名 order by 排序字段名 | [desc])
例如要按照部门分组对工资进行升序的窗口函数:
OVER(partition by depton order by sal)
窗口函数在查询语句中紧跟着排序函数 如果不需要分组可以忽略partition by

--emp表中按照部门分组，工资排序
SELECT ROW_NUMBER() OVER(PARTITION BY EMP.DEPTNO ORDER BY SAL) AS EMPINDEX,EMP.*,DEPT.DNAME FROM

EMP
INNER JOIN DEPT ON DEPT.DEPTNO = EMP.DEPTNO
--按照工资排序查询表中第6到第10条数据

SELECT * FROM
(SELECT row_number() over(ORDER BY sal) AS idx,empno,ename,sal,deptno FROM  emp)
WHERE idx BETWEEN 2 AND 5

--编写语句显示生日在1984年7月1日之前学生的详细信息，如果日期晚于'01-7月-1984'则四舍五入到下一年
select stuno,stuname ,stuaddress,round(studate,'year') from student where studate<'01-7月-1984'

--查询入职时间大于1987年的所有员工
select * from emp where emp.hiredate>to_date('1987','yyyy');

--emp表中每个月工资大于SMITH的员工
 SELECT ename,sal + NVL(comm, 0),job,e.deptno ,d.dname FROM emp e,dept d
  WHERE e.deptno = d.deptno AND sal + NVL(comm, 0) > 
  (SELECT sal + NVL(comm,0)  FROM emp WHERE ename = 'SMITH') ;

--30年前入职的员工
SELECT ename,hiredate  FROM emp  WHERE SYSDATE - hiredate> 365*30;

--在当前日期上加上七天
select to_char((sysdate+7),'yyyy-mm-dd') from dual

--显示empno为 123，342，800的员工
select * from emp where empno in(123,342,800)

--查询工资高于500或是岗位为MANAGER 的雇员。同时还要满足他们的姓名首写字母为大写
select * from emp where (emp.sal>500 or job='MANAGER') and ename like 'S%'
--求出工资在公司的平均工资之上的人的信息
select * from emp where sal > (select avg(sal) from emp );
--求出每个人的名字，工资，职位，编号，以及对应的直接领导的名字，工资，职位，编号信息
select e.ename,e.sal,e.job,e.empno,e1.ename,e1.sal,e1.job,e1.empno
  from emp e join emp e1 on e.mgr = e1.empno
--查出名字里面第二个字母不是A的人的信息以及所在的部门情况以及工资的等级情况

方法一： select e.* ,dname,loc ,grade
 from emp e,dept d,salgrade s
 where e.deptno = d.deptno and e.sal between s.losal and s.hisal
 and upper(e.ename) not like '_A%'

方法二： select e.* ,dname,loc ,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 substr(upper(ename),2,1) != 'A';

每个人和对应的经理人的信息（包含king）

select e.* from emp e left join emp m on e.mgr = m.empno

求出每个部门平均薪水等级

select grade ,deptno,avg_sal from (
  select avg(sal) avg_sal,deptno from emp group by deptno) join salgrade
  on avg_sal between losal and hisal

求出每个部门平均薪水等级

select grade ,deptno,avg_sal from (
  select avg(sal) avg_sal,deptno from emp group by deptno) join salgrade
  on avg_sal between losal and hisal;

 求平均薪水最高的部门编号
 select deptno from (
  select deptno, avg(sal) avg_sal from emp group by deptno)
 where avg_sal = (select max(avg(sal)) from emp group by deptno);

求平均薪水最高的部门名称
 select dname from dept
  where deptno = ( select deptno from (
   select e1.deptno, avg(sal) avg_sal from emp e1 group by e1.deptno) e
   where avg_sal = (select max(avg(sal)) from emp group by deptno));

 求平均薪水等级最低的部门部门名称

要想求平均薪水最低的部门名------->首先要求平均薪水最低的部门的部门号 -------->最低的平均薪水级别

------->每个部门的平均薪水级别------>求每个部门的平均薪水

1 每个部门的平均薪水

select avg(sal) ，deptno from emp
 group by deptno;

2  每个部门的平均薪水级别

select grade,deptno from (select avg(sal) avg_sal,deptno from emp
 group by deptno) a,salgrade s
 where avg_sal between s.losal and s.hisal;

3 最低平均薪水级别

select min(grade) from
 (select grade,deptno from (select avg(sal) avg_sal,deptno from emp
 group by deptno) a,salgrade s
 where avg_sal between s.losal and s.hisal);

4    平均薪水最低的部门的部门号

select deptno from
 (select grade,deptno from (select avg(sal) avg_sal,deptno from emp
 group by deptno) a,salgrade s
 where avg_sal between s.losal and s.hisal) a
 where grade = (
  select min(grade) from
  (select grade,deptno from (select avg(sal) avg_sal,deptno from emp
  group by deptno) a,salgrade s
  where avg_sal between s.losal and s.hisal));

5   平均薪水最低的部门名
  select dname from dept
  where deptno in(
  select deptno from
  (select grade,deptno from (select avg(sal) avg_sal,deptno from emp
  group by deptno) a,salgrade s
  where avg_sal between s.losal and s.hisal) a
  where grade = (
   select min(grade) from
   (select grade,deptno from (select avg(sal) avg_sal,deptno from emp
   group by deptno) a,salgrade s
   where avg_sal between s.losal and s.hisal)))
  ;

薪水最高的前5个人
 select rownum r,ename,empno,sal from(
  select ename,empno,sal from emp order by sal desc)
  where rownum <6;

 

 *按薪水从高到低排列的第6个到第10个人的信息

 select * from (
  select rownum r,a.* from(
  select ename,empno,sal from emp order by sal desc) a )
  where r between 6 and 10;

 查询出名字中包含a的，并且薪水大于1200，并且入职日期大于1979-01-03的人里面薪水由高到低顺序排名

的3到7人的信息
select * from (
 select rownum r,a.* from(
 select * from emp
 where lower(ename) like '%a%'
 and sal > 1200
 and hiredate > to_date('1979-01-03','yyyy-mm-dd')
 order by sal desc) a )
 where r between 3 and 7;

 查询出名字中包含a的，并且薪水大于1200，并且入职日期大于1979-01-03的人里面薪水由高到低顺序排名

的3到7人的编号，姓名，职位，月薪，年薪，部门名称，所在地以及工资的等级情况
select * from(
 select rownum r,a.* from(
 select empno,ename,job,sal,(sal+nvl(comm,0))*12 annual_sal,dname,loc,grade
 from emp e,dept d,salgrade s
 where e.deptno=d.deptno
 and e.sal between s.losal and s.hisal
 and lower(ename) like '%a%' and sal >1200
 and hiredate > to_date('1979-01-03','yyyy-mm-dd')
 order by sal desc) a)
 where r between 3 and 7;