Oracle数据库原理

1、1. 数据库原理1.1. 数据库简介1.1.1. 文件存储对数据的存储需求一直存在。保存数据的方式，经历了手工管理、文件管理等阶段，直至数据库管理阶段。文件存储方式保存数据的弊端：缺乏对数据的整体管理,数据不便修改；不利于数据分析和共享;数据量急剧增长,大量数据不可能长期保存在文件中。数据库应运而生，是人们存放数据、访问数据、操作数据的存储仓库。1.1.2. 数据库简介主流数据库产品：Oracle（Oracle）DB2（IBM）SQL Server（MS）MySQL（Oracle）1.1.3. 表的概念在数据库中，数据被存放于二维数据表(Table)中。一个数据库由多个数据表组成，数据表是数据库的基本存储结构，由行和列组成，行（Row）也就是横排数据，也经常被称作记录(Record)，列（Column）就是纵列数据，也被称作字段(Field)。表和表之间是存在关联关系的。1.2. 主流数据库1.2.1. Oracle数据库概述Oracle是当今著名的Oracle(甲骨文)公司的数据库产品，它是世界上第一个商品化的关系型数据库管理系统，也是第一个推出和数据库结合的第四代语言开发工具的数据库产品。Oracle采用标准的SQL结构化查询语言，支持多种数据类型，提供面向对象的数据支持，具有第四代语言开发工具，支持UNIX、WINDOWS、OS/2等多种平台。Oracle公司的软件产品丰富，包括Oracle服务器产品，Oracle开发工具和Oracle应用软件。其中最著名的就是Oracle数据库，目前最新的版本是Oracle 12c。1.2.2. DB2数据库概述DB2是IBM的关系型数据库管理系统，DB2有很多不同的版本，可以运行在从掌上产品到大型机不同的终端机器上。DB2 Universal Database Personal Edition和DB2 Universal Database Workgroup Edition分别是为OS/2和Windows系统的单用户和多用户提供的数据库管理系统。DB2在高端数据库的主要竞争对手是Oracle。1.2.3. Sybase数据库Sybase是美国Sybase公司研制的一种关系型数据库系统，是较早采用C/S技术的数据库厂商，是一种典型的UNIX或Windows NT平台上客户机/服务器环境下的大型数据库系统。 Sybase通常与Sybase SQL Anywhere用于客户机/服务器环境，前者作为服务器数据库，后者为客户机数据库，采用该公司研制的PowerBuilder为开发工具，在国内大中型系统中具有广泛的应用。SYBASE主要有三种版本，一是UNIX操作系统下运行的版本，二是Novell Netware环境下运行的版本，三是Windows NT环境下运行的版本。对UNIX操作系统目前广泛应用的为SYBASE 10 及SYABSE 11 for SCO UNIX。2010年Sybase被SAP收购。1.2.4. SQL Server数据库概述Microsoft SQL Server是运行在Windows NT服务器上，支持C/S结构的数据库管理系统。它采用标准SQL语言，微软公司对它进行了部分扩充而成为事务SQL（Transact-SQL）。SQL Server最早是微软为了要和IBM竞争时，与Sybase合作所产生的，其最早的发展者是Sybase，和Sybase数据库完全兼容。在与Sybase终止合作关系后，微软自主开发出SQL Server 6.0版，往后的SQL Server即均由微软自行研发。最新的版本是SQL Server 2012，上一版本是2008。Microsoft SQL Server几个初始版本适用于中小企业的数据库管理，但是后来它的应用范围有所扩展，已经触及到大型、跨国企业的数据库管理。1.2.5. MySQL数据库概述MySQL是一个开放源码的小型关系型数据库管理系统，开发者为瑞典MySQL AB公司。目前MySQL被广泛地应用在Internet上的中小型网站中。与其它的大型数据库例如Oracle、IBM DB2等相比，MySQL自有它的不足之处，如规模小、功能有限等，但对于一般个人使用者和中小型企业来说，MySQL提供的功能已经绰绰有余，而且由于MySQL是开放源码软件，因此可以大大降低总体拥有成本，许多中小型网站为了降低网站总体拥有成本而选择了MySQL作为网站数据库。2008年1月16日，Sun正式收购MySQL。2009年4月20日，SUN被Oracle公司收购。目前的最新版本是MySQL5.6.1.3 Oracle数据库安装及访问1.3.1. 使用SQL*PLUS访问数据库首先，登录到数据库所在的机器上，输入机器操作系统的帐号和密码：然后在%提示符下，输入SQLPlus命令，并输入数据库的帐号和密码：如果出现SQL提示符，则表示登录成功：退出时，在SQL>后输入exit即可：再输入exit，即退出远程服务器。1.3.3. 使用Oracle SQL Developer 访问据库第一次进入SQL Developer，需要设置连接参数。首先在窗口左边的Connection上按右键，开启一个新的连接，输入远程数据库的参数1.3.4.表空间查看表空间：select * from v$tablespace;查看所有表单:　　select * from user_tables;创建表空间：查看所有表空间信息：select * from dba_data_files;删除表空间（包括表空间内内容以及数据空间）：drop tablespace tablespace_name including contents and datafiles;创建用户:create user用户名identified byuser_passworddefault tablespacetablespace_name;给用户授权：grant connect,resource to用户名;oracle为了兼容以前的版本，提供了三种标准的角色(role)：connect、resource和dba。1. connect role(连接角色)临时用户，特别是那些不需要建表的用户，通常只赋予他们connectrole。connect是使用oracle的简单权限，这种权限只有在对其他用户的表有访问权时，包括select、insert、update和delete等，才会变得有意义。拥有connect role的用户还能够创建表、视图、序列(sequence)、簇(cluster)、同义词(synonym )、会话(session)和与其他数据库的链(link)。2. resource role(资源角色)更可靠和正式的数据库用户可以授予resource role。resource提供给用户另外的权限以创建他们自己的表、序列、过程(procedure)、触发器(trigger)、索引(index)和簇(cluster)。3. dba role(数据库管理员角色)dba role拥有所有的系统权限----包括无限制的空间限额和给其他用户授予各种权限的能力。system由dba用户拥有。下面介绍一些dba经常使用的典型权限。回收用户权限：revoke connect,resource from用户名;删除用户:drop user 用户名;设置表空间:alter user 用户名 default tablespace tablespace_name;1.4. SQL概述1.4.1. 结构化查询语言SQL(Structured Query Language) 是结构化查询语言的缩写。SQL是在数据库上执行数据操作、检索及维护所使用的标准语言,可以用来查询数据，操纵数据，定义数据，控制数据，所有数据库都使用相同或者相似的语言。SQL可分为:数据定义语言（DDL） : Data Definition Language数据操纵语言（DML） : Data Manipulation Language事务控制语言（TCL）：Transaction Control Language)数据查询语言（DQL）：Data Query Language数据控制语言（DCL） : Data Control Language执行SQL语句时，用户只需要知道其逻辑含义，而不需要知道SQL语句的具体执行步骤。1.4.2. 数据定义语言（DDL）用于建立、修改、删除数据库对象，包括创建语句(CREATE)、修改语句(ALTER)、删除语句(DROP)。这类语言不需要事务的参与，自动提交。1.4.3. 数据操作语言（DML）用于改变数据库数据，包括INSERT、UPDATE、DELETE三条语句。其中，INSERT语句用于将数据插入到数据库中，UPDATE语句用于更新数据库中已存在的数据，DELETE用于删除数据库中已存在的数据。DML语言和事务是相关的，执行完DML操作后必须经过事务控制语句提交后才真正的将改变应用到数据库中。1.4.4. 事务控制语言（TCL）用来维护数据一致性的语句，包括提交(COMMIT)、回滚(ROLLBACK)、保存点(SAVEPOINT)三条语句，其中COMMIT用来确认已经进行的数据库改变， ROLLBACK语句用来取消已经进行的数据库改变，当执行DML操作后(也就是上面说的增加、修改、删除等动作)，可以使用COMMIT语句来确认这种改变，或者使用ROLLBACK取消这种改变。SAVEPOINT语句用来设置保存点，使当前的事务可以回退到指定的保存点，便于取消部分改变。1.4.5. 数据查询语言（DQL）用来查询所需要的数据。使用最广泛，语法灵活复杂。1.4.6. 数据控制语言（DCL）用于执行权限的授予和收回操作、创建用户等，包括授予权限(GRANT)语句，收回(REVOKE)语句，CREATE USER语句。DCL语句也不需要事务的参与，是自动提交的。2.1. Oracle数据类型2.1.1. NUMBERNUMBER表示数字类型，经常被定义成NUMBER（P，S）形式，其中：P表示数字的总位数S表示小数点后面的位数例如在表Emp中的Sal列的定义如下：1)Sal NUMBER(6,2)表示Sal列中的数据，整数位最大为4位，小数位最大位数是2位，也就是最大取值：9999.99。2.1.2. CHARCHAR表示固定长度的字符类型，经常被定义成CHAR（N）形式, N表示占用的字节数，N的最大取值是2000。例如在表Emp中的Ename列的定义如下：1)Ename CHAR(20)；表示Ename列中最多可存储20个字节的字符串，并且占用的空间是固定的20个字节。2.1.3. VARCHAR2VARCHAR2表示变长的字符类型，定义格式是VARCHAR2（N）, N表示最多可占用的字节数，最大长度是4000字节。例如在表Emp中的JOB列的定义如下：1)JOB VARCHAR2(100)；表示JOB列中最多可存储长度为100个字节的字符串。根据其中保存的数据长度，占用的空间是变化的，最大占用空间为100个字节。2.1.4. DATE和TIMESTAMPDATE用于定义日期时间的数据，长度是7个字节，默认格式是：DD-MON-RR, 例如：“11-APR-71”。如果是中文环境，是“11-4月-71”这种形式。TIMESTAMP这也是ORACLE常用的日期类型，它与date的区别是不仅可以保存日期和时间，还能保存小数秒，小数位数可以指定为0-9，默认为6位，所以最高精度可以到ns(纳秒)例如在表Emp中的Hiredate列的定义如下:1)Hiredate DATE；表示Hiredate列中存放的是日期数据。2.1.5. LONG和CLOB类型LONG类型可以认为是VARCHAR2的加长版，用来存储变长字符串，最多达2GB的字符串数据，但是LONG类型有诸多限制，所以不建议使用：每个表只能有一个LONG类型列；不能作为主键；不能建立索引；不能出现在查询条件中等CLOB用来存储定长或变长字符串，最多达4GB的字符串数据，ORACLE建议开发中使用CLOB替代LONG类型2.2. 创建表2.2.1. CREATE语句创建表的语法是：CREATE TABLE table_name( column_name datatype[DEFAULT expr][,…]);创建职员表的例子：--创建表empCREATE TABLE emp( id NUMBER(10), name VARCHAR2(20), gender CHAR(1), birth DATE, salary NUMBER(6,2), job VARCHAR2(30), deptid NUMBER(2) );2.2.2. DESC语句DESC是查看表的数据结构，语法是:1)DESC table_name;查看emp表的结构，:2.2.3. DEFAULT可以通过DEFAULT子句给列指定默认值，例如，在emp表中给gender列赋默认值‘M’,如果插入数据时没有指定性别的员工，则默认是男性。创建表emp1：CREATE TABLE emp1( id NUMBER(10), name VARCHAR2(20), gender CHAR(1) DEFAULT ‘M’, birth DATE, salary NUMBER(6,2), job VARCHAR2(30), deptid NUMBER(2) );2.2.4. NOT NULL默认情况下，任何列都允许有空值。非空(Not Null)是一种约束条件，用于确保字段值不为空，当某个字段被设置了非空约束条件，这个字段中必须存在有效值，也就是说，当执行插入数据的操作时，必须提供这个列的数据，当执行更新操作时，不能给这个列的值设置为NULL。创建表emp2，设置name字段为非空，也即是表emp2中的每条记录，name列都必须有值。CREATE TABLE emp2( id NUMBER(10), name VARCHAR2(20) NOT NULL, gender CHAR(1), birth DATE, salary NUMBER(6,2), job VARCHAR2(30), deptid NUMBER(2) );2.3. 修改表2.3.1. 修改表名在建表后如果希望修改表名，可以使用RENAME语句实现，语法如下，将改变表名old_name为new_name：1)RENAME old_name TO new_name;举例说明，如果要将表名emp修改为employee，使用如下语法:1)RENAME emp TO employee;2.3.2. 增加列在建表之后，要给表增加列可以使用ALTER TABLE的ADD子句实现。语法如下：1)ALTER TABLE table_name ADD(column datatype [DEFAULT expr] [, column datatype…])注意一点，列只能增加在最后，不能插入到现有的列中。下例给表employee增加一列hiredate，并设置默认值为当前日期:1)ALTER TABLE employee ADD (hiredate DATE DEFAULT sysdate);2.3.3. 删除列在建表之后，使用ALTER TABLE的DROP子句删除不需要的列。语法如下：1)ALTER TABLE table_name DROP (column);删除字段需要从每行中删掉该字段占据的长度和数据，并释放在数据块中占据的空间，如果表记录比较大，删除字段可能需要比较长的时间。下例删除表employee的列hiredate：1)ALTER TABLE employee DROP (hiredate);2.3.4. 修改列建表之后，可以改变表中列的数据类型、长度和默认值，注意这种修改仅对以后插入的数据有效，另外如果表中已经有数据的情况下，把长度由大改小，有可能不成功，比如原来的类型是VARCHAR2(100),其中已经存放了100个字节长度的数据，如果要改为VARCHAR2（80），则不会修改成功。语法如下：1)ALTER TABLE table_name MODIFY(column datatype [DEFAULT expr] [, column datatype…])下例修改表employee的列job，并增加默认值的设置:1)ALTER TABLE employeeMODIFY(job VARCHAR2(40) DEFAULT 'CLERK' );2.4. DML语句在做后续练习之前，先熟悉本部分使用的样例表emp和dept的结构和数据：DROP TABLE dept;CREATE TABLE dept (deptno NUMBER(2),dname VARCHAR2(14) ,loc VARCHAR2(13) ) ;DROP TABLE emp;CREATE TABLE emp (empno NUMBER(4),ename VARCHAR2(10),job VARCHAR2(15),mgr NUMBER(4),hiredate DATE,sal NUMBER(7,2),comm NUMBER(7,2),deptno NUMBER(2));INSERT INTO dept VALUES (10,'ACCOUNTING','NEW YORK');INSERT INTO dept VALUES (20,'RESEARCH','DALLAS');INSERT INTO dept VALUES (30,'SALES','CHICAGO');INSERT INTO dept VALUES (40,'OPERATIONS','BOSTON');SELECT * FROM dept;INSERT INTO emp VALUES(7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);INSERT INTO emp VALUES(7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);INSERT INTO emp VALUES(7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);INSERT INTO emp VALUES(7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);INSERT INTO emp VALUES(7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);INSERT INTO emp VALUES(7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);INSERT INTO emp VALUES(7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);INSERT INTO emp VALUES(7788,'SCOTT','ANALYST',7566,to_date('19-4-87','dd-mm-yyyy'),3000,NULL,20);INSERT INTO emp VALUES(7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);INSERT INTO emp VALUES(7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);INSERT INTO emp VALUES(7876,'ADAMS','CLERK',7788,to_date('23-5-87','dd-mm-yyyy'),1100,NULL,20);INSERT INTO emp VALUES(7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);INSERT INTO emp VALUES(7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);INSERT INTO emp VALUES(7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);2.4.1. INSERT语句INSERT语句用来给数据表增加记录，每次增加一条记录。语法如下：1) INSERT INTO table_name[(column[, column…])]VALUES(value[, value…]);需要注意的一点，所有的DML操作，都需要再执行事务提交语句commit，才算真正确认了此操作。向employee表插入一条记录的例子如下:1)INSERT INTO employee(id, name, job, salary)VALUES(1001, 'rose', 'PROGRAMMER', 5500);Oracle中的日期数据比较特殊，如果插入的列有日期字段，需要考虑日期的格式。Oracle的默认的日期格式‘DD-MON-RR，你或者按照默认格式插入数据，或者自定义日期格式，用TO_DATE函数转换为日期类型的数据。/*使用默认日期格式插入记录*/INSERT INTO employee(id, name, job,birth)VALUES(1002, 'martha', 'ANALYST', '01-9月-89'); /*使用自定义日期格式插入记录*/INSERT INTO employee(id, name, job, birth)VALUES(1003, 'donna', 'MANAGER', TO_DATE('1978-09-01', 'YYYY-MM-DD'));2.4.2. UPDATE语句UPDATE语句用来更新表中的记录，语法如下：UPDATE table_nameSET column = value [, column = value]…[WHERE condition];其中WHERE子句是可选的，但是如果没有WHERE子句，则全表的数据都会被更新，务必小心。下例更改职员ROSE的薪水为8500:1)UPDATE employee SET salary = 8500 WHERE name = 'ROSE';2.4.3. DELETE语句DELETE语句用来删除表中的记录，语法如下：1)DELETE [FROM] table_name [WHERE condition];和UPDATE语句一样，WHERE子句是可选的，但是如果没有WHERE子句，则全表的数据都会被删除，务必确认后再予以删除。下例将删除职位是空的员工记录:1)DELETE FROM employee WHERE job is null;