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

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

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

3、al、int 等），用戶定義的數(shù)據(jù)類(lèi)型即基于該類(lèi)型。system_data_type 是 sysname，無(wú)默認(rèn)值。 nulltype = null_type 指定必須如何處理 null 值。null_type 是 varchar(8)，設(shè)置值為 NULL（默認(rèn)）、NOT NULL 或 NONULL,Access數(shù)據(jù)庫(kù)常用數(shù)據(jù)類(lèi)型,Access 2000中字段可用的數(shù)據(jù)類(lèi)型有以下幾種： 文本(Text)：文本、文本與數(shù)字、不必計(jì)算的數(shù)字，最長(zhǎng)255個(gè)字符。 備注(Memo)：較長(zhǎng)的文本，最長(zhǎng)64,000字符。 數(shù)字(Number)型：用于算術(shù)運(yùn)算的數(shù)字。可在“字段大小”中分字節(jié)、整型、單精度等

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

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

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

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

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

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

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

11、主（即建立表的人）根據(jù)需要建立 有些DBMS自動(dòng)建立以下的索引 PRIMARY KEY UNIQUE,一、建立索引,語(yǔ)句格式 CREATE UNIQUE INDEX ON (, )； 用指定要建索引的基本表名字 索引可以建立在該表的一列或多列上，各列名之間用逗號(hào)分隔 用指定索引值的排列次序，升序：ASC，降序：DESC。缺省值：ASC UNIQUE表明此索引的每一個(gè)索引值只對(duì)應(yīng)唯一的數(shù)據(jù)記錄,例題,例14 為學(xué)生-課程數(shù)據(jù)庫(kù)中的Student，Course兩個(gè)表建立索引。其中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 ； 刪除索引時(shí)，系統(tǒng)會(huì)從數(shù)據(jù)字典中刪去有關(guān)該索引的描述。 例15 刪除Student表的stu_sname_index索引。 DROP INDEX stu_sname_index； 注意：主鍵索引不能被刪除；刪除主鍵約束時(shí)，主鍵索引會(huì)被自動(dòng)刪除。,3.4.1 單表查詢,查詢僅涉及一個(gè)表，是一種最簡(jiǎn)單的查詢操作 一、選擇表中的若干列 二、選擇表中的若干元組 三、order by 子句

13、四、聚集函數(shù)（aggregate functions） 五、group by 子句,一、選擇表中的若干列,1.查詢指定列 例1 查詢?nèi)w學(xué)生的學(xué)號(hào)與姓名。 SELECT Sno，Sname FROM Student； 例2 查詢?nèi)w學(xué)生的姓名、學(xué)號(hào)、所在系。 SELECT Sname，Sno，Sdept FROM Student；,例3 查詢?nèi)w學(xué)生的詳細(xì)記錄。 SELECT Sno，Sname，Ssex，SBirthday，Sdept FROM Student； 或 SELECT * FROM Student；,2.查詢?nèi)苛?SELECT子句的為表達(dá)式 算術(shù)表達(dá)式 字符串常量 函數(shù) 列別名

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

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

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

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

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

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

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

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

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

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

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

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

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