查詢和修改數(shù)據(jù)

第四章查詢和修改數(shù)據(jù)Select過濾數(shù)據(jù)格式化結(jié)果集

Inserting

DataDeleting

DataUpdating

Data查詢和修改數(shù)據(jù)內(nèi)容簡介Select過濾數(shù)據(jù)格式化結(jié)果集Inserting

DataDeleting

DataUpdating

Data查詢和修改數(shù)據(jù)Partial

SyntaxSELECT

[ALL

|

DISTINCT]

<select_list>FROM

{<table_source>}

[,…n]WHERE

<search_condition>1

SELECT

StatementSelect

ListWHEREFROM查詢和修改數(shù)據(jù)1.1指定列employeeid

lastnameDavolioFullerLeverlingPeacockBuchananSuyamaKingCallahanDodsworthfirstnameNancyAndrewJanetMargaretStevenMichaelRobertLauraAnnetitleSales

RepresentativeVice

President,

SalesSales

RepresentativeSales

RepresentativeSales

ManagerSales

RepresentativeSales

RepresentativeInside

Sales

CoordinatorSales

RepresentativeUSE

northwindSELECT

employeeid,

lastname,

firstname,

titleFROM

employeesGO查詢和修改數(shù)據(jù)1.2

WHERE——過濾行employeeid5lastnameBuchananfirstnameSteventitleSales

ManagerUSE

northwindSELECT

employeeid,

lastname,

firstname,

titleFROM

employeesWHERE

employeeid

=

5GO查詢和修改數(shù)據(jù)修改列名SELECT

au_fname,

au_lname,

city,

state， phone

AS

Telephone

FROM

authors--推薦使用SELECT

au_fname,

au_lname,

city,

state， Telephone=

phone

FROM

authorsSELECT

au_fname,

au_lname,

city,

state，phone

Telephone

FROM

authors查詢和修改數(shù)據(jù)更改表名SELECT

a.*

from

authors

as

aSELECT

a.*

from

authors

a3）select

b.*

from(select

*

from

authors)

as

b查詢和修改數(shù)據(jù)Insert單行數(shù)據(jù)INSERT

…VALUES多行數(shù)據(jù)INSERT…SELECTSELECT

INTO插入部分?jǐn)?shù)據(jù)使用默認(rèn)插入數(shù)據(jù)查詢和修改數(shù)據(jù)插入單行數(shù)據(jù)語法INSERT

INTO

表名[列名序列]

VALUES(值序列）例子查詢和修改數(shù)據(jù)DELETE語法DELETE

FROM表名[WHERE刪除條件]例子USE

northwindDELETE

ordersWHERE

DATEDIFF(MONTH,

shippeddate,

GETDATE())

>=

6GO查詢和修改數(shù)據(jù)USE

northwindUPDATE

productsSET

unitprice

=

(unitprice

*

1.1)GOUpdate語法UPDATE表名SET列名=常量值|表達(dá)式

[WHERE更新條件]例子查詢和修改數(shù)據(jù)回顧Select過濾數(shù)據(jù)Inserting

DataDeleting

DataUpdating

Data查詢和修改數(shù)據(jù)內(nèi)容簡介Select過濾數(shù)據(jù)格式化結(jié)果集Inserting

DataDeleting

DataUpdating

Data查詢和修改數(shù)據(jù)USE

northwindUPDATE

productsSET

unitprice

=

(unitprice

*

1.1)GOUpdate語法UPDATE表名SET列名=常量值|表達(dá)式

[WHERE更新條件]例子查詢和修改數(shù)據(jù)回顧Select過濾數(shù)據(jù)Inserting

DataDeleting

DataUpdating

Data查詢和修改數(shù)據(jù)1.2

WHERE——過濾行employeeid5lastnameBuchananfirstnameSteventitleSales

ManagerUSE

northwindSELECT

employeeid,

lastname,

firstname,

titleFROM

employeesWHERE

employeeid

=

5GO查詢和修改數(shù)據(jù)Insert單行數(shù)據(jù)INSERT

…VALUES多行數(shù)據(jù)INSERT…SELECTSELECT

INTO插入部分?jǐn)?shù)據(jù)使用默認(rèn)插入數(shù)據(jù)查詢和修改數(shù)據(jù)USE

northwind