在Oracle中之前使用emp、dept等都是系统内建好的表,在SQL语法只能够同样支持了表的创建语句,要想创建表,则应该首先了解Oracle中最常使用到的几种数据类型
1)常用的数据类型
常用过的数据类型,比如之前的emp表中已经出现的三种:NUMBER,VARCHAR2,DATE
No | 数据类型 | 描述 |
1 | VARCHAR2 | 表示的是一个字符串,有长度限制,255 |
2 | NUMBER | NUMBER(n):表示一个正整数,数字的长度是n,可以使用INT NUMBER(m,n):表示一个小数,数字小数长度为n,数字整数长度是m-n,可以使用FLOAT |
3 | DATE | 表示日期的类型,按照标准的日期格式进行存放 |
4 | CLOB | 大对象,表示大文本数据,4G的文本 |
5 | BLOB | 大对象,表示二进制数据,最大可以存放4G,例如存放电影,歌曲,图片 |
2)表的建立
按照标准的语法进行,但是有时会指定约束,此处先给出简单语法
建立表的语法:
CREATE TABLE 表名称(
字段名称 字段类型 [DEFAULT 默认值],
字段名称 字段类型 [DEFAULT 默认值],
…
)
复制表的语法:
CREATE TABLE 表名称 AS (子查询)
如果现在子查询写的是SELECT * FROM emp,表示将表的结构和内容一起复制
如果现在子查询写的是SELECT * FROM emp WHERE 1=2,加入了一个永远不能成立的条件,则表示只复制表的结构,但是不复制表的内容
范例:复制表的结构
CREATE TABLE temp AS SELECT * FROM emp WHERE 1=2;
现在假设要创建一张person表,表中的字段类型及意义如下所示:
No | 字段名称 | 字段类型 | 描述 |
1 | pid | VARCHAR2(18) | 表示人员编号 |
2 | name | VARCHAR2(200) | 表示人员姓名 |
3 | age | NUMBER(3) | 表示人员年龄 |
4 | birthday | DATE | 表示人员生日 |
5 | sex | VARCHAR2(2) | 表示人员性别,默认值是男 |
CREATE TABLE person(
pid VARCHAR2(18) ,
name VARCHAR2(200) ,
age NUMBER(3) ,
birthday DATE,
sex VARCHAR2(2) DEFAULT '男'
) ;
范例:向表中增加数据
INSERT INTO person(pid,name,age,birthday,sex) VALUES ('1', '张三',TO_CHAR('1976-02-13', 'yyyy-mm-dd'), '女') ;
INSERT INTO person(pid,name,age,birthday,sex) VALUES ('2', '李四',TO_CHAR('1986-02-13', 'yyyy-mm-dd')) ;
李四的性别就是默认的男
如果希望在表中增加一个address的字段,则可以将表删除之后再重新创建
3)表的删除
表的删除语法:
DROP TABLE 表名称;
范例:删除表,并重新编写脚本(数据库脚本就是删除表、建立表和增加表内容),加入address
DROP TABLE person PURGE;
CREATE TABLE person(
pid VARCHAR2(18) ,
name VARCHAR2(200) ,
age NUMBER(3) ,
birthday DATE,
sex VARCHAR2(2) DEFAULT '男'
address VARCHAR2(200)
) ;
4)表的修改(了解)
假设表中已经存在大量记录,为了增加列而删除,就很麻烦,所以SQL语法中有专门修改表结构的命令:增加列ALTER、修改列MODIFY
增加列的语法:
ALTER TABLE 表名称 ADD (列的名称 列的类型 DEFAULT 默认值, 列的类型 DEFAULT 默认值,…)
范例:为最早的person中增加address列
ALTER TABLE person ADD(address VARCHAR2(200) DEFAULT '暂无地址');
修改表结构的语法:
ALTER TABLE 表名称 MODIFY(列的名称 类的类型 DEFAULT 默认值, 列的名称 类的类型 DEFAULT 默认值,…);
但是在修改表的结构的时候,如果数据库中对应的字段里面有一个很长的数据,则无法将表的长度缩小,例如现在在name字段中存在一个长度为20的字符串,要将name的长度从200修改为10,则无法实现。
范例:将person中的name字段的长度修改为50,默认值为“无名氏”
ALTER TABLE person MODIFY(name VARCHAR2(50) DEFAULT '无名氏') ;
范例:再向表中插入数据,但是不给名字
INSERT INTO person(pid,age,birthday) VALUES ('1',32,TO_DATE('1986-02-13', 'yyyy-mm-dd')) ;
SELECT * FROM person;
表中身份证编号重复,因为没有增加约束
在一般的数据库程序开发中,很少去修改表结构,这一点的IBM DB2数据库中就没有提供ALTER TABLE指令,所以在建表的时候一定要考虑到位
5)表的重命名(了解)
Oracle独有
在Oracle中提供了RENAME命令,可以为表进行重命名
表的重命名的语法格式:
RENAME 旧的表名称 TO 新的表名称;
范例:将person表 重命名为tperson
RENAME person TO tperson
6)表的截断(了解)
Oracle独有
之前讲解过,如果现在将person表中的一条数据使用DELETE语句删除了,可以rollback回滚,DELETE FROM emp10 WHERE empno=7782;
如果想清空一张表的数据,同时不需要回滚,可以立刻释放资源,就需要使用截断表
截断表的语法:
TRUNCATE TABLE 表名称;
范例:截断tperson表
TRUNCATE TABLE tperson;
表被截断
SELECT * FROM tperson;
未选定行
Rollback;
回退已完成
SELECT * FROM tperson;
未选定行
截断表不是删除表,而是删除表中的数据立刻释放资源,不能进行回滚
在数据库表的开发中,约束是比不可少的支持,使用约束可以更好的保证数据库中数据的完整性。数据库外码的定义、
约束主要分为以下几种:
·主键约束:主键表示唯一的标识,不能为空,如身份证编号
·唯一约束:在一个表中只允许一个主键,其他列如果不希望出现重复值的话,可使用唯一约束
·检查约束:检查一个列的内容是否合法,例如年龄只能在0-150
·非空约束:如姓名字段里面的内容不能为空
·外键约束:在两张表中进行约束
1)主键约束(PRIMARY KEY)
主键约束一般是在ID上使用,而且本身已经默认了内容不能为空,可以在建立表时指定
范例:建立person表,增加主键
CREATE TABLE person(
pid VARCHAR2(18) PRIMARY KEY,
name VARCHAR2(200),
age NUMBER(3),
birthdary DATE,
sex VARCHAR2(2) DEFAULT '男'
) ;
范例:插入数据,其中插入的主键重复
INSERT INTO person(pid,name,age,birthday,sex) VALUES ('1', '张三',30,TO_CHAR('1976-02-13', 'yyyy-mm-dd'), '女') ;
INSERT INTO person(pid,name,age,birthday,sex) VALUES ('1', '李四',30,TO_CHAR('1976-02-13', 'yyyy-mm-dd'), '男') ;
ORA-00001:违反唯一的约束条件(SCOTT.SYS_C005429)
错误信息上给出的是一个编号的形式,实际上此编号表示约束的名称
范例:插入数据,其中插入的主键为空
INSERT INTO person(pid,name,age,birthday,sex) VALUES (null, '王五',30,TO_CHAR('1976-02-13', 'yyyy-mm-dd'), '男') ;
ORA-01400:无法将NULL插入("SCOTT". "PERSON". "PID")
错误信息明确指出在SCOTT用户下的person表的PID字段不允许为空
以上的约束属于系统自动分配好的约束名称,也可以通过CONSTRAINT指定一个约束的名字
范例:将person中的pid指定名称
CREATE TABLE person(
pid VARCHAR2(18),
name VARCHAR2(200),
age NUMBER(3),
birthdary DATE,
sex VARCHAR2(2) DEFAULT '男',
CONSTRAINT pid_pk PRIMARY KEY(pid)
) ;
INSERT INTO person(pid,name,age,birthday,sex) VALUES ('1', '张三',30,TO_CHAR('1976-02-13', 'yyyy-mm-dd'), '女') ;
INSERT INTO person(pid,name,age,birthday,sex) VALUES ('1', '李四',30,TO_CHAR('1976-02-13', 'yyyy-mm-dd'), '男') ;
ORA-00001:违反唯一约束条件(SCOTT.PID_PK)
PERSON_PID_PK,就表示之前在建立数据库表的时候指定的约束名称
2)非空约束(NOT NULL)
使用非空约束,表示一个字段的内容不许为空
CREATE TABLE person(
pid VARCHAR2(18),
name VARCHAR2(200) NOT NULL,
age NUMBER(3) NOT NULL,
birthdary DATE,
sex VARCHAR2(2) DEFAULT '男',
CONSTRAINT pid_pk PRIMARY KEY(pid)
) ;
范例:插入空姓名
INSERT INTO person(pid,name,age,birthday,sex) VALUES ('1',null,30,TO_CHAR('1976-02-13', 'yyyy-mm-dd'), '女') ;
ORA-01400:无法将NULL插入("SCOTT". "PERSON". "NAME")
INSERT INTO person(pid,name,birthday,sex) VALUES ('1','李四',TO_CHAR('1976-02-13', 'yyyy-mm-dd'), '女') ;
ORA-01400:无法将NULL插入("SCOTT". "PERSON". "AGE")
错误提示中已经明确指明了错误发生的字段名称。非空约束不能像主键约束一样指定名称
3)唯一约束(UNIQUE)
表示一个字段中的内容是唯一,列上的内容不允许重复
范例:假设姓名不允许出现重名的情况
CREATE TABLE person(
pid VARCHAR2(18),
name VARCHAR2(200) UNIQUE NOT NULL,
age NUMBER(3) NOT NULL,
birthdary DATE,
sex VARCHAR2(2) DEFAULT '男',
CONSTRAINT pid_pk PRIMARY KEY(pid)
) ;
INSERT INTO person(pid,name,age,birthday,sex) VALUES ('1','张三',30,TO_CHAR('1976-02-13', 'yyyy-mm-dd'), '女') ;
INSERT INTO person(pid,name,age,birthday,sex) VALUES ('2','张三',30,TO_CHAR('1976-02-13', 'yyyy-mm-dd'), '女') ;
ORA-00001:违反唯一约束条件(SCOTT.SYS_C005441)
第一条数据正常插入,第二条数据出现错误,错误信息也是使用自动的约束编号,也可以指定约束的名称,用CONSTRAINT
CREATE TABLE person(
pid VARCHAR2(18),
name VARCHAR2(200) NOT NULL,
age NUMBER(3) NOT NULL,
birthdary DATE,
sex VARCHAR2(2) DEFAULT '男',
CONSTRAINT pid_pk PRIMARY KEY(pid),
CONSTRAINT name_uk UNIQUE(name)
) ;
4)检查约束(CHECK)
使用检查约束来判断一个列中插入的内容是否合法,例如年龄的取值范围、性别的取值范围
CREATE TABLE person(
pid VARCHAR2(18),
name VARCHAR2(200) NOT NULL,
age NUMBER(3) NOT NULL CHECK(age BETWEEN 0 AND 150),
birthdary DATE,
sex VARCHAR2(2) DEFAULT '男' CHECK(sex IN ('男', '女', '中')),
CONSTRAINT pid_pk PRIMARY KEY(pid),
CONSTRAINT name_uk UNIQUE(name)
) ;
范例:插入错误的年龄
INSERT INTO person(pid,name,age,birthday,sex) VALUES ('1', '张三',300,TO_CHAR('1976-02-13', 'yyyy-mm-dd'), '女') ;
ORA-02290:违反检查约束条件(SCOTT.SYS_C005448)
出现错误,错误信息也是使用自动的约束编号,也可以指定约束的名称,用CONSTRAINT
CREATE TABLE person(
pid VARCHAR2(18),
name VARCHAR2(200) NOT NULL,
age NUMBER(3) NOT NULL,
birthdary DATE,
sex VARCHAR2(2) DEFAULT '男',
CONSTRAINT pid_pk PRIMARY KEY(pid),
CONSTRAINT name_uk UNIQUE(name),
CONSTRAINT age_ck(age BETWEEN 0 AND 150),
CONSTRAINT sex_ck(sex IN ('男', '女', '中'))
) ;
5)外键约束
在两张表中进行约束。数据库的定义。
范例:要求完成一个程序,一本书要属于一个人,书本身应该是一张表,一本书必然有一个字段表示属于哪个人
CREATE TABLE person(
pid VARCHAR2(18),
name VARCHAR2(200) NOT NULL,
age NUMBER(3) NOT NULL,
birthdary DATE,
sex VARCHAR2(2) DEFAULT '男',
CONSTRAINT pid_pk PRIMARY KEY(pid),
CONSTRAINT name_uk UNIQUE(name),
CONSTRAINT age_ck(age BETWEEN 0 AND 150),
CONSTRAINT sex_ck(sex IN ('男', '女', '中'))
) ;
CREATE TABLE book(
bid NUMBER PRIMARY KEY NOT NULL,
bname VARCHAR(30),
bprice NUMBER(5,2),
pid VARCHAR2(18)
) ;
创建表后,插入测试数据:
INSERT INTO person(pid,name,age,birthday,sex) VALUES ('1','张三',30,TO_CHAR('1976-02-13', 'yyyy-mm-dd'), '女') ;
INSERT INTO book(bid,bname,bprice,pid) VALUES (1,'JAVA SE',89.9, '1') ;
下面插入以下的数据:
INSERT INTO book(bid,bname,bprice,pid) VALUES (2,'JAVA SE',89.9, '2') ;
按道理,此编号的人员不存在,该条数据肯定不应该插入。此时要解决这样的问题,就要使用主-外键关联,关联之后子表的数据要跟随父表的数据内容。
CREATE TABLE person(
pid VARCHAR2(18),
name VARCHAR2(200) NOT NULL,
age NUMBER(3) NOT NULL,
birthdary DATE,
sex VARCHAR2(2) DEFAULT '男',
CONSTRAINT pid_pk PRIMARY KEY(pid),
CONSTRAINT name_uk UNIQUE(name),
CONSTRAINT age_ck(age BETWEEN 0 AND 150),
CONSTRAINT sex_ck(sex IN ('男', '女', '中'))
) ;
CREATE TABLE book(
bid NUMBER PRIMARY KEY NOT NULL,
bname VARCHAR(30),
bprice NUMBER(5,2),
pid VARCHAR2(18),
CONSTRAINT pid_fk FOREIGN KEY(pid) REFERENCES person(pid)
) ;
INSERT INTO book(bid,bname,bprice,pid) VALUES (2,'JAVA SE',89.9, '2') ;
ORA-02991:违反完整约束条件(SCOTT.PID_FK)- 未找到父项关键字
此时可以保证两张表的数据的完整性,不会出现找不到对应数据的情况
在使用主-外键关联的时候注意:在子表中设定的外键必须是父表的主键;删除是应该先删除子表,再删除父表。
DROP TABLE person;
ORA-02449:表中的唯一/主键被外键引用
级联删除:
1、删除子表的时候,可以强制删除父表,不管约束,但是这种做法一般不使用
语句是:
DROP TABLE book CASCADE CONSTRAINT;
2、若有子表中数据关联了父表,则无法删除父表中的这一项。要想完成删除的话,必须先将父表中的对应数据线删除掉
INSERT INTO person(pid,name,age,birthday,sex) VALUES ('1','张三',30,TO_CHAR('1976-02-13', 'yyyy-mm-dd'), '女') ;
INSERT INTO book(bid,bname,bprice,pid) VALUES (1,'JAVA SE',89.9, '1') ;
DELETE FROM person WHERE pid='1';
ORA-02292:违反完整约束条件(SCOTT.PID_FK)- 已找到自己录
如果希望在表数据删除时,可以自动删除掉漆对应的子表记录,可以使用级联删除
CREATE TABLE person(
pid VARCHAR2(18),
name VARCHAR2(200) NOT NULL,
age NUMBER(3) NOT NULL,
birthdary DATE,
sex VARCHAR2(2) DEFAULT '男',
CONSTRAINT pid_pk PRIMARY KEY(pid),
CONSTRAINT name_uk UNIQUE(name),
CONSTRAINT age_ck(age BETWEEN 0 AND 150),
CONSTRAINT sex_ck(sex IN ('男', '女', '中'))
) ;
CREATE TABLE book(
bid NUMBER PRIMARY KEY NOT NULL,
bname VARCHAR(30),
bprice NUMBER(5,2),
pid VARCHAR2(18),
CONSTRAINT pid_fk FOREIGN KEY(pid) REFERENCES person(pid) ON DELETE CASCADE
) ;
INSERT INTO book(bid,bname,bprice,pid) VALUES (1,'JAVA SE',89.9, '1') ;
DELETE FROM person WHERE pid='1';
已删除1行
SELECT * FROM book;
未选定行
可以看到删除父表的人的时候,相应的子表中的书也被删除了。如果不希望书被删除,而是将其对应父表的字段设置为空,则需要设置ON DELETE SET NULL
6)修改约束
如果一张表已经建立完成,可以为其添加约束
添加约束的语法如下:
ALTER TABLE 表名称 ADD CONSTRAINT 约束名称 约束类型(约束字段);
关于约束类型的命名一定要统一:
PRIMARY KEY:主键字段_PK
UNIQUE:字段_UK
CHECK:字段_CK
FOREIGN KEY:父字段_子字段_FK
范例:为表中增加主键约束
ALTER TABLE book ADD CONSTRAINT book_bid_PK PRIMARY KEY(bid);
既然可以增加约束,就可以删除约束,删除的时候要指定约束的名称。
删除约束的语法如下:
ALTER TABLE 表名称 DROP CONSTRAINT 约束名称;
范例:删除主键
ALTER TABLE book DROP CONSTRAINT book_bid_PK
1)ROWNUM(重点)
表示行号,实际上这是一个列,但是是个伪列,此列可以在每张中出现。
范例:在查询雇员表上,加入ROWNUM
SELECT ROWNUM,empno,ename,job,sal,hiredate FROM emp;
从运行结果上看,ROWNUM采用自动编号的形式出现
范例:只想显示前5条记录
SELECT ROWNUM,empno,ename,job,sal,hiredate
FROM emp WHERE ROWNUM<=5;
范例:查询中间5条记录
SELECT ROWNUM,empno,ename,job,sal,hiredate
FROM emp WHERE ROWNUM BETWEEN 6 AND 10;
ROWNUM没有这样的功能,这个查询只能使用子查询
范例:要求每页显示5条,第二页应该显示6-10条,那么对于数据库操作来讲,它在查询应该先查询出1-10条,之后再在查询的结果中取出后5条
SELECT *
FROM (
SELECT ROWNUM rn,empno,ename,job,sal,hiredate
FROM emp
WHERE ROWNUM<=10) temp
WHERE temp.rn>5;
范例:输出最后的四条
SELECT *
FROM (
SELECT ROWNUM rn,empno,ename,job,sal,hiredate
FROM emp
WHERE ROWNUM<=15) temp
WHERE temp.rn>10;
2)综合练习
有某个学生运动会比赛信息的数据库,保存了如下的表:
·运动员sporter(运动员编号sporterid,运动员姓名name,运动员性别sex,所属系号department)
·项目item(项目编号itemid,项目名称itemname,项目比赛地点location)
·成绩grade(运动员编号sporterid,项目编号itemid,积分mark)
前提:一个运动员可以参加多个项目,一个项目需要由多个运动员完成。
请用SQL语句完成如下功能:
1、建表,并在相应字段上增加约束;
定义各个表的主键外键约束
运动员的姓名和所属系别不能为控制
积分要么为控制,要么为6,4,2,0,分别代表第一、二、三名和其他名词的积分
-- 删除数据表,先删除子表,再删除主表
DROP TABLE grade PURGE;
DROP TABLE sporter PURGE;
DROP TABLE item PURGE;
-- 创建数据表
CREATE TABLE sporter(
sporterid NUMBER(4),
name VARCHAR2(20) NOT NULL,
sex VARCHAR2(10),
department VARCHAR2(20),
CONSTRAINT pk_sporterid PRIMARY KEY(sporterid),
CONSTRAINT ck_sex CHECK(sex IN ('男','女'))
);
CREATE TABLE item(
itemid VARCHAR2(4),
itemname VARCHAR2(20) NOT NULL,
location VARCHAR2(20) NOT NULL,
CONSTRAINT pk_itemid PRIMARY KEY(itemid)
);
CREATE TABLE grade(
sporterid NUMBER(4),
itemid VARCHAR2(4),
mark NUMBER,
CONSTRAINT fk_sporterid FOREIGN KEY(sporterid) REFERENCES sporter(sporterid) ON DELETE CASCADE,
CONSTRAINT fk_itemid FOREIGN KEY(itemid) REFERENCES item(itemid) ON DELETE CASCADE,
CONSTRAINT ck_mark CHECK (mark IN (0,2,4,6))
);
2、向表中插入指定的数据;
运动员(
1001,李明,男,计算机系
1002,张三,男,数学系
1003,李四,男,计算机系
1004,王二,男,物理系
1005,李娜,女,心理系
1006,孙丽,女,数学系)
-- 增加数据
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,'孙丽','女','数学系');
项目(
x001,男子五千米,一操场
x002,男子标枪,一操场
x003,男子跳远,二操场
x004,女子跳高,二操场
x005,女子三千米,三操场)
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','女子三千米','三操场');
积分(
1001,x001,6
1002,x001,4
1003,x001,2
1004,x003,0
1001,x003,4
1002,x003,6
1004,x004,2
1005,x004,6
1006,x004,4
1003,x002,6
1005,x002,4
1006,x002,2
1001,x002,0)
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',0);
INSERT INTO grade(sporterid,itemid,mark) VALUES(1001,'x003',4);
INSERT INTO grade(sporterid,itemid,mark) VALUES(1002,'x003',6);
INSERT INTO grade(sporterid,itemid,mark) VALUES(1004,'x004',2);
INSERT INTO grade(sporterid,itemid,mark) VALUES(1005,'x004',6);
INSERT INTO grade(sporterid,itemid,mark) VALUES(1006,'x004',4);
INSERT INTO grade(sporterid,itemid,mark) VALUES(1003,'x002',6);
INSERT INTO grade(sporterid,itemid,mark) VALUES(1005,'x002',4);
INSERT INTO grade(sporterid,itemid,mark) VALUES(1006,'x002',2);
INSERT INTO grade(sporterid,itemid,mark) VALUES(1001,'x002',0);
-- 提交事务
COMMIT;
3、查询要求
1)求出目前总积分最高的系名,及其积分
第一步:求出所有的姓名及其积分,系名都在sporter表中,所以要与grade表关联
SELECT s.department,SUM(g.mark)
FROM sporter s,grade g
WHERE s.sporterid=g.sporterid
GROUP BY s.department;
第二步:降序排序
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;
第三步(1):使用ROWNUM,使用子查询,求得最高的积分的系
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;
第三步(2):如果不使用ROWNUM,第三步如何做?
SELECT DISTINCT s.department,temp.sum
FROM (
SELECT s.department dept,SUM(g.mark) sum
FROM sporter s,grade g
WHERE s.sporterid=g.sporterid
GROUP BY s.department
ORDER BY sum DESC) temp,sporter s,grade g
WHERE temp.sum=(
SELECT MAX(SUM(g.mark)) sum
FROM sporter s,grade g
WHERE s.sporterid=g.sporterid
GROUP BY s.department)
AND s.department=temp.dept
2)找出在一操场进行比赛的各项目名称及其冠军的姓名
第一步:item表找出项目名称和每个项目的最高成绩
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;
第二步:根据以上结果求最高分
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;
3)找出参加了张三参加过的项目的其他同学的姓名
第一步:找出张三参加过的项目
SELECT i.itemname,s.name
FROM item I,grade g,sporter s
WHERE s.name='张三'
AND i.itemid=g.itemid
AND s.sporterid=g.sporterid;
SELECT g.itemid
FROM grade g,sporter s
WHERE s.name='张三'
AND s.sporterid=g.sporterid;
第二步:找出这些项目的参加的同学的姓名,除了张三
SELECT DISTINCT s.name
FROM sporter s,grade g
WHERE g.itemid IN (
SELECT g.itemid
FROM grade g,sporter s
WHERE e.name='张三'
AND s.sporterid=g.sporterid)
AND s.name<>'张三'
AND s.sporterid=g.sporterid
4)经查张三因为使用了违禁药品,其成绩都记零分,请在数据库中做出相应修改
确定张三的运动员编号,依此编号作为更新的条件
UPDATE grade SET mark=0
WHERE sporterid=(
SELECT sporterid
FROM sporter
WHERE name='张三');
5)经组委会协商,需要删除女子跳高比赛项目
执行delete语句即可
DELETE FROM item WHERE itemname='女子跳高';
版权声明:本站所有资料均为网友推荐收集整理而来,仅供学习和研究交流使用。
工作时间:8:00-18:00
客服电话
电子邮件
admin@qq.com
扫码二维码
获取最新动态