金沙国际官网_金沙国际平台登录

因为这个金沙国际官网_金沙国际平台登录网站与很多的大型澳门赌场都有合作,金沙国际官网_金沙国际平台登录尽职尽责,高效执行,保持好奇心,不断学习,追求卓越,点击进入金沙国际官网_金沙国际平台登录马上体验吧,所以现在也正式地开始了营业。

您的位置:金沙国际官网 > 数据库 > SERVER常用语法汇总,索引的作用

SERVER常用语法汇总,索引的作用

发布时间:2019-10-25 15:32编辑:数据库浏览(56)

    阅读目录

    )深入浅出理解索引结构

    转自:http://www.maomao365.com/?p=5416

    • 一、SQL分类
    • 二、基础语句
    • 三、sql技巧
    • 四、(MS SQL Server)SQL语句导入导出大全

    实际上,您可以把索引理解为一种特殊的目录。微软的SQL SERVER提供了两种索引:聚集索引(clustered index,也称聚类索引、簇集索引)和非聚集索引(nonclustered index,也称非聚类索引、非簇集索引)。下面,我们举例来说明一下聚集索引和非聚集索引的区别:

     

    回到目录

    其实,我们的汉语字典的正文本身就是一个聚集索引。比如,我们要查“安”字,就会很自然地翻开字典的前几页,因为“安”的拼音是“an”,而按照拼音排序汉字的字典是以英文字母“a”开头并以“z”结尾的,那么“安”字就自然地排在字典的前部。如果您翻完了所有以“a”开头的部分仍然找不到这个字,那么就说明您的字典中没有这个字;同样的,如果查“张”字,那您也会将您的字典翻到最后部分,因为“张”的拼音是“zhang”。也就是说,字典的正文部分本身就是一个目录,您不需要再去查其他目录来找到您需要找的内容。我们把这种正文内容本身就是一种按照一定规则排列的目录称为“聚集索引”。

    摘要:
    order by 1,2 的含义是对表的第一列  按照从小到大的顺序进行排列

    一、SQL分类

    DDL—数据定义语言(CREATE,ALTER,DROP,DECLARE)

    DML—数据操纵语言(SELECT,DELETE,UPDATE,INSERT)

    DCL—数据控制语言(GRANT,REVOKE,COMMIT,ROLLBACK)

     

    回到目录

    如果您认识某个字,您可以快速地从自动中查到这个字。但您也可能会遇到您不认识的字,不知道它的发音,这时候,您就不能按照刚才的方法找到您要查的字,而需要去根据“偏旁部首”查到您要找的字,然后根据这个字后的页码直接翻到某页来找到您要找的字。但您结合“部首目录”和“检字表”而查到的字的排序并不是真正的正文的排序方法,比如您查“张”字,我们可以看到在查部首之后的检字表中“张”的页码是672页,检字表中“张”的上面是“驰”字,但页码却是63页,“张”的下面是“弩”字,页面是390页。很显然,这些字并不是真正的分别位于“张”字的上下方,现在您看到的连续的“驰、张、弩”三字实际上就是他们在非聚集索引中的排序,是字典正文中的字在非聚集索引中的映射。我们可以通过这种方式来找到您所需要的字,但它需要两个过程,先找到目录中的结果,然后再翻到您所需要的页码。我们把这种目录纯粹是目录,正文纯粹是正文的排序方式称为“非聚集索引”。

     然后再对第二列按照从小到大的顺序进行排列

    二、基础语句

    通过以上例子,我们可以理解到什么是“聚集索引”和“非聚集索引”。进一步引申一下,我们可以很容易的理解:每个表只能有一个聚集索引,因为目录只能按照一种方法进行排序。

      order by 1,2 等同于 order by [第一列],[第二列] 

    2.1、说明:创建数据库

    create database db-name
    

    二、何时使用聚集索引或非聚集索引

     详见以下举例说明  

    2.2、说明:删除数据库

    drop database db-name 
    

    下面的表总结了何时使用聚集索引或非聚集索引(很重要):

     

    2.3、说明:备份sql server

    --- 创建 备份
    USE master
    EXEC sp_addumpdevice 'disk', 'testBack', 'c:mssql7backupMyNwind_1.dat'
    --- 开始 备份
    BACKUP DATABASE pubs TO testBack
    

    动作描述

    使用聚集索引

    使用非聚集索引

    列经常被分组排序

    返回某范围内的数据

    不应

    一个或极少不同值

    不应

    不应

    小数目的不同值

    不应

    大数目的不同值

    不应

    频繁更新的列

    不应

    外键列

    主键列

    频繁修改索引列

    不应

     

    2.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
    

    事实上,我们可以通过前面聚集索引和非聚集索引的定义的例子来理解上表。如:返回某范围内的数据一项。比如您的某个表有一个时间列,恰好您把聚合索引建立在了该列,这时您查询2004年1月1日至2004年10月1日之间的全部数据时,这个速度就将是很快的,因为您的这本字典正文是按日期进行排序的,聚类索引只需要找到要检索的所有数据中的开头和结尾数据即可;而不像非聚集索引,必须先查到目录中查到每一项数据对应的页码,然后再根据页码查到具体内容。

    create table A (keyId varchar(20),name varchar(120))
    go
    insert into A (name,keyId)values(10,'we')
    insert into A (name,keyId)values(2,'b')
    insert into A (name,keyId)values(3,'C')
    insert into A (name,keyId)values(4,'a')
    insert into A (name,keyId)values(5,'E')
    insert into A (name,keyId)values(7,'F')
    go
    select * from A 
    go
    select * from A order by  1,2 
    go
    truncate table A
    drop table A 
    go 
    

    2.5、说明:删除新表

    drop table tabname
    

    三、结合实际,谈索引使用的误区

     

    2.6、说明:增加一个列

    Alter table tabname add column col type
    

    注:列增加后将不能删除。DB2中列加上后数据类型也不能改变,唯一能改变的是增加varchar类型的长度。

    2.7、说明:添加/删除主键

    Alter table tabname add primary key(col)
    Alter table tabname drop primary key(col)
    

    2.8、说明:创建/删除索引

    create [unique] index idxname on tabname(col….)
    drop index idxname
    

    注:索引是不可更改的,想更改必须删除重新建。

    2.9、说明:创建/删除视图

    create view viewname as select statement
    drop view viewname
    

    理论的目的是应用。虽然我们刚才列出了何时应使用聚集索引或非聚集索引,但在实践中以上规则却很容易被忽视或不能根据实际情况进行综合分析。下面我们将根据在实践中遇到的实际问题来谈一下索引使用的误区,以便于大家掌握索引建立的方法。

    2.10、说明:基本sql语句选择

    图片 1

    查询:select * from table1 where 范围
    插入:insert into table1(field1,field2) s(1,2)
    删除:delete from table1 where 范围
    更新:update table1 set field1=1 where 范围
    查找:select * from table1 where field1 like ’%1%’ ---like的语法
    排序:select * from table1 order by field1,field2 [desc]
    总数:select count * as totalcount from table1
    求和:select sum(field1) as sum from table1
    平均:select avg(field1) as avg from table1
    最大:select max(field1) as max from table1
    最小:select min(field1) as min from table1
    

    图片 2

    1、主键就是聚集索引

    2.11、说明:高级查询运算

    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),不消除重复行。

    注:使用运算词的几个查询结果行必须是一致的。

    这种想法笔者认为是极端错误的,是对聚集索引的一种浪费。虽然SQL SERVER默认是在主键上建立聚集索引的。

    2.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: 全外连接:不仅包括符号连接表的匹配行,还包括两个连接表中的所有记录。

     

    通常,我们会在每个表中都建立一个ID列,以区分每条数据,并且这个ID列是自动增大的,步长一般为1。我们的这个办公自动化的实例中的列Gid就是如此。此时,如果我们将这个列设为主键,SQL SERVER会将此列默认为聚集索引。这样做有好处,就是可以让您的数据在数据库中按照ID进行物理排序,但笔者认为这样做意义不大。

    补充:

    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
    

    显示结果:

    图片 3

    type vender pcs
    电脑 A 1
    电脑 A 1
    光盘 B 2
    光盘 A 2
    手机 B 3
    手机 C 3
    

    图片 4

    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
    

     

    回到目录

    显而易见,聚集索引的优势是很明显的,而每个表中只能有一个聚集索引的规则,这使得聚集索引变得更加珍贵。

    三、sql技巧

    从我们前面谈到的聚集索引的定义我们可以看出,使用聚集索引的最大好处就是能够根据查询要求,迅速缩小查询范围,避免全表扫描。在实际应用中,因为ID号是自动生成的,我们并不知道每条记录的ID号,所以我们很难在实践中用ID号来进行查询。这就使让ID号这个主键作为聚集索引成为一种资源浪费。其次,让每个ID号都不同的字段作为聚集索引也不符合“大数目的不同值情况下不应建立聚合索引”规则;当然,这种情况只是针对用户经常修改记录内容,特别是索引项的时候会负作用,但对于查询速度并没有影响。

    3.1、如何删除一个表中重复的记录?

    图片 5

    create table a_dist(id int,name varchar(20))
    
    insert into a_dist values(1,'abc')
    insert into a_dist values(1,'abc')
    insert into a_dist values(1,'abc')
    insert into a_dist values(1,'abc')
    
    exec up_distinct 'a_dist','id'
    
    select * from a_dist
    

    图片 6

     存储过程如下:

    图片 7

    图片 8

     1 create procedure up_distinct(@t_name varchar(30),@f_key varchar(30))
     2 --f_key表示是分组字段﹐即主键字段
     3 
     4 as
     5 begin
     6 declare @max integer,@id varchar(30) ,@sql varchar(7999) ,@type integer
     7 select @sql = 'declare cur_rows cursor for select '+@f_key+' ,count(*) from ' +@t_name +' group by ' +@f_key +' having count(*) > 1'
     8 exec(@sql)
     9 
    10 open cur_rows
    11 fetch cur_rows into @id,@max
    12 
    13 while @@fetch_status=0
    14 begin
    15 select @max = @max -1
    16 set rowcount @max
    17 select @type = xtype from syscolumns where id=object_id(@t_name) and name=@f_key
    18 if @type=56
    19 select @sql = 'delete from '+@t_name+' where ' + @f_key+' = '+ @id
    20 if @type=167
    21 select @sql = 'delete from '+@t_name+' where ' + @f_key+' = '+''''+ @id +''''
    22 exec(@sql)
    23 
    24 fetch cur_rows into @id,@max
    25 end
    26 
    27 close cur_rows
    28 deallocate cur_rows
    29 
    30 set rowcount 0
    31 end
    32 
    33 select * from systypes
    34 select * from syscolumns where id = object_id('a_dist')
    

    图片 9

    在办公自动化系统中,无论是系统首页显示的需要用户签收的文件、会议还是用户进行文件查询等任何情况下进行数据查询都离不开字段的是“日期”还有用户本身的“用户名”。

    3.2、查询数据的最大排序问题(只能用一条语句写)

    图片 10

    CREATE TABLE hard (qu char (11) ,co char (11) ,je numeric(3, 0))
    
    insert into hard values ('A','1',3)
    insert into hard values ('A','2',4)
    insert into hard values ('A','4',2)
    insert into hard values ('A','6',9)
    insert into hard values ('B','1',4)
    insert into hard values ('B','2',5)
    insert into hard values ('B','3',6)
    insert into hard values ('C','3',4)
    insert into hard values ('C','6',7)
    insert into hard values ('C','2',3)
    

    图片 11

    要求查询出来的结果如下: 

    图片 12

    qu co je
    ----------- ----------- -----
    A 6 9
    A 2 4
    B 3 6
    B 2 5
    C 6 7
    C 3 4
    
    就是要按qu分组,每组中取je最大的前2位,且只能用一句sql语句。
    

    图片 13

    select * from hard a where je in (select top 2 je from hard b where a.qu=b.qu order by je)
    

    通常,办公自动化的首页会显示每个用户尚未签收的文件或会议。虽然我们的where语句可以仅仅限制当前用户尚未签收的情况,但如果您的系统已建立了很长时间,并且数据量很大,那么,每次每个用户打开首页的时候都进行一次全表扫描,这样做意义是不大的,绝大多数的用户1个月前的文件都已经浏览过了,这样做只能徒增数据库的开销而已。事实上,我们完全可以让用户打开系统首页时,数据库仅仅查询这个用户近3个月来未阅览的文件,通过“日期”这个字段来限制表扫描,提高查询速度。如果您的办公自动化系统已经建立的2年,那么您的首页显示速度理论上将是原来速度8倍,甚至更快。

    3.3、求删除重复记录的sql语句

    怎样把具有相同字段的记录删除,只留下一条。

    例如,表test里有id,name字段

    如果有name相同的记录 只留下一条,其余的删除。

    name的内容不定,相同的记录数不定。

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

    一个完整的解决方案:

    图片 14

    1)将重复的记录记入temp1表:
    select [标志字段id],count(*) into temp1 from [表名]
    group by [标志字段id]
    having count(*)>1
    
    
    2)将不重复的记录记入temp1表:
    insert temp1 select [标志字段id],count(*) from [表名] group by [标志字段id] having count(*)=1
    
    3)作一个包含所有不重复记录的表:
    select * into temp2 from [表名] where 标志字段id in(select 标志字段id from temp1)
    
    4)删除重复表:
    delete [表名]
    
    5)恢复表:
    insert [表名] select * from temp2
    
    6)删除临时表:
    drop table temp1
    drop table temp2
    

    图片 15

    在这里之所以提到“理论上”三字,是因为如果您的聚集索引还是盲目地建在ID这个主键上时,您的查询速度是没有这么高的,即使您在“日期”这个字段上建立的索引(非聚合索引)。下面我们就来看一下在1000万条数据量的情况下各种查询的速度表现(3个月内的数据为25万条):

    3.4、行列转换--普通

    图片 16

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

    图片 17

    图片 18

    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)
    

    图片 19

    (1)仅在主键上建立聚集索引,并且不划分时间段:

    3.5、行列转换--合并

    图片 20

    有表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
    

    图片 21

    创建一个合并的函数

    图片 22

    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
    

    图片 23

    调用自定义函数得到结果

    select distinct id,dbo.fmerg(id) from 表A
    

     

    1.Select gid,fariqi,neibuyonghu,title from tgongwen

    3.6、如何取得一个数据表的所有列名

    先从SYSTEMOBJECT系统表中取得数据表的SYSTEMID,然后再SYSCOLUMN表中取得该数据表的所有列名。

    SQL语句如下:

    图片 24

    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
    

    图片 25

    或 

    SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME ='users'
    

     

    用时:128470毫秒(即:128秒)

    3.7、通过SQL语句来更改用户的密码

    修改别人的,需要sysadmin role
    EXEC sp_password NULL, 'newpassword', 'User'
    
    如果帐号为SA执行
    EXEC sp_password NULL, 'newpassword', sa
    

    (2)在主键上建立聚集索引,在fariq上建立非聚集索引:

    3.8、怎么判断出一个表的哪些字段不允许为空

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

     

    1.select gid,fariqi,neibuyonghu,title from Tgongwen

    3.9、如何在数据库里找到含有相同字段的表

    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.未知列名查所有在不同表出现过的列名

    图片 26

    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
    )
    

    图片 27

     

    2.where fariqi> dateadd(day,-90,getdate())

    3.10、查询第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]
    

     

    注:行数为绝对行数

    用时:53763毫秒(54秒)

    3.11、日期计算

    图片 28

    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))。
    

    图片 29

     

    (3)将聚合索引建立在日期列(fariqi)上:

    3.12、获取表结构[把 'sysobjects' 替换 成 'tablename' 即可]

    图片 30

    SELECT CASE IsNull(I.name, '')
    When '' Then ''
    Else '*'
    End as IsPK,
    Object_Name(A.id) as t_name,
    A.name as c_name,
    IsNull(SubString(M.text, 1, 254), '') as pbc_init,
    T.name as F_DataType,
    CASE IsNull(TYPEPROPERTY(T.name, 'Scale'), '')
    WHEN '' Then Cast(A.prec as varchar)
    ELSE Cast(A.prec as varchar) + ',' + Cast(A.scale as varchar)
    END as F_Scale,
    A.isnullable as F_isNullAble
    FROM Syscolumns as A
    JOIN Systypes as T
    ON (A.xType = T.xUserType AND A.Id = Object_id('sysobjects') )
    LEFT JOIN ( SysIndexes as I
    JOIN Syscolumns as A1
    ON ( I.id = A1.id and A1.id = object_id('sysobjects') and (I.status & 0x800) = 0x800 AND A1.colid <= I.keycnt) )
    ON ( A.id = I.id AND A.name = index_col('sysobjects', I.indid, A1.colid) )
    LEFT JOIN SysComments as M
    ON ( M.id = A.cdefault and ObjectProperty(A.cdefault, 'IsConstraint') = 1 )
    ORDER BY A.Colid ASC
    

    图片 31

    1.select gid,fariqi,neibuyonghu,title from Tgongwen

    3.13、提取数据库内所有表的字段详细说明的SQL语句

    图片 32

    SELECT
    (case when a.colorder=1 then d.name else '' end) N'表名',
    a.colorder N'字段序号',
    a.name N'字段名',
    (case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else ''
    end) N'标识',
    (case when (SELECT count(*)
    FROM sysobjects
    WHERE (name in
    (SELECT name
    FROM sysindexes
    WHERE (id = a.id) AND (indid in
    (SELECT indid
    FROM sysindexkeys
    WHERE (id = a.id) AND (colid in
    (SELECT colid
    FROM syscolumns
    WHERE (id = a.id) AND (name = a.name))))))) AND
    (xtype = 'PK'))>0 then '√' else '' end) N'主键',
    b.name N'类型',
    a.length N'占用字节数',
    COLUMNPROPERTY(a.id,a.name,'PRECISION') as N'长度',
    isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as N'小数位数',
    (case when a.isnullable=1 then '√'else '' end) N'允许空',
    isnull(e.text,'') N'默认值',
    isnull(g.[value],'') AS N'字段说明'
    FROM syscolumns a
    left join systypes b
    on a.xtype=b.xusertype
    inner join sysobjects d
    on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
    left join syscomments e
    on a.cdefault=e.id
    left join sysproperties g
    on a.id=g.id AND a.colid = g.smallid
    order by object_name(a.id),a.colorder
    

    图片 33

    2.where fariqi> dateadd(day,-90,getdate())

    3.14、快速获取表test的记录总数[对大容量表非常有效]

    快速获取表test的记录总数:

    图片 34

    select rows from sysindexes where id = object_id('test') and indid in (0,1)
    
    update 2 set KHXH=(ID+1)2 2行递增编号
    update [23] set id1 = 'No.'+right('00000000'+id,6) where id not like 'No%' //递增
    update [23] set id1= 'No.'+right('00000000'+replace(id1,'No.',''),6) //补位递增
    delete from [1] where (id%2)=1
    
    
    奇数
    替换表名字段
    update [1] set domurl = replace(domurl,'Upload/Imgswf/','Upload/Photo/') where domurl like '%Upload/Imgswf/%'
    
    截位
    SELECT LEFT(表名, 5)
    
    截位
    SELECT LEFT(表名, 5)
    

    图片 35

    回到目录

    用时:2423毫秒(2秒)

    四、(MS SQL Server)SQL语句导入导出大全

    虽然每条语句提取出来的都是25万条数据,各种情况的差异却是巨大的,特别是将聚集索引建立在日期列时的差异。事实上,如果您的数据库真的有1000万容量的话,把主键建立在ID列上,就像以上的第1、2种情况,在网页上的表现就是超时,根本就无法显示。这也是我摒弃ID列作为聚集索引的一个最重要的因素。得出以上速度的方法是:在各个select语句前加:

    4.1、导出到excel

    EXEC master..xp_cmdshell 'bcp SettleDB.dbo.shanghu out c:temp1.xls -c -q -S"GNETDATA/GNETDATA" -U"sa" -P""'
    

     

    1.declare @d datetime

    4.2、 导入Excel

    图片 36

    SELECT *
    FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
    'Data Source="c:test.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions
    
    SELECT cast(cast(科目编号 as numeric(10,2)) as nvarchar(255))+' ' 转换后的别名
    FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
    'Data Source="c:test.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions
    
    select * from OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 5.0;HDR=YES;DATABASE=c:Book1.xls',Sheet1$)
    
    HDR=YES;Excel第一行当成标题行
    HDR=NO;第一行不当成标题行
    

    图片 37

    2.set @d=getdate()

    4.3、 导入文本文件

    EXEC master..xp_cmdshell 'bcp "dbname..tablename" in c:DT.txt -c -Sservername -Usa -Ppassword' 
    

    并在select语句后加:

    4.4、 导出文本文件

    图片 38

    EXEC master..xp_cmdshell 'bcp "dbname..tablename" out c:DT.txt -c -Sservername -Usa -Ppassword'
    或
    EXEC master..xp_cmdshell 'bcp "Select * from dbname..tablename" queryout c:DT.txt -c -Sservername -Usa -Ppassword'
    
    导出到TXT文本,用逗号分开
    exec master..xp_cmdshell 'bcp "库名..表名" out "d:tt.txt" -c -t ,-U sa -P password'
    
    
    BULK INSERT 库名..表名
    FROM 'c:test.txt'
    WITH (
    FIELDTERMINATOR = ';',
    ROWTERMINATOR = 'n'
    )
    

    图片 39

     

     补充:

    图片 40

    --/* dBase IV文件
    select * from
    OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
    ,'dBase IV;HDR=NO;IMEX=2;DATABASE=C:','select * from [客户资料4.dbf]')
    --*/
    
    --/* dBase III文件
    select * from
    OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
    ,'dBase III;HDR=NO;IMEX=2;DATABASE=C:','select * from [客户资料3.dbf]')
    --*/
    
    --/* FoxPro 数据库
    select * from openrowset('MSDASQL',
    'Driver=Microsoft Visual FoxPro Driver;SourceType=DBF;SourceDB=c:',
    'select * from [aa.DBF]')
    --*/
    

    图片 41

    1.select [语句执行花费时间(毫秒)]=datediff(ms,@d,getdate())

    4.5、导入DBF文件

    图片 42

    select * from openrowset('MSDASQL',
    'Driver=Microsoft Visual FoxPro Driver;
    SourceDB=e:VFP98data;
    SourceType=DBF',
    'select * from customer where country != "USA" order by country')
    go
    

    图片 43

     

    2、只要建立索引就能显著提高查询速度

    4.6、 导出到DBF 文件

    如果要导出数据到已经生成结构(即现存的)FOXPRO表中,可以直接用下面的SQL语句

    insert into openrowset('MSDASQL',
    'Driver=Microsoft Visual FoxPro Driver;SourceType=DBF;SourceDB=c:',
    'select * from [aa.DBF]')
    
    select * from 表
    

     

    说明:

    SourceDB=c: 指定foxpro表所在的文件夹

    aa.DBF 指定foxpro表的文件名.

     

    事实上,我们可以发现上面的例子中,第2、3条语句完全相同,且建立索引的字段也相同;不同的仅是前者在fariqi字段上建立的是非聚合索引,后者在此字段上建立的是聚合索引,但查询速度却有着天壤之别。所以,并非是在任何字段上简单地建立索引就能提高查询速度。

    4.7、导出到Access

    insert into openrowset('Microsoft.Jet.OLEDB.4.0',
    'x:A.mdb';'admin';'',A表) select * from 数据库名..B表
    

    从建表的语句中,我们可以看到这个有着1000万数据的表中fariqi字段有5003个不同记录。在此字段上建立聚合索引是再合适不过了。在现实中,我们每天都会发几个文件,这几个文件的发文日期就相同,这完全符合建立聚集索引要求的:“既不能绝大多数都相同,又不能只有极少数相同”的规则。由此看来,我们建立“适当”的聚合索引对于我们提高查询速度是非常重要的。

    4.8、导入Access

    insert into B表 selet * from openrowset('Microsoft.Jet.OLEDB.4.0',
    'x:A.mdb';'admin';'',A表)
    

    3、把所有需要提高查询速度的字段都加进聚集索引,以提高查询速度

    4.9、 导入 xml文件

     

    图片 44

    图片 45

     1 DECLARE @idoc int
     2 DECLARE @doc varchar(1000)
     3 
     4 --sample XML document
     5 SET @doc ='
     6 <root>
     7 <Customer cid= "C1" name="Janine" city="Issaquah">
     8 <Order oid="O1" date="1/20/1996" amount="3.5" />
     9 <Order oid="O2" date="4/30/1997" amount="13.4">Customer was very satisfied
    10 </Order>
    11 </Customer>
    12 <Customer cid="C2" name="Ursula" city="Oelde" >
    13 <Order oid="O3" date="7/14/1999" amount="100" note="Wrap it blue
    14 white red">
    15 <Urgency>Important</Urgency>
    16 Happy Customer.
    17 </Order>
    18 <Order oid="O4" date="1/20/1996" amount="10000"/>
    19 </Customer>
    20 </root>
    21 '
    22 
    23 -- Create an internal representation of the XML document.
    24 EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
    25 
    26 
    27 -- Execute a SELECT statement using OPENXML rowset provider.
    28 SELECT *
    29 FROM OPENXML (@idoc, '/root/Customer/Order', 1)
    30 WITH (oid char(5),
    31 amount float,
    32 comment ntext 'text()')
    33 
    34 EXEC sp_xml_removedocument @idoc
    

    图片 46

     

     

     

    上面已经谈到:在进行数据查询时都离不开字段的是“日期”还有用户本身的“用户名”。既然这两个字段都是如此的重要,我们可以把他们合并起来,建立一个复合索引(compound index)。

    4.10、导整个数据库

    用bcp实现的存储过程

    实现数据导入/导出的存储过程

    根据不同的参数,可以实现导入/导出整个数据库/单个表

    调用示例:

    很多人认为只要把任何字段加进聚集索引,就能提高查询速度,也有人感到迷惑:如果把复合的聚集索引字段分开查询,那么查询速度会减慢吗?带着这个问题,我们来看一下以下的查询速度(结果集都是25万条数据):(日期列fariqi首先排在复合聚集索引的起始列,用户名neibuyonghu排在后列):

    4.10.1、导出调用示例

    ----导出单个表

    exec file2table 'zj','','','xzkh_sa..地区资料','c:zj.txt',1
    

     

    ----导出整个数据库

    exec file2table 'zj','','','xzkh_sa','C:docman',1 
    

    1.(1)select gid,fariqi,neibuyonghu,title from Tgongwen where fariqi>''2004-5-5''

    4.10.2、导入调用示例

    ----导入单个表

    exec file2table 'zj','','','xzkh_sa..地区资料','c:zj.txt',0
    

     

    ----导入整个数据库

    exec file2table 'zj','','','xzkh_sa','C:docman',0 
    

    查询速度:2513毫秒

     

    图片 47

    图片 48

     1 if exists(select 1 from sysobjects where name='File2Table' and objectproperty(id,'IsProcedure')=1)
     2 drop procedure File2Table
     3 go
     4 create procedure File2Table
     5 @servername varchar(200) --服务器名
     6 ,@username varchar(200) --用户名,如果用NT验证方式,则为空''
     7 ,@password varchar(200) --密码
     8 ,@tbname varchar(500) --数据库.dbo.表名,如果不指定:.dbo.表名,则导出数据库的所有用户表
     9 ,@filename varchar(1000) --导入/导出路径/文件名,如果@tbname参数指明是导出整个数据库,则这个参数是文件存放路径,文件名自动用表名.txt
    10 ,@isout bit --1为导出,0为导入
    11 as
    12 declare @sql varchar(8000)
    13 if @tbname like '%.%.%' --如果指定了表名,则直接导出单个表
    14 begin
    15 set @sql='bcp '+@tbname
    16 +case when @isout=1 then ' out ' else ' in ' end
    17 +' "'+@filename+'" /w'
    18 +' /S '+@servername
    19 +case when isnull(@username,'')='' then '' else ' /U '+@username end
    20 +' /P '+isnull(@password,'')
    21 
    22 exec master..xp_cmdshell @sql
    23 
    24 end
    25 else
    26 begin --导出整个数据库,定义游标,取出所有的用户表
    27 
    28 declare @m_tbname varchar(250)
    29 if right(@filename,1)<>'' set @filename=@filename+''
    30 set @m_tbname='declare #tb cursor for select name from '+@tbname+'..sysobjects where xtype=''U'''
    31 exec(@m_tbname)
    32 
    33 open #tb
    34 
    35 fetch next from #tb into @m_tbname
    36 while @@fetch_status=0
    37 begin
    38 set @sql='bcp '+@tbname+'..'+@m_tbname
    39 +case when @isout=1 then ' out ' else ' in ' end
    40 +' "'+@filename+@m_tbname+'.txt " /w'
    41 +' /S '+@servername
    42 +case when isnull(@username,'')='' then '' else ' /U '+@username end
    43 +' /P '+isnull(@password,'')
    44 
    45 exec master..xp_cmdshell @sql
    46 
    47 fetch next from #tb into @m_tbname
    48 end
    49 
    50 close #tb
    51 deallocate #tb
    52 end
    53 go
    

    图片 49

    1.(2)select gid,fariqi,neibuyonghu,title from Tgongwen where fariqi>''2004-5-5'' and neibuyonghu=''办公室''

     

     

    查询速度:2516毫秒

     

    1.(3)select gid,fariqi,neibuyonghu,title from Tgongwen where neibuyonghu=''办公室''

    4.11、Excel导到Txt

    想用

    select * into opendatasource(...) from opendatasource(...)

    实现将一个Excel文件内容导入到一个文本文件

     

    假设Excel中有两列,第一列为姓名,第二列为很行帐号(16位)

    且银行帐号导出到文本文件后分两部分,前8位和后8位分开。

     

    如果要用你上面的语句插入的话,文本文件必须存在,而且有一行:姓名,银行账号1,银行账号2

    然后就可以用下面的语句进行插入

    注意文件名和目录根据你的实际情况进行修改.

    图片 50

    insert into
    opendatasource('MICROSOFT.JET.OLEDB.4.0'
    ,'Text;HDR=Yes;DATABASE=C:'
    )...[aa#txt]
    --,aa#txt)
    --*/
    
    select 姓名,银行账号1=left(银行账号,8),银行账号2=right(银行账号,8)
    from
    opendatasource('MICROSOFT.JET.OLEDB.4.0'
    ,'Excel 5.0;HDR=YES;IMEX=2;DATABASE=c:a.xls'
    --,Sheet1$)
    )...[Sheet1$]
    

    图片 51

     

     

    如果你想直接插入并生成文本文件,就要用bcp

    declare @sql varchar(8000),@tbname varchar(50) 
    

    首先将excel表内容导入到一个全局临时表

    图片 52

    select @tbname='[##temp'+cast(newid() as varchar(40))+']'
    ,@sql='select 姓名,银行账号1=left(银行账号,8),银行账号2=right(银行账号,8)
    into '+@tbname+' from
    opendatasource(''MICROSOFT.JET.OLEDB.4.0''
    ,''Excel 5.0;HDR=YES;IMEX=2;DATABASE=c:a.xls''
    )...[Sheet1$]'
    
    exec(@sql)
    

    图片 53

    然后用bcp从全局临时表导出到文本文件

    set @sql='bcp "'+@tbname+'" out "c:aa.txt" /S"(local)" /P"" /c'
    exec master..xp_cmdshell @sql
    

    删除临时表

    exec('drop table '+@tbname) 
    

    查询速度:60280毫秒

    4.12、用bcp将文件导入导出到数据库的存储过程:

    bcp-二进制文件的导入导出

    支持image,text,ntext字段的导入/导出

    image适合于二进制文件;text,ntext适合于文本数据文件

     

    注意:导入时,将覆盖满足条件的所有行

    导出时,将把所有满足条件的行也出到指定文件中

     

    此存储过程仅用bcp实现

     

    /*--调用示例

    --数据导出

    exec p_binaryIO 'zj','','','acc_演示数据..tb','img','c:zj1.dat' 
    

    --数据导出

    exec p_binaryIO 'zj','','','acc_演示数据..tb','img','c:zj1.dat','',0
    

     

    --*/

     

    图片 54View Code

     

     生成数据处理应答文件

    图片 55

    set @m_tbname='[##temp'+cast(newid() as varchar(40))+']'
    set @sql='select * into '+@m_tbname+' from(
    select null as 类型
    union all select 0 as 前缀
    union all select '+@fsize+' as 长度
    union all select null as 结束
    union all select null as 格式
    ) a'
    
    exec(@sql)
    
    select @fname_in=@fname+'_temp'
    ,@sql='bcp "'+@m_tbname+'" out "'+@fname_in
    +'" /S"'+@servename
    +case when isnull(@username,'')='' then ''
    else '" /U"'+@username end
    +'" /P"'+isnull(@password,'')+'" /c'
    
    exec master..xp_cmdshell @sql
    

    图片 56

     

    删除临时表

    图片 57

    set @sql='drop table '+@m_tbname
    exec(@sql)
    
    if @isout=1
    begin
    set @sql='bcp "select top 1 '+@fdname+' from '
    +@tbname+case isnull(@tj,'') when '' then ''
    else ' where '+@tj end
    +'" queryout "'+@fname
    +'" /S"'+@servename
    +case when isnull(@username,'')='' then ''
    else '" /U"'+@username end
    +'" /P"'+isnull(@password,'')
    +'" /i"'+@fname_in+'"'
    
    
    exec master..xp_cmdshell @sql
    end
    else
    begin
    

    图片 58

     

    为数据导入准备临时表

    set @sql='select top 0 '+@fdname+' into '
    +@m_tbname+' from ' +@tbname
    
    exec(@sql)
    

    将数据导入到临时表

    图片 59

    set @sql='bcp "'+@m_tbname+'" in "'+@fname
    +'" /S"'+@servename
    +case when isnull(@username,'')='' then ''
    else '" /U"'+@username end
    +'" /P"'+isnull(@password,'')
    +'" /i"'+@fname_in+'"'
    
    exec master..xp_cmdshell @sql
    

    图片 60

     

     将数据导入到正式表中

    图片 61

    set @sql='update '+@tbname
    +' set '+@fdname+'=b.'+@fdname
    +' from '+@tbname+' a,'
    +@m_tbname+' b'
    +case isnull(@tj,'') when '' then ''
    else ' where '+@tj end
    
    exec(@sql)
    

    图片 62

    删除数据处理临时表

    set @sql='drop table '+@m_tbname
    end
    

    删除数据处理应答文件

    set @sql='del '+@fname_in
    
    exec master..xp_cmdshell @sql
    go
    

     

     导入文本文件

    EXEC master..xp_cmdshell 'bcp "dbname..tablename" in c:DT.txt -c -Sservername -Usa -Ppassword'
    
    改为如下,不需引号
    
    EXEC master..xp_cmdshell 'bcp dbname..tablename in c:DT.txt -c -Sservername -Usa -Ppassword'
    

    导出文本文件

    EXEC master..xp_cmdshell 'bcp "dbname..tablename" out c:DT.txt -c -Sservername -Usa -Ppassword'
    
    此句需加引号
    

    从以上试验中,我们可以看到如果仅用聚集索引的起始列作为查询条件和同时用到复合聚集索引的全部列的查询速度是几乎一样的,甚至比用上全部的复合索引列还要略快(在查询结果集数目一样的情况下);而如果仅用复合聚集索引的非起始列作为查询条件的话,这个索引是不起任何作用的。当然,语句1、2的查询速度一样是因为查询的条目数一样,如果复合索引的所有列都用上,而且查询结果少的话,这样就会形成“索引覆盖”,因而性能可以达到最优。同时,请记住:无论您是否经常使用聚合索引的其他列,但其前导列一定要是使用最频繁的列。

    四、其他书上没有的索引使用经验总结

    1、用聚合索引比用不是聚合索引的主键速度快

    下面是实例语句:(都是提取25万条数据)

    1.select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi=''2004-9-16''

    使用时间:3326毫秒

    1.select gid,fariqi,neibuyonghu,reader,title from Tgongwen where gid<=250000

    使用时间:4470毫秒

    这里,用聚合索引比用不是聚合索引的主键速度快了近1/4。

    2、用聚合索引比用一般的主键作order by时速度快,特别是在小数据量情况下

    1.select gid,fariqi,neibuyonghu,reader,title from Tgongwen order by fariqi

    用时:12936

    1.select gid,fariqi,neibuyonghu,reader,title from Tgongwen order by gid

    用时:18843

    这里,用聚合索引比用一般的主键作order by时,速度快了3/10。事实上,如果数据量很小的话,用聚集索引作为排序列要比使用非聚集索引速度快得明显的多;而数据量如果很大的话,如10万以上,则二者的速度差别不明显。

    3、使用聚合索引内的时间段,搜索时间会按数据占整个数据表的百分比成比例减少,而无论聚合索引使用了多少个:

    1.select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi>''2004-1-1''

    用时:6343毫秒(提取100万条)

    1.select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi>''2004-6-6''

    用时:3170毫秒(提取50万条)

    1.select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi=''2004-9-16''

    用时:3326毫秒(和上句的结果一模一样。如果采集的数量一样,那么用大于号和等于号是一样的)

    1.select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi>''2004-1-1'' and fariqi<''2004-6-6''

    用时:3280毫秒

    4、日期列不会因为有分秒的输入而减慢查询速度

    下面的例子中,共有100万条数据,2004年1月1日以后的数据有50万条,但只有两个不同的日期,日期精确到日;之前有数据50万条,有5000个不同的日期,日期精确到秒。

    1.select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi>''2004-1-1'' order by fariqi

    用时:6390毫秒

    1.select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi<''2004-1-1'' order by fariqi

    用时:6453毫秒

    五、其他注意事项

    “水可载舟,亦可覆舟”,索引也一样。索引有助于提高检索性能,但过多或不当的索引也会导致系统低效。因为用户在表中每加进一个索引,数据库就要做更多的工作。过多的索引甚至会导致索引碎片。

    所以说,我们要建立一个“适当”的索引体系,特别是对聚合索引的创建,更应精益求精,以使您的数据库能得到高性能的发挥。

    当然,在实践中,作为一个尽职的数据库管理员,您还要多测试一些方案,找出哪种方案效率最高、最为有效。

    (二)改善SQL语句

    很多人不知道SQL语句在SQL SERVER中是如何执行的,他们担心自己所写的SQL语句会被SQL SERVER误解。比如:

    1.select * from table1 where name=''zhangsan'' and tID > 10000和执行select * from table1 where tID > 10000 and name=''zhangsan''

    本文由金沙国际官网发布于数据库,转载请注明出处:SERVER常用语法汇总,索引的作用

    关键词:

上一篇:没有了

下一篇:没有了