当前位置: 澳门新濠3559 > 数据库 > 正文

例 3  澳门新濠3559:根据表的 STUDENTS,在school数

时间:2019-11-22 23:13来源:数据库
表操作 例1对于表的教学管理数据库中的表STUDENTS,可以定义如下:复制代码 代码如下:CreateTABLESTUDENTSNOTNULLSNAMECHARNOTNULL AGENUMERICBPLACECHAR例2对于表的教学管理数据库中的表ENROLLS,可以定

表操作 例1对于表的教学管理数据库中的表STUDENTS,可以定义如下:复制代码 代码如下:CreateTABLESTUDENTSNOTNULL SNAMECHARNOTNULL AGENUMERIC BPLACECHAR例2对于表的教学管理数据库中的表ENROLLS,可以定义如下:复制代码 代码如下:CreateTABLEENROLLSNOTNULL CNOCHARNOTNULL GRADEINT PRIMARYKEY FOREIGNKEYREFERENCESSTUDENTSREFERENCESCOURSES CHECKor)例3根据表的STUDENTS表,建立一个只包含学号、姓名、年龄的女学生表。复制代码 代码如下:CreateTABLEGIRLASSelectSNO,SNAME,AGE FROMSTUDENTSWhereSEX='女';例4删除教师表TEACHER。 DropTABLETEACHER 例5在教师表中增加住址列。 AlterTABLETEACHERSADD 例6把STUDENTS表中的BPLACE列删除,并且把引用BPLACE列的所有视图和约束也一起删除。 AlterTABLESTUDENTSDropBPLACECASCADE 例7补充定义ENROLLS表的主关键字。 AlterTABLEENROLLSADDPRIMARYKEY 例9建立一个只包括教师号、姓名和年龄的视图FACULTY。 CreateVIEWFACULTYASSelectTNO,TNAME,AGEFROMTEACHERS 例10从学生表、课程表和选课表中产生一个视图GRADE_TABLE,它包括学生姓名、课程名和成绩。 CreateVIEWGRADE_TABLEASSelectSNAME,CNAME,GRADE FROMSTUDENTS,COURSES,ENROLLS WhereSTUDENTS.SNO=ENROLLS.SNOAND COURSES.CNO=ENROLLS.CNO 例11删除视图GRADE_TABLE DropVIEWGRADE_TABLERESTRICT 索引操作 例12在学生表中按学号建立索引。 CreateUNIQUEINDEXSTONSTUDENTS 例13删除按学号所建立的索引。 DropINDEXST 数据库模式操作 例14创建一个简易教学数据库的数据库模式TEACHING_DB,属主为ZHANG。 CreateSCHEMATEACHING_DBAUTHRIZATIONZHANG 例15删除简易教学数据库模式TEACHING_DB。 选用CASCADE,即当删除数据库模式时,则本数据库模式和其下属的基本表、视图、索引等全部被删除。 选用RESTRICT,即本数据库模式下属的基本表、视图、索引等事先已清除,才能删除本数据库模式,否则拒绝删除。 DropSCHEMATEACHING_DBCASCADE 123下一页阅读全文

澳门新濠3559 1 

范式

数据库学习初步
一、数据定义
(一)、基本表操作
先建立school数据库,在school数据库里做以下的操作。www.2cto.com

表操作  

第一范式(1NF)无重复的列

1.建立基本表
1)创建学生表Student,由以下属性组成: 学号SNO(INT 型,主码),姓名SNAME(CHAR型,长度为8,非空唯一),性别SEX(CHAR 型,长度为2),所在系DEPTNO (INT 型)。
 澳门新濠3559 2
2)创建课程表Course,由以下属性组成:课程号CNO(INT 型),课程名CNAME(CHAR型,长度为20,非空),授课教师编号TNO(INT型),学分CREDIT(INT型)。其中(CNO,TNO)为主码。www.2cto.com
 澳门新濠3559 3
3)创建学生选课表SC,由以下属性组成:学号SNO,课程CNO,成绩GRADE。所有属性均为INT 型,其中(SNO,CNO)为主码。
 澳门新濠3559 4
4)创建教师表Teacher,由以下属性组成:教师编号TNO(INT 型,主码),教师姓名TNAME(CHAR型,长度为8,非空),所在系DEPTNO(INT 型)。www.2cto.com

 

        所谓第一范式(1NF)是指数据库表的每一列都是不可分割的基本数据项,同一列中不能有多个值,即实体中的某个属性不能有多个值或者不能有重复的属性。

 澳门新濠3559 5
5)创建系表Dept,由以下属性:系号DEPTNO(INT型,主码),系名DNAME(CHAR 型,长度为20,非空)。
 澳门新濠3559 6
2.修改基本表
1)在Student 表中加入属性SAGE(INT 型)。www.2cto.com
 
2)将Student 表中的属性SAGE 类型改为SMALLINT 型。
 
3.删除基本表
1)在所有操作结束后删除STUDENT 表。
 
2)在所有操作结束后删除COURSE 表。
 
3)在所有操作结束后删除SC 表。
 
4)在所有操作结束后删除TEACHER 表。
 
5)在所有操作结束后删除DEPT 表。
 www.2cto.com
(二)、索引操作
1.建立索引
1)在Student 表上建立关于SNO 的唯一索引stusno。
 
2)在Course 表上建立关于CNO 升序的唯一索引coucno。
 
2.删除索引
1)删除Student 表上的索引stusno。
 
2)删除Course 表上的索引coucno。
 
(三)、视图操作
1.建立视图
在插入数据的Student 基本表上为计算机科学与技术系的学生记录建立一个视图CS_STUDENT。
 
2.删除视图
在操作结束后删除视图CS_STUDENT。
 
二、数据操作
(一)、更新操作
1.插入数据
1)向STUDENT 表插入下列数据:
1001,张天,男,10,20
1002,李兰,女,10,21
1003,陈铭,男,10,21
1004,刘茜,女,20,21
1005,马朝阳,男,20,22 www.2cto.com
 澳门新濠3559 7
2)向COURSE 表插入下列数据:
1,数据结构,101,4
2,数据库,102,4
3,离散数学,103,4
4,C 语言程序设计,101,2
 澳门新濠3559 8
3)向SC 表插入下列数据:
1001,1,80
1001,2,85
1001,3,78
1002,1,78
1002,2,82
1002,3,86
1003,1,92
1003,3,90
1004,1,87
1004,4,90
1005,1,85
1005,4,92
澳门新濠3559 9
 
4)向TEACHER 表插入下列数据:
101,张星,10
102,李珊,10
103,赵天应,10
104,刘田, 20
澳门新濠3559 10
 
5)向DEPT 表插入下列数据:
10,计算机科学与技术
20,信息
澳门新濠3559 11
 
2.修改数据
将张星老师数据结构课的学生成绩全部加2 分

例 1  对于表的教学管理数据库中的表 STUDENTS ,可以定义如下:

第二范式(2NF)属性完全依赖于主键

3.删除数据
删除马朝阳同学的所有选课记录
 
(二)、查询操作
1.单表查询
1)查询所有学生的信息。

   CREATE  TABLE  STUDENTS

        第二范式(2NF)要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性,如果存在,那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体,新实体与原实体之间是一对多的关系。为实现区分通常需要为表加上一个列,以存储各个实例的惟一标识。简而言之,第二范式就是属性完全依赖于主键。

2)查询所有女生的姓名。

   (SNO      NUMERIC (6, 0) NOT NULL

实例:

3)查询成绩在80 到89 之间的所有学生选课记录,查询结果按成绩的降序排列。

   SNAME    CHAR (8) NOT NULL

选课关系 SCI(SNO,CNO,GRADE,CREDIT),其中SNO为学号, CNO为课程号,GRADEGE 为成绩,CREDIT 为学分。 关键字为组合关键字(SNO,CNO)

4)查询各个系的学生人数。
 
2.连接查询
查询信息系年龄在21 岁以下(含21 岁)的女生姓名及其年龄。
 
3.嵌套查询
1)查询修课总学分在10 学分以下的学生姓名。

   AGE      NUMERIC(3,0)

在应用中使用以上关系模式有以下问题:
a.数据冗余,假设同一门课由40个学生选修,学分就 重复40次。
b.更新异常,若调整了某课程的学分,相应的元组CREDIT值都要更新,有可能会出现同一门课学分不同。
c.插入异常,如计划开新课,由于没人选修,没有学号关键字,只能等有人选修才能把课程和学分存入。
d.删除异常,若学生已经结业,从当前数据库删除选修记录。某些门课程新生尚未选修,则此门课程及学分记录无法保存。
原因:

2)查询各门课程取得最高成绩的学生姓名及其成绩。

   SEX      CHAR(2)

非关键字属性CREDIT仅函数依赖于CNO,也就是CREDIT部分依赖组合关键字(SNO,CNO)而不是完全依赖。
解决方法:

3)查询选修了1001 学生选修的全部课程的学生学号。

   BPLACE  CHAR(20)

分成两个关系模式 SC1(SNO,CNO,GRADE),C2(CNO,CREDIT)。新关系包括两个关系模式,它们之间通过SC1中的外关键字CNO相联系,需要时再进行自然联接,恢复了原来的关系

4)查询选修了张星老师开设的全部课程的学生姓名。
 www.2cto.com
出现的问题及解决方案:
1.在创建基本表时是否可以缺省主码?

   PRIMARY KEY(SNO))

第三范式(3NF)属性不依赖于其它非主属性

2.对基本表进行修改,执行ALTER TABLE Student MODIFY Sage SMALLINT 显示执行失败。
 
自己做的答案:
(一)基本表操作
建立基本表
1) 建立Student表
命令行操作:
CREATE TABLE Student
( SNO INT NOT NULL PRIMARY KEY,
  SNAME CHAR(8) NOT NULL,
  SEX CHAR(2) NOT NULL,
  DEPTNO INT NOT NULL)
 www.2cto.com
2) 建立Course表
命令行操作:
CREATE TABLE Course
(
  CNO INT NOT NULL,
  CNAME CHAR(20) NOT NULL,
  TNO INT NOT NULL,
  CREDIT INT NOT NULL,
PRIMARY KEY(CNO, TNO)
)
3) 建立SC表
命令行操作:
CREATE TABLE SC
(
SNO INT NOT NULL,
CNO INT NOT NULL,
GRAGE INT NOT NULL,
 PRIMARY KEY(SNO, CNO)
)www.2cto.com
4) 建立Teacher表
命令行操作:
CREATE TABLE Teacher
( TNO INT NOT NULL,
  TNAME CHAR(8) NOT NULL PRIMARY KEY,
  DEPTNO INT NOT NULL
)
5) 建立Depth表
命令行操作:
 CREATE TABLE Dept
(
  DEPTNO INT NOT NULL PRIMARY KEY,
  DNAME CHAR(20) NOT NULL
)
2.修改基本表
1)在Student 表中加入属性SAGE(INT 型)。
命令行操作:
ALTER TABLE Student ADD SAGE INT;
2)将Student 表中的属性SAGE 类型改为SMALLINT 型。
 命令行操作:
ALTER TABLE Student alter column SAGE SMALLINT;
 
3.删除基本表
1)在所有操作结束后删除STUDENT表。
命令行操作:
DROP TABLE Student;
2)在所有操作结束后删除COURSE表。
DROP TABLE Course;
3)在所有操作结束后删除SC 表。
DROP TABLE SC;
4)在所有操作结束后删除TEACHER表。
DROP TABLE Teacher;
5)在所有操作结束后删除DEPT表。
DROP TABLE Dept;www.2cto.com
 
(二)、索引操作
1.建立索引
1)在Student 表上建立关于SNO 的唯一索引stusno。
CREATE UNIQUE INDEX stusno ON Student(SNO);
2)在Course 表上建立关于CNO 升序的唯一索引coucno。
CREATE UNIQUE INDEX coucno ON Course(CNO);
 
2.删除索引
1)删除Student 表上的索引stusno。
DROP INDEX Student.stusno;
2)删除Course 表上的索引coucno。
DROP INDEX Course.coucno;
 
(三)、视图操作
1.建立视图
在插入数据的Student 基本表上为计算机科学与技术系的学生记录建立一个视图CS_STUDENT。
 
2.删除视图
在操作结束后删除视图CS_STUDENT。
 
二、数据操作
(一)、更新操作
1.插入数据
1)向STUDENT 表插入下列数据:
1001,张天,男,10,20
1002,李兰,女,10,21
1003,陈铭,男,10,21
1004,刘茜,女,20,21
1005,马朝阳,男,20,22
命令行操作:
INSERT INTO Student
VALUES(1001,'张天','男',10,20);
INSERT INTO Student
VALUES(1002,'李兰','女',10,21);
INSERT INTO Student
VALUES(1003,'陈铭','男',10,21);
INSERT INTO Student
VALUES(1004,'刘茜','女',20,21);
INSERT INTO Student
VALUES(1005,'马朝阳','男',20,22);
www.2cto.com
2)向COURSE 表插入下列数据:
1,数据结构,101,4
2,数据库,102,4
3,离散数学,103,4
4,C 语言程序设计,101,2
命令行操作:
INSERT INTO Course
VALUES(1,'数据结构',101,4);
INSERT INTO Course
VALUES(2,'数据库',102,4);
INSERT INTO Course
VALUES(3,'离散数学',103,4);
INSERT INTO Course
VALUES(4,'C 语言程序设计',101,2);
 
3)向SC 表插入下列数据:
1001,1,80
1001,2,85
1001,3,78
1002,1,78
1002,2,82
1002,3,86
1003,1,92
1003,3,90
1004,1,87
1004,4,90
1005,1,85
1005,4,92
命令行操作:
INSERT INTO SC(SNO, CNO, GRAGE)
SELECT 1001,1,80 union
SELECT 1001,2,85 union
SELECT 1001,3,78 union
SELECT 1002,1,78 union
SELECT 1002,2,82 union
SELECT 1002,3,86 union
SELECT 1003,1,92 union
SELECT 1003,3,90 union
SELECT 1004,1,87 union
SELECT 1004,4,90 union
SELECT 1005,1,85 union
SELECT 1005,4,92

例 2  对于表的教学管理数据库中的表 ENROLLS ,可以定义如下:

实例:

4)向TEACHER 表插入下列数据:
101,张星,10
102,李珊,10
103,赵天应,10
104,刘田, 20
命令行操作:
INSERT INTO Teacher(TNO, TNAME, DEPTNO)
SELECT 101,'张星',10 union
SELECT 102,'李珊',10 union
SELECT 103,'赵天应',10 union
SELECT 104,'刘田', 20
 
5)向DEPT 表插入下列数据:
10,计算机科学与技术
20,信息
命令行操作:
INSERT INTO Dept(DEPTNO, DNAME)
SELECT 10,'计算机科学与技术' union
SELECT 20,'信息'
 
2.修改数据
将张星老师数据结构课的学生成绩全部加2 分
 
3.删除数据
删除马朝阳同学的所有选课记录
 
(二)、查询操作
1.单表查询
1)查询所有学生的信息。
SELECT SNO, SNAME, SEX, DEPTNO, SAGE
FROM Student;
 
2)查询所有女生的姓名。
SELECT SNAME
FROM Student
WHERE SEX IN('女');
 
3)查询成绩在80 到89 之间的所有学生选课记录,查询结果按成绩的降序排列。
SELECT CNO
FROM SC
WHERE GRAGE BETWEEN 80 AND 89;
4)查询各个系的学生人数。
SELECT DISTINCT DEPTNO
FROM Dept;
2.连接查询
查询信息系年龄在21 岁以下(含21 岁)的女生姓名及其年龄。
SELECT SNAME, SAGE
FROM Student, Dept
WHERE DNAME = '信息' AND SAGE <= 21 AND SEX = '女';
3.嵌套查询
1)查询修课总学分在10 学分以下的学生姓名。
SELECT SNAME
FROM Student, Course
WHERE CREDIT < 10;
2)查询各门课程取得最高成绩的学生姓名及其成绩。
SELECT SNAME ,GRAGE,
FROM Student, SC
WHERE Student.SNO = SC.SNO AND GRAGE IN
 (SELECT MAX(GRAGE)
 FROM SC
 GROUP BY CNO);
3)查询选修了1001 学生选修的全部课程的学生学号。
 
4)查询选修了张星老师开设的全部课程的学生姓名。
 
出现的问题及解决方案:
1.在创建基本表时是否可以缺省主码?
解:可以
2.对基本表进行修改,执行ALTER TABLE Student MODIFY Sage SMALLINT 显示执行失败。
解:将MODIFY更改为alter column就可以解决。  

        CREATE  TABLE  ENROLLS

学生表Student(学号,姓名, 年龄,性别,系别,系办地址、系办电话),关键字为单一关键字"学号"

        (SNO      NUMERIC(6,0)  NOT NULL

因为存在如下决定关系:
(学号)→ (姓名, 年龄,性别,系别,系办地址、系办电话)
但是还存在下面的决定关系
(学号) → (所在学院)→(学院地点, 学院电话)
即存在非关键字段"学院地点"、"学院电话"对关键字段"学号"的传递函数依赖,这样会存在数据冗余, 根据第三范式把学生关系表分为如下两个表就可以满足第三范式了:

        CNO     CHAR(4)  NOT NULL

学生:(学号, 姓名, 年龄, 性别,系别);
系别:(系别, 系办地址、系办电话)。

        GRADE   INT

索引

        PRIMARY KEY(SNO,CNO)

        索引是一种特殊的文件,包含了对数据表中所有记录的引用指针,如果想知道对某个特定话题的讨论都在书中的哪些地方出现过,索引可以让读者无需把书从头到尾翻一遍就能把他们查出来。当然,索引也是有缺陷的,因为每修改一个数据索引就要刷新一次,(有些sql命令里面有一个delay_key_write,可以暂停插入数据时对索引的刷新),插入效率会受影响;另外一个索引会在硬盘上占据相当大的空间。

        FOREIGN KEY(SNO) REFERENCES STUDENTS(SNO)

普通索引:唯一任务就是加快对数据的访问

        FOREIGN KEY(CNO) REFERENCES COURSES(CNO)

唯一索引:普通索引是允许数据重复的,如果确定了某列数据不会重复,则可以创建唯一索引,这样有两个好处,索引更有效率;插入新数据的时候如果重复了,mysql会拒绝插入新纪录

        CHECK ((GRADE IS NULL) OR (GRADE BETWEEN 0 AND 100)))

主索引:这个就是为主键创建的索引,应该是默认都要创建的

例 3  根据表的 STUDENTS 表,建立一个只包含学号、姓名、年龄的女学生表。

全文索引:文本字段上的普通索引只能加快对出现在字段内最前面的字符串进行的检索操作,如果字段里存放的是由几个或者多个单词构成的大段文字,普通索引就不行了,这种场合用全文索引比较合适

        CREATE TABLE GIRL

        只有当数据库里已经有足够多的数据时,测试索引才有实际参考价值,因为不管有没有索引,数据库在执行第一条查询命令之后就被加载到内存中了,后面查起来都非常快,因此只有当数据库里记录超过1000条、数据总量也超过内存的总量时,测试结果才有意义。

        AS SELECT SNO, SNAME, AGE

视图

        FROM STUDENTS

        视图使得人们可以为一个或者多个数据表定义一个特殊的表现形式。视图在表现行为上与表没有差别,可以select查询,也可以用insert,update,delete来修改数据。

        WHERE SEX=' 女 ';

        一般使用视图的理由有两个,一个是安全,例如一个表中包含了员工的个人资料,那像电话姓名这些是所有人都可以查询的,但是像薪水这些东西就只有特定的人能查询了,因此最好的办法就是将所有人都可以访问的数据部分创建为一个视图,供别人查询。另一个是方便,视图使用起来很方便,不用输入复制的命令

 

      在视图里修改数据:能不能修改某个视图中的数据取决于视图的select命令,可刷新的视图需要满足以下几个条件:

例 4  删除教师表 TEACHER 。

(1)当初定义的select中不得包含group by、distinct、limit、union或having等子命令

        DROP  TABLE  TEACHER

(2)如果视图中的数据来自一个以上的表,那它总是不可刷新的

例 5  在教师表中增加住址列。

(3)视图中应包含主键索引,唯一索引,外间约束条件所涉及到的所有数据列

       ALTER TABLE TEACHERS

事务

       ADD (ADDR CHAR(50))

        为什么要使用事务操作? 有助于提高数据库系统的运行效率和安全性,举个例子,要从一个人的账户装100美元到另一个的账户中,需要两步操作,更新第一个人的账户余额,更新第二个人的账户余额,如果第一个操作执行完以后系统出了问题,那后果就严重了。如果有事务的话,如果成功了,那就最好,如果在执行到一半的时候失败了,那执行了的部分也会被撤销。事务也可以保证同一批数据不会被两位用户同时修改,并发控制。

例 6  把 STUDENTS 表中的 BPLACE 列删除,并且把引用 BPLACE 列的所有视图和约束也一起删除。

ACID原则:

        ALTER TABLE STUDENTS

原子性:这意味事务就想原子那样是不可分割的

        DROP BPLACE CASCADE

稳定性:这意味着事务执行完毕后数据库必须处于一个稳定的状态

例 7  补充定义 ENROLLS 表的主关键字。

隔离性:多个事务可以独立运行,不受彼此干扰,如果有一个事务提交了,所有受到影响的事务将自动撤销并返回错误信息,这样便于用户进行操作处理

       ALTER TABLE ENROLLS

可靠性:事务一定能够经受住软、硬件或者其他意外故障,故障消除后能够继续执行

       ADD PRIMARY KEY (SNO,CNO) ;

       强调一下:只有InnoDB数据表支持数据,MyISAM数据表不支持事务

 

事务与锁定:

视图操作(虚表)

共享锁:select ...lock in share mode  ,这确保了事务过程中读取的数据记录不会被其他用户正在修改和删除操作

 

排它锁:select .... for update ,排它锁不禁止其他客户使用普通的select来读取锁定的数据记录,但是会禁止其他共享锁和排它锁

例 9  建立一个只包括教师号、姓名和年龄的视图 FACULTY 。 ( 在视图定义中不能包含 ORDER BY 子句 )

存储过程(stored procedure)

        CREATE VIEW FACULTY

        由mysql服务器直接存储和执行的定制过程或者函数。几个特点:更快的速度、避免代码冗余、提高数据库的安全性

        AS SELECT TNO, TNAME, AGE

触发器

        FROM TEACHERS

       在insert、update、delete命令之前或者之后对sql命令或者sp的自动调用。mysql对触发器有许多限制,比如:

例 10  从学生表、课程表和选课表中产生一个视图 GRADE_TABLE , 它包括学生姓名、课程名和成绩。

(1)触发器代码里无法访问任何数据表,连触发器为之定义的那个数据表也不能访问,类似sp函数的情况,select、insert、update等命令不允许在触发器代码里出现

        CREATE VIEW GRADE_TABLE

(2)触发器不能用call命令调用一个sp

        AS SELECT  SNAME,CNAME,GRADE

(3)触发器中不能调用事务命令

        FROM  STUDENTS,COURSES,ENROLLS

Mysql服务器优化内存管理:

        WHERE  STUDENTS.SNO = ENROLLS.SNO AND

       调整/etc/my.cnf or  my.ini文件中的一些参数:

        COURSES.CNO=ENROLLS.CNO

key_buffer_size(默认8MB) 索引缓冲区长度,这个越大,对数据表里有索引的数据列访问速度就会越快

例 11  删除视图 GRADE_TABLE

table_cache(默认64B)可以同时打开数据表的个数

        DROP VIEW GRADE_TABLE RESTRICT

sort_buffer(默认为2MB),排序缓冲区长度,如果没有索引,带order by或者group bye字句的select命令将使用这个缓冲区对数据进行排序,如果这个太小,就需要用到一个临时文件,这可就慢多了。

 

        另外还有几个,这里就不具体再说明了。

索引操作

 

例 12  在学生表中按学号建立索引。

        CREATE  UNIQUE  INDEX  ST

        ON STUDENTS (SNO,ASC)

例 13  删除按学号所建立的索引。

        DROP INDEX ST

 

数据库模式操作

 

例 14  创建一个简易教学数据库的数据库模式   TEACHING_DB ,属主为 ZHANG 。

        CREATE SCHEMA TEACHING_DB  AUTHRIZATION  ZHANG

例 15  删除简易教学数据库模式 TEACHING_DB 。(( 1 )选用 CASCADE ,即当删除数据库模式时,则本数据库模式和其下属的基本表、视图、索引等全部被删除。( 2 )选用 RESTRICT ,即本数据库模式下属的基本表、视图、索引等事先已清除,才能删除本数据库模式,否则拒绝删除。)

        DROP SCHEMA TEACHING_DB CASCADE

单表操作

 

例 16  找出 3 个学分的课程号和课程名。

         SELECT CNO, CNAME

         FROM   COURSES

         WHERE   CREDIT = 3

例 17  查询年龄大于 22 岁的学生情况。

         SELECT  *

         FROM   STUDENTS

         WHERE  AGE > 22

例 18   找出籍贯为河北的男生的姓名和年龄。

         SELECT SNAME, AGE

         FROM   STUDENTS

         WHERE   BPLACE = ' 河北 '  AND  SEX = ' 男 '

例 19  找出年龄在 20 ~ 23 岁之间的学生的学号、姓名和年龄,并按年龄升序排序。 (ASC (升序)或 DESC (降序)声明排序的方式,缺省为升序。 )

         SELECT SNO, SNAME, AGE

         FROM   STUDENTS

         WHERE  AGE BETWEEN 20 AND 23

         ORDER  BY  AGE

例 20  找出年龄小于 23 岁、籍贯是湖南或湖北的学生的姓名和性别。(条件比较运算符=、< 和逻辑运算符 AND (与),此外还可以使用的运算符有:>(大于)、>=(大于等于)、<=(小于等于)、<>(不等于)、 NOT (非)、 OR (或)等。

谓词 LIKE 只能与字符串联用,常常是 “ <列名>   LIKE  pattern” 的格式。特殊字符 “_” 和 “%” 作为通配符。

谓词 IN 表示指定的属性应与后面的集合(括号中的值集或某个查询子句的结果)中的某个值相匹配,实际上是一系列的 OR (或)的缩写。谓词 NOT IN 表示指定的属性不与后面的集合中的某个值相匹配。

谓词 BETWEEN 是 “ 包含于 … 之中 ” 的意思。)

        SELECT SNAME, SEX

        FROM   STUDENTS

        WHERE  AGE < 23  AND  BPLACE  LIKE' 湖% '

        或

        SELECT SNAME, SEX

        FROM   STUDENTS

        WHERE  AGE < 23  AND  BPLACE  IN  ( ' 湖南 ' , ' 湖北 ' )

例 22  找出学生表中籍贯是空值的学生的姓名和性别。(在 SQL 中不能使用条件:<列名>= NULL 。在 SQL 中只有一个特殊的查询条件允许查询 NULL 值:)

       SELECT SNAME, SEX

       FROM   STUDENTS

       WHERE  BPLACE IS NULL

 

多表操作

 

例 23  找出成绩为 95 分的学生的姓名。(子查询)

        SELECT SNAME

        FROM   STUDENTS

        WHERE   SNO =

              (SELECT SNO

               FROM   ENROLLS

               WHERE  GRADE = 95)

例 24  找出成绩在 90 分以上的学生的姓名。

        SELECT  SNAME

        FROM   STUDENTS

        WHERE  SNO IN

                (SELECT SNO

                FROM ENROLLS

                WHERE GRADE > 90)

        或

        SELECT  SNAME

        FROM   STUDENTS

        WHERE  SNO = ANY

                (SELECT SNO

                FROM ENROLLS

                WHERE GRADE > 90)

例 25  查询全部学生的学生名和所学课程号及成绩。(连接查询)

        SELECT  SNAME, CNO, GRADE

        FROM   STUDENTS, ENROLLS

        WHERE  STUDENTS.SNO = ENROLLS.SNO

例 26  找出籍贯为山西或河北,成绩为 90 分以上的学生的姓名、籍贯和成绩。(当构造多表连接查询命令时,必须遵循两条规则。第一,连接条件数正好比表数少 1 (若有三个表,就有两个连接条件 ) ;第二,若一个表中的主关键字是由多个列组成,则对此主关键字中的每一个列都要有一个连接条件(也有少数例外情况))

        SELECT  SNAME, BPLACE, GRADE

        FROM   STUDENTS, ENROLLS

        WHERE  BPLACE IN (‘ 山西 ’ , ‘ 河北 ’)  AND  GRADE >= 90  AND   STUDENTS.SNO=ENROLLS.SNO

例 28  查出课程成绩在 80 分以上的女学生的姓名、课程名和成绩。( FROM 子句中的子查询)

        SELECT  SNAME,CNAME, GRADE

        FROM   (SELECT SNAME, CNAME , GRADE

                        FROM  STUDENTS, ENROLLS,COURSES

                        WHERE  SEX = ' 女 ')

        AS TEMP (SNAME, CNAME,GRADE)

        WHERE  GRADE > 80

 

 

 

 

表达式与函数的使用

 

例 29  查询各课程的学时数。(算术表达式由算术运算符+、-、 * 、/与列名或数值常量所组成。)

        SELECT  CNAME,COURSE_TIME = CREDIT*16

        FROM   COURSES

例 30  找出教师的最小年龄。(内部函数: SQL 标准中只使用 COUNT 、 SUM 、 AVG 、 MAX 、 MIN 函数,称之为聚集函数( Set Function )。 COUNT 函数的结果是该列统计值的总数目, SUM 函数求该列统计值之和, AVG 函数求该列统计值之平均值, MAX 函数求该列最大值, MIN 函数求该列最小值。)

        SELECT  MIN(AGE)

        FROM   TEACHERS

例 31  统计年龄小于等于 22 岁的学生人数。(统计)

        SELECT  COUNT(*)

        FROM   STUDENTS

        WHERE  AGE < = 22

例 32  找出学生的平均成绩和所学课程门数。

        SELECT  SNO, AVG(GRADE), COURSES = COUNT(*)

        FROM   ENROLLS

        GROUP  BY SNO

 

 

例 34  找出年龄超过平均年龄的学生姓名。

SELECT SNAME

FROM STUDENTS

WHERE AGE >

      (SELECT  AVG(AGE)

        FROM   STUDENTS)

 

例 35  找出各课程的平均成绩,按课程号分组,且只选择学生超过 3 人的课程的成绩。( GROUP BY 与 HAVING

        GROUP BY 子句把一个表按某一指定列(或一些列)上的值相等的原则分组,然后再对每组数据进行规定的操作。

        GROUP BY 子句总是跟在 WHERE 子句后面,当 WHERE 子句缺省时,它跟在 FROM 子句后面。

        HAVING 子句常用于在计算出聚集之后对行的查询进行控制。)

         SELECT CNO, AVG(GRADE), STUDENTS = COUNT(*)

         FROM ENROLLS

         GROUP BY CNO

         HAVING COUNT(*) >= 3

 

相关子查询

 

例 37  查询没有选任何课程的学生的学号和姓名。(当一个子查询涉及到一个来自外部查询的列时,称为相关子查询( Correlated Subquery) 。相关子查询要用到存在测试谓词 EXISTS 和 NOT EXISTS ,以及 ALL 、 ANY ( SOME )等。)

        SELECT SNO, SNAME

        FROM   STUDENTS

        WHERE  NOT EXISTS

              (SELECT *

              FROM ENROLLS

              WHERE ENROLLS.SNO=STUDENTS.SNO)

例 38   查询哪些课程只有男生选读。

        SELECT DISTINCT CNAME

        FROM   COURSES C

        WHERE ' 男 ' = ALL

               (SELECT SEX

                FROM  ENROLLS , STUDENTS

                WHERE ENROLLS.SNO=STUDENTS.SNO AND

                      ENROLLS.CNO=C.CNO)

例 39  要求给出一张学生、籍贯列表,该表中的学生的籍贯省份,也是其他一些学生的籍贯省份。

        SELECT  SNAME, BPLACE

        FROM   STUDENTS A

        WHERE  EXISTS

               (SELECT *

                 FROM STUDENTS B

                 WHERE A.BPLACE=B.BPLACE AND

                       A.SNO < > B.SNO)

 

 

例 40  找出选修了全部课程的学生的姓名。

        本查询可以改为:查询这样一些学生,没有一门课程是他不选修的。

        SELECT  SNAME

        FROM   STUDENTS

        WHERE  NOT EXISTS

               (SELECT *

                FROM COURSES

                WHERE  NOT EXISTS

                        (SELECT *

                         FROM ENROLLS

                         WHERE ENROLLS.SNO = STUDENTS.SNO

                             AND ENROLLS.CNO = COURSES.CNO))

关系代数运算

 

例 41  设有某商场工作人员的两张表:营业员表 SP_SUBORD 和营销经理表 SP_MGR ,其关系数据模式如下:

        SP_SUBORD (SALPERS_ID, SALPERS_NAME, MANAGER_ID, OFFICE)

        SP_MGR (SALPERS_ID, SALPERS_NAME, MANAGER_ID, OFFICE)

        其中,属性 SALPERS_ID 为工作人员的编号 , SALPERS_NAME 为工作人员的姓名 , MANAGER_ID 为所在部门经理的编号 , OFFICE 为工作地点。

若查询全部商场工作人员,可以用下面的 SQL 语句:

        (SELECT * FROM SP_SUBORD)

        UNION

        (SELECT * FROM SP_MGR)

        或等价地用下面的 SQL 语句:

        SELECT *

        FROM (TABLE SP_SUBORD UNION TABLE SP_MGR)

( 2 ) INTERSECT

         (SELECT * FROM SP_SUBORD)

         INTERSECT

         (SELECT * FROM SP_MGR)

        或等价地用下面的 SQL 语句:

        SELECT *

        FROM (TABLE SP_SUBORD INTERSECT TABLE SP_MGR)

        或用带 ALL 的 SQL 语句:

        (SELECT * FROM SP_SUBORD)

        INTERSECT ALL

        (SELECT * FROM SP_MGR)

        或

        SELECT *

        FROM (TABLE SP_SUBORD INTERSECT ALL TABLE SP_MGR)

( 3 ) EXCEPT

        (SELECT * FROM SP_MGR)

        EXCEPT

        (SELECT * FROM SP_SUBORD)

        或等价地用下面的 SQL 语句:

        SELECT *

        FROM (TABLE SP_MGR EXCEPT TABLE SP_ SUBORD)

        或用带 ALL 的 SQL 语句:

        (SELECT * FROM SP_MGR)

        EXCEPT  ALL

        (SELECT * FROM SP_SUBORD)

例 42  查询籍贯为四川、课程成绩在 80 分以上的学生信息及其成绩。(自然连接)

        (SELECT * FROM STUDENTS

         WHERE BPLACE=‘ 四川 ’)

        NATURAL JOIN

        (SELECT * FROM ENROLLS

         WHERE GRADE >=80)

例3.43          列出全部教师的姓名及其任课的课程号、班级。

(外连接与外部并外连接允许在结果表中保留非匹配元组,空缺部分填以 NULL 。外连接的作用是在做连接操作时避免丢失信息。

        外连接有 3 类:

( 1 )左外连接( Left Outer Join )。连接运算谓词为 LEFT [OUTER] JOIN ,其结果表中保留左关系的所有元组。

( 2 )右外连接( Right Outer Join )。连接运算谓词为 RIGHT [OUTER] JOIN ,其结果表中保留右关系的所有元组。

( 3 )全外连接( Full Outer Join )。连接运算谓词为 FULL [OUTER] JOIN ,其结果表中保留左右两关系的所有元组。)

          SELECT TNAME, CNO, CLASS

          FROM TEACHERS LEFT OUTER JOIN TEACHING USING (TNO)

 

SQL 的数据操纵

 

例 44  把教师李映雪的记录加入到教师表 TEACHERS 中。(插入)

        INSERT INTO TEACHERS

        VALUES(1476 , ' 李映雪 ' , 44 , ' 副教授 ')

例 45  成绩优秀的学生将留下当教师。

        INSERT INTO TEACHERS (TNO , TNAME)

        SELECT DISTINCT SNO , SNAME

        FROM   STUDENTS , ENROLLS

        WHERE STUDENTS.SNO = ENROLLS.SNO AND GRADE >= 90

例 47  把所有学生的年龄增加一岁。(修改)

        UPDATE STUDENTS

        SET AGE = AGE+1

例 48  学生张春明在数据库课考试中作弊,该课成绩应作零分计。

        UPDATE ENROLLS

        SET GRADE = 0

        WHERE CNO = 'C1' AND

             ' 张春明 ' =

             (SELECT SNAME

             FROM STUDENTS

             WHERE STUDENTS.SNO=ENROLLS.SNO)

例 49  从教师表中删除年龄已到 60 岁的退休教师的数据。(删除)

        DELETE FROM TEACHERS

        WHERE  AGE >= 60

 

SQL 的数据控制

 

例 50  授予 LILI 有对表 STUDENTS 的查询权。(表/视图特权的授予

        一个 SQL 特权允许一个被授权者在给定的数据库对象上进行特定的操作。授权操作的数据库对象包括:表 / 视图、列、域等。授权的操作包括: INSERT 、 UPDATE 、 DELETE 、 SELECT 、 REFERENCES 、 TRIGGER 、 UNDER 、 USAGE 、 EXECUTE 等。其中 INSERT 、 UPDATE 、 DELETE 、 SELECT 、 REFERENCES 、 TRIGGER 有对表做相应操作的权限,故称为表特权。)

        GRANT SELECT ON STUDENTS

        TO LILI

        WITH GRANT OPTION

例 51  取消 LILI 的存取 STUDENTS 表的特权。

        REVOKE ALL

        ON STUDENTS

        FROM LILI CASCADE

  

编辑:数据库 本文来源:例 3  澳门新濠3559:根据表的 STUDENTS,在school数

关键词: