oracle基础知识，oracle sql练习

一、建表

--dept职位表
drop table dept_test; --删除语句
create table dept_test(
deptno number(2) ,
dname char(20) ,
location char(20)) ;

insert into dept_test values(10 , 'developer' , 'beijing') ;
insert into dept_test values(20 , 'account' , 'shanghai') ;
insert into dept_test values(30 , 'sales' , 'guangzhou') ;
insert into dept_test values(40 , 'operations' , 'tianjin') ;
commit ; --事务控制语句

--emp员工表
drop table emp_test;
create table emp_test(
empno number(4) ,
ename varchar2(20) ,
job varchar2(15) ,
salary number(7 , 2) ,
bonus number(7 , 2) ,
hiredate date,
mgr number(4) ,
deptno number(10)
) ;
insert into emp_test values(1001 , '张无忌' , 'Manager' ,10000 , 2000 , '12-MAR-10' , 1005 , 10) ;
insert into emp_test values(1002 , '刘苍松' , 'Analyst' ,8000 , 1000 , '01-APR-11' , 1001, 10) ;
insert into emp_test values(1003 , '李翊' , 'Analyst' ,9000 , 1000 , '11-APR-10' , 1001, 10) ;
insert into emp_test values(1004 , '郭芙蓉' , 'Programmer' ,5000 , null , '01-JAN-11' , 1001 , 10) ;
insert into emp_test values(1005 , '张三丰' , 'President' ,15000 , null , '15-MAY-08' , null , 20) ;
insert into emp_test values(1006 , '燕小六' , 'Manager' ,5000 , 400 , '01-FEB-09' , 1005 , 20) ;
insert into emp_test values(1007 , '陆无双' , 'clerk' ,3000 , 500 , '01-FEB-09' , 1006 , 20) ;
insert into emp_test values(1008 , '黄蓉' , 'Manager' ,5000 , 500 , '1-MAY-09' , 1005 , 30) ;
insert into emp_test values(1009 , '韦小宝' , 'salesman' ,4000 , null , '20-FEB-09' , 1008 , 30) ;
insert into emp_test values(1010 , '郭靖' , 'salesman' ,4500 , 500 , '10-MAY-09' , 1008 , 30) ;
commit ; --事务控制语句
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

二、练习sql（由浅入深）

1、计算员工的月收入？（包括工资和奖金） 
错误写法：select ename , salary , bonus , salary+bonus month_sal from emp_test ; 
关于空值：空值和任何数据做算数运算 , 结果都是 null；空值和字符串类型做连接操作 , 结果相当与空值不存在。 
正确写法：select ename, salary , bonus , salary + nvl(bonus, 0) month_sal from emp_test ;

2、distinct 关键字的使用 
distinct 必须( 只能 )跟在 select 后边 
机构中有多少种职位？ 
select distinct job from emp_test ; 
查询每个部门不重复的职位？ 
select distinct deptno, job from emp_test ;

3、模糊匹配 
如果要查询的数据中有特殊字符( 比如_或% ),在做模糊查询时,需要加上\符号表示转义 , 并且用 escape 短语指明转义字符\

列出职位中第二个字符是 a 的员工数据？ 
select * from emp_test where job like ‘_a%’ ; 
查询数据库中有多少个名字中以 ‘S_’ 开头的表？ 
select count(*) from user_tables where table_name like ‘S/_%’ escape ‘/’ ; 
查询数据库中名字带‘%’的数据？ 
select * from user_tables where table_name like ‘%/%%’ escape ‘/’

---

---

下面练习一些常用函数

4、nvl(d1 , d2) 
如果 d1 为 null 则用 d2 替代 
计算总月收入多少 
select ename, salary , bonus , salary + nvl(bonus, 0) month_sal from emp_test ; 
mysql中空值处理：mysql中没有nvl（expr1,expr2）函数，但是有下面两个： 
ifNull(expr1,expr2)：如果expr1不为空则结果为expr1，如果expr1为空则结果为expr2; 
if(expr1,expr2,expr3)：如果expr1为true，则结果为expr2，如果expr1为false则结果为expr3；

5、字符函数 
substr/ upper / lower / initcap/length / lpad / rpad / replace / trim

6、数字函数 
round( 数字 , 小数点后的位数 )：用于数字的四舍五入 
trunc( 数字 , 小数点后的位数 )：用于截取(mysql没有该函数) 
ceil(n) 取大于等于数值n的最小整数； 
floor(n)取小于等于数值n的最大整数

7、日期函数 
months_between 两个日期之间的月份数 
add_months 给定一个日期 , 为该日期增加指定月份 
last_day 找出参数时间点所在月份的最后一天

select sysdate from dual ; –dual 为虚表，获取系统当前时间 
计算员工入职多少天? 
select ename , hiredate , round( sysdate - hiredate ) days from emp_test ;–日期数据相减 , 得到两个日期之间的天数差 , 不足一天用小数表示。可以用 round 函数处理一下 
计算员工入职多少个月？ 
select ename , hiredate ,round( months_between( sysdate , hiredate ) ) months from emp_test ; 
计算 12 个月之前的时间点 
select add_months(sysdate, -12) from dual; 
计算本月的最后一天 
select last_day(sysdate) from dual;

8、转换函数 
to_char(日期数据 , 格式): 将日期数据 按指定格式 转换为 字符串数据 
select to_char( sysdate , ‘yyyy-mm-dd hh24：mi：ss ‘) from dual ; 
to_date(日期数据 , 格式)：将字符串数据 按指定格式 转换为 日期数据 
insert into emp_test( empno , ename , hiredate ) values( 1012 , ‘amy ’ ,to_date( ‘2011-10-10 ’ , ‘yyyy-mm-dd ’ ) ) ; 
to_number(数字，格式)：将字符串转换成数字格式 
select to_number(‘ 7,912,345.67′,′ 9,999,999.99’) from dual ;–输出7912345.7

mysql中使用 CAST(xxx AS 类型) 该函数进行转换，日期转字符串格式用DATE_FORMAT函数。

9、case 语句 
相当于 Java 中的 switch-case 语句

根据员工的职位 , 计算加薪后的薪水数据 
要求： 
1) 如果职位是 Analyst：加薪 10% 
2) 如果职位是 Programmer：加薪 5% 
3) 如果职位是 clerk：加薪 2% 
4) 其他职位：薪水不变

select ename , salary , job ,
        case job when 'Analyst' then salary * 1.1 --注意这里没有“ , ”
                 when 'Programmer' then salary * 1.05
                 when 'clerk' then salary * 1.02
                else salary        --else 相当于 Java 中 case 语句的 default
        end new_salary     --end 是 case 语句的结束标识
from emp_test ;    --new_salary是从case开始到end结束这部分的别名

  
1
2
3
4
5
6
7
8

10、decode 函数 
decode()函数是Oracle 中等价于 case when 语句的函数 , 作用同 case 语句相同。 
decode 函数语法如下： 
decode(判断条件 , 匹配 1 , 值 1 , 匹配 2 , 值 2 , … , 默认值) 
表达的意思是：如果判断条件 = 匹配 1 , 则返回值 1 
判断条件 = 匹配 2 , 则返回值 2

select ename , salary , job ,
    decode( job , 'Analyst' , salary * 1.1 ,
                 'Programmer' , salary * 1.05 ,
                 'clerk' , salary * 1.02 ,
                salary) as new_salary
from emp_test ;
  
1
2
3
4
5
6

11、查询结果排序 order by 
asc:升序； 
desc：降序； 
按部门排序 , 同一部门按薪水由高到低排序 
select ename , deptno , salary from emp_test order by deptno , salary desc ;

---

---

下面练习常用组函数

12、count(*) 
注意：count 函数忽略空值，所以用conut(*)比具体某个可能存在空值的数据更能精确总数据

13、avg() sum() max() min() 
计算员工的人数总和、薪水总和、平均薪水是多少？ 
错误写法：select count(*) num , sum(salary) sum_sal , avg(salary) avg_sal from emp_test ; 
原因：薪水平均值 = 薪水总和 / 人数总和 avg(salary) = sum(salary) / count(*)，而 avg(salary)叧按有薪水的员工人数计算平均值。这样得到的数据不够准确。 
正确写法：select count(*) num , sum(salary) sum_sal ,avg(nvl(salary , 0)) avg_sal from emp_test ;

注意： 
组函数：count / avg / sum / max / min 如果函数中写列名 , 默认忽略空值 
avg / sum 针对数字的操作 
max / min 对所有数据类型都可以操作

计算最早和最晚的员工入职时间 
select max(hiredate) max_hiredate , min(hiredate) min_hiredate from emp_test ;

14、分组查询 group by 
按部门计算每个部门的最高、最低薪水、薪水总和、平均薪水、总人数分别是多少？ 
select deptno , max(salary) max_s , min(salary) min_s , sum(salary) sum_s , avg(nvl(salary,0)) avg_s, count(*) emp_num 
from emp_test group by deptno ; 
按职位分组 , 每个职位的最高、最低薪水和人数？ 
select job , max(salary) max_s ,min(salary) min_s ,count(*) emp_num 
from emp_test group by job order by emp_num ;

注意：select 后出现的列 , 凡是没有被组函数包围的列 , 必须出现在 group by 短语中。mysql没有此限制，但是那样的sql毕竟不规范。

15、having 子句 
having 子句用于对分组后的数据进行过滤。 
注意区别 where 是对表中数据的过滤 ;having 是对分组得到的结果数据进一步过滤

平均薪水大于 5000 元的部门数据 , 没有部门的不算在内？

select deptno , avg(nvl(salary , 0)) avg_s
from emp_test
where deptno is not null
group by deptno
having avg(nvl(salary , 0)) > 5000 ;
  
1
2
3
4
5

薪水总和大于 20000 元的部门数据？

select deptno , sum(salary) sum_s
from emp_test
where deptno is not null
group by deptno
having sum(salary) > 20000 ;
  
1
2
3
4
5

哪些职位的人数超过 2 个人？

select job , count(*) emp_num
from emp_test
where job is not null
group by job
having count(*) > 2
order by emp_num ;--注意：order by 一定要放到最后
  
1
2
3
4
5
6

16、总结下： 
使用频率比较高的函数 * 
1) 单行函数：substr / upper / round / to_char / to_date / nvl 
2) 组函数：count / avg / sum / max / min

查询语句的基本格式： 
select 字段 1 , 字段 2 , 字段 3 , 表达式 , 函数 , … 
from 表名 
where 条件 
group by 列名 
having 带组函数的条件 
order by 列名 
其执行顺序为：select–>from–where–group by–>having–>order by

---

---

下面开始练习多表查询

17、子查询

17-1、单行比较运算符 > < >= <= = <>

单行比较运算符都只能和一个值比较

查询最高薪水的是谁？ 
select ename from emp_test 
where salary = ( select max(salary) from emp_test) ; 
研发部有哪些职位？ 
select distinct job from emp_test 
where deptno = ( select deptno 
from dept_test 
where dname = ‘developer’ ) ;

17-2、多行运算符 >ALL >ANY <ALL <ANY in 
谁的薪水比张无忌高？ 
错误写法：select enmae from emp_test where salary > (select salary from emp_test where ename='张无忌'); 
错误理由：如果有多个叫张无忌的人将会报错，单行子查询返回多个行 
正确写法：

select ename from emp_test
where salary > ALL( select salary from emp_test
where ename = '张无忌' ) ;--查询谁的薪水比所有叫张无忌的薪水都高，把all改成any也行
  
1
2
3

谁和刘苍松同部门？列出除了刘苍松之外的员工名字

select ename,salary,job
from emp_test
where deptno = (select deptno from emp_test
where ename = '刘苍松')
and ename <> '刘苍松' ;
  
1
2
3
4
5

谁和刘苍松同部门？列出除了刘苍松之外的员工名字( 如果子查询得到的结果是多个 )

select ename,salary,job,deptno
from emp_test
where deptno in ( select deptno from emp_test
where ename = '刘苍松' )
and ename <> '刘苍松' ;
  
1
2
3
4
5

每个部门拿最高薪水的是谁？

select ename, salary, job, deptno
from emp_test
where (deptno, salary) in ( select deptno, max(salary)
from emp_test
where deptno is not null
group by deptno ) ;
  
1
2
3
4
5
6

注意：子查询的条件是单列还是多列没关系 , 关键是要分清返回的是单行还是多行。

17-3、子查询出现在 having 短语中

哪个部门的人数比部门 号30 的人数多？

select deptno , count(*)
from emp_test
group by deptno
having count(*) > ( select count(*) from emp_test
where deptno = 30 ) ;
  
1
2
3
4
5

列出员工名字和职位 , 这些员工所在的部门平均薪水大于 5000 元

select ename, job
from emp_test
where deptno in (select deptno
from emp_test
group by deptno
having avg( nvl(salary,0)) > 5000 ) ;
  
1
2
3
4
5
6

18、关联子查询

18-1、子查询中不再是独立的 Sql 语句 , 需要依赖主查询传来的参数 , 这种方式叫关联子查询

哪些员工的薪水比本部门的平均薪水低？

select ename, salary, deptno
from emp_test a
where salary < ( select avg(nvl(salary,0))
from emp_test
where deptno = a.deptno ) ;
  
1
2
3
4
5

18-2、Exists关键字和in

介绍一下existis和in: 
exists 关键字判断子查询有没有数据返回 , 有则为 ture , 没有则为 false，它不关心子查询的结果 , 所以子查询中 select 后面写什么都可以，如本例中我写常量“1”； 
in是把外表和内表作hash 连接，而exists 是对外表作loop 循环，每次loop 循环再对内表进行查询，所以如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in： 
如果查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引，而not extsts 的子查询依然能用到表上的索引，所以无论那个表大，用not exists 都比not in 要快。

哪些人是其他人的经理？

select ename from emp_test a
where exists (select 1 from emp_test
where mgr = a.empno) ;--使用了关联子查询
select ename from emp_test
where empno in ( select distinct mgr
from emp_test) ;--使用普通子查询
  
1
2
3
4
5
6

哪些人不是别人的经理？

select ename from emp_test a
where not exists (select 1 from emp_test
where mgr = a.empno) ;--关联子查询
select ename from emp_xx
where empno not in ( select distinct mgr
from emp_test
where mgr is not null) ;--普通子查询
  
1
2
3
4
5
6
7

**注意：**not in (列表)：如果列表中有 null 值 , 将没有结果返回 ;in(列表)没有关系。 
哪些部门没有员工？

select deptno, dname from dept_test d
where not exists (select 1
from emp_test
where deptno = d.deptno) ;
  
1
2
3
4

19、集合操作

两个结果集必须结构相同：当列的个数、列的顺序、列的数据类型一致时 , 我们称这两个结果集结构相同 
只有结构相同的结果集才能做集合操作 
集合类型： 
合集：union 和 union all 
union 去掉重复记录 , union all 不去重 
union 排序 , union all 不排序 
交集：intersect 
差集：minus(两个集合做减法) 
如

select ename , salary from emp_test
where deptno = 10
union
select ename , salary from emp_test
where salary > 6000 ;
  
1
2
3
4
5

---

---

下面开始练习表间关联查询

表 emp_test 和表 dept_test 之间存在的参照关系： 
1) emp_test 的所在部门( deptno )参照 dept_test 的部门编码( deptno ) 
2) dept_test 是主表( 父表 ) , emp_test 是从表( 子表 ) 
表 emp_test 自身存在一种参照关系: 
员工的经理( mgr )列参照职员编码( empno )列

20、内连接

语法：表 1 join 表 2 on 条件 
1) 表 1 叫做驱动表 , 表 2 叫做匹配表 
3) 执行方式：遍历驱动表 , 在匹配表中查找匹配数据

列出员工的姓名和所在部门的名字和城市 
select ename , dname , location 
from emp_test e join dept_test d 
on e.deptno = d.deptno ; 
通过查看结果集 , 我们可以得出结论： 
1) 子表( emp_test )中的外键值( deptno )为 null 的数据不包含在结果集中； 
2) 父表( dept_test )中主键值( deptno )没有被参照的数据不包含在结果集中；

列出员工的姓名和他的上司的姓名( 自连接 ) 
select t1.ename , t2.ename 
from emp_test t1 join emp_test t2 
on t1.mgr = t2.empno ; 
–t1 表示从表 , t2 表示主表 
– 没有上司的员工不会列出

21、外连接

1) 左外连接语法结构： 表 1 left (outer) join 表 2 on 条件 
2) 右外连接语法结构： 表 1 right (outer) join 表 2 on 条件 
3) 外连接的特征： 
 如果驱动表在匹配表中找不到匹配记录 , 则匹配一行空行 
 外连接的结果集 = 内连接的结果集 + 驱动表在匹配表中匹配不上的记录和空值 
 外连接的本质是驱动表中的数据一个都不能少 
 left join 以左边的表为驱动表 
 right join 以右边的表为驱动表

列出员工的姓名和他所在部门的名字 , 把没有部门的员工也查出来 
select e.empno , ename , d.deptno , d.dname , d.location 
from emp_test e left join dept_test d 
on e.deptno = d.deptno ;

on后面可以接等值连接，也可以非等值连接( on 后面的条件不是等值操作 )。非等值连接指在多个表间使用非等号连接 , 查询在多个表间有非等值关系的数据 , 非等值连接操 
作符包括：>、<、<>、>=、<=以及 Between And、like、in 等。

22、full outer join 全外连接

1) 全外连接可以把两个表中的记录全部查出来 
2) 全外连接的结果集 = 内连接的结果集 + 
驱动表中在匹配表中找不到匹配记录的数据和 null 值 + 
匹配表中在驱动表中找不到匹配记录的数据和 null 值 
3) 驱动表和匹配表可以互换

---

---

23、复制表 
语法： 
create table 表名 as 查询语句

23、rowid 关键字 
1) rowid 是 Oracle 数据库的伪列 , 可以看作是一条数据在数据库中的物理位置 
2) rowid 是 Oracle 数据库独有的 
注意：每一条记录的 rowid 在数据库中都是唯一的 
删除重复数据： 
delete from emp_bak1 
where rowid not in ( select max(rowid) from emp_bak1 
group by empno , ename , salary ) ; 
也可以distinct全部字段（除id）查询出所有的不重复数据;

24、rownum 关键字 
rownum 是 Oracle 数据库提供的 , 代表行号。

--查询前8条数据
select * from emp_test where rownum <= 8;
--查询第10到第15条数据
select * from (select *,rownum as num from emp_test)
where num between 10 and 15;

--rownum不支持以下方式的查询:
select * from emp_test where rownum <=2;
select * from emp_test where rownum>10 and rownum <15;
  
1
2
3
4
5
6
7
8
9

25、alter( 修改表结构 )

alter table mytemp_test add(name char(10));    --add  表增加字段
alter table mytemp_test rename column password to pwd;   --rename  字段重命名
alter table mytemp_test modify (pwd char(8));  --modify  修改字段类型
alter table mytemp_test drop column pwd; -- drop 删除列
  
1
2
3
4

26、约束条件 
26-1、主键约束 
列级约束：deptno number(2) primary key, 
表级约束：constraint dept_ning2_deptno_pk primary key (deptno) 
26-2、非空约束( not null , 简称 NN ) 
name varchar2(10) not null,

唯一约束( Unique , 简称 UK ) 
email varchar2(30) unique,

检查约束( Check , 简称 CK ) 
gender char(1) check(gender in(‘F’, ‘M’) )–‘F’代表女生 ;’M’代表男生

外键( Foreign key, 简称 FK )

全部在表级定义：

constraint student_ning3_id_pk primary key(id),
constraint student_ning3_email_uk unique(email),
constraint student_ning3_age_ck check(age > 10),
constraint student_ning3_gender_ck check(gender in('F', 'M', 'f', 'm'))
  
1
2
3
4

27、视图 View 
1) 视图的使用和表相同 
2) 视图的好处：简化查询 ;隐藏数据表的列 
3) 视图不包含任何数据。是基表数据的投影

28、索引 Index 
如果数据表有 PK/Unique 两种约束 , 索引自动创建 , 除此以外 , 索引必须手动创建 
自定义索引语法： 
create index 索引名 on 表名(列名) ;

29、序列 Sequence 
序列的特性：产生连续的不同的数字值用来作为数据表的主键，序列是数据库中的独立对象。 
建议：一个序列为一个表产生主键 
序列这种对象在 Oracle、db2 等数据库中有 , 在 mysql、sql server 中没有。

– 产生从 1 开始的数字值 , 步进是 1 
create sequence myseq_ning ; 
– 查看序列产生的值 
select myseq_ning.nextval from dual ; 
– 使用序列产生的值作为表的主键值 
insert into student_ning7(id,name) values(myseq_ning.nextval , ‘amy’) ; 
select * from sutdent_ning7 ; 
– 显示结果为 2 amy 
– 注意： 每调用一次 myseq_ning.nextval 就会获得 1 个递增的数

---

---

28、oracle与mysql的区别： 
1、字段类型上 
数字类型oracl用number,mysql用int，如果小数oracle用number(4,2)这种形式，mysql用decimal(4,2)； 
字符串oracle用varchar2(20),mysql用varchar(20),mysql没有varchar2类型； 
具体： 
数值类型： 
oracle:NUMBER/NUMBER( p,s ) 
mysql:INT或INTEGER/TINYINT/SMALLINT/MEDIUMINT/BIGINT/FLOAT/DOUBLE/DECIMAL 
字符类型： 
oracle:CHAR/VARCHAR2/VARCHAR 
mysql:CHAR/VARCHAR/TEXT/BLOB/LONGBLOB/LONGTEXT 
日期类型： 
oracle:DATE/TIMESTAMP/ 
mysql:DATE/TIME/DATETIME/TIMESTAMP/YEAR

2、个别常用函数上 
mysql字符拼接用concat函数,oracle用 || 
在时间格式化方面也有区别，oracle用to_char,mysql用DATE_FORMAT

3、分页上 
mysql用limit，oracle用rownum;

4、主键增长上 
mysql可以让主键自动增长（auto increment），但是oracle需要创建序列，然后在增加数据的时候把序列带进去；

展开更多