1、4数据查询(1)第章第1页,共30页。2回顾回顾v SQL分为数据操作语言、数据控制语言和数据定义语言;T-SQL是SQL的扩展v 通配符经常与LIKE一起使用来进行非精确的限制;v 一次插入多行数据,可以使用INSERTSELECT、SELECTINTO或者UNION关键字来实现v 标识列不能插入指定的数据值v 使用UPDATE更新数据,一般都有限制条件v 使用DELETE删除数据时,不能删除被外键值所引用的数据行v 使用TRUNCATE TABLE来删除所有数据时,效率要比DELETE语句高v 上面的数据操作语句在程序中会经常使用到第2页,共30页。3目标目标v 理解查询的机制v 使用SE
2、LECT语句进行条件查询v 在查询中返回限制行、进行查询排序v 在查询中使用表达式、运算符和函数第3页,共30页。4什么是查询?什么是查询?Application Logic客户程序查询请求查询结果集SQL SERVERA B C D E F G SELECT*FROM SALES查询产生一个虚拟表,看到的是表形式显示的结果,但结果并不真正存储,每次执行查询只是现从数据表中提取数据,并按照表的形式显示出来第4页,共30页。5怎么查的?怎么查的?学员编号学员编号学员姓名学员姓名地址地址所在班级所在班级.001001张明全张明全湖南长沙湖南长沙S201S201002002李菲李菲湖北宜昌湖北宜昌S
3、202S202003003于寄谦于寄谦甘肃天水甘肃天水S204S204004004刘国正刘国正山东荷泽山东荷泽S201S201005005周接轮周接轮台湾新竹台湾新竹S204S204006006巩小妹巩小妹香港龙湾香港龙湾S203S203007007巩大妹巩大妹香港龙湾香港龙湾S203S203008008张明敏张明敏北京顺义北京顺义S202S202009009矛十八矛十八四川棉阳四川棉阳S204S204010010罗林光罗林光陕西临潼陕西临潼S202S202011011司马坡司马坡新疆喀什新疆喀什S201S201S202陕西临潼陕西临潼罗林光罗林光010S202湖北宜昌湖北宜昌李菲李菲002S
4、202北京顺义北京顺义张明敏张明敏008SELECT*FROM StudentsH WHERE 所在班级=S202第5页,共30页。6编写查询语句之前编写查询语句之前第6页,共30页。7查询查询SELECT FROM WHERE ORDER BY ASC或或DESC列名称列名称SELECT SCode,SName,SAddress表名表名FROMStudents过滤条件过滤条件WHERESSEX=0排序条件排序条件ORDER BYSCodeSELECT SCode,SName,SAddress FROM Students WHERE SSEX=0 ORDER BY SCode第7页,共30页。
5、8数据查询数据查询-基础基础查询全部的行和列SELECT*FROM Students 查询部分行SELECT SCode,SName,SAddress FROM StudentsWHERE SAddress=河南新乡SELECT SCode,SName,SAddress FROM StudentsWHERE SAddress 河南新乡第8页,共30页。9数据查询数据查询-列名列名使用AS来命名列SELECT SCode AS 学员编号,SName AS学员姓名,SAddress AS 学员地址 FROM StudentsWHERE SAddress 河南新乡SELECT FirstName+.
6、+LastName AS 姓名 FROM Employees使用=来命名列SELECT 姓名=FirstName+.+LastName FROM Employees第9页,共30页。10数据查询数据查询-空行常量列空行常量列查询空行SELECT SName FROM Students WHERE SEmail IS NULL使用常量列SELECT 姓名=SName,地址=SAddress,河北新龙 AS 学校名称FROM Students思考:非空行怎么查?第10页,共30页。11数据查询数据查询-限制行数限制行数限制固定行数SELECT TOP 5 SName,SAddressFROM St
7、udents WHERE SSex=0返回百分之多少行SELECT TOP 20 PERCENT SName,SAddressFROM Students WHERE SSex=0第11页,共30页。12数据查询数据查询-排序排序 2-1升序排列SELECT StudentID As 学员编号,(Score*0.9+5)As 综合成绩FROM ScoreWHERE(Score*0.9+5)60ORDER BY Score降序排列SELECT Au_Lname+.+Au_fName AS EMP From Authors UnionSELECT fName+.+LName AS EMP From
8、EmployeeORDER BY EMP DESC第12页,共30页。13数据查询数据查询-排序排序 2-2按多列排序SELECT StudentID As 学员编号,Score As 成绩FROM ScoreWHERE Score60ORDER BY Score,CourseID思考:排序中的字段,可以使用表达式吗?如果不可以,请说明原因;如果可以,请举例说明;第13页,共30页。14SQL Server中的函数中的函数相当于C语言中的内部函数q字符串函数q日期函数q数学函数q系统函数第14页,共30页。15字符串函数字符串函数SELECT STUFF(ABCDEFG,2,3,我的音乐我的世
9、界)返回:A我的音乐我的世界EFG在一个字符串中,删除指定长度的字符,并在该位置插入一个新的字符串STUFFSELECT CHARINDEX(ACCP,My Accp Course,1)返回:4用来寻找一个指定的字符串在另一个字符串中的起始位置CHARINDEX举例举例描述描述函数名函数名SELECT LEN(SQL Server课程)返回:12返回传递给它的字符串长度LENSELECT LOWER(SQL Server课程)返回:sql server课程把传递给它的字符串转换为小写LOWERSELECT UPPER(sql server课程)返回:SQL SERVER课程把传递给它的字符串转
10、换为大写UPPERSELECT LTRIM(周智宇 )返回:周智宇 (后面的空格保留)清除字符左边的空格LTRIMSELECT RTRIM(周智宇 )返回:周智宇(前面的空格保留)清除字符右边的空格RTRIMSELECT RIGHT(买卖提.吐尔松,3)返回:吐尔松从字符串右边返回指定数目的字符RIGHTSELECT REPLACE(莫乐可切.杨可,可,兰)返回:莫乐兰切.杨兰替换一个字符串中的字符REPLACE第15页,共30页。16日期函数日期函数SELECT DATEPART(day,01/15/2000)返回:15日期中指定日期部分的整数形式DATEPARTSELECT GETDATE
11、()返回:今天的日期取得当前的系统日期GETDATE举例描述函数名SELECT DATEADD(mm,4,01/01/99)返回:以当前的日期格式返回05/01/99将指定的数值添加到指定的日期部分后的日期DATEADDSELECT DATEDIFF(mm,01/01/99,05/01/99)返回:4两个日期之间的指定日期部分的区别DATEDIFFSELECT DATENAME(dw,01/01/2000)返回:Saturday日期中指定日期部分的字符串形式DATENAME第16页,共30页。17数学函数数学函数SELECT SQRT(9)返回:3取浮点表达式的平方根SqrtSELECT AB
12、S(-43)返回:43取数值表达式的绝对值ABS举例举例描述描述函数名函数名SELECT CEILING(43.5)返回:44返回大于或等于所给数字表达式的最小整数CEILINGSELECT POWER(5,2)返回:25取数值表达式的幂值POWERSELECT ROUND(43.543,1)返回:43.5将数值表达式四舍五入为指定精度ROUNDSELECT SIGN(-43)返回:-1对于正数返回+1,对于负数返回-1,对于0 则返回0SignSELECT FLOOR(43.5)返回:43取小于或等于指定表达式的最大整数FLOOR第17页,共30页。18系统函数系统函数SELECT USER
13、_NAME(1)返回:从任意数据库中返回“dbo”从给定的用户I D返回用户名USER_NAMESELECT CONVERT(VARCHAR(5),12345)返回:字符串12345用来转变数据类型CONVERT 举例举例描述描述函数名函数名SELECT CURRENT_USER返回:你登录的用户名返回当前用户的名字CURRENT_USER SELECT DATALENGTH(中国A盟)返回:7返回用于指定表达式的字节数DATALENGTHSELECT HOST_NAME()返回:你所登录的计算机的名字返回当前用户所登录的计算机名字HOST_NAME SELECT SYSTEM_USER返回:
14、你当前所登录的用户名返回当前所登录的用户名称SYSTEM_USER 第18页,共30页。19案例分析案例分析1要求要求v 某公司印了一批充值卡,卡的密码是随机生成的,现在出现这个问题:卡里面的“O和0”(哦和零)“i和1”(哎和一),用户反映说看不清楚,公司决定,把存储在数据库中的密码中所有的“哦”都改成“零”,把所有的“i”都改成“1”;请编写SQL语句实现以上要求;v 数据库表名:Card;密码字段名:PassWord;第19页,共30页。20案例分析案例分析1分析分析v 这是更新语句,需要使用UPDATE语句;v 因为牵涉到字符串的替换,需要使用到SQL Server中的函数Replac
15、e;第20页,共30页。21案例分析案例分析1T-SQL两行SQL语句Update Card Set PassWord=Replace(密码,O,0)Update Card Set PassWord=Replace(密码,i,1)一行SQL语句Update Card Set PassWord=Replace(Replace(密码,O,0),i,1)第21页,共30页。22案例分析案例分析2要求要求v 在数据库表中有以下字符数据,如:13-1、13-2、13-3、13-10、13-100、13-108、13-18、13-11、13-15、14-1、14-2现在希望通过SQL语句进行排序,并且首先
16、要按照前半部分的数字进行排序,然后再按照后半部分的数字进行排需,输出要排成这样:13-1、13-2、13-3、13-10、13-11、13-15、13-18、13-100、13-108、14-1、14-2v 数据库表名:SellRecord;字段名:ListNumber;第22页,共30页。23案例分析案例分析2分析分析v 这是查询语句,需要使用SELECT语句v 需要使用到ORDER BY进行排序,并且在ORDER BY的排序列中,也需要重新计算出排序的数字来v 前半部分的数字,可以从先找到“-”符号的位置,然后,取其左半部分,最后再使用Convert函数将其转换为数字:Convert(in
17、t,Left(ListNumber,CharIndex(-,ListNumber)-1)v 后半部分的数字,可以先找到“-”符号的位置,然后把从第一个位置到该位置的全部字符替换为空格,最后再使用Convert函数将其转换为数字:Convert(int,Stuff(ListNumber,1,Charindex(-,ListNumber),)第23页,共30页。24案例分析案例分析2T-SQL,SELECT ListNumberFROM SellRecordORDER BY Convert(int,Left(ListNumber,CharIndex(-,ListNumber)-1),Convert
18、(int,Stuff(ListNumber,1,Charindex(-,ListNumber),)思考:还有其他的计算出排序列方法吗?思考:还有其他的计算出排序列方法吗?第24页,共30页。25思考思考v 在上一章我们学习了对表的数据的操作,其中学到了Insert语句,但Insert每次只能插入一行记录,如果我们要进行多行数据插入,能行吗?第25页,共30页。26插入多行数据插入多行数据 4-1INSERT INTO TongXunLu(姓名,地址,电子邮件)SELECT SName,SAddress,SEmailFROM StudentsINSERT INTO(新表列名)SELECT FRO
19、M 当新表中的表结构与旧表的表结构相同时,可以用下面语句把旧表中的所需数据一次性插入到新表中第26页,共30页。27插入多行数据插入多行数据 4-2SELECT Students.SName,Students.SAddress,Students.SEmailINTO TongXunLuFROM StudentsSELECT(源表列名)INTO FROM 猜一猜:该语句可以执行两次吗?猜一猜:该语句可以执行两次吗?我要对一张表作测试工作,但又不想破坏表的数据,怎么办?-把这张表复制出来第27页,共30页。28插入多行数据插入多行数据 4-3SELECT Students.SName,Studen
20、ts.SAddress,Students.SEmail,IDENTITY(int,1,1)As StudentIDINTO TongXunLuEXFROM Students紧接上面的问题,当我复制了源表相应的列出来后,发现没有适合做主键的列,怎么?-插入新的标识列SELECT IDENTITY(数据类型,标识种子,标识增长量)AS 列名INTO 新表FROM 原始表第28页,共30页。29插入多行数据插入多行数据 4-4INSERT STUDENTS(SName,SGrade,SSex)SELECT 测试女生1,7,0 UNIONSELECT 测试女生2,7,0 UNIONSELECT 测试男
21、生4,7,1 UNIONSELECT 测试男生5,7,1INSERT INTO(列名)SELECT UNIONSELECT UNION前面讲的多行插入都是有源数据存在,那么我想自己输入新的前面讲的多行插入都是有源数据存在,那么我想自己输入新的数据进去,但只想用一条数据进去,但只想用一条Insert语句,能成功吗?语句,能成功吗?第29页,共30页。30总结总结v 查询将逐行筛选表中的数据,最后符合要求的记录重新组合成“记录集”,记录集的结构类似于表结构v 判断一行中的数据项是否为空,使用IS NULLv 使用ORDER BY进行查询记录集的排序,并且可以按照多个列进行排序v 在查询中,可以使用常量、表达式、运算符v 在查询中使用函数,能够像在程序中那样处理查询得到的数据项第30页,共30页。