《MySQL数据库项目式教程》项目五 数据查询

MySQL项目五数据查询2019年7月掌握常用聚合函数的使用方法学习单表查询与多表查询的方法学习目标MySQL熟悉select语句的基本语法具有使用Navicat工具完成数据查询的能力重点、难点重点MySQLselect语句的基本语法一一、select语句的基本语法MySQLselect[all|distinct]要查询的内容from表名列表[where条件][groupby字段列表[having分组条件]][orderby字段列表[asc|desc]][limit[offset,]n];语法格式：一、select语句的基本语法MySQLfrom表名列表：用于指定查询的数据表的名称以及它们之间的逻辑关系。where条件：用于按指定条件进行查询。groupby字段列表：用于指定将查询结果根据什么字段进行分组。having分组条件：用于指定对分组的过滤条件，选择满足条件的分组记录。orderby字段列表[asc|desc]：用于指定查询结果集的排序方式，默认为升序。asc用于表示结果集按指定的字段升序排列，desc表示结果集按指定的字段以降序排列。limit[offset,]n：用于限制查询结果的数量。参数说明：一、select语句的基本语法MySQL在上述语法结构中，select语句共有6个子句，其中select和from子句为必选子句，而where、groupby、orderby和limit子句为可选子句，having子句与groupby子句联合使用，不能单独使用注意：MySQL单表查询二二、单表查询MySQL1.查询所有字段select*from表名;语法格式：select*fromstudent;示例语句：二、单表查询MySQL2.查询指定字段select字段名列表from表名;语法格式：selectstuNo,name,sexfromstudent;示例语句：二、单表查询MySQL3.定义查询字段的别名select字段名[as]别名from表名;语法格式：selectstuNo学号,name姓名,sex性别fromstudent;示例语句：二、单表查询MySQL4.消除查询结果集中重复行selectdistinct字段名列表from表名;语法格式：selectdistinctspecfromstudent;示例语句：二、单表查询MySQL5.where子句select字段名列表from表名where条件;语法格式：select*fromstudentwheresex='女';带比较运算符的where条件示例语句：注意：MySQL支持的比较运算符有=、>、<、>=、<=、<>或!=等二、单表查询MySQL5.where子句select*fromstudentwherespec='电子商务'andsex='男';带逻辑运算符的where条件示例语句：注意：逻辑运算用于将多个表达式通过逻辑运算符非、与、或（！或NOT、&&或and、||或OR）来组成更为复杂的查询条件二、单表查询MySQL5.where子句select*fromstudentwherenamelike'王%';带LIKE关键字的where条件示例语句：注意：LIKE关键字用于模糊查询，它有两种通配符：“%”、和下划线“_”。“%”可以匹配一个或多个字符，而下划线“_”则表示匹配一个字符。二、单表查询MySQL5.where子句select*fromstudentwherebirthdaybetween'2001-01-01'and'2001-12-31';带betweenand关键字的where条件示例语句：注意：betweenand用于判断指定字段的值是否在指定的范围内。如果字段的值在指定范围内，则满足查询条件的记录将会被查询出来；如果不在指定范围内，则不满足条件。二、单表查询MySQL5.where子句select*fromstudentwherestuNoIN('190003','190005');带IN关键字的where条件示例语句：注意：IN关键字用于判断某个字段的值是否在指定的集合中。如果字段的值在集合中，则满足条件的记录将被显示出来；如果不在集合中，则不满足条件。二、单表查询MySQL5.where子句select*fromstudentwherephoneisnotnull;带空值比较的where条件示例语句：注意：当需要判断一个表达式的值是否为空值时，可使用ISnull或ISNOTnull关键字。二、单表查询MySQL6.orderby子句select字段名列表from表名where条件orderby字段列表[asc|desc];语法格式：参数说明：字段列表：可以在orderby子句中指定多个字段，查询结果首先按照第一个字段的值排序，第一个字段值相同的数据行，再按照第二个字段值排序，依次类推。[asc|desc]：可以规定数据行按升序排序（使用参数asc），也可以规定数据行按降序排序（使用参数、desc），默认参数为升序。orderby子句要写在where子句的后面。二、单表查询MySQL7.limit子句[limit[offset,]n];语法格式：说明：limit子句是select语句的最后一个子句，主要用于限制查询结果集返回的行数select*fromstudentorderbybirthdaylimit3;示例语句：二、单表查询MySQL8.将查询结果插入到新表createtable<新表名>(select字段1,字段2,……from原表);语法格式：createtable：表示创建一个新表，专门用于保存查询结果。新表名：表示将查询结果保存至某个新表的名字，要注意的是，这个新表无须提前创建，系统会自动根据查询的结果集创建表的结构并添加记录。select：表示查询，可用“*”表示查询表的全部字段，该命令的具体用法将在后续的项目中学习。参数说明：二、单表查询MySQL9.将查询结果插入到已经存在的表insertinto<表名><子查询语句>;语法格式：<表名>：表示一个已经存在表，用于插入新的查询结果到这个表中。<子查询语句>：表示准备存放至新表的查询结果记录集。参数说明：insertintonewstudentselect*fromstudentwherestuNo='190003';示例语句：MySQL统计查询三三、统计查询MySQL1.聚合函数表达式：可以是常量、字段名、函数。all|distinct：all表示对所有值进行运算，distinct表示去除重复值，默认为all。count(*)：使用count(*)函数时将返回检索行的总数目，不论其是否包含null值。count(all|distinct表达式|*);Count函数语法格式：参数说明：selectcount(*)as学生总人数fromstudent;示例语句：三、统计查询MySQL1.聚合函数max和min函数分别用于统计表中满足条件的所有值项的最大值和最小值max/min(all|distinct表达式);max和min函数语法格式：参数说明：selectcouNoas课程号,max(result)as最高分,min(result)as最低分fromscorewherecouNo='g01';示例语句：三、统计查询MySQL1.聚合函数sum和avg函数分别用于统计表中满足条件的所有值项的总和与平均值。sum/avg(all|distinct表达式);sum和avg函数语法格式：参数说明：selectcouNoas课程号,sum(result)as总分,avg(result)as平均分fromscorewherecouNo='g01';示例语句：三、统计查询MySQL2.groupby子句groupby：该子句可以根据一个或多个字段进行分组。使用groupby子句进行分组统计时，select子句要查询的字段只能是以下两种情况：第一，字段应用了聚合函数；第二，未应用聚合函数的字段必须包含在groupby子句中。having子句与groupby子句的区别：第一，where子句设置的查询筛选条件在groupby子句之前发生作用，并且条件不能使用聚合函数；第二，having子句设置的查询筛选条件在groupby子句之后发生作用，并且条件中允许使用聚合函数。select字段名列表from表名[where条件]groupby字段列表|表达式[having分组条件];语法格式：参数说明：三、统计查询MySQL2.groupby子句selectcouNoas课程号,max(result)as最高分,min(result)as最低分fromscoregroupbycouNohavingcouNo='g01'||couNo='z02';groupby子句示例语句：MySQL多表查询四四、多表查询MySQL[表名.]目标字段名[as]别名：指显示的查询结果的字段名，若查询结果的字段在两个表之间的重名字段，则需要指定显示具体某个表的字段名，否则[表名]部分可以省略。on连接条件：指表与表之间连接的条件，一般是指表之间拥有相同的值的列。连接类型：主要包括内连接与外连接两种类型select[表名.]目标字段名[as]别名,……from表1[as别名]连接类型表2[as别名]on连接条件[where条件表达式];语法格式：参数说明：三、统计查询MySQL1.内连接select字段名列表from表1[as别名][inner]join表2[as别名]on表1.字段名比较运算符表2.字段名[where条件表达式];语法格式（1）：select字段名列表from表1[as别名],表2[as别名]where表1.字段名比较运算符表2.字段名语法格式（2）：三、统计查询MySQL1.内连接字段名列表：指显示的查询结果的字段名，若查询结果的字段在两个表之间的重名字段，则需要指定使用[表名.字段名]的格式。on连接条件：指表与表之间连接的条件，一般是指表之间拥有相同的值的列。参数说明：三、统计查询MySQL1.内连接selects.stuNo,c.couName,s.resultfromcourseascinnerjoinscoreassonc.couNo=s.couNo;示例语句（1）：selects.stuNo,c.couName,s.resultfromcourseasc,scoreasswherec.couNo=s.couNo;示例语句（2）：三、统计查询MySQL2.外连接select字段名列表from表1[as别名]left|right|fulljoin表2[as别名]on表1.字段名比较运算符表2.字段名;语法格式：参数说明：字段名列表与on：这里的字段名列表与on关键字的用法与内连接的用法一致，但外连接只适用于两个表。left：指左外连接，即左表为主表，连接关键字为leftjoin。right：指右外连接，即右表为主表，连接关键字为rightjoin。full：指全外连接，查询结果集中包括两个表的所有数据行。MySQL子查询五五、子查询MySQLselectstuNo,couNo,resultfromscorewhereresult>(selectavg(result)fromscoregroupbystuNohavingstuNo='190001');在学生成绩管理数据库mystudent中，查询比学号为“190001”的平均成绩的高的学生学号、课程号、成绩信息，示例语句如下：1.带比较运算符的子查询五、子查询MySQLselectstuNo,namefromstudentwherestuNo=any(selectstuNofromscorewhereresult<60);在学生成绩管理数据库mystudent中，查询考试有不及格的学生学号及姓名，示例语句如下：2.带IN关键字的子查询五、子查询MySQLselect*fromstudentwherestuNo=any(selectstuNofromscorewhereresult<60);在学生成绩管理数据库mystudent中，查询考试有不及格的学生基本信息，示例语句如下：3.批量比较子查询MySQL合并查询结果集六六、合并查询结果集MySQLselectstuNoas学号,nameas姓名fromstudentunionselectcouNoas学号,couNameas姓名fromcourse;在学生成绩管理数据库mystudent中，输出所有学生和课程的编号和名字信息，示例语句如下：MySQL使用Navicat工具实现数据查询七七、使用Navicat工具实现数据查询MySQL1.在左侧“连接树”工具栏中打开mystudent数据库，单击工作区中的“新建查询”按钮；图1新建查询七、使用Navicat工具实现数据查询MySQL2.在“查询创建工具”中可以选择要查询的表和字段，以及输入相应的查询条件；图2通过查询创建工具辅助创建查询七、使用Navicat工具实现数据查询MySQL3.在工作区中设置好相应的查询子句，再点击主界面上的“运行”按钮；图3在Navicat工具中运行查询七、使用Navicat工具实现数据查询MySQL4.此外也还可以在“查询编辑器”中直接输入查询的select语句直接运行；图4在查询编辑器工具中运行查询MySQL任务实施八八、任务实施MySQL1.查询所有电子商务专业所有男生的基本信息，SQL语句如下：select*