tp4-数据查询(一).doc

第四章 数据查询（一）一、T-SQL查询基础1、查询和记录集：a、查询：是针对表中已经存在的数据行而言的，可以理解为“筛选”。b、记录集：数据表在接受查询的时候，会把符合条件的查询结果逐行筛选并组织到一起，形成一个类似表的结构，也可以称为虚拟表。c、在查询一个表得到的虚拟表中依然可以进行再次筛选： 语法：select ，from(select * from Table) as newTabwhere 注意：*虚拟表的查询语句必须用小括号括起来。*必须给虚拟表取名，关键字“as”可以省略。 *其中“列1”和“列2”必须是虚拟表“newTab”中的列。d、在多表连接查询得到的虚拟表中进行再次筛选： 语法：select ，from(select a.列 as 列1，b.列 as 列2，b.列 as 列3from A表 as a，B表 as bwhere a . A表外键 = b . B表主键列) as newTabwhere 注意：*这是对两表连接查询得到的虚拟表进行的再次查询。*其中虚拟表的查询必须用小括号括起来。*必须给虚拟表取名，关键字“as”可以省略。 *其中“列1”和“列2”必须是虚拟表“newTab”中的列。2使用“select - from - where - order by”语句进行基本的查询： 语法：select ，from where order by asc 或 desc注意：*如果要显示表中所有数据，则把“select”后面的列名换成“*”。*where查询条件是可选的，如果省略，则查询表中目标列的所有数据。*一个特殊的查询条件“1=1”：表示永久成立，如果在where后面的条件表达式后面加上“or 1=1”，则查询效果和没有where查询条件的效果是一样的。*order by是用来排序的，排序的列可以使：数字、字母、日期，必须写在where条件后面。*其中“asc”：升序排列，“desc”：降序排列（如果不写，则默认为升序）3、更改列名的方法：（实际上是更改虚拟表中的列名，原表中的列名没有更改）方法更改表达式说明关键字：“as”原表列名 as 新列名as可以省略，新列名可以是中文，而且不需要用引号，注意顺序。运算符号：“=”新列名 = 原表列名解释：其中，原表列名可以是一个也可以是多个列名组合在一起，如果是多个列名需要用连接运算符：“+”。语法：(列1+_+列2) as 新列名 | | 新列名 = (列1+_+列2)（其中，“as”和小括号都可以省略，连接的列必须都是字符或字符串类型，如果有其他类型，可以转换成字符串类型。）将其他类型转换为字符串类型：语法：convert（转换后的类型，要转换的数据） 例如：convert（varchar(10)，123） 结果：“123”语法：cast（要转换的数据 as 转换后的类型） *（日期和字符串虽然都必须用单引号，但是两种类型互不兼容，也需要转换。） 例如：cast（2011-09-26as varchar(10)） 结果：“2011-09-26”4、在查询中使用常量列（即在虚拟表中插入默认值并赋给列名）： 语法：select as ，from 或select = ，from 注意：*默认值如果是字符串类型，要用单引号。*插入的这一列的所有值都是默认值。5、查询空行(不存在数据的行)的条件： 语法：select * from where is null or =6、查询返回限制的行数： 语法：返回前n条记录：select Top 返回记录行数 ，from 返回前n%条记录：select Top 返回记录的百分数 percent ，from 注意：*如果返回的记录总数是奇数n，则返回50%的行数就是(n/2+1)。例如：返回距离今天最近的日期的前n条记录：先按照日期降序排列，然后查询返回前n条就是了。二、查询排序：升序（ASC）、降序（DESC） 语法：select from where order by ASC或， ASC或注意：*“order by”必须写在“where”查询条件的后面*排序的列的类型有：字母(varchar)、数字(int)、日期(date)。*如果是升序可以不写，即省略“ASC”。*如果是降序“DESC”必须写。*上面语法，如果列2中的某几行数据对应列1中的数据是相等的，则可以先按照列1排序，再按照列2排序。例如：按照总分排名次学生编号总分101460102550103460排序方法：先按照总分降序排列，其中“101”和“103”的总分相等，则可以按照学生编号升序或降序排列。结果：第一名是“102”、第二名时“101”、第三名“103”。三、常用函数*字符串函数和常用的系统：1、“CharIndex”：返回指定字符在字符串中的起始位置 语法：CharIndex（要查询的字符或字符串，字符串，查询的起始位置）注意：*字符在字符串中的位置，从“1”开始。可以写0。*如果查询的起始位置不写，则默认从“1”开始查询。例如：select CharIndex（a，cabcad，1） 结果：2select CharIndex（a，cabcad，3） 结果：5select CharIndex（ab，cabcad，1） 结果：22、“Len”：返回字符串中字符的个数 语法：Len（字符串）注意：*字符串中可以包含空格和汉字，它们都算一个。*如果没有字符则返回0例如：select Len（ma 光） 结果：43、“DataLength”：返回字符串中字符的字节数 语法：DataLength（字符串）注意：*字符串中可以包含空格和汉字，汉字占两个字节。*如果没有字符则返回0例如：select DataLength（ma 光） 结果：54、“Upper”：小写字母转换为大写 语法：Upper（字符串）注意：*字符串中可以包含汉字例如：select Upper（ma 光） 结果：MA 光5、“Lower”：大写字母转换为小写字母 语法：Lower（字符串）注意：*字符串中可以包含汉字例如：select Lower（MA 光） 结果：ma 光6、“Ltrim”：清楚字符串左边的空格 语法：Ltrim（字符串）注意：*字符串中间的空格不能清楚例如：select Ltrim（ ma 光 ） 结果：ma 光 7、“Rtrim”：清楚字符串右边的空格 语法：Rtrim（字符串）注意：*字符串中间的空格不能清楚例如：select Rtrim（ ma 光 ） 结果： ma 光8、“Left”：返回字符串左边指定数量的字符 语法：Left（字符串，返回数量）例如：select Left（ma光guang，3） 结果：ma光9、“Right”：返回字符串右边指定数量的字符 语法：Right（字符串，返回数量）例如：select Right（ma光guang，5） 结果：guang10、“Replace”：将字符串中的某个字符或字符串替换为指定字符或字符串 语法：Replace（字符串，要替换的字符，替换为指定字符）注意：*如果要替换的字符在字符串中有很多个，则都会被替换掉。*可以将字母替换成汉字或其他字符*可以将字符串中的某一段连续字符替换为单个字符或汉字例如：select Replace（ma马，马，ma） 结果：mamaselect Replace（ma guang，a，A） 结果：mA guAngselect Replace（ma guang，guang，光） 结果：ma 光11、“Stuff”： 删除字符串从指定位置开始以后的指定数量的字符，并在该位置插入新的字符或字符串。 语法：Stuff（字符串，起始位置，删除字符个数，插入新的字符）注意：*字符在字符串中的位置，从“1”开始。可以写0。*如果删除字符数量为0，则会在起始位置直接插入新的字符。例如：Stuff（MA马光，3，2，guang） 结果：MAguangStuff（MA马光，3，0，guang） 结果：MAguang马光函数“Replace”和“Stuff”的区别ReplaceStuff相同点都可以起到替换字符的作用不同点必须找到字符串中指定的字符或字符串（哪怕是空格），才能替换新的字符串。它是先删除从起始位置以后指定数量的字符个数，如果删除的字符个数为0，也就是没删除，结果就会在起始位置直接插入新的字符或字符串。12、“Rand”：产生随机数 语法：Rand（）注意：*产生的随机数的范围是：0 rand() 1想得到随机数的范围：（1 = rand() = 9）表达式：select subString(convert(varchar(20),(RAND()*10),1,1)select LEFT(RAND ()*10),1)13、“Convert”：用来转换数据类型 语法：convert（转换后的类型，要转换的数据） 例如：convert（varchar(10)，123） 结果：“123”14、“Cast”：用来转换数据类型 语法：cast（要转换的数据 as 转换后的类型） 例如：select CAST (2011-09-26 as varchar(10)结果：2011-09-26select CAST (0342 as int) 结果：342select CAST (2010 as money) 结果：2010.00 *（日期和字符串虽然都必须用单引号，但是两种类型互不兼容，也需要转换。）15、“subString”：截取字符串 语法：subString（字符串，起始位置，截取字符个数）注意：*字符在字符串中的位置，从“1”开始。可以写0。例如：select SUBSTRING (maguang,3,5) 结果：guang*日期函数日期时间名称一览表年year、y、yy、yyyy （int类型）月month、m、mm （int类型）日day、d、dd （int类型）时hour、hh （int类型）分minute、mi、n （int类型）秒second、s、ss （int类型）毫秒millisecond、ms （int类型）一刻钟quarter、q、qq （int类型）周（星期名称）weekday、dw、w （字符串类型）周（第几周）week、ww、wk （int类型）1、“GetDate”：获取当前日期，精确到毫秒。 语法：GetDate（）例如：select GETDATE () 结果：2011-08-26 16:59:05.2172、“DateAdd”：将指定数值加到指定的日期部分后的日期上 语法：DateAdd（日期名称，加数，日期）例如：select DateAdd(dd,2,2011-09-26) 结果：2011-09-283、“DateDiff”：将两个日期的指定部分相见 语法：DateDiff（日期名称，被减日期，日期）注意：*被减日期在前面，注意顺序。例如：select DateDiff(MM ,2011-03-26,2011-09-26) 结果：64、“DateName”：返回日期中指定日期部分的字符串形式 语法：DateName（日期名称，日期）例如：select DATENAME (DW,2011-08-26) 结果：星期五 （周五）select DATENAME (WW,2011-08-26) 结果：35 （第35周）select DATENAME (dd ,2011-08-26) 结果：265、“DatePart”：返回日期中指定日期部分的证书形式 语法：DatePart（日期名称，日期）例如：select datepart (DW,2011-08-26) 结果：6解释：星期天是一周中的第一天，所以是周一，返回1。星期六返回7。解释：星期名称：weekday、dw、w （字符串类型）外国的weekday星期名称星期天星期一星期二星期三星期四星期五星期六一周中的第几天1234567*区别日期函数（返回指定日期是星期几）语法：DateName（日期名称，日期）DatePart（日期名称，日期）举例select DATENAME (DW,2011-08-27)select datepart (DW,2011-08-27)结果：星期六7返回指定日期结果：具体星期几具体周几（星期天是一周的第一天，所以是周一，返回1。而星期六是一周的最后一天，所以是周末，返回7）*数学函数函数名描述举例返回值Abs取数据表达式的绝对值Abs（-2）2Ceiling取大于或等于数值的最小整数，不会四舍五入。Ceiling（23.5）24Floor取小于或等于数值的最大整数，不会四舍五入。Floor（23.5）23Power去表达式的次方值Power（2,3）23=8Round将小数四舍五入为指定精度Round（3.58，1）3.6Sign是整数返回“+1”；是负数返回“-1”；是0，返回0。Sign（-23）-1Sqrt取浮点表达式的平方根Sqrt（9）3*系统函数函数名描述举例返回值Convert用来转换数据类型convert(varchar(5)，123）字符串123Current_User返回当前用户的名字select current_User你登陆的用户名DataLength返回字符串的字节数dataLength(中国abc)7Host_Name返回登陆计算机的名字select Host_Name登陆计算机的名字System_User返回登陆的用户名select System_User返回当前登陆的用户User_Name从给定的用户ID返回用户名select User_Name(0)从任意数据库中返回“dbo”四、数据查询的特殊案例1、替换字符：数据库表“Card”中的密码列“Password”中的字母“O”和数字“0”、字母“I”和数字“1”看不清，要求把字母“O”和字母“I”改成数字“0”和“1”。方法一：同时运行下面两条sql语句改字母“O”：update card set password=Replace(password,O，0)改字母“I”：update card set password=Replace(password,I，1)方法二：嵌套使用R