常用SQL语句(Update)

常用 SQL 语句：

删除表格

USE MyTest

GO

DROP TABLE Stud_drop

GO

 

新建表格

USE MyTest

GO

CREAT TABEL stud_new

(

Sid char(2) PRIMARY KEY ,

Sname char(8),

Sex char(2),

Sbirth datetime

)

GO

 

修改表格

USE MyTest

GO

ALTER TABALE stud_aler

 ADD Slesion_add varchar(10)

  ALTER COLUMN Sname char(20)

  DROP COLUMN Sex

GO

---------------------

插入

INSERT [INTO] table_name

       [(column_list)]

VALUES

       (data_values)

 

USE MyTest

GO

INSERT stud

(sid, sname, sex, age, lession)

VALUES

('0016', 'Killy', 'female', '19', 'English')

GO

 

‘insert the record into Table stud from Table stud_1

USE MyTest

GO

INSERT stud_1

SELECT sid, sname, sex, age, lession

FROM stud

WHERE lession = 'English'

GO

-------------

更新

UPDATE table_name

SET

{column_name = [expression | DEFAULT | NULL]}[, ..n]

[FROM {<table_source>}[, ..n]] [WHERE <search_condition>]

 

USE MyTest

GO

UPDATE stud

SET sex = 'female'

WHERE sname = 'Tom'

GO

 

---------------

删除

DELETE table_name

[ FROM { <table_source>}]

[ WHERE {<search_condition>}]

 

USE MyTest

GO

DELETE stud

WHERE sname = 'Tom'

GO

 

-------------

简单查询

SELECT select_list

[ INTO new_table_name]

FROM table_list

[ WHERE search_conditions]

[ GROUP BY group_by_expression]

[ HAVING search_conditions]

[ ORDER BY order_expression [ ASC| DESC]]

 

‘select into a new Table stud_2

USE MyTest

GO

SELECT * INTO stud_2

FROM stud

WHERE age > 22

GO

 

连接查询

SELECT select_list

FROM table1 [INNER] JOIN table2

ON table1.list = table2.list

 

SELECT select_list

FROM table1 LEFT [OUTER] JOIN table2

ON table1.title_id = table2.title_id

 

SELECT select_list

FROM table1 RIGHT [OUTER] JOIN table2

ON table1.title_id = table2.title_id

 

SELECT select_listg

FROM table1 FULL [OUTER] JOIN table2

ON table1.title_id = table2.title_id

`    `

    

子查询

带IN

SELECT title_id1,title_id2

FROM table1

WHERE title_id1 IN

                ( SELECT title_id1

FROM table2

WHERE search_condition

)

 

带比较运算符

SELECT title_id1,title_id2

FROM table1

WHERE title_id1 >

                ( SELECT title_id1

FROM table2

WHERE search_condition

)