SQL_Server_2008完全学习之第六章数据查询和管理

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 FieldName

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子句

UPDATE T_UserInfo set
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（百分比）]

[WITH TIES()]
]

 

--查询前10条符合条件的记录
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]]
]

select * from 成绩信息 where 课程编号=2
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)