sql server 2005

By Ashley Graham,2014-05-25 20:00
18 views 0
sql server 2005


    Create database NOT NULL


     Drop database

     -name> DEFAULTult value>

     default Alter database

     Modify name=


    Alter table

    Alter table table_name sp_helpdb Add column_name data_type


     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


     On commit delete rows : COMMIT

    SP_RENAME Create table table_name

     ( 1

    Sp_rename .column 2

    UNIQUE, 3




    Index index_name on table_name |



    table_name | View_name



    Drop index table_name.index_name


    primary keyuniquesql


     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


    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 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 ascDESC And subsidy>100 order by sex,birthday,sex


    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



    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


     +-*/% _

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

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


    (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)


    CASTCASTdatetime Getdate()


    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


    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 Order by no 3 Dd/mm/yy 4 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


     ,day,1980-01-01 01


     ABC(numeric_expression) ASCII()

    ASCII Ceiling(numeric_expression)

     ASCII char()

    Floor(numeric_expression) lower()

    Round(numeric_expression, upper() integer_expression Integer_expression)


     Sin(float_expression) rtrim()

    Asin(float_expression) len()


     Cos(float_expression) )

    integer_expressionAcos(float_expression )

     Tan(float_expression) right(,

    ) Atan(float_expression)



    Substring(, Log(float_expression)


    start_positionlength Sqrt(float_expression)


    Charindex 10-1


    substring_expression) .Pi()






Select 12 表示用char_expression3 Replace(,

    From 12 换在char_expression1中的,

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

And 3.stno=2.stno UPPERLOWER


     Select top 10 supplierid ,upper(companyname) as


     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()




    Select tno,tname,tage,gname,gmark

    From teachers NATURAL JOIN groups Count()


    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



    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



    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



     ANY >\>=\!< ALL


    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 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 )



     Select * from subjects as t

     Where exists(select * from studachieve

    UNIQUE Where studdname=t.subname)

     Order by subid


     Selecvt * from subjects as T

     Where subid!=1001003

     And Exists (select * from studachieve

     where studdname=t.subname)

     order by subid

Report this document

For any questions or suggestions please email