经典的SQL文sql

资料来源网上

 

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

 

1.    行列转换--普通

假设有张学生成绩表(CJ)如下
Name Subject Result
张三 语文 80
张三 数学 90
张三 物理 85
李四 语文 85
李四 数学 92
李四 物理 82

想变成
姓名 语文 数学 物理
张三 80 90 85
李四 85 92 82

declare @sql varchar(4000)
set @sql = 'select Name'
select @sql = @sql + ',sum(case Subject when '''+Subject+''' then Result end) ['+Subject+']'
from (select distinct Subject from CJ) as a
select @sql = @sql+' from test group by name'
exec(@sql)

2. 行列转换--合并

有表A,
id pid
1 1
1 2
1 3
2 1
2 2
3 1
如何化成表B:
id pid
1 1,2,3
2 1,2
3 1

创建一个合并的函数
create function fmerg(@id int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+','+cast(pid as varchar) from 表A where id=@id set @str=right(@str,len(@str)-1)
return(@str)
End
go

--调用自定义函数得到结果
select distinct id,dbo.fmerg(id) from 表A

3. 如何取得一个数据表的所有列名

方法如下：先从SYSTEMOBJECT系统表中取得数据表的SYSTEMID,然后再SYSCOLUMN表中取得该数据表的所有列名。
SQL语句如下：
declare @objid int,@objname char(40)
set @objname = 'tablename'
select @objid = id from sysobjects where id = object_id(@objname)
select 'Column_name' = name from syscolumns where id = @objid order by colid

是不是太简单了？ 呵呵 不过经常用阿.

4. 通过SQL语句来更改用户的密码

修改别人的,需要sysadmin role
EXEC sp_password NULL, 'newpassword', 'User'

如果帐号为SA执行EXEC sp_password NULL, 'newpassword', sa

5. 怎么判断出一个表的哪些字段不允许为空？

select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where IS_NULLABLE='NO' and

TABLE_NAME=tablename

6. 如何在数据库里找到含有相同字段的表？
a. 查已知列名的情况
SELECT b.name as TableName,a.name as columnname
From syscolumns a INNER JOIN sysobjects b
ON a.id=b.id
AND b.type='U'
AND a.name='你的字段名字'

b. 未知列名查所有在不同表出现过的列名
Select o.name As tablename,s1.name As columnname
From syscolumns s1, sysobjects o
Where s1.id = o.id
And o.type = 'U'
And Exists (
Select 1 From syscolumns s2
Where s1.name = s2.name
And s1.id <> s2.id
)

7. 查询第xxx行数据

假设id是主键：
select *
from (select top xxx * from yourtable) aa
where not exists(select 1 from (select top xxx-1 * from yourtable) bb where aa.id=bb.id)

如果使用游标也是可以的
fetch absolute [number] from [cursor_name]
行数为绝对行数

8. SQL Server日期计算
a. 一个月的第一天
SELECT DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)
b. 本周的星期一
SELECT DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)
c. 一年的第一天
SELECT DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)
d. 季度的第一天
SELECT DATEADD(qq, DATEDIFF(qq,0,getdate()), 0)
e. 上个月的最后一天
SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))
f. 去年的最后一天
SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))
g. 本月的最后一天
SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0))
h. 本月的第一个星期一
select DATEADD(wk, DATEDIFF(wk,0,
dateadd(dd,6-datepart(day,getdate()),getdate())
), 0)
i. 本年的最后一天
SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0))。

1.用一条语句得出某日期所在月份的最大天数？

SELECT DAY(DATEADD(dd, -1, DATEADD(mm, 1, DATEADD(dd, 1-DAY('2004-8-31'), '2004-8-31')))) AS 'Day Number'

 

2.少记录变成多条记录问题 flash.3326.com 中联网FLASH游戏站

有表tbl
日期       收入    支出
2004-02-11 00:00:00 60 45
2004-03-01 00:00:00 60 45
2004-03-02 00:00:00 40 50
2004-03-05 00:00:00 50 40
/*
测试数据：
Create Table tbl([日期] smalldatetime,[收入] int ,[支出] int)

Insert Into tbl
SELECT '2004-02-11', 60, 45
union SELECT '2004-03-01',60, 45
union SELECT '2004-03-02',40, 50
union SELECT '2004-03-05',50, 40
*/

3326.com 中联网

要得到的结果：
日期                                                           收入          支出          余额         
------------------------------------------------------ ----------- ----------- -----------
2004-02-01 00:00:00                                    NULL        NULL        NULL
2004-02-02 00:00:00                                    NULL        NULL        NULL

2004-02-03 00:00:00                                    NULL        NULL        NULL
2004-02-04 00:00:00                                    NULL        NULL        NULL
2004-02-05 00:00:00                                    NULL        NULL        NULL

2004-02-06 00:00:00                                    NULL        NULL        NULL

2004-02-07 00:00:00                                    NULL        NULL        NULL
2004-02-08 00:00:00                                    NULL        NULL        NULL
2004-02-09 00:00:00                                    NULL        NULL        NULL
2004-02-10 00:00:00                                    NULL        NULL        NULL

2004-02-11 00:00:00                                    60            45           15
2004-02-12 00:00:00                                    NULL        NULL        15
2004-02-13 00:00:00                                    NULL        NULL        15
2004-02-14 00:00:00                                    NULL        NULL        15
2004-02-15 00:00:00                                    NULL        NULL        15
2004-02-16 00:00:00                                    NULL        NULL        15
2004-02-17 00:00:00                                    NULL        NULL        15
2004-02-18 00:00:00                                    NULL        NULL        15
2004-02-19 00:00:00                                    NULL        NULL        15
2004-02-20 00:00:00                                    NULL        NULL        15
2004-02-21 00:00:00                                    NULL        NULL        15
2004-02-22 00:00:00                                    NULL        NULL        15
2004-02-23 00:00:00                                    NULL        NULL        15
2004-02-24 00:00:00                                    NULL        NULL        15
2004-02-25 00:00:00                                    NULL        NULL        15

2004-02-26 00:00:00                                    NULL        NULL        15
2004-02-27 00:00:00                                    NULL        NULL        15
2004-02-28 00:00:00                                    NULL        NULL        15
2004-02-29 00:00:00                                    NULL        NULL        15
2004-03-01 00:00:00                                    60            45           30
2004-03-02 00:00:00                                    40            50           20
2004-03-03 00:00:00                                    NULL        NULL        20

2004-03-04 00:00:00                                    NULL        NULL        20
2004-03-05 00:00:00                                    50            40           30
2004-03-06 00:00:00                                    NULL        NULL        30

2004-03-07 00:00:00                                    NULL        NULL        30
2004-03-08 00:00:00                                    NULL        NULL        30
2004-03-09 00:00:00                                    NULL        NULL        30
2004-03-10 00:00:00                                    NULL        NULL        30
2004-03-11 00:00:00                                    NULL        NULL        30
2004-03-12 00:00:00                                    NULL        NULL        30
2004-03-13 00:00:00                                    NULL        NULL        30
2004-03-14 00:00:00                                    NULL        NULL        30

2004-03-15 00:00:00                                    NULL        NULL        30
2004-03-16 00:00:00                                    NULL        NULL        30
2004-03-17 00:00:00                                    NULL        NULL        30
2004-03-18 00:00:00                                    NULL        NULL        30

2004-03-19 00:00:00                                    NULL        NULL        30
2004-03-20 00:00:00                                    NULL        NULL        30
2004-03-21 00:00:00                                    NULL        NULL        30
2004-03-22 00:00:00                                    NULL        NULL        30
2004-03-23 00:00:00                                    NULL        NULL        30
2004-03-24 00:00:00                                    NULL        NULL        30
2004-03-25 00:00:00                                    NULL        NULL        30
2004-03-26 00:00:00                                    NULL        NULL        30
2004-03-27 00:00:00                                    NULL        NULL        30
2004-03-28 00:00:00                                    NULL        NULL        30
2004-03-29 00:00:00                                    NULL        NULL        30
2004-03-30 00:00:00                                    NULL        NULL        30
2004-03-31 00:00:00                                    NULL        NULL        30

答案：

SELECT Y.[日期], tbl.[收入], tbl.[支出], (
    SELECT SUM(ISNULL(tbl.[收入], 0)-ISNULL(tbl.[支出], 0)) FROM tbl WHERE [日期]<=Y.[日期]) AS [余额]
        FROM tbl RIGHT JOIN (
            SELECT DATEADD(dd, N.i, DATEADD(dd, 1-DAY(m.MinDay), m.MinDay)) AS [日期]
            FROM (
                SELECT 0 AS i
                UNION ALL SELECT 1
                UNION ALL SELECT 2
                UNION ALL SELECT 3
                UNION ALL SELECT 4

                UNION ALL SELECT 5
                UNION ALL SELECT 6
                UNION ALL SELECT 7
                UNION ALL SELECT 8
                UNION ALL SELECT 9
                UNION ALL SELECT 10
                UNION ALL SELECT 11
                UNION ALL SELECT 12
                UNION ALL SELECT 13
                UNION ALL SELECT 14
                UNION ALL SELECT 15
                UNION ALL SELECT 16
                UNION ALL SELECT 17
              UNION ALL SELECT 18
                UNION ALL SELECT 19
                UNION ALL SELECT 20
                UNION ALL SELECT 21
                UNION ALL SELECT 22
                UNION ALL SELECT 23

                UNION ALL SELECT 24
                UNION ALL SELECT 25
                UNION ALL SELECT 26
                UNION ALL SELECT 27
                UNION ALL SELECT 28
                UNION ALL SELECT 29
                UNION ALL SELECT 30
                UNION ALL SELECT 31
            ) N,
            (
                SELECT MIN(日期) AS MinDay
                FROM tbl
                GROUP BY DATEDIFF(month, 0, 日期)
            ) M
            WHERE DATEDIFF(mm, DATEADD(dd, N.i, DATEADD(dd, 1-DAY(m.MinDay), m.MinDay)), M.MinDay)=0) AS Y
        ON tbl.[日期]=Y.日期

说明：得到表中最小的未使用的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)

1、说明：创建数据库
CREATE DATABASE database-name
2、说明：删除数据库
drop database dbname
3、说明：备份sql server
--- 创建 备份数据的 device
USE master
EXEC sp_addumpdevice 'disk', 'testBack', 'c:/mssql7backup/MyNwind_1.dat'
--- 开始 备份
BACKUP DATABASE pubs TO testBack
4、说明：创建新表
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..)
根据已有的表创建新表：
A：create table tab_new like tab_old (使用旧表创建新表)
B：create table tab_new as select col1,col2… from tab_old definition only
5、说明：删除新表drop table tabname
6、说明：增加一个列
Alter table tabname add column col type
注：列增加后将不能删除。DB2中列加上后数据类型也不能改变，唯一能改变的是增加varchar类型的长度。
7、说明：添加主键： Alter table tabname add primary key(col)
说明：删除主键： Alter table tabname drop primary key(col)
8、说明：创建索引：create [unique] index idxname on tabname(col….)
删除索引：drop index idxname
注：索引是不可更改的，想更改必须删除重新建。
9、说明：创建视图：create view viewname as select statement
删除视图：drop view viewname
说明：几个高级查询运算词
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)，不消除重复行。
注：使用运算词的几个查询结果行必须是一致的。

1.查询和关闭锁表session

Select v2.username, v2.sid, v2.serial#, v2.logon_time
From v$locked_object v1, v$session v2
 Where v1.session_id = v2.sid
 order by v2.logon_time;
 
 alter       system       kill       session       ''26,6469'';

2.统计 (相当于查找选了所有课程的学生)

select distinct(test.person_age)
  from test
 where test.person_age in
       (select test.person_age
          from test
         group by test.person_age
        having count(*) = (select count(distinct(test.person_sex)) from test));

5.找出表中某一列相同的数据行
select  *from  table  where  (column  in(select  column  from  table  group  by  column  having  count(*)> 1)

6.如何取得表中第6到第10条记录的值

假设ddl语句如下：
　　CREATE TABLE T(ID VARCHAR2(4) PRIMARY KEY, VALUE INT)
　　那么第一种方法就是取出前5条，再取出前10条，然后采用集合运算的方法把前10条减去前5条就OK了，SQL语句如下:

　　SELECT * FROM T WHERE ROWNUM ＜= 10
　　MINUS
　　SELECT * FROM T WHERE ROWNUM ＜= 5;
　　另外一种方法，采用子查询:
　　子查询的这种方法相对比较复杂一点，不过性能要比刚才的集合相减要好一些。这种方法首先在子查询中得到前10条数据，顺路也取得前10条数据的rownum，然后再一次查询的时候取得刚才查询的rownum大于5的那些数据。SQL语句如下:
　　SELECT ID, VALUE FROM
　　(SELECT ID, VALUE, ROWNUM R FROM T WHERE R ＜＝10)
　　WHERE
　　R ＞ 5;
　　通过上面的语句，就得到了6到第10条数据了。

6. 如何在数据库里找到含有相同字段的表？
a. 查已知列名的情况
SELECT b.name as TableName,a.name as columnname
From syscolumns   a INNER JOIN   sysobjects b  

ON a.id=b.id  
AND b.type=''U''  
AND a.name=''你的字段名字''

b. 未知列名查所有在不同表出现过的列名
Select o.name As tablename,s1.name As columnname
From syscolumns s1, sysobjects o
Where s1.id = o.id
  And o.type = ''U''
  And Exists (
    Select 1 From syscolumns s2  
    Where s1.name = s2.name  
    And s1.id <> s2.id
    )

7. 查询第xxx行数据

假设id是主键：
select *
from (select top xxx * from yourtable) aa
where not exists(select 1 from (select top xxx-1 * from yourtable) bb where aa.id=bb.id)

如果使用游标也是可以的
fetch absolute [number] from [cursor_name]
行数为绝对行数

8. SQL Server日期计算
a. 一个月的第一天
SELECT DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)
b. 本周的星期一
SELECT DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)
c. 一年的第一天
SELECT DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)
d. 季度的第一天
SELECT DATEADD(qq, DATEDIFF(qq,0,getdate()), 0)
e. 上个月的最后一天
SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))
f. 去年的最后一天
SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))
g. 本月的最后一天
SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0))
h. 本月的第一个星期一
select DATEADD(wk, DATEDIFF(wk,0,                                      
                    dateadd(dd,6-datepart(day,getdate()),getdate())    
                                                                ), 0)    
i. 本年的最后一天
SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0))。
-----------------------------------------------------------------------
1.按姓氏笔画排序:
Select * From TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as

2.数据库加密:
select encrypt(''原始密码'')
select pwdencrypt(''原始密码'')
select pwdcompare(''原始密码'',''加密后密码'') = 1--相同；否则不相同 encrypt(''原始密码'')
select pwdencrypt(''原始密码'')
select pwdcompare(''原始密码'',''加密后密码'') = 1--相同；否则不相同

3.取回表中字段:
declare @list varchar(1000),@sql nvarchar(1000)
select @list=@list+'',''+b.name from sysobjects a,syscolumns b where a.id=b.id and a.name=''表A''
set @sql=''select ''+right(@list,len(@list)-1)+'' from 表A''
exec (@sql)

4.查看硬盘分区:
EXEC master..xp_fixeddrives

5.比较A,B表是否相等:
if (select checksum_agg(binary_checksum(*)) from A)
  =
  (select checksum_agg(binary_checksum(*)) from B)
print ''相等''
else
print ''不相等''

6.杀掉所有的事件探察器进程:
DECLARE hcforeach CURSOR GLOBAL FOR SELECT ''kill ''+RTRIM(spid) FROM master.dbo.sysprocesses
WHERE program_name IN(''SQL profiler'',N''SQL 事件探查器'')
EXEC sp_msforeach_worker ''?''

7.记录搜索:
开头到N条记录
Select Top N * From 表
-------------------------------
N到M条记录(要有主索引ID)
Select Top M-N * From 表 Where ID in (Select Top M ID From 表) Order by ID Desc
----------------------------------
N到结尾记录
Select Top N * From 表 Order by ID Desc

8.如何修改数据库的名称:
sp_renamedb ''old_name'', ''new_name''

9：获取当前数据库中的所有用户表
select Name from sysobjects where xtype=''u'' and status>=0

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

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

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

13：查询用户创建的所有数据库
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

14：查询某一个表的字段和数据类型
select column_name,data_type from information_schema.columns
where table_name = ''表名''