1.数据查询
SELECT [ ALL | DISTINCT] <目标列表达式>[,<目标列表达式>] …
FROM <表名或视图名>[, <表名或视图名> ] …
[ WHERE <条件表达式> ]
[ GROUP BY <列名1> [ HAVING <条件表达式> ] ]
[ ORDER BY <列名2> [ ASC|DESC ] ];
SELECT子句:指定要显示的属性列
FROM子句:指定查询对象(基本表或视图)
WHERE子句:指定查询条件
GROUP BY子句:对查询结果按指定列的值分组,该属性列值相等的元组为一个组。通常会在每组中作用聚集函数。
HAVING短语:只有满足指定条件的组才予以输出
ORDER BY子句:对查询结果表按指定列值的升序或降序排序
1.单表查询
查询仅涉及一个表
1.选择表中的若干列
查询指定列:
查询全体学生的学号与姓名。
SELECT Sno, Sname
FROM Student;
选出所有属性列: 在SELECT关键字后面列出所有列名或 将<目标列表达式>指定为 *
SELECT Sno,Sname,Ssex,Sage,Sdept
FROM Student; 或
SELECT *
FROM Student;
查询需要计算的值
SELECT子句的<目标列表达式>可以为: 算术表达式; 字符串常量; 函数 ;列别名。
查全体学生的姓名及其出生年份
SELECT Sname,2004-Sage /*假定当年的年份为2004年*/
FROM Student;
查询全体学生的姓名、出生年份和所有系,要求用小写字母表示所有系名
SELECT Sname, 'Year of Birth:' , 2004-Sage, LOWER(Sdept)
FROM Student;
使用列别名改变查询结果的列标题:
SELECT 原名 新名,……SELECT Sname NAME, 'Year of Birth: ’ BIRTH,2000-Sage BIRTHDAY, LOWER(Sdept) DEPARTMENTFROM Student;
2.选择表中的若干元组
1.消除取值重复的行
如果没有指定DISTINCT关键词,则缺省为ALL
SELECT Sno
FROM SC;等价于:
SELECT ALL Sno
FROM SC;指定DISTINCT关键词,去掉表中重复的行
SELECT DISTINCT Sno
FROM SC;
2.查询满足条件的元组
常用查询条件:
1.比较大小
查询计算机科学系全体学生的名单
SELECT Sname
FROM Student
WHERE Sdept=‘CS’ ; 查询所有年龄在20岁以下的学生姓名及其年龄
SELECT Sname,Sage
FROM Student
WHERE Sage < 20;查询考试成绩有不及格的学生的学号。
SELECT DISTINCT Sno
FROM SC
WHERE Grade<60;
2.确定范围
BETWEEN … AND …
NOT BETWEEN … AND …
查询年龄在20~23岁(包括20岁和23岁)之间的学生的姓名、系别和年龄SELECT Sname,Sdept,Sage FROM StudentWHERE Sage BETWEEN 20 AND 23;查询年龄不在20~23岁之间的学生姓名、系别和年龄SELECT Sname,Sdept,Sage FROM StudentWHERE Sage NOT BETWEEN 20 AND 23;
3.确定集合
IN <值表>
NOT IN <值表>
查询信息系(IS)、数学系(MA)和计算机科学系(CS)学生的姓名和性别。SELECT Sname,Ssex FROM StudentWHERE Sdept IN ( 'IS','MA','CS' );查询既不是信息系、数学系,也不是计算机科学系的学生的姓名和性别。
SELECT Sname,Ssex FROM StudentWHERE Sdept NOT IN ( 'IS‘,'MA‘,'CS' );
4.字符匹配
[NOT] LIKE ‘<匹配串>’ [ESCAPE ‘ <换码字符>’]
1)匹配串为固定字符串
查询学号为201215121的学生的详细情况。SELECT * FROM Student WHERE Sno LIKE ‘201215121';
等价于: SELECT * FROM Student WHERE Sno = ‘ 201215121 ’;
若LIKE后的匹配串不含通配符,则与=等价
2)匹配串为含通配符的字符串
查询所有姓刘学生的姓名、学号和性别。SELECT Sname,Sno,SsexFROM StudentWHERE Sname LIKE ‘刘%’;查询姓"欧阳"且全名为三个汉字的学生的姓名。SELECT SnameFROM StudentWHERE Sname LIKE '欧阳_‘;查询名字中第2个字为"阳"字的学生的姓名和学号。SELECT Sname,SnoFROM StudentWHERE Sname LIKE ‘_阳%’;查询所有不姓刘的学生姓名。SELECT Sname,Sno,SsexFROM StudentWHERE Sname NOT LIKE ‘刘%’;查询名字里包含“阳”的学生的姓名和学号。SELECT Sname,SnoFROM StudentWHERE Sname LIKE ‘%阳%’;查询姓刘或者姓李的学生姓名和学号。SELECT Sname,SnoFROM StudentWHERE Sname LIKE ‘[刘李] %’
3)使用换码字符将通配符转义为普通字符
查询DB_Design课程的课程号和学分。SELECT Cno,CcreditFROM CourseWHERE Cname LIKE ‘DB\_Design’ ESCAPE ‘\’;查询以"DB_"开头,且倒数第2个字符为 i的课程的详细情况。SELECT *FROM CourseWHERE Cname LIKE 'DB\_%i_' ESCAPE ' \ ‘;ESCAPE '\' 表示“ \” 为换码字符
5.涉及空值的查询(NULL)
IS NULL 或 IS NOT NULL
“IS” 不能用 “=” 代替
某些学生选修课程后没有参加考试,所以有选课记录,但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号。SELECT Sno,CnoFROM SCWHERE Grade IS NULL
查所有有成绩的学生学号和课程号。SELECT Sno,CnoFROM SCWHERE Grade IS NOT NULL
6.多重条件查询
逻辑运算符:AND和 OR来联结多个查询条件
AND的优先级高于OR ,可以用括号改变优先级
可用来实现多种其他谓词
[NOT] IN
[NOT] BETWEEN … AND …
查询计算机系年龄在20岁以下的学生姓名。SELECT SnameFROM StudentWHERE Sdept= 'CS' AND Sage<20;查询计算机科学系(CS)、数学系(MA)和信息系(IS)学生的姓名和性别。
SELECT Sname, Ssex
FROM Student
WHERE Sdept IN ('CS ','MA ','IS')可改写为:SELECT Sname, Ssex
FROM Student
WHERE Sdept= ' CS' OR Sdept= ' MA' OR Sdept= 'IS ';
3.ORDER BY子句
可以按一个或多个属性列排序
升序:ASC;降序:DESC;缺省值为升序
当排序列含空值时
ASC:排序列为空值的元组最先显示
DESC:排序列为空值的元组最后显示
查询选修了3号课程的学生的学号及其成绩,查询结果按分数降序排列。SELECT Sno,Grade FROM SCWHERE Cno= ‘3’ORDER BY Grade DESC;查询全体学生情况,查询结果按所在系的系号升序排列,同一系中的学生按年龄降序排列。
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] <列名>)
查询学生总人数。
SELECT COUNT(*)
FROM Student; 查询选修了课程的学生人数。
SELECT COUNT(DISTINCT Sno)
FROM SC;计算3号课程的学生平均成绩。
SELECT AVG(Grade)
FROM SC
WHERE Cno= ' 3 ';查询选修3号课程的学生最高分数。
SELECT MAX(Grade)
FROM SC
WHER Cno= ‘3 ‘;查询学生201215122选修课程的总学分数。
SELECT SUM(Ccredit)
FROM SC, Course
WHERE Sno='201215122' ANDSC.Cno=Course.Cno;
5.GROUP BY子句
GROUP BY子句分组:
细化聚集函数的作用对象 如果未对查询结果分组,聚集函数将作用于整个查询结果
对查询结果分组后,聚集函数将分别作用于每个组
按指定的一列或多列值分组,值相等的为一组
求各个课程号及相应的选课人数。SELECT Cno,COUNT(Sno)FROM SCGROUP BY Cno; 查询选修了3门以上课程的学生学号。SELECT SnoFROM SCGROUP BY SnoHAVING COUNT(*) >3;查询平均成绩大于等于90分的学生学号和平均成绩SELECT Sno, AVG(Grade)FROM SCGROUP BY SnoHAVING AVG(Grade)>=90;
WHERE子句中不能用聚集函数作为条件表达式
HAVING短语与WHERE子句的区别: 作用对象不同
WHERE子句作用于基表或视图,从中选择满足条件的元组
HAVING短语作用于组,从中选择满足条件的组。
2.练习
/*1.查询课程总门数*/
select count(*)
from Course;/*2.查询专业数*/
select APPROX_COUNT_DISTINCT (Smajor) as '数目'
from Student;/*3.查询计算机科学与技术专业的学生人数*/
select count(Sno)
from Student
where Smajor='计算机科学与技术';/*4.查询计算机科学与技术专业学生的平均年龄*/
select AVG(yEAR(GETDATE())-year(sbirthdate))
from Student
where Smajor='计算机科学与技术';/*5.查询学号为20180001学生选修课程的总分*/
select sum(Grade)
from SC
where Sno='20180001';/*6.查询81001号课程的最高分数,最低分和平均分*/
select max(Grade) '最高分',MIN(grade) '最低分',avg(Grade) '平均分'
from SC
where Cno='81001';/*7.查询每门课程的课程号、最高分数,最低分和平均分(分组)*/
select Cno ,max(Grade) '最高分',MIN(grade) '最低分',avg(Grade) '平均分'
from SC
group by Cno;/*8.查询各个专业和该专业的人数(分组)*/
select Cno,count(sno) as '人数'
from SC
group by Cno;/*9.查询专业人数大于2的专业名称和人数(分组)*/
select SC.Cno,Course.Cname,count(SC.sno) as '人数'
from SC,Course
where SC.cno=Course.cno
group by SC.Cno,Course.Cname
having '人数'>'2';/*10.查询选课人数大于等于3的课程号和选课人数(分组)*/
select Cno,count(Cno) as '人数'
from SC
group by Cno
having '人数'>='3';/*11.查询平均成绩大于等于80分的学生学号和平均成绩(分组)*/
select Sno,avg(Grade)
from SC
group by Sno
having avg(Grade)>='80';/*12.查询平均成绩大于等于80分的学生学号、姓名和平均成绩(分组,连接)*/
select Student.Sno,Sname,avg(SC.Grade)
from SC,Student
where SC.Sno=Student.Sno
group by Student.Sno,Student.Sname
having avg(SC.Grade)>='80';/*13.查询学分最小的课程号和课程名(排序后用top)*/
select top 1 cno ,cname
from Course
order by Ccredit asc;/*14.查询计算机专业年龄最大的三位学生学号和姓名(排序后用top)*/
select top 3 sno ,sname
from Student
order by yEAR(GETDATE())-year(sbirthdate) desc;/*15.查询平均分最高的课程号(分组、排序后用top)*/
select top 1 SC.Cno ,avg(SC.Grade) as '平均分'
from SC
group by SC.Cno
order by avg(SC.Grade) desc;/*16.查询平均分最高的课程号和课程名(分组、排序后用top、连接)*/
select top 1 SC.Cno,Course.Cname,avg(SC.Grade) as '平均分'
from SC,Course
group by SC.Cno,Course.Cname
order by '平均分' desc;/*17.查询选修了数据结构课程所有学生的学号(连接)*/
select SC.Sno
from Course,SC
where Course.Cno=SC.Cno and Course.Cname='数据结构';/*18.查询选修了数据结构课程所有学生的学号和姓名(连接)*/
select SC.Sno,Student.Sname
from Course,SC,Student
where Course.Cno=SC.Cno and SC.Sno=Student.Sno and Course.Cname='数据结构';/*19.查询选修了数据结构课程且成绩在90分以上的所有学生的学号和姓名(连接)*/
select SC.Sno,Student.Sname
from Course,SC,Student
where Course.Cno=SC.Cno and SC.Sno=Student.Sno and Course.Cname='数据结构' and SC.Grade>'90';/*20.查询数据结构课程的最高分、最低分和平均分(连接、聚集函数)*/
select max(SC.Grade) '最高分',MIN(SC.grade) '最低分',avg(SC.Grade) '平均分'
from Course,SC
where Course.Cno=SC.Cno and Course.Cname='数据结构'
group by SC.Cno;/*21.查询和刘晨在同一个专业的学生学号和姓名(用自身连接实现)*/
select SECOND.sno,SECOND.sname
from Student FIRST,Student SECOND
where FIRST.Sname='刘晨'and SECOND.Smajor=FIRST.Smajor;/*22.查询学生选修课程的总门数和总学分*/
select SC.Sno,COUNT(SC.Cno)as '总门数',sum(Course.Ccredit)as '总学分'
from Course,SC
where Course.Cno=SC.Cno
group by SC.Sno;/*23.查询选修课程总门数大于2且总学分大于8的学生学号、选修门数和选修总学分*/
select SC.Sno,COUNT(SC.Cno) as '总门数',sum(Course.Ccredit) as '总学分'
from Course,SC
where Course.Cno=SC.Cno
group by SC.Sno
having sum(Course.Ccredit)>'8' and COUNT(SC.Cno)>'2';