oracle基础题
drop table emp;
create table EMP
(
EMPNO NUMBER(4) not null primary key,--员工号
ENAME VARCHAR2(10),--员工名
JOB VARCHAR2(9),--工种
MGR NUMBER(4), --上司员工号
HIREDATE DATE, --任职日期
SAL NUMBER(7,2), --工资
COMM NUMBER(7,2), --津贴
DEPTNO NUMBER(2) --部门号
)
insert into emp values(1,'张三','计算机',1001,to_date('2005-05-26','yyyy-mm-dd'),2000,500,30);
insert into emp values(3,'王五','计算机',1001,to_date('2005-07-07','yyyy-mm-dd'),2600,700,30);
insert into emp values(2,'李四','商业',1002,to_date('2007-04-04','yyyy-mm-dd'),2500,600,31);
insert into emp values(4,'赵六',null,null,to_date('2003-03-04','yyyy-mm-dd'),null,null,null);
---------------------创建用户------------------------------
--配置本地网络服务名
--用scott用户登陆
--创建个人用户
create user rao identified by rao
default tablespace accpt03;
--分配权限
grant connect to rao; --授权连接数据库和创建对象
grant resource to rao; --授权使用表空间
grant oem_monitor to rao; --授权使用企业管理器
grant select on emp to rao; --授权可以查询scott用户的表emp
--请在以后的练习中使用自己创建的用户
select * from scott.emp;
-----------------------第二章------------------------------
--使用伪列：
select * from emp;
select emp.*,rowid from emp;
select emp.*,rownum from emp; --按物理顺序编号
--查找前10条的记录
select emp.*,rownum from emp where rownum<=10;
--按sal升序查找前10条的记录
select * from emp where rownum<=10 order by sal;
select * from emp order by sal;
select * from (select * from emp order by sal)
where rownum<=10;
--查询10--15行的记录???
--通过现有表创建新表
create table emp_20 as select * from emp where deptno=20;
--使用别名
select ename as emp_name from emp;
select ename "emp name" from emp;
select ename as "from" from emp;
--------------事务控制语言----------------------------
select * from emp;
update emp set comm=200 where empno=7369;
savepoint p1;
update emp set comm=comm+100 where empno=7369;
rollback to savepoint p1;
commit;
-------------集合操作符------------------------------
select * from emp;
select * from emp_20;
select * from emp_30;
-- union:查询所有不重复的记录
select * from emp
union
select * from emp_20
union
select * from emp_30;
-- union all: 查询所有记录
select * from emp
union all
select * from emp_20
-- intersect: 查询两个结果集中同时出现的记录
select * from emp
intersect
select * from emp_20
intersect
select * from emp_30;
-- minus: 查询在第一个查询中有,第二个查询中没有的记录
select * from emp
minus
select * from emp_30
union
select * from emp_20;
-- 如何显示'
select 'smith''s' from dual;
select * from dual;
-----------------------------------------------------------------
-----------------日期函数----------------------------------------
-----------------------------------------------------------------
-- add_months[返回日期加(减)指定月份后(前)的日期]
select sysdate S1,
add_months(sysdate,10) S2,
add_months(sysdate,-5) S3 from dual;
-- last_day [返回该月最后一天的日期]
select last_day(sysdate) from dual;
select last_day(to_date('2000-5-5','yyyy-mm-dd')) from dual;
-- months_between[返回日期之间的月份数]
select sysdate S1,
months_between('1-4月-04',sysdate) S2,
months_between('1-4月-04','1-2月-04') S3
from dual
-- next_day(d,day): 返回下个星期几的日期,
-- day为1-7或星期日-星期六,1表示星期日
select sysdate S1,
next_day(sysdate,1) S2,
next_day(sysdate,'星期一') S3 FROM DUAL
-- round[舍入到最接近的日期](day:舍入到最接近的星期日)
select sysdate S1,
round(sysdate) S2 ,
round(sysdate,'year') YEAR,
round(sysdate,'month') MONTH ,
round(sysdate,'day') DAY from dual
-- trunc[截断到最接近的日期]
select sysdate S1,
trunc(sysdate) S2,
trunc(sysdate,'year') YEAR,
trunc(sysdate,'month') MONTH ,
trunc(sysdate,'day') DAY from dual
-- 返回日期列表中最晚日期
select greatest('01-1月-06','04-1月-06','10-2月-06')
from dual;
--------------------------------------------------------------
------------------字符串函数----------------------------------
--------------------------------------------------------------
-- 字符串截取
select substr('abcdef',3,3) from dual;
-- 查找子串位置
select instr('abcfdgfdhd','cfd') from dual
-- 字符串连接
select 'HELLO'||' hello world' from dual;
-- 去掉字符串中的空格
select ltrim(' abc') s1,
rtrim('zhang ') s2,
trim(' zha ng ') s3 from dual;
-- 去掉前导和后缀
select trim(leading 9 from 9998767999) s1,
trim(trailing 9 from 9998767999) s2,
trim(9 from 999965579999) s3 from dual;
-- 返回字符串首字母的Ascii值
select ascii('a') from dual
-- 返回ascii值对应的字母
select chr(97) from dual
-- 计算字符串长度
select length('abcdef') from dual
-- initcap（首字母变大写） ,lower(变小写),upper(变大写)
select lower('ABC') s1,
upper('def') s2,
initcap('efg') s3 from dual;
-- Replace：替换
select replace('abc','b','xy') from dual;
-- translate: 翻译
select translate('abcbctoad','abcd','xy') from dual;
select translate('abcbycaaa','abc','在个人') from dual;
select translate('ab在个人aa在a','在个人','123') from dual;
-- lpad [左添充] rpad [右填充](用于控制输出格式)
select lpad('func',15,'=') s1, rpad('func',15,'-') s2
from dual;
select dname,lpad(dname,14,'=') from dept;
-- decode[实现if ..then 逻辑]
select deptno,deptno from dept;
select deptno,decode(deptno,10,'部门1',
20,'部门2',
30,'部门3',
'其他')
from dept;
-----------------------数学函数-----------------------------
-- 取整函数（ceil 向上取整,floor 向下取整）
select ceil(66.6) N1,floor(66.6) N2 from dual;
-- 取幂(power) 和 求平方根(sqrt)
select power(3,2) N1,sqrt(9) N2 from dual;
-- 求余
select mod(9,5) from dual;
-- 返回固定小数位数 (round:四舍五入，trunc:直接截断)
select round(66.667,2) N1,trunc(66.667,2) N2 from dual;
-- 返回值的符号(正数返回为1,负数为-1)
select sign(-32),sign(293) from dual;
-------------------转换函数------------------------------
-- to_char()[将日期和数字类型转换成字符类型]
select to_char(sysdate) s1,
to_char(sysdate,'yyyy-mm-dd') s2,
to_char(sysdate,'yyyy') s3,
to_char(sysdate,'yyyy-mm-dd hh12:mi:ss') s4,
to_char(sysdate, 'hh24:mi:ss') s5,
to_char(sysdate,'DAY') s6 from dual;
SELECT TO_CHAR(sysdate,'YYYY"年"fmMM"月"fmDD"日" HH24:MI:SS')
FROM dual;
SELECT TO_CHAR(sysdate,'YYYY"年"MM"月"DD"日" HH24:MI:SS')
FROM dual;
select sal,to_char(sal,'$99999') n1,to_char(sal,'$99,999') n2
from emp;
select to_char(deptno,'$9999') from emp;
-- to_date()[将字符类型转换为日期类型]
insert into emp(empno,hiredate)
values(8000,to_date('2004-10-10','yyyy-mm-dd'));
-- to_number() 转换为数字类型
select to_number('1200')+1 from dual;
------------------------其他函数-------------------------
-- user: 返回登录的用户名称
select user from dual;
-- user: 返回登录的用户机器名
select userenv('TERMINAL') from dual;
-- nvl(ex1,ex2): ex1值为空则返回ex2,否则返回该值本身ex1
-- 例：显示员工的实发工资(sal+comm)
select ename,nvl(sal+comm,sal) from emp;
-- nvl2(ex1,ex2,ex3) : 如果ex1不为空，返回ex2,否则返回ex3
-- 如：列出员工名及是否有佣金
select ename,nvl2(comm,'yes','no') from emp;
-- nullif(ex1,ex2): 值相等返回空，否则返回第一个值
-- 例：如果工资和佣金相等，则显示空，否则显示工资
select nullif(sal,comm),sal,comm from emp;
----------------------分组函数---------------------------
-- 求部门30 的最高工资，最低工资,平均工资，总人数，
-- 工种数量及工资总和
select max(sal),min(sal),avg(sal),count(empno),
count(distinct(job)),sum(sal)
from emp;
-- 按部门分组求最高工资，最低工资，总人数，工种数，
-- 及工资总和
select max(sal),min(sal),count(empno),count(distinct(job)),sum(sal)
from emp
group by deptno;
-- 部门工资总和大于10000的各部门的最高工资，最低工资，总人数，
-- 工种数量及工资总和
select max(sal),min(sal),count(empno),sum(sal) from emp
where deptno
in (select deptno
from emp group by deptno
having sum(sal) > 3000)
-------------------------分析函数--------------------------
-- row_number: 按组排名次,排名号唯一
-- 按所有员工的工资排名
select * from emp;
select emp.*,row_number() over(order by nvl(sal,0) desc) "名次" from emp;
-- 分部门进行工资排名
select emp.*,row_number() over(partition by deptno order by sal) "名次" from emp;
-- rank: 按组排名次,同值同号,下一序号跳跃
select emp.*,rank() over(partition by deptno order by sal) "名次" from emp;
-- dense_rank: 按组排名次,同值同号,序号连续
select emp.*,dense_rank() over(partition by deptno order by sal) "名次" from emp;
create table EMP
(
EMPNO NUMBER(4) not null primary key,--员工号
ENAME VARCHAR2(10),--员工名
JOB VARCHAR2(9),--工种
MGR NUMBER(4), --上司员工号
HIREDATE DATE, --任职日期
SAL NUMBER(7,2), --工资
COMM NUMBER(7,2), --津贴
DEPTNO NUMBER(2) --部门号
)
insert into emp values(1,'张三','计算机',1001,to_date('2005-05-26','yyyy-mm-dd'),2000,500,30);
insert into emp values(3,'王五','计算机',1001,to_date('2005-07-07','yyyy-mm-dd'),2600,700,30);
insert into emp values(2,'李四','商业',1002,to_date('2007-04-04','yyyy-mm-dd'),2500,600,31);
insert into emp values(4,'赵六',null,null,to_date('2003-03-04','yyyy-mm-dd'),null,null,null);
---------------------创建用户------------------------------
--配置本地网络服务名
--用scott用户登陆
--创建个人用户
create user rao identified by rao
default tablespace accpt03;
--分配权限
grant connect to rao; --授权连接数据库和创建对象
grant resource to rao; --授权使用表空间
grant oem_monitor to rao; --授权使用企业管理器
grant select on emp to rao; --授权可以查询scott用户的表emp
--请在以后的练习中使用自己创建的用户
select * from scott.emp;
-----------------------第二章------------------------------
--使用伪列：
select * from emp;
select emp.*,rowid from emp;
select emp.*,rownum from emp; --按物理顺序编号
--查找前10条的记录
select emp.*,rownum from emp where rownum<=10;
--按sal升序查找前10条的记录
select * from emp where rownum<=10 order by sal;
select * from emp order by sal;
select * from (select * from emp order by sal)
where rownum<=10;
--查询10--15行的记录???
--通过现有表创建新表
create table emp_20 as select * from emp where deptno=20;
--使用别名
select ename as emp_name from emp;
select ename "emp name" from emp;
select ename as "from" from emp;
--------------事务控制语言----------------------------
select * from emp;
update emp set comm=200 where empno=7369;
savepoint p1;
update emp set comm=comm+100 where empno=7369;
rollback to savepoint p1;
commit;
-------------集合操作符------------------------------
select * from emp;
select * from emp_20;
select * from emp_30;
-- union:查询所有不重复的记录
select * from emp
union
select * from emp_20
union
select * from emp_30;
-- union all: 查询所有记录
select * from emp
union all
select * from emp_20
-- intersect: 查询两个结果集中同时出现的记录
select * from emp
intersect
select * from emp_20
intersect
select * from emp_30;
-- minus: 查询在第一个查询中有,第二个查询中没有的记录
select * from emp
minus
select * from emp_30
union
select * from emp_20;
-- 如何显示'
select 'smith''s' from dual;
select * from dual;
-----------------------------------------------------------------
-----------------日期函数----------------------------------------
-----------------------------------------------------------------
-- add_months[返回日期加(减)指定月份后(前)的日期]
select sysdate S1,
add_months(sysdate,10) S2,
add_months(sysdate,-5) S3 from dual;
-- last_day [返回该月最后一天的日期]
select last_day(sysdate) from dual;
select last_day(to_date('2000-5-5','yyyy-mm-dd')) from dual;
-- months_between[返回日期之间的月份数]
select sysdate S1,
months_between('1-4月-04',sysdate) S2,
months_between('1-4月-04','1-2月-04') S3
from dual
-- next_day(d,day): 返回下个星期几的日期,
-- day为1-7或星期日-星期六,1表示星期日
select sysdate S1,
next_day(sysdate,1) S2,
next_day(sysdate,'星期一') S3 FROM DUAL
-- round[舍入到最接近的日期](day:舍入到最接近的星期日)
select sysdate S1,
round(sysdate) S2 ,
round(sysdate,'year') YEAR,
round(sysdate,'month') MONTH ,
round(sysdate,'day') DAY from dual
-- trunc[截断到最接近的日期]
select sysdate S1,
trunc(sysdate) S2,
trunc(sysdate,'year') YEAR,
trunc(sysdate,'month') MONTH ,
trunc(sysdate,'day') DAY from dual
-- 返回日期列表中最晚日期
select greatest('01-1月-06','04-1月-06','10-2月-06')
from dual;
--------------------------------------------------------------
------------------字符串函数----------------------------------
--------------------------------------------------------------
-- 字符串截取
select substr('abcdef',3,3) from dual;
-- 查找子串位置
select instr('abcfdgfdhd','cfd') from dual
-- 字符串连接
select 'HELLO'||' hello world' from dual;
-- 去掉字符串中的空格
select ltrim(' abc') s1,
rtrim('zhang ') s2,
trim(' zha ng ') s3 from dual;
-- 去掉前导和后缀
select trim(leading 9 from 9998767999) s1,
trim(trailing 9 from 9998767999) s2,
trim(9 from 999965579999) s3 from dual;
-- 返回字符串首字母的Ascii值
select ascii('a') from dual
-- 返回ascii值对应的字母
select chr(97) from dual
-- 计算字符串长度
select length('abcdef') from dual
-- initcap（首字母变大写） ,lower(变小写),upper(变大写)
select lower('ABC') s1,
upper('def') s2,
initcap('efg') s3 from dual;
-- Replace：替换
select replace('abc','b','xy') from dual;
-- translate: 翻译
select translate('abcbctoad','abcd','xy') from dual;
select translate('abcbycaaa','abc','在个人') from dual;
select translate('ab在个人aa在a','在个人','123') from dual;
-- lpad [左添充] rpad [右填充](用于控制输出格式)
select lpad('func',15,'=') s1, rpad('func',15,'-') s2
from dual;
select dname,lpad(dname,14,'=') from dept;
-- decode[实现if ..then 逻辑]
select deptno,deptno from dept;
select deptno,decode(deptno,10,'部门1',
20,'部门2',
30,'部门3',
'其他')
from dept;
-----------------------数学函数-----------------------------
-- 取整函数（ceil 向上取整,floor 向下取整）
select ceil(66.6) N1,floor(66.6) N2 from dual;
-- 取幂(power) 和 求平方根(sqrt)
select power(3,2) N1,sqrt(9) N2 from dual;
-- 求余
select mod(9,5) from dual;
-- 返回固定小数位数 (round:四舍五入，trunc:直接截断)
select round(66.667,2) N1,trunc(66.667,2) N2 from dual;
-- 返回值的符号(正数返回为1,负数为-1)
select sign(-32),sign(293) from dual;
-------------------转换函数------------------------------
-- to_char()[将日期和数字类型转换成字符类型]
select to_char(sysdate) s1,
to_char(sysdate,'yyyy-mm-dd') s2,
to_char(sysdate,'yyyy') s3,
to_char(sysdate,'yyyy-mm-dd hh12:mi:ss') s4,
to_char(sysdate, 'hh24:mi:ss') s5,
to_char(sysdate,'DAY') s6 from dual;
SELECT TO_CHAR(sysdate,'YYYY"年"fmMM"月"fmDD"日" HH24:MI:SS')
FROM dual;
SELECT TO_CHAR(sysdate,'YYYY"年"MM"月"DD"日" HH24:MI:SS')
FROM dual;
select sal,to_char(sal,'$99999') n1,to_char(sal,'$99,999') n2
from emp;
select to_char(deptno,'$9999') from emp;
-- to_date()[将字符类型转换为日期类型]
insert into emp(empno,hiredate)
values(8000,to_date('2004-10-10','yyyy-mm-dd'));
-- to_number() 转换为数字类型
select to_number('1200')+1 from dual;
------------------------其他函数-------------------------
-- user: 返回登录的用户名称
select user from dual;
-- user: 返回登录的用户机器名
select userenv('TERMINAL') from dual;
-- nvl(ex1,ex2): ex1值为空则返回ex2,否则返回该值本身ex1
-- 例：显示员工的实发工资(sal+comm)
select ename,nvl(sal+comm,sal) from emp;
-- nvl2(ex1,ex2,ex3) : 如果ex1不为空，返回ex2,否则返回ex3
-- 如：列出员工名及是否有佣金
select ename,nvl2(comm,'yes','no') from emp;
-- nullif(ex1,ex2): 值相等返回空，否则返回第一个值
-- 例：如果工资和佣金相等，则显示空，否则显示工资
select nullif(sal,comm),sal,comm from emp;
----------------------分组函数---------------------------
-- 求部门30 的最高工资，最低工资,平均工资，总人数，
-- 工种数量及工资总和
select max(sal),min(sal),avg(sal),count(empno),
count(distinct(job)),sum(sal)
from emp;
-- 按部门分组求最高工资，最低工资，总人数，工种数，
-- 及工资总和
select max(sal),min(sal),count(empno),count(distinct(job)),sum(sal)
from emp
group by deptno;
-- 部门工资总和大于10000的各部门的最高工资，最低工资，总人数，
-- 工种数量及工资总和
select max(sal),min(sal),count(empno),sum(sal) from emp
where deptno
in (select deptno
from emp group by deptno
having sum(sal) > 3000)
-------------------------分析函数--------------------------
-- row_number: 按组排名次,排名号唯一
-- 按所有员工的工资排名
select * from emp;
select emp.*,row_number() over(order by nvl(sal,0) desc) "名次" from emp;
-- 分部门进行工资排名
select emp.*,row_number() over(partition by deptno order by sal) "名次" from emp;
-- rank: 按组排名次,同值同号,下一序号跳跃
select emp.*,rank() over(partition by deptno order by sal) "名次" from emp;
-- dense_rank: 按组排名次,同值同号,序号连续
select emp.*,dense_rank() over(partition by deptno order by sal) "名次" from emp;
