第一章 SQL*Plus 命令第一节SQL*Plus介绍SQL*Plus是一个非常重要的操作Oracle数据库的客户端工具主要用于数据查询和数据处理。SQL*Plus工具具有以下功能定义变量编写SQL语句实现数据的插入、修改、删除、查询以及执行PL/SQL语句格式化查询结构、运算处理、保存、打印输出等显示任何一个表的字段定义并实现与用户进行交互完成数据库几乎所有管理工作运行存储在数据库中的子程序或包以sysdba身份登录数据库实例可以实现自动停启数据库实例(除此功能外其余皆可由SQL Developer工具实现)SQL*Plus可以使用两种基本类型命令1.本地命令命令在SQL*Plus本地执行通常不发送到服务器。这些SQL*Plus命令以新行结束不需要使用命令结束符2.服务器指定的命令服务器执行的命令不在SQL*Plus本地执行而是通过服务器进行处理。第二节设置SQL*Plus运行环境SQL*Plus的运行环境是用来输入、执行SQL*Plus命令和显示返回结果的场所其中SET命令是最基本的命令可以为会话设置环境。1.SET命令SET命令语法格式SET system_variable value;system_variable:变量名value变量值查看SET命令功能 help set常见SQL*Plus系统变量变量功能用法ARRAY[SIZE]确定一次性从数据库中提取的行数,默认15行SET ARRAY 50AUTO[COMMIT]{OFF|ON}指定事务的提交为自动(ON)或手动(OFF)SET AUTO ONCOLSEP用于设置选定列之间的分隔符,默认为空格SET COLSEPCOPYC[COMMIT]设置使用COPY命令时的提交频率SET COPY 1000DEF[INE] {|c|ON|OFF}设置在变量置换中使用的前缀字符SET DEFINE ONECHO {OFF}ON}设置回显为ON或OFF;如果ECHO ON,则每个命令将在其输出到屏幕前被显示SET ECHO ONEDITF[ILE]设置在使用默认编辑器时的默认文件名SET EDITF draft.sqlFEED[BACK] {OFF|ON}指定SQL*Plus是否显示指定返回的记录数SET FEEDBACK OFFFLU[SH] {OFF|ON}确定输出是否缓冲或消除屏障SET FLUSH OFFHEA[DING] {OFF|ON}指定是否显示查询结果的列标题,默认显示列标题SET HEADING OFFLIN[ESIZE] {80|n}指定每行显示的字符数SET LINSIZE 40LONG {80|n}指定LONG CLOB NCLOB和 XML Type值的最大长度SET LONG 100000NEWP[AGE] {1|n|none}指定每页顶部的空行数SET NEWPAGE 0NUM[WIDTH]{10|n}指定数字的显示格式SET NUMPAGES {IZE}{14|n}指定每页的行数SET PAGESIZE 60PAU[SE]{OFF|ON}指定打印到屏幕的输出量SET PAUSE ONSERVEROUT[PUT] {OFF|ON} [SIZE n]指定是否显示PL/SQL代码的输出结果SET SERVEROUTPUT ONSQLP[ROMPT] {SQL|TEXT}指定SQL*Plus会话的命令提示符SET SQLPROMPT salapatiTERM[OUT] {OFF|ON}指定是否显示命令文件的输出SET TERMOUT OFFTI[ME] {OFF|ON}控制当前日期的显示.若为ON时,则每个命令前显示当前时间;若为OFF,则禁止时间显示SET TIME OFFTIMI[NG] {OFF|ON}控制时间统计的显示.若为ON,则显示每一个运行的SQL命令或PL/SQL的时间统计;若为OFF,则禁止每一个命令的时间统计SET TIMENG OFFVER[IFY] {OFF|ON}指定在变量置换后是否显示SQL文本.若为ON,则显示文本;若为OFF,则禁止列清单SET VERIFY OFF以上表格中,中括号([])中的选项为可选部分;({})中为选项和默认值,({})中第一位为默认值2.常用SQL*Plus命令SQL*Plus命令一般分为两种:第一种:工作命令,可以实际完成某件事的命令;第二种:格式化命令,用来帮助整理查询出的信息HELP命令HELP命令用于帮助用户查询指定命令的功能描述和缩写形式以及参数选项语法格式: HELP [topic] 或 ? [topic]topic参数表示将要查询的命令的完成名称DESCRIBE命令DESCRIBE命令用于查询指定数据对应的组成结构语法格式:DESC[RIBE] object_nameDESCRIBE可以缩写为DESC,object_name表示将要查询的对象名称CONN命令CONN命令:在一个数据库中会存在多个用户,对不同的用户可以直接使用命令进行切换语法格式:CONN 用户名/口令 [AS SYSDBA]3.注释注释:用于提升代码可读性,加入注释常用方法包括REMARK命令和/*....*/以及--,此三种1.REMARK命令该命令可以实现单行注释,并且用于加入命令文件中2./*......*/使用/*.......*/可以对SQL语句或PL/SQL程序库或命令文件的一行或多行进行注释3.--使用 -- 可以进行单行注释第三节SQL*Plus格式化输出1.格式化列通过COLUMN命令,可以实现格式化查询结果语法格式:COL[UMN] [column_name] [option]column_name:用于指定要设置的列的名称option:用于指定某个列的显示格式option选项的值及其说明option选项的值说明CLEAT清除指定列所设置的显示属性,从而恢复列使用默认的显示属性FORMAT格式化指定的列HEADING定义列标题JUSTIFY调整列标题的对齐方式.默认情况下,数值类型的列为右对齐,其他类型的列为左对齐NULL指定一个字符串,若列的值为null,则由该字符串代替PRINT/NOPRINT显示列标题或隐藏列标题,默认为PRINTON|OFF控制定义的显示属性的状态,OFF表示定义的所有显示属性都不起作用,默认为ONWRAPPED当字符串的长度超过显示宽度时,将字符串的超出部分折叠刀下一行显示WORD_WRAPPED表示从一个完整的字符处折行TRUNCATED表示截断字符串尾部若关键字COLUMN后未指定任何参数,则COLUMN命令将显示SQL*Plus环境中所有列的当前定义属性;如果在COLUMN后指定某个列名,则显示指定列的当前定义属性1.修改列标题命令格式:COL[UMN] column_name HEADING column_headingcolumn_name:用于指定要设置的列的名称column_heading:用于指定列的别名,通过它可以将英文列名设置为中文2.格式化显示列的宽度语法格式: COL[UMN] column_name FORMAT 宽度3.列出当前的显示属性命令格式:COL[UMN] column_name2.定义页与报告的标题1.设置顶部标题语法格式:TTI[TLE] [printspec [text|variable]...] | [OFF|ON]text:用于设置输出结果的头标题(即报告/报表头文字)variable:用于在头标题中输出相应的变量值OFF:表示禁止打印头标题ON:表示允许打印头标题printspec:用于作为头标题的修饰性选项printspec选项的值及其说明printspec选项的值说明COL指定在当前行的第几列打印头标题SKIP跳到从下一行的第几行,默认为1LEFT在当前行左对齐打印数据CENTER在当前行中间打印数据RIGHT在当前行右对齐打印数据BOLD以黑体打印数据2.设置底部标题语法格式:BTI[TLE] [printspec [text|variable]...] | [OFF|ON]text:用于设置输出结果的头标题(即报告/报表头文字)variable:用于在头标题中输出相应的变量值OFF:表示禁止打印头标题ON:表示允许打印头标题printspec:用于作为头标题的修饰性选项printspec选项的值及其说明printspec选项的值说明COL指定在当前行的第几列打印头标题SKIP跳到从下一行的第几行,默认为1LEFT在当前行左对齐打印数据CENTER在当前行中间打印数据RIGHT在当前行右对齐打印数据BOLD以黑体打印数据注意:在使用BTITLE和TTITLE命令后以及其他类似SQL*Plus命令后,需手动关闭防止后续SQL仍继承这些设置第二章数据表操作1.ORACLE常用数据类型ORACLE常用数据类型类型长度描述CHAR(n)n1~2000B保存定长字符串VARCHAR2(n)n1~4000B可以放数字 字母以及ASCII码字符集NUMBER(M,N)M1~38B N-84B,127B表示数字,其中m为数字的总长度,n为小数部分长度,整数部分长度为m-nDATE-用于存放日期时间型数据(不包含毫秒)TIMESTAMP-用于存放日期时间型数据(包含毫秒)CLOB4GB用于存放海量数据BLOB4GB用于保存二进制文件技巧:一般在200个中文字符之内的信息通常使用VARCHAR2如果觉着区分整数和小数麻烦,可以直接使用NUMBER表示时间可以用DATE(时间戳时TIMESTAMP)表示大文件通常使用CLOB,尽量少使用BLOB2.数据表概念表结构:每个数据库包含了若干个表记录:每个表包含了若干行数据,它们是表的值,表中的一行称为一个记录,因此,表是记录的有限合集字段(列):每个记录由若干字段(列)构成关键字:若表中记录的某一字段或字段组合能够标识记录,则称该字段或字段组合为候选关键字.一个表中可以存在多个关键字,则选定其中一个为主关键字,也成为主键.当一个表仅有唯一的一个候选关键字时,该候选关键字就是主关键字,可以用来唯一标识记录行.3.数据定义语言(DDL)3.1创建表创建表语法格式:CREATE TABLE 用户名.表名称( 字段名称 字段类型 [DEFAULT 默认值], 字段名称 字段类型 [DEFAULT 默认值], ...... );表名称和列名称定义:必须以字母开头长度为1~30字符表中名称只能由字母(A~Z a~z)和数字(0~9)和_(下划线)以及$和#组成,且名称需具有意义对同一个用户不能使用相同的表名称不能使用ORALCE 中的保留字3.2修改表修改表语法格式:ALTER 对象类型 名称 .....3.2.1为表中增加字段语法格式:ALTER TABLE 表名称 ADD(字段名称 字段类型 DEFAULT 默认值,.......);3.2.2修改表中字段语法格式:ALTER TABLE 表名称 MODIFY(字段名称 字段类型 DEFAULT 默认值);3.2.3表重命名语法格式:ALTER TABLE 旧的表名 RENAME 新的表名;或RENAME 旧的表名 to 新的表名3.2.4删除表中字段ALTER TABLE 表名称 DROP COLUMN 列名称;3.2.5删除表语法格式:DROP TABLE 表名;第三章询基础SQL查询1.SQL的分类1.1.数据查询语言(Data Query Language,DQL)用于检索数据库中的数据,主要用SELECT命令1.2.数据库操纵语言(Data Manipulation Language,DML)用于改变数据库中的数据,主要包括INSERT(将数据插入到数据库) UPDATE(更新数据库已存在的数据) 和 DELETE(删除数据库中已存在的数据)1.3.事务控制语言(Transaction Control Language,TCL)用于维护数据的一致性,包括COMMIT(提交对数据库的更改) ROLLBACK(取消对数据库的更改) 和 SAVEPOINT(设置保存点)1.4.数据定义语言(Data Definition Language,DDL)用于建立 修改和删除数据库对象1.5.数据控制语言(Data Control Language,DCL)用于执行权限授予和权限收回操作,主要包括GRANT(用于给用户或角色授予权限)和REVOKE(用于收回用户或角色所具有的权限)2.SQL编写规则2.1SQL关键字不区分大小写(推荐使用大写)2.2对象名和列名不区分大小写(推荐和表中保持一致)2.3引号内区分大小写2.4SQL语句可以分行显示3.SELECT语法语法格式:SELECT {[DISTINCT | ALL ] columns | *} FROM {tables | views | other SELECT} [WHERE conditions] [GROUP BY columns] [HAVING conditions] [ORDER BY columns [ASC | DESC]]说明:用中括号[]括起来的部分表示可选的;用{}括起来的部分表示必须从中选一个SELECT语句中各子句的功能类型说明(8位等于1字节)SELECT子句选择数据表或视图中的列FROM子句指定数据来源,包括表或视图或其他SELECT子句WHERE子句对检索的数据进行筛选,只显示满足WHERE条件的行GROUP BY 子句对检索结果进行分组显示.与聚合函数一起使用时,GROUP BY 创建组,聚集函数运算每组的值HAVING子句从使用GROUP BY 子句分组后的查询结果中筛选数据行ORDER BY 子句对结果集进行排序(包括升序和降序,默认为升序)3.1查询所有列语法格式:SELECT [DISTINCT | *(代表所有列) | 列名称 [AS] [列别名],.......] FROM 表名称 [表别名]; -- 最简写法 SELECT * FROM 表名称; -- 查询多个表中所有列 SELECT [DISTINCT | *(代表所有列) | 列名称 [AS] [列别名],.......] FROM 表名称1 [表别名],表名称2 [表别名] .....;3.2查询指定列语法格式:SELECT 列名1,列名2,..... FROM 表名称 [表别名];3.3伪列概念:在ORACLE数据中,有一个标识行中唯一特性的行标识符,名称为ROWID.伪列无法使用DESCRIBE命令查看,需使用SELECT查询,且伪列不支持插入 更新 修改以及删除操作.3.4消除重复行语法:使用DISTINCT 关键字SELECT DISTINCT 列名1,列名2,.... FROM 表名|视图名 [表别名 | 视图名];注意:DISTINCT关键字作用为消除重复内容,但所谓的消除重复内容,指一行完整的数据全部重复,如果多行记录仅仅一列重复,则不会消除3.5带有表达式的SELECT子句在使用SELECT语句时,对数字数据或日期数据都可以使用算数运算表达式;在使用算数运算符时遵循先乘除,后加减的逻辑3.6为列指定别名语法格式:SELECT 列名1 [AS] 列别名1,列名2 [AS] 列别名2.... FROM 表名称 [表别名];3.7处理NULL注意:当算数运算表达式中某一项目为NULL,则显示结果也为NULL3.7.1使用NVL处理NULL语法:NVL(string1,replace_with)功能:如果string1为NULL,则NVL函数返回replace_with的值,否则返回string1的值,如果两个参数均为NULL,则反馈回NULL;该函数的目的是把一个NULL转换成一个实际的值;其中string1和replace_with的数据类型必须一致3.7.2使用NVL2处理NULLNVL2是ORACLE 9i版本后增加的新功能函数,相比NVL,NVL2函数可以同时对NULL或非NULL的值进行判断并返回不同的结果语法格式:NVL2(exp1,exp2,exp3)功能:如果参数exp1值为NULL,则NVL2函数返回参数表达式exp3的值;如果参数exp1值不为NULL,则NVL2函数返回参数表达式exp2的值,即NVL2(表达式,不为空设值,为空设值)3.8链接字符串3.8.1使用 || 操作符链接字符串注意:当链接字符串时,如果在字符串中加入数字值,可以直接指定数字值;如果在字符串中加入字符值或日期,需使用单引号引起来3.8.2使用函数CONCAT链接字符串语法格式:CONCAT(A,B) AB第四章SQL查询进阶1.筛选查询语法格式:SELECT columns_list FROM table_name WHERE conditional_expression;columns_list:字段列表table_name:表名conditional_expression:筛选条件表达式1.1比较筛选基本的比较筛选主要是以下6种:A B:比较A和B的值是否相当A ! B 或 A B:比较A与B是否不相等A B:比较A是否大于BA B:比较A是否小于BA B:比较A是否大于等于BA B:比较A是否小于等于B1.2逻辑查询逻辑运算符一共有以下三种:AND:链接多个条件,多个条件同时满足返回真,有一个不满足返回假(全真为真,有假则假)OR:链接多个条件,多个条件只有有一个满足返回真,全部不满足返回假(有真则真,全假则假)NOT:求反操作,将真变为假或将假变为真优先级:NOT AND OR1.3模糊查询模糊查询也成为字符串匹配或字符串模糊查询,使用关键字LIKELIKE运算符可以使用两个通配符 % 和 _百分号(%):可以匹配任意类型和长度下划线(_):匹配单个任意字符1.4列表范围查找关键字IN的语句可以分为以下两种:值 IN(目标值,目标值....)值 NOT IN(目标值,目标值....)1.5范围查询使用BETWEEN....AND 或 NOT BETWEEN....AND指定范围注意:使用BETWEEN....AND查询时,指定的第一个值必须小于第二个值NOT BETWEEN...AND:返回某个数据值在两个指定值范围之外1.6判断内容是否为空判断是否为NULL的语法:判断为空:字段 | 值 IS NULL判断不为空:字段 | 值 IS NOT NULL2.分组查询2.1使用GROUP BY 进行分组查询关键字:GROUP BY语法格式:SELECT columns_list FROM table_name [WHERE conditional_expression] GROUP BY columns_list;columns_list:字段列表table_name:表名conditional_expression:筛选条件表达式注意:GROUP BY 可以进行单字段分组,也可以进行多个字段分组常用的聚合函数函数说明AVG返回一个数字列或是计算列的平均值COUNT返回查询结果中的记录数MAX返回一个数字列或计算列的最大值MIN返回一个数字列或计算列的最小值SUM返回一个数字列或是计算列的总和2.2使用HAVING子句限制分组结果语法格式:SELECT 聚合函数(column) FROM table_name [WHERE condition_expression] GROUP BY columns_list HAVING condition_expression;3.排序查询语法格式:SELECT [DISTINCT] * | 列名称1 [AS] 列别名.... FROM 表名称 表别名 [WHERE 条件] [ORDER BY 排序字段1 | 列索引序号 ASC|DESC ...];注意:若不明确指出是按照ASC(升序)还是DESC(降序)排序,则默认为升序;且ODER BY 必须在语句末尾4.多表关联查询4.1内链接语法格式:SELECT columns_list FROM table_name1 [INNER] JOIN table_name2 ON join_condition;columns_list:字段列table_name1和table_name2:需要进行关联的表join_condition:实现内链接的条件表达式注意:内连接和普通多表链接没有区别4.2外链接外链接通常分为以下三种:左外链接:关键字为 LEFT JOIN ... ON 或LEFT OUTER JOIN ... ON右外链接:关键字为 RIGHT JOIN ....ON或 RIGHT OUTER JOIN ... ON完全外连接:FULL JOIN ...ON或 FULL OUTER JOIN ... ON注意:左外链接时以左表为主,左表信息全部显示,右表没有匹配到的内容显示为NULL右外链接时以左表为主,右表信息全部显示,左表没有匹配到的内容显示为NULL完全外连接相当于同时执行一个左连接和右链接,在执行完成后将记录合并并去重4.3自然链接自然链接和内连接功能类似,自然链接使用关键字NATURAL JOIN4.4自链接字面意思:自己链接自己4.5交叉链接语法格式:SELECT columns_list FROM table_name1 CROSS JOIN table_name2;columns_list:字段列表table_name1和table_name2:两个实现交叉链接的表名注意:交叉链接的执行结果是冗余的,可以通过WHERE条件过滤信息第五章子查询1.子查询定义当一个查询结果是另一个查询的条件时,那么就称为子查询,子查询是在SQL语句内另外一条SELECT语句注意:子查询必须使用()括起来子查询中不能包括ODERBY子句子查询允许嵌套多层,但不能超过255层2.单行子查询单行子查询是指返回一行数据的子查询语句3.多行子查询多行子查询指的是提供了一个数据的范围3.1.使用IN运算符在多行子查询中使用IN运算符时,它的功能是用于指定一个查询范围,外查询会尝试与这个范围中任意一个数值进行匹配,只要有一个匹配成功,则查询返回当前检索的记录3.2.使用ANY运算符ANY运算符表示与子查询中返回的每个结果进行比较;ANY运算符必须与单行操作符结合使用,并且返回行只要匹配子查询的任意一个结果即可;ANY在使用中有如下3种形式:ANY:表示与子查询中的每个元素进行比较,功能与IN类似ANY:比子查询中返回的最小结果要大(包含ANY)ANY:比子查询中返回的最大结果要小(包含ANY)3.3.使用ALL运算符ALL运算符和ANY运算符类似,表示的是匹配子查询中所有的数据,ALL在使用中有以下3种形式:ALL:等价于NOT IN (但是ALL 不等价于 IN)ALL:比子查询中最大的值还要大(包含ALL)ALL:比子查询中最小的值还要小(包含ALL)第六章操作数据库(数据库操纵语法DML)1.插入数据(INSERT语句)插入数据就是将数据记录添加到已经存在的数据表中1.1单行插入数据语法格式:INSERT INTO table_name [(column_name1)[,column_name2]....] VALUES(express1[,express2]....)table_name:表示要插入数据的表名column_name1和column_name2:指定表的完全或部门列名称,称为列或字段列表,如果指定多个列,那么列之间用逗号分开express1和express2:表示要插入的值列表1.2批量插入数据语法格式:INSERT INTO table_name [(column_name1)[column_name2]....] SELECT Subquerytable_name:表示要插入数据的表名column_name1和column_name2:指定表的完全或部门列名称,称为列或字段列表,如果指定多个列,那么列之间用逗号分开SELECT Subquery:任何合法的SELECT语句,其所选列的个数和类型要与语句中的列数一致2.更新语句(UPDATE语句)语法格式:UPDATE table_name SET {column_name1 express1[,column_name2express2]..... | (column_name1[,column_name2]...)(SELECT Subquery)} [WHERE condition]table_name:表示要插入数据的表名column_name1和column_name2:指定要更新的列名SELECT Subquery:任何合法的SELECT语句,其所选列的个数和类型要与语句中的column一致condition:筛选条件表达式,只要符合筛选条件的记录才能被更新3.删除数据(DELETE语句和TRUNCATE语句)3.1DELETE语句语法格式:DELETE FROM table_name [WHERE condition]table_name:表示要删除记录的表名condition:可选,筛选条件表达式,当该筛选条件存在时,只有符合条件的才进行删除3.2TRUNCATE语句语法格式:TRUNCATE TABLE table_name注意:TRUNCATE是没有删除条件的第七章常用系统函数1.字符类函数常用字符类函数函数语法函数功能ASCII(字符)返回与指定字符对应的十进制数字(即ASCII码值)CHR(数字)给出一个数字,返回与之对应的字符,与ACSII功能互逆CONCAT(列1 | 字符串1,列2 | 字符串2)将字符串2链接到字符串1的后面,并返回结果INITCAP(列|字符串)将字符串中每个单词的开头首字母大写,其他字母小写INSTR(列|字符串,要查找的字符串,开始位置,出现位置)查找一个子字符串是否在指定的位置出现LENGTH(列|字符串)返回字符串长度LOWER(列|字符串)将字符串的内容全部转为小写UPPER(列|字符串)将字符串的内容全部转为大写REPLACE(列|字符串1,字符串2)使用字符串2替代字符串1SUBSTR(列|字符串,开始点i[,长度j])从字符串的第i个位置开始截取长度为j的子字符串2.数字类函数常用数字类函数函数语法函数功能ABS(n)返回n的绝对值CEIL(n)返回大于或等于数值n的最小整数EXP(n)返回e的n次幂,e2.71828183FLORR(n)返回小于或等于n的最大整数LOG(n1,n2)返回以n1为底n2的对数MOD(n1,n2)取模,即返回n1除以n2的余数POWER(n1,n2)返回n1的n2次方SIGN(n)若n为负数,则返回-1;若n为整数,则返回1,若n为0,则返回0COS(n)返回n的余弦值,n为弧度SIN(n)返回n的正弦值,n为弧度SQST(n)返回n的平方根,n为非负数ROUND(数字,[保留位数])对数字进行四舍五入,可以指定保留位数如果不指定,则表示将小数点之后的数字全部进行四舍五入TRUNC(数字,[保留位数])保留指定位数的小数,如果不指定,则表示不保留小数3.日期和时间类函数常用的日期和时间类函数函数语法函数功能ADD_MONTHS(d,i)返回日期d加上i个月之后的结果,其中i为任意整数LAST_DAY(d)返回d月份的最后一天MONTHS_BETWEEN(d1,d2)返回d1和d2之间月份数,若d1和d2的日期相同,或者都是该月的最后一天,则返回一个整数,否则返回一个小数NEW_TIME(d1,t1,t2)返回t1时区中的日期和时间 d1在t2时区中对应的日期和时间.其中d1时一个日期类数据,t1和t2是字符串SYSDATE返回系统当前的日期4.转换类函数常用转换类函数函数语法函数功能TO_CHAR(X[,format])该函数实现将表达式转换为字符串,format表示字符串格式TO_DATE(s[,format[lan]])该函数将字符串s转换成日期类型,format表示字符串格式,lan表示所使用的语言TO_NUMBER(s[,format[lan]])该函数将返回字符串s代表的数字,format表示字符串格式,lan表示所使用的语言日期格式化标记函数语法函数功能YYYY表示完成的年份Y,YYY带逗号的年,比如2,017YYY年的后三位YY年的后两位Y年的最后一位YEAR表示年份的文字,直接显示四位的年MONTH表示月份的文字,直接显示2位的月MM用两位数来表示月份DAY表示天数的文字DDD表示一年里的天数DD表示一个月里的天数D表示一周里的天数DY用文字表示星期几WW表示一年里的周数W表示一个月里的周数HH表示12小时制,小时是两位数字,使用2个HHHH24表示24小时制MI表示分钟SS表示秒,秒是2位数字,使用两个SSSSSSS午夜之后的秒数字表示AM|PM表示上午或下午5.聚合类函数常用聚合类函数函数语法函数功能AVG(x[DISTINCT|ALL])计算选择列的平均值,可以是一个列或多个列的表达式COUNT(x[DISTINCT|ALL])返回查询结果中的记录数MAX(x[DISTINCT|ALL])返回选择列中的最大数,可以是一个列或多个列的表达式MIN(x[DISTINCT|ALL])返回选择列中的最小数,可以是一个列或多个列的表达式SUM(x[DISTINCT|ALL])返回选择列中的数值总和,可以是一个列或多个列的表达式VARIANCE(x[DISTINCT|ALL])返回选择列中的统计方差,可以是一个列或多个列的表达式STDDEV(x[DISTINCT|ALL])返回选择列中的标准偏差,可以是一个列或多个列的表达式第八章完整性约束约束分为以下5种:非空约束:表示此字段内容不允许为空唯一性约束:此列的内容不允许出现重复主键约束:表示一个唯一的表示,具有唯一性和非空性外键约束:用来约束两个表中列之间的关系检查约束:用于自行编写设置内容的检查条件1.非空约束非空约束:限制必须为某个列提供值,在创建表结构时对于不许为NULL的列可以用NOT NULL标记修改为非空约束: ALTER TBALE 表名 MODIFY 列名 NOT NULL; 删除非空约束: ALTER TABLE 表名 MODIFY 列名 NULL;2.唯一性约束唯一性约束强调所在的列不允许有相同的值,但是允许为空;若要设置某一列为唯一性约束,通常使用CONSTRAINT....unique标记该列-- 添加唯一性约束 ALTER TABLE 表名 NOT CONSTRAINT 约束名 UNIQUE (列名1,列名2....); -- 删除唯一性约束 ALTER TABLE 表名 DROP CONSTRAINT 约束名;3.主键约束主键约束用于唯一地标识表中的每一行记录,在一个表中,最多只能有一个主键约束;主键约束既可以由一个列组成,也可以由两个或两个以上的列组成(联合主键);主键约束同时具有唯一和不为NULL的特点-- 添加主键约束 ALTER TABLE 表名 ADD CONSTRAINT 约束名 PRIMARY KEY (列名); -- 删除主键约束 ALTER TABLE 表名 DROP CONSTRAINT 约束名;4.外键约束外键是指一个表中含有与另一个表中的主键相同的列或列组,而另外一个表中被引用的列必须具有主键约束或唯一性约束-- 添加外键约束 ALTER TABLE 表名 ADD CONSTRAINT 约束名 FOREIGN KEY (列名) REFERENCES 约束名(列名); 注:若外键表的外键列与被引用表的被引用列列明一致可以省略 REFERENCES 后的内容 -- 删除具有外键约束的表 注: 1.删除被引用表的数据之前,应先删除外键表所对应的数据 2.删除被引用表之前,应先将外键表删除 DROP TABLE 表名 CASCADE CONSTRAINT; -- 删除外键约束 ALTER TABLE 表名 DROP CONSTRAINT 约束名;5.禁用和激活约束-- 禁用已经存在的约束 ALTER TABLE 表名 DISABLE CONSTRAINT 约束名 注:在禁用主键约束时,ORACLE会默认删除约束对应的唯一索引,而重新激活约束时,ORACLE会重新建立唯一索引;可以在约束名后放置 KEEP INDEX 实现删除约束时保留对应的唯一索引 -- 激活约束 ALTER TABLE 表名 ENABLE CONSTRAINT 约束名 -- 删除约束 ALTER TABLE 表名 DROP CONSTRAINT 约束名;第九章索引和视图创建索引的注意:1.索引应当建立在WHERE子句频繁引用的列上2.如果经常需要基于某列或某几行列执行排序操作,那么在这些列上建立索引可以加快数据排序速度3.限制表的索引个数,索引主要用于加快查询速度,但是会降低DML操作的速度4.指定索引块空间的使用参数.5.将表和索引部署到相同的表空间,可以简化表空间的管理;将表和索引部署到不同的表空间,可以提高访问性能.6.不要在小表上建立索引.7.为提高表链接的性能,应该在链接列上建立索引1.创建B树索引-- 创建索引语句 CREATE INDEX 索引名 ON 表名(列名);2.创建位图索引-- 创建位图索引 CREATE BITMAP INDEX 索引名3.建立反向键索引-- 建立反向键索引 CREATE INDEX 索引名 ON 表名(列名) REVERSE;4.合并和重建索引-- 合并索引 ALTER INDEX 索引名 COALESCE DEALLOCATE UNUSED; -- 重建索引 ALTER INDEX 索引名 REBUILD;5.删除索引DROP INDEX 索引名;6.显示索引信息-- 显示表的所有索引 SELECT index_name,index_type FROM DBA_INDEXES WHERE owner 模式名; -- 显示索引列 SELECT column_name,column_length FROM DBA_IND_COLUMNS WHERE index_name 索引名; -- 显示索引位置及大小 SELECT tablespace_name,segment_type,bytes FROM USER_SEGMENTS WHERE segment_name 索引名; -- 显示函数索引 SELECT column_expression FROM USER_IND_EXPRESSIONS WHERE index_name 索引名;第十章视图-- 普通视图 CREATE [or replace ] VIEW 视图名称 [(别名1,别名2....)] AS 子查询 -- 只读视图 CREATE [or replace ] VIEW 视图名称 [(别名1,别名2....)] AS 子查询 WITH READ ONLY; -- 管理视图 DESC USER_VIEWS; -- 修改视图定义 重点是 REPLACE CREATE OR REPLACE VIEW 视图名称 [(别名1,别名2....)] AS 子查询 -- 重新编译视图 ALTER VIEW 视图名 COMPILE; -- 删除视图 DROP VIEW 视图名; Oracle基础笔记
第一章 SQL*Plus 命令第一节SQL*Plus介绍SQL*Plus是一个非常重要的操作Oracle数据库的客户端工具主要用于数据查询和数据处理。SQL*Plus工具具有以下功能定义变量编写SQL语句实现数据的插入、修改、删除、查询以及执行PL/SQL语句格式化查询结构、运算处理、保存、打印输出等显示任何一个表的字段定义并实现与用户进行交互完成数据库几乎所有管理工作运行存储在数据库中的子程序或包以sysdba身份登录数据库实例可以实现自动停启数据库实例(除此功能外其余皆可由SQL Developer工具实现)SQL*Plus可以使用两种基本类型命令1.本地命令命令在SQL*Plus本地执行通常不发送到服务器。这些SQL*Plus命令以新行结束不需要使用命令结束符2.服务器指定的命令服务器执行的命令不在SQL*Plus本地执行而是通过服务器进行处理。第二节设置SQL*Plus运行环境SQL*Plus的运行环境是用来输入、执行SQL*Plus命令和显示返回结果的场所其中SET命令是最基本的命令可以为会话设置环境。1.SET命令SET命令语法格式SET system_variable value;system_variable:变量名value变量值查看SET命令功能 help set常见SQL*Plus系统变量变量功能用法ARRAY[SIZE]确定一次性从数据库中提取的行数,默认15行SET ARRAY 50AUTO[COMMIT]{OFF|ON}指定事务的提交为自动(ON)或手动(OFF)SET AUTO ONCOLSEP用于设置选定列之间的分隔符,默认为空格SET COLSEPCOPYC[COMMIT]设置使用COPY命令时的提交频率SET COPY 1000DEF[INE] {|c|ON|OFF}设置在变量置换中使用的前缀字符SET DEFINE ONECHO {OFF}ON}设置回显为ON或OFF;如果ECHO ON,则每个命令将在其输出到屏幕前被显示SET ECHO ONEDITF[ILE]设置在使用默认编辑器时的默认文件名SET EDITF draft.sqlFEED[BACK] {OFF|ON}指定SQL*Plus是否显示指定返回的记录数SET FEEDBACK OFFFLU[SH] {OFF|ON}确定输出是否缓冲或消除屏障SET FLUSH OFFHEA[DING] {OFF|ON}指定是否显示查询结果的列标题,默认显示列标题SET HEADING OFFLIN[ESIZE] {80|n}指定每行显示的字符数SET LINSIZE 40LONG {80|n}指定LONG CLOB NCLOB和 XML Type值的最大长度SET LONG 100000NEWP[AGE] {1|n|none}指定每页顶部的空行数SET NEWPAGE 0NUM[WIDTH]{10|n}指定数字的显示格式SET NUMPAGES {IZE}{14|n}指定每页的行数SET PAGESIZE 60PAU[SE]{OFF|ON}指定打印到屏幕的输出量SET PAUSE ONSERVEROUT[PUT] {OFF|ON} [SIZE n]指定是否显示PL/SQL代码的输出结果SET SERVEROUTPUT ONSQLP[ROMPT] {SQL|TEXT}指定SQL*Plus会话的命令提示符SET SQLPROMPT salapatiTERM[OUT] {OFF|ON}指定是否显示命令文件的输出SET TERMOUT OFFTI[ME] {OFF|ON}控制当前日期的显示.若为ON时,则每个命令前显示当前时间;若为OFF,则禁止时间显示SET TIME OFFTIMI[NG] {OFF|ON}控制时间统计的显示.若为ON,则显示每一个运行的SQL命令或PL/SQL的时间统计;若为OFF,则禁止每一个命令的时间统计SET TIMENG OFFVER[IFY] {OFF|ON}指定在变量置换后是否显示SQL文本.若为ON,则显示文本;若为OFF,则禁止列清单SET VERIFY OFF以上表格中,中括号([])中的选项为可选部分;({})中为选项和默认值,({})中第一位为默认值2.常用SQL*Plus命令SQL*Plus命令一般分为两种:第一种:工作命令,可以实际完成某件事的命令;第二种:格式化命令,用来帮助整理查询出的信息HELP命令HELP命令用于帮助用户查询指定命令的功能描述和缩写形式以及参数选项语法格式: HELP [topic] 或 ? [topic]topic参数表示将要查询的命令的完成名称DESCRIBE命令DESCRIBE命令用于查询指定数据对应的组成结构语法格式:DESC[RIBE] object_nameDESCRIBE可以缩写为DESC,object_name表示将要查询的对象名称CONN命令CONN命令:在一个数据库中会存在多个用户,对不同的用户可以直接使用命令进行切换语法格式:CONN 用户名/口令 [AS SYSDBA]3.注释注释:用于提升代码可读性,加入注释常用方法包括REMARK命令和/*....*/以及--,此三种1.REMARK命令该命令可以实现单行注释,并且用于加入命令文件中2./*......*/使用/*.......*/可以对SQL语句或PL/SQL程序库或命令文件的一行或多行进行注释3.--使用 -- 可以进行单行注释第三节SQL*Plus格式化输出1.格式化列通过COLUMN命令,可以实现格式化查询结果语法格式:COL[UMN] [column_name] [option]column_name:用于指定要设置的列的名称option:用于指定某个列的显示格式option选项的值及其说明option选项的值说明CLEAT清除指定列所设置的显示属性,从而恢复列使用默认的显示属性FORMAT格式化指定的列HEADING定义列标题JUSTIFY调整列标题的对齐方式.默认情况下,数值类型的列为右对齐,其他类型的列为左对齐NULL指定一个字符串,若列的值为null,则由该字符串代替PRINT/NOPRINT显示列标题或隐藏列标题,默认为PRINTON|OFF控制定义的显示属性的状态,OFF表示定义的所有显示属性都不起作用,默认为ONWRAPPED当字符串的长度超过显示宽度时,将字符串的超出部分折叠刀下一行显示WORD_WRAPPED表示从一个完整的字符处折行TRUNCATED表示截断字符串尾部若关键字COLUMN后未指定任何参数,则COLUMN命令将显示SQL*Plus环境中所有列的当前定义属性;如果在COLUMN后指定某个列名,则显示指定列的当前定义属性1.修改列标题命令格式:COL[UMN] column_name HEADING column_headingcolumn_name:用于指定要设置的列的名称column_heading:用于指定列的别名,通过它可以将英文列名设置为中文2.格式化显示列的宽度语法格式: COL[UMN] column_name FORMAT 宽度3.列出当前的显示属性命令格式:COL[UMN] column_name2.定义页与报告的标题1.设置顶部标题语法格式:TTI[TLE] [printspec [text|variable]...] | [OFF|ON]text:用于设置输出结果的头标题(即报告/报表头文字)variable:用于在头标题中输出相应的变量值OFF:表示禁止打印头标题ON:表示允许打印头标题printspec:用于作为头标题的修饰性选项printspec选项的值及其说明printspec选项的值说明COL指定在当前行的第几列打印头标题SKIP跳到从下一行的第几行,默认为1LEFT在当前行左对齐打印数据CENTER在当前行中间打印数据RIGHT在当前行右对齐打印数据BOLD以黑体打印数据2.设置底部标题语法格式:BTI[TLE] [printspec [text|variable]...] | [OFF|ON]text:用于设置输出结果的头标题(即报告/报表头文字)variable:用于在头标题中输出相应的变量值OFF:表示禁止打印头标题ON:表示允许打印头标题printspec:用于作为头标题的修饰性选项printspec选项的值及其说明printspec选项的值说明COL指定在当前行的第几列打印头标题SKIP跳到从下一行的第几行,默认为1LEFT在当前行左对齐打印数据CENTER在当前行中间打印数据RIGHT在当前行右对齐打印数据BOLD以黑体打印数据注意:在使用BTITLE和TTITLE命令后以及其他类似SQL*Plus命令后,需手动关闭防止后续SQL仍继承这些设置第二章数据表操作1.ORACLE常用数据类型ORACLE常用数据类型类型长度描述CHAR(n)n1~2000B保存定长字符串VARCHAR2(n)n1~4000B可以放数字 字母以及ASCII码字符集NUMBER(M,N)M1~38B N-84B,127B表示数字,其中m为数字的总长度,n为小数部分长度,整数部分长度为m-nDATE-用于存放日期时间型数据(不包含毫秒)TIMESTAMP-用于存放日期时间型数据(包含毫秒)CLOB4GB用于存放海量数据BLOB4GB用于保存二进制文件技巧:一般在200个中文字符之内的信息通常使用VARCHAR2如果觉着区分整数和小数麻烦,可以直接使用NUMBER表示时间可以用DATE(时间戳时TIMESTAMP)表示大文件通常使用CLOB,尽量少使用BLOB2.数据表概念表结构:每个数据库包含了若干个表记录:每个表包含了若干行数据,它们是表的值,表中的一行称为一个记录,因此,表是记录的有限合集字段(列):每个记录由若干字段(列)构成关键字:若表中记录的某一字段或字段组合能够标识记录,则称该字段或字段组合为候选关键字.一个表中可以存在多个关键字,则选定其中一个为主关键字,也成为主键.当一个表仅有唯一的一个候选关键字时,该候选关键字就是主关键字,可以用来唯一标识记录行.3.数据定义语言(DDL)3.1创建表创建表语法格式:CREATE TABLE 用户名.表名称( 字段名称 字段类型 [DEFAULT 默认值], 字段名称 字段类型 [DEFAULT 默认值], ...... );表名称和列名称定义:必须以字母开头长度为1~30字符表中名称只能由字母(A~Z a~z)和数字(0~9)和_(下划线)以及$和#组成,且名称需具有意义对同一个用户不能使用相同的表名称不能使用ORALCE 中的保留字3.2修改表修改表语法格式:ALTER 对象类型 名称 .....3.2.1为表中增加字段语法格式:ALTER TABLE 表名称 ADD(字段名称 字段类型 DEFAULT 默认值,.......);3.2.2修改表中字段语法格式:ALTER TABLE 表名称 MODIFY(字段名称 字段类型 DEFAULT 默认值);3.2.3表重命名语法格式:ALTER TABLE 旧的表名 RENAME 新的表名;或RENAME 旧的表名 to 新的表名3.2.4删除表中字段ALTER TABLE 表名称 DROP COLUMN 列名称;3.2.5删除表语法格式:DROP TABLE 表名;第三章询基础SQL查询1.SQL的分类1.1.数据查询语言(Data Query Language,DQL)用于检索数据库中的数据,主要用SELECT命令1.2.数据库操纵语言(Data Manipulation Language,DML)用于改变数据库中的数据,主要包括INSERT(将数据插入到数据库) UPDATE(更新数据库已存在的数据) 和 DELETE(删除数据库中已存在的数据)1.3.事务控制语言(Transaction Control Language,TCL)用于维护数据的一致性,包括COMMIT(提交对数据库的更改) ROLLBACK(取消对数据库的更改) 和 SAVEPOINT(设置保存点)1.4.数据定义语言(Data Definition Language,DDL)用于建立 修改和删除数据库对象1.5.数据控制语言(Data Control Language,DCL)用于执行权限授予和权限收回操作,主要包括GRANT(用于给用户或角色授予权限)和REVOKE(用于收回用户或角色所具有的权限)2.SQL编写规则2.1SQL关键字不区分大小写(推荐使用大写)2.2对象名和列名不区分大小写(推荐和表中保持一致)2.3引号内区分大小写2.4SQL语句可以分行显示3.SELECT语法语法格式:SELECT {[DISTINCT | ALL ] columns | *} FROM {tables | views | other SELECT} [WHERE conditions] [GROUP BY columns] [HAVING conditions] [ORDER BY columns [ASC | DESC]]说明:用中括号[]括起来的部分表示可选的;用{}括起来的部分表示必须从中选一个SELECT语句中各子句的功能类型说明(8位等于1字节)SELECT子句选择数据表或视图中的列FROM子句指定数据来源,包括表或视图或其他SELECT子句WHERE子句对检索的数据进行筛选,只显示满足WHERE条件的行GROUP BY 子句对检索结果进行分组显示.与聚合函数一起使用时,GROUP BY 创建组,聚集函数运算每组的值HAVING子句从使用GROUP BY 子句分组后的查询结果中筛选数据行ORDER BY 子句对结果集进行排序(包括升序和降序,默认为升序)3.1查询所有列语法格式:SELECT [DISTINCT | *(代表所有列) | 列名称 [AS] [列别名],.......] FROM 表名称 [表别名]; -- 最简写法 SELECT * FROM 表名称; -- 查询多个表中所有列 SELECT [DISTINCT | *(代表所有列) | 列名称 [AS] [列别名],.......] FROM 表名称1 [表别名],表名称2 [表别名] .....;3.2查询指定列语法格式:SELECT 列名1,列名2,..... FROM 表名称 [表别名];3.3伪列概念:在ORACLE数据中,有一个标识行中唯一特性的行标识符,名称为ROWID.伪列无法使用DESCRIBE命令查看,需使用SELECT查询,且伪列不支持插入 更新 修改以及删除操作.3.4消除重复行语法:使用DISTINCT 关键字SELECT DISTINCT 列名1,列名2,.... FROM 表名|视图名 [表别名 | 视图名];注意:DISTINCT关键字作用为消除重复内容,但所谓的消除重复内容,指一行完整的数据全部重复,如果多行记录仅仅一列重复,则不会消除3.5带有表达式的SELECT子句在使用SELECT语句时,对数字数据或日期数据都可以使用算数运算表达式;在使用算数运算符时遵循先乘除,后加减的逻辑3.6为列指定别名语法格式:SELECT 列名1 [AS] 列别名1,列名2 [AS] 列别名2.... FROM 表名称 [表别名];3.7处理NULL注意:当算数运算表达式中某一项目为NULL,则显示结果也为NULL3.7.1使用NVL处理NULL语法:NVL(string1,replace_with)功能:如果string1为NULL,则NVL函数返回replace_with的值,否则返回string1的值,如果两个参数均为NULL,则反馈回NULL;该函数的目的是把一个NULL转换成一个实际的值;其中string1和replace_with的数据类型必须一致3.7.2使用NVL2处理NULLNVL2是ORACLE 9i版本后增加的新功能函数,相比NVL,NVL2函数可以同时对NULL或非NULL的值进行判断并返回不同的结果语法格式:NVL2(exp1,exp2,exp3)功能:如果参数exp1值为NULL,则NVL2函数返回参数表达式exp3的值;如果参数exp1值不为NULL,则NVL2函数返回参数表达式exp2的值,即NVL2(表达式,不为空设值,为空设值)3.8链接字符串3.8.1使用 || 操作符链接字符串注意:当链接字符串时,如果在字符串中加入数字值,可以直接指定数字值;如果在字符串中加入字符值或日期,需使用单引号引起来3.8.2使用函数CONCAT链接字符串语法格式:CONCAT(A,B) AB第四章SQL查询进阶1.筛选查询语法格式:SELECT columns_list FROM table_name WHERE conditional_expression;columns_list:字段列表table_name:表名conditional_expression:筛选条件表达式1.1比较筛选基本的比较筛选主要是以下6种:A B:比较A和B的值是否相当A ! B 或 A B:比较A与B是否不相等A B:比较A是否大于BA B:比较A是否小于BA B:比较A是否大于等于BA B:比较A是否小于等于B1.2逻辑查询逻辑运算符一共有以下三种:AND:链接多个条件,多个条件同时满足返回真,有一个不满足返回假(全真为真,有假则假)OR:链接多个条件,多个条件只有有一个满足返回真,全部不满足返回假(有真则真,全假则假)NOT:求反操作,将真变为假或将假变为真优先级:NOT AND OR1.3模糊查询模糊查询也成为字符串匹配或字符串模糊查询,使用关键字LIKELIKE运算符可以使用两个通配符 % 和 _百分号(%):可以匹配任意类型和长度下划线(_):匹配单个任意字符1.4列表范围查找关键字IN的语句可以分为以下两种:值 IN(目标值,目标值....)值 NOT IN(目标值,目标值....)1.5范围查询使用BETWEEN....AND 或 NOT BETWEEN....AND指定范围注意:使用BETWEEN....AND查询时,指定的第一个值必须小于第二个值NOT BETWEEN...AND:返回某个数据值在两个指定值范围之外1.6判断内容是否为空判断是否为NULL的语法:判断为空:字段 | 值 IS NULL判断不为空:字段 | 值 IS NOT NULL2.分组查询2.1使用GROUP BY 进行分组查询关键字:GROUP BY语法格式:SELECT columns_list FROM table_name [WHERE conditional_expression] GROUP BY columns_list;columns_list:字段列表table_name:表名conditional_expression:筛选条件表达式注意:GROUP BY 可以进行单字段分组,也可以进行多个字段分组常用的聚合函数函数说明AVG返回一个数字列或是计算列的平均值COUNT返回查询结果中的记录数MAX返回一个数字列或计算列的最大值MIN返回一个数字列或计算列的最小值SUM返回一个数字列或是计算列的总和2.2使用HAVING子句限制分组结果语法格式:SELECT 聚合函数(column) FROM table_name [WHERE condition_expression] GROUP BY columns_list HAVING condition_expression;3.排序查询语法格式:SELECT [DISTINCT] * | 列名称1 [AS] 列别名.... FROM 表名称 表别名 [WHERE 条件] [ORDER BY 排序字段1 | 列索引序号 ASC|DESC ...];注意:若不明确指出是按照ASC(升序)还是DESC(降序)排序,则默认为升序;且ODER BY 必须在语句末尾4.多表关联查询4.1内链接语法格式:SELECT columns_list FROM table_name1 [INNER] JOIN table_name2 ON join_condition;columns_list:字段列table_name1和table_name2:需要进行关联的表join_condition:实现内链接的条件表达式注意:内连接和普通多表链接没有区别4.2外链接外链接通常分为以下三种:左外链接:关键字为 LEFT JOIN ... ON 或LEFT OUTER JOIN ... ON右外链接:关键字为 RIGHT JOIN ....ON或 RIGHT OUTER JOIN ... ON完全外连接:FULL JOIN ...ON或 FULL OUTER JOIN ... ON注意:左外链接时以左表为主,左表信息全部显示,右表没有匹配到的内容显示为NULL右外链接时以左表为主,右表信息全部显示,左表没有匹配到的内容显示为NULL完全外连接相当于同时执行一个左连接和右链接,在执行完成后将记录合并并去重4.3自然链接自然链接和内连接功能类似,自然链接使用关键字NATURAL JOIN4.4自链接字面意思:自己链接自己4.5交叉链接语法格式:SELECT columns_list FROM table_name1 CROSS JOIN table_name2;columns_list:字段列表table_name1和table_name2:两个实现交叉链接的表名注意:交叉链接的执行结果是冗余的,可以通过WHERE条件过滤信息第五章子查询1.子查询定义当一个查询结果是另一个查询的条件时,那么就称为子查询,子查询是在SQL语句内另外一条SELECT语句注意:子查询必须使用()括起来子查询中不能包括ODERBY子句子查询允许嵌套多层,但不能超过255层2.单行子查询单行子查询是指返回一行数据的子查询语句3.多行子查询多行子查询指的是提供了一个数据的范围3.1.使用IN运算符在多行子查询中使用IN运算符时,它的功能是用于指定一个查询范围,外查询会尝试与这个范围中任意一个数值进行匹配,只要有一个匹配成功,则查询返回当前检索的记录3.2.使用ANY运算符ANY运算符表示与子查询中返回的每个结果进行比较;ANY运算符必须与单行操作符结合使用,并且返回行只要匹配子查询的任意一个结果即可;ANY在使用中有如下3种形式:ANY:表示与子查询中的每个元素进行比较,功能与IN类似ANY:比子查询中返回的最小结果要大(包含ANY)ANY:比子查询中返回的最大结果要小(包含ANY)3.3.使用ALL运算符ALL运算符和ANY运算符类似,表示的是匹配子查询中所有的数据,ALL在使用中有以下3种形式:ALL:等价于NOT IN (但是ALL 不等价于 IN)ALL:比子查询中最大的值还要大(包含ALL)ALL:比子查询中最小的值还要小(包含ALL)第六章操作数据库(数据库操纵语法DML)1.插入数据(INSERT语句)插入数据就是将数据记录添加到已经存在的数据表中1.1单行插入数据语法格式:INSERT INTO table_name [(column_name1)[,column_name2]....] VALUES(express1[,express2]....)table_name:表示要插入数据的表名column_name1和column_name2:指定表的完全或部门列名称,称为列或字段列表,如果指定多个列,那么列之间用逗号分开express1和express2:表示要插入的值列表1.2批量插入数据语法格式:INSERT INTO table_name [(column_name1)[column_name2]....] SELECT Subquerytable_name:表示要插入数据的表名column_name1和column_name2:指定表的完全或部门列名称,称为列或字段列表,如果指定多个列,那么列之间用逗号分开SELECT Subquery:任何合法的SELECT语句,其所选列的个数和类型要与语句中的列数一致2.更新语句(UPDATE语句)语法格式:UPDATE table_name SET {column_name1 express1[,column_name2express2]..... | (column_name1[,column_name2]...)(SELECT Subquery)} [WHERE condition]table_name:表示要插入数据的表名column_name1和column_name2:指定要更新的列名SELECT Subquery:任何合法的SELECT语句,其所选列的个数和类型要与语句中的column一致condition:筛选条件表达式,只要符合筛选条件的记录才能被更新3.删除数据(DELETE语句和TRUNCATE语句)3.1DELETE语句语法格式:DELETE FROM table_name [WHERE condition]table_name:表示要删除记录的表名condition:可选,筛选条件表达式,当该筛选条件存在时,只有符合条件的才进行删除3.2TRUNCATE语句语法格式:TRUNCATE TABLE table_name注意:TRUNCATE是没有删除条件的第七章常用系统函数1.字符类函数常用字符类函数函数语法函数功能ASCII(字符)返回与指定字符对应的十进制数字(即ASCII码值)CHR(数字)给出一个数字,返回与之对应的字符,与ACSII功能互逆CONCAT(列1 | 字符串1,列2 | 字符串2)将字符串2链接到字符串1的后面,并返回结果INITCAP(列|字符串)将字符串中每个单词的开头首字母大写,其他字母小写INSTR(列|字符串,要查找的字符串,开始位置,出现位置)查找一个子字符串是否在指定的位置出现LENGTH(列|字符串)返回字符串长度LOWER(列|字符串)将字符串的内容全部转为小写UPPER(列|字符串)将字符串的内容全部转为大写REPLACE(列|字符串1,字符串2)使用字符串2替代字符串1SUBSTR(列|字符串,开始点i[,长度j])从字符串的第i个位置开始截取长度为j的子字符串2.数字类函数常用数字类函数函数语法函数功能ABS(n)返回n的绝对值CEIL(n)返回大于或等于数值n的最小整数EXP(n)返回e的n次幂,e2.71828183FLORR(n)返回小于或等于n的最大整数LOG(n1,n2)返回以n1为底n2的对数MOD(n1,n2)取模,即返回n1除以n2的余数POWER(n1,n2)返回n1的n2次方SIGN(n)若n为负数,则返回-1;若n为整数,则返回1,若n为0,则返回0COS(n)返回n的余弦值,n为弧度SIN(n)返回n的正弦值,n为弧度SQST(n)返回n的平方根,n为非负数ROUND(数字,[保留位数])对数字进行四舍五入,可以指定保留位数如果不指定,则表示将小数点之后的数字全部进行四舍五入TRUNC(数字,[保留位数])保留指定位数的小数,如果不指定,则表示不保留小数3.日期和时间类函数常用的日期和时间类函数函数语法函数功能ADD_MONTHS(d,i)返回日期d加上i个月之后的结果,其中i为任意整数LAST_DAY(d)返回d月份的最后一天MONTHS_BETWEEN(d1,d2)返回d1和d2之间月份数,若d1和d2的日期相同,或者都是该月的最后一天,则返回一个整数,否则返回一个小数NEW_TIME(d1,t1,t2)返回t1时区中的日期和时间 d1在t2时区中对应的日期和时间.其中d1时一个日期类数据,t1和t2是字符串SYSDATE返回系统当前的日期4.转换类函数常用转换类函数函数语法函数功能TO_CHAR(X[,format])该函数实现将表达式转换为字符串,format表示字符串格式TO_DATE(s[,format[lan]])该函数将字符串s转换成日期类型,format表示字符串格式,lan表示所使用的语言TO_NUMBER(s[,format[lan]])该函数将返回字符串s代表的数字,format表示字符串格式,lan表示所使用的语言日期格式化标记函数语法函数功能YYYY表示完成的年份Y,YYY带逗号的年,比如2,017YYY年的后三位YY年的后两位Y年的最后一位YEAR表示年份的文字,直接显示四位的年MONTH表示月份的文字,直接显示2位的月MM用两位数来表示月份DAY表示天数的文字DDD表示一年里的天数DD表示一个月里的天数D表示一周里的天数DY用文字表示星期几WW表示一年里的周数W表示一个月里的周数HH表示12小时制,小时是两位数字,使用2个HHHH24表示24小时制MI表示分钟SS表示秒,秒是2位数字,使用两个SSSSSSS午夜之后的秒数字表示AM|PM表示上午或下午5.聚合类函数常用聚合类函数函数语法函数功能AVG(x[DISTINCT|ALL])计算选择列的平均值,可以是一个列或多个列的表达式COUNT(x[DISTINCT|ALL])返回查询结果中的记录数MAX(x[DISTINCT|ALL])返回选择列中的最大数,可以是一个列或多个列的表达式MIN(x[DISTINCT|ALL])返回选择列中的最小数,可以是一个列或多个列的表达式SUM(x[DISTINCT|ALL])返回选择列中的数值总和,可以是一个列或多个列的表达式VARIANCE(x[DISTINCT|ALL])返回选择列中的统计方差,可以是一个列或多个列的表达式STDDEV(x[DISTINCT|ALL])返回选择列中的标准偏差,可以是一个列或多个列的表达式第八章完整性约束约束分为以下5种:非空约束:表示此字段内容不允许为空唯一性约束:此列的内容不允许出现重复主键约束:表示一个唯一的表示,具有唯一性和非空性外键约束:用来约束两个表中列之间的关系检查约束:用于自行编写设置内容的检查条件1.非空约束非空约束:限制必须为某个列提供值,在创建表结构时对于不许为NULL的列可以用NOT NULL标记修改为非空约束: ALTER TBALE 表名 MODIFY 列名 NOT NULL; 删除非空约束: ALTER TABLE 表名 MODIFY 列名 NULL;2.唯一性约束唯一性约束强调所在的列不允许有相同的值,但是允许为空;若要设置某一列为唯一性约束,通常使用CONSTRAINT....unique标记该列-- 添加唯一性约束 ALTER TABLE 表名 NOT CONSTRAINT 约束名 UNIQUE (列名1,列名2....); -- 删除唯一性约束 ALTER TABLE 表名 DROP CONSTRAINT 约束名;3.主键约束主键约束用于唯一地标识表中的每一行记录,在一个表中,最多只能有一个主键约束;主键约束既可以由一个列组成,也可以由两个或两个以上的列组成(联合主键);主键约束同时具有唯一和不为NULL的特点-- 添加主键约束 ALTER TABLE 表名 ADD CONSTRAINT 约束名 PRIMARY KEY (列名); -- 删除主键约束 ALTER TABLE 表名 DROP CONSTRAINT 约束名;4.外键约束外键是指一个表中含有与另一个表中的主键相同的列或列组,而另外一个表中被引用的列必须具有主键约束或唯一性约束-- 添加外键约束 ALTER TABLE 表名 ADD CONSTRAINT 约束名 FOREIGN KEY (列名) REFERENCES 约束名(列名); 注:若外键表的外键列与被引用表的被引用列列明一致可以省略 REFERENCES 后的内容 -- 删除具有外键约束的表 注: 1.删除被引用表的数据之前,应先删除外键表所对应的数据 2.删除被引用表之前,应先将外键表删除 DROP TABLE 表名 CASCADE CONSTRAINT; -- 删除外键约束 ALTER TABLE 表名 DROP CONSTRAINT 约束名;5.禁用和激活约束-- 禁用已经存在的约束 ALTER TABLE 表名 DISABLE CONSTRAINT 约束名 注:在禁用主键约束时,ORACLE会默认删除约束对应的唯一索引,而重新激活约束时,ORACLE会重新建立唯一索引;可以在约束名后放置 KEEP INDEX 实现删除约束时保留对应的唯一索引 -- 激活约束 ALTER TABLE 表名 ENABLE CONSTRAINT 约束名 -- 删除约束 ALTER TABLE 表名 DROP CONSTRAINT 约束名;第九章索引和视图创建索引的注意:1.索引应当建立在WHERE子句频繁引用的列上2.如果经常需要基于某列或某几行列执行排序操作,那么在这些列上建立索引可以加快数据排序速度3.限制表的索引个数,索引主要用于加快查询速度,但是会降低DML操作的速度4.指定索引块空间的使用参数.5.将表和索引部署到相同的表空间,可以简化表空间的管理;将表和索引部署到不同的表空间,可以提高访问性能.6.不要在小表上建立索引.7.为提高表链接的性能,应该在链接列上建立索引1.创建B树索引-- 创建索引语句 CREATE INDEX 索引名 ON 表名(列名);2.创建位图索引-- 创建位图索引 CREATE BITMAP INDEX 索引名3.建立反向键索引-- 建立反向键索引 CREATE INDEX 索引名 ON 表名(列名) REVERSE;4.合并和重建索引-- 合并索引 ALTER INDEX 索引名 COALESCE DEALLOCATE UNUSED; -- 重建索引 ALTER INDEX 索引名 REBUILD;5.删除索引DROP INDEX 索引名;6.显示索引信息-- 显示表的所有索引 SELECT index_name,index_type FROM DBA_INDEXES WHERE owner 模式名; -- 显示索引列 SELECT column_name,column_length FROM DBA_IND_COLUMNS WHERE index_name 索引名; -- 显示索引位置及大小 SELECT tablespace_name,segment_type,bytes FROM USER_SEGMENTS WHERE segment_name 索引名; -- 显示函数索引 SELECT column_expression FROM USER_IND_EXPRESSIONS WHERE index_name 索引名;第十章视图-- 普通视图 CREATE [or replace ] VIEW 视图名称 [(别名1,别名2....)] AS 子查询 -- 只读视图 CREATE [or replace ] VIEW 视图名称 [(别名1,别名2....)] AS 子查询 WITH READ ONLY; -- 管理视图 DESC USER_VIEWS; -- 修改视图定义 重点是 REPLACE CREATE OR REPLACE VIEW 视图名称 [(别名1,别名2....)] AS 子查询 -- 重新编译视图 ALTER VIEW 视图名 COMPILE; -- 删除视图 DROP VIEW 视图名;