数据查询
单表查询
1、选择表中的若干列
(3)查询经过计算的值
SELECT子句的<目标列表达式>不仅可以是表中的属性列,也可以是表达式。
①<目标列表达式>为算术表达式
例:查询学生的出生日期
代码:
SELECT Sname,2021-Sage
FROM Student;
查询结果:
注意:在SQL中,计算表达式查询结果的列名为表达是本身,而在SQL SERVER中,计算表达式的列名无。
本例有些不好的地方在于,Sage本身每年在变化,因此该查询过程复杂,仍需改变,因此在创建表示,可以直接添加出生日期该属性列,方便操作。
②<目标列表达式>为字符串常量、函数
例:查询全体学生的姓名、出生年份和所在院系,要求用小写字母表示系名。
代码:
SELECT Sname,'Year Of Birth',2021-Sage,LOWER(Sdept)
FROM Student;
查询结果:
若要输出结果为大写,其函数为UPPER()
代码:
SELECT Sname,'Year Of Birth',2021-Sage,UPPER(Sdept)
FROM Student;
查询结果:
(4)指定别名
用户可以通过指定别名来改变查询结果的列标题,这对于含算术表达式、常量、函数名的目标列表达式尤为有用。
例:
代码:
SELECT Sname NAME,'Year Of Birth' BIRTH,2021-Sage BIRTHDAY,UPPER(Sdept) DEPARTMENT
FROM Student;
查询结果:
此时无列名的属性名就有了名字了!
2、选择表中的若干元组
(1)消除取值重复的行
两个本来并不相同的元组在投影到指定的某些列上后,可能会变成相同的行。可以用DISTINCT消除他们。
例子:查询选修了课程的学生学号
若不加DISTINCT:
代码:
SELECT Sno
FROM SC;
查询结果:
若使用DISTINCT:
代码:
SELECT DISTINCT Sno
FROM SC;
查询结果:
注意:若没有指定DISTINCT关键字,则默认为ALL,既保留结果表中取值重复的行。
(2)查询满足条件的元组
查询满足指定条件的元组可以通过WHERE子句实现。
表:WHERE子句常用的查询条件
查询条件 | 谓词 |
---|---|
比较 | =, >, <, >=, <=, !=, <>, !>, !<; NOT + 上述比较运算符 |
确定范围 | BETWEEN AND, NOT BETWEEN AND |
确定集合 | IN, NOT IN |
字符匹配 | LIKE, NOT LIKE |
空值 | IS NULL, IS NOT NULL |
多重条件(逻辑运算) | AND, OR, NOT |
注意:=NULL 是错误表达式
①比较大小
用于进行比较的运算符一般包括=(等于), >(大于), <(小于), >=(大于等于), <=(小于等于), !=或<>(不等于), !>(不大于), !<(不小于)。
例1:查询计算机科学系的学生名单
代码:
SELECT Sname
FROM Student
WHERE Sdept='CS';
查询结果:
例2:查询所有年龄在20岁以下的学生姓名及其年龄
代码:
SELECT Sname,Sage
FROM Student
WHERE Sage<20;
查询结果:
例3:查询成绩不及格的学生的学号
代码:
SELECT DISTINCT Sno
FROM SC
WHERE Grade<60;
查询结果:
②确定范围
BETWEEN AND:BETWEEN后是范围的下界(即低值) ,AND后是范围的上界(即高值)。
例1:查询年龄在20~30之间(包括20,30)的学生姓名、系别和年龄
代码:
SELECT Sname,Sdept,Sage
FROM Student
WHERE Sage BETWEEN 20 AND 30;
查询结果:
例2:查询年龄不在20~30之间的学生姓名、系别和年龄
SELECT Sname,Sdept,Sage
FROM Student
WHERE Sage NOT BETWEEN 20 AND 30;
查询结果:
③确定集合
谓词 IN (NOT IN)可以用来查找属性值属于(不属于)指定集合的元组
例1:查询计算机科学系、数学系和信息系学生的姓名和性别
代码:
SELECT Sname,Ssex
FROM Student
WHERE Sdept IN ('IS','MA','IS');
查询结果:
例2:查询计算机科学系、数学系和信息系学生的姓名和性别
代码:
SELECT Sname,Ssex
FROM Student
WHERE Sdept NOT IN ('IS','MA','IS');
--使用OR语句
SELECT Sname,Ssex
FROM Student
WHERE Sdept='IS' OR Sdept='MA' OR Sdept='IS';
查询结果:
④字符匹配
谓词LIKE可以用来进行字符串匹配。语法格式为
NOT LIKE'<匹配串>' [ESCAPE'<换码字符>']
匹配串是完整的字符串:
例:查询学号为001的学生的详细情况
代码:
SELECT *
FROM Student
WHERE Sno like'001';
代码等同于:
SELECT *
FROM Student
WHERE Sno='001';
查询结果:
此时NOT LIKE 可用 != 或 <>代替
匹配串中包含通配符:
通配符 | 作用 |
---|---|
%(百分号) | 代表任意长度**(长度可以为0)**的字符串 |
_(下横线) | 代表任意单个字符 |
例1:查询所有姓刘的学生的姓名、学号和性别
代码:
SELECT Sname,Sno,Ssex
FROM Student
WHERE Sname LIKE'刘%';
例2:查询姓“欧阳”且全名为三个汉字的学生的姓名
SELECT Sname
FROM Student
WHERE Sname LIKE'欧阳_';
查询姓“欧阳”且全名为四个汉字的学生的姓名
SELECT Sname
FROM Student
WHERE Sname LIKE'欧阳__';
注意:此时显示结果包含汉字数目小于题目中数目的搜索项,这是和标准SQL不同的地方。
例3:查询名字中第二个字为“阳”的学生的姓名和学号
代码:
SELECT Sname,Sno
FROM Student
WHERE Sname LIKE'_阳%';
查询结果:
例4:查询所有不姓刘的学生的姓名、学号和性别
代码:
SELECT Sname,Sno,Ssex
FROM Student
WHERE Sname NOT LIKE'刘%';
查询结果:
当所查询的字符串本身就含有通配符%或_,可使用ESCAPE '<换码字符>'短语对通配符进行转义。
例1:查询DB_Design课程的课程号和学分
代码:
SELECT Cno,Ccredit
FROM Course
WHERE Cname LIKE'DB\_Design' ESCAPE'\';
查询结果:
例2:查询以“DB_”开头,且倒数第三个字符为i的课程的详细情况
代码:
SELECT *
FROM Course
WHERE Cname LIKE'DB\_%i__' ESCAPE'\';
查询结果:
⑤涉及空值的查询
注意:“IS NULL” 不能用 “=NULL”代替
例1:查询所有有成绩的学生学号和课程号
代码:
SELECT Sno,Cno
FROM SC
WHERE Grade IS NOT NULL;
查询结果:
例2:某些学生选修课后没有参加考试,所以有选课记录,但没有考试成绩,查询缺少成绩的学生的学号和相应的课程号
代码:
SELECT Sno,Cno
FROM SC
WHERE Grade IS NULL;
查询结果:
⑥多重条件查询
逻辑运算符AND和OR可用来连接多个查询条件,AND的优先级高于OR,但用户可以用括号改变优先级。
例:查询计算机科学系年龄在20岁以下的学生姓名
代码:
SELECT sname
FROM Student
WHERE Sdept='CS' AND Sage<20;
查询结果:
3、ORDER BY子句
用户使用ORDER BY子句对查询结果按照一个或多个属性列的升序(ASC)或降序(DESC)排列,默认值为升序。
例1:查询选修了3号课程的学生的学号及其成绩,查询结果按分数的降序排列
代码:
SELECT Sno,Grade
FROM SC
WHERE Cno=3
ORDER BY Grade DESC;
查询结果:
例2:查询全体学生情况,查询结果按所在系的系号升序排列,同一系中的学生按年龄降序排列
代码:
SELECT *
FROM Student
ORDER BY Sdept,Sage DESC;
查询结果:
对于空值,排序时显示的次序有具体系统实现来决定。各个系统的实现可以不同,只要保持一致就行。
4、聚集函数
聚集函数 | 作用 |
---|---|
COUNT(*) | 统计元组个数 |
COUNT( [DISTINCT\ALL] <列名>) | 统计一列中值的个数 |
SUM( [DISTINCT\ALL] <列名>) | 计算一列值的总和 (此列必须是数值型) |
AVG( [DISTINCT\ALL] <列名>) | 计算一列值的平均值(此列必须是数值型) |
MAX( [DISTINCT\ALL] <列名>) | 求一列值中的最大值 |
MIN([DISTINCT\ALL] <列名>) | 求一列值中的最小值 |
如果指定DISTINCT 短语,则表示在计算时要取消指定列中的重复值。如果不指定DISTINCT短语或指定ALL短语(ALL 为默认值),则表示不取消重复值。
例1:查询学生总人数。
代码:
SELECT COUNT(Sno) --COUNT(*)
FROM Student;
查询结果:
例2:查询选修了课程的学生人数。
代码:
SELECT COUNT(DISTINCT Sno)
FROM SC;
查询结果:
学生每选修门课,在sc中都有一条相应的记录。一个学生要选修多门课程, 为避免重复计算学生人数,必须在COUNT函数中用DISTINCT短语。
例3:计算选修1号课程的学生平均成绩。
代码:
SELECT AVG(Grade)
FROM SC
WHERE Cno=1;
查询结果:
例4:查询选修1号课程的学生最高成绩。
代码:
SELECT MAX(Grade)
FROM SC
WHERE Cno=1;
查询结果:
例5:查询学生001选修课程的总学分数
代码:
SELECT SUM(Ccredit)
FROM SC,Course
WHERE Sno='001' AND SC.Cno=Course.Cno;
查询结果:
注意,WHERE子句中是不能用聚集函数作为条件表达式的。聚集函数只能用干SELECT子句和GROUP BY中的HAVING子句。
5、GROUP BY子句
GROUP BY子句将查询结果按某一列或多列的值分组,值相等的为一组。
对查询结果分组的目的是为了细化聚集函数的作用对象。如果未对查询结果分组,牙共函数将作用于整个查询结果,分组后聚集函数将作用于好组,即每一组都有一个函数值。
例1:求各个课程号及相应的选课人数。
代码:
SELECT Cno,COUNT(Sno)
FROM SC
GROUP BY Cno;
查询结果:
该语句对查询结果按Cno的值分组,所有具有相同Cno值的元组为一组,然后对每组作用聚集函数COUNT进行计算,以求得该组的学生人数。
如果分组后还要求按一定 的条件对这些组进行筛选,最终只输出满足指定条件的组,则可以使用HAVING短语指定筛选条件。
例2:查询选修了三门以上课程的学生学号。
代码:
SELECT Sno
FROM SC
GROUP BY Sno
HAVING COUNT(*)>3;
查询结果:
WHERE子旬与HAVING短语的区别在于作用对象不同。WHERE 子句作用于基本表或视图,从中选择满足条件的元组。HAVING短语作用于组,从中选择满足条件的组。
例3:查询平均成绩大于等于90分的学生学号和平均成绩。
代码:
SELECT Sno,AVG(Grade)
FROM SC
GROUP BY Sno
HAVING AVG(Grade)>=90; --不可使用WHERE AVG(Grade)>=90
查询结果:
OVER!!!