Oracle 实训资料

Oracle SQLdatebase (DB) Structured query language 结构化查询语言 DDL(数据定义语言) Date definition language create table 建表 alter table 修改表结构 drop table 删表 column data type width constraints(约束) DML(数据操作语言) Data manipulati

Oracle

SQLdatebase (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

 

Installsoftware rdbms 安装软件

createdatabase 创建数据库

 

登录数据库 sqlplus username/password

 

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

体的数据库

 

show user 查看当前用户

 

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

 

查询员工的姓名，和工资

selectfirst_name,salary from s_emp;

 

查询员工的名字和职位

selectfirst_name,title from s_emp;

 

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

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

 

列出每个员工的年薪

selectfirst_name,salary*12 from s_emp;

 

列出每个员工的总收入

selectfirst_name,salary*12*(1+commission_pct/100) "tol sal" from s_emp;

空值会导致算术表达式为空，Oracle认为null为无穷大

selectfirst_name,salary*12*(1+nvl(commission_pct,0)/100) tol_sal,

Commission_pct

froms_emp

 

nvl(p1,p2)

if(p1 is null) then return p2;

else return p1;

 

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

 

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

 

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

selectfirst_name||last_name employee from s_emp;

selectfirst_name||' '||last_name employee from s_emp;

selectfirst_name||' is in department '||dept_id||'.' froms_emp;

 

列出有哪些部门

selectdistinct name from s_dept;

 

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

 

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

selectdistinct title from s_emp;

 

set feed on 显示查询返回的记录数

 

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

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

 

列出工资大于1000的员工

selectfirst_name,salary from s_emp where salary>1000;

 

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

selectfirst_name,salary*12 from s_emp where salary>1000;

 

where子句不能跟列的别名

 

列出Carmen的年薪

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

 

哪些人的职位是Stock Clerk

selectfirst_name,title from s_emp where title='Stock Clerk';

 

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

selectfirst_name,salary from s_emp where salary>=1550 and salary

selectfirst_name,salary from s_emp where salary between1550 and 2000;

 

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

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

selectfirst_name,salary,dept_id from s_emp where dept_id in(31,41,43);

selectfirst_name,salary,dept_id from s_emp where dept_id =any(31,41,43);

 

%表示0或多个字符

 

_表示任意一个字符

 

select last_name from s_emp wherelast_name like 'M%';

 

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

 

查询当前用户下有哪些表

selecttable_name from user_tables;

 

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

selecttable_name from user_tables where table_name like 'S\_%' escape '\';

escape '\' 表示定义\为转义字符

 

哪些员工没有提成

selectfirst_name,commission_pct from s_emp where commission_pct is null;

 

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

 

哪些员工有提成

selectfirst_name,commission_pct from s_emp where commission_pct is not null;

 

 

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

selectfirst_name,dept_id,salary from s_emp where dept_id not in(31,41,43);

selectfirst_name,dept_id,salary from s_emp where dept_id 31 and

dept_id 41 and dept_id 43;

selectfirst_name,dept_id,salary from s_emp where dept_id all (31,41,43);

 

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

 

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

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

 

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

selectfirst_name,salary from s_emp order by salarydesc;

order by 后的字段要想使用索引，必须保证不能为null

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

 

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

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

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

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

 

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

selectfirst_name,dept_id,salary from s_emp order by dept_id,salary desc;

 

函数

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

字符类型

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

select lower('SQL') fromdual;

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 froms_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='SIMPLIFIEDCHINESE_CHINA.ZHS16GBK'

export NLS_LANG

 

NLS_LANG=AMERICAN_AMERICA.US7ASCII

 

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

select first_name,nvl(to_char(manger_id),'Boss') froms_emp;

 

多行函数

 

 

 

 

 

 

多表查询

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

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

selectfirst_name,dept_id,name from s_emp cross join s_dept;300条

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

300条

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

 

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

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

 

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

selectd.id,d.name,r.id,r.name from s_dept d join s_region r ond.region_id=r.id;

 

内连接

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

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

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

 

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

 

Carmen在哪个部门上班

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

 

亚洲地区有哪些部门

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

 

Carmen在哪个地区上班

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

 

亚洲地区有哪些员工

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

 

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

selecte.ename,e.sal,s.grade from emp e join salgrade s one.sal between s.losal ands.hisal;

 

3级和5级有哪些员工

select e.ename,e.sal,s.grade from emp e joinsalgrade s on e.sal betweens.losal and s.hisal and s.gradein (3,5);

 

列出每个员工的领导

selecte.first_name,m.first_name from emp e join emp m one.manager_id=m.id;

 

哪些人是领导

selectdistinct m.first_name from emp e join emp m one.manager_id=m.id;

 

 

外连接 leftouter 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

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

 

哪个部门没有员工

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

 

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

select e.first_name,m.first_namefrom s_emp e right join s_empm on e.manager_id=m.id wheree.id is null;

 

哪个部门没有叫SMITH的员工

select d.deptno,d.dnamefrom emp e right join deptd on e.deptno=d.deptno ande.ename='SMITH' where e.empno is null;

 

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

selectd.dname from emp e,dept d where e,deptno(+)=d.deptnoand 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) froms_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 froms_emp group by dept_id;

 

各个职位的平均工资

selecttitle avg(salary) froms_emp group by title;

 

每个提成有多少人

selectcommission_pct,count(*) froms_emp group by commission_pct;

分组时所有的空值都在同一个组

 

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

selectdept_id,count(distincttitle) from s_emp groupby dept_id;

 

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

selectdept_id,title,avg(salary) from s_emp group bydept_id,title;

 

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

select max(dept_id),round(avg(salary)) from s_empwhere dept_id=42;

selectdept_id,round(avg(salary))from s_emp wheredept_id=42 group by dept_id;

 

若没有groupby子句，select后面有一个是组函数，其他都必须是组函数

有group by子句，select后面可跟group by后面跟的表达式以及组函数，其他会报错

 

每个工资级别有多少人

selects.grade,count(e.empno) fromemp e right joinsalgrade s on e.sal betweens.losal and s.hisal groupby s.grade order by s.grade;

 

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

selectd.name,r.name,avg(e.salary) from s_emp e joins_dept d on e.dept_id=d.id join s_region r ond.region_id=r.id group by d.name,r,name;

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

 

 

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

   过滤组，而where是过滤记录

 

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

selectdept_id,avg(salary) from s_emp group by dept_id havingavg(salary)>2000;

 

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

selectdept_id,avg(salary) from s_emp where dept_id in (32,42) groupby dept_id;优化

selectdept_id,avg(salary) from s_emp group by dept_id havingdept_id in(32,42);

 

where和having的区别

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

having子句过滤的是分组（组标识，每组数据的聚合结果）

where子句包含单行函数

having子句只能包含group by 后面的表达式和组函数

where子句执行在前，having子句执行在后

where子句和having子句都不允许用列别名

 

 

 

 

子查询

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

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

子查询只执行一遍

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

查询

 

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

 

单列子查询

 

谁的工资最低

selectfirst_name,title from s_emp where salary=(selectmin(salary) from s_emp);

 

谁和SMITH的职位一样

select last_name,titlefrom s_emp wheretitle=(select title from s_emp where last_name='Smith')and last_name'Smith';

 

哪些人是领导

selectfirst_name from s_emp whereid in (select manager_id from s_emp);

 

哪些人是员工

selectfirst_name from s_emp whereid not in (select manager_id from s_emp wheremanager_id is not null);

 

not in 比外连接的效率低

 

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

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

 

多列子查询

 

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

select first_name,dept_id,salaryfrom 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表中的所有记录判断一边

 

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

selectfirst_name,dept_id,salary from s_emp o where salary>(selectavg(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表中的所有记录判断一边

 

哪些人是领导

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

 

哪个部门有员工

selectdname from dept d whereexists (select 1 from emp e where e.deptno=d.deptno);

 

not exists 不存在

 

 

哪些人是员工

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

 

in和exists的比较

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

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

 

标量子查询

 

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

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

 

查出每个工资级别的人数

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

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

基于数据库的开发

将业务需求转换成可操作的数据库

 

E-R图

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

属性，描述实体，区分实体

 R 关系，描述实体和实体的关系

 

 

                            虚线表示可选的，可以

 

 

 

 

 

 

        

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

 

* 表示强制属性

o 表示可选属性

# 表示唯一属性

 

实体和实体的关系从实例之间的数量关系的角度分为一对一，一对多，多对多

实体和实体之间的关系从紧密程度上分为必须和可以

递归关系：同一实体里的实例之间有关系

 

 

完整性约束

保证数据的一致性

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

数据库约束

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

 

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

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

 

主键

主键值要求唯一且非空

联合主键：多列联合唯一，任意一列都可以重复，每一列都不能为null

表中只能有一个主键

 

外键

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

上的主键列或唯一列

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

外键的取值必须匹配主键值或唯一键值还可以是空值

若外键是主键的一部分，它的取值不能为空

 

1.先create parent table（pk，uk），再create child table（fk）

2.先insert into parent table，再insert into childtable

3.先delete from child table，再delete fromparent 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='yyyymm dd hh24:mi:ss';修改会话日期格式

 

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

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

 

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

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

 

 

 

查出三月份入职的员工

selectfirst_name,start_date from s_emp where to_char(start_date,'mm')

='03';

 

selectfirst_name,start_date from s_emp where to_char(start_date,'fmmonth')

='march';

 

selectfirst_name,start_date from s_emp where rtrim(to_char

(start_date,'month'))='march';

 

rtrim 压缩右边的空格

 

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

 

十分钟之后的时间是多少

selectsysdate,sysdate+1/144 from dual;

 

列出员工的工作时间

select sysdate-start_datefrom 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秒

selectadd_months(trunc(sysdate,'MONTH'),1) from dual;

selectround(last_day(sysdate),'MONTH') from dual;

select trunc(last_day(sysdate)+1)from dual;

 

 

插入语句

 

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

 

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

 

删除记录

delete fromtablename where ……

没有where子句删除所有记录

用delete删除记录不会释放空间

不用delete删除一张大表

 

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

 

更新记录

updatetablename set column=value,.... where ....

没有where子句更新所有的记录

 

约束

 

主键约束,唯一且非空

 

create table test(

c1 numberprimary key

);

 

列级约束

create table test(

c1 numberconstraints test_c1_pk primary key

);

constraints test_c1_pk 给约束其名字

 

表级约束（主要适用于联合主键）

create table test(

c1 number，

constraintstest_c1_pk primary key(c1)

);

 

create table test(

c1 number，

c2 number，

constraintstest_c1_pk primary key(c1,c2)

);

 

 

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

 

createtable parent(

c1 numberprimary key

);

 

列级约束

create table child(

c1 numberprimary key,

c2 numberconstraints child_c2_ fk references parent(c1));

references 表示引用父表中的某列

 

droptable parent cascadeconstraints;

先解除子表的外键约束，再删除父表

 

create table child(

c1 numberprimary key,

c2 numberreferences parent(c1) ondelete cascade

);

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

 

create table child(

c1 numberprimary key,

c2 numberreferences parent(c1) ondelete set null

);

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

 

表级约束

create table child(

c1 numberprimary key,

c2 number，

foreign key(c2)references parent(c1)

);

 

非空约束

 

create table test(

c1 numberprimary key,

c2 numbernot null

);

 

唯一约束，

允许为null，允许多个null

 

列级约束

create table child(

c1 numberprimary key,

c2 numberunique

);

 

表级约束，联合唯一

create table child(

c1 numberprimary key,

c2 number，

c3 number,

unique(c2,c3)

);

 

检查约束

 

列级约束

create tabletest(

c1 numbercheck (c1>100)

);

 

表级约束

createtable test(

c1 number，

check (c1>100)

);

 

 

建表脚本

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

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

  已经连接上数据库后执行脚本文件@test.sql，Oracle只会在当前目录

  下找脚本文件，如果不在脚本所在的文件，可写绝对路径

 

带子查询的createtable

 

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 wheredept_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 wherecommission_pct is not null;

 

create table emp_new as select *from s_emp where 1>2;

只复制表结构，没有复制记录

 

 

 

带子查询的insert语句

insert intotablename(字段名,....) select ........

 

带子查询的update语句

updates_emp_new n set ann_sal=(selectann_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 tableemp_42 add foreign key(dept_id) referencess_dept(id);

 

修改表名字

rename xxto xxx

 

 

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

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

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

 

 

使用索引是要占用独立的空间的（空间代价）

维护代价（dml操作变慢）

 

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

 

whyuse an index?

>OracleServer 通过rowid能快速定位要找的行

>通过rowid定位能有效降低读取数据快的数量

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

 

 

操作索引的语句：(当使用索引一段时间后，感觉系统运行效率降低时)

>alterindex：快，用空间换时间  SQL:alterindex s_emp_last_name_idx rebuild;

>dropindex和createindex ：慢，占用的空间少，用时间换空间

createindex（会进行排序的动作，使用的时间较长）

 

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

 

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

 

适合建索引的列：

>经常出现where子句的列

>经常用于表连接的列

>该列是高基数数据列（高基数数据列是指有很多不同的值，重复度很低）

>该列包含很多的null值                   索引里面不建空值

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

>PK、UK列 系统会自动创建唯一性索引

>外键列（FK）

>经常需要排序（order by）和分组（group by）的列

>索引不是万能的

 

不适合建索引的列：

>小表

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

>查询的结果集也大

>该列被反复更新

 

 

唯一性索引： create unique index test_c3_idx  on test(c3); 相当于唯一性约束

非唯一性索引：用于提高查询效率