例题"/>
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.视图
略
更多推荐
SQL的常用查询操作及例题
发布评论