您的当前位置：首页 Oracle+总结 (1)

Oracle+总结 (1)

来源：九壹网

Oracle

SQL datebase (DB)

Structured query language 结构化查询语言

DDL(数据定义语言)

Date definition language create table 建表

alter table 修改表结构 drop table 删表

column data type width constraints(约束)

DML(数据操作语言)

Data manipulation language insert update delete data row record

TCL(事物控制语句)

Transcation control language

commit(提交) rollback(回滚) savepoint

DQL(数据查询语言)

Data query language select

Install software rdbms 安装软件 create database 创建数据库

登录数据库 sqlplus username/password

ORACLE_SID(环境变量) 数据库对应的实例的名字，该名字决定了连接哪个具

体的数据库

show user 查看当前用户

desc tablename 查看表结构 desc describe的缩写

查询员工的姓名，和工资

select first_name,salary from s_emp;

查询员工的名字和职位

select first_name,title from s_emp;

edit 修改sql语句 l 查看 /运行

select * from s_dept; 列出部门表的所有信息

列出每个员工的年薪

select first_name,salary*12 from s_emp;

列出每个员工的总收入

select first_name,salary*12*(1+commission_pct/100) \"tol sal\" from s_emp; 空值会导致算术表达式为空，Oracle认为null为无穷大

select first_name,salary*12*(1+nvl(commission_pct,0)/100) tol_sal, Commission_pct from s_emp

nvl(p1,p2)

if(p1 is null) then return p2; else return p1;

coalesce 和nvl实现的是同样的功能，nvl只能用在Oracle，coalesce可以用在多种数据库,nvl的两个参数的类型必须一致

给列起别名在列后直接跟别名，别名有空格，或者大小写敏感，要给别名加“”

将姓和名拼接起来 ''表示字符串，“”表示别名，||表示字符串的拼接

select first_name||last_name employee from s_emp; select first_name||' '||last_name employee from s_emp;

select first_name||' is in department '||dept_id||'.' from s_emp;

列出有哪些部门

select distinct name from s_dept;

distinct 去重(null值夜只保留一个)，只能放在select后

列出公司有哪些不同的职位

select distinct title from s_emp; set feed on 显示查询返回的记录数

各个部门有哪些不同的职位

select title,dept_id from s_emp;部门号和职位联合起来唯一

列出工资大于1000的员工

select first_name,salary from s_emp where salary>1000;

年薪大于12000的员工(where语句后的字段最好不要使用表达式，影响效率)

select first_name,salary*12 from s_emp where salary>1000;

where子句不能跟列的别名

列出Carmen的年薪

select first_name,salary from s_emp where first_name='Carmen';

哪些人的职位是Stock Clerk

select first_name,title from s_emp where title='Stock Clerk';

哪些员工的工资在1550-2000之间

select first_name,salary from s_emp where salary>=1550 and salary<=2000; select first_name,salary from s_emp where salary between 1550 and 2000;

列出部门号为31,41,43的员工的工资

select first_name,salary,dept_id from s_emp where dept_id=31 or dept_id=41 or dept_id=43;

select first_name,salary,dept_id from s_emp where dept_id in (31,41,43); select first_name,salary,dept_id from s_emp where dept_id =any (31,41,43);

%表示0或多个字符

_表示任意一个字符

select last_name from s_emp where last_name like 'M%';

系统表，user_tables 记录数据库中有哪些表

查询当前用户下有哪些表

select table_name from user_tables;

查找用户下所有以“s_”的表名

select table_name from user_tables where table_name like 'S\\_%' escape '\\'; escape '\\' 表示定义\\为转义字符

哪些员工没有提成

select first_name,commission_pct from s_emp where commission_pct is null;

判断字段的值是否为空 is null 而不是 =null

哪些员工有提成

select first_name,commission_pct from s_emp where commission_pct is not null;

列出除了31,41,43部门的员工的工资

select first_name,dept_id,salary from s_emp where dept_id not in (31,41,43); select first_name,dept_id,salary from s_emp where dept_id <>31 and dept_id <>41 and dept_id <>43;

select first_name,dept_id,salary from s_emp where dept_id <>all (31,41,43);

如果集合里面包含null值，not in 一定查不出结果，null与任何值比较都为false

列出部门号为32,42，工资大于1500的员工

select first_name,dept_id,salary from s_emp where salary>1500 and dept_id in(32,42);

列出员工的姓名和工资，并按工资由大到小排序

select first_name,salary from s_emp order by salary desc; order by 后的字段要想使用索引，必须保证不能为null

select first_name,salary from s_emp where salary is not null order by salary desc;

列出42部门的员工信息，按年薪降序排列

select first_name,dept_id,salary*12 ann_sal from s_emp where dept_id=42 order by salary desc;

select first_name,dept_id,salary*12 ann_sal from s_emp where dept_id=42 order by ann_sal desc;

select first_name,dept_id,salary*12 ann_sal from s_emp where dept_id=42 order by 3 desc;

按部门号升序排列，同一部门按工资的降序排列

select first_name,dept_id,salary from s_emp order by dept_id,salary desc; 函数

单行函数：每一条记录都对应有结果集

字符类型

lower() 将字符串转化为小写

select lower('SQL') from dual; upper() 将字符串转化为大写 initcap() 将字符的首字母大写

concat(string,string) 字符串的拼接

substr(string,1,3) 字符串的截取,第二个参数表示字符串的起始位置，第三个表示截取的长度

length(string) 字符串的长度

lpad(string,10,'*') 字符串的长度是10，不足的在左边补'*' rpad(string,10,'*') 字符串的长度是10，不足的在右边补'*' 调文字值和表没有关系的时候使用系统表dual

查询Carmen的工资（不知道大小写）

select first_name,salary from s_emp where lower(first_name)= 'carment';

找出每个人名字的最后两个字符

select first_name,substr(first_name,length(first_name)-1,2) from s_emp;

select first_name,substr(first_name,-2,2) from s_emp; 负数表示从右向左

数值类型

round(处理的数字,保留小数点后几位) 四舍五入 trunc(处理的数字,截取几位) 截取

日期类型

转换函数

to_number('1550') 转化为数字

to_number(字段,'xx') 按16进制处理字段值

隐式数据类型转换，默认字符转为数字，可能导致索引用不了

to_char(字段,'$99,999,99') 按格式转化成字符串

格式：9代表数字位 0代表定义宽度大于实际值宽度，有0补齐

L 表示本地货币

如果显示位数不足（定义宽度小于实际值宽度），用#代替

更改本地语言

NLS_LANG='SIMPLIFIED CHINESE_CHINA.ZHS16GBK' export NLS_LANG

NLS_LANG=AMERICAN_AMERICA.US7ASCII

将没有领导的员工的领导位置设为boss

select first_name,nvl(to_char(manger_id),'Boss') from s_emp;

多行函数

多表查询

列出员工的名字，和部门的名称

cross join 笛卡尔积，两个表的每条记录都匹配

select first_name,dept_id,name from s_emp cross join s_dept;300条

select e.first_name,e.dept_id,d.id,d.name from s_emp e cross join s_dept d;

300条

如果两个表里有字段名相同，必须指出是哪个表的字段

inner join 内连接(将满足条件的记录匹配,精确匹配)可省略的写成join

select e.first_name,e.dept_id,d.id,d.name from s_emp e inner join s_dept d on e.dept_id=d.id; 25条

列出部门号，地区号，地区名称

select d.id,d.name,r.id,r.name from s_dept d join s_region r on d.region_id=r.id;

内连接

等值连接：两张表有描述共同属性的列

非等值连接：可以用between and把两张表中的列写成表达式

自连接：同一张表的列之间有关系实际反映的是同一张表的行之间有关系，通过给表起别名将同一张表的列之间的关系转换成不同表的列之间的关系

所谓表之间的关系，实际指表中的行之间的关系，该关系通过将表中的列写成表达式来体现

Carmen在哪个部门上班

select e.first_name,d.name from s_emp e join s_dept d on e.dept_id=d.id and e.first_name='Carmen';先做过滤在做连接

亚洲地区有哪些部门

select d.id,d.name,r.name from s_dept d join s_region r on d.region_id=r.id and r.name='Asia';

Carmen在哪个地区上班

select e.first_name,r.name from s_emp e join s_dept d on e.dept_id=d.id and e.first_name='Carmen' join s_region r on d.region_id=r.id;

亚洲地区有哪些员工

select e.first_name,r.name from s_emp e join s_dept d on e.dept_id=d.id join s_region r on d.region_id=r.id and r.name='Asia';

列出员工的工资，名字，及工资的级别

select e.ename,e.sal,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;

3级和5级有哪些员工

select e.ename,e.sal,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal and s.grade in (3,5);

列出每个员工的领导

select e.first_name,m.first_name from emp e join emp m on e.manager_id=m.id;

哪些人是领导

select distinct m.first_name from emp e join emp m on e.manager_id=m.id;

外连接 left outer join right outer join outer可以省略

from t1 left join t2 on t1.c1=t2.c2 表示t1做驱动表 from t1 right join t2 on t1.c1=t2.c2 表示t2做驱动表外连接把驱动表不能匹配的记录也会放在结果集里，没有匹配上的字段设置为null，驱动表里的数据一个都不能少

外连接的结果集=内连接的结果集+t1表中匹配不上的记录和一条null记录的组合

关键：谁做驱动表

列出每个员工的领导，并且打印出boss

select e.first_name,nvl(m.first_name,'Boss') from emp e left join emp m on e.manager_id=m.id;

哪个部门没有员工

select e.ename,d.deptno from emp e right join dept d on e.deptno=d.deptno where e.empno is null;这里用where不用and，因为and是先过滤在连接

哪些人是员工(哪些人不是领导)

select e.first_name,m.first_name from s_emp e right join s_emp m on e.manager_id=m.id where e.id is null;

哪个部门没有叫SMITH的员工

select d.deptno,d.dname from emp e right join dept d on e.deptno=d.deptno and e.ename='SMITH' where e.empno is null;

哪个部门没有叫SMITH的部门(Oracle)

select d.dname from emp e,dept d where e,deptno(+)=d.deptno and e.ename(+)='SMITH' and e.ename is null;

第一个+表示dept表为驱动表，第二个+表示在连接之前过滤

组函数

操作在一组记录上，每组返回一个结果

处理的是所有的非空值（count(*)表示记录数）如果所有记录都是null，count返回0，其他返回空

avg 平均值(数值)

sum 求和（数值）

count 计数（字符，数字，日期）

max 最大值（字符，数字，日期）

min 最小值（字符，数字，日期）

工资的平均值，提成的平均值，和提成的平均值

select avg(salary),avg(commission_pct),max(commission_pct) from s_emp;

所有人提成的平均值

select sum(commission_pct)/count(*) from s_emp; select avg(nvl(commission_pct,0)) from s_emp;

group by 给记录分组

根据group by子句指定的表达式，将要处理的数据（若有where子句即为通过条件过滤后的数据）分成若干组，每组有唯一标识，组内有若干条记录，根据select语句后的组函数处理每个组的记录，每个组返回一直值

各个部门的平均工资

select avg(salary),dept_id from s_emp group by dept_id;

各个职位的平均工资

select title avg(salary) from s_emp group by title;

每个提成有多少人

select commission_pct,count(*) from s_emp group by commission_pct; 分组时所有的空值都在同一个组

各个部门有多少种不同的职位

select dept_id,count(distinct title) from s_emp group by dept_id;

各个部门不同职位的平均工资

select dept_id,title,avg(salary) from s_emp group by dept_id,title;

42部门的平均工资，列出部门号

select max(dept_id),round(avg(salary)) from s_emp where dept_id=42; select dept_id,round(avg(salary)) from s_emp where dept_id=42 group by dept_id;

若没有group by子句，select后面有一个是组函数，其他都必须是组函数有group by子句，select后面可跟group by后面跟的表达式以及组函数，其他会报错

每个工资级别有多少人

select s.grade,count(e.empno) from emp e right join salgrade s on e.sal between s.losal and s.hisal group by s.grade order by s.grade;

各个部门的平均工资（列出部门名称：部门名称+地区名称）

select d.name,r.name,avg(e.salary) from s_emp e join s_dept d on

e.dept_id=d.id join s_region r on d.region_id=r.id group by d.name,r,name;

select max(d.name),max(r.name),avg(e.salary) from s_emp e join s_dept d on e.dept_id=d.id join s_region r on d.region_id=r.id group by d.id;

having 对分组后的结果进行过滤(后面跟组函数)

过滤组，而where是过滤记录

哪些部门的平均工资比2000高

select dept_id,avg(salary) from s_emp group by dept_id having avg(salary)>2000;

32部门和42部门的平均工资

select dept_id,avg(salary) from s_emp where dept_id in (32,42) group by dept_id;优化

select dept_id,avg(salary) from s_emp group by dept_id having dept_id in(32,42);

where和having的区别

where子句过滤的是行（记录）

having子句过滤的是分组（组标识，每组数据的聚合结果） where子句包含单行函数

having子句只能包含group by 后面的表达式和组函数 where子句执行在前，having子句执行在后 where子句和having子句都不允许用列别名

子查询

就是在一条SQL语句中嵌入select语句

先执行子查询，子查询的返回结果作为主查询的条件，在执行主查询子查询只执行一遍

若子查询的返回结果为多个值，Oracle会去掉重复值后，再将结果返回给主查询

非关联子查询：主查询和子查询之间没有表连接

单列子查询

谁的工资最低

select first_name,title from s_emp where salary=(select min(salary) from s_emp);

谁和SMITH的职位一样

select last_name,title from s_emp where title=(select title from s_emp where last_name='Smith') and last_name<>'Smith';

哪些人是领导

select first_name from s_emp where id in (select manager_id from s_emp);

哪些人是员工

select first_name from s_emp where id not in (select manager_id from s_emp where manager_id is not null);

not in 比外连接的效率低

哪个部门的平均工资比32部门的平均工资高

select dept_id,avg(salary) from s_emp group by dept_id having avg(salary)>(select avg(salary) from s_emp where dept_id = 32);

多列子查询

哪个员工的工资等于本部门的平均工资

select first_name,dept_id,salary from s_emp where (dept_id,salary) in (select dept_id ,avg(salary) from s_emp group by dept_id);

关联子查询

先做主查询，再执行子查询执行顺序

1.外部查询得到一条记录（查询先从outer表中读取数据）并将其传入到内部查询

2.内部查询基于传入的值执行

3.内部查询从其结果中把值传回外部查询，外部查询使用这些值来完成其处理，若符合条件，outer表中得到的那条记录放入结果集中，否则放弃，该记录不符合条件

4.重复执行步骤1-3，直到把outer表中的所有记录判断一边

哪个员工的工资比本部门的平均工资高

select first_name,dept_id,salary from s_emp o where salary>(select avg(salary) from s_emp i where o.dept_id=i.dept_id);

exists运算符（存在）

主查询与子查询的条件是否匹配（一旦匹配就返回，不会继续找下去） exists采用的是循环方式，判断outer表中是否存在记录只要在inner表中找到一条匹配的记录即可

1.外部查询得到一条记录（查询先从outer表中的去数据）并将其传入到内部查询表

2.对inner表中的记录依次扫描，若根据条件存在一条记录与outer表中的记录匹配，立即停止扫描，返回true，将outer表中的记录放入结果集中，若扫描了全部的记录，没有任何一条记录符合匹配条件，返回false，outer表中的该记录被过滤掉

3.重复执行步骤1-2，直到把outer表中的所有记录判断一边

哪些人是领导

select first_name,dept_id from s_emp outer where exists (select 'x' from s_emp where manager_id=outer.id);

哪个部门有员工

select dname from dept d where exists (select 1 from emp e where e.deptno=d.deptno);

not exists 不存在

哪些人是员工

select first_name,dept_id from s_emp outer where not exists (select 'x' from s_emp where manager_id=outer.id);

in和exists的比较

exists是用循环的方式，由outer表的记录数决定循环的次数，对于exists的影响最大，所以，外表的记录数要少

in先执行子查询，子查询的返回结果去重之后，在执行主查询，所以，子查询的返回结果越少，越适合用该方式

标量子查询

列出员工的名字和领导名字的对应关系

select first_name,nvl((select first_name from s_emp i where o.manager_id=i.id),'Boss') from s_emp o;

查出每个工资级别的人数

select grade,(select count(empno) from emp e where e.sal between s.losal and s.hisal) from salgrade;

基于数据库的开发

将业务需求转换成可操作的数据库 E-R图

E 实体，有共同属性的一类对象的集合

属性，描述实体，区分实体 R 关系，描述实体和实体的关系

虚线表示可选的，可以

EMPLOYEE CUSTOMER

#* id #*id

* last name * name

O first name o phone

表示实体实线表示强制的，必须

* 表示强制属性 o 表示可选属性 # 表示唯一属性

实体和实体的关系从实例之间的数量关系的角度分为一对一，一对多，多对多实体和实体之间的关系从紧密程度上分为必须和可以递归关系：同一实体里的实例之间有关系

完整性约束

保证数据的一致性

通过数据库的特性或应用程序完成数据库约束

主键（primary key），唯一键（unique key），外键（foreign key）

实体完整性：主键值唯一且非空（PK）

引用完整性：外键值必须是已存在的主键值或为空（FK）

主键

主键值要求唯一且非空

联合主键：多列联合唯一，任意一列都可以重复，每一列都不能为null 表中只能有一个主键外键

外键是定义在子表（child table）上一列，它的取值要引用父表（parent table）

上的主键列或唯一列

外键的定义是基于数据值的，是纯逻辑概念

外键的取值必须匹配主键值或唯一键值还可以是空值若外键是主键的一部分，它的取值不能为空

1.先create parent table（pk，uk），再create child table（fk） 2.先insert into parent table，再insert into child table 3.先delete from child table，再delete from parent table 4.先drop child table，再drop parent table

E-R图向表转换

表和列分别对应实体和属性 1.将实体映射成表 2.将属性映射成列

强制属性定义成非空列名不能用保留字 3.将唯一标识映射成主键

一个表只能有一个主键 4.将关系映射成外键

一对多：一的那边定义成主键，或唯一键，多的那边定义成外键一对一：合表

在外键列上增加唯一约束主键即外键

多对多：通过增加中间表将一个多对多关系转换成两个一对多关系

三个范式

最小化数据冗余减少完整性问题

标识丢失的实体，关系，表

第一范式：表中不会有重复的记录，即有主属性（pk）；每个属性值不可再

分

第二范式：每个非主属性必须完全依赖于主属性

第三范式：每个非主属性不能依赖于另一个非主属性

DDL建表语句

命名规则

首字母必须是字符长度是1-30

只能包含A-Z,a-z,0-9,_,$,#

同一个区域不能定义相同的对象不能定义Oracle的保留字

create table tablename(

字段数据类型默认值约束， „„ );

数据类型

字符 varchar2 后面必须跟宽度，按照实际长度存，最大4000个字节，列

取值长度不固定用varchar2，比较时按实际字符长度比，对空格是敏感的

char 默认一个字符，按照定义长度存，最大2000个字节，取值长度固定用char，比较时，会将短字符串补齐后，在与长字符串比较，对空格不敏感

数字 number(p,s) 可以不定义长度，缺省38位,p表示数值中所有数字位的个数，最大38位，s表示刻度范围，s为正数，表示小数点右边的数字的个数，为负数，表示小数点开始向左进行计算数字位的个数。刻度范围从-84~127

日期类型 date Oracle用7个字节来存储日期和时间，默认格式是DD-MON- RR，格式敏感。

sysdate 返回当前系统时间

alter session set nls_date_format='yyyy mm dd hh24:mi:ss';修改会话日期格式

to_date('01-JAN-09','dd-MON-rr') 将字符串按照日期格式转化为日期值,不写格式为默认格式

to_date('2009 01 01 10:10:10','yyyy mm dd hh24:mi:ss')

to_char(date,'dd-MON-rr')将日期类型转化为字符类型

格式：fm能去掉两端的空格以及去掉前导零

查出三月份入职的员工

select first_name,start_date from s_emp where to_char(start_date,'mm') ='03';

select first_name,start_date from s_emp where to_char(start_date,'fmmonth')

='march';

select first_name,start_date from s_emp where rtrim(to_char (start_date,'month'))='march';

rtrim 压缩右边的空格

日期可以进行运算，+1，-1，加减一天

十分钟之后的时间是多少

select sysdate,sysdate+1/144 from dual;

列出员工的工作时间

select sysdate-start_date from s_emp;

日期函数

months_between(date,date) 两个日期的隔了几个月 add_months(date,6) 给日期加几个月

next_day(date,'FRIDAY')从date开始第一个星期五 last_day(date)给定日期的最后一天

round(to_date(''),'MONTH') 以月为单位进行四舍五入 trunc(to_date(''),'MONTH') 以月为单位截取

列出下个月0点0分0秒

select add_months(trunc(sysdate,'MONTH'),1) from dual; select round(last_day(sysdate),'MONTH') from dual; select trunc(last_day(sysdate)+1) from dual;

插入语句

insert into tablename values (字段值，„„);给表里所有的字段插入数据

insert into tablename (字段名，„„) values (字段值，null，„„);给表里指定字段插入数据

删除记录

delete from tablename where „„ 没有where子句删除所有记录用delete删除记录不会释放空间不用delete删除一张大表

truncate table tablename (DDL)清空大表，不能rollback

更新记录

update tablename set column=value,.... where .... 没有where子句更新所有的记录

约束

主键约束,唯一且非空

create table test(

c1 number primary key );

列级约束

create table test(

c1 number constraints test_c1_pk primary key );

constraints test_c1_pk 给约束其名字

表级约束（主要适用于联合主键） create table test(

c1 number，

constraints test_c1_pk primary key(c1) );

create table test(

c1 number， c2 number，

constraints test_c1_pk primary key(c1,c2) );

外键约束,引用的值必须唯一

create table parent(

c1 number primary key

);

列级约束

create table child(

c1 number primary key,

c2 number constraints child_c2_ fk references parent(c1)); references 表示引用父表中的某列

drop table parent cascade constraints; 先解除子表的外键约束，再删除父表

create table child(

c1 number primary key,

c2 number references parent(c1) on delete cascade );

on delete cascade 级联删除，删父表级联删除子表

create table child(

c1 number primary key,

c2 number references parent(c1) on delete set null );

on delete set null 先将子表的外键置为null，然后删除父表

表级约束

create table child(

c1 number primary key, c2 number，

foreign key(c2) references parent(c1) );

非空约束

create table test(

c1 number primary key, c2 number not null );

唯一约束，

允许为null，允许多个null

列级约束

create table child(

c1 number primary key, c2 number unique

);

表级约束，联合唯一 create table child(

c1 number primary key, c2 number， c3 number, unique(c2,c3) );

检查约束

列级约束

create table test(

c1 number check (c1>100) );

表级约束

create table test(

c1 number， check (c1>100) );

建表脚本

1.创建一个脚本文件（建议后缀名是.sql），内容是sql语句

2.保存脚本文件，执行脚本文件 sqlplus openlab/open123 @test.sql

已经连接上数据库后执行脚本文件@test.sql，Oracle只会在当前目录下找脚本文件，如果不在脚本所在的文件，可写绝对路径

带子查询的create table

create table emp_24 as select * from s_emp where dept_id=24; 只有非空约束不需要定义可以直接复制过来

create table emp_43 (id primary key,dept_id,salary) as select id,dept_id,salary from s_emp where dept_id=43;

数据类型和宽度都是从表中复制来的，不用定义

create table emp_new (id primary key,first_name,ann_sal) as select id,first_name,salary*12 from s_emp;

若有表达式，要么定义别名，要么在新表中定义字段名

create table emp_new (id primary key,commission_pct) as select

id,commission_pct from s_emp where commission_pct is not null;

create table emp_new as select * from s_emp where 1>2; 只复制表结构，没有复制记录

带子查询的insert语句

insert into tablename(字段名,....) select ........

带子查询的update语句

update s_emp_new n set ann_sal=(select ann_sal*(1+commission_pct/100) from s_emp_comm c where n.id=c.id) where id in (select id from s_emp_comm);

定义缺省值default

create table test(

c1 number default 10, c2 number );

事务

事务的结束：commit/rollback DDL语句自动提交

事务的开始：上一个事务的结束就是下一个事务的开始

事务的特性：原子性：一个事务要么完全发生，要么完全不发生

一致性：事务把数据库从一个一致状态转变到另一个状态隔离性：在事务提交之前，其他事物觉察不到事务的影响持久性：一旦事务提交，他是永久的

事务的隔离级别：（read committed）一个事务只可以读取在事务开始之前提交的数据和本事务正在修改的数据

事务的提交会释放对象锁，释放回滚段的空间锁

排它锁（X）：如果一个对象上加了X锁，在这个锁被采用commit和 rollback释放之前，该对象上不能施加任何其他类型的锁

控制事务

savepoint 设置保留点，可以设置事务的部分回滚

savepoint XXX rollback to XXX

修改表结构

增加约束

alter table emp_42 add foreign key(dept_id) references s_dept(id);

修改表名字

rename xx to xxx

索引使用的是b*tree结构（Oracle server）

索引上叶子节点实际上是双向连接的表，一旦找到叶子节点的开始点，就可以对叶子节点的开始值进行顺序扫描，

那样不必再进行结构导航，只用对叶子节点进行转发就可以了。

使用索引是要占用的空间的（空间代价）维护代价（dml操作变慢）

update数据时，相当于先delete，后insert

why use an index?

>Oracle Server 通过rowid能快速定位要找的行 >通过rowid定位能有效降低读取数据快的数量

>索引的使用和维护是自动的，一般情况不需要用户的干预。

操作索引的语句：(当使用索引一段时间后，感觉系统运行效率降低时) >alter index：快，用空间换时间 SQL:alter index s_emp_last_name_idx rebuild; >drop index和create index ：慢，占用的空间少，用时间换空间 create index（会进行排序的动作，使用的时间较长）

索引：物理上与表是的，逻辑上依赖于表

＊＊＊＊＊数据量大，结果集小适合建索引(会进行排序)

适合建索引的列：

>经常出现where子句的列 >经常用于表连接的列

>该列是高基数数据列（高基数数据列是指有很多不同的值，重复度很低） >该列包含很多的null值索引里面不建空值

>表很大，查询的结果集小

>PK、UK列系统会自动创建唯一性索引 >外键列（FK）

>经常需要排序（order by）和分组（group by）的列 >索引不是万能的

不适合建索引的列： >小表

>列很少出现where子句既要创建额外的空间，再操作数据时，又要进行维护。不划算

>查询的结果集也大 >该列被反复更新

唯一性索引： create unique index test_c3_idx on test(c3); 相当于唯一性约束非唯一性索引：用于提高查询效率单列索引：建在一列上

联合索引：建在多列上：经常c4和c5联合起来进行查询 alter table test add (c4 number,c5 number); create index test_c4_c5_inx on test(c4,c5);

那些写法会导致索引用不了

>函数 where upper(first_name)='CARMEN'; 索引里面记录的是索引本身的值，而非转换后的值 >表达式

>部分隐式数据类型where c1=2;(c1是varchar2类型) 相当于 where to_number(c1)=2;

>like 和 substr 尽量用like,不用substr

>否定形式尽量不使用否定形式 >is null 索引里面不记录空值

函数索引：

create index test_c4_funind on test(round(c1)); select * from test where round(c1)=1;

FROM后面跟子查询（NILINE VIEWS）

那个员工的工资等于本部门的平均工资?

SQL> select first_name,dept_id,salary 2 from s_emp

3 where (dept_id,salary) in (select dept_id,avg(salary) 4 from s_emp

5 group by dept_id);

FIRST_NAME DEPT_ID SALARY ------------------------- ---------- ----------

Colin 31 1400 Midori 31 1400 Yasmin 33 1515 Andre 35 1450

1 select first_name,dept_id,salary 2 from s_emp e

3 where salary=(select avg(salary) 4 from s_emp d

5* where e.dept_id=d.dept_id) SQL> /

FIRST_NAME DEPT_ID SALARY ------------------------- ---------- ----------

Midori 31 1400 Colin 31 1400 Yasmin 33 1515 Andre 35 1450

1 select e.first_name,e.dept_id,e.salary

2 from s_emp e join (select dept_id,avg(salary) avgsal 3 from s_emp

4 group by dept_id) a 5 on e.dept_id=a.dept_id 6* and e.salary=a.avgsal SQL> /

FIRST_NAME DEPT_ID SALARY ------------------------- ---------- ----------

Midori 31 1400 Colin 31 1400 Yasmin 33 1515 Andre 35 1450

****＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊ ROWNUM 系统里面临时生成的记录号。

SQL> select rownum,id,first_name 2 from s_emp;

ROWNUM ID FIRST_NAME ---------- ---------- ------------------------- 1 1 Carmen 2 2 LaDoris 3 3 Midori 4 4 Mark 5 5 Audry 6 6 Molly 7 7 Roberta 8 8 Ben

9 9 Antoinette 10 10 Marta 11 11 Colin

ROWNUM ID FIRST_NAME ---------- ---------- ------------------------- 12 12 Henry 13 13 Yasmin 14 14 Mai 15 15 Andre 16 16 Elena 17 17 George 18 18 Akira 19 19 Vikram 20 20 Chad

21 21 Alexander 22 22 Eddie

ROWNUM ID FIRST_NAME ---------- ---------- ------------------------- 23 23 Radha 24 24 Bela 25 25 Sylvie

25 rows selected.

第一条记录

1 select first_name,dept_id,salary 2 from s_emp

3* where rownum=1 SQL> /

FIRST_NAME DEPT_ID SALARY ------------------------- ---------- ----------

Carmen 50 3500 第二条记录

1 select first_name,dept_id,salary 2 from s_emp

3* where rownum=2 SQL> /

no rows selected

2—5条记录

1 select first_name,dept_id,salary 2 from s_emp

3* where rownum between 2 and 5 SQL> /

no rows selected

前两条记录

1 select first_name,dept_id,salary 2 from s_emp

3* where rownum<=2 SQL> /

FIRST_NAME DEPT_ID ------------------------- ---------- ----------

Carmen 50 LaDoris 41

从一开始的记录可以查询

＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊8 分页问题：

工资最高的前10人：

SQL> select rownum,first_name,dept_id,salary 2 from s_emp

3 order by salary desc 4 where rownum<=10;

SALARY 3500 1450 where rownum<=10 *

ERROR at line 4:

ORA-00933: SQL command not properly ended

order by 和where的顺序错误了

1 select rownum,first_name,salary

2 from (select first_name,salary from s_emp 3 order by salary desc) 4* where rownum<11 SQL> /

ROWNUM FIRST_NAME SALARY ---------- ------------------------- ----------

1 Carmen 3500 2 Audry 1550 3 Mai 1525 4 Yasmin 1515 5 Henry 1490 6 LaDoris 1450 7 Andre 1450 8 Mark 1450 9 Midori 1400 10 Elena 1400

10 rows selected.

＠＠＠＠

rownum是一个伪列

选择11条到20条记录（将rownum固定为真正的列）

1 select first_name,salary

2 from (select rownum r,first_name,salary from s_emp 3 where rownum<=20) 4* where r>10 SQL> /

FIRST_NAME SALARY ------------------------- ----------

Colin 1400 Henry 1490

Yasmin 1515 Mai 1525 Andre 1450 Elena 1400 George 940 Akira 1200 Vikram 795 Chad 750

10 rows selected.

将工资11到20的人显示：

2 from (

3 select rownum rn,first_name,salary 4 from (select first_name,salary from s_emp 5 order by salary desc) 6 where rownum<=20

7 ) 8 where rn>10;

FIRST_NAME SALARY ------------------------- ----------

Elena 1400 Marta 1307 Antoinette 1300 Roberta 1250 Molly 1200 Akira 1200 Ben 1100 Sylvie 1100 George 940 Bela 860

10 rows selected.

＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊ CASE WHEN AND DECOODE

》在select语句中可以实现if_then_else的逻辑＠＠＠＠＠

部门31的员工工资涨10％部门32的员工工资涨20％部门33的员工工资涨30％其他部门工资不动

1 select dept_id,first_name,salary,

2 case when dept_id=31 then salary*1.1 3 when dept_id=32 then salary*1.2 4 when dept_id=33 then salary*1.3 5 else

6 salary 7 end aft_sal 8* from s_emp SQL> /