1、第四章 关系数据库语言SQL,4.2 查询语句,4.2.1 基本查询 4.2.2 使用列表达式 4.2.3 WHERE从句的进一步使用 4.2.4 数据汇总 4.2.5 连接查询 4.2.6 嵌套查询 4.2.7 联合查询 4.2.8 使用系统内置函数的查询,SQL查询语句的完整语法描述如下：,SELECT ALL|DISTINCT ， FROM ， WHERE GROUP BY HAVING ORDER BY ASC|DESC ；,温故知新：,回忆： WHERE从句,常用的查询条件,4.2.4 数据汇总,（1）聚合函数,例：查询学生总数,例题1：比较： select count(*) as
2、学生数 from stu_info 和 select count(xh) from stu_info（因为学号是主码，所以没有空值，以上计算结果相同）例题2：select count(bh) from stu_infoselect count (distinct bh) from stu_info若将bh 换成 xbm？,例：查询学生选修高等数学的最高分和最低分。 Select max(kscj) as 高等数学最高分,min(kscj) as 高等数学最低分 From xk Where kch=090101,例：已知数据库表XK中高等数学课程的代码为 090101，查询学生选修高等数学的平均
3、成绩：,select avg(KSCJ) as 高数平均分 from XK where KCH=090101,（2）GROUP BY子句,Group by 子句将查询结果表按某一列或多列值分组，值相等的分为一组。常常与聚合函数联合使用，用于针对分组进行统计汇总，使的每个分组都有一个函数值。 如：在表xk中查询各课程编号及相应的选课人数SELECT kch,count(xh) as 选课人数FROM xkGROUP BY kch,如何理解Group by,思考： 用销售人员编号来分组的话，分几组？ 结果是一个关系（二维表）吗？有几个元祖？ 用count能求出什么？用sum能求出什么？ 还可以用什
4、么来分组？,（3）HAVING 短语HAVING短语指定分组或聚合的条件。只有满足条件的分组才被选出来,列出来，HAVING通常与GROUP BY子句一起使用。,例：查询选修了3门以上课程的学生及选课数： SELECT XH, COUNT(*) FROM xk GROUP BY XH HAVING (COUNT(*) 3) 例：只查询选修了3门以上课程的学号前4位是2010的学生的学号及选课数。 SELECT XH, COUNT(*) FROM xk WHERE (XH LIKE 2010%) /位置不能放后面 GROUP BY XH HAVING (COUNT(*) 3),Where与hav
5、ing 有什么区别？,Having 短语指定选择组的条件，只有满足条件的组才被选出来。,Where子句从基本表中选择满足条件的元组，而不是指定满足条件的分组，这是二者的根本区别。,若一个查询同时涉及两个以上的表，称为连接查询。 连接查询是关系数据库中最主要的查询，包括等值连接、自然连接、非等值连接、自身连接、外连接、内连接、左连接、右连接查询。我们重点学习的是等值连接。,4.2.5 连 接 查 询,连接查询中用来连接两个关系的条件称为连接条件或连接谓词，格式： . 其中比较运算符主要有：=、=、=、!= 当连接运算符为=时，叫等值连接。 使用其它运算符称为非等值连接。 连接谓词中的列名称为连接
6、字段。,等值连接举例,student,Course,Student 与course表的等值连接,例：查询每个学生及其选课情况,select stu_info.*,xk.* From stu_info,xk Where stu_info.xh=xk.xh,注意： 连接查询涉及的所有表名都放在FROM子句中。 连接条件放在WHERE子句中。 如果列名在参加连接的各表中是唯一的，可以省略表名前缀；如果该列名在两个及以上表中都有，则一定要加表名前缀。,例：查询物理012班每个学生及其选修课程的情况,SELECT stu_info.xh,stu_info.xm,stu_info.bh,stu_info.
7、xbm,xk.kch,xk.kscj FROM stu_info,xk WHERE stu_info.xh = xk.xh and stu_info.bh=物理012/复合条件连接,例如：查询选修了有机化学这门课程的学生的姓名 Select xm from stu_info, xk, gcourse Where gcourse.km=有机化学 and gcourse.kch=xk.kch and xk.xh=stu_info.xh,为了简化，表名可以取别名，且别名只在本次查询有效,例：select xm from stu_info s,xk x,gcourse c Where c.km=有机化
8、学 and c.kch=x.kch and x.xh=s.xh,商品表：P(PNO,PNAME,CITY,COLOR) 商品号、商品名、产地和商品颜色 商场表：M(MNO,MNAME,CITY) 商场号、商场名和所在城市； 销售表：S(MNO,PNO,QTY) 商场号、商品号和销售数量。 主键为（mno，pno）。SQL语句写出下列查询： 检索销售了产地为杭州的商品的商场名和商场所在城市。,SELECT MNAME,M.CITY FROM M,S,P WHERE S.PNO=P.PNO ANDP.CITY=杭州 AND S.MNO=M.MNO;,检索上海市所有商场中各种商品的平均销售量。,SE
9、LECT Pno， AVG(QTY) FROM S,M WHERE M.CITY=上海 AND M.MNO=S.MNO GROUP BY Pno,检索销售了蓝色商品的商场号、商品号和数量,Select s.mno,s.pno,s,qty From s,m,p Where p.pno=s.pno and s.mno=m.mno and color=blue,36.设某数据库有三个关系： 音像（音像编号，音像名，租金，类别） 会员（会员编号，会员名，年龄，所在地区，联系电话） 租借（音像编号，会员编号，租借日期，归还日期）用SQL语言查询李扬租借过的音像制品的名称和类别。.用SQL语句查询2010
10、年5月以前租借音像制品的会员编号。 （注：租借日期为字符型，格式为2010/01/01）,SELECT 音像名，类别 FROM 音像，会员，租借 WHERE音像.音像编号=租借.音像编号 AND 会员.会员编号=租借.会员编号 AND 会员名=李扬,SELECT DISTINCT 会员编号 FROM 租借 WHERE 租借日期 2010/05/01,小结,汇总 连接查询 实验四,实 验 四,查询信息科学与工程学院的学生的学号、姓名、性别 查询成绩在85分以上的学生的学号、姓名、课程名称 查询学号的前4位是2000的学生的学号、姓名、学院名称 查询高等数学（kch=090101）成绩不及格的学生
11、的学号、姓名 查询信息科学与工程学院考试成绩不及格的同学的学号、姓名、课程名称 查询2001-2002学年第一学期（kkny=20011）选修课程超过10门的学生的学号、姓名、学院名称,下面讨论另一类基于多个关系的查询，这类查询所要求的结果出自一个关系，但相关的条件却涉及多个关系。在前面的例子中，WHERE 之后是一个相对独立的条件，这个条件或者为真、或者为假。但是，有时需要用另外的方式来表达检索要求。比如，当检索关系X中的记录时，它的条件依赖于相关的关系Y中的记录属性值，这时使用SQL的嵌套查询功能将非常方便。,4.2.6 嵌 套 查 询,上节课的例子： 检索销售了蓝色商品的商场号、商品号和
12、数量。（要求用嵌套子查询完成）,SELECT MNO, PNO, QTY FROM S WHERE PNO IN (SELECT PNO FROM P WHERE P.COLOR=BLUE);,在SQL语言中，一个select-from-where语句称为一个查询块，将一个查询块嵌套在另一个查询块的where子句或having短语的条件中的查询称为嵌套查询。 例：在stu_info表中查询选修了090201号课程的学生姓名 SELECT 姓名 FROM stu_info WHERE (学号 IN(SELECT 学号FROM xkWHERE (课程号 = 090201),可用连接查询完成： Se
13、lect 姓名 from stu_info,xk Where stu_info.学号=xk.学号 and 课程号=090201,例：查询与刘玉涛在同一个班学习的学生,SELECT xh, xm, bh FROM stu_info WHERE (bh IN(SELECT bHFROM stu_infoWHERE xm = 刘玉涛),(1)带有in谓词的子查询 （在同一个表中的查询）,(2)带有比较运算符的子查询,例：查询与学号是2000029001的学生在同一个班学习的学生，按学号排序。,SELECT xh, xm, bh FROM stu_info WHERE (bh =(SELECT bHF
14、ROM stu_infoWHERE xh = 2000029001) ORDER BY xh,(3)带有any或all谓词的子查询,比较子查询将某个列的值与内部查询的结果进行运算比较，结果为真则返回该行，否则不返回。比较子查询通常用到操作符ALL、ANY。其通用格式为：ALL|ANY 例：查询其他班级中比物理012班某个学生年龄小的学生的姓名和年龄 Select xm,nl,bh from stu_info Where nl物理012,延伸,select xh,nl,xm from stu_info where nl (select min(nl) from stu_info where bh
15、=物理012),4.2.7 联合(集合)查询,select语句的查询结果是元组的集合，所以多个select语句的结果可进行集合操作。 集合操作主要包括：并操作UNION、交操作INTERSECT、差操作MINUS 标准SQL只提供UNION，其语法格式如下：SELECT_1 UNION ALL SELECT_2 例：查询01学院的学生及所有的本科生 Select * from stu_info where xsh=01 union Select * from stu_info where pyccm=本科,可以用OR运算符来代替UNION，查询语句可以改写为：Select * from stu
16、_info where xsh=01 or pyccm=本科,4.2.8 使用系统内置函数的查询,(1) 数学函数ABS(数字型表达式):返回给定数字表达式的绝对值.RAND(整型表达式):返回01之间的一个随机值. (2) 字符串处理函数ASCII(字符型)：返回表达式最左端字符的ASCII码值CHAR(0-255之间的整数)：将ASCII转换为字符LEFT(字符型，整型)：返回从字符串左边开始指定个数的字符LTRIM(字符型)：删除字符串最左边的空格，并返回字符串REPLACE(字符型1，字符型2，字符型3)：用3替换1中包含的2，并返回替换后的表达式SUBSTRING(express,s
17、tart,length)：返回express中指定的部分数据STR(float_exp ,length ,decimal)：将数字数据转换为字符数据,例:查询学号最左边的4个字符 SELECT LEFT(XH,4) FROM STU_INFO 例:已知某学校学号(XH,CHAR)的前四位是入学年,第5位是性别代码,后5位是流水号,如某女生的学号:2002030101。在学生基本情况数据库表STU_INFO中查询所有学生的学号、姓名、入学年、性别等信息。SELECT XH,XM,LEFT(XH,4) AS 入学年,SUBSTRING(XH,5,1) AS 性别码 FROM STU_INFO OR
18、DER BY XH,(3) 系统函数 getdate():返回当前的系统日期和时间,返回值DATETIME. year()、month()、day():分别返回指定日期的年、月、日,返回值为INT. 例:SELECT YEAR(GETDATE(),MONTH(GETDATE(),DAY(GETDATE(),GETDATE(),简单CASE语法格式： CASE 输入表达式WHEN 比较表达式 THEN 结果表达式ELSE 表达式 END,搜索型CASE语法格式： CASEWHEN 条件1 THEN 表达式1WHEN 条件2 THEN 表达式2ELSE 表达式 END,case函数：一种是简单的C
19、ASE函数，另一种是搜索型的CASE函数。,例：在STU_INFO中查询03学院学生的学号、姓名、性别，并将性别分别转换成“男生”、“女生”。SELECT XH,XM,XBM,XBM=CASE 男WHEN XBM THEN 男生ELSE 女生ENDFROM STU_INFOWHERE XSH=03,例：查询成绩表XK中选修了“数据库技术及应用”课程的学生的学号、姓名、5分制成绩SELECT xh,KSCJ,5分制成绩=CASEWHEN KSCJ=60 AND KSCJ=70 AND KSCJ=80 AND KSCJ=90 THEN 优秀ENDFROM XKWHERE KCH=090101,cast(表达式 AS 数据类型)：将表达式的值转换为数据类型参数所指定的类型。参数表达式可以任何有效的数据类型,数据类型是系统提供的基本类型，不能为用户自定义类型。 例：查询选课表，其中KSCJ、XH等数据类型都是CHAR，现要查询考试成绩在50-60分的学生及成绩，并将成绩加10分显示。SELECT XH, KSCJ,CAST(KSCJ AS FLOAT)+10 AS 加分后成绩 FROM XK WHERE KSCJ LIKE 5_,
