【数据库原理及应用教程】第三章 SQL

建立数据库

学生选课数据库SCT

学生表：Student(SNo char(8), Sname char(10), Ssex char(2), Sage integer, DNo, char(2), Sclass char(6))

院系表：Dept(DNo char(2), Dname char(10), Dean char(10))

课程表：Course(CNo char(3), Cname char(12), Chourse integer, Credit float(1), TNo char(3))

教师表：Teacher(TNo char(3), Tname char(10), DNo char(2), Slaary float(2))

选课表：SC(SNo char(8), CNo char(3), Score float(1))

建立数据库包括：定义数据库和表（使用DDL），向表中追加元组（DML），两件事。

DDL：创建database，创建table，定义.约束.View.Index…；删除，drop；更改，Alter。
DML：insert, delete, select, update
DCL：授权/取消授权，Grant, Revoke

DDL通常由DBA使用，也可以DBA授权之后的程序

创建数据库

表达式：

Create database 数据库名
1

示例：创建SCT

Create database SCT
1

定义数据库的同时，也可以定义很多物理特性，这里指介绍最简单的定义。然后可以创建表。
表达式：

Create table 表名（列名 数据类型[primary key|unique][Not null]
					[列名 数据类型],...）
1
2

定义表的时候，最少要有一列

• Primary Key每个表只能创建一个主键约束
• Unique唯一性约束（候选键），可以有多个
• Not null非空约束。该列不允许出现空值
• 其它
  示例：定义学生表Student

Create Student(SNo char(10) not null, Sname char(8), Ssex char(2), Sage integer, DNo char(2), Sclass char(6));
1

选择元组select

表达式

Select 列名     				（子句）
	From 表明				（子句）
	Where 检索条件			（子句）
1
2
3

此操作就相当于对一个关系，进行条件查找后再进行投影。
示例：检索学生表中所有学生的信息

Select * 
	From Student;
1
2

示例：检索学生表中所有学生的姓名及年龄

Select Sname, Sage
	From Student
1
2

示例：检索学生表中所有年龄大于等于19岁的学生的年龄及姓名

Select Sage, Sname
	From Student
	Where Sage >= 19;
1
2
3

关键：检索条件书写
not, and, or运算优先级

示例：检索教师表中所有工资少于1500元或者工资大于2000元 并且是03系的教师姓名

Select Tname
	From Teacher
	Where (Salary < 1500 or Salary > 2000) and DNo = '03'; 
	#注意 not, and, or 的运算优先级
1
2
3
4

示例：求或者学过001号课程，或者学过002号课程学生的学号

Select
	From SC
	Where CNo='001' or CNo = '002';
1
2
3

示例：求既学过001课程，又学过002课程学生的学号

Select SNo
	From SC as SC1, SC as SC2
	Where SC1.SNo=SC2.SNo and SC1.CNo = '001' and SC2.CNo = '002';
#下面的查询结果是空，也就是select语句书写有问题
Select SNo # 检索结果为空
	From SC
	Where CNo='001' and CN0='002'; # 不可能既等于001，又等于002

1
2
3
4
5
6
7
8

结果唯一性问题

• 关系模型不允许出现重复的元组，但是现实的DMBS中，允许出现重复元组
• 在Table中，要求无重复元组是通过Primary Key或Unique保证
• 在检索结果中，要求无重复元组，通过DISTINCT保留字实现
  示例：在选课表中，检索成绩大于80分的所有学号

Select DISTINCT SNo
	From SC
	Where Score > 80;
1
2
3

结果排序问题
在select语句中增加order by子句实现排序功能，asc表示升序，desc表示降序
示例：按学号由小到大的顺序显示出所有学生的学号及姓名

Select SNo, SName
	From Student
	Order By SNo asc, Sname asc
1
2
3

示例：检索002号课程大于80分的所有同学学号并按成绩由高到低排序

Select SNo
	From SC
	Where Score > 80 and DNo = ='002'
	Order By Score desc;
1
2
3
4

模糊查询问题
%：匹配0个或多个字符
_：匹配任意单一字符
****：转义字符。比如只想检索百分号，而不是百分号当作通配符使用。

多表查询

多表查询通过连接运算完成，连接运算通过笛卡尔积完成。将连接条件放到Where子句中即可完成。
示例：按“001”号课程成绩由高到低顺序显示所有写上姓名（两表）

Select Student.SName
	From SC, Student
	Where SC.SNo=Student.SNo and SC.CNo='001'
	Order By SC.CNo desc;
1
2
3
4

示例：按‘数据库’课程成绩由高到低顺序显示所有同学姓名（三表）

Select Student.SName
	From Course, SC, Student
	Where Course.CNo=SC.CNo and SC.SNo=Student.SNo and Course.CName='数据库'
	Order By SC.Score;
1
2
3
4

对同一个表进行连接操作：对同一个表，（From子句中）使用不同的别名进行处理
示例：求有薪水差额的任意两位教师

Select T1.TName, T2.TName
	From Teacher T1, Teacher T2
	Where T1.Salary > T2.Salary;
1
2
3

示例：求‘001’号课程有成绩差的任意两位同学

Select S1.SName, S2.SName
	From SC as SC1, SC as SC2, Student as S1, Student as S2
	Where S1.SNo=SC1.SNo and S2.SNo=SC2.SNo and SC1.Score > SC2.Score
1
2
3

示例：求既学过’001’号课又学过‘002’号课的所有学生的学号

Select S1.SNo
	From SC as S1, SC as S2
	Where S1.SNo=S2.SNo and S1.CNO='001' and S2.CNo='002';
1
2
3

示例：列出没学过李明老师教授课程的所有同学姓名？

Select Student.SName
	From Student, Teacher, Course, SC
	Where Course.CNo = Teacher.CNo and Course.CNo=SC.CNo and Course.SNo = Student.SNo and (not Teacher.TName='李明')
	
1
2
3
4

追加元组 insert

表达式：

insert into 表明【列名...】
		values （值...）
1
2

【注意】需要值与列一一对应
示例：向Student表中追加元组

insert into Student
	values('98030101', '张三', '男', '20', '03', '980301');
1
2

嵌套子查询

将values换成select…from…where子句
示例：新建立Table：St(SNo, SName），将以“伟”结尾的同学新增到该表中

Insert into St(SNo, SName)
	Select SNo, SName
		From Student
		Where SName like '%伟';
1
2
3
4

【注意】新增元组时，DMBS会检查用户定义的完整性约束条件，如不符合完整性约束条件，则不会执行新增动作。

元组删除命令 delete

表达式：

Delete From 表名 [Where 条件表达式];
1

如果Where条件省略，则删除所有的元组
Select…From…Where句子很重要
1、当不写Where时，就是删除所有元组
2、Where满足条件的元组
示例：删除SC表中所有元组

Delete From SC;
1

示例：删除98030101号同学所选的所有课程

Delete From SC Where SNo = '98030101';
1

示例：【删除自动控制系的所有同学】：嵌套语句，属于子查询部分（属于后面内容）。

Delete From Student Where in
	(Select DNo From Dep Where Dname='自动控制');
1
2

【更复杂的条件删除语句】
示例：删除有四门不及格课程的所有同学

Delete From Student Where SNo in
	(Select SNo From SC Where Score<60
	Group by SNo Having Count(*)>=4);
1
2
3

• 此SELECT语句书法语法后面解释
• 当删除元组时，DBMS会检查用户定义的完整性约束条件，如不符合完整性约束条件，则不会执行删除动作

**Select…From…Where…**语句很重要

Update命令

元组更新Update命令：用指定要求的值更新指定表中满足指定条件的元组

Update 表名
	Set 列名 = 表达式 ｜ （子查询）
		[[,列名=表达式｜（子查询）]]
		[Where 条件表达式];
1
2
3
4

如果Where 条件省略，则更新所有的元组

示例：将所有教师工资上调5%

Update Teacher
	Set Sal = Sal + Sal * 0.05;
1
2

示例：将所有计算机系教师工资上调10%

Update Teacher
	Set Sal = Sal + Sal*0.1
	Where DNo in
		(Select DNo From Department WHere Dname='计算机');
1
2
3
4

修正与撤销数据库

SQL-DDL撤销与修改

**修正数据库：**修正数据库的定义，主要是修正表的定义
修正基本表的定义

Alter table tablename
ADD {columnname datatype,...}
drop 约束
modify {columnname datatype,...};
1
2
3
4

示例：在学生表Student(SNp, Sname, Ssex, Sage, DNo, Scalss)基础上，增加两列：Saddr, PID

Alter table Student ADD Saddr char(40), PID char(18);
1

示例：将上例表中Sname列的数据类型（原char(8)）增加两个字符

Alter table Student Modify Sname char(10);
1

示例：删除学生姓名必须取名唯一值的约束

Alter table Student Drop Unique(Sname);
1

撤销基本表

drop table 表名;
1

【注意】

1、Delete...From...是删除表中的元组，并不删除表；Drop table 是撤销基本表，包括表的格式、表中所有元组、由该表导出的视图等相关所有内容。 2、只有Delete From，没有Delete table 3、是Drop table

drop database 数据库名;
1

指定数据库

use 数据库名;
1

关闭数据库

close 数据库名;
1