展会信息港展会大全

特殊SQL语句及优化原则 按姓氏笔画排序 数据库加密 取回表中字段 查看硬盘分区
来源:互联网   发布日期:2016-01-28 13:01:08   浏览:2196次  

导读:1 按姓氏笔画排序: Select * From TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as 2 数据库加密: select encrypt( 39;原始密码 39;) select pwdencrypt( 39;原始密 ...

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) FROMmaster.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 = '表名'

[n].[标题]:

Select * From TableName Order By CustomerName

[n].[标题]:

Select * From TableName Order By CustomerName

--------------------------------------------------------------------------------------

Sql优化是一项复杂的工作,以下的一些基本原则是本人看书时所记录下来的,很明确且没什么废话:

1. 索引的使用:

(1).当插入的数据为数据表中的记录数量的10%以上,首先需要删除该表的索引来提高数据的插入效率,当数据插入后,再建立索引。

(2).避免在索引列上使用函数或计算,在where子句中,如果索引是函数的一部分,优化器将不再使用索引而使用全表扫描。如:

低效:select * from dept where sal*12 >2500;

高效:select * from dept where sal>2500/12;

(3).避免在索引列上使用not和!= ,索引只能告诉什么存在于表中,而不能告诉什么不存在于表中,当数据库遇到not 和!= 时,就会停止使用索引而去执行全表扫描。

(4).索引列上>=代替>

低效:select * from emp where deptno > 3

高效:select * from emp where deptno >=4

两者的区别在于,前者dbms将直接跳到第一个deptno等于4的记录,而后者将首先定位到deptno等于3的记录并且向前扫描到第一个 deptno大于3的。

(5).非要对一个使用函数的列启用索引,基于函数的索引是一个较好的方案。

2. 游标的使用:

当在海量的数据表中进行数据的删除、更新、插入操作时,用游标处理的效率是最慢的,但是游标又是必不可少的,所以正确使用游标十分重要:

(1). 在数据抽取的源表中使用时间戳,这样每天的维表数据维护只针对更新日期为最新时间的数据来进行,大大减少需要维护的数据记录数。

(2). 在insert和update维表时都加上一个条件来过滤维表中已经存在的记录,例如:

insert into dim_customer select * from ods_customer where ods_customer.code not exists (dim_customer.code)

ods_customer为数据源表。dim_customer为维表。

(3). 使用显式的游标,因为隐式的游标将会执行两次操作,第一次检索记录,第二次检查too many rows这个exception,而显式游标不执行第二次操作。

3. 据抽取和上载时的sql优化:

(1). Where 子句中的连接顺序:

oracle采用自下而上的顺序解析where子句,根据这个原理,表之间的连接必须写在其他where条件之前,那些可以过滤掉大量记录的条 件必须写在where子句的末尾。如:

低效:select * from emp e where sal>5000 and job =managerand 25<(select count (*) from emp where mgr=e.empno);

高效:select * from emp e where 25<(select count(*) from emp where mgr=e.empno) and sal>5000 and job= manager ;

(2). 删除全表时,用truncate 替代 delete,同时注意truncate只能在删除全表时适用,因为truncate是ddl而不是dml。

(3). 尽量多使用commit

只要有可能就在程序中对每个delete,insert,update操作尽量多使用commit,这样系统性能会因为commit所释放的资 源而大大提高。

(4). 用exists替代in ,可以提高查询的效率。

(5). 用not exists 替代 not in

(6). 优化group by

提高group by语句的效率,可以将不需要的记录在group by之前过滤掉。如:

低效:select job, avg(sal) from emp group by job having job =presidentor job= manager ;

高效: select job, avg(sal) from emp having job= presidentor job= managergroup by job;

(7). 有条件的使用union-all 替代 union:这样做排序就不必要了,效率会提高3到5倍。

(8). 分离表和索引

总是将你的表和索引建立在不同的表空间内,决不要将不属于oracle内部系统的对象存放到system表空间内。同时确保数据表空间和索引表 空间置于不同的硬盘控制卡控制的硬盘上。

赞助本站

人工智能实验室

相关热词: 语句 SQL 排序

相关内容
AiLab云推荐
展开

热门栏目HotCates

Copyright © 2010-2024 AiLab Team. 人工智能实验室 版权所有    关于我们 | 联系我们 | 广告服务 | 公司动态 | 免责声明 | 隐私条款 | 工作机会 | 展会港