内部学习严禁外传 第3章 MySQL查询和视图 应用数据库数据是建立数据库的出发点和立足点,查询数据库数据是应用数据库数据的基本 操作。在 MySQL中,对数据库的查询使用 SELECT语句,功能非常强大、使用较为灵活。 可以把经常查询的操作定义为视图,它相当于一个逻辑表,可以用操作表的方式操作视图 本章介绍查询和视图 3.1 My SQL数据库查询 在第2章学生成绩数据库(xsqj)已经创建了学生表(xs)并且输入了若干条记录,用户可以 采用命令或者图形界面工具创建课程表(kc)和学生成绩表(xskc),并且输入若干条记录。表 结构如附录A所示 SELECT语句可以从一个或多个表中选取符合某种条件的特定的行和列,结果通常是生成 个临时表。下面介绍 SELECT 它是SQL的核心。 SELECT语句的语法格式如下 ALL DISTINCT DISTIN [WHERE条件 GROUP BY{列名|表达式| position)[ ASC DESC [ HAVING条件] [ ORDER BY{列名表达式| position}[AsC|DEsc], PROCEDURE存储过程名(参数.) [ INTO OUTFILE·文件名[ CHARACTER SET字符集 export options| INTO DUMPFILE'文件名’|IN0变量名 [FOR UPDATE I LOCK IN SHARE MODEI 下面具体介绍一下 SELECT语句中包含的几个常用的子句 3.11选择输出列 SELECT语句中需要指定查询的列
MSQL数据库教程(视频指导版 选择指定的列 使用 SELECT语句选择一个表中的某些列,各列名之间要以逗号分隔,所有列用“*”表示 其语法格式为如下 SELECT*列名,列名,…frm表名 【例31】查询xscj数据库的xs表中各个同学的姓名、专业名和总学分 use xsc select姓名,专业名,总学分 from xs 执行结果是xs表中全部学生的姓名、专业名和总学分列上的信息。 说明 2.定义列别名 当希望查询结果中的列显示时使用自己选择的列标题,可以在列名之后使用AS子句,语法 格式如下 SELECT……列名[AS列别名 【例3.2】查询xs表中计算机专业同学的学号、姓名和总学分,结果中各列的标题分别指定 为 number、name和mark,语法格式如下 select学号 as number,姓名 as name,总学分 as mark where专业名=·计算机 结果如图3.1所示 811 图3.1查询xs表结果 当自定义的列标题中含有空格时,必须使用引号将标题括起来。例如 select学号as' student number',姓名as' student name',总学分 as mark from xs where专业名=·计算机'; 不允许在 WHERE子句中使用列别名。这是因为执行 WHERE代码时,可能尚未确 定列值。例如,这个查询是非法的 select性别asse 说明 from xs where sex = 0;
当第3章MsOL查询和视图 3.替换查询结果中的数据 要替换查询结果中的数据,则要使用查询中的CASE表达式,语法格式如下 CASE WHEN条件1THEN表达式 HEN条件2THEN表达式2 ELsE表达式 【例3.3】査询ⅹs表中计算机专业各同学的学号、姓名和总学分,对总学分按如下规则进行替 若总学分为空值,替换为“尚未选课”; 若总学分小于50,替换为“不及格”; 若总学分在50~52之间,替换为“合格 若总学分大于52,替换为“优秀”; 总学分列的标题更改为“等级”。 替换操作代码如下 elect学号,姓名 when总学分isnu1 L then'尚未选课 when总学分=50and总学分<=52then合格 e1se"优秀 trom xs here专业名计算机 执行结果如图3.2所示 号:姓名:等级 图3.2替换结果 4.计算列值 SELECT的输出列可使用表达式,格式如下: SELECT表达式 【例34】按120分制重新计算成绩,显示xskc表中学号为081101的学生成绩信息,代码如
MSQL数据库教程(视频指导版 select学号,课程号,成绩*1.20as成绩120 from xs k where学号=08110 执行结果如图3.3所示。 学号:课程号:成绩128 图3.3重新计算结 5.消除结果集中的重复行 对表只选择其某些列时,输出的结果可能会出现重复行。可以使用 DISTINCT或 DISTINCTROW关键字消除结果集中的重复行,格式如下 SELECT DISTINCT| DISTINCTROW列名 【例35】对xsj数据库的xs表只选择专业名和总学分,消除结果集中的重复行,代码如下 select distinct专业名,总学分 执行结果如图34所示。 图3.4消除重复行 6.聚合函数 SELECT I的输出列还可以包含所谓的聚合函数。 聚合函数常常用于对一组值进行计算,然后返回单个值。除 COUNT函数外,聚合函数都会 略空值。表3.1列出了一些常用的聚合函数。 表3.1 常用聚合函数 函数名 COUNT 求组中项数,返回int类型整数 MAX 求最大值 MIN 求最小值 返回表达式中所有值的和 AVG 求组中值的平均值 STD或 STDDEV 返回给定表达式中所有值的标准差 VARIANCE 返回给定表达式中所有值的方差
当第3章MsOL查询和视图 续表 函数名 GROUP CONCAT 返回由属于一组的列值连接组合而成的结果 BIT AND 逻辑或 bIT OI 逻辑与 BIT XOR 逻辑异或 (1) COUNTO函数 COUNTO函数用于统计组中满足条件的行数或总行数,返回 SELECT语句检索到的行中非 NULL值的数目,若找不到匹配的行,则返回0。 COUNTO函数的语法格式 COUNT(f[ALL| DISTINCT]表达式)1* 其中,表达式的数据类型可以是除BLOB或TEXT之外的任何类型。AL表示对所有值进行 运算, DISTINCT表示去除重复值,默认为ALL。使用 COUNT(*)时将返回检索行的总数目,不 论其是否包含NULL值 【例3.6】求学生的总人数。 select count(*)as!学生总数 from xs; 执行结果如图3.5所示。 3.5执行结果 例37】统计备注不为空的学生数 select count(备注)as备注不为空的学生数目 执行结果如图3.6所示。 备注不为空的学生数目 图3.6执行结果 这里 COUNT(备注)计算时备注为NULL的行被忽略,所以这里是7而不是22。 法意 【例38】统计总学分在50分以上的人数 select count(总学分)as'总学分50分以上的人数 here总学分>50
MSQL数据库教程(视频指导版 执行结果如图3.7所示。 总学分5分以上的人数 图3.7执行结果 (2)MAXO函数和MINO函数 MAXO函数和MINO函数分别用于求表达式中所有值项的最大值与最小值,语法格式为 表达式) 【例39】求选修101课程的学生的最高分和最低分 select max(成绩),min(成绩) where课程号=101 执行结果如图3.8所示。 图3.8执行结 当给定列上只有空值或检索出的中间结果为空时,MAX和MN函数的值也为空。 油意 (3)SUMO两数和AVG(函数 SUMO函数和AVGO函数分别用于求表达式中所有值项的总和与平均值,语法格式为 表达式) 【例3.10】求学号081101的学生所学课程的总成绩。 select sum(成绩)as课程总成绩 from xs kc where学号="081101'; 执行结果如图39所示。 图39执行结果 【例3.11】求选修101课程的学生的平均成绩。 select avg(成绩)as'课程101平均成绩 from xs kc where课程号=1101 执行结果如图3.10所示
当第3章MsOL查询和视图 课程11平均成绩 图3.10执行结果 (4) VARIANCEO函数和 STDDEVO函数 VARIANCEO函数和 STDDEVO函数分别用于计算特定的表达式中的所有值的方差和标准差。 语法格式 ARIANCE/ STDDEV([AL| DISTINCA]表达式) 【例3.12】求选修101课程的成绩的方差。 select variance(成绩 from xs kc 执行结果如图3.11所示。 B5.2274999999999 图3.11执行结果 方差的计算按以下几个步骤进行 计算相关列的平均值 求列中的每一个值和乎均值的差 说明 计算差值的平方的总和 用总和除以(列中的)值的个数得到结果。 STDDEVO函数用于计算标准差。标准差等于方差的平均根。所以 STDDEV()和 SQRT( VARIANCE(…)这两个表达式是相等的。 【例3.13】求选修101课程的成绩的标准差。 select stade(成绩 from xs kc where课程号=1101'; 执行结果如图3.12所示。 16.25845622826991 图3.12执行结果 其中, stddev可以缩写为std,这对结果没有影响 (5) GROUP CONCATO函数 MySQL支持一个特殊的聚合函数 GROUP CONCAT。该函数返回来自一个组指定列的所有 非NUL值,这些值一个接着一个放置,中间用逗号隔开,并表示为一个长长的字符串。这个字
MSQL数据库教程(视频指导版 符串的长度是有限制的,标准值是1024。 语法格式 OUP CONCAT(([ ALL DISTINC]表达式}1*) 【例3.14】求选修了206课程的学生的学号。 select group concat(学号) 课程 执行结果如图3.13所示。 group concat(学号) 881161,881182.081163,881184681106,88118?,881188,881189,881118,881111,881113 图3.13执行结果 (6) BIT ANDO函数、 BIT ORO函数和 BIT XORO函数。入 与二进制运算符|(或)、&(与)和^(异或)相对应的聚合函数也存在,分别是 BIT OR BIT AND、 BIT XOR。 语法格式 BIT⊥ AND BIT OR BIT XOR( ALL I DISTINCT表达式}1·) 【例3.15】有一个表bis,其中有一列 bin value上有3个 INTEGER值:1、3、7,获取在该 列上执行 BIT OR的结果,使用如下语句 select bin(bit or(bin value)) from bits; MySQL在后台执行表达式:(001011)11,结果为111其中,bin函数用于将结 果转换为二进制位 312数据来源:FROM子句 FROM子句可以制定 SELECT查询的对象 1.引用一个表 用户可以用如下两种方式引用表。 第一种方式是使用USE语句让一个数据库成为当前数据库,FROM子句中指定表名应该属于 当前数据库 第二种方式是指定表名前带上表所属数据库的名字。 例如,假设当前数据库是dbl,现在要显示数据库db2里的表tb的内容,使用如下语句 db2. tb 当然,在 SELECT指定列名也可以在列名前带上所属数据库和表的名字,但是一般来说 果选择的字段在各表中是唯一的,就没有必要去特别指定。 2.多表连接 如果要在不同表中查询数据,则必须在FROM子句中指定多个表,这时就要用到连接。将不
当第3章MsOL查询和视图 同列的数据组合到一个表中叫作表的连接 连接的方式有以下两种。 (1)全连接 将各个表用逗号分隔,就指定了一个全连接。FROM子句产生的中间结果是一个新表,新表 是每个表的每行都与其他表中的每行交叉以产生所有可能的组合。这种连接方式会潜在地产生数 量非常大的行,因为可能得到的行数为每个表行数之积! 使用 WHERE子句设定条件将结果集减少为易于管理的大小,这样的连接即为等值连接。 【例316】查找ⅹs数据库中所有学生选过的课程名和课程号。使用如下语句 select distinct kc.课程名,xskc.课程号 from kC, xs kc where ko.课程号=xske.课程号; 执行结果如图3.14所示。 课程名 课程 投计写语言 图3.14执行结果 (2)JON连接 JON连接的语法格式 J0IN表ON连接条件 使用I0N关键字的连接主要分为如下三种 ①内连接。指定了 INNER关键字的连接是内连接 【例3.17】查找xsej数据库中所有学生选过的课程名和课程号。 可以使用以下语句 select distinct课程名,x5kc.课程号 from kc inner join xs kc on(ke.课程号=x5ke.课程号 它的功能是合并两个表,返回满足条件的行。内连接是系统默认的,可以省略 INNER关键 字。使用内连接后,FROM子句中ON条件主要用来连接表,其他并不属于连接表的条件可以使 用 WHERE子句来指定 【例3.18】查找选修了206课程且成绩在80分以上的 姓名及成绩 select姓名,成绩 from xs join xs kc on xs.学号=xske.学号 ere课程号 执行结果如图3.15所示 图3.15执行结果
MSQL数据库教程(视频指导版 内连接还可用于多个表的连接 【例3.19】査找选修了“计算机基础”课程且成绩在80分以上的学生学号、姓名、课程名及 成绩。 select xs.学号,姓名,课程名,成绩 from xs join xs kc on xs.学号=xskc.学号 join kc on xs kc.课程号=kc.课程号 here课程名 算机基础'and成绩>=80 执行结果如图3.16所示。 学号:姓名!课程名 图3.16执行结果 作为特例,可以将一个表与它自身进行连接,称为自连接。若要在一个表中查找具有相同列 值的行,则可以使用自连接。使用自连接时需为表指定两个别名,且对所有列的引用均要用别名 【例3:20】查找ⅹs数据库中课程不同、成绩相同的学生的学号、课程号和成绩。 学号a课程号,课程减 join xs kc as b on9成绩中,成绩ana.,学号b,学号anaa,课程号!=b,课程号 执行结果如图3.17所示 号:课程号:课程号1成 图3.17执行结果 如果要连接的表中有相同列名,并且连接的条件就是列名相等,那么ON条件也可以换成 USING子句。USⅠNG(两表中相同的列名)子句用于为一系列的列进行命名。 【例3.21】查找kc表中所有学生选过的课程名 select from kc inner join xs kc using(课程号) 查询的结果为xske表中所有出现的课程号对应的课程名 说明 ②外连接。指定了 OUTER关键字的连接为外连接,其中的 OUTER关键字均可省略