-注释
/*
数据定义语言 DDL：create(创建) alter(修改) drop(丢弃)
数据操纵语言 DML：insert(增) update(改) delete(删)
数据查询语言 DQL：select(查)基本查询语句 order by(排序) group by(分组)
数据控制语言 DCL：grant(授权) revoke(回收)
事务控制语言 TCL：commit(提交) rollback(回滚) savepoint(存储点)
*/
–语法结构：创建用户
create user 用户名 identified by 口令 [account lock|unlock];
–ex:
create user tom
identified by tom
account unlock;

–语法结构：修改密码
alter user 用户名 identified by 新密码;

–语法结构：修改用户处于锁定、非锁定状态
alter user 用户名 account lock|unlock;

–语法结构：删除用户
drop user 用户名 cascade; --如果需要删除的用户下没有数据对象，可以省略cascade关键字

/*权限->角色->用户
系统把权限赋给角色，然后把角色赋给用户
connect：连接数据的角色
resource：资源角色
dba：最高权限
*/
–语法结构：授予权限
grant 角色|权限 to 用户|角色;
–ex:
grant connect, resource to tom;

–语法结构：回收权限
revoke 角色|权限 from 用户|角色;

–练习
– 新建一个用户 tom，密码设置为 love

– 给用户 tom 授权登录，访问资源的权限

– 锁定 tom 用户的账户，使其不能登录

– 给 tom 用户解锁

– 收回 tom 用户的登录、访问资源的权限

– 删除用户 tom

---

–语法结构：创建表
create table 表名(
列名1 类型 [null | not null] [constraint],
列名2 类型
);
–ex:
create table test(
name varchar2(6),
sex varchar2(4),
age number(3)
);

–语法结构：修改表名
rename 原表名 to 新表名;
–ex:
rename test to test10;

–语法结构：添加列
alter table 表名 add 列名 列类型 约束;

–语法结构：添加多列
alter table 表名 add (列名1 列类型 约束, 列名2 列类型 约束);
–ex:
alter table test10 add (
fname1 char(10),
fname2 varchar2(10)
);

–语法结构：删除列
alter table 表名 drop column 列名;
–ex:
alter table test10 drop column fname2;

–语法结构：删除表
drop table 表名;
–ex:
drop table test10;

–语法结构：创建约束
alter table 表名 add constraint 约束名 约束内容;
–ex：创建学生信息表与约束
create table infos(
stuid varchar2(7) primary key, – 主键约束
name varchar2(10) not null, --非空约束
sex varchar2(3) check(sex = ‘男’ or sex = ‘女’), --检查约束
seat number(10) unique, – 唯一约束
address varchar2(50) default ‘地址不详’ --默认约束
);

–主键(主表)和外键(从表)的类型必须保持一致
–1.从表中外键的字段必须来源于主表.
–2.对主表执行删除操作时，如果删除的主键值在从表中出现，那么就删除失败

–ex：创建成绩表
create table scores(
–id varchar2(7) not null references infos(stuid), 创建表时即设置外键约束
id varchar2(7),
term varchar2(5),
score number(4)
);

alter table scores
add constraint ck_scores_score check(score>=0 and score<=100); --检查约束

alter table scores
add constraint fk_scores_id foreign key(id) references infos(stuid); --外键约束

alter table SCORES modify SCORE not null; --添加非空约束
alter table SCORES modify SCORE default 0; --添加默认约束

–语法结构：删除约束
alter table 表名 drop constraint 约束名;

–练习
– 创建表 class_info
/*
字段名 类型 长度 可选值
c_id 字符串 20 主键
c_type 字符串 6 UI or 测试 or 开发
c_position 字符串 20 非空
start_time 日期(date)
c_status 字符串 6 在读 or 毕业
*/
create table class_info(
c_id varchar2(20) primary key,
c_type varchar2(6) check(c_type in (‘UI’, ‘测试’, ‘开发’)),
c_position varchar2(20) not null,
start_time date,
c_status varchar2(6) check(c_status in (‘在读’, ‘毕业’))
);
– 把表 class_info 改成 class_info2
rename class_info to class_info2;

–上面使用system用户
–下面使用scott用户

/*语法结构：基本查询
select *|列名|表达式
from 表名;

select 识别什么列

•      表示表中的所有列
  

列名可以选择若干列，各个列之间中逗号分隔
表达式可以是列名、函数、常数等组成
from 从哪个表查
*/
–ex:查询所有列
select *
from dept;

–ex：选择指定的列
select deptno, loc
from dept;

–ex:使用别名
select d.deptno, d.dname
from dept d;

select d.deptno “部门编号”, d.dname as 部门名称
from dept d;

/*语法结构：基本查询
select *|列名|表达式
from 表名
where 条件
order by 列名;

select 识别什么列

•      表示表中的所有列
  

列名可以选择若干列，各个列之间中逗号分隔
表达式可以是列名、函数、常数等组成
from 从哪个表查
where 查询的条件
order by 在查询结果中排序，默认升序 ASC，降序 DESC
*/
–ex:
select *
from emp e
where e.sal > 2000
order by e.sal asc;
/*操作符
算数运算：+ - * /
关系运算：= > < <= >= != <>
逻辑运算：and or not
字符串连接操作符：||
/
select e.sal, e.sal+100, e.sal-2, e.sal2, e.sal/10
from emp e;

select e.ename, e.sal
from emp e
where e.sal = 3000;

select *
from emp e
where e.sal > 2500 or e.job = ‘MANAGER’;

–别名、表名、列名用双引号，其他用单引号

select e.ename, e.sal, ‘请’||e.ename||‘带上工资’||e.sal||‘来吃饭’
from emp e
where e.sal >= 2500
order by e.sal desc;

–清除重复行：distinct
select distinct e.deptno
from emp e;

–null
select *
from emp
where comm is not null;

select *
from emp
where comm is null;

–in
select *
from emp e
where e.job = ‘CLERK’ or e.job = ‘SALESMAN’;

select *
from emp
where job in (‘CLERK’, ‘SALESMAN’);

select *
from emp
where job not in (‘CLERK’, ‘SALESMAN’);

–between…and…
select *
from emp e
where e.sal >=1000 and e.sal <= 2000;

select *
from emp e
where e.sal between 1000 and 2000;

–模糊查找：like
–语法结构：like
like ‘字符串’ [escape ‘字符’]
/*通配符
% 表示0个或多个任意字符
_ 表示一个任意字符

escape 后面的字符作为转义字符

S% 以S开头的字符串
S% 第二个字符是S的字符串
%S 以S结尾的字符串
'/%’ escape ‘/’ 以 _ 开头的字符串。escape 表示后面的 / 为转义字符。
*/
–ex：
select *
from emp
where ename like ‘S%’;

select *
from emp
where ename like ‘_L%’;

select *
from emp
where ename like ‘%S’;

select *
from emp
where ename like ‘/_%’ escape ‘/’;

select *
from emp e
where e.ename like ‘__中%’;

/*集合运算：要求返回的列数与列的数据类型必须匹配，列名可以不同
intersect：交集
union：并集----不包括重复记录
union all：并集----包括重复记录
minus：补集：Oracle、Mysql
except：补集：MySQL、Microsoft SQL Server
*/
–ex:
select deptno from dept
intersect
select deptno from emp;

select deptno from dept
union
select deptno from emp;

select deptno from dept
union all
select deptno from emp;

select deptno from dept
minus
select deptno from emp;

select deptno from emp
minus
select deptno from dept;
–练习
– 显示薪水大于 2000，且工作类别是 MANAGER 的雇员信息
select *
from emp e
where e.sal > 2000 and e.job = ‘MANAGER’;

– 显示年薪大于 30000，工作类别不是 MANAGER 的雇员信息
select *
from emp e
where e.sal * 12 > 30000 and e.job != ‘MANAGER’;

– 显示薪水在 1500 到 3000 之间，工作类别以“M”开头的雇员信息
select *
from emp e
where e.sal between 1500 and 3000
and e.job like ‘M%’;

– 显示奖金为空并且部门号为 20 或 30 的雇员信息
select *
from emp e
where e.comm is null and e.deptno in (20, 30);

– 显示奖金不为空或者部门号为 20 的雇员信息，要求按照薪水降序排列
select *
from emp e
where e.comm is not null or e.deptno = 20
order by e.sal desc;

– 显示年薪大于 30000 工作类别不是 MANAGER，且部门号不是 10 和 40 的雇员信息，要求按照雇员姓名进行排序
select *
from emp e
where e.sal * 12 > 30000 and e.job != ‘MANAGER’
and e.deptno not in (10, 40)
order by e.ename;

– 选择在部门 30 中员工的所有信息
select *
from emp e
where e.deptno = 30;

– 列出职位为（MANAGER）的员工的编号，姓名
select e.empno, e.ename
from emp e
where e.job = ‘MANAGER’;

– 找出部门 10 中的经理 (MANAGER) 和部门 20 中的普通员工 (CLERK)
select *
from emp e
where (e.deptno = 10 and e.job = ‘MANAGER’)
or (e.deptno = 20 and e.job = ‘CLERK’);

– 找出部门 10 中既不是经理也不是普通员工，而且工资大于等于 2000 的员工
select *
from emp e
where e.deptno = 10 and e.job not in (‘MANAGER’, ‘CLERK’)
and e.sal >=2000;

– 找出没有奖金或者奖金低于 500 的员工
select *
from emp e
where e.comm is null or e.comm < 500;

– 显示雇员姓名，根据其服务年限，将最老的雇员排在最前面
select e.ename
from emp e
order by e.hiredate asc;

– 找出有奖金的员工的不同工作
select distinct e.job
from emp e
where e.comm is not null;

– 找出姓名中不带 R 这个字母的员工
select *
from emp e
where e.ename not like ‘%R%’;

– 显示所有员工，按工作降序排序，若相同，则按工资升序排序
select *
from emp e
order by e.job desc, e.sal asc;

– 查找出不属于任何部门的员工
select *
from emp e
where e.deptno is null;

---

/*多表查询
交叉连接、内连接、外链接：左外、右外、满外
*/
–交叉连接：笛卡尔积（集）
select *
from emp, dept;
–内连接：等值连接
–ex：查询员工所在部门的部门名称
select e.ename, e.deptno, d.dname
from emp e, dept d
where e.deptno = d.deptno;
–语法结构：
select *
from A
join B
on 条件;
–ex:
select e.ename, e.deptno, d.dname
from emp e
join dept d
on e.deptno = d.deptno;

–内连接：非等值连接
–ex：求员工的薪水等级
select e.*, s.grade
from emp e, salgrade s
where e.sal between s.losal and s.hisal;

–内连接：自连接
–查询员工的领导姓名
select 员工表.*, 领导表.ename
from emp 员工表, emp 领导表
where 员工表.mgr = 领导表.empno;

–外连接：左外连接：从A表查数据，左连接B表，A表全部显示
–语法结构：
select *
from A
left outer join B
on 条件;
–ex:
select e.ename, e.deptno, d.dname
from emp e
left join dept d
on e.deptno = d.deptno;

–外连接：右外连接：从A表查数据，右连接B表，B表全部显示
–语法结构：
select *
from A
right outer join B
on 条件;
–ex:
select e.ename, e.deptno, d.dname
from emp e
right join dept d
on e.deptno = d.deptno;

–外连接：满外连接：从A表查数据，满连接B表，A，B表都全部显示
–语法结构：
select *
from A
full outer join B
on 条件;
–ex:
select e.ename, e.deptno, d.dname
from emp e
full join dept d
on e.deptno = d.deptno;

–语法结构：
select *
from A
right outer join B
on AB表关联的条件
left outer join C
on 条件
where 条件
order by 列;

/*子查询：出现在任何地方
单行子查询 =
多行子查询 in
*/
–ex：查询出销售部（SALES）下面的员工信息
select d.deptno
from dept d
where d.dname = ‘SALES’;

select *
from emp e
where e.deptno = (
select d.deptno
from dept d
where d.dname = ‘SALES’
);

/*any
<any 小于最高的 10 20 30 可以确定这个值一定会小于30

any 大于最小的 10 20 30 可以确定这个值一定会大于10
=any 等于任意一个 10 20 30 可以确定这个值一定是10 20 30中的一个
*/
–ex:查询emp表中比销售员（SALESMAN）最高工资低的员工信息
select sal
from emp
where job = ‘SALESMAN’;
select *
from emp e
where e.sal <any (
select sal
from emp
where job = ‘SALESMAN’
);

/*all

all 大于最高的 10 20 30 可以确定这个值一定大于30
<all 小于最低的 10 20 30 可以确定这个值一定小于10
*/
–ex:查询比所有销售员薪水都高的员工信息
select sal
from emp
where job = ‘SALESMAN’;

select *
from emp e
where e.sal >all (
select sal
from emp
where job = ‘SALESMAN’
);

–练习
– 创建一查询，显示与 BLAKE 在同一部门工作的雇员的姓名和受雇日期、部门编号，但是 BLAKE 不包含在内。
select e.ename, e.hiredate, e.deptno
from emp e
where e.deptno = (
select e1.deptno
from emp e1
where e1.ename = ‘BLAKE’
) and e.ename != ‘BLAKE’;

select e.ename, e.hiredate, e.deptno
from emp e
join emp e1 on e.deptno = e1.deptno
and e1.ename = ‘BLAKE’
where e.ename != ‘BLAKE’;

– 显示位置在 DALLAS 的部门内的雇员姓名、工作。
select e.ename, e.job
from emp e
where e.deptno = (
select d.deptno
from dept d
where d.loc = ‘DALLAS’
);

select e.ename, e.job
from emp e
join dept d on e.deptno = d.deptno and d.loc = ‘DALLAS’;

– 显示被 KING 直接管理的雇员的姓名以及薪水。
select ename,sal
from emp
where mgr=(
select empno
from emp
where ename = ‘KING’
);

select e.ename, e.sal
from emp e
join emp e1 on e.mgr = e1.empno and e1.ename = ‘KING’;

– 创建一查询，显示能获得与 SCOTT 一样薪水的其他雇员的姓名、受雇日期以及薪水。
select ename,hiredate,sal
from emp
where sal in (
select sal
from emp
where ename = ‘SCOTT’
) and ename != ‘SCOTT’;

select e.ename, e.hiredate, e.sal
from emp e
join emp e1 on e.sal = e1.sal and e1.ename = ‘SCOTT’
where e.ename != ‘SCOTT’;

– 查找出工资等级不为 4 级的员工的员工名字，部门名字，部门位置
select e.ename, d.dname, d.loc, e.sal, s.*
from emp e
left join dept d on e.deptno = d.deptno
join salgrade s on e.sal between s.losal and s.hisal and s.grade != 4;

---

–增删改
–语法结构：根据查询结果创建表
create table 表名 as select语句;
–ex:
create table empinfo as
select e.ename, d.dname, d.loc, e.sal, s.*
from emp e
left join dept d on e.deptno = d.deptno
join salgrade s on e.sal between s.losal and s.hisal and s.grade != 4;

select * from empinfo;

–ex:复制表结构
create table empinfo1 as
select *
from emp
where 1=2;

select * from empinfo1;

–语法结构：数据插入
insert into 表名(列名1, 列名2,…) values (值1, 值2,…);
–ex:
select * from dept;

insert into dept values(50, ‘保卫部’, ‘深圳’);
insert into dept values(51, ‘保卫部’, null);
insert into dept(deptno, loc) values(52, ‘深圳’);

–语法结构：把查询结果一次插入到表中
insert into 表名 select语句;
–ex:
select * from empinfo1;

insert into empinfo1
select *
from emp
where sal > 2500;

–语法结构：更新数据
update 表名 set 列名1=值, 列名2=值 where 条件;
–ex:
select * from empinfo1;

update empinfo1
set comm=1000
where empno=7788;

–语法结构：删除数据
delete from 表名 where 条件;
–ex:
delete from empinfo1
where comm = 1000;

–语法结构：删除全部数据：高度危险，无法回滚
truncate table 表名;
–ex:
select * from empinfo1;

truncate table empinfo1;

–练习
– 将 EMP 表中工资大于 2000 的员工数据，插入到 BONUS 表中
select * from bonus;

insert into bonus
select e.ename, e.job, e.sal, e.comm
from emp e
where e.sal > 2000;

/*事务
commit 提交
savepoint 设置保存点
rollback 回滚
rollback to A1 回滚到保存点A1

Oracle的事务开始于一个DML语句。
当以下情况发生时，事务结束：
1、COMMIT/ROLLBACK
2、执行了DDL/DCL语句
*/
select * from empinfo1;

insert into empinfo1
select *
from emp
where sal > 2500;

update empinfo1 set comm = 5 where deptno = 20;
savepoint A1;
update empinfo1 set comm = 5.1 where deptno = 20;
savepoint A2;
update empinfo1 set comm = 5.3 where deptno = 20;
savepoint A3;

rollback to A1;
rollback;1

commit;

/*数值函数
abs 绝对值
ceil 大于等于输入参数的最小整数
floor 返回小于等于x的最大整数值
round 返回四舍五入后的值
trunc 返回x按精度y截取后的值
*/
select abs(100), abs(-100), abs(‘100’)
from dual;

select ceil(10), ceil(‘10.5’), ceil(-10.2)
from dual;

select floor(3.1),floor(2.8+1.3),floor(0), floor(-10.2)
from dual;

select round(5555.6666, 2.1),round(5555.6666, -2.6) ,round(5545.6666, -2.6), round(5555.6666)
from dual;

select trunc(5555.66666, 2.1),trunc(5555.66666,-2.6),trunc(5555.033333) from dual;

/*字符函数
concat 字符拼接
instr 在一个字符串中搜索指定的字符,返回发现指定的字符的位置;
length 字符串的长度
lower 将所有字母小写
upper 将所有字母大写
initcap 首字母大写
ltrim 删除左边空格
rtrim 删除右边空格
trim 去除指定字符串string的左右空格
replace 替换字符串
substr 截取字符串
*/
select concat(‘Hello’, ‘world’), concat(‘我’, ‘爱你’) from dual;
select concat(‘Hello’, INITCAP(‘world’)), concat(‘我’, ‘爱你’) from dual;
select instr(‘Hello world’, ‘or’) from dual;
select length(‘Hello’), length(‘我爱你’) from dual;
select lower(‘AaBbCcDd’)AaBbCcDd from dual;
select upper(‘AaBbCcDd’) upper from dual;
select initcap(ename) from emp;
select ltrim(’ hello ‘), ltrim(’-----hello----’, ‘-’) from dual;
select rtrim(’ hello ‘), rtrim(’-----hello----’, ‘-’) from dual;
select TRIM(‘X’ from ‘XXXgao qian jingXXXX’),TRIM(’ XXXgaoXXjingXXXX ') text from dual;
select replace(‘he love you’,‘he’,‘i’) test from dual;
select substr(‘123456789000’, 3, 8), substr(‘123456789000’, 3) test from dual;

/*日期函数
sysdate
last_day
add_months
months_between
/
select sysdate, last_day(sysdate), add_months(sysdate, 2) from dual;
select sysdate + 2, sysdate + 1/242 from dual;
select months_between(sysdate, to_date(‘2017-1-12’, ‘yyyy-mm-dd’)) from dual;

/*转换
to_char
to_date
to_number
*/
select to_char(sysdate, ‘yyyy-mm-dd hh24:mi:ss’), to_char(-123456789, ‘L999,999,999’) from dual;
select to_date(‘2018-07-12 16:43:56’, ‘yyyy-mm-dd hh24:mi:ss’) from dual;
select to_number(‘123465’), to_number(’ -￥123,456,789’, ‘L999,999,999’) from dual;
/*null
nvl(expr1,expr2) 如果expr1为null,则返回expr2,否则返回expr1
nvl2(expr1,expr2,expr3) 如果expr1为null,则返回expr3,否则返回expr2
*/
–ex：对薪水是2000以下的员工，如果没有奖金，每人奖金100元
select e.ename, e.sal, e.comm, nvl(e.comm, 100)
from emp e
where e.sal < 2000;
–ex：对薪水是2000以下的员工，如果没有奖金，每人奖金100元，如果有奖金，则在原有基础上扣50
select e.ename, e.sal, e.comm, nvl2(e.comm, e.comm-50, 100)
from emp e
where e.sal < 2000;

–练习
– 找出每个月倒数第三天受雇的员工（如：2009-5-29）
/*
1、求倒数第一天
*/
select *
from emp e
where e.hiredate = last_day(e.hiredate) - 2;

– 找出 25 年前雇的员工
/求日期之间月份/
select *
from emp e
where e.hiredate <= add_months(sysdate, -25*12);

select *
from emp e
where months_between(sysdate, e.hiredate) / 12 >= 25;

– 所有员工名字前加上 Dear , 并且名字首字母大写
select e.*, concat(‘Dear’, initcap(e.ename))
from emp e;

– 找出姓名为 5 个字符的员工
select *
from emp e
where length(e.ename) = 5;

– 显示所有员工的姓名的第一个字
select e.ename, substr(e.ename, 1, 1)
from emp e;

– 找到 2 月份受雇的员工
select *
from emp e
where to_char(e.hiredate, ‘mm’) = 2;

– 对薪水是 2000 元以下的员工进行查询，如果没有发奖金，每人奖金 100 元。
select e.*, nvl(e.comm, 100)
from emp e
where e.sal < 2000;

– 对 EMP 表中薪水为 2000 元以下的员工进行查询，如果没有奖金，则显示奖金为 200 元，如果有奖金，则在原来的奖金基础上加 100 元。
select e.*, nvl2(e.comm, e.comm+100, 200)
from emp e
where e.sal < 2000;

---

/*聚合函数（聚组函数–单组分组函数）:会忽略列中的空值
avg
sum
min
max
count
/
–ex:
select avg(nvl(e.comm, 0)), sum(e.comm), min(e.comm), max(e.comm), count(e.comm), count()
from emp e;

–ex：求各部门的平均薪水
select e.deptno, avg(e.sal)
from emp e
group by e.deptno;

–ex：求各部门的平均薪水大于1700的部门
select e.deptno, avg(e.sal)
from emp e
group by e.deptno
having avg(e.sal) > 1700;

–ex：求除了部门20之外，各部门平均薪水大于1700的部门
select e.deptno, avg(e.sal)
from emp e
group by e.deptno
having avg(e.sal) > 1700 and e.deptno != 20;

select e.deptno, avg(e.sal)
from emp e
where e.deptno != 20
group by e.deptno
having avg(e.sal) > 1700;

–练习
– 列出至少有一个员工的所有部门。
select *
from dept d
where d.deptno in (
select e.deptno
from emp e
group by e.deptno
having count(e.ename) > 0
);

– 查询出 KING 所在部门的工作年限最大的员工名字
select e.ename
from emp e
where e.deptno = (
select deptno
from emp
where ename = ‘KING’
) and e.hiredate = (
select min(e1.hiredate)
from emp e1
where e1.deptno in (
select deptno
from emp
where ename = ‘KING’
)
);

– 查询出 KING 所在部门的部门号、部门名称、部门人数
select d.deptno, d.dname,t. 人数
from dept d, (
select e.deptno, count(*) 人数
from emp e
where e.deptno = (
select deptno
from emp
where ename = ‘KING’
)
group by e.deptno
) t
where d.deptno = t.deptno;

– 算出部门 30 中得到最多奖金的员工姓名
select e.ename
from emp e
where e.comm = (
select max(comm)
from emp
where deptno = 30
) and e.deptno = 30;

– 统计各部门下平均工资大于 500 的部门
select e.deptno, avg(e.sal)
from emp e
group by e.deptno
having avg(e.sal) > 500;

– 统计各工作下平均工资大于 500 的工作
select e.job, avg(e.sal)
from emp e
group by e.job
having avg(e.sal) > 500;

---

–语法结构：查询
select [distinct] *
from A
join B
on 表之间关联的条件
right outer join C
on 表之间关联的条件
left outer join D
on 表之间关联的条件
full outer join E
on 表之间关联的条件
where 结果筛选条件
group by 分组列
having 分组后结果筛选条件
order by 排序列 [asc|desc];

---

–语法结构：case：多分支条件
case
when 条件1 then 语句1
when 条件2 then 语句2
when 条件3 then 语句3
else 语句4
end;
–ex:
select e.ename, e.sal, (
case
when e.sal > 3500 then ‘高工资’
when e.sal > 2000 then ‘中等工资’
else ‘低等工资’
end
) 工资等级
from emp e;
–ex:
select e.deptno, (count(
case
when e.sal <= 2000 then e.sal
else null
end
)) “<=2000人数”, (count(
case
when e.sal > 2000 then e.sal
else null
end
)) “>2000的人数”
from emp e
group by e.deptno;

---

/*伪列：只能查询，不能增删改
rowid：该行数据在数据文件中的物理地址，插入数据时自动生成
rownum：查询数据时生成，表明查询结果中行的次序
*/

select e.*, rowid, rownum
from emp e;

–ex：查询出员工表中前5名员工信息
select *
from emp e
where rownum <= 5;

–ex：分页查询：查询薪水最高的前5名员工信息
select *
from (
select *
from emp
order by sal desc
)
where rownum <= 5;

–ex：分页查询：查询薪水最高的第6名到第11名的员工信息
select *
from (
select e.*, rownum R
from (
select *
from emp
order by sal desc
) e
)
where R between 6 and 11;

–练习
– 按部门统计员工数，查出员工数最多的部门的第二名到第五名（列出部门名字，部门位置）
/*
1、按部门编号统计人数
2、根据人数排序
3、加入行号–名次
4、关联部门表
5、筛选名次为第2到第5
/
select d.dname, d.loc
from (
select rownum no, deptno
from(
select deptno, count() 人数
from emp
group by deptno
order by 人数 desc
)
) t, dept d
where no between 2 and 5
and t.deptno = d.deptno;

– 查找出部门 10 和部门 20 中，工资最高第 3 名到第 5 名的员工的员工名字，部门名字，部门位置
/*
1、查询部门10与部门20的员工信息
2、根据工资排序
3、给结果添加行号–次序
4、关联部门表
5、筛选次序为第3到第5
*/
select d.dname, d.loc
from dept d, (
select rownum no, ename, deptno
from (
select *
from emp
where deptno in (10, 20)
order by sal desc
)
) t
where d.deptno = t.deptno
and t.no between 3 and 5;

---

/*视图：预定义的查询，可以作为表一样的查询使用。是一张虚拟表
使数据简单化
使数据独立化
使数据更安全
/
–语法结构：创建视图
create [or replace] [[no] force] view 视图名
as
select查询
[with read only];
/
[or replace] 表示新建视图可以覆盖同名视图
[[no] force] 表示是否强制创建视图
[with read only] 表示视图只读
/
–ex
create or replace view empdeptall
as
select e., d.dname, d.loc
from emp e
full join dept d
on e.deptno = d.deptno
with read only;

select * from empdeptall;
–语法结构：删除视图
drop view 视图名;

–语法结构：索引
–当建立一个索引时，必须指定用于跟踪的表名以及一个或多个表列。一旦建立了索引，在用户表中建立、更改和删除数据库时， Oracle就自动地维护索引。
create [unique] index 索引名称 on 表名(列1,列2);
–ex:
create index idx_loc on empinfo(loc);
create index idx_dname on empinfo(lower(ename));

–语法结构：删除索引
drop index 索引名;

---

/*存储过程：数据库开发
一段存储在数据中执行某种功能的程序，其中包括一条或多条SQL语句。
*/
create or replace procedure sp_update_sal(name in varchar2)
– 通知Oracle数据库创建一个叫 sp_update_sal 存储过程，如果已经存在就覆盖
is – 表示后面将跟随一个PL/SQL体
begin – 表示PL/SQL体的开始
update emp set sal = sal +1
where ename = name;
commit;
end; --表示PL/SQL体 结束

–需要在命令窗口执行
exec sp_update_sal(‘KING’);

–pl/sql使用:
begin
sp_update_sal(‘KING’);
end;

select *
from emp
where ename = ‘KING’;

---

Oracle数据库