SQLServer数据查询select语言及函数

第四章数据查询(1)2回顾SQL分为数据操作语言、数据控制语言和数据定义语言；T-SQL是SQL的扩展通配符经常与LIKE一起使用来进行非精确的限制；一次插入多行数据，可以使用INSERT…SELECT…、SELECT…INTO…或者UNION关键字来实现标识列不能插入指定的数据值使用UPDATE更新数据，一般都有限制条件使用DELETE删除数据时，不能删除被外键值所引用的数据行使用TRUNCATETABLE来删除所有数据时，效率要比DELETE语句高上面的数据操作语句在程序中会经常使用到目标理解查询的机制使用SELECT语句进行条件查询在查询中返回限制行、进行查询排序在查询中使用表达式、运算符和函数什么是查询？ApplicationLogic客户程序查询请求查询结果集SQLSERVERABCDEFGSELECT*FROMSALES查询产生一个虚拟表，看到的是表形式显示的结果，但结果并不真正存储，每次执行查询只是现从数据表中提取数据，并按照表的形式显示出来怎么查的？学员编号学员姓名地址所在班级…….001张明全湖南长沙S201002李菲湖北宜昌S202003于寄谦甘肃天水S204004刘国正山东荷泽S201005周接轮台湾新竹S204006巩小妹香港龙湾S203007巩大妹香港龙湾S203008张明敏北京顺义S202009矛十八四川棉阳S204010罗林光陕西临潼S202011司马坡新疆喀什S201S202陕西临潼罗林光010…S202湖北宜昌李菲002S202北京顺义张明敏008SELECT*FROMStudentsHWHERE所在班级=‘S202’思考既然查询得到的“结果集”的结构类似于一张表，那么可以在“结果集”上继续进行查询吗？查询SELECT列名FROM表名[WHERE查询条件表达式][ORDERBY排序的列名[ASC或DESC]]列名称SELECTSCode,SName,SAddress表名FROMStudents过滤条件WHERESSEX=0排序条件ORDERBYSCodeSELECTSCode,SName,SAddressFROMStudentsWHERESSEX=0ORDERBYSCode数据查询-基础查询全部的行和列SELECT*FROMStudents查询部分行SELECTSCode,SName,SAddressFROMStudentsWHERESAddress='河南新乡'SELECTSCode,SName,SAddressFROMStudentsWHERESAddress'河南新乡'数据查询-列名使用AS来命名列SELECTSCodeAS学员编号,SNameAS学员姓名,SAddressAS学员地址FROMStudentsWHERESAddress'河南新乡'SELECTFirstName+'.'+LastNameAS'姓名'FROMEmployees使用=来命名列SELECT'姓名'=FirstName+'.'+LastNameFROMEmployees数据查询-空行常量列查询空行SELECTSNameFROMStudentsWHERESEmailISNULL使用常量列SELECT姓名=SName,地址=SAddress,'河北新龙'AS学校名称FROMStudents思考：非空行怎么查？数据查询-限制行数限制固定行数SELECTTOP5SName,SAddressFROMStudentsWHERESSex=0返回百分之多少行SELECTTOP20PERCENTSName,SAddressFROMStudentsWHERESSex=0数据查询-排序2-1升序排列SELECTStudentIDAs学员编号,(Score*0.9+5)As综合成绩FROMScoreWHERE(Score*0.9+5)60ORDERBYScore降序排列SELECTAu_Lname+'.'+Au_fNameASEMPFromAuthorsUnionSELECTfName+'.'+LNameASEMPFromEmployeeORDERBYEMPDESC数据查询-排序2-2按多列排序SELECTStudentIDAs学员编号,ScoreAs成绩FROMScoreWHEREScore60ORDERBYScore,CourseID思考：排序中的字段，可以使用表达式吗？——如果不可以，请说明原因；——如果可以，请举例说明；SQLServer中的函数相当于excel中的内部函数字符串函数日期函数数学函数系统函数字符串函数SELECTSTUFF('ABCDEFG',2,3,'我的音乐我的世界')返回：A我的音乐我的世界EFG在一个字符串中，删除指定长度的字符，并在该位置插入一个新的字符串STUFFSELECTCHARINDEX(‘test','MytestCourse',1)返回：4用来寻找一个指定的字符串在另一个字符串中的起始位置CHARINDEX举例描述函数名SELECTLEN('SQLServer课程')返回：12返回传递给它的字符串长度LENSELECTLOWER('SQLServer课程')返回：sqlserver课程把传递给它的字符串转换为小写LOWERSELECTUPPER('sqlserver课程')返回：SQLSERVER课程把传递给它的字符串转换为大写UPPERSELECTLTRIM('周智宇')返回：周智宇（后面的空格保留）清除字符左边的空格LTRIMSELECTRTRIM('周智宇')返回：周智宇（前面的空格保留）清除字符右边的空格RTRIMSELECTRIGHT('买卖提.吐尔松',3)返回：吐尔松从字符串右边返回指定数目的字符RIGHTSELECTREPLACE('莫乐可切.杨可','可','兰')返回：莫乐兰切.杨兰替换一个字符串中的字符REPLACE日期函数SELECTDATEPART(day,’01/15/2000’)返回：15日期中指定日期部分的整数形式DATEPARTSELECTGETDATE()返回：今天的日期取得当前的系统日期GETDATE举例描述函数名SELECTDATEADD(mm,4,’01/01/99’)返回：以当前的日期格式返回05/01/99将指定的数值添加到指定的日期部分后的日期DATEADDSELECTDATEDIFF(mm,’01/01/99’,’05/01/99’)返回：4两个日期之间的指定日期部分的区别DATEDIFFSELECTDATENAME(dw,’01/01/2000’)返回：Saturday日期中指定日期部分的字符串形式DATENAME数学函数SELECTSQRT(9)返回：3取浮点表达式的平方根SqrtSELECTABS(-43)返回：43取数值表达式的绝对值ABS举例描述函数名SELECTCEILING(43.5)返回：44返回大于或等于所给数字表达式的最小整数CEILINGSELECTPOWER(5,2)返回：25取数值表达式的幂值POWERSELECTROUND(43.543,1)返回：43.5将数值表达式四舍五入为指定精度ROUNDSELECTSIGN(-43)返回：-1对于正数返回+1，对于负数返回-1，对于0则返回0SignSELECTFLOOR(43.5)返回：43取小于或等于指定表达式的最大整数FLOOR系统函数SELECTUSER_NAME(1)返回：从任意数据库中返回“dbo”从给定的用户ID返回用户名USER_NAMESELECTCONVERT(VARCHAR(5)，12345)返回：字符串12345用来转变数据类型CONVERT举例描述函数名SELECTCURRENT_USER返回：你登录的用户名返回当前用户的名字CURRENT_USERSELECTDATALENGTH('中国A盟')返回：7返回用于指定表达式的字节数DATALENGTHSELECTHOST_NAME()返回：你所登录的计算机的名字返回当前用户所登录的计算机名字HOST_NAMESELECTSYSTEM_USER返回：你当前所登录的用户名返回当前所登录的用户名称SYSTEM_USER案例分析1—要求某公司印了一批充值卡，卡的密码是随机生成的，现在出现这个问题：卡里面的“O和0”（哦和零）“i和1”（哎和一），用户反映说看不清楚，公司决定，把存储在数据库中的密码中所有的“哦”都改成“零”，把所有的“i”都改成“1”；请编写SQL语句实现以上要求；数据库表名：Card；密码字段名：PassWord；案例分析1—分析这是更新语句，需要使用UPDATE语句；因为牵涉到字符串的替换，需要使用到SQLServer中的函数Replace；案例分析1—T-SQL两行SQL语句UpdateCardSetPassWord=Replace(密码,'O','0')UpdateCardSetPassWord=Replace(密码,'i','1')一行SQL语句UpdateCardSetPassWord=Replace(Replace(密码,'O','0'),'i','1')案例分析2—要求在数据库表中有以下字符数据，如：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数据库表名：SellRecord；字段名：ListNumber；案例分析2—分析这是查询语句，需要使用SELECT语句需要使用到ORDERBY进行排序，并且在ORDERBY的排序列中，也需要重新计算出排序的数字来前半部分的数字，可以从先找到“-”符号的位置，然后，取其左半部分，最后再使用Convert函数将其转换为数字：Convert(int,Left(ListNumber,CharIndex('-',ListNumber)-1))后半部分的数字，可以先找到“-”符号的位置，然后把从第一个位置到该位置的全部字符替换为空格，最后再使用Convert函数将其转换为数字：Convert(int,Stuff(ListNumber,1,Charindex('-',ListNumber),''))案例分析2—T-SQL,,,,,,,,,,,,,,SELECTListNumberFROMSellRecordORDERBYConvert(int,Left(ListNumber,CharIndex('-',ListNumber)-1)),Convert(int,Stuff(ListNumber,1,Charindex('-',ListNumber),''))思考：还有其他的计算出排序列方法吗？总结查询将逐行筛选表中的数据，最后符合要求的记录重新组合成“记录集”，记录集的结构类似于表结构判断一行中的数据项是否为空，使用ISNULL使用ORDERBY进行查询记录集的排序，并且可以按照多个列进行排序在查询中，可以使用常量、表达式、运算符在查询中使用函数，能够像在程序中那样处理查询得到的数据项