简明SQL基础教程
前言
本文以学生选课系统数据库为例介绍SQL语言的基本语法。首先在Microsoft Access中创建关系如下的如下学生选课系统数据库:
该数据库包含四张表:Stus学生信息表、StuClass学生选课课程信息表、Depts学生所属院系信息表、Classes课程信息表。其中个表中包含的信息分别为:StuID学号、StuName学生姓名、Sex性别、Age学生年龄、Dept学生所在院系、ClassID课号、Grade成绩、Director系主任、Buliding院系所属教学楼、ClassName课程名、Credit课程学分、Teacher授课教师、PreClassID先修课课程号(假设一门课只有一个先修课)。本文将以该数据库为例对SQL语言进行介绍。
1.1 SQL语言概述
1.1.1 关系型数据库的标准语言
1.SQL:
SQL(Structured Query Language)结构化查询语言,是关系型数据库的标准语言。
2.SQL语言的功能:
对数据库进行查询。SQL集数据定义语言、数据操纵语言、数据控制语言于一体,可独立完成数据库生命周期中中的全部活动。
3.SQL语言的特点:
使用简单,只要提出“做什么”而无需指明“怎么做”。例如:在学生选课数据库中查询全体学生的全部列,可以在Access中通过如下的SQL语句进行查询(在Access中执行SQL语言是通过创建查询来完成的。):
SELECT * FROM Stus;
其中“ * ”表示“全体”。
4.在Access中SQL语句(查询)的执行结果:
- 查询的结果是“一张表”,但它不是基本表,它只是视图。
- 基本表(Base Table)是本身独立存在的表,可带有索引。
- 视图是虚表,是从一个或几个基本表导出的表。它本身不独立存储在数据库中,即在数据库中只储存视图的定义(如SQL语句)而不储存试图对应的数据。
- 基本表和视图都是关系,SQL语言可以同时对基本表和视图进行操作。
- 可以在视图上再定义视图。
1.2 SQL基础语句
1.2.1 数据查询语句SELECT
- SQL提供了SELECT语句进行数据库的查询。
- SELECT语句的一般格式:
SELECT [ALL/DISTINCT] <列表达式1> [AS 别名1], <列表达式2> [AS 别名2], ..., <列表达式n> [AS 别名n]
FROM <表名或视图名1> [别名1], <表名或视图名2> [别名2], ..., <表名或视图名n> [别名n]
[WHERE <条件表达式>]
[GROUP BY <列名> [HAVING <条件表达式>]]
[ORDER BY <列名> [DESC/ASC]];
- 常用查询条件表达方法:
查询条件 | 谓词 |
---|---|
比较 | =, >, <, >=, <=, <>, NOT |
确定范围 | BETWEEN AND, NOT BETWEEN AND |
确定集合 | IN, NOT IN |
字符匹配 | LIKE, NOT LIKE |
空值 | IS NULL, IS NOT NULL |
多重事件 | AND, OR |
1.SELECT语句的列选择:
- 查询全体学生的学号与姓名:
SELECT StuID, StuName FROM Stus;
- 查询全体学生的姓名、学号与所在系:
SELECT StuID, StuName, Dept FROM Stus;
- 查询全体学生的全部列:
SELECT * FROM Stus;
- 查询经过计算的值:
SELECT StuName, 2023 - Age FROM Stus;
- 查询结果:第二列不是一个列名而是一个表达式,进行相关计算。
- 用AS重新命名列名称:
SELECT StuName, 2023 - Age AS BirthYear FROM Stus;
- 查询结果:第二列的列名为BirthYear。
2.SELECT语句的行选择:
- 消除取值重复的行:两个不完全相同的行投影到指定的列上后就有可能变成相同的列。
- 例如:
SELECT StuID FROM Stus;
若不进行去重操作则实际等效于:SELECT ALL StuID FROM StuClass;
- 在SQL语句中可用DISTINCT短语去掉结果中相同的行:
SELECT DISTINCT StuID FROM StuClass;
- 例如:
- 查询满足条件的元组:
- 关系条件:
(1)查询生物系全体学生的名单:
SELECT StuName FROM Stus
WHERE Dept = '生物'
(2)查询所有年龄在20岁以下的学生姓名及年龄:
# 有两种方法
# 方法一:
SELECT StuName, Age FROM Stus
WHERE Age < 20;
# 方法二:
SELECT StuName, Age FROM Stus
WHERE NOT Age >= 20;
(3)查询考试成绩有不及格的学生的学号:
# 使用DISTINCT短语,当一个学生有多门课程不及格时,学号只列一次
SELECT DISTINCT StuID FROM StuClass
WHERE Grade < 60;
- BETWEEN…AND…谓词:
(1)查询年龄在20~22岁(包括20和22岁)之间的学生的姓名、所在系和年龄:
SELECT StuName, Dept, Age FROM Stus
WHERE Age BETWEEN 20 AND 22;
(2)查询年龄不在20~22岁的学生的姓名、所在系和年龄:
SELECT StuName, Dept, Age FROM Stus
WHERE AGE NOT BETWEEN 20 AND 22;
- IN谓词:
(1)查询生物学、医学系和计算机系学生的姓名和性别:
SELECT StuName, Sex FROM Stus
WHERE Dept IN ('生物', '医学', '计算机');
(2)查询既不是生物,也不是计算机,也不是以学习的学生的姓名和性别:
SELECT StuName, Sex FROM Stus
WHERE Dept NOT IN ('生物', '医学', '计算机');
- 用于字符匹配的LIKE谓词:谓词LIKE可用来表示字符串的匹配条件,* 表示任意多个字符,?表示任意一个字符(有些系统分别用 % 和 - 表示)。
(1)查询所有姓刘的学生的姓名、学号和性别:
SELECT StuName, StuID, Sex FROM Stus
WHERE StuName LIKE '刘*';
(2)查询所有姓王且全名是三个字的学生的姓名、学号和性别:
SELECT StuID, StuName, Sex FROM Stus
WHERE StuName LIKE '王??';
(3)查询名字的第二个字为“阳”的同学的姓名、学号和性别:
SELECT StuName, StuID, Sex FROM Stus
WHERE StuName LIKE '?阳*';
(4)查询所有不姓刘的学生的姓名、学号和性别:
SELECT StuName, StuID, Sex FROM Sex
WHERE StuName NOT LIKE '刘*';
- 涉及空值的查询
(1)查询缺少考试成绩的学生的学号和相应的课号:
SELECT StuID, ClasID FROM StuClas
WHERE Grade IS NULL;
(2)查询所有有成绩的学生的学号和相应的课号:
SELECT StuID, ClasID FROM StuClas
WHERE Grade IS NOT NULL;
- 多重条件查询
(1)查询生物系年龄在20岁以下的学生姓名:
SELECT StuName FROM Stus
WHERE Dept = '生物' AND Age < 20;
(2)查询生物系、医学系和计算机系学生的姓名和性别:
SELECT StuName, Sex FROM Stus
WHERE Depts = '生物' OR Depts = '医学' OR Depts = '计算机';
- 查询结果排序:用 ORDER BY 字句对查询结果按一个或多个属性列升序(ASC,默认)或降序(DESC)排序
- 例:
(1)查询选修了C003号课程的学生的学号和成绩,查询结果按分数的降序排列:
SELECT StuID, Grade FROM StuClas
WHERE ClassID = 'C003'
ORDER BY Grade DESC;
(2)查询全体学生情况,按所在系系名升序排列,统一院系的学生按年龄降序排列:
SELECT * FROM Stus
ORDER BY Depts, Age DESC;
- 使用集函数进行查询:
- SQL的主要集函数:
集函数 | 功能 |
---|---|
COUNT(*) | 统计元组个数 |
COUNT(列名) | 统计一列中值的个数 |
SUM(列名) | 求一列值的总和 |
AVG(列名) | 求一列值的平均值 |
MAX(列名) | 求一列值中的最大值 |
MIN(列名) | 求一列值中的最小值 |
- 例:
(1) 统计学生总人数
SELECT COUNT(*) FROM Stus;
(2)计算C001课程的平均成绩、最高分:
SELECT AVG(Grade), MAX(Grade) FROM StuClas
WHERE ClassID = 'C001';
1.2.2 对查询结果进行分组统计的GROUP BY短语
1. GROUP BY 语句的用法:
- 使用GROUP BY 子句可对查询结果按一个或多个属性分组(一个或多个列值相等的为一组),然后对每一组分别进行集函数的统计
- 例:
(1)求各个课程号及相应的选课人数:
SELECT ClassID, COUNT(*) FROM StuClas
GROUP BY ClassID;
技巧:GROUP BY 后面的内容一般就是SELECT后列标题列表中除了集函数外的内容
2. GROUP BY 短语使用HAVING子句设置筛选条件:
- 例:
(1)查询选修了三门及以上课程的学生学号:
#查询每个学生选了几门课
SELECT StuID, COUNT(*) FROM StuClas
GROUP BY StuID
#筛选出第二列>= 的行
HAVING COUNT(*) >= 3;
(2)查询平均成绩>=90分的学生学号和平均成绩:
SELECT StuID, AVG(Grade) FROM StuClas
GROUP BY StuID
HAVING AVG(Grade) >= 90;
- GROUP BY 短语一般进行针对一个表的查询。
1.2.3 连接查询
1. 连接查询的概念:
- 若一个查询同时涉及两个或两个以上的表,则称为连接查询。连接查询的基础是关系之间的笛卡尔积和连接运算。连接查询是关系型数据库中最主要的查询。
2. 自然连接:
- 例:
(1)查询每个学生及其选修课程的情况:
#方法一:
SELECT Stus.*, StuClas.* FROM Stus, StuClas
WHERE Stus.StuID = StuClas.StuID;
#方法二:用自然连接完成
#StuID在两个表中都有,故要写出表名前缀
SELECT Stu.StuID, StuName, Sex, Age, Dept, ClassID, Grade FROM Stus, StuClas
WHERE Stus.StuID = StuClas.StuID;
- 技巧:这类链接查询的WHRE条件的内容一般都是由连接谓词和其他限定条件形成的。
3. 复合条件连接:
- 例:
(1)查询选修了C002课程且成绩在90分以上的所有学生的学号和姓名:
#AND为连接谓词,AND后接其他限定条件
SELECT Stus.StuID, StuName FROM Stus, StuClas
WHERE Stus.StuID = StuClas.StuID
AND StuClas.ClassID = 'C002'
AND StuClas.Grade >= 90;
(2)查询每个学生的学号、姓名、选修的课程名、学分和成绩:
SELECT Stus.StuID, StuName, ClassName, Credit, Grade FROM Stus, Classes, StuClas
WHERE Stus.StuID = StuClas.StuID
AND StuClas.ClassID = Classes.ClassID;
4. 自身连接:
- 例:
(1)查询每一门课的间接先修课:
#创建两个Classes的查询表,将其分别命名为First, Second
SELECT FIRST.ClassID, Second.PreClassID FROM Classes First, Classes Second
WHERE First.PreClassID = Second.ClassID;
- 用INNER JOIN子句实现连接:
- INNER JOIN:只返回两个表中联结字段相等的行
- LEFT JOIN(左连接):返回包括左表中的所有记录和右表中联结字段相等的记录
- RIGHT JOIN(右连接):返回包括右表中的所有记录和左表中联结字段中相等的记录
(1)用INNER JOIN 子句实现查询每个学生及其选修课的情况:
#举一反三:可修改代码为SELECT <要查询的列标题>或*
#注意:两表非同时有的记录不能连接
SELECT * FROM StuClas
INNER JOIN ON StuClas.StuID = Stus.StuID;
(2)查询每个学生及其选修课的情况,要求将未选课的学生列出,其选课情况保持空白:
#方法一:
SELECT * FROM StuClas
RIGHT JOIN ON StuClas.StuID = Stus.StuID
#方法二:
SELECT * FROM StuClas
LEFT JOIN ON Stus.StuID = StuClas.StuID
1.2.4 嵌套查询
1. 概念:
- 一个SELECT—FROM—WHERE语句称为一个查询块。将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询
2.实例:
- 例:
(1)查询选修了C002课程的学生名单:先在StuClas表中查询选修了C002课程的学生的学号,再在Stus表中按照查询到的学号查询学生姓名。
SELECT StuName FROM Stus
WHERE StuID IN
(SELECT StuID FROM StuClas
WHERE ClassID = 'C002');
(2)查询选修了课程的学生人数:
SELECT COUNT(*) FROM
(SELECT DISTINCT StuID FROM StuClas);
(3)查询与于洁在同一个系中学习的学生:
#法一:
SELECT StuID, StuName, Dept FROM Stus
WHERE Dept =
(SELECT Dept FROM Stus
WHERE StuName = '于洁');
#法二:Stus.S1 与 Stus.S2 表连接
SELECT S1.StuID, S1.StuName, S1.Dept FROM Stus S1, Stus S2
WHERE S1.Dept = S2.Dept
AND S2.StuName = '于洁';
1.2.5 含谓词的子查询
1. 子查询的谓词:
- ANY:查询子查询的某一个值。
- ALL:查询子查询中的所有值。
- EXIST:子查询存在记录。
2.实例:
- 例:
(1)查询其他系中,比生物系中某一学生年龄小的学生的姓名和年龄:
SELECT StuName, Age FROM Stus
WHERE Dept <> '生物'
AND Age < ANY (SELECT Age FROM Stus WHERE Dept = '生物');
(2)查询其他系中,比生物系中所有学生年龄都小的学生的姓名和年龄:
SELECT StuName, Age FROM Stus
WHERE Dept <> '生物'
AND Age < ALL (SELECT Age FROM Stus WHERE Dept = '生物');
1.2.6 集合查询
1.概述:
- 对多个SELECT语句的查询结果可以进行集合查询操作
- UNION:并操作。
- INTERSECT:交操作。
- EXCEPT(有些系统为MINUS):差操作。
- 注意:Access不支持INTERSECT与EXCEPT操作。
2.实例:
- 例:
(1)查询选修了C001课程或选修了C002课程的学生学号:
SELECT StuID FROM StuClass
WHERE ClassID = 'C001'
UNION
SELECT StuID FROM StuClass
WHERE ClassID = 'C002';
1.3 数据更新语句和其他SQL语句
1.3.1 数据更新语句
1. 插入(即新增)数据的SQL语句INSERT:
- INSERT语句的一般格式:
INSERT INTO <表名> [(<属性列1>, <属性列2>, ..., <属性列n>)]
VALUES (<常量1>, <常量2>, ...,<常量n>);
- 例:
(1)将一个新学生记录插入到Stus表中:
INSERT INTO Stus
VALUES ('3001', '陈东', '男', '18', '生物');
(2)插入一条选课记录:
INSERT INTO StuClass (StuID, ClassID)
VALUES ('1001', 'C001');
2. 修改数据的SQL语句UPDATE:
- UPDATE语句的一般格式:
UPDATE <表名> SET <列名1> = <表达式1>, <列名2> = <表达式2>, ...,<列名n> = <表达式n>
WHERE <条件>;
- 例:
(1)将学生’3001’的年龄改为20岁:
UPDATE Stus SET Age = 20
WHERE StuID = '3001';
(2)将所有学生的年龄增加1岁:
UPDATE Stus SET Age = Age + 1;
1.3.2 其他SQL语句
1. 删除数据行的SQL语句DELETE:
- DELETE语句的一般格式:
DELETE FROM <表名>
WHERE <条件>;
- 例:
(1)删除学号为’3001’的学生:
DELETE FROM Stus
WHERE StuID = '3001';
(2)删除所有学生的选课记录(清空StuClass表):
DELETE FROM StuClass;
2. 常用SQL语句:
SQL功能 | 动词 |
---|---|
数据查询 | SELECT |
数据定义 | CREATE, DROP, ALTER |
数据操纵 | INSERT, UPDATE, DELETE |
数据控制 | GRANT, REVOKE |
2.1 总结
- SQL(结构化查询语言)是关系型数据库的标准语言。
- SELECT语句是SQL中最重要的语句,其作用是数据查询。SELECT语句可以实现单表查询、多表连接查询、数据的计算、统计、排序、分类汇总等操作。
- INSERT语句、UPDATE语句、DELETE语句分别实现数据的插入(新增)、更新(修改)、数据行的删除,它们也是SQL中常用的语句。