特殊SQL语句及优化原则

  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 @[email protected]+','+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 = ‘manager’ and 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 = ‘president’ or job=’manager’;

  高效: select job, avg(sal) from emp having job=’president’ or job=’manager’ group by job;

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

  (8). 分离表和索引

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

分类:mysql数据库 时间:2012-01-09 人气:3
本文关键词:
分享到:

相关文章

  • Linux下如何将数据库脚本文件从sh格式变为sql格式 2012-01-01

      1. sh文件内容   本文中的文件名为example.sh,其内容如下:   #!/bin/bash   function Init()   {   if [ -f"example.sql" ]   then   echo"example.sql is exits and is deleting it,then recreate it"   rm -fexample.sql   else   echo"example.sql no exits and is creating it"   f

  • Sql Server 存储过程实例讲解 2012-01-02

      一、遇到的问题   我们在之前学习的课程写了不少的批处理语句,这些批处理语句存在两个问题:   1.没法像函数那样传参数运行(自定义化)   2.没法像函数那样可以反复地调用(功能化模块化)   说到这里,我们可以猜到,数据库中应该可以建立函数形式的数据库对象来解决这样的问题。但是介绍这种数据库对象之前,我们再来看几个需要解决的问题:   我们已经学会把一条select语句封装在视图中,但是它只能用来查询,如果我们希望进行其它操作,比如增删改记录、建删库表等,是不能用视图来完成的。   另一

  • 修改SQL Server Management Studio默认设置提高开发效率 2012-01-03

    修改SQL Server Management Studio默认设置提高开发效率 2012导脚本默认没有判断对象是否存在的,导致执行删除创建的脚本会先报一次错。可以在工具=>选项==>sqlserver对象资源管理器中的编写脚本的.检查是否存在对象。 可以设置默认数据库。可以对数据库用户设置最近主要开发的数据库为默认,这样直接点工具栏的新建查询就不会每次需要选数据库了。 个人习惯,不喜欢启动ssms时候自动弹出一个新的查询而且还需要你去填写用户和密码。我是每次都要取消,当然你也可以去

  • 突破Sql 防注入过滤程序继续注入的一点方法.经验.技巧总结 2012-01-03

    看了以下帖子想写一点东西,原帖内容如下: 标题:防注入真的有用吗? 作者:798146410 时间:2012-2-22 21:39 内容: 现在网上流传很多防注入代码。这些真的有用吗?这是在网上找的一个防注入代码 例如: ''''--------定义部份------------------ DimFy_Post,Fy_Get,Fy_In,Fy_Inf,Fy_Xh,Fy_db,Fy_dbstr''''自定义需要过滤的字串,用"|"分隔Fy_In="''''|;|and|exec|insert|se

  • 在EA中将画出的ER图转换成SQL脚本 2012-01-04

    在进行数据库设计的时候,想着正好安装着EA软件呢,看能不能用EA画ER模型。结果发现不仅能画,而且还能进行整套数据库设计(生成SQL脚本)。 下面以机房收费系统用户--学生为例。学生可以查看余额,可以查看上机记录,可以查看上机状态,可以查看充值记录,可以修改密码。步骤如下: (1)建立ER图之前,首先在EA中应选择使用哪种数据库。如图所示: (3)转换成数据模型图,选中所有的实体和属性名,右击,点击转换出现如图: (5)任何的数据库表都需要主键,外键和约束,下面我们对这些表进行修改,选择任何一个

  • 计世网sql注射,xss大礼包集合 2012-01-04

    多个sql注射+大量xss+一个上传,后台泄露,报错等。 面对G哥,压力大,果断翻硬盘找啊。找。 不说了。看详情。 注射漏洞: http://www.ccw.com.cn/oa/category/index.php?categoryId=165'%20or%20'1'= http://internet.ccw.com.cn/commlist/index.php?categoryId=1393'%20or%20% http://cio.ccw.com.cn/gq/index.php?nodeId=

  • php云两处SQL二次注入 2012-01-04

    php云两处二次注入 最新版。两个注入点。顺带一个绕过waf的小技巧。 第一处:/member/model/index.class.php 39行 function index_action() { $this->public_action(); $this->member_satic(); $this->com_cache(); $resume = $this->obj->DB_select_once("resume","`uid`='".$this->ui

  • SQL Server 2014新特性Data Explorer ForExcel的特点 2012-01-06

    Data Explorer是即将发布的SQL Server 2014里的一个新特性,借助这个特性讲使企业中的自助式的商业智能变得更加的灵活,从而也降低了商业智能的门槛。 Data Explorer Preview for Excel提供了一种新的方式来为自助式的商业智能发现数据,整合数据以及提炼数据。如果你对它还比较陌生,那么如下五点将使你了解它如何提升你在Excel中处理数据的方法。 借助Data Explorer,你可以: 1. 发现数据 Data Explorer带来了在Excel中数据检

  • sql访问远程数据库 2012-01-07

      1、启用Ad Hoc Distributed Queries   在使用openrowset/opendatasource前搜先要启用Ad Hoc Distributed Queries服务,因为这个服务不安全所以SqlServer默认是关闭的   启用Ad Hoc Distributed Queries的方法   SQL Server 阻止了对组件 'Ad Hoc Distributed Queries' 的 STATEMENT'OpenRowset/OpenDatasource'   的

  • SQL Server 2012中自带的SSDS多维数据集的浏览功能 2012-01-07

    在SQL Server 2012自带的SQL Server Data Tools(SSDT),上个版本中SQL Server 2008中对应为BIDS, 已经废除了多维数据集的数据浏览功能,同时在SSMS中也同步废除,只能浏览单维数据。 解决方法:在维度浏览界面,点击“Analyze in Excel”,在相应的Excel界面完成浏览。 www.2cto.com 关于“Management Studio 和多维数据集设计器中的多维数据集浏览器已被删除”的详细, 请看MSDN:http://msd

Copyright (C) quwantang.com, All Rights Reserved.

趣玩堂 版权所有 京ICP备15002868号

processed in 0.067 (s). 10 q(s)