數(shù)據(jù)庫(kù)原理三章節(jié)關(guān)系數(shù)據(jù)庫(kù)標(biāo)準(zhǔn)語(yǔ)言SQL.ppt

數(shù)據(jù)庫(kù)原理 第三章：關(guān)系數(shù)據(jù)庫(kù)標(biāo)準(zhǔn)語(yǔ)言SQL,授課教師：王哲,復(fù)習(xí)上節(jié)課內(nèi)容,視圖基礎(chǔ)概念 管理視圖 定義視圖 修改視圖 刪除視圖 操作視圖數(shù)據(jù) 查詢視圖,本節(jié)課內(nèi)容,T-SQL語(yǔ)言 常量、變量與數(shù)據(jù)類型 運(yùn)算符與表達(dá)式 系統(tǒng)內(nèi)置函數(shù) 用戶自定義數(shù)據(jù)類型 用戶定義函數(shù),一、T-SQL語(yǔ)言,Transact-SQL語(yǔ)言主要組成部分 數(shù)據(jù)定義語(yǔ)言（DDL，Data Definition Language） 數(shù)據(jù)操縱語(yǔ)言（DML，Data Manipularion Language） 數(shù)據(jù)控制語(yǔ)言（DCL，Data Control Language） 一些附加的語(yǔ)言元素,數(shù)據(jù)定義語(yǔ)言（DDL）,數(shù)據(jù)定義語(yǔ)言是指用來(lái)定義和管理數(shù)據(jù)庫(kù)以及數(shù)據(jù)庫(kù)中的各種對(duì)象的語(yǔ)句，這些語(yǔ)句包括CREATE、ALTER和DROP等語(yǔ)句。在SQL Server 中，數(shù)據(jù)庫(kù)對(duì)象包括表、視圖、觸發(fā)器、存儲(chǔ)過程、規(guī)則、缺省、用戶自定義的數(shù)據(jù)類型等。這些對(duì)象的創(chuàng)建、修改和刪除等都可以通過使用CREATE、ALTER、DROP等語(yǔ)句來(lái)完成。,數(shù)據(jù)操縱語(yǔ)言（DML）,數(shù)據(jù)操縱語(yǔ)言是指用來(lái)查詢、添加、修改和刪除數(shù)據(jù)庫(kù)中數(shù)據(jù)的語(yǔ)句，這些語(yǔ)句包括SELECT、INSERT、UPDATE、DELETE等。在默認(rèn)情況下，只有sysadmin、dbcreator、db_owner或db_datawriter等角色的成員才有權(quán)利執(zhí)行數(shù)據(jù)操縱語(yǔ)言。,數(shù)據(jù)控制語(yǔ)言（DCL）,數(shù)據(jù)控制語(yǔ)言（DCL）是用來(lái)設(shè)置或者更改數(shù)據(jù)庫(kù)用戶或角色權(quán)限的語(yǔ)句，這些語(yǔ)句包括GRANT、DENY、REVOKE等語(yǔ)句，在默認(rèn)狀態(tài)下，只有sysadmin、dbcreator、db_owner或db_securityadmin等角色的成員才有權(quán)利執(zhí)行數(shù)據(jù)控制語(yǔ)言。,其它語(yǔ)言元素,這部分是微軟為了用戶編程方便增加的語(yǔ)言元素，這些語(yǔ)言元素包括運(yùn)算符、變量、函數(shù)和流程控制語(yǔ)句。,運(yùn)算符與表達(dá)式 變量 用戶自定義數(shù)據(jù)類型 系統(tǒng)內(nèi)置函數(shù) 用戶自定義函數(shù),1、運(yùn)算符與表達(dá)式,運(yùn)算符是一些符號(hào)，它們能夠用來(lái)執(zhí)行算術(shù)運(yùn)算、字符串連接、賦值以及在字段、常量和變量之間進(jìn)行比較。 在SQL Server 2005中，運(yùn)算符主要有以下六大類：算術(shù)運(yùn)算符、賦值運(yùn)算符、位運(yùn)算符、比較運(yùn)算符、邏輯運(yùn)算符以及字符連接運(yùn)算符、賦值運(yùn)算符。,1）字符串連接運(yùn)算符,字符串串聯(lián)運(yùn)算符允許通過加號(hào) (+) 進(jìn)行字符串串聯(lián)，這個(gè)加號(hào)即被稱為字符串串聯(lián)運(yùn)算符。例如對(duì)于語(yǔ)句SELECT abc+def，其結(jié)果為abcdef。,例1：列出student表中記錄，其中姓名和院系顯示在一列。 use stu select sno, sname+sdept, sage from student go,例2：顯示xs表中記錄，其中姓名和院系名顯示在一列，以逗號(hào)隔開。 use stu select sno, sname+，+sdept, sage from student go,2）賦值運(yùn)算符,Transact-SQL 中只有一個(gè)賦值運(yùn)算符，即等號(hào)（=）； 賦值運(yùn)算符使我們能夠?qū)?shù)據(jù)值指派給特定的對(duì)象。給局部變量賦值的SET和SELECT語(yǔ)句中使用=運(yùn)算符。 另外，還可以使用賦值運(yùn)算符在列標(biāo)題和為列定義值的表達(dá)式之間建立關(guān)系。,2、變量,變量用于臨時(shí)存放數(shù)據(jù)，變量中的數(shù)據(jù)隨著程序的運(yùn)行而變化。 變量定義時(shí)，必須有名字及數(shù)據(jù)類型兩個(gè)屬性。 變量是一種語(yǔ)言中必不可少的組成部分。Transact-SQL語(yǔ)言中有兩種形式的變量，一種是用戶自己定義的局部變量，另外一種是系統(tǒng)提供的全局變量。,1）局部變量,局部變量可以保存數(shù)據(jù)值，以供控制流語(yǔ)句測(cè)試以及保存由存儲(chǔ)過程返回的數(shù)據(jù)值等。 它的作用范圍僅限制在程序內(nèi)部。 另外，局部變量被引用時(shí)要在其名稱前加上標(biāo)志“”，而且必須先用DECLARE命令定義后才可以使用。,局部變量的使用,局部變量的定義 語(yǔ)法格式： DECLARE local_variable datatype ,n 局部變量的賦值 用SET語(yǔ)句賦值： SET local_variableexpression /*一個(gè)set語(yǔ)句只能給一個(gè)變量賦值*/ 用SELECT語(yǔ)句賦值 SELECT local_variableexpression ,n,例1：創(chuàng)建局部變量xuehao并賦值，然后輸出所有等于該值的記錄。 DECLARE xuehao char(9) Set xuehao=200515001 Select sname, ssex, sdept from student where sno= xuehao Go,例2：創(chuàng)建局部變量sex、zym并賦值，然后輸出數(shù)據(jù)表中等于變量的值的所有記錄。 DECLARE sex char(2),zym char(10) Set sex=男 Set yx=CS Select sno，sname, ssex, sdept from student where sdept =yx and ssex = sex Go,例3：將學(xué)號(hào)為200515001的記錄的姓名賦值給變量name。 DECLARE name char(8) Set name= (Select sname from student where sno=200518001) select name as 姓名 Go,例4：將學(xué)號(hào)為的記錄的姓名賦值給變量name。 DECLARE sno char(9) set sno=200515001 Select sname as 姓名 from xs where sno= sno Go,2）全局變量,引用全局變量時(shí)，必須以標(biāo)記符“”開頭。 局部變量的名稱不能與全局變量的名稱相同。 全局變量是SQL Server系統(tǒng)內(nèi)部使用的變量，其作用范圍并不僅僅局限于某一程序，而是任何程序均可以隨時(shí)調(diào)用。,3、用戶自定義數(shù)據(jù)類型,對(duì)于數(shù)據(jù)庫(kù)stu，數(shù)據(jù)表student和sc都有字段“學(xué)號(hào)” ，有相同的數(shù)據(jù)類型、字段長(zhǎng)度及是否為空的屬性。為了使用方便，并使含義明確，可以先定義一數(shù)據(jù)類型，來(lái)描述“學(xué)號(hào)”的類型屬性，然后讓數(shù)據(jù)表student和sc的 “學(xué)號(hào)”字段定義為該類型。 定義方式：調(diào)用系統(tǒng)存儲(chǔ)過程 sp_addtype,語(yǔ)法格式： 例1：使用Sp_addtype創(chuàng)建數(shù)據(jù)類型sno。 Use student exec sp_addtype sno , char(9) , not null,刪除用戶自定義數(shù)據(jù)類型,語(yǔ)法格式： Sp_droptype typename= type 例1：刪除數(shù)據(jù)類型sno。 Use xsbook Exec sp_droptype sno,4、系統(tǒng)內(nèi)置函數(shù)的使用,SQL Server 2005的內(nèi)置函數(shù)非常多，常用的系統(tǒng)內(nèi)置函數(shù): 數(shù)學(xué)函數(shù) 字符串函數(shù) 日期時(shí)間函數(shù),1）數(shù)學(xué)函數(shù),例1：數(shù)學(xué)函數(shù)綜合應(yīng)用：求絕對(duì)值。 Select abs(8.9),abs(-5.0) ,abs(0.0),abs(0) 例2：數(shù)學(xué)函數(shù)綜合應(yīng)用：返回半徑為1英寸、高為5英寸的圓柱面積。 declare h float,r float Select h=5,r=2 Select pi()*square(r)*h as 圓柱面積,2）字符串函數(shù),LEFT函數(shù)和RIGHT函數(shù) 函數(shù)格式： LEFT（expression1,n） 功能：返回字符串expression1從左邊開始n個(gè)字符組成的字符串。如果n=0，則返回一個(gè)空字符串。 返回值數(shù)據(jù)類型：varchar 例：LEFT(abcde, 3)結(jié)果為abc。 RIGHT(abcde, 3)結(jié)果為cde,LEN函數(shù) 函數(shù)格式： LEN（expression1） 功能：返回字符串expression1中的字符個(gè)數(shù)，不包括字符串末尾的空格。 返回值數(shù)據(jù)類型：int 例：LEN(abcde )結(jié)果為5 LOWER函數(shù)和UPER函數(shù) 函數(shù)格式： LOWER（expression1） 功能：將字符串expression1中的大寫字母替換為小寫字母。 返回值數(shù)據(jù)類型：varchar 例：LOWER(12asABC45*%def)結(jié)果為12asabc45*%def。 UPER(12ABC45*%def)結(jié)果為12ABC45 *% DEF。,3）日期時(shí)間函數(shù),GETDATE函數(shù) 功能：按SQL Server內(nèi)部標(biāo)準(zhǔn)格式返回系統(tǒng)日期和時(shí)間。 返回值數(shù)據(jù)類型：datetime 例如：getdate() -結(jié)果為2007-04-13 21:51:32.390 YEAR函數(shù) 功能：返回指定日期date中年的整數(shù)。 返回值數(shù)據(jù)類型：int 例如： year(2004-3-5) -結(jié)果為2004,DATENAME函數(shù) 函數(shù)格式：DATENAME（datepart,date） 功能：返回日期date中由datepart指定的日期部分的字符串。 返回值數(shù)據(jù)類型：nvarchar 例如： datename(yy,1993-3-4) -結(jié)果為1993 datename (m,1993-3-4) -結(jié)果為03 datename (d,1993-3-4) -結(jié)果為4,DATEPART函數(shù) 函數(shù)格式：DATEPART（datepart,date） 功能：與DATENAME類似，只是返回值為整數(shù)。 返回值數(shù)據(jù)類型：int 例如： datepart(yy,1993-3-4) -結(jié)果為1993 datepart (m,1993-3-4) -結(jié)果為3 datepart (d,1993-3-4) -結(jié)果為4,5、用戶自定義函數(shù),內(nèi)置函數(shù)大大方便了用戶進(jìn)行程序設(shè)計(jì)，但用戶編程時(shí)常常需要將一個(gè)或多個(gè)TSQL語(yǔ)句組成子程序，以便反復(fù)調(diào)用。 SQL SERVER 2005允許用戶根據(jù)需要自己定義函數(shù)。 標(biāo)量函數(shù) 內(nèi)嵌表值函數(shù) 多語(yǔ)句表值函數(shù) 關(guān)鍵字Create Function,例1：定義一函數(shù)，按院系統(tǒng)計(jì)各院系的平均年齡。 CREATE FUNCTION avg_age(sdept char(4) RETURNS int AS BEGIN declare avg int select avg =(select avg(sage) from student where sdept=sdept) Return avg END,利用select語(yǔ)句 Declare sdept char(4), avg int Set sdept =cs select avg=dbo.avg_age(sdept) select avg as 平均年齡 利用exec 語(yǔ)句 Declare sdept char(4), avg int exec avg=dbo.avg_age sdept =CS select avg as 平均年齡,調(diào)用函數(shù),練習(xí)1：定義一函數(shù)，按學(xué)號(hào)顯示院系。 CREATE FUNCTION sdept(sno char(9) RETURNS char AS BEGIN declare yx char select yx=(select sdept from student where sno=sno) Return yx END,例2：定義一函數(shù)，按根據(jù)院系名查詢?cè)搶I(yè)學(xué)生的基本信息。 CREATE FUNCTION student_info(yx char(4) RETURNS table AS RETURN(select sno,sname,ssex,sage,sdept from