sql基礎(chǔ)學(xué)習(xí)筆記

1、1基本概念：數(shù)據(jù)庫(kù)和數(shù)據(jù)庫(kù)管理系統(tǒng)數(shù)據(jù)庫(kù)意思就是數(shù)據(jù)的倉(cāng)庫(kù)要理解的兩個(gè)概念：數(shù)據(jù)庫(kù)和數(shù)據(jù)庫(kù)管理系統(tǒng)數(shù)據(jù)庫(kù)管理系統(tǒng)(database management system, dbms)數(shù)據(jù)庫(kù)管理系統(tǒng)是一種操縱和管理數(shù)據(jù)庫(kù)的系統(tǒng)軟件，是用于建立、使用和維護(hù)數(shù)據(jù)庫(kù)。它對(duì)數(shù)據(jù)庫(kù)進(jìn)行統(tǒng)一的管理和控制，以保證數(shù)據(jù)庫(kù)的安全性和完整性。川戶通過(guò)dbms訪問(wèn)數(shù)據(jù)庫(kù)中的數(shù)據(jù)，數(shù)據(jù)庫(kù)管理員也通過(guò)dbms進(jìn)行數(shù)據(jù)庫(kù)的維護(hù)工作。 它提供多種功能，町使多個(gè)應(yīng)用程序利用戶用不同的方法在同時(shí)或不同時(shí)刻公建立，修改和 詢問(wèn)數(shù)據(jù)庫(kù)。它使用八能方便地定義和操縱數(shù)據(jù)，維護(hù)數(shù)據(jù)的安全性和完整性，以及進(jìn)行多 用戶下的并發(fā)控制和恢復(fù)數(shù)據(jù)庫(kù)。

2、有時(shí)很多人也將dbms簡(jiǎn)稱為“數(shù)據(jù)庫(kù)”，但是一定要區(qū)分“數(shù)據(jù)庫(kù)”的這兩個(gè)不同的意思。2主流數(shù)據(jù)庫(kù)管理系統(tǒng)介紹目前有許多dbms產(chǎn)品如 db2 oracle、microsoft sql server> sybase sqlserverlnformix mysql 等3 microsoft sql server 的由來(lái)？microsoft sql server是微軟推出的一款數(shù)據(jù)庫(kù)產(chǎn)甜。細(xì)心的讀者也許己經(jīng)發(fā)現(xiàn)我們前 面提到了另外一個(gè)名字非常相似的sybase sqlserver,這里的名字相似并不是一種巧合，這 還要從microsoft sql server的發(fā)展史談起。微軟當(dāng)初要進(jìn)軍圖形化

3、操作系統(tǒng)，所以就開(kāi)始和ibm “合作”開(kāi)發(fā)os/2,最終當(dāng)然無(wú) 疾而終，但是微軟就很快的推出了口己的新一代視窗操作系統(tǒng)；陽(yáng)當(dāng)微軟發(fā)現(xiàn)數(shù)據(jù)庫(kù)系統(tǒng)這 塊新的帀場(chǎng)的時(shí)候，微軟沒(méi)有自己重頭開(kāi)發(fā)一個(gè)數(shù)據(jù)庫(kù)系統(tǒng)，而是找到了 sybase來(lái)“合作” 開(kāi)發(fā)基于os/2的數(shù)據(jù)產(chǎn)品，當(dāng)然微軟達(dá)到目的以后就立即停止和sybase的合作了，于 1995年推出了白己的microsoft sql server6.0,經(jīng)過(guò)幾年的發(fā)展終于在1998年推出了轟 動(dòng)一時(shí)的microsoft sql server7.0,也正是這一個(gè)版本使得微軟在數(shù)據(jù)庫(kù)產(chǎn)品領(lǐng)域冇了 一席z地。正因?yàn)檫@段"合作”歷史，所以使得microso

4、ft sql server和sybase sqlserver 在很多地方非常類似，比如底層采用的tds協(xié)議、支持的語(yǔ)法擴(kuò)展、函數(shù)等等。微軟在2000年推出了 microsoft sql server 2000,這個(gè)版本繼續(xù)穩(wěn)固了 microsoft sql server的市場(chǎng)地位，山于windows操作系統(tǒng)在個(gè)人計(jì)算機(jī)領(lǐng)域的普及，microsoft sql server理所當(dāng)然的成為了很多數(shù)據(jù)庫(kù)開(kāi)發(fā)人員的接觸的笫一個(gè)而r有可能也是唯一一個(gè)數(shù) 據(jù)庫(kù)產(chǎn)品，很多人英至在“sql server"和“數(shù)據(jù)庫(kù)”之間劃上了等號(hào)，而且用“sql” 一 次來(lái)專指microsoft sql server

5、,可見(jiàn)微軟的市場(chǎng)普及做的還是非常好的。做足夠的市 場(chǎng)以后，微軟在2005年“審時(shí)度勢(shì)”的推出了 microsoft sql server 2005,并將于 2008 年發(fā)布新一代的 microsoft sql server2008omicrosoft sql server的可用性做的非常好，提供了很多了外圍工具來(lái)幫助用戶對(duì)數(shù)據(jù) 庫(kù)進(jìn)行管理，用戶甚至無(wú)需直接執(zhí)行任何sql語(yǔ)句就可以完成數(shù)據(jù)庫(kù)的創(chuàng)建、數(shù)據(jù)表的創(chuàng) 建、數(shù)據(jù)的備份/恢復(fù)等工作；microsoft sql server的開(kāi)發(fā)者社區(qū)也是非常龐大的，因 此有眾多可以參考的學(xué)習(xí)資料，學(xué)習(xí)成本非常低，這是其他數(shù)據(jù)庫(kù)產(chǎn)品做不具有的優(yōu)勢(shì)；同 時(shí)從m

6、icrosoft sql server 2005開(kāi)始開(kāi)發(fā)人員可以使用任何支持.net的語(yǔ)言來(lái)編寫存 儲(chǔ)過(guò)程，這進(jìn)一步降低了 microsoft sql server的使用門檻。不過(guò)正如微軟產(chǎn)品的一貫風(fēng)格，microsoft sql server的劣勢(shì)也是非常明顯的：只能運(yùn) 行于windows操作系統(tǒng)，因此我們無(wú)法在linux、unix ±運(yùn)行它；不悖微軟給出什么樣的 測(cè)試數(shù)據(jù)，在實(shí)際使用中microsoft sql server在人數(shù)據(jù)量和大交易量的壞境中的表現(xiàn) 都是不盡人意的，當(dāng)金業(yè)的業(yè)務(wù)量到達(dá)一個(gè)水平后就要考慮升級(jí)到oracle或者db2 了。4數(shù)據(jù)庫(kù)作用?數(shù)據(jù)庫(kù)能夠幫助你儲(chǔ)存、

7、組織和檢索數(shù)據(jù)。5 catalog數(shù)據(jù)庫(kù)就是數(shù)據(jù)的倉(cāng)庫(kù)，而dbms是數(shù)據(jù)庫(kù)的“管理員”一個(gè)dbms可以管理多個(gè)數(shù)據(jù)庫(kù)，我們將人力資源數(shù)據(jù)保存在hr數(shù)據(jù)庫(kù)屮，而將核心業(yè) 務(wù)數(shù)據(jù)保存在biz數(shù)據(jù)庫(kù)中，我們將這些不同數(shù)據(jù)庫(kù)叫做catalog （在冇的dbms中也稱 為database,即數(shù)據(jù)庫(kù)）。6 catalog的好處?1便于對(duì)各個(gè)catalog進(jìn)行個(gè)性化管理。dbms允許我們指定將不同的catalog保存在 不同的磁盤上，我們還可以對(duì)每個(gè)catalog所能占據(jù)的最大磁盤空問(wèn)、日志大小甚至先級(jí)進(jìn) 行指定，這樣就可以針對(duì)不同的業(yè)務(wù)數(shù)據(jù)進(jìn)行個(gè)性化定制了。2.避免了命名沖突。同一個(gè)catalog中的表名

8、是不許重復(fù)的，而不同catalog中的表名 則是ij以重復(fù)的，這樣hr中nj以有persons表，而biz中也町以有persons表，一者 結(jié)構(gòu)可以完全不相同，保存的數(shù)據(jù)也不會(huì)互相干擾。3安全性更高。dbms許為不同的catalog指定不同的用戶，并且可以限定用戶能訪問(wèn) 的catalogo比如用戶hrl23只能訪問(wèn)hr,而用戶salesool只能訪問(wèn)bi乙這就大大加 強(qiáng)了系統(tǒng)數(shù)據(jù)的安全性。7 表（t able）將數(shù)據(jù)庫(kù)的數(shù)據(jù)分成多個(gè)表7.1表關(guān)聯(lián)這種將兩張農(nóng)通過(guò)字段關(guān)聯(lián)起來(lái)的方式就被稱為“表關(guān)聯(lián)”，關(guān)聯(lián)到其他表主鍵的字段 被稱為“外鍵”，上邊例子中貨物表中的“牛產(chǎn)廠家編號(hào)”字段就是外鍵。表關(guān)聯(lián)

9、也是關(guān)系 數(shù)據(jù)庫(kù)的核心理念，它使得數(shù)據(jù)庫(kù)屮的數(shù)據(jù)不再互相孤立，通過(guò)表關(guān)聯(lián)我們可以表達(dá)非常復(fù) 雜的數(shù)據(jù)關(guān)系。8 列（column）這里的“姓名”、“部門”和“入職時(shí)間”就被稱為員工表的列(column),有時(shí)也叫 做字段(field),每個(gè)列描述了數(shù)據(jù)的一個(gè)特性。9數(shù)據(jù)類型(datatype)除了可以對(duì)數(shù)據(jù)進(jìn)行規(guī)范z外，數(shù)據(jù)類型還冇下面的作用：1提高效率。對(duì)不同的數(shù)據(jù)賦了不同的類型能夠使得數(shù)據(jù)庫(kù)更好的對(duì)數(shù)據(jù)進(jìn)行存儲(chǔ)和管 理，從而減少空間占用并且提供數(shù)據(jù)的訪問(wèn)速度。比如,如果將數(shù)字123454321以文本 類型存儲(chǔ)的話將會(huì)占川9字節(jié)的存儲(chǔ)空間，而以整數(shù)類型保存的話將只需要占jij 4字節(jié) 的存儲(chǔ)空

10、間。2. 能夠確定對(duì)數(shù)據(jù)進(jìn)行操作所需要的正確處理方式。比如如果是整數(shù)類型，那么123+234 被解釋為兩個(gè)整數(shù)的加法運(yùn)算，所以其結(jié)果是357；如果是文本類型，那么123+234則 會(huì)被解釋為兩個(gè)字符串的相連操作，所以其結(jié)果是123234o9.1整數(shù)類型oracle中沒(méi)有專門的整數(shù)類型，因此需耍使用number(lo)來(lái)表示整形。mssqlserverbit其值只能是0、1或空值。這種數(shù)據(jù)類型用于存儲(chǔ)只有兩種nj能值 的數(shù)據(jù)，如 yes 或 no、true 或 false> on 或 o正常'大小整數(shù)，取值范圍是 -2147483648 到 2147483647。sma

11、lli nt可以存儲(chǔ)從-32768到32767之間的整數(shù)。這種數(shù)據(jù)類型對(duì)存儲(chǔ)一 些常限定在特定范圍內(nèi)的數(shù)值型數(shù)據(jù)非常有用。這種數(shù)據(jù)類型在數(shù) 據(jù)庫(kù)里占用2字節(jié)空間。tinyint能存儲(chǔ)從0到255 z間的整數(shù)。它在你只打算存儲(chǔ)有限數(shù)冃的數(shù) 值吋很有用。這種數(shù)據(jù)類型在數(shù)據(jù)庫(kù)屮占用1個(gè)字節(jié)。bigint可以精確的表示從一2 63到 2 63 -1(即從-9,223,372,036,854,775,808 到9,223,372,036,854,775,807)之間的整數(shù),它占用了八個(gè)字節(jié)的 存儲(chǔ)空間。9.2數(shù)值類型oraclenu mber(m, n)數(shù)值空，m是所有有效數(shù)字的位數(shù)，n是小數(shù)點(diǎn)以后的位

12、數(shù)。如：number(5,2),則這個(gè)字段的最大值是99,999,如果數(shù)值超出了 位數(shù)限制就會(huì)被截取多余的位數(shù)。如：number(5,2),但在一行數(shù)據(jù)中的這個(gè)字段輸入575.316,則真 正保存到字段中的數(shù)值是575.32。如：number(3,0),輸入575.316,真正保存的數(shù)據(jù)是575。mssqlservermoney貨幣型。用來(lái)表示貨幣值。這種數(shù)據(jù)類 型能存儲(chǔ)從-9220億到9220億之間的 數(shù)據(jù)，精確到貨幣單位的萬(wàn)分z-0smallm oney貨幣型。用來(lái)表示貨幣值。這種數(shù)據(jù)類 型能存儲(chǔ)從-214748.3648到 214748.3647之間的數(shù)據(jù),精確到貨幣 單位的萬(wàn)分zflo

13、at近似數(shù)值型。float數(shù)據(jù)類型是一種近 似數(shù)值類空，供浮點(diǎn)數(shù)使用。浮點(diǎn)數(shù)可 以是從-1.79e+308 到 1.79e+308 之間 的任意數(shù)。realreal數(shù)據(jù)類型像浮點(diǎn)數(shù)一樣，它可以表 示數(shù)值在-3.40e+38到3.40e+38之間的 浮點(diǎn)數(shù)9.3字符相關(guān)類型oraclechar(m)固定長(zhǎng)度字符串，長(zhǎng)度為mvarchar2(m)可變長(zhǎng)度字符串，最人長(zhǎng)度為mn varchar2(m)可變長(zhǎng)度國(guó)際化字符串，最大長(zhǎng)度為4gbclob可變長(zhǎng)度大字符串，最大長(zhǎng)度216-1字節(jié)對(duì)變長(zhǎng)度大字符串，最大 長(zhǎng)度nclob可變長(zhǎng)度國(guó)際化大字符串，最大長(zhǎng)度為4gbmssqlserverchar(m)固

14、定長(zhǎng)度字符串，長(zhǎng)度為mvarchar(m)可變長(zhǎng)度字符串,最大長(zhǎng)度為mtext可變長(zhǎng)度字符串，最大長(zhǎng)度231-1字節(jié)n char(m)固定長(zhǎng)度國(guó)際化字符串，長(zhǎng)度為mnvarchar(m)可變長(zhǎng)度國(guó)際化字符串，最大長(zhǎng)度為mntext可變長(zhǎng)度國(guó)際化人字符串，其最犬長(zhǎng)度為230 -1 (1,073,741,823)個(gè)字符。9.4日期時(shí)間類型oracledate日期時(shí)間數(shù)據(jù)timestamp時(shí)間戳mssqlserverdatetime從1753年1刀1 口到9999年12刀31丨|的ii期時(shí)間數(shù)據(jù)，精確到百分之二秒smalldatetime從1900年1月1日到2079年6月6 日的日期和時(shí)間數(shù)據(jù),精

15、確到分鐘timestamp時(shí)間戳9.5二進(jìn)制類型oracle： blobmssqlserver： image雖然類型名為image,但是并不意味著只能保存圖片一進(jìn)制數(shù)據(jù)，實(shí) 際上它可以保存任何二進(jìn)制數(shù)據(jù)。10 記錄（record）記錄有可以被稱為行(row),可以通俗的認(rèn)為它是數(shù)據(jù)表屮的一行數(shù)據(jù)。11 主鍵(primarykey)我們可以說(shuō)能唯一標(biāo)識(shí)一行記錄的字段就是此表的主鍵11.1定義主鍵在所冇字段定義完后，primary key (字段名稱)主鍵是一個(gè)，聯(lián)合主鍵/復(fù)合主鍵是兩個(gè)字段primary key (fcompanynamelnternalnumber)12 索引(index)無(wú)索

16、引的表就是一個(gè)無(wú)序的行集。給表建立索引，其實(shí)就像給書(shū)建立目錄。12.1優(yōu)點(diǎn):可以快速的查詢到我們需要的數(shù)據(jù)22缺點(diǎn):索引占據(jù)一定磁盤空間，就像有按筆畫查找的目錄的書(shū)會(huì)比沒(méi)有這種口錄的書(shū)頁(yè)數(shù)要多一 些。索引減慢了數(shù)據(jù)插入和刪除的速度。因?yàn)槊看尾迦牒蛣h除的時(shí)候都需要更新索引，一個(gè) 表?yè)碛械乃饕蕉?，則寫操作的平均性能下降就越大。13數(shù)據(jù)庫(kù)的語(yǔ)言sqlsql是專為數(shù)據(jù)庫(kù)而建立的操作命令集，是一種功能齊全的數(shù)據(jù)庫(kù)語(yǔ)言。在使用它時(shí)，只需要發(fā)出“做什么”的命令，“怎么做”是不用使用者考慮的。sql 的英文全稱是 structured query languagesql是高級(jí)的非過(guò)程化編程語(yǔ)言，允許用戶在髙

17、層數(shù)據(jù)結(jié)構(gòu)上工作。使用它，用戶無(wú) 需指定對(duì)數(shù)據(jù)的存放方法，也不盂要用戶了解具體的數(shù)據(jù)存放方式，所以具冇完全不同底層 結(jié)構(gòu)的不同數(shù)據(jù)庫(kù)系統(tǒng)可以使用相同的sql語(yǔ)言作為數(shù)據(jù)輸入與管理的接口。它以記錄集 合作為操縱對(duì)象，所有sql語(yǔ)句接受集合作為輸入，返回集合作為輸出，這種集合特性允 許一條sql語(yǔ)句的輸出作為另一條sql語(yǔ)句的輸入，所以sql語(yǔ)言可以恢套，這使它具 有極人的靈活性和強(qiáng)人的功能，在多數(shù)悄況下，在其他語(yǔ)言屮需要一人段程序?qū)崿F(xiàn)的一個(gè)單 獨(dú)事件只需要一個(gè)sql語(yǔ)旬就可以達(dá)到目的，這也意味著用sql語(yǔ)言可以寫出非常復(fù)雜 的語(yǔ)句。sql具有下而4個(gè)主耍的功能：創(chuàng)建數(shù)據(jù)庫(kù)并定義表的結(jié)構(gòu)；查詢需要

18、的數(shù)據(jù)；更新 或者刪除指定的數(shù)據(jù)；控制數(shù)據(jù)庫(kù)的安全。使用sql我們可以完成和dbms的幾乎所有交 互任務(wù)。比如我們要查找年齡小于18歲的員工信息，那么我們只要執(zhí)行下面的sql就可以： select * from employees where age<1814 dba與程序員數(shù)據(jù)庫(kù)系統(tǒng)管理員（dba）1. 安裝和配置數(shù)據(jù)庫(kù)，創(chuàng)建數(shù)據(jù)庫(kù)以及帳戶；2. 監(jiān)視數(shù)據(jù)庫(kù)系統(tǒng)，保證數(shù)據(jù)庫(kù)不宕機(jī)；3. 收集系統(tǒng)統(tǒng)計(jì)和性能信息以便進(jìn)行調(diào)整；4. 發(fā)現(xiàn)性能糟糕的sql,并給開(kāi)發(fā)人員提出調(diào)優(yōu)建議；5. 管理數(shù)據(jù)庫(kù)安全性；6. 備份數(shù)據(jù)庫(kù)，當(dāng)發(fā)牛故障時(shí)要及時(shí)恢復(fù)；7. 升級(jí)dbms并且在必要時(shí)為系統(tǒng)安裝補(bǔ)?。?/p>

19、8. 執(zhí)行存儲(chǔ)和物理設(shè)計(jì)，均衡設(shè)計(jì)問(wèn)題以完成性能優(yōu)化; 開(kāi)發(fā)人員開(kāi)發(fā)人員大部分時(shí)間是在用sql實(shí)現(xiàn)業(yè)務(wù)邏輯15創(chuàng)建表create table 表名name varchar2(20),age number(lo)16非空約束字段數(shù)據(jù)類型notnull17定義默認(rèn)值default'默認(rèn)值,fismastervarchar2 default'no1fismaster字段的默認(rèn)值為no18定義外鍵foreign key外鍵字段references h標(biāo)表名(被關(guān)聯(lián)的字段名稱)foreign key(fdepartmentld) references t_department(fld)1

20、9添加、刪除字段添加：alter table待修改的農(nóng)名add字段名字段類型刪除：alter table待修改的表名drop待刪除的字段名20刪除表drop table要?jiǎng)h除的農(nóng)名注總：如果在表之間創(chuàng)建了外鍵關(guān)聯(lián)關(guān)系，那么在刪除被引用數(shù)據(jù)表的時(shí)候會(huì)刪除火敗，因?yàn)檫@樣會(huì)導(dǎo)致關(guān)聯(lián)關(guān)系被破壞，所以必須首先刪除引川表，然后才能刪除被引 用表。比如a表創(chuàng)建了指向b表的外鍵關(guān)聯(lián)關(guān)系，那么必須首先刪除a表后才能刪除b 表。21 數(shù)據(jù)的插a(insert into)insert into t_person(fname,fage,fremark) valuesctomls/usa*) 字符串類型的需要用單引號(hào)引

21、起來(lái)；整數(shù)類型的不盂要用單引號(hào)引起來(lái)。22數(shù)據(jù)的更新(update set)update t_pers onset fremark = 'sonic',fage=25where fname ='jim' or fname=*lxf'23 delete初學(xué)者往往容易把drop table語(yǔ)句和delete混淆,雖然二者名字中都有“刪除” 兩個(gè)字，不過(guò)delete語(yǔ)句僅僅是刪除表中的數(shù)據(jù)行，而表的結(jié)構(gòu)還存在，而drop table 語(yǔ)句則不僅將表屮的數(shù)據(jù)行全部刪除，而且還將表的結(jié)構(gòu)也刪除。delete from t_person where fage>

22、 20 or fremark = 'mars'24數(shù)據(jù)的檢索(select)取出一張表屮所有的數(shù)據(jù)select * from t_employee檢索出需要的列，多個(gè)列select fnumber from t_employeeselect fname,fage from t_employee25列別名(as)列名as別名select fnumber as numberl,fname as name,fage as age,fsalary as salary fromt_employee26過(guò)濾條件（where）select * from t_employeewhere fsa

23、lary<5000 or fage>2527 聚合函數(shù)（max/min/avg/sum/count）max：m大值min：最小值avg：平均值sum：合計(jì)值count：統(tǒng)計(jì)數(shù)據(jù)條數(shù)count（*）、count（字段名）都能統(tǒng)計(jì)出記錄的條數(shù)但是count）*）統(tǒng)計(jì)出來(lái)的記錄的條數(shù)是總的數(shù)據(jù)的條數(shù)（數(shù)據(jù)中可能冇空值的情況）count（字段名）統(tǒng)計(jì)出來(lái)的記錄的條數(shù)是總的條數(shù)，但是字段值不含空值的條數(shù)28 排序（order by）order by字段名升序/降序升序：asc降序：desc按年齡升序select * from t_employeeorder by fageasc按年齡、工資降

24、序select * from t_employeeorder by fagedesc,fsalary desc29高級(jí)數(shù)據(jù)過(guò)濾（like/%/jlike和單字符匹配（半角下劃線“_）select * from t_employeewhere fname like '_erry多字符匹配（）select * from t_employeewhere fname like t%1單字符和多字符町以一起使用select * from t_employee where fname like *%nj30空值檢測(cè)(is null)查詢?yōu)榭誷elect * from t_employeewhere fname is null查詢不為空select * from t_employeewhere fname is not null31反義運(yùn)算符(!/not)“二”、“v”、“”等運(yùn)算符都是用來(lái)進(jìn)行數(shù)值判斷的，有的時(shí)候則會(huì)想使用這些運(yùn)算符 的反義,比如“不等于”、“不小于”或者“不大于”,mssqlserver. db2提供了 “!”運(yùn)算 符來(lái)對(duì)運(yùn)算符求反義，也就是“!二”表示“不等于”、“