Oracle学习系列6
************************************************************************************
删除约束（重点）：
格式：
alter table person drop constraint constraint_name ;
创建person表：
create table person(
UID varchar(18)
name varchar(200) not null, //非空约束
age number(3) not null, //非空约束
birthday date,
sex varchar(2) default '男',
) ;
/**
主键约束：主键字段_PK
唯一约束：字段_UK
检查约束：字段_CK
外键约束：父字段_子字段_FK
*/
ex:为表添加若干个约束，
格式：
alter table tab_name add constraint constraint_name constraint_type(constrain_colx) ;
ex:添加约束
alter table person add constraint person_uid_PK primary key(uid);//增加主键约束
alter talbe person add constraint person_name_UK unique(uid); //增加唯一约束
alter table person add constraint person_age_CK checke(age between 0 and 150) ;
alter table person add constraint person_sex_CK check(sex in ('男','女','中')) ;
ex:删除约束
alter table person drop constraint person_uid_PK ;//删除主键约束
alter talbe person drop constraint person_name_UK ; //删除唯一约束
alter table person drop constraint person_age_CK ; //删除检查约束
alter table person drop constraint person_sex_CK ; //删除检查约束
************************************************************************************
rownum（重点）：
rownum: 表示行号，实际上是一个伪列，可在每张表中出现
select rownum, empno, ename ,job,sal,hiredate
from emp ; // ROWNUM 采用自动编号的方式呈现
ex:
select rownum, empno, ename, job, sal hiredate
from emp
where rownum <=5 ;
************************************************************************************
建表、约束、查询综合练习：
题目背景：
有某个学生运动会比赛信息的数据库，保存了如下的表：
运动员sporter（运动员编号sporterid, 运动员姓名name, 运动员性别sex, 所属系好department）
项目item (项目编号itemid, 项目名称itemname, 项目比赛地点location)
成绩grade(运动员编号sportid,项目编号itemid, 积分mark)
1建表要求：
a,定义各个表的主键外码约束
b,运动员的姓名和所属系别不能为空值
c,积分要么为null，要么为6，4，2，0，分别代表第一二三和其他名词的积分
create table sporter(
sporterid nummber(4) primary key not null,
name varchar2(50) not null,
sex varchar2(2) not null,
department varchar2(30) not null,
constraint sporter_sex_CK check(sex in('男','女'))
);
create table item(
itemid varchar2(4) primary key not null,
itemname varchar2(50) not null,
location varchar2(50) not null
);
create table grade(
sporterid number(4),
itemid varchar2(4),
mark number(2),
constraint sporter_grade_sporterid_FK foreign key(sporterid)
references sporter(sporterid) on delete cascade
constraint item_grade_itemid_FK foreign key(itemid)
references item(itemid) on delete cascade
constraint grade_mark_CK check(mark in (6,4,2,0))
);
测试数据：
insert into sporter(sportid, name, sex, department )
values(1101,'黎明','男','计算机系');
insert into sporter(sportid, name, sex, department )
values(1102,'张三','男','数学系系');
insert into sporter(sportid, name, sex, department )
values(1103,'李四','男','计算机系');
insert into sporter(sportid, name, sex, department )
values(1104,'王五','男','物理系');
insert into sporter(sportid, name, sex, department )
values(1105,'李楠','女','心理系');
insert into sporter(sportid, name, sex, department )
values(1106,'孙俪','女','艺术系');
---------------------------------------------------------
insert into item(itemid ,itemname, location)
values('x001','男子五千米','一操场') ;
insert into item(itemid ,itemname, location)
values('x002','男子标枪','一操场') ;
insert into item(itemid ,itemname, location)
values('x003','男子跳远','二操场') ;
insert into item(itemid ,itemname, location)
values('x004','女子跳高','二操场') ;
insert into item(itemid ,itemname, location)
values('x005','女子三千米','三操场') ;
---------------------------------------------------------
insert into grade(sporterid, itemid,mark)
values(1001,'x001',6);
insert into grade(sporterid, itemid,mark)
values(1002,'x001',4);
insert into grade(sporterid, itemid,mark)
values(1003,'x001',2);
insert into grade(sporterid, itemid,mark)
values(1004,'x003',2);
insert into grade(sporterid, itemid,mark)
values(1005,'x006',6);
insert into grade(sporterid, itemid,mark)
values(1006,'x004',0);
insert into grade(sporterid, itemid,mark)
values(1003,'x005',2);
insert into grade(sporterid, itemid,mark)
values(1003,'x003',4);
insert into grade(sporterid, itemid,mark)
values(1003,'x002',6);
要求：
求出目前总积分最高的系名，及其积分：
select * from (
select s.department , sum(g.mark) sum
from sporter s, grade g
where s.sporterid=g.sporterid
group by s.department
order by sum desc
)
where rownum=1
找出在一操场进行比赛的各项目名称及其冠军的姓名：
select i.itemname, s.name, g.mark
from item i, grade g,sporter s
where i.location='一操场'
and i.itemid=g.itemid
and s.sporterid=g.sporterid
and g.mark=6 ;
找出参加了张三所参加过的项目的其他同学的姓名：
select distinct s.name
from sporter s, grade g
where s.sporterid=g.sporterid and s.name <>'张三'
and g.itemid IN (
select g.itemid from sporter s ,grade g
where s.sporterid=g.sporterid
and s.name='张三'
) ；
经查张三使用了违禁药品，其成绩都记0分，请在数据库中做出相依修改：
update grade
set mark=0
where sporterid =(
select sportid from sporter where name ='张三'
) ；
经组委会协商，需要删除女子跳高比赛项目：
delete from item
where itemname='女子跳高' ；
------------------------------------------------------------
删除顺序： //先删子表，再删主表
drop table grade;
drop table sporter;
drop table item ;
************************************************************************************
集合操作：
分类：
并（UNION） :将多个查询的结果组合到一个查询结果中，无重复值 //UNIONALL：包含重复值
交（INTERSECT）：返回多个查询结果相同的部分
差(MINUS) ：返回两个结果的差集
复制emp表，将部门20的雇员信息取出来：
create table emp20
as select * from emp
where deptno=20 ;
验证UNION：
select * from emp
union
select * from emp20 ;
验证UNIONALL：
select * from emp
unionall
select * from emp20 ;
验证INTERSECT：
select * from emp
INTERSECT
select * from emp20 ;
验证MINUS：
select * from emp
minus
select * from emp20 ;
----------------------------------------------------------------------------------
SQL查询最终格式：
////////////////////////////////////////////////////////////////////////////////////////////
select { distinct } * | col1 别名1 col2 别名2 ...
from tab1 别名1 ， tab2 别名2 ，
（
select { distinct } * | col1 别名1 col2 别名2 ...
from tab1 别名1 ， tab2 别名2 ， tab3 别名3 ，...
{where 条件s }
{group by 分组条件 { having 分组条件 } }
{ order by col1 ASC | DESC , col2 ASC | DESC, ...} ;
）别名x tab3 别名3 ，...
{where 条件s
（
select { distinct } * | col1 别名1 col2 别名2 ...
from tab1 别名1 ， tab2 别名2 ， tab3 别名3 ，...
{where 条件s }
{group by 分组条件 { having 分组条件 } }
{ order by col1 ASC | DESC , col2 ASC | DESC, ...} ;
）
}
{group by 分组条件 { having 分组条件 } }
{ order by col1 ASC | DESC , col2 ASC | DESC, ...} ;
{UNION | INTERSECT |MINUS}
select { distinct } * | col1 别名1 col2 别名2 ...
from tab1 别名1 ， tab2 别名2 ，
（
select { distinct } * | col1 别名1 col2 别名2 ...
from tab1 别名1 ， tab2 别名2 ， tab3 别名3 ，...
{where 条件s }
{group by 分组条件 { having 分组条件 } }
{ order by col1 ASC | DESC , col2 ASC | DESC, ...} ;
）别名x tab3 别名3 ，...
{where 条件s
（
select { distinct } * | col1 别名1 col2 别名2 ...
from tab1 别名1 ， tab2 别名2 ， tab3 别名3 ，...
{where 条件s }
{group by 分组条件 { having 分组条件 } }
{ order by col1 ASC | DESC , col2 ASC | DESC, ...} ;
）
}
{group by 分组条件 { having 分组条件 } }
{ order by col1 ASC | DESC , col2 ASC | DESC, ...} ;
////////////////////////////////////////////////////////////////////////////////////////////
