SQL脚本

1.写一个包含连接和分组的sql

select a,b,rank() over(partition by a order by b ) nmb from test

2:写一个包含连接和case when的sql

SELECT
 CASE
   WHEN user_name IS NULL THEN '李四' 
   ELSE user_name 
 END AS userName 
FROM
  USER;

3:写一个包含连接和Cast()函数的sql

SELECT CAST('12' AS int) FROM　DUAL;

4.写一个包含连接和Convert()函数的sql

select name,school,address,convert(Varchar(10),aeg)+'岁',tim,from Table_2

5:写一个包含子查询的sql

select Sno,Cno
from tb_SC x
where Grade >(
select AVG(Grade)
from tb_SC y
where x.Sno=y.sno
)

6.写一个视图的sql

Create view vw1 as
 
Select 学号,姓名,所属院系
 
From student
 
Where 课名=’软件工程’ and 所属院系=’计算机’
 

7.创建索引的sql

CREATE INDEX index_name ON table_name(column_name,column_name) include(score)

 

8:分页存储过程sql

create proc pageForUsers
      @currPage int,        --当前页数
      @pageSize int,        --每页多少条记录
      @count int output    --总记录数    
      as
      declare @firstIndex  int
     declare @lastIndex  int
     declare @sqlText varchar(200)
 
     --统计总记录数
     select @count=count(*) from users
     --首先计算当前页第一条记录索引
     set @firstIndex=(@currPage - 1) * @pageSize + 1
     --当前页最后一条记录索引
     set @lastIndex = @firstIndex + @pageSize - 1
     --显示结果
     set @sqlText='select top ' + convert(varchar(5),@pageSize) + ' * from users
         where [id] not in (select top ' + convert(varchar(5),@firstIndex-1) + '[id] from users)'
     exec (@sqlText)

 

9:写一个触发器sql

//AFTER 触发器语法：
CREATE [ OR ALTER ] TRIGGER [ schema_name . ]trigger_name   
ON { table }   
[ WITH <dml_trigger_option> [ ,...n ] ]  
{ FOR | AFTER }   
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }   
AS { sql_statement  [ ; ] [ ,...n ] }  
 
<dml_trigger_option> ::=  
    [ NATIVE_COMPILATION ]  
    [ SCHEMABINDING ]  
    [ EXECUTE AS Clause ]
 
//INSTEAD OF 触发器语法：
CREATE [ OR ALTER ] TRIGGER [ schema_name . ]trigger_name   
ON { table | view }   
[ WITH <dml_trigger_option> [ ,...n ] ]  
{ FOR | AFTER | INSTEAD OF }   
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }   
[ WITH APPEND ]  
[ NOT FOR REPLICATION ]   
AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME <method specifier [ ; ] > }  
 
<dml_trigger_option> ::=  
    [ ENCRYPTION ]  
    [ EXECUTE AS Clause ]  
 
<method_specifier> ::=  
    assembly_name.class_name.method_name
 
//DDL 触发器语法：
 
CREATE [ OR ALTER ] TRIGGER trigger_name   
ON { ALL SERVER | DATABASE }   
[ WITH <ddl_trigger_option> [ ,...n ] ]  
{ FOR | AFTER } { event_type | event_group } [ ,...n ]  
AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier >  [ ; ] }  
 
<ddl_trigger_option> ::=  
    [ ENCRYPTION ]  
    [ EXECUTE AS Clause ]
 
//登陆触发器语法：
CREATE [ OR ALTER ] TRIGGER trigger_name   
ON ALL SERVER   
[ WITH <logon_trigger_option> [ ,...n ] ]  
{ FOR| AFTER } LOGON    
AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier >  [ ; ] }  
 
<logon_trigger_option> ::=  
    [ ENCRYPTION ]  
    [ EXECUTE AS Clause ]

10:写一个存储过程包含事务的sql

CREATE PROCEDURE YourProcedure    
AS
BEGIN
    SET NOCOUNT ON;
 
    BEGIN TRY---------------------开始捕捉异常
       BEIN TRAN------------------开始事务
        UPDATE A SET A.names = B.names FROM T1 AS A INNER JOIN T2 AS B ON A.id = B.id
 
        UPDATE A SET A.names = B.names FROM T1 AS A INNER JOIN T2 AS B ON A.TEST = B.TEST
 
    COMMIT TRAN -------提交事务
    END TRY-----------结束捕捉异常
    BEGIN CATCH------------有异常被捕获
        IF @@TRANCOUNT > 0---------------判断有没有事务
        BEGIN
            ROLLBACK TRAN----------回滚事务
        END 
        EXEC YourLogErrorProcedure
    END CATCH
END

 

11:如何一次性往一张表【插入/更新】10条数据,存储过程实现。

UPDATE 会员表
SET 帐户分值 = 帐户分值 + t.分值
FROM 会员表,
(
SELECT 会员ID, SUM(分值) 分值
FROM 问题表
WHERE DateDiff(day,发布时间,GetDate())>10
AND 状态 = 3
GROUP BY 会员ID
) t
WHERE 会员表.ID = t.会员ID
 
INSERT INTO 记录表 (UID,分数,时间)
SELECT 会员ID,分值,GetDate()
FROM (
SELECT 会员ID, SUM(分值) 分值
FROM 问题表
WHERE DateDiff(day,发布时间,GetDate())>10
AND 状态 = 3
GROUP BY 会员ID
) t
 
UPDATE 问题表
SET 状态 = 4
WHERE DateDiff(day,发布时间,GetDate())>10
AND 状态 = 3

12:分页的实现方式？至少写3种

第一种：使用not in，select top 方法：
select top 5 * from T_user where ID not in(select top (3-1)*5 id from T_user order by ID)
 
说明：select top 页大小 [要查询的字段名称] from 表名 where ID not in(select top （当前页数-1）*页大小 id from 表名 order by ID)
第二种：使用select top ,max方法：
select top 5 * from T_user where(ID>=(select MAX(id)from(select top (3-1)*5+1 ID from T_user order by ID)as t))order by ID
说明:select top 页大小 [要查询的字段名称] from 表名 where(ID>=(select Max(id)from(select top(当前页数-1)*页大小+1) ID from 表名 order by ID)as t)order by ID)
第三种：如果SQLServer是2005及以上版本，可以使用ROW_NUMBER()函数进行分页：
select * from
(
select *, row_number()over(order by id) as num from T_user
)as t where t.num between (3-1)5+1 and 35
说明：select * from(select [要查询的字段名称], row_number()over(order by id) as num from 表名 )as t where t.num between (当前页-1)页大小+1 and 当前页页大小

 

13:写一个包含连接和分组,并且根据某个字段拼接的sql

select id,name,sum(num) count from st group by id,name;

 

14:写一个包含having写法的sql

SELECT deptno, AVG(sal) mysal FROM emp GROUP by deptno;

15:写一个包含连接和分组,排序的sql

select a,b,rank() over(partition by a order by b ) nmb from test