SQL Server 常用命令.docx

1、SQL Server 常用命令SQL Server 2000常用命令(1) 数据记录筛选：sql=select * from 数据表 where 字段名=字段值 order by 字段名 descsql=select * from 数据表 where 字段名 like %字段值% order by 字段名 descsql=select top 10 * from 数据表 where 字段名 order by 字段名 descsql=select * from 数据表 where 字段名 in (值1,值2,值3)sql=select * from 数据表 where 字段名 between 值

2、1 and 值2(2) 更新数据记录：sql=update 数据表 set 字段名=字段值 where 条件表达式sql=update 数据表 set 字段1=值1,字段2=值2 字段n=值n where 条件表达式(3) 删除数据记录：sql=delete from 数据表 where 条件表达式sql=delete from 数据表 (将数据表所有记录删除)(4) 添加数据记录：sql=insert into 数据表 (字段1,字段2,字段3 ) values (值1,值2,值3 )sql=insert into 目标数据表 select * from 源数据表 (把源数据表的记录添加到目

3、标数据表)(5) 数据记录统计函数：AVG(字段名) 得出一个表格栏平均值COUNT(*|字段名) 对数据行数的统计或对某一栏有值的数据行数统计MAX(字段名) 取得一个表格栏最大的值MIN(字段名) 取得一个表格栏最小的值SUM(字段名) 把数据栏的值相加引用以上函数的方法：sql=select sum(字段名) as 别名 from 数据表 where 条件表达式set rs=conn.excute(sql)用 rs(别名) 获取统的计值，其它函数运用同上。(5) 数据表的建立和删除：CREATE TABLE 数据表名称(字段1 类型1(长度),字段2 类型2(长度) )例：CREATE

4、TABLE tab01(name varchar(50),datetime default now()DROP TABLE 数据表名称 (永久性删除一个数据表)4. 记录集对象的方法：rs.movenext 将记录指针从当前的位置向下移一行rs.moveprevious 将记录指针从当前的位置向上移一行rs.movefirst 将记录指针移到数据表第一行rs.movelast 将记录指针移到数据表最后一行rs.absoluteposition=N 将记录指针移到数据表第N行rs.absolutepage=N 将记录指针移到第N页的第一行rs.pagesize=N 设置每页为N条记录、更改表格A

5、LTER TABLE table_nameADD COLUMN column_name DATATYPE说明：增加一个栏位（没有删除某个栏位的语法。ALTER TABLE table_nameADD PRIMARY KEY (column_name)说明：更改表得的定义把某个栏位设为主键。ALTER TABLE table_nameDROP PRIMARY KEY (column_name)说明：把主键的定义删除。、建立索引CREATE INDEX index_name ON table_name (column_name)说明：对某个表格的栏位建立索引以增加查询时的速度。、删除DROP ta

6、ble_nameDROP index_name二、的资料形态 DATATYPEssmallint16 位元的整数。interger32 位元的整数。decimal(p,s)p 精确值和 s 大小的十进位整数，精确值p是指全部有几个数(digits)大小值，s是指小数点後有几位数。如果没有特别指定，则系统会设为 p=5; s=0 。float32位元的实数。double64位元的实数。char(n)n 长度的字串，n不能超过 254。varchar(n)长度不固定且其最大长度为 n 的字串，n不能超过 4000。graphic(n)和 char(n) 一样，不过其单位是两个字元 double-b

7、ytes， n不能超过127。这个形态是为了支援两个字元长度的字体，例如中文字。vargraphic(n)可变长度且其最大长度为 n 的双字元字串，n不能超过 2000。date包含了 年份、月份、日期。time包含了 小时、分钟、秒。timestamp包含了 年、月、日、时、分、秒、千分之一秒。三、资料操作 （Data Manipulation Language)资料定义好之後接下来的就是资料的操作。资料的操作不外乎增加资料（insert)、查询资料（query）、更改资料（update) 、删除资料（delete）四种模式，以下分 别介绍他们的语法：、增加资料：INSERT INTO ta

8、ble_name (column1,column2,.)valueS ( value1,value2, .)说明：1.若没有指定column 系统则会按表格内的栏位顺序填入资料。2.栏位的资料形态和所填入的资料必须吻合。3.table_name 也可以是景观 view_name。INSERT INTO table_name (column1,column2,.)SELECT columnx,columny,. FROM another_table说明：也可以经过一个子查询（subquery）把别的表格的资料填入。、查询资料：基本查询SELECT column1,columns2,.FROM t

9、able_name说明：把table_name 的特定栏位资料全部列出来SELECT *FROM table_nameWHERE column1 = xxxAND column2 yyy OR column3 zzz三)交叉连接交叉连接不带WHERE 子句，它返回被连接的两个表所有数据行的笛卡尔积，返回到结果集合中的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数。例，titles表中有6类图书，而publishers表中有8家出版社，则下列交叉连接检索到的记录数将等于6*8=48行。SELECT type,pub_nameFROM titles CROSS J

10、OIN publishersORDER BY typeUNION运算符可以将两个或两个以上上SELECT语句的查询结果集合合并成一个结果集合显示，即执行联合查询。UNION的语法格式为：select_statementUNION ALL selectstatementUNION ALL selectstatementn其中selectstatement为待联合的SELECT查询语句。ALL选项表示将所有行合并到结果集合中。不指定该项时，被联合查询结果集合中的重复行将只保留一行。联合查询时，查询结果的列标题为第一个查询语句的列标题。因此，要定义列标题必须在第一个查询语句中定义。要对联合查询结果排

11、序时，也必须使用第一查询语句中的列名、列标题或者列序号。在使用UNION 运算符时，应保证每个联合查询语句的选择列表中有相同数量的表达式，并且每个查询选择表达式应具有相同的数据类型，或是可以自动将它们转换为相同的数据类型。在自动转换时，对于数值类型，系统将低精度的数据类型转换为高精度的数据类型。在包括多个查询的UNION语句中，其执行顺序是自左至右，使用括号可以改变这一执行顺序。例如：查询1 UNION (查询2 UNION 查询3)INSERT语句用户可以用INSERT语句将一行记录插入到指定的一个表中。例如，要将雇员John Smith的记录插入到本例的表中，可以使用如下语句：INSERT

12、 INTO EMPLOYEES valueS (Smith,John,1980-06-10, Los Angles,16,45000);通过这样的INSERT语句，系统将试着将这些值填入到相应的列中。这些列按照我们创建表时定义的顺序排列。在本例中，第一个值“Smith”将填到第一个列LAST_NAME中；第二个值“John”将填到第二列FIRST_NAME中以此类推。我们说过系统会“试着”将值填入，除了执行规则之外它还要进行类型检查。如果类型不符（如将一个字符串填入到类型为数字的列中），系统将拒绝这一次操作并返回一个错误信息。如果SQL拒绝了你所填入的一列值，语句中其他各列的值也不会填入。这是

13、因为SQL提供对事务的支持。一次事务将数据库从一种一致性转移到另一种一致性。如果事务的某一部分失败，则整个事务都会失败，系统将会被恢复（或称之为回退）到此事务之前的状态。 回到原来的INSERT的例子，请注意所有的整形十进制数都不需要用单引号引起来，而字符串和日期类型的值都要用单引号来区别。为了增加可读性而在数字间插入逗号将会引起错误。记住，在SQL中逗号是元素的分隔符。同样要注意输入文字值时要使用单引号。双引号用来封装限界标识符。对于日期类型，我们必须使用SQL标准日期格式（yyyy-mm-dd）-查询当前连接的实例名select servername-察看任何数据库属性sp_helpdb

14、master-设置单用户模式，同时立即断开所有用户alter database Northwind set single_user with rollback immediate-恢复正常alter database Northwind set multi_user-察看数据库属性sp_helpdb-察看数据库恢复模式select databasepropertyex(Northwind,recovery)-设置自动创建统计alter database Northwind set auto_create_statistics on/off-设置自动更新统计alter database Nort

15、hwind set auto_update_statistics on/off-查看作业列表select * from msdb.sysjobs-查看作业详细信息exec msdb.sp_help_job job_name = HQCRM-CrmNew-CrmNew_2Dimension-NANJINGCRM-216-修改作业信息exec msdb.sp_update_job job_id = 0x4CA27521C9033C48954E7BFC6B965395, enabled = 1-察看服务器角色exec sp_helpsrvrolemember-添加服务器角色exec sp_addsr

16、vrolemember member_name,sysadmin-删除服务器角色exec sp_dropsrvrolemember member_name,sysadmin-察看数据库角色exec sp_helprole-添加数据库角色exec sp_addrole role_name-删除数据库角色exec sp_droprole role_name-查看用户信息exec sp_helpuser-注意删除guest帐户use dbnamegoexec sp_dropuser guest-修改对象拥有者exec sp_changeobjectowner old_onwer.table_name

17、, new_owner-查看BUILTINAdministrators帐号exec master.xp_logininfo BUILTINAdministrators, members-修改默认数据库exec sp_defaultdb login, defaultdb-创建新的登陆exec sp_addlogin loginame = esProgram,passwd = h1J2P97vfdlK34,defdb = career-更改登陆数据库访问权限use exec sp_grantdbaccess loginame =esProgram,name_in_db = esProgram-把角

18、色db_appuser添加用户帐号中use exec sp_addrolemember rolename = db_appuser,membername = esProgram-预测增长use master/* Procedure for 8.0 server */create proc usp_databasesasset nocount ondeclare name sysnamedeclare SQL nvarchar(600)/* Use temporary table to sum up database size w/o using group by */create table

19、#databases ( DATABASE_NAME sysname NOT NULL, size int NOT NULL)declare c1 cursor for select name from master.dbo.sysdatabases where has_dbaccess(name) = 1 - Only look at databases to which we have accessopen c1fetch c1 into namewhile fetch_status = 0begin select SQL = insert into #databases select N

20、+ name + , sum(size) from + QuoteName(name) + .dbo.sysfiles /* Insert row for each database */ execute (SQL) fetch c1 into nameenddeallocate c1select DATABASE_NAME, DATABASE_SIZE = size*8,/* Convert from 8192 byte pages to K */ RUN_DT=GETDATE()from #databasesorder by 1GOcreate table DatabaseSizeRepo

21、rt(Database_Name Varchar(32),Database_Size int,CreateDt datetime)insert into DatabaseSizeReport exec usp_databasesselect * from DatabaseSizeReport-快捷方式ALT-F1 sp_helpCTRL-1 sp_whoCTRL-2 sp_lock-查看对象空间exec sp_spaceused /-察看文件是否存在exec xp_fileexist c:test.txt-察看文件详细信息exec master.xp_getfiledetails c:test

22、.txt-查看系统出错日志set nocount on -执行一个查询或者是存储过程是要保证这个是开的。可以提高速度create table #sunying_error_lg(errortext varchar(500),continuerow int)insert into #sunying_error_lg exec master.xp_readerrorlogselect * from #sunying_error_lgdrop table #sunying_error_lg-获得dbcc命令的完整列表dbcc help(?)-检测数据库损坏情况alter database set s

23、ingle_user with rollback immediatedbcc checkdb (,repair_fast)-数据库损坏处理步骤1.检查SQL SERVER和Windows NT错误日志，看是否能找出问题所在。例如，可能硬盘驱动器已满。2.以单用户模式启动SQL Server。3.用dbname参数执行sp_resetstatus(比如，sp_resetstatus dbname=pubs)。来使数据库摆脱损坏状态4.以单用户模式重新启动SQL Server。5.如果数据库仍处于置疑状态，可将它重设回正常模式，并试着用下面命令转储置疑的数据库的事务：dump transactio

24、n Northwind with no_log6.再次以单用户模式启动SQL Server，如果数据库出现了，可对其进行详细的DBCC检查(checkdb,checkalloc,等等)7.运行一些随机查询，看看是否会遇到问题。8.如果没有问题出现，可停止并重新启动SQL Server，然后将数据库投入生产应用。-将数据库置于紧急模式sp_configure allow updates,1reconfigure with overridegoupdate master.sysdatabases set status=-32768 where name=pubsgosp_configure all

25、ow updates,0reconfigure with overridego-当数据库处于这种模式时,你只能从中读取数据。-查看日志空间dbcc sqlperf(logspace)-查看高速缓存命中率dbcc sqlperf(lrustats)-查看活动事务(open transaction)dbcc opentran-如果有事务是活动的比如(SPID:54 UID:1)，还有看这个事务打开了多久了。如果有人忘了，可以用-kill 54-来干掉他-查看用户使用set命令的全部内容dbcc useroptions-比如可以查看set nocount on-系统函数app_name()函数返回当

26、前从SQL Server请求数据的应用程序名称。get_date()函数返回SQL Server上的当前时间。host_name()函数确定是哪台工作站正在连接到SQL Server。system_user函数提供正在连接的用户的登陆名db_name()告诉你连接是哪个数据库标识列drop table sunying_testcreate table sunying_test (aid int identity,val varchar(500)insert into sunying_test (val) select abc 为1insert into sunying_test (val) s

27、elect abc 为2insert into sunying_test (val) select abc 为3select * from sunying_testselect identity-在不同连接里查identity她返回nullselect identity-和identity相同，不同点即使停止SQL Server并且重新建立连接，这个命令仍然会返回正确的值select ident_current()-查看I/O，执行计划set statistics io onselect count(*) from sunying_test-断开一个数据库的所有用户sp_whocreate p

28、rocedure usp_killusers dbname varchar(50) asset nocount ondeclare strSQL varchar(255)print Killing Usersprint -create table #tmpUsers(spid int,eid int,status varchar(30),loginname varchar(50),hostname varchar(50),blk int,dbname varchar(50),cmd varchar(30)insert into #tmpUsers exec sp_whodeclare logi

29、ncursor cursorread_onlyfor select spid,dbname from #tmpUsers wheredeclare spid varchar(10)declare dbname2 varchar(40)open logincursorfetch next from logincursor into spid,dbname2while (fetch_status-1)beginif (fetch_status-2)begin print Killing set strSQL=KILL exec (strSQL)endfetch next from logincursor into spid,dbname2endclose logincursordeallocate logincursordrop table #tmpUs