一些有用的sql语句整理 推荐收藏

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
10、说明:几个简单的基本的sql语句 选择:select * from table1 where 范围
插入:insert into table1(field1,field2) values(value1,value2)
删除:delete from table1 where 范围 更新:update table1 set
field1=value1 where 范围 查找:select * from table1 where field1 like
‘%value1%’ —like的语法很精妙,查资料! 排序:select * from table1
order by field1,field2 [desc] 总数:select count * as totalcount from
table1 求和:select sum(field1) as sumvalue from table1 平均:select
avg(field1) as avgvalue from table1 最大:select max(field1) as maxvalue
from table1 最小:select min(field1) as minvalue from table1
11、说明:几个高级查询运算词 A: UNION 运算符 UNION
运算符通过组合其他两个结果表并消去表中任何重复行而派生出一个结果表。当
ALL 随 UNION
一起使用时,不消除重复行。两种情况下,派生表的每一行不是来自 TABLE1
就是来自 TABLE2。 B: EXCEPT 运算符 EXCEPT 运算符通过包括所有在 TABLE1
中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随
EXCEPT 一起使用时 (EXCEPT ALL),不消除重复行。 C: INTERSECT 运算符
INTERSECT 运算符通过只包括 TABLE1 和 TABLE2
中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT
一起使用时 (INTERSECT ALL),不消除重复行。
注:使用运算词的几个查询结果行必须是一致的。 12、说明:使用外连接
A、left outer join:
左外连接:结果集几包括连接表的匹配行,也包括左连接表的所有行。 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
B:right outer join:
右外连接(右连接):结果集既包括连接表的匹配连接行,也包括右连接表的所有行。
C:full outer join:
全外连接:不仅包括符号连接表的匹配行,还包括两个连接表中的所有记录。
其次,大家来看一些不错的sql语句 1、说明:复制表(只复制结构,源表名:a
新表名:b) (Access可用) 法一:select * into b from a where 11
法二: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 ext: 1.
查看数据库的版本 select @@version 常见的几种SQL SERVER打补丁后的版本号:
8.00.194 Microsoft SQL Server 2000 8.00.384 Microsoft SQL Server 2000
SP1 8.00.532 Microsoft SQL Server 2000 SP2 8.00.760 Microsoft SQL Server
2000 SP3 8.00.818 Microsoft SQL Server 2000 SP3 w/ Cumulative Patch
MS03-031 8.00.2039 Microsoft SQL Server 2000 SP4 2.
查看数据库所在机器操作系统参数 exec master..xp_msver 3.
查看数据库启动的参数 sp_configure 4. 查看数据库启动时间 select
convert(varchar(30),login_time,120) from master..sysprocesses where
spid=1 查看数据库服务器名和实例名 print ‘Server Name……………: ‘ +
convert(varchar(30),@@SERVERNAME) print ‘Instance………………: ‘ +
convert(varchar(30),@@SERVICENAME) 5. 查看所有数据库名称及大小
sp_helpdb 重命名数据库用的SQL sp_www.463.com,renamedb ‘old_dbname’, ‘new_dbname’

核心提示:一些有用的sql语句实例以下是相关的操作代码

  1. 查看所有数据库用户登录信息 sp_helplogins
    查看所有数据库用户所属的角色信息 sp_helpsrvrolemember
    修复迁移服务器时孤立用户时,可以用的fix_orphan_user脚本或者LoneUser过程
    更改某个数据对象的用户属主 sp_changeobjectowner [@objectname =]
    ‘object’, [@newowner =] ‘owner’ 注意:
    更改对象名的任一部分都可能破坏脚本和存储过程。
    把一台服务器上的数据库用户登录信息备份出来可以用add_login_to_aserver脚本
    查看某数据库下,对象级用户权限 sp_helprotect 7. 查看链接服务器
    sp_helplinkedsrvlogin 查看远端数据库用户登录信息 sp_helpremotelogin
    8.查看某数据库下某个数据对象的大小 sp_spaceused @objname
    还可以用sp_toptables过程看最大的N(默认为50)个表
    查看某数据库下某个数据对象的索引信息 sp_helpindex @objname
    还可以用SP_NChelpindex过程查看更详细的索引情况 SP_NChelpindex @objname
    clustered索引是把记录按物理顺序排列的,索引占的空间比较少。
    对键值DML操作十分频繁的表我建议用非clustered索引和约束,fillfactor参数都用默认值。
    查看某数据库下某个数据对象的的约束信息 sp_helpconstraint @objname
    9.查看数据库里所有的存储过程和函数 use @database_name
    sp_stored_procedures 查看存储过程和函数的源代码 sp_helptext
    ‘@procedure_name’ 查看包含某个字符串@str的数据对象名称 select distinct
    object_name(id) from syscomments where text like ‘%@str%’
    创建加密的存储过程或函数在AS前面加WITH ENCRYPTION参数
    解密加密过的存储过程和函数可以用sp_decrypt过程
    10.查看数据库里用户和进程的信息 sp_who 查看SQL
    Server数据库里的活动用户和进程的信息 sp_who ‘active’ 查看SQL
    Server数据库里的锁的情况 sp_lock 进程号1–50是SQL
    Server系统内部用的,进程号大于50的才是用户的连接进程.
    spid是进程编号,dbid是数据库编号,objid是数据对象编号
    查看进程正在执行的SQL语句 dbcc inputbuffer ()
    推荐大家用经过改进后的sp_who3过程可以直接看到进程运行的SQL语句 sp_who3
    检查死锁用sp_who_lock过程 sp_who_lock
    11.查看和收缩数据库日志文件的方法 查看所有数据库日志文件大小 dbcc
    sqlperf(logspace)
    如果某些日志文件较大,收缩简单恢复模式数据库日志,收缩后@database_name_log的大小单位为M
    backup log @database_name with no_log dbcc shrinkfile
    (@database_name_log, 5) 12.分析SQL Server SQL 语句的方法: set
    statistics time {on | off} set statistics io {on | off}
    图形方式显示查询执行计划 在查询分析器-查询-显示估计的评估计划(D)-Ctrl-L
    或者点击工具栏里的图形 文本方式显示查询执行计划 set showplan_all {on |
    off} set showplan_text { on | off } set statistics profile { on | off }
    13.出现不一致错误时,NT事件查看器里出3624号错误,修复数据库的方法
    先注释掉应用程序里引用的出现不一致性错误的表,然后在备份或其它机器上先恢复然后做修复操作
    alter database [@error_database_name] set single_user
    修复出现不一致错误的表 dbcc
    checktable(‘@error_table_name’,repair_allow_data_loss)
    或者可惜选择修复出现不一致错误的小型数据库名 dbcc
    checkdb(‘@error_database_name’,repair_allow_data_loss) alter
    database [@error_database_name] set multi_user CHECKDB 有3个参数:
    repair_allow_data_loss
    包括对行和页进行分配和取消分配以改正分配错误、结构行或页的错误,
    以及删除已损坏的文本对象,这些修复可能会导致一些数据丢失。
    修复操作可以在用户事务下完成以允许用户回滚所做的更改。
    如果回滚修复,则数据库仍会含有错误,应该从备份进行恢复。
    如果由于所提供修复等级的缘故遗漏某个错误的修复,则将遗漏任何取决于该修复的修复。
    修复完成后,请备份数据库。 repair_fast
    进行小的、不耗时的修复操作,如修复非聚集索引中的附加键。
    这些修复可以很快完成,并且不会有丢失数据的危险。 repair_rebuild 执行由
    repair_fast 完成的所有修复,包括需要较长时间的修复。
    执行这些修复时不会有丢失数据的危险。 sql语句实例 1 Examples
    ======================================= select id,age,Fullname from
    tableOne a where a.id!=(select max(id) from tableOne b where a.age=b.age
    and a.FullName=b.FullName) =========================================
    delete from dbo.Schedule where RoomID=29 and StartTime’2005-08-08′ and
    EndTime’2006-09-01′ and Remark like ‘preset’ and UserID=107 and (
    (ScheduleID=3177 and ScheduleID=3202 ) or (ScheduleID=3229 and
    ScheduleID=3254) or (ScheduleID=3307 and ScheduleID=3332)
    ========================================= delete tableOne where
    tableOne.id!=(select max(id) from tableOne b where tableOne.age=b.age
    and tableOne.FullName=b.FullName);
    ========================================== DataClient 12/23/2005 5:03:38
    PM select top 5 DOC_MAIN.CURRENT_VERSION_NO as Version,
    DOC_MAIN.MODIFY_DATE as ModifyDT, DOC_MAIN.SUMMARY as Summary,
    DOC_MAIN.AUTHOR_EMPLOYEE_NAME as AuthorName, DOC_MAIN.TITLE as
    Title, DOC_MAIN.DOCUMENT_ID as DocumentID, Attribute.ATTRIBUTE_ID as
    AttributeId, Attribute.CATALOG_ID as CatalogId,
    DOC_STATISTIC.VISITE_TIMES as VisiteTimes, DOC_STATISTIC.DOCUMENT_ID
    as DocumentID2 from DOC_MAIN DOC_MAIN Inner join
    CATALOG_SELF_ATTRIBUTE Attribute on
    DOC_MAIN.CATALOG_ID=Attribute.CATALOG_ID Left join DOC_STATISTIC
    DOC_STATISTIC on DOC_MAIN.DOCUMENT_ID=DOC_STATISTIC.DOCUMENT_ID
    where (DOC_MAIN.AUTHOR_EMPLOYEE_ID = 1) and (Attribute.ATTRIBUTE_ID
    = 11) order by VisiteTimes DESC ====================================
    select top 1 DOCUMENT_ID,EMPLOYEE_NAME,COMMENT_DATE,COMMENT_VALUE
    from dbo.DOC_COMMENT where DOCUMENT_ID=19 and COMMENT_DATE = (select
    max(COMMENT_DATE) from DOC_COMMENT where DOCUMENT_ID=19)
    ==================================== select TITLE, (select top 1
    EMPLOYEE_NAME from dbo.DOC_COMMENT where DOCUMENT_ID=19) Commentman,
    (select top 1 COMMENT_DATE from dbo.DOC_COMMENT where DOCUMENT_ID=19)
    COMMENT_DATE from DOC_MAIN where DOCUMENT_ID=19
    ====================================== alter view ExpertDocTopComment as
    select DOCUMENT_ID, max(ORDER_NUMBER ) as lastednum from
    dbo.DOC_COMMENT group by DOCUMENT_ID go alter view ExpertDocView as
    select TITLE , a.AUTHOR_EMPLOYEE_ID , c.EMPLOYEE_NAME ,
    c.COMMENT_DATE from dbo.DOC_MAIN a left join ExpertDocTopComment b on
    a.DOCUMENT_ID = b.DOCUMENT_ID inner join DOC_COMMENT c on
    b.DOCUMENT_ID = c.DOCUMENT_ID and b.lastednum = c. ORDER_NUMBER
    ====================================== select a.Id ,a.WindowsUsername ,
    0 , 1 , a.Email , case b.EnFirstName when null then a.Username else
    b.EnFirstName end, case b.EnLastName when null then a.Username else
    b.EnLastName end from UUMS_KM.dbo.UUMS_User a left join
    UUMS_KM.dbo.HR_Employee b on a. HR_EmployeeId = b.id
    ===================================== 列出上传文档最多的五个人的ID
    select AUTHOR_EMPLOYEE_ID,count(AUTHOR_EMPLOYEE_ID) from
    dbo.DOC_MAIN group by AUTHOR_EMPLOYEE_ID order by
    count(AUTHOR_EMPLOYEE_ID) 2719 2 6 9 12 30 1 116
    列出上传文档最多的五个人的信息 select distinct AUTHOR_EMPLOYEE_ID
    ,AUTHOR_EMPLOYEE_NAME from dbo.DOC_MAIN where AUTHOR_EMPLOYEE_ID in
    ( select top 5 AUTHOR_EMPLOYEE_ID from dbo.DOC_MAIN group by
    AUTHOR_EMPLOYEE_ID order by count(AUTHOR_EMPLOYEE_ID) )

1Examples =======================================

select id,age,Fullname from tableOne awhere a.id!=(select max(id) from
tableOne b where a.age=b.age and a.FullName=b.FullName)

=========================================

delete from dbo.Schedule where

RoomID=29 and StartTime2005-08-08 and EndTime2006-09-01 and Remark like
preset and UserID=107

and (

(ScheduleID=3177 and ScheduleID=3202 )

or (ScheduleID=3229 and ScheduleID=3254)

or (ScheduleID=3307 and ScheduleID=3332)

=========================================

delete tableOne where tableOne.id!=(select max(id) from tableOne b where
tableOne.age=b.age and tableOne.FullName=b.FullName);

==========================================

DataClient12/23/2005 5:03:38 PM

select top 5

DOC_MAIN.CURRENT_VERSION_NO as Version, DOC_MAIN.MODIFY_DATE as
ModifyDT, DOC_MAIN.SUMMARY as Summary, DOC_MAIN.AUTHOR_EMPLOYEE_NAME
as AuthorName, DOC_MAIN.TITLE as Title, DOC_MAIN.DOCUMENT_ID as
DocumentID,Attribute.ATTRIBUTE_ID as AttributeId, Attribute.CATALOG_ID
as CatalogId,DOC_STATISTIC.VISITE_TIMES as VisiteTimes,
DOC_STATISTIC.DOCUMENT_ID as DocumentID2

from DOC_MAIN DOC_MAIN

Inner join CATALOG_SELF_ATTRIBUTE Attribute on
DOC_MAIN.CATALOG_ID=Attribute.CATALOG_ID

Left join DOC_STATISTIC DOC_STATISTIC on
DOC_MAIN.DOCUMENT_ID=DOC_STATISTIC.DOCUMENT_ID

where (DOC_MAIN.AUTHOR_EMPLOYEE_ID = 1) and (Attribute.ATTRIBUTE_ID
= 11)

order by VisiteTimes DESC

====================================

select top 1 DOCUMENT_ID,EMPLOYEE_NAME,COMMENT_DATE,COMMENT_VALUE

from dbo.DOC_COMMENT

where DOCUMENT_ID=19 and COMMENT_DATE = (select max(COMMENT_DATE)
from DOC_COMMENT where DOCUMENT_ID=19)

====================================

select TITLE, (select top 1 EMPLOYEE_NAME

from dbo.DOC_COMMENT where DOCUMENT_ID=19) Commentman,

(select top 1 COMMENT_DATE

from dbo.DOC_COMMENT where DOCUMENT_ID=19) COMMENT_DATE

from DOC_MAIN where DOCUMENT_ID=19

======================================

alter view ExpertDocTopComment

as

selectDOCUMENT_ID, max(ORDER_NUMBER ) as lastednum

from dbo.DOC_COMMENT

group by DOCUMENT_ID

go

alter view ExpertDocView

as

select TITLE, a.AUTHOR_EMPLOYEE_ID , c.EMPLOYEE_NAME ,
c.COMMENT_DATE

from dbo.DOC_MAINa

发表评论

电子邮件地址不会被公开。 必填项已用*标注