Oracle 11g SQL 听课笔记(一)

这是本人听课记下的简单笔记，比较简陋，但觉得都是自己容易忘记和忽视的地方，不对的地方请指教。

本课程的PPT文档在资源下载中有

前言：概述
A relatinal database is a collection of relation or two-dimensinal tables.

 

SQL:Structured query language,非过程化语言
DML：select,insert,update,delete,merge
DDL:create,alter,drop,rename,truncate,comment
DCL:grant,revoke
Transaction control:commit,rollback,savepoint
解锁HR用户：Alter user hr account unlock;
启用HR用户：Alter user hr identified by hr;

第一章：Restricting Data Using the SQL SELECT Statment(select语句的基本用法)

三种查询功能：Projection(选择列),Selection(选择行),Join(多表连接)
SQL语句本身对大小写不敏感，可以写一行或多行，关键字不能缩写或跨行，一般一个子句放一行，在SQLPLUS中要加；或/结束执行语句
在SQLPLUS中，字符和日期型列是左对齐，数字型列是右对齐，都是大写显示
运算符：(+)Add,(-)Substract,(*)Multiply,(/)Divide
字符和日期型不能用*和/运算符，数字型都能用
Null指空值

ORACLE中，含有空值的表列长度为零

ORACLE允许任何一种数据类型的字段为空，除了以下两种情况：
1、主键字段（primary key），
2、定义时已经加了NOT NULL限制条件的字段
Null is a value that is unavailable,unassigned,unknown,or inapplicable
Null is not the same as zero or blank space
比较时使用is null 或is not null
对Null值做加、减、乘、除等运算操作，结果仍为Null值
字符串含Null值运算结果还是字符串本身

空值不能被索引，所以查询时有些符合条件的数据可能查不出来，在count(*)中，用nvl(列名,0)处理后再查

排序时比其他数据都大（索引默认是降序排列，小→大），所以NULL值总是排在最后

Null的处理使用NVL函数

列别名：column Alias
列别名在列名后用空格或as连接
列别名可以使用双引号保留大小写不变，也可以保留空格和特殊字符
用||连接字符串
Literal Character Strings使用单引号,即引用原意字符串
用DISTINCT(唯一的)去除重复列,多个列时是组合唯一
用describe即desc查询表结构

 

第二章：Restricting and Sorting Data(限制和排序数据)

where子句中一列名不能使用别名
where子句中的字符串值区分大小写，必须使用单引号
where子句中的日期型值默认格式是DD-MON-RR,必须使用单引号
大写函数:UPPER(字符串)：小写函数：LOWER(字符串)
where子句中的比较操作符：＝,>,>=,<,<=,<>(!=或^=),BETWEEN...AND...,IN,LIKE,IS NULL
between..and...中可以用数值和日期也可以用字符串
IN中可以用数值/字符串/日期

like模式匹配/通配符匹配
% 匹配零个或多个字符
_ 匹配一个字符
可以使用转义字符/来匹配含有%或_的字符,后面再加上ESACPE '/'

Logical Operators逻辑运算符
and,or,not 与,或,非

Rules of Precedence运算优先等级
算术运算符最高，逻辑运算符最低(not>and>or)
使用()优先级最高

order by排序 默认是升序,列名后加desc是降序,放在最后一条语句使用
在order by中可以使用别名或列名序号
可以多列(升序/降序)排序
NULL值在升序中排在最后,在降序中排在最前

substitution variables：替换变量
使用&variables_name或&&variables_name
在select,where,order by,column expressions,table names中使用
是客户端功能不是服务功能
只是简单的字符串替换,不检查是否正确
可以先使用DEFINE先定义变量,用UNDEFINE取消变量定义
set verify on/off来设置使用变量定义前后的提示

 

第三章：Using single-Row Functions to Customize Output(使用单行函数优化输出)
单行函数：Single-row functions,Return one result per row
多行函数：Multiple-row functions,Return one result per set of rows

字符串大小函数：Lower,upper,Initcap(单词首字母大写)
字符串操作函数：Concat,Substr,Length,Instr,Lpad|Rpad,Trim,Replace
CONCAT('Hello','World')=HelloWorld
SUBSTR('HelloWorld',1,5)=Hello
LENGTH('HelloWorld')=10
INSTR('HelloWorld','W')=6
LPAD(salary,10,'*')=*****24000
RPAD(salary,10,'*')=24000*****
REPLACE('JACK and JUE','J','BL')=BLACK and BLue
TRIM('H' FROM 'HelloWorld')=elloWorld

数字函数：ROUND,TRUNC,MOD
ROUND(45.936,2)=45.93
TRUNC(45.926,2)=45.92
MOD(1600,300)=100
DUAL表:DUAL is a dummy table that you can use to view results from functions and calcuations.

日期函数:默认格式:DD-MON-RR
sysdate is a function that returns:date and time
日期操作函数:MONTHS_BETWEEN,ADD_MONTHS,NEXT_DAY,LAST_DAY,ROUND,TRUNC
MONTTHS_BETWEEN('01-SEP-95','11-JAN-94')=19.6774194
ADD_MONTHS('31-JAN-96',1)='29-FEB-96'
NEXT_DAY(01-SEP-95','FRIDAY')='08-SEP-95'
LAST_DAY('01-FEB-95')='28-FEB-95'
ROUND('25-JUL-03','MONTH')='01-AUG-03'
ROUND('25-JUL-03','YEAR')='01-JAN-04'
TRUNC('25-JUL-03','MONTH')='01-JUL-03'
TRUNC('25-JUL-03','YEAR')='01-JAN-03'

第四章:Using Conversion Functions and Conditional Expressions(使用转换函数和条件表达示)

隐式转换函数:Implicit data type conversion
显示转换函数:Explicit data type conversion
三种显示转换函数:TO_NUMBER,TO_DATE,TO_CHAR
TO_CHAR(date,'format-model'),format_model使用单引号,区分大小写,包含任何有效的日期元素,加fm前缀消除前面的0
format_model中的有效元素:YYYY,YEAR,MM,MONTH,MON,DY,DAY,DD,HH24:SS AM
TO_CHAR(number,'format_model'),format_model中的有效元素:9,0,$,L,.,,
TO_CHAR(60000,'$99,999.00')=$60,000.00

TO_NUMBER(char[,'format_model'])
TO_NUMBER('-$12,345.67','$99,999.99')=-12345.67
TO_DATE(char[,'format_model'])
TO_DATE('July 4,2007','Month DD,YYYY')=04-JUL-07

Nesting Functions:嵌套函数

General Functions:通用函数
NVL(commission_pct,0),commission非空返回commission_pct,为空返回后面的0值
NVL2(comission_pct,a,b)commission非空返回第一个a值,空则返回b值
NULLIF(a,b)a和b相等返回null值,否则返回a值
COALESCE 从左到右判断n个参数,非空则返回此参数
DECODE(col|expression,search1,result1,search2,result2,...,default)

 

第五章:Reporting Aggregated Data Using the Group Functions(使用组函数产生聚合数据)

常见组函数:AVG,COUNT,MAX,MIN,STDDEV(标准方差),SUM,VARIANCE
COUNT(*)/COUNT(1)所有行,count(expr)所有非空行,count(distinct expr)去除非空重复行
AVG(expr)只计算非空的行,AVG(NVL(expr,0))计算所有行

在where子句中不能使用组函数
HAVING放在GROUP BY之后

 

第六章:DIsplaying data from Multiple Tables(多表查询)

Joins类型:Natural joins,Self-joins,Nonequijoins,Outer joins,Cross joins
Natural joins:NATURAL JOIN clause,USING clause,ON clause
Outer joins:LEFT OUTER JOIN,RIGHT OUTER JOIN,FULL OUTER JOIN
在from中给表取别名不能用as,在表名后用空格连接别名
在from中给表取别名后,在引用表名的地方必须使用表的别名

natural joins:自动寻找相同列名和类型的列(包括多列),并匹配连接
join...using(列名):用指定的列(相同列名就行)匹配连接,列名要用括弧引起来,using和之后的where子句中的列名不能加表前辍
join...on(列名1=列名2):on中的列名可以不相同,多个列匹配用and,两个以上的表用多个join...on

inner join:默认的join,只显示匹配上的数据
left(right) outer join:还显示出left(right)中没匹配上的数据
full join:显示出两边都没匹配上的数据
在oracle syntax中用(+)表示左连接和右连接,where中某列名后加(+)表示另外一列的数据全显示出来
笛卡尔乘积:a表和b表中所有列两两组合,a join condition is omitted,a join condition is invalid

 

第七章：Using Subqueries to Solve Queries使用子查询

子查询(subquery)即内查询(inner query),在外查询(outer query)前先执行,内查询的结果被外查询使用
子查询一般用括弧引起来,子查询放在比较符的右边,返回单(多)行结果用单(多)行操作符
单行操作符:=,>,>=,<,<=<>
子查询中也可以用组函数

在子查询中结果是null值的话,最后的结果也是null值
多行操作符:IN,ANY(放在=,!=,>,<,>=,<=后,子查询没有返回结果则是FALSE),ALL(放在!=,>,<,>=,<=后,子查询没有返回结果则是TRUE)
<any即小于最大值,>any即大于最小值,=any即为in
<all即小于最小值,>all即大于最大值,!=all即not in

 

第八章:
Using the Set Operators(使用set操作符/集合操作符)

Set Operators:union(去年重复行)/union all(包括重复行),intersect,minus即并,交,差
在使用集合操作符时,两个查询集合的列数要相同,秩序也一样,三个操作符等级一致
查询结果中列名为第一个查询集合中的列名,查询结果除使用union all外默认是按第一列升序排序的,即只有union all结果不排序

两个集合查询的列不匹配时可以补齐所需列,用to_char(null)补齐,也可以用常量凑齐
在组合查询中只能用一次order by,且放在语句最后,在order by中只能用第一个查询的列名或列名序号

 

第九章：
Manipulating Data(操作数据)

DML:Insert,Delete,Update(增删改)
Insert:INSERT INTO 表名[(列名1 [,列名2...])] VALUES (值1 [,值2...]);
如果插入数据完整的话，可以不用指定列，可以插入null值；插入部分值时，必须指定列名，则前后顺序一致,示指定列为其缺省值
在values中可以用函数或替换变量等；可以用select语句替代values，即批量插入加外一个表的内容
插入日期值时使用to_date('2009-01-16 11:12:40','yyyy-mm-dd hh24:mi:ss')类似的格式

Update: UPDATE 表名 set 列名1＝值1[,列名2＝值2,...] [where condition];
通过where子句指定更新范围，set column_name=null设置某列为null值
在set和where中可用子查询(单行)
Delete:DELETE [FROM] 表名 [where condition];
在where中指定删除范围，可以使用子查询
执行delete会产生回滚信息，不影响高水位(HWM)，删除整个表比较慢，可以用rollback回滚取消删除操作

Truncate:TRUNCATE TABLE 表名
执行truncate不会产生回滚信息，删除整个表比较快，没有where子句指定范围，执行truncate后rollback回滚不能取消删除操作,truncate执行速度比delete快

Database Transactions:事务
一个事务可以由一组DML语句或一个DDL语句或一个DCL语句组成
事务由第一条DML SQL语句开始，由COMMIT/ROLLBACK语句或DDL/DCL语句或在SQL Developer/SQL*PLUS中退出或系统CRASH后结束
在事务中间可以执行SAVEPOINT A/B/C...设置保存点，可以使用ROLLBACK TO SAVEPOINT A/B/C...回滚到保存点(即取消保存点后面的操作)，如果执行ROLLBACK/COMMIT则取消/提交整个事务，所有保存点失效

 

Implicit Transaction Processing隐含事务
隐含事务即自动提交：执行DDL/DCL语句或没有使用COMMIT or ROLLBACK就正常退出SQL Developer or SQL*Plus
如果非正常退出SQL Developer or SQL*Plus或系统崩溃则自动回滚，即取消所有操作
设置SET AUTO ON后在执行DML后可自动提交
在执行COMMIT之前在当前会话中用户可以用select看操作之后的结果，但其它会话/用户还看不到结果，即为读一致性

在SELECT语句中使用FOR UPDATE对查询结果进行行加锁，可以用COMMIT解锁，加锁后不能进行DML操作

 

第十章:
Using DDL Statements to Create and Manage Tables(使用DDL语句创建和管理表)

Database Objects:Table(表),View(视图),Sequence(序列),Index(索引),Synonym(同义词)...
表的命名规则:字母开头,最长30个字符,字符范围(A-Z,a-z,0-9,_,$,#),不包含Oracle保留字
CREATE TABLE [schema.]表名 (列名 数据类型 [DEFAULT option][,...]);
DEFAULT option(缺省值)是指在执行插入语句时的缺省值,为字母/表达式/SQL函数,不能是另外一列的列名,缺省值必须匹配此列的类型

列类型:VARCHAR2(size),CHAR(size),NUMBER(p,s),DATE,LONG(已过时),CLOB,RAW and LONG RAW(已过时),BLOB,BFILE,ROWID(内部)

CONSTRAINT(约束)保证数据的有效和完整,类型有:NOT NULL(非空值),UNIQUE(唯一值),PRIMARY KEY(主键),FOREIGN KEY(外键),CHECK(自定义约束条件)

如果某个约束只作用于单独的字段，即可以在字段级定义约束，也可以在表级定义约束，但如果某个约束作用于多个字段，必须在表级定义约束

在定义约束时可以通过CONSTRAINT关键字为约束命名，如果没有指定，ORACLE将自动为约束建立默认的名称,约束都保存在数据字典(在user_objects中以index类型保存)中
一个列可以定义多个约束
查询约束语句:select dbms_metadata.get_ddl('table','table_name') from dual;

定义约束时Oracle会自动创建对应的索引,可在user_objects中查询到约束名
NOT NULL指非空,UNIQUE指唯一值,可以为NULL值,即NULL值与NULL值不重复,PRIMARY KEY指非空且唯一值
一个表只能有一个主键约束,主键约束可以定义多个列
外键指子表的一列限定为父表中某列的内容,可为空值
定义外键时用references指定父表及列
定义外键时可设置on delete cascade或on delete set null,表示删除父表时,子表中对应行也删除或对应行所在列被设置为null值,不设置的话删除父表时会报错(ora-02292)

CHECK Constraint 检查约束
check(condition),condition中不能使用SYSDATE,UID,USER,USERENV等函数,可定义多个条件

通过子查询创建一个表:
CREATE TABLE 表名 [(列1,列2...)] AS subquery;
指定列时前后列的数量要匹配,未指定列名时,子查询中的列名会被使用到新表中来,可在子查询中定义别名来引用到新表中
原表中的约束不会传递到新表中,除not null外

ALTER TABLE:增加新列,修改列,定义新列的缺省值,删除某列,重命名列名,改变表到只读状态(read only)...
ALTER TABLE 表名 READ ONLY;ALTER TABLE 表名 READ WRITE;
改变到只读状态后不允许进行增删改,但可以drop掉

DROP TABLE:删除整个表,10g后是放在recycle bin(回收站)中,recycle bin默认开启

第十一章:
Creating Other Schema Objects(创建表以外的其它模式对象)

Schema(模式)：模式是一系列逻辑数据结构或者对象的集合。一个数据库用户拥有一个模式，模式的名字和数据库用户的名字相同。每个用户有一个单独的模式，用户创建的对象放在自己的模式中，这些对象叫模式对象。

VIEW(视图):实际上存储是查询语句(物化视图除外),使用时数据从基表中查询,即现用现取,对视图的操作即是对基表的操作
视图作用:限制数据的访问,简化复杂语句,保持数据的独立性,同样的数据展示不同的视图
Simple Views/Complex Views:简单视图/复杂视图
简单视图基于一张表,不包含函数,不包含组数据,可增删改数据(指基表中的数据)即DML操作
复杂视图基于一张以上的表,包含函数,包含组数据,如果使用了组函数或group by子句或表达示定义列或关键字DISTINCT,ROWNUM等则不能进行DML操作
通过CREATE OR REPLACE VIEW可创建视图也可修改视图

ROWNUM伪列,表的序号列
基表中的列如果约束了NOT NULL则不能对视图中对应的列进行修改
创建视图时可在后面加上WITH CHECK OPTION选项,对where子句的条件进行约束,即修改视图时必须满足where子句的条件,可保护视图数据的完整性
可加上WITH READ ONLY选项定义视图只读
删除视图:DROP VIEW 视图名;并不是删除基表

Sequences:独立的类型,数值型,自动产生唯一的数字,共享的对象,主要作用是产生唯一的关键值,可在MEMORY中缓存
CREATE SEQUENCE sequence_name
       [INCREMENT BY n]
       [START WITH n]
       [{MAXVALUE n | NOMAXVALUE}]
       [{MINVALUE | NOMINVALUE}]
       [{CYCLE | NO CYCLE}]
       [{CACHE n |NOCAHCE}];

使用NEXTVAL,CURRVAL进行调用sequences,即sequence_name.nextval,sequences.currval
在当前会话中使用CURRVAL调用前先用NEXTVAL对sequences进行调用,使用NEXTVAL自动增加产生一个序号
ALTER SEQUENCE sequence_name,修改sequence,不能修改起始值
DROP SEQUENCE sequence_name,删除sequence

 

Indexes索引:可以提高查询速度,和被索引的表相互独立,索引被Oracle自动使用和维护
使用PRIMARY KEY OR UNIQUE后会自动创建索引
手工创建:CREATE [UNIQUE] [BITMAP] INDEX index_name ON 表名(列1[,列2]...);
建议使用的情况:某些列的值非常分散,某些列包含大量NULL值,某些列在where子句中被一起经常使用,表很大而且查询量比较大...
不建议使用:某列很少被使用,表很小,查询量小,被经常更新,列经常作为表达式的一部分...
DROP INDEX index_name;删除索引

Synonym同义词:可以创建某个对象的引用
CREATE [PUBLIC] SYNONYM synonym_name(别名) FOR object;
DROP SYNONYM synonym_name;删除同义词