4.3查询基础数据表

MySQL数据库项目四：学生寝室管理数据库MySQL数据库4.3.1查询数据表任务SELECT单表查询12JOIN多表连接查询单表查询SELECT查询数据列是指从数据表中查询指定的字段值组成结果集。通过SELECT子句的字段名组成结果集的列。单表查询SELECT（1）查询指定的列如果只需要查询数据表中的某些字段的值，在关键字SELECT后指定需要查询的字段，当不止一个字段时字段之间用半角逗号“，”隔开语法格式：SELECT

字段名1,字段名2,...,字段名nFROM

数据表名;单表查询SELECT【例】查询student表中学生的信息，包含studentid、studentname和sex三个字段SQL语句为：SELECTstudentid,studentname,sexFROMstudent;单表查询SELECT（2）查询所有的列如果查询数据表中所有字段的值，可以使用关键字“*”表示数据表中所有字段，而不用一一列举表中所有字段，字段顺序与数据表结构一致。语法格式：SELECT*FROM数据表名;单表查询SELECT【例】查询学生表student的数据记录SQL语句：SELECT*FROMstudent;单表查询SELECT（3）计算列值使用SELECT语句进行查询时，不仅可以查询数据表中字段的值，还可以通过表达式进行计算。单表查询SELECT【例】从学生表student中查询出学生的姓名和年龄SQL语句：SELECTstudentname,YEAR(CURDATE())-YEAR(birthday)FROMstudent;单表查询SELECT（4）为结果集中的列指定标题默认情况下，结果集显示的列标题就是查询字段的名称或表达式，当希望查询结果中显示的列使用自定义的列标题时，可使用AS子句更改结果集中的列标题，AS关键字也可以省略。SELECT

字段名[AS]别名FROM数据表名;单表查询SELECT【例】从学生表student中查询出学生的姓名和年龄，并将结果集中的列标题指定为“姓名”，“年龄”SQL语句：SELECTstudentnameAS

姓名,YEAR(CURDATE())-YEAR(birthday)年龄FROMstudent;单表查询SELECT为列指定别名操作时，必须注意：①当引用中文别名时，可以不加引号，但是不能使用全角引号，否则查询会出错。②当引用英文的别名超过两个单词时，则必须用引号将其引起来。单表查询DISTINCT当在MySQL中执行数据查询时，查询结果可能会包含重复行。如果需要去除这些重复行，可以在SELECT语句中使用关键字DISTINCT。语法格式：SELECTDISTINCT字段名FROM表名;单表查询DISTINCT【例】在寝室表dorm中查询寝室都有哪些朝向SQL语句：SELECT

DISTINCTtowardFROMdorm;单表查询LIMITLIMIT通常放在SELECT语句后面，用来指定查询结果从哪一条记录开始，一共查询多少条记录。使用LIMIT限制结果集返回的行数，语法格式：SELECT*|字段名[,字段名...]FROM

数据表名LIMIT[偏移量,]记录数;语法说明：偏移量指定查询结果从哪一行开始，如果省略偏移量（默认值为0），则查询从第一行开始。记录数指定查询结果中查询的记录的行数。单表查询LIMIT【例】在学生表student中查询前6行学生信息SQL语句：SELECT*FROMstudentLIMIT6;或SELECT*FROMstudentLIMIT0,6;单表查询LIMIT【例】在学生表student中查询从第5行开始的连续6行的学生信息，SQL语句：SELECT*FROMstudentLIMIT6,6;单表查询ORDERBY排序是指查询时仅按照一个指定字段进行升序或降序排序语法格式：SELECT[ALL|DISTINCT]*|字段名[,字段名...]FROM数据表名ORDERBY字段名[ASC|DESC];语法说明：ASC为升序，DESC为降序，省略的情况下默认为ASC。单表查询ORDERBY【例】在学生表student中查询班级编号为“200101”的班级学生的姓名、性别和生日，查询结果按生日升序排序SQL语句：SELECTstudentname,sex,birthdayFROMstudentWHEREclassid='200101'ORDERBYbirthday;单表查询WHERE在SELECT语句中可以WHERE子句指定查询条件语法格式：SELECT*|字段名[,字段名...]FROM数据表WHERE

条件表达式;单表查询WHERE使用比较运算符指定查询条件语法格式：WHERE

表达式1比较运算符表达式2单表查询WHERE【例】在寝室表dorm中查询寝室朝向为“南”的寝室信息SQL语句：SELECT*FROMdormWHEREtoward='南';单表查询WHERE【例】在寝室表dorm中查询寝室床位数超过4个的寝室信息SQL语句：SELECT*FROMdormWHERE

totalbeds>=4;单表查询WHERE使用逻辑运算符指定查询条件语法格式：WHERE[NOT]表达式1逻辑运算符表达式2[...逻辑运算符表达式n]单表查询WHERE【例】在学生表student中查询2002年4月出生的学生信息SQL语句：SELECT*FROM

student

WHERE

YEAR(birthday)=2002AND

MONTH(birthday)=4;单表查询WHERE【例】在学生表student中查询班级编号为“200101”和“200102”的两个班的学生信息SQL语句：SELECT*FROM

student

WHERE

classid='200101'ORclassid='200102';单表查询WHERE使用范围运算符指定查询条件语法格式：WHERE

表达式[NOT]BETWEEN

起始值AND

终止值单表查询WHERE【例】在寝室表dorm中查询寝室床位数在6到8之间的寝室信息SQL语句：SELECT*FROM

dorm

WHERE

totalbedsBETWEEN6AND8;或SELECT*FROMdormWHEREtotalbeds>=6ANDtotalbeds<=8;单表查询WHERE使用IN运算符可以确定表达式的取值是否属于某一值列表，当表达式取值与值列表中的任何一个值匹配时，即返回TRUE，否则返回FALSE。如果表达式不属于某一值列表，可使用NOTIN运算符。语法格式：WHERE

表达式[NOT]

IN

(值1,值2,...,值n)单表查询WHERE【例】在学生表student中查询班级编号为“200101”和“200102”的两个班的学生信息SQL语句：SELECT*FROM

student

WHERE

classidIN('200101','200102');单表查询WHERE在SELECT语句中，可以使用空值运算符判断字段值是否为空值。ISNULL可以查询某字段值为空的记录；ISNOTNULL与ISNULL相反，可以查询字段值不为空的记录。语法格式：WHERE

字段名IS[NOT]NULL单表查询WHERE【例】在学生表student中查询走读学生的信息SQL语句：SELECT*FROM

student

WHERE

classidISNULL;【例】在学生表student中查询住宿学生的信息SQL语句：SELECT*FROM

student

WHERE

classidISNOTNULL;单表查询WHERE

模糊查询是查找数据表中与用户输入关键字相近或相似的记录信息。模糊匹配运算符通常与通配符一起使用，由字符串和通配符组成的字符串必须用半角单引号（'）或双引号（"）引起来。单表查询WHERE使用模糊匹配运算符指定查询条件语法格式：WHERE

字段名[NOT]LIKE'字符串’;语法说明：（1）字符串一般是由普通字符和通配符组成，必须用半角单引号（'）或双引号（"）引起来。（2）通配符%(百分号)

代表零个或任意多个字符，_(下划线)代表任意单个字符。单表查询WHERE【例】在学生表student中查询所有姓李的学生信息SQL语句：SELECT*FROM

student

WHERE

studentnameLIKE'李%';【例】在学生表student中查询生源地不是沈阳市的学生信息SQL语句：SELECT*FROM

student

WHERE

addressNOT

LIKE'%沈阳市%';单表查询WHERE使用模糊匹配运算符指定查询条件语法格式：WHERE

字段名[NOT]LIKE'字符串’;语法说明：（1）字符串一般是由普通字符和通配符组成，必须用半角单引号（'）或双引号（"）引起来。（2）通配符%(百分号)

代表零个或任意多个字符，_(下划线)代表任意单个字符。单表查询GROUPBY

统计函数用于对一组数据值进行统计计算并返回单一值，又称为组合函数、聚合函数等。SELECT子句中可以使用统计函数进行计算，计算结果作为新列出现在查询结果集中。单表查询GROUPBY函数名

说明COUNT返回组中的项数MAX返回组中的最大值MIN返回组中的最小值SUM返回组中的所有值的和AVG返回组中的各值的平均值MySQL支持的常用统计函数及其作用单表查询GROUPBY1.COUNT()函数COUNT函数用于统计数据表中数据记录的行数，或者满足特定条件的数据记录的行数

COUNT(*)统计数据表中数据记录的行数，包括值为NULL的记录。

COUNT(字段名)统计数据表中某一字段的行数，忽略值为NULL的记录。单表查询GROUPBY【例】在学生表student中，统计在籍学生总人数及住宿学生总人数SQL语句：SELECT

COUNT(*)AS'在籍人数',COUNT(dormid)AS'住宿人数'FROMstudent;单表查询GROUPBY2.MAX函数和MIN函数MAX函数和MIN函数分别用于求表达式中所有值中的最大值与最小值。3.SUM函数和AVG函数SUM函数和AVG函数分别用于求表达式中所有值的总和与平均值。语法格式：MAX(表达式)MIN(表达式)SUM(表达式)AVG(表达式)单表查询GROUPBY【例】通过学生表student，统计寝室床位数的总数和每个寝室床位数的平均数，统计寝室床位数的最大值与最小值SQL语句：SELECT

MAX(totalbeds)AS'最大值',MIN(totalbeds)AS'最小值',SUM(totalbeds)AS'总床位数',AVG(totalbeds)AS'平均数'FROMdorm;单表查询GROUPBY如果需要按某字段数据值进行分组，在分组的基础上再进行统计计算，就要使用GROUPBY子句。语法格式：SELECT

字段名[,字段名...]FROM

数据表名GROUPBY字段名[HAVING

条件表达式]

;单表查询GROUPBY【例】在寝室表dorm中根据寝室的朝向进行分组SQL语句：SELECT*FROMdormGROUPBYtoward;单表查询GROUPBY【例】在寝室表dorm中统计每个朝向的寝室数量SQL语句：SELECTtoward,COUNT(dormid)FROMdormGROUPBYtoward;单表查询GROUPBY【例】在寝室表dorm中统计每个寝室楼的床位总数SQL语句：SELECTapartment,SUM(totalbeds)FROMdormGROUPBYapartment;单表查询GROUPBY使用GROUPBY子句进行分组统计时，为了能得到有意义的数据，SELECT子句要查询的字段只能是以下两种情况。（1）字段应用了统计函数。（2）未应用统计函数的字段必须包含在GROUPBY子句中。单表查询GROUPBY【例】在寝室表dorm中统计哪个朝向的寝室数量超过15SQL语句：SELECTtoward,COUNT(dormid)FROMdormGROUPBYtowardHAVINGCOUNT(dormid)>15;单表查询GROUPBYHAVING和WHERE的不同点:

HAVING子句针对结果集中的数据；WHERE子句针对的是数据表中的数据。

HAVING子句只过滤分组后的数据；WHERE子句在分组前对数据进行过滤。

HAVING子句位于GROUPBY子句之后；而WHERE子句位于GROUPBY子句之前。

HAVING可以与统计函数一起使用；而WHERE则不可以。多表连接查询JOIN多表连接查询是指查询同时涉及两个或两个以上的表。一、交叉连接（CROSSJOIN）二、内连接（INNERJOIN）三、外连接（OUTERJOIN）四、自连接（SELFJOIN）多表连接查询CROSSJOIN交叉连接（CROSSJOIN）查询结果集返回两个表的任何记录行的笛卡尔积，即查询结果集的列为各连接表的字段之和，记录行数为各连接表记录行数的乘积值。语法格式:SELECT*|字段名[,字段名...]FROM

数据表1,数据表2[,数据表3...];或SELECT*|字段名[,字段名...]FROM

数据表CROSSJOIN数据表2[CROSSJOIN数据表3...];多表连接查询CROSSJOIN【例】将班级表class与学生表student进行交叉连接SQL语句:SELECT*FROMclass,student;或SELECT*FROMclassCROSSJOINstudent;多表连接查询CROSSJOIN多表连接查询INNERJOIN内连接（INNERJOIN）是使用比较运算符指定连接条件，只返回满足连接条件的数据行，即在交叉连接生成的结果集中按照连接条件进行筛选后形成的结果。简单地说，就是利用条件表达式来消除交叉连接的某些数据行。语法格式：SELECT*|字段名[,字段名...]FROM

数据表名1,数据表名2WHERE

连接条件;或者SELECT*|字段名[,字段名...]FROM

数据表名1[INNER]JOIN数据表名2ON

连接条件;多表连接查询INNERJOIN语法格式：SELECT*|字段名[,字段名...]FROM

数据表名1[AS]别名1,数据表名2[AS]别名2WHERE

连接条件;或者SELECT*|字段名[,字段名...]FROM

数据表名1[AS]别名1[INNER]JOIN

数据表名2[AS]别名2ON

连接条件;多表连接查询INNERJOIN内连接的类型（1）等值连接：在连接条件中使用等号（=）比较运算符来比较连接字段的值，其查询结果中包含被连接表的所有字段，包括重复字段。（2）非等值连接：在连接条件中使用了除等号之外的比较运算符（>、<、>=、<=、!=）来比较连接字段的值。（3）自然连接：与等值连接相同，都是在连接条件中使用等号（=）比较运算符，但结果集中不包括重复字段。多表连接查询INNERJOIN【例】将班级表class与学生表student进行内连接SQL语句：SELECT*FROMclass,studentWHEREclass.classid=student.classid;或SELECT*FROMclassAScINNERJOINstudentASsONc.classid=s.classid;多表连接查询INNERJOIN【例】查询“20汽营1班”学生的姓名、电话及家庭住址SQL语句：SELECT

studentname,telephone,address

FROMclass,studentWHEREclass.classid=student.classidANDclassname='20汽营1班';或SELECT

studentname,telephone,address

FROMclassAScINNERJOINstudentASsONc.classid=s.classidWHEREclassname='20汽营1班';多表连接查询INNERJOIN【例】将班级表class与学生表student进行自然连接SQL语句：SELECT

student.*,classnameFROMclass,studentWHEREclass.classid=student.classid;或SELECT

student.*,classnameFROMclassAScINNERJOINstudentASsONc.classid=s.classid;多表连接查询OUTERJOIN通过外连接，在返回符合连接条件的记录的基础上，还可以返回某个数据表不符合条件的数据。外连接包括3种类型：左外连接、右外连接和全外连接。多表连接查询OUTERJOIN

左外连接，也可称为左连接。以左边数据表为基准去连接右边数据表，返回连接关键字（LEFTJOIN）左边数据表中所有的记录，以及右边数据表中符合连接条件的记录。

当左边数据表的某行记录在右边数据表中没有匹配的记录时，在连接结果中该行记录对应的右边数据表的字段均以NULL填充。语法格式：SELECT*|字段名[,字段名...]FROM数据表名1LEFT[OUTER]JOIN

数据表名2ON

连接条件;多表连接查询OUTERJOIN【例】将寝室表dorm和学生表student进行左外连接SQL语句：SELECT

*FROM

dormdLEFTOUTERJOINstudentsONd.dormid=s.dormid;多表连接查询OUTERJOIN【例】统计空闲寝室的数量SQL语句：SELECT

COUNT(*)

FROM

dormdLEFTOUTERJOINstudentsONd.dormid=s.dormidWHEREstudentnameISNULL;多表连接查询OUTERJOIN

右外连接，也可称为右连接。以右边数据表为基准去连接左边数据表，返回连接关键字（RIGHTJOIN）右边数据表（主表）中所有的记录，以及左边数据表（从表）中符合连接条件的记录。当右边数据表的某行记录在左边数据表中没有匹配的记录时，在连接结果中该行记录对应的左边数据表的字段均以NULL填充。语法格式：SELECT*|字段名[,字段名...]FROM数据表名1RIGHT[OUTER]JOIN

数据表名2ON

连接条件;多表连接查询OUTERJOIN【例】将寝室表dorm和学生表student进行右外连接SQL语句：SELECT*FROMdormdRIGHTOUTERJOINstudentsONd.dormid=s.dormid;多表连接查询OUTERJOIN

全外连接，也可称为完全连接，连接关键字为FULLJOIN。

全外连接得到的结果集除了包含符合连接条件的记录外，同时也包含不满足条件的两个数据表的记录，即包含两个表的全部记录。

当左边数据表的某行记录在右边数据表中没有匹配的记录时，在连接结果中该行记录对应的右边数据表的字段均以NULL填充。

当右边数据表的某行记录在左边数据表中没有匹配的记录时，在连接结果中该行记录对应的左边数据表的字段均以NULL填充。多表连接查询OUTERJOIN全外连接的语法格式:SELECT*|字段名[,字段名...]FROM

数据表名1FULL[OUTER]JOIN

数据表名2ON

连接条件;MySQL暂不支持全外连接，可以通过其他方法间接实现全外连接。多表连接查询OUTERJOIN自连接把一个数据表与它自身的副本进行连接。

自连接的连接操作可以利用别名的方法实现一个数据表自身的连接。

实际上，这种自身连接方法与两个数据表的连接操作完全相似，只是需要为数据表指定别名，字段名前也要加上数据表的别名进行限定。多表连接查询OUTERJOIN【例】查询和李旺在同一个班级的学生的姓名SQL语句：SELECTa.studentname,b.studentnameFROMstudentaJOINstudentbONa.classid=b.classidWHEREa.studentname='李旺'ANDb.studentname!='李旺';MySQL数据库4.3.2比较子查询、IN子查询任务<、<=、=、>、>=、!=、<>比较子查询12IN、NOTININ子查询子查询

子查询也被称为嵌套查询，就是SELECT查询时另一个查询的附属。子查询就是将一个查询语句嵌套在另一个查询语句中。外层的SELECT语句称为外部查询、父查询或主查询，内层的SELECT语句称为内部查询或子查询。子查询的执行过程可以描述为：首先执行子查询中的语句，并将返回的结果作为外层查询的筛选条件，然后再执行外层查询。子查询子查询通常与比较运算符、IN、ANY（SOME）、ALL以及EXISTS等关键字结合使用。比较子查询<、<=、=、>、>=、!=、<>比较子查询是指在父查询与子查询之间用比较运算符进行连接的查询。语法格式：WHERE表达式比较运算符{ALL|SOME|ANY}(子查询)语法说明：表达式为与子查询进行比较的表达式。比较运算符可以为<、<=、=、>、>=、!=、<>。

ALL、SOME和ANY操作符说明对比较运算的限制。比较子查询<、<=、=、>、>=、!=、<>【例】查询“王建国”同学所住寝室的信息SQL语句：SELECT*FROMdormWHEREdormid=(SELECTdormidFROMstudentWHEREstudentname='王建国');比较子查询<、<=、=、>、>=、!=、<>【例】在student表中查询年龄最大的学生所在班级SQL语句：SELECTclassnameFROMclassWHEREclassid=(SELECTclassidFROMstudentWHEREbirthday=(SELECTMIN(birthday)FROMstudent));比较子查询<、<=、=、>、>=、!=、<>【例】

在student表中查询比“200101”班任意一名学生年龄小的学习信息（查询结果不包括“200101”本班同学）SQL语句：SELECT*FROMstudentWHEREclassid<>'200101'ANDbirthday>ANY(SELECTbirthdayFROMstudentWHEREclassid='200101');比较子查询<、<=、=、>、>=、!=、<>【例】在student表中查询比“200101”班所有学生年龄都小的学习信息（查询结果不包括“200101”本班同学）SQL语句：SELECT*FROMstudentWHEREclassid<>'200101'ANDbirthday>ALL(SELECTbirthdayFROMstudentWHEREclassid='200101');IN子查询IN、NOTININ子查询是指父查询与子查询之间用IN或NOTIN进行连接并判断某个字段的值是否在子查询返回的结果集中。如果子查询的结果多于一个时再使用“=”比较会出错，可以IN运算符进行比较。语法格式：WHERE表达式[NOT]IN(子查询)其中，当表达式与子查询的结果集中的任何一个值相等时，条件表达式的值为TRUE，否则为FALSE；如果使用了NOT，则条件表达式的值正好相反。IN子查询IN、NOTIN【例】查询所有住在A座寝室楼的学生的信息SQL语句：SELECT*FROMstudentWHEREdormidIN(SELECTdormidFROMdormWHEREapartment='A’);本示例还可以使用ANY关键字SQL语句：SELECT*FROMstudentWHEREdormid=ANY(SELECTdormidFROMdormWHEREapartment='A');MySQL数据库4.3.3EXISTS子查询EXISTS子查询EXISTS、NOTEXISTSEXISTS子查询不需要返回任何实际数据，而只需要返回一个逻辑值（逻辑真值“TRUE”或逻辑假值“FALSE”）。也就是说，它的作用是在WHERE子句中测试子查询返回的行是否存在。如果存在则返回真值，否则返回假值。

EXISTS子查询EXISTS、NOTEXISTS【例】如果A座102寝室有同学入住，查询该寝室的朝向，如果该寝室空闲，则不查询。SQL语句：SELECTtowardFROMdormWHEREapartment='A'ANDroomno='102'ANDEXI