一、數據插入

1、語法

INSERT?INTO 表名 [<屬性列>]

VALUES（參數列表）

注解：

①沒有說明屬性列，即要給出所有的列的值

②不確定的列，可以設置為null。（在允許為空的情況下）

2、實例

①在“人事管理系統”中，新增一個員工信息（員工編號‘100508’、員工姓名‘小龍女’、所在部門編號‘10001’、籍貫‘河南’）

insert into 員工信息 ( 員工編號 , 員工姓名 , 所在部門編號 , 籍貫 )

values ( '100508' , ' 小龍女 ' , '10001' , ' 河南 ' )

②將“人事管理系統”數據庫的“員工信息”表中籍貫為“河南”并且所在部門編號為”10001”數據插入到“新員工信息”表中。

insert into 新員工信息 ( 員工編號 , 員工姓名 , 所在部門編號 )

select 員工編號 , 員工姓名 , 所在部門編號 from 員工信息

where 籍貫 = ' 河南 ' and 所在部門編號 = '10001'

?

二、數據更新

1、語法

UPDATE表名 SET 列名=表達式

[WHERE<條件>]

2、實例

①在“人事管理系統”數據庫“部門信息”表中，將部門的員工人數設置為10

update 部門信息 set 員工人數 = 10

②在“人事管理系統”數據庫“員工信息”表中，將文化程度為“大專”，并且在“2005-05-01”到“2007-05-01”之間入職的所有員工調動到編號為“10006”的部門去

update 員工信息 set 所在部門編號 = '10006'

where 入職時間 between '2005-05-01' and '2007-05-01' and 文化程度 = ' 大專 '

③在“人事管理系統”數據庫中對部門進行了重組和調整，原來編號為10006的部門名稱變為“市場開發部”，人數也調整為20人

update 部門信息 set ? 部門名稱 = ' 市場開發部 ' , 員工人數 = 20

where 部門編號 = 10006

?

三、數據刪除

1、語法

（1）刪除表的記錄，保留表的結構，寫日志可以恢復

DELETEFROM 表名

[WHERE<條件>]

（2）刪除表的所有記錄，保留表的結構，不寫日志，無法恢復，速度快

TRUNCATETABLE 表名

2、實例

①在“人事管理系統”數據庫中，編號為“100503”的新員工升級為正式員工，需要在“新員工信息”表中刪除他的記錄。

delete from 新員工信息 where 員工編號 = '100503'

②在“人事管理系統”數據庫中，需要刪除%的員工信息

delete top ( 5 ) percent from 員工信息

③在“人事管理系統”數據庫中，刪除“新員工信息”表中的所有記錄

truncate table 新員工信息

或 delete from 新員工信息

④將學生“陳霞”所在班級的其他學生并且成績不合格的學生成績刪除

delete 成績表

where ? 成績 < 60 and 學號 in (

select 學號 from 學生信息

where 班級編號 =( select 班級編號 from 學生信息 where 姓名 = ' 陳霞 ' ))

?

四、數據查詢

1、語法

SELECT[ALL|DISTINCT] <目標列表達式>

FROM<表名|視圖名>

WHERE<條件表達式>

GROUPBY <列名>

HAVING<條件表達式>

ORDERBY <列名> [ASC|DESC]

2、注解

（1） SELECT [ALL|DISTINCT] <目標列表達式>

①不僅可以是表中的屬性列，也可以是表達式，還可以是字符串常量、函數。

②用戶可以通過指定別名來改變查詢結果的列標題。

③ALL|DISTINCT

l? DISTINCT：去掉結果表中重復行。

l? ALL：保留結果表中取值重復的行。如果沒有指定DISTINCT關鍵字，則缺省為ALL。

④查詢全部列：可以使用“*”

（2） WHERE <條件表達式>

①比較(=等于 >大于 <小于 >=大等于 <=小等于！=或<>不等于！>不大于！<不小于)

②確定范圍(BETWEEN AND,NOT BETWEEN AND)

③確定集合（IN,NOT IN）

④字符匹配（LIKE,NOT LIKE）

l? %：代表任意長度（長度可以為0）的字符串

l? _：代表任意單個字符。

⑤空值（IS NULL,IS NOT NULL）

⑥多重條件（AND,OR,NOT）

（3） ORDER BY <列名> [ASC|DESC]

l? DESC：降序

l? ASC：升序，缺省為ASC

（4）聚集函數

COUNT([ALL|DISTINCT]*)	統計元組個數
COUNT([ALL|DISTINCT]<列名>)	統計一列中值得個數
SUM([ALL|DISTINCT]<列名>)	計算一列值得總和
AVG([ALL|DISTINCT]<列名>)	計算一列值得平均值
MAX([ALL|DISTINCT]<列名>)	求一列值中的最大值
MIN([ALL|DISTINCT]<列名>	求一列值中的最小值

（5） GROUP BY <列名> HAVING <條件表達式>

①將查詢結果按某一列或多列的值分組，值相等的為一組。

②除了聚集函數，其他都要分組。

③如果分組后，還要求一定的條件對這些組進行篩選，最終只輸出滿足條件的組，可以使用 HAVING<條件表達式>。

④WHERE子句的作用于基本表或試圖，從中選擇滿足條件的元組。HAVING作用于組，從中選擇滿足條件的組。

3、關于查詢

（1）單表查詢：針對一個表

（2）連接查詢：針對2個表以上

①等值連接|非等值連接

②自然連接：在等值連接中把目標列中重復的屬性去掉。

③自身連接：一個表與自己進行連接。

④外連接（左外連接、右外連接）

⑤復合條件連接

（3）嵌套查詢

①定義：將一個查詢塊嵌套在另一個查詢塊的查詢

②類型：帶有IN的子查詢、帶有比較運算符的子查詢、帶有ANY或ALL的子查詢、帶有EXISTS子查詢

（4）集合查詢

①并操作UNION

②交操作INTERSECT

③差操作EXCEPT

4、實例

（1）單表查詢

①查詢學生表的所有記錄

select * from 學生表

②查詢學號為‘1005’的學生信息

select * from 學生信息

where 學號 = '1005'

③在“人事管理系統”數據庫“員工信息”表中，查詢工齡大于4年的員工信息

select * from 員工信息

where year ( getdate ())- year ( 入職時間 )> 4

④查詢學生的籍貫字段不為空的記錄

select * from 學生信息

where 籍貫 is not null

⑤查詢姓名第一個字符是‘張’且姓名共兩個字符的學生信息

select * from 學生信息

where 姓名 like ' 張 _'

⑥查詢學生姓名中帶有“麗”字或“娜”字的學生信息

select * from 學生信息

where 姓名 like '% 麗 %' or 姓名 like '% 娜 %'

⑦查詢教師表的職稱字段有哪幾種取值

select distinct 職稱 from 教師信息

（2）分頁查詢

①查詢學生信息中前5條記錄

select top 5 * from 學生信息

②查詢學生信息中前20%條記錄

select top 20 percent * from 學生信息

（3）聚集函數

①統計學生表中所有男生的信息，并將查詢結果字段命名為“男生總人數”

select COUNT (*) as 男生總人數 from 學生信息

where 性別 = ' 男 '

②根據籍貫查詢各省學生人數，并顯示省份和人數信息

select 籍貫 as 省份 , count (*) as 學生人數 from 學生信息

group by 籍貫

③按課程號計算選課表中各門課程的平均分，總分

select 課程編號 , AVG ( 成績 ) as 平均分 , SUM ( 成績 ) as 總分 from 成績表

group by 課程編號

④查詢員工表中2000年以后入職的姓名，政治面貌字段，并按政治面貌分組顯示查詢結果

select 員工姓名 , 政治面貌 from 員工信息

where 入職時間 > '2000' and

政治面貌 in (

select 政治面貌 from 員工信息

group by 政治面貌 )

（4）連接（多表）查詢

①查詢參加考試的學生姓名，課程名，成績信息

方法一：

select 姓名 , 課程名稱 , 成績

from ? 學生信息 , 課程信息 , 成績表

where 學生信息 . 學號 = 成績表 . 學號 and

課程信息 . 課程編號 = 成績表 . 課程編號

方法二：

select 姓名 , 課程名稱 , 成績

from ? 學生信息 t1 , 課程信息 t2 , 成績表 t3

where t1 . 學號 = t3 . 學號 and

t2 . 課程編號 = t3 . 課程編號

②查詢出所有考試及格的學生的成績信息，包括學生的學號、姓名、性別、年級、班級編號及考試成績，并且按照成績進行降序排列

select ? 學生信息 . 學號 , 姓名 , 性別 , 年級 , 班級編號 , 成績

from ? 學生信息 , 成績表

where 學生信息 . 學號 = 成績表 . 學號 and

成績 >= 60

order by 成績 desc

③詢所有學生的考試成績信息，包括學生學號、姓名、課程編號和成績信息。

方法一：左外連接

select ? a . 學號 , a . 姓名 , b . 課程編號 , b . 成績

from 學生信息 a left ? outer join 成績表 b on a . 學號 = b . 學號

方法二：右外連接

select ? a . 學號 , a . 姓名 , b . 課程編號 , b . 成績

from 學生信息 a right ? outer join 成績表 b ? on a . 學號 = b . 學號

方法三：全外連接

select ? a . 學號 , a . 姓名 , b . 課程編號 , b . 成績

from 學生信息 a full outer join 成績表 b ? on a . 學號 = b . 學號

（5）嵌套查詢

①查詢成績低于分的學生姓名

select 姓名 from 學生信息

where 學號 in (

select 學號 from 成績表

where 成績 > 85 )

或連接（多表）查詢

select 姓名 from 學生信息 , 成績表

where 學生信息 . 學號 = 成績表 . 學號 and

成績 > 85

②查詢平均成績大于的課程信息。

select * from 課程信息

where 課程編號 in (

select 課程編號 from 成績表

group by 課程編號

having avg ( 成績 )> 70 )

③查詢出最高成績(比任意學生成績都高)的學生的學號信息

select * from 學生信息

where 學號 in(

select 學號 from 成績表

where 成績 >=all( select 成績 from 成績表 ))

④查詢出不是最低成績的學生的學號信息

select * from 學生信息

where 學號 in(

select 學號 from 成績表

where 成績 >=any( select 成績 from 成績表 ))

（6）集合查詢

①“人事管理系統”數據庫的“員工信息”表中，查詢“所任職位”為“經理”的員工編號和員工姓名信息，并為其增加新列“所在位置”，列的內容為“員工信息表”；從“部門信息”表中查詢所有的部門編號和部門名稱信息，并定義新增列的內容為“部門信息表”；最后將兩個查詢結果聯合在一起。

select 員工編號 , 員工姓名 , ' 員工信息表 ' ? as 所在位置 from 員工信息

where 所任職位 = ' 經理 '

union

select 部門編號 , 部門名稱 , ' 部門信息表 '

from 部門信息

（7）關于查詢的其他操作

①創建一個學生補考表，表中數據來自成績表，條件為成績小于60分，要求補考表中僅顯示學生的學號和補考課程的編號。

select 學號 , 課程編號

into 補考表

from 成績表

where 成績 < 60

②創建新表：及格成績表（學號，課程號，成績），并將選課表中所有及格成績的記錄插入到新表中

select 學生信息 . 學號 , 課程編號 , 成績

into 及格表

from 學生信息 , 成績表

where 學生信息 . 學號 = 成績表 . 學號 and

成績 >= 60

?

sql server 數據增刪改查