2. SQL 数据查询

数据查询

1. SELECT 语句一般格式

语句格式：

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

2. 单表查询

** 功能： ** 对一个表的内容进行查询

2.1. 选择表中的若干列

（1）查询指定列

** 格式：** 在SELECT 后面指定列名，FROM后面指定列所在的表名。

【例】查询全体学生的学号与姓名。

SELECT Sno,Sname
FROM Student;

【例】查询全体学生的姓名、学号、所在系。

SELECT Sname,Sno,Sdept
FROM Student;

（2）查询全部列

** 功能：**选出表中所有属性列。

** 格式：*在SELECT关键字后面列出所有列名或将<目标列表达式>指定为。

【例】查询全体学生的详细记录。

SELECT  Sno,Sname,Ssex,Sage,Sdept
FROM Student;
//两种方式
SELECT  *FROM Student;

（3）查询经过计算的值

** 功能：**选出表中指定的属性列，并经过计算后输出。

** 格式：**在SELECT子句的<目标列表达式>可以为：

算数表达式
字符串常量
函数
列别名

【例】查全体学生的姓名及其出生年份。这里假定目前年份是2004年。

SELECT Sname,2004-Sage
FROM Student;

Sname	2004-Sage
李永	1984
刘成	1985
张力	1986

【例】查询全体学生的姓名、出生年份和所有系，要求用小写字母表示所有系名，这里假定目前年份是2004年。

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

Sname	Year of Birth:	(1)	(2)
李永	Year of Birth:	1984	cs
刘成	Year of Birth:	1985	ma
张力	Year of Birth:	1986	is

【例】使用列别名改变查询结果的列标题。

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

NAME	BIRTH	BIRTHDAY	DEPARTMENT
李永	Year of Birth:	1984	cs
刘成	Year of Birth:	1985	ma
张力	Year of Birth:	1986	is

####2.2. 选择表中的若干元组（行）

（1）关键词 DISTINCT 消除取值重复的行，若没有指定DISTINCT关键词，则缺省为ALL。

【例】查询选修了课程的学生学号。

SELECT Sno FROM SC;
/*等价于：*/
SELECT ALL  Sno  FROM SC;

Sno
201215121
201215121
201215122

【例】查询选修了课程的学生学号。指定DISTINCT关键词，去掉表中重复的行。

SELECT DISTINCT Sno
FROM SC;

Sno
201215121
201215122

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

查询条件	谓词
比较	=，>,<,>=,<=,!=,<>,!>,!<;NOT+上述比较运算符
确定范围	BETWEEN AND,NOT BETWEEN AND
确定集合	IN,NOT IN
字符匹配	LIKE,NOT LIKE
空值	IS NULL,IS NOT NULL
多次条件（逻辑运算）	AND,OR,NOT

a. 比较

【例】查询计算机科学系全体学生的名单。

SELECT Sname
FROM Student
WHERE Sdept='CS';

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

SELECT Sname,Sage
FROM Student
WHERE Sage < 20;

b. 确定范围

【例】查询年龄在20~23岁（包括20岁和23岁）之间的学生的姓名，系和年龄。

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

【例】查询年龄不在20~23岁之间的学生姓名、系别和年龄。

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

c. 确定集合

【例】查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名和性别。

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

【例】查询既不是信息系、数学系，也不是计算机科学系的学生的姓名和性别。

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

d. 字符匹配

** 【注意】 % 表示任意长度的字符, _ ** 表示一个字符。

【例】查询学号为201215121的学生的详细情况。

SELECT *
FROM  Student
WHERE  Sno LIKE '201215121';
/*等价于：*/
SELECT  *
FROM  Student
WHERE Sno = '201215121';

【例】查询所有姓刘学生的姓名、学号和性别。

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

【例】查询姓”欧阳”且全名为三个汉字的学生的姓名。

SELECT Sname
FROM   Student
WHERE  Sname LIKE '欧阳_';

** 【注意】使用换码字符’’将通配符转义为普通字符**

ESCAPE ‘＼’ 表示“ ＼” 为换码字符

【例】查询DB_Design课程的课程号和学分。

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

【注意】这里使用ESCAPE ‘ ' 表示其中的’_’并不是占位符，而是实际存在的符号。

e. 空值

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

SELECT Sno,Cno
FROM  SC
WHERE  Grade IS NULL;

【例】查所有有成绩的学生学号和课程号。

SELECT Sno,Cno
FROM  SC
WHERE  Grade IS NOT NULL;

f. 多重条件（逻辑运算）

** 逻辑运算符**

AND和 OR来联结多个查询条件
AND的优先级高于OR
可以用括号改变优先级

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

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

【例】查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名和性别。

SELECT Sname,Ssex
FROM Student
WHERE Sdept IN ( 'IS','MA','CS' );
/*可改写为：*/
SELECT Sname,Ssex
FROM   Student
WHERE  Sdept= 'IS' OR Sdept= 'MA' OR Sdept= 'CS';

2.3. ORDER BY 子句

可以按一个或多个属性列排序：

升序：ASC；
降序：DESC；
缺省值为升序；

当排序列含空值时：（空值默认为最大值）

ASC：排序列为空值的元组最后显示
DESC：排序列为空值的元组最先显示

【例】查询选修了3号课程的学生的学号及其成绩，查询结果按分数降序排列。

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

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

SELECT  *
FROM  Student
ORDER BY Sdept,Sage DESC;

2.4. 聚合函数

计数
COUNT（[DISTINCT|ALL] *）
COUNT（[DISTINCT|ALL] <列名>）
计算总和
SUM（[DISTINCT|ALL] <列名>）
计算平均值
AVG（[DISTINCT|ALL] <列名>）
最大最小值
MAX（[DISTINCT|ALL] <列名>）
MIN（[DISTINCT|ALL] <列名>）

【注意】DISTINCT 代表去掉重复。

【例】查询学生总人数。

SELECT COUNT(*)
FROM  Student;

【例】查询选修了课程的学生人数。

SELECT COUNT(DISTINCT Sno)
FROM SC;

【例】计算2号课程的学生平均成绩。

SELECT AVG(Grade)
FROM SC
WHERE Cno= '2';

【例】查询学生201215121选修课程的总学分数。

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

2.5. GROUP BY子句

** GROUP BY子句分组：细化聚集函数的作用对象**

未对查询结果分组，聚集函数将作用于整个查询结果
对查询结果分组后，聚集函数将分别作用于每个组
作用对象是查询的中间结果表
按指定的一列或多列值分组，值相等的为一组

** HAVING短语与WHERE子句的区别：**

作用对象不同
WHERE子句作用于基表或视图，从中选择满足条件的元组
HAVING短语作用于组，从中选择满足条件的组。

【例】求各个课程号及相应的选课人数。

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

【例】查询选修了2门以上课程的学生学号。

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

3. 连接查询

** 连接查询：同时涉及多个表的查询**

**连接条件或连接谓词：用来连接两个表的条件 **

** 连接条件一般格式：**

[<表名1>.]<列名1> <比较运算符> [<表名2>.]<列名2>
[<表名1>.]<列名1> BETWEEN [<表名2>.]<列名2> AND [<表名2>.]<列名3>

** （1）等值与非等值连接查询 **

** a. 等值连接**

【例】查询每个学生及其选修课程的情况。

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

** b. 自然连接：等值连接中去掉目标列中重复的属性列称为自然连接。**

【例】对上例用自然连接完成。

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

** c. 自身连接**

自身连接：一个表与其自己进行连接
需要给表起别名以示区别
由于所有属性名都是同名属性，因此必须使用别名前缀

【例】查询每一门课的间接先修课（即先修课的先修课）

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

**d. 外连接 **

** 普通连接与外连接的区别：**普通连接操作值输出满足连接条件的元组。

** 外连接：** 操作以指定表为连接主体，将主题表中不满足连接条件的元组一并输出。

左外连接：LEFT OUT JOIN SC ON

  关键字从左表（table1）返回所有的行，即使右表（table2）中没有匹配。如果右表中没有匹配，则结果为 NULL。

右外连接：RIGHT OUT JOIN SC ON

  关键字从右表（table2）返回所有的行，即使左表（table1）中没有匹配。如果左表中没有匹配，则结果为 NULL。

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

SELECT Sno,SC.Cno,Grade,Course.Cno,Cname,Cpno,Ccredit
FROM  SC LEFT JOIN Course ON (SC.Cno=Course.Cno);
/*LEFT JOIN 与 LEFT OUTER JOIN结果相同*/
SELECT Sno,SC.Cno,Grade,Course.Cno,Cname,Cpno,Ccredit
FROM  SC LEFT OUTER JOIN Course ON (SC.Cno=Course.Cno);

** e. 多表连接**

【例】查询每个学生的学号、姓名、选修的课程名及成绩。

SELECT Student.Sno,Sname,Cname,Grade
FROM    Student,SC,Course  /*多表连接*/
WHERE Student.Sno = SC.Sno
and SC.Cno = Course.Cno;

** f. 嵌套查询**

一个SELECT-FROM-WHERE语句称为一个查询块。
将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询。

【例】

 SELECT Sname/*外层查询/父查询*/
 FROM Student
 WHERE Sno IN
(SELECT Sno    /*内层查询/子查询*/
 FROM SC
 WHERE Cno= '2');

** 带有IN谓词的子查询**

【例】查询与“刘晨”在同一个系学习的学生。此查询要求可以分步来完成。

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

【例】查询选修了课程名为“信息系统”的学生学号和姓名。

SELECT Sno,Sname /*③ 最后在Student关系中取出Sno和Sname*/
FROM    Student
WHERE Sno  IN
(  SELECT Sno  	/*② 然后在SC关系中找出选修了3号课程的学生学号*/
 FROM    SC
 WHERE  Cno IN
    ( SELECT Cno   /*① 首先在Course关系中找出 “信息系统”的课程号,为3号*/
      FROM Course
      WHERE Cname= '信息系统'
 )
);

SQL

2. SQL 数据查询

http://yuukichen.github.io/2024/07/05/sql数据查询/

Author

Yuukichen

Posted on

July 5, 2024

Licensed under

3. SQL 中基本表更新 Previous

1. SQL概述 Next