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 视图 视图是一种数据库对象，是从一个或者多个数据表或视图中导出的虚表，视图的结构和数据是对数据表查询的结果；只存放视图的定义，不存放视图对应的数据；基表中的数据发生变化，从视图中查询出来的数据也随之改变