sql 练习

LTrim--除去字符串左边空格
RTrim--除去字符串左边空格

DECLARE @STR VARCHAR(100)
SET @STR='  this string has left empty'
PRINT 'I'+LTrim(@STR)

DECLARE @STR2 VARCHAR(100)
SET @STR2='this string has left empty  '
PRINT RTrim(@STR2)+'I'

DECLARE @STR3 VARCHAR(100)
SET @STR3='  this string has left empty  '
PRINT 'I'+LTrim(RTrim(@STR3))+'I'--除去两边空格
----------------------------------------------------------------

USE test  --清除表记录的存储过程
GO
CREATE PROCEDURE Table_Truncate
@TableName VARCHAR(100)
AS
EXEC ('TRUNCATE TABLE '+@TableName)
--------------------------------------------------------------
USE test  --创建表的存储过程 DROP PROC Table_Create
SET NOCOUNT ON--默认情况下，存储过程将返回过程中每个语句影响的行数。
     --当 SET NOCOUNT 为 ON 时，不返回计数。当 SET NOCOUNT 为 OFF 时，返回计数。
     --这将删除客户端和服务器之间的一个或多个往返过程。尽管这不是大问题，但它可以为高流量应用程序的性能产生负面影响。
GO
CREATE PROCEDURE Table_Create
@TableName VARCHAR(100),
@ColumnName1 VARCHAR(100),
@ColumnName2 VARCHAR(100)
AS
DECLARE @TName VARCHAR(100)
SET @TName=@TableName
DECLARE @Create VARCHAR(200)
SET @Create=@ColumnName1+' INT PRIMARY KEY IDENTITY(1,1),'
SET @Create=@Create+@ColumnName2+' VARCHAR(100)'
EXEC ('CREATE TABLE '+@TableName+'('+@Create+')')

EXEC Table_Create 'Table1','Id','Name'
--------------------------------------------------------函数
USE test
GO
CREATE FUNCTION F1(@Id INT)   --标量型函数
RETURNS VARCHAR(100)
AS
BEGIN
 DECLARE @Return VARCHAR(100)
 SET @Return=(SELECT Name FROM b WHERE id=@Id)
 RETURN @Return
END
 
USE test
GO
SELECT test.dbo.F1(2)
----------------------------------
USE test  --DROP FUNCTION F2
GO
CREATE FUNCTION F2()   --内嵌表值函数   没有begin end
RETURNS TABLE
AS
 RETURN SELECT ID, Name FROM b

USE test --truncate table b
GO
SELECT NAME FROM test.dbo.F2()

---------------------------------------------触发器
use Test
go
create table a
(
 id int identity(1,1) primary key,
 cardNum varchar(20),
 name varchar(100)
)

insert into a values ('1111','1111')
insert into a values ('2222','2222')
insert into a values ('3333','3333')
insert into a values ('4444','4444')
insert into a values ('5555','5555')
insert into a values ('6666','6666')

use Test
go
create table b
(
 id int identity(1,1) primary key,
 cardNum varchar(20),
 name varchar(100)
)

insert into b values ('1111','aaaa')
insert into b values ('2222','ssss')
insert into b values ('3333','dddd')
insert into b values ('4444','ffff')
insert into b values ('5555','gggg')
insert into b values ('6666','hhhh')

---------------------虚拟表Inserted                       虚拟表Deleted
--在表记录新增时     存放新增的记录                         不存储记录
--修改时           存放用来更新的新记录                   存放更新前的记录
--删除时           不存储记录                             存放被删除的记录

 

USE Test   --drop trigger UpdatecardNum
GO
CREATE TRIGGER UpdatecardNum
ON a  --在a表中创建触发器
FOR UPDATE  --为什么事件触发
AS   --事件触发后所要做的事情
IF UPDATE(cardNum)
BEGIN
 UPDATE b
 SET b.cardNum=i.cardNum
 FROM b,Deleted d,Inserted i
 WHERE b.cardNum=d.cardNum
END

USE Test
GO
CREATE TRIGGER DeleteCardNum
ON a
FOR DELETE
AS
BEGIN
 DELETE b
 FROM b,Deleted d
 WHERE b.cardNum=d.cardNum
END

--------------------------
USE Test
GO
SELECT COUNT(*) AS Rank, a.id,a.name,a.cardNum
FROM a
--WHERE a1.au_lname + a1.au_fname >= a2.au_lname + a2.au_fname
GROUP BY a.id,a.name,a.cardNum
ORDER BY Rank

--给查询结果自定添加编号 该方法只能用于临时表  T T
--SQL提供了一个IDENTITY Function，可以得到标识列值，
--这个函数只能用于SELECT INTO语句，所以我们引入一个临时表
USE Test 
GO
SELECT IDENTITY(INT, 1, 1) AS Rank,a.name,a.cardNum
INTO #tmp FROM a
SELECT * FROM #tmp
DROP TABLE #tmp

-------------------------------给查询的结果添加编号
USE Test 
GO
SELECT ROW_NUMBER()OVER(ORDER BY id) AS ROWINDEX ,* FROM a

USE Test 
GO
SELECT RANK()OVER(ORDER BY id) AS ROWINDEX ,* FROM a

USE Test 
GO
SELECT DENSE_RANK()OVER(ORDER BY id) AS ROWINDEX ,* FROM a