SQL 基础（2）
case when 查询
SELECT name,course,score, case when score<60 then '不及格' when score >=60 then '及格' when score<80 and score>=70 '良好' else '优秀' end as column_name FROM table_name
SELECT name,course,score case course when '高级班' then '走向高级开发' when '架构班' then '走向架构师' end as column_name FROM table_name
with 关键字使用
WITH subquery AS (SELECT name,age,sex FROM table_name) SELECT * subquery
可利用with做递归查询
WITH subquery(PId,Id,name) AS (SELECT PId,Id,name FROM table_name1 WHERE condition UNION ALL SELECT PId,Id,name FROM table_name1 a INNER JOIN subquery ON a.Id = subquery.PId SELECT * FROM subquery
in/exist关键字查询
in查询，先执行子查询再执行外围查询
SELECT Column_name1,column_name2 FROM talbe_name WHERE column_name1 IN(SELECT column_name FROM table_name2 WHERE condition)
exist查询，主查询与exist查询一同查询，大性能比in查询好
SELECT column_name1,column_name2 FROM table_name1 as tab1 WHERE exist(SELECT 1 FROM table_name2 WHERE tab1.Id = Id AND condition)
表备份:复制新表
SELECT * INTO new_table_name FROM old_table_name
复制表数据：#把结构相同的表数据复制到指定表
分页查询
declare @pageSize int; select @pageSize=5; declare @pageIndex int; select @pageIndex =2; SELECT TOP @pageSize column_name1,column_name2 FROM table_name WHERE id not in(SELECT TOP @pageSize*(@pageIndex-1) id FROM table_name order by id))
分页查询2：2005及以上版本支持
row_number() over (order by id)) as rownumber
declare @pageSize int; select @pageSize=5; declare @pageIndex int; select @pageIndex =2; SELECT TOP @pageSize * FROM ( SELECT row_number() over (order by id)) as rownumber,column_name1,column_name2 FROM table_name) ) WHERE rownumber>@pageSize*(@pageIndex-1)
分页查询3：2008及以上版本支持
OFFSET pageSize*(pageIndex) ROWS FETCH NET pageSize ROWS ONLY
注意：一定要ORDER BY
declare @pageSize int; select @pageSize=5; declare @pageIndex int; select @pageIndex =2; SELECT column_name1,column_name2 FROM table_name ORDER BY column_name OFFSET @pageSize*(@pageIndex-1) ROWS FETCH @pageSize ROWS ONLY
行转列
SELECT name , isnull(sum(case course when '高级班' then score end),0 ) as '高级班'， isnull(sum(case course when '架构班' then score end),0 ) as '架构班' FROM table_name GROUP BY name
视图
视图是一种数据库对象，是从一个或者多个数据表或视图中导出的虚表，视图的结构和数据是对数据表查询的结果；只存放视图的定义，不存放视图对应的数据；基表中的数据发生变化，从视图中查询出来的数据也随之改变