數(shù)據(jù)類型和索引和表.ppt

1、,組長：韓立娜 匯報人：于思會 組員：韓立娜 于思會 劉述玲 程雁春 桓國艷 趙晴晴,Oracle數(shù)據(jù)庫中常用的基本數(shù)據(jù)類型,Sqlserver數(shù)據(jù)庫中常用的數(shù)據(jù)類型,數(shù)據(jù)類型指定列、存儲過程參數(shù)及局部變量的數(shù)據(jù)特性 數(shù)據(jù)按照數(shù)據(jù)類型存儲在列中 系統(tǒng)數(shù)據(jù)類型：系統(tǒng)數(shù)據(jù)類型是 SQL Server 支持的內置數(shù)據(jù)類型 用戶定義的數(shù)據(jù)類型：用戶根據(jù)系統(tǒng)數(shù)據(jù)類型自己定義的數(shù)據(jù)類型,系統(tǒng)數(shù)據(jù)類型 (1),系統(tǒng)數(shù)據(jù)類型 (2),系統(tǒng)數(shù)據(jù)類型 (3),系統(tǒng)數(shù)據(jù)類型 (4),系統(tǒng)數(shù)據(jù)類型 (5),系統(tǒng)數(shù)據(jù)類型 (6),系統(tǒng)數(shù)據(jù)類型 (7),系統(tǒng)數(shù)據(jù)類型 (8),用戶定義的數(shù)據(jù)類型特性,使用系統(tǒng)數(shù)據(jù)類型定義

2、具有預定義長度 指定其中是否可存儲 null 值 指定是否必須賦予默認值 指定是否要應用現(xiàn)有規(guī)則,管理用戶定義的數(shù)據(jù)類型 (1),sp_addtype typename = type, phystype = system_data_type , nulltype = null_type,管理用戶定義的數(shù)據(jù)類型 (2),參數(shù)： typename = type 用戶定義數(shù)據(jù)類型的名稱。數(shù)據(jù)類型名稱必須遵循標識符規(guī)則，并且在每個數(shù)據(jù)庫中必須是唯一的。type 是sysname，無默認值。 phystype = system_data_type 物理或 SQL Server 提供的數(shù)據(jù)類型（decim

3、al、int 等），用戶定義的數(shù)據(jù)類型即基于該類型。system_data_type 是 sysname，無默認值。 nulltype = null_type 指定必須如何處理 null 值。null_type 是 varchar(8)，設置值為 NULL（默認）、NOT NULL 或 NONULL,Access數(shù)據(jù)庫常用數(shù)據(jù)類型,Access 2000中字段可用的數(shù)據(jù)類型有以下幾種： 文本(Text)：文本、文本與數(shù)字、不必計算的數(shù)字，最長255個字符。 備注(Memo)：較長的文本，最長64,000字符。 數(shù)字(Number)型：用于算術運算的數(shù)字?？稍凇白侄未笮　敝蟹肿止?jié)、整型、單精度等

4、子類型 日期及時間(Date/Time)：從100到9999的日期及時間值?？蛇M行日期及時間的計算。8字節(jié) 貨幣(Currency)：貨幣值。使用貨幣類型可避免計算時四舍五入。8字節(jié) 自動編號(AutoNumber)：添加記錄時的自動序號 遞增：添一條記錄，字段值增1； 隨機：添一條記錄，字段值為另一隨機數(shù)。,2. 字段的數(shù)據(jù)類型,是/否(Yes/No)：取兩種值之一，如 Yes/No、True/False、On/Off。大小為1位。 OLE對象(Object)：可鏈接或嵌入其他使用OLE 協(xié)議的程序所創(chuàng)建的對象，如Word文檔、圖象、聲音等。最多1G。對象可鏈接或嵌入到Access表中。但只

5、能在窗體或報表中使用綁定對象框來顯示。 超鏈接(Hyperlink)：超文本，用作超鏈接地址。超鏈接最多包含三部分： 顯示文本：在字段或控件中顯示的文本； 地址：到文件（UNC）或頁面（URL）的路徑； 子地址：在文件或頁面中的地址。 查閱向導(Lookup Wizard)：由查閱向導創(chuàng)建。字段的值來自另一個表或數(shù)據(jù)列表中，可在列表框或組合框中選擇一個數(shù)據(jù)作為其值。,備注型、超鏈接型和OLE對象型字段不能索引。 若在表中輸入數(shù)據(jù)后更改字段的數(shù)據(jù)類型，則 在保存表時，由于進行大量數(shù)據(jù)轉換，時間較長； 若新的“數(shù)據(jù)類型”屬性的設置與段原數(shù)據(jù)類型沖突，可能會丟失某些數(shù)據(jù)。 Number、Date/T

6、ime、Currency和Yes/No類型具有預定義格式?？稍O置格式，但OLE對象型除外,5. 選擇數(shù)據(jù)類型時應注意的問題,MySQL數(shù)據(jù)類型,數(shù)據(jù)類型種類 數(shù)值列類型 字符串列類型 日期和時間列類型,數(shù)據(jù)類型種類,數(shù)值型 分為整型和浮點型 整型： 可以由十進制和十六進制表示 整數(shù)由數(shù)字序列組成，如：1，100。 由十六進制表示方法：0 x且后面加19和AF 的任意數(shù)字或字母，并且0 x中的X不能大寫。 浮點型: 浮點數(shù)由一個數(shù)字加一個小數(shù)點再加上一個數(shù)字組成。兩個數(shù)字序列不能同時為空。,字符串: 由單引號或者雙引號括起來的字符或者數(shù)字。 如：”abc”,abc10 字符串中要用轉義字符才能表

7、示的特殊符號,日期和時間值 是存儲如“2005 -1-1” 或者“12:00:00”這樣的數(shù)值的值。在MySQL中日期是按”年-月-日”的順序。 NULL值 是一種無類型的值，表示“空，什么也沒有”。,數(shù)值列類型,MySQL為除了NULL值外的所有通用數(shù)據(jù)類型提供了列類型。列類型是一種手段，通過這種手段可以描述表的列可以包含什么樣類型的值。 數(shù)值列類型 所有數(shù)值列類型的類型名及其說明和所占的字節(jié)數(shù)見下表：,數(shù)值列類型包括整型和浮點型 說明：TINYINT, SAMLLINT, MEDIUMINT, INT, BIGINT每個數(shù)據(jù)類型的取值范圍不同，故分別可以表示不同的數(shù)值范圍。在定義整型列時，

8、可以指定可選的顯示尺寸M(見上表),M是從1到255的值，它表示顯示列中值的字符數(shù)。,字符串列類型 所有字符串列類型及其說明和所占的字節(jié)數(shù)見下表：,說明： 對于可變長的字符串類型，其長度取決于實際存放在列中的值的長度。此長度在上表中用L來表示。附加若干額外字節(jié)，用來記錄字符串長度，也就是說總長度等于Ln（n1、2、3、4、8）,CHAR和VARCHAR類型 CHAR類型和VARCHAR類型長度范圍都是0255之間的大小。他們之間的差別在于MySQL處理存儲的方式： CHAR把這個大小視為值的準確大?。ㄓ每崭裉钛a比較短的值）。 VARCHAR類型把它視為最大值并且只使用了存儲字符串實際上需要的字

9、節(jié)數(shù)（增加了一個額外的字節(jié)記錄長度）。因而，較短的值當被插入一個語句為VARCHAR類型的字段時，將不會用空格填補（然而，較長的值仍然被截短 ）。,BLOB和TEXT類型 BLOB是二進制大對象，如果想存儲二進制數(shù)BLOB將是最佳選擇，而TEXT與之相同，只是BOLOB按照二進制編碼排序（區(qū)分大小寫），TEXT按照字符文本模式排序（不區(qū)分大小寫）。 ENUM 和SET類型 是特殊的串類型，其列值必須從固定的串集中選擇，二者差別為前者必須是只能選擇其中的一個值，而后者可以多選。（不推薦使用）,日期和時間列類型 所有時間和日期列類型的類型名及其說明和所占的字節(jié)數(shù)見下表：,說明： 每個時間和日期列類

10、型都有一個零值，當插入非法數(shù)值時就用零值來添加 表示日期時必須先按：年，月，日的順序給出 DATE ,TIME ,DATETIME分別是存儲日期，時間與日期和時間的組合，其格式為“YYYY-MM-DD”，“hh:mm:ss”和“YYYY-MM-DD hh:mm:ss”，對于DATETIME類型，日期和時間部分都需要 TIMESTAMP 時間戳列類型以YYYYMMDDhhmmss的格式來表示值，其取值范圍是19700101000000到2037年的某個時間，主要用于記錄更改或創(chuàng)建某個記錄,總結：常用數(shù)據(jù)類型,3.3.3 索引的建立和刪除,建立索引是加快查詢速度的有效手段 建立索引 DBA或表的屬

11、主（即建立表的人）根據(jù)需要建立 有些DBMS自動建立以下的索引 PRIMARY KEY UNIQUE,一、建立索引,語句格式 CREATE UNIQUE INDEX ON (, )； 用指定要建索引的基本表名字 索引可以建立在該表的一列或多列上，各列名之間用逗號分隔 用指定索引值的排列次序，升序：ASC，降序：DESC。缺省值：ASC UNIQUE表明此索引的每一個索引值只對應唯一的數(shù)據(jù)記錄,例題,例14 為學生-課程數(shù)據(jù)庫中的Student，Course兩個表建立索引。其中Student表按姓名升序建索引，Course表按課程名稱降序建唯一索引。 CREATE INDEX stu_sname

12、_index ON Student(S_name)； CREATE UNIQUE INDEX course_cname_index ON Course(C_name desc)；, 刪除索引,DROP INDEX ； 刪除索引時，系統(tǒng)會從數(shù)據(jù)字典中刪去有關該索引的描述。 例15 刪除Student表的stu_sname_index索引。 DROP INDEX stu_sname_index； 注意：主鍵索引不能被刪除；刪除主鍵約束時，主鍵索引會被自動刪除。,3.4.1 單表查詢,查詢僅涉及一個表，是一種最簡單的查詢操作 一、選擇表中的若干列 二、選擇表中的若干元組 三、order by 子句

13、四、聚集函數(shù)（aggregate functions） 五、group by 子句,一、選擇表中的若干列,1.查詢指定列 例1 查詢全體學生的學號與姓名。 SELECT Sno，Sname FROM Student； 例2 查詢全體學生的姓名、學號、所在系。 SELECT Sname，Sno，Sdept FROM Student；,例3 查詢全體學生的詳細記錄。 SELECT Sno，Sname，Ssex，SBirthday，Sdept FROM Student； 或 SELECT * FROM Student；,2.查詢全部列,SELECT子句的為表達式 算術表達式 字符串常量 函數(shù) 列別名

14、 等,3. 查詢經(jīng)過計算的值,3. 查詢經(jīng)過計算的值,例4 查全體學生的姓名及其年齡。 SELECT Sname，Ceil(SysDate-Sbirthday)/365) FROM Student； 輸出結果： Sname Ceil(SysDate-Sbirthday)/365) - - 李勇 20 劉晨 21 王名 19 張立 19,3. 查詢經(jīng)過計算的值,例5 查詢全體學生的姓名、出生年份和所有系，要求用小寫字母表示所有系名。 SELECT Sname,age:,Ceil(SysDate-Sbirthday)/365) ,LOWER(Sdept) FROM Student；,例題（續(xù)）,輸

15、出結果： Sname age: Ceil(SysDate-Sbirthday)/365) LOWER(Sdept) - - - - 李勇 age: 20 cs 劉晨 age: 21 is 王名 age: 19 ma 張立 age: 19 is,例6 使用列別名改變查詢結果的列標題,SELECT Sname 姓名,Ceil(SysDate-Sbirthday)/365) 年齡,LOWER(Sdept) 所在系 FROM Student； 輸出結果： 姓名 年齡 所在系 - - - 李勇 20 cs 劉晨 21 is 王名 19 ma 張立 19 is,二、選擇表中的若干元組,消除取值重復的行 查

16、詢滿足條件的元組,1. 消除取值重復的行,在SELECT子句中使用DISTINCT短語 假設SC表中有下列數(shù)據(jù) Sno Cno Grade - - - 95001 1 92 95001 2 85 95001 3 88 95002 2 90 95002 3 80,ALL 與 DISTINCT,例6 查詢選修了課程的學生學號。 (1) SELECT Sno FROM SC; 或(默認 ALL) SELECT ALL Sno FROM SC; 結果： Sno - 95001 95001 95001 95002 95002,例題（續(xù)）,(2) SELECT DISTINCT Sno FROM SC;

17、結果： Sno - 95001 95002,例題（續(xù)）,注意 DISTINCT短語的作用范圍是所有目標列 例：查詢選修課程的各種成績 錯誤的寫法 SELECT DISTINCT Cno，DISTINCT Grade FROM SC; 正確的寫法 SELECT DISTINCT Cno，Grade FROM SC;,2.查詢滿足條件的元組,WHERE子句常用的查詢條件,(1) 比較大小,在WHERE子句的中使用比較運算符 =，=，!，!= 20；,(2) 確定范圍,使用謂詞 BETWEEN AND NOT BETWEEN AND 例10 查詢年齡在2023歲（包括20歲和23歲）之間的學生的姓名

18、、系別和年齡。 SELECT Sname，Sdept，Sage FROM Student WHERE Sage BETWEEN 20 AND 23；,例題（續(xù)）,例11 查詢年齡不在2023歲之間的學生姓名、系別和年齡。 SELECT Sname，Sdept，Sage FROM Student WHERE Sage NOT BETWEEN 20 AND 23；,(3) 確定集合,使用謂詞 IN , NOT IN ：用逗號分隔的一組取值 例12查詢信息系（IS）、數(shù)學系（MA）和計 算機科學系（CS）學生的姓名和性別。 SELECT Sname，Ssex FROM Student WHERE S

19、dept IN ( IS，MA，CS );,(3) 確定集合,例13查詢既不是信息系、數(shù)學系，也不是計算 機科學系的學生的姓名和性別。 SELECT Sname，Ssex FROM Student WHERE Sdept NOT IN ( IS，MA，CS );,(4) 字符串匹配,NOT LIKE ESCAPE ：指定匹配模板 匹配模板：固定字符串或含通配符的字符串 當匹配模板為固定字符串時， 可以用 = 運算符取代 LIKE 謂詞 用 != 或 運算符取代 NOT LIKE 謂詞,通配符,% (百分號) 代表任意長度（長度可以為0）的字符串 例：a%b表示以a開頭，以b結尾的任意長度的字符

20、串。如acb，addgb，ab 等都滿足該匹配串 _ (下橫線) 代表任意單個字符 例：a_b表示以a開頭，以b結尾的長度為3的任意字符串。如acb，afb等都滿足該匹配串,ESCAPE 短語：,當用戶要查詢的字符串本身就含有 % 或 _ 時，要使用ESCAPE 短語對通配符進行轉義。,例題,1) 匹配模板為固定字符串 例14 查詢學號為95001的學生的詳細情況。 SELECT * FROM Student WHERE Sno LIKE 95001； 等價于： SELECT * FROM Student WHERE Sno = 95001；,例題（續(xù)）,2) 匹配模板為含通配符的字符串 例1

21、5 查詢所有姓劉學生的姓名、學號和性別。 SELECT Sname，Sno，Ssex FROM Student WHERE Sname LIKE 劉%；,例題（續(xù)）,匹配模板為含通配符的字符串（續(xù)） 例16 查詢姓歐陽且全名為三個漢字的學生的姓名。 SELECT Sname FROM Student WHERE Sname LIKE 歐陽_；,例題（續(xù)）,匹配模板為含通配符的字符串（續(xù)） 例17 查詢名字中第2個字為陽字的學生的姓名和學號。 SELECT Sname，Sno FROM Student WHERE Sname LIKE _陽%；,例題（續(xù)）,匹配模板為含通配符的字符串（續(xù)） 例1

22、8 查詢所有不姓劉的學生姓名。 SELECT Sname FROM Student WHERE Sname NOT LIKE 劉%；,例題（續(xù)）,使用換碼字符將通配符轉義為普通字符(續(xù)) 例20 查詢以DB_開頭，且倒數(shù)第3個字符為 i的課程的詳細情況。 SELECT * FROM Course WHERE Cname LIKE DB_%i_ _ ESCAPE ；,(5) 涉及空值的查詢,使用謂詞 IS NULL 或 IS NOT NULL “IS NULL” 不能用 “= NULL” 代替 例21 某些學生選修課程后沒有參加考試，所以有選課記錄，但沒有考試成績。查詢缺少成績的學生的學號和相應

23、的課程號。 SELECT Sno，Cno FROM SC WHERE Grade IS NULL；,例題(續(xù)),例22 查所有有成績的學生學號和課程號。 SELECT Sno，Cno FROM SC WHERE Grade IS NOT NULL；,(6) 多重條件查詢,用邏輯運算符AND和 OR來聯(lián)結多個查詢條件 AND的優(yōu)先級高于OR 可以用括號改變優(yōu)先級 可用來實現(xiàn)多種其他謂詞 NOT IN NOT BETWEEN AND ,例題,例23 查詢計算機系年齡在20歲以下的學生姓名。 SELECT Sname FROM Student WHERE Sdept= CS AND Sage20；,

24、改寫例12,例12 查詢信息系（IS）、數(shù)學系（MA）和計算機科學系（CS）學生的姓名和性別。 SELECT Sname，Ssex FROM Student WHERE Sdept IN ( IS，MA，CS ) 可改寫為： SELECT Sname，Ssex FROM Student WHERE Sdept= IS OR Sdept= MA OR Sdept= CS ；,改寫例10,例10 查詢年齡在2023歲（包括20歲和23歲）之間的學生的姓名、系別和年齡。 SELECT Sname，Sdept，Sage FROM Student WHERE Sage BETWEEN 20 AND 23

25、； 可改寫為： SELECT Sname，Sdept，Sage FROM Student WHERE Sage=20 AND Sage=23；,三、order by 子句,使用ORDER BY子句 可以按一個或多個屬性列排序 升序：ASC；降序：DESC；缺省值為升序 當排序列含空值時 ASC：排序列為空值的元組最后顯示 DESC：排序列為空值的元組最先顯示,Order by子句（續(xù)）,例24 查詢選修了3號課程的學生的學號及其成績，查詢結果按分數(shù)降序排列。 SELECT Sno，Grade FROM SC WHERE Cno= 3 ORDER BY Grade DESC；,查詢結果,Sno

26、Grade - - 95010 95024 95007 92 95003 82 95010 82 95009 75 95014 61 95002 55,Order by 子句（續(xù)）,例25 查詢全體學生情況，查詢結果按所在系的系號升序排列，同一系中的學生按年齡降序排列。 SELECT * FROM Student ORDER BY Sdept，Sage DESC；,四、聚集函數(shù),5類主要集函數(shù) 計數(shù) COUNT（DISTINCT|ALL *） COUNT（DISTINCT|ALL ） 計算總和 SUM（DISTINCT|ALL ） 計算平均值 AVG（DISTINCT|ALL ）,聚集函數(shù) （續(xù)）,求最大值 MAX（DISTINCT|ALL ） 求最小值 MIN（DISTINCT|ALL ） DISTINCT短語：在計算時要取消指定列中的重復值 ALL短語：不取消重復值 ALL為缺省值,聚集函數(shù) （續(xù)）,例26 查詢學生總人數(shù)。 S