sql基礎(chǔ)學習筆記

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

19、8. 執(zhí)行存儲和物理設(shè)計，均衡設(shè)計問題以完成性能優(yōu)化; 開發(fā)人員開發(fā)人員大部分時間是在用sql實現(xiàn)業(yè)務(wù)邏輯15創(chuàng)建表create table 表名name varchar2(20),age number(lo)16非空約束字段數(shù)據(jù)類型notnull17定義默認值default'默認值,fismastervarchar2 default'no1fismaster字段的默認值為no18定義外鍵foreign key外鍵字段references h標表名(被關(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要刪除的農(nóng)名注總：如果在表之間創(chuàng)建了外鍵關(guān)聯(lián)關(guān)系，那么在刪除被引用數(shù)據(jù)表的時候會刪除火敗，因為這樣會導(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*) 字符串類型的需要用單引號引

21、起來；整數(shù)類型的不盂要用單引號引起來。22數(shù)據(jù)的更新(update set)update t_pers onset fremark = 'sonic',fage=25where fname ='jim' or fname=*lxf'23 delete初學者往往容易把drop table語句和delete混淆,雖然二者名字中都有“刪除” 兩個字，不過delete語句僅僅是刪除表中的數(shù)據(jù)行，而表的結(jié)構(gòu)還存在，而drop table 語句則不僅將表屮的數(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檢索出需要的列，多個列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過濾條件（where）select * from t_employeewhere fsa

23、lary<5000 or fage>2527 聚合函數(shù)（max/min/avg/sum/count）max：m大值min：最小值avg：平均值sum：合計值count：統(tǒng)計數(shù)據(jù)條數(shù)count（*）、count（字段名）都能統(tǒng)計出記錄的條數(shù)但是count）*）統(tǒng)計出來的記錄的條數(shù)是總的數(shù)據(jù)的條數(shù)（數(shù)據(jù)中可能冇空值的情況）count（字段名）統(tǒng)計出來的記錄的條數(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高級數(shù)據(jù)過濾（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空值檢測(is null)查詢?yōu)榭誷elect * from t_employeewhere fname is null查詢不為空select * from t_employeewhere fname is not null31反義運算符(!/not)“二”、“v”、“”等運算符都是用來進行數(shù)值判斷的，有的時候則會想使用這些運算符 的反義,比如“不等于”、“不小于”或者“不大于”,mssqlserver. db2提供了 “!”運算 符來對運算符求反義，也就是“!二”表示“不等于”、“