转自【柳永法】SQL统计：从北京到天津的与从天津到北京的汇总到一起常用sql语句 SQL Server 生成版权信息及测试环境信息

最新推荐文章于 2024-09-15 23:28:35 发布

宸路

最新推荐文章于 2024-09-15 23:28:35 发布

阅读量824

点赞数

分类专栏： SQL SERVER 文章标签： sql sql server 测试数据库 sqlserver 交通

SQL SERVER 专栏收录该内容

33 篇文章 1 订阅

订阅专栏

一、SQL统计：从北京到天津的与从天津到北京的汇总到一起

http://www.yongfa365.com/Item/SQLTongJiCongBeiJingDaoTianJinDeYuCongTianJinDaoBeiJingDeHuiZongDaoYiQi.html

--建表 CREATE TABLE LuFei( id INT, fromaddress VARCHAR(50), toaddress VARCHAR(50), types VARCHAR(50), prices INT ) GO --添加数据 INSERT LuFei SELECT 1,'北京','天津','交通费',100 UNION ALL SELECT 2,'北京','天津','交通费',200 UNION ALL SELECT 3,'天津','北京','交通费',300 UNION ALL SELECT 4,'北京','上海','交通费',400 UNION ALL SELECT 5,'上海','北京','交通费',500 UNION ALL SELECT 6,'朝阳','中关村','交通费',600 UNION ALL SELECT 7,'中关村','朝阳','交通费',700 GO --执行查询 SELECT SUM(prices) AS addprice,COUNT(prices) AS coun, CASE WHEN (UNICODE(fromaddress) - UNICODE(toaddress)) > 1 THEN fromaddress + '<==>' +toaddress ELSE toaddress + '<==>' + fromaddress END AS LineName FROM LuFei WHERE types='交通费' GROUP BY CASE WHEN (UNICODE(fromaddress) - UNICODE(toaddress)) > 1 THEN fromaddress + '<==>' + toaddress ELSE toaddress+ '<==>' + fromaddress END ORDER BY addprice DESC GO SELECT * FROM LuFei lf --删除这个表 DROP TABLE LuFei GO

二、常用SQL语句

http://www.yongfa365.com/Item/LiuYongFaChangYongSQLYuJuBeiWang.html

 
 --------------------------数据库操作--------------------------  
--建数据库  
create database yongfa365_com  
on  
( name = yongfa365_comt,  
  filename = 'd:/yongfa365_com.mdf',  
  size = 4,  
  maxsize = 10,  
  filegrowth = 1  
)  
  
  
--删数据库  
drop database yongfa365_com  
  
--备份数据库  
backup database yongfa365_com to disk='d:/yongfa365_com.bak'  
  
--批量收缩所有数据库  
DECLARE cur CURSOR FOR SELECT name FROM Master..SysDatabases WHERE name NOT IN ('master','model','msdb','Northwind','pubs')  
DECLARE @tb SYSNAME  
  
OPEN cur  
FETCH NEXT FROM cur INTO @tb  
WHILE @@fetch_status = 0  
    BEGIN  
        DUMP TRANSACTION  @tb  WITH NO_LOG  
        BACKUP LOG  @tb WITH NO_LOG  
        DBCC shrinkdatabase(@tb)  
        FETCH NEXT FROM cur INTO @tb  
    END  
CLOSE cur  
DEALLOCATE cur   
--------------------------数据库操作--------------------------  
  
  
----------------------------表操作----------------------------  
--删除表  
drop table movies  
  
--SQL Server通用建表结构  
Create Table [test] (  
  [Id] int primary key identity(1,1),--ID，主键，自动号  
  [txtTitle] nvarchar(255),--标题  
  [txtContent] nvarchar(MAX),--内容  
  [Adder] nvarchar(20),--添加人  
  [AddTime] datetime Default (getdate()),--提交时间  
  [ModiTime] datetime Default (getdate()),--修改时间  
  [Hits] int Default (0),--点击数  
  [Flags] int Default (0) ,--标识  
  [SortID] int Default (0)--排序号  
 )  
  
  
--SQLite建表  
Create Table [test] (  
  [Id] integer Primary key not null,  
  [txtTitle] text null,--标题  
  [txtContent] text null,--内容  
  [Adder]  text null,--添加人  
  [AddTime]  text DEFAULT (datetime('now','localtime')) not null,--提交时间  
  [ModiTime]  text DEFAULT (datetime('now','localtime')) not null,--修改时间  
  [Hits] integer Default (0)  not null,--点击数  
  [Flags] integer Default (0)  not null ,--标识  
  [SortID] integer Default (0)  not null--排序号  
 )  
  
--ACCESS使用SQL语句建表  
CREATE TABLE 表名 (  
  [Id] AutoIncrement NOT NULL ,  
  [AddTime] Date NULL ,  
  [Hits] Integer NULL ,  
  [Title] Text (255) NULL ,  
  [Remark] Memo NULL ,  
  [RMB] Currency NULL ,  
  [Flags] bit NULL ,  
    
  PRIMARY KEY (ID)  
)  
  
  
--重命名表  
sp_rename '表名', '新表名', 'object'  
----------------------------表操作----------------------------  
  
  
---------------------------字段操作---------------------------  
--添加字段  
alter table [表名] add [字段名] [varchar] (50)  
  
--删除字段  
alter table [表名] drop [字段名]  
  
--修改字段  
alter table [表名] alter column [字段名] varchar(50)  
  
--添加约束  
alter table [表名] add constraint [约束名] check ([约束字段] <= '2009-1-1')  
  
--删除约束  
alter table [表名] drop constraint [约束名]  
  
--添加默认值  
alter table [表名] add constraint [默认值名] default 'http://www.yongfa365.com/' for [字段名]  
  
--删除默认值  
alter table [表名] drop constraint [默认值名]  
  
--让SQL Server 自动编号ID从1开始  
dbcc checkident('表名',reseed,0)  
  
---------------------------字段操作---------------------------  
  
----------------------表及字段描述信息------------------------  
  
--为表添加描述信息  
EXEC sp_addextendedproperty N'MS_Description' , N'人员信息表' , N'SCHEMA' , N'dbo' , N'TABLE' , N'表名' , NULL , NULL  
  
--为字段XingMing添加描述信息  
EXEC sp_addextendedproperty N'MS_Description' , N'姓名' , N'SCHEMA' , N'dbo' , N'TABLE' , N'表名' , N'COLUMN' , N'XingMing'  
  
--更新表中列XingMing的描述属性：  
EXEC sp_updateextendedproperty N'MS_Description' , N'真实姓名' , N'SCHEMA' , N'dbo' , 'TABLE' , '表名' , 'COLUMN' , N'XingMing'  
  
--删除表中列XingMing的描述属性：  
EXEC sp_dropextendedproperty N'MS_Description' , N'SCHEMA' , N'dbo' , 'TABLE' , '表名' , 'COLUMN' , N'XingMing'  
  
----------------------表及字段描述信息------------------------  
  
  
---------------------------数据操作---------------------------  
--插入数据  
insert into [表名] (字段1,字段2) values (100,'http://www.yongfa365.com/')  
  
--删除数据  
delete from [表名] where [字段名]>100  
  
--删除重复记录(效率低，适合少量数据操作,极不适合ACCESS使用)  
delete from [表名] where id not in (select max(id) from [表名] group by txtTitle,txtContent)  
  
--NOT IN 效率太低，20000条数据都让access死掉了。可行的方法：建个临时表，在需要判断是否重复的字段上加主键，用insert into InfoTemp select * from Info将原表的数据导入临时表， 数据库可以自动筛去重复数据，delete * from Info 清空原表，再用insert into Info select * from InfoTemp 将临时表中数据导回原表  
  
--更新数据  
update [表名] set [字段1] = 200,[字段2] = 'http://www.yongfa365.com/' where [字段三] = 'haiwa'  
  
--多表，用一个表更新另一个表(SQL Server版)  
update 表一  
set 表一.a = 表二.b  
from 表一,表二  
where 表一.id = 表二.id  
  
update 表一  
set a = 表二.b  
from 表二  
where id = 表二.id  
  
--多表，用一个表更新另一个表(ACCESS版)  
update 表一   
inner join 表二  
on 表一.id = 表二.id  
set 表一.a = 表二.b   
  
--查询结果存储到新表  
select * into [新表表名] from [表名]  
  
--从table 表中取出从第 m 条到第 n 条的记录：(not in 版本)  
select top n-m+1 * from [表名] where id not in (select top m-1 id from [表名])  
  
--例：取出第31到第40条记录  
select top 10 * from [表名] where id not in (select top 30 id from [表名])  
  
--随机取10条数据,newid()是SQL数据库里的一个函数，跟数据库里的ID没关  
select top 10 * from [表名] order by newid()   
  
--随机取10条数据,如果是ACCESS数据库用order by rnd(数字字段)  
select top 10 * from [表名] order by rnd(id)  
  
--连接查询left join  
select * from Article left join Category on Article.CategoryID=Category.ID  
  
  
  
--查询结果插入到另一个表的相关字段里（可以插入一个表的字段，也可以是一个数字常量）  
insert into desttbl(fld1, fld2) select fld1, 5 from srctbl  
  
--把当前表里的数据再复制一份到这个表里  
insert into [表名] select * from [表名]  
  
  
--向数据库中添加5000条数据  
declare @i int  
set @i=1  
while @i<=5000  
begin  
  insert into users(userid,username) values(@i,'username' convert(varchar(255),@i))  
  set @i=@i+1  
end  
go  
  
--统计SQL语句执行时间  
declare @dt datetime  
set @dt=getdate()  
--要执行的SQL语句  
select [语句执行花费时间(毫秒)]=datediff(ms,@dt,getdate())  
  
--Case When语句  
SELECT UserName,sex=  
CASE  
  WHEN sex='男' THEN '男人'  
  WHEN sex='女' THEN '女人'  
  ELSE '哈哈'  
END  
,Age  
FROM Users  
  
--having使用方法  
--一个表中的UserName有很多重复,  
--只显示重复项:   
select UserName,COUNT(*) from Users group by UserName having count(*)>1   
--不显示重复项:   
select UserName,COUNT(*) from Users group by UserName having count(*)=1   
  
---------------------------数据操作---------------------------  
 

添加主键

增加主键约束

ALTER TABLE [dbo].[表名] ADD
CONSTRAINT [PK_表名] PRIMARY KEY CLUSTERED (
[主键1],[主键2]
) ON [PRIMARY]
GO

三、SQL Server 生成版权信息及测试环境信息

http://www.yongfa365.com/Item/SQL-Server-CopyRight-Environment.html

四、SQL快速生成大量数据记录及日志记录

http://www.yongfa365.com/Item/SQL-quick-make-big-mdf-log.html

view plain copy to clipboard print ?

use master
drop database dbname
create database dbname
go
use dbname
go
create table testlog ( usr varchar(50),pwd varchar(100))
go
--先插入一条数据
insert into testlog select 'asdfasdfsadfasf','asdfasdfasfdasdf'
go
--复制表到他本身,执行20次,生成1048576条数据
declare @i int
declare @d1 datetime
set @i=0
while @i<20
begin
set @d1 = getdate()
insert into [testlog] select * from [testlog]
set @i=@i+1
print '第' + Cast(@i as varchar(50)) + '圈，用时：' + Cast(datediff(ms,@d1,getdate()) as varchar(50)) + 'ms'
end
--收缩日志
use master
dump transaction [dbname] with no_log
backup log [dbname] with no_log
dbcc shrinkdatabase([dbname])

view plain copy to clipboard print ?

PRINT REPLICATE('-',82)
PRINT '-- Subject : XXXXXXXXXXXXXXXX'
PRINT '-- Author : 柳永法(yongfa365) http://www.yongfa365.com/ cto@yongfa365.com'
PRINT '-- CreateDate : '+CONVERT(VARCHAR,GETDATE(),20)
PRINT '-- Environment : '+ REPLACE(SUBSTRING(@@VERSION,0,LEN(@@VERSION)),CHAR(9),'-- ')
PRINT '-- Reference : XXXXXXXXXXXXXXXX'
PRINT REPLICATE('-',82)
--生成样式：
----------------------------------------------------------------------------------
-- Subject : XXXXXXXXXXXXXXXX
-- Author : 柳永法(yongfa365) http://www.yongfa365.com/ cto@yongfa365.com
-- CreateDate : 2010-05-03 11:28:06
-- Environment : Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
-- Reference : XXXXXXXXXXXXXXXX
----------------------------------------------------------------------------------