数据库系统概论中SQL语句

greedy-hat

已于 2022-03-24 11:01:01 修改

阅读量1.2k

点赞数 5

文章标签： sql mysql 数据库

于 2022-02-08 21:48:53 首次发布

本文链接：https://blog.csdn.net/qq_41638851/article/details/122831204

版权

考研复试专栏收录该内容

5 篇文章 10 订阅

订阅专栏

文章目录

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

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

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

create database s_c;

use s_c;

1.建立 Student表

CREATE TABLE Student (
    Sno CHAR(9) PRIMARY KEY,
    Sname CHAR(20) UNIQUE,
    Ssex CHAR(2),
    Sage SMALLINT,
    Sdept CHAR(20)
);

2.建立Course表

CREATE TABLE Course (
    Cno CHAR(4) PRIMARY KEY,
    Cname CHAR(40),
    Cpno CHAR(4),
    Ccredit SMALLINT,
    FOREIGN KEY (Cpno) REFERENCES Course (Cno)
);

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] 向Student表增加“入学时间”列，其数据类型为日期型。

ALTER TABLE Student ADD S_entrance DATE;

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

ALTER TABLE Student ALTER COLUMN Sage INT;

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

ALTER TABLE Course ADD UNIQUE(Cname);

删除基本表

[例4] 删除Student表

DROP TABLE  Student  CASCADE ;

(二) 装载数据

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

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

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

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

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

2.往course表插入数据

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

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

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

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

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

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

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

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

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

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

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

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

select * from course;

3.往sc表插入数据

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

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

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

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

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

select * from sc;

(三) 数据查询

单表查询

1. 选择表中的若干列

（1）查询指定列

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

SELECT Sno,Sname 
FROM Student

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

SELECT Sname,Sno,Sdept 
FROM Student

（2）查询全部列

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

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

或

SELECT * FROM Student

（3）查询经过计算的值

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

SELECT Sname, 2014-Sage    
FROM Student

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

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

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

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

2.选择表中的若干元组

（1）消除取值重复的行

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

SELECT DISTINCT Sno
FROM SC

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

比较大小

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

SELECT Sname
FROM Student
WHERE Sdept='CS'

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

SELECT Sname,Sage
FROM  Student   
WHERE Sage < 20

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

SELECT DISTINCT Sno
FROM  SC
WHERE Grade<60

确定范围

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

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

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

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

确定集合

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

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

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

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

字符匹配

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

SELECT *   
FROM  Student 
WHERE  Sno LIKE '200215121'

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

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

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

SELECT Sname     
FROM   Student      
WHERE  Sname LIKE '欧阳__'

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

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

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

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

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

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

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

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

涉及空值的查询

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

SELECT Sno,Cno     
FROM  SC     
WHERE  Grade IS NULL

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

SELECT Sno,Cno     
FROM  SC     
WHERE  Grade IS NOT NULL

多重条件查询

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

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

3.ORDER BY子句

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

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

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

SELECT  *       
FROM  Student       
ORDER BY Sdept ASC,Sage DESC

4.聚焦函数

COUNT()

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

SELECT COUNT( * )
FROM Student

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

SELECT COUNT(DISTINCT Sno)   
FROM SC

AVG()

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

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

MAX()

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

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

SUM()

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

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

5.GROUP BY子句

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

SELECT Cno,COUNT(Sno)   
FROM    SC   
GROUP BY Cno

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

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

连接查询

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

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

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

SELECT  Student.*,SC.*
FROM     Student,SC
WHERE  Student.Sno = SC.Sno

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

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

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

SELECT 
    Student.Sno, Sname
FROM
    Student,
    SC
WHERE
    Student.Sno = SC.Sno AND SC.Cno = '2'
        AND SC.Grade > 90

2.自身连接

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

SELECT  FIRST.Cno,SECOND.Cpno    
FROM  Course  FIRST,Course  SECOND  
WHERE FIRST.Cpno = SECOND.Cno

3.外连接

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

4.多表连接

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

SELECT 
    Student.Sno, Sname, Cname, Grade
FROM
    Student,
    SC,
    Course
WHERE
    Student.Sno = SC.Sno
        AND SC.Cno = Course.Cno

嵌套查询

1.带有IN谓词的子查询

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

SELECT 
    Sno, Sname, Sdept
FROM
    Student
WHERE
    Sdept IN 
    	(SELECT 
            Sdept
         FROM
            Student
         WHERE
            Sname = '刘晨')；

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

SELECT 
    Sno, Sname
FROM
    Student
WHERE
    Sno IN 
    	(SELECT 
            Sno
         FROM
            SC
         WHERE
            Cno IN 
            	(SELECT 
                    Cno
                 FROM
                    Course
                 WHERE
                    Cname = '信息系统'))

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

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

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

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

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

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

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

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

4.带有EXISTS谓词的子查询

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

用嵌套查询

SELECT 
    Sname
FROM
    Student
WHERE
    EXISTS( SELECT 
            *
        FROM
            SC
        WHERE
            Sno = Student.Sno AND Cno = ' 1 ')

用连接运算

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

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

SELECT 
    Sname
FROM
    Student
WHERE
    NOT EXISTS( SELECT 
            *
        FROM
            SC
        WHERE
            Sno = Student.Sno AND Cno = '1')

[例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)
     );

[例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)
      )

集合查询

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

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

方法二：

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

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

SELECT 
    Sno
FROM
    SC
WHERE
    Cno = ' 1 ' 
UNION 
SELECT 
    Sno
FROM
    SC
WHERE
    Cno = ' 2 '

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

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

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

SELECT Sno  
FROM SC
WHERE Cno='1'  
INTERSECT
SELECT Sno  
FROM SC    
WHERE Cno='2'

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

SELECT *  

FROM Student    

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

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.插入元组

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

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

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

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

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

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

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

2.插入子查询结果

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

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

INSERT 
INTO  Dept_age(Sdept,Avg_age)
SELECT  Sdept,AVG(Sage)
FROM  Student
GROUP BY Sdept;

修改数据

1.修改某一个元组的值

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

UPDATE  Student
SET Sage=22
WHERE  Sno='201215121';

2.修改多个元组的值

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

UPDATE Student
SET Sage= Sage+1;

3.带子查询的修改语句

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

UPDATE SC
SET  Grade=0
WHERE Sno IN
	( SELECT Sno
	  From Student
	  WHERE Sdept='CS');

删除数据

1.删除某一个元组的值

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

DELETE
FROM Student
WHERE Sno= '200215128';

2.删除多个元组的值

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

DELETE
FROM SC；

3.带子查询的删除语句

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

DELETE
FROM SC
WHERE Sno IN
	( SELECT Sno
	  From Student
	  WHERE Sdept='CS');

(五) 视图

定义视图

1.建立视图

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

CREATE VIEW IS_Student
AS 
SELECT Sno,Sname,Sage
FROM    Student
WHERE  Sdept= 'IS';

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

CREATE VIEW IS_Student
AS 
SELECT Sno,Sname,Sage
FROM  Student
WHERE  Sdept= 'IS'
WITH CHECK OPTION;

[例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';

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

CREATE VIEW IS_S2
AS
SELECT Sno,Sname,Grade
FROM  IS_S1
WHERE  Grade>=90;

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

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

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

CREATE  VIEW S_G(Sno,Gavg)
AS  
SELECT Sno,AVG(Grade)
FROM  SC
GROUP BY Sno;

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

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

2.删除视图

[例8] 删除视图IS_S1

DROP VIEW IS_S1;

查询视图

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

SELECT  Sno,Sage
FROM    IS_Student
WHERE   Sage<=20;

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

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

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

SELECT *
FROM   S_G
WHERE  Gavg>=90;

更新视图

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

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

UPDATE  Student
SET Sname= '刘辰'
WHERE Sno= '201215125' 
AND Sdept= 'IS';

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

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

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

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

DELETE
FROM IS_Student
WHERE Sno= '200215129';

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

(六)数据库安全性控制

授权：授予与收回

1.GRANT

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

GRANT SELECT 
ON TABLE Student 
TO U1;

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

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

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

GRANT SELECT 
ON TABLE SC 
TO PUBLIC;

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

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

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

GRANT INSERT 
ON TABLE SC 
TO U5
WITH GRANT OPTION;

2.REVOKE

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

REVOKE UPDATE(Sno)
ON TABLE Student 
FROM U4;

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

REVOKE SELECT 
ON TABLE SC 
FROM PUBLIC;

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

REVOKE INSERT 
ON TABLE SC 
FROM U5 CASCADE ;

数据库角色

1.角色的创建

CREATE ROLE R1；

2.给角色授权

GRANT SELECT，UPDATE，INSERT 
ON TABLE Student 
TO R1

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

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

4.角色权限的收回

REVOKE  R1 
FROM 王平；

(七)数据库完整性

实体完整性

CREATE TABLE Student
(Sno  CHAR(9)  PRIMARY KEY，
 Sname  CHAR(20) NOT NULL，     
 Ssex  CHAR(2) ，
 Sage  SMALLINT，
 Sdept  CHAR(20)
);

CREATE TABLE Student
(Sno  CHAR(9)，  
 Sname  CHAR(20) NOT NULL，
 Ssex  CHAR(2)， 
 Sage  SMALLINT，
 Sdept  CHAR(20)，
 PRIMARY KEY (Sno)
);

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

参照完整性

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] 显式说明参照完整性的违约处理示例

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] 在定义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不允许取空值，则在列级不允许取空值的定义就不必写了*/
）;

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

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

[例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)
);

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.设置完整性约束

[例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)
)；

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

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

ALTER TABLE Student
DROP CONSTRAINT C1;

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

ALTER TABLE Student
DROP CONSTRAINT C3;

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

断言

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=数据库)
		   );

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

CREATE ASSERTION ASSE_SC_CNUMI
	  CHECK( 60>=ALL(SELECT count (*)
 			         FROM SC							
					 GROUP by cno)
		    );

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

ALTER TABLE SC ADD TERM DATE;

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

2.删除断言

DROP ASSERTION <断言名>;

触发器

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;

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

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

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;

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;

2.激活触发器

3.删除触发器

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

greedy-hat

关注

5
点赞
踩
33

收藏

觉得还不错? 一键收藏
1
评论
数据库系统概论中SQL语句

数据库系统概论中SQL语句
复制链接

扫一扫

专栏目录