创建批处理后的执行（课件）-《MySQL数据库技术与应用》同步教学（清华版）

第7章运算符、表达式和系统函数本章的学习目标：了解Transact-SQL语言的发展过程理解Transact-SQL语言附加的语言元素掌握常量、变量、运算符和表达式掌握流程控制语句掌握常用函数7.13.5创建批处理后的执行创建批处理后，可以使用sqlcmd命令来执行,一般命令格式如下：sqlcmd–Usa–Ppasswd–imysql.sql【例】演示使用sqlcmd运行Transact-SQL脚本文件。步骤如下：1.使用记事本创建一个简单的Transact-SQL脚本文件（1）单击“开始”菜单，选择“所有程序”|“附件”|“记事本”。（2）复制以下Transact-SQL代码并将其粘贴到“记事本”，将文件保存为sqlTest.sql，实现T-SQL脚本文件的创建。USEMASTERGOIFDB_ID('DBase1')--返回数据库标识(ID)号 ISNOTNULL DROPDATABASEDBase1;GOCREATEDATABASEDBase1GOUSEDBase1GOCREATETABLETable1(col1int,col2int)INSERTINTOTable1(col1,col2)VALUES(1,1)INSERTINTOTable1(col1,col2)VALUES(2,2)SELECT*FROMTable1

2.运行脚本文件（1）打开命令提示符窗口。（2）在命令提示符窗口中，键入如下格式的命令：sqlcmd-SmyServer\instanceName-iC:\mysql.sql注意，请将myServer和instanceName替换成自己所用的服务器名和数据库实例名。（3）按Enter键。Table1表的信息便会输出到命令提示符窗口。3.将此输出保存到文本文件中（1）打开命令提示符窗口。（2）在命令提示符窗口中键入如下命令：sqlcmd-SserverName\instanceName-iC:\sqlTest.sql-oC:\sqlTest.txt并按Enter键。需将serverName和instanceName改为所用的服务器名和数据库实例名。命令提示符窗口中不会返回任何输出，而是将输出发送到sqlTest.txt文件。打开sqlTest.txt文件可以查看此输出

另外，也可使用EXEC来执行相应的批处理，语法格式如下：EXEC((字符串变量)|(字面值命令字符串))EXECUTE((字符串变量)|(字面值命令字符串))命令：DECLARE@InVarvarchar(50)--DECLARE@OutVarvarchar(50)----SetupourstringtofeedintotheEXECcommand--SET@InVar='SELECT@OutVar=col1FROMdbo.Table1WHEREcol1=1'--消息137，级别15，状态1，第1行--必须声明标量变量"@OutVar"。SET@InVar='SELECTcol1FROMdbo.Table1WHEREcol1=1'EXEC(@Invar)7.13.6批处理中的错误批处理中的错误分为以下两类：（1）

语法错误（2）

运行时错误如果查询分析器发现一个语法错误，那么批处理的处理过程会立即被取消。因为任何在遇到运行时错误之前执行的语句已经完成了，所以，除非是未提交的事务的一部分，否则这些语句所做的任何事情已经是事实了。7.13.7GO不是T-SQL命令一个常见的错误是认为GO是T-SQL命令。其实GO是一个只能被编辑工具(ManagementStudio、sqlcmd)识别的命令。如果使用了第三方工具，那么它可能支持也可能不支持GO命令，但是大多数支持SQLServer的工具都支持GO命令。7.14

SQLServer2016函数简介7.14.1字符串函数字符串函数功能ASCII(字符表达式)ASCII函数，返回字符串表达式中最左端字符的ASCII码值CHAR(整型表达式)将ASCII码值转换为相对应的字符，参数为介于0~255之间的整数。没有输入0~255之间的ASCII码值则CHAR()返回NULL。LEN(字符表达式)字符串长度函数，返回字符串表达式中字符的个数LOWER(字符表达式)小写字母函数，将大写字母转换为小写字母，返回表达式的小写字母表示UPPER(字符表达式)大写字母函数，返回指定表达式的大写字母表示REPLACE(字符表达式1，字符表达式2，字符表达式3)字符替换函数，用第三个表达式替换第一个字符串表达式中包含的所有第二个字符表达式，并返回替换后的表达式LEFT(字符表达式，n)左子串函数，返回从字符串左边开始的n个字符RIGHT(字符表达式，n)右子串函数，返回从字符串右边开始的n个字符LTRIM(字符表达式)删除头部的空格函数，返回删除了前导空格的字符表达式RTRIM(字符表达式)删除尾随空格函数，返回删除所有尾随空格的字符表达式STR(数值表达式[，长度n[，小数位]])数字向字符转换函数，返回由数字转换过来的字符串，长度用于指定总长度，包括小数点，小数位指小数点右边的位数LEN用于返回指定字符串的字符（而不是字节）个数。

【例】selectLEN('loving')selectlen('2016.10.01')selectLEN('李华生日快乐！')【例】使用常用字符串函数计算A的ASCII码，‘hello’的前3个字符，‘数据库原理’字符串的长度，将‘hello转换为大写字母，将‘英语四级’改为‘英语六级’。SELECTN'A的ASCII'=ASCII('A'), N'hello的前3个字符'=LEFT('hello',3),

N'数据库原理的长度'=LEN(N'数据库原理'), N'将hello转换为大写字母'=UPPER('hello'), N'将英语四级改为英语六级'=REPLACE(N'英语四级',N'四',N'六')【例】将"北京,欢迎您!"变成"欢迎您!北京"declare@svarchar(50)set@s='北京,欢迎您!'selectright(@s,4)+left(@s,2)【例】declare@svarchar(50)set@s='2001年07月欢庆北京申奥成功' --变成北京2001年07月申奥成功selectsubstring(@s,11,2)+LEFT(@s,8)+RIGHT(@s,4)SUBSTRING(<字符表达式>，<开始位置>，长度)

返回从“字符表达式”左边第“开始位置”个字符起“长度”个字符的部分。selectReplace('SQLSERVER','ER','xx')Replace函数将字符串中的子字符串替换为指定字符串selectASCII('hello')ASCII函数返回最左端字符的ASCII代码值。纯数字的字符串可以不用单引号，而其他字符表达式必需用,否则出错。STR函数：将数值表达式的值转换为字符串，转换时自动四舍五入。默认长度为10，若不指定小数位则保留到整数位，如果长度小于数值表达式值的整数位数，则返回一串*号。理想长度=整数+小数点+小数要求长度>理想长度：加前导符整数长度<要求长度<理想长度：保留整数部分，调整小数部分。要求长度<整数长度：***【例】将数值型数据123.456转换为字符型数据，并显示其结果。

解：SELECTSTR(123.456)

主屏幕显示结果为：凵凵凵凵凵凵凵123SELECTSTR(123.456,7,2),STR(123.456,5,2),STR(123.456,2)

主屏幕显示结果分别为：凵123.46123.5**7.14.2数学函数数学函数功能ABS(数值表达式)绝对值函数，返回数值表达式的绝对值ACOS(实型表达式)反余弦函数，返回一个余弦值对应的角度。角度以弧度表示，实型表达式取值范围从-1~1，若参数超过此范围，函数返回NULL并报告错误SIN(数值表达式)正弦函数，返回表达式中以弧度表示的指定角的正弦值COS(数值表达式)余弦函数，返回表达式中以弧度表示的指定角的余弦值TAN(数值表达式)正切函数，返回指定表达式中以弧度表示的指定角的正切值EXP(数值表达式)指数函数，返回表达式的指数值LOG(数值表达式)自然对数函数，返回表达式的自然对数值PI()圆周率函数，返回14位小数的圆周率常量值RAND()随机函数，随机返回0~1之间的float数值SIGN(数值表达式)符号函数，返回表达式的正号、零或负号SQRT(数值表达式)平方根函数，返回表达式的平方根【例】数学函数运算示例SELECTN'绝对值函数'=ABS(-5.6), N'圆周率函数'=PI(), N'平方根函数'=SQRT(64), N'随机函数'=RAND(), N'对数函数LOG10(100)'=LOG10(100), N'自然对数函数LOG(2.71828)'=LOG(2.71828), N'指数函数'=LOG(EXP(2.71828))7.14.3数据类型转换函数SQLServer会自动完成数据类型的转换，这种转换称隐式转换。但有些类型就不能自动转换，如int整型与char型，这时就要显式转换函cast、convertCAST(表达式AS数据类型[(长度)])CONVERT(数据类型[(长度)]，表达式[，样式])长度：如果数据类型允许设置长度，可以设置长度，例如varchar(10);样式：用于将日期类型数据转换为字符数据类型的日期格式的样式。【例】SELECTCAST(1234.567ASINT),CONVERT(INT,1234.567)7.14.4日期和时间函数日期和时间函数描述GETDATE(日期时间型数据)以标准格式返回本地服务器的DATETIME值DAY(日期时间型数据)返回指定日期的日期值MONTH(日期时间型数据)返回指定日期的月份值YEAR(日期时间型数据)返回指定日期的年份值DATEADD(时间间隔，数值表达式，日期)为DATETIME值添加间隔。时间间隔项决定时间间隔的单位，可取Year、Dayofyear(一年的日数)、Quarter、Month、Day、Week、Weekday(一周的日数)、Hour、Minute、Second、Millisecond。数值表达式为加上或者减去的时间间隔DATEDIFF(时间间隔，日期1，日期2)计算两个指定日期之间指定时间间隔的差，返回数值型数据。时间间隔项决定时间间隔的单位，取值与DATEADD中的时间间隔项相同【例】使用日期和时间函数显示当前日期，在当前日期后10天的日期，当前日期与2016年10月01日相隔的天数。SELECT N'显示当前系统日期'=GETDATE(), N'在当前日期后10天的日期'=DATEADD(day,10,GETDATE()), N'当前日期与2011年1月1日相隔的天数'=DATEDIFF(DAY,GETDATE(),'2016-10-01')7.14.5系统函数用户可以在需要时通过系统函数获取当前主机名称、用户名称、数据库名称及系统错误信息。(1).函数HOST_NAME()返回服务器端计算机的名称。(2).函数OBJECT_NAME()返回数据库对象的名称。(3).函数USER_NAME()返回数据库用户名。(4)函数SUSER_NAME()返回用户登录名(5)函数DB_NAME()返回数据库名【例】系统函数示例SELECTHOST_NAME(),SUSER_NAME(),DB_NAME(),USER_NAME()7.15为学生选课表增加10行测试数据1.创建数据库StuClass--先手工创建目录F:\USEmasterGOIFEXISTS(SELECTnameFROMsys.databasesWHEREname=N'StuClass')DROPDATABASEStuClassGO--创建数据库CREATEDATABASEStuClassONPRIMARY(NAME=N'StuClass',FILENAME=N'F:\StuIClass.mdf',SIZE=6848KB,MAXSIZE=UNLIMITED,FILEGROWTH=10%)LOGON(NAME=N'StuClass_log',FILENAME=N'F:\StuClass_log.ldf',SIZE=1024KB,MAXSIZE=102400KB,FILEGROWTH=10%)GO

2.创建表

USEstuClassGOIFEXISTS(SELECT*FROMsys.objectsWHEREobject_id=OBJECT_ID(N'selectedCourses')ANDtypein(N'U'))DROPTABLEselectedCoursesGOCREATETABLEselectedCourses( stuNointprimarykey,--编号 stuNamevarchar(10)NOTNULL,--姓名 stuSexbit,--性别 courseNovarchar(10)NOTNULL,--课程号 gradeintNOTNULL, recordedTimedatetime)GO3.使用WHILE循环插入10万条记录

SETnocountonDECLARE@startTimedatetimeSELECT@startTime=getdate()--SELECT'执行时间(毫秒)：'--SELECTdatediff(MILLISECOND,@startTime,getdate())DECLARE@iint,@cntint,@ddatetimeSELECT@d=getdate(),@i=1,@cnt=100000WHILE(@i<=@cnt)BEGIN INSERTINTOselectedCourses VALUES ( @i, 'Name'+convert(varchar(6),@i), @i%2, left(convert(varchar(40),newid()),10), ROUND(@i%100,0), @d-@i%1000 ); SET@i=@i+1ENDSELECT'执行时间(毫秒)：'SELECTdatediff(MS,@startTime,getdate())--19363ms.GO4.使用随机函数插入数据在插入数据时,使用随机函数产生随机数据，修改Insert语句如下:--...INSERTINTOselectedCoursesVALUES( @i, left(convert(varchar(40),newid()),10), @i%2, left(convert(