标签：salary name employees OraclePractice department id select

Oracle

第 1 章基本SQL-SELECT语句

对于日期型数据, 做 *, / 运算不合法
包含空值的数学表达式的值都为空值
别名使用双引号!
oracle 中连接字符串使用 "||", 而不是 java 中的 "+"
日期和字符只能在单引号中出现. 输出 last_name`s email is email

select last_name || ' `s email is ' || email EMAIL
from employees

distinct 关键字, 以下语法错误

select last_name, distinct department_id
from employees

第 2 章过滤和排序数据

WHERE 子句紧随 FROM 子句
查询 last_name 为 'King' 的员工信息

错误1: King 没有加上单引号

select first_name, last_name
from employees
where last_name = King

错误2: 在单引号中的值区分大小写

select first_name, last_name
from employees
where last_name = 'king'

正确

select first_name, last_name
from employees
where last_name = 'King'

查询 1998-4-24 来公司的员工有哪些?

注意: 日期必须要放在单引号中, 且必须是指定的格式

select last_name, hire_date
from employees
where hire_date = '24-4月-1998'

查询工资在 5000 -- 10000 之间的员工信息.

1). 使用 AND
select *
from employees
where salary >= 5000 and salary <= 10000

2). 使用 BETWEEN .. AND .., 注意: 包含边界!!
select *
from employees
where salary between 5000 and 10000
查询工资等于 6000, 7000, 8000, 9000, 10000 的员工信息

1). 使用 OR
select *
from employees
where salary = 6000 or salary = 7000 or salary = 8000 or salary = 9000 or salary = 10000

2). 使用 IN
select *
from employees
where salary in (6000, 7000, 8000, 9000, 10000)
查询 LAST_NAME 中有 'o' 字符的所有员工信息.

**select ***
from employees
where last_name like '%o%'
查询 LAST_NAME 中第二个字符是 'o' 的所有员工信息.

**select ***
from employees
where last_name like '_o%'
查询 LAST_NAME 中含有 '_' 字符的所有员工信息

1). 准备工作:
update employees
set last_name = 'Jones_Tom'
where employee_id = 195

2). 使用 escape 说明转义字符.
**select ***
from employees
where last_name like '%_%' escape ''
查询 COMMISSION_PCT 字段为空的所有员工信息
select last_name, commission_pct
from employees
where commission_pct is null
查询 COMMISSION_PCT 字段不为空的所有员工信息
select last_name, commission_pct
from employees
where commission_pct is not null
ORDER BY:
1). 若查询中有表达式运算, 一般使用别名排序
2). 按多个列排序: 先按第一列排序, 若第一列中有相同的, 再按第二列排序.
格式： ORDER BY 一级排序列 ASC/DESC,二级排序列 ASC/DESC；

第 3 章单行函数

 concat ('hello','world')  - >  helloworld   拼接
 substr ('helloworld',1,5) - >  hello        截取
 length ('hello')          - >  5            长度
 instr  ('hello','e')      - >  2            e第一次出现的位置
 lpad   (salary,10,'*')    - >  ******7000   左对齐，10位数，不足的在左边补上 * 号
 rpad                                        右对齐
 trim   ('h' FROM 'hehlloh') - >  ehllo      将首尾h从hehlloh中剔除
 replace('helloh','h','w')  - > wellow       将h全部替换为w  
 round  (888.888,2)        - >  888.89       保留两位小数（四舍五入）
 round  (888.888)          - >  888          取整（四舍五入）
 trunc  (888.88,1)         - >  888.8        截断
 mod    (1600,300)         - >  100          求余
 sysdate                   - >  2022/11/24   当前时间（在日期函数加上或减去一个数字结果仍然为日期）
 months_between(sysdate , hire_date)         两个日期相差的月数
 add_months(sysdate,2) 当前日期加两个月         add_months(sysdate,3) 当前日期加减去3个月
 next_day(sysdate,'星期日')     指定日期的下一个星期对应的日期
 last_day(hire_date)           月的最后一天
 ==>（NUMBER，VARCHAR，DATE三种类型的显性转换）<==
 Date日期类型转字符类型（Varchar）   　- >  to_char(hire_date,'yyyy-mm-dd')             =================================　- > to_char(hire_date,'yyyy"年"mm"月"dd"日"')
 ================================　- > to_char(sysdate,'yyyy"年"mm"月"dd"日" hh:mi:ss') 
 Number数字类型转字符类型 - >  to_char(1234567.89,'999,999,999.99')9数字 0零 $美元符 L本地货币 .小数点 ，千位符  
 to_date   Varchar型转Date型      - >  to_date('1994-09-08','yyyy-mm-dd')
 to_number Varchar型转Number型 to_number  to_number
 nvl(expr1,expr2)如果expr1为null,就赋值为expr2。 将空值转换成一个已知的值
 nvl2(expr1,expr2,expr3)如果expr1不为null返回expr2,为null返回expr3
 nullif(expr1,expr2)相等返回null,不等返回expr1
 coalesce(expr1,expr2,expr3)  expr1为null返回expr2  expr2为null返回expr3  如此往复
 case xxx       ==> case表达式  条件判断
      when xxx 
      then xxx  
      else xxx 
      end  别名（可加可不加）
 
 decode(department_id,10,salary * 1 ,
                      20,salary * 2 ,
                         salary * 3) 别名
                         
 内连接：合并具有同一列的两个以上的表的行，结果集中不包含一个表与另一个表不匹配的行
 employees e JOIN departments d USING(department_id)  <==> where e.department_id = d.department_id
 <==> employees e JOIN departments d ON e.department_id = d.department_id
 
 外连接：
      左外连接 emp.ids = dept.ids(+)   返回左表不满足条件的，在右边添加（+）
      右外连接 emp.ids(+) = dept.ids   返回右表不满足条件的，在左边添加（+）
        	select last_name, e.department_id, department_name
 	           from employees e, departments d
 	           where e.department_id = d.department_id(+)
 	
 	   select last_name, d.department_id, department_name
 	          from employees e, departments d
 	          where e.department_id(+) = d.department_id
      
 from employees e RIGHT OUTER JOIN departments d ON e.department_id = d.department_id  右外连接
 from employees e LEFT OUTER JOIN departments d ON e.department_id = d.department_id   左外连接
 from employees e FULL OUTER JOIN departments d ON e.department_id = d.department_id   满外连接
 ===> ==>select e.last_name,d.department_id,d.department_name 
                  from employees e RIGHT  OUTER JOIN  departments d                                                            ON e.department_id = d.department_id;
 自连接 ：  查询员工老板的详细信息
       select boss.last_name "老板",worker.last_name "员工",boss.salary,boss.email 
       from employees worker , employees boss 
       where worker.manager_id = boss.employee_id
 
 组函数：      
 max() count() min()函数 支持varchar类型 、DATE类型、和NUMBER类型
 avg() sum()函数         支持NUMBER类型   不支持varchar类型、DATE类型  
 使用组函数时，添加过滤条件不可用where，应当使用having
   
 count(distinct dept)查询共有多少个部门
 group by 查询的列只要不是组函数，都应该出现在group by后面   
     select dept_id,job_id,AVG(salary) from emp GROUP BY dept_id,job_id
     
 过滤条件中出现组函数时，要使用having
 
 查询平均工资最低的部门信息和该部门的平均工资
 select d.* ,(select avg(salary) from employees where department_id=d.department_id) as "平均工资"
 from departments d
 where department_id = (
       select department_id 
       from employees
       having avg(salary) =(
                  select min(avg(salary))
                  from employees
                  group by department_id
       )
       group by  department_id
  )

打印出 "2009年10月14日 9:25:40" 格式的当前系统的日期和时间.

select to_char(sysdate, 'YYYY"年"MM"月"DD"日" HH:MI:SS')
from dual

注意: 使用双引号向日期中添加字符
格式化数字: 1234567.89 为 1,234,567.89

select to_char(1234567.89, '999,999,999.99')
from dual
字符串转为数字时
1). 若字符串中没有特殊字符, 可以进行隐式转换:
select '1234567.89' + 100
from dual

2). 若字符串中有特殊字符, 例如 '1,234,567.89', 则无法进行隐式转换, 需要使用 to_number() 来完成

select to_number('1,234,567.89', '999,999,999.99') + 100
from dual
对于把日期作为查询条件的查询, 一般都使用 to_date() 把一个字符串转为日期, 这样可以不必关注日期格式

select last_name, hire_date
from employees
where hire_date = to_date('1998-5-23', 'yyyy-mm-dd')
-- where to_char(hire_date,'yyyy-mm-dd') = '1998-5-23'
转换函数: to_char(), to_number(), to_date()
查询每个月倒数第 2 天入职的员工的信息.

select last_name, hire_date
from employees
where hire_date = last_day(hire_date) - 1
计算公司员工的年薪

--错误写法: 因为空值计算的结果还是空值
select last_name, salary * 12 * (1 + commission_pct) year_sal
from employees

--正确写法
select last_name, salary * 12 * (1 + nvl(commission_pct, 0)) year_sal
from employees
查询部门号为 10, 20, 30 的员工信息, 若部门号为 10, 则打印其工资的 1.1 倍, 20 号部门, 则打印其工资的 1.2 倍, 30 号部门打印其工资的 1.3 倍数

--使用 case-when-then-else-end
select last_name, department_id, salary, case department_id when 10 then salary * 1.1
when 20 then salary * 1.2
when 30 then salary * 1.3
end new_sal
from employees
where department_id in (10, 20, 30)

--使用 decode
select last_name, department_id, salary, decode(department_id, 10, salary * 1.1,
20, salary * 1.2,
30, salary * 1.3
) new_sal
from employees
where department_id in (10, 20, 30)

第 4 章多表查询

多表连接查询时, 若两个表有同名的列, 必须使用表的别名对列名进行引用, 否则出错!
查询出公司员工的 last_name, department_name, city

select last_name, department_name, city
from departments d, employees e, locations l
where d.department_id = e.department_id and d.location_id = l.location_id

查询出 last_name 为 'Chen' 的 manager 的信息. (员工的 manager_id 是某员工的 employee_id)

0). 例如: 老张的员工号为: "1001", 我的员工号为: "1002",

    我的 manager_id 为 "1001" --- 我的 manager 是"老张"

1). 通过两条 sql 查询:

	select manager_id
	from employees
	where lower(last_name) = 'chen' --返回的结果为 108
	
	select *
	from employees
	where employee_id = 108

2). 通过一条 sql 查询(自连接):

	select m.*
	from employees e, employees m
	where e.manager_id = m.employee_id and e.last_name = 'Chen'

3). 通过一条 sql 查询(子查询):

	select *
	from employees
	where employee_id = (
	                      select manager_id 
	                      from employees
	                      where last_name = 'Chen'
	                    )

查询每个员工的 last_name 和 GRADE_LEVEL(在 JOB_GRADES 表中). ---- 非等值连接

	select last_name, salary, grade_level, lowest_sal, highest_sal
	from employees e, job_grades j
	where e.salary >= j.lowest_sal and e.salary <= j.highest_sal

左外连接和右外连接

	select last_name, e.department_id, department_name
	from employees e, departments d
	where e.department_id = d.department_id(+)
	
	select last_name, d.department_id, department_name
	from employees e, departments d
	where e.department_id(+) = d.department_id
	
	理解 "(+)" 的位置: 以左外连接为例, 因为左表需要返回更多的记录,
	右表就需要 "加上" 更多的记录, 所以在右表的链接条件上加上 "(+)"
	
	注意: 1). 两边都加上 "(+)" 符号, 会发生语法错误!
	      2). 这种语法为 Oracle 所独有, 不能在其它数据库中使用.

SQL 99 连接 Employees 表和 Departments 表
1).
select *
from employees join departments
using(department_id)

	缺点: 要求两个表中必须有一样的列名.
	
	2).
	select *
	from employees e join departments d
	on e.department_id = d.department_id
	
	3).多表连接
	select e.last_name, d.department_name, l.city
	from employees e join departments d
	on e.department_id = d.department_id
	join locations l
	on d.location_id = l.location_id

SQL 99 的左外连接, 右外连接, 满外连接
1).
select last_name, department_name
from employees e left outer join departments d
on e.department_id = d.department_id

	2).
	select last_name, department_name
	from employees e right join departments d
	on e.department_id = d.department_id
	
	3).
	select last_name, department_name
	from employees e full join departments d
	on e.department_id = d.department_id

第 5 章分组函数

查询 employees 表中有多少个部门

select count(distinct department_id)
from employees

查询全公司奖金基数的平均值(没有奖金的人按 0 计算)
```
select avg(nvl(commission_pct, 0))
from employees		
```

查询各个部门的平均工资

--错误: avg(salary) 返回公司平均工资, 只有一个值; 而 department_id 有多个值, 无法匹配返回
select department_id, avg(salary)
from employees				 

**在 SELECT 列表中所有未包含在组函数中的列都应该包含在 GROUP BY 子句中

--正确: 按 department_id 进行分组
select department_id, avg(salary)
from employees
group by department_id

Toronto 这个城市的员工的平均工资

SELECT avg(salary)
FROM employees e JOIN departments d
ON e.department_id = d.department_id
JOIN locations l
ON d.location_id = l.location_id
WHERE city = 'Toronto'

(有员工的城市)各个城市的平均工资

SELECT city, avg(salary)
FROM employees e JOIN departments d
ON e.department_id = d.department_id
JOIN locations l
ON d.location_id = l.location_id
GROUP BY city

查询平均工资高于 8000 的部门 id 和它的平均工资.

SELECT department_id, avg(salary)
FROM employees e
GROUP BY department_id
HAVING avg(salary) > 8000

查询平均工资高于 6000 的 job_title 有哪些

SELECT job_title, avg(salary)
FROM employees e join jobs j
ON e.job_id = j.job_id
GROUP BY job_title
HAVING avg(salary) > 6000

第 6 章子查询

谁的工资比 Abel 高?

1). 写两条 SQL 语句.

SELECT salary
FROM employees
WHERE last_name = 'Abel'

--返回值为 11000

SELECT last_name, salary
FROM employees
WHERE salary > 11000

2). 使用子查询 -- 一条 SQL 语句

SELECT last_name, salary
FROM employees
WHERE salary > (
	SELECT salary
	FROM employees
	WHERE last_name = 'Abel'
)

子查询注意:

	1). 子查询要包含在括号内
	2). 将子查询放在比较条件的右侧

查询工资最低的员工信息: last_name, salary
查询平均工资最低的部门信息

43*. 查询平均工资最低的部门信息和该部门的平均工资

查询平均工资最高的 job 信息
查询平均工资高于公司平均工资的部门有哪些?
查询出公司中所有 manager 的详细信息.
各个部门中最高工资中最低的那个部门的最低工资是多少
查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email, salary
查询 1999 年来公司的人所有员工的最高工资的那个员工的信息.

查询工资最低的员工信息: last_name, salary

SELECT last_name, salary
FROM employees
WHERE salary = (
	SELECT min(salary)
	FROM employees
)

查询平均工资最低的部门信息

SELECT *
FROM departments
WHERE department_id = (
	SELECT department_id
	FROM employees
	GROUP BY department_id 
	HAVING avg(salary) = (
		SELECT min(avg(salary))
		FROM employees
		GROUP BY department_id
	) 
)

查询平均工资最低的部门信息和该部门的平均工资

select d.*, (select avg(salary) from employees where department_id = d.department_id)
from departments d
where d.department_id = (
SELECT department_id
FROM employees
GROUP BY department_id
HAVING avg(salary) = (
SELECT min(avg(salary))
FROM employees
GROUP BY department_id
)
)

查询平均工资最高的 job 信息

1). 按 job_id 分组, 查询最高的平均工资
SELECT max(avg(salary))
FROM employees
GROUP BY job_id

2). 查询出平均工资等于 1) 的 job_id
SELECT job_id
FROM employees
GROUP BY job_id
HAVING avg(salary) = (
SELECT max(avg(salary))
FROM employees
GROUP BY job_id
)

3). 查询出 2) 对应的 job 信息
SELECT *
FROM jobs
WHERE job_id = (
SELECT job_id
FROM employees
GROUP BY job_id
HAVING avg(salary) = (
SELECT max(avg(salary))
FROM employees
GROUP BY job_id
)
)
查询平均工资高于公司平均工资的部门有哪些?

1). 查询出公司的平均工资
SELECT avg(salary)
FROM employees

2). 查询平均工资高于 1) 的部门 ID
SELECT department_id
FROM employees
GROUP BY department_id
HAVING avg(salary) > (
SELECT avg(salary)
FROM employees
)
查询出公司中所有 manager 的详细信息.
1). 查询出所有的 manager_id
SELECT distinct manager_id
FROM employeess

2). 查询出 employee_id 为 1) 查询结果的那些员工的信息
SELECT employee_id, last_name
FROM employees
WHERE employee_id in (
SELECT distinct manager_id
FROM employees
)
各个部门中最高工资中最低的那个部门的最低工资是多少
1). 查询出各个部门的最高工资
SELECT max(salary)
FROM employees
GROUP BY department_id

2). 查询出 1) 对应的查询结果的最低值: 各个部门中最低的最高工资(无法查询对应的 department_id)
SELECT min(max(salary))
FROM employees
GROUP BY department_id

3). 查询出 2) 所对应的部门 id 是多少: 各个部门中最高工资等于 2) 的那个部门的 id
SELECT department_id
FROM employees
GROUP BY department_id
HAVING max(salary) = (
SELECT min(max(salary))
FROM employees
GROUP BY department_id
)

4). 查询出 3) 所在部门的最低工资
SELECT min(salary)
FROM employees
WHERE department_id = (
SELECT department_id
FROM employees
GROUP BY department_id
HAVING max(salary) = (
SELECT min(max(salary))
FROM employees
GROUP BY department_id
)
)
查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email, salary

1). 各个部门中, 查询平均工资最高的平均工资是多少
SELECT max(avg(salary))
FROM employees
GROUP BY department_id

2). 各个部门中, 平均工资等于 1) 的那个部门的部门号是多少
SELECT department_id
FROM employees
GROUP BY department_id
HAVING avg(salary) = (
SELECT max(avg(salary))
FROM employees
GROUP BY department_id
)

3). 查询出 2) 对应的部门的 manager_id
SELECT manager_id
FROM departments
WHERE department_id = (
SELECT department_id
FROM employees
GROUP BY department_id
HAVING avg(salary) = (
SELECT max(avg(salary))
FROM employees
GROUP BY department_id
)
)

4). 查询出 employee_id 为 3) 查询的 manager_id 的员工的 last_name, department_id, email, salary
SELECT last_name, department_id, email, salary
FROM employees
WHERE employee_id = (
SELECT manager_id
FROM departments
WHERE department_id = (
SELECT department_id
FROM employees
GROUP BY department_id
HAVING avg(salary) = (
SELECT max(avg(salary))
FROM employees
GROUP BY department_id
)
)
)

查询 1999 年来公司的人所有员工的最高工资的那个员工的信息.

1). 查询出 1999 年来公司的所有的员工的 salary
SELECT salary
FROM employees
WHERE to_char(hire_date, 'yyyy') = '1999'

2). 查询出 1) 对应的结果的最大值
SELECT max(salary)
FROM employees
WHERE to_char(hire_date, 'yyyy') = '1999'

3). 查询工资等于 2) 对应的结果且 1999 年入职的员工信息		
SELECT *
FROM employees
WHERE to_char(hire_date, 'yyyy') = '1999' AND salary = (
	SELECT max(salary)
	FROM employees
	WHERE to_char(hire_date, 'yyyy') = '1999'
)

多行子查询的 any 和 all

select department_id
from employees
group by department_id
having avg(salary) >= any(
                          --所有部门的平均工资
                          select avg(salary)
                          from employees
                          group by department_id
                       )

any 和任意一个值比较, 所以其条件最为宽松, 所以实际上只需和平均工资最低的比较, 返回所有值
而 all 是和全部的值比较, 条件最为苛刻, 所以实际上返回的只需和平均工资最高的比较, 所以返回
平均工资最高的 department_id

第 7 章创建和管理表

利用子查询创建表 myemp,
该表中包含 employees 表的 employee_id(id), last_name(name), salary(sal), email 字段

1). 创建表的同时复制 employees 对应的记录

create table myemp
as
select employee_id id, last_name name, salary sal, email from employees

2). 创建表的同时不包含 employees 中的记录, 即创建一个空表

create table myemp
as
select employee_id id, last_name name, salary sal, email from employees where 1 = 2
对现有的表进行修改操作

1). 添加一个新列

ALTER TABLE myemp
ADD(age number(3))

2). 修改现有列的类型

ALTER TABLE myemp
MODIFY(name varchar2(30));

3). 修改现有列的名字

ALTER TABLE myemp
RENAME COLUMN sal TO salary;

4). 删除现有的列

ALTER TABLE myemp
DROP COLUMN age;
清空表中的数据 (截断： truncate), 不能回滚!! truncate table 表名
rename emp1 to emp2; 修改表名
create table 、 alter table 、 drop table 、 rename to 、 truncate table DDL操作不可回滚

1). 创建一个表, 该表和 employees 有相同的表结构, 但为空表:
create table emp2 as select * from employees where 1 = 2;

2). 把 employees 表中 80 号部门的所有数据复制到 emp2 表中:

insert into emp2 select * from employees where department_id = 80;

第 8 章数据处理

更改 108 员工的信息: 使其工资变为所在部门中的最高工资, job 变为公司中平均工资最低的 job

1). 搭建骨架
update employees
set salary = (

), job_id = (

) where employee_id = 108;

2). 所在部门中的最高工资
select max(salary)
from employees
where department_id = (
select department_id
from employees
where employee_id = 108
)

3). 公司中平均工资最低的 job
select job_id
from employees
group by job_id
having avg(salary) = (
select min(avg(salary))
from employees
group by job_id
)

4). 填充
update employees e set salary = (
select max(salary)
from employees
where department_id = e.department_id
), job_id = (
select job_id
from employees
group by job_id
having avg(salary) = (
select min(avg(salary))
from employees
group by job_id
)
) where employee_id = 108;

删除 108 号员工所在部门中工资最低的那个员工.

1). 查询 108 员工所在的部门 id
select department_id
from employees
where employee_id = 108;

2). 查询 1) 部门中的最低工资:

select min(salary)
from employees
where department_id = (
select department_id
from employees
where employee_id = 108
)

3). 删除 1) 部门中工资为 2) 的员工信息:

delete from employees e
where department_id = (
select department_id
from employees e
where employee_id = 108
) and salary = (
select min(salary)
from employees
where department_id = e.department_id
)

第9章约束

（not null 、unique 、primary key 、foreign key 、check)
定义非空约束

1). 非空约束只能定义在列级.

2). 不指定约束名
create table emp2 (
name varchar2(30) not null,
age number(3)
);

3). 指定约束名
create table emp3(
name varchar2(30) constraint name_not_null not null,
age number(3));

唯一约束
1). 列级定义

①. 不指定约束名
create table emp2 (
name varchar2(30) unique, 
age number(3)
);

②. 指定约束名
create table emp3 (
name varchar2(30) constraint name_uq unique, 
age number(3)
);

2). 表级定义: 必须指定约束名
①. 指定约束名
create table emp3 (
name varchar2(30),
age number(3),
constraint name_uq unique(name)
);

58.1 主键约束：唯一确定一行记录。表明此属性：非空，唯一

外键约束
1). 列级定义

①. 不指定约束名
create table emp2(
       emp_id number(6), 
       name varchar2(25), 
       dept_id number(4) references dept2(dept_id))

②. 指定约束名
create table emp3(
       emp_id number(6), 
       name varchar2(25), 
       dept_id number(4) constraint dept_fk3 references dept2(dept_id))

2). 表级定义: 必须指定约束名

①. 指定约束名
create table emp4(
       emp_id number(6), 
       name varchar2(25), 
       dept_id number(4),
       constraint dept_fk2 foreign key(dept_id) references dept2(dept_id))

约束需要注意的地方

1). ** 非空约束(not null)只能定义在列级

2). ** 唯一约束(unique)的列值可以为空

3). ** 外键(foreign key)引用的列起码要有一个唯一约束
建立外键约束时的级联删除问题:
1). 级联删除:

create table emp2(
id number(3) primary key,
name varchar2(25) unique,
dept_id number(3) references dept2(dept_id) on delete cascade)

2). 级联置空

create table emp3(
id number(3) primary key,
name varchar2(25) unique,
dept_id number(3) references dept2(dept_id) on delete set null)

第 10 章视图

可以使用 with read only 选项来屏蔽对视图的DML操作
例如 ：
　　　create or replace view  empview3
    as
    select last_name,department_name,city,email
    from employees e 
    join departments d on e.department_id = d.department_id
    join locations l on d.location_id = l.location_id
    with read only
    
删除视图ｄｒｏｐ　ｖｉｅｗ　ｅｍｐｖｉｅｗ３

复杂视图指的是使用了组函数的视图，并且使用了组函数的视图不可进行增删改操作

查询员工表中 salary 前 10 的员工信息.

select rownum , last_name, salary (本行的rownum可加可不加)
from (select last_name, salary from employees order by salary desc)
where rownum <= 10

说明: rownum "伪列" ---- 数据表本身并没有这样的列, 是 oracle 数据库为每个数据表 "加上的"  列. 
可以标识行号.默认情况下 rownum 按主索引来排序. 若没有主索引则自然排序.
当把一个列声明为主键的时候，会自动给它加上索引

注意: ****对 ROWNUM 只能使用 < 或 <=, 而是用 =, >, >= 都将不能返回任何数据.**

查询员工表中 salary 10 - 20 的员工信息. （找10至20之间的信息，需要同时使用大于号和小于号，但是rownum不能使用大于号，因此需要加一层，并为内层的rownum取一个别名，这样就能使用大于号了）

例子1：

select *
from(
select rownum rn, temp.*
from (
select last_name, salary
from employees e
order by salary desc
) temp
)
where rn > 10 and rn < 21

例子2：

select *
from (
select rownum rn ,employee_id , last_name ,salary
from (
select employee_id, last_name , salary
from employees
order by salary desc

)
)
where rn > 5 and rn < 10

对 oralce 数据库中记录进行分页: 每页显示 10 条记录, 查询第 5 页的数据

select employee_id, last_name, salary
from (
select rownum rn, employee_id, last_name, salary
from employees
) e
where e.rn <= 50 and e.rn > 40

注意: **对 oracle 分页必须使用 rownum "伪列"!

select employee_id, last_name, salary
from (
select rownum rn, employee_id, last_name, salary
from employees
) e
where e.rn <= pageNo * pageSize and e.rn > (pageNo - 1) * pageSize

第 11 章其它数据库对象

创建序列:

CREATE SEQUENCE dept_deptid_seq

INCREMENT BY 10 每次增长10

START WITH 200 从200开始包含200

MAXVALUE 10000 最大值

NOCACHE

NOCYCLE;

1). create sequence hs
increment by 10
start with 10

2). NEXTVAL 应在 CURRVAL 之前指定，二者应同时有效

序列通常用来生成主键:

INSERT INTO emp2 VALUES (emp2_seq.nextval, 'xx', ...)

总结： what -- why -- how
表table
视图view
序列sequence
索引index
同义词synonym 为表取别名 create synonym emp for empployees;

删除序列 drop sequence 序列名；
在某一列创建索引 create index empindex on emp(last_name);
常见数据库对象

表table 基本的数据存储集合，由行和列组成

**视图 **view 从表中抽取的逻辑上相关的数据集合

序列sequence 提供有规律的数值 (提供主键)

索引index 提高查询的效率

同义词synonym 给对象起别名

第 12章 SET操作符

　　　　　　　　　　UNION 　、UNION　ALL　、INTERSECT　、MINUS　、ORDER　BY

使用 SET 操作符注意事项

在SELECT 列表中的列名和表达式在数量和数据类型上要相对应
括号可以改变执行的顺序
ORDER BY 子句:
  --只能在语句的最后出现
  --可以使用第一个查询中的列名, 别名或相对位置
不指定ORDER BY的情况下　是按照第一个字段（列）升序排列
除 UNION ALL之外，系统会自动将重复的记录删除
系统将第一个查询的列名显示在输出中
除 UNION ALL之外，系统自动按照第一个查询中的第一个列的升序排列

1 . union 返回两个查询的结果集的并集

select employee_id,department_id
from empl01
union
select employee_id,department_id
from empl02

2 .union all 返回两个查询的结果集的并集。对于两个结果集的重复部分，不去重。

select employee_id,department_id
from empl01
union all
select employee_id,department_id
from empl02

3 .intersect 返回交集

select employee_id,department_id
from empl01
INTERSECT
select employee_id,department_id
from empl02

4 .minus 返回empl01自身结果集，需要减去与empl02相交的部分

select employee_id,department_id
from empl01
minus
select employee_id,department_id
from empl02

5 .当出现数据类型不匹配或数量不匹配的情况怎么处理

匹配各SELECT 语句举例

select last_name , employee_id , to_number(null) , hire_date
from empl01
union
select **to_char(null) **, employee_id , department_id , to_date(null)
from empl02

6 .使用相对位置排序举例

select job_id , department_id ,1
from employees
where department_id =10
union
select job_id , department_id ,3
from employees
where department_id =50
union
select job_id , department_id ,2
from employees
where department_id =20
order by ３ asc　

本题的order by ３意思是按照第三列排序。这个例子的第三列是数字类型的１，所以按照数字类型排序

如果不写order by ３ asc，那么默认使用第一列的升序排列，即 job_id的升序

７．COLUMN a_dummy NOPRINT 不让a_dummy 这一列打印：

使用方法 1）执行这条命令COLUMN a_dummy NOPRINT

2）将 a_dummy 放在不想打印的列后即可

3）例如：select last_name , empid a_dummy

８

高级子查询，返回多列数据

select employee_id,manager_id,department_id
from employees
where (manager_id,department_id) in(
select manager_id,department_id
from employees
where employee_id in (141,174))
and employee_id not in (141,174)

WITH 子句

－－使用 WITH 子句, 可以避免在 SELECT 语句中重复书写相同的语句块

－－WITH 子句将该子句中的语句块执行一次并存储到用户的临时表空间中

－－使用 WITH 子句可以提高查询效率

with Abs as (select salary from employees where last_name = 'Abel')

select last_name,employee_id
from employees
where salary > (
select salary
from Abs
)

第十三章　ＰＬ／ＳＱＬ

declare
v_sal varchar(20);
begin
select salary into v_sal from employees where employee_id = 100;　　－－查询
dbms_output.put_line(v_sal);　　－－输出
end;

.......

1.导入sql文件
  File->New->Command Window
  @E:/hr_popul.sql;
2.当使用完一个查询语句想要做修改 可输入 ed 进行编辑 然后输入 / +Enter即可执行
3.连接符 ||  用于拼接  与 Java 中的 + 作用类似    select  last_name ||  '`s job is ' ||  job_id from employees; 
  Tom`s  job is AC_ACCOUNT
4.取别名时 可用 “ ” 、 也可直接把别名写在后面 、 也可把别名写在as后面。  
区别使用" "别名会和“ ”中写的一样，例如 “Name”  ==> Name  。  而 后两种后直接变成全大写  Name ==> NAME
5.日期和字符只能在单引号中出现  ' '
6.distinct 
7.sql里下角标是从1开始的
8.中文要放在" "双引号里

来源：尚硅谷-宋红康老师14年Oracle视频

标签：salary,name,employees,OraclePractice,department,id,select
From： https://www.cnblogs.com/szrup0126/p/16949420.html

OraclePractice

Oracle

第 1 章基本SQL-SELECT语句

第 2 章过滤和排序数据

第 3 章单行函数

第 4 章多表查询

第 5 章分组函数

第 6 章子查询

第 7 章创建和管理表

第 8 章数据处理

第9章约束

第 10 章视图

第 11 章其它数据库对象

第 12章 SET操作符

第十三章　ＰＬ／ＳＱＬ

相关文章

赞助商

阅读排行

OraclePractice

Oracle

第 1 章 基本SQL-SELECT语句

第 2 章 过滤和排序数据

第 3 章 单行函数

第 4 章 多表查询

第 5 章 分组函数

第 6 章 子查询

第 7 章 创建和管理表

第 8 章 数据处理

第9章 约束

第 10 章 视图

第 11 章 其它数据库对象

第 12章 SET操作符

第十三章 ＰＬ／ＳＱＬ

相关文章

赞助商

阅读排行

第 1 章基本SQL-SELECT语句

第 2 章过滤和排序数据

第 3 章单行函数

第 4 章多表查询

第 5 章分组函数

第 6 章子查询

第 7 章创建和管理表

第 8 章数据处理

第9章约束

第 10 章视图

第 11 章其它数据库对象

第十三章　ＰＬ／ＳＱＬ