数据库-拾贝-白红宇的个人博客

数据库-拾贝

发布日期：2021-07-01 05:45:19 浏览次数：2 分类：技术文章

本文共 24668 字，大约阅读时间需要 82 分钟。

数据库设计最佳实践

数据库设计是整个程序的重点之一，为了支持相关程序运行，最佳的数据库设计往往不可能一蹴而就，只能反复探寻并逐步求精，这是一个复杂的过程，也是规划和结构化数据库中的数据对象以及这些数据对象之间关系的过程。下面给出了20个数据库设计最佳实践，当然，所谓最佳，还是要看它是否适合你的程序。一起来了解了解吧。

1. 使用明确、统一的标明和列名，例如 School, SchoolCourse, CourceID。

2. 数据表名使用单数而不是复数，例如 StudentCourse，而不是StudentCourses。

3. 数据表名不要使用空格。

4. 数据表名不要使用不必要的前缀或者后缀，例如使用School，而不是TblSchool，或者SchoolTable等等。

5. 数据库中的密码要加密，到应用中再解密。（其实就是散列存储、单向加密）

6. 使用整数作为ID字段，也许现在没有这个必要，但是将来需要，例如关联表，索引等等。

7. 使用整数字段做索引，否则会带来很大的性能问题。

8. 使用 bit 作为布尔字段，使用整数或者varcha是浪费。同时，这类字段应该以“Is”开头。

9. 要经过认证才能访问数据库，不要给每一个用户管理员权限。

10. 尽量避免使用“select *”，而使用“select [required_column_list]”以获得更好的性能。

11. 假如程序代码比较复杂，使用ORM框架，例如hibernate，iBatis。ORM框架的性能问题可以通过详细的配置去解决。

12. 分割不常使用的数据表到不同的物理存储以获得更好的性能。

13. 对于关键数据库，使用安全备份系统，例如集群，同步等等。

14. 使用外键，非空等限制来保证数据的完整性，不要把所有的东西都扔给程序。

15. 缺乏数据库文档是致命的。你应该为你的数据库设计写文档，包括触发器、存储过程和其他脚本。

16. 对于经常使用的查询和大型数据表，要使用索引。数据分析工具可以帮助你决定如何建立索引。

17. 数据库服务器和网页服务器应该放在不同的机器上。这回提高安全性，并减轻CPU压力。

18. Image和blob字段不应该定义在常用的数据表中，否则会影响性能。

19. 范式（Normalization）要按照要求使用以提高性能。Normalization做的不够会导致数据冗余，而过度Normalization 会导致太多的join和数据表，这两种情况都会影响性能。

20. 多花点时间在数据库设计上，否则你将来会付出加倍的时间来偿还。

联合查询

SQl联合查询（inner join、left join、rightjoin、outer join区别）

inner join(等值连接) 只返回两个表中联结字段相等的行

left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录

right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录

层次数据库

层次数据库，代表产品：IMS层次数据库。IMS是最早的大型数据库管理系统，其数据库模式是多个物理数据库记录型(PDBR)的集合。每个PDBR对应层次数据模型的一个层次模式，用一个DBD定义。各个用户所需数据的逻辑结构称为外模式，每个外模式是一组逻辑数据库记录型(LDBR)的集合。LDBR 是应用程序所需的局部逻辑结构。用户按照外模式操纵数据。

数据库四级考试相关

一：引论

4. 数据是信息的符号表示，或称为载体，信息则是数据的语言解释，是数据的内涵。

3. 数据是描述现实世界事物的符号记录，是用物理符号记录下来的可以识别的信息。

2. 信息具有：可感知、可存储、可加工、可传递和可再生的自然属性

1. 信息，物质和能量是组成客观世界并促进社会发展的三大基本要素。

二：数据库应用系统生命周期

1. 常见的软件开发模型

瀑布模型、快速原型模型、增量模型、螺旋模型。

2. 性能需求分析的性能指标主要包括:

1). 数据访问响应时间

2). 系统吞吐量

3). 允许并发访问的最大用户数

4). 每TPS代价值

3. 影响DBAS性能的主要因素有：

1) 系统硬件资源

2) 网络通信设备性能

3) 操作系统环境

4) 数据库的逻辑设计和物理设计的质量、数据库配置参数等

5) DBMS的配置和性能

6) 数据可应用程序本身

系统设计

4. 数据库应用系统设计包括概念设计、逻辑设计、物理设计三个步骤。每个步骤中的设计活动按照数据组织与存储设计、数据访问与处理设计、应用设计三天主线进行。

三：需求分析与功能建模方法

1. 系统分析员的主要任务是确定应用信息系统及软件产品应该达到的各项功能性要求和非功能要求。

2. 系统分析员应该具备下列素质

1) 获取需求的能力

2) 管理及沟通能力

3) 技术素养

3.获取需求的方法

1) 面谈

2) 实地观察

3) 问卷调查

4) 查阅资料

4. 需求分析过程

1) 标识问题

2) 建立需求模型

3) 描述需求

5. DFD建模方法(Data Flow Diagram)

DFD方法由四种基本对象元素组成：数据流、处理、数据存储和数据源

1) 数据流。具有名字且有流向的数据，用标有名字的箭头表示。一个数据流可以是记录或基本项。

2) 处理。表示对数据进行的加工和变换，用矩形框表示。指向处理的数据流为该处理的输入数据，离开处理的数据流为该处理的输出数据。

3) 数据存储。表示用数据库形式(或文件形式)存储的数据。对其进行的存取分别以指向或离开数据存储的箭头表示。

4) 数据源及数据终点。表示当前系统的数据来源或数据去向。又称为外部项，用平行四边形表示。

6. IDEF0建模方法

基本元素：

矩形框代表活动，写在矩形框内的动词短语描述活动的名称，活动的编号按要求写在矩形框右下角指定的位置。

连到矩形框4条边上的箭头分别表示输入、控制、输出与机制。

建模规则：

1) 用动词或动词短语为矩形框内的活动名称命名。每个矩形框至少有一个控制箭头和输出箭头。一个活动可以没有输入但不允许既没有输入又没有控制。

2) 箭头代表数据约束，而不是代表流或顺序。

四：数据库概念设计及数据建模

1. 两种概念设计工具

ER方法和IDEF1X方法

2. 数据库概念设计阶段的目标

a. 定义和描述应用领域涉及的数据范围

b. 获取应用领域或问题域的信息模型

c. 描述清楚数据的属性特征

d.描述清楚数据之间的关系

e. 定义和描述数据的约束

f. 说明数据的安全性要求

g. 支持用户的各种数据处理需求

h. 保证信息模型方便地转换成数据库的逻辑结构，也方便用户理解。

3. 数据库概念设计包含以下基本步骤：

a. 确定实体集

b. 确定联系和联系类型

c. 建立有信息模型表示的企业模型

d. 确定实体集属性

e. 对信息模型优化

4. ER建模

基本概念：

a.实体

b.实体集

c.属性

d.码：实体集中能惟一标识每一个实例的属性或属性组称为该实体集的码。

e.联系

ER方法语法

矩形框表示实体集，矩形框内写上实体集的名称。

菱形框表示联系，联系名写在菱形框内。

属性用椭圆或圆角矩形表示，属性的名字写在椭圆或圆角矩形框内。

5. IDEF1X建模方法

实体集：用矩形框表示独立实体集。用圆角矩形表示从属实体集。每一个实体集分配一个惟一的名字和编码，名字和编码之间用斜杠(/)分开，放在矩形框的上方，编码应该是正整数。

联系：

标定型联系，-- 实线

非标定型联系， -- 虚线

分类联系，

不确定联系。两端带有实心圆的线段表示。

属性：

主码：属性后加(PK)标注，列在属性列表的顶端，其他属性列在下面，并且用水平线把主码与其他属性分开。

外码：属性后加(FK)标注是由联系继承得到的外来属性。

建模过程：

a. 建模规划及准备

b. 定义实体集

c. 定义联系

五：关系数据库逻辑设计

关系模型：是用最简单的方法，即采用一个二维表格在计算机中组织、存储和管理数据。

元组：关系（表）中的行

域：每个属性取值的类型

关系的完整性

a. 主码约束：惟一性，最小性

b. 外码约束

c. 用户定义的完整性

2. 属性集闭包

利用迭代法计算闭包X+

计算步骤：

3. 规范化设计方法

第一范式：关系中每一个属性值都是不可分的数据项。

九：事务与事务调度

1. 事务的性质， ACID

原子性 Atomicity

一致性 Consistency

隔离性 Isolation

持久性 Durability

2. 可串行化调度

如果并发调度S等价于某一定义在TS上的串行调度，那么S称为可串行化调度，否则S是不可串行化的调度。

3. 冲突等价

给定两个定义在事务集TS上的调度S和S'，如果可以通过交换S中一系列非冲突操作的执行顺序将S转换为S', 则称S与S'是冲突等价的。

如果定义在事务集TS上的并发调度S冲突等价于事务集TS上的某个串行调度S'. 则城S是冲突可串行的。

4. 主要的并发控制技术包括：

基于锁的并发控制技术

时间戳协议

基于有效性检查的协议

多粒度

多版本机制

互斥锁

共享锁

6. 常见的加锁协议有：

保证数据一致性的三级加锁协议

保证事务调度可串行性的两阶段锁协议 2PL(Phase Locking)

可预防死锁的加锁协议等

7. 三级加锁协议

1级加锁协议要求事务T在修改数据项Q之前必须对Q加X锁，直到事务结束才释放该锁。事务结束包括正常结束和非正常结束，但事务如果只是读Q而不对其进行修改，是不需要对Q加锁的。

2级加锁协议在1级加锁协议基础上，要求事务T在读取数据项Q之前必须先对S加锁，读完后可以立即释放S锁。

3级加锁协议则是在1级加锁协议基础上，要求事务T在读取数据项Q之前必须先对其加S锁，但是需要等到事务结束时才释放该S锁。

8. 遵守两阶段锁协议的并发事务的执行结果一定是正确的，但反过来，在一个可串行化调度中，并不一定所有事务都必须符合两阶段锁协议。

9. 目前在数据库系统中，解决死锁主要采用两类方法

一类是使用死锁预防以避免系统进入死锁状态

另一类则是允许系统进入死锁状态，但是在系统进入死锁状态以后使用死锁检测与恢复机制使系统摆脱死锁状态。

10. 活锁

为避免活锁现象的发生，DBMS可以采用先来先服务策略处理事务的数据操作请求。

十：数据库的实施、运行和维护

1. 数据库的试运行也称为联合调试。

2. 优化查询的常用方法

a. 合理使用索引

b. 避免或简化排序

c. 消除对大型表行数据的顺序存取

d. 避免相关子查询

e. 避免困难的正规表达式

f. 使用临时表加速查询

g. 用排序来取代非顺序磁盘存取

h. 不充分的连接条件

i: 存储过程

j: 不要随意使用游标

k: 事务处理

十一：其他

1. 数据模型按不同的应用层次分成三种类型：分别是概念数据模型、逻辑数据模型、物理数据模型。

概念数据模型 -- E-R模型

逻辑数据模型 -- 层次模型、网状模型、关系模型。

2.聚集索引，非聚集索引，填充因子，稠密索引，稀疏索引

索引键值的逻辑顺序与索引所服务的表中相应行的物理顺序相同的索引，被称为聚集索引，反之为非聚集索引。

聚集索引（Clustered Index）：对表的物理数据页中的数据按列进行排序，然后再重新存储到磁盘上，即如果说在一个表中建立了聚集索引，则表中的数据页会在会按照索引的顺序来存放

非聚集索引（Nonclustered Index）：具有完全独立于数据行的结构，使用非聚集索引不用将物理数据页中的数据按列排序，即非聚集索引不会影响数据表中记录的实际存储顺序。非聚集索引的叶节点存储了组成非聚集索引的关键字值和行定位器。

填充因子：指索引中一个叶子节点的填充率，若都填满就是100%，若填充率为50%，则只有一半的数据

如果被索引的字段的每个值都有一个索引与其对应，那么这种索引叫做稠密索引，否则叫做稀疏索引。

3.在数据库应用系统生命周期中，规划与分析阶段的主要工作内容有：

系统规划与定义

-任务陈述

-确定任务目标

-确定范围和边界

-确定用户视图

可行性分析

-技术可行性

-经济可行性

-操作可行性

-开发方案选择

项目规划

-项目团队

-项目环境

-项目活动

-成本预算

-进度计划

ADO - ActiveX Data Objects

Microsoft提出的应用程序接口（API）用以实现访问关系或非关系数据库中的数据

OLE 全称 Object Link and embed，即对象连接与嵌入

作为微软的组件对象模型（COM）的一种设计，OLE DB是一组读写数据的方法（在过去可能被称为渠道）。

LDAP

服务器的连接字串格式为： ldap://servername/DN

DN有三个属性: CN , OU , DC

DC -- Domain Component

CN -- Common Name

OU -- Organizational Unit

例如：

cn=test,ou=developer,dc=domain,dc=com

cn=test，代表一个用户名

ou 代表一个active directory中的组织单位。

test 这个对象在domainname.com域的developer 组织单元中。

MySQL

http://www.5idev.com/p-php_mysql_create_database.shtml

http://dev.mysql.com/doc/refman/5.1/zh/sql-syntax.html#alter-table、

1. 登录Command Line Client

如果有配环境变量，可以直接在shell下运行 mysql

否则到开始--所有程序--MYSQL-- 找到Command Line Client 点击进入。

2. 显示所有的数据库

show databases;

3. 使用或更换数据库

use databasename;

4. 列出表清单

show tables;

5. 列出表中的字段清单

show columns from tablename;

或则使用： desc tablename;

6. 列出表中的数据

select * from tablename;

7. 插入数据到表

insert into tablename(field1,field2) values (value1,value2);

8. 更新表数据

update tablename set field1='value1' where field2= '';

9. 创建数据库

create database databasename;

Oracle

修改 column 寬度

alter table Chart_M_Chart modify( CHART_NAME varchar2(100));

1. LEFT OUTER JOIN 后加条件竟然速度会慢6倍

INNER JOIN (select * from tablename WHERE NAME='XXX') t2 ON t1.branch_name = t2.branch_name AND UPPER(t1.project_name) = UPPER(t2.project_name))

LEFT OUTER JOIN (select * from

tablename where BRANCH_NAME='

XXX')

2. join 右边加条件变慢，左边加条件变快

大数据量时：

UPPER(BRANCH_NAME)=N'XXXX')

UPPER 和 N都会使速度变慢

语法：ROW_NUMBER() OVER(PARTITION BY COLUMN ORDER BY COLUMN)

简单的说row_number()从1开始，为每一条分组记录返回一个数字，这里的ROW_NUMBER() OVER (ORDER BY xlh DESC) 是先把xlh列降序，再为降序以后的每条xlh记录返回一个序号。

示例：

xlh row_num

1700 1

1500 2

1085 3

710 4

SELECT LEVEL l FROM dual CONNECT BY LEVEL<=10;

Oracle 性能调优方式：

1. index

2. rownum 分页

3. 分区是个好方法。

另外，如果经常按照某个字段查询的话，可以对该字典使用聚簇方法。这样子会大大减小需要读取的block。

作为数据库管理员能做的就是分区，加索引。如果想进一步优化，可以：

1.配合代码将表拆分；

2.使用job or procedure提取数据到中间表，查询时从中间表查。

3.优化SQL（这个方法针对编写的比较垃圾的SQL还是比较有用的）

4.增加服务器性能。

数据库上：可以添加分区，增加索引，建立视图，增大缓冲区，将语句KEEP到共享池……

服务器硬件：增加CPU,增大内存,使用高速磁盘或者使用SSD……

1)最常见的调优方法是在数据库中增加索引，索引(index)是常见的数据库对象，充分利用索引来减少表扫描的 I/O 次数，使用索引可以避免不必要的大表全表扫描，索引设置的位置要视 where 子句中索引列所应用的查询条件而定，通过索引对表的数据进行检索比起直接全表扫描所引起的I/O操作要小得多，索引可增加查询速度;

2)若某种业务的数据量增长非常快，可以使用分区表技术将数据进行分散，将不同表空间分布到不同的磁盘当中，使得硬盘之间 I/O 负载均衡，在一定程度上缓解了数据量过大引起的负面影响，并且会缩短查询时间;

3)使用存储过程完成数据库中频繁执行的应用逻辑，使代码编程与数据库的操作分离，可以降低网络传输量，提高数据请求的执行效率，执行存储过程时，用户只需要发出执行命令，而不再进行SQL语句提交，节省了系统的SQL语法分析，充分利用了SQL共享池;

4)使用Oracle优化器执行直接提交的查询SQL 语句，可以大大提高语句的执行效率;

5)使用触发器自动执行的SQL语句，可以降低网络 I/O及系统资源的消耗，如需要对所有访问数据库的程序自动实施一定规则或检查，则使用触发器可以大大提高效率。

6)根据数据库运行状况重新调整SGA的大小，对每个节点修改SGA大小的方法如下：

　　SQL>alter system set sga_target=200m scope =both sid=’RACDB1’;

　　SQL>alter system set sga_target=200m scope =both sid=’RACDB2’;

7)调整 SQL 的关键是使数据库寻找数据的路径最简化，限制动态SQL的使用，优化操作符，如in或not in，is null 或 is not null，like ，union 等操作符，应尽量少用;

8)避免相关子查询，查询嵌套层次越多，效率越低，为了加速查询速度，可以使用临时表;

个人总结一下sql优化，来提高查询速度：

1、写查询语句时，要把查询的字段一一列出

查询时不要使用类似select * from x_table的语句，要尽量使用select id,name from x_table，以避免查询出不需要的数据浪费资源。对于海量数据而言，一个字段所占用的资源和查询时间是相当可观的。

2、减少不必要的查询条件

当我们在做查询时，常常是前台提交一个查询表单到后台，后台解析这个表单，而后进行查询操作。在我们解析表单时，为了方便起见，常常喜欢将一些不需要查询的条件用永真的条件来代替（如：select count(id) from x_table where name like ‘%’），其实这样的SQL对资源的浪费是相当可怕的。我试过对于同样的近一千万条记录的查询来说，使用select count(id) from x_table 进行表查询需要11秒，而使用select count(id) from x_table where name like ‘%’却花了33秒。

3、避免在查询时使用表连接

在做海量数据查询时，应尽量避免表连接（特别是左、右连接），万不得已要进行表连接时，被连接的另一张表数据量一定不能太大，若连接的另一张表也是数万条的话，那估计可以考虑重新设计库表了，因为那需要等待的时间决不是正常用户所能忍受的。

4、嵌套查询时，尽可能地在第一次select就把查询范围缩到最小

在有多个select嵌套查询的时候，应尽量在最内层就把所要查询的范围缩到最小，能分页的先分页。很多时候，就是这样简单地把分页放到内层查询里，对查询效率来说能形成质的变化。

row_number()over(partition by col1 order by col2)表示根据col1分组，在分组内部根据col2排序，而此函数计算的值就表示每组内部排序后的顺序编号（组内连续的唯一的）。

与rownum的区别在于：使用rownum进行排序的时候是先对结果集加入伪劣rownum然后再进行排序，而此函数在包含排序从句后是先排序再计算行号码。

row_number()和rownum差不多，功能更强一点（可以在各个分组内从1开始排序）。

rank()是跳跃排序，有两个第二名时接下来就是第四名（同样是在各个分组内）

dense_rank()也是连续排序，有两个第二名时仍然跟着第三名。相比之下row_number是没有重复值的。

INNER JOIN

create table TEST_EMPLOYEE

(

EID NUMBER,

DEPTID NUMBER,

ENAME VARCHAR2(200),

ADDRESS VARCHAR2(200)

);

create table TEST_DEPT

(

DEPTID NUMBER,

DEPTNAME VARCHAR2(200)

);

insert into TEST_EMPLOYEE values(100,1,'zhang san','Stress 1');

insert into TEST_EMPLOYEE values(100,1,'zhang san','Stress 2');

insert into TEST_EMPLOYEE values(100,1,'zhang san','Stress 3');

insert into TEST_DEPT values(1,'IT');

insert into TEST_DEPT values(1,'IT2');

count(*) 与 count(列) 谁快? 众说纷纭

有不同的观点。

个人测试感觉是 count(*)比count(列) 稍微快一点

官方的说明是一样的：

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1156159920245

分页查询

SELECT *

FROM (

SELECT ROWNUM SN, t.*

FROM Esdm_Project_Config t

ORDER BY ROWID

)

WHERE SN>5 AND SN<=30;

in MySQL, I can do this:

select *

from sometable

order by name

limit 20,10

to get the 21st to the 30th rows (skip the first 20, give the next 10). The rows are selected after the order by, so it really starts on the 20th name alphabetically.

In Oracle, the only thing people mention is the rownum pseudo-column, but it is evaluated before order by, which means this:

select *

from sometable

where rownum <= 10

order by name

will return a random set of ten rows ordered by name, which is not usually what I want. It also doesn't allow for specifying an offset.

Fetch size

问题描述：大量的外部终端通过socket访问内部的UNIX服务器，要求从服务器的Oracle数据库中得到数据在终端处理显示。这里要求socket最大的传输量是5000个字符，那么对于海量数据就要求分批次取得数据并且多次传输，怎么实现呢？

解决方案：首先考虑使用ROWNUM，但是在Oracle（version 10g）中，ROWNUM不能使用">", 并且经过简单测试，发现“ORDER BY XXX”与 ROWNUM结合起来查询时，不能确保查询到的数据一致，顺序变了（为什么？我想可能是先做了SELECT, 然后再做的ORDER BY 排序，所以会出现这种情形。真相以后再查证啦..）。所以考虑嵌套SELECT解决ROWNUM不能使用“>”的问题，那么ORDER BY 呢？考虑到ROWID是唯一标识记录的标志，就用ORDER BY ROWID吧，简单测试后，问题貌似解决了。

oralcle 语法

alter table SQA_ALL_ISSUE MODIFY (FEATURE_NAME varchar2(200));

ALTER TABLE (表名) ADD (列名数据类型);

ALTER TABLE (表名) MODIFY (列名数据类型);

ALTER TABLE (表名) RENAME COLUMN (当前列名) TO (新列名);

ALTER TABLE (表名) DROP COLUMN (列名);

ALTER TABLE (当前表名) RENAME TO (新表名);

这是SQL 语法，修改栏位的类型与长度

ALTER TABLE table_name

ALTER COLUMN column_name datatype

计算的栏位用来排序看上去不work.

看上去要使用 MAIL_DATE||PRODUCT_LINE

是否是此原因导致？正常的栏位是否如此？

TO_CHAR(TO_DATE('2013/12/30','YYYY/MM/DD')+1, 'YYIW')

年底的最后一天会有问题。

会跑到上一年去。

如果一年当中第52周别之后至当年的12月31日之间，还有大于或等于4天的话，则定为当年的第53周，

否则剩余这些天数被归为下一年的第1周；

如果在不足52周别的话，则以下一年的时间来补；

每周固定的从周一开始作为本周的第1天，到周日作为本周的第7天；

比如：在Oracle中 2006/01/01 依然属于IW周别 05年的第52周的第7天

DROP INDEX <index_name>; 删除index

select * into target_table from source_table; ==> 目标 table 不存在

insert into target_table(column1,column2) select column1,5 from source_table; 目标 table存在

在Oracle中select into from不可以使用-----原因很简单：select into是PL/SQL language 的赋值语句！如果使用则Oracle会抛出0RA-00905:missing keyword的异常！

但是可以用create table as select代替该功能！！！

SQL> select * into scott.v from scott.emp;

select * into scott.v from scott.emp

ERROR at line 1:

ORA-00905: missing keyword

SQL> create table scott.v as select * from scott.emp;

Table created.

两种方法实现两个数据的copy

1. create as select * ; index 是否会创建出来？ ==》 index 不会自动创建

2. 先把table create 出来，在insert into

ORA-01445

定义view 的时候

如果 trunc(tfail.FAIL_COUNT*100/tall.BUILD_COUNT,2) as FAIL_RATE

没有给 FAIL_RATE 这样的别名的话会产生这个错误。

ORA-01445: 无法从没有键值保存表的连接视图中选择 ROWID

where 和 on 的区别

nvl函数，具体语法如下nvl(val1,val2)如果val1为空值，则返回val2，否则返回val1，val1，val2数据类型匹配，否则出错。

case 函数

CASE sex

WHEN '1' THEN '男'

WHEN '2' THEN '女'

ELSE '其他' END

oracle val 函数

WHERE name=N'sdaf'

加上 N 代表存入数据库时以 Unicode 格式存储。

N'string' 表示string是个Unicode字符串

create index idx_XXX on XXXX(XX,XX,XXX);

create index Stuname on student(name);

24：wm_contact

select id,wmsys.wm_concat(items) name from table

group by id;

23.

oracle中字段类型varchar2最大长度是多少？ 4000

21. Select 'test''' from dual;

单引号输入

20. 连接行的值

sys_connect_by_path

19.

MERGE INTO t_tianjue_maomao USING dual ON ( day='10-11-22' and domain='cpc.brand.sogou.com' and ip='61.135.188.148')

WHEN MATCHED THEN UPDATE SET "COUNT"="COUNT"+1

WHEN NOT MATCHED THEN insert values('10-11-22','cpc.brand.sogou.com','61.135.188.148',1);

18;

oracle sql developer 默认显示50条，往下拉的时候动态取

这个路径下的

C:\Documents and Settings\User\Application Data\SQL Developer\system3.2.09.30\o.sqldeveloper.11.2.0.9.30

这个文件：

product-preferences.xml

中的这段：

</hash>

这个值配置。

在工具--》首选项--》数据库--》高级

SQL 数组提取大小(最大值500), 不填的话使用50

17.

oracle的substr函数的用法, start_position从 1 开始(如果是 0的话，默认为1)，截取的字串包含start_position从

取得字符串中指定起始位置和长度的字符串 substr( string, start_position, [ length ] )

如:

substr('This is a test', 6, 2) would return 'is'

substr('This is a test', 6) would return 'is a test'

substr('TechOnTheNet', -3, 3) would return 'Net'

substr('TechOnTheNet', -6, 3) would return 'The'

16. 忽略大小写

upper(col) = 'MT'

15.查看Oracle的连接信息

select username,count(username) from v$session group by username;

14. sqlplus dbuser/dbpassword@192.168.0.2/mydb

13. tkprof

tkprof 是一个分析oracle跟踪文件并且产生一个更加人性话清晰的输出结果的可执行工具

位于 /oracle/ora92/bin/tkprof.exe

1) alter system set timed_statistics=true; (好像可以不设)

2) alter session set sql_trace=true;

) 用户级自跟踪：

SQL>ALTER SESSION SET SQL_TRACE=TRUE;

SQL>ALTER SESSION SET SQL_TRACE=FALSE;

用户级DBA跟踪：(例如sys跟踪test，需要用sysdba登录)

. a). SQL>select s.USERNAME,s.SID,s.SERIAL#,s.COMMAND from v$session s

where s.USERNAME='COLM' ;

b). SQL>exec sys.dbms_system.set_sql_trace_in_session(9,7,true);

c). SQL>exec sys.dbms_system.set_sql_trace_in_session(9,7,false);

3) tkprof colm_ora_2056.trc trace.txt print=100 record=sql.txt sys=no

12.exists, not exists, in, not in

select participant from usr where exists (select * from roleassn where usr.participant = roleassn.usrgrpname);

11. oracle常见SQL

show user;

显示当前连接用户

select * from all_users; -- 查看系统拥有哪些用户

create user user1 identified by pass1; -- 默认建在SYSTEM表空间里

grant connect,resource to user1;

conn user1/pass1;

select * from tab; --查询当前用户下所有的对象

create table table1(a number); --创建表

desc table1; - 查询表结构

insert into table1 values(); -插入数据

10. 删除表空间和user

select username,default_tablespace from dba_users where default_tablespace='TSTDIPSPACE';

select t1.name,t2.name from v$tablespace t1, v$datafile t2 where t1.ts# = t2.ts#;

drop tablespace TSTDIPSPACE including contents and datafiles;

ALTER USER cde30 ACCOUNT UNLOCK;

SQL having如何使用？

9.Oracle中TO_DATE TO_CHAR格式

http://www.douban.com/note/31849478/

http://apps.hi.baidu.com/share/detail/30480331

http://wenwen.soso.com/z/q338902739.htm

8.字符型的格式

to_char(t1.SWITCHINGPOWER,'FM99,999,999,999,990.000000')

1：其9代表：如果存在数字则显示数字，不存在则显示空格

2：其0代表：如果存在数字则显示数字，不存在则显示0，即占位符。

3：其FM代表：删除如果是因9带来的空格，则删除之

7.日期型转换为字符型

to_char(CHECKLIST_RESULT."DATE",'yyyy/mm/dd') as "DATE",

6.查看oracle版本

select * from v$instance;

select * from v$version;

5. 创建关键字的栏位

那就是在以关键字如level和size等命名的字段，在上边加上“”号，即“size”，“level”

enq是一种保护共享资源的锁定机制，一个排队机制，先进先出(FIFO)

发生TX锁的原因一般有几个:

不同的session更新或删除同一个记录。

唯一索引有重复索引

位图索引多次更新

同时对同一个数据块更新

等待索引块分裂

enq:TX

1）. enq: TX - row lock contention ，顾名思义，该event是row级别的，要处理该问题，唯一的方式就是调整应用逻辑。

2）. enq: TX - allocate ITL entry ，该event明显是index itl不足（通常是在高并发的情况下），当然最简单的方式就是调大init_trans了。

3）. enq: TX - index contention ，该event的出现通常也是在高并发的时候，不过一般是由于index split等导致，处理方式的话，一般手段是将index重建为反向键index或将index进行hash分区。如果是跟sequence相关的话，那建议将sequence cache值调大。

4）. enq: TX - contention ，该event跟前面3种不一样了，该event属于事务级别。原理跟第一类似，不过是针对事务而言了。

3. oracle 前几条

SELECT * FROM TABLE1 WHERE ROWNUM < 2 ;

db time= cpu time + wait time（不包含空闲等待）（非后台进程）

说白了就是db time就是记录的服务器花在数据库运算(非后台进程)和等待(非空闲等待)上的时

--------physical reads

Total number of data blocks read from disk. Thisnumber equals the value of "physical reads direct" plus

all reads into buffer cache.

physical reads direct

Number of reads directly from disk, bypassing the buffer cache. For example, in high bandwidth,data-intensive operations such as parallel query, reads of disk blocks bypass the buffer cache to maximize transfer rates and to prevent the premature aging of shared data blocks resident in the buffer cache.

物理读是内容不在内存中，要去硬盘中读入内存增加IO

逻辑读内容在内存中，不需要读硬盘无IO

1. 当前的会话记录保存在v$session中

处于等待的会话会被复制一份放在 v$session_wait

视图： v$session_wait_history，每个活动session在v$session_wait中最近10次的等待事件

视图： v$active_session_history ， ASH。保存处于等待状态的活动session的信息，每秒从v$session_wait中采样一次，并将采样信息保存在内存中

视图： wrh$_active_session_history，AWR（auto workload repository）。

数据库访问性能优化法则

http://blog.csdn.net/yzsind/archive/2010/12/06/6059209.aspx

1.oracle 字符串连接符是 “||”

2.oracle 错误查询

http://www.superfunction.net/oraerror/error_code.jsp?OraNo=01747

3. oracle 保留字查询

select * from v$reserved_words

4. declare v_XXX varchar2(60);

begin

for curTable in (select XX as XXf rom XX)

loop

dbms_output.put_line(curTable.XX);

end loop;

commit;

end;

create tablespace oscar999 datafile 'D:\oracle\product\10.2.0\oradata\orcl\oscar999.dbf' size 50M;

create user oscar999 identified by oscar999 default tablespace oscar999;

grant dba to oscar999;

commit;

autoextend on next 10M maxsize 10G;

create tablespace PRPC61 datafile 'D:\oracle\product\10.2.0\oradata\orcl\PRPC61.dbf' size 4G autoextend on next 50m maxsize 6G;

create user prpc61 identified by prpc61 default tablespace PRPC61;

grant dba to prpc61;

commit;

DROP TABLESPACE dzzwpt INCLUDING CONTENTS AND DATAFILES;

6. 查看字符集

服务端字符集

select * from nls_database_parameters;

客户端字符集

select * from nls_instance_parameters;

会话字符集

select * from nls_session_parameters;

客户端的字符集要求与服务器一致，才能正确显示数据库的非Ascii字符

字符集要求一致，但是语言设置却可以不同，语言设置建议用英文。如字符集是zhs16gbk，则nls_lang可以是American_America.zhs16gbk。

create table tstDMSID

(

NEWID varchar2(40),

OLDID varchar2(40)

)

7.http://space.itpub.net/8334342/viewspace-474816

现象：

在数据库EXP/IMP操作，需要设置NLS_LANG环境变量，以保证数据库在导入导出时，保证不出现乱码

8. db lock

SELECT A.OWNER,

A.OBJECT_NAME,

B.SESSION_ID,

B.ORACLE_USERNAME,

B.OS_USER_NAME,

B.PROCESS,

B.LOCKED_MODE,

C.SID,

C.SERIAL#,

C.PROGRAM

FROM ALL_OBJECTS A,

V$LOCKED_OBJECT B,

SYS.GV_$SESSION C

WHERE ( A.OBJECT_ID = B.OBJECT_ID )

AND (B.PROCESS = C.PROCESS )

AND A.OBJECT_NAME='ZBINDB';

alter system kill session 'sid,serial#' immediate;

9.字符转码

CONVERT(NAME,'WE8ISO8859P1','ZHS16GBK')

select CONVERT(NAME,'WE8ISO8859P1','ZHS16GBK') from tab_user;

10.查看oracle 支持的字符集

select * from v$nls_valid_values where parameter='CHARACTERSET';

SIMPLIFIED CHINESE_CHINA.ZHS16GBK

AMERICAN_AMERICA.ZHS16GBK

select XX from XX where XX='60701';

11.excel是采用的UTF-8编码

12.-bom utf-8可以强制在提取的文本前添加EF BB BF前缀

13. drop user ；仅仅是删除用户，drop user ×× cascade ；会删除此用户名下的所有表和视图。

14.

外键的作用？

外键是数据库一级的一个完整性约束，就是数据库基础理论书中所说的“参照完整性”的数据库实现方式。

外键属性当然是可以去掉的，如果你不想再用这种约束，对编程当然不会有什么影响，但相应的录入数据的时候就不对录入的数据进行“参照完整性”检查了

FOREIGN KEY 约束用于预防破坏表之间连接的动作。

FOREIGN KEY 约束也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。

15. havaing count

16. char和varchar2的区别

1．CHAR的长度是固定的，而VARCHAR2的长度是可以变化的，比如，存储字符串“abc"，对于CHAR (20)，表示你存储的字符将占20个字节(包括17个空字符)，而同样的VARCHAR2 (20)则只占用3个字节的长度，20只是最大值，当你存储的字符小于20时，按实际长度存储。

2．CHAR的效率比VARCHAR2的效率稍高。

CHAR与VARCHAR2是一对矛盾的统一体，两者是互补的关系.

VARCHAR2比CHAR节省空间，在效率上比CHAR会稍微差一些，即要想获得效率，就必须牺牲一定的空间，这也就是我们在数据库设计上常说的‘以空间换效率’。

VARCHAR2虽然比CHAR节省空间，但是如果一个VARCHAR2列经常被修改，而且每次被修改的数据的长度不同，这会引起‘行迁移’(Row Migration)现象，而这造成多余的I/O，是数据库设计和调整中要尽力避免的，在这种情况下用CHAR代替VARCHAR2会更好一些。

varchar2的最大长度是4000bite。

可见char的最大长度是2000bite。

http://hi.baidu.com/bjn_wuming/blog/item/9e2fc34ec60277c7d1c86a35.html

DECLARE v_job NUMBER(10);

SELECT job INTO v_job FROM employee；

declare sRight varchar2(60);

icount number(4);

begin

for curloop

if icount>2 then

end if;

end loop;

commit;

end;

http://jxb1016.iteye.com/blog/648897

SQLServer

3. Query 其他数据库的table 需要加上dbo

user.dbo.tablename

2. sql server 字符串连接使用 +

oracle 使用 ||

1. 显示表栏位的命令， oracle 是Desc

sp_help 表名；

分离数据库

use master

create proc killspid (@dbname varchar(20))

begin

declare @sql nvarchar(500),@temp varchar(1000)

declare @spid int

set @sql='declare getspid cursor for

select spid from sysprocesses where dbid=db_id('''+@dbname+''')'

exec (@sql)

open getspid

fetch next from getspid into @spid

while @@fetch_status<>-1

begin

set @temp='kill '+rtrim(@spid)

exec(@temp)

fetch next from getspid into @spid

end

close getspid

deallocate getspid

end

--用法

use master

exec killspid '数据库名'

在sql 2000中删除数据库有时会提示“无法删除数据库因为该数据库当前正在使用”。

解决办法：

第1步、右键->所有任务->分离数据库->在弹出的界面先点击"清除",此清除是关闭连接。最后一步点"取消"。此一步目的就是为了断开所有与此数据库的连接。并非真正的分离数据库。

第2步、删除数据库即可。

注：sql2005操作就不用这么复杂了。

改变数据库的Owner

USE MSDB

EXEC sp_changedbowner 'sa'

Performance tuning

http://blog.csdn.net/robinson_0612/article/details/7422254

http://wenku.baidu.com/view/56c61f19fad6195f312ba633.html

http://hi.baidu.com/optical/item/840dd4445d72e42311ee1e52

http://www.erp100.com/thread-31755-1-1.html

http://nesta2001zhang.iteye.com/blog/1157868

http://wenku.baidu.com/view/87c87f6548d7c1c708a145b5.html

工具： MySQL SQL Query Analyzer，Oracle SQL Performance Analyzer，或是微软SQL Query Analyzer

全表检索： select * from user where lastname = “xxxx” 性能差

解决方法： 1. 分表 2. 建索引

索引：不要在索引字段上做计算、类型转换、函数、空值判断、字段连接操作。索引一般出现在where 或是 Order By子句中，所以对Where 和Order By 子句中的字段最好不要进行计算操作，或是加上什么NOT之类的或是使用什么函数。

多表查询：。有人说，EXISTS的性能要好于IN，IN的性能要好于JOIN，我各人觉得，这个还要看你的数据、schema和SQL语句的复杂度，对于一般的简单的情况来说，都差不多，所以千万不要使用过多的嵌套，千万不要让你的SQL太复杂，宁可使用几个简单的SQL也不要使用一个巨大无比的嵌套N级的SQL。还有人说，如果两个表的数据量差不多，Exists的性能可能会高于In，In可能会高于Join，如果这两个表一大一小，那么子查询中，Exists用大表，In则用小表。这个，我没有验证过

JOIN操作。有人说，Join表的顺序会影响性能，只要Join的结果集是一样，性能和join的次序无关。因为后台的数据库引擎会帮我们优化的。Join有三种实现算法，嵌套循环，排序归并，和Hash式的Join。（MySQL只支持第一种）

嵌套循环，就好像是我们常见的多重嵌套循环。注意，前面的索引说过，数据库的索引查找算法用的是B-Tree，这是O(log(n))的算法，所以，整个算法复法度应该是O(log(n)) * O(log(m)) 这样的。

Hash式的Join，主要解决嵌套循环的O(log(n))的复杂，使用一个临时的hash表来标记。

排序归并，意思是两个表按照查询字段排好序，然后再合并。当然，索引字段一般是排好序的。

还是那句话，具体要看什么样的数据，什么样的SQL语句，你才知道用哪种方法是最好的。

部分结果集：

一般来说，返回top n的记录数据需要我们使用order by，注意在这里我们需要为order by的字段建立索引。有了被建索引的order by后，会让我们的select语句的性能不会被记录数的所影响。使用这个技术，一般来说我们前台会以分页方式来显现数据，Mysql用的是OFFSET，SQL Server用的是FETCH NEXT，这种Fetch的方式其实并不好是线性复杂度，所以，如果我们能够知道order by字段的第二页的起始值，我们就可以在where语句里直接使用>=的表达式来select，这种技术叫seek，而不是fetch，seek的性能比fetch要高很多

字符串。正如我前面所说的，字符串操作对性能上有非常大的恶梦，所以，能用数据的情况就用数字，比如：时间，工号，等。

全文检索。千万不要用Like之类的东西来做全文检索，如果要玩全文检索，可以尝试使用Sphinx。

其它。

不要select *，而是明确指出各个字段，如果有多个表，一定要在字段名前加上表名，不要让引擎去算。

不要用Having，因为其要遍历所有的记录。性能差得不能再差。

尽可能地使用UNION ALL 取代 UNION。

索引过多，insert和delete就会越慢。而update如果update多数索引，也会慢，但是如果只update一个，则只会影响一个索引表。

等等。

下午一个同事过来说某Linux测试服务器超级慢，严重影响工作。

登录到服务器上使用top查看，发现oracle进程把CPU占到了100%。

于是用了以下步骤把导致性能问题的SQL找出来：

1、使用top找到占CPU100%的ORACLE进程的pid为1234；

2、打开PL/SQL DEV，查询这个进程的信息：

SELECT * FROM V$PROCESS WHERE spid=1234;

3、找到这个进程对应的会话的信息：

SELECT sid, program FROM V$SESSION s

WHERE EXISTS(SELECT 1 FROM V$PROCESS WHERE spid=1234 AND ADDR = S.PADDR);

4、从上面已经知道是哪个客户端的哪个程序导致的了，继续：

找到这些会话的SQL

SELECT sql_text

FROM V$SESSION a LEFT OUTER JOIN v$sqltext b ON a.sql_address=b.address

WHERE EXISTS(SELECT 1 FROM V$PROCESS WHERE spid=17518 AND ADDR = a.PADDR)

ORDER BY a.SERIAL#, b.piece

5、这个语句要更好一点，根据CPU占用情况排序

SELECT b.sql_text

FROM V$SESSION a LEFT OUTER JOIN v$sqlarea b ON a.sql_address=b.address

WHERE EXISTS(SELECT 1 FROM V$PROCESS WHERE spid=1234 AND ADDR = a.PADDR)

ORDER BY b.CPU_TIME DESC

转载地址：https://oscar.blog.csdn.net/article/details/78980020 如侵犯您的版权，请留言回复原文章的地址，我们会给您删除此文章，给您带来不便请您谅解！

上一篇：EMS --Web Development Kit开发

下一篇：C-拾贝

发表评论

关于作者

喝酒易醉，品茶养心，人生如梦，品茶悟道，何以解忧？唯有杜康！

-- 愿君每日到此一游！

数据库设计最佳实践

联合查询

层次数据库

数据库四级考试相关

LDAP

MySQL

Oracle

SQLServer

Performance tuning

发表评论

最新留言

关于作者

推荐文章