创建一张表
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;