Oracle11gR2_SQL+教程

Oracle11g SQL SQL语句类别Select（DQL）insertupdatedeletemergeDMLcreatealterdroprenametruncatecommentDDLgrantrevokeDCLcommit rollback savepoint transaction control*：表示非空o：表示可以为空#*：表示主键（主键必须非空）Sql DeveloperSqlplus1Select语句（Data Retrieval Using the SQL SELECT Statement）使用SQL的select语句来检索数据。

1.1Select语句基础Capabilities（能力）：从一张表或多张表中查询数据。

Keywords（关键字）：select、from……Clauses：注意：应用程序中，列出所需查询的所有列，以提高效率，不要使用“*”。

写SQL：SQL语句大小写不敏感。

（规范和风格：keyword大写，列名小写）可写在一行，也可写在多行。

Keywords不能缩写，不能跨行。

Clauses（子句）通常单写一行。

可以用缩进增强可读性。

在SQLPLUS中，以“分号”结束，或另起一行打“/”。

结果集中，缺省的列头：全部大写。

字符和日期型左对齐。

数字右对齐。

（可修改）1.2算术表达式（arithmetic）和NULL算术表达式（arithmetic expressions）：加减乘除+，-，*，/ （字符串只可使用+,-）用于From子句外的所有子句使用括号改变优先级NULL值：unavailable, unassigned, unknown, or inapplicable不等于0或空字符串算术表达式中有Null参与，结果既是null1.3列别名（column alias）结果集中，重命名一列。

方便引用。

空格或as隔开列名和别名。

如果别名不想大写，或别名中有空格、特殊符号或关键字时，可加双引号扩起别名。

1.4连接操作符（concatenation operators），DISTINCTconcatenation operators：连接一列和字符串或其他列。

“||”，列连接符。

结果集中的该列，是字符表达式。

例如：select department_name ||' department' || q'<'s manager ID : >'|| manager_id from departments; select department_name ||' department' || q'['s manager ID : ]'|| manager_id from departments; select department_name ||' department' || q'{'s manager ID : }'|| manager_id from departments; select department_name ||' department' || q'('s manager ID : )'|| manager_id from departments;注意：以上语句，SQLPLUS中可以执行，PL/SQL developer中不能执行。

DISTINCT取消重复行。

DISTINCT后面所有列的组合要唯一。

1.5字符串字面语义（literal character string）Select语句中的常值，用单引号扩起，每行都会输出该值。

literal character string：数字，字符，日期等常量。

单引号扩起。

每行输出一次该常量。

1.6引用操作符alternative quote(Q) operator:需要在字符串中使用单引号时，可用[],<>,{}。

q'<'s Job>'1.7DESC指令描述表结构。

Desc table_name2过滤和排序数据（Restricting and sort data）过滤、排序包含：Where子句筛选Order by 子句排序替代变量2.1使用where子句Where字句用到多种操作符：比较操作符逻辑操作符Between …and…InLikeIs null注意：where子句是不能使用别名的。

String是大小写敏感的。

Where upper(last_na me) = …WILSON‟ 、lower（）2.1.1使用比较操作符（comparision operator）大于、小于、等于…=,<,<=,>,>=,!=(<>,^=)（==是不能使用的）Select * from table1 where a>02.1.2Between…and…Select * from table1 where a between 0 and 100Between….and….也可以比较字符串2.1.3InIN效果等同于where…or…or…，但效率稍微逊色。

2.1.4Like通配符：“%”，通配任意个字符例如：Date like ‟%95‟通配符：“_”，表示通配一个字符例如：name like …_a%‟，(表示查询name列里，第二个字符为a的记录) 2.1.5Is null该列为空值Where a is not null2.1.6使用逻辑操作符（logical operators）AndOrNot 返回条件失败的记录2.1.7转义Escape例如：Where job like …_a\_%‟ escape …\‟2.2使用order by 子句ASC [nulls last]DESC [nulls first]Order by 3 --表示select后面第三列。

升序null值默认放在最后。

也可以按照多列排序。

2.3替代变量(Substitution variables)使用&或&&来声明替换变量使用范围：Where条件Order by子句列表达式表名全部select语句输入字符串时，需用单引号扩起。

例如：WHERE JOBID = '&V_NAME'注意：类似于C中的宏定义，只是简单的替换。

使用&&号，简化多次输入。

使用定义指令。

例如：DEFINE V_NAME = 200Select 语句UNDEFINE V_NAMEVerify开关：（显示变量替换过程）Set Verify onSet Verify off3单行函数（Single-Row Functions）3.1函数（Functions）单行函数（Single-Row Functions）输入一行，输出一行多行函数（Multiple-Row Functions | Aggregate Functions）输入多行，输出一行3.2字符函数Character Functions3.2.1大小写转换函数（case-conversion）Upper()：大写Lower()：小写Initcap()：单词首字符大写3.2.2字符操作函数（character manipulation functions）LENGTH()CONCAT()SUBSTR()INSTR()LPAD | RPAD：填充TRIM：除去REPLACE：替换3.3数字函数（Number Functions）RoundTruncMod：求余数3.4日期函数（Date Functions）MONTHS_BETWEEN()ADD_MONTHS()LAST_DAY()NEXT_DAY()SYSDATE()ROUND()TRUNC()RRRR格式年份算法：4转换函数和条件表达式（Using Conversion Functions and Conditional Expressions）4.1隐式和显式数据类型转换（Implicit and explicit data typeconversion）4.1.1隐式（Implicit）Oracle内部自动转换：数值插入字符串列字符串类型数字插入数值列日期字符串插入日期列……等注意：尽量使用显式转换，方便阅读，另外隐式转换容易报错。

Select…… where hire_date > ‟01-JAN-90‟4.1.2显式（explicit）Oracle提供的转换函数。

4.1.2.1to_char,to_date,to_number函数to_char:TO_CHAR(nchar | clob | nclob)TO_CHAR({ datetime | interval } [, fmt [, 'nlsparam' ] ]) TO_CHAR(n [, fmt [, 'nlsparam' ] ])注：fm去掉前导零to_date严格转换+fxto_number4.2函数嵌套（Nesting functions）4.3通用函数（General functions）NVL（expr1,expr2）1空返2NVL2(expr1,expr2.expr3) 1空返3NULLIF(expr1,expr2)12等返null，不等返1COALELCE(expr1,expr2,…,exprn)从左数，返回第一个非null4.4条件表达式（Conditional expressions）4.4.1CASESql国际标准中的条件表达式case exp1 when exp2 then exp3when exp4 then exp5……else expnend4.4.2DECODEOracle特定decode(exp1,serch1 ,return1 ,serch2 ,reruen2 ,……default)5使用组函数产生聚合数据（Reporting Aggregated Data Using the Group Functions）5.1组函数（Group functions）多行输入，返回一行类型和语法（Types and syntax）Stddev（）取标准方差使用avg、sum、min、max、count使用distinct关键字在组函数中Count(distinct col)组函数中的null值，组函数不计有null的记录Avg（nvl（com，0））5.2Group子句使用group by 子句，把表分成更小的组。