一、SQL统计:从北京到天津的与从天津到北京的汇总到一起
二、常用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
http://www.yongfa365.com/Item/SQL-Server-CopyRight-Environment.html
http://www.yongfa365.com/Item/SQL-quick-make-big-mdf-log.html
- 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])
- 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
- -- Copyright (c) 1988-2005 Microsoft Corporation
- -- Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
- -- Reference : XXXXXXXXXXXXXXXX
- ----------------------------------------------------------------------------------