SQL精典语句
来源：互联网 发布：南威软件最新消息 编辑：程序博客网 时间：2024/06/10 23:30
1.
选出表中重复数据
select a.* from 表 a,(select 列2 from 表 group by 列2 having count(*)>1) b
where a.列2=b.列2
create table tb (列1 int, 列2 char(2),列3 char(2))
insert tb values(1,'我','它')
insert tb values(2,'你','谁')
insert tb values(3,'我','谁')
select a.* from tb a,(select 列2 from tb group by 列2 having count(*)>1) b
where a.列2=b.列2
---
列1 列2 列3
----------- ---- ----
3 我 谁
1 我 它
insert into rybase
(员工编号,员工姓名,性别,民族)
values('124499',N'哈哈','fg','d')
select a.name 表名
from sysobjects a,syscolumns b
where a.id=b.id and OBJECTPROPERTY(a.id, N'IsUserTable') = 1 and b.name='你的列名'
select 对象类型=case xtype
when 'U' then '用户表'
when 'S' then '系统表'
when 'V' then '视图'
when 'P' then '存储过程'
else '其他' end
,对象名=name
from 库名..sysobjects a
where exists(
select 1 from syscolumns
where id=a.id and name='列名')
select 对象类型=case xtype
when 'U' then '用户表'
when 'S' then '系统表'
when 'V' then '视图'
when 'P' then '存储过程'
else '其他' end
,对象名=name
from sysobjects a
where exists(
select 1 from syscolumns
where id=a.id and name='期末余额')
--参考:
--找出在T1中但不在T2中的记录
create table t1(a varchar(10),b varchar(10))
create table t2(a varchar(10),b varchar(10))
insert t1
select 'a','1'
insert t1
select 'a','2'
insert t2
select 'a','2'
/*
select * from t1 bb
where not exists (select * from t2 where a=bb.a and b=bb.b)
*/
net start mssqlserver
osql -S"你的server名" -U"sa" -P"" -d"你的數據庫名"
--如果只是单表导入,也可以直接写SQL语句来完成
--在SQL中操纵读取ACCESS数据库
SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'c:/test.mdb';'admin';''
,[表名])
SELECT *
FROM opendatasource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:/test.mdb";Jet OLEDB:Database Password=数据库密码')...[表名]
/*--说明:
c:/test.mdb 是你要操作的ACCESS数据库名,如果不在SQL服务器上,需要设置文件所在的目录为完全共享,并将目录改为网络目录
表名 是你要操作和ACCESS数据库中的表名
数据库密码 如果你的ACCESS数据库有密码,就要用第二种方式
其他部分不需要做任何变动
如果是导入数据到现有表,对应的在: SELECT *
前加上: INSERT INTO 表
语句
如果是导入数据并生成新表,对应的在: SELECT *
后加上: INTO 表
语句
update 表 set 列3=(select sum(列2) from 表 where 列1<=a.列1)
from 表 a
delete from 表 where id = 2
declare @test int,
set @test = 3
if @test<= 4
begin
update 表 set id = @test - 1 where id = @test
set @test = @test + 1
end
select * from 表
--或者写个触发器自动完成
create trigger tr_delete on 表
for delete
as
declare @id int,@i int
select @id=min(id),@i=@id-1 from deleted
update 表 set @i=@i+1,id=@i
where id>@id
select 人员,购买数量=sum(购买数量),金额=sum(金额) from 表格 group by 人员
use test
select dbo.supertotol.物料编码,dbo.supertotol.销售数量,dbo.supertotol.金额,dbo.supertotol.承包价格,dbo.caiwutotol.物料名称
into endcaiwu
from supertotol
right join dbo.caiwutotol
on dbo.supertotol.物料编码=dbo.caiwutotol.物料编码
select * from dbo.endcaiwu
Dim dcmtmp As New SqlClient.SqlCommand("sprCustomerAdd", Dcolink)
dcolink.open()
dcmtmp.CommandType = CommandType.StoredProcedure
'添加输入变量
dcmtmp.Parameters.Add("@Sn1", StrStoreID)
dcmtmp.Parameters.Add("@Sn2", TxtName.Text)
'定义输出变量
Dim param As SqlClient.SqlParameter
param = dcmtmp.Parameters.Add("@Cn", SqlDbType.VarChar)
param.Direction = ParameterDirection.Output
dcmtmp.ExecuteNonQuery()
'变量param 就是你要的输出变量
use test
select *
into singlecaiwu
from dbo.caiwu
where 物料编码 like 'f%'
use test
select 物料编码,物料名称,销售数量=sum(销售数量),金额=sum(金额)
into caiwutotle
from singlecaiwu
group by 物料编码,物料名称
CREATE TABLE doc_exb ( column_a INT, column_b VARCHAR(20) NULL)
GO
ALTER TABLE doc_exb DROP COLUMN column_b
GO
--从Excel文件中,导入数据到SQL数据库中,直接用下面的语句:
/*===================================================================*/
--如果接受数据导入的表已经存在
insert into 表 select * from
OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'Excel 5.0;HDR=YES;DATABASE=c:/test.xls',sheet1$)
--如果导入数据并生成表
select * into 表 from
OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'Excel 5.0;HDR=YES;DATABASE=c:/test.xls',sheet1$)
DROP TABLE [TABLE NAME]
--下面是在SQL中,直接用SQL语句读取几种常见的文件格式中的数据
--/* 文本文件
select * from
OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'Text;HDR=NO;DATABASE=C:/' --c:/是目录
,aa#txt) --aa#txt是文本文件名aa.txt
--*/
--/* Excel文件
select * from
OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'Excel 8.0;IMEX=1;HDR=YES;DATABASE=c:/test.xls' --c:/test.xls是excel文件名
,sheet1$)
--/* dBase IV文件
select * from
OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'dBase IV;DATABASE=C:/' --c:/是目录
,'select * from [客户资料4.dbf]') --客户资料4.dbf是文件名
--*/
--/* dBase III文件
select * from
OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'dBase III;DATABASE=C:/'
,'select * from [客户资料3.dbf]')
--*/
--/* FoxPro 数据库
select * from openrowset('MSDASQL',
'Driver=Microsoft Visual FoxPro Driver;SourceType=DBF;SourceDB=c:/', --c:/是目录
'select * from [aa.DBF]') --aa.dbf是文件名
/*--说明:
SourceDB=c:/ c:/是dbf文件的存放目录
[aa.DBF] 是dbf文件名
--*/
--*/
***************************************************************************************
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_exporttb]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_exporttb]
GO
/*--数据导出EXCEL
导出查询中的数据到Excel,包含字段名,文件为真正的Excel文件
,如果文件不存在,将自动创建文件
,如果表不存在,将自动创建表
基于通用性考虑,仅支持导出标准数据类型
--邹建 2003.10--*/
/*--调用示例
p_exporttb @sqlstr='select * from 地区资料'
,@path='c:/',@fname='aa.xls',@sheetname='地区资料'
--*/
create proc p_exporttb
@sqlstr varchar(8000), --查询语句,如果查询语句中使用了order by ,请加上top 100 percent
@path nvarchar(1000), --文件存放目录
@fname nvarchar(250), --文件名
@sheetname varchar(250)='' --要创建的工作表名,默认为文件名
as
declare @err int,@src nvarchar(255),@desc nvarchar(255),@out int
declare @obj int,@constr nvarchar(1000),@sql varchar(8000),@fdlist varchar(8000)
--参数检测
if isnull(@fname,'')='' set @fname='temp.xls'
if isnull(@sheetname,'')='' set @sheetname=replace(@fname,'.','#')
--检查文件是否已经存在
if right(@path,1)<>'/' set @path=@path+'/'
create table #tb(a bit,b bit,c bit)
set @sql=@path+@fname
insert into #tb exec master..xp_fileexist @sql
--数据库创建语句 --连接数据库 exec @err=sp_oamethod @obj,'open',null,@constr --创建表的SQL select @sql='',@fdlist='' exec @err=sp_oamethod @obj,'execute',@out out,@sql exec @err=sp_oadestroy @obj --导入数据 exec('insert into '+@sql+'('+@fdlist+') select '+@fdlist+' from ['+@tbname+']') set @sql='drop table ['+@tbname+']' lberr: *********************************************** --导出查询的情况 说明：复制表(只复制结构,源表名：a 新表名：b) 说明：两张关联表，删除主表中已经在副表中没有的信息 找表： 字段 ######################################################################################### 执行XP相关命令 xp_cmdshell 说明 在 Microsoft® Windows® 98 操作系统中执行 xp_cmdshell 时，将不把 xp_cmdshell 的返回代码设置为唤醒调用的可执行文件的进程退出代码。返回代码始终为 0。 参数 是在操作系统命令行解释器上执行的命令字符串。command_string 的数据类型为 varchar(8000) 或 nvarchar(4000)，没有默认值。command_string 不能包含一对以上的双引号。如果由 command_string 引用的文件路径或程序名称中有空格，则需要使用一对引号。如果使用嵌入空格不方便，可考虑使用 FAT 8.3 文件名作为解决办法。 no_output 是可选参数，表示执行给定的 command_string，但不向客户端返回任何输出。 返回代码值 结果集 xp_cmdshell 'dir *.exe' 行以 nvarchar(255) 列的形式返回。 执行下列 xp_cmdshell 语句将返回随后的结果集： xp_cmdshell 'dir *.exe', NO_OUTPUT 下面是结果： The command(s) completed successfully. 注释 当授予用户执行权限时，用户能在 Microsoft Windows NT® 命令行解释器上执行运行 Microsoft SQL Server™ 的帐户有权执行的任何操作系统命令。 默认情况下，只有 sysadmin 固定服务器角色的成员才能执行此扩展存储过程。但是，也可以授予其他用户执行此存储过程的权限。 当作为 sysadmin 固定服务器角色成员的用户唤醒调用 xp_cmdshell 时，将在运行 SQL Server 服务的安全上下文中执行 xp_cmdshell。当用户不是 sysadmin 组的成员时，xp_cmdshell 将模拟使用 xp_sqlagent_proxy_account 指定的 SQL Server 代理程序的代理帐户。如果代理帐户不能用，则 xp_cmdshell 将失败。这只是针对于 Microsoft® Windows NT® 4.0 和 Windows 2000。在 Windows 9.x 上，没有模拟，且 xp_cmdshell 始终在启动 SQL Server 的 Windows 9.x 用户的安全上下文下执行。 说明 在早期版本中，获得 xp_cmdshell 执行权限的用户在 MSSQLServer 服务的用户帐户上下文中运行命令。可以通过配置选项配置 SQL Server，以便对 SQL Server 无 sa 访问权限的用户能够在 SQLExecutiveCmdExec Windows NT 帐户的上下文中运行 xp_cmdshell。在 SQL Server 7.0 中，该帐户称为 SQLAgentCmdExec。现在，不是 sysadmin 固定服务器角色成员的用户将在该帐户上下文中运行命令，而无需再进行配置更改。 重要 如果为 MSSQLServer 服务选用的 Windows NT 帐户不是本地管理员组的成员，则非 sysadmin 固定服务器角色成员的用户将无法执行 xp_cmdshell。 EXEC master..xp_cmdshell 'dir *.exe' B. 使用 Windows NT net 命令 CREATE PROC shutdown10 C. 不返回输出 USE master D. 使用返回状态 DECLARE @result int E. 将变量内容写入文件 DECLARE @cmd sysname, @var sysname F. 将命令的结果捕获到文件 DECLARE @cmd sysname, @var sysname 1.权限 2.执行方式 3.不能执行用户交互的命令,比如,执行记事本这种需要用户录入,关闭等操作的程序,就会挂死程序 4.如果程序是要弹出用户界面的,用户界面不会弹出
set @sql=@path+@fname
if exists(select 1 from #tb where a=1)
set @constr='DRIVER={Microsoft Excel Driver (*.xls)};DSN='''';READONLY=FALSE'
+';CREATE_DB="
exec @err=sp_oacreate 'adodb.connection',@obj out
if @err<>0 goto lberr
if @err<>0 goto lberr
declare @tbname sysname
set @tbname='##tmp_'+convert(varchar(38),newid())
set @sql='select * into ['+@tbname+'] from('+@sqlstr+') a'
exec(@sql)
select @fdlist=@fdlist+',['+a.name+']'
,@sql=@sql+',['+a.name+'] '
+case when b.name in('char','nchar','varchar','nvarchar') then
'text('+cast(case when a.length>255 then 255 else a.length end as varchar)+')'
when b.name in('tynyint','int','bigint','tinyint') then 'int'
when b.name in('smalldatetime','datetime') then 'datetime'
when b.name in('money','smallmoney') then 'money'
else b.name end
FROM tempdb..syscolumns a left join tempdb..systypes b on a.xtype=b.xusertype
where b.name not in('image','text','uniqueidentifier','sql_variant','ntext','varbinary','binary','timestamp')
and a.id=(select id from tempdb..sysobjects where name=@tbname)
select @sql='create table ['+@sheetname
+']('+substring(@sql,2,8000)+')'
,@fdlist=substring(@fdlist,2,8000)
if @err<>0 goto lberr
set @sql='openrowset(''MICROSOFT.JET.OLEDB.4.0'',''Excel 5.0;HDR=YES
;DATABASE='+@path+@fname+''',['+@sheetname+'$])'
exec(@sql)
return
exec sp_oageterrorinfo 0,@src out,@desc out
lbexit:
select cast(@err as varbinary(4)) as 错误号
,@src as 错误源,@desc as 错误描述
select @sql,@constr,@fdlist
go
--如果从SQL数据库中,导出数据到Excel,如果Excel文件已经存在,而且已经按照要接收的数据创建好表头,就可以简单的用:
insert into OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'Excel 5.0;HDR=YES;DATABASE=c:/test.xls',sheet1$)
select * from 表
--如果Excel文件不存在,也可以用BCP来导成类Excel的文件,注意大小写:
--导出表的情况
EXEC master..xp_cmdshell 'bcp 数据库名.dbo.表名 out "c:/test.xls" /c /S"服务器名" /U"用户名" -P"密码"'
EXEC master..xp_cmdshell 'bcp "SELECT au_fname, au_lname FROM pubs..authors ORDER BY au_lname" queryout "c:/test.xls" /c /S"服务器名" /U"用户名" -P"密码"'
/*--说明:
c:/test.xls 为导入/导出的Excel文件名.
sheet1$ 为Excel文件的工作表名,一般要加上$才能正常使用.
SQL: select * into b from a where 1<>1
说明：拷贝表(拷贝数据,源表名：a 目标表名：b)
SQL: insert into b(a, b, c) select d,e,f from b;
说明：显示文章、提交人和最后回复时间
SQL: select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b
说明：外连接查询(表名1：a 表名2：b)
SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c
说明：日程安排提前五分钟提醒
SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5
SQL:
delete from info where not exists ( select * from infobz where info.infid=infobz.infid )
说明：--
SQL:
SELECT A.NUM, A.NAME, B.UPD_DATE, B.PREV_UPD_DATE
FROM TABLE1,
(SELECT X.NUM, X.UPD_DATE, Y.UPD_DATE PREV_UPD_DATE
FROM (SELECT NUM, UPD_DATE, INBOUND_QTY, STOCK_ONHAND
FROM TABLE2
WHERE TO_CHAR(UPD_DATE,'YYYY/MM') = TO_CHAR(SYSDATE, 'YYYY/MM')) X,
(SELECT NUM, UPD_DATE, STOCK_ONHAND
FROM TABLE2
WHERE TO_CHAR(UPD_DATE,'YYYY/MM') =
TO_CHAR(TO_DATE(TO_CHAR(SYSDATE, 'YYYY/MM') || '/01','YYYY/MM/DD') - 1, 'YYYY/MM') ) Y,
WHERE X.NUM = Y.NUM （+）
AND X.INBOUND_QTY + NVL(Y.STOCK_ONHAND,0) <> X.STOCK_ONHAND ) B
WHERE A.NUM = B.NUM
说明：--
SQL:
select * from studentinfo where not exists(select * from student where studentinfo.id=student.id) and 系名称='"&strdepartmentname&"' and 专业名称='"&strprofessionname&"' order by 性别,生源地,高考总成绩
说明：从数据库中去一年的各单位电话费统计(电话费定额贺电化肥清单两个表来源）
SQL:
SELECT a.userper, a.tel, a.standfee, TO_CHAR(a.telfeedate, 'yyyy') AS telyear,
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '01', a.factration)) AS JAN,
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '02', a.factration)) AS FRI,
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '03', a.factration)) AS MAR,
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '04', a.factration)) AS APR,
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '05', a.factration)) AS MAY,
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '06', a.factration)) AS JUE,
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '07', a.factration)) AS JUL,
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '08', a.factration)) AS AGU,
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '09', a.factration)) AS SEP,
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '10', a.factration)) AS OCT,
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '11', a.factration)) AS NOV,
SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '12', a.factration)) AS DEC
FROM (SELECT a.userper, a.tel, a.standfee, b.telfeedate, b.factration
FROM TELFEESTAND a, TELFEE b
WHERE a.tel = b.telfax) a
GROUP BY a.userper, a.tel, a.standfee, TO_CHAR(a.telfeedate, 'yyyy')
说明：四表联查问题：
SQL: 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 .....
说明：得到表中最小的未使用的ID号
SQL:
SELECT (CASE WHEN EXISTS(SELECT * FROM Handle b WHERE b.HandleID = 1) THEN MIN(HandleID) + 1 ELSE 1 END) as HandleID
FROM Handle
WHERE NOT HandleID IN (SELECT a.HandleID - 1 FROM Handle a)
select * from sysobjects where xtype='u' and name ='SP_WZ_ZD_CXJL'
select * from syscolumns where ...
#########################################################################################3
以操作系统命令行解释器的方式执行给定的命令字符串，并以文本行方式返回任何输出。授予非管理用户执行 xp_cmdshell 的权限。
语法
xp_cmdshell {'command_string'} [, no_output]
'command_string'
0（成功）或 1（失败）
执行下列 xp_cmdshell 语句将返回当前目录的目录列表。
xp_cmdshell 以同步方式操作。在命令行解释器命令执行完毕之前，不会返回控制。
权限
xp_deletemail 的执行权限默认授予 sysadmin 固定服务器角色的成员，但可以授予其他用户。
示例
A. 返回可执行文件列表
下例显示执行目录命令的 xp_cmdshell 扩展存储过程。
下例显示 xp_cmdshell 在存储过程中的使用。下例先用 net send 通知用户 SQL Server 即将关闭，然后用 net pause 暂停服务器，最后用 net stop 关闭服务器。
AS
EXEC xp_cmdshell 'net send /domain:SQL_USERS ''SQL Server shutting down
in 10 minutes. No more connections allowed.', no_output
EXEC xp_cmdshell 'net pause sqlserver'
WAITFOR DELAY '00:05:00'
EXEC xp_cmdshell 'net send /domain: SQL_USERS ''SQL Server shutting down
in 5 minutes.', no_output
WAITFOR DELAY '00:04:00'
EXEC xp_cmdshell 'net send /domain:SQL_USERS ''SQL Server shutting down
in 1 minute. Log off now.', no_output
WAITFOR DELAY '00:01:00'
EXEC xp_cmdshell 'net stop sqlserver', no_output
下例使用 xp_cmdshell 执行命令字符串，且不向客户端返回输出。
EXEC xp_cmdshell 'copy c:/sqldumps/pubs.dmp //server2/backups/sqldumps',
NO_OUTPUT
在下例中，xp_cmdshell 扩展存储过程也给出了返回状态。返回代码值存储在变量 @result 中。
EXEC @result = xp_cmdshell 'dir *.exe'
IF (@result = 0)
PRINT 'Success'
ELSE
PRINT 'Failure'
下例将 @var 变量的内容写入当前服务器目录下名为 var_out.txt 的文件中。
SET @var = 'Hello world'
SET @cmd = 'echo ' + @var + ' > var_out.txt'
EXEC master..xp_cmdshell @cmd
下例将当前目录的内容写入当前服务器目录中名为 dir_out.txt 的文件中。
SET @var = 'dir/p'
SET @cmd = @var + ' > dir_out.txt'
EXEC master..xp_cmdshell @cmd
注意几点:
xp_deletemail 的执行权限默认授予 sysadmin 固定服务器角色的成员，但可以授予其他用户
xp_cmdshell 以同步方式操作。在命令行解释器命令执行完毕之前，不会返回控制。
