*注:此笔记为个人在学习Oracle时从教学视频、参考书上摘录整理而成,纯手打完成,如需转载麻烦表明出处,附上连接(http://blog.csdn.net/sherkyoung/article/details/26738837),谢谢!
SQL语句
一、数据表的创建及管理
对于数据库而言实际上每一张表都表示的是对一个数据库的对象,而数据库对象指的就是DDL定义的所有操作,例如:表、试图、索引、序列、约束等,都属于对象的操作,所以表的建立就是对象的建立,而对对象的操作主要分为三类:
l 创建对新疆:CREATE对象名....;
l 删除对象:DROP对象名....;
l 修改对象:ALTER对象名称...;
1、常用的数据字段
每一张数据表实际上都是又若干个字段组成,而每一个字段都会有对应的数据类型,在Oracle中常用的数据类型有以下几种:
No. | 数据类型 | 关键字 | 描述 |
1 | 字符串 | VARCHAR2(n) | 其中n表示的字符串所能存储的最大长度,基本上保存200左右 |
2 | 整数 | NUMBER(n) | 表示最多为n位的整数,有时也能用int代替 |
3 | 小数 | NUMBER(n.m) | 其中m为小数的位数,n-m为整数的位数,有时也用FLOAT代替 |
4 | 日期 | DATE | 可以存放日期 |
5 | 大文本 | CLOB | 可以存储海量的文字(4G) |
6 | 大对象 | BLOB | 可存放二进制,例如:电影MP3 文字 图片等 |
一般的开发之中使用最多的就是:VARCHAR2()、NUMBER、DATE、CLOB,而BLOB使用较少。BLOB虽然可以存放4G的二进制数据,但是存放进去之后会导致数据库过于庞大而且读取不方便。
2、数据表的创建
创建表的语法如下:
CREATE TABLE 表名称(
字段1 数据类型 [DEFAULT 默认值] ,
字段2 数据类型 [DEFAULT 默认值] ,
........
字段n 数据类型 [DEFAULT 默认值]
) ;
下面创建一张成员表(member),有如下的信息:姓名、年龄、生日、个人简介
CREATE TABLE member(
name VARVHAR2(50) DEFAULT ‘无名氏’,
age NUMBER(3) ,
Birthday DATE DEFAULT SYSTEM ,
Content CLOB
) ;
现在插入数据:
INSERT INTO member(name,age,birthday,content)
VALUES('张三',22,TO_DATE('1992-08-12','yyyy-mm-dd'),'我叫张三,今年20岁');
一定要记住,标的创建属于数据库对象的车创建,所以使用CREATE语法。
三、数据表的复制
进行表的复制时,用一下语法:
CREATE TABLE 复制表名称 AS 子查询 ;
范例:复制一张只包含20部门的雇员信息的表;
CREATE TABLE emp20 AS SELECT * FROM emp WHERE empno=20 ;
范例:将emp表的表结构复制出来,不要数据;——写一个永远也无法满足的条件即可
CREATE TABLE emp20 AS SELECT * FROM emp WHERE 1=2 ;
但是以上操作只有Oracle数据库所支持的操作,其他数据库语法上有所不同;
四、为表重命名
在Oracle数据路之中,所有的数据实际上都是通过数据字典保存的;例如:
SELECT * FROM tab ;
以上就是一个数据字典,在Oracle中提供了三种类型的数据字典,最常用的是:dba_、user_,所以下面查询一个user_tables数据字典:
SELECT * FROM usr_tables ;
也就是说Oracle中所有的数据都是按照文件保存的,那么所有的内容都会在数据字典中注册,既然这样,所谓的修改表名称实际上对于Oracle而言就相当于修改一条数据而已,而修改表名称的语法如下:
RENAME 旧表名称 TO 新表明
但是这种操作是Oracle独有的特性,所以了解即可。
五、截断表
删除表数据操作使用的是DELETE,但是这种操作本身有一个特点,即:可以进行事务的回滚,也就是说删除之后不会立即释放数据资源。如果像策划释放一张表所占的全部资源(表空间、索引等),可以使用截断表语法,语法如下:
TRUNCATE TABLE 表名称 ;
但是这种语法只有Oracle所有。
六、表的删除
表的删除操作指的是数据库对象的删除,既然是删除则使用DROP操作,语法如下:
DROP TABLE 表名称 ;
这样进行删除之后所以表没了但是会留下一些东西
七、Oracle 10g+新特性闪回技术
在Oracle 10g直呼,为了预防用户的误删表操作,专门提供了回收站的功能。用户删除的表被删除之后默认会被放入回收站之中,用户可以通过回收站复原被误删的表。此技术被称为:闪回技术(FlashBack)
范例:查看回收站;
SHOW RECYCLBIN ;
此时可以查看到所有被删除的表都在回收站之中保存,然后就可以使用如下语句进行回复:
FLASHBACK TABLE 表名称 TO BEFORE DROP ;
当然也可以直接删除回收站中的一些表数据,语法如下:
PURGE TABLE 表名称 ;
也可以清空回收站:
PURGE RECYCLEBIN ;
也可以再进行删除操作的同时,不让其进入回收站:
DROP TABLE 表名称 PURGE ;
注:闪回技术只有Oracle 10g以上的版本才会有。
问题:问,在回收站之中存在一张tab表,然后又建立了一张tab表,此时回复回收站的tab表,会怎么样?
答:这个时候是无法回复的
八、修改表的结构
如果一张建立好的数据表,发现其初期的结构不能满足于后期的使用要求,则可以使用修改操作,而表的修改操作实际上就是数据库对象的修改操作,所以要使用ALTER指令完成,而不是UPDATE,例如:
CREATE TABLE member(
mid NUMBER(3) ,
name VARCHAR2(50) DEFAULT'无名氏')
插入数据:
INSERT INTO member(mid,name) VALUES( 1,'张三') ;
INSERT INTO member(mid,name) VALUES( 2,'') ;
INSERT INTO member(mid,name) VALUES( 3,'王二') ;
SELECT * FROM member ;
此时再为表添加字段,语法:
ALTER TABLE 表名称 ADD (列名称 数据类型 [DEFAULT 默认值],
列名称 数据类型 [DEFAULT 默认值],.......) ;
为member添加字段:
ALTER TABLE member ADD(
age NUMBER(3) ,
birthday DATE DEFAULT SYSDATE
) ;
如果增加的数据列没有默认值,则所有已有的数据列的内容都是null,而如果增加了列指定了DEFAULT默认值的话,则所有的已有的数据列都是设置的默认值。
也可以修改已有的表结构,此时的语法如下:
ALTER TABLE 表名称 MODIFY(列名称 数据类型 [DEFAULT 默认值],
列名称 数据类型 [DEFAULT 默认值],.......) ;
ALTER TABLE member MDIFY(name VARCHAR2(10) DEFUALT ‘无名氏’) ;
虽然在SQL语法中以及Oracle数据库中,都给出了修改表结构的操作,但是这种操作尽量少用,从大型数据库来讲,世界上性能最高的数据库是IBM的DB2,但是DB2本身存在一个平台的限制问题,所以如果说跨平台的数据库则Oracle数据库的性能是最高的。
IBM DB2数据库中是不允许修改表结构的,即:表建立之后就不能在修改,所以在开发之中尽量少去修改表的结构。
补充:关于软件项目的开发基本流程
1、获取需求 --> 软件销售,在软件销售分两类:1、朋友介绍2、竞标
2、需求分析 --> 根据客户的要求、条件,做出需求分析
3、业务分析,数据库设计及接口设计 --> 数据表设计
4、编码实现 --> 业务的梳理过程及人际关系的建立
5、软件测试 --> 理论上应有专门的测试人员
6、软件培训及项目的维护
维护常常是销售和技术员的矛盾所在
数据表的建立前提 = 软件的业务分析
九、思考题(面试题)
现要求建立一张nation表,表中有一个name字段,里面保存四条记录:中国、美国、巴西、荷兰,要求通过查询实现如下的效果:
u 中国 美国
u 中国 巴西
u 中国 荷兰
u 美国 巴西
u 美国 中国
u 美国 荷兰
依次类推,现在要求建立新的表并完成此查询的操作。
本题目的主要目的并不是在于查询的编写,而是在于规范化数据库创建脚本的格式,编写数据库创建脚本的格式如下:
1、脚本文件的命名必须是”*.sql” ;
2、先写删除语句,产出相应的数据表;
3、编写创建表的语句
4、增加测试数据
5、提交事务
--1、删除表
DROP TABLE nation PURGE ;
--2、创建表
CREATE TABLE nation(name VARCHAR2(10)) ;
--3、添加测试数据
INSERT INTO nation(name) VALUES('中国') ;
INSERT INTO nation(name) VALUES('美国') ;
INSERT INTO nation(name) VALUES('荷兰') ;
INSERT INTO nation(name) VALUES('巴西') ;
--4、事务的提交
COMMIT ;
本程序可通过笛卡尔积完成,属于自身关联;
--5、查询结果
SELECT n1.name,n2.name
FROM nation n1,nation n2
WHERE n1.name<>n2.name ;
而且以后如果面试之中,如果出现复杂查询,一定要尽可能的把脚本写全了!
约束
表虽然建立完了,但是表中的数据是否合法并不能检查,而如果想要针对表中的数据进行一些过滤的话,可以通过约束来完成,约束的主要功能是保证表中的数据合法性,而按照约束的分类,一共有5种约束:非空约束、唯一约束、主键约束、检查约束、外键约束。
非空约束(NOT NULL):NK
当数据表中的某个字段上的内容不希望被设置成null值的话,可以采用NOT NULL进行指定,如下:
CREATE TABLE member(
name VARVHAR2(50) NOT NULL ,
age NUMBER(3)
)
因为此时存在NOT NULL的约束,所以此时插入数据,name值不能为空,但是可以插入空格值
唯一约束(UNIQUE):UK
唯一约束是指每一个列上的数据是不允许重复的,例如:phone号码每个用户都应该是不同的,可以用唯一约束完成。
CREATE TABLE member(
name VARCHAR2(10) NOT NULL:,
phone NUMBER(20) UNIQUE
) ;
此时Phone值便不能重复,如果插入重复数据则会提示:违反唯一约束。
但是这个提示与之前的非空约束相比并不是完善,只给出了一个代号而已,这是因为在定义约束的时候并没有为约束指定一个名字,所以由系统默认分配了,而且约束的名字建议的格式为:“约束类型_字段”,例如:UK_phone,指定约束名称使用CONSTRAINT完成。
CREATE TABLE member(
name VARCHAR2(10) NOT NULL ,
phone NNUMBER(20) ,
CONSTRAINT UK_Phone UNIQUE(phone)
) ;
主键约束(Primary Key):PK
主键约束 = 非空约束 + 唯一约束,在之前设置为一约束的时候,可以设置为null,而在社会自了主键约束之后则不能为空。而主键一般作为数据的唯一一个标记出现,例如人员的ID。
范例:建立主键约束
CREATE TABLE member(
mid NUMBER PRIMARY KEY,
name VARCHAR2() NOT NULL
) ;
范例:增加正确的数据
INSERT INTO member(mid,name) VALUES(1,’张三’) ;
范例:错误的数据
1、主键设置为null
INSERT INTO member(mid,name) VALUES(null,’张三’) ;
2、主键重复
INSERT INTO member(mid,name) VALUES(1,’李四’) ;
从正常的开发而言,一张表只设置一个主键,但是在Oracle中可以设置多个主键,称为符合主键,在符合主键的使用中,只有两个字段的内容都一样的时候才算是数据重复。插入错误的时候会报错,错误信息为:违反唯一约束条件。(复合主键什么的知道就可以了)
检查约束(Check):CK
检查约束是为表中的数据增加一些过滤条件,例如:
|-设置年龄的时候:0-200
|-设置性别的时候应该是:男、女
范例:设置检查约束
DROP TABLE member PURGE ;
CREATE TABLE member(
mid NUMBER ,
name VARCHAR2(50) NOT NULL,
sex VARCHAR2(10) NOT NULL ,
age NUMBER(3) ,
CONSTRAINT pk_mid PRIMARY KEY (mid) ,
CONSTRAINT ck_sex CHECK (sex IN('男','女')),
CONSTRAINT ck_age CHECK (sex BETWEEN 0 AND 200)
) ;
检查约束就是对输入数据进行的一个过滤。
主-外键约束(核心难点)
之前的四种约束都是在一张表中进行的,而主-外键表是在两张表中进行的,这两张表之间存在父子关系,即:子表中的某个字段的取值范围由父表决定。
例如:现在要求表示出一种关系,每一个人拥有多本书,所以要创建两张表,一张是人员呢表,一张是书的表:
DROP TABLE member PURGE ;
DROP TABLE book PURGE ;
CREATE TABLE member(
mid NUMBER ,
name VARCHAR2(10) NOT NULL ,
CONSTRAINT pk_mid PRIMARY KEY(mid)
) ;
CREATE TABLE book(
bid NUMBER ,
title VARCHAR2(20) NOT NULL ,
mid NUMBER ,
CONSTRAINT pk_bid PRIMARY KEY(bid)
) ;
此时创建了两张表,但是两者之间并不存在主-外键约束,然后插入数据:
INSERT INTO member(mid,name) VALUES(1,'张三') ;
INSERT INTO member(mid,name) VALUES(2,'李四') ;
INSERT INTO book(bid,title,mid) VALUES(101,'JAVA开发',1) ;
INSERT INTO book(bid,title,mid) VALUES(102,'AJAX开发',1) ;
INSERT INTO book(bid,title,mid) VALUES(103,'EJB开发开发',1) ;
INSERT INTO book(bid,title,mid) VALUES(104,'Android开发开发',2) ;
INSERT INTO book(bid,title,mid) VALUES(105,'PhotoShop手册',2) ;
然后验证这些数据是否有意义:
范例:统计每个人员所拥有的书的数量
SELECT m.mid,COUNT(b.bid)
FROM member m,book b
WHERE m.mid=b.mid
GROUP by m.mid;
范例:查询出每个人员的编号,姓名,拥有的书名
SELECT m.mid,m.name,b.bid,b.title
FROM member m,book b
WHERE m.mid=b.mid ;
以上的查询可得出正确的结果表明,book.mid字段和member.mid字段是相关联的,但是由于没有设置约束条件,可以向book表中添加如下数据:
INSERT INTO book(bid,title,mid) VALUES(107,'反正都是错的',4) ;
INSERT INTO book(bid,title,mid) VALUES(108,'反正也是错的',6) ;
此时添加了两条数据。,但是由于其对应的mid是不存在的所以这两条数据是没有任何意义的,想要解决这个问题,就必须要靠主-外键约束来解决。
让book.mid字段的取值由member.mid决定,如果member.mid数据真实存在,则表示可以更新:
DROP TABLE member PURGE ;
DROP TABLE book PURGE ;
CREATE TABLE member(
mid NUMBER ,
name VARCHAR2(10) NOT NULL ,
CONSTRAINT pk_mid PRIMARY KEY(mid)
) ;
CREATE TABLE book(
bid NUMBER ,
title VARCHAR2(20) NOT NULL ,
mid NUMBER ,
CONSTRAINT pk_bid PRIMARY KEY(bid) ,
CONSTRAINT fk_mid FOREIGN KEY(mid) REFERENCES member(mid)
) ;
此时在插入数据:
INSERT INTO member(mid,name) VALUES(1,'张三') ;
INSERT INTO member(mid,name) VALUES(2,'李四') ;
INSERT INTO book(bid,title,mid) VALUES(101,'JAVA开发',1) ;
INSERT INTO book(bid,title,mid) VALUES(102,'AJAX开发',1) ;
INSERT INTO book(bid,title,mid) VALUES(103,'EJB开发开发',1) ;
INSERT INTO book(bid,title,mid) VALUES(104,'Android开发开发',2) ;
INSERT INTO book(bid,title,mid) VALUES(105,'PhotoShop手册',2) ;
然后插入错误数据:
INSERT INTO book(bid,title,mid) VALUES(107,'反正都是错的',4) ;
INSERT INTO book(bid,title,mid) VALUES(108,'反正也是错的',6) ;
这时就会报错:
使用外键的最大好处就是控制了子表中某些数据的取值问题,但是同时也带来了其他问题:
1、删除数据时,如果主表中的数据有对应的子表数据,则无法删除 ;
范例:删除member中mid为1的数据:
DELETE FROM member WHERE mid=1 ;
这时会报错误信息:违反完整约束条件-已找到了子记录
此时,只能先删除子表记录,之后在删除父表记录
DELETE FROM book WHERE mid=1 ;
DELETE FROM member WHERE mid=1 ;
但是这种操作极为不便,可以设置级联删除功能,使得在删除主表记录之后子表中对应的数据也会被删除:
DROP TABLE member PURGE ;
DROP TABLE book PURGE ;
CREATE TABLE member(
mid NUMBER ,
name VARCHAR2(10) NOT NULL ,
CONSTRAINT pk_mid PRIMARY KEY(mid)
) ;
CREATE TABLE book(
bid NUMBER ,
title VARCHAR2(20) NOT NULL ,
mid NUMBER ,
CONSTRAINT pk_bid PRIMARY KEY(bid) ,
CONSTRAINT fk_mid FOREIGN KEY(mid) REFERENCES member(mid) ON DELETE CASCADE
) ;
此时删除主表数据时子表中的数据也会随之被删除。
2、删除数据之后,让子表中的对应数据设置为null ;
当主表中的数据删除之后,对应的表中的数据相关项也希望将其设置为null,而不是删除:
DROP TABLE member PURGE ;
DROP TABLE book PURGE ;
CREATE TABLE member(
mid NUMBER ,
name VARCHAR2(10) NOT NULL ,
CONSTRAINT pk_mid PRIMARY KEY(mid)
) ;
CREATE TABLE book(
bid NUMBER ,
title VARCHAR2(20) NOT NULL ,
mid NUMBER ,
CONSTRAINT pk_bid PRIMARY KEY(bid) ,
CONSTRAINT fk_mid FOREIGN KEY(mid) REFERENCES member(mid) ON DELETE SET NULL
) ;
3、删除父表之前必须先删除子表,否则无法删除
但是这样做非常麻烦,因为对于一个陌生的数据库,必须先知道表之间的父子关系,所以Oracle中提供饿了一个强制性删除表操作而不再关心约束条件:
DROP TABLE member CASCADE CONSTRAINT PURGE ;
此时不关心子表是否存在,直接强制性删除父表。但是这种情况下,子表是依旧存在的,不会被一起删除。
合理做法:在以后的数据表进行删除的时候,最好是先删除子表,在删除父表。
修改约束
约束本身也属于数据库对象,那么对其修改则需要使用ALTER语句,修改约束主要指的是以下两种操作:
l 为表增加约束
ALTER TABLE 表名称 ADD CONSTRAINT 约束名称 约束类型(字段) ;
l 删除表中的约束
ALTER TABLE 表名称 DROP CONSTRAINT 约束名称 ;
可以发现,如果要维护约束,需要一个正确的名字名字才行,可是在这约束之中,非空约束作为一种特殊的约束而无法操作。
现有如下的一张数据表:
DROP TABLE membet VASCADE CONSTRAINT PURGE ;
CREATE TABLE member(
mid NUMBER ,
name VARCHAR2(50) NOT NULL ,
age NUMBER(3)
) ;
范例:为上表添加主键约束;
ALTER TABLE member ADD CONSTRAINT pk_mid PRIMARY KEY(mid) ;
但是如果在表中存在非法数据,则无法添加约束条件。必须将相应的非法条件删除之后再进行添加。
注意:约束和表结构一样,最好不要修改。在建表的时候也要注意将约束定义好,以后便不再修改。
查询约束(了解)
在Oracle中所有的对象都是在数据字典之中保存的,所以想要查询有那些约束,只需要查询”user_CONSTRAINT”数据字典即可。
SELECT owner,constraint_name,table_name FROM user_constraint ;
但是这个查询出来的约束只是显示了约束的名字并没有显示在哪个字段上有这个约束,所以此时可以查询另外一个数据字典:”user_coms_columns” ;
建表、更新、查询综合练习(重点)
1、某学生的运动会比赛信息的数据库,保存了如下的表:
a) 运动员sportid(运动员的编号sporterid,运动员姓名name,运动员性别sex,所属系号department)
b) 项目item(项目标号itemid,项目名称itemname,项目比赛地点location)
c) 成绩grade(运动员编号id,项目编号itemid,积分mark)
请使用SQL语句完成如下操作:
a) 建表,并在相应的字段上添加约束
|-定义各个表的主键和外键约束;
|-运动员的姓名和所属系别不能为空 ;
|-积分要么为空,要么为6,4,2,0,分别代表一二三名和其他名次的积分,注意名次可以有并列名次,后面的排名不往前提升,例如,两个并列第一,则没有第二名。
b) 向表内插入指定数据:
运动员(
1001,黎明,男,计算机系
1002,张三,男,数学系
1003,李四,男,计算机系
1004,王二,男,物理系
1005,李娜,女,心理系
1006,孙俪,女,表演系
)
项目(
X001,男子五千米,一操场
X002,男子标枪,一操场
X003,男子跳远,二操场
X004,女子跳高,二操场
X005,女子三千米,三操场
)
积分(
1001, x001, 6
1002, x001, 4
1003, x001, 2
1004, x001, 0
1001, x003, 4
1002, x003, 6
1004, x003, 2
1005, x004 , 6
1006, x004, 4
1003, x002, 6
1005, x002, 4
1006, x002, 2
1001, x002, 0
)
c) 完成如下查询:
i. 求出目前积分最高的系名,及其积分;
ii. 找出在一操场进行比赛的各项目名称及其冠军的姓名
iii. 找出参加了张三所参加的所有项目的其他同学
iv. 经查,张三因为使用饿了违禁药瓶,其成绩都记为0分,请在数据库中做出相应的修改。
v. 经组委会协商,需要删除女子跳高比赛项目
解:
1、创建sql脚本
--删除表
DROP TABLE grade PURGE ;
DROP TABLE sporter PURGE ;
DROP TABLE item PURGE ;
--创建表
CREATE TABLE sporter(
sporterid NUMBER(5),
name VARCHAR2(6) NOT NULL ,
sex VARCHAR2(2) ,
department VARCHAR2(10) NOT NULL ,
CONSTRAINT pk_sporterid PRIMARY KEY(sporterid),
CONSTRAINT ck_sex CHECK(sex IN('男','女'))
) ;
--sportid为唯一主键;name、departname非空;sex设定检查约束只能男、女二选一;
CREATE TABLE item(
itemid VARCHAR2(5) ,
itemname VARCHAR2(10) NOT NULL,
location VARCHAR2(5) NOT NULL,
CONSTRAINT pk_itemid PRIMARY KEY(itemid)
);
--itemid为唯一主键;itemname、localtion非空;
CREATE TABLE grade(
id NUMBER(5) ,
itemid VARCHAR2(5) ,
mark NUMBER(3) ,
CONSTRAINT fk_id FOREIGN KEY(id) REFERENCES sporter(sporterid),
CONSTRAINT fk_itemid FOREIGN KEY(itemid) REFERENCES item(itemid),
CONSTRAINT ck_mark CHECK(mark IN(6,4,2,0))
) ;
--插入数据
INSERT INTO sporter(sporterid,name,sex,department)
VALUES(1001,'黎明','男','计算机系') ;
INSERT INTO sporter(sporterid,name,sex,department)
VALUES(1002,'张三','男','数学系') ;
INSERT INTO sporter(sporterid,name,sex,department)
VALUES(1003,'李四','男','计算机系');
INSERT INTO sporter(sporterid,name,sex,department)
VALUES(1004,'王二','男','物理系') ;
INSERT INTO sporter(sporterid,name,sex,department)
VALUES(1005,'李娜','女','心理系') ;
INSERT INTO sporter(sporterid,name,sex,department)
VALUES(1006,'孙俪','女','表演系') ;
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(id,itemid,mark)
VALUES(1001,'x001',6) ;
INSERT INTO grade(id,itemid,mark)
VALUES(1002,'x001',4) ;
INSERT INTO grade(id,itemid,mark)
VALUES(1003,'x001',2) ;
INSERT INTO grade(id,itemid,mark)
VALUES(1004,'x001',0) ;
INSERT INTO grade(id,itemid,mark)
VALUES(1001,'x003',4) ;
INSERT INTO grade(id,itemid,mark)
VALUES(1002,'x003',6) ;
INSERT INTO grade(id,itemid,mark)
VALUES(1004,'x003',2) ;
INSERT INTO grade(id,itemid,mark)
VALUES(1005,'x004',6) ;
INSERT INTO grade(id,itemid,mark)
VALUES(1006,'x004',4) ;
INSERT INTO grade(id,itemid,mark)
VALUES(1003,'x003',6) ;
INSERT INTO grade(id,itemid,mark)
VALUES(1005,'x002',2) ;
INSERT INTO grade(id,itemid,mark)
VALUES(1006,'x002',2) ;
INSERT INTO grade(id,itemid,mark)
VALUES(1001,'x002',0) ;
2、查询结果
a) 求出目前积分最高的系名,及其积分;
1.确定所需使用的数据表
|-grade表 soprter表
2.确定已知的关联字段
|-sporter(sporterid) =grade(id)
语句:
SELECT * FROM(
SELECT s.department,SUM(mark)
FROM sporter s, grade g
WHERE s.sporterid=g.id
GROUP BY(s.department)
ORDER BY SUM(mark) DESC ;
)WHERE ROWNUM =1 ;
b) 找出在一操场进行比赛的各项目名称即其冠军的姓名
1.确定需要使用的数据表
|-grade表
|-sporter表
|-item表
2.确定已知的关联选项
|-sporter(sporterid)=grade(id)
|-item(itemid)=grade(itemid)
语句:
SELECT s.name,g.itemid,temp.max,i.itemname
FROM sporter s, grade g,(
SELECT i.itemid iid, MAX(g.mark) max
FROM item i, grade g
WHERE i.location='一操场' AND i.itemid=g.itemid
GROUP BY i.itemid) temp,item i
WHERE s.sporterid=g.id AND temp.iid=g.itemid AND g.mark=temp.max
AND g.itemid=i.itemid AND temp.iid=i.itemid ;
c) 找出参加了张三所参加的所有项目的其他同学
1.确定需要用到的数据表
|-sporter表
|-grade表
2.确定已知的关联字段
sporter.soporterid=grade.id
语句:
SELECT name
FROM sporter
WHERE sporterid IN(
SELECT g.id
FROM grade g
WHERE g.itemid IN(
SELECT g.itemid
FROM grade g
WHERE g.id=(
SELECT s.sporterid
FROM sporter s
WHERE s.name='张三'
)
))
AND name<>'张三' ;
d) 经查,张三因为使用了违禁药瓶,其成绩都记为0分,请在数据库中做出相应的修改。
UPDATE grade SET mark =0 WHERE sporterid =(
SELECT sporterid FROM sporter WHERE name=’张三’
) ;
e) 经组委会协商,需要删除女子跳高比赛项目
此项目需要在建表时设置级联删除,添加级联删除在进行删除即可:
CREATE TABLE grade(
id NUMBER(5) ,
itemid VARCHAR2(5) ,
mark NUMBER(3) ,
CONSTRAINT fk_id FOREIGN KEY(id) REFERENCES sporter(sporterid)ON DELETE CASCADE,
CONSTRAINT fk_itemid FOREIGN KEY(itemid) REFERENCES item(itemid),
CONSTRAINT ck_mark CHECK(mark IN(6,4,2,0))
) ;
--在建立grade表设置外键时,同时设置级联删除
DELETE FROM item WHERE itemname=’女子跳高’ ;