)

    Create database NOT NULL

     NULL

     Drop database

     -name> DEFAULTult value>

     default “” Alter database

     Modify name=

    sp_helpdb

    Alter table

    Alter table table_name sp_helpdb Add column_name data_type

    ADD

     Create global | local temporary table

     on commit (presservr rows | delete rows)

    Alter table (column _ name data type [(length)][NULL | NOT

    Alter column address char(50) NULL], …..n)

    Address50 | [LINK]

    Alter table | [table_constraint] [,……n]

    Alter column address ntext Column:

    Address Datatype:

    Alter table

    Drop column duty

    duty |

    Drop table table_name restrict|cascade Global temporary:

     Local temporary :

    RESTRICT On commit preserve rows : COMMIT

    DBMSCASCADE

     On commit delete rows : COMMIT

    SP_RENAME „‟‟‟ Create table table_name

     ( 1

    Sp_rename „.‟column 2

    UNIQUE, 3

     CLUSTERED,

    NONCLUSTERED,

Create UNIQUE[CLUSTERED | NONCLUSTERED]

    Index index_name on table_name |

    view_name(column_name[1,….,n])

    Index_name

    table_name | View_name

Column_name

    no

    Drop index table_name.index_name

    Table_nameindex_name

    primary keyuniquesql

    server

     Create view address_view Createview[schema_name.]view_name [(column[,….n])] As select * from employee_view

    Where address=‟‟ [with[,….n]]

     As select_statement[;]

    [with check option] Createviewemp_view(view_sname,view_phone,view_address) ::= As select sname,phone,address { From employee

     [encryption]

    Createviewemp_view) [schemabinding]

    [view_metadata] As select sname,phone,address } From employee schema_name: vIew_name: Column: Sp_rename „employee_view‟,‟view_emp‟ Select_statement:select check optionAlter view employee_view

    select_statement As select sname,sex,phone,address Encryption:sys.syscommentscreate viewFrom employee

    Schemabinding: Where sex =‟‟ View_metadata: Drop view employee_view

    sql serverDB-LibraryODBCOLEDBAPI ()

     Create view emppay_view

     As select no,sname , basicpay , subsidy Create view employee_view From employee , pays As Where employee.no=pays.sno Select sname,phone,address

    From employee Create view tables_view

     As select student.stno,student.stname,achievement.suname, Create view employee_view achievement.achieve,class.cname As Select sname,phone,address from student,achievement,class From employee where achievement.stno=student.stno Where address=‟‟ and student.stpno=class.cld

     From employee

    Create view pays_view Select no,sname,phone As select no,sname , basicpay , subsidy From employee

    From employee , pays order by no asc

    Where employee.no=pays.sno ascDESC And subsidy>100 order by sex,birthday,sex

    pays_view,employeepaysbirthday

    100Order by sex asc , birthday desc

Select * from pays_view order by no

    order by Select suname,sum(achieve) as „‟

    pays_view100From achievement

    1200 Group by suname

    Create view basicpays_view Having sum(achieve)<200 As select * from pays_view Sum Where basicpay>1200

    Having sum(achieve)<200200

    GROUP BY NULL NULL Select [All|distinct]select_list Select address , count(*) as „‟ From table_name[,.,table_namen] From employee [join join_condition] Group by address [where search_condition] Count() [group by group_by_expression] WHERE [having search_condition] Select column from table_name [order by order_expression[asc | desc]] Where column condition value DISTINCT ConditionVALUE Select distinct no,sname,phone Where column condition value--- From employee order by no Condition Select no as „‟ Between Sname as „‟ Select column from table_name Phone as „‟ Where column between value1 and value2

    TOP Select column from table_name Select top n [percent] select_list Where column in („vaule_list‟,‟vaule_list‟)

    vaule_listcolumn From table_name

    NPERCENTNot in

    N

    NOTBETWEEN

    NULL Select * from depart

    IS NOT NULL Where not emp_depart=‟‟

    emp Select * from employee

    Where address is not null

    Order by no Select * from employee

     Where address is not null Where address is null Where birthday not between „1979-01-01‟ and „1982-01-01‟

     Order by no

    1979-01-011982-01-01birthday LINK

     CHARvarchartextLINK

     +-*/% _

    % Select no,name,nvalues/12 as [] From sellvalue

    [^] Nvalues/12 / Link „%‟NOT LINK no1/(no2+no3) as

     SQLUNIONINTERSECT

    (AND)OR Select no,sname from employee

     [where sname link „%‟] SELECT * from employee

    Where sex=‟‟ Union INTERSECT And (OR) address link „%%‟ Select stno,stname from student

     employee And (OR) birthday>‟1980-01-01‟

     INTERSFCT Where condition1 on (condition2 and condition3)

IN

    CASTCASTdatetime Getdate()

     CONVERT

    CASESelect no,sname,sex,cast(birthday as char(11)) as „‟

     From employee stle

    Order by no 0 Mon dd yyyy hh:mi am/pm

    char() 9 Mon dd yyyy hh:mi:ss:mmmm am/pm

     13 Dd mon yyyy hh:mm:ss:mmm

    Case 20 Yyyy-mm-dd hh:mi:ss

     Select no,sname,sex,birthday,= 21 Yyyy-mm-dd hh:mi:ss:mmm

    Case

    When birthday>‟1981-01-01‟ and „1989-01-01‟ then „‟ 8 hh:mm:ss

    When birthday>‟1970-01-01‟ and „1981-01-01‟ then „‟ 14 Hh:mm:ss:mmm When birthday>‟1955-01-01‟ and „1970-01-01‟ then „‟ End 1 Mm/dd/yy From employee 2 yy.mm.dd Order by no 3 Dd/mm/yy 4 dd.mm.yy SQL 5 dd-mm-yy Substring() 6 Dd mon yy Convert() 7 Mon dd,yy Getdate() 10 mm-dd-yy 11 Yy/mm/dd Day() 12 yymmdd Month() Select convert(varchar,getdate(),20) as „ Year() ‟ Dateadd(day,3,indate) as „Dateadd() indateday3Select na,sname,sex,birthday, indate Conwert(varchar,birthday,11) as „‟ 12Datediff() From employees

    Datename()

     ,day,1980-01-01 01

     Datepart()

     ABC(numeric_expression) ASCII()

    ASCII Ceiling(numeric_expression)

     ASCII char()

    Floor(numeric_expression) lower()

    Round(numeric_expression, upper() integer_expression Integer_expression)

     ltrim()

     Sin(float_expression) rtrim()

    Asin(float_expression) len()

     left(,

     Cos(float_expression) )

    integer_expressionAcos(float_expression )

     Tan(float_expression) right(,

    ) Atan(float_expression)

    integer_expression

     Exp(float_expression)

    Substring(, Log(float_expression)

    rt_position>,length)

    start_positionlength Sqrt(float_expression)

     Sign(numeric_expression)

    Charindex 10-1

    (,

    substring_expression) ….Pi()

    3.1415926

    expression0~1Rand()

    replicate(char_expression,

    char_expressioninteger_expression)

     reverse()

Select 12 表示用char_expression3替 Replace(,

    From 12 换在char_expression1中的,

    Where 1.stno=2.stno 子串char_expression2 )

And 3.stno=2.stno UPPERLOWER

UNION JON Upper

     Select top 10 supplierid ,upper(companyname) as

     „companyname‟

     Upper(contactname) as „contactname‟

     From suppliers

     supplierssupplierid ,

     companyname ,contactname

     companyname ,contactname

     Select distinct a1.studname,a1.studdname,a1.studmark

    From studachieve as a1,studachieve as a2

    Where a1.studid=a2.studid Sum()

    And a2.studmark<60

    Order by a1.studname Max()

     Min()

    NATURAL JOIN

     Avg()

    Select tno,tname,tage,gname,gmark

    From teachers NATURAL JOIN groups Count()

    where

    Select s.tno,s.tname,s.tage,t.gid,t.gname,t.gmark Select sum() as „‟

    From teachers as s, From

    Groups as t

    Where s.gid=t.gid

    INNER JOIN CROSS JOIN

    =

    Select s.tno,s.tname,s.tage,t.gid,t.gname,t.gmark Select s.tno,s.tname,s.tage,t.gid,t.gname,t.gmark From teachers as s From teachers as s

    Inner join groups as t Cross join groups as t

    On s.gid=t.gid Order by tno

    Order by tno

    =

    on s.gid {=}t.gid 1selectfromwhere \having

     Group by.where

    (left outer join) 2

    3INselect

     select

    NULL where

    (right outer join) 4

    Select s.tno,s.tname,s.tage,t.gid,t.gname,t.gmark 5EXISTS selectFrom teachers as s * left outer join(right outer join) groups as t 6order by On s.gid=t.gid 7255 Order by tno

    NULLSelect studid,studname,studdname,studmark

     From studachieve

    Select s.tno,s.tname,s.tage,t.gid,t.gname,t.gmark Where studname=(select stname from student where stname From teachers as s =‟‟)

    full outer join groups as t

    On s.gid=t.gid

    Order by tno

    ()1001003

    subjects

     ANY >\>=\!< ALL

    EXISTS All ANY

    Selecvt * from subjects as T IN = ANY

    Where subid!=‟1001003‟ NOT IN <> ANY

    or Exists (select * from studachieve <\<=\!> ALL

    where studdname=t.subname) ANY

    order by subid Select * from teacher

     Where tage>any(select tage from teacher)

    NOT EXISTS

    NOT EXISTSSQL

    NOT EXISTS Create view showstud

    Selecvt * from subjects as T As select studid,studname,studdname,studmark

    Where not Exists (select * from studachieve From studachieve

    where studdname=t.subname) Where studmark>(select avg(studmark) from studachieve)

    order by subid IN

     Select column_name from table_ame

    HAVING Where expression [not] in

     (select expression from table_name

     where column_name2<60 )

     EXISTS

     Flase

     Select * from subjects as t

     Where exists(select * from studachieve

    UNIQUE Where studdname=t.subname)

     Order by subid

     EXISTS

     Selecvt * from subjects as T

     Where subid!=‟1001003‟

     And Exists (select * from studachieve

     where studdname=t.subname)

     order by subid