SQLServer学习笔记系列12
一.写在前面的话
这个sql学习系列，今天准备告一段落，虽然短短的十几篇文章，深刻感受到将学习的东西记录下来，是需要一种坚持！
这些东西只有反复的学习吸收，最终沉淀下来的才是属于自己的知识。也是提醒自己，今后的日子更要有计划，转眼又是7月份了，
时间不等人，岁月不饶人！坚持自己的计划，坚持向往的东西，踏实学习，因为自己不会的还太多，那些大牛还在学习，
我就更没理由逃避！也希望结交一些朋友，一起讨论技术，一起学习，一起进步！
二.触发器
触发器是一种特殊类型的存储过程，不能被显示的执行。它所监控的是对某一个表的操作，然后对应的执行相应的sql语句。
常见的触发器有三种：分别应用于Insert , Update , Delete 事件。触发器没有参数。 分为：after触发器instead of 触发器。
实例:审核货运公司表，表发生了操作以后，记录日志。用触发器监控。
1.创建日志表，记录对货运公司的操作：
1 USE TSQLFundamentals2008; 2 IF OBJECT_ID('shipper_log') IS NOT NULL 3 DROP TABLE shipper_log; 4 CREATE TABLE shiiper_log 5 ( 6 logId INT IDENTITY(1,1) PRIMARY KEY, 7 opdate datetime DEFAULT GETDATE(), 8 opuser nvarchar(200), 9 op nvarchar(200), 10 shipname nvarchar(200), 11 shipphone nvarchar(200) 12 )
2.创建审核货运公司的触发器：
1 2 CREATE TRIGGER tg_ship 3 ON sales.shippers AFTER INSERT ,DELETE,UPDATE 4 as 5 INSERT INTO dbo.shiiper_log 6 ( opdate , 7 opuser , 8 op , 9 shipname , 10 shipphone 11 ) 12 SELECT GETDATE() , -- opdate - datetime 13 CURRENT_USER , -- opuser - nvarchar(200) 14 N'插入' , -- op - nvarchar(200) 15 companyname, -- shipname - nvarchar(200) 16 phone -- shipphone - nvarchar(200) 17 FROM INSERTED;
3.创建完成触发器以后，我们可以查看一下记录货运公司日志表里面的内容：
1 SELECT * FROM dbo.shiiper_log;
可以看到数据为空，当我们向货运公司插入一条记录如何了？
1 INSERT INTO Sales.Shippers 2 ( companyname, phone ) 3 VALUES ( N'Isoftstone', -- companyname - nvarchar(40) 4 N'15377541070' -- phone - nvarchar(24) 5 )
插入以后，我们再查看一下多货运公司操作的记录表：
1 SELECT * FROM dbo.shiiper_log;
我们看到插入一条记录以后，执行消息里面受影响行数有两条：
我们继续查询下日志表记录：
1 SELECT * FROM dbo.shiiper_log;
这就实现了对货运公司操作的监控，从日志表里面就可以看到货运公司表里面操作的记录。
三.获取标识问题
标识列有利于相当于一个特殊标记，我们可以根据标识列很容易的去查看数据。
（1）获取系统提供的标识值，整个系统范围内。
1 INSERT INTO Sales.Shippers 2 ( companyname, phone ) 3 VALUES ( N'Isoftstone', -- companyname - nvarchar(40) 4 N'15377541070' -- phone - nvarchar(24) 5 ) 6 7 --整个系统范围内 8 SELECT @@IDENTITY;
我们先向货运公司表里面插入一条记录，然后查询系统的标识值，由于货运公司表做了触发器处理，所以插入一条记录时候，同时向货运公司
日志表里面也插入了一条记录。所以我们在查询系统当前的标识值时，查询到的是最新的系统范围内的标识值。
（2）获取当前作用域中最新的的标识值。
1 --当前作用域内最新的标识值 2 SELECT SCOPE_IDENTITY();
通过查询货运公司表可以看到当前作用域内最大的标识值为8.
这个学习系列写完，让自己也学会了很多，知道做一件事情坚持下去，才会让自己成长！
脚踏实地，一步一步踏踏实实走下去，相信越努力越幸运！后续会继续学习，
永远相信美好的事情即将发生！希望各位大牛给出指导，不当之处虚心接受学习！谢谢！
这里分享下这个系列所写的sql脚本。
select top 10 * from A where ID not in(select top 30 ID from A order by ID asc) order by ID asc select top 10 * fron A where ID> (select Max(ID) from (select top 30 ID from A order by ID)as t) order by ID asc select top 10 * from A a1 WHERE NOT EXISTS (SELECT * from (SELECT TOP 30 * FROM A ORDER BY id asc) a2 WHERE a2.id =a1.id ) select top(20) percent * from hr.employees select count(*) as N'总人数' from hr.employees select top 5 with ties orderid,orderdate from sales.orders order by orderdate desc select firstname,lastname ,count(*) over() as N'总人数' from hr.employees select orderid,custid,sum(val) over (partition by custid) as N'顾客消费总额', sum(val) over() as N'订单总额' from sales.ordervalues select country,ntile(3) over (order by country) as N'ntile分组',dense_rank() over(order by country) as N'dense_rank排名', lastname,firstname from hr.employees order by country select lastname,firstname,country,row_number() over( partition by country order by country) as N'排名' from hr.employees select * from sales.ordervalues where val>=1000 and val<=2000 select * from sales.ordervalues where val between 1000 and 2000 select * from sales.ordervalues where custid=1 or custid=2 or custid=9 select * from sales.ordervalues where custid in(1,2,9) select * from Hr.employees where lastname like '%a%' declare @t char(10); set @t='hello'; set @t=isnull(@t,'')+'world'; print datalength(rtrim(@t) select productname,replace(productname,'Product','产品名') from production.products select productname,stuff(productname,8,1,'::::') from production.products select productname,upper(productname),lower(productname) from production.products declare @s char(10); set @s='hello'; select datalength(rtrim(@s)); print len(@s); select firstname,lastname, case region when 'WA' then '华盛顿地区' else '其他地区' end from hr.employees select firstname,lastname,region from hr.employees select firstname,lastname, case when region ='WA' then '华盛顿地区' when region is null then '未知地区' else '其他地区' end from hr.employees select * from sales.orders where orderdate>'20080301' select * from sales.orders where orderdate>cast('20080301' as datetime) select datepart(year,getdate()) as N'年份', datepart(month,getdate()) as N'月份', datepart(day,getdate()) as N'日', datepart(hour,getdate()) as N'时', datepart(minute,getdate()) as N'分', datepart(second,getdate()) as N'秒' select datepart(dayofyear,getdate()) as N'一年中的第几天', datepart(weekday,getdate()) as N'一星期中第几天', datepart(week,getdate()) as N'今年的第几周' select dateadd(day,20,getdate()) as N'20天后的是什么日子', datediff(year,'19491001',getdate()) as N'祖国成立这么多年啦', datediff(year,'19911002',getdate()) as N'屌丝多大啦' select convert(nvarchar,getdate(),112) as N'转化后的形式', left( convert(nvarchar,getdate(),112),6) as N'取出年月' select orderid,custid,empid from sales.orders select * from hr.employees; select * from sales.shippers; select a.*,b.* from hr.employees a cross join sales.shippers b; select * from production.categories select * from production.products select a.categoryid,a.categoryname,b.productid,b.productname from production.categories a inner join production.products b on a.categoryid=b.categoryid; select * from sales.customers select * from sales.orders select a.custid,b.custid,a.contactname,a.fax, count(b.orderid) as N'顾客订单数量' from sales.orders b right join sales.customers a on a.custid=b.custid group by a.custid ,a.fax,a.contactname,b.custid order by count(b.custid); select distinct orderdate,count(*) as N'每日订单量' from sales.orders where orderdate between '20080101' and '20081231' group by orderdate create table nums ( n int ); select * from nums; declare @i int; set @i=0; while @i<400 begin set @i=@i+1; insert into nums(n) values(@i); end select dateadd(day,f.n,'20071231'),count(orderid) as N'每日订单数量' from nums f left join sales.orders m on dateadd(day,f.n,'20071231')= m.orderdate group by dateadd(day,f.n,'20071231') order by dateadd(day,f.n,'20071231') select birthdate,lastname from hr.employees where birthdate= ( select max(birthdate) from hr.employees ) select * from Sales.OrderValues select custid,contactname,country from sales.customers where custid= ( select custid from Sales.OrderValues where val= ( select max(val) as N'最贵订单' from Sales.OrderValues ) ) select * from sales.customers SELECT distinct country from sales.customers where country not in ( select country from production.suppliers ) select custid, count(*) as N'订单数量' from sales.orders group by custid order by custid select distinct custid,count(*) over (partition by custid) as N'订单数量' from sales.orders select n.custid,n.contactname, ( select count(*) from sales.orders m where m.custid=n.custid ) as N'订单数量' from sales.customers n select distinct m.country from sales.customers m where not exists ( select n.country from production.suppliers n where n.country= m.country ) select distinct ( select max(custid) from sales.orders m where m.custid< n.custid ) as N'前一个订单',n.custid as N'当前订单', ( select min(custid) from sales.orders p where p.custid> n.custid ) as N'后一个订单' from sales.orders n select n.orderyear, ( select sum(qty) from Sales.OrderTotalsByYear m where m.orderyear<=n.orderyear ) as N'累计订单数量' from Sales.OrderTotalsByYear n order by n.orderyear; SELECT * FROM ( SELECT custid,COUNT(*) OVER(PARTITION BY country) FROM Sales.Customers ) t(custid,顾客数量) DECLARE @country NVARCHAR(300); SET @country='UK'; WITH USE_Customers(公司名,国家名) AS ( SELECT companyname ,country FROM Sales.Customers WHERE country=@country ) SELECT * FROM USE_Customers SELECT YEAR(orderdate) AS N'年度',custid,COUNT(*) AS N'订单数量' FROM Sales.Orders GROUP BY YEAR(orderdate),custid HAVING COUNT(*) >10; --(1) SELECT YEAR(orderdate) AS orderyear,custid FROM Sales.Orders --(2) SELECT orderyear,custid,COUNT(*) AS N'订单数量' FROM ( SELECT YEAR(orderdate) AS orderyear,custid FROM Sales.Orders ) AS t1 GROUP BY orderyear,custid --(3) SELECT orderyear,custid,ordercount FROM ( SELECT orderyear,custid,COUNT(*) AS ordercount FROM ( SELECT YEAR(orderdate) AS orderyear,custid FROM Sales.Orders ) AS t1 GROUP BY orderyear,custid ) AS t2 WHERE ordercount >10 WITH yearorder01 AS ( SELECT YEAR(orderdate) AS orderyear,custid FROM Sales.Orders ), yearorder02 AS ( SELECT orderyear,custid,COUNT(*) AS ordercount FROM yearorder01 GROUP BY orderyear,custid ), yearorder03 AS ( SELECT orderyear,custid,ordercount FROM yearorder02 WHERE ordercount>10 ) SELECT * FROM yearorder03 SELECT pre_orderyear,now_orderyear,pre_custcount,now_custcount, (now_custcount-pre_custcount) AS N'顾客数量差' FROM ( SELECT YEAR(orderdate) AS now_orderyear,COUNT(DISTINCT custid) AS now_custcount FROM Sales.Orders GROUP BY YEAR(orderdate) ) AS t1 LEFT JOIN ( SELECT YEAR(orderdate) AS pre_orderyear,COUNT(DISTINCT custid) AS pre_custcount FROM Sales.Orders GROUP BY YEAR(orderdate) ) AS t2 ON t1.now_orderyear=t2.pre_orderyear+1; WITH custcount AS ( SELECT YEAR(orderdate) AS orderyear,COUNT(DISTINCT custid) AS custcount FROM Sales.Orders GROUP BY YEAR(orderdate) ) SELECT t1.orderyear AS nowYear,t2.orderyear AS preYear,t1.custcount AS nowcount,t2.custcount AS precount, (t1.custcount-t2.custcount) AS N'顾客数量差' FROM custcount t1 LEFT JOIN custcount t2 ON t1.orderyear=t2.orderyear+1; SELECT t1.empid,t1.mgrid,t1.lastname,t2.empid,t2.lastname FROM HR.Employees t1 LEFT JOIN hr.Employees t2 ON t1.mgrid =t2.empid SELECT * FROM HR.Employees WHERE mgrid in (SELECT empid FROM hr.Employees WHERE mgrid=2 ) DECLARE @mgrid INT; SET @mgrid=2; WITH Emplist AS ( --此处为起点，执行一次 SELECT empid,lastname,mgrid FROM HR.Employees WHERE mgrid=@mgrid UNION ALL --递归开始 SELECT e.empid,e.lastname,e.mgrid FROM HR.Employees e INNER JOIN Emplist m ON e.mgrid=m.empid ) SELECT * FROM Emplist CREATE VIEW USA_cusomers AS ( SELECT * FROM sales.customers WHERE country='USA' ) SELECT custid,country FROM dbo.USA_cusomers; DROP VIEW dbo.USA_cusomers; SELECT country FROM Sales.Customers UNION SELECT country FROM hr.Employees; SELECT country FROM Sales.Customers intersect SELECT country FROM hr.Employees; SELECT country FROM Sales.Customers EXCEPT SELECT country FROM hr.Employees; IF OBJECT_ID('dbo.orders','U') IS NOT NULL DROP TABLE dbo.orders; CREATE TABLE dbo.orders ( orderid int NOT NULL PRIMARY KEY, empid int NOT NULL, custid int NOT NULL, orderdate datetime, qty int ); INSERT INTO dbo.orders(orderid,empid,custid,orderdate,qty) VALUES (30001,3,1,'20070802',10), (30002,2,4,'20070601',20), (10001,4,5,'20070802',30), (20001,5,2,'20070802',40), (40001,3,2,'20070802',50), (30006,5,6,'20070802',50), (30008,4,8,'20070802',60), (60001,6,1,'20070802',70) SELECT * FROM dbo.orders SELECT empid,SUM(qty) AS N'顾客消费金额' FROM dbo.orders GROUP BY empid; SELECT empid, SUM(CASE when empid=2 THEN qty end) AS N'2号顾客消费金额', SUM(CASE when empid=3THEN qty end) AS N'3号顾客消费金额', SUM(CASE when empid=4 THEN qty end) AS N'4号顾客消费金额', SUM(CASE when empid=5 THEN qty end) AS N'5号顾客消费金额', SUM(CASE when empid=6 THEN qty end) AS N'6号顾客消费金额' FROM dbo.orders GROUP BY empid; SELECT empid,[1],[2],[4],[6],[8] FROM ( --只返回pivot中用到的列 SELECT empid,qty,custid FROM dbo.orders ) AS t PIVOT ( SUM(t.qty) FOR t.custid IN ([1],[2],[4],[6],[8])--做列名称 ) AS P USE TSQLFundamentals2008; SELECT * FROM sales.Shippers INSERT INTO Sales.Shippers ( companyname, phone ) VALUES ( N'顺风', -- companyname - nvarchar(40) N'0277665555' -- phone - nvarchar(24) ), (N'申通', N'027888223'), ( N'中通', N'0274433332' ) DELETE FROM Sales.Shippers WHERE shipperid=6 SELECT * FROM sales.Shippers BEGIN TRANSACTION; DELETE FROM Sales.Shippers WHERE shipperid=4; DELETE FROM Sales.Shippers WHERE shipperid=5; COMMIT; BEGIN TRANSACTION; UPDATE Sales.Shippers SET companyname='abc' WHERE shipperid=1; UPDATE Sales.Shippers SET companyname='XYZ' WHERE shipperid=2; COMMIT; KILL 53 USE TSQLFundamentals2008; BEGIN TRANSACTION; UPDATE Production.Products SET unitprice=unitprice+1 WHERE productid=2; --TSQL编程 --定义变量 DECLARE @s INT; SET @s=10; PRINT @s; DECLARE @str NVARCHAR; SET @str ='Hello World'; PRINT @str; DECLARE @m NVARCHAR(100); SELECT @m=99; PRINT @m; DECLARE @sum INT; SELECT @sum=COUNT(*) FROM Sales.Customers; PRINT @sum; DECLARE @mi DATETIME; SET @mi=DATEPART(MINUTE,GETDATE()); IF(@mi>10) PRINT '该睡觉了！'; ELSE PRINT '继续学习！' DECLARE @sumadd int; DECLARE @k INT; SET @k=0; SET @sumadd=0; WHILE @k<100 BEGIN SET @K=@K+1; SET @sumadd=@sumadd+@k; END PRINT @sumadd; SELECT companyname FROM Sales.Customers; DECLARE @name NVARCHAR(100); SELECT @name=companyname FROM Sales.Customers; PRINT @name; --1.声明游标,基于查询 DECLARE c CURSOR FOR SELECT companyname FROM Sales.Customers; DECLARE @name NVARCHAR(100); --2.在使用时候,必须打开游标 OPEN c; --3.从游标中读取数据,每次可以读取出来一条数据 FETCH NEXT FROM c INTO @name; --4.注意fetch,并不一定能获得实际的数据 WHILE @@fetch_status=0 BEGIN PRINT @name; FETCH NEXT FROM c INTO @name; END; --5.游标使用完成以后,一定要关闭 CLOSE c; --6.释放游标 DEALLOCATE c; --存储过程 CREATE PROCEDURE ModifyPrice ( @num money ) AS UPDATE Production.Products SET unitprice=unitprice+@num; CREATE PROCEDURE GetCustomersCount ( @count int OUTPUT ) AS DECLARE @num INT; SELECT @num=COUNT(*) FROM Sales.Customers; --传出 SET @count=@num; go --必须使用变量来保存传出的参数 DECLARE @myCount int; --前面是参数中定义的传出参数 --后面是我们定义的用来保存输出结果的变量 EXEC GetCustomersCount @count=@myCount OUTPUT; PRINT @myCount; --创建用户 CREATE PROCEDURE CreateUser ( @username nvarchar(100) ) AS DECLARE @namelen INT; SET @namelen=LEN(@username); IF @namelen>5 RETURN 0 ELSE RETURN 1 ; GO --定义变量保存结果 DECLARE @ReturnValue INT; EXEC @ReturnValue=dbo.CreateUser @username = N'liupeng' -- nvarchar(100) PRINT @ReturnValue; --创建函数 CREATE FUNCTION Getminnutes ( @datevalue datetime --传入参数 ) --函数可以直接返回一个值 RETURNS int AS begin --函数体 DECLARE @mi INT; SET @mi=DATEPART(MINUTE,@datevalue); RETURN @mi; END; SELECT dbo.Getminnutes(GETDATE()) --创建数据库testDB if DB_ID('testDB') is not NULL DROP DATABASE testDB; CREATE DATABASE testDB ; go IF OBJECT_ID('testTable') IS NOT NULL DROP TABLE testTable; CREATE TABLE testTable ( id INT IDENTITY(1,1) PRIMARY KEY NOT NULL, userName NVARCHAR(200) NOT NULL, userPWD NVARCHAR(200) NOT NULL, userPhone NVARCHAR(200) NOT NULL ) go --插入数据 set identity_insert testTable on --设置为on时，可以向标识列中插入 declare @count int set @count=1 while @count<=322446 begin insert into testTable(id,userName,userPWD,userPhone) values(@count,'liupeng','liupeng_IT','@liupengwuhan@gmail.com') set @count=@count+1 end set identity_insert testTable off SELECT * FROM testTable create procedure proc_pagedFenye_with_selectMax --利用select top and select max(列) ( @pageIndex int, --页索引 @pageSize int --页记录数 ) as begin set nocount on; declare @timediff datetime declare @sql nvarchar(500) select @timediff=Getdate() set @sql='select top '+str(@pageSize)+' * From testTable where(ID>(select max(id) From (select top '+str(@pageSize*@pageIndex)+' id From testTable order by ID) as TempTable)) order by ID' execute(@sql) select datediff(ms,@timediff,GetDate()) as 查询时间 set nocount off; END EXEC proc_pagedFenye_with_selectMax 10,10 create procedure proc_pagedFenye_with_notin --利用select top and select not in ( @pageIndex int, --页索引 @pageSize int --每页记录数 ) as begin set nocount on; declare @timediff datetime --耗时 declare @sql nvarchar(500) select @timediff=Getdate() set @sql='select top '+str(@pageSize)+' * from testTable where(ID not in(select top '+str(@pageSize*@pageIndex)+' id from testTable order by ID ASC)) order by ID' execute(@sql) --因select top后不支技直接接参数，所以写成了字符串@sql select datediff(ms,@timediff,GetDate()) as 查询时间 set nocount off; END EXEC proc_pagedFenye_with_notin 10,10 create procedure proc_pagedFenye_with_Rownumber --利用SQL 2005中的Row_number() ( @pageIndex int, @pageSize int ) as begin set nocount on; declare @timediff DATETIME; select @timediff=getdate() select * from (select *,Row_number() over(order by ID asc) as IDRank from testTable) as IDWithRowNumber where IDRank>@pageSize*(@pageIndex) and IDRank<@pageSize*(@pageIndex+1) select 3 as 查询时间 set nocount off; END EXEC proc_pagedFenye_with_Rownumber 10,10 select * from (select *,Row_number() over(order by ID asc) as IDRank from testTable) as IDWithRowNumber where IDRank>10*(10-1) and IDRank<10*(10+1) SELECT* FROM testTable USE TSQLFundamentals2008; SET NOCOUNT ON; SELECT TOP 5 * FROM Sales.Customers --触发器 USE TSQLFundamentals2008; IF OBJECT_ID('shipper_log') IS NOT NULL DROP TABLE shipper_log; CREATE TABLE shiiper_log ( logId INT IDENTITY(1,1) PRIMARY KEY, opdate datetime DEFAULT GETDATE(), opuser nvarchar(200), op nvarchar(200), shipname nvarchar(200), shipphone nvarchar(200) ) CREATE TRIGGER tg_ship ON sales.shippers AFTER INSERT ,DELETE,UPDATE as INSERT INTO dbo.shiiper_log ( opdate , opuser , op , shipname , shipphone ) SELECT GETDATE() , -- opdate - datetime CURRENT_USER , -- opuser - nvarchar(200) N'插入' , -- op - nvarchar(200) companyname, -- shipname - nvarchar(200) phone -- shipphone - nvarchar(200) FROM INSERTED; SELECT * FROM dbo.shiiper_log; INSERT INTO Sales.Shippers ( companyname, phone ) VALUES ( N'Isoftstone', -- companyname - nvarchar(40) N'15377541070' -- phone - nvarchar(24) ) --整个系统范围内 SELECT @@IDENTITY; --当前作用域内最新的标识值 SELECT SCOPE_IDENTITY(); select * FROM Sales.Shippers;
