第七章 数据查询基础.docx
《第七章 数据查询基础.docx》由会员分享，可在线阅读，更多相关《第七章 数据查询基础.docx（16页珍藏版）》请在冰豆网上搜索。
第七章数据查询基础
第七章数据查询基础
本章工作任务
✓查询学生信息
✓查询成绩信息
本章技能目标
✓理解查询的机制
✓使用SELECT语句进行条件查询
✓掌握查询排序
✓使用表达式、运算符和函数实现查询
本章简介
前一章学习了什么是SQL，并且介绍了SQL中的运算符，这些运算符除了在约束中使用之外，也经常会在UPDATE和DELETE语句中使用，此外还介绍了对数据库表进行增删改的SQL注意事项。
以及在操作MySQL数据时，经常使用到的一些函数，如字符处理函数，数学函数，以及日期函数等等。
预习作业
请按以下步骤预习本章内容：
1.带着以下任务，阅读、学习本章的相关资料
2.标注出本章看不懂或存在疑惑的部分
3.整理、记录学习中的问题
4.熟记本章的英文单词
请到中心听课前，完成下面的作业。
1.背诵英文单词
（1）recordset:
（2）order:
（3）null:
（4）trim:
（5）replace:
（6）floor:
（7）ceiling:
（8）round:
（9）cast:
（10）length:
2.预习并回答以下问题
阅读本章内容，在作业本上完成以下简答题
（1）从学生表中查询全部数据，SQL语句应怎么写?
（2）查询排序使用的关键字是什么?
3.预习并完成编码题
请按照id的倒序排列查询该表全部信息，表结构如图所示。
4.记录预习的问题
记录学习中遇到的问题，填写于下面的横线上，以便集中探讨解决
——————————————————————————————————————
——————————————————————————————————————
——————————————————————————————————————
7.1SQL查询基础
7.1.1查询机制简介
在讲解查询之前，有必要说明一下查询的机制和查询的结果。
查询是针对表中已经存在的数据行而言的，可以简单的理解为“筛选”，将一定条件的数据抽取出来。
数据表在接收查询请求的时候，可以简单的理解为“它将逐行判断”，判断是否符合查询条件。
如果符合查询条件就提取出来，然后把所有被选中的行组织在一起，形成另外一个类似于表的结构，构成查询的结果，通常叫做记录集（RecordSet）
由于记录集类似于表，所以记录集可以再次查询。
7.1.2使用SELECT语句进行查询
语法：
SELECT<列名>FROM<表名>[WHERE<查询条件>]
[ORDERBY<排序的列名>[ASC或者DESC]]
1.查询所有的行和列，如：
select*fromStudent
2.查询部分行或列，如：
selectStudentNo,StudentName,AddressfromStudentwhereStudentNo=1
3.在查询中使用列的别名，如：
selectStudentNoas学号fromStudent,也可以拼接两个列，如：
select（firstname+’.’+lastname）as全名fromemployees
4.查询空值，如：
select*fromStudentwhereEmailisnull
5.在查询中使用常量列，如：
selectStudentName,’新乡中学’as学校fromStudent
6.查询返回限制行数，如：
select*fromStudentlimit0,5
（只显示学生表的前5行，从索引号0的数据行开始，得到索引号连续的5行数据）
7.2查询排序
如果需要按照一定顺序排列语句选中的行，则需要使用ORDERBY子句，并且排序可以是升序（ASC）或者降序（DESC）。
如果不指定ASC或者DESC，记录集按照默认的升序排序。
上述的SQL语句，都可以在其后加上ORDERBY子句进行排序。
例如，查询学生成绩的时候，如果把所有成绩都降低10%后加5分，再查询及格成绩并且按照成绩高低进行排列，SQL语句如下：
SELECTStudentNoas学号,（StudentResult*0.9+5）as综合成绩fromResultwhere（StudentResult*0.9+5）>60orderby（StudentResult*0.9+5）DESC
7.3在查询中使用函数
7.3.1字符串函数（如表7-1）
函数名
描 述
示 例
LOWER
把传递给它的字符串转换为小写
SELECTLOWER（'MySQL课程'）
返回：
mysql课程
Char_length
返回传递给它的字符串长度
SELECTchar_length（'SQL'）
返回：
3
UPPER
把传递给它的字符串转换为大写
SELECTUPPER（'mysql'）
返回：
MYSQL
LTRIM和RTRIM
清除字符左（右）边的空格
SELECTLTRIM（'周智宇'）
返回：
周智宇（后面的空格保留）
LOCATE
获取指定字符串在源字符串中的位置
SELECTLOCATE（‘-’,’2000-1’）
返回：
5
RIGHT和LEFT
从字符串右（左）边返回指定数目的字符
SELECTRIGHT（'买卖提.吐尔松',3）
返回：
吐尔松
REPLACE
替换一个字符串中的字符
SELECTREPLACE（'莫乐可切.杨可','可','兰'）
返回：
莫乐兰切.杨兰
SUBSTRING
截取指定长度的子字符串
SELECTsubstring（'ABCDEFG',2,3）
返回：
BCD
表7-1
7.3.2日期函数（如表7-2）
函数名
描 述
示 例
CURDATE或NOW
取得当前的系统日期
SELECTCURDATE（）
返回：
今天的日期（仅返回年月日）,NOW（）精确到秒
DATE_ADD
将指定的数值添加到指定的日期部分后的日期
SELECTDATE_ADD（‘1982-1-2’,INTERVAL31DAY）
返回：
1982-2-2
DATE_FORMAT
日期格式化
SELECTDATE_FORMAT（curdate（）,’%y%m%d%T’）
返回：
格式化显示当前系统时间
DATEDIFF
返回两个指定日期的差值，单位：
天
SELECTDATEDIFF（CURDATE（）,’1982-1-1’）/365
返回：
32.6164
DATE_SUB
将指定的数值减去指定的日期部分后的日期
SELECTDATE_SUB（‘1982-2-2’,interval31day）
返回：
1982-1-2
表7-2
说明：
（1）DATE_FORMAT函数中使用到的格式描述符：
●%d月的天，数值（00-31）
●%e月的天，数值（0-31）
●%H小时（00-23）
●%h小时（01-12）
●%i分钟，数值（00-59）
●%j年的天（001-366）
●%m月，数值（00-12）
●%r时间，12-小时（hh:
mm:
ssAM或PM）
●%S秒（00-59）
●%s秒（00-59）
●%T时间,24-小时（hh:
mm:
ss）
●%W星期名
●%w周的天（0=星期日,6=星期六）
●%Y年，4位
（2）DATE_ADD的语法说明
DATE_ADD（指定起始时间,INTERVAL数量单位），
如：
DATE_ADD（‘2014-1-1’,INTERVAL31DAY）结果是2014-2-1
如：
DATE_ADD（'1982-1-112:
0:
0',interval2HOUR）结果是1982-01-0114:
00:
00
其中该函数最后的“单位”包含以下可用值：
单位名称
描 述
MICROSECOND
毫秒
SECOND
秒
MINUTE
分钟
HOUR
小时
DAY
日
WEEK
星期
MONTH
月
YEAR
年
7.3.3数学函数（如表7-3）
函数名
描 述
示 例
RAND
返回从0到1之间的随机float值
SELECTRAND（）
返回：
0.79288062146374
ABS
取数值表达式的绝对值
SELECTABS（-43）
返回：
43
CEILING
取大于或等于指定数值、表达式的最小整数
SELECTCEILING（43.5）
返回：
44
FLOOR
取小于或等于指定表达式的最大整数
SELECTFLOOR（43.5）
返回：
43
POWER
取数值表达式的幂值
SELECTPOWER（5,2）
返回：
25
ROUND
将数值表达式四舍五入为指定精度
SELECTROUND（43.543,1）
返回：
43.500
SIGN
对于正数返回+1，对于负数返回-1，对于0则返回0
SELECTSIGN（-43）
返回：
-1
SQRT
取浮点表达式的平方根
SELECTSQRT（9）
返回：
3
表7-3
7.3.4系统函数（如表7-4）
函数名
描 述
示 例
CAST或CONVERT
用来转变数据类型
SELECTCAST（‘1982-1-1’asDATE）或者
SELECTCONVERT（'1982-1-1',DATE）
返回：
将指定字符串转换成DATE类型
CURRENT_USER
返回当前用户的名字
SELECTCURRENT_USER（）
返回：
你登录的用户名
LENGTH
返回用于指定表达式的字节数
SELECTLENGTH（'中国'）
返回：
4
BIT_LENGTH
返回用于指定表达式的位数
SELECTBIT_LENGTH（‘中国’）
返回：
32
表7-4
说明：
CAST或CONVERT转换时类型：
●二进制，同带binary前缀的效果:
BINARY
●字符型，可带参数:
CHAR（）
●日期:
DATE
●时间:
TIME
●日期时间型:
DATETIME
●浮点数:
DECIMAL
●整数:
SIGNED
●无符号整数:
UNSIGNED
7.4数据查询的案例分析
以下给出了两个实际的案例，这些案例都用到了本课程中已经讲述过的内容。
7.4.1案例1：
更新用户卡信息
（1）需求：
某公司印了一批充值卡，卡的密码是随机生成的，现在出现这个问题：
卡里面的字母“O和数字0”、“字母i和数字1”，用户反映说看不清楚，公司决定，把存储在数据库中的密码中所有的“O”都改成“0”，把所有的“i”都改成“1”；
请编写SQL语句实现以上要求
（2）说明：
数据库表名：
Card
密码列名：
PassWord
（3）分析：
实现卡密码更新的功能，需要使用UPDATE语句
牵涉到字符串的替换，需要使用到MySQL中的函数REPLACE（）
两条SQL语句
UPDATECardSETPassWord=REPLACE（PassWord,'O','0'）
UPDATECardSETPassWord=REPLACE（PassWord,'i','1'）
也可以使用一条SQL语句
UPDATECardSETPassWord=REPLACE（REPLACE（PassWord,'O','0'）,'i','1'）
7.4.2案例2：
特殊排序
（1）需求：
在数据库表中有以下字符数据，如：
13-1、13-2、13-3、13-10、13-100、13-108、13-18、13-11、13-15、14-1、14-2
现在希望通过SQL语句进行排序，并且首先要按照前半部分的数字进行排序，然后再按照后半部分的数字进行排序，输出要排成这样：
13-1、13-2、13-3、13-10、13-11、13-15、13-18、13-100、13-108、14-1、14-2
（2）说明：
数据库表名：
SellRecord
列名：
ListNumber
（3）分析：
排序：
ORDERBY
在ORDERBY的排序中,需要重新算出排序的数字
前半部分的数字,找到“-”符号的位置,然后取其左半部分
使用CAST函数将其转换为数字
CAST（LEFT（ListNumber,locate（'-',ListNumber）-1）assigned）
后半部分的数字：
找到“-”符号的位置，取其右半部分，再使用CAST函数将其转换为数字
CAST（RIGHT（ListNumber,CHAR_LENGTH（ListNumber）-LOCATE（'-',ListNumber））assigned）
7.5技能训练
[上机练习1]
练习：
查询学生相关基本信息
需求说明：
⏹查询全部S1的学生信息
提示：
GradeId=1
⏹查询全部S2的学生姓名和电话
⏹查询全部S1女同学的信息
提示：
Sex='女'
⏹查询课时超过60的科目信息
[上机练习2]
练习：
查询学生相关复杂信息
需求说明：
⏹查询S2科目名称
⏹查询S2男同学的姓名和住址
⏹查询无电子邮件的学生姓名和年级信息
⏹查询出生日期在1993年之后的S2的学生姓名和年级信息
⏹查询参加了日期为2013年2月15日的“HTML和CSS网页技术”科目考试的成绩信息
难点分析：
注意各个表之间的关系，例如通过查看S1的年级编号在科目表中查询对应科目
SELECTGradeIdFROMGradeWHEREGradeName='S1'
[上机练习3]
练习：
使用排序查询学生相关信息
需求说明：
--按照出生日期查询S1的学生信息。
--按日期由前到后、成绩由高到低的次序查询参加编号为1的科目考试信息。
--查询2013年3月22日参加“面向对象程序设计”考试的前5名学员成绩信息。
SELECT*FROMResultWHERESubjectId=8ANDExamDate='2013-3-22'ORDERBYStudentResultDESCLIMIT0,5
--查询y2的课时最多的科目名称及课时。
--查询年龄最小的学生所在的年级及姓名。
--查询2013年3月22日参加考试的最低分出现在哪个科目。
--查询学号为“S1101003”的学生参加过的所有考试信息，并按照时间前后次序显示。
--查询学号为“S1101003”的学生参加过的所有考试中最高分及时间、科目
本章总结
◆查询将逐行筛选表中的数据，最后将符合要求的记录重新组合成“记录集”，记录集的结构类似于表结构。
◆使用ISNULL判断一行中的某个列是否为空。
◆使用ORDERBY进行查询记录集的排序，并且可以按照多个列进行排序。
◆在查询中，可以使用常量、表达式、运算符。
◆在查询中使用函数，能够像程序中那样处理数据。
本章作业
一．选择题
1．Employee表中有三列：
EmpId，EmpGrade，EmpsalaryGrade，并且列值都是整型数据类型，则以下（）中的语句能正确执行。
A、selectEmpIdfromEmployeeorderbyEmpIdwhereEmpId=EmpGrade
B、selectEmpIdfromEmployeewhereEmpId=EmpGrade=EmpSalaryGrade
C、selectEmpIdfromEmployeeorderbyEmpGrade+EmpSalaryGrade
D、selectEmpId,EmpSalaryGradefromEmployeewhereEmpGrade+EmpSalaryGrade
2．表Employee中，LastName列保存顾客的姓，FirstName列保存顾客的名。
现在需要查询顾客姓名的组合，例如，某行数据中LastName的值为“张”，FirstName的值为“国华”，查询结果应该返回“张国华”，则正确的查询语句为（）。
A.selectLastname,Firstnamefromemployee
B.select*fromemployeeorderbyLastName,FirstName
C.selectlastname+firstnamefromemployee
D.selectlastnameandfirstnamefromemployee
3．现在Students表中已经储存了数据，Nation列的数据储存了学生的民族信息，默认值应该为“汉族”，可是在设计表的时候没有考虑，现在已经输入了大量的数据。
对于少量民族的学生，民族的信息已经输入；对于“汉族”的学生，数据都为空。
此时，如何解决这个问题（）。
A.在表中为该列添加notnull的约束
B.使用“updatestudentssetNation= ‘汉族’whereNationisnull”更新数据
C.使用“updatestudentssetdefault=‘汉族’”更新数据
D.手动输入所有的“汉族”信息
4．一个小组正在开发大型的银行存款系统，系统中包含上百万条客户信息。
现在正在调试SQL语句，以进行查询的优化。
可是，他们每次执行查询时，都返回好几百万条数据，显示查询结果非常耗时，此时，比较好的解决办法是（）。
A.删除这些数据，只保留几行
B.把这些数据转换到文本文件中，再在文本文件中执行查找
C.在查询语句中使用它LIMIT子句限制返回行
D.在查询语句中使用orderby进行排序
5．执行以下SQL语句：
SelectpercentSName,SAddressfromStudentslimit0,40
结果返回了20条数据，则（）。
A.表中只有40条数据
B.表中只有20条数据
C.表中大约有50条数据
D.表中大约有100条数据
6．在Math表中有Ori和Dest两列，要把Ori列的平方根写到Dest列，正确的SQL语句为（）
A.updateMathsetDest=SQRT（Ori）
B.updateMathsetOri=Ori/2
C.selectDestfromMathsetDest=Ori.SQRT
D.selectOrifromMathSetDest=Ori/2
7．以下（）能够得到今天属于哪个月份。
A.selectDate_Diff（CURDATE（）,’%m’）
B.selectDate_FORMAT（CURDATE（）,’%m’）
C.selectDate_Part（CURDATE（）,’%m’）
D.selectDate_Name（CURDATE（）,’%m’）
8．以下（）能够在结果集中创建一个新列“查询用户”，并且使用sql当前用户名来填充列值。
A.SelectSname,‘User’as查询用户fromStudents
B.SelectSname,SYSTEM_USER（）as查询用户fromStudents
C.SelectSname,查询用户fromStudents
D.SelectSname,SYSTEM_USER（）=查询用户fromStudents
二、简答题
1.如何查询非空行的数据？
2.常用的日期函数有哪些？
3.如何对查询结果进行排序？
三、编程题
1.完成本章7.4部分的两个案例
2.需求变更：
假设7.4案例二中数据为
13-1、13-2、13-3、13-10、13-100、13-108、13-18、13-11、13-15、14-1、14-2、14-90
要求查询出右半部分大于等于10的数据，并按照右半部分倒叙排列，如结果：
13-108、13-100、14-90、13-18、13-15、13-11、13-10
3.将7.4案例二中的数据中的减号替换成英文字母SP，如：
13-1变成13SP1
升级会员 