SQL的常用查询操作及例题

SQL分支及常用命令脑图：link.

0. 查询时用到的表格

表结构如下

• 学生课：Student(Sno,Sname,Ssex,Sage,Sdept)
• 课程表：Student(Cno,Cname,Cpno,Ccredit)
• 学生选课表：SC(Sno,Cno,Ssex,Grade)

注：加粗字体为主关键字。

(a) Student

学号(Sno)	姓名（Sname）	性别（Ssex）	年龄（Sage）	所在系（Sdept）
20125121	李勇	男	20	CS
201215122	刘晨	女	19	CS
201215122	王敏	女	18	MA
201215125	张立	男	19	IS

(b) Course

课程号(Cno)	课程名（Cname）	先行课（Cpno）	学分（Ccredit）
1	数据库	5	4
2	数学	null	2
3	信息系统	1	4
4	操作系统	6	3
5	数据结构	7	4
6	数据处理	null	2
7	PASCAL语言	6	4

（c）SC

学号（Sno)	课程号（Cno）	成绩（Crade）
201215121	1	92
201215121	2	85
201215121	3	88
201215122	2	90
201215122	3	80

另外，我们要了解这四个词的含义：模式，基本表，视图，索引。

一．数据定义

1. 模式定义与删除

SQL的数据定义语句：

操作对象	创建	删除	修改
模式	CREATE SCHEMA	DROP SCHEMA	无
表	CREATE TABLE	DROP TABLE	ALTER TABLE
视图	CREATE VIEW	DROP VIEW	无
索引	CREATE INDEX	DROP INDEX	ALTER INDEX

1.1 定义模式

在SQL中，模式定义语句如下：

CREATE SCHEMA <模式名> AUTHORIZATION <用户名>;

【例1.1】为用户WANG定义一个学生-课程模式S-T：
CREATE SCHEMA ”S-T” AUTHORIZATION WANG;

1.2 模式删除

在SQL中，模式删除语句如下：

DROP SCHEMA <模式名> <CASCADE|RESTRICT>;

【例1.2】删除模式ZHANG和其中定义的表TAB1；
DROP SCHEMA ZHANG CASCADE;

2. 基本表的定义，删除与修改

2.1 定义基本表

在SQL中，定义基本表语句如下：

CREATE TABLE <表名> ( <列名> <数据类型>[列级完整性约束条件],
　　　　　　　　　　　[列名> <数据类型>[列级完整性约束条件],
　　　　　　　　　　　…
　　　　　　　　　　　<表级完整性约束条件>]);

【例 2.1】建立学生选课表SC， 表©。
CREATE TABLE SC
　(Sno CHAR(9), 　　　　　 //定义Sno，数据类型为CHAR，字节不多于9
　Cno CHAR(4),
　Grade SMALLINT, 　　　　//定义Grade，数据类型为SMALLINT
　PRIMARY KEY(Sno, Cno), //主码由两个属性构成，必须作为表级完整性进行定义
　FOREIGE KEY (Sno) REFERENCES Student(Sno),
//表级完整性定义，Sno为外码，被参照表为Student
　FOREIGE KEY (Cno) REFERENCES Course(Cno),
//表级完整性定义，Cno为外码，被参照表为Course
);

2.2 修改基本表 &2.3 删除基本表 &2.4索引的建立与删除

略

3．数据查询

其一般格式为：

SELECT [ALL|DISTINCT] <目标列表达式> [，<目标列表达式>] …
FROM <表名或视图名> [,<表名或视图名>] |(<SELECT语句>)[AS]<别名>
[WHERE <条件表达式>]
[GROUP BY <列名1> [HAVING <条件表达式>]]
[ORDER BY <列名2> [ASC|DESC]];

含义为：

• 根据WHERE子句的表达式从FROM子句指定的表中找出满足条件的元组，再按照SELECT子句中的目标表达式选出元组中的属性值形成结果表。
• 如果有GROUP BY表达式，则将结果按照<列名1>分组。HAVING 为输出指定条件的组。
• 如果有ORDER BY表达式，则将结果按照 <列名2>升序或者降序。

3.1 单表查询：仅涉及一个表的查询

WHERE常用的查询条件

查询条件	谓词
比较	=,>,<,>=,<=,!=,<>,!>,!<;NOT加上述比较符号
确定范围	BETWEEN AND, NOT BETWEEN AND
确定集合	IN, NOT IN
字符匹配	LIKE,NOT LIKE（%任意长度；_单个字符）
空值	IS NULL, IS NOT NULL
多重条件（逻辑运算）	AND, OR, NOT

[例 3.1] 查询年龄在20岁以下的学生姓名和年龄
SELECT Sname, Sage
　FROM Student
　WHERE Sage<20;

3.2　连接查询：涉及两个表以上的查询

[例3.2.1：等值与非等值连接查询] 查询每个学生及其选修课程的情况
SELECT Student,SC. 　　　　　　　 / /若把列中重复的属性列去掉改为自然连接：则为** 　Student.Sno,Sname,Ssex,Sage,Sdpet,Cno,Grade 　
　FROM Student, SC　　　　　　　　//容易混淆时，属性名前应该加前缀
　WHERE Student.Sno=SC.Sno;　　//将Student与SC中同一学生的元组连接起来

[例3.2.2：等值与非等值连接查询] 查询选修2号课程且成绩在90分以上的所有学生的学号和姓名
SELECT Student.Sno,Sname
　FROM Student,SC
　WHERT Student.Sno=SC.Sno AND SC.Cno=’2’AND SC.Grade>90;

[例3.2.3：自身连接] 查询每一门课的间接先修课（即先修课的先修课）
SELECT FIRST.Cno,SECOND Cpno //注意要编号
　FROM Course FIRST.Course SECOND
　WHERE FIRST.Cpno=SECOND.Cno;

[例3.2.4：外连接：将不满足条件的元组作为结果输出] 改进例3.2.1
SELECT Student.Sno,Sname,Ssex,Sage,Sdpet,Cno,Grade
　FROM Student LEFT OUTER JOIN SC ON (Student.Sno=SC.Sno);

[例3.2.5：多表连接] 查询每个学生的学号，姓名，选修课的课程名及成绩
SELECT Student,Sno,Sname,Cname,Grade
　FROM Student,SC,Course
　WHERE Student.Sno=SC.SnoAND SC.Cno=Course.Cno;

3.3　嵌套查询：

[例3.3.1：带有IN谓词的子查询]查询“刘晨”在同一个系学习的学生
首先确定“刘晨”所在的系名
SELECT Sdept
　FROM Student
　WHERE Sname=’刘晨’；
再确定CS系的学生
SELECT Sno,Student,Sdept
　FROM Student
　WHERE Sdept=’CS’;
最后第一步查询嵌入到第二步中
SELECT Sno,Sname,Sdept
　FROM Student
　WEHRE Sdept IN
　(SELECT Sdept
　FROM Student
　WHERE Sname=’刘晨’
)；
//子查询不依赖父查询，成为不相关子查询

[例3.2.2：带有比较运算符的子查询]　是相关子查询

[例3.2.3：带有ANY(SOME)或ALL谓词的子查询] 略

[例3.2.4：带有EXISTS谓词的子查询]　带有EXISTS谓词的子查询不返回数据，只返回真（内层不为空）和假（内层为空）。

[例5：基于派生表查询 ]略

3.4 数据更新

[例3.4.1：插入数据INSERT]插入一条选课记录（‘201215128’,’1’）
INSERT
　INTO SC(Sno,Cno)
　 VALUES(‘201215128’,’1’)

[例3.4.2：修改数据UPDATE]将学生201215121的年龄改为22岁

UPDATE Student
　 SET Sage=22
　 WHERE Sno=’201215121’;
　
[例3.4.3：删除数据DELETE]删除学号为201215128的学生记录
DELETE
　FROM Student
　WHERE Sno=’201215128’;

4．视图

略