1、简单的SELECT语句
SELECT语法格式
1)SELECT [ALL|DISTINCT] select _list
2)[INTO new_table]
3)FROM table_source
4)[WHERE search_conditions]
5)[GROUP BY group_by_expression]
6)[HAVING search_conditions]
7)[ORDER BY order_expression [ASC|DESC]]
2、设置查询条件
WHERE
3、排序
Order by
4、分组
Group by rollup | cube (FieldName)
5、使用函数 MIN/MAX/SUM/AVG/TOP...
6、使用HAVING子句
Having 配合 Group by 子句使用，对聚合结果进行筛选，如果 Where.
7、插入数据
INSERT语法
INSERT [INTO] table_or_view [(column_list)] VALUES (data_values)
8、INSTER ...SELECT语句
INSTER ...SELECT 语句语法
INSTER table_name [column_list]
SELECT column_list
FROM table_list
WHERE searhc_conditions
9、SELECT ...INTO语句
SELECT ...INTO 语句语法
SELECT <select_list>
INTO new_table
FROM [<table_source>][,...n]
WHERE <search_condition>
此语句常用来向临时表中写入查询结果
10、 UPDATE语句
UPDATE语句语法
UPDATE [TOP] {table_name|view_name} SET {column_name={expression|DEFAULT|NULL}|@variable=expression}[,...n]
WHERE {search_conditions}
11、在UPDATE语句中使用FROM子句
UserId = b.userId,UserName=b.userName,CompanyName=c.CompanyName
from T_Member a join T_Company b
on
a.userId=b.userId
where b.areaId=106
12、DELETE语句
DELETE语句语法
DELETE FROM <table_name> [WHERE <search_conditions]
13、TOP关键字和TOP表达式
语法格式：
[
TOP(expression) [PERCENT（百分比）]
]
select top 10 * from T_Member where sex = 1
--查询前10%符合条件的记录
select top 10 percent from T_Member where sex = 1
--使用变量
declare @count int
set @count = 5
select top (@count) * from T_Member where sex = 1 order by RegisterTime desc
--列出所有相同数据 with ties，此函数应配合 order by 一同使用才有效
select top 5 * from T_Member where sex = 0 order by areaId desc
--在UPDATE语句中使用TOP函数
update top (5) T_Member set password='000000'
14、COMPUTE子句（产生查询结果的合计）
[
COMPUTE
{{AVG|COUNT|MAX|MIN|STDEV|STDEVP|VAR|VARP|SUM}
(expression)}[,...n]
[BY expression [,...n]]
]
compute sum(分数),avg(分数),max(分数),min(分数)
--带by子句的合计,该操作将会对结果按考试编号进行分别合计运算
select * from 成绩信息 where 课程编号=2 order by 考试编号
compute sum(分数),avg(分数),max(分数),min(分数) by 考试编号
15、在Where子句使用运算符
1）between min and max
2）fieldName % 5 = 0
3）in (1,2,3,4,5)
4）not in (1,2,3,4,5)