众人合力学数据库
大家对数据库学习的怎么样啊 ，这里我找到一些数据库的常用语句，意在分享，当然希望大家踊跃补充，以后学数据库找语句就到这里了，谢谢
现有语句如下：
1、说明：复制表(只复制结构,源表名：a 新表名：b) (Access可用)
法一：select * into b from a where 1<>1
法二：select top 0 * into b from a
2、说明：拷贝表(拷贝数据,源表名：a 目标表名：b) (Access可用)
insert into b(a, b, c) select d,e,f from b;
3、说明：跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用)
insert into b(a, b, c) select d,e,f from b in ‘具体数据库’ where 条件
例子：..from b in '"&Server.MapPath(".")&"\data.mdb" &"' where..
4、说明：子查询(表名1：a 表名2：b)
select a,b,c from a where a IN (select d from b ) 或者: select a,b,c from a where a IN (1,2,3)
5、说明：显示文章、提交人和最后回复时间
select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b
6、说明：外连接查询(表名1：a 表名2：b)
select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c
7、说明：在线视图查询(表名1：a )
select * from (SELECT a,b,c FROM a) T where t.a > 1;
8、说明：between的用法,between限制查询数据范围时包括了边界值,not between不包括
select * from table1 where time between time1 and time2
select a,b,c, from table1 where a not between 数值1 and 数值2
9、说明：in 的使用方法
select * from table1 where a [not] in (‘值1’,’值2’,’值4’,’值6’)
10、说明：两张关联表，删除主表中已经在副表中没有的信息
delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 )
11、说明：四表联查问题：
select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where .....
12、说明：日程安排提前五分钟提醒
SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5
13、说明：一条sql 语句搞定数据库分页
select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段
14、说明：前10条记录
select top 10 * form table1 where 范围
15、说明：选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.)
select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b)
16、说明：包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表
(select a from tableA ) except (select a from tableB) except (select a from tableC)
17、说明：随机取出10条数据
select top 10 * from tablename order by newid()
18、说明：随机选择记录
select newid()
19、说明：删除重复记录
Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...)
20、说明：列出数据库里所有的表名
select name from sysobjects where type='U'
21、说明：列出表里的所有的
select name from syscolumns where id=object_id('TableName')
22、说明：列示type、vender、pcs字段，以type字段排列，case可以方便地实现多重选择，类似select 中的case。
select type,sum(case vender when 'A' then pcs else 0 end),sum(case vender when 'C' then pcs else 0 end),sum(case vender when 'B' then pcs else 0 end) FROM tablename group by type
显示结果：
type vender pcs
电脑 A 1
电脑 A 1
光盘 B 2
光盘 A 2
手机 B 3
手机 C 3
23、说明：初始化表table1
TRUNCATE TABLE table1
24、说明：选择从10到15的记录
select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc
[解决办法]
谢谢分享..
[解决办法]
挺好的
[解决办法]
挺好的。。。对新手我有用
[解决办法]
嘎嘎、多谢你滴
-> Jet 引擎访问 Excel select * from OpenRowSet('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;HDR=Yes;IMEX=1;Database=D:\Roy.xls', 'select * from [Sheet1$]') select * from OpenRowSet('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;HDR=Yes;IMEX=1;Database=D:\Roy.xls', [Sheet1$]) select * from OpenDataSource('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;HDR=Yes;IMEX=1;Database=D:\Roy.xls')...[Sheet1$] select * from OpenDataSource('Microsoft.Jet.OLEDB.4.0', 'Data Source=D:\Roy.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1"')...[Sheet1$] --> ACE 引擎访问 Excel 97-2003 select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;IMEX=1;Database=D:\Roy.xls', 'select * from [Sheet1$]') select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;IMEX=1;Database=D:\Roy.xls', [Sheet1$]) select * from OpenDataSource('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;IMEX=1;Database=D:\Roy.xls')...[Sheet1$] select * from OpenDataSource('Microsoft.ACE.OLEDB.12.0', 'Data Source=D:\Roy.xls;Extended Properties="Excel 12.0;HDR=Yes;IMEX=1"')...[Sheet1$]--创建链接服务器 exec sp_addlinkedserver 'ITSV ', ' ', 'SQLOLEDB ', '远程服务器名或ip地址 ' exec sp_addlinkedsrvlogin 'ITSV ', 'false ',null, '用户名 ', '密码 ' --查询示例 select * from ITSV.数据库名.dbo.表名 --导入示例 select * into 表 from ITSV.数据库名.dbo.表名 --以后不再使用时删除链接服务器 exec sp_dropserver 'ITSV ', 'droplogins ' --连接远程/局域网数据(openrowset/openquery/opendatasource) --1、openrowset --查询示例 select * from openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名) --生成本地表 select * into 表 from openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名) --把本地表导入远程表 insert openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名) select *from 本地表 --更新本地表 update b set b.列A=a.列A from openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名)as a inner join 本地表 b on a.column1=b.column1 --openquery用法需要创建一个连接 --首先创建一个连接创建链接服务器 exec sp_addlinkedserver 'ITSV ', ' ', 'SQLOLEDB ', '远程服务器名或ip地址 ' --查询 select * FROM openquery(ITSV, 'SELECT * FROM 数据库.dbo.表名 ') --把本地表导入远程表 insert openquery(ITSV, 'SELECT * FROM 数据库.dbo.表名 ') select * from 本地表 --更新本地表 update b set b.列B=a.列B FROM openquery(ITSV, 'SELECT * FROM 数据库.dbo.表名 ') as a inner join 本地表 b on a.列A=b.列A --3、opendatasource/openrowset SELECT * FROM opendatasource( 'SQLOLEDB ', 'Data Source=ip/ServerName;User ID=登陆名;Password=密码 ' ).test.dbo.roy_ta --把本地表导入远程表--语法：DATEADD (datepart , number, date )select DATEADD(YEAR,1,getdate())--当前时间加一年select DATEADD(MONTH,1,GETDATE())--当前时间加一月select DATEADD(day,1,GETDATE())--当前时间加一天select DATEADD(month,3,GETDATE())--当前时间加一季度- datepart是将为其返回integer 的date（日期或时间值）的一部分。下表列出了所有有效的datepart 参数。--用户定义的变量等效项是无效的--语法:DATEPART ( datepart , date )例子：select DATEPART(yy,GETDATE())--获取年,DATEname(yy,GETDATE())--获取年-DATEDIFF ( datepart , startdate , enddate )select DATEDIFF(yy,getdate(),dateadd(yy,11,getdate()))--11年后减去当前年--CONVERT ( data_type [ ( length ) ] , expression [ , style ] ) 参数expression 任何有效的表达式。data_type 目标数据类型。这包括 xml、bigint 和 sql_variant。不能使用别名数据类型。有关可用数据类型的详细信息，请参阅数据类型 (Transact-SQL)。length 指定目标数据类型长度的可选整数。默认值为 30。style 指定 CONVERT 函数如何转换 expression 的整数表达式。如果样式为 NULL，则返回 NULL。该范围是由 data_type 确定的例子：select CONVERT(varchar,GETDATE(),101)--字符转换为日期时,Style的使用--1. Style=101时,表示日期字符串为:mm/dd/yyyy格式SELECT CONVERT(datetime,'11/1/2003',101)--结果:2003-11-01 00:00:00.000--2. Style=101时,表示日期字符串为:dd/mm/yyyy格式SELECT CONVERT(datetime,'11/1/2003',103)--结果:2003-01-11 00:00:00.000/*== 日期转换为字符串==*/DECLARE @dt datetimeSET @dt='2003-1-11'--1. Style=101时,表示将日期转换为:mm/dd/yyyy 格式SELECT CONVERT(varchar,@dt,101)--结果:01/11/2003--2. Style=103时,表示将日期转换为:dd/mm/yyyy 格式SELECT CONVERT(varchar,@dt,103)--结果:11/01/2003/*== 这是很多人经常犯的错误,对非日期型转换使用日期的style样式==*/SELECT CONVERT(varchar,'2003-1-11',101)--结果:2003-1-11
------解决方案--------------------
-以下示例演示了在不同的语言环境(SET LANGUAGE)下，DATENAME与CONVERT函数的不同结果。
--SET LANGUAGE { [ N ] 'language' | @language_var }
USE master
--设置会话的语言环境为: English
SET LANGUAGE N'English'
SELECT
DATENAME(Month,GETDATE()) AS [Month],
DATENAME(Weekday,GETDATE()) AS [Weekday],
CONVERT(varchar,GETDATE(),109) AS [CONVERT]
/*--结果:
Month Weekday CONVERT
------------- -------------- -------------------------------
March Tuesday Mar 15 2005 8:59PM
--*/
--设置会话的语言环境为: 简体中文
SET LANGUAGE N'简体中文'
SELECT
DATENAME(Month,GETDATE()) AS [Month],
DATENAME(Weekday,GETDATE()) AS [Weekday],
CONVERT(varchar,GETDATE(),109) AS [CONVERT]
/*--结果
Month Weekday CONVERT
------------- --------------- -----------------------------------------
05 星期四 05 19 2005 2:49:20:607PM
DECLARE @dt datetime
SET @dt=GETDATE()
--1．短日期格式：yyyy-m-d,把后面的“-0”换成‘-’，N是什么意思没有懂
SELECT REPLACE(CONVERT(varchar(10),@dt,120),N'-0','-')
--2．长日期格式：yyyy年mm月dd日
--A. 方法
--select STUFF('123456',2,3,2233);结果：;从第二个位置开始，向后数到第三个位置，还成.
SELECT STUFF(STUFF(CONVERT(char(8),@dt,112),5,0,N'年'),8,0,N'月')+N'日'
--B. 方法
SELECT DATENAME(Year,@dt)+N'年'+DATENAME(Month,@dt)+N'月'+DATENAME(Day,@dt)+N'日'
--3．长日期格式：yyyy年m月d日
SELECT DATENAME(Year,@dt)+N'年'+CAST(DATEPART(Month,@dt) AS varchar)+N'月'+DATENAME(Day,@dt)+N'日'
--4.完整日期+时间格式：yyyy-mm-dd hh:mi:ss:mmm
SELECT CONVERT(char(11),@dt,120)+CONVERT(char(12),@dt,114)
2、SQL连接EXCEL查EXCEL数据
select * from opendatasource('Microsoft.Ace.OleDB.12.0','data source=d:\10W.xlsx;extended properties=excel 12.0')...[sheet1$]
3、把EXCLE的数据传到SQL，传的数据要建一个新表
2.3.1 OpenDataSource 方式
select * into [0111121] from opendatasource('Microsoft.Ace.OleDB.12.0','data source=d:\10W.xlsx;extended properties=excel 12.0')...[sheet1$]
注：把d:\10W.xlsx, sheet1的信息放到SQL表[0111121]中，[0111121]为一个新表
下面这个一样的效果
select * from OpenDataSource
('Microsoft.ACE.OLEDB.12.0', 'Data Source=D:\10w.xls;Extended Properties="Excel 12.0;HDR=no;IMEX=1"')...[Sheet1$]
HDR=yes或NO表示第一列是否为行标。
2.3.2 OpenRowSet方式
select * from OpenRowSet
('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;IMEX=1;Database=D:\10w.xls', 'select * from [Sheet1$]')
select * from OpenRowSet
('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;IMEX=1;Database=D:\10w.xls', [Sheet1$])
4、把EXCLE的数据写到SQL的一个表中（原表的值不变）
表的格式要求一样的
2.4.1 OpenRowSet方式
insert into [0111121]
select * from OpenRowSet
('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;IMEX=1;Database=D:\10w.xls', [Sheet1$])
注：把d:\10W.xlsx, sheet1的信息追加到SQL表[0111121]中，[0111121]在原SQL中存在的。
2.4.2 opendatasource方式
insert into [0111121]
select * from opendatasource('Microsoft.Ace.OleDB.12.0','data source=d:\10W.xlsx;extended properties=excel 12.0')...[sheet1$]
5、数据库中导出、导入bcp（OK）
--从数据库中导出数据并存到文件中, c是字段类型，一定要小写的，大写的不对了。还有一定要放在共享文件夹下面。
EXEC master..xp_cmdshell 'bcp test.dbo.成绩out d:\test\123.txt -c -S"PC2011061511NRV" -U"sa" -P"30687724"'
--找出自己想要的行数据：要加QUERYOUT OR QUERYOUTIN
EXEC master..xp_cmdshell 'bcp "select * from test.dbo.成绩" queryout d:\test\123.xls -c -S"PC2011061511NRV" -U"sa" -P"30687724"'
————————————————————————————————————————
--从文件中导入数据到数据库对应表中，-U用IP或用户名都可以。”-q”要不要都行，-q是若要指定包含空格或单引号的数据库名称，必须使用 -q 选项：
EXEC master..xp_cmdshell 'bcp test.dbo.成绩 in d:\test\123.xls -c –q -S"127.0.0.1" -U"sa" -P"30687724"'
注：以上方法对于XLSX（好像打不开，没有懂）、CSV（要分裂这个还没有搞明白）文件也是支持的。一般用TXT，小文件用XLS即可。复制50W也就5秒，快死了。
如果是TXT文件，最好格式要求为：“文本文件（制表符分隔）”而且原表的数据类型要求为“VARCHAR(50)”
EXEC master..xp_cmdshell 'bcp test.dbo.book1 in D:\test\yh_2010-03-2\2010-3-2\2010-3-2.txt -t"\t" -c -S"127.0.0.1" -U"sa" -P"30687724"'
如果有提示启用什么的输入以下：
--已测试EXEC sp_configure 'show advanced options', 1;RECONFIGURE;EXEC sp_configure 'xp_cmdshell', 1;RECONFIGURE;
--未测试，网上的办法；--启用xp_cmdshell
USE master
GO
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'show advanced options', 0
GO
RECONFIGURE WITH OVERRIDE
--关闭xp_cmdshell
USE master
GO
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'xp_cmdshell', 0
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'show advanced options', 0
GO
RECONFIGURE WITH OVERRIDE
7、BULK INSERT导入法
BULK INSERT test.dbo.book1 FROM 'D:\test\yh_2010-03-2\2010-3-2\2010-3-2.txt'
WITH (
FIELDTERMINATOR = '\t',
ROWTERMINATOR = '\n'
)
如果是TXT文件，最好格式要求为：“文本文件（制表符分隔）”
[解决办法]
楼主抛砖引玉，都有大师的风范
高手们的知识实在是我等菜鸟难以企及的， 粪斗
[解决办法]
学数据库，不仅要学会高级查询，还有学会优化
例如：
数据库的索引，前段时间就是因为数据库索引没有建好导致很大问题
我来说一下这个索引吧
作为一个存放大量数据的表，没有索引，查询时，数据的冗余量，吞吐量会一下子飙升，最严重的问题是，一句简单的查询语句，会导致数据库查询超时，所以索引是很重要的。
作为索引，用什么字段要在乎自己项目的查询条件来创建。
以上就是我对数据库索引的想法，至于索引的详细应用还是多走走论坛吧！