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

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