当前位置：Gxlcms > 数据库问题 > oracle--

oracle--

时间：2021-07-01 10:21:17 帮助过：3人阅读

结构化查询语言：Structured Query Language

数据的持久化（保存数据）

DDL 化表头

create table

（column name 列名）

（data type 数据类型 length 长度）

（constraint 约束唯一且非空）

alter table 修改表结构

drop table

DML 填数据

insert 插入数据，一行

update 修改数据，某行的某列值／某些行的某列值／所有行的某列值

delete 删除一行

TCL --TransactionControl Langage

commit 提交

rollback 回滚（刚才作的动作消除了相当于ctrl+z）

DQL --Data QueryLangage

select 查询

DCL Data ControlLangage

grant 授权

revoke 收回授权

多用户系统

权限

例如：connect tarena/tarena

create table account();

grant select on account to jsd1303//给jsd1303看account表

revoke select on account to jsd1303//不给jsd1303看account表

connect jsd1303/jsd1303

select...from tarena.account

select...form account(jsd1303里的account)

create database 创建数据库

DBA(database adminstrator) 数据库管理(ocp)

在平台(os)安装oracle软件 unix,linux

linux:sql developer

solaris:192.168.0.20 192.168.0.23192.168.0.26 //数据库的IP

连接数据库需要的信息数据库的IP 端口号数据库名用户名口令

查询表：源表

desc account

select 语句的查询结果：结果集

投影操作：结果是部分列

选择操作：结果是部分行

连接操作：结果集来自多张表

select real_name,idcard_no fromaccount

selectbase_cost,base_duration,unit_cost from cost

select 语句包含多个子句

select 子句实现投影操作

from 子句标名

sql 4GL 我要什么，rdbms 解放程序员

列别名适合计算字段，原名和别名之间可以使用as关键字

别名中包含空格特殊字符或者希望大小写敏感，用“”

双引号表示标识名，列别名就是一个标识名

oracle中的‘s用‘‘‘s‘表示,其中的两个‘’表示中文‘

desc account;

selectnvl(base_cost,0)+(250-nvl(base_duration,0))*nvl(unit_cost,0) fee_250 from cost;

selectnvl(base_cost,0)+(250-nvl(base_duration,0))*nvl(unit_cost,0) "Fee 250"from cost;

select * from cost;

select real_name ||‘ ‘|| idcard_no client from account;

select real_name||‘‘‘s IDCARD NOis ‘||idcard_no||‘.‘ carddd from account;

select distinct unix_host fromservice;

select子句后面可以跟列名，＊，表达式（算术，字符串），distinct

distinct 对结果集去重

where 子句后跟条件表达式

列名常量

比较运算符

文字值

子句后不能跟列别名

session 会话

select count(*) from v$session whereusername=‘JSD1303_1’;

会话（session）查看jsd1303_1进行登录的人数

课外练习：

1 有哪些不同包在线时常 base_duration

2 理解连接过程

3 预习where子句

比较：

select base_cost*12,base_duration*12 fromcost where base_cost*12=70.8;

select base_duration*12 from cost wherebase_cost=70.8/12;//这个相对来说较好

执行顺序：from-->where-->select

where 不可跟列别名，后尽量不跟表达式

“”引起来的oracle表示标识符

select unix_host from service where"OS_USERNAME"=“huangr“

select unix_host from service whereos_username=“huangr“;其中"huangr"表示列名

不加‘’也表示标识符select unix_host from service where os_username=huangr;其中huangr表示列名

select unix_host from service whereos_username=‘HUANGR’;//字符串不一样

upper(p1)大写 lower(p1)小写 initcap(p1)首字符大写

select unix_host from service whereupper(os_username) = ‘HUANGR‘;

select unix_host from service wherelower(os_username) = ‘huangr‘;

select unix_host from service whereinitcap(os_username) = ‘Huangr‘;

where 条件用and or连接，并且and优先级高于or

between..A1..and..B1. ==[A1,B1]

多值运算的集合 in （） =any()

select base_cost from cost where base_costin(5.9,8.5,10.5);

select base_cost , base_duration from costwhere base_cost = any(5.9,8.5,10.5);

in（）多值运算的集合

＝单值运算

＝any() 多值运算的集合

null不等于0

null不等于空格

算术表达式中包含null结果为null

算术表达式中包含null需空值转换nvl

nvl()类型可以是number characterdate,参数类型必须一致

＝精确比较

like 像....什么一样模糊匹配

%表示0个任意多个字符

_表示任意一个字符

通配符的转义: _的转义

select os_username,unix_host fromservice where os_username like ‘h\_%‘ escape ‘\‘;

is null:

select base_cost,unit_cost from costwhere unit_cost is null;

不等于：<> ^= !=

is null －－－－－－－－－－－－－> isnot null

like－－－－－－－－－－－－－－－ >not like

between and －－－－－－－－－－ > not between and

in－－－－－－－－－－－－－-－－－> not in<============> <>and <>and<> <>all

select base_cost,unit_cost from costwhere base_cost <>5.9 and base_cost^=8.5 and base_cost!=10.5;

not in()集合中包含null值，select语句执行结果为no rows selected

select base_cost,unit_costfrom cost where base_cost not in(5.9,8.5,10.5) or base_cost is null;

in()集合中包含null值，select语句执行结果和没有null都是一样的

select base_cost,unit_cost from costwhere base_cost in (5.9,8.5,10.5) or base_cost is null;

asc升序 des降序

按照整张表的第二列排序：

select base_cost,unit_cost from costorder by 2;

select 后面跟的2是常量

select 2 from cost;

单行单列

select 2*2 from dual;

select upper(‘abc‘) fromdual;//ABC

select sysdate from dual;//查时间

alter session set nls_date_format=‘yyyy-mm-ddhh24:mi:ss‘;//修改时间

drop table slf purge;

字符大小敏感，日期格式敏感

要一个日期值，to_date

处理一个date类型的值，为了获得时间信息，to_char

select create_date,os_usernamefrom service where to_char(create_date,‘fmmm‘)=‘03‘;／／加上fm表示去掉空格和前倒立

select to_number(‘ab‘,‘xx‘)fromdual; 16*10+11

number 可以定义宽度

date 一定不能定义宽度

archar2必须定义宽度

char 可以不定义宽度（缺省是1）

字符大小写敏感 varchar2对空格是敏感的

select * from account;

select * from cost;

select * from service;

selectreal_name,create_date,idcard_no,birthdate,telephone from account;

select base_cost,base_durationfrom cost where base_cost=5.9;

selectbase_cost*12,base_duration*12 ann_duration from cost where base_cost=5.9;

selectbase_cost*12,base_duration*12 from cost where base_cost*12=70.8;

select base_duration*12 from costwhere base_cost=70.8/12;

select unix_host from servicewhere os_username=‘huangr‘;

select unix_host from servicewhere upper(os_username) = ‘HUANGR‘;

select unix_host from servicewhere lower(os_username) = ‘huangr‘;

select unix_host from servicewhere "OS_USERNAME" = ‘huangr‘;

select unix_host from servicewhere upper(os_username) = ‘HUANGR‘;

select unix_host from servicewhere lower(os_username) = ‘huangr‘;

select unix_host from servicewhere initcap(os_username) = ‘Huangr‘;

select unix_host from servicewhere upper(os_username) = ‘HUANGR‘;

select unix_host from servicewhere lower(os_username) = ‘huangr‘;

select base_cost from cost wherebase_cost>=5 and base_cost<=10;

select base_cost from cost wherebase_cost between 5 and 10;

select base_cost from cost wherebase_cost = 5.9 or base_cost=8.5 or base_cost=10.5;

select base_cost from cost wherebase_cost in(5.9,8.5,10.5);

select base_cost , base_durationfrom cost where base_cost = any(5.9,8.5,10.5);

select os_username,unix_host fromservice where os_username like ‘h%‘;

select os_username from service;

select os_username,unix_host fromservice where os_username like ‘h\_%‘ escape ‘\‘;

select base_cost,unit_cost

from cost

where unit_cost is null;

select base_cost,unit_cost

from cost

where unit_cost is not null;

select base_cost,unit_cost fromcost

where nvl(base_cost,0) notin(5.9,8.5,10.5);

select base_cost,unit_cost fromcost

where base_cost <>5.9 andbase_cost^=8.5 and base_cost!=10.5;

select base_cost,unit_cost fromcost

where base_cost notin(5.9,8.5,10.5) or base_cost is null;

select base_cost,unit_cost fromcost

where base_cost in (5.9,8.5,10.5)

or base_cost is null;

select os_username,create_datefrom service order by create_date;

select os_username,create_date fromservice order by create_date desc;

select base_cost from cost orderby base_cost desc;

select base_cost*12,name fromcost order by base_cost*12 desc;

select base_cost,unit_cost fromcost order by 2;

select 2 from cost;

select 2*2 from dual;

select upper(‘abc‘) from dual;

select unix_host,create_date fromservice

order by unix_host,create_datedesc;

create table slfslf(c1 number,c2number(6),c3 number(4,3),c4 number(3,-3),c5 number(2,4));

insert into slfslf(c1,c2) values(15000,2222);

select * from slfslf;

insert into slfslf values(1,2222,2.222,333033.,0.0042);

insert into slfslf values(2,555555,5.666,3333,0.0099);

insert into slfslf values(2,555555,5.6666,3333,0.0099);

update slfslf set c3=9.999 wherec1=15000;

select round(base_cost/30,1),round(base_cost/30),

trunc(base_cost/30),trunc(base_cost/30,1)

from cost;

select sysdate from dual;

alter session setnls_date_format=‘yyyy-mm-dd hh24:mi:ss‘;

select create_date from service;

create table slf (c1 date);

insert into slf values(‘2008-05-23 14:22:21‘);

//insert into slfvalues(to_date(‘1-september-08‘,‘DD-MON-RR‘));

insert into slf values(to_date(‘2008-01-01‘,‘yyyy-mm-dd‘));

select * from slf;

select c1 from slf;

selectto_char(c1,‘DD-MON-RR‘)from slf;

insert into slf values (to_date(‘200101 01‘,‘yyyy mm dd‘));

select to_char(c1,‘yyyy‘)fromslf;

insert into slfvalues(to_date(‘2008-08-08 08:08:08‘,‘yyyy-mm-dd hh24:mi:ss‘));

select to_char(c1,‘yyyy-mm-ddhh24:mi:ss‘)from slf;

insert into slfvalues(‘2013-10-12 03:40:20‘);

select to_char(c1,‘yyyy-mm-ddhh:mi:ss‘)from slf;

select create_date,os_usernamefrom service where to_char(create_date,‘fmmm‘)=‘3‘;

select to_number(‘ab‘,‘xx‘)fromdual;

selectbase_cost,nvl(to_char(unit_cost),‘no unit cost‘) from cost;

列出基础资费

select base_cost,nvl(to_char(unit_cost),‘nonuit cost‘)from cost;

列出昨天今天和明天

alter session set nls_date_format=‘yyyy mm dd hh24:mi:ss‘;

selectsysdate-1,sysdate,sysdate+1 from dual;

select sysdate,sysdate+1/144 fromdual;

列出主机，用户名，以及开通的多长时间，并按照开通时间排序

select unix_host,os_username,create_date,round(sysdate-create_date)days

from service order by 4 desc;

／／设置日期格式此是默认的

alter session setnls_date_format=‘DD-MON-RR‘;

alter session setnls_language=‘AMERICAN‘;

半年后

select add_months(‘01-JAN-08‘,6)from dual;

输出星期几（字符格式）

select to_char(sysdate,‘DAY‘)from dual;

输出95-9-8的下个星期五

selectnext_day(‘08-SEP-95‘,‘FRIDAY‘) from dual;

输出当月的最后一天

select last_day(sysdate)fromdual;

alter session set nls_territory =america;

select add_months(sysdate,-1)from dual;／／上个月

select add_months(sysdate,1) fromdual;／／下个月

select last_day(sysdate) fromdual;／／最后一天

selectto_char(last_day(sysdate),‘DAY‘)from dual;／／星期几

／／base_duration 为20时unit_cost加0.05，base_duration为40时unit_cost加0.03

方法1

select case when base_duration =20 then unit_cost + 0.05

when base_duration = 40 thenunit_cost + 0.03

else unit_cost

endnew_unit_cost,base_duration,unit_cost

from cost;

方法2

selectdecode(base_duration,20,unit_cost+0.05,

40,unit_cost+0.03,

unit_cost)

new_unit_cost,base_duration,unit_cost

from cost;

函数

单行函数

数据函数 number varchar2 char date

数值函数 round trunc

字符函数 upper lower initcap length trim

日期函数 add_months months_between last_day

转换函数 to_date to_char to_number

一般函数 nvl decode

表达式 case when

环境设置 session

NetCTOSS 报表

avg 平均值 number

sum 求和 number

count 计数

max 最大值 number

min 最小值 number

组函数的返回值

1 所有的非空值的处理,返回值不为null

2 处理值都为null，count为0，其他都为null

语法顺序

select from where group by havingorder by

执行顺序

from where group byhaving select order by

select(对数据处理)

from(数据源：表)

where(选择操作:过滤行记录)

group by(分组)

having(过滤组记录)

order by(排序对象，select)

1. 当月包在线时长为20小时，单位费用涨5分，为40小时涨3分，其他不变（用UNION ALL实现）

select case when base_durationbetween 20 and 40

then unit_cost+0.05

when base_duration >40

then unit_cost+0.03

else unit_cost

endnew_unit_cost,unit_cost,base_duration

from cost;

2 月包在线时长在40到100之间，月固定费用涨5角，月包在线时长大约100小时，月固定费用涨3角，其余不变

select case when base_durationbetween 40 and 100

then base_cost + 0.05

when base_duration<40

then base_cost

else

base_cost+0.03

endnew_base_cost,unit_cost,base_cost,base_duration

from cost;

3 huangrong的推荐人

select real_name, idcard_no

from account

where id in(select recommender_id

from account

where real_name=‘huangrong‘);

4 huangrong推荐了谁

select real_name,idcard_no

from account

where recommender_id in(select id

from account

wherereal_name=‘huangrong‘);

5 那些客户推荐了新客户又被老客户推荐

select real_name,idcard_no

from account

where recommender_id is not null

and id in (select recommender_id

from account);

6 哪些用户没被推荐，也没推荐别人

select real_name,idcard_no

from account

where recommender_id is null

and id not in(selectnvl(recommender_id,0)

from account);

7 那些os帐号的开通天数比同一台上unix服务器上的平均开通天数长

selectunix_host,os_username,create_date

from service o

whereround(sysdate-create_date)>(select round(avg(sysdate-create_date))

from servicei

wherei.unix_host=o.unix_host);

关联子查询：

子查询引用主查询的列

8 哪些客户是推荐人

select real_name,idcard_no

from account o

where exists (select 1 fromaccount i

where o.id=i.recommender_id);

9 哪些客户开通了远程登录业务

select real_name,idcard_no

from account

where exists(select 1 fromservice

whereaccount.id=service.account_id);

10 哪些客户没有申请远程登录业务

select real_name,idcard_no

from account

where not exists(select 1 fromservice

whereaccount.id=service.account_id);

11 哪些客户不是推荐人

select real_name,idcard_no

from account o

where not exists(select 1 fromaccount i

where o.id=i.recommender_id);

子查询：

非关联子查询 in,not in：

单列子查询

多列子查询（多列同时比较）

关联子查询 exists,not exists

主属性：记录的唯一标识 id 约束主键

idunix_host,os_username,,,account_id real_name,idno

非主属性 account_id 和real_name

real_name依赖account_id

组合问题

select tabname1.colname1,tabname2.colname2

from tabname1 join tabname2

on tabname1.colname2=val1

and tabname1.colname1=tabname2.colname2;

from on(过滤表)joinon(内连接)select

from(t1 t2)-->t1表过滤-->过滤后的结果集跟t2内连接

哪些客户在unix服务器上申请了远程登录

selecta.real_name,a.id,s.account_id,s.os_username

from account a cross join services

where a.id=s.account_id;

哪些客户在unix服务器上申请了远程登录

selectaccount.real_name,service.account_id

from account join service

on account.id=service.account_id;

客户huangrong在那些unix服务器上申请了远程登录

selecta.id,a.real_name,s.unix_host

from account a join service s

on a.real_name=‘huangrong‘

and a.id=s.account_id;

列出客户姓名以及开通的远程登录的数量

selecta.real_name,count(s.os_username)

from account a join service s

on a.id=s.account_id

group by a.real_name;

先连接（记录数多），再分组，再计算

select a.real_name,s.cnt

from account a join(selectaccount_id ,count(*) cnt

from service group byaccount_id)s

on a.id=s.account_id;

先分组，再计算，结果集做内连接

先执行s,s和account 做内连接

列出每台中比开通天数比平均开通天数大的

selecta.create_date,a.os_username,s.days

from service a join(selectunix_host,round(avg(sysdate-create_date))days

from service group byunix_host)s

on a.unix_host=s.unix_host

and round(sysdate-a.create_date)> s.days;

列出客户姓名以及他的推荐人

select a.id,a.real_namerecommendername,s.real_name

from account a join(selectreal_name,recommender_id

from account)s

on a.id=s.recommender_id;

select t1.real_name client,

decode(t2.id,t1.id,‘norecommender‘,t2.real_name) recommender

from account t1 join account t2

on nvl(t1.recommender_id,t1.id) =t2.id;

哪些客户是推荐人

select distinct t2.real_name

from account t1 join account t2

ont1.recommender_id=t2.id;

关联子查询

主表和子表之间建关联，两张表的列写成表达式＝

account service a.id=s.account_id

service cost s.cost_id=c.id

内连接

主表：驱动表

子表：匹配表

驱动表和匹配表建关联，两张表的列写成表达式＝

两张不同表的记录关系一对多

同一张表的记录关系一对多（一个客户可以推荐多个客户）

同一张表的记录关系叫自连接

外连接

左外连接，右外连接

跟内连接的区别：

驱动表是固定的

驱动表的所有记录都在结果集里

from t1 lef (outer)join t2 ／／t1是驱动表

on t1.c1=t2.c2

外连接的结果集：是内连接的结果集＋t1表中不匹配的记录和一条null记录（按t2表的结构）的组合

from t1 right (outer) joint2 ／／t2是驱动表

on t1.c1=t2.c2

from t1 full join t2

on t1.c1=t2.c2

列出客户姓名以及推荐人

select t1.real_nameclient,t2.real_name recommender

from account t1 join account t2

onnvl(t1.recommender_id,t1.id)=t2.id;

列出客户姓名以及推荐人

select t1.real_name client,

decode(t2.id,t1.id,‘Norecommender‘,t2.real_name) recommender

from account t1 join account t2

onnvl(t1.recommender_id,t1.id)=t2.id;

select t1.id,t1.real_nameclient,nvl(t2.real_name,‘No recommender‘) recommender

from account t1 left join accountt2

on t1.recommender_id = t2.id;

列出顾客开通那些远程登录

selecta.id,a.real_name,s.unix_host

from account a left join services

on a.id=s.account_id;

列出顾客开通那些远程登录

select a.id,a.real_name,s.*

from account a left join services

on a.id=s.account_id;

列出每个顾客都开通了多少远程登录（count跟匹配表里的非空列）

select max(a.id),max(a.real_name),count(s.cost_id)

from account a left join services

on a.id=s.account_id

group by a.id;／／groupby 后跟驱动表中的列

select a.real_name,nvl(s.cnt,0)

from account a left join(selectaccount_id ,count(id) cnt

from service

group by account_id)s

on a.id=s.account_id;／／效率高

left join所有的客户（申请业务的客户＋没有申请业务的客户）

目的：降低表连接的数据量

那些人不是推荐人

select t1.real_name,t2.real_name

from account t1 left join accountt2

on t1.id =t2.recommender_id

where t2.id is null;

from t1 left join t2

on t1.c1=t2.c2

and t2.c3=‘‘

在外连接之前对匹配表作过滤

from t1 t2 -->对t2过滤－－>外连接

对匹配表的过滤弱项发生在连接之前，用on过滤

from t1 left join t2

on t1.c1=t2.c2

where t2.c1 is null

from t1 t2-->外连接-->where通过t2的列对外连接的结果集过滤

对驱动表的过滤必须用where子句实现

内连接解决问题：

匹配

外连接解决问题：

1 . 匹配问题＋不匹配问题（一个都不能少）

2. 不匹配问题（outer join+where匹配表.非空列 is null)-->not in,not exists

外连接的执行顺序

先将表进行外连接错作，再外连接的结果集用where子句进行过滤，最后用select 声称结果

哪些服务器上没有os帐号weixb

select h.id,s.os_username

from host h left join service s

on h.id=s.unix_host

and s.os_username=‘weixb‘

where s.os_username is null;

select h.id,s.os_username

from host h left join(selectunix_host,os_username

from service)s

on h.id=s.unix_host

and s.os_username=‘weixb‘

where s.os_username is null;

列出那个人属于那个年龄段

selecta.id,a.real_name,round((sysdate-a.birthdate)/365),s.name

from account a join age_segment s

onround((sysdate-a.birthdate)/365) between s.lowage and s.hiage;

huangrong的年龄段

selecta.id,a.real_name,round((sysdate-a.birthdate)/365),s.name,s.lowage,s.hiage

from account a join age_segment s

onround((sysdate-a.birthdate)/365) between s.lowage and s.hiage

where a.real_name=‘huangrong‘;

列出每个年龄段有多少客户

selectmax(s.name),count(a.real_name)

from age_segment s left join account a

on round((sysdate-a.birthdate)/365)between s.lowage and s.hiage

group by s.id;

各类连接的应用场合

交叉连接（cross join）

笛卡尔积

内连接（inner join）

解决匹配问题

1 等值连接＝

2 非等值连接 > <

3 自连接

外连接（outer join）

解决不匹配问题

表的所有记录出现在结果集

集合

union/union all 并集（去重）／并集（不去重）

intersect 交集（不重复）

minus 集合1－集合2

当月保在线时常为20小时单位费用涨5分，为40小时单位费用涨3分，其他不变

selectbase_duration,unit_cost+0.05

from cost

where base_duration=20

union all

selectbase_duration,unit_cost+0.03

from cost

where base_duration=40

union all

select base_duration,unit_cost

from cost

where base_duration not in(20,40)

or base_duration is null;

列出客户姓名以及推荐人（包含所有的客户）

select t2.real_name ,t1.real_namerecommender

from account t1 join account t2

on t1.id=t2.recommender_id

union all

select real_name,‘no recommender‘

from account

where recommender_id is null;

列出‘sun280’和‘sun-server‘用了那些相同的资费

select s.cost_id

from host h join service s

on h.id=s.unix_host

and h.name=‘sun280‘

intersect

select s.cost_id

from host h join service s

on h.id=s.unix_host

and h.name=‘sun-server‘;

那些服务器上没有开通远程登录

select id

from host

minus

select h.id

from host h join service s

on h.id =s.unix_host

group by h.id;

找出第一列

select rownum,real_name

from account where rownum=1;

select rownum,real_name

from account

where rownum between 1 and 5;

rownum只能找从1开始的数据

找出4-6条记录

方法1：

select rownum,real_name fromaccount where rownum<=6

minus

select rownum,real_name fromaccount where rownum<=3;

方法2：

select real_name

from (select rownum rm,real_name

from account where rownum <= 6) t

where t.rm>=4;

最早开通netctoss系统的前三个客户

selectreal_name,create_date,rownum

from (select real_name,create_date

from account order by create_date)

where rownum <=3;

第4到第6位开通的用户

select rm,real_name,create_date

from (select rownum rm,real_name,create_date

from account

order by create_date)s

where rm between 4 and 6;

pl/sql课程

jdbc

service_detail具体登录信息，详单

pl/sql项目课程计费月出帐

列出每个客户申请了那些资费政策

方法1

select ss.id,ss.real_name,ss.cost_id,c.name

from cost c join(selectc.id,c.real_name,a.cost_id

from account c join(selectaccount_id,cost_id,unix_host

fromservice)a

on a.account_id=c.id)ss

on c.id=ss.cost_id;

方法2

select a.real_name,c.name

from account a join service s

on a.id=s.account_id

join cost c

on c.id=s.cost_id

create table sfl(

c1 number constraint sfl_c1_pkprimary key,

c2 number);

insert into sfl values(1,1);

insert into sfl values(1,1);／／uniqueconstraint (JSD1303_1.SFL_C1_PK) violated错误用户.约束名(JSD1303_1.SFL_C1_PK)

主键约束：primary key

必须唯一并且不为空

列级约束：create table sfl(

c1 number constraint sfl_c1_pkprimary key,

c2 number);

表级约束：create table sfl(

c1 number(2),

c2 number,

constraint sfl_c1_pk primarykey(c1));

create table stu_cou(

sid number(4),

cid number(2),

constraint stu_cou_sidcid_pk primary key(sid,cid),

score number(3));//联合约束

给存在的表加约束

alter table account add constraintaccount_id_pk primary key(id)

唯一键约束：unique key

列名＋数据类型+constraint 表名_列名_ukunique

不允许重复

可以插入多个null

create table test(

c1 number(3) constraint test_c1_pk primary key,

c2 number(2),

c3 number(4),

constraint test_c3_uk unique(c2,c3));／／c2和c3组合唯一

非空约束：not null

列名＋数据类型＋not null

外键约束：references foreign key

create table child(

c1 number(3)constraint child_c1_pkprimarykey,

c2 number(2) constraint child_c2_fk referencesparent(1));／／表child中的c2列要从parent去取得

被引用的表：父表parent pk

定义外键的表：子表child fk 可以重复

保证一对多关系的实现

在子表的一列上定义外键：外键列

外键列要引用父表中的一列，要求这列唯一特性（pk,uk）

1 先create parent table,被引用列必须保证唯一（pk,uk），再create child table,建fk列

2 先insert into parenttable,再insert into child table

3 先delete from child,再deletefrom parent

4 先drop child tble,再dropparent table

drop table parent cascade constraints purge;//删除父表和子表之间的外键联系，并且删除父表

create table child1(

c1 number primary key,

c2 number references parent(c1)

on delete cascade);//加on delete cascade 时可以直接用drop parentwhere c1=2，将父表和子表中对应的记录同时删除

create table child2(

c1 number primary key,

c2 number references parent(c1)

on delecte set null);//删除drop parent where c1=2，删除父表中的记录，并且将子表的c2=2的记录设置为c2=null

检查约束：check

列名＋数据类型＋constraints+表名_列名_ckchect （表达式）；

create table child(c1number(3)constraint child_c1_pk primarykey,

c2 number(2) constraintchild_c2_fk references parent(1));

create table stlt_cou(

suid number,

cuid number,

constraint stlt_cou_suidcuid_pkprimary key(suid,cuid),

score number);

create table childd(

c1 number(2) constraintchildd_c1_pk primary key,

c2 number(3) constraintchildd_c2_fk references parentt(c1));

多对多

增加一张中间表，两个fk,fk引用多对多的两张表。两个一对多，从而实现多对多

事物transaction

commit(提交) －－》TCL

transaction=dmls+commit/rollbanck

dmls

create table(ddl自动提交)

数据库中有很多session,每个session只有一个活动事物（未commit）

事物隔离级别

pk uk fk ck nn

1 列上多个约束

1 列定义在多个列上

1对多关系 pk、uk fk 违反3范式

多对多关系增加一张表，定义2个fk,违反2范式

分开：没有数据冗余，没有数据不一致，dml的负担，数据记录很纯粹，select 作join操作，规范化

合表：dml负担重，select简单，快，去规范化做法

fk pk pk

name id |id name

zs 1 1 ls

sql脚本（放在跟数据库建立连接的本机上）

//创建 slf_account表

create table slf_account(

id number(9) ,

recommender_id number(9),

login_name varchar2(30) not null,

login_passwd varchar2(8) notnull,

status char(1) not null,

create_date date default sysdate,

pause_date date,

clost_date date,

real_name varchar2(20) not null,

idcard_no char(18) not null,

birthdate date,

gender char(1) not null,

occupation varchar2(50),

telephone varchar2(15) not null,

email varchar2(50),

mailaddress varchar2(50),

zipcode char(6),

qq varchar2(15),

last_login_time date,

last_login_ip varchar2(15),

constraint slf_account_id_pkprimary key(id),

constraintslf_account_recommender_id_fk foreign key(recommender_id) referencesslf_account(id),

constraint slf_account_status_ckcheck(status in(0,1,2)),

constraintslf_account_idcard_no_uk unique(idcard_no),

constraint slf_account_gender_ckcheck(gender in(0,1)));

//创建slf_cost表

create table slf_cost(

id number(4) ,

name varchar2(50) not null,

base_duration number(11),

base_cost number(7,2),

unit_cost number(7,4),

status char(1) not null,

descr varchar2(100),

creatime date default sysdate,

startime date,

constraint sl_cost_id_pk primarykey(id),

constraint sl_cost_statuscheck(status in (0,1)));

//创建slf_service表

create table slf_service(

id number(10),

account_id number(9) not null,

unix_host varchar2(15) not null,

os_username varchar2(8) not null,

login_passwd varchar2(8) notnull,

status char(1) not null,

create_date date default sysdate,

pause_date date,

close_date date,

cost_id number(4) not null,

constraint slf_service_id_pkprimary key(id),

constraintslf_service_account_id_fk foreign key(account_id)references slf_account(id),

constraintslf_service_host_username_uk unique(os_username,unix_host),

constraint slf_service_status_ckcheck (status in(0,1,2)),

constraint slf_service_cost_id_fkforeign key(cost_id) references slf_cost(id)

);

//创建slf_host表

create table slf_host(

id varchar2(15) primary key,

name varchar2(20) not null,

location varchar2(20)

);

//创建slf_service_detail表

create table slf_service_detail(

id number(11) ,

service_id number(10) not null,

client_host varchar2(15),

os_username varchar2(8),

pid number(11),

login_time date,

logout_time date,

duration number(20,9),

cost number(20,6),

constraintslf_service_detail_id_pk primary key(id),

constraintslf_service_detail_sid_fk foreign key(service_id)references slf_service(id)

);

//创建slf_fee_log表

create table slf_fee_log(

log_date date not null,

service_id number(10),

fee_month char(6),

descr varchar2(500)

);

//创建slf_month_duration表

create table slf_month_duration(

service_id number(10),

month_id char(6),

service_detail_id number(11),

sofar_duration number(11)

);

//创建slf_bill表

create table slf_bill(

id number(11),

account_id number(9)not null,

bill_month char(6)not null,

cost number(13,2) not null,

payment_mode char(1),

pay_state char(1) default 0,

primary key(id),

foreign key(account_id)referencesslf_account(id),

check(payment_mode in(0,1,2,3)),

check(pay_state in(0,1))

);

//创建slf_bill_item表

create table slf_bill_item(

item_id number(11),

bill_id number(11) not null,

service_id number(10) not null,

cost number(13,2),

primary key(item_id),

foreign key(bill_id)referencesslf_bill(id),

foreign key(service_id)referencesslf_service(id)

);

//创建slf_role_info表

create table slf_role_info(

id number(11),

name varchar2(20)not null,

primary key(id)

);

INSERT INTO SLF_COST VALUES(1,‘5.9元套餐‘,20,5.9,0.4,0,‘5.9元20小时/月,超出部分0.4元/时‘,DEFAULT,NULL);

INSERT INTO SLF_COST VALUES(2,‘6.9元套餐‘,40,6.9,0.3,0,‘6.9元40小时/月,超出部分0.3元/时‘,DEFAULT,NULL);

INSERT INTO SLF_COST VALUES(3,‘8.5元套餐‘,100,8.5,0.2,0,‘8.5元100小时/月,超出部分0.2元/时‘,DEFAULT,NULL);

INSERT INTO SLF_COST VALUES(4,‘10.5元套餐‘,200,10.5,0.1,0,‘10.5元200小时/月,超出部分0.1元/时‘,DEFAULT,NULL);

INSERT INTO SLF_COST VALUES (5,‘计时收费‘,null,null,0.5,0,‘0.5元/时,不使用不收费‘,DEFAULT,NULL);

INSERT INTO SLF_COST VALUES (6,‘包月‘,null,20,null,0,‘每月20元,不限制使用时间‘,DEFAULT,NULL);

commit;

ALTER SESSION SET NLS_DATE_FORMAT= ‘yyyy mm dd hh24:mi:ss‘;

INSERT INTOSLF_ACCOUNT(ID,RECOMMENDER_ID,LOGIN_NAME,LOGIN_PASSWD,STATUS,CREATE_DATE,