ITPub博客

首页 > 数据库 > Oracle > OCP课程2:SQL之介绍

OCP课程2:SQL之介绍

原创 Oracle 作者:stonebox1122 时间:2015-11-11 10:04:18 0 删除 编辑

Oracle 11G的OCP课程中讲SQL的课程是051,但是我们上的课程是047,主要是因为047考试通过后会有一个单独的SQL EXPERT的证书,047这门课程是Oracle在10G推出的,在内容上,这两门课程差不多,047多一个证书,所以选择了047。

第0章主要是介绍Oracle的一些功能和关系数据库的原理,这些都比较简单,就不详细说明了,重点说一下这门课程例子中需要用得到表以及使用sqlplus和pl/sql developer连接到数据库。

 

 

1、关系数据库的术语

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

clipboard

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

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

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

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

第五个标签表示一个域值(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命令分类:

clipboard[1]

 

 

(2)登录SQLPLUS:

clipboard[2]

 

 

(3)描述表结构:

clipboard[3]

tablename:可以是表,视图或者同义词

 

 

(4)格式化输出:

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

SQL> set linesize 100

设置输出的最大行宽为100

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


SQL> col name for a20

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

 

 

(5)SQLPLUS的编辑命令:

clipboard[4]

clipboard[5]

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文件命令:

clipboard[6]

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块的输出

clipboard[7]

 

 

(8)SPOOL命令:

将查询结果保存到文件

clipboard[8]

clipboard[9]

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语句后产生的报告,包括执行计划及统计信息

clipboard[10]

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)连接到数据库

clipboard[11]

其中:

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

 

 

(2)创建联机文档

选择“Help”“Oracle HTML Manuals”

clipboard[12]

点击“Setup”

clipboard[13]

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

clipboard[14]

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

clipboard[15]

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

clipboard[16]

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

clipboard[17]

 

 

5、SQL

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

clipboard[18]

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

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

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

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

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

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

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28536251/viewspace-1828183/,如需转载,请注明出处,否则将追究法律责任。

上一篇: OCP课程1:准备
请登录后发表评论 登录
全部评论

注册时间:2013-12-13

  • 博文量
    204
  • 访问量
    1078432