Oracle 10g SQL 基础培训-优快云博客

作者：赵磊

博客：http://elf8848.iteye.com

本文章通过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-23fm作用：去掉前导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语句可以正常执行
select last_name ,salary from employees where salary > (select salary from employees where last_name ='Abel');

子查询只返多条结果，并使用了单行运算符，SQL语句不可以正常执行
select last_name ,salary from employees where salary > (select salary from employees where last_name like 'K%');

第 1 行出现错误:
ORA-01427: 单行子查询返回多个行

子查询 -- 在having子句中：

 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标准），实现表的内连接：（经常用）

两张表中可连接的字段名称不相同

select employee_id,last_name,location_id,e.department_id
from employees e join departments d on 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。

SELECT department_id,department_name,location_id,city
FROM departments
NATURAL JOIN locations;

--USING 自然连接（SQL标准），实现表的内连接：（很少用）

两张表中可连接的字段名称相同，就可以进行连接，类型不相同oracle可以自动转换

如果两张表中各有两个字段符合上述要求，但使用的是哪一个字段来连接呢，

我们可以通过using来指定，using与natural join 不可同时出现。

select employee_id,last_name,location_id,department_id
from employees join departments
using(department_id);

department_id 字段两个表都有，但不要加前缀或表别名

--使用Oracle专有的语法，实现表的内连接：（经常用）

SELECT table1.column,table2.column 
FROM table1,table2
WHERE table1.column=table2.column ;

可以按多个字段来连接表

SELECT employee_id,last_name,department_name
FROM employees e,departments d
WHERE e.department_id=d.department_id AND e.manager_id=d.manager_id;

三张表连接

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;

Self join 自己连接自己 （是内连接，连接字段为null值或不能实现“拉手”连接的行，不会出现在连接结果集合中）

同一张表起两个不同的别名，实现连接自己

下面是SQL标准的语法：

SELECT w.last_name emp,m.last_name mgr
FROM employees w JOIN employees m
ON w.manager_id=m.employee_id;

下面是Oracle的语法：

 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 ont1.工资between t2.本档最低工资 and t2.本档最高工资

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

--left outer join 左外连接

SQL标准语法：

SELECT e.last_name,d.department_name
FROM employees e LEFT OUTER JOIN departments d
ON e.department_id = d.department_id;

Oracle语法：在右边表加一个(+)

SELECT e.last_name,d.department_name
FROM employees e ,departments d
WHERE e.department_id = d.department_id(+);

--right outer join 右外连接

SQL标准语法：

SELECT e.last_name,d.department_name
FROM employees e RIGHT OUTER JOIN departments d
ON e.department_id = d.department_id;

Oracle语法：在左边表加一个(+)

SELECT e.last_name,d.department_name
FROM employees e ,departments d
WHERE e.department_id(+) = d.department_id;

--full outer join 全连接

SQL标准语法：

SELECT e.last_name,d.department_name
FROM employees e FULL OUTER JOIN departments d
ON e.department_id = d.department_id;

Oracle语法：

不知

Cartesian product 笛卡儿积

--Corss join

第一张表有n条记录，第二张表有m条记录，连接后会生成 n*m条记录的表。

当连接表时，条件失效，会把所有的可能连接都连接一次，就会形成n*m条记录的新表。

我们应避免笛卡儿积，只有想生成大量测试数据来填充表进才使用

SQL标准语法：

SELECT last_name,department_name
FROM employees
CROSS JOIN departments;

Oracle语法：

就是不要where条件。

---------------------------insert部分-------------------------------

插入一条：

INSERT INTO departments (department_id,department_name,manager_id,location_id)
VALUES ( 71,'Public Relaations',100,1700);

批量插入：

INSERT INTO 表名字段(一致) SELECT 字段(一致) FROM 表名

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 ]);

创建：

CREATE TABLE t3
(id int,
dname varchar2(14),
age number(5),
hire_date DATE DEFAULT SYSDATE);

查看表结构：

SQL> DESC  t3
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
ID NUMBER(38)
DNAME VARCHAR2(14)
AGE NUMBER(5)
HIRE_DATE DATE

创建两表有主外键关系的表：

   表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约束会传递给新表，其它约束丢失不传递。

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

 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 两个字段

 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

ROLLBACKTO a 回滚到保存点a

---------------------------- 常用的数据库对象---------------------------------

table 表

view 视图

sequence 序列

index 索引

synonym同义词

存储过程

触发器

查看用户对象

 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 自定义

创建表时在字段后添加主键约束：

 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----------------------------------------------------------

语法：

 CREATE [UNIQUE] [BITMAP] INDEX index_name
ON 表名(列名 [,列名]... );

创建索引：

 CREATE INDEX emp_last_name_idx ON employees(last_name);

删除索引：

DROP INDEX index_name;

--------------------------------------------------同义词 Synonym----------------------------------------------------

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

语法：

CREATE [PUBLIC] SYNONYM synonym_name FOR object;

不加PUBLIC，这个同义词就保存在当前用户的对象，加了PUBLIC，这个同义词就不只是当前用户的，其它用户也可以使用。

创建同义词：

CREATE SYNONYM d_sum
FOR  数据库对象;

删除同义词：

DROP SYNONYM  d_sum(同义词的名称)