知識點、SQL語句學習及詳細總結

1、知識點、sql 語句學習及詳細總 結一. 數(shù)據(jù)庫簡介和創(chuàng)建1. 系統(tǒng)數(shù)據(jù)庫在安裝好 sql server 后，系統(tǒng)會自動安裝 5 個用于維護系統(tǒng)正常運行的系 統(tǒng)數(shù)據(jù)庫：（1） master：記錄了 sql server 實例的所有系統(tǒng)級消息，包括實例范圍的 元數(shù)據(jù)（如登錄帳號）、端點、鏈接服務器和系統(tǒng)配置設置。（2） msdb：供 sql server 代理服務調度報警和作業(yè)以及記錄操作員的使 用，保存關于調度報警、作業(yè)、操作員等信息。（備份還原時）（3） model：sql server 實例上創(chuàng)建的所有數(shù)據(jù)庫的模板。（4） tempdb：臨時數(shù)據(jù)庫，用于保存臨時對象或中間結果集，為數(shù)據(jù)庫的

2、排 列等操作提供一個臨時工作空間。（每次啟動都會重新創(chuàng)建）（5） resource：一個只讀數(shù)據(jù)庫，包含了 sql server 的所有系統(tǒng)對象。 （隱藏的數(shù)據(jù)庫）2. 數(shù)據(jù)庫的組成2.1 數(shù)據(jù)文件（1）主要數(shù)據(jù)文件：擴展名為 .mdf ，每個數(shù)據(jù)庫有且只能有一個。 （2）次要數(shù)據(jù)文件：擴展名為 .ndf ， 可以沒有或有多個。2.2 日志文件擴展名為 .ldf ，用于存放恢復數(shù)據(jù)庫的所有日志信息。2.3 數(shù)據(jù)的存儲分配（ 1 ） 數(shù) 據(jù) 文 件 和 日 志 文 件 的 默 認 存 放 位 置 為 ： filesmicrosoftprogramesqlservermssql.1mssqldat

3、a文件夾。（2）數(shù)據(jù)的存儲分配單位是數(shù)據(jù)頁。一頁表是一塊 8kb 的連續(xù)磁盤空間。 （3）頁是存儲數(shù)據(jù)的最小空間分配單位，頁的大小決定了數(shù)據(jù)庫表中一行數(shù)據(jù) 的最大大小。3. sql 語句 數(shù)據(jù)庫操作（1）創(chuàng)建數(shù)據(jù)庫create database database_name二. sql 基礎sql（structured query language ，結構化查詢語言）是用戶操作關系數(shù)據(jù) 庫的通用語言。1. sql 功能概述2. 系統(tǒng)提供的數(shù)據(jù)類型 2.1 數(shù)值數(shù)據(jù)類型數(shù)據(jù)類型bittinyintsmallint說明bit 數(shù)據(jù)類型是整型，其值只能是 0、 1 或空值。這種數(shù)據(jù)類型用于存儲只 有兩

4、種可能值的數(shù)據(jù)，如 yes 或 no、true 或 false 、on 或 off. （很省空間的一種數(shù)據(jù)類型， 如果能夠滿足需求應該盡量多用。）tinyint 數(shù)據(jù)類型能存儲從 0 到 255 之間的整數(shù)。它在你只打算存儲有 限數(shù)目的數(shù)值時很有用。smallint 數(shù)據(jù)類型可以存儲從 - 2 的 15 次冪(-32768)到 2 的 15 次冪存儲空間1 字節(jié)1 字節(jié)2 字數(shù)據(jù)類型說明存儲空間(32767)之間的整數(shù)。這種數(shù)據(jù)類型 節(jié) 對存儲一些常限定在特定范圍內的 數(shù)值型數(shù)據(jù)非常有用。（如果 tinyint 類型太單調不能滿足您的需求，您 可以考慮用 smallint 類型，因為這 個類型

5、相對也是比較安全的，不接 受惡意腳本內容的嵌入。）int 數(shù)據(jù)類型可以存儲從 - 2 的 31次冪(-2147483648)到 2 的 31 次冪 4 個int (2147483 647)之間的整數(shù)。存儲到 字數(shù)據(jù)庫的幾乎所有數(shù)值型的數(shù)據(jù)都 節(jié) 可以用這種數(shù)據(jù)類型numeric（p,s） 或數(shù)據(jù)類型能用來存儲從 -10 的 38 次 最 冪-1 到 10 的 38 次冪-1 的固定精度 多數(shù)據(jù)類型說明存儲空間decimal(p,s) 和范圍的數(shù)值型數(shù)據(jù)。使用這種數(shù) 17據(jù)類型時，必須指定范圍和精度。 個 范圍是小數(shù)點左右所能存儲的數(shù)字 字 的總位數(shù)。精度是小數(shù)點右邊存儲 節(jié) 的數(shù)字的位數(shù)2.2

6、 普通編碼字符串類型數(shù)據(jù)類型說明存儲char 數(shù)據(jù)類型用來存儲指定長度的定長char(n)非統(tǒng)一編碼型的數(shù)據(jù) ,n 表示字符串的最 n 字 大長度，取值范圍為 18000 （若實際字 _符串控件小于 n,系統(tǒng)自動在后面補空格）數(shù)據(jù)類型說明存儲varchar(n)可變長度的字符串類型 ,n 表示字符串的 最大長度，取值范圍為 18000。字符字節(jié)銷texttext 數(shù)據(jù)類型用來存儲大量的非統(tǒng)一編 碼型字符數(shù)據(jù)。這種數(shù)據(jù)類型最多可以有 231-1 或 20 億個字符.每個個字char 和 varchar 的區(qū)別：若某列數(shù)據(jù)類型為 varchar(20)，存字符串”jone”時，只占用 4 個字節(jié)，

7、而 char （20）會在為填滿的空間中填寫空格。所以，varchar 類型比 char 類型更節(jié)省 空間，但它的開銷會大一些，處理速度也慢一些。因此，n 值比較?。ㄐ∮?4）， 用 char 類型更好些。2.3 統(tǒng)一編碼字符串類型（unicode）數(shù)據(jù)類型說明存nchar(n)nchar 數(shù)據(jù)類型用來存儲定長統(tǒng)一編碼 字符型數(shù)據(jù)。統(tǒng)一編碼用雙字節(jié)結構來2n_存儲每個字符，而不是用單字節(jié) (普通文數(shù)據(jù)類型說明存本中的情況)。它允許大量的擴展字符。 此數(shù)據(jù)類型能存儲 4000 種字符，使用的 字節(jié)空間上增加了一倍 .nvarchar 數(shù)據(jù)類型用作變長的統(tǒng)一編字nvarchar(n) 碼字符型數(shù)據(jù)

8、。此數(shù)據(jù)類型能存儲 4000 字種字符，使用的字節(jié)空間增加了一倍 .銷ntext最多可存儲 2 的 30 次方-1 將近 10 億個 每 字符 個三. sql 數(shù)據(jù)操作語言1.數(shù)據(jù)查詢語句1.1 查詢語句的基本結構select -需要哪些列from -來自哪張表 where group by having order by select 子句用于指定輸出的字段；from 子句用于指定數(shù)據(jù)的來源；where 子句用于指定數(shù)據(jù)的選擇條件；group by 子句用于對檢索到的記錄進行分組；having 子句用于指定組的選擇條件；order by 子句用于對查詢的結果進行排序；以上子句中， selec

9、t 子句和 from 子句是必需的，其它是可選的。1.2 單表查詢1.2.1 選擇表中若干列（1）查詢指定的列select 列名 from 表名1例子 ：select sname,sno from student（2）查詢全部列select * from 表名1例子 ：select * from student（3）查詢經過計算的列select 列名 from 表名1例 子 ：select sname,year(getdata() -year(birthdate) from student 1.2.2 選擇表中的若干元祖（1）消除取值相同的行： distinctselect distinct

10、sno from 表名1例子 ：select distinct sno from student（2）查詢滿足條件的元祖 查詢條件謂 詞比較=、=、=、!=、!、!確定范圍betweenand、 not查詢條件謂 詞確定集合字符匹配空值betweenandin 、not inlike 、not likeis null、is not null多重條件（邏輯謂詞） and、or a.比較大小例 子 ：select sname from student whereyear(getdata() - year(birthdate) = 80 and grade = 90例子 ：select sno,cn

11、o from sc where gradenot between 80 and 90此 查 詢 等 價 于 ：select sno,cno from sc wheregrade 90c. 確定集合in 運算符的含義：當列中的值和集合中的某個常量值相等時，結果為 true。 not in 運算符的含義：當列中的值和集合中的全部常量值都不相等時，結果為 true。例子 ：select sno from student where deptin (信息管理系,計算機系)此查詢等價于：select sno from student wheredept = 信息管理系 or dept = 計算機系 )例

12、子 ：select sno from student where deptnot in (信息管理系,計算機系)此查詢等價于：select sno from student wheredept != 信息管理系 and dept != 計算機 系)d. 字符串匹配like 運算符用于查找指定列中與匹配串匹配的元祖。 列名 not like 通配符_（下劃線）%（百分號）含義匹配任意一個字符匹配 0 個或多個字符匹配中的任意一個字符。如 abcd表示匹配 ab 任何一個，若是連續(xù)的，可以用 - 表示，如a-d不匹配中的任意一個字符。如 abcd表示不匹通配符含義其中任何一個，若是連續(xù)的，可以用

13、- 表示，如例子 ：(查詢姓“張”的學生詳細信息 )select * from student where sname like 張%(查詢不姓“張”的學生詳細信息)select * from student where sname not like 張%（查詢姓“張”、“李”的學生詳細信息）select * from student where sname like 張李%（查詢名字的第二個字為 “小” 或 “大”的學生 詳細信息）select * from student where sname like _小大%e. 涉及空值的查詢空值（null）在數(shù)據(jù)庫中有特殊含義，表示當前不確定或未

14、知的值。判斷是否 為 null 時，不可用普通的比較運算符，需用 is null例子 ：select sno from student where gradeis null1.2.3 對查詢結果進行排序將查詢結果按照指定的順序顯示。 asc 表示按列值升序排列（從上往下，值從 大到?。esc 表示按列值降序排列（從上往下，值從小到大）。默認為 asc。order by asc|desc例子 ：select sno,grade from sc order bygrade desc1.2.4 使用聚合函數(shù)統(tǒng)計數(shù)據(jù)聚合函數(shù)也稱為統(tǒng)計函數(shù)或集合函數(shù)，作用是對一組值進行計算并返回一個統(tǒng) 計結果。聚合

15、函數(shù)含義聚合函數(shù)count(*)count(distinct)含義統(tǒng)計表中元祖的個數(shù)統(tǒng)計本列的非空列值個數(shù)sum()計算列值的和值（必須是數(shù)值avg()計算列值的平均值（必須是數(shù)max()min()計算列值的最大值計算列值的最小值上述函數(shù)除 count(*) 外，其它函數(shù)在計算過程中均忽略 null 值 （統(tǒng)計學生總人數(shù)）select count(*) from student（統(tǒng)計“001”學號學生的考試平均成績）select avg(grade) from sc where sno = 001（查詢“c001”號課程考試成績的最高分和最低 分）select max(grade) 最高分,m

16、in(grade) 最 低分 from sc where cno = c001聚合函數(shù)不能出現(xiàn)在 where 子句中！1.2.5 對數(shù)據(jù)進行分組統(tǒng)計需要先對數(shù)據(jù)進行分組，然后再對每個組進行統(tǒng)計。分組子句 group by。在 一個查詢語句中，可以用多個列進行分組。分組子句跟在 where 子句的后面：group by ,.nhaving （1）使用 group by 子句（統(tǒng)計每門課程的選課人數(shù)，列出課程號和選課 人數(shù)）select cno as 課程號, count(sno) as 選 課人數(shù) from sc group by cno（統(tǒng)計每個學生的選課門數(shù)和平均成績）select sno

17、學號, count(*)選課門數(shù),avg(grade) 平均成績 from sc group by sno帶 where 子句的分組（統(tǒng)計每個系的女生人數(shù)）select dept, count(*) 女生人數(shù) from s tudent where sex = 女 group by dept（2）使用 having 子句having 子句用于對分組后的統(tǒng)計結果再進行篩選，它的功能與 where 子句 類似，它用于組而不是單個記錄。在 having 子句中可以使用聚合函數(shù)，但在 where 子句中不能，通常與 group 子句一起使用。（查詢選課門數(shù)超過 3 門的學生的學號和選課 門數(shù)）sele

18、ct sno 學號, count(*)選課門數(shù),avg(grade) 平均成績 from sc group by sno h aving count(*) 3（3）where 、group by 、having 的作用及執(zhí)行順序where 子句用于篩選 from 子句中指定的數(shù)據(jù)所產生的行數(shù)據(jù)。 group by 子句用于對經 where 子句篩選后的結果數(shù)據(jù)進行分組。 having 子句用于對分組后的統(tǒng)計結果再進行篩選。可以分組操作之前應用的篩選條件，在 where 子句中指定它們更有效，這樣 可以減少參與分組的數(shù)據(jù)行。在 having 子句中指定的篩選條件應該是那些必 須在執(zhí)行分組操作之后

19、應用的篩選條件。（查詢計算機系和信息管理系每個系的學生人 數(shù)）第一種：select dept,count(*) from student grou p by dept having dept in(計算機系,信 息管理系)第二種：select dept,count(*) from student wher e dept in (計算機系,信息管理系)grou p by dept以上例子比較：第一種是按照系分組好了之后，只采取所有系中的兩個系，顯 然效率不高。而第二種是先進行 where 篩選條件之后，再進行 group by 計 算，顯示更好。1.3 多表連接查詢若一個查詢同時涉及到兩張或以上

20、的表，則稱為連接查詢。1.3.1 內連接使用內連接時，如果兩個表的相關字段滿足條件，則從兩個表中提取數(shù)據(jù)組成 新的記錄。from 表 1 inner join 表 2 on 1注意：連接條件中的連接字段必須是可比的，必須是語義相同的列。（查詢學生及選課的詳細信息）select * from student inner join sc on student.sno = sc.sno（查詢計算機系學生的選課情況，列出該學生的 名字、所修課程號、成績） -行選擇條件select sname,cno,grade from student in ner join sc on student.sno =

21、sc.sno wh ere dept = 計算機系（統(tǒng)計每個系的平均成績） -分組的多表 查詢select dept,avg(grade) as averagegrade from student s inner join sc on s.sno = sc.sno group by dept（統(tǒng)計計算機系每個學生的選課門數(shù)、平均成 績、最高成績、最低成績） -分組和行選 擇條件的多表連接查詢select sno,count(*),avg(grade),max(gr ade),min(grade) from student s join sc on s.sno = sc.sno where de

22、pt = 計算 機系 group by dept1.3.2 自連接自連接是一種特殊的內連接，相互連接的表在物理上是一張表，但在邏輯上可 以看做是兩張表。from 表 1 as t1 join 表 1 as t21通過為表取別名的方法，可以讓物理上的一張表在邏輯上成為兩張表。（一定 要為表取別名?。ú樵兣c劉晨在同一個系學習的學生的姓名、所 在系）select s1.sname,s1.dept from student s 1 join student s2on s1.dept = s2.dept -同一個系的學 生where s2.sname = 劉晨 詢條件and s1.sname != 劉

23、晨-s2 表作為查-s1 表作為結果表，并從中去掉 劉晨本人信息1.3.3 外連接在內連接操作中，只有滿足條件的元祖才能出現(xiàn)在查詢結果集中。外連接是只限制一張表中的數(shù)據(jù)必須滿足條件，而另一張表的數(shù)據(jù)可以不滿足 條件。from 表 1 left|right outer join 表 2 o n 1left outer join 稱為左外連接，含義是限制表 2 中的數(shù)據(jù)必須滿足條件， 但不管表 1 中的數(shù)據(jù)是否滿足條件，均輸出表 1 中的數(shù)據(jù)。left outer join 稱為右外連接，含義是限制表 1 中的數(shù)據(jù)必須滿足條件， 但不管表 2 中的數(shù)據(jù)是否滿足條件，均輸出表 2 中的數(shù)據(jù)。內連接與

24、外連接的區(qū)別：內連接：表 a 與表 b 進行內連接，則結果為兩個表中滿足條件的記錄集，即 c 部分。外連接：如果表 a 和表 b 進行左外連接，則結果為 記錄集 a + 記錄集 c；如 果表 a 和表 b 進行右外連接，則結果為 記錄集 b + 記錄集 c。（查詢沒有人選的選修課程名）select cname from course c left join sc on c.cno = sc.cno where sc.cno is n ull例子解析：如果存在部分課程為被人選擇，則必定在 course 表中有但在 sc 表 中沒有出現(xiàn)，即在進行外連接時沒人選的課程在與 sc 表構成的連接結果集中

25、， 對應的 sno、cno、grade 列必定為空，所以只需 *在連接后的結果中選出 *sc 表中 sno 或 cno 為空的元祖即可。（統(tǒng)計計算機系每個學生的選課門數(shù)，包括沒選 課的學生）select s.sno as 學號,count(sc.cno) as 選課門數(shù) from student s left join sc on s.sno = sc.sno where dept = 計算機系 group by s.sno例子解析： 上述例子要求統(tǒng)計每個學生的 .，所以在 group by 分組時，是 按照學生表中的學號來分。而對于聚合函數(shù) count，上述要求統(tǒng)計每個學生的 選課門數(shù)，若寫

26、成 count(s.sno)或 count(*)，則對沒選課的學生都返回1， 因為在外連接結果中， s.sno 不會是 null，而 count(*)函數(shù)本身也不考慮 null，它是直接對元祖?zhèn)€數(shù)進行計數(shù)。注意：在對外連接的結果進行分組、統(tǒng)計等操作時，一定要注意分組依據(jù)列和 統(tǒng)計列的選擇。1.4 使用 top 限制結果集行數(shù)在使用 select 語句進行查詢時，有時只需要前幾行數(shù)據(jù)。top (expression) percent with ties1expression：指定返回行數(shù)的數(shù)值表達式。如果指定了 percent， expression 將隱式轉換成 float，否則是 bigin

27、tpercent：指定只返回結果集中前 expression% 行數(shù)據(jù)。with ties：指定從基本結果集中返回額外的數(shù)據(jù)行（只有在 select 子句中包含了 order by 子句時，才能使用）。top 謂詞寫在 select 單詞的后面（如果有 distinct，則在 distinct 后面）。（查詢考試成績最高的 3 個成績。列出學號、課 程號、成績）select top 3 sno,cno,grade from sc ord er by grade desc若要包括并列第 3 名的成績：select top 3 sno,cno,grade with ties f rom sc or

28、der by grade desc2.數(shù)據(jù)更改功能2.1 插入數(shù)據(jù)insert into 表名(列名) values (值)（1）簡單插入語句insert into student values (001,陳 東,男,1996/6/23,信息管理系)（2）多行插入語句insert into sc values(001,c001,9 0),11001( ,c002,30),001( ,c005,null)（3）不按表順序插入語句按與表列順序不同的順序插入數(shù)據(jù)insert into student(sno,sname,sex,dep t) values (001,陳東,男,1996/6/2 3,信

29、息管理系)2.2 更新數(shù)據(jù)update 表名 set 列名 = 值（1）無條件更新update sc set grade = grade+10（2）有條件更新（將“c001”號課程的學分改成 5 分）update course set grade = 5 where cno = c001（將計算機系全體學生的成績加 5 分）update sc set grade = grade+5 from sc join student s on s.sno = sc.sno where dept = 計算機系2.3 刪除數(shù)據(jù)delete top (expression) percentfrom 表名（1）

30、無條件刪除delete from student（2）有條件刪除（刪除所有考試成績不合格的學生的選課記錄） delete from sc where grade = 90 then 優(yōu)when grade between 80 and 99 t hen 良when grade between 70 and 79 t hen 中when grade between 60 and 69 t hen 及格end as 成績fromsc on where cno = c0012. 子查詢如果一個 select 語句嵌套在另一個 select、insert、update 或 delete 語句中，則稱為

31、子查詢或內層查詢；而包含子查詢的語句稱為主查詢。子查詢通常用于滿足下列需求之一：象把一個查詢分解成一系列的邏輯步驟提供一個列表作為 where 子句和 in、exists、 any、 all 的目標對 提供由外層查詢中每一條記錄驅動的查詢子查詢通常有幾種形式：where 列名 not in (子查詢 ) where 列名 比較運算符 (子查詢) where exists(子查詢 )2.1 使用基于集合測試的嵌套子查詢使用嵌套子查詢進行基于集合的測試時，子查詢返回的是一個值列表，外層查 詢通過運算符 in 或 not in，對子查詢返回的結果集進行比較。select from .where no

32、t in (select from)包含這種子查詢形式的查詢語句是分步驟實現(xiàn)的，即先執(zhí)行子查詢，然后在子 查詢的結果基礎上執(zhí)行外層查詢（先內后外）。子查詢返回的結果是一個集合， 外層查詢就是在這個集合上使用 in 運算符進行比較。（查詢與劉晨在同一系的學生）select sno,sname,dept from student whe re dept in(select dept from student where sn ame = 劉晨)（查詢選修了 java 課程學生的姓名）select sname from stduent where sno in (select sno from sc

33、 where cno in(select cno from course where c name = java)第二個例子也可以用了連接查詢來做：select sname from student s join sc on s.sno = sc.sno join course c on c.cno = sc.cno where cname = java上述的例子可以看出子查詢和連接查詢可以互通，但在某些情況下是不可以的： （統(tǒng)計選了 java 課程的學生的姓名和所在系）子查詢：select sno 學號,count(*) 選課門數(shù),avg(g rade) 平均成績from sc where

34、sno in(select sno from sc join course con c.cno = sc.cnowhere cname = java) group by sno這個查詢不能完全用連接查詢實現(xiàn)，因為這個查詢的語義是要先找出選了java 課程的學生，然后再計算這些學生的選課門數(shù)和平均成績。連接查詢：select sno 學號,count(*) 選課門數(shù),avg(g rade) 平均成績from sc join course c on c.cno= sc.cno where cname = java oup by snogr以上查詢是錯誤的，查出來學生的選課門數(shù)都是 1 ！ 實際上這

35、個 1 指的是 java 這一門課程，其平均成績也是 java 成績?！咀⒁猓骸窟B接查詢和子查詢的區(qū)別：之所以這樣，是因為在執(zhí)行有連接操作的查詢時，系統(tǒng)首先將所有被連 接的表連接成一張大表，這張大表中的數(shù)據(jù)全部滿足連接條件的數(shù)據(jù)。之 后再在這張連接后的大表上執(zhí)行 where 子句，然后是 group by 子句。執(zhí)行完 where 子句之后，連接的大表中的數(shù)據(jù)就只剩下 java 這一門課程的 情況了，顯然不符情況。對于含有嵌套的子查詢的查詢，是先執(zhí)行子查詢，然后在子查詢的結果 基礎上再執(zhí)行外層查詢。【注意：】在子查詢中否定和在外查詢中否定的區(qū) 別 in 和 != 的搭配 相較于 not in

36、和 =的搭配是否相同？在子查詢中否定和在外查詢中否定的區(qū)別？（查詢沒選 c001 課程的學生的姓名和所在系）1.多表連接：select sname,dept from student s join sc on s.sno= sc。sno where cno != c001 2. 嵌套子查詢：1）在子查詢中否定select sname,dept from studentwhere sno not in(select sno from sc where cno = c001)2）在外層查詢中否定select sname,dept from studentwhere sno in(select sno from sc where cno != c001)這個例子，連接查詢是錯誤的，嵌套子查詢中方法一在子查詢中的否定是錯誤 的！嵌套子查詢中方法二在外