文稿sql教程chapter 5liu第五章

SQL

Server數(shù)據(jù)管理第五章回顧SQL

Server的表和相關(guān)的操作；在企業(yè)管理器中創(chuàng)建表和使用T-SQL創(chuàng)建表；SQL

Server實(shí)體完整性、引用完整性和域完整性的策略和實(shí)現(xiàn)；修改表結(jié)構(gòu)和刪除數(shù)據(jù)庫(kù)表；約束對(duì)象及分類、使用T-SQL創(chuàng)建約束對(duì)象；目標(biāo)在T-SQL

中使用條件表達(dá)式和邏輯運(yùn)算符；使用T-SQL

向表中插入數(shù)據(jù)；更新現(xiàn)有表中的數(shù)據(jù)；刪除現(xiàn)有表中的數(shù)據(jù)；使用轉(zhuǎn)換工具導(dǎo)入和導(dǎo)出數(shù)據(jù)。條件表達(dá)式表達(dá)式是符號(hào)和運(yùn)算符的組合，SQL

Server可以對(duì)它進(jìn)行運(yùn)算以得到一個(gè)單值。表達(dá)式可以包含下列一個(gè)或多個(gè)部分：常數(shù)列{一元運(yùn)算符}{二元運(yùn)算符}比較運(yùn)算符運(yùn)算符含義=等于>大于<小于>=大于等于<=小于等于<>不等于通配符通配符解釋例子'_'一個(gè)字符Branch

Like

'L

'%任意長(zhǎng)度Route_Code

Like

'AMS-%'[]指定范圍內(nèi)Airbusno

Like

'AB0[1-5]

'[^]不在括號(hào)中Airbusno

Like

'AB0[^1-5]

'邏輯運(yùn)算符2-1T-SQL支持的邏輯運(yùn)算符如下：AND

和OR運(yùn)算符用于在WHERE

子句中連接搜索條件：NOT：否定搜索條件AND

：連接兩個(gè)條件且僅當(dāng)兩個(gè)條件都為真時(shí)返回

TRUEOR

：

連接兩個(gè)條件，但只要其中任一個(gè)為真就返回

TRUE邏輯運(yùn)算符2-2ANDPrice

<

100

AND

Edition

=

1ORCITY

=

‘DALLAS’

OR

STATE

=‘TEXAS’NOTMARITALSTATUSNOT=‘SINGLE’利用查詢創(chuàng)建表使用SQL從現(xiàn)有表創(chuàng)建新表SELECT

*

INTO

BackupItems

from

Item;利用查詢創(chuàng)建表USENorthWindSelect

IDENTITY(int,1,1)

As

P_idintoproduct_Det

From

products向表中添加數(shù)據(jù)向表添加數(shù)據(jù)使用語句

insert有兩種可能由用戶輸入數(shù)據(jù)從其它表導(dǎo)入數(shù)據(jù)向表中插入數(shù)據(jù)2-1INSERT

[INTO]

<TABLENAME>

VALUES

<VALUE>INSERT

INTO

MyTable

(PriKey,

Description)VALUES

(123,

'A

description

of

part

123.')MyTable向表中插入數(shù)據(jù)2-2SomeViewINSERT

INTO

MyTable (PriKey,

Description)SELECT

ForeignKey,

DescriptionFROM

SomeView刪除數(shù)據(jù)DELETE

FROM

表名；DELETE

FROM

表名WHERE

條件；注意：它將刪除一條記錄而不是某個(gè)字段的值示例：DELETE

FROM

table1；DELETE

FROM

table1 WHERESrNo=2；刪除數(shù)據(jù)行3-1DELETE

FROM

<TABLENAME>

[WHERE

<Search_Condition>]刪除數(shù)據(jù)行3-2DELETE

FROM

SALESDELETE

FROM

JOBS

WHERE

JOB_ID

>1000刪除數(shù)據(jù)行3-3TRUNCATE

TABLE

SALES與delete的區(qū)別刪除數(shù)據(jù)行是否刪除了表？Delete

語句可使用“Delete”語句刪除記錄DELETE

FROM

DespMasterWHERE

DespDate

BETWEEN

1/10/99

AND

1/15/99;更新數(shù)據(jù)行2-1UPDATE

<TABLENAME>

SET

<Column_Name

=

Value>[WHERE

<Search

condition>]UPDATE

TITLES

SET

Price

=

Price

+0.25*PriceWhere

Title_ID

=

‘TC777’更新數(shù)據(jù)行2-2演示在查詢分析器中更新數(shù)據(jù)行…聯(lián)接

3

-

1聯(lián)接內(nèi)聯(lián)接外聯(lián)接自聯(lián)接聯(lián)接

3

-

2Table_A

AS

Table_Alias_A

JOINTable_B

AS

Table_Alias_B

ONTable_Alias_A.

<Common_Field>

=Table_Alias_B.

<Common_Field>CustomerCustomer

CodeNameCityAddressPin

codeOrdersOrder

NumberOrder

DateCustomer

codeMr.X

的詳細(xì)信息如果多個(gè)表之間存在關(guān)系，則可以同時(shí)查看其中的數(shù)據(jù)； 定義明確的關(guān)系有助于維護(hù)高級(jí)完整性，如果orders

表中存在某客戶的記錄，則不能從customer

表中刪除該客戶的記錄；Customer

Orders按Mr.X

排列的Orders的詳細(xì)信息刪除聯(lián)接3-3使用內(nèi)聯(lián)接更新外聯(lián)接外聯(lián)接類型左向外聯(lián)接右向外聯(lián)接完整外部聯(lián)接使用外聯(lián)接更新UPDATE

TITLES

SET

Price

=

Price

+

10From

Titles

LEFT

OUTER

JOIN

TitleAuthorON

Titles.Title_IS

=

TitleAuthor.Title_IDWHERE

Titles.Title

=

'SuShi,AnyOne?'UPDATE

TITLES

SET

Price

=

Price

+

10From

Titles

RIGHT

OUTER

JOIN

TitleAuthorON

Titles.Title_IS

=

TitleAuthor.Title_IDWHERE

Titles.Title

=

'SuShi,AnyOne?'UPDATE

TITLES

SET

Price

=

Price

+

10From

Titles

FULL

OUTER

JOIN

TitleAuthorON

Titles.Title_IS

=

TitleAuthor.Title_IDWHERE

Titles.Title

=

'SuShi,AnyOne?'導(dǎo)入和導(dǎo)出數(shù)據(jù)2-1演示在企業(yè)管理器中導(dǎo)出表為文本數(shù)據(jù)…