51CTO下载-Oracle 11g学习笔记基础篇
基本语法
2011年8月16日
下午 01:18
SQL语句中不区分大小写，习惯性把关键字用大写表示，其他用小写。
在Oracle里字符串数组小标都是从1开始。
在SQL *PLUS中必须用“;”结束语句，也可以用“/”，否则只会当作换行。
null值表示未知状态，但不代表0或者空。当null跟算术表达式一起就等于null。当null与逻辑运算表达式一起就等于空。
语句类型
DML:INSERT,UPDATE,DELETE
DDL: 对表的管理操作
DCL:设置数据库用户，权限等
主要数据类型
CHARACTER类型
CHAR
VARCHAR2
LONG
NUMBER类型
NUMBER(p,s) p代表数的模型（有没有小数点之类），s代表位数
DATE类型
RAW类型和LONG RAW类型
用于存储二进制数据
LOB类型
CLOB 超级long字符集，最长达到4G
BLOB 二进制字符集，最长达到4G
BFILE 指针，指向多媒体文件
ROWID 基于64位操作系统设定每个表的都有一列作标记每一行
数据库的对象：
Table 表
View 视图（一般只读），存储查询语句，现用现查
Sequence 序列
Index 索引
Synonym 别名
Procedure 存储过程
Trigger触发器
数据库命名规范
1.必须以字母开头
2.最大长度30字符
3.只能按有A-Z,a-z，0-9,_,$和#(通常不用$和#)
4.相同类型的数据名称不能重复
5不能包含oracle的关键字
数据定义命令
CREATE命令
ALTER命令
DROP命令
TRUNCATE命令
数据操纵命令
INSERT
SELECT
UPDATE
DELETE
事务处理和数据控制命令
事务处理控制命令
COMMIT
SAVEPOINT
ROLLBACK
数据控制语言
GRANT
RREVOKE
语句优先级从高到低：
SELECT——FROM——WHERE——GROUP BY——组合函数——HAVING——ORDER BY WHERE,GROUP BY,HAVING语句中都不能使用别名
select语法
SELECT * FROM Table
创建现有表的副本
CREATE table
插入来自其他表达记录
INSERT INTO 为列显示不同名称 SELECT column_name 可以使用算术表达式,只有数字才能使用乘除法 例如： Select last_name,salary,3*salary +300 From employees 在程序查询中为了提高速度通常不用*，而是把表中所有的字段名罗列出来 SQL *PLUS中的运算符 算术运算符 用于执行数学计算，+，-,*,/ 比较运算符 用于将一个表达式与另一个表达式进行比较。=,!=,<,>,<=,>=,IN,LIKE,IS NULL,BETWEEN,NOT BETWEEN BETWEEN a（包含a）AND b（包含b） 判断某值包含在一组集合里 LIKE模糊查询 LIKE '' %——代表多个字符 _——代表单个字符 例如：查找以S开头 例如：查找第二字字母是‘o’ SELECT last_name FROM employee 如果查找字段本身包含_或者%，要用\_或者\%.(|_或|%也可以） 判断字段中是否有NULL SELECT last_name,managere_id FROM employees WHERE manager_id IS NULL 不含有NULL SELECT last_name,managere_id FROM employees WHERE manager_id IS NOT NULL 逻辑运算符 用于合并两个条件的结果以产生单个结果。AND,OR,NOT。 集合运算符 用于合并两个独立查询结果。UNION,UNION ALL,INTERSECT,MINUS 连接运算符 用于将两个或多个字符串并在一起。||表示连接运算符。 例如： SELECT last_name || job_id AS "Employees" FROM employee 表达式优先级 序号表达式 1算术表达式 2链接表达式 3比较表达式 4IS[ NOT] ,NULL,LIKE,[NOT] IN 6不等于 7NOT 8AND 9OR WHERE语句条件判断 SELECT * FROM table WHERE department=90(当使用了WHERE就不能使用别名） 用WHERE使用查询字符串内容时，需要用''，例如WHERE hire_date = '17-FEB-96' 当不能确认查询内容的大小写可以用以下两种方法来进行查询 1.WHERE UPPER(last_name) = 'WILL' 将查询字段的内容全部转换成大写再对比，其字段内容自身不发生改变 2.WHERE LOWER(las_name) = 'will' 将查询字段的内容全部转换成小写再对比，其字段内容自身不发生改变 Aliases列的别名 保留别名的大小写或别名中含有其他非英文数字的符号，需要用双引号""包起来。 例如： SELECT last_name AS name FROM employee 或者： SELECT last_name name FROM employee Literal Charcter Strings字符串 使用单引号'' 例如： SELECT last_name || ' is a '||job_id AS "Employee Deails" FROM employee Alternative Quote Operator 引用操作符 如果要输出显示关键字或操作符的内容，需要用q'[]'包起来，也可以用感叹号，要成对。 例如： SELECT department_name || ' Department' || FROM departments 显示不重复的列 SELECT DISTINCT department_id,tv,vc FROM employees DESCRIBE 显示数据表的基本结构（不是Oracle的语句，是SQL PLUS的命令） 不能显示主键，外键 DESC[RIBE]tablename ORDER BY 排序语句（默认升序） 可以使用别名。ASC升序，DESC降序。排序中升序时NULL在最后，降序时NULL在最前。可以用NULL FIRST或者NULL LAST修改NULL的位置。 例如： SELECT employee_id,last_name,salary*12 annsal FROM employees ORDER BY annsal 按照第3个字段排序，在这个例子中是按照department_id排序 SELECT last_name,job_id,department_id,hire_date FROM employees ORDER BY 3 按照两种方式排序： 例：按departmen_id升序排列并且按salary降序排列 SELECT last_name,department_id,salary FROM employees ORDER BY deparment_id,salary DESC Substitution Variables变量（客户端独有） &变量名，即使变量名相同，每次引用都是新的变量，也就是多次引用，类似形参&&变量名，同一个变量可以多次使用 若变量为字符串的时候必须用'',如'&变量名'或者'&&变量名' DEFINE定义常量，反之UNDEFINE DEFINE employee_num = 200 可以被同名变量直接引用 VERIFY指令查看变量改变情况 SET VERIFY ON 显示变化，SET VERIFY OFF不显示变化 GROUP BY分组 用于将信息划分为较小的组，每一组返回针对每组的单个结果。语句中不能使用别名.ORDER BY只能在复合查询中使用一起，并且在最后才使用。 语法 SELECT colum,group_function(column) FROM table [WHERE condition] [GROUP BY group_by_expression] [ORDER BY column] 例子：（进行两次分组） SELECT department_id dept_id,job_id,SUM(salary) FROM employees GROUP BY department_id,job_id ORDER BY department_id HAVING语句条件判断 用于指定针对行的某些条件，它限定组中的行。语句中不能使用别名 语法： SELECT column,group_function FROM table [WHERE condition] [GROUP BY group_by_expression] [HAVING group_condition] [ORDER BY column] ROLLUP ROLLUP(column_name1,column_name2) 结果集包含非分组行和小计行 CUBE CUBE(column_name1,column_name2,column_name3) 产生交叉表格报表 临时表 只在用户会话期间存在的表。数据在用户事务处理期间持续存在，使用CREAE GLOBAL TEMPORARY TABLE命令，不会自动获得空间，插入的值仅在此特定会话中可用。 单行函数 对每一行返回一个值，也成为标量函数，在SELECT 和WHERE 子句中使用Function_name*(arg1,arg2,…..)+ 函数名称 结果LOWER('SQL Course')sql course UPPER('SQL Course') SQL COURSE INITCAP('SQL Course')Sql Course 字符函数函数名称 结果解释CONCAT('Hello','World') HelloWorld 合并字符串 SUBSTR('HelloWorld',1,5)Hello 从第1个字母开始抽5个字母LENGTH('HelloWorld')10求字符串的长度 INSTR('HelloWorld','W')6查找首次出现w 的位置返回小标 LPAD(salary,10,'*') *****24 右填充*使得字符串是10个字符 RPAD(salary,10,'*')24000*****左填充*使得字符串是10个字符 REPLACE('JACK and JUE','J','BL') BLACK and BLUE 把字符串里的J替换成BL TRIM('H' FROM 'HelloWorld')ehlloWorld 去掉H和空格 数字函数函数名称结果解释 ROUND(45.925,2)45.93保留小数点后2位TRUNC(45.926,2)45.92保留2位有效数字MOD(1600,300) 100求余数 基本函数 2011年8月17日上午 10:41 日期函数（查看当前日期） SELECT sysdate FROM dual 例子：计算员工在公司呆了多少周 SELECT last_name,(SYSDATE-hire_date)/7 AS WEEKS FROM employees WHERE department_id = 90 函数名称结果解释 MONTHS_BETWEEN（'01-SEP-95','11-JAN-94')19.677419两个日期相差多少个 ADD_MONTHS('31-JAN-96')29-FEB-96加多个月 NEXT_DAY('01-SEP-95','FRIDAY')08-FEB-95指定时间的下一个星 期几 LAST_DAY('01-FEB-95')28-FEB-95某月的最后一天 假设现在的日期是：SYSDATE = '25=JUL-03' 函数名称结果 ROUND(SYSDATE,'MONTH')01-AUG-03 ROUND(SYSDATE,'YEAR')01-JAN-04 TRUNC(SYSDATE,'MONTH')01-JUL-03 TRUNC(SYSDATE,'YEAR')01-JAN-03 转换函数 CHAR定长字符串 VARCHAR可变长度字符串 VARCHAR2优化了VARCHAR TO_CHAR(date,'format_model')日期转字符串 例如：SELECT employee_id,TO_CHAR(hire_date,'MM/YY') Month_Hired FROM employees WHERE last_name ='Williams' Format_model写成fmYY/MM,含有fm则可以去掉月份或者日期为个位数的时候出现的0 日期格式描述 YYYY年份全部数字表示 YEAR年份英文全称 MM月份数字表示 MON月份首3个字母 DY星期首3个字母 DAY星期英文全称 DD日期数字表示 时间格式例子 HH24:MI:SS AM15:45:32 PM DD 'of' MONTH12 of OCTOBER ddspth fourteenth TO_CHAR(number，'format_model')时间转字符串 格式描述 90-9 00 $$ L设置本地字符集 .. ,3位数一个逗号 DD日期数字表示 当format_model限制的长度不够时，显示结果会出现# TO_NUMBER(char[,'format_model'])字符串转数字 TO_DATE(char[,'format_model'])字符串转日期 fx精确转换，格式必须保持一致 TO_CHAR和TO_DATE都可以使用RR格式代表年份，RR代表的是最近世纪的年份，例如:现在是2011年，如果日期是12-JAN-88，则是1988年1月12日；如果日期是12-JAN-20,则日期是2020年1月12日 嵌套函数 F3(F2(F1(col,arg1),ag2,arg3) 常规函数 NVL(expr1,expr2) 如果expr1是空，则返回expr2 NVL2(expr1,expr2,expr3) 如果expr1是非空，则返回expr2；如果expr1是空，贼返回expr3 NULLIF(expr1,expr2) 如果expr1等于expr2则返回NULL；如果expr1不等于expr2，则返回expr1 N个参数中，遇到第一个非空则返回谁 条件函数（对多种可能性判断） CASE CASE expr WHEN compariso_expr1 THEN return_expr1 [WHEN compariso_expr2 THEN return_expr2 WHEN compariso_exprn THEN return_exprn ELSE else_expr] END DECODE DECODE(col|expresssion,search1,result1 *,search2,result2,…, ,default]) 其他函数 UID,USER,VSIZE 组合函数(GROUP BY关键字） 对多行操作。忽略列中的NULL值。 不能在WHERE语句中使用组合函数。 语法格式： SELECT group_function(column),… FROM table [WHERE condition] [ORDER BY column] 函数名称注释 AVG平均值 COUNT计算行数 MAX最大值 MIN最小值 STDDEV标准差 SUM求和 VARIANCE求方差 Count函数 DISTINCT expr 不计算重复行 ALL expr 计算所有行（默认） 例子：工资大于10000的部门 SELECT department_id,MAX(salary) FROM employees HAVING MAX(salary)>10000 例子：按职位排列求职位工资大于13000 SELECT job_id,SUM(salary) PAYROLL FROM employees WHERE job_id NOT LIKE '%REP%' GROUP BY job_id HAVING SUM(salary)>13000 ORDER BY SUM(salary) 多表查询 2011年8月18日 下午 04:36 SQL1999国际标准 SELECT table1.column,table2.column From table1 [NATURAL JOIN table2]| [JOIN table2 USING (column_name)]| [JOIN table2 ON (table1.column_name = table2.column_name)]| [LEFT|RIGHT|FULL OUTER JOIN table2 ON (table1.column_name = table2.column_name)]| [CROSSS JOIN table2] Oracle标准 SELECT table1.column,table2.column FROM table1,table2 WHERE table1.column1 = table.column2 例子： SELECT employee_id,last_name,e.department_id,e.department_id,department_name FROM employee e,departments d （不能用AS） WHERE e.department_id = d.department_id 如果已经给表定义了别名，使用表的时候必须用表的别名。 内连接 表之间的字段信息有关系，等于或者不等于。 Natural Join自然连接 两张表中必须至少含有一个相同字段并且数据类型相同，才能组合在一起 例： （SQL标准） SELECT department_id,department_name,location_id,city FROM departments NATURAL JOIN locations （Oracle标准） SELECT d.department_id,d.department_name,d.location_id,l.city FROM departments d,locations l WHERE d.location_id = l.location_id 例： 两张表中有相同的字段但数据类型不同，或者指定按表中的一个字段进行连接。 （SQL标准) SELECT employee_id,last_name,location_id,department_id FROM employees JOIN departments USING(department_id) USING字句语法：USING()引用的列，在WHERE里面使用不能加表前缀。 （Oracle标准） SELECT d.department_id,d.department_name,d.location_id,l.city FROM departments d,locations l WHERE d.location_id = l.location_id; 例： 两张表中没有相同的字段的连接 （SQL标准) SELECT e.employee_id,https://www.wendangku.net/doc/1f10370627.html,st_name,e.department_id,d.department_id,d.location_id FROM employees e JOIN departments d ON (e.department_id = d.department_id) (Oracle标准） SELECT e.employee_id,https://www.wendangku.net/doc/1f10370627.html,st_name,e.department_id,d.department_id,d.location_id FROM employees e,departments d WHERE e.department_id = d.department_id; 多表连接例(三张表)： （SQL标准） SELECT emploee_id,city,department_name FROM employees e JOIN departments d ON d.department_id = e.department_id JOIN locations l ON (d.location_id = l.location_id) (Oracle标准) SELECT employee_id,city,department_name FROM employees e,departments d,locations l WHERE e.department_id = d.department_id AND d.location_id = l.location_id 条件性JOIN连接 例： （SQL标准） SELECT e.employee_id,https://www.wendangku.net/doc/1f10370627.html,st_name,e.department_id,d.department_id,d.location_id FROM employees e JOIN departments d ON (e.department_id = d.department_id) AND e.manager id = 149 //或者WHERE e.manager_id = 149 (Oracle标准) SELECT d.deparment_id,d.department_name,l.city FROM departments d,locations l WHERE d.location_id=l.location_id AND d.department id IN(20,50) 自连接 例： 例： （SQL标准） SELECT https://www.wendangku.net/doc/1f10370627.html,st_name emp,https://www.wendangku.net/doc/1f10370627.html,st_name mgr FROM employees worker JOIN employees manger ON (worker.manager_id = mgr.employee_id) （Oracle标准） SELECT https://www.wendangku.net/doc/1f10370627.html,st_name || 'works for' || https://www.wendangku.net/doc/1f10370627.html,st_name FROM employees worker,employeees manager WHERE worker.manager_id = manager.employee_id Nonequijoins连接（范围比较后，再连接） 例： （SQL标准） SELECT https://www.wendangku.net/doc/1f10370627.html,st_name,e.salary,j.grade_level FROM employees e JOIN job_grades ON e.salary BETWEEN j.lowest_sal AND j.highest_sal （Oracle标准） SELECT https://www.wendangku.net/doc/1f10370627.html,st_name,e.salary,j.grade_level FROM employees e JOIN job_grades WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal 外连接 表之间的字段存在没有直接联系，即没有等于或者不等于的关系。 左外连接 左表所有记录都显示 （Oracle标准） SELECT table.column,table2.column FROM table1,table2 WHERE table1.column = table2.column(+) 例： （SQL标准） SELECT https://www.wendangku.net/doc/1f10370627.html,st_name,e.department_id,d.department_name FROM employees e LEFT OUT JOIN departments d ON (e.department_id = d.department_id) （Oracle标准） SELECT https://www.wendangku.net/doc/1f10370627.html,st_name,e.department_id,d.department_name FROM employees e,departments d WHERE e.department_id = d.department_id(+) 右外连接 右表所有记录都显示 （Oracle标准） SELECT table.column,table2.column FROM table1,table2 WHERE table1.column(+) = table2.column 例： （SQL标准） SELECT https://www.wendangku.net/doc/1f10370627.html,st_name,e.department_id,d.department_name FROM employees e RIGHT OUT JOIN departments d ON (e.department_id = d.department_id) （Oracle标准） SELECT https://www.wendangku.net/doc/1f10370627.html,st_name,e.department_id,d.department_name FROM employees e,departments d WHERE e.department_id(+) = d.department_id 全外连接 左右表所有记录都显示 例： （SQL标准） SELECT https://www.wendangku.net/doc/1f10370627.html,st_name,e.department_id,d.department_name FROM employees e FULL OUT JOIN departments d ON (e.department_id = d.department_id) 笛卡尔乘积(CROSS JOIN) 第一张表的所有记录和第二张表的所有记录组合起来，n*m组合。产生的原因： 1.内连接或外连接被忽略了 2.内连接或外连接无效 3.随意两张表两两组合，产生大量记录（用于测试） 例： (SQL标准) SELECT last_name,department_name FROM employees CROSS JOIN departments (Oracle标准) SELCET last_name,department_name FROM employees,departments 高级查询 2011年8月19日 下午 02:07 子查询（复合查询） 语法： SELECT select_list FROM table WHERE expr oprator (SELECT select_list FROM table) 黑色部分为外部查询，绿色为内部查询。通常内部查询只出现在外部查询的FROM,WHERE,HAVING子句中。 先执行内部查询再执行外部查询。 例： SELECT last_name,salary FROM employees WHERE salary> (SELECT salary FROM employees WHERE last_name = 'Abel') 单行子查询 例： SELECT last_name,job_id FROM employees WHERE job_id= (SELECT job_id FROM employees WHERE employee_id=141); 例： SELECT last_name,job_id,salary FROM employees WHERE salary= (SELECT MIN(salary