##子查询
1.使用子查询 查询谁的工资比 Abel高
-- 谁的工资比 Abel高
select salary,last_name
from employees
where salary>(select salary
from employees
where last_name = 'Abel')
2.查询员工名为chen的manager的信息
-- 查询员工名为chen的manager的信息
select employee_id,last_name,email
from employees
where employee_id = (select manager_id
from employees
where lower(last_name) = 'chen'
)
3.返回job_id与141号员工相同,salary 比 143号员工多的员工姓名，job_id和工资
-- 返回job_id与141号员工相同,salary 比 143号员工多的员工姓名，job_id和工资
select last_name,job_id,salary
from employees
where job_id = (select job_id
from employees
where employee_id = 141)
and salary > (select salary
from employees
where employee_id = 143)
4.返回工资最少的 last_name,job_id,salary
-- 返回工资最少的 last_name,job_id,salary
select last_name,job_id,salary
from employees
where salary = (select min(salary)
from employees)
5.查询最低工资大于50号部门的最低工资的部门id和它的最低工资
-- 查询最低工资大于50号部门的最低工资的部门id和它的最低工资
select department_id,min(salary)
from employees
group by department_id
having min(salary) > (select min(salary)
from employees
where department_id = 50)
注意当子查询中的内容返回是空时，总查询不会报错，并且不会返回任何结果
使用any 关键字来查询任意值，all所有的都满足
6.返回其他部门中比job_id为’IT_PROG’部门任一工资低的员工的员工号，姓名，job_id 以及salary
-- 返回其他部门中比job_id为'IT_PROG'部门任一工资低的
--员工的员工号，姓名，job_id 以及salary
select employee_id,last_name,job_id,salary
from employees
where job_id <> 'IT_PROG' and salary < any (
select salary
from employees
where job_id = 'IT_PROG'
)
####子查询练习
1.查询工资最低的员工信息
-- 查询工资最低的员工信息
select last_name,salary
from employees
where salary = (select min(salary)
from employees)
2.查询平均工资最低的部门信息
-- 查询平均工资最低的部门信息
--1.查询各部门的平均工资
--2.找到各部门中工资最低的
--3.哪个部门平均 工资 等于2的结果
--4.查询此部门的部门信息
select *
from departments
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
)
3.查询平均工资最低的部门信息，和它的平均工资
select d.* ,(select avg(salary) from employees where department_id = d.department_id)
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
)
4.查询平均工资最高的job的信息
-- 查询平均工资最高的job的信息
--1.按 job 分组查询 最高的平均工资
--2.查询得到哪个job_id的平均工资等于1，得到的值
--3.从 jobs 表中返回job_id对应项的信息
select * from jobs
-- 注意这里可能会有多个值，所以要使用in
where job_id in (
select job_id
from employees
having avg(salary) = (
select max(avg(salary))
from employees
group by job_id
)
group by job_id
)
5.查询平均工资高于公司的平均工资的部门
-- 查询平均工资高于公司的平均工资的部门
select department_id,avg(salary)
from employees
group by department_id
having avg(salary) > (select avg(salary)
from employees
)
6.查询出公司中所有 manager 的详细信息
-- 查询出公司中所有 manager 的详细信息
select * from employees
where employee_id in(
select manager_id
from employees
)
7.查询出各个部门中 最高工资中最低的那个部门的 最低工资是多少
-- 查询出各个部门中 最高工资中最低的那个部门的 最低工资是多少
-- 1.查询所有最高工资最低的工资
-- 2. 根据这个工资查询部门号
select department_id
from employees
having max(salary) = (
select min(max(salary))
from employees
group by department_id
)
group by department_id
8.查询平均工资最高的部门的manager 的 详细信息
-- 查询平均工资最高的部门的manager 的 详细信息
-- 1.查询出平均工资最高的部门
-- 2.根据部门id来查询下面所有的manager_id
-- 3.根据manager_id来查找所有的信息
select last_name,employee_id,email,salary
from employees
where (employee_id in (
select distinct manager_id
from employees
where department_id = (
select department_id
from employees
having avg(salary) in (
select max(avg(salary))
from employees
group by department_id
)
group by department_id
)
)
)
9.查询1999年来公司的员工中最高工资的那个员工信息
-- 查询1999年来公司的员工中最高工资的那个员工信息
select *
from employees
where salary in (
select max(salary)
from employees
where employee_id in (
select employee_id
from employees
where to_char(hire_date,'yyyy') ='1999'
)
)
10.查询和Zlotkey相同部门的员工姓名和雇用日期
--查询和Zlotkey相同部门的员工姓名和雇用日期
select last_name,hire_date
from employees
where department_id = (
select department_id
from employees
where lower(last_name) = 'zlotkey'
)
and lower(last_name) != 'zlotkey'
11.查询工资比公司平均工资高的员工的员工号，姓名和工资。
--2.查询工资比公司平均工资高的员工的员工号，姓名和工资。
select employee_id,last_name,salary
from employees
where salary > (
select avg(salary)
from employees
)
12.查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资
select employee_id,last_name,salary
from employees e1
where salary > (
select avg(salary)
from employees e2
where e1.department_id = e2.department_id
group by department_id
)
13.查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名
--查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名
select employee_id,last_name
from employees
where department_id in (
select department_id
from employees
where last_name like '%u%'
)
and last_name not like '%u%'
14.查询在部门的location_id为1700的部门工作的员工的员工号
--查询在部门的location_id为1700的部门工作的员工的员工号
select employee_id
from employees
where department_id in (
select department_id
from departments
where location_id = 1700
)
15.查询管理者是King的员工姓名和工资
--查询管理者是King的员工姓名和工资
select last_name,salary
from employees where manager_id in (
select employee_id
from employees where last_name = 'King'
)
####管理和创建表
DML用于查询和修改数据
DDL 定义数据库的结构，创建修改删除数据库对象
DCL 用于控制数据库的访问
#####查询用户定义的表
SELECT table_name
From user_tables;
#####查看用户定义的数据库对象
SELECT DISTINCT object_type
FROM USER_OBJECTS;
#####查看用户定义的表，视图，同义词和序列
SELECT * FROM USER_CATALOG
####命名规则
- 必须以字母开头
- 必须在1-30个字符之间
- 只能包含a-z A-Z 0-9 _ $ #
- 必须不能和用户定义的其他对象重名
- 必须不能是oracle的保留字
####创建表
1.第一种方式创建表（白手起家）
oracle使用的是varchar2 mysql使用varchar
create table emp1(
id number(10),
name varchar2(20),
salary number(10,2),
hire_date date
)
2.创建表的第二种方式（依托于现有的表 ）
获取employees的三列值
CREATE table emp2
AS
SELECT employee_id id,last_name name,hire_date,salary
FROM employees
只获取employees的 80 号部门的数据
CREATE TABLE emp3
AS
SELECT employee_id id,last_name name,hire_date,salary
from employees
where department_id = 80
如果想不获取数据直接就让过滤条件不满足就行
CREATE TABLE emp4
AS
SELECT employee_id id,last_name name,hire_date,salary
from employees
where 1=2
####数据类型
- VARCHAR2(size) 可变长字符串数据
- CHAR(size) 定长字符串数据
- NUMBER(p,s) 可变长数值数据
- DATE 日期
- LONG 可变长字符数据，最大达到2G
- CLOB 字符数据，最大可达到4G
- BLOB 二进制数据最大达到4G
- BFILE 存贮外部文件的二进制数据，最大可以达到4G
- ROWID 行地址
####为现有的表 添加字段 修改字段 删除列 重命名列
- 注意当你表中有数据时，如果修改的的字段属性变成其他类型是不行的，当你修改的字段属性只是修改的字段长度是只会对后面加入的数据有影响。
为emp1添加 email 字段
--创建 表 表名 create table table_name
alter table emp1
add(email varchar2(20))
将emp1的id字段 修改成15
alter table emp1
modify (id number(15))
将emp1的salary字段修改成20,2位小数 默认值200
alter table emp1
modify (salary number(20,2) default 200)
将emp1表中的 email 列删除
alter table emp1
drop column email
将emp1中的表 salary列名修改成 sal
alter table emp1
rename column salary to sal
回滚，只有增删改才能回滚，修改表结构不能回滚因为已经自动提交了
rollback;
####清空表中的内容
第一种方式清空 删除表中的所有数据释放空间（不可回滚）
truncate table emp3;
第二种方式清空（可以回滚）
delete from emp2
重命名表
rename emp2 to employees2;
1.创建表dept1
name Null? type
id Number(7)
name Varchar2(25)
sql语句
create table dept(
id number(7),
name varchar2(25)
)
2.将表departments中的数据插入新表dept2中
create table dept2
as select * from departments
3.创建表emp5
name Null? type
id Number(7)
First_name Varchar2(25)
Last_name Varchar2(25)
Dept_id Number(7)
sql 语句
create table emp5(
id number(7),
First_name varchar2(25),
Last_name varchar2(25),
Dept_id number(7)
)
4.将列Last_name的长度增加到50
alter table emp5
modify (last_name varchar2(50))
5.根据表employees创建employees2
create table employees2
as select * from employees
6.删除表emp5
--6.删除表emp5
drop table emp5
7.将表employees2重命名为emp5
--7.将表employees2重命名为emp5
rename employeess2 to emp5
8.在表dept和emp5中添加新列test_column，并检查所作的操作
--在表dept和emp5中添加新列test_column，并检查所作的操作
alter table dept
add(test_column number(20))
9.在表dept和emp5中将列test_column设置成不可用，之后删除
设置成不可用
alter table dept
set unused column test_column
删除不用的列
alter table dept
drop unused columns
10.直接删除表emp5中的列 dept_id
--10.直接删除表emp5中的列 dept_id
alter table emp5
drop column department_id
####插入数据
#####第一种方式 一条一条添加
向emp1中插入一条数据
insert into emp1
values(1001，'aa',sysdate,10000)
当你插入的类型是date类型的时候需要用到to_date转换，空值用Null代替
insert into emp1
values(1002，'BB',to_date('1998-12-21','yyyy-mm-dd'),20000)
只选择其中三项值添加（可以改变字段的顺序添加）不可用出现非空约束的列为null
insert into emp1(employee_id,last_name,hire_date)
values(1003，'BB',to_date('1998-12-21','yyyy-mm-dd'))
#####第二种方式从其他表拷贝数据
将现有的数据从employees表中导入到emp1中
insert into emp1(employee_id,hire_date,last_name,salary)
select employee_id,hire_date,last_name,salary
from employees
where department_id = 80
#####插入一条数据 使用的是脚本
insert into emp1(employee_id,last_name,salary,hire_date)
values(10007,'xxx',1000,'7-6月-1995')
####更新数据
按照条件查询取得对象然后使用set来更新值
update emp1
set salary = 10000
where employee_id = 10007
如果你添加数据是可以回滚的，但是你可以使用commit提交来使它不能回滚
commit;
1.更新114号员工的工作和工资使其与205号员工的工作和工资相同
--更新114号员工的工作和工资使其与205号员工的工作和工资相同
update employees1
set job_id=(
select job_id
from employees1
where employee_id = 205
),salary = (
select salary
from employees1
where employee_id = 205
)
where employee_id = 114
2.调整employee_id 为200的员工的job_id相同的员工的department_id为employee_id为100的员工的department_id
--调整employee_id 为200的员工的job_id相同的
--员工的department_id为employee_id为100的员工的department_id
update employees1
set department_id = (
select department_id
from employees1
where employee_id = 100
)
where job_id = (
select job_id
from employees1
where employee_id = 100
)
####删除操作
3.从empoyees1表中删除部门名称中含Public字符的部门id
--从empoyees1表中删除部门名称中含Public字符的部门id
delete from employees1
where department_id = (
select department_id
from departments
where department_name like '%Public%'
)
##事务
一组逻辑单元，使数据从一种状态变成另一种状态。
构成
- 一个或多个dml语句
- 一个ddl语句 （数据定义语言）
- 一个dcl语句 （数据控制语言）
以commit 或者rollback 结束
创建一个存储点A（相当于存档）
savepoint A;
返回到A这个位置
rollback to savepoint A;
只有锁被释放的时候，其他用户才可以操作涉及到的数据
####练习1
1.更改108号员工的信息：使其工资变成所在部门的最高工资
job变为公司中平均工资最低的job
--更改108号员工的信息：使其工资变成所在部门的最高工资
--job变为公司中平均工资最低的job
update employees1
set salary = (
select max(salary)
from employees1
where department_id = (
select department_id
from employees1
where employee_id = 108
)
),
job_id = (
select job_id
from employees1
group by job_id
having avg(salary) = (
select min(avg(salary))
from employees1
group by job_id
)
)
where employee_id = 108
2.删除108号员工所在部门中工资最低的那个员工
--删除108号员工所在部门中工资最低的那个员工
--优化版
delete from employees1 e
where employee_id = (
select employee_id
from employees1
where salary = (
select min(salary)
from employees1
where department_id = e.department_id
)
and department_id = (
select department_id
from employees1
where employee_id = 108
)
)