Oracle增删改查(CRUD)

准备数据

--1.创建一个yylspace 的表空间
create tablespace yylspace
datafile 'D:\OracleDemo\yylspace.dbf'
size 5m
autoextend on
--2.创建一个yyltemp的表空间

create temporary tablespace yyltemp
tempfile 'D:\OracleDemo\yyltemp.dbf'
size 5m
autoextend on 
--3.创建一个yyl用户，使用你创建表空间和临时表空间
create  user yy
identified by yy
default tablespace yylspace
temporary tablespace yyltemp
quota 5m
on yylspace

--4.给yyl用户赋予DBA角色权限
grant dba to yy
--5.使用yyl登录

--6.创建表 使用Oracle自带表 如果创建不成功 直接使用全名表 hr.employees
create table emp as select * from hr.employees;
create table dept as select * from hr.departments;
create table job as select * from hr.jobs;
create table loc as select * from hr.locations;

介绍

hr.employees：员工表emp
hr.departments：部门表dep
hr.locations：工作地点表loc
hr.jobs：工作job

工资 ＝ 薪金 ＋ 佣金 因为写nvl(e.COMMISSION_PCT,0))这种麻烦 所以除了最后一题 其他题都没有计算工资 直接写的薪金
select * from emp; --员工表   employee 职工id first 第一 last 最后 email 邮箱 phone电话号码 hire雇佣时间 job工作 salary薪金 commission_pct 佣金百分比 manager 经理 department部门
select * from dept; --部门表  department部门  manager 经理  location 定位
select * from job; --工作地点表  title 标题 min salary 最小薪水 max salary 最大薪水
select * from loc;  --工作    location 位置 street address 街道地址 postal code 邮政编码 city 城市 state province 省州 country 国家

C Create

插入

方式一

--7.插入数据
insert into dept values (10,'IT',200,300)

方式二 指定字段

insert into dept(department_id,department_name,manager_id,location_id) values (10,'HR',200,300)

U Update

update dept d  set  d.location_id=520 where d.department_name='IT'

D Delete

1.删除表

逐条删除

drop table dept

2.删除所有数据

直接清空

 truncate table dept

3.删除一条数据

delete from dept d where d.department_name='IT'

R Retrieve

查询 18道

注：查询方法不止一种，提供的SQL不满意可以按自己想的来

1. 列出至少有一个员工的所有部门。

1． 列出至少有一个员工的所有部门。

select * from emp --department_id
select * from dept d 

select d.department_name ,count(e.department_id) from dept d,emp e where e.department_id=d. department_id  group by d.department_name having count(e.department_id) > 1

2. 列出薪金比’Alexander’多的所有员工。

2．列出薪金比'Alexander'多的所有员工。
select *  from emp e where e.salary >(select min(e.salary)  from emp e where e.first_name ='Alexander')

3. 列出所有员工的姓名及其直接上级的姓名。

3． 列出所有员工的姓名及其直接上级的姓名。
select e.first_name ,(select  p.first_name from emp p where e.manager_id=p.employee_id) from emp e

 4．请在EMP表中查找部门号在10－30之间的雇员的姓名、部门号、部门名称、工资。
select e.first_name,e.department_id,e.salary, (select d.department_name from dept d where d.department_id=e.department_id ) from emp e where e.department_id between 10 and 30

5. 列出部门名称和这些部门的员工信息，同时并列出那些没有员工的部门。
   左连接

 5．列出部门名称和这些部门的员工信息，同时并列出那些没有员工的部门。
 select d.department_name,e.*  from dept d left join emp e 
   on  d.department_id  =e.department_id

6. 列出所有在 ‘Seattle’（city等于这个值）工作的员工的姓名及其部门名称。

 6．列出所有在 'Seattle'（city等于这个值）工作的员工的姓名及其部门名称。
select * from dept
select * from loc c where c.city='Seattle' 

select e.first_name,d.department_name from emp e,dept d ,loc c  where d.location_id=c.location_id and c.city='Seattle' 

7. 列出最低薪金大于15000的各种工作。
   相关子查询

 7．列出最低薪金大于15000的各种工作。
select * from job where job.min_salary >15000
select e.salary,(select d.department_name from dept d where e.department_id=d.department_id) from emp e where e.salary>15000

8. 列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号。

 8．列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号。
select * from dept d where d.department_name='Sales'
select e.first_name from emp e ,dept d where e.department_id  =d.department_id and d.department_name='Sales'

9. 列出薪金高于公司平均薪金的所有员工。

 9．列出薪金高于公司平均薪金的所有员工。
select * from emp where emp.salary>(select avg(e.salary) from emp e )

10. 列出与“Alexander”从事相同工作的所有员工。

 10．列出与“Alexander”从事相同工作的所有员工。
 select * from emp  where emp.department_id=( select e.department_id  from emp e where e.first_name='Alexander'
 )

11. 列出 Shipping部门在Shipping城市工作的所有员工的姓名和薪金

 11．列出 Shipping部门在Shipping城市工作的所有员工的姓名和薪金

select d.location_id from dept d where d.department_name='Shipping' 
select e.first_name,e.salary from emp e where e.department_id =(select d.department_id from dept d where d.department_name='Shipping' )
select * from emp 
 select * from loc 

12. 列出薪金高于在Shipping部门工作的所有员工的薪金的员工姓名和薪金。(大于员工最大工资就可以大于所有)

 12．列出薪金高于在Shipping部门工作的所有员工的平均薪金的员工姓名和薪金。(大于最大值)
select * from emp   where emp.salary >
(select max( e.salary) from emp e where e.department_id =(select d.department_id from dept d where d.department_name='Shipping' )
)

13. 列出在每个部门工作的员工数量、平均工资。

 13．列出在每个部门工作的员工数量、平均工资。

select d.department_name ,count(e.department_id) ,avg(e.salary) from dept d,emp e where e.department_id=d. department_id  group by d.department_name  

14. 列出所有员工的姓名、部门名称、工作地址、岗位名称和工资。
    左查询

 14．列出所有员工的姓名、部门名称、工作地址、岗位名称和工资。
--select * from loc
--select * from job
  where e.job_id=j.job_id and d.location_id=l.location_id and e.department_id=d.department_id
select e.first_name 姓名,d.department_name 部门名称,l.street_address 工作地址,j.job_title 岗位名称,e.salary 工资 from emp e left 
                         join dept d on e.department_id = d.department_id
                         left join loc l on d.location_id = l.location_id
                         left join job j on j.job_id = e.job_id

15. 列出所有部门名字和部门人数、部门薪资总和。

 15．列出所有部门名字和部门人数、部门薪资总和。

select d.department_name ,count(e.department_id) ,sum(e.salary) from dept d,emp e where e.department_id=d. department_id  group by d.department_name  

16. 列出各种工作的最低工资。

 16．列出各种工作的最低工资。
select j.job_title,min( j.min_salary) from job j group by j.job_title  

• 列出各个部门的MANAGER（经理）的最低薪金。

17．列出各个部门的MANAGER（经理）的最低薪金。
select * from dept 
select * from job 
select * from emp
select    e.employee_id,  j.min_salary  from dept  d , job  j ,emp e where d.manager_id=e.employee_id and j.job_id=e.job_id

• 18．列出所有员工的年工资,按年薪从低到高排序。

18．列出所有员工的年工资,按年薪从低到高排序。
select  (e.SALARY+nvl(e.COMMISSION_PCT,0))*12  from emp e order by e.salary 

2.拓展

1.基于伪列的查询

伪列是oracle中独有的，伪列也是真实存在的列，也是可以用来进行查询操作的，伪列只能用来查，不能做增删改操作
1 ROWID：物理文件上唯一区别这条记录的唯一标识
表中的每一行在数据文件中都有一个物理地址，ROWID伪列返回的就是该行的物理地址。使用ROWID可以快速的定位表中的某一行。ROWID值可以唯一的标识表中的一行。由于ROWID返回的是该行的物理地址，因此使用ROWID可以显示行是如何存储的。

select rowID,e.* from emp e

2 ROWNUM 分页查询需要用到此伪列
在查询的结果集中，ROWNUM为结果集中每一行标识一个行号，第一行返回1，第二行返回2，以此类推。通过ROWNUM伪列可以限制查询结果集中返回的行数。

select rownum,e.* from emp e

2.drop、delete与truncate的区别

SQL中的drop、delete、truncate都表示删除，但是三者有一些差别

1. delete和truncate只删除表的数据不删除表的结构
2. 速度,一般来说: drop> truncate >delete
3. delete语句是dml，数据删除可以回滚；truncate,drop是ddl，数据删除后不能回滚

使用场景：
不再需要一张表的时候，用drop
想删除部分数据行时候，用delete，并且带上where子句
保留表而删除所有数据的时候用truncate

3.分页查询

1.简单分页

需求：分页查询deal，每页10条记录
我们首先显示前10条记录，语句如下：

--查询所有
select * from deal
--分页
select rownum,d.* from deal d where rownum<=10;

那么我们显示第11条到第20条的记录呢？编写语句：

--10-20
select rownum,d.* from deal d
where rownum>10 and rownum<=20

Emmm…怎么没有结果？
这是因为rownum是在查询语句扫描每条记录时产生的，所以不能使用“大于”符号，只能使用“小于”或“小于等于” ，只用“等于”也不行。
那怎么办呢？我们可以使用子查询来实现，后写分页条件

--10-20
select * from 
(select rownum r,d.* from deal d where rownum<=20)
where r>10

2.基于排序的分页

需求：分页查询台账表deal，每页10条记录，按使用字数降序排序。

select * from 
(select rownum r,d.* from deal d where rownum<=20 order by d.volume desc)
where r>10

经过验证，我们看到第2页的结果应该是下列记录

所以推断刚才的语句是错误的！那为什么是错误的呢？
我们可以先单独执行嵌套查询里面的那句话

select rownum r,d.* from deal d
where rownum<=20 order by d.volume desc

你会发现排序后的R是乱的。这是因为ROWNUM伪列的产生是在表记录扫描是产生的，而排序是后进行的，排序时R已经产生了，所以排序后R是乱的。

那该如何写呢？
很简单，我们只要再嵌套一层循环（一共三层），让结果先排序，然后对排序后的结果再产生R，这样就不会乱了。
语句如下：

select a.* from 
(select rownum r,t.* from 
        (select * from deal d order by d.volume desc) t) a
where a.r between 11 and 20;

下一章讲Oracle的函数

祝你幸福
送一首歌：《那些花儿》朴树
附图：象限仪座流星群 北京天文馆镜像