OCP培训课程：sql简单介绍

OCP培训课程：sql简单介绍 主要是介绍Oracle的一些功能和关系数据库的原理，重点说一下这门课程例子中需要用得到表以及使用sqlplus和pl/sql developer连接到数据库。

1、关系数据库的术语

参考下面这个图，介绍关系数据库的术语

第一个标签表示一行，也叫一条记录。

第二个标签表示一列，也叫表的字段或者属性，同时这一列也是这个表的主键，主键的值用于唯一标识表中的某一条记录，主键可由多列组成，但是一个表里面只能有一个主键。可以看到上图中，使用不重复的人员编号来唯一标识某一个人。

第三个标签表示一个数字列。

第四个标签表示一个外键列，外键用于表和表的关联，比如人员表和部门表，我们要从人员表里面取出人员姓名、部门编号及对应的部门名字，但人员表里面只有部门编号，要去查部门名字就要从部门表里面去查，部门表里面有部门编号和部门名字，这个时候部门表的部门编号和人员表的部门编号形成一种主外键，人员表的部门编号就是一个外键，而部门表的部门编号是一个主键。

第五个标签表示一个域值(field)，就是行和列交叉的地方，只能有一个值。

第六个标签表示这个值为空(null)。

2、课程中用到的表

047这门课程例子用到的表主要是HR模式下的表，位于example表空间，包括以下几张表：

(1)人员表(employees)，是公司人员的基本信息，共107条记录，包含的字段如下：

SQL> desc employees;

Name

---------------------------

EMPLOYEE_ID 人员编号(主键)

FIRST_NAME 姓

LAST_NAME 名

EMAIL 邮箱

PHONE_NUMBER 电话

HIRE_DATE 入职时间

JOB_ID 职位编号(外键，参考职位表jobs的主键)

SALARY 薪水

COMMISSION_PCT 提成比例

MANAGER_ID 部门主管编号(外键，参考人员表自己的主键)

DEPARTMENT_ID 部门编号(外键，参考部门表departments的主键)

(2)部门表(departments)，是公司部门的基本信息，共27条记录，包含的字段如下：

SQL> desc departments;

Name

-----------------------------

DEPARTMENT_ID 部门编号(主键)

DEPARTMENT_NAME 部门名字

MANAGER_ID 部门主管编号(外键，参考人员表employees的主键)

LOCATION_ID 位置编号(外键，参考位置表locations的主键)

(3)职位表(jobs)，是公司职位的基本信息，共19条记录，包含的字段如下：

SQL> desc jobs;

Name

-----------------------------

JOB_ID 职位编号(主键)

JOB_TITLE 职位名称

MIN_SALARY 最低薪水

MAX_SALARY 最高薪水

(4)职位历史表(job_history)，是员工职位历史信息，共10条记录，包含的字段如下：

SQL> desc job_history

Name

------------------------------

EMPLOYEE_ID 人员编号(复合主键，外键，参考人员表employees的主键)

START_DATE 开始日期(复合主键)

END_DATE 结束日期

JOB_ID 职位编号(外键，参考职位表jobs的主键)

DEPARTMENT_ID 部门编号(外键，参考部门表departments的主键)

(5)位置表(locations)，是部门所在的位置信息，共23条记录，包含的字段如下：

SQL> desc locations

Name

-------------------------------

LOCATION_ID 位置编号(主键)

STREET_ADDRESS 街道地址

POSTAL_CODE 邮编

CITY 城市名字

STATE_PROVINCE 省份

COUNTRY_ID 国家编号(外键，参考国家表countries的主键)

(6)国家表(countries)，是部门位置所在的国家信息，共25条记录，包含的字段如下：

SQL> desc countries

Name

--------------------------------

COUNTRY_ID 国家编号(主键)

COUNTRY_NAME 国家名称

REGION_ID 地区编号(外键，参考地区表regions的主键)

(7)地区表(regions)，是国家所在的地区信息，共4条记录，包含的字段如下：

SQL> desc regions

Name

---------------------------------

REGION_ID 地区编号(主键)

REGION_NAME 地区名称

3、sqlplus

Oracle的sqlplus是与Oracle进行交互的客户端工具，借助sqlplus可以查看、修改数据库记录。在sqlplus中，可以运行sqlplus命令与sql语句。

(1)SQLPLUS命令分类：

(2)登录SQLPLUS：

sqlplus [username[/password[@databae]]]

(3)描述表结构：

desc[ribe] tablename

tablename：可以是表，视图或者同义词

(4)格式化输出：

使用set linesize设置sqlplus输出的最大行宽

SQL> set linesize 100

设置输出的最大行宽为100

使用col(column)修改字段显示的长度

SQL> col name for a20

将name字段显示的长度设置为20

(5)SQLPLUS的编辑命令：

SQL> select department_id

2 from departments where department_id=100;

DEPARTMENT_ID

-------------

100

使用list列出刚才执行过的命令

SQL> list

1 select department_id

2* from departments where department_id=100

使用1(数字1)列出刚才执行语句的第一行

SQL> 1

1* select department_id

使用a(append)，在刚才这一行语句的最后增加后面的内容

SQL> a ,department_name

1* select department_id,department_name

SQL> list

1 select department_id,department_name

2* from departments where department_id=100

使用r(run)运行修改后的内容

SQL> r

1 select department_id,department_name

2* from departments where department_id=100

DEPARTMENT_ID DEPARTMENT_NAME

------------- ------------------------------

100 Finance

使用l(list)列出刚才执行过的命令

SQL> l

1 select department_id,department_name

2* from departments where department_id=100

使用1(数字1)列出刚才执行语句的第一行

SQL> 1

1* select department_id,department_name

使用c(change)进行替换

SQL> c/department_name/manager_id

1* select department_id,manager_id

使用l(list)列出刚才执行过的命令

SQL> l

1 select department_id,manager_id

2* from departments where department_id=100

使用r(run)运行替换后的内容

SQL> r

1 select department_id,manager_id

2* from departments where department_id=100

DEPARTMENT_ID MANAGER_ID

------------- ----------

100 108

(6)SQLPULS文件命令：

SQL> l

1 select department_id,manager_id

2* from departments where department_id=100

使用save保存刚才执行的语句到sql脚本文件

SQL> save my_query

Created file my_query.sql

使用start执行sql脚本文件

SQL> start my_query

DEPARTMENT_ID MANAGER_ID

------------- ----------

100 108

(7)SERVEROUTPUT命令：

控制在sqlplus里面是否显示存储过程或者PL/SQL块的输出

(8)SPOOL命令：

将查询结果保存到文件

SQL> spool sql.txt

SQL> select * from departments where department_id=10;

DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID

------------- ------------------------------ ---------- -----------

10 Administration 200 1700

SQL> spool off

[Oracle@Oracletest ~]$ cat sql.txt

SQL> select * from departments where department_id=10;

DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID

------------- ------------------------------ ---------- -----------

10 Administration 200 1700

SQL> spool off

(9)AUTOTRACE命令：

成功执行select及DML语句后产生的报告，包括执行计划及统计信息

SQL> set autotrace on

SQL> select * from hr.departments where department_id=10;

DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID

------------- ------------------------------ ---------- -----------

10 Administration 200 1700

Execution Plan

----------------------------------------------------------

Plan hash value: 4024094692

-------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

-------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 21 | 1 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 | 21 | 1 (0)| 00:00:01 |

|* 2 | INDEX UNIQUE SCAN | DEPT_ID_PK | 1 | | 0 (0)| 00:00:01 |

-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("DEPARTMENT_ID"=10)

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

2 consistent gets

0 physical reads

0 redo size

642 bytes sent via SQL*Net to client

508 bytes received via SQL*Net from client

1 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

4、PL/SQL Developer

(1)连接到数据库

其中：

Username为用户，Password为密码，Database后需要输入数据库服务器所在的IP地址，端口及服务名，或者输入服务命名，Connect as后面选择连接身份。

(2)创建联机文档

选择“Help”“Oracle HTML Manuals”

点击“Setup”

点击“Download”，进入联机文档下载页面

注册Oracle用户，点击“Oracle 11.2 Library”下载

下载完成后，选择所在路径，点击“Build”

完成后，就可以看到联机文档里面的书了，选上就可以进行搜索了，也可以直接双击查看PDF版本的。

5、SQL

SQL(Structured Query Language)是一门 ANSI 的标准计算机语言，用来访问和操作数据库系统。

我们用到的SQL语句包含4个类型：

第一个是DML语句，数据操纵语言，上图把查询也放到这儿来了，查询不属于DML语句，DML包括增删改，merge是增删改的和。

第二个是DDL语句，对数据结构进行修改的语言，数据定义语言，包含创建，修改，删除，重命名，截断，以及加注释。

第三个是DCL语句，授权和取消授权。

第四个是事务控制，用于保证数据的完整性，提交，回滚，回到保存点。

实际工作中，就是通过SQLPLUS或者PL/SQL Developer连接到数据库，使用SQL语句查询及操纵数据。