sql 小全
前些日子sql用到哪里写到哪里,乱七八糟,今天整理了一下,以作备份(虽然开通博客已经八个月了,但是今天还是第一次发表博文,好紧张啊~~)
--2014.08.27号整理sql语句
1:进入数据库
use [数据库名]
eg: use [dev]
2:创建表
create table 表名( [ID] int identity(1,1) primary key, [列名] 数据类型 约束 , )
eg:
create table atblTest1( [ID] int identity(1,1) primary key, --从1开始每次增加1 [Key] varchar(200) unique , [Value] decimal(18,4) , --小数点后面保留四位小数,数据长18位(不加小数点位) [Unit1] varchar(100), --Message text, --Time datetime --[count] int, )
3:操作现有表/字段
--修改字段数据类型 alter table 表名 alter column 字段名 字段类型 --删除字段出现约束什么错误 ALTER TABLE 表名 DROP CONSTRAINT 默认约束名 GO ALTER TABLE 表名 DROP COLUMN 字段名 GO
--获取默认约束名 select name from sysobjects where id=(select cdefault from syscolumns where name='字段名' and id = (select id from sysobjects where name = '表名'))
--添加字段 alter table 表名 add 字段名 字段类型 --不允许空字符: alter table 表名 add 新字段 字段类型 not NULL --允许空字符: alter table 表名 add 新字段 字段类型 NULL --修改字段 exec sp_rename '表名.原列名','新列名','column' --修改表名 exec sp_rename '旧表名', '新表名'
eg:
exec sp_rename 'atblTest1', 'atblTest' exec sp_rename 'atblTest.Unit1','Unit','column' alter table atblTest alter column [Unit] varchar(200) alter table atblTransactions add IsInsertAccounting bit alter table atblMembers add AccountUserId int alter table atblAccounts add PendingBalance money
4:添加数据
insert into 表名(列名1,列名2,列名3) values(数据1,数据2,数据3)
eg:
insert into atblTest values('LOBcheckFee',3,'dollars')
5:更改数据
update 表名 set 列名 = 列名数据 where id=标识数据
eg:
update atblTest set [Unit] ='dollar' where [ID]=1
6:创建触发器 (就不连贯着来发例子了啊)
create trigger 触发器名 --创建触发器名字 on 触发器所在表 --在这个表中创建触发器 for Update -- 因为哪个事件而触发(insert ,update,delete) as --事件触发后所做的事情 if Update(该表字段) --如果修改XX字段 begin 引发的sql操作 end
eg:(主要是如果ablLeads表QuantityOnHand数量改变,就会将改变的过程存到另一个表中:1-->0)
create trigger trQuantityOnHand on atblLeads for Update as if Update(QuantityOnHand) begin declare @QuantityOnHandOld int, @QuantityOnHandNew int,@LeadID int; select @QuantityOnHandNew=QuantityOnHand,@LeadID=LeadID from inserted; select @QuantityOnHandOld=QuantityOnHand from deleted; if(@QuantityOnHandOld != @QuantityOnHandNew) begin insert into atblTrigger([Type],[Time],[Result],[LeadID]) values('QuantityOnHand',getdate(),convert(varchar, @QuantityOnHandOld)+' -> '+convert(varchar,@QuantityOnHandNew),@LeadID); end end
7:删除触发器
if(object_id('触发器名称') is not null) drop trigger 触发器名称 if(object_id('trQuantityOnHand') is not null) drop trigger trQuantityOnHand
8:对表数据分页
select * from ( select * , row_number() over ( order by 某种规律的字段名(id) ) as rownum from 表名 )DATA where DATA.rownum>开始页码*每页列显数量 and DATA.rownum<开始页码*每页列显数量+每页列显数量
eg:列显第六页的数据即600-610之间的数据
select * from ( select * , row_number() over ( order by LEADID ) as rownum from atblLeads )DATA where DATA.rownum>60*10 and DATA.rownum<60*10+10
9:删除表
drop table 表名
-----如果表存在就drop掉----------- if exists (select 1 from sysobjects where id = object_id('表名') and type = 'U') drop table 表名 go
--eg:
if exists (select 1 from sysobjects where id = object_id('[dbo].[db3_IndexingDetails]') and type = 'U') drop table [dbo].[db3_IndexingDetails] go
注意:
--truncate 删除内容,并释放空间,并不删除表结构,删除标识列,标识列重新从1开始
--delete 删除内容,不释放空间,不删除表结构,不删除标识列,标识列继续增加
--drop 直接删除表
10:控制sql修改数量
eg:
begin tran update atblOrders set OrderStatus = 2 where EbayOrderID = '131235536269-0' if(@@ROWCOUNT>1) --@@ROWCOUNT 是受影响的行数 begin rollback tran print 'sql超过了指定的受影响行数,将不会执行该语句' end else if(@@ROWCOUNT=0) begin print '执行失败' end else begin commit tran print '恭喜你执行完成' end
11:获得行数
select count(*) from 你的表名
12:获得列名以及详细数据
select * from syscolumns where id = object_id('表名')
eg:
select * from syscolumns where id = object_id('atblUsers')
13:获得列名数量
select count(*) from syscolumns where id=object_id('你的表名')
14:读取库中的所有表名
select name from sysobjects where xtype='u'
15:读取指定表的所有列名
select name from syscolumns where id=(select max(id) from sysobjects where xtype='u' and name='表名')
16:获取字段类型
select t.name from sysobjects o,syscolumns c,systypes t where o.id=c.id and c.usertype=t.usertype and o.name='表名' and c.name='列名'
17:Select语句 区分查询数据的大小写
--不区分大小写(默认不区分)
select top 10 * from atblUsers where [Password]='sunflower134' COLLATE Chinese_PRC_CI_AS
eg:--区分大小写 针对某个字段
select top 10 * from atblUsers where [Password]='sunflower134' COLLATE Chinese_PRC_CS_AS AND Status=1
18:数据查询
eg:
select TransactionID,AccountID,TransactAmount,TransactDate,TransactType, Credit = CASE WHEN TransactAmount = 0 THEN 0 WHEN TransactAmount > 0 THEN TransactAmount END, --(添加的列显字段) Debit = CASE WHEN TransactAmount = 0 THEN 0 WHEN TransactAmount < 0 THEN -TransactAmount END FROM [atblAccounting] WHERE [AccountID] = 53 order by TransactDate DESC ,TransactType desc
select Sum(TransactAmount) from atblAccounting where AccountID=54
select TOP 200 * from atblAccounting order by TransactionID DESC
select b.IsInsertAccounting, * from atblAccounting a inner join atblTransactions b on a.ebayOrderID=b.eBayOrderID and a.ItemID = b.ItemID
select count(*) from atblUsereBayAuthToken
19:删除表中的所有数据
DELETE FROM 表名
20:查询SQLserver的详细版本信息
select @@VERSION
21:递归查找 根据父节点查找子节点数据/子节点查找父节点数据
根据这张图片,你大概已经知道表是什么结构的了吧。
a)-递归查找数据 这是从父节点到子节点的递归(需要限制大小)
with cte as( select CategoryID,CategoryName,CategoryParentID ,CategoryLevel from atbleBayCategories where CategoryID ='6000' union all select a.CategoryID,a.CategoryName,a.CategoryParentID,a.CategoryLevel from atbleBayCategories a inner join cte b on a.CategoryParentID = b.CategoryID ) select top 100 * from cte
b)--子节点到父节点(逆向递归)
with temp as( select * from atbleBayCategories where CategoryID ='33636' union all select a.* from atbleBayCategories a inner join temp b on a.CategoryID = b.CategoryParentID where a.CategoryID != b.CategoryID ) select * from temp
特别注意
由于大家表结构可能一致,数据也看似一致可能会导致sql运行错误(就因为之前没有加上这句:a.CategoryID != b.CategoryID,导致sql死循环,执行不成功,楼主当时都准备改表结构了..)
22:设置表中A字段数据=B字段数据:
情景:当用户修改商品信息,需要将修改的数据显示在最前方,假设数据库没有DateModify这个字段,你需要添加这个字段,并且设置这些DateModify数据:
Update tableName set DateModify = DateCreate
23:复制表A数据到表B中
1.INSERT INTO SELECT语句
语句形式为:
Insert into Table2(field1,field2,...) select value1,value2,... from Table1
2.SELECT INTO FROM语句
语句形式为:
SELECT vale1, value2 into Table2 from Table1
要求目标表Table2不存在,因为在插入时会自动创建表Table2,并将Table1中指定字段数据复制到Table2中
24:查询数据库中的存储过程是否存在某文本内容
SELECT ROUTINE_NAME, ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE '%text%' AND ROUTINE_TYPE='PROCEDURE'
查找存储过程包含某文字
select name from sysobjects o, syscomments s where o.id = s.id and text like '%sp_WidgetCrossTabTables%' and o.xtype = 'P'
25:替换表中某列字段
update 表名 set 字段名=replace(字段名,'aaaa','cccc');
eg:
aaaaxxxbbb 变成 ccccxxxbbb
aaaamtbbb 变成 ccccmtbbb
26:获取具体月份的数据
SELECT CreateDate as time, * FROM [EKU_PrimaryData] where DATEPART(YEAR,CreateDate) = 2017 and MONTH(CreateDate) in( 10,11) order by CreateDate
--最近一个月 SELECT DATEADD(month, -1, getdate()) --上个月 select convert(varchar(7),dateadd(month,-1,getdate()),120)
27:partition by 分组
select eBayItemID,Cost,FreeShipping from ( --根据ebayitemid分组,价格从高到低排序 并标上序号 最后取第一行 select row_number() over( partition by eBayItemID order by cost desc) as rn,* from [440294_Iguide].[dbo].[atbleBayItemUSShippingServices] --where ebayitemID =105490 --where len(FileName)>0 ) as t where t.rn=1
28:总数据:db1, 不在db2 ,在db3, 获取数据
with db1 as( select t1.Id, t1.ReferenceNumber, t2.ID as InventoryId, t1.UserName from post_GoAntiques t1 left join EuroLuxProductBE.dbo.pdt_Inventory t2 on t1.ReferenceNumber=t2.ITEM ), db2 as (--not in select ProductNumber from post_ProductCommon where WebSiteName='GoAntiques' ), db3 as( -- in select min(Id) as Id from post_GoAntiques group by ReferenceNumber ) select * from db1 where db1.ReferenceNumber in ( select ReferenceNumber from db1 EXCEPT --去除db2的数据 SELECT ProductNumber as ReferenceNumber from db2 ) and db1.ID in ( select id from db1 INTERSECT-- 取db3的交集 SELECT id from db3 )
29:sqlserver库占用大小
SELECT DB_NAME(database_id) AS [Database Name],[Name] AS [Logical Name],[Physical_Name] AS [Physical Name],((size * 8) / 1024) AS [Size(MB)],
[differential_base_time] AS [Differential Base Time] FROM sys.master_files
30:一个积分等级表,一个用户积分表。获取用户属于哪个等级: left outer join
select * from [TianYaDemo].[dbo].[Vip_ScoreLogs] left outer join [TianYaDemo].[dbo].[Vip_ScoreGrade] as sg on TotalValue between MinValue and MaxValue
31:行列转换
create table tb ( Name varchar(10) , Subject varchar(10) , Result int ); insert into tb(Name , Subject , Result) values('张三' , '语文' , 74) insert into tb(Name , Subject , Result) values('张三' , '数学' , 83) insert into tb(Name , Subject , Result) values('张三' , '物理' , 93) insert into tb(Name , Subject , Result) values('李四' , '语文' , 74) insert into tb(Name , Subject , Result) values('李四' , '数学' , 84) insert into tb(Name , Subject , Result) values('李四' , '物理' , 94) select * from tb select name 姓名, max(case subject when'语文'then result else 0 end) 语文, max(case subject when'数学'then result else 0 end) 数学, max(case subject when'物理'then result else 0 end) 物理 from tb --where 数学='83' group by name
32:批量更数据库字段的部分数据
update 表的名称 set 此表要替换的字段名=REPLACE(此表要替换的字段名, '原来内容', '新内容') update [eku_WholeData_{0}] set Voltage =REPLACE(Voltage, Voltage, STUFF(Voltage, charindex('\"key',Voltage), 45, ''))
where Voltage like '%\"key%'
33:统计库中每个表的行数
SELECT a.name, b.rows FROM sysobjects AS a INNER JOIN sysindexes AS b ON a.id = b.id WHERE (a.type = 'u') AND (b.indid IN (0, 1)) ORDER BY b.rows DESC
34:插入10w条数据
INSERT INTO Department(name,[Company],groupname) VALUES('销售部','中国你好有限公司XX分公司','销售组') GO 100000
35:输出sql执行的时间
set statistics time on select * from dbo.EKU_PrimaryDataFiles set statistics time off
36:sql server 规则使用
创建规则->将规则绑定到表上-->测试规则(插入不满足的条件测试)-->取消绑定规则
--创建规则:价格要在0-1000以内 go create rule rule_Price as @price >0 and @price<1000 --查询表 select * from books --将规则绑定到表上 exec sp_bindrule 'rule_Price','books.book_price' --这里insert的数据不满足规则,会报错 insert into books values('三国演义',1001,'罗贯中') --取消绑定 exec sp_unbindrule 'books.book_price' --再去执行即可insert成功 insert into books values('三国演义',1001,'罗贯中') --删除规则 drop rule rule_price
37:默认值设置
设置默认值 -->将默认值绑定到某表某列 --->测试---> 取消默认值绑定 --->删除默认值
--查询表 select * from books --设置默认值 create default defaultBookAuth as '作者不详' --将默认值绑定到表列 exec sp_bindefault 'defaultBookAuth' ,'books.book_auth' insert into books(book_name) values('不存在的人2019') --查询表 select * from books --取消绑定 exec sp_unbindefault 'books.book_auth' --删除默认值 drop default defaultBookAuth
38:存储过程
--创建有参存储过程
create proc p_BookAdd @Name varchar(30) as insert into books(book_name)values(@Name) go --sqlserver 执行存储过程测试,图中给【作者】创建了默认值,所以作者字段有默认值 exec p_BookAdd '测试书名称'
- 创建无参存储过程
--、不带参数的存储过程,获取MyStudentInfo表的所有记录 CREATE proc Proc_GetInfo as select * from MyStudentInfo20190311 go --执行存储过程 exec Proc_GetInfo go
- 创建带输入参数的存储过程
CREATE proc Proc_InsertData @Id int, @Name varchar(16), @Age int, @Gender varchar(2), @Phone varchar(16), @Address varchar(50), @GradeId int, @Score int as begin insert INTO MyStudentInfo20190311 VALUES(@Id,@Name,@Age,@Gender,@Phone,@Address,@Gender,@Score) end
- 创建带默认值的输入参数的存储过程
CREATE proc Proc_InsertDefault @Id int, @Name varchar(16), @Age int, @Gender varchar(2)='1', @Phone varchar(16), @Address varchar(50)='沈阳', @GradeId int, @Score int as begin insert INTO MyStudentInfo20190311 VALUES(@Id,@Name,@Age,@Gender,@Phone,@Address,@Gender,@Score) end
- 创建带输出参数的存储过程(根据输入的ID返回名字)
create proc Proc_Out @Id int, @Name varchar(200) out as begin select @Name=Name from MyStudentInfo20190311 where id=@id end
--sql执行存储过程 declare @Name nvarchar(200) exec Proc_Out 1 , @Name out select @Name as 输出的值
- 存储过程返回多个结果
create proc Proc_Page @StartIndex int, @EndIndex int as begin select COUNT(*) as 总数 from MyStudentInfo20190311 select * from ( SELECT ROW_NUMBER() OVER(ORDER BY Id) AS rowid,* FROM MyStudentInfo20190311 ) temp where temp.rowid between @StartIndex AND @EndIndex end --sql调用存储过程 exec Proc_Page 1,2
39:函数