数据库关闭

      其实上次那篇是想写oracle启动与关闭的,岂料图太难画了,所以只写了启动的部分,关闭没有涉及。其实关闭的部分我也想了一个不错的例子,今天跟大家分享一下我对oracle关闭的一些理解。如果有不对的地方,还望各位指正。

我觉得oracle的很多东西,应该是oracle的工程师一步一步的向自己提出问题,然后解决问题,最后慢慢完善起来的。比如昨天看到的重做日志:一般我们认为做一个联机重做日志就行了吧?oracle觉得光联机的时候用不安全,还要可以备份——归档日志就有了;一般我们觉得一个联机重做日志就行了吧?oracle认为写满了归档的时候不能写入归档日志,影响数据库性能——联机重做日志文件组就有了;一般我们觉得一个组就一个重做日志就行了吧?oracle怕一个文件损坏就杯具了——多个重做日志互为镜像就有了……

这一次我也用问题引导的方式跟大家一起分享一下我对关闭数据库的理解。

  1. 关闭数据库需要什么样的权限?

只有具有SYSDBA或SYSOPER权限的用户才能启动DB。回忆下开启数据库,是不是很像呢?其实一样……

  1. 关闭数据库需要用什么工具呢?

a)       SQL*Plus

b)       OEM控制台

c)        Recover Manager管理工具

d)       命令行管理方式

如上次我说,我还是对前两者比较熟悉,第三个好像是传说中的RMAN?呵呵

  1. 数据库关闭分为几个阶段?

如图所示,数据库关闭分为三个阶段,分别是关闭数据库,卸载数据库,关闭实例。这三个阶段连续,数据库不会停在其中某阶段。

数据库的启动好比上楼梯,一共三个台阶,你可以一步迈到顶,也可以一步一步往上走。数据库的关闭好比跳楼,三层楼,你只能一次跳到底,不能跳到二楼,再往下跳……这样一说应该好理解了吧。

  1. 数据库的关闭方式有几种呢?

这个其实对应着开启,但有些不同。开启对应着迈步的方式,是一次一级台阶,还是两级,或是三级。关闭对应着跳楼的方式,前空翻,后仰,还是……我邪恶了。

关闭的方式有四种:

l   正常关闭(NORMAL)

执行条件:无用户连接到数据库上

执行命令后如何运行:

i.禁止任何用户建立新连接

ii.等待当前用户主动断开连接

iii.所有连接断开后关闭数据库

结果:数据库一致完整

l   事务关闭方式(TRANSACTIONAL)

执行条件:所有事务执行完毕

执行命令后如何运行:

i.禁止任何用户建立新连接

ii.等待所有正执行的事务完成

iii.关闭数据库

结果:数据库一致完整

l   立即关闭方式(IMMRDIATE)

原因:即将开始数据库自动备份;即将系统断电;无法通知用户断开

执行条件:将未提交事务退回,直接关闭数据库

执行命令后如何运行:

i.禁止任何用户建立新连接

ii.回退所有未提交事务

iii.断开连接,关闭数据库

结果:数据库一致完整

l   终止关闭(ABORT)

原因:数据库异常,无法关闭;一分钟内断电;启动数据库中故障

执行命令后如何运行:

i.禁止任何用户建立新连接

ii.终止SQL语句执行

iii.不回退事务

iv.高速缓存不屑道数据文件中

v.重做日志缓冲的内存不写到重做日志文件中

vi.断开所有连接,关闭数据库

这四种关闭方式的区别,以图表的方式可能更好理解:

  等待连接 等待事务 执行检查 直接SHUTDOWN
  NORMAL TRANSACTIONAL IMMEDIATE ABORT
允许新连接 × × × ×
等待连接结束 × × ×
等待事务结束 × ×
强制执行检查点和关闭物理文件 ×

注意:

1.   最上面一行,是这四种关闭方式最根本的区别,可帮助大家一句话记住。

2.   对于NORMAL和TRANSACTIONAL,我觉得区别是这样的:

NORMAL 等待连接结束,若有用户执行完事务,但仍然连接,则不能关数据库
TRANSACTIONAL 等待事务结束,若有用户执行完事务,但仍然连接,则oracle将断开其连接,关闭数据库

上面对数据库的四种关闭方式做了详细的介绍,我这再举一个生活中的例子,希望能帮助大家更容易的理解数据库的关闭。

关闭数据库像是饭店打烊一样:

NORMAL:执行此命令后,不接待新客人(禁止新连接),等最后一个客人主动离开后(断开连接),打扫收拾(执行备份,检查完整性等操作),关门(关闭数据库)。

TRANSACTIONAL:执行此命令后,不接待新客人(禁止新连接),等客人吃饭饭后结账(比如吃晚饭正在聊天),就将其赶走(断开连接),打扫收拾(执行备份,检查完整性等操作),关门(关闭数据库)。

IMMEDIAT:执行此命令后,不接待新客人(禁止新连接),把所有正在吃饭的客人赶走(回退事务,断开联机),打扫收拾(执行备份,检查完整性等操作),关门(关闭数据库)。

ABORT:执行此命令后,不接待新客人(禁止新连接),把所有正在吃饭的客人赶走(回退事务,断开联机),打扫收拾(执行备份,检查完整性等操作),关门(关闭数据库)。

  1. 关闭数据库用什么命令?

SQL>SHUTDOWN NORMAL;

作者:仇实

数据库启动

      使用Oracle,必须要启动数据库。但是很多人只知道启动数据库的命令为SHTARTUP,,但其背后究竟系统是如何工作的,并不清楚。今天跟大家一起分享一下我对数据库开启的理解。

  1. 启动数据库应具有的权限

数据库不是任何用户想启动就能启动的,必须具有一定的权限,Oracle规定具有SYSDBA权限的用户才能启动数据库,SYS和SYSTEM用户可以启动,其他具有SYSDBA权限的用户也可以启动。但是要注意一点,以SYS用户登陆的时候,可以使用SYSDBA或是SYSOPER身份;以SYSTEM用户登录只能使用SYSDBA权限(见下表)。

  SYSDBA SYSOPER NORMAL
SYS ×
SYSTEM × ×
  1. 启动数据库的工具

a)         SQL*Plus

b)         OEM控制台

c)          Recover Manager管理工具

d)         命令行管理方式

通常我使用SQL*Plus启动数据库,但是也可以登录OEM后点击相关图标启动数据库,后两种方式还没有使用过。

  1. 数据库启动模式

数据库的启动可以被看作4个阶段(或3个,不包括SHUTDOWN的话)。

如上图所示,数据库启动的四个阶段为SHUTDOWN,NOMOUNT,MOUNT和OPEN。

模式下面紧跟的文字说明为在此种模式下DBA可对数据库进行的操作,大箭头指的是在左下侧模式切换到右上侧模式时,数据库进行的操作。

 从SHUTDOWN模式切换到NOMOUNT模式,数据库查找初始化参数文件,并启动实例。在NOMOUNT模式下,数据库未被挂载,此时DBA可进行创建新数据库,中间控制文件等操作。

 从NOMOUNT模式切换到MOUNT模式,数据库查找控制文件,加载数据库。在MOUNT模式下,数据库已被加载,但是没有被打开。此时DBA可以进行对数据文件重命名、添加删除重命名重做日志、恢复数据库、改变数据库归档模式等操作。

 从MOUNT模式切换到OPEN模式,执行打开数据库的操作。在OPEN模式下,数据库已被打开。此时DBA可对数据库进行查询修改插入等操作。

 以上的四个模式,如果理解上有困难,我想了一个例子,可能能帮助理解。

 数据库可被看做是一个仓库,实例可以被看作是仓库工人。

                                例子 实际
SHUTDOWN 有一个空仓库,仓库里没有货物,没有管理仓库的工人。 数据库关闭,实例未启动
NOMOUNT 有一个空仓库,仓库里没有货物,但是为仓库找了一些工人(实例)。 数据库关闭,实例启动
MOUNT 运来一些货物,堆在仓库里,仓库里,但是不能搬运这些货物(挂载数据库,但未打开数据库)。工人只能在仓库外活动,不对仓库中货物进行操作 数据库已经加载,但是并未打开。
OPEN 仓库里有货物,仓库外有工人,工人可搬运仓库中的货物(已打开数据库并可进行插入修改等操作) 数据库已加载并打开
  1. 一些必须知道的命令

l   启动到指定模式

SQL>STARTUP NOMOUNT;

SQL>STARTUP MOUNT;

SQL>STARTUP OPEN;

(只写STARTUP默认为STARTUP OPEN)

l   强行启动数据库

SQL>STARTUP MOUNT FORCE;

STARTUP FORCE = STARTUP OPEN FORCE

l   按指定的初始文件启动到相应模式

SQL>STARTUP MOUNT PFILE=d:\oracle\admin\oradb01\pfile\init002.ora;

对SPFILE文件, 用SPFILE命令替换PFILE

此种模式下也可用FORCE

l   在启动模式间转换(只能向上转换)

SQL>STARTUP NOMOUNT;

SQL>ALTER DATABASE MOUNT;

作者:仇实

在Linux虚拟机中安装Oracle Database 10g

 

 Oracle Database 一般运行在Linux操作系统下,我们需要安装在Linux操作系统下安装和使用Oracle DB来熟悉这种环境,为将来打下基础。为了避免安装双系统带来的一些麻烦,我们可以在虚拟机里安装使用Linux。以下介绍了在Linux虚拟机中安装Oracle DB的方法

准备:(1) Red Hat Enterprise Linux 5 安装镜像

(2) Oracle Database 10g for Linux32 安装程序

(3)VMware Workstation 7.0 安装程序

 

1. 安装虚拟机

安装VMware Workstation 7.0

然后我们要启动VMware,在虚拟机里安装一个操作系统

选择File-New-Virtual Machine 在弹出的窗口中选择Typical,然后Next>

 

然后如图所示进行配置,完毕后Next>

 

填写个人姓名,设置用户名和密码,注意这里设置的密码也是root密码

给虚拟机起名字并选择存放的位置

下一步根据需要选择分配给虚拟机的磁盘空间,

再下一步选择Customize Hardware

选择分配给虚拟机的内存空间,如果你的内存够大,建议这里设置的内存大一些,否则运行起来会很卡,

如果你的CPU是多核的,也可以进行如下配置

最后点击Finish结束配置并耐心等待安装

Tips:
按快捷键Ctrl+Alt可以使鼠标指针返回,按Ctrl+G或双击虚拟机界面可以控制虚拟机,按Ctrl+Alt+Enter可以进入全屏模式。

2. 安装VMware Tools

安装VMware Tools是为了以后能直接把Windows下的一些文本直接复制到虚拟机中里,这样可以很方便的把一些命令直接复制到Shell中执行。VMware 7.0会自动在你安装虚拟机的时候装上VMware Tools,如果没有的话可以执行以下步骤安装。

如何检查是否安装了VMware Tools
->
选择菜单栏的VM,看里面是否有Reinstall VMware Tools,如果有,则说明你已经安装了。

操作方法如下,

(1).以管理员身份登录图形界面,

在菜单栏选择VM,再选择Install VMware Tools

此时Red Hat会自动挂载VMware Tools的虚拟光驱,同时屏幕下方会有提示:照做即可,以下为详细步骤:

选择Applications-Accessories-Terminal以进入终端界面,

输入命令(输入命令的时候你可以通过按Tab键来自动补全命令)

cp /media/VMware\ Tools/VMwareTools-8.1.3-203739.tar.gz /tmp

/*将安装文件复制到/tmp目录下*/

cd /tmp

tar xfzv VMwareTools-8.1.3-203739.tar.gz

/*解包安装文件*/

cd vmware-tools-distrib

./vmware-install.pl

即可开始安装

期间如果系统询问一些确认信息或路径时,输入中括号中推荐的字符。

 

完成安装之后,你可以在虚拟机和Windows之间复制文本。

3. 配置开机启动命令行

选择Applications-Accessories-Terminal进入终端界面

输入命令

vi /etc/inittab

把红圈处的5改成3

然后输入 :wq

重新启动之后就进入命令行界面,在命令行界面输入startx可以进入图形界面。

 

4. 针对Oracle配置你的Linux

启动虚拟机,以root账户登录。建议登录之后输入startx进入图形界面,然后打开Terminal执行以下操作,这样可以直接把本教程里的命令复制到虚拟机中运行,以免除输入的麻烦。

(1) 验证系统要求

A)Oracle10.2
针对
RH
Linux
操作系统版本要求:

Red Hat Enterprise Linux AS/ES 3.0 (Update 4 or later) Red
Hat Linux 4.0

Oracle10.2 针对 RH Linux3 内核版本要求:

Red Hat Enterprise Linux 3.0

2.4.21-27.EL

Oracle10.2 针对 RH Linux4 内核版本要求:

Red Hat Enterprise Linux 4.0 and Asianux 2.0:

2.6.9-5.EL

检验操作系统版本:使用命令 cat /etc/issue

检验内核版本:使用命令 uname –r

 

实际安装时系统会提示

说明该示例的系统版本不能正常安装

所以需要作出如下修改

输入命令

dd 将里面的内容删除,然后按i,输入 redhat-4

Esc键,输入 :wq 保存并退出。

B)安装Oracle
Database 10g需要最小512MB的RAM,最小1GB的Swap空间, 软件需要2.5GB的可用磁盘空间,数据库另需1.2GB的可用磁盘空间,/tmp目录需要至少400MB可用空间。

用以下命令分别查看内存空间,Swap空间,磁盘空间。

grep MemTotal /proc/meminfo

grep SwapTotal /proc/meminfo

df –h

检查其他所需的软件包版本,以下为最低要求

  • gcc-2.96-124
  • make-3.79
  • binutils-2.11
  • openmotif-2.1.30-11
  • glibc-2.2.4-31

使用命令 rpm –q gcc make binutils openmotif glibc
查看

安装缺少的和版本过低的软件包

安装以后再检查一下:

(2)创建Oracle组和用户帐户

执行以下命令

cd /usr/sbin

groupadd oinstall

groupadd dba

useradd –m –g
oinstall –G dba oracle

id oracle

为帐号oracle设置密码

passwd oracle

(3)创建存储Oracle软件和数据库文件的目录

执行以下命令创建目录并更改权限:

mkdir -p /u01/app/oracle

mkdir -p /u02/oradata

chown -R oracle:oinstall
/u01/app/oracle /u02/oradata

chmod -R 775 /u01/app/oracle
/u02/oradata

(4)配置Linux内核参数

Oracle 数据库 10g 需要以下所示的内核参数设置。其中给出的是最小值,因此如果您的系统使用的值较大,则不要更改它。

kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000

运行以下命令来检验你的参数

/sbin/sysctl -a | grep shm

/sbin/sysctl -a | grep sem

 /sbin/sysctl -a | grep file-max

 /sbin/sysctl -a | grep ip_local_port_range

如果你的参数小于以上的最小值,需要使用vi编辑/etc/sysctl.conf文件

 

修改完成后,使用以下命令激活更改

/sbin/sysctl –p

(5)oracle用户设置Shell限制

执行以下命令

cat >>
/etc/security/limits.conf <<EOF

oracle soft nproc 2047

oracle hard nproc 16384

oracle soft nofile 1024

oracle hard nofile 65536

EOF

 

cat >>
/etc/pam.d/login <<EOF

session required
/lib/security/pam_limits.so

EOF

使用命令

vi /etc/profile

i键,在done之前添加以下内容

if [ \$USER = “oracle” ]; then

if [ \$SHELL = “/bin/ksh” ]; then

ulimit -p 16384

ulimit -n 65536

else

ulimit -u 16384 -n 65536

fi

umask 022

fi

然后按Esc键,输入:wq保存并退出

(6)设置环境变量

先用命令 su oracle 切换为oracle用户

提示符变为

输入如图命令

添加

ORACLE_BASE=/u01/app/oracle

export ORACLE_BASE

ORACLE_SID=demo1
(此处是定义数据库名称,通常不超过5个字符,本示例使用demo1作为名称)

export
ORACLE_SID
到文件尾

5. 安装Oracle Database 10g

Windows下共享装有Oracle安装程序的文件夹

重新启动,以oracle帐号登录

输入startx进入图形界面

打开终端,输入命令 mkdir ~/10g_db 建立的目录

选择Places-Network Services-Windows Network-Workgroup-【你的电脑】-【你共享的文件夹】

从那里把安装程序复制到10g_db目录

直接使用图形界面操作解压安装包

打开终端界面,输入命令

cd ~/10g_db

./runInstaller

选择

如果前面的操作正确,后面的路径都使用默认值即可

安装类型选择

接受默认配置

接受默认配置

 

 

为全局数据库命名,字符集选择Unicode,勾选

下一步接受默认项

再下一步按下图配置

下一步接受默认配置

下一步建议配置统一的密码(如图)

然后选择进行安装

下面你将遇到一个弹出窗口,需要以root的身份执行一个shell script

打开一个新的Terminal,输入如下命令

su

【输入root密码】

cd /u01/app/oracle/oraInventory

./orainstRoot.sh

操作结束之后点击 OK,安装结束

点击Exit退出即可。

 

至此在Linux虚拟机上的Oracle
Database 10g安装结束,欢迎走进Oracle
Database 10g的精彩世界!

作者:纪晓文

Database Design 翻译

国内的图书往往通过各种范式、规则来讲解数据库设计。Stéphane Faroult先生则是通过一个关于电影的例子,生动形象的说明了数据库设计过程中遇到的问题和解决方法。视频分为上下两集,时间大约11分钟。相信看完这段视频和文字之后,会对你有所启发。

Hello; my name is Stéphane Faroult. I’m a French database consultant and a database book author.
大家好,我是StéphaneFaroult,是一名法国的数据库顾问,同时还是《SQL语言艺术》这本书的作者。
I’d like to present a brief introduction to database design by showing you on a small example some issues you can encounter and some ways to solve them.
我希望通过视频中的一个小例子,及在这个例子中遇到的问题和解决方法,来向大家简要的介绍下数据库设计。
For my example, I’d like to create a website that provides information about movies.
这个例子是:建立一个提供电影信息的网站
You are probably aware that in a database, data is stored in tables.
你可能已经知道,在数据库中数据是存储于表中的。
We’ll obviously have some kind of MOVIES table inside our database.
显然我们的数据库中会有各类的MOVIES表。
Tables in a database are very different from spreadsheets, but let’s start with a spreadsheet that contains information about a small number of classic movies: title, country, year, director, and leading actors.
数据库中的表和电子表格是很不同的,但是不妨让我们先创建一些电子表格,表格中包含一部分经典电影的信息,如影片名,国家,上映年份,导演,主演。
Initially we shall model our main table after the spreadsheet.
最初,我们的主表需要模仿电子表格。
Each row brings together a number of pieces of information that are the characteristics of a movie.
每一行整合了很多关于电影属性的信息。
The order of columns doesn’t matter.
列的顺序无关紧要。
The order of rows doesn’t matter.
行的顺序也不重要。
What matters is that the different pieces of information on one row are related, and the titles of the columns define the relation. This is why we talk about relational databases.
重要的是,一行中不同的信息是相关的,列的标题就是在定义它们之间的关系。这就是我们要讨论关系型数据库的原因。
It makes no sense to have the same row several times. An important step, when designing a database, consists in identifying the characteristics that allow us to distinguish one row from another; and very often these characteristics are a subset of all the characteristics. They are collectively called “the key”. What is the smallest subset of characteristics that uniquely identifies a movie?
拥有相同行是毫无意义的。设计数据库时很重要的一步,正是标识出那些能够帮助我们区分不同行的属性,通常这些属性是所有属性的一个子集。它们被统称为“键”。而什么是唯一标识一部电影属性的最小子集呢?
The title? No, the title isn’t enough because you have remakes.
电影名称?不,只有名称是不够的,因为一部电影可以重拍多次。
For instance, there are several movies based on the “Treasure Island” novel.
比如说,有很多部电影都是改编自《金银岛》(”Threasure Island”)这部小说。
Title and director perhaps?
电影名称和导演?
It doesn’t work either, because Alfred Hitchcock for instance made two “The man who knew too much”, one in 1934 and the other one in 1956.
这同样不管用,比如阿尔弗莱德?希区柯克(Alfred Hithchcock)导演了两部《擒凶记》(“The man who knew  too much”),一部在1934年,另一部在1956年。
Title, director and year may seem a better choice – except that “director” isn’t as simple a characteristic as it may look;
电影名称,导演,上映时间似乎是一个好一点的选择了,除非“导演”这一项比较非典型。
Some movies have several directors, and it may lead to ambiguities.
比如一些电影有好几位导演,这就可能会导致表达不明确。
Title, country and year look reasonable. It is very unlikely, not least for commercial reasons, that two movies with exactly the same title will be produced the same year in the same country. It is sometimes possible to find several groups of characteristics that uniquely identify a row in table; you are free to choose the group that you find the most convenient as the main identifier, and this group of characteristics is called the “primary key”.
电影名称,国家、上映看起来比较合理。至少从商业理由而言,两个完全同名的电影在同一国家,同一年发行,是非常不可能的。有时,可能找到一些属性组去唯一标识表中的一行;你可以自由的选择你认为最方便的组作为主要标识符,这一组属性被称为“主键”。
The ultimate goal of all “data bases” is to be queried. That means that people will search the table, using some interface, to retrieve movies that satisfy some particular conditions.
数据库的最终目标是用于查询。这意味着人们将通过一些界面来搜索表,检索出符合特定条件的电影。
Behind the scene, the program will issue an SQL query in which you name the characteristics you want
后台进程中,程序将根据你键入的属性发出一个SQL查询
Followed by the name of the table
接着是表名,
Followed by filtering conditions
再接下来是设置过滤条件。
And here we have several problems. If we want to retrieve all the movies that were directed by one given director, it is essential that the condition exactly matches the contents of the table – that is that if the row has for “Director” attribute “Hitchcock, A.”, the filtering condition is exactly entered as “Hitchcock, A.”, not as r “Hitchcock”), and definitely not as “Alfred Hitchcock”.
在这里我们有一些问题,当我们想要检索出同一位导演所执导的所有影片,就必须要准确输入与表中内容一致的条件,就像是如果这一行中“导演”的属性是“Hitchcock,A”,那么筛选的信息必须按照“Hitchcock,A”这样输入,而不可以是“Hitchcook”,更不能是“Alfred Hitchcock”(这几种是阿尔弗莱德?希区柯克英文名的不同书写形式)。
There is also a consistency issue with data that will probably be entered by different people at different times and must respect the same conventions: if you say that “North by Northwest” and “Rear Window” are “Hitchcock, A.” movieswhile “Strangers on a Train” is an “Alfred Hitchcock” movie, users who query the database will have much trouble getting the full list of Hitchcock movies.
不同时间向数据库中键入数据的人也要遵守数据一致性的约定:如果你说《西北偏北》(“North by Northwest”),《后窗》(“ Rear Window”)是导演Hitchcock, A.(阿尔弗雷德?希区柯克)的电影作品,而《火车怪客》(“ Strangers on a Train”)是导演Alfred Hitchcock(阿尔弗雷德?希区柯克英文名字的另一种格式)的另一部电影。这样用户在查询导演阿尔弗雷德?希区柯克电影作品的详细列表时就会遇到麻烦。
As a general rule, character fields that are free-form text call for trouble when they are used for searches. This is true for directors, and this is true as well for the leading stars. We must therefore define some kind of standard for storing names; storing firstname and surname separately, in uppercase perhaps. Standardization is a first step in the right direction. But we still have a few numbers of problems to solve. The first one is typos. A user who mistypes a name may realize the mistake, check the spelling, and try again. But if there are some misspellings in the database, information that is misspelt will only be found by users doing the same mistake when they query; not a desirable situation.
一般来说,没有规定格式的文本在查询时会引起麻烦。对于导演一栏是这样,对于主演一栏也是如此。因此我们必须定义一些用于存储名字信息的标准,例如姓氏与名字分开存储,或者使用大写。标准化是向正确方向迈进的第一步。但我们仍然有一些问题需要解决。第一个就是拼写错误。用户查询信息,输错一个名字时可能会意识到这个错误,检查拼写,并且再试一次。但是当数据库中存储的信息本身存在一些拼写错误时,这些拼错的信息只能在用户查询并出现同样的错误时才能被查询出来,这很杯具。
Let’s ditch this model
让我们抛弃这种数据模型
And let’s try a new one. To ensure that names are correctly entered, the best solution is to enter them only once (and if they are misspelt, we’ll have only one place to correct). This can be performed by a very convenient feature of the SQL language, which is the ability to perform joins.
我们来试试另一个。确保名字准确输入的最好方法就是只输入一次(当它们被输错了,我们将只有一个地方需要去修改)。这可以通过SQL语言中一个十分便捷的方法来完成——执行连接(join)。
We have been dealing so far with a single table. But we may have several tables, with some common columns, and SQL allows to associate data from one table to data from another table by using the common columns to establish the link – this operation is called a join.
迄今为止我们一直在处理单个表。但是我们可能有一些表,这些表中有部分列相同,SQL中允许数据通过共同的列从一个表到另一个关联表建立连接,这种操作就称为连接(join)。
Let’s see how it works. Suppose that we create a table of directors. In this table, we shall store first name, surname and perhaps some biographical information.
我们来看看它是如何工作的。假设我们创建了一个关于导演的表。在这个表中,我们将存储导演的姓氏、名字,并且或许还可以加上其他一些个人信息。
The key will be firstname and surname; however, since we want to link movies and directors and since we want to avoid misspellings, we are going to use a system-generated numerical identifier that will be a synonym for the key and that we shall call ID. This will be, technically speaking, a second key and we can make ID the primary key of the DIRECTORS table while asking the DBMS to ensure that firstname and surname remain unique across the table.
键是姓氏和名字,然而,为避免在连接MOVIES表和DIRECTORS表时出现拼写错误,我们用系统生成的数字标识符代替姓名键,我们称它为ID。ID也是表的键,我们可以把它设为DIRECTORS表的主键同时可以利用数据库管理系统确保表中的名字和姓氏是唯一存在的。
Inside the MOVIES table we shall replace the identification of the director by the id; of course, we’ll do something similar for actors. When a user wants to retrieve the titles and year of the movies directed by someone whose only surname has been provided, the system will issue an SQL query that will look like this one.
在MOVIES表里我们将用ID替换导演列的数据,当然,我们也将对演员作相应的更改。当用户想要检索由一位只提供了姓的导演所执导的电影的片名及年份,系统将发出一个SQL查询。如演示。
However, we have a problem with the infrequent movies that have more than one director.
然而,我们又遇到一个问题,个别电影会有多个导演
Should we reserve two columns for storing director ids? That would mean joining on two columns, which can be done but isn’t most convenient.
我们应该为存储导演ID号保留两列吗?这意味着在两个表建立连接,这种方法可行但却不便捷。
But two isn’t enough. Some movies have three directors.
而且有时两列并不够。一些电影有三个导演。
Some even have five.
有些电影甚至有五个导演。
We have the same problem, even more acute, with actors. How many actors should we record? The three leading actors?
在记录演员方面,我们有相同甚至更加严重的问题。我们应该记录多少演员?三个主演?
What about a star-studded movie such as “The Longest Day”?
如果是像《最长的一日》(The Longest Day)这样众星云集的电影呢?
What if one day we decide to improve the database and record more actors, and perhaps the full cast? If we want to add more actor identifiers to the table we shall have to review all the existing queries to add “OR” conditions.
如果有一天,我们决定改进数据库并记录更多的演员,甚至是记录全部演员,应该怎么办?如果我们向表中添加更多的演员标识符,我们就必须回顾现存的所有查询并加入“或”(OR)条件。
Storing director and actor identifiers inside the movies table will make the evolution of the application very painful.
将导演及演员标识符存储在MOVIE表中会使更改应用变得十分困难。
This isn’t therefore a good design
因此,这并不是一个好的设计。
And we must try harder.
并且我们需要拥有一个更好的设计。
Instead of having one column per director or actor in the movies table, a DBMS allows us to have an intermediate table that, through two joins, will allow us to relate one director to several movies, or several directors to one movie if need be. We shall call this table directed_by and it implements a link between the table of movies and the table of directors.
与director和actor各占一列这种模式不同,数据库管理系统允许我们有一个中间表,通过两个JOIN,我们可以将一个导演和多个影片或者将多个导演和一个影片相关联起来。我们称这个表为directed_by,它实现了MOVIES表和DIRECTORS表的连接。
The director and the leading actors will no longer be an attribute of the movie.
而导演以及主演将不再是MOVIES表中的属性。
Since we must reference movies in another table, the real life key may be a bit difficult to work with. And in the same way as for directors and actors, we are going to ask the system to generate for us a numerical identifier movie id that will be used as a shorter alias for the title, year and country and that we’ll use to refer to one particular movie.
因为我们必须在其他表中使用MOVIES表中内容,用现实中属性做键可能有些难处理。DIRECTORS表和ACTORS表也是相同的道理,这时我们就要利用系统自动生成的数字标识符作为列title,year,country的别名,以此来查询一个特定的电影
We have the movies, we have the directors, and a new table called directed_by will record the fact that one movie was directed by one director.  The unique combination of a director id and a movie id is the key of the new table.
我们有MOVIES表,DIRECTORS表,还有一个新表directed_by,用来记录电影与导演的对应关系。一个director id 和movie id 的特定组合就是这个新表中的键。
The SQL query to retrieve the information will be hardly more complicated.
这样检索信息的SQL查询就不会那么复杂了
In the same way, we can also have a table called PLAYED_IN that links the table that records actors to the movies table.
用相同的方法,我们也可以创建一个新表PLAYED_IN,用来将关联表Actors与表Movies
Now, we still have a problem. Orson Welles directed and played in Citizen Kane, and actors turned directors, or directors who occasionally or often play in their own movies are rather common.
现在,我们仍旧有一个问题。好比奥逊?威尔斯执导并出演了《公民凯恩》,一些演员同时又是导演,一些导演也会时不时的出现在他们自己的影片中,这种现象非常普遍。
If we store separately directors and actors, some information will be duplicated, and if we update one table and forget the other one we shall have inconsistencies.
如果我们分开存放directors 和actors信息,一些信息就将出现重复,而如果我们更新其中一个表而不更新另一个,就会导致信息不一致。
What we really want is to merge the two tables and keep all the information about one person at a single location.
我们真正需要的是合并两个表并确保某一个人的信息来源于同一数据表。
Let’s ditch once again this model, and try a last one.
让我们把这种模型也抛弃,继续尝试最后一种模型。
We could still keep played_in and directed_by, and make them link the MOVIES table to a PEOPLE table.
我们仍然可以保留中间表playes_in 和directed_by ,并且通过它们将表MOVIES和PEOPLES相关联。
Another solution is to merge played_in and directed_by into a MOVIE_CREDITS table that would hold a movie id, a person id, and another attribute to tell in which capacity a person participated to the movie: actor, or director. This last design is probably better if we plan to also record one day who composed the music, who was the photography director, who designed the set and so on … which would otherwise require one additional table per type of involvement.
另一种方法是把表played_in和directed_by合并为表MOVIE_CREDIT,表中包含列movie id ,person_id 还有一些另外的属性来说明某个人是以何种身份参与到电影中:导演(D)或者演员(A)。最后一个设计或许会更好一些如果我们计划也将有一天的记录是谁谱的曲子,谁是摄影导演,谁设计的舞台等等,否则以上每种分类将会需要一个额外的表。
A good design guarantees data integrity and allows evolution.
一个好的设计既能保证信息的完整性又允许对数据库的进行升级改善。
This is a very simple example, but I hope that it will be enough to convince you of the importance of database design.
这是一个很简单的例子,但是我希望它足以使你了解数据库设计的重要意义。
Thank you for your attention.
感谢观看。

作者:Stéphane Faroult
翻译:王瑞莹,郑冉
校对:Kamus(ACOUG),仇实

SQL Joins 翻译

Stéphane Faroult先生在SQL Joins里简单介绍了SQL各种Join的内涵,运用了一个形象的舞池比喻。

以下分别是中文字幕视频和翻译文档的中英文对照。

Hello. I’m Stéphane Faroult, a French database consultant, and I’ve designed this short video note about joins.

大家好,我是Stéphane Faroult,一个法国的数据库顾问,我设计了这个关于联接(join)的简短的视频笔记(希望能帮助你了解join的工作原理)。

You probably know what a join is: in a relational database, when two tables have one or several common columns, you can link them by saying that a row in one table matches a row from the other table that has the same values in the common columns.

也许你已经知道联接(join)是什么:

在关系型数据库中,如果两个表有一个或多个公共列,我们可以连接两表中对应列里列值相同的行

For instance, you can store in your database the registered members of a web site; they are identified by their screen name, and a numerical id is used as a convenient reference. Your table probably contains more information, such as the date when members joined, and perhaps a number of points acquired by providing useful answers in a forum.

例如,您可以在您的数据库中储存一个网站的注册会员,他们由网名标识,并且用一个数字的id方便的作为参考。你的表中也许包含着更多的信息,例如会员的注册日期,以及在论坛中回帖所得的点数。

Another table will contain the messages posted by members on the forum. I have just shown here the member id and the subject of the post, you would obviously also have a date, the message body and possibly some other attributes.

另一个表将会包含论坛会员发帖的信息。我在这只显示了会员ID和帖子主题,在真实生活中,这个表中或许还应包含发帖时间,帖子内容和帖子的一些其他属性。

If we want to list the messages, most recent first, we also want to display who posted the message, as well as the member’s points. We will therefore write a query such as this one, in which the ‘memberid’ column in the messages table refers to the ‘memberid’ column in the members table and is used to perform the join.

如果我们想列出这些信息,最新的优先,同时也想要显示发帖人,以及他的点数。我们需要写一个像这样的查询,其中messages表的memberid列参考members表中的memberid列,用来完成联接。

When you are taught SQL, you are often explained that the operation consists in generating all possible combinations between the rows from the members table and the rows from the messages table, to only retain the combinations for which the common column contains the same value.

当你学习SQL时,你学习到有一个操作在于生成在members表和messages表的行和行之间所有可能的组合,只保留包含相同值的共同的列的组合。

This operation is called a cartesian join, after René Descartes, one of the major European philosophers and mathematicians of the 17th century. Although a database system sometimes executes cartesian joins, the most common ways to perform a join are nested loops, and hash or merge joins.

这个操作就叫做笛卡尔联接,是根据17世纪欧洲主要哲学家和数学家勒内·笛卡尔命名的。尽管数据库系统有时执行笛卡尔联接,但是执行联接的最常用方法是嵌套循环联接(nested loops)和哈希(hash)联接或合并(merge)联接。

To explain the difference between those two categories of joins, Tom Kyte, who is senior technologist with Oracle, once used an excellent analogy on his popular “Ask Tom” site, and I’ve tried to illustrate Tom’s  dance floor analogy.

为了解释一下那两种联接的区别, Oracle的高级技术人员Tom Kyte,曾经在他的Ask Tom网站上使用了一个杰出的类比,下面我将尝试阐述Tom的舞池类比。

Here is how it goes. Suppose that you have a few guys, and a few girls. Each guy is looking for a partner of about the same size as himself. The first guy moves forward, and is strongly attracted by a girl who looks like a model. Unfortunately, models are tall, and our guy is rather short. A second girl quite lovely, but a tad too tall for him. Finally,he finds a suitable partner who has both the right size and a nice smile, and invites her. If each guy repeats the same process, we have in effect the same algorithm as with a nested loop.

这是它的内容。假设有几个男士和几个女士,每个男士在寻找一个和他身高差不多的舞伴。第一个男士前进一步,并且被一个看起来像模特的女士强烈的吸引住了,不幸的是,模特很高,而那个男士相当的矮;第二个女孩很可爱,但是还是比他高一点;最后,他找到了一个合适的舞伴,她有着和他相仿的身高和甜美的微笑,男士邀请了她。如果每个男士重复这个同样的步骤,我们就有与嵌套循环联接(nested loop)实质相同的算法。

Now suppose that we have a lot of girls, and a lot of guys. If we take the same Goldilock approach to flirting as before, it is likely that dawn will be there before all couples are formed. Therefore, we are going to trade romanticism against efficiency, and ask all girls and all boys to get in rank by order of size, so that we can easily match them. This would be an overkill with very few individuals. But with a large number of people, this is probably the best way to proceed, and it is exactly the way a merge join works.

现在假定有很多的女士和很多的男士。如果我们还像之前那样接近并与一个金发女郎调情,很可能要等到黎明时才能使所有的人都找到舞伴。因此,我们将放弃浪漫而追求效率,让所有的女士和所有的男士都按身高排队,然后他们可以简单的进行配对。如果人数少的话,用这种方法就像是“用大炮打蚊子”,小题大做了。但是如果有一大堆人的话,这可能就是最好的方法了,并且它恰好和合并联接(merge joins)是一样的。

In the dry world of databases, to perform a nested loop an SQL engine will scan the smallest of the two tables, then find in turn the matching rows in the other table.

在数据库的世界里,要执行一个嵌套循环联接(nested loop),SQL引擎将扫描两个表中最小的那个表,然后依次匹配另一个表中的行。

It is very important that the column used for the join is indexed in the bigger table, because otherwise you would have to scan it each time you consider a new row in the smaller table.

如果在一个很大的表中,给联接要用到的列建立索引是很有必要的,否则每次根据一个小表中的行来寻找大表中相同的行,都要对大表进行完全扫描。

We have seen how merge joins work. Hash joins are a variant that is a good compromise in terms of CPU and memory usage. In a hash join , a temporary hash table is created. A hash table is a kind of array that contains pointers to the rows of a table. A special function, called the hash function,  applied to the key associates each key to a position in the array.

我们已经看过合并联接(merge joins)是怎样运作的了。哈希联接(hash joins)是一个变体,是CPU和内存使用的方面很好的折中。在哈希联接里,一个临时的哈希表被建立。哈希表是一个数组,包含指向表中行的指针。一个特别的函数,称作哈希函数,应用于表中的每个键(主键),在数组中每个键占一个位置。

The smallest table is scanned first, and the hash table is populated with pointers to the rows that match each key.

最小的表首先被扫描,然后哈希表被填入相匹配的每个键的行指针。

Then the second table is scanned, only once, the function directs us to the proper slot in the hash table and from there you find the pointers to the matching rows.

然后,第二个表被扫描,只扫描一次,那个函数引导我们到哈希表中适合的位置,并且从那里,你找到匹配的行指针。

Contrarily to the nested loop join, indexes are no longer so important. An SQL optimizer may choose to scan an index rather than a table because indexes are usually smaller than tables, but this is a marginal improvement.

与嵌套循环联接相反,索引不再是非常重要的了。一个SQL优化器会选择扫描一个索引,而不是一个表,因为索引通常比表小,但是这只是一个微小的改进。

When the query isn’t madly complicated, when data statistics allow to get a fair idea about the expected size of the result set and if existing indexes allow a choice, the query optimizer can usually decide which type of join will be fastest.  If the query is really complex, the way you write it often sends signals to the optimizer.

当查询并不是很复杂,数据统计信息对预计的结果集大小有正确的认识,并且存在的索引允许选择,那么查询优化器通常可以决定哪种类型的联接将会更快。如果查询语句真的特别复杂,那么你构造语句的方法通常会影响优化器的选择。

 But that, as Kipling used to say, is another story. Thank you for listening.

不过,正如英国作家吉卜林说(译者注:英国作家,1865-1936,1907年获诺贝尔文学奖),是另一个故事。感谢您们的聆听。

作者:Stéphane Faroult
翻译:纪晓文,苗洁明
校对:Kamus(ACOUG),仇实

普人特福的博客cnzz&51la for wordpress,cnzz for wordpress,51la for wordpress