ch07-SQL数据查询语句

第7章SQL数据查询语句本章介绍SQL中数据查询语言的基本概念及数据操作。数据库系统原理及应用7.1数据查询语句基本结构7.2简单查询7.3联合查询7.4连接查询主要内容7.5嵌套查询数据查询语句基本结构7.17.1数据查询语句基本结构SELECT[ALL|DISTINCT]col_name1(,...n)FROMtable_references[WHEREwhere_condition][GROUPBYcol_name,...][HAVINGwhere_condition][ORDERBYcol_name[ASC|DESC],...]参数：（1）SELECT：指定查询结果的列或列表达式。ALL|DISTINCT：使用ALL选项时，显示符合条件的所有行。使用DISTINCT选项时，对于所有重复的数据行在结果集合中只保留一行。默认为ALL。col_name1(,...n)：查询结果中显示的列（4）

FROM：指定查询范围。WHERE：指定查询条件。不使用WHERE子句时返回所有行。GROUPBY：指定分组依据。HAVING：指定对分组结果的筛选条件。不使用HAVING子句时返回所有分组结果。ORDERBY：对查询结果按照指定的列进行排序，ASC是升序，DESC是降序，默认值是升序。SELECT语句的基本语法格式简单查询7.27.2.1SELECT子句指定查询列1）“*”表示显示表中所有列[例7-1]:显示student中所有数据。SELECT*FROMstudent;3）显示/删除重复行2）仅显示部分列[例7-2]：查询student表中的学号和学生名。SELECTSID,snameFROMstudent;[例7-3]:查询student中学生所在的院系，有多少人就显示多少个学院名称。SELECTALLcollegeFROMstudent;或者SELECTcollegeFROMstudent;7.2.1SELECT子句指定查询列4）限制返回的行数

LIMITn：返回前n行；

LIMITn,m：返回第n行之后的m行。[例7-4]:查询student中前5名学生的校园卡号和姓名。SELECT*FROMstudentLIMIT5;5）重命名

oldname[AS]newname[例7-5]:查询student中的sname和college，把表名命名为学生表，把各列命名为对应的中文。SELECTSIDAS学号,CID卡号,sname姓名,gender性别,collegeAS院系FROMstudent;7.2.2WHERE子句设置查询条件7.2.2WHERE子句设置查询条件1）比较运算符（大小比较）>、>=、=、<、<=、<>、!=[例7-6]:查询消费金额大于等于50元的消费信息。SELECT*FROMsalebillWHERE

payamount>=50;2）范围运算符（表达式值是否在指定的范围）BETWEEN…AND…和NOTBETWEEN…AND…[例7-7]:查询消费金额在20元至30元之间的消费信息。SELECT*FROMsalebillWHEREpayamountBETWEEN20AND30;3）列表运算符（判断表达式是否为列表中的指定项）IN(项1,项2……)或者NOTIN(项1,项2……)[例7-8]:查询管理学院和机械学院的学生信息。SELECT*FROMstudentWHEREcollegeIN('管理学院','机械学院');7.2.2WHERE子句设置查询条件4）空值判断符(判断表达式是否为空)ISNULL和ISNOTNULL[例7-9]:查询尚未办理校园卡的学生姓名和院系。SELECTsname,collegeFROMstudentWHERECIDISNULL;5）逻辑运算符(用于多条件的逻辑连接)AND、NOT、OR其优先级从高到低依次是NOT、AND、OR。[例7-10]:查询2020年5月28日B003商户的销售情况。SELECT*FROMsalebillWHEREBID='B003'ANDsaledate='2020-5-28';7.2.2WHERE子句设置查询条件[例7-13]:查询密码为“12_”开头的校园卡卡号。6）模式匹配符（用于字符串的模糊匹配）LIKE和NOTLIKE[例7-11]:查询姓“张”的学生的姓名。SELECTsnameFROMstudentWHEREsnameLIKE'张__';SELECTsnameFROMstudentWHEREsnameLIKE'张%';百分号%:可匹配任意类型和长度的字符。下划线_:匹配单个任意字符，它常用来限制表达式的字符长度。[例7-12]:查询姓“张”且姓名一共三个字的人员的信息。转义符:如果要查询的字符串中包含%或_，需要使用转义符。\为默认的转义符，也可以使用ESCAPE子句指定其他转义符SELECTCID,passwordFROMcardWHERE`password`LIKE'12_%';7.2.3聚合函数1）计数函数COUNT2）求和函数SUM3）求平均值函数AVG7.2.3聚合函数4）求最大值函数MAX5）求最小值值函数MIN7.2.3聚合函数[例7-14]:统计学生表中的学生总人数。SELECTCOUNT(*)FROMstudent;[例7-15]:统计消费清单中单比消费的最大值和最小值，以及总金额和平均消费水平。SELECTMAX(payamount)，MIN(payamount)，SUM(payamount)，AVG(payamount)FROMsalebill;7.2.4GROUPBY子句进行分组计算分组的目的是细化聚合函数的统计范围。分组语句跟在WHERE子句的后面，它的基本形式为

GROUPBYcol_name[例7-16]:统计每个院系的学生人数。SELECTcollegeAS院系,COUNT(*)AS学生人数FROMstudentGROUPBYcollege;7.2.5HAVING子句对分组结果进行筛选子句HAVING用来对GROUPBY后的数据进行条件筛选。HAVING子句的基本语法格式为：

HAVINGwhere_condition

参数：

where_condition：HAVING的条件表达式中一般都包含聚合函数，

以此来筛选符合条件的分组。子句HAVING必须放在GROUPBY之后，ORDERBY之前。[例7-17]:统计每个学院的学生数，只列出人数大于2人的学院名称和学生总数。SELECTSchoolAS学院名称,COUNT(*)AS学生数FROMTable_Student GROUPBYSchool HAVINGCOUNT(*)>27.2.6ORDERBY子句对查询结果排序ORDERBY子句可以对查询结果排序。ORDERBY子句的语法格式为：

ORDERBYcol_name[ASC|DESC][,…n]

参数：

ASC表示升序，为默认值，DESC表示降序；

对多个列排序，列名之间用逗号分开，每个列都可以指定按升序或降序排序。[例7-18]:

查询校园卡的卡号和消费总额，并将查询结果按消费总额的升序排序。SELECTCID,SUM(payamount)消费总额FROMsalebillGROUPBYCIDORDERBYSUM(payamount);联合查询7.3集合查询种类联合查询语法格式select_statementUNION[ALL|DISTINCT]selectstatementUNION[ALL|DISTINCT]selectstatement][…n]

参数：select_statement：待联合的SELECT查询语句。

ALL：不会消除重复行直接返回联合运算的结果。

DISTINCT：消除重复行。MySQL仅支持UNION集合运算INTERSECT和EXCEPT和UNION语法格式类似联合查询[例7-19]:

查询消费总额低于100元或高于300元的校园卡信息。SELECTCID校园卡号,SUM(payamount)消费总额FROMsalebillGROUPBYCIDHAVINGSUM(payamount)<100UNIONSELECTCID校园卡号,SUM(payamount)消费总额FROMsalebillGROUPBYCIDHAVINGSUM(payamount)>300;联合查询联合查询时，查询结果的列标题为第一个查询语句的列标题。因此，要定义列标题必须在第一个查询语句中定义。要对联合查询结果排序时，也必须使用第一查询语句中的列名、列标题或者列序号。在使用联合查询时，应保证每个联合查询语句的选择列表中列数相同，对应列的数据类型相同，或是可以自动将它们转换为相同的数据类型。在自动转换时，对于数值类型，系统将低精度的数据类型转换为高精度的数据类型。连接查询7.4为了从多个表中获取数据，需要进行多表连接。连接查询在SELECT

语句的FROM子句中实现语法格式为FROM

join_table

join_type

join_table[ON

(join_condition)]其中，join_table指出参与连接操作的表名，连接可以对同一个表操作，也可以对多表操作，对同一个表操作的连接又称做自连接。join_type指的是连接类型，可分为内连接、外连接和交叉连接三种类型。ON

(join_condition)

子句指出连接条件，它由被连接表中的列和比较运算符、逻辑运算符等构成。连接查询7.4.1内连接（Inner

Join或Join）使用比较运算符进行表间某(些)列数据的比较操作，并列出这些表中与连接条件相匹配的数据行。（1）等值连接:在连接条件中使用等于（=）运算符比较被连接列的列值，其查询结果中列出被连接表中的所有列，包括其中的重复列。（2）自然连接：在连接条件中使用等于（=）运算符比较被连接列的列值，但它使用选择列表指出查询结果集合中所包括的列，并删除连接表中的重复列。（3）不等连接

：在连接条件使用除等于运算符以外的其它比较运算符比较被连接的列的列值。这些运算符包括>、>=、<=、<、!>、!<和<>。7.4.1内连接[例7-20]:查询在2019年6月29号，消费金额累计超过20元的学生学号和其消费总额。SELECTStudentID,SUM(PayAmount)AS消费总额FROMstudentAJOINsalebillBONA.CardID=B.CardIDWHERESaleDate='2019-06-29'GROUPBYStudentIDHAVINGSUM(PayAmount)>20多表的连接查询SELECTa.*,b.*,c.*,d.*FROMTable_StudentaJOINTable_Cardb ONa.CardID=b.CardIDJOINTable_SaleBillc ONb.CardID=c.CardIDJOINTable_Machined ONc.MachineID=d.MachineID7.4.2外连接外连接分为三种左外连接（LeftOuterJoin或LeftJoin）右外连接（Right

Outer

Join或RightJoin）全外连接（Full

Outer

Join或Full

Join）MySQL中没有全外连接7.4.2外连接外连接分为三种左外连接（LeftOuterJoin或LeftJoin）右外连接（Right

Outer

Join或RightJoin）全外连接（Full

Outer

Join或Full

Join）MySQL中没有全外连接7.4.2外连接7.4.2外连接7.4.2外连接7.4.2外连接左外连接左连接的含义是不管表1的元组是否满足连接条件，均输出表1的内容。其语法格式为FROM表1LEFTJOIN表2ON<连接条件>[例7-21]:查询所有饭卡的消费情况，包括没有消费的饭卡和消费的饭卡。SELECTcard.CardID,salebill.CardID,PayAmount,SaleDateFROMcardLEFTJOINsalebillONcard.CardID=salebill.CardID7.4.2外连接右外连接右外连接的含义是不管表2的元组是否满足连接条件，均输出表2的内容。其语法格式为FROM表1RIGHTJOIN表2ON<连接条件>[例7-22]:查询所有饭卡的消费情况，包括没有消费的饭卡和消费的饭卡。SELECTcard.CardID,salebill.CardID,PayAmount,SaleDateFROMsalebillRIGHTJOINcardONcard.CardID=salebill.CardID;7.4.2外连接全外连接全外连接没有WHERE子句，它返回连接表中所有数据行的笛卡尔积，其结果集合中的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数。嵌套查询7.5嵌套查询嵌套查询嵌套查询1）子查询必须用括号包含。2）子查询中不能使用ORDERBY子句，ORDERBY子句永远只能对外层查询结果排序。3）返回多行的子查询可以和多值操作符如IN一起使用。4）BETWEEN操作符不能和子查询一起使用，但在子查询中可以使用BETWEEN。5）子查询一定要跟在比较运算符之后。注意：7.5.1IN子查询通过使用选择项IN和NOTIN，判断某个属性列的值是否在子查询的结果集合中。IN子查询的执行顺序是由里向外依次执行，里层子查询的结果用于构建外层查询的查找条件。WHERE<表达式>[NOT]IN(子查询)[例7-23]:查询与“周萍”在一个院系的学生姓名。SELECTsname,collegeFROMstudentWHEREcollegeIN(SELECTcollegeFROMstudentWHEREsname='周萍');先由子查询找到周萍所在的院系名称“管理学院”再由父查询找到“管理学院”的学生姓名7.5.2比较子查询使用子查询进行比较是指用比较运算符（=、<、>、<=、>=）将子查询返回的单值与一个表达式的值进行比较。WHERE<表达式>比较运算符（子查询）如果比较运算的结果为真，则比较测试返回TRUE；如果比较运算的结果为假，则比较测试返回FALSE。注意使用子查询进行比较时，要求子查询语句返回的结果必须是单值的。[例7-24]:查询与“周萍”在一个院系的学生姓名。如果没有同名的学生，则子查询返回的结果就是单值，集合运算可以用比较运算符“=”来代替。SELECTCID,sname,schoolFROMstudentWHEREcollege=(SELECTcollegeFROMstudentWHEREsname='周萍');7.5.3EXISTS子查询使用EXISTS的子查询可以进行存在性测试，其语法格式为WHERE[NOT]EXISTS(子查询)EXISTS的含义是当子查询中有满足条件的数据时，EXISTS返回真值，否则返回假值。NOTEXISTS的含义是当子查询中有满足条件的数据时，NOTEXISTS返回假值，否则返回真值。带EXISTS的嵌套查询是先执行外层查询，