第七章 数据查询基础.docx
- 文档编号:2414228
- 上传时间:2023-05-03
- 格式:DOCX
- 页数:16
- 大小:26.37KB
第七章 数据查询基础.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
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 第七章 数据查询基础 第七 数据 查询 基础