Oracle 10G sql 培训

本文章通过oracle 10g 11g自带的HR用户下的7张表，来学习SQL基础知识。
在安装数据库时，勾选相应的选项，就会安装HR用户下练习使用的7张表。
如果你的oracle数据库没有HR用户，可以下载本文附件中的工具包来安装。

官方联机文档下载地址,400多M吧。
http://www.oracle.com/technology/documentation/index.html

--------------------------------------基本准备-------------------------------------------------------
使用sqlplus连接数据库：
1 本地主机连接远端的oracle数据库，本地主机要安装oracle客户端，输入如下命令
sqlplus 用户名/密码@网络服务名
网络服务名是什么？其实就是本地主机安装oracle客户端时配置远端数据库的别名，在Oracle Net Manager中叫“网络服务名”， 在sqlplusw中叫“主机字符串”。

2 本地主机连接远端的oracle数据库成功后，切换到其实用户
SQL> conn scott/tigger@网络服务名
SQL> conn  sys/sys的密码@网络服务名 as sysdba

3 连接本机的oracle数据库，输入如下命令
sqlplus /nolog  
conn / as sysdba

4 连接本机的oracle数据库成功后，切换到其实用户
SQL> conn scott/tigger;


常用SQL/PLUS命令
查看一行显示字符数：SQL> show linesize;   （简写：show lines）
设置显示宽度为200字符：SQL> set linesize 200; (简写：set lines 200)
查看目前的pagesize,默认是14：show pagesize;
将pagesize设置好100：set pagesize 100;

常用PL/SQL命令
查看表的结构（详细）： select dbms_metadata.get_ddl('TABLE','大写表名') FROM dual;
查看表的结构（简单）： desc 表名
查看当前数据库登录操作的用户 ：show user

常 用字典、包
查看数据库有哪些用户从“用户字典”（注意权限）：select username,account_status from dba_users;
查看当前用户有哪些表：select table_name from user_tables;
查看当前用户的对象：select object_name,object_type from user_objects;

解锁scott帐户
alter user scott account unlock; //解锁帐户
grant connect,resource,unlimited tablespace to scott; //分配权限
alter user scott identified by tiger ; //设置密码

Oracle sqlplus的清屏
如果是通过本身的sqlplus那么方法有：
同时按SHIFT和DELETE键然后点OK、clear screen ；或 clea scre；实现

如果是通过cmd模式进入的话：
可以通过host cls命令来实现清屏

----------------------------------------------------Schema-------------------------------------------------
HR用户下的7张表 Schema

表的说明：
employees 员工表
departments 部门表（员工所在的部门）
locations  地址表（部门在哪个地址）
countries 国家表（地址在哪个国家）
regions   大区表（亚太区，北美区）  （国家在哪个大区）
job         工作岗位表 （员工的工作岗位）
jbo_history  工作变动历史表

----------------------------运算符部分------------------------------
=,>,<,>=,<=,<>,
between ...  and ...  (两端包含)
in() , not in()
like '%磊_'  ， %配置多个字符   ， _ 配置一个字符。  like '%磊\_' escape '\'   ,说明\是转义字符，可以查询结果中包含_。
is null
逻辑运算符：and , or , not

----------------------------子句部分------------------------------
select子句
from子句
where子句
order by 子句， 结果有null值时，升序时null放在结果的最后面，降序null值放在结果的最前面。可以修改：order by id desc nulls last/first;
group by 子句
having 子句
for update子句

---------------------------函数部分-------------------------------
字符函数：
UPPER('String') 转换成大写字母
LOWER('String') 转换成小写字母
INITCAP('String')转换为首字母大写，其它小字
SUBSTR('HelloWorld',1,5)  结果是：Hello。参数说明：('HelloWorld',1,5)意思是截取'HelloWorld'串从第1个开始，取5个。SQL中“第一”是从1开始的。
INSTR('HelloWorld','W')     结果是：6
replace('JACK and JUE','J','BL') 替换，把'J' 替换成'BL'。
trim() 去空格

数学函数：
ROUND(45.926,2)   结果：45.93  保留两位小数，四舍五入。
ROUND(45.926,-1)  结果：50
TRUNC(45.926,2)    结果：45.92  保留两位小数，多余部分直接截去，无四舍五入。
TRUNC(45.926,-1)   结果：40
MOD(1600,300)      结果：100     取余数

日期函数：
SQL> select sysdate from dual;    结果是：23-7月 -10       sysdate是一个无参数的函数。

转换函数：  (隐式转换，显示转换)
---- 日期转字符
SQL> select to_char(sysdate,'YYYY-MM-DD') as date_ from dual;   结果：2010-07-23
SQL> select to_char(sysdate,'fmYYYY-MM-DD') as date_ from dual;    结果：2010-7-23  fm作用：去掉前导0，07月显示为7月。
SQL> select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') as date_ from dual;  结果：2010-07-23 12:06:19

---- 数字转字符
SQL>   select to_char(100,'L99,999.00') from dual;  结果：￥100.00   以本地货币格式化成字符串。

---- 字符转成数值
TO_NUMBER('100')

---- 字符转成日期
TO_DATE('2009-11-01','YYYY-MM-DD')

null相关函数：  相当于if else
NVL(name,0)    如果name值是null,就返回0，否则返回name。
NVL2( name,1 ,0 )  如果name值是null，就返回0，否则返回1。
NULLIF(1,1)  如果两个参数相等，就返回null，否则返回第一个参数。
COALESCE(name,age,pwd) 可跟多个参数，参数中从前向后，返回第一个不为null的参数。

聚合（组）函数:
MAX()
MIN()
AVG()
COUNT()
SUM()
STDDEV()
VARIANCE()





----------------------------select部分------------------------------
去除重复的值， 例select distinct department_id,job_id from employees;   distinct 关键字后跟多个列时，列的组合总体要维一。
查看表的结构，   DESCRIBE ，他是sql plus的命令，可以简写为DESC，后面可以不写分号。 例DESC employees

子查询：
子查询可以出现在where,having,from 子句中。子查询中的语句要使用() 括起来。

当子查询返回结果为null时，整个SQL使用了非 "is null"运算符时，查询结果就是0条记录。
注意  ****  not in ( 集合[111,222,null])时，整个SQL查询结果就是0条记录。


子查询 -- 在where子句中：
= ， < ， > ， <>是单行运算符，使用单行运算时，子查询只能返回一条结果（一行一列）。
in ，not in ，any，all ，聚合函数，    是多行运算符，使用多行运算符时，子查询可以返回多条结果（多行一列）。

Sql代码  收藏代码
子查询只返一条结果，并使用了单行运算符，SQL语句可以正常执行 
elect last_name ,salary from employees where salary > (select salary from employees where last_name ='Abel'); 

Sql代码  收藏代码
子查询只返多条结果，并使用了单行运算符，SQL语句不可以正常执行 
select last_name ,salary from employees where salary > (select salary from employees where last_name like 'K%'); 
 
第 1 行出现错误: 
ORA-01427: 单行子查询返回多个行 


子查询 -- 在having子句中：
Sql代码  收藏代码
SELECT department_id ,MIN(salary) 
FROM employees 
GROUP BY department_id 
HAVING MIN(salary)   > 
(SELECT MIN(salary) 
FROM employees 
WHERE department_id=50); 


子查询 -- 在from子句中：
略。。。



--------------------select多张表  join部分-------------------------


内外连接说明表

内连接


外连接


连接字段为null值或不能实现“拉手”连接的行，
不会出现在连接结果集合中


连接字段为null值或不能实现“拉手”连接的行，
会现在连接结果集合中

Inner Joins 内连接
Self join 自己连接自己
Nonequijoins 不等连接
left outer join 左外连接
right outer join 右外连接
full outer join 全连接



Inner Joins 内连接   （是内连接，连接字段为null值或不能实现“拉手”连接的行，不会出现在连接结果集合中）
--使用join on 方式（SQL标准），实现表的内连接：（经常用）
两张表中可连接的字段名称不相同
Sql代码  收藏代码
select employee_id,last_name,location_id,e.department_id 
from employees e <strong>join</strong> departments d <strong>on</strong> e.department_id=d.department_id; 
e.department_id    字段两个表都有，要加前缀或表别名。
三张表以上时使用: t1 join t2  on t1.n=t2.n join t3 on t3.n=t2.n


--Natural join 自然连接（SQL标准），实现表的内连接： （很少用）
两张表中可连接的字段名称相同，类型相同 就可以使用这种简写的连接方法，
在departments表与locations表中都有location_id字段且类型相同。
如果两张表中各有两个字段符合上述要求，就用这两个字段一起做join。

Sql代码  收藏代码
SELECT department_id,department_name,location_id,city 
FROM departments 
<strong>NATURAL JOIN</strong> locations; 



--USING  自然连接（SQL标准），实现表的内连接：（很少用）
两张表中可连接的字段名称相同，就可以进行连接，类型不相同oracle可以自动转换
如果两张表中各有两个字段符合上述要求，但使用的是哪一个字段来连接呢，
我们可以通过using来指定，using与natural join 不可同时出现。
Java代码  收藏代码
select employee_id,last_name,location_id,department_id 
from employees <strong>join</strong> departments 
<strong>using</strong>(department_id); 
department_id  字段两个表都有，但不要加前缀或表别名


--使用Oracle专有的语法，实现表的内连接：（经常用）
Sql代码  收藏代码
SELECT table1.column,table2.column  
FROM table1,table2 
<strong>WHERE table1.column=table2.column</strong> ; 
可以按多个字段来连接表
Sql代码  收藏代码
SELECT employee_id,last_name,department_name 
FROM employees e,departments d 
<strong>WHERE e.department_id=d.department_id AND e.manager_id=d.manager_id</strong>; 
三张表连接
Sql代码  收藏代码
SELECT employee_id,city,department_name 
FROM employees e,departments d,locations l 
<strong>WHERE e.department_id=d.department_id AND d.location_id = l.location_id</strong>; 


Self join 自己连接自己   （是内连接，连接字段为null值或不能实现“拉手”连接的行，不会出现在连接结果集合中）
同一张表起两个不同的别名，实现连接自己

下面是SQL标准的语法：

Sql代码  收藏代码
SELECT w.last_name emp,m.last_name mgr 
FROM <strong>employees w</strong> JOIN <strong>employees m</strong> 
ON w.manager_id=m.employee_id; 
下面是Oracle的语法：

Sql代码  收藏代码
SELECT w.last_name emp,m.last_name mgr 
FROM employees w,employees m 
WHERE w.manager_id = m.employee_id; 


Nonequijoins 不等连接   （是内连接，连接字段为null值或不能实现“拉手”连接的行，不会出现在连接结果集合中）
连接的条件不是“等于”，是在一个范围内。
t1员工表, t2是工资档次表 ， 求员工的工资在哪个档次之间。
t1 join t2 on  t1.工资 between t2.本档最低工资  and   t2.本档最高工资


Outer jion 外连接   （是外连接，连接字段为null值或不能实现“拉手”连接的行，会现在连接结果集合中）
--left outer join 左外连接
SQL标准语法：
Sql代码  收藏代码
SELECT e.last_name,d.department_name 
FROM employees e <strong>LEFT OUTER JOIN</strong> departments d 
ON e.department_id = d.department_id; 
Oracle语法： 在右边表加一个(+)
Sql代码  收藏代码
SELECT e.last_name,d.department_name 
FROM employees e ,departments d 
WHERE e.department_id = d.department_id(+); 

--right outer join 右外连接
SQL标准语法：
Sql代码  收藏代码
SELECT e.last_name,d.department_name 
FROM employees e <strong>RIGHT OUTER JOIN</strong> departments d 
ON e.department_id = d.department_id; 
Oracle语法：在左边表加一个(+)
Sql代码  收藏代码
SELECT e.last_name,d.department_name 
FROM employees e ,departments d 
WHERE e.department_id(+) = d.department_id; 

--full outer join 全连接
SQL标准语法：
Sql代码  收藏代码
SELECT e.last_name,d.department_name 
FROM employees e <strong>FULL OUTER JOIN</strong> departments d 
ON e.department_id = d.department_id; 
Oracle语法：
Sql代码  收藏代码
不知 


Cartesian product   笛卡儿积
--Corss join
第一张表有n条记录，第二张表有m条记录， 连接后会生成 n*m条记录的表。
当连接表时， 条件失效，会把所有的可能连接都连接一次，就会形成n*m条记录的新表。
我们应避免笛卡儿积，只有想生成大量测试数据来填充表进才使用
SQL标准语法：
Sql代码  收藏代码
SELECT last_name,department_name 
FROM employees 
CROSS JOIN departments; 
Oracle语法：
   就是不要where条件。



---------------------------insert部分-------------------------------
插入一条：
Sql代码  收藏代码
INSERT INTO departments (department_id,department_name,manager_id,location_id) 
VALUES ( 71,'Public Relaations',100,1700); 

批量插入：
INSERT INTO 表名 字段(一致)   SELECT     字段(一致)  FROM  表名
Sql代码  收藏代码
INSERT INTO copy_departments 
SELECT * FROM departments; 
 
由于两个表的 字段的数据类型，字段的顺序都一样，所以省略了字段名。 

---------------------------update部分-------------------------------
UPDATE 表名 SET  列=值，列=值  WHERE  条件
UPDATE 表名 SET  列=（子查询），列=（子查询）  WHERE  条件=（子查询）


---------------------------detete部分-------------------------------
DELETE [FROM] 表名 WHERE 条件
DELETE [FROM] 表名 WHERE 条件=（子查询）


---------------------------- 管理表-----------------------------------
复制表：
CREATE TABLE copy_departments AS SELECT * FROM departments;

清空表：
TRUNCATE TABLE copy_departments;
与delete的不同是，TRUNCATE 不可以容易的回滚。

创建表：
CREATE TABLE [schema.]表名 ( column 数据类型 [default expr ]);

    创建：
Sql代码  收藏代码
CREATE TABLE t3 
(id int, 
dname varchar2(14), 
age number(5), 
hire_date DATE DEFAULT SYSDATE); 
    查看表结构：
Sql代码  收藏代码
SQL> DESC  t3 
名称                                      是否为空? 类型 
----------------------------------------- -------- ---------------------------- 
ID                                                 NUMBER(38) 
DNAME                                              VARCHAR2(14) 
AGE                                                NUMBER(5) 
HIRE_DATE                                          DATE 

创建两表有主外键关系的表：
Sql代码  收藏代码
   表1：book表，字段有id(主键)，name (书名)； 
   表2：bookEnrol表（图书借出归还登记），字段有id，bookId（外键），dependDate（变更时间），state（1.借出 2.归还）。 
 
 
create table book( 
  id int , 
  name varchar2(30), 
  PRIMARY KEY (id) 
) 
或 
create table book2( 
  id number PRIMARY KEY, 
  name varchar2(30)) 
   
   
create table bookEnrol( 
  id int, 
  bookId int, 
  dependDate date, 
  state int, 
  FOREIGN KEY (bookId) REFERENCES book(id) ON DELETE CASCADE 
) 


用子查询创建表：
语法：CREATE TABLE tablename [(新列名,新列名...)]  AS 子查询
新列名可以省略，新列名的数量要与 子查询的列数量一列。
如果子查询某列是表达式无列名，要起别名。
原表的约束，只有not null约束会传递给新表，其它约束丢失不传递。

Sql代码  收藏代码
CREATE TABLE t8 as select *  from  employees; 
建了一个t8表，与employees一样， employees原有的约束只有not null约束会传递给t8表，其实约束丢失不传递。 

Sql代码  收藏代码
CREATE TABLE t7 (new_id,new_name) as select employee_id,last_name from  employees; 
 
建了t7表，有new_id,new_name两个字段，数据来源于employees的employee_id,last_name 两个字段 

Sql代码  收藏代码
CREATE TABLE t9 AS 
SELECT employee_id,last_name, 
salary*12 ANNSAL,  //表达式无列名，同时又没有指定新列名，这时要起别名。  或者在AS前指定新列名。 
HIRE_DATE 
FROM employees 
WHERE  department_id=80; 

删除表：
DROP TABLE table_name;
DROP TABLE table_name PURGE; 被删除表不进回收站，直接删除。(10G新加的功能)

修改表：
ALTER TABLE  ....  略。。。
---------------------------- 事务-----------------------------------------------
事务什么时候会开始与结束--事务的边界：
1 开始执行一条DML语句时，比如insert，会自动开启一个新事务。
2 当COMMIT 或 ROLLBACK 时，事务会结束。
3 当开始一个DDL 或 DCL 语句时，前面的事务会提交，因为DDL ,DCL语句要在一个单独的事务中执行。
4 用户正常退出SQLplus时会提交事务。
5 当ORACLE系统死掉挂掉，再重新启动后，会回滚之前没有提交的事务。

COMMIT               提交事务
ROLLBACK            回滚事务
SAVEPOINT a       定义保存点a
ROLLBACK TO  a  回滚到保存点a

---------------------------- 常用的数据库对象---------------------------------
table           表
view           视图
sequence   序列
index          索引
synonym    同义词
存储过程
触发器

    查看用户对象
Sql代码  收藏代码
SELECT object_name,object_type FROM user_objects; 

-------------------------------命名规则----------------------------------------
1 必须以字母开头
2 最大长度为30
3 A-Z，a-z，0-9，_，$，#   范围内
4 不能包含Oracle保留关键字
-------------------------------数据类型----------------------------------------
VARCHAR2(size)
VARCHAR2(200 CHAR)
CHAR(size)
NUMBER(p,s)
DATE
CLOB
BLOB
BFILE一个大文件保存在操作系统上，Oracle保存一个指针
FOWID每个表都有一列叫rowid
TIMESTAMP
---------------------------------常用的约束-----------------------------------
NOT NULL   非空
UNIQUE      唯一
PRIMARY KEY   主键
FOREIGN KEY  外键
CHECK            自定义


创建表时在字段后添加主键约束：
Sql代码  收藏代码
CREATE TABLE t5 ( emp_id number(4) PRIMARY KEY , dname varchar2(14) NOT NULL, age number(5)  NOT NULL, hire_date DATE DEFAULT SYSDATE); 

修改约束：
primary key的语法：
alert table table_name add constraint key_name primary key(columns);

unique key的语法：
alert table table_name add contraint key_name unique(columns);

删除约束：
ALTER TABLE table_name DROP PRIMARY KEY



--------------------------------------------------视图 View------------------------------------------------------------

创建视图的语法：


UPDATE视图:
视图通常是用来读的，不过也可以有条件的UPDATE视图中的记录，
比如经过avg()一类的聚合函数计算出来的平均值就不能修改，
group by 分组出来的数据就不能修改，DISTINCT过滤后的数据，ROWNUM列做为视图中的一列时，表达式计算出来的结果，都不能修改
而与基表一一对应的项目就可以修改。
修改时使用UPDATE语句，与修改一般表一样的。

ALTER VIEW :
略，请查询联机文档
http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/statements_4004.htm#SQLRF01104

删除视图：
DROP VIEW view_name;
--------------------------------------------------序列  Sequence-----------------------------------------------------
创建序列：
CREATE SEQUENCE squence_name;

删除序列：
DROP SEQUENCE squence_name;

查出序列值 - 下一个值 ，取出后，原值加1：
SELECT squence_name.NEXTVAL FROM dual;

查出序列值 - 当前值，取出后，原值不变：
SELECT squence_name.CURRVAL FROM dual;

使用序列：
INSERT INTO 表 (主键字段,其它字段... ) VALUES (squence_name.NEXTVAL , 其它值...);


--------------------------------------------------索引  Index----------------------------------------------------------
语法：
Sql代码  收藏代码
CREATE [UNIQUE] [BITMAP] INDEX index_name 
N 表名(列名 [,列名]... ); 


创建索引：
Sql代码  收藏代码
CREATE INDEX emp_last_name_idx ON employees(last_name); 

删除索引：
Sql代码  收藏代码
DROP INDEX index_name; 

--------------------------------------------------同义词 Synonym----------------------------------------------------
同义词就是为一个对象起一个别名。

语法：
Sql代码  收藏代码
CREATE [PUBLIC] SYNONYM synonym_name FOR object; 
 
不加PUBLIC，这个同义词就保存在当前用户的对象，加了PUBLIC，这个同义词就不只是当前用户的，其它用户也可以使用。 


创建同义词：
Sql代码  收藏代码
CREATE SYNONYM d_sum 
FOR  数据库对象; 


删除同义词：
Sql代码  收藏代码
DROP SYNONYM  d_sum(同义词的名称)