数据库培训-SQL基础

Oracle数据库基础山东众阳软件有限公司张录Oracle数据库概述SQL基础处理数据第三方工具简单使用Oracle数据库概述Oracle（甲骨文）公司是世界上最大的企业软件公司，主要向全球用户提供数据库、工具和应用软件以及相关的咨询、培训和支持服务。Oracle数据库（OracleDatabase）是甲骨文公司推出的关系型数据库产品，是世界上使用最为广泛的数据库管理系统，到目前仍旧在数据库市场上占有主要份额。关系型数据库:介绍关系型数据库：简而言之就是关系/二维表的集合。Oracle数据库表名:EMPLOYEES表名:DEPARTMENTS关系型数据库：表的术语2.主键3.列4.外键6.空值1.行5.域空值既不是空格也不是0多表关联表中每一行数据均由主键保证唯一性。多表通过外键关联表:EMPLOYEES表:DEPARTMENTS主键主键外键…Oracle的总体框架结构Oracle实例一个oracle实例由一系列的后台进程和内存结构组成当启动实例的时候，oracle会开辟一块内存（SGA）用于存放数据库信息，并启动后台进程，SGA内的信息是被后台进程所共享的。实例和数据库的关系实例是“内存”和“后台进程”的集合，“数据库”是数据的物理存储，要特别注意，一个实例可以用于一个数据库，多个实例也可同时用于一个数据库（RAC），实例和数据库是多对一的关系。Oracle的内存结构Oracle的内存由PGA（ProgramGlobalArea）和SGA（SystemGlobalArea）组成。PGA中包括服务器进程的数据和控制信息，其中的信息是不被共享的。SGA中包括数据库实例的数据和控制信息，是可以被共享的。SGA被划分成多个区域，主要的区域如下：数据库高速缓冲区（DatabaseBufferCache），用于存放数据文件的副本，所有的用户进程都可以并发的连接到这个实例，共享数据库高速缓冲区中的数据。共享池（SharedPool）又分为库缓冲区（LibraryCache）和字典缓冲区（DictionaryCache）。库缓冲区用于存放使用过的sql，包括这些sql语句的执行计划及这些sql的语法分析树。数据库对象的信息存储在数据字典表中，包括用户账号数据、数据文件名、段名、盘区位置、表的说明和权限等，当数据库需要这些信息时就会读取数据字典并将这些信息存储在字典缓冲区中。重做日志缓冲区（RedoLogBuffer）用于存放用户对数据库的改变，这是一片循环使用的内存区域。oracle的存储结构Oracle的存储结构分为物理存储结构和逻辑存储结构。物理存储结构由数据文件、联机日志文件、控制文件构成，这些是数据库严格意义上的“物理结构组成”，除此之外，组成oracle的还有其他的一些文件。Oracle存储的逻辑结构由数据块（DataBlock）、区（Extent）、段（Segment）和表空间（Tablespace）组成，块是最基本的存储单元，它们是一个自下而上的逻辑结构。数据块数据块数据块由块头、表目录、行目录、行数据和空余空间五部分组成。我们又把块头、表目录、行目录合称为头部信息区，存放块的信息而不存放数据。数据块与操作系统块不同，但是又有关系，一个数据块的大小是操作系统块大小的整数倍。行链接：如果插入的一行数据很大以至于一个块存不下，oracle就会把一行数据分成几段存在数据块中，这个过程叫行链接。行迁移：数据块中存在一条记录，update操作使记录变长，而当前块没有足够的空间容纳这条记录，无奈之下，只能把整行数据移到一个新的数据块中，并在原数据块中留下一个指针指向新的数据块，被移动的数据的rowid保持不变，这就是行迁移。注意行链接和行迁移都会影响数据库的性能。区和段区是数据库的一个逻辑存储单元，它由连续的数据块组成，特别强调的是区是一段连续的存储空间，当段中的空间耗尽时oracle会分配一个新的区给这个段。可以通过存储参数来控制区的分配。段是存放数据的逻辑存储单元，由一系列的区组成，包括数据段、索引段、临时段、回滚段等。表空间系统默认创建的是小文件表空间，是传统的表空间类型。大文件表空间由一个单独的很大的数据文件组成，使oracle能够使用和管理超级大的文件。系统表空间（SYSTEM）：存放数据字典的数据，一旦打开数据库，系统表空间必须在线。辅助表空间（SYSAUX）：对系统表空间的补充，存放数据库组件的信息，必要时可分担系统表空间的负载。回滚表空间（UNDOTBS）：存放回滚数据。每个数据库可以有多个回滚表空间，但只能有一个活动的。临时表空间：是临时存放数据的地方，创建用户的时候应该给用户指定一个临时表空间。USERS表空间:如果创建用户时没有使用DEFAULTTABLESPACE关键字指定默认表空间，oracle会将其指定给用户作为默认表空间。SQL基础什么是

SQL?SQL：结构化查询语言

(StructuredQueryLanguageSQL)是访问Oracle数据库中数据的描述方法SQL语言主要提供下述功能：查询表中数据行插入、修改、删除表中的数据行创建、修改、删除数据库对象保证数据库的一致性和完整性控制数据库及其访问主要的SQL语句SELECTINSERTUPDATEDELETEMERGECREATEALTERDROPRENAMETRUNCATECOMMITROLLBACKSAVEPOINTGRANTREVOKE数据获取数据处理语言(DML)数据定义语言(DDL)事务控制语言(TCL)数据控制语言(DCL)COMMENT基本SQLSELECT语句SELECT*|{[DISTINCT]column|expression[alias],...}FROMtable[WHEREcondition(s)][ORDERBY{column,expr,alias}[ASC|DESC]];在最简单的形式中，SELECT语句必须包含下面的内容：一个SELECT子句，指定被显示的列一个FROM子句，指定表，该表包含SELECT子句中的字段列表选择所有列与指定列SELECT*FROMdepartments;

用SELECT关键字后面的星号(*)，能够显示表中数据的所有列。SELECTdepartment_id,location_idFROMdepartments;

用SELECT语句来显示表的指定列，指定列名之间用逗号分隔。SELECT后也可以指定伪列，一个伪列的行为像表中的列一样，但不是真的存储在表中。不能插入或删除伪列的值。一些可用的伪列如下：CURRVAL、NEXTVAL、LEVEL、ROWID和ROWNUM。算数运算符用算术运算符创建数字和日期数据的表达式上表列出了SQL提供的算术运算符。除了FROM子句，你可以在任何一个SQL语句的子句中使用算术运算符，注意优先级。对于日期数据，只能对DATE和TIMESTAMP数据类型使用加和减操作。操作+-*/说明加减乘除空值null是一个未分配的、未知的值，既然是未知的，就有无数种可能性null不是0，也不是空格判断一个字段是否为NULL，应该用ISNULL或ISNOTNULL，而不能用‘=’，同样的，对NULL的!=、>、<、>=、<=等操作的结果都是未知的，也就算说，这些操作的结果仍然是NULL包含空值的算术表达式计算结果为空

总结：除了ISNULL、ISNOTNULL以外，对NULL的任何操作的结果还是NULL经典的例子SQL> CREATEORREPLACEPROCEDUREP1(P_ININNUMBER)AS

2BEGIN

3IFP_IN>=0THEN

4DBMS_OUTPUT.PUT_LINE('TRUE');

5ELSE

6DBMS_OUTPUT.PUT_LINE('FALSE');

7ENDIF;

8END;

SQL> CREATEORREPLACEPROCEDUREP2(P_ININNUMBER)AS

2BEGIN

3IFP_IN<0THEN

4DBMS_OUTPUT.PUT_LINE(‘FALSE’);

5ELSE

6DBMS_OUTPUT.PUT_LINE(‘TRUE’);

7ENDIF;

8END;

如果传入的是null值，结果是什么呢？定义列的别名列别名：改变列标题的名字可用于计算结果跟在列名后面或在列名和别名之间使用AS关键字如果别名中包含有空格、或特殊字符、或大小写敏感，要求用双引号

SELECTlast_nameASname,commission_pctcommFROMemployees;连字运算符连字运算符：连接列或者字符串到其它的列用两个竖线表示(||)构造一个字符表达式的合成列

SELECTfirst_name||last_nameASEmployeesFROMemployees;文字字符串文字字符串是包含在SELECT列表中的一个字符串，一个数字或者一个日期日期和字符的文字字符串值必须用单引号括起来每个文字字符串在每行输出一次

SELECTlast_name||'isa'||job_idAS“EmployeeDetails”FROMemployees;使用WHERE子句

用WHERE子句限制返回的行

SELECT*|{[DISTINCT]

column|expression[alias],...}FROMtable[WHEREcondition(s)];

WHERE子句跟着FROM子句

WHERE限制查询满足条件的行

condition由列名、表达式、常数和比较操作组成

WHERE子句能够比较列值、文字值、算术表达式或者函数，WHERE子句由三个元素组成：列名，比较条件，列名、常量或值列表。字符串和日期WHERE子句中：字符串和日期的值必须包含在单引号中，但是，数字常数不应该包含在单引号中字符串的值是大小写敏感的，日期值是格式敏感的日期的默认格式是DD-MON-RR.

SELECTlast_name,job_id,department_idFROMemployeesWHERElast_name='Whalen';

比较条件

运算含义

=等于

>大于

>=大于等于

<小于

<=小于等于

<>!=

^=

不等于

比较条件被用于一个表达式与一个值或与另一个表达式的比较。 ...WHEREhire_date='01-JAN-95' ...WHEREsalary>=6000 ...WHERElast_name='Smith'其他比较条件(一)

操作含义

BETWEEN...AND...在两个值之间(包含)

IN(set)匹配一个任意值列表

LIKE匹配一个字符模板

ISNULL是一个空值

使用BETWEEN: SELECTlast_name,salary

FROMemployees

WHEREsalaryBETWEEN2500AND3500;

使用IN:

SELECTemployee_id,last_name,salary,manager_id

FROMemployees

WHEREmanager_idIN(100,101,201);其他比较条件(二)使用LIKE条件使用LIKE条件执行有效搜索串值的通配符搜索搜索条件既可以包含文字也可以包含数字：

%表示任意顺序的零个或多个字符

_表示一个字符

SELECTfirst_nameFROMemployeesWHEREfirst_nameLIKE'%S_';

使用NULL条件用ISNULL操作来测试空值

SELECTlast_name,manager_idFROMemployeesWHEREmanager_idISNULL;

NULL条件，包括ISNULL条件和ISNOTNULL条件。逻辑条件

运算含义

AND

如果两个组成部分的条件都为真，返回TRUE

OR如果两个组成部分中的任一个条件为真，返回TRUE

NOT如果跟随的条件为假，返回TRUE

可以在WHERE子句中用AND和OR运算符使用多个条件。

如果要得到正确的结果：使用AND时要求两个条件同时为真使用OR时要求两者或多者之一为真即可NOT运算符则可以用于另一个SQL运算符，例如，IN、BETWEEN、LIKE、和NULL。注意：表达式NOT(column_nameISNULL)是正确的优先规则表达式求值顺序

1算术运算

2连字操作

3比较操作

4IS[NOT]NULL,LIKE,[NOT]IN5[NOT]BETWEEN6NOT逻辑条件

7AND逻辑条件

8OR逻辑条件使用圆括号改变优先规则集合操作UNION

由每个查询选择的所有不重复的行，即

并集不包含重复值

UNIONALL

由每个查询选择的所有的行，包括所有

重复的行完全，即并集包含重复值

INTERSECT

由每个查询选择的所有不重复的相交行

即交集

MINUS

在第一个查询中，不在后面查询中，并

且结果行不重复，即差集ORDERBY子句用ORDERBY子句排序

–ASC:升序排序，默认

–DESC:降序排序语法:SELECTexprFROMtable[WHEREcondition(s)][ORDERBY{column,expr}[ASC|DESC]];注意：ORDERBY子句在查询完成后执行，如果使用了ORDERBY子句，它必须位于SQL语句的最后。可以指定一个表达式，或者一个别名，或者列序号作为排序条件。SQL函数函数是SQL的一个非常强有力的特性，函数能够用于下面的目的：执行数据计算修改单个数据项操纵输出进行行分组格式化显示的日期和数字转换列数据类型SQL函数有输入参数，并且总有一个返回值。SQL函数(续)函数分为单行函数和多行函数单行函数

仅对单个行进行运算，并且每行返回一个结果。

我们主要讨论以下几种常用的函数类型：字符数字日期转换多行函数

能够操纵成组的行，每个行组给出一个结果，这些函数也被称为组函数。单行函数单行函数：操纵数据项接受多个参数（列或者表达式），并返回一个值作用于每一个返回行每行返回一个结果可以修改数据类型可以嵌套单行函数(续)常用的单行函数：字符函数：接受字符输入，可以返回字符或者数字值数字函数：接受数字输入，返回数字值日期函数：对DATE数据类型的值进行运算(除了MONTHS_BETWEEN函数返回一个数字，所有日期函数都返回一个DATE数据类型的值。)转换函数：从一个数据类型转换为另一个数据类型通用函数：

NVL、NVL2、NULLIF、COALSECE、CASE、DECODE字符函数字符函数可以被分为大小写处理和字符处理两种：大小写处理函数如下：LOWER(column|expression) 转换字符值为小写 UPPER(column|expression) 转换字符值为大写 INITCAP(column|expression)转换每个单词的首字母值为大写，所有其它值为小写字符处理函数如下：

CONCAT(column1|expression1,column2|expression2)

连接第一个字符值到第二个字符值；等价于连接运算符(||)SUBSTR(column|expression,m[,n])

从字符值中返回指定的字符，开始位置在m，n字符长度(如果m是负数，计数从字符值末尾开始；如果n被忽略，返回到串结束的所有字符)。字符函数（续）LENGTH(column|expression)返回表达式中的字符数INSTR(column|expression,’string’,[,m],[n])

返回一个命名串的数字位置。随意地，你可以提供一个位置m作为查找的开始，在字符串中第n次发现的位置。m和n的默认值是1，意味着在起始开始查找，并且报告第一个发现的位置。 LPAD(column|expression,n,'string')

RPAD(column|expression,n,'string')

填充字符值左、右调节到n字符位置的总宽度 TRIM([leading|trailing|both]trim_characterFROMtrim_source)

使你能够从一个字符串修整头或尾字符(或两者)。如果trim_character或trim_source是字符文字，你必须放在单引号中。REPLACE(text,search_string,replacement_string)

从字符串查找一个文本表达式，如果找到，用指定的值串代替它字符函数(续)

函数结果 CONCAT('Hello','World')HelloWorld SUBSTR('HelloWorld',1,5)Hello LENGTH('HelloWorld')10 INSTR('HelloWorld','W')6 LPAD(salary,10,'*')*****24000 RPAD(salary,10,'*')24000***** TRIM('H'FROM'HelloWorld')elloWorld数字函数ROUND(45.926,2)45.93TRUNC(45.926,2)45.92MOD(1600,300)100

ROUND(column|expression,n)

四舍五入列、表达式或值为n位小数位，如果n被忽略，无小数位。(如果n是负值，小数点左边的数被四舍五入)。TRUNC(column|expression,n)

截断列、表达式或值到n位小数，如果n被忽略，那么n默认为0。MOD(m,n)

返回m除以n的余数。常用于确定一个数是奇数还是偶数。日期的使用Oracle数据库用内部数字格式存储日期：世纪，年，月，日，小时，分钟和秒，其内部存储格式举例如下： CENTURYYEARMONTHDAYHOURMINUTESECOND 201607159

2043默认日期显示格式是DD-MON-RR.

SYSDATE函数返回：

•Date•TimeSYSDATE是一个日期函数，它返回当前数据库服务器的日期和时间。

SELECTSYSDATEFROMDUAL;用日期计算既然数据库以数字方式存储日期，那么就可以用算术运算符进行计算，例如，加或减。日期可以加或减数字常数以及日期，如下示例：

运算 结果 说明 date+number

日期加一个天数到一个日期上date-number

日期从一个日期上减一个天数date-date

天数用一个日期减另一个日期date+number/24日期加一个小时数到一个日期上从日期加或者减一个数，结果是一个日期值两个日期相减，得到两个日期之间的天数用小时数除以24，可以加小时到日期上日期函数

函数说明 MONTHS_BETWEEN两个日期之间的月数 ADD_MONTHS加日历月到日期 NEXT_DAY下个星期几是几号 LAST_DAY指定月的最后一天 ROUND四舍五入日期 TRUNC截断日期日期函数(续)MONTHS_BETWEEN(date1,date2)：计算date1和date2之间的月数，其结果可以是正的也可以是负的。如果date1大于date2，结果是正的，反之，结果是负的。结果的小数部分表示月的一部分。ADD_MONTHS(date,n)：添加n个日历月到date。n的值必须是整数，但可以是负的。NEXT_DAY(date,‘char’)：计算在date之后的下一个周(‘char’)指定天的日期。char的值可能是一个表示一天的数或者是一个字符串。LAST_DAY(date)：计算包含date的月的最后一天的日期ROUND(date[,‘fmt’])：返回用格式化模式fmt四舍五入到指定单位的date，如果格式模式fmt被忽略，date被四舍五入到最近的天。TRUNC(date[,‘fmt’])：返回用格式化模式fmt截断到指定单位的带天的时间部分的date，如果格式模式fmt被忽略，date被截断到最近的天。使用日期函数MONTHS_BETWEEN(‘01-SEP-15’,‘11-JAN-14’) 19.6774194ADD_MONTHS(‘11-JAN-16’,6) '11-JUL-16'NEXT_DAY(‘01-SEP-15’,‘3’)下个星期二是几号 '08-SEP-15'LAST_DAY('01-FEB-16') '29-FEB-16'使用日期函数(续)假定SYSDATE='25-JUL-95':ROUND(SYSDATE,'MONTH')01-AUG-95ROUND(SYSDATE,'YEAR')01-JAN-96TRUNC(SYSDATE,'MONTH')01-JUL-95TRUNC(SYSDATE,'YEAR')01-JAN-95TRUNC(TO_DATE('25-JUL-95'),'YEAR') 01-JAN-95转换函数数据类型转换隐式数据类型转换显式数据类型转换对于直接赋值，Oracle服务器能够自动地进行下面的转换：

从

到

VARCHAR2orCHAR NUMBER VARCHAR2orCHAR DATE NUMBERVARCHAR2 DATEVARCHAR2对于表达式赋值，Oracle服务器能自动地进行下面的转换：

从 VARCHAR2orCHAR

到 DATE

从 VARCHAR2orCHAR

到 NUMBER转换函数(续)显式数据类型转换SQL提供三种函数来从一种数据类型转换值到另一种：

TO_CHAR(number|date[,fmt][,nlsparams])

转换一个数字或日期值为一个VARCHAR2字符串，带格式化样式fmt。数字转换：nlsparams参数指定下面的字符，它由数字格式化元素返回：小数字符 99999.99前导0 09999本地货币符号 L9999国际货币符号 $9999如果忽略nlsparams或其它参数，该函数在会话中使用默认参数值。转换函数(续)转化字符串到数字TO_NUMBER(char[,'format_model'])转化字符串到日期TO_DATE(char[,'format_model'])格式模板必须放在单引号中。常用日期格式的模板 YYYY/MM/DDHH24:MI:SSselectto_timestamp('2016-07-1615:13:28.45','yyyy-mm-ddhh24:mi:ss.ff')fromdual;通用函数以下函数可用于任意数据类型，并且适用于空值NVL(expr1,expr2)NVL2(expr1,expr2,expr3)NULLIF(expr1,expr2)COALESCE(expr1,expr2,...,exprn)NVL

转换空值为一个实际值。NVL2

如果expr1非空，返回expr2；如果expr1为空，返回expr3。NULLIF比较两个表达式，如果相等返回空；如果不相等，返回第一个表达式COALESCE返回表达式列表中的第一个非空表达式。条件表达式在SQL语句中提供IF-THEN-ELSE逻辑的使用。两种用法：

–CASE表达式

–DECODE函数CASE表达式

CASEexpr

WHENcomparison_expr1THENreturn_expr1

WHENcomparison_expr2THENreturn_expr2

WHENcomparison_exprnTHENreturn_exprn

ELSEelse_expr]

ENDCASE使用示例 SELECTlast_name,job_id,salary, CASEsalary WHEN‘5000'THEN1.10*salary WHEN‘4000'THEN1.15*salary WHEN‘3500'THEN1.20*salary ELSEsalary

END"REVISED_SALARY"

FROMemployees;DECODE函数 DECODE(col|expression,search1,result1

[,search2,result2,...,][,default]) DECODE函数在比较表达式(expression)和每个查找(search)值后解码表达式，如果表达式与查找相同，返回结果。

如果省略默认值，当没有查找值与表达式相匹配时返回一个空值。SELECTlast_name,job_id,salary,

DECODE(salary,

‘5000’,1.10*salary,

‘4000’,1.15*salary,

‘3500’,1.20*salary,

salary)

REVISED_SALARYFROMemployees;多表连接查询连接表的方式：内连接外连接（左外连接、右外链接）全连接（全外连接）内连接(innerjoin)实际上就是等值连接，表中作为连接条件的列的值必须相等，返回2个表中完全符合条件的记录。外连接（outerjoin）除了显示匹配相等连接条件的数据外,还可以显示基于某一个表中无法匹配相等连接条件的记录。全连接（fulljoin）的查询结果是左外连接和右外链接查询结果的并集，即使是记录关联不上，也会把这部分信息查询出来。

外连接语法外连接运算符是加号(+)SELECTt1.column,t2.columnFROMt1,t2WHEREt1.column(+)=t2.column;SELECTt1.column,t2.columnFROMt1,t2WHEREt1.column=t2.column(+);外连接可以从一个表中返回那些在另一个表中没有直接匹配的行外连接操作符是一个在圆括号中的加号(+)，它被放置在连接的缺少信息的一侧。为了使来自不完善表的一行或多行能够被连接，该操作符有产生一个或多个空行的作用。组函数组函数不象单行函数，组函数对行的集合进行操作，对每组给出一个结果。这些集合可能是整个表或者是表分成的组。

以下为常用的组函数，每个函数接收一个参数：

函数

说明

AVG([DISTINCT|ALL]n)

n的平均值，忽略空值COUNT({*|[DISTINCT|ALL]expr})

计数，求除了空计算，*包括重

复和带空值的行。MAX([DISTINCT|ALL]expr)

最大值，忽略空值MIN([DISTINCT|ALL]expr)

最小值，忽略空值STDDEV([DISTINCT|ALL]x)

n的标准差，忽略空值SUM([DISTINCT|ALL]n)

合计n的值，忽略空值VARIANCE([DISTINCT|ALL]x)

n的方差，忽略空值GROUPBY子句GROUPBY就是通过列将数据进行分组SELECTcolumn,group_function(column)FROMtable[WHEREcondition][GROUPBYgroup_by_expression][ORDERBYcolumn];在语法中，group_by_expression指定那些用于将行分组的列，这些列的值作为行分组的依据。使用WHERE子句，你可以在划分行成组以前过滤行。在GROUPBY子句中必须包含列。在GROUPBY子句中你不能用列别名。默认情况下，行以包含在GROUPBY列表中的字段的升序排序。你可以用ORDERBY子句覆盖这个默认值。HAVING子句用HAVING子句约束分组，执行过程：如果语句有WHERE子句，服务器建立候选行行被分组应用组函数匹配HAVING子句的组被显示SELECTcolumn,group_functionFROMtable[WHEREcondition][GROUPBYgroup_by_expression][HAVINGgroup_condition][ORDERBYcolumn];数据库对象表

基本存储单元,由行和列组成视图

逻辑地从一个或多个表中表示数据子集序列

数字值发生器索引

改善一些查询的性能同义词给对象可选择的名字还有其他的数据库对象，如存储过程、触发器、包、函数、约束等。命名规则表命名和列命名：•必须以字母开始•必须是1–30个字符长度•只能包含A–Z,a–z,0–9,_,$,和#•同一个用户所拥有的对象之间不能重名•不能用Oracle服务器的保留字注意：默认情况下名字是大小写不敏感的，例如，EMPLOYEES与eMPloyees或eMpLOYEES作为同一个名字来处理，oracle会自动转成大写。如果在创建时使用了“”，那么oracle就会区分大小写。Oracle数据库中的表•用户表：

–由用户创建和维护的表的集合，包含用户信息•数据字典：

–由Oracle服务器创建和维护的表的集合，包含数据库信息有四种数据字典视图，每一种有一个特定的前缀来反映其不同的目的。USER_这些视图包含关于用户所拥有的对象的信息。 ALL_这些视图包含所有用户可访问的表(对象表和相关的表)的信息。DBA_这些视图是受限制的视图，它们只能被分配有DBA角色的用户所访问。 V$ 这些视图是动态执行的视图，包含数据库服务器的性能、存储器和锁的信息。常用数据类型

数据类型说明VARCHAR2(size)

可变长度的字符数据（最大为4000字符）CHAR(size)

固定长度的字符数据（默认为1，最大为2000）NUMBER(p,s)

可变长度的数字数据DATE

日期和时间值TIMESTAMP(size) 时间戳，默认长度为6LONG

最大2G的可变长度字符数据CLOB

最大4G的字符数据RAWandLONGRAW

原始二进制数据BLOB

最大4G的二进制数据BFILE

最大4G的，存储在外部文件中的二进制数据ROWID一个64进制的数制系统，表示表中一行的唯一

地址CREATETABLE语句•用户必须有：

–CREATETABLE权限

–一个存储区域CREATETABLE[schema.]table(columndatatype[DEFAULTexpr][,...]);•必须指定：

–表名

–列名、列数据类型和列的大小schema与所有者的名字一样table表的名字DEFAULTexpr指定默认值column列的名字datatype列的数据类型和长度用子查询创建表该方法既可以创建表还可以将从子查询返回的行插入新创建的表中。CREATETABLEdept80[(column[,column...])]ASSELECTemployee_id,last_name,salary*12ANNSAL,hire_dateFROMemployeesWHEREdepartment_id=80;被创建的表要带指定的列名，并且由SELECT语句返回的行被插入到新表中。字段的定义只能包括列名和默认值。如果给出了指定的列，列的数目必须等于子查询中列表的列数目。如果没有给出了指定的列，表的列名应和子查询中的列名相同。完整性规则不会被传递到新表中，仅列的数据类型被定义。ALTERTABLE语句用ALTERTABLE语句可以：•添加一个新列•修改一个已存在的列•为新列定义一个默认值•删除一个列添加列ALTERTABLEtableADD

(columndatatype[DEFAULTexpr][,columndatatype]...);修改列ALTERTABLEtableMODIFY

(columndatatype[DEFAULTexpr][,columndatatype]...);删除列ALTERTABLEtableDROP(column);修改列可以改变列的数据类型、大小和默认值ALTERTABLEdept80MODIFY(last_nameVARCHAR2(30));对默认值的改变只影响后来插入表中的数据原则可以增加宽度或一个数字列的精度。可以增加数字列或字符列的宽度。可以减少一个列的宽度，但仅在列中只包含空值或表中没有行时。可以改变数据类型，但仅在列中只包含空值时。CHAR列与VARCHAR2数据类型可以相互转换，但仅当列中只包含空值时，或者不改变列的大小时。对默认值的改变仅影响以后插入的列。删除表注意：在表中的所有数据和结构都被删除任何未决的事务都被提交所有的索引被删除你不能回退DROPTABLE语句子查询SELECTselect_listFROMtableWHEREexproperator

(SELECTselect_listFROMtable);子查询放在圆括号中，在主查询之前执行一次将子查询放在比较条件的右边子查询的结果被用于主查询(外查询)在子查询中的ORDERBY子句一般不需要子查询可以被放在CREATEVIEW语句中、CREATETABLE语句、UPDATE语句、INSERT语句的INTO子句和UPDATE语句的SET子句中。子查询类型单行子查询：从内SELECT语句只返回一行的查询多行子查询：从内SELECT语句返回多行的查询单行子查询仅返回一行，使用单行比较符

运算符含义

=等于

>大于

>=大于或等于

<小于

<=小于或等于

<>不等于多行子查询返回多于一行使用多行比较符

操作含义

IN等于列表中的任何成员

ANY比较子查询返回的每个值

ALL比较子查询返回的全部值<ANY意思是小于最大值，>ANY意思是大于最小值，=ANY等同于IN。<ALL意思是小于最小值，>ALL意思是大于最大值。处理数据DML-数据操纵语言（

datamanipulationlanguage）是SQL的一个核心部分，用于操作数据库中的数据，DML语言不隐含COMMIT语句，因此需要用户手动提交对数据库的修改。当你做下面操作时，DML语句被执行：–添加新行到表中（增）

–删除表中的行（删）

–修改表中的行（改）事务：由一系列的DML语句组成，成为一个逻辑工作单元。事务中的语句必须全部成功，否则必须全部回退。

DML语言包括：INSERTUPDATEDELETESELECT（DMLorDQL?）MERGECALLEXPLAINPLANLOCKTABLEINSERT语句语法使用INSERT语句添加新行到表中 INSERTINTOtable[(column[,column...])] VALUES(value[,value...]);

在语法中：table是表的名字column是表中的列名value是列的相应值用该语法一次只能插入一行插入新行插入包含每一个列值的新行值以表中列的默认顺序列表在INSERT子句中字段可以随意列表

INSERTINTOdepartments(department_id,department_name,manager_id,location_id)VALUES(70,'PublicRelations',100,1700);字符和日期要用单引号括起来插入带空值的行隐式方法:省略字段列表中的列INSERTINTOdepartments(department_id,epartment_name)VALUES(30,'Purchasing');显式方法:在VALUES子句中指定NULL关键字INSERTINTOdepartments

(department_id,department_name,manager_id,location_id)VALUES(100,'Finance',NULL,NULL);插入特殊的值SYSDATE函数取出当前的日期时间insertintoemp(empno,ename,job,mgr,hiredate,sal,comm,deptno)values(7196,'GREEN','SALESMAN',7782,SYSDATE,2000,null,10);TO_DATE函数可作日期转换，插入特定的日期时间的值

insertintoemp(empno,ename,job,mgr,hiredate,sal,comm,deptno)values(7196,'GREEN','SALESMAN',7782,

TO_DATE('2016-06-0108:30:20',‘YYYY-MM-DDHH24:MI:SS')

,

2000,null,10);从另一个表中复制行创建带有子查询的INSERT语句INSERTINTOsales_reps(id,name,salary,commission_pct)SELECTemployee_id,last_name,salary,commission_pctFROMemployeesWHEREjob_idLIKE'%REP%';不要使用VALUES子句在子查询中列的数目要匹配INSERT子句中列的数目UPDATE语句使用UPDATE语句更改现有的行UPDATEtableSETcolumn=value[,column=value,...][WHEREcondition];

table是表的名字column是表中列的名字value是相应的值或对应列的子查询condition是where条件的内容，以此确定要被更新的行。用查询表来显示受更新的行以确认更新操作。如果需要，一次更新多行更新表中的行如果使用了WHERE子句，指定的一行或多行将被修改UPDATEemployees

SETdepartment_id=70WHEREemployee_id=113;如果遗漏WHERE子句，表中所有的行都会被修改UPDATEcopy_empSETdepartment_id=110;用子查询更新多个列更改7698号雇员的工作和部门，使其与7499号雇员的相同

UPDATEemp

SET(job,deptno)=

(selectjob,deptnofromemp

whereempno=7499)

WHEREempno=7698;对基于另一个表的行进行更改在UPDATE语句中，用子查询来更新基于另一个表中值的那些行。UPDATEcopy_empSETdepartment_id=(SELECTdepartment_id

FROMemployees

WHEREemployee_id=100)WHEREjob_id=any(SELECTjob_id

FROMemployees

WHEREemployee_id=200);用基于来自EMPLOYEES表的值更新COPY_EMP表，它用employee100的部门号改变所有工作岗位与employee200的工作岗位相同的那些雇员的部门号DELETE语句使用DELETE语句从表中删除已存在的行•如果指定了WHERE子句，则删除指定的行DELETEFROMdepartmentsWHEREdepartment_name='Finance';•如果遗漏了WHERE子句，表中所有的行都被删除DELETEFROMcopy_emp;删除基于另一个表的行在DELETE语句中用子查询来删除表中的基于另一个表中值的行DELETEFROMemployeeWHEREdeptno=

(SELECTdeptnofromdeptwheredname='SALES');TRUNCATETRUNCATEtablecopy_emp;

DELETEFROMcopy_emp;清空表中所有数据删除数据的效率明显比DELETE高恢复数据需要备份，代价高基本SQLMERGE合并语句按照指定的条件执行插入或删除操作如果满足条件的行存在，执行更新操作；否则执行插入操作:避免多次重复执行插入和删除操作提高效率而且使用方便在数据仓库应用中经常使用合并语句的语法可以使用merge语句，根据指定的条件进行插入或更新操作MERGEINTOtable_name

table_aliasUSING(table|view|sub_query)aliasON(joincondition)WHENMATCHEDTHENUPDATESET

col1=col_val1,col2=col2_valWHENNOTMATCHEDTHENINSERT(column_list)VALUES(column_values);MERGEINTOcopy_empcUSINGemployeeseON(c.employee_id=e.employee_id)WHENMATCHEDTHENUPDATESETc.first_name=e.first_name,c.last_name=e.last_name,...c.department_id=e.department_idWHENNOTMATCHEDTHENINSERTVALUES(e.employee_id,e.first_name,e.last_name,e.email,e.phone_number,e.hire_date,e.job_id,e.salary,mission_pct,e.manager_id,e.department_id);合并语句举例在对表COPY_EMP使用merge语句，根据指定的条件从表EMPLOYEES中插入或更新数据。数据库事务Oracle数据库基于事务处理确保数据的一致性。要么全部完成，要么全部废弃的操作集合。数据库事务可以由下面的语句组成：•对数据做出一致性修改的DML语句•一个DDL语句•一个DCL语句事务的开始与结束开始于第一个被执行的DML语句遇到下面事件之一结束：

COMMIT或ROLLBACK语句被运行

DDL或DCL语句被执行(自动提交)

用户正常退出（自动提交）

用户非正常退出（自动回滚）

系统异常终止（自动回滚）COMMIT和ROLLBACK语句COMMIT--提交，简单来说就是结束当前事务，把所做的修改保存到数据库ROLLBACL

--回滚，结束当前事务，丢弃所有未决的数据改变用COMMIT和ROLLBACK语句，你能够：确保数据的一致性在数据永久改变之前进行预览分组逻辑相关的操作控制事务使用COMMIT、SAVEPOINT和ROLLBACK语句控制事务逻辑。SAVEPOINTname

在当前事务中标记保存点

ROLLBACKTOSAVEPOINTname

回滚当前事务到指定的保存点，从而丢弃保存点创建后的任何改变。如果忽略了TOSAVEPOINT子句，ROLLBACK语句回滚整个事务。由于保存点是逻辑的，因此，没有办法列出已经创建的保存点。举个例子用SAVEPOINT语句在当前事务中创建一个标记用ROLLBACKTOSAVEPOINT语句回退到该标记UPDATE...SAVEPOINTupdate_done;savepointcreated.INSERT...ROLLBACKTOupdate_done;Rollbackcomplete.隐式事务处理在下面的情况下，一个自动提交发生：

–DDL语句被发送

–DCL语句被发送

–正常退出iSQL*Plus，没有明确地发送COMMIT或ROLLBACK语句当iSQL*Plus非正常退出时，或者发生系统故障时，一个自动回退发生COMMIT或ROLLBACK之前数据的状态以前的数据状态能够被恢复当前用户能用SELECT语句查看DML操作的结果其他用户不能观察当前用户DML语句的结果受影响的行被锁定，其他用户不能改变受影响的行中数据COMMIT之后数据的状态数据在数据库中被永久地改变数据的以前状态被永久地丢失所有用户都能观察该结果受影响行的锁定被释放，其它用户可以操纵那些行所有保存点被擦除ROLLBACK之后的数据状态用ROLLBACK语句丢弃所有未决的改变：数据的改变被撤消数据的以前状态被恢复受影响行的锁定被释放读一致性读一致性确保在任意时间点，数据库都能提供数据的一致性视图读一致性要求对于同一份数据：查询不需要等待修改修改不需要等待查询修改需要等待修改视图视图是基于表或另一个视图的逻辑表，一个视图并不包含它自己的数据，它象一个窗口，通过该窗口可以查看或改变表中的数据。视图基于其上的表称为基表。视图在数据字典中作为一个SELECT语句存储。视图的作用：•限制数据访问•使得复杂的查询容易•提供数据的独立性•表现相同数据的不同观察简单视图和复杂视图下面是简单视图：数据仅来自一个表不包含函数或数据分组能通过视图执行DML操作下面是复杂视图：数据来自多个表包含函数或数据分组不总是允许通过视图进行DML操作Top-N分析•Top-N查询寻找一列的n个最大或最小值，例如：

–销售最好的前10位产品是什么?–销售最差的前10位产品是什么?•最大值和最小值在Top-N查询中设置•Top-N查询在需要基于一个条件，从表中显示最前面的n条记录或最后面的n条记录时是有用的。执行Top-N分析Top-N分析查询的高级结构是：SELECT[column_list],ROWNUMFROM(SELECT[column_list]FROMtableORDERBYTop-N_column)WHEREROWNUM<=N;Top-N分析的例子为了从EMPLOYEES表中显示挣钱最多的3个人的名字及其薪水：SELECTROWNUMasRANK,last_name,salaryFROM(SELECTlast_name,salaryFROMemployeesORDERBYsalaryDESC)WHEREROWNUM<=3;序列是自动产生的唯一的数，可被多个用户共享以产生唯一的整数典型的用途是创建一个主键值可以代替应用程序编号，是一个节省时间的对象序列号独立于表被存储和产生，因此，相同的序列可以被多个表使用当使用高速缓存存储器时，访问序列值的效率提高。CREATESEQUENCE语法定义一个序列来自动产生有顺序的数：CREATESEQUENCEsequence

[INCREMENTBYn][STARTWITHn][{MAXVALUEn|NOMAXVALUE}][{MINVALUEn|NOMINVALUE}][{CYCLE|NOC