Sqlserver常用SQL 1

日期的处理

---3 Months ago
select CONVERT(VARCHAR(10),DATEADD(M,-3,GETDATE()),112)
--删除前3个月
delete from Sales where outdate>=CONVERT(VARCHAR(10),DATEADD(M,-3,GETDATE()),112)

常用查询：

---经常碰到一些忘记表名称的情况，此时只记得个大概，此时可通过查询系统表Sysobjects找到所要的表名，如要查找包含用户的表名，可通过以下SQL语句实现， 

Select * 
From sysobjects 
Where name like '%user%'

----如果知道列名，想查找包含有该列的表名，可加上系统表syscolumns来实现，如想查找列名中包含有user的所有表名，可通过以下SQL语句来实现 
Select * 
From sysobjects s 
Where Exists( 
Select * 
From syscolumns 
Where ID = s.ID and name like '%user%'
)

---查看所有表名：
select    name    from    sysobjects    where    type='U'

---查询表的所有字段名：
Select name from syscolumns Where ID=OBJECT_ID('表名')

select * from information_schema.tables
select * from information_schema.views
select * from information_schema.columns

----查看所有表名：
select    name    from    MSysObjects    where    type=1    and    flags=0

-----MSysObjects是系统对象，默认情况是隐藏的。通过工具、选项、视图、显示、系统对象可以使之显示出来。 

-----1.按姓氏笔画排序: 
Select * From TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as 

-----2.分页SQL语句 
select * from(select (row_number() OVER (ORDER BY tab.ID Desc)) as rownum,tab.* from 表名 As tab) As t where rownum between 起始位置 And 结束位置 

-----3.获取当前数据库中的所有用户表 
select * from sysobjects where xtype='U' and category=0 

-----4.获取某一个表的所有字段 
select name from syscolumns where id=object_id('表名') 

-----5.查看与某一个表相关的视图、存储过程、函数 
select a.* from sysobjects a, syscomments b where a.id = b.id and b.text like '%表名%' 

-----6.查看当前数据库中所有存储过程 
select name as 存储过程名称 from sysobjects where xtype='P' 

-----7.查询用户创建的所有数据库 
select * from master..sysdatabases D where sid not in(select sid from master..syslogins where name='sa') 
-----或者 
select dbid, name AS DB_NAME from master..sysdatabases where sid <> 0x01 

-----8.查询某一个表的字段和数据类型 
select column_name,data_type from information_schema.columns 
where table_name = '表名' 
-----或者
sp_help 表名
-----9.使用事务 
-----在使用一些对数据库表的临时的SQL语句操作时，可以采用SQL SERVER事务处理，防止对数据操作后发现误操作问题 
-----开始事务 
Begin tran 
Insert Into TableName Values(…) 
-----SQL语句操作不正常，则回滚事务。 
-----回滚事务 
Rollback tran 
-----SQL语句操作正常，则提交事务，数据提交至数据库。 
-----提交事务 
Commit tran 
-----10. 按全文匹配方式查询 
字段名 LIKE N'%[^a-zA-Z0-9]China[^a-zA-Z0-9]%' 
OR 字段名 LIKE N'%[^a-zA-Z0-9]China' 
OR 字段名 LIKE N'China[^a-zA-Z0-9]%' 
OR 字段名 LIKE N'China'

-----11．计算执行SQL语句查询时间 
declare @d datetime 
set @d=getdate() 
select * from SYS_ColumnProperties select [语句执行花费时间(毫秒)]=datediff(ms,@d,getdate()) 

-----12、说明：几个高级查询运算词 
A： UNION 运算符 
UNION 运算符通过组合其他两个结果表（例如 TABLE1 和 TABLE2）并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时（即 UNION ALL），不消除重复行。两种情况下，派生表的每一行不是来自 TABLE1 就是来自 TABLE2。 
B： EXCEPT 运算符 
EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL)，不消除重复行。 
C： INTERSECT 运算符 
INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL)，不消除重复行。

常用修改

 ---修改列名  
 sp_rename 'Dim_Date.Date','Dim_Date','column'  
 --添加单列:  
   
ALTER TABLE 表名 ADD 列名 数据类型   
---添加多列:  
ALTER TABLE 表名 ADD 列名1 数据类型1,列名2 数据类型2  
  
--修改单列数据类型:  
ALTER TABLE 表名 ALTER COLUMN 列名 数据类型  

--修改表内容
  update Dim_Date set Dim_Week=1 where DateKey='2013-12-30'

对表的操作

--新建
CREATE TABLE [dbo].[Dim_Date](
	[DateKey] [nvarchar](18) NULL,
	[Year] [int] NULL,
	[Qu] [int] NULL,
	[QuCN] [varchar](20) NULL,
	[QuEN] [varchar](20) NULL,
	[Month] [int] NULL,
	[MonthCN] [varchar](20) NULL,
	[MonthEN] [varchar](20) NULL,
	[Ten] [int] NULL,
	[TenCN] [varchar](20) NULL,
	[Week] [int] NULL,
	[WeekDay] [varchar](20) NULL,
	[Day] [int] NULL,
	[Date] [date] NULL
)
---删除
drop table Dim_Date


--while循环插入数据
DECLARE @BeginDate DATE;

SELECT @BeginDate = '20100101';
WHILE @BeginDate<='20151231'
BEGIN
INSERT INTO Dim_Date
SELECT 
CONVERT(varchar(10),@BeginDate,120)  AS DateKey,
YEAR(@BeginDate)                                        AS Year,
Datepart(QUARTER,@BeginDate)                            AS Qu,
CASE
WHEN Datepart(QUARTER,@BeginDate)=1 then '第一季度'
WHEN Datepart(QUARTER,@BeginDate)=1 then '第二季度'
WHEN Datepart(QUARTER,@BeginDate)=1 then '第三季度'
ELSE '第四季度'
 END                                                    AS  QuCN,
 CASE
WHEN Datepart(QUARTER,@BeginDate)=1 then 'Q1'
WHEN Datepart(QUARTER,@BeginDate)=1 then 'Q2'
WHEN Datepart(QUARTER,@BeginDate)=1 then 'Q3'
ELSE 'Q4'
 END                                                    AS  QuEN,
 MONTH(@BeginDate)                                      as Month,
 case
 when MONTH(@BeginDate)= 1 then '一月'
 when MONTH(@BeginDate)= 2 then '二月'
 when MONTH(@BeginDate)= 3 then '三月'
 when MONTH(@BeginDate)= 4 then '四月'
 when MONTH(@BeginDate)= 5 then '五月'
 when MONTH(@BeginDate)= 6 then '六月'
 when MONTH(@BeginDate)= 7 then '七月'
 when MONTH(@BeginDate)= 8 then '八月'
 when MONTH(@BeginDate)= 9 then '九月'
 when MONTH(@BeginDate)= 10 then '十月'
 when MONTH(@BeginDate)= 11 then '十一月'
 else '十二月'
 end                                                as MonthCN,
 case
 when MONTH(@BeginDate)= 1 then 'Jan'
 when MONTH(@BeginDate)= 2 then 'Feb'
 when MONTH(@BeginDate)= 3 then 'Mar'
 when MONTH(@BeginDate)= 4 then 'Apr'
 when MONTH(@BeginDate)= 5 then 'May'
 when MONTH(@BeginDate)= 6 then 'Jun'
 when MONTH(@BeginDate)= 7 then 'Jul'
 when MONTH(@BeginDate)= 8 then 'Aug'
 when MONTH(@BeginDate)= 9 then 'Sept'
 when MONTH(@BeginDate)= 10 then 'Oct'
 when MONTH(@BeginDate)= 11 then 'Nov'
 else 'Dec'
 end                                                as MonthEN,
 CASE
 when DATEPART(DAY,@BeginDate)<=10 THEN 1
 WHEN DATEPART(DAY,@BeginDate)>20 THEN 3
 ELSE 2              
 END                                                as Ten,
 CASE
 when DATEPART(DAY,@BeginDate)<=10 THEN '上旬'
 WHEN DATEPART(DAY,@BeginDate)>20 THEN '下旬'
 ELSE '中旬'              
 END                                                as TenCN,
 DATEPART(WEEK,@BeginDate) AS Week,
 DATENAME(WEEKDAY,@BeginDate) as WeekDay,
 Day(@BeginDate) AS Day,
   convert(varchar(10),convert(varchar(10),@BeginDate,120),112) as Date
 SET @BeginDate=DATEADD(DAY,1,@BeginDate);
 end;
 
 ---修改列名
 sp_rename 'Dim_Date.Date','Dim_Date','column'
 --添加单列:
 
ALTER TABLE 表名 ADD 列名 数据类型 
---添加多列:
ALTER TABLE 表名 ADD 列名1 数据类型1,列名2 数据类型2

--修改单列数据类型:
ALTER TABLE 表名 ALTER COLUMN 列名 数据类型

查找重复项

select id, name, memo
from A
where id in (select id from A group by id having count(1) >= 2)

select * from (
SELECT colthno
	  ,sku=colthno+color
  FROM BI_Product
 where  classname1 in('ANTHONY VACCARELLO','JKJY by STELLA','PIERRE BALMAIN','STELLALUNA','WHATFOR')
 )A
 where A.sku in(
 select sku from 
 (SELECT colthno
	  ,sku=colthno+color
  FROM BI_Product
 where  classname1 in('ANTHONY VACCARELLO','JKJY by STELLA','PIERRE BALMAIN','STELLALUNA','WHATFOR')
 ) B group by B.sku  having COUNT(B.sku)>=2
 )

小数：

select area_name,CONVERT(decimal(10,2), sum(nb))/COUNT(distinct nos) upt from db_bi a,BI_sale b
where a.dbno=b.store
and b.outdate>='20130601'
and b.outdate<='20131231'
and a.class2='A'
and b.endprice<>0
group by area_name

 select  sum(nb)/convert(float,COUNT(distinct nos)) UPT2013_ABCO from BI_Sales a,db_bi B  
where a.store=b.dbno  
and b.class2 in('A','B','C','O')  
and endprice <>0  
and outdate>='20130101'  
and outdate<='20131231' 

sudu=cast(qty_sold as decimal(18,2))/case when n1 IS NOT NULL then n1 else case when n2 IS NOT NULL then n2
 else case when n3 IS NOT NULL then n3 else case when n4 is not null then n4 else case when n5 IS not null then n5 end end end end end

查询门店代码长度为5：

select * from db_bi where LEN(dbno_bi)=5

将截断字符串或二进制数据。

语句已终止。 原因 定义的长度和写入的不一致

  insert storeview_user (shop_id,bz1,bz,position,right_rank,shop_pass) values('ss','ss','ss','ss',3,'ss')

修改表的字段长度和非空

  
  alter table storeview_user
     alter column shop_pass  nvarchar(15) NOT NULL

表的权限设置  点击打开链接

关联：

两个表相关联的问题，有销售表和产品表如下，其中销售表的color前两位是颜色后3位是尺码，产品表只要2位的颜色