ORACLE DAY_04

最新推荐文章于 2021-07-04 21:59:09 发布

Ward_Tian

最新推荐文章于 2021-07-04 21:59:09 发布

阅读量207

点赞数

分类专栏： java基础

本文链接：https://blog.csdn.net/Ward_Tian/article/details/84504213

版权

java基础专栏收录该内容

49 篇文章 0 订阅

订阅专栏

DAY04

ALTER TABLE emp ADD(
loc CHAR(40)
)
SELECT * FROM emp
UPDATE emp SET loc='guangdong' WHERE name='Demi' OR name='Denis'
子查询：是一条查询语句，其嵌套在其他SQL语句中，作用是为外层的SQL语句提供数据的。

查询和tom相同部门的员工
1.查询 tom的部门
SELECT depino FROM emp
WHERE name='tom'

2.查询tom同一个部门的员工
SELECT name ,depino FROM emp
WHERE depino=(SELECT depino FROM emp
WHERE name='tom')

在DDL中应用：使用子查询的结果快速创建一张表!
CREATE TABLE myemployee
AS
SELECT e.name,e.job,e.salary,e.loc,d.dname
FROM emp e, dept d
WHERE e.depino=d.depino

desc myemployee
SELECT * FROM myemployee

若子查询中查询的内容是函数或者表达式。该字段必须给别名

DML中使用子查询

删除和jack相同部门的员工
DELETE FROM dept
WHERE depino=(
SELECT depino FROM emp WHERE name='jack')

SELECT * FROM dept
把和tom所在部门的员工工资提高10%

UPDATE emp SET salary=salary*1.1
WHERE depino=(SELECT depino FROM emp WHERE name='tom')

子查询根据查询结果集不同通常分为：
单行单列：常用在WHERE中(配合=,>,>=等)
多行单列：常用在WHERE中(配合IN,ANY,ALL)
多行多列：常用在FROM 中作为表看待

查看比CLERK 和teacher工资都高的员工
SELECT name ,salary FROM emp WHERE salary>ALL(SELECT salary FROM emp
WHERE job='teacher' or JOB='clerk')

查看和CLERK相同部门的其他职位的员工?

SELECT name,job,depino FROM emp WHERE depino IN(
SELECT depino FROM emp WHERE job='CLERK') AND job<>'CLERK'

EXISTS 关键字，用在WHERE中，其后跟一个子查询，
作用是若后边的子查询至少查询出一条数据，则EXISTS表达式返回真
NOT EXISTS 则起到相反的作用，查不到数据则返回真

查看有员工的部门
SELECT d.dname,d.depino FROM dept d
WHERE EXISTS (SELECT * FROM emp e WHERE e.depino=d.depino )

查询列出最低薪水高于30部门的最低薪水的部门

SELECT MIN(salary) ,depino FROM emp
GROUP BY depino
HAVING MIN(salary)>
（SELECT MIN(salary) FROM emp e
WHERE depino=30）

子查询在FROM 的使用：

查询高于自己所在部门平均工资的员工的信息
SELECT e.name,e.salary ,e.depino FROM emp e,
(SELECT AVG(salary) avg_sal, depino FROM emp e GROUP BY depino) t
WHERE e.depino=t.depino
AND e.salary>t.avg_sal

子查询在SELECT 的使用：
通常是外连接的一种写法。
SELECT e.name,e.salary,
(SELECT d.dname FROM dept d WHERE d.depino=e.depino) dname
FROM emp e;

分页查询：
通常一个查询语句查询的数据过大时，都会使用分页机制，分页就是将数据分批查询出来
一次只查询部分内容，这样的好处可以减少系统响应时间，减少系统资源开销
由于标准SQL没有定义，不同数据库语法不同
ORACLE中使用ROWNUM这个伪列来实现分页
ROWNUM，该列不存在与数据库任何表中，但是任何表都可以查询该列，该列在结果集中的值ishi每条记录
的行号，行号从1开始。编号是在查询的过程中进行的，只要可以从中查询出一条数据，那么该条记录的ROWNUM
字段值即为这条记录的行号。

SELECT ROWNUM,name,job,salary,depino
FROM emp
WHERE ROWNUM BETWEEN 6 AND 10
在使用ROWNUM对结果集编号的查询过程中不要使用ROWNUM做>1以上的数字判断，否则查询不到是任何数据

select * FROM
(SELECT ROWNUM rn,name,job,salary,depino
FROM emp)
WHERE rn BETWEEN 6 AND 9

若对查询内容有排序需求，要先进行排序操作
取工资工资排名5-8
select *
FROM (SELECT ROWNUM rn,t.*
FROM
(SELECT name,salary,job FROM emp ORDER BY salary DESC) t
)
WHERE rn BETWEEN 5 AND 8

以下方法加了条件，先过滤不需要的数据
select * from
( select ROWNUM rn ,t.*
FROM
(SELECT name,salary,job FROM emp ORDER BY salary DESC) t
WHERE ROWNUM<=10)
WHERE rn>=5

换算范围值
PageSize:每页显示的条数
Page:页数
start=(Page-1)*PageSize+1
end=PageSize*Page

DECODE函数基本语法，类似于 if-else，job为CLERK的时候，salary*1.2...
SELECT name, job, salary,
DECODE (job,
'CLERK', salary*1.2,
'teacher',salary*1.1,
'doctor',salary*1.05，
salary
) bonus
FROM emp

和DECODE函数功能相似的CASE语句。
SELECT name,job,salary,
CASE job WHEN 'manager' THEN salary*1.2
WHEN 'doctor' THEN salary*1.1
ELSE salary END
bonus
FROM emp

DECODE基本语法2
统计人数，将manager和teacher看作一组，其他职位看做另一组
SELECT COUNT(*),
DECODE(job,
'manager', 'VIP',
'teacher','VIP',
'OTHER' )
FROM emp
GROUP BY DECODE(job,
'manager', 'VIP',
'teacher','VIP',
'OTHER' )

DECODE的语法3，按字段内容排序
SELECT depino,dname
FROM dept
ORDER BY
DECODE(
dname,'OP',1,
'SALES',2,
'ACCOUNTING',3
)



排序函数：
排序函数可以将结果集按照指定的字段分组，然后在组内按照指定的字段
排序，并为组内每条记录生成一个编号。

ROW_NUMBER：组内连续且唯一的数字
看公司每个部门的工资排名：
SELECT
name,salary,depino,
ROW_NUMBER() OVER(
PARTITION BY depino
ORDER BY salary DESC
)rank
FROM emp

RANK:生成组内不连续也不唯一的数字,有并列排名的情况会并列同一名
SELECT
name,salary,depino,
RANK() OVER(
PARTITION BY depino
ORDER BY salary DESC
)rank
FROM emp

DENSE_RANK:生成子内连续但不唯一的数字
SELECT
name,salary,depino,
DENSE_RANK() OVER(
PARTITION BY depino
ORDER BY salary DESC
)rank
FROM emp

高级
UNION UNION ALL，并集
为了合并多个SELECT
UNION ALL,查询的结果集有并集，会重复显示
SELECT name,job ,salary FROM emp
WHERE job='doctor'
UNION SELECT name,job,salary FROM emp
WHERE salary>3000;

union all并集所有重复的集都会查出来
SELECT name,job ,salary FROM emp
WHERE job='doctor'
UNION ALL
SELECT name,job,salary FROM emp
WHERE salary>3000;

INTERSECT 交集
SELECT name,job,salary FROM emp
WHERE job='teacher'
INTERSECT
SELECT name,job,salary FROM emp
WHERE salary>5000

MINUS差集
只有在第一个结果集中存在，在第二个结果集中不存在的数据，才能够显示出来
就是结果集1减去结果集2
SELECT name,job,salary FROM emp
WHERE job='teacher'
MINUS
SELECT name,job,salary FROM emp
WHERE salary>5000

高级分组函数：以下是随机创建1000条数据的代码
CREATE TABLE sales_tab(
year_id NUMBER NOT NULL,
month_id NUMBER NOT NULL,
day_id NUMBER NOT NULL,
sales_value NUMBER(10,2)NOT NULL
);
INSERT INTO sales_tab
SELECT TRUNC(DBMS_RANDOM.value(2010,2012)) AS yera_id,
TRUNC(DBMS_RANDOM.value(1,13)) AS month_id,
TRUNC(DBMS_RANDOM.value(1,32)) AS day_id,
ROUND(DBMS_RANDOM.value(1,100),2) AS sales_value
FROM dual
CONNECT BY level<=1000;

SELECT year_id,month_id,day_id,sales_value FROM sales_tab
ORDER BY year_id,month_id,day_id

查看每天的营业额
SELECT year_id,month_id,day_id,SUM(sales_value)
FROM sales_tab
GROUP BY year_id,month_id,day_id
order by year_id,month_id,day_id

查看每月的营业额
SELECT year_id,month_id, SUM(sales_value)
FROM sales_tab
GROUP BY year_id,month_id
order by year_id,month_id

ROLLUP 高级分组函数
ROLLUP 分组次数有指定的参数决定，次数有参数个数+1次，而且分组原则是每个参数递减的形式，然后将
这些分组的结果并在一个结果集中显示
GROUP BY ROLLUP(a,b,c...)
例如：
GROUP BY ROLLUP(a,b,c...)
等同于
GROUP BY a,b,c
UNION ALL GROUP BY a,b
UNION ALL GROUP BY a
UNION ALL 全表

查看每天，每月，每年和总营业额
SELECT year_id,month_id,day_id,
SUM(sales_value)
FROM sales_tab
GROUP BY ROLLUP(year_id,month_id,day_id)

CUBE函數：分組次數是2的n次方，n为参数个数
会将每种组合进行一次分组并将所有结果并在一个结果集中显示

GROUP BY CUBE(a,b,c)等同于以下8中的并集
a,b,c a,b a,c b,c
a b c 全表

SELECT year_id,month_id,day_id,
SUM(sales_value)
FROM sales_tab
GROUP BY CUBE(year_id,month_id,day_id)

GROUPING SETS()
该分组函数允许按照指定的分组方式进行分组，
然后将这些分组统计的结果并在一个结果集中显示
函数的每一个参数就是一种分组方式

只查看每天与每月营业额？
SELECT year_id,month_id,day_id，SUM(sales_value)
FROM sales_tab
GROUP BY
GROUPING SETS (
(year_id,month_id,day_id),
(year_id,month_id)
)
ORDER BY year_id,month_id,day_id