sql常用语句

最新推荐文章于 2021-05-31 17:24:03 发布

banlengcheng2628

最新推荐文章于 2021-05-31 17:24:03 发布

阅读量176

点赞数 1

文章标签：数据库 ui

原文链接：http://www.cnblogs.com/nullnullnull/p/11581362.html

版权

--https://liushilive.github.io/
--数据 data
--数据库 DB Database
--数据库管理系统 DBMS
/*学什么
数据定义语言 DDL：create、alter、drop
数据操纵语言 DML：insert、update、delete、select
数据查询语言 DQL：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;

--语法结构：修改用户密码
alter user 用户名 identified by 新密码;
--ex:
alter user tom identified by 123456;

--语法结构：锁定|非锁定用户
alter user 用户名 account lock|unlock;
--ex:
alter user tom account unlock;

--语法结构：删除用户
drop user 用户名 cascade;
--ex:
drop user tom;

/*权限-角色
connect 角色：连接数据库
resource 角色：资源角色
dba 角色：系统管理员
*/
--语法结构：授予权限
grant 角色|权限 to 用户|角色;
--ex:
grant connect, resource to tom;

--语法结构：回收权限
revoke 角色|权限 from 用户|角色;
--ex:
revoke resource from tom;

--练习
-- 新建一个用户 tom，密码设置为 love
create user tom
identified by love;

-- 给用户 tom 授权登录，访问资源的权限
grant connect, resource to tom;

-- 锁定 tom 用户的账户，使其不能登录
alter user tom account lock;

-- 给 tom 用户解锁
alter user tom account unlock;

-- 收回 tom 用户的登录、访问资源的权限
revoke connect, resource from tom;

-- 删除用户 tom
drop user tom;

-------------------------------------------------------------

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

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

--语法结构：添加新列
alter table 表名 add 列名列类型列约束;
--ex:
alter table test10 add fname char(10);

--语法结构：添加多个新列
alter table 表名 add (列名列类型列约束, 列名列类型列约束);
--ex:
alter table test10 add (fname1 char(10), fname2 char(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, --主键约束
stuname varchar2(10) not null, -- 非空约束
sex varchar2(3) check(sex='男' or sex='女'), --检查约束
seat number unique, --唯一约束
address varchar2(20) default '地址不详' --默认约束
);

create table scores(
id varchar2(7),
term varchar2(2),
score number
);

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; -- 添加默认约束

--ex:查询表约束
select *
from all_constraints
where table_name='SCORES';

--语法规则：删除约束
alter table 表名 drop constraint 约束名;
--ex:
alter table scores drop constraint fk_scores_id;

--练习
-- 创建表 class_info
/*
字段名类型长度约束
id varchar2 20 主键
type varchar2 6 UI、测试、开发
position varchar2 20 非空
start_time date
status varchar2 6 在读、毕业
*/
create table class_info(
id varchar2(20) primary key,
type varchar2(6) check(type in ('UI', '测试', '开发')),
position varchar2(20) not null,
start_time date,
status varchar2(6) check(status='在读' or status='毕业')
);

-- 把表 class_info 改成 class_info2
rename class_info to class_info2;

------------------------------------------------------------------
-----------------使用 scott 用户
------------------------------------------------------------------
--语法结构：基本查询
select * | 列名 | 表达式
from 表名;
/*
* 所有列
各个列名之间用逗号分隔

select 识别什么列
from 识别数据来自哪个表
*/
--ex:查询所有列
select *
from dept;

--ex:查询指定列
select deptno, loc
from dept;

--ex:使用别名
select d.deptno "部门编号", d.loc as 部门位置
from dept d;
/*
表名、列名、别名用双引号
字符串用单引号
*/

--语法结构：基本查询
select * | 列名 | 表达式
from 表名
where 条件
order by 列名 [asc | desc];
/*
* 所有列
各个列名之间用逗号分隔

select 识别什么列
from 识别数据来自哪个表
where 查询的条件
order by 排序 asc 升序 desc 降序
*/
--ex:
select *
from emp
where sal > 2000
order by deptno asc;

/*操作符
算术运算：+ - * /
关系运算：= > < != <> >= <=
逻辑运算：and or not
拼接字符串：
Access、Microsoft SQL Server ： +
Oracle、DB2、SQLite ： ||
Mysql 只能使用函数
*/
select e.ename, e.sal, e.sal + 100, e.sal * 1000, e.sal / 10
from emp e;

select *
from emp e
where e.sal = 5000;

select e.ename, e.sal, '我喜欢' || e.ename || '，带上工资' || e.sal || '来约会'
from emp e
where e.sal >= 2500
order by e.sal desc;
----------------------------------------------------
--null
select *
from emp
where comm is null;

select *
from emp
where comm is not null;

select *
from emp
where not comm is null;

------------------------------------------
--in
select *
from emp e
where e.job = 'SALESMAN' or e.job = 'CLERK';

select *
from emp e
where e.job in ('SALESMAN', 'CLERK');

select *
from emp e
where e.job not in ('SALESMAN', 'CLERK');
----------------------------------------------------
--between ... and ...
select *
from emp
where sal >= 1000 and sal <= 2000;

select *
from emp
where sal between 1000 and 2000;
------------------------------------------------------
--模糊查询：like
/*通配符
% 表示 0 个或多个字符
_ 表示一个任意字符
*/
like '字符串' [escape '字符']
/*
S% 以S开头
_S% 第二个字符是S的字符串
%S 以S结尾
'/_%' escape '/' 以 _ 开头的字符串
*/
select *
from emp
where ename like 'S%';

select *
from emp
where ename like '_L%';

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

/*优先级
算术运算
连接运算
关系运算
is null like in
between
not
and
or
*/
-------------------------------------------
--distinct：以行为单位去重
select distinct deptno, job
from emp;
------------------------------------------------
--练习
-- 显示薪水大于 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.job 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);

select *
from emp e
where e.comm is null
and (e.deptno = 20 or e.deptno = 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.job = 'MANAGER' and e.deptno = 10)
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;
select * from dept;
select * from salgrade;

--交叉连接：笛卡尔乘积：cross join：无条件的连接方式
select *
from emp, dept;

select *
from emp
cross join dept;

select *
from emp
cross join dept
cross join salgrade;

--内连接：两张表中同时符合某种条件的数据记录的组合：inner join：等值连接-非等值连接-自连接
select *
from emp e, dept d
where e.deptno = d.deptno;

select *
from emp e
inner join dept d
on e.deptno = d.deptno;

select *
from emp e
inner join salgrade s
on e.sal between s.losal and s.hisal;

select e1.ename 员工姓名, e2.ename 领导姓名
from emp e1
inner join emp e2
on e1.mgr = e2.empno;

select e1.ename 员工姓名, e2.ename 领导姓名
from emp e1
join emp e2
on e1.mgr = e2.empno;

--外连接：左外连接-右外连接
--左外连接：left outer join
--右外连接：right outer join
select *
from emp e
left outer join dept d
on e.deptno = d.deptno;

select *
from emp e
inner join dept d
on e.deptno = d.deptno;

select *
from emp e
right outer join dept d
on e.deptno = d.deptno;

select *
from emp e
right join dept d
on e.deptno = d.deptno
where sal > 3000;

--联合查询：union union all
select deptno from emp
union
select deptno from dept;

select deptno from emp
union all
select deptno from dept;

/* 字段数量与类型必须一致
select ename from emp
union
select deptno from dept;
*/

--全（满）连接：full join
select *
from emp e
full join dept d
on e.deptno = d.deptno;

--总结
select *
from A
join B --内连接
on 表之间关联的条件
left join C --左连接
on 表之间关联的条件
right join D --右连接
on 表之间关联的条件
full join E --全连接
on 表之间关联的条件
where 筛选条件
order by 排序列;

--子查询
/*
单行子查询：最多返回一行结果
多行子查询：返回多行或0行
*/
--ex：查询销售部下的员工信息
select deptno
from dept
where dname = 'SALES';

select *
from emp
where deptno = 30;

select *
from emp
where deptno = (
select deptno
from dept
where dname = 'SALES'
);

/*
ANY 子查询
<any 小于最大的
>any 大于最小的
=any 等于任意一个
*/
--ex：查询比销售员最高薪水低的员工信息
select sal
from emp
where job = 'SALESMAN';

select *
from emp
where sal < 1602;

select *
from emp
where sal <any (
select sal
from emp
where job = 'SALESMAN'
);

/*
all 子查询
>all 大于最大的
<all 小于最小的
*/
--ex:查询比所有销售员薪水都高的员工信息
select *
from emp
where sal >all (
select sal
from emp
where job = 'SALESMAN'
);

--练习
-- 创建一查询，显示与 BLAKE 在同一部门工作的雇员的姓名和受雇日期、部门编号，但是 BLAKE 不包含在内。
--1、查询出 BLAKE 所在部门编号
select deptno
from emp
where ename = 'BLAKE';
--2、查询 30 部门的成员信息
select e.ename, e.hiredate, e.deptno
from emp e
where e.deptno = 30
and e.ename != 'BLAKE';

select e.ename, e.hiredate, e.deptno
from emp e
where e.deptno = (
select deptno
from emp
where ename = 'BLAKE'
) and e.ename != 'BLAKE';

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

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

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

-- 显示被 KING 直接管理的雇员的姓名以及薪水。
select e.ename, e.sal
from emp e
where e.mgr = (
select e1.empno
from emp e1
where e1.ename = 'KING'
);

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

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

select e.ename, e.hiredate, e.sal
from emp e
where e.sal =any (
select sal
from emp
where ename = 'SCOTT'
) and e.ename != 'SCOTT';

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

-- 查找出工资等级不为 4 级的员工的员工名字，部门名字，部门位置
--1、将员工信息与部门信息组合
select *
from emp e
left join dept d
on e.deptno = d.deptno;
--2、将第一步的查询结果作为一张表看待，别名为 e2。结合工资等级表查询
select e2.ename, e2.dname, e2.loc
from (
select *
from emp e
left join dept d
on e.deptno = d.deptno
) e2
join salgrade s
on s.grade != 4
and e2.sal between s.losal and s.hisal;

select e.ename, d.dname, d.loc
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;

--------------------------------------------------------------------
--多分支条件
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;

-----------------------------------------------------
--语法规则：根据查询结果创建表
create table 表名 as select语句;
--ex:
create table empino
as
select e.ename, e.sal, (
case
when e.sal >3500 then '高工资'
when e.sal >2000 then '中等工资'
else '低等工资'
end
) 工资等级
from emp e;

select * from empino;

--语法结构：将查询结果插入表中
insert into 表名 select语句;
--ex:
insert into empino
select e.ename, e.sal, (
case
when e.sal >3500 then '高工资'
when e.sal >2000 then '中等工资'
else '低等工资'
end
) 工资等级
from emp e;

--语法规则：插入数据
insert into 表名 (列名, 列名) values (值, 值);
--ex:
insert into empino values(
'王五', 100, null
);

insert into empino (ename) values(
'李四'
);

--语法规则：更新数据
update 表名 set 列=值, 列=值 where 条件;
--ex:
update empino
set sal = sal+0.1;

update empino
set sal = sal+0.01
where sal>1200;

select * from empino;

--语法规则：删除数据
delete from 表名 where 条件;
--ex:
delete from empino where sal > 1200;
delete from empino;

--语法规则：删除全部数据！！高危!!!!
truncate table 表名;
--ex:
truncate table empino;

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

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

----------------------------------------------------------------------
--单行函数：每行返回一个结果：select、where、order by
--聚合函数：多行返回一个结果：group by、having
--dual表：
select * from dual;
--https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlqr/SQL-Functions.html#GUID-6E9F72DE-6939-4A15-8EB8-B833CBE8733C
--数值型函数
--abs 绝对值
select abs(100), abs(-100), abs('-100')
from dual;
--ceil 向上取整
select ceil(10), ceil(10.5), ceil(10.2), ceil(-10.2)
from dual;
--floor 向下取整
select floor(10), floor(10.2), floor(10.5)
from dual;
--mod 取余
select mod(5, 2), mod(10, 2)
from dual;
--round 四舍五入
select round(100.123456, 4), round(100.456, 2), round(100.12), round(100.52)
from dual;
--trunc 去尾
select trunc(10.1235, 3), trunc(10.123, 2), trunc(10.723)
from dual;

--字符型函数
--concat 拼接
select concat('我的', '爱人')
from dual;

select concat('我爱', e.ename)
from emp e;

--initcap 首字母大写
select initcap('ab cd e')
from dual;

--lower 小写
select lower('Abvfd')
from dual

--upper 大写
select upper('this Is A test')
from dual;

--length 求长度
select length('我爱 any')
from dual;

--substr : 计数从1开始数：从1开始截取一个，从3 开始截取1个
select substr('王五', 1, 1), substr('我爱东，爱我中华', 3, 1)
from dual;

--replace 替换
select replace('我爱你', '爱', '恨')
from dual;

--日期函数
--sysdate 服务器系统时间
select sysdate
from dual;

-- 时间运算
select sysdate, sysdate + 2, sysdate + 1 / 24 * 2, sysdate + 1 / 24 / 60 / 60 * 2
from dual;

--add_months 添加月
select add_months(sysdate, 2)
from dual;

--last_day 最后一天
select last_day(sysdate)
from dual;

--months_between 求两个日期之间的月份数
select months_between(to_date('2020-06-05', 'YYYY-MM-DD'), sysdate)
from dual;

--转换函数
--to_char 转换为字符串
select to_char(16.89), 16.89, to_char(1000000, '999,999,999.99')
from dual;

select to_char(sysdate), to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS'), to_char(sysdate, 'DAY')
from dual;

--to_date 转换为时间
select to_date('2020-06-05', 'YYYY-MM-DD')
from dual;

--to_number 转换为数字
select to_number(' 1,000,000.00', '999,999,999.99')
from dual;

--null函数
--nvl
select e.comm, nvl(e.comm, 0) + 1
from emp e;

--nvl2
select e.comm, nvl2(e.comm, 1, 2)
from emp e;

--练习
-- 找出每个月倒数第三天受雇的员工（如：2009-5-29）
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);

-- 所有员工名字前加上 Dear , 并且名字首字母大写
select e.ename, 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.ename, e.sal, e.comm, nvl(e.comm, 100)
from emp e
where e.sal < 2000;

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

----------------------------------------------------------
--聚合函数：单组分组：默认忽略 null
--avg 平均值
select * from emp;
select avg(e.comm), avg(e.sal)
from emp e;

--group by having：分组查询
select e.deptno, avg(e.sal)
from emp e
group by e.deptno;

select e.deptno,e.job, avg(e.sal)
from emp e
group by e.deptno, e.job;

select e.deptno,e.job, avg(e.sal)
from emp e
where deptno != 30
group by e.deptno, e.job;

select e.deptno,e.job, avg(e.sal)
from emp e
where deptno != 30
group by e.deptno, e.job
having avg(e.sal) > 1500;

--count 计数
select count(*), count(comm), count(distinct sal)
from emp;

--max 最大值
select max(sal)
from emp ;

select *
from emp
where sal = (
select max(sal)
from emp
);

--min 最小值
--sum 求和
select sum(e.sal)
from emp e;

select sum(e.sal), e.deptno
from emp e
group by e.deptno;

--练习
-- 列出至少有 5 个员工的部门信息。
--1、查询至少有5个人的部门编号
select e.deptno
from emp e
group by e.deptno
having count(*) >= 5;
--2、根据部门编号显示部门信息
select *
from dept d
where d.deptno in (30, 20);

select *
from dept d
where d.deptno in (
select e.deptno
from emp e
group by e.deptno
having count(*) >= 5
);

-- 查询出 KING 所在部门的工作年限最大的员工名字
--1、查询king 所在部门编号
select deptno
from emp
where ename = 'KING';
--2、查询该部门 10 工作年限最大的信息：1981/6/9
select min(hiredate)
from emp
where deptno = 10;

select *
from emp e
where e.hiredate = (
select min(hiredate)
from emp
where deptno = (
select deptno
from emp
where ename = 'KING'
)
) and e.deptno = (
select deptno
from emp
where ename = 'KING'
);

-- 查询出 KING 所在部门的部门号、部门名称、部门人数
--1、查询king 所在部门编号
select deptno
from emp
where ename = 'KING';
--2、查询King部门的信息
select d.dname, d.deptno
from dept d
where d.deptno = 10;
--3、统计 10 部门的人数
select count(*) 人数, deptno
from emp
where deptno = 10
group by deptno;
--4、合并
select d.dname, d.deptno, t.人数
from dept d, (
select count(*) 人数, deptno
from emp
where deptno = (
select deptno
from emp
where ename = 'KING'
)
group by deptno
) t
where d.deptno = t.deptno;

-- 算出部门 30 中得到最多奖金的员工姓名
--1、部门30的员工奖金的最大值
select max(comm)
from emp
where deptno = 30;
--2、根据最大值筛选
select ename
from emp
where comm = 1400
and deptno = 30;
--3、合并
select *
from emp
where comm = (
select max(comm)
from emp
where deptno = 30
) and deptno = 30;

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

-- 按部门统计工资大于等于 2000 以及小于 2000 的人数
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;

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

commit;
rollback;
savepoint A;
rollback to A;

select * from bonus;

savepoint A;
update bonus set sal = sal + 1 where sal<3000;

savepoint B;
update bonus set sal = sal + 1 where sal<3000;

savepoint C;
update bonus set sal = sal + 1 where sal<3000;

rollback to A;

-----------------------------------------------------------
--伪列：rowid rownum
--rowid：物理地址
--rownum：查询时实时产生行号
select e.*, rowid, rownum
from emp e
where e.sal > 2000;

select *
from emp e
where rownum <= 5;

select t.*, rownum
from (
select e.*
from emp e
order by e.sal desc
) t
where rownum <= 5;

--分页查询
select *
from (
select e.*, rownum r
from emp e
) t
where t.r between 5 and 10;

--练习
-- 按部门统计员工数，查出员工数最多的部门的第二名到第五名（列出部门名字，部门位置）
--1、按部门分组查询员工数量
select deptno, count(*) 人数
from emp
group by deptno
order by 人数 desc;

--2、加上行号
select e.deptno, rownum r
from (
select deptno, count(*) 人数
from emp
group by deptno
order by 人数 desc
) e;
--3、依据行号筛选
select *
from dept d
join (
select e.deptno, rownum r
from (
select deptno, count(*) 人数
from emp
group by deptno
order by 人数 desc
) e
) t
on d.deptno = t.deptno
and t.r between 2 and 5;

-- 查找出部门 10 和部门 20 中，工资最高第 3 名到第 5 名的员工的员工名字，部门名字，部门位置

select d.dname, d.loc, e.ename
from dept d, (
select ename, sal, deptno, rownum r
from (
select *
from emp
where deptno in (10, 20)
order by sal desc
)
) e
where e.r between 3 and 5
and e.deptno = d.deptno;

--------------------------------------------------------------------------
--视图：虚拟表、可以作为表一样查询使用、预定义的查询
create [or replace] [[no] force] view 视图名
as
select查询
[with read only];
--ex:
create or replace view empinfo
as
select e.*, d.dname, d.loc
from emp e
left join dept d
on e.deptno = d.deptno
with read only;

select *
from empinfo
where sal > 2000;

drop view empinfo;
--索引：加速查询
create [unique] index 索引名字 on 表名(列);
--ex:
create index emp_index_id on emp(ename);

drop index emp_index_id;

--存储过程:
create procedure sp_sal(name in varchar)
is
begin
update emp set sal = sal + 0.1 where ename = name;
commit;
end sp_sal;

create procedure sp_sal(name in varchar)
is
begin
update emp set sal = sal + 0.1 where ename = name;
commit;
end sp_sal;

select *
from emp;

-- exec sp_sal('wangwu');

create or replace procedure sp_sal(name in varchar)
-- 创建一个存储过程
is
i number;
--创建一个变量
begin
select e.sal into i
from emp e
where e.ename = name;
--查询语句，查询结果赋予给 i
if i > 2000 then
-- 条件成立
update emp set sal = sal + 0.01 where ename = name;
else
--条件不成立
update emp set sal = sal + 0.1 where ename = name;
end if;
--提交
commit;
end sp_sal;