- 相关推荐
Oracle复习知识点汇总
Oracle认证考试由Oracle公司授权国际考试认证中心对考生进行资格认证的。那么Oracle认证考试复习要点有哪些呢?下面跟yjbys小编一起来学习吧!
desc emp; 显示emp表的结构
select count(*) FROM emp; 统计记录条数 count(*)是统计函数
SELECT * FROM emp; 查询所有的列
SELECT ename,hiredate FROM emp; 查询员工的入职时间(投影)
SELECT ename,sal AS Salary,job FROM emp; 查询员工的sal，显示为Salary---------结果是显示为SALARY
SELECT ename,sal "Salary",job "Job" FROM emp; 更改5为如此，as可以省略
******************select 子句的功能****************
SELECT ename,sal,sal*12 "Year_Salary" FROM emp; 查询员工的年薪，显示为Year_Salary
SELECT ename,sal,comm,sal+comm "Month_sal" FROM emp; 查询员工月总酬劳(月薪+奖金)-------结果出现NULL
SELECT ename,sal,nvl(comm,0) "Comm",sal+nvl(comm,0) "Month_sal" FROM emp; 更改点7，nvl(列名，值)表示如果该字段为NULL则把它转换为该值
SELECT ename || ' works as '|| job "work" FROM emp; ||为连接表达式，可随意连接额外的字符-------注意：额外字符要用''括起来
SELECT distinct job FROM emp; distinct作用是去掉重复值
***************where 子句的功能******************
SELECT ename,deptno,job from emp where deptno=&no; 利用绑定变量的形式查询不同部门的员工姓名，所在部门，职位;&no是我们待输入的值
SELECT ename,job FROM emp WHERE sal between &sal1 and &sal2 需要多少个参数就定义多少个变量
SELECT ename,sal,job FROM emp where job='MANAGER';查询职位是“MANAGER”的职员信息-----注意：=后要用' ',表中的数据有大小写区分
SELECT ename,sal FROM emp where sal between 1500 and 3000; between...and的用法，查询工资在1500～3000员工信息;取反为：not between
SELECT ename, deptno,job from emp where deptno=10 or deptno=20; or的用法，只要一个满足条件就显示
SELECT ename, deptno,job from emp where job in('MANAGER','ANALYST'); 14、in的用法，可代替or，只要满足括号里的一个字段就显示;取反为：not in
SELECT ename,job from emp where ename like 'M%' like用作模糊查询，%表示0或多个字符，_表示任意单个字符，如'_M%'则表示第二个字符为M的字段;取反为：not like
SELECT ename,job from emp where ename like 'A\_%' ESCAPE '\'; 显示以A_***格式的字段，escape表示\后面的字符不作为通配符来用
SELECT ename,comm FROM emp where comm is NULL; 查询没有奖金的员工，即comm为空，不可以写为comm=null;取反：is not null
SELECT ename,job FROM emp WHERE job <> 'SALESMAN'; 查询职位表示SALESMAN的员工，<>表示为不等于
SELECT ename,sal,deptno FROM emp WHERE sal>=1000 and deptno=10 or deptno=20; 先对A和B求交集，再和C求并集
SELECT ename,sal,deptno FROM emp WHERE sal>=1000 and (deptno=10 or deptno=20); 先对B和C求并集，再和A求交集
Alter session set nls_language='AMERICAN'; 转换为英文环境
Select *from emp where hiredate between '01-JAN-81' and '31-DEC-81'; 查询入职年份为1981的员工信息
***************order by子句的功能***************************
正序(asc)是从小到大，倒序(desc)是从大到小
SELECT ename,sal,deptno
SELECT ename,sal,deptno FROM emp order by deptno,sal DESC; 先对deptno进行正序排序(asc省略了)，后对sal进行倒序(DESC)排序
desc dual; dual是一个虚表，只有一个字段
select * FROM dual;
***********************单行函数**************************
*******1、字符函数*******
SELECT lower('Sql Server') from dual; lower()转换为小写
SELECT initcap('sql server') FROM dual; initcap()首字母大写
SELECT * FROM emp WHERE upper(ename)='SCOTT'; upper()转换为大写，在不知道其数据的大小写情况下使用方便
SELECT concat('hello', 'world') from dual;concat()连接字符串
SELECT substr('helloworld', 3, 3) FROM dual; substr(字符串，开始位置，截取长度)截取子串，从1开始算
SELECT length('helloworld') FROM dual; length()求字符串长度
SELECT lpad('hello',10,'#') FROM dual; lpad(原字符串，设置的总长度，定义字符)，不足总长度就左边补字符
SELECT rpad('hello',10,'#') FROM dual; rpad(原字符串，设置的总长度，定义字符)，不足总长度就右边补字符
*******2、数字函数********
SELECT round(45.678,2) from dual; round()四舍五入,默认有效位为0---------特例，当有效位为-1，结果是50
SELECT trunc(45.678,-1) FROM dual; trunc()截取----------- 特例，当有效位为-1，结果是40
select mod(16,5) FROM dual; mod()取模，相当于C语言中的%运算符，如16%5=1
*******3、日期函数********
SELECT sysdate FROM dual; sysdate获取当前系统日期
SELECT ename, round(sysdate-hiredate,2) hiredate FROM emp;计算员工入职天数
SELECT ename, round((sysdate-hiredate)/365) hiredate FROM emp; 计算员工入职年数
SELECT to_char (sysdate,'yyyy-mm-dd hh24:mi:ss') FROM dual; to_char()指定日期时间数据的格式
SELECT to_char (sysdate,'yyyy"年"mm"月"dd"日"') FROM dual; 注释：补to_char(),加入非日期格式的内容用""引起来
select ename,round(months_between(sysdate,hiredate)) hiredate from emp; months_between()表示间隔多少个月
select add_months(sysdate,6) FROM dual; add_months()表示加上多少个月--------注意区分sysdate+6(加6天)
SELECT next_day(sysdate,'星期一') FROM dual; next_day()表示下一个周几的哪一天，周日代表1，周一代表2...'星期一'是在中文环境里的表示方法，非中文要表示为'mon'
Alter session set nls_language='AMERICAN'; 转换为英文环境
Alter session set nls_language='SIMPLIFIED CHINESE'; 转换为中文环境
select last_day('21-NOV-2012') from dual; last_day()表示本月最后一天是几号
********4、转换函数********
select to_number('12')*12 from dual; to_number()字符串转换为数字
select to_number('$12,345.67','$99,999.99') from dual; 补to_number()，表示仅仅取数字，不取美元符号(即带两个参数，同样的格式)
select to_char(12345.67,'$000,000.00') from dual;to_char(数值，表示格式)数字转换为字符串，0的作用是显示数字，如果位数不够，用0补齐;若0改为9，则9的用法是显示数字并忽略前面的0;最常用的是小数点前为9，后为0
insert into emp(empno,ename,hiredate)
values ('2233','amy1',to_date(' 2012-10-01','yyyy-mm-dd')); to_date(日期，格式)字符串转换为日期---------注意，插入数据时，类型要对应
********5、通用函数*******
select ename,sal,comm,nvl(comm,100) bonus from emp; nvl(列名，数据)空值处理函数，数据可以是数字、日期和字符串
select ename,comm,sal,coalesce(comm,sal*0.5m,100) bonus from emp; coalesce()参数列表函数，作用是返回参数列表第一个非空参数;该语句含义是：如果comm不是null，则奖金额为comm，如果为null，则奖金额为sal*0.5，如果comm和sal都是null，则安慰奖100
select ename,job,sal decode
(job,'SALESMAN',1.1*sal,'ANALYST',1.05*sal,'MANAGER0',1.02*sal,sal) new_salary from emp; decode(判断条件，匹配1，值，匹配2，值，...，默认值，相当于C语言中的case语句)
*******6、组函数*******
如果函数中写列名，默认忽略空值
select count(*) from emp; count(列名)统计记录总数
select sum(sal),avg(sal) from emp; sum(列名)计算总和;avg(列名)计算平均数---------注意：这两个函数只针对数字类型
select max(sal),min(sal) from emp; min(列名)求最小值;max(列名)求最大值-----------注意：这两个函数适用于任何类型
************GROUP BY及HAVING子句*******************
select deptno,sum(sal) from emp group by deptno; 计算每个部门员工的工资和，一个一个部门来，
select job,avg(sal) from emp;报错：不是单组分组函数。应该改为：select avg(sal) from emp group by job;即先分组，再求平均数
注意：select后出现的列，凡是没有被组函数包围的列, 必须出现在group by短语中;出现在group by后面的列，不一定出现在select后面，但是信息不完整。
结论：select后面的非组函数的列和group by后面列保持一致。*/
select job,max(sal) Max_s,min(sal) Min_s,count(*) from emp group by job; 注释：区别where 是对表中数据的过滤;having是对分组得到的结果数据进一步过滤。select--from--where--group by--having--order by 正常执行顺序
select deptno,avg(sal) from emp group by deptno having avg(sal)>2000; 查询哪些部门的平均薪水大于2000;对部门进行分组，求平均薪水，但只有平均薪水大于2000才显示出来----------注意：凡是对组函数过滤，使用having，非组函数用where
select job,count(*) from emp where job is not null group by job having count(*)>2 order by count(*); 先找出job不为空的记录，接着对job进行分组，然后选取人数大于2的记录，最后按每组记录个数从小到大排序输出(asc省略了)
select deptno,count(*) from emp group by deptno having count(*)>5 order by count(*);
****************非关联子查询*****************
select ename from emp where sal>(select sal from emp where ename='FORD'); 先执行子查询，然后在作为一个结果参与主查询的执行*/
select ename,sal from emp where sal=(select max(sal)from emp); 找出最大工资的值，然后与其它值进行比较，相等的输出------------注意：单行比较运算符都只能和一个值比较*/
select ename,job from emp where deptno in (select deptno from emp where job='CLERK'); 如果子查询返回多行数据，必须使用多行比较运算符：in
select deptno,ename,sal from emp where (deptno,sal) in (select deptno,max(sal) from emp group by deptno); 子查询执行后是多行，所以要使用in，查询结果2列，where显示要与之对应*/
select deptno,avg(sal) from emp group by deptno having avg(sal)>(select avg(sal) from emp where deptno=20); 先算出每个部门的平均工资，再和20部门的平均工资进行比较
select ename,job from emp where deptno in (select deptno from emp group by deptno having avg(sal)>2000)
select ename form emp where empno not in (select mgr from emp) 查询哪些人不是领导，返回结果有null值，且使用not in 运算符，则无结果*/
*************关联子查询************
select ename,sal,deprno from emp outer where sal>(select avg(sal) from emp where deptno=outer.deptno) 查询哪些员工薪水比本部门的平均薪水高，子查询不再是独立的Sql语句，需要以来主查询传来的参数*/
select ename,job,deptno from emp outer where exists (select 'a'from emp where mgr=outer.empno); exists关键字判断子查询有没有数据返回，有则为ture，没有则为false。它不关心子查询的结果，所以select后写什么都行*/
************多表查询***************
select e.deptno,e,ename,d.dname from emp e join dept d on e.deptno=d.deptno; 两个表连接，表1 join 表2 on 条件，结论：没有部门的员工和没有员工的部门都不会出现
***内连接的表现形式***
1、等值连接
select e.ename,e.job,e.sal,d.dname from emp e join dept d on e.deptno =d.deptno and e.job='MANAGER'; 先用条件e.job = ‘MANAGER’对emp表进行过滤，结果集再和dept表进行内连接。
2、非等值连接
select e.ename, e.sal, s.grade,s.losal, s.hisal from emp e join salgrade s on e.sal between s.losal and s.hisal; 查询emp表中员工的工资属于哪个级别
3、自连接
select worker.ename,manager.ename from emp worker join emp manager on worker.mgr=manager.empno; 涉及同一张表的数据
内连接总结：一张表里的记录一定在另一张表中存在匹配的记录,否则不能出现在结果集中
外连接的特征：
如果驱动表在匹配表中找不到匹配记录，则匹配一行空行。
外连接结果集=内连接结果集+驱动表在匹配表中匹配不上的记录和空值。
外连接的本质是驱动表中的数据一个都不能少。
left outer join以左边的表为驱动表。
right outer join以右边的表为驱动表。
select e.empno , e.ename , d.dname from emp e left outer join dept d on e.deptno = d.deptno ; 结果集中包括有部门的员工和没有部门的员工 */
select e.empno , e.ename , d.dname from dept d left outer join emp e on e.deptno = d.deptno; 结果集中包括有员工的部门和没有员工的部门*/
结论：t1 left outer join t2等价于: t2 right outer join t1
t1 right outer join t2等价于:t2 left outer join t1
全外连接的结果集 = 内连接的结果集 +
驱动表中在匹配表中找不到匹配记录的数据和null值 +
匹配表中在驱动表中找不到匹配记录的数据和null值
***************集合***************
A = {1,2,3,4,5,6}
B = {2,4,6,8}
A union B = {1,2,3,4,5,6,8}
A union all B = {1,2,2,3,4,4,5,6,6,8}
A intersect B = {2,4,6}
A minus B = {1,3,5}
********数据操作语言(DML)*********
****1、insert语句****
一次增加多条记录，使用子查询代替values 。在emp表中查询部门10的员工编号，员工姓名，那么id、name与empno和ename相对应。
insert into student_ning(id, name) select empno, ename from emp where deptno = 10;
****2、复制表****
create table 表名 as 查询语句; 注释：复制全表
create table salgrade_yyy as select * from salgrade where 1<>1; 只复制结构，不复制数据*
create table emp_yyy as select empno,ename,sal*12 year_sal from emp where deptno = 10;复制一部分数据
create table emp_bak as select * from emp where 1=0; 创建一个空表
****3、update语句****
update emp_ning set sal = 1000 where empno = 7369; update语法结构： update 表名 set 列名 = 新的列值, 列名 = 新的列值. ….where 条件;
****4、delete语句****
语法结构： delete [from] 表名 where 条件;
drop table 会删除表结构和数据;truncate 删除表数据，保留表结构。drop 和truncate 都不可以回退。delete 仅删除数据，可以回退。
***5、事务语句****
包括：
commit事务提交：将所有的数据改动提交
rollback：事务回滚、回退到事务之初，数据的状态和事务开始之前完全一致。
savepoint：设置保存点 , 可以回滚( rollback )到指定的保存点。
结论
1) 提交、回滚前事务内部的数据改变只有在自己的会话中能够看到。
2) 提交、回滚前事务会对操作的数据加锁，不允许其它事务操作。
3) 如果提交( commit )后，数据的改变被确认，则
4) 所有的会话都能看到被改变的结果;
5) 数据上的锁被释放;
6) 保存数据的临时空间被释放
7) 如果回滚( rollback ) , 则
8) 数据的改变被取消;
9) 数据上的锁被释放;
0) 临时空间被释放。
*************数据定义语言***************
包括create / drop / alter / truncate
****1、建表****
建表的两种方式：
第1种，自定义表的列和数据类型
create table 表名(
列名 列的数据类型, ....
);
第2种，由一个现存的表复制新表
create table 表名
as
查询语句 ;
****2、删表****
drop 语法结构： drop table 表名 ;
select a.table_name,b.created from user_tables a join user_objects b on a.table_name = b.object_name where b.created > '10-SEP-12'; 找出12年9月10日后创建的表，删除过时的表。
****3、截取****
1) truncate 保留表结构，删除表中所有数据
2) truncate 操作不需提交( commit )，没有回退( rollback )的机会
3) 语法结构： truncate table 表名 ;
4) truncate 和 delete 的区别：
? truncate 在功能上等同于：delete + commit。
? delete 操作将删除数据存储到临时空间中，不直接删除，可以回退。
truncate 操作直接删除，不占用临时空间，不能回退。
****4、修改****
alter table temp_ning add(name char(10)); 增加列(只能增加在最后一列)
alter table temp_ning rename column password to pwd;修改字段命名
alter table temp_ning modify(pwd char(8)); 修改列的数据类型
alter table temp_ning drop column pwd; 删除列
**************数据控制语言****************
grant select on emp to zhang; 将查看emp 表的权限赋予zhang , zhang只能看不能改
revoke select on emp from zhang; 将select权限回收;zhang 账户再次访问时出错。
**************约束条件*********************
表上的约束(CONSTRAINTS)保证数据的完整性一致性
约束条件:
主键：primary key，PK = unique + not null，是约束条件的一种，
用于标识唯一+非空。
外键：foreign key，FK。Major: 专业。
唯一：unique。注册一个论坛，e-mail是唯一的。允许为空
非空：not null。注册邮箱时候，用户名、密码是非空。
检查：check。学生的年龄只能是正数。性别设定只能男/女，M:male / F:female，还可以设置为空。
****1、主键约束****
创建主键约束有2种方式:
1)建表时创建，可以建立在列级或表级(列级约束是跟在列定义后边的约束定义;表级约束就是定义完列后再定义的约束。)。
2)在建表后创建，建立在表级。
CREATE TABLE student_ning1(
id NUMBER,
name VARCHAR2(20),
CONSTRAINT stuning1_id_pk PRIMARY KEY(id));
主键约束建立在表级
****2、非空约束***
创建非空约束：只能建立在列级。
create table student_ning(
id number primary key,
name varchar2(20) not null);
****3、唯一约束****
唯一约束可以定义在表级、列级。
****4、check约束****
create table student_ning (
id number primary key,
name varchar2(20),
sex char,
constraint stuning_sex_ck check (sex in ('M','F')));
****5、外键约束****
constraint stuning_mid foreign key (mid) references major_ning(id) ); 语法结构：foreign key (外键名) references 子表(主键名)
在建立子表时，外键约束增加设定条件，有两个可选短语：on delete set null(一旦主表数据被删除，从表的外键列值设置为null。)和on delete cascade(级联删除(主表数据删除，从表相关联数据统统删除) 。)。
alter table student_ning drop constraint stuning_mid_fk; 删除约束条件
【Oracle复习知识点】相关文章：
Oracle认证：ORACLE绑定变量BINDPEEKING08-26
Oracle认证：Oracle内存结构研究-PGA篇08-26
oracle的sql语句06-18
Oracle认证考试06-05
中考英语知识点复习06-04
小升初英语知识点复习08-10
中考地理复习知识点08-20