SQL脚本

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

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

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


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

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

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

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

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

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

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

6.写一个视图的sql

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

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

 

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值