《TSQL語(yǔ)言基礎(chǔ)》PPT課件.ppt

第6章 T-SQL語(yǔ)言概述 主講教師：衛(wèi)琳,6.1 T-SQL語(yǔ)言概述,6.1.1 T-SQL語(yǔ)言的發(fā)展過(guò)程和特點(diǎn) 6.1.2 T-SQL語(yǔ)言的分類 數(shù)據(jù)定義語(yǔ)言(DDL) 數(shù)據(jù)操縱語(yǔ)言(DML) 數(shù)據(jù)控制語(yǔ)言(DCL),DDL,DML,DCL,6.1.3 T-SQL法約定,1.注釋 “”：?jiǎn)涡凶⑨專◤碾p連字符到行尾的內(nèi)容） /*/：多行注釋（對(duì)/*/ 之間所有內(nèi)容） 2.系統(tǒng)保留字 不要使用保留關(guān)鍵字作為對(duì)象名稱或標(biāo)識(shí)符,/*創(chuàng)建名為example的數(shù)據(jù)庫(kù)，并存放在“e:sql”目錄中*/ CREATE DATABASE example ON (name=example,-設(shè)置數(shù)據(jù)庫(kù)文件名稱 filename=e:sqlexample.mdf, -設(shè)置文件存放位置 size=10,-指定文件大小 maxsize=50)-指定文件的最大容量 LOG ON-指定日志文件 (name=examplog,-指定日志文件名稱 filename=e:sqlexample.ldf, -指定日志文件存放位置 size=5mb, maxsize=25mb, filegrowth=5mb) go,6.2 附加的語(yǔ)言元素,6.1.2 標(biāo)識(shí)符 1. 標(biāo)識(shí)符格式 （1）標(biāo)識(shí)符的首字符必須是下列字符之一 。 統(tǒng)一碼（Unicode）2.0標(biāo)準(zhǔn)中所定義的字母，包括拉丁字母a-z和A-Z，以及來(lái)自其他語(yǔ)言的字符。 下劃線“_”、符號(hào)“”或者數(shù)字符號(hào)“#”。 （2）標(biāo)識(shí)符的后續(xù)字符可以是以下3種。 統(tǒng)一碼（Unicode）2.0標(biāo)準(zhǔn)中所定義的字母。 來(lái)自拉丁字母或其他國(guó)家/地區(qū)腳本的十進(jìn)制數(shù)字。 “”符號(hào)、美元符號(hào)“$”、數(shù)字符號(hào)“#”或下劃線“_”。 （3）標(biāo)識(shí)符不允許是Transact-SQL的保留字。 （4）不允許嵌入空格或其他特殊字符。,6.1.2 標(biāo)識(shí)符 2標(biāo)識(shí)符分類 SQL Server將標(biāo)識(shí)符分為以下兩種類型： 常規(guī)標(biāo)識(shí)符：符合標(biāo)識(shí)符的格式規(guī)則。 分隔標(biāo)識(shí)符：包含在雙引號(hào)(“”)或者方括號(hào)( )內(nèi)的標(biāo)識(shí)符。 該標(biāo)識(shí)符可以不符合標(biāo)識(shí)符的格式規(guī)則，如MR GZGLXT、MR和GZGLXT之間含有空格，但因?yàn)槭褂昧朔嚼ㄌ?hào)，所以視為分隔標(biāo)識(shí)符。 注意：常規(guī)標(biāo)識(shí)符和分隔標(biāo)識(shí)符包含的字符數(shù)必須在1128之間，對(duì)于本地臨時(shí)表，標(biāo)識(shí)符最多可以有116個(gè)字符。,6.2.2 常量 (1)字符型常量 ASCII字符串常量：用單引號(hào)括起來(lái)，由ASCII字符組成。 如果在字符常量中已經(jīng)包含了一個(gè)單引號(hào)，那么可以使用兩個(gè)單引號(hào)表示這個(gè)帶單引號(hào)的字符。 Unicode字符串常量的格式與ASCII字符串常量相似，但它前面有一個(gè)前綴N，而且N前綴必須是大寫的。 如：NSQL Server、N張三、N計(jì)算機(jī)科學(xué)與技術(shù)。,6.2.2 常量 (2) 數(shù)值型常量 數(shù)值型常量包含整型常量和實(shí)數(shù)型常量。 整型常量(Integer)用來(lái)表示整數(shù)?？杉?xì)分為二進(jìn)制整型常量、十六進(jìn)制整型常量和十進(jìn)制整型常量。二進(jìn)制整型常量以數(shù)字0或1表示；十六進(jìn)制整型常量由前綴0 x后跟十六進(jìn)制數(shù)組成；十進(jìn)制整型常量即不帶小數(shù)點(diǎn)的十進(jìn)制數(shù)； 實(shí)數(shù)型常量用來(lái)表示帶小數(shù)部分的數(shù)，有定點(diǎn)數(shù)和浮點(diǎn)數(shù)兩種表示方式，其中浮點(diǎn)數(shù)使用科學(xué)記數(shù)法來(lái)表示。如：0.3E-5。,(3) 日期時(shí)間型常量(datetime) 日期時(shí)間型常量使用特定格式的字符日期值來(lái)表示，并且用單引號(hào)括起來(lái)。如2009年4月1日可以用以下方式表示：April 1，2009、04/01/2009或20090401。 (4) 貨幣型常量(money) 貨幣型常量以前綴“$”作為標(biāo)識(shí)。如$123.45。,6.2.2 變量,（1）變量名稱 在SQL Server 2008系統(tǒng)中，變量的命名規(guī)則如下： 第一個(gè)字符必須是字母、數(shù)字、下畫線或符號(hào)。需要注意的是，符號(hào)“”開頭的變量表示局部變量、符號(hào)“”開頭的變量表示全局變量。 變量名不能是T-SQL語(yǔ)言的系統(tǒng)保留字(如IF、ELSE、CONTINUE等)，包括大寫和小寫形式。 變量名中不允許出現(xiàn)空格或其他特殊字符。 根據(jù)以上規(guī)則，下列變量名都是合法的：a2、abc、student_3和average。 變量在使用中需要先聲明再使用，聲明變量用DECLARE語(yǔ)句，其語(yǔ)法格式如下： DECLARE 變量名稱 變量的數(shù)據(jù)類型 ，n 說(shuō)明： 為表示局部變量，變量名稱的第一個(gè)字符必須是 所有變量在聲明后均設(shè)置初值為NULL,6.2.2 變量,（2）變量賦值 有兩種為變量賦值的方式：使用SET語(yǔ)句直接為變量賦值和使用SELECT語(yǔ)句選擇表中的值來(lái)為變量賦值。 語(yǔ)法格式如下： 格式1：使用SET語(yǔ)句賦值 SET 變量名稱=表達(dá)式 格式2：使用SELECT語(yǔ)句賦值 SELECT 變量名稱=表達(dá)式 ，n 說(shuō)明： 表達(dá)式可以是任何有效的SQL表達(dá)式； 一個(gè)SELECT語(yǔ)句可以給多個(gè)變量賦值，而一個(gè)SET語(yǔ)句一次只能給一個(gè)變量賦值。,例：用賦值語(yǔ)句分別定義兩個(gè)整型變量x和y。使x的值為20，y的值為5，計(jì)算并顯示x，y，3x +4y，xy，和x /y的值。 declare x int,y int set x=20 set y=5 select x,y,3*x+4*y,x*y,x/y 例：創(chuàng)建兩個(gè)局部變量，并賦值，然后輸出變量的值。 declare var1 char(4),var2 char(20) set var1=中國(guó) set var2=var1+是一個(gè)偉大的國(guó)家 select var1,var2 go,示例：創(chuàng)建一個(gè)名為sex的局部變量，并在select語(yǔ)句中使用該局部變量查找表student中所有女同學(xué)的信息。 use xscj2005 declare xb char(2) set xb=女 select * from student where ssex=xb,例：使用查詢給變量賦值。 use XSCJ2005 go declare xm varchar(8) set xm =(select sname from student where sno=2008056103) select xm Go use XSCJ2005 go declare xm varchar(8) select xm =sname from student select xm Go 說(shuō)明：如果返回多個(gè)值，將返回的最后一個(gè)值賦給變量,例： use XSCJ2005 go declare xm varchar(8) select xm=劉豐 select xm=sname from student where sno=123456789 select xm as name go,說(shuō)明：如果select語(yǔ)句沒有返回行，變量將保留當(dāng)前值,6.3 運(yùn)算符和表達(dá)式,SQL Server 2008提供以下幾類運(yùn)算符：算術(shù)運(yùn)算符、關(guān)系運(yùn)算符、邏輯運(yùn)算符、字符運(yùn)算符和位運(yùn)算符。,6.3.1 算術(shù)運(yùn)算符 算術(shù)運(yùn)算符對(duì)兩個(gè)表達(dá)式執(zhí)行數(shù)學(xué)運(yùn)算。,算術(shù)運(yùn)算符,例【6-3】：計(jì)算6/5、6.0/5.0、5/6、5.0/6.0與6%5的值。 select 6/5,6.0/5.0,0,5.0/6.0,6%5 select 14/5,6.0/5.0,0,5.0/6.0,6%5 select 6.0/5,6.0/5.0,0,5.0/6.0,6%5,通過(guò)執(zhí)行語(yǔ)句我們注意到：6/5的結(jié)果為1，而6.0/5.0的結(jié)果為1.200000，兩者運(yùn)算結(jié)果并不相同。原因在于當(dāng)兩個(gè)具有相同數(shù)據(jù)類型的數(shù)進(jìn)行算術(shù)運(yùn)算時(shí)，運(yùn)算結(jié)果依然是當(dāng)前的數(shù)據(jù)類型。但是，當(dāng)兩個(gè)不同數(shù)據(jù)類型的數(shù)進(jìn)行算術(shù)運(yùn)算時(shí)，數(shù)據(jù)類型優(yōu)先級(jí)規(guī)則指定將優(yōu)先級(jí)較低的數(shù)據(jù)類型轉(zhuǎn)換為優(yōu)先級(jí)較高的數(shù)據(jù)類型。這樣能夠在最大程度上保護(hù)運(yùn)算結(jié)果的正確性和合理性。同樣，5/6與5.0/6.0的運(yùn)算結(jié)果也不相同。,6.3.2 關(guān)系運(yùn)算符,說(shuō)明： 兩個(gè)數(shù)值型數(shù)據(jù)比較時(shí)，按照值的大小直接比較； 兩個(gè)日期時(shí)間型數(shù)據(jù)比較時(shí)，按照年、月、日的先后順序比較； 兩個(gè)字符型數(shù)據(jù)比較時(shí)，英文字母按照ASCII碼值大小比較，漢字按照拼音先后順序比較。,use XSCJ go declare student char(6) set student=001111 if(student0) select * from xs where 學(xué)號(hào)=student,6.3.3 邏輯運(yùn)算符,例：查詢成績(jī)高于李明最高成績(jī)的學(xué)生姓名、課程名和成績(jī) select sname,cname,grade from student join sc join course on o=o on student.sno=sc.sno and gradeall (select grade from student join sc join course on o=o on student.sno=sc.sno and sname=李明 ),1)any,all,例：查詢成績(jī)高于李明最低成績(jī)的學(xué)生姓名、課程名和成績(jī) select sname,cname,grade from student join sc join course on o=o on student.sno=sc.sno and gradeany (select grade from student join sc join course on o=o on student.sno=sc.sno and sname=李明 ),2）.Between 例：查詢91及92年出生的學(xué)生信息 select * from student where sbirth between 1991-1-1 and 1992-12-31 例：查詢不是91、92年出生的學(xué)生信息 select * from student where sbirth not between 1991-1-1 and 1992-12-31,查詢姓王或姓劉的學(xué)生信息 select * from student where sname like 王劉% 查詢不姓王也不姓劉的學(xué)生信息 select * from student where sname like 王劉%,3）.Like,查詢所有選課的學(xué)生信息 select * from student where exists (select * from sc where sno=student.sno) 查詢沒有選課的學(xué)生信息 select * from student where not exists (select * from sc where sno=student.sno),4. exists,6.3.4 字符運(yùn)算符,字符運(yùn)算符只有一個(gè)“+”，又稱為字符串串聯(lián)運(yùn)算符。字符串之間通過(guò)“+”實(shí)現(xiàn)字符串的連接。,select 8899+7788 select 8899+7788 select 8899+7788,說(shuō)明：當(dāng)數(shù)據(jù)類型不相同時(shí)，轉(zhuǎn)換成優(yōu)先級(jí)別高的。,6.3.5 位運(yùn)算符,位運(yùn)算符在兩個(gè)表達(dá)式之間實(shí)現(xiàn)按位操作，這兩個(gè)表達(dá)式應(yīng)該為整型數(shù)據(jù)類型或與整型兼容的數(shù)據(jù)類型（如字符型等，但不能是image類型),6.3.5 位運(yùn)算符,示例： select 128&129,128|129,128129,6.3.6 運(yùn)算符的優(yōu)先順序 當(dāng)運(yùn)算符的級(jí)別不同時(shí)，先對(duì)較高級(jí)別的運(yùn)算符進(jìn)行運(yùn)算，然后再對(duì)較低級(jí)別的運(yùn)算符進(jìn)行運(yùn)算。當(dāng)一個(gè)表達(dá)式中運(yùn)算符的級(jí)別相同時(shí)，一般按照從左到右的順序進(jìn)行運(yùn)算。當(dāng)表達(dá)式中有括號(hào)時(shí)，應(yīng)先對(duì)括號(hào)內(nèi)的表達(dá)式進(jìn)行求值；若表達(dá)式中有嵌套的括號(hào)，則首先對(duì)嵌套最深的表達(dá)式求值。,例：判斷兩個(gè)數(shù)大小 declare x int,y int set x=8 set y=3 if xy print x大于y else print x小于或等于y,6.4 流程控制語(yǔ)句,6.4.2 IF ELSE語(yǔ)句 IF 條件表達(dá)式 語(yǔ)句體1 ELSE 語(yǔ)句體2,例：輸入一個(gè)坐標(biāo)值，判斷其在哪一個(gè)象限 declare x int,y int set x=8 set y=-3 if x0 if y0 print xy位于第一象限 else print xy位于第四象限 else if y0 print xy位于第二象限 else print xy位于第三象限,IF.ELSE語(yǔ)句,例【6-6】：設(shè)定變量score，根據(jù)score的值判斷成績(jī)是否合格，并輸出結(jié)論。 declare score int,a nchar(10) set score=55 if score=60 set a=N成績(jī)合格 else set a=N成績(jī)不合格 select a,1.簡(jiǎn)單case語(yǔ)句 計(jì)算 input_expression，然后按指定順序?qū)γ總€(gè)when子句的input_expressionwhen_expression進(jìn)行計(jì)算。 返回第一個(gè)取值為true的input_expression= when_expression的result_expression. 如果沒有取值為true的input_expression= when_expression，則當(dāng)指定else子句時(shí)，sql server將返回else_result_expression；若沒有指定else子句時(shí)，則返回null值。 2.Case 搜索語(yǔ)句 按指定順序?yàn)槊總€(gè)when子句的Boolean_expression求值。 返回第一個(gè)取值為true的Boolean_expression的result_expression。 如果沒有取值為true的Boolean_expression，則當(dāng)指定else子句時(shí)，sql server將返回else_result_expression；若沒有指定else子句時(shí)，則返回null值。,6.4.3 case 語(yǔ)句,-簡(jiǎn)單Case語(yǔ)句 CASE sex WHEN 1 THEN 男 WHEN 2 THEN 女 ELSE 其他 END -Case搜索語(yǔ)句 CASE WHEN sex = 1 THEN 男 WHEN sex = 2 THEN 女 ELSE 其他 END,簡(jiǎn)單Case語(yǔ)句只返回第一個(gè)符合條件的值，剩下的Case部分將會(huì)被自動(dòng)忽略。 -比如說(shuō)，下面這段SQL，你永遠(yuǎn)無(wú)法得到“第二類”這個(gè)結(jié)果 CASE WHEN col_1 IN ( a, b) THEN 第一類 WHEN col_1 IN (a) THEN 第二類 ELSE其他 END,select sno,cno,grade, case when grade=90 then 優(yōu)秀 when grade=80 then 良好 when grade=70 then 中等 when grade=60 then 及格 when grade<60 then 不及格 end as 成績(jī)等級(jí) from sc,1.簡(jiǎn)單Case 語(yǔ)句,6.4.3 case 語(yǔ)句 2.Case 搜索語(yǔ)句 例6-7:,create table temp (score int not null) insert into temp values(60) insert into temp values(30) insert into temp values(90) insert into temp values(106) insert into temp values(87),select score,等級(jí)= case when score=60 and score=80 and score=90 and score<100 then 優(yōu)秀 else 數(shù)據(jù)出界 end from temp,說(shuō)明:若省略了else子句,則結(jié)果為NULL,6.4.3 case 語(yǔ)句 3.利用case語(yǔ)句創(chuàng)建視圖 例:,create view v as select 學(xué)號(hào),課程號(hào),成績(jī),等級(jí)= case when 成績(jī)=0 and 成績(jī)=60 and 成績(jī)=70 and 成績(jī)=80 and 成績(jī)=90 and 成績(jī)<=100 then 優(yōu)秀 end from sc go,select * from v,6.4.4 goto語(yǔ)句 無(wú)條件轉(zhuǎn)移語(yǔ)句，使用goto語(yǔ)句可以將執(zhí)行流程轉(zhuǎn)移到標(biāo)簽指定的位置。為了與前面的版本兼容，SQL SERVER 2008 支持goto語(yǔ)句，但由于該語(yǔ)句破壞了語(yǔ)句的結(jié)構(gòu)，容易引發(fā)不易發(fā)現(xiàn)的問題，所以應(yīng)該盡量減少或避免使用。,declare x int set x=1 loving: print x select x=x+1 while x<=3 goto loving,說(shuō)明：標(biāo)號(hào)是GOTO目標(biāo)，它不僅僅標(biāo)識(shí)了跳轉(zhuǎn)目標(biāo)，標(biāo)號(hào)不隔離其前后語(yǔ)句。執(zhí)行標(biāo)號(hào)前面語(yǔ)句的用戶跳過(guò)標(biāo)號(hào)并執(zhí)行標(biāo)號(hào)后的語(yǔ)句。除非標(biāo)號(hào)前面的語(yǔ)句本身是控制流語(yǔ)句（return），這種情況才發(fā)生。,declare pjf float if (select COUNT(*)from sc where sno=2007056101)=0 goto label else begin select pjf=AVG(grade) from sc where sno=2007056101 print pjf end label: print 無(wú)學(xué)生或此學(xué)生沒有選課！,示例：輸出2007056101號(hào)學(xué)生平均成績(jī)，若此學(xué)生沒有選課，則顯示相應(yīng)提示信息，用goto完成。,說(shuō)明：標(biāo)號(hào)是GOTO目標(biāo)，它不僅僅標(biāo)識(shí)了跳轉(zhuǎn)目標(biāo)，標(biāo)號(hào)不隔離其前后語(yǔ)句。執(zhí)行標(biāo)號(hào)前面語(yǔ)句的用戶跳過(guò)標(biāo)號(hào)并執(zhí)行標(biāo)號(hào)后的語(yǔ)句。除非標(biāo)號(hào)前面的語(yǔ)句本身是控制流語(yǔ)句（return），這種情況才發(fā)生。,6.4.5 whilebreak和continue 語(yǔ)句 1.while 語(yǔ)句,declare s int,num int select s=0,num=1 while num<=100 begin set s=s+num set num=num+1 end print 1+2+3+.+100=+convert(char,s) go,declare x int set x=1 while x<=3 begin print x select x=x+1 end,6.4.5 whilebreak和continue 語(yǔ)句 2.break 語(yǔ)句 break 語(yǔ)句一般用在while循環(huán)或if .else語(yǔ)句中，用于退出本層循環(huán)。,例：求1100之間的累加和，當(dāng)和超過(guò)1000時(shí)停止累加，顯示累加和以及累加到的位置。,declare i int,s int set i=1 set s=0 while i=1000 break set i=i+1 end select s as s,i as i,6.4.5 whilebreak和continue 語(yǔ)句 3.continue 語(yǔ)句 continue 命令可以讓程序跳過(guò)continue命令之后的語(yǔ)句，回到while循環(huán)的第一行命令。Break則讓程序完全跳出循環(huán)，結(jié)束while命令的執(zhí)行。,例：求110之間的偶數(shù)和，并用continue控制語(yǔ)句的輸出。,declare x int,sum int set x=1 set sum=0 while x<10 begin set x=x+1 if x%2=0 set sum=sum+x else continue print 只有x是偶數(shù)才輸出這句話 end print sum,6.4.6 waitfor語(yǔ)句 waitfor delay time|time time| 延遲一段時(shí)間間隔執(zhí)行 指定從何時(shí)起執(zhí)行，用于指定觸發(fā)語(yǔ)句塊，存儲(chǔ)過(guò)程以及事物執(zhí)行時(shí)刻。,waitfor delay 00:00:03 print 蔥蔥好漂亮！ waitfor time 10:52:00 print 請(qǐng)獲得格萊美最高榮譽(yù)獎(jiǎng)的李小蔥發(fā)言！,6.4.7 return 語(yǔ)句 return語(yǔ)句用于從過(guò)程、批處理或語(yǔ)句塊中無(wú)條件地退出，位于return語(yǔ)句之后語(yǔ)句將不執(zhí)行。,use xscj2005 go if exists (select * from course where cname=高等數(shù)學(xué)) begin print 高等數(shù)學(xué)課程記錄已存在！ return end else insert into course values(1001,高等數(shù)學(xué),1005,4),declare x int set x=3 if x0 print 遇到return之前 return print 遇到return之后,SQL SERVER2005新增功能 try.catch語(yǔ)句,Bgein try 語(yǔ)句語(yǔ)句塊 End try Begin catch 語(yǔ)句語(yǔ)句塊 End catch 類似C+的異常處理，當(dāng)執(zhí)行try語(yǔ)句塊中的代碼出現(xiàn)錯(cuò)誤時(shí)，系統(tǒng)將把控制傳遞到catch語(yǔ)句塊進(jìn)行處理。,begin try create database test end try begin catch print test數(shù)據(jù)庫(kù)已存在 drop database test print 原有的test數(shù)據(jù)庫(kù)已刪除 create database test print test數(shù)據(jù)庫(kù)再次成功創(chuàng)建 end catch,6.5 函數(shù),6.5.1 數(shù)學(xué)函數(shù),例【6-8】：使用常用數(shù)學(xué)函數(shù)計(jì)算-1的絕對(duì)值，e的10次方，5的自然對(duì)數(shù)，半徑為3的圓的面積和49的平方根。 SELECT N-1絕對(duì)值=ABS(-1), Ne的10次方=EXP(10), N5的自然對(duì)數(shù)=LOG(5), N半徑為3的圓的面積=PI()*3*3, N49的平方根=SQRT(49),6.5.2 字符串函數(shù),例【6-9】：使用常用字符串函數(shù)計(jì)算A的ASCII碼，SQL Server的前3個(gè)字符，數(shù)據(jù)庫(kù)原理字符串的長(zhǎng)度，將China轉(zhuǎn)換為大寫字母，將英語(yǔ)四級(jí)改為英語(yǔ)六級(jí)。 T-SQL語(yǔ)句如下： SELECT NA的ASCII=ASCII(A), NSQL Server的前3個(gè)字符=LEFT(SQL Server,3), N數(shù)據(jù)庫(kù)原理的長(zhǎng)度=LEN(N數(shù)據(jù)庫(kù)原理), N將China轉(zhuǎn)換為大寫字母=UPPER(China), N將英語(yǔ)四級(jí)改為英語(yǔ)六級(jí)=REPLACE(N英語(yǔ)四級(jí),N四,N六),declare s varchar(50) set s=北京,歡迎您! select right(s,4)+left(s,2),將北京,歡迎您!變成歡迎您! 北京,select ASCII(loving) /*返回最左端字符的ascii碼值,Len用于返回指定字符串的字符（而不是字節(jié)）個(gè)數(shù)。,select LEN(loving) select len(2011.10.01) select LEN (李小蔥生日快樂！),declare s varchar(50) set s=2001年月歡慶北京申奧成功 -變成北京年月申奧成功 select substring(s,11,2)+LEFT(s,8)+RIGHT(s,4),select replicate(loving,3),select REPLACE(SQL SERVER,ER,loving),Replace函數(shù)將字符串中的子字符串替換為指定字符串,Replicate函數(shù)用于指定的次數(shù)重復(fù)字符串表達(dá)式,select ASCII(loving),ascii函數(shù)返回最左端字符的ascii代碼值。 純數(shù)字的字符串可以不用單引號(hào)，而其他字符表達(dá)式必需用,否則出錯(cuò)。,子串替換函數(shù) 格式：STUFF（字符表達(dá)式1，起始位置，長(zhǎng)度，字符表達(dá)式2） 功能：用“字符表達(dá)式2”的值替換“字符表達(dá)式1”中由“起始位置”和“長(zhǎng)度”指明的一個(gè)子串。 select STUFF(計(jì)算機(jī)等級(jí)考試,4,2,專業(yè)) select STUFF(計(jì)算機(jī)等級(jí)考試,6,0,二級(jí)),select charindex(l,I like football) 搜索的起始位置是1 select charindex(l,I like football,4) 搜索的起始位置是4 select charindex(l,I like football,15) 搜索的起始位置是15,Charindex函數(shù)返回字符串指定表達(dá)式的起始位置。,6.5.3 日期和時(shí)間函數(shù),Convert CONVERT() 函數(shù)是把日期轉(zhuǎn)換為新數(shù)據(jù)類型的通用函數(shù)。 CONVERT() 函數(shù)可以用不同的格式顯示日期/時(shí)間數(shù)據(jù)。 例如：convert(char(4),year(date1),例【6-10】：使用日期和時(shí)間函數(shù)顯示當(dāng)前日期，在當(dāng)前日期后10天的日期，當(dāng)前日期與2011年1月1日相隔的天數(shù)。 T-SQL語(yǔ)句如下： SELECT N顯示當(dāng)前系統(tǒng)日期=GETDATE(), N在當(dāng)前日期后10天的日期=DATEADD(day,10,GETDATE(), N當(dāng)前日期與2011年1月1日相隔的天數(shù)=DATEDIFF(DAY,GETDATE(),2011-01-01),補(bǔ)充：數(shù)據(jù)類型轉(zhuǎn)換函數(shù) 數(shù)據(jù)類型轉(zhuǎn)換函數(shù)可以將某一種類型的數(shù)據(jù)轉(zhuǎn)換成另一種類型的數(shù)據(jù)。 1)數(shù)值轉(zhuǎn)換為字符函數(shù) 格式：STR(數(shù)值表達(dá)式 ，長(zhǎng)度 ，小數(shù)位數(shù)) 功能：將數(shù)值表達(dá)式的值轉(zhuǎn)換為字符串，轉(zhuǎn)換時(shí)自動(dòng)四舍五入。默認(rèn)長(zhǎng)度為10，若不指定小數(shù)位則保留到整數(shù)位，如果長(zhǎng)度小于數(shù)值表達(dá)式值的整數(shù)位數(shù)，則返回一串*號(hào)。 理想長(zhǎng)度=整數(shù)+小數(shù)點(diǎn)+小數(shù) 要求長(zhǎng)度理想長(zhǎng)度：加前導(dǎo)符 整數(shù)長(zhǎng)度<要求長(zhǎng)度<理想長(zhǎng)度：保留整數(shù)部分，調(diào)整小數(shù)部分。 要求長(zhǎng)度<整數(shù)長(zhǎng)度：*,【例】將數(shù)值型數(shù)據(jù)123.456轉(zhuǎn)換為字符型數(shù)據(jù)，并顯示其結(jié)果。 解：? STR(123.456) 主屏幕顯示結(jié)果為： 凵凵凵凵凵凵凵123 ? STR(123.456,7,2), STR(123.456,5,2), STR(123.456,2) 主屏幕顯示結(jié)果分別為： 凵123.46 123.5 *,select str(123.456),str(123.456,5,2),STR(123.456,2),SQL Server會(huì)自動(dòng)完成數(shù)據(jù)類型的轉(zhuǎn)換，這種轉(zhuǎn)換稱隱式轉(zhuǎn)換。但有些類型就不能自動(dòng)轉(zhuǎn)換，如int整型與char型，這時(shí)就要顯式轉(zhuǎn)換函數(shù)。（case，convert） 示例 select cast(123 as int)+10 說(shuō)明：as之后是系統(tǒng)提供的數(shù)據(jù)類型，包括bigint和sql_variant，不能使用用戶定義的數(shù)據(jù)類型。 select CONVERT(nvarchar(30),getdate(),102) +轉(zhuǎn)換成字符型,說(shuō)明：102是日期樣式,6.5.4 聚合函數(shù),avg：返回一組值的平均值。 binary_checksum：返回對(duì)表中的行或者表達(dá)式列表計(jì)算的二進(jìn)制校驗(yàn)位。 checksum：返回在表中的行或者表達(dá)式列表計(jì)算的校驗(yàn)值，該函數(shù)用于生成哈希索引。 checksum_agg：返回一組值的校驗(yàn)值。 count:返回一組值中項(xiàng)目的數(shù)量。(返回值為int類型)。 count_big：返回一組值中項(xiàng)目的數(shù)量。(返回值為bigint類型)。 grouping：產(chǎn)生一個(gè)附加的列，當(dāng)用cube或rollup運(yùn)算符添加行時(shí)，附加的列輸出為1，當(dāng)添加的行不是由cube或rollup運(yùn)算符產(chǎn)生時(shí)，附加的列輸出為0。 max：返回表達(dá)式或者項(xiàng)目中的最大值。 min：返回表達(dá)式或者項(xiàng)目中的最小值。 sum：返回表達(dá)式中所有項(xiàng)的和，或者只返回distinct值。sum只能用于數(shù)字列。 stdev：返回表達(dá)式中所有值的統(tǒng)計(jì)標(biāo)準(zhǔn)偏差。 stdevp：返回表達(dá)式中所有值的統(tǒng)計(jì)統(tǒng)計(jì)標(biāo)準(zhǔn)偏差。 var：返回表達(dá)式中所有值的統(tǒng)計(jì)標(biāo)準(zhǔn)方差。 varp：返回表達(dá)式中所有值的統(tǒng)計(jì)統(tǒng)計(jì)標(biāo)準(zhǔn)方差。,【例6-11】avg函數(shù)的使用。以下語(yǔ)句統(tǒng)計(jì)所有學(xué)生成績(jī)的平均值。 use 實(shí)例數(shù)據(jù)庫(kù) select avg(分?jǐn)?shù)) as 平均成績(jī) from 選課表 go 【例6-12】max函數(shù)的使用。以下語(yǔ)句返回選課表中學(xué)生成績(jī)的最高分?jǐn)?shù)。 use 實(shí)例數(shù)據(jù)庫(kù) select max(分?jǐn)?shù)) as 最高成績(jī) from 選課表 go 【例6-13】count函數(shù)的使用。以下語(yǔ)句返回學(xué)生表中的記錄個(gè)數(shù)。 use 實(shí)例數(shù)據(jù)庫(kù) select count(學(xué)號(hào)) as 總?cè)藬?shù) from 選課表 go,6.5.5 用戶自定義函數(shù),用戶在編寫程序的過(guò)程中，除了可以調(diào)用系統(tǒng)函數(shù)外，還可以根據(jù)自己的需要自定義函數(shù)。自定義函數(shù)包括表值函數(shù)和標(biāo)量值函數(shù)兩類，其中表值函數(shù)又包括內(nèi)聯(lián)表值函數(shù)和多語(yǔ)句表值函數(shù)。 內(nèi)聯(lián)表值函數(shù)：返回值為可更新表。如果用戶自定義函數(shù)包含單個(gè)SELECT語(yǔ)句且該語(yǔ)句可以更新，則該函數(shù)返回的表也可以更新。 多語(yǔ)句表值函數(shù)：返回值為不可更新表。如果用戶自定義函數(shù)包含多個(gè)SELECT語(yǔ)句，則該函數(shù)返回的表不可更新。 標(biāo)量函數(shù)：返回值為標(biāo)量值。 用戶自定義函數(shù)的創(chuàng)建有兩種方法: 直接在SQL Server Management Studio中創(chuàng)建。 利用代碼進(jìn)行創(chuàng)建。,create function maxf (para1 real,para2 real) returns real as begin declare max real if para1para2 set max=para1 else set max=para2 return(max) end,1.標(biāo)量函數(shù),use xscj2005 go create function a(no char(10) returns table as return (select student.學(xué)號(hào),姓名,課程名,成績(jī) from student,sc,course where student.學(xué)號(hào)=sc.學(xué)號(hào) and sc.課程號(hào)=course.課程號(hào) and student.學(xué)號(hào)=no) go select * from a(2008056102),2.內(nèi)嵌表值函數(shù)： 只能通過(guò)select語(yǔ)句調(diào)用，在調(diào)用時(shí)可以省略函數(shù)的所有者,use xscj2005 go create function b(no char(10) returns score table (sno char(10), sname varchar(8), cname varchar(30), grade tinyint) as begin insert score select student.學(xué)號(hào),姓名,課程名,成績(jī) from student,sc,course where student.學(xué)號(hào)=sc.學(xué)號(hào) and sc.課程號(hào)=course.課程號(hào) and student.學(xué)號(hào)=no return end go select * from b(2008056103),3.多語(yǔ)句表值函數(shù)：,print dbo.maxf(12,38.6) select N最大數(shù)為:+ convert(varchar(10),dbo.maxf(2.8,15676.9) declare m real exec m=dbo.maxf 12.8,789.6 select m as 最大數(shù)為 declare m real exec m=dbo.maxf para1=789.6,para2=12.8 select m as 最大數(shù)為,4.自定義函數(shù)的調(diào)用 調(diào)用標(biāo)量函數(shù)，必順提供至少由兩部分組成的名稱（所有者.函數(shù)名），可用print, select, exec語(yǔ)句調(diào)用,內(nèi)嵌表值函數(shù)和多語(yǔ)句表值函數(shù)都返回表，二者不同之處在于： 內(nèi)嵌表值函數(shù)沒有函數(shù)體，返回的表是單個(gè)select語(yǔ)句的結(jié)果集。 多語(yǔ)句表值函數(shù)在begin.end 定義的函數(shù)主體包含T-SQL語(yǔ)句，這些語(yǔ)句可生成行并將行插入表中，最后返回表。,