Monodyee

这个屌丝很懒，什么也没留下！

热门标签

数据库系统概论中SQL语句_创建教材中的学生-课程数据库

作者：Monodyee | 2024-04-25 14:39:31

踩

创建教材中的学生-课程数据库

文章目录

(一) 创建教材学生-课程数据库
(二) 装载数据
(三) 数据查询
(四) 数据更新
(五) 视图
(六)数据库安全性控制
(七)数据库完整性

(一) 创建教材学生-课程数据库

注意注意！本文使用MySQL数据库，其中database和schema是同意义。

create database s_c;
1

use s_c;
1

1.建立 Student表

CREATE TABLE Student (
    Sno CHAR(9) PRIMARY KEY,
    Sname CHAR(20) UNIQUE,
    Ssex CHAR(2),
    Sage SMALLINT,
    Sdept CHAR(20)
);
1
2
3
4
5
6
7

2.建立Course表

CREATE TABLE Course (
    Cno CHAR(4) PRIMARY KEY,
    Cname CHAR(40),
    Cpno CHAR(4),
    Ccredit SMALLINT,
    FOREIGN KEY (Cpno) REFERENCES Course (Cno)
);
1
2
3
4
5
6
7

3.建立SC表

CREATE TABLE SC (
    Sno CHAR(9),
    Cno CHAR(4),
    Grade SMALLINT,
    PRIMARY KEY (Sno , Cno),
    FOREIGN KEY (Sno)  REFERENCES Student (Sno),
    FOREIGN KEY (Cno)  REFERENCES Course (Cno)
);
1
2
3
4
5
6
7
8

修改基本表

[例1] 向Student表增加“入学时间”列，其数据类型为日期型。

ALTER TABLE Student ADD S_entrance DATE;
1

[例2] 将年龄的数据类型由字符型（假设原来的数据类型是字符型）改为整数。

ALTER TABLE Student ALTER COLUMN Sage INT;
1

[例3] 增加课程名称必须取唯一值的约束条件。

ALTER TABLE Course ADD UNIQUE(Cname);
1

删除基本表

[例4] 删除Student表

DROP TABLE  Student  CASCADE ;
1

(二) 装载数据

1.往学生表（student）插入数据

insert into student values('200215121','李勇','男',20,'CS');
1

insert into student values('200215122','刘晨','女',19,'CS');
1

insert into student values('200215123','王敏','女',18,'MA');
1

insert into student values('200215125','张立','男',19,'IS');
1

2.往course表插入数据

insert into course values('1','数据库',null,4);
1

insert into course values('2','数学',null,2);
1

insert into course values('3','信息系统',null,4);
1

insert into course values('4','操作系统',null,3);
1

insert into course values('5','数据结构',null,4);
1

insert into course values('6','数据处理',null,2);
1

insert into course values('7','PASCAL语言',null,4);
1

update course set cpno='5' where cno='1';
1

update course set cpno='1' where cno='3';
1

update course set cpno='6' where cno='4';
1

update course set cpno='7' where cno='5';
1

update course set cpno='6' where cno='7';
1

select * from course;
1

3.往sc表插入数据

insert into sc values('200215121','1',92);
1

insert into sc values('200215121','2',85);
1

insert into sc values('200215121','3',88);
1

insert into sc values('200215122','2',90);
1

insert into sc values('200215122','3',80);
1

select * from sc;
1

(三) 数据查询

单表查询

1. 选择表中的若干列

（1）查询指定列

[例1] 查询全体学生的学号与姓名。

SELECT Sno,Sname 
FROM Student
1
2

[例2] 查询全体学生的姓名、学号、所在系。

SELECT Sname,Sno,Sdept 
FROM Student
1
2

（2）查询全部列

[例3] 查询全体学生的详细记录。

SELECT  Sno,Sname,Ssex,Sage,Sdept
FROM Student
1
2

或

SELECT * FROM Student
1

（3）查询经过计算的值

[例4] 查全体学生的姓名及其出生年份。

SELECT Sname, 2014-Sage    
FROM Student 
1
2

[例5] 查询全体学生的姓名、出生年份和所有系,要求用小写字母表示所有系名

SELECT Sname,'Year of Birth: ',2014-Sage birth_year,LOWER(Sdept)
FROM Student
1
2

使用列别名改变查询结果的列标题:

SELECT Sname NAME,
       'Year of Birth:' BIRTH,
       2014-Sage  BIRTHDAY,
       LOWER(Sdept) DEPARTMENT
FROM Student
1
2
3
4
5

2.选择表中的若干元组

（1）消除取值重复的行

指定DISTINCT关键词,去掉表中重复的行

SELECT DISTINCT Sno
FROM SC
1
2

（2）查询满足条件的元组

比较大小

[例7] 查询计算机科学系全体学生的名单。

SELECT Sname
FROM Student
WHERE Sdept='CS' 
1
2
3

[例8] 查询所有年龄在20岁以下的学生姓名及其年龄。

SELECT Sname,Sage
FROM  Student   
WHERE Sage < 20
1
2
3

[例9] 查询考试成绩有不及格的学生的学号。

SELECT DISTINCT Sno
FROM  SC
WHERE Grade<60
1
2
3

确定范围

[例10] 查询年龄在20~23岁（包括20岁和23岁）之间的学生的姓名、系别和年龄

SELECT Sname,Sdept,Sage
FROM    Student
WHERE   Sage BETWEEN 20 AND 23
1
2
3

[例11] 查询年龄不在20~23岁之间的学生姓名、系别和年龄

SELECT Sname,Sdept,Sage
FROM    Student
WHERE Sage NOT BETWEEN 20 AND 23
1
2
3

确定集合

[例12]查询信息系(IS)、数学系（MA）和计算机科学系（CS）学生的姓名和性别。

SELECT Sname,Ssex
FROM  Student
WHERE Sdept IN ( 'IS','MA','CS' );
1
2
3

[例13]查询既不是信息系、数学系,也不是计算机科学系的学生的姓名和性别。

SELECT Sname,Ssex
FROM Student
WHERE Sdept NOT IN ( 'IS','MA','CS' )
1
2
3

字符匹配

[例14] 查询学号为200215121的学生的详细情况。

SELECT *   
FROM  Student 
WHERE  Sno LIKE '200215121'
1
2
3

[例15] 查询所有姓刘学生的姓名、学号和性别。

SELECT Sname,Sno,Ssex    
FROM Student     
WHERE  Sname LIKE '李%'
1
2
3

[例16] 查询姓"欧阳"且全名为三个汉字的学生的姓名。

SELECT Sname     
FROM   Student      
WHERE  Sname LIKE '欧阳__'
1
2
3

–[例17] 查询名字中第2个字为"阳"字的学生的姓名和学号。

SELECT Sname,Sno     
FROM Student    
WHERE Sname LIKE '__阳%'
1
2
3

[例18] 查询所有不姓刘的学生姓名。

SELECT Sname,Sno,Ssex    
FROM Student   
WHERE Sname NOT LIKE '刘%'
1
2
3

[例19] 查询DB_Design课程的课程号和学分。

SELECT Cno,Ccredit     
FROM Course     
WHERE Cname LIKE 'DB\_Design' ESCAPE '\'
1
2
3

[例20] 查询以"DB_"开头,且倒数第3个字符为 i的课程的详细情况。

SELECT  *   
FROM   Course     
WHERE  Cname LIKE  'DB\_%i_ _' ESCAPE '\'
1
2
3

涉及空值的查询

[例21] 某些学生选修课程后没有参加考试,所以有选课记录,但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号。

SELECT Sno,Cno     
FROM  SC     
WHERE  Grade IS NULL
1
2
3

[例22] 查所有有成绩的学生学号和课程号。

SELECT Sno,Cno     
FROM  SC     
WHERE  Grade IS NOT NULL
1
2
3

多重条件查询

[例23] 查询计算机系年龄在20岁以下的学生姓名。

SELECT Sname     
FROM  Student     
WHERE Sdept= 'CS' AND Sage<20
1
2
3

3.ORDER BY子句

[例24] 查询选修了3号课程的学生的学号及其成绩,查询结果按分数降序排列。

SELECT Sno,Grade        
FROM  SC        
WHERE  Cno= ' 3 '       
ORDER BY Grade DESC
1
2
3
4

[例25] 查询全体学生情况,查询结果按所在系的系号升序排列,同一系中的学生按年龄降序排列。

SELECT  *       
FROM  Student       
ORDER BY Sdept ASC,Sage DESC
1
2
3

4.聚焦函数

COUNT()

[例26] 查询学生总人数。

SELECT COUNT( * )
FROM Student
1
2

[例27] 查询选修了课程的学生人数。

SELECT COUNT(DISTINCT Sno)   
FROM SC
1
2

AVG()

[例28] 计算1号课程的学生平均成绩。

SELECT AVG(Grade)       
FROM SC         
WHERE Cno='1'
1
2
3

MAX()

[例29] 查询选修1号课程的学生最高分数。

SELECT MAX(Grade)   
FROM SC   
WHERE Cno='1'
1
2
3

SUM()

[例30] 查询学生200215121选修课程的总学分数。

SELECT SUM(Ccredit)       
FROM  SC, Course         
WHERE Sno='200215121' AND SC.Cno=Course.Cno
1
2
3

5.GROUP BY子句

[例31] 求各个课程号及相应的选课人数

SELECT Cno,COUNT(Sno)   
FROM    SC   
GROUP BY Cno
1
2
3

[例32] 查询选修了3门以上课程的学生学号。

SELECT Sno   
FROM  SC    
GROUP BY Sno   
HAVING  COUNT(*) >3
1
2
3
4

连接查询

若一个查询同时涉及两个以上的表，则称之为连接查询

1.等值查询与非等值查询

[例33] 查询每个学生及其选修课程的情况

SELECT  Student.*,SC.*
FROM     Student,SC
WHERE  Student.Sno = SC.Sno
1
2
3

[例34] 对[例33]用自然连接完成。

SELECT  Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROM     Student,SC  
WHERE  Student.Sno = SC.Sno
1
2
3

[例35]查询选修2号课程且成绩在90分以上的所有学生

SELECT 
    Student.Sno, Sname
FROM
    Student,
    SC
WHERE
    Student.Sno = SC.Sno AND SC.Cno = '2'
        AND SC.Grade > 90
1
2
3
4
5
6
7
8

2.自身连接

[例36]查询每一门课的间接先修课（即先修课的先修课）

SELECT  FIRST.Cno,SECOND.Cpno    
FROM  Course  FIRST,Course  SECOND  
WHERE FIRST.Cpno = SECOND.Cno
1
2
3

3.外连接

SELECT Student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROM  Student  
LEFT OUT JOIN SC 
ON (Student.Sno=SC.Sno)
1
2
3
4

4.多表连接

[例38]查询每个学生的学号、姓名、选修的课程名及成绩

SELECT 
    Student.Sno, Sname, Cname, Grade
FROM
    Student,
    SC,
    Course
WHERE
    Student.Sno = SC.Sno
        AND SC.Cno = Course.Cno
1
2
3
4
5
6
7
8
9

嵌套查询

1.带有IN谓词的子查询

[例39] 查询与“刘晨”在同一个系学习的学生。

SELECT 
    Sno, Sname, Sdept
FROM
    Student
WHERE
    Sdept IN 
    	(SELECT 
            Sdept
         FROM
            Student
         WHERE
            Sname = '刘晨')；
1
2
3
4
5
6
7
8
9
10
11
12

[例40]查询选修了课程名为“信息系统”的学生学号和姓名

SELECT 
    Sno, Sname
FROM
    Student
WHERE
    Sno IN 
    	(SELECT 
            Sno
         FROM
            SC
         WHERE
            Cno IN 
            	(SELECT 
                    Cno
                 FROM
                    Course
                 WHERE
                    Cname = '信息系统'))
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

2.带有比较运算符的子查询

[例41] 找出每个学生超过他选修课程平均成绩的课程号。

SELECT 
    Sno, Cno
FROM
    SC x
WHERE
    Grade >= (SELECT 
            AVG(Grade)
        FROM
            SC y
        WHERE
            y.Sno = x.Sno)

1
2
3
4
5
6
7
8
9
10
11
12

3.带有ANY(SOME)或ALL谓词的子查询

[例42] 查询其他系中比计算机科学某一学生年龄小的学生姓名和年龄

SELECT 
    Sname, Sage
FROM
    Student
WHERE
    Sage < ANY (SELECT 
            Sage
        FROM
            Student
        WHERE
            Sdept = 'CS')
        AND Sdept <> 'CS'

1
2
3
4
5
6
7
8
9
10
11
12
13

[例43] 查询其他系中比计算机科学系所有学生年龄都小的学生姓名及年龄。

SELECT 
    Sname, Sage
FROM
    Student
WHERE
    Sage < ALL (SELECT 
            Sage
        FROM
            Student
        WHERE
            Sdept = ' CS ')
        AND Sdept <> ' CS '
1
2
3
4
5
6
7
8
9
10
11
12

4.带有EXISTS谓词的子查询

[例44]查询所有选修了1号课程的学生姓名。

用嵌套查询

SELECT 
    Sname
FROM
    Student
WHERE
    EXISTS( SELECT 
            *
        FROM
            SC
        WHERE
            Sno = Student.Sno AND Cno = ' 1 ')
1
2
3
4
5
6
7
8
9
10
11

用连接运算

SELECT Sname  
FROM Student, SC
WHERE Student.Sno=SC.Sno AND SC.Cno= '1'
1
2
3

[例45] 查询没有选修1号课程的学生姓名。

SELECT 
    Sname
FROM
    Student
WHERE
    NOT EXISTS( SELECT 
            *
        FROM
            SC
        WHERE
            Sno = Student.Sno AND Cno = '1')
1
2
3
4
5
6
7
8
9
10
11

[例46] 查询选修了全部课程的学生姓名。

SELECT 
    Sname
FROM
    Student
WHERE
    NOT EXISTS
    (SELECT 
      *
     FROM
      Course
     WHERE
         NOT EXISTS
         (SELECT 
           *
          FROM
           SC
          WHERE
           Sno = Student.Sno AND Cno = Course.Cno)
     );
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

[例47]查询至少选修了学生200215122选修的全部课程的学生号码。

SELECT DISTINCT
    Sno
FROM
    SC SCX
WHERE
    NOT EXISTS
    (SELECT 
      *
     FROM
      SC SCY
     WHERE
      SCY.Sno = ' 200215122' AND NOT EXISTS
      							 (SELECT 
                                   *
                				  FROM
                    			   SC SCZ
                				  WHERE
                    			   SCZ.Sno = SCX.Sno AND SCZ.Cno = SCY.Cno)
      )
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

集合查询

[例48] 查询计算机科学系的学生及年龄不大于19岁的学生。
方法一：

SELECT 
    *
FROM
    Student
WHERE
    Sdept = 'CS' 
UNION 
SELECT 
    *
FROM
    Student
WHERE
    Sage <= 19

1
2
3
4
5
6
7
8
9
10
11
12
13
14

方法二：

SELECT  
DISTINCT  *     
FROM Student       
WHERE Sdept= 'CS'  OR  Sage<=19
1
2
3
4

[例49] 查询选修了课程1或者选修了课程2的学生。

SELECT 
    Sno
FROM
    SC
WHERE
    Cno = ' 1 ' 
UNION 
SELECT 
    Sno
FROM
    SC
WHERE
    Cno = ' 2 '
1
2
3
4
5
6
7
8
9
10
11
12
13

[例50] 查询计算机科学系的学生与年龄不大于19岁的学生的交集

SELECT 
 	*
FROM 
 	Student
WHERE 
	Sdept='CS'
INTERSECT
SELECT 
	*
FROM 
	Student
WHERE 
	Sage<=19
1
2
3
4
5
6
7
8
9
10
11
12
13

[例51] 查询选修课程1的学生集合与选修课程2的学生集合的交集

SELECT Sno  
FROM SC
WHERE Cno='1'  
INTERSECT
SELECT Sno  
FROM SC    
WHERE Cno='2'
1
2
3
4
5
6
7

[例52] 查询计算机科学系的学生与年龄不大于19岁的学生的差集。

SELECT *  

FROM Student    

WHERE Sdept='CS' 
   
EXCEPT 
    
SELECT  *   
 
FROM Student 
 
WHERE Sage <=19
1
2
3
4
5
6
7
8
9
10
11
12
13

SELECT 
    *
FROM
    Student s1
WHERE
    s1.Sdept = 'CS'
        AND NOT EXISTS( SELECT 
            *
        FROM
            Student s2
        WHERE
            s2.Sage <= 19 AND s1.sno = s2.sno)
1
2
3
4
5
6
7
8
9
10
11
12

基于派生表的查询

(四) 数据更新

插入数据

1.插入元组

[例1] 将一个新学生元组（学号：200215128；姓名：陈冬；性别：男；所在系：IS；年龄：18岁）插入到Student表中。

INSERT
INTO  Student (Sno，Sname，Ssex，Sdept，Sage)
VALUES ('200215128'，'陈冬'，'男'，'IS'，18)；
1
2
3

[例2] 将学生张成民的信息插入到Student表中。

INSERT
INTO  Student
VALUES ('200215126', '张成民', '男',18,'CS'); 
1
2
3

[例3] 插入一条选课记录( ‘200215128’，'1 ')。

INSERT
INTO SC(Sno,Cno)			//RDBMS将在新插入记录的Grade列上自动地赋空值
VALUES ('200215128','1');
1
2
3

INSERT
INTO SC
VALUES ('200215128','1',NULL);
1
2
3

2.插入子查询结果

[例4] 对每一个系，求学生的平均年龄，并把结果存入数据库。

CREATE  TABLE  Dept_age(
Sdept  CHAR(15),      	  /* 系名*/
Avg_age SMALLINT		  /*学生平均年龄*/
);   	 
1
2
3
4

INSERT 
INTO  Dept_age(Sdept,Avg_age)
SELECT  Sdept,AVG(Sage)
FROM  Student
GROUP BY Sdept;
1
2
3
4
5

修改数据

1.修改某一个元组的值

[例5] 将学生201215121的年龄改为22岁

UPDATE  Student
SET Sage=22
WHERE  Sno='201215121'; 
1
2
3

2.修改多个元组的值

[例6] 将所有学生的年龄增加1岁

UPDATE Student
SET Sage= Sage+1;
1
2

3.带子查询的修改语句

[例7] 将计算机科学系全体学生的成绩置零。

UPDATE SC
SET  Grade=0
WHERE Sno IN
	( SELECT Sno
	  From Student
	  WHERE Sdept='CS');
1
2
3
4
5
6

删除数据

1.删除某一个元组的值

[例8] 删除学号为201215128的学生记录

DELETE
FROM Student
WHERE Sno= '200215128';
1
2
3

2.删除多个元组的值

[例9] 删除所有的学生选课记录。

DELETE
FROM SC；
1
2

3.带子查询的删除语句

[例10] 删除计算机科学系所有学生的选课记录。

DELETE
FROM SC
WHERE Sno IN
	( SELECT Sno
	  From Student
	  WHERE Sdept='CS');
1
2
3
4
5
6

(五) 视图

定义视图

1.建立视图

[例1] 建立信息系IS学生的视图。

CREATE VIEW IS_Student
AS 
SELECT Sno,Sname,Sage
FROM    Student
WHERE  Sdept= 'IS';
1
2
3
4
5

[例2]建立信息系学生的视图，并要求进行修改和插入操作时仍需保证该视图只有信息系IS的学生。

CREATE VIEW IS_Student
AS 
SELECT Sno,Sname,Sage
FROM  Student
WHERE  Sdept= 'IS'
WITH CHECK OPTION;
1
2
3
4
5
6

[例3] 建立信息系选修了1号课程的学生视图。

CREATE VIEW IS_S1(Sno,Sname,Grade)
AS 
SELECT Student.Sno,Sname,Grade
FROM  Student,SC
WHERE  Sdept= 'IS' 
	   AND Student.Sno=SC.Sno 
       AND SC.Cno= '1';
1
2
3
4
5
6
7

[例4] 建立信息系选修了1号课程且成绩在90分以上的学生的视图。

CREATE VIEW IS_S2
AS
SELECT Sno,Sname,Grade
FROM  IS_S1
WHERE  Grade>=90;
1
2
3
4
5

[例5] 定义一个反映学生出生年份的视图。

CREATE  VIEW BT_S(Sno,Sname,Sbirth)
AS 
SELECT Sno,Sname,2000-Sage
FROM  Student;
1
2
3
4

[例6] 将学生的学号及他的平均成绩定义为一个视图

CREATE  VIEW S_G(Sno,Gavg)
AS  
SELECT Sno,AVG(Grade)
FROM  SC
GROUP BY Sno;
1
2
3
4
5

[例7]将Student表中所有女生记录定义为一个视图

CREATE VIEW F_Student(F_Sno,name,sex,age,dept)
AS
SELECT *
FROM  Student
WHERE Ssex='女';
1
2
3
4
5

2.删除视图

[例8] 删除视图IS_S1

DROP VIEW IS_S1;
1

查询视图

[例9] 在信息系学生的视图中找出年龄小于等于20岁的学生。

SELECT  Sno,Sage
FROM    IS_Student
WHERE   Sage<=20;
1
2
3

[例10] 查询选修了1号课程的信息系学生

SELECT  IS_Student.Sno,Sname
FROM    IS_Student,SC
WHERE   IS_Student.Sno =SC.Sno AND SC.Cno= '1';
1
2
3

[例11]在S_G视图中查询平均成绩在90分以上的学生学号和平均成绩

SELECT *
FROM   S_G
WHERE  Gavg>=90;
1
2
3

更新视图

[例12] 将信息系学生视图IS_Student中学号201215125的学生姓名改为“刘辰”。

UPDATE  IS_Student
SET  Sname= '刘辰'
WHERE  Sno= '201215125'; 
1
2
3

UPDATE  Student
SET Sname= '刘辰'
WHERE Sno= '201215125' 
AND Sdept= 'IS';
1
2
3
4

[例13] 向信息系学生视图IS_Student中插入一个新的学生记录：200215111，赵新，20岁

INSERT
INTO IS_Student
VALUES('200215111','赵新',20);	//插入后基本表Student字段Sdept为空，视图表IS_Student无数据
1
2
3

INSERT
INTO   Student(Sno,Sname,Sage,Sdept)
VALUES('200215129','赵新2',20,NULL);
1
2
3

[例14]删除信息系学生视图IS_Student中学号为200215129的记录

DELETE
FROM IS_Student
WHERE Sno= '200215129';
1
2
3

DELETE
FROM Student
WHERE Sno= '200215129' AND Sdept= 'IS';
1
2
3

(六)数据库安全性控制

授权：授予与收回

1.GRANT

[例1] 把查询Student表权限授给用户U1

GRANT SELECT 
ON TABLE Student 
TO U1;
1
2
3

[例2] 把对Student表和Course表的全部权限授予用户U2和U3

GRANT ALL PRIVILIGES 
ON TABLE Student, Course 
TO U2, U3;
1
2
3

[例3] 把对表SC的查询权限授予所有用户

GRANT SELECT 
ON TABLE SC 
TO PUBLIC;
1
2
3

[例4] 把查询Student表和修改学生学号的权限授给用户U4

GRANT UPDATE(Sno), SELECT 
ON TABLE Student 
TO U4;
1
2
3

[例5] 把对表SC的INSERT权限授予U5用户，并允许他再将此权限授予其他用户

GRANT INSERT 
ON TABLE SC 
TO U5
WITH GRANT OPTION;
1
2
3
4

2.REVOKE

[例6] 把用户U4修改学生学号的权限收回

REVOKE UPDATE(Sno)
ON TABLE Student 
FROM U4;
1
2
3

[例7] 收回所有用户对表SC的查询权限

REVOKE SELECT 
ON TABLE SC 
FROM PUBLIC;
1
2
3

[例8] 把用户U5对SC表的INSERT权限收回

REVOKE INSERT 
ON TABLE SC 
FROM U5 CASCADE ;
1
2
3

数据库角色

1.角色的创建

CREATE ROLE R1；
1

2.给角色授权

GRANT SELECT，UPDATE，INSERT 
ON TABLE Student 
TO R1
1
2
3

3.将一个角色授予其他的角色或用户

GRANT  R1 
TO 王平，张明，赵玲；
1
2

4.角色权限的收回

REVOKE  R1 
FROM 王平；
1
2

(七)数据库完整性

实体完整性

CREATE TABLE Student
(Sno  CHAR(9)  PRIMARY KEY，
 Sname  CHAR(20) NOT NULL，     
 Ssex  CHAR(2) ，
 Sage  SMALLINT，
 Sdept  CHAR(20)
);
1
2
3
4
5
6
7

CREATE TABLE Student
(Sno  CHAR(9)，  
 Sname  CHAR(20) NOT NULL，
 Ssex  CHAR(2)， 
 Sage  SMALLINT，
 Sdept  CHAR(20)，
 PRIMARY KEY (Sno)
); 
1
2
3
4
5
6
7
8

CREATE TABLE SC
(Sno  CHAR(9)  NOT NULL， 
 Cno  CHAR(4)  NOT NULL，  
 Grade  SMALLINT，
 PRIMARY KEY (Sno，Cno)     /*只能在表级定义主码*/
); 
1
2
3
4
5
6

参照完整性

CREATE TABLE SC
(Sno    CHAR(9)  NOT NULL， 
 Cno    CHAR(4)  NOT NULL，  
 Grade  SMALLINT，
 /*在表级定义实体完整性*/
 PRIMARY KEY (Sno， Cno)，
 /*在表级定义参照完整性*/   
 FOREIGN KEY (Sno) REFERENCES Student(Sno)，  
 /*在表级定义参照完整性*/
 FOREIGN KEY (Cno) REFERENCES Course(Cno)    
);
1
2
3
4
5
6
7
8
9
10
11

[例1] 显式说明参照完整性的违约处理示例

CREATE TABLE SC
(Sno   CHAR(9)  NOT NULL，
 Cno   CHAR(4)  NOT NULL，
 Grade  SMALLINT，
 
 PRIMARY KEY（Sno，Cno）， 				
 FOREIGN KEY (Sno) REFERENCES Student(Sno) 
 ON DELETE CASCADE    	/*级联删除SC表中相应的元组*/
 ON UPDATE CASCADE， 	/*级联更新SC表中相应的元组*/
 
 FOREIGN KEY (Cno) REFERENCES Course(Cno) 
 /*当删除course 表中的元组造成了与SC表不一致时拒绝删除*/	                    
 ON DELETE NO ACTION 	
 /*当更新course表中的cno时，级联更新SC表中相应的元组*/
 ON UPDATE CASCADE   	 
)；
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

用户定义的完整性

1.属性上的约束条件

[例2] 在定义SC表时，说明Sno、Cno、Grade属性不允许取空值

CREATE TABLE SC
(Sno  CHAR(9)  NOT NULL，	
 Cno  CHAR(4)  NOT NULL，	
 Grade  SMALLINT NOT NULL，	
 PRIMARY KEY (Sno， Cno)，  
/*如果在表级定义实体完整性，隐含了Sno，Cno不允许取空值，则在列级不允许取空值的定义就不必写了*/
）;
1
2
3
4
5
6
7

[例3] 建立部门表DEPT，要求部门名称Dname列取值唯一，部门编号Deptno列为主码

CREATE TABLE DEPT
(Deptno  NUMERIC(2)，
 Dname  CHAR(9)  UNIQUE，/*要求Dname列值唯一*/
 Location  CHAR(10)，
 PRIMARY KEY (Deptno)
);
1
2
3
4
5
6

[例4］Student表的Ssex只允许取“男”或“女”。

CREATE TABLE Student
(Sno  CHAR(9) PRIMARY KEY，
 Sname CHAR(8) NOT NULL，                     
 Ssex  CHAR(2)  CHECK (Ssex IN (‘男’，‘女’))， /*性别属性Ssex只允许取'男'或'女' */               
 Sage  SMALLINT，
 Sdept  CHAR(20)
);
1
2
3
4
5
6
7

2.元组上的约束条件

[例5] 当学生的性别是男时，其名字不能以Ms.打头。

CREATE TABLE Student
(Sno    CHAR(9)， 
 Sname  CHAR(8) NOT NULL，
 Ssex    CHAR(2)，
 Sage   SMALLINT，
 Sdept  CHAR(20)，
 PRIMARY KEY (Sno)，
 CHECK (Ssex='女' OR Sname NOT LIKE 'Ms.%') 
)；
1
2
3
4
5
6
7
8
9

完整性约束命名子句

1.设置完整性约束

[例6] 建立学生登记表Student，要求学号在90000~99999之间，姓名不能取空值，年龄小于30，性别只能是“男”或“女”。

CREATE TABLE Student
(Sno  NUMERIC(6)
 CONSTRAINT C1 CHECK (Sno BETWEEN 90000 AND 99999)，
 
 Sname  CHAR(20)  
 CONSTRAINT C2 NOT NULL，
       
 Sage  NUMERIC(3)
 CONSTRAINT C3 CHECK (Sage < 30)，
       
 Ssex  CHAR(2)
 CONSTRAINT C4 CHECK (Ssex IN ( '男'，'女'))，
       
 CONSTRAINT StudentKey PRIMARY KEY(Sno)
)；
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

2.修改表中的完整性限制

[例7] 修改表Student中的约束条件，要求学号改为在900000~999999之间，年龄由小于30改为小于40

ALTER TABLE Student
DROP CONSTRAINT C1;
1
2

ALTER TABLE Student
ADD CONSTRAINT C1 CHECK (Sno BETWEEN 900000 AND 999999);      
1
2

ALTER TABLE Student
DROP CONSTRAINT C3;
1
2

ALTER TABLE Student
ADD CONSTRAINT C3 CHECK (Sage < 40);
1
2

断言

MySQL不支持断言

1.创建断言

[例8]限制数据库课程最多 60名学生选修。

CREATE ASSERTION ASSE_SC_DB_ NUM
	CHECK ( 60>=(SELECT count(*)
		  	     FROM Course,SC
			     WHERE SC.Cno=Course.Cno AND Course.Cname=数据库)
		   );
1
2
3
4
5

[例9]限制每一门课程最多60名学生选修。

CREATE ASSERTION ASSE_SC_CNUMI
	  CHECK( 60>=ALL(SELECT count (*)
 			         FROM SC							
					 GROUP by cno)
		    );
1
2
3
4
5

[例10] 限制每个学期每一门课程最多60名学生选修

ALTER TABLE SC ADD TERM DATE;
1

CREATE ASSERTION ASSE_SC_CNUM2
		CHECK( 60>=ALL(select count (*) 
					   from SC 
					   group by Cno,TERM )
	          ); 
1
2
3
4
5

2.删除断言

DROP ASSERTION <断言名>;
1

触发器

1.定义触发器

在这里插入图片描述

[例11] 定义一个BEFORE行级触发器，为教师表Teacher定义完整性规则“教授的工资不得低于4000元，如果低于4000元，自动改为4000元”。

CREATE TRIGGER Insert_Or_Update_Sal 
BEFORE INSERT OR UPDATE ON Teacher   
/*MySQL不需要进行引用*/
FOR EACH ROW                      /*行级触发器*/
BEGIN                			  /*定义触发动作体，是PL/SQL过程块*/
	IF (new.Job='教授') AND (new.Sal < 4000) 
	THEN   new.Sal :=4000;                
    END IF;
END;       
1
2
3
4
5
6
7
8
9

[例12] 定义AFTER行级触发器，当教师表Teacher的工资发生变化后就自动在工资变化表Sal_log中增加一条相应记录

CREATE TABLE Sal_log
(Eno    NUMERIC(4)  references teacher(eno)，
 Sal     NUMERIC(7，2)，
 Username  char(10)，
 Date   TIMESTAMP
)；
1
2
3
4
5
6

CREATE TRIGGER Insert_Sal               	
AFTER INSERT ON Teacher      	/*触发事件是INSERT*/
FOR EACH ROW
BEGIN
    INSERT 
    INTO Sal_log 
    VALUES(new.Eno，new.Sal，CURRENT_USER，CURRENT_TIMESTAMP);
END;
1
2
3
4
5
6
7
8

CREATE TRIGGER Update_Sal            	
AFTER UPDATE ON Teacher    	
FOR EACH ROW
BEGIN 
    IF (new.Sal <> old.Sal) THEN 
    	INSERT 
    	INTO Sal_log 
    	VALUES(new.Eno，new.Sal，CURRENT_USER，CURRENT_TIMESTAMP);
    END IF;
END;
1
2
3
4
5
6
7
8
9
10

2.激活触发器

3.删除触发器

DROP TRIGGER <触发器名> ON <表名>;
1

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/Monodyee/article/detail/485826