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