计算机SQL Server常用场理命令小结_MsSql_脚本之家

2019-11-23 23:08 来源:未知

之前就想要把一些 SQL 的常用函数记录下来,不过一直没有实行。。。嘿嘿。。。

  1. 查看数据库的版本 select @@version 2. 查看数据库所在机器操作系统参数 exec master..xp_msver 3. 查看数据库启动的参数 sp_configure 4. 查看数据库启动时间 select convert,login_time,120) from master..sysprocesses where spid=1 查看数据库服务器名和实例名 print 'Server Name...............:' convert,@@SERVERNAME) print 'Instance..................:' convert,@@SERVICENAME) 5. 查看所有数据库名称及大小 sp_helpdb 重命名数据库用的SQL sp_renamedb 'old_dbname', 'new_dbname' 6. 查看所有数据库用户登录信息 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个表 查看某数据库下某个数据对象的索引信息 sp_helpindex @objname 还可以用SP_NChelpindex过程查看更详细的索引情况 SP_NChelpindex @objname clustered索引是把记录按物理顺序排列的,索引占的空间比较少。 对键值DML操作十分频繁的表我建议用非clustered索引和约束,fillfactor参数都用默认值。 查看某数据库下某个数据对象的的约束信息 sp_helpconstraint @objname 9.查看数据库里所有的存储过程和函数 use @database_name sp_stored_procedures 查看存储过程和函数的源代码 sp_helptext [url=mailto:'@procedure_name']'@procedure_name'[计算机,/url] 查看包含某个字符串@str的数据对象名称 select distinct object_name from syscomments where text like [url=mailto:'%@str%']'%@str%'[/url] 创建加密的存储过程或函数在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 如果某些文章文件较大,收缩简单恢复模式数据库文章,收缩后@database_name_log的大小单位为M backup log @database_name with no_log dbcc shrinkfile (@database_name_log, 5) 13.查看数据库在哪里 SELECT * FROM sysfiles

今天有朋友推荐个活就是把Sql Server 2005的数据库转到Sql Server 2000里,但实际操作起来遇到的问题还不少,网上搜了一些办法,在此陈列出来,并把我使用时遇到的问题及解决方案一并记录下来,以备后期查阅

格式: CONVERT(data_type,expression[,style]) 说明: 此样式一般在时间类型(datetime,smalldatetime)与字符串类型(nchar,nvarchar,char,varchar) 相互转换的时候才用到. 例子: SELECT CONVERT,101) now 结果为: now --------------------------------------- |09/15/2001 Style数字在转换时间时的含义如下: 复制代码 代码如下: Style | Style | 输入输出格 式 0 | 100 | mon dd yyyy hh:miAM 1 | 101 美国 | mm/dd/yy 2 | 102 ANSI | yy-mm-dd 3 | 103 英法 | dd/mm/yy 4 | 104 德国 | dd.mm.yy 5 | 105 意大利 | dd-mm-yy 6 | 106 | dd mon yy 7 | 107 | mon dd,yy 8 | 108 | hh:mm:ss 9 | 109 | mon dd yyyy hh:mi:ss:mmmmAM 10 | 110 美国 | mm-dd-yy 11 | 111 日本 | yy/mm/dd 12 | 112 ISO | yymmdd 13 | 113 欧洲默认值 | dd mon yyyy hh:mi:ss:mmm 14 | 114 | hh:mi:ss:mmm 20 | 120 ODBC 规范 | yyyy-mm-dd hh:mi:ss 21 | 121 | yyyy-mm-dd hh:mi:ss:mmm 语句及查询结果: 复制代码 代码如下: SELECT CONVERT, GETDATE: 05 16 2006 10:57AM SELECT CONVERT, GETDATE: 05/16/06 SELECT CONVERT, GETDATE: 06.05.16 SELECT CONVERT, GETDATE: 16/05/06 SELECT CONVERT, GETDATE: 16.05.06 SELECT CONVERT, GETDATE: 16-05-06 SELECT CONVERT, GETDATE: 16 05 06 SELECT CONVERT, GETDATE: 05 16, 06 SELECT CONVERT, GETDATE: 10:57:46 SELECT CONVERT, GETDATE: 05 16 2006 10:57:46:827AM SELECT CONVERT, GETDATE: 05-16-06 SELECT CONVERT, GETDATE: 06/05/16 SELECT CONVERT, GETDATE: 060516 SELECT CONVERT, GETDATE: 16 05 2006 10:57:46:937 SELECT CONVERT, GETDATE: 10:57:46:967 SELECT CONVERT, GETDATE: 2006-05-16 10:57:47 SELECT CONVERT, GETDATE: 2006-05-16 10:57:47.157 SELECT CONVERT, GETDATE: 05/16/06 10:57:47 AM SELECT CONVERT, GETDATE: 2006-05-16 SELECT CONVERT, GETDATE: 10:57:47 SELECT CONVERT, GETDATE: 2006-05-16 10:57:47.250 SELECT CONVERT, GETDATE: 05 16 2006 10:57AM SELECT CONVERT, GETDATE: 05/16/2006 SELECT CONVERT, GETDATE: 2006.05.16 SELECT CONVERT, GETDATE: 16/05/2006 SELECT CONVERT, GETDATE: 16.05.2006 SELECT CONVERT, GETDATE: 16-05-2006 SELECT CONVERT, GETDATE: 16 05 2006 SELECT CONVERT, GETDATE: 05 16, 2006 SELECT CONVERT, GETDATE: 10:57:49 SELECT CONVERT, GETDATE: 05 16 2006 10:57:49:437AM SELECT CONVERT, GETDATE: 05-16-2006 SELECT CONVERT, GETDATE: 2006/05/16 SELECT CONVERT, GETDATE: 20060516 SELECT CONVERT, GETDATE: 16 05 2006 10:57:49:513 SELECT CONVERT, GETDATE: 10:57:49:547 SELECT CONVERT, GETDATE: 2006-05-16 10:57:49 SELECT CONVERT, GETDATE: 2006 SQL常用到的CONVERT()函数 次次用,次次查,次次忘,干脆记下来。 复制代码 代码如下: SELECT CONVERT, GETDATE: 05 16 2006 10:57AM SELECT CONVERT, GETDATE: 05/16/06 SELECT CONVERT, GETDATE: 06.05.16 SELECT CONVERT, GETDATE: 16/05/06 SELECT CONVERT, GETDATE: 16.05.06 SELECT CONVERT, GETDATE: 16-05-06 SELECT CONVERT, GETDATE: 16 05 06 SELECT CONVERT, GETDATE: 05 16, 06 SELECT CONVERT, GETDATE: 10:57:46 SELECT CONVERT, GETDATE: 05 16 2006 10:57:46:827AM SELECT CONVERT, GETDATE: 05-16-06 SELECT CONVERT, GETDATE: 06/05/16 SELECT CONVERT, GETDATE: 060516 SELECT CONVERT, GETDATE: 16 05 2006 10:57:46:937 SELECT CONVERT, GETDATE: 10:57:46:967 SELECT CONVERT, GETDATE: 2006-05-16 10:57:47 SELECT CONVERT, GETDATE: 2006-05-16 10:57:47.157 SELECT CONVERT, GETDATE: 05/16/06 10:57:47 AM SELECT CONVERT, GETDATE: 2006-05-16 SELECT CONVERT, GETDATE: 10:57:47 SELECT CONVERT, GETDATE: 2006-05-16 10:57:47.250 SELECT CONVERT, GETDATE: 05 16 2006 10:57AM SELECT CONVERT, GETDATE: 05/16/2006 SELECT CONVERT, GETDATE: 2006.05.16 SELECT CONVERT, GETDATE: 16/05/2006 SELECT CONVERT, GETDATE: 16.05.2006 SELECT CONVERT, GETDATE: 16-05-2006 SELECT CONVERT, GETDATE: 16 05 2006 SELECT CONVERT, GETDATE: 05 16, 2006 SELECT CONVERT, GETDATE: 10:57:49 SELECT CONVERT, GETDATE: 05 16 2006 10:57:49:437AM SELECT CONVERT, GETDATE: 05-16-2006 SELECT CONVERT, GETDATE: 2006/05/16 SELECT CONVERT, GETDATE: 20060516 SELECT CONVERT, GETDATE: 16 05 2006 10:57:49:513 SELECT CONVERT, GETDATE: 10:57:49:547 SELECT CONVERT, GETDATE: 2006-05-16 10:57:49 SELECT CONVERT, GETDATE: 2006-05-16 10:57:49.700 SELECT CONVERT, GETDATE: 2006-05-16T10:57:49.827 SELECT CONVERT, GETDATE: 18 ???? ?????? 1427 10:57:49:907AM SELECT CONVERT, GETDATE: 18/04/1427 10:57:49:920AM

本文实例讲述了SQL Server查询前N条记录的常用方法。分享给大家供大家参考。具体如下:

直到今天用到substring()这个函数,C# 里面这个方法起始值是 0,而 SQL 里面起始值是 1。傻傻分不清楚。。。

前几步和网上说的一样,先生成2000的脚本,然后再到2000里面执行。操作如下:

SQL Server查询前N条记录是我们经常要用到的操作,下面对SQL Server查询前N条记录的方法作了详细的介绍,如果您感兴趣的话,不妨一看。

这篇博客作为记录 SQL 的函数的使用方法,想到哪里用到哪里就写到哪里。。。

打开 SQL Server 2005 的Manger Studio

SQL Server查询前N条记录:

SubString():用于截取指定字符串的方法。该方法有三个参数:

-- 打开"对象资源管理器", 连接到你的实例

因为id可能不是连续的,所以不能用取得10

参数1:用于指定要操作的字符串。

找到你要转换的数据库(建议先将此数据库里的登录名清空,否则转换会出现问题)

一、搜索前20条记录,指定不包括前10条

参数2:用于指定要截取的字符串的起始位置,起始值为 1 。

-- 右键选择要转成 SQL Server2000 的数据库

语句:复制代码 代码如下:select top 20 * from tbl where id not in (select top 10 id from tbl)二、搜索记录生成临时表,建立临时表的自增id。通过取得自增id的10复制代码 代码如下:select identity as id,* into #temp from tbl; select * from #temp where id between 10 and 20

参数3:用于指定要截取的长度。

-- 选择 任务

第二个方法实际上是两条语句,但你可以让他连续执行,就像一条语句一样完成任务。

select substring -- 返回 abcselect substring -- 返回 1,即第一位,最好不要这样做

-- 选择 生成脚本

三、朋友们觉得第一种方法效率太低,经过讨论,得出第三种方法:语句:复制代码 代码如下:SELECT TOP 10 * FROM (SELECT TOP 20 * FROM tblORDER BY id) as tbl2 ORDER BY tbl2.id DESC

Left():用于返回指定字符串中指定长度的左侧部分。该方法有两个参数:

-- 在"脚本向导"的"选择数据库"中, 确定选择的是要转到2000的库

希望本文所述对大家的SQL Server数据库程序设计有所帮助。

参数1:用于指定要操作的字符串。

-- 勾选"为所选数据库中的所有对象编写脚本"

参数2:用于指定要返回的子字符串的长度。

-- 在接下来的"选择脚本选项"中, 找到"为服务器版本编写脚本"项, 选择"SQL Server 2000"

select LEFT -- 返回 abcselect LEFT -- 返回 左侧部分

-- 其他选项根据需要设置

Right():用于返回指定字符串中指定长度的右侧部分。该方法有两个参数:

-- 最后把脚本保存到一个 .sql 脚本文件

参数1:用于指定要操作的字符串。

复制代码 代码如下:有可能会遇到的问题:目标服务器版本不支持该对象或不支持该对象的一个属性。 (Microsoft.SqlServer.Smo)脚本无法成功生成。此问题是由于“数据库兼容性级别”导致的。解决方案:server有个改兼容性级别的语句:sp_dbcmptlevel [ [ @dbname = ] name ] [ , [ @new_cmptlevel = ] version ]语句如下:EXEC sp_dbcmptlevel '你的数据库名', '80';GO这个80就是各个版本对应的版本号60 = SQL Server 6.065 = SQL Server 6.570 = SQL Server 7.080 = SQL Server 2000 ——8.090 = SQL Server 2005 ——9.0由于是转到2000于是兼容性级别就要调到80

参数2:用于指定要返回的子字符串的长度。

  1. 在2000中创建目标数据库在查询分析器(或2005的manger studio在打开脚本文件), 连接到SQL Server 2000,执行上面生成的脚本.以创建一个新的数据库 。
select RIGHT -- 返回 123select RIGHT -- 返回 右侧部分

复制代码 代码如下:有可能会出现的问题:max附近有语法错误server2000里面没有max这种用法,所以建议大家把SQL脚本里的MAX改成一个很大的数就是了,比如4000。odb问题其他数据库我不知道,但是不论你用server什么数据库,只要不是你自己的,通常管理员是不会给你配odb权限的,所以把所有的odb檫掉就可以了。另外还有个办法,用你的远程数据库登录名可以改成那个用户的所有者,在本地也修改一下,然后再运行,就OK啦。

CharIndex():用于返回指定字符串中指定子字符串出现的起始位置。如果未找到就返回 0。该方法有两个参数:

  1. 将数据从2005导到2000

参数1:用于指定要进行查找的字符串。

首先打开SQL Server 2005 的Manage Studio-- 打开"对象资源管理器", 连接到你的实例-- 右键选择要转成 SQL Server2000 的数据库--选择 任务 --导出数据(注意,如果你的Manage Studio 为 Express版本,那任务里是没有导出数据选项的,你可以直接找到你的数据安装目录里的Binn文件夹,例如 C:Program FilesMicrosoft SQL Server90DTSBinn 然后执行DTSWizard.exe文件即可。)-- 在"SQL Server 导入和导出向导"的"选择数据源"步骤中, 确定选择的是要导出的数据库-- 在"选择目标"步骤中, 连接到 2000, 并选择步骤2新建的库-- 在"选择源表和源视图"中, 选择所有的表(注意,如果表中有标识的话,要在此处选中表,然后单击下方的“编辑映射”按钮,新开窗口中勾选“使用标识插入”,每个有标识的表都要这样选择一次。还有,此处导入数据如果牵扯到主外键关系或者其他约束的话,一定要分批次按照关系的先后顺序导入,否则会报错。)-- 最后完成

参数2:用于指定用作检索的字符串。

复制代码 代码如下:最后注意下2000的select top 后跟的不可以是表达式,所以如果你的分页里有用到类似declare @pageIndex intselect top *12 newsID from News的话,请一律改成字符串拼接方法,例如下面的例子:declare @strSql varchardeclare @pageIndex intset @pageIndex= *12set @strSql='select top ' str ' newsID from News'exec

select charindex -- 返回 4select charindex -- 返回 0select charindex -- 返回 4

sql server 2005转到2000的步骤2005转到2000的步骤1. 生成for 2000版本的数据库脚本2005 的manger studio-- 打开对象资源管理器, 连接到你的实例-- 右键要转到2000的库-- 任务-- 生成脚本-- 在脚本向导的选择数据库中, 确定选择的是要转到2000的库-- 勾选为所选数据库中的所有对象编写脚本-- 在接下来的选择脚本选项中, 将”编写创建数据库的脚本”设为True,找到为服务器版本编写脚本项, 选择SQL Server 2000-- 其他选项根据需要设置-- 最后把脚本保存到一个 .sql 脚本文件

Stuff():用于删除指定长度的字符,并在删除的位置插入新的字符/值。该方法有四个参数:

  1. 在2000中创建目标数据库在查询分析器(或2005的manger studio在打开脚本文件), 连接到SQL Server 2000,执行上面生成的脚本.以创建一个新的数据库

  2. 将数据从2005导到2000打开SQL Server 2000的企业管理器-- 右键要导入数据的数据库-- 任务-- 导入数据-- 在SQL Server 导入和导出向导的选择数据源步骤中, 确定选择的是要导出的数据库-- 在选择数据源步骤中, 连接到 2005, 并选择源数据库-- 在选择源表和源视图中, 选择所有的表-- 最后完成

参数1:用于指定要操作的字符串。

参数2:用于指定要删除字符的起始位置。

参数3:用于指定要删除字符的长度。

参数4:用于指定在删除的位置插入的新的字符串/值。

select stuff('123abc456',4,3,'ABC') -- 返回 123ABC456select stuff -- 返回 abc456,用空字符串替代

Len():用于返回指定文本的值的长度。前导空格计算在内,尾随空格不计算在内。该方法有一个参数:

参数1:用于指定要操作的文本或字符串。

select len -- 返回 3select len -- 返回 3

Difference():用于返回一个整数值,指示两个字符表达式的 SOUNDEX 值之间的差异。那么什么是 SOUNDEX 值呢?先记着,下面轮到它了。

返回的值从 0 到 4 不等:0 表示几乎不同或完全不同,4 表示几乎相同或完全相同。该方法有两个参数:

参数1:用于指定要进行比对的第一个字符串 SOUNDEX 值 。

参数2:用于指定要进行比对的第二个字符串 SOUNDEX 值。

select difference -- 返回 2select difference -- 返回 4

Soundex():用于返回指定字符串的 SOUNDEX 值。SOUNDEX是一种语音算法,利用英文字的读音计算近似值,值由四个字符构成,第一个字符为英文字母,后三个为数字。在拼音文字中有时会有会念但不能拼出正确字的情形,可用 Soundex 做类似模糊匹配的效果。这里的模糊匹配跟 LIKE 不同。

-- 将英文字按以下规则替换(不使用第一个字符进行匹配,并且不使用对应值为 0 的英文字符的值) a e h i o u w y -> 0 b f p v -> 1 c g j k q s x z -> 2 d t -> 3 l -> 4 m n -> 5 r -> 6

如果字符串中存在拥有相同对应数字的2个或以上的字母在一起,则删除其他的,只保留1个。去除对应值为 0 的字符,只返回前4个字节,不够用 0 填充。

select soundex -- 返回 S215select soundex -- 返回 S210select soundex -- 返回 0000select soundex -- 返回 0000

PS:除英文字符以外的字符都将返回 0000,所以上面的方法 Difference。

Lower():用于返回指定英文字符串的小写形式的字符串。如果不为英文字符串,则返回原值。该方法有一个参数:

参数1:用于指定要转换为小写形式的字符串。

select lower -- 返回 abcselect lower -- 返回 123

Upper():用于返回指定英文字符串的大写形式的字符串。如果不为英文字符串,则返回原值。该方法有一个参数:

参数1:用于指定要转换为大写形式的字符串。

select upper -- 返回 ABCselect upper -- 返回 123

Ltrim():用于返回删除前导空格之后的字符串。该方法有一个参数:

参数1:用于指定要进行删除前导空格操作的字符串。

select ltrim -- 返回 123select ltrim -- 返回 好多空格select len -- 返回 11select len -- 返回 3

Rtrim():用于返回截断尾随空格之后的字符串。该方法有一个参数:

参数1:用于指定要进行截断尾随空格操作的字符串。

select rtrim -- 返回 123select len -- 返回 3

Replace():用第三个表达式替换第一个字符串表达式中出现的所有第二个给定字符串表达式。该方法有三个参数:

参数1:用于指定要操作的字符串,即被匹配的字符串。

参数2:用于指定要进行匹配的字符串。

参数3:用于指定用作替换存在的匹配项的字符串。

--把 abc 替换为 xxx select replace('123abc456','abc','xxx') -- 返回 123xxx456-- 用空字符串替换匹配项select replace -- 返回 123456

以上所述是小编给大家介绍的SQL Server 常用函数使用方法小结,希望对大家有所帮助,如果大家有任何疑问欢迎给我留言,小编会及时回复大家的,在此也非常感谢大家对脚本之家网站的支持!

版权声明:本文由19463331韦德国际发布于计算机网络,转载请注明出处:计算机SQL Server常用场理命令小结_MsSql_脚本之家