Oracle基础入门

CREATE TABLE[<方案名>.]<表名>

(

<列名1><数据类型>,

<列名2><数据类型>,

……

<列名n><数据类型>

)

问题：

某移动通信公司需要建设网上营业厅，需要使用Oracle数据库存储用户个人信息，内容包括：机主姓名、手机号码、地市归属、入网时间、消费品牌名称（如动感地带）等基本信息。如何创建数据库表以保存个人信息呢？

以SCOTT用户登录SQL Plus，输入示例21.1的SQL代码。

示例1.1

CREATE TABLE PERSONAL_INFO

(

OWNER_NAME VARCHAR2(20) NOT NULL,

TEL_CODE VARCHAR2 (15) NOT NULL,

CITY VARCHAR2 (20) NOT NULL,

NETWORK_DATE DATE NOT NULL,

BRAND_NAME VARCHAR2 (20) NOT NULL

);

执行后，使用DESC 命令查看表结构。运行结果如图1.4.11.7所示：

图1.4.11.7创建个人信息表的显示结果

我们在之前学习SQL Server知识的时候，已经学习过对表字段添加各种约束，例如唯一约束，在我们新建的个人信息表中，TEL_CODE（手机号码）需要添加唯一约束，怎么实现呢？修改表结构，并添加唯一约束，如示例1.2所示：

示例1.2

SQL> ALTER TABLE PERSONAL_INFO ADD CONSTRAINT U_TEL_CODE UNIQUE (TEL_CODE);

SQL>COL COLUMN_NAME FOR A20;

该代码表示在接下来的查询中，列“COLUMN_NAME”按照20个字符宽度显示。

SQL> SELECT CONSTRAINT_NAME,COLUMN_NAME FROM USER_CONS_COLUMNS WHERE TABLE_NAME='PERSONAL_INFO';

该代码使用了数据库视图USER_CONS_COLUMNS，此视图存储了数据库表中所有的约束信息，对应的列描述见表1-41-1。

表1-41-1 列信息

列名

描述

CONSTRAINT_NAME

约束名称

COLUMN_NAME

约束所在的列名称

TABLE_NAME

约束所在的表名称

通过对图1.4.21.8的观察，发现在TEL_CODE列上，建立了U_TEL_CODE唯一约束。

1.1使用SQL命令管理序列

已经创建了数据库表，下一步就要录入数据。需要解决的一个问题是：个人信息表没有主键，如果有一ID字段并能够实现自动编号功能，就方便多了。Oracle中是如何解决主键自动增长的呢？

Oracle并没有类似SQLServer中的Identity来定义主键自增属性。如果要实现主键自增，常用的方法是定义一个序列Sequence，然后在插入数据记录时取序列中的下一个值nextval即可。

序列（Sequence）是Oracle提供的的用于产生一组等间隔整型数值的数据库对象，可以通过在插入语句中使用序列值来实现主键自增。

Oracle中的CREATE SEQUENCE命令用于创建序列。

CREATE SEQUENCE <序列名称>

[START WITH 初始值 ]

[INCREMENT BY 步长 ]

[MINVALUE 最小值 | NOMINVALUE ]

[MAXVALUE 最大值 | NOMAXVALUE ]

[CYCLE | NOCYCLE ]

[CACHE 预分配序列个数 | NOCACHE]

[ORDER | NOORDER]

说明如下：

?START WITH 用于指定序列的起始值。

?INCREMENT BY 用来指定序列的增量，如果取负值，则为递减序列。

?MINVALUE | NOMINVALUE 指定序列是否有最小值。

?MAXVALUE | NOMAXVALUE 用来说明序列是否有最大值。

?CYCLE | NOCYCLE 用于指定序列值达到最大之后，是否重新从最小值开始产生。

?CACHE | NOCACHE 用来指定是否在缓存中保存预先分配的序列值，如果选择CACHE，可以提高获取序列的速度。

?ORDER | NOORDER 是否确保序列值是否唯一和有序。

下面定义SEQ_PERSONAL_INFO序列用来实现PERSONAL_INFO表的主键自动编号，如示例1.3所示。

示例1.3

CREATE SEQUENCE SEQ_PERSONAL_INFO

START WITH 1

INCREMENT BY 1

NOMAXVALUE

CACHE 20;

使用序列时，需要用到序列的两个伪列NEXTVAL和CURRVAL，其中NEXTVAL返回序列的下一个值，而CURRVAL返回序列的当前值。

至此，我们只需要修改PERSONAL_INFO表，添加ID列，之后就可以使用序列实现自增了。如示例1.4所示。

示例1.4

ALTER TABLE PERSONAL_INFO ADD ID NUMBER PRIMARY KEY;

INSERT INTO PERSONAL_INFO VALUES('张三','19900000000','北京','18-7月-2011',

'动感地带',SEQ_PERSONAL_INFO.NEXTVAL);

INSERT INTO PERSONAL_INFO VALUES('李四','19900007777',

'全球通',SEQ_PERSONAL_INFO.NEXTVAL);

首先修改了PERSONAL_INFO表，添加了主键字段ID。然后往表中新增了2条记录。

1.1使用SQL命令完成数据增删改查

在本章前一节的内容中，我们接触了INSERT命令，会发现与之前学习的SQLServer操作很相似。事实上，虽然Oracle数据库功能非常强大，体系结构与其他数据库有较大差异，但其同样遵循标准的SQL语言。因此，对于修改和表数据，删除表数据不再在过多介绍，只给出语法。

UPDATE 表名 SET 列名=值 [,列名=值 ,…… ] [ WHERE 条件 ]

DELETE [ FROM ] 表名 [ WHERE 条件 ]

使用SELECT编写子查询

在数据库数据操作语句（DML）中，查询是较复杂的一类操作，也是体现数据库技能掌握是否良好的重要指标。在涉及基于数据库编程方面，查询也是至关重要的知识。本节我们着重讲解Oracle数据库数据查询的知识。

1，.SCOTT示例方案简介

Oracle数据库安装的时候会提示是否安装示例数据库，如果选择安装了，会提供SCOTT方案供学习Oracle数据库使用。SCOTT方案有表如1-6-11-2所示的数据表。

表1-1-12

表名

描述

EMP

雇员表

DEPT

部门表

SALGRADE

薪水等级表

BONUS

奖金表

2，.单行子查询

子查询是指嵌套在其他SQL语句中的查询，它可以出现在SELETE、WHERE、FROM等语句中。使用子查询，可以用一系列简单的查询构成复杂的查询，从而增强SQL语句的功能。

单行子查询不向外层查询返回记录或者只返回一条记录。子查询可以放在SELECT语句的WHERE或FROM等子句中。

示例1.5要求查询工资最高的雇员信息。

示例1.5

SELECT EMPNO,ENAME,SAL,HIREDATE FROM EMP

WHERE SAL= (SELECT MAX (SAL) FROM EMP);

多行子查询

多行子查询可以向父查询返回多行记录。在WHERE语句中使用多行子查询时，必须使用多行运算符（IN、NOT IN、EXISTS、NOT EXISTS、ALL和ANY等）。例如，查询工资高于部门20中所有员工的雇员信息，先利用子查询获得部门20中所有员工的工资，再利用父查询获得其工资大于等于这些员工的雇员信息。

示例1.6

SELECT ENAME,JOB,DEPTNO FROM EMP

WHERE SAL >= ALL (SELECT SAL FROM EMP WHERE DEPTNO=20);

相关子查询

相关子查询是指引用了父查询中某些列的子查询。在相关子查询中，父查询所处理的每一行都先被传递给子查询。子查询依次处理这些行，即将其应用到子查询，如果满足子查询中的条件，则父查询中的这一行就是最终结果集中的一行，直到父查询中的每一行都处理完成为止。一般可以在SELECT或WHERE语句中使用相关子查询。例如，查询负责管理其他雇员的管理员信息，父查询提供一个雇员编号（EMPNO）给子查询，子查询利用该雇员编号查询是否存在一个管理者（MGR）与父查询提供的雇员编号（EMPNO）对应。若存在，则说明该EMPNO就是管理者，将其显示出来。

示例1.7

SELECT EMPNO,DEPTNO,SAL FROM EMP A

WHERE EXISTS (SELECT * FROM EMP B WHERE B.MGR = A.EMPNO);

添加数据

数据库用来保存数据的，添加数据使用insert语句

（1）添加一条语句

SQL>insert into dept(deptno,dname,loc) values (60,’市场部’,’Beijing’);

插入多行

将SELECT语句检索出来的所有数据行都插入到表中.这条语句通常在从一个表向另一个表快速复制数据行.

SQL> create table dept1(deptno number(2) primary key,dname varchar2(14),loc varchar2(13));

SQL> insert into dept1 select * from dept;

修改语句

SQL> updatedeptsetdname= ‘国内市场部’,loc=’HongKong’wheredeptno= 60;

删除语句

SQL> delete from dept where deptno = 60 or dname = ‘销售部’;

Delete语句中如果没有where子句表示删除表中的所有数据

8．使用Truncate 删除所有数据

SQL> truncate table shopinfo;

使用delete语句可以删除表中的全部数据，但是delete操作会被记录在日志文件中，

9．Oracle分页查询

Rownum是一个伪列，它会根据返回的记录生成一个序列化的数字。可以使用ROWNUM列返回查询结果集中前N条记录

SQL> select rownum,deptno,ename,job,mgr,hiredate from emp where rownum<10

分页实现

比如每5行为一页，现在要显示第2页，就是要显示第6行到第10行

SQL> select e.* from

( select rownum r,hiredate from emp ) e

Where r>5 and r<11;

10. rowid伪列

rowid就是唯一标志记录物理位置的一个id,内容是当前行位于哪个文件，块，行的详细信息。

SQL> select rownum,loc from dept

dual虚表

dual是系统定义的表,只有一行一列数据. 写成select 100*200 from dual 这里的dual只是为了完善语义的。对于虚表（dual）来说，其中的列往往是不相关的或无关紧要的。

SQL> select 5*9 from dual;

SQL> select sysdate from dual;

1.1使用JDBC访问Oracle数据

在第二学期，我们详细的学习了JDBC相关知识，在这里我们简要的回顾一下关键步骤和核心对象。

?第一步，加载驱动。关键代码：Class.forName(“……”);

?第二步，建立连接。关键代码：DriverManager.getConnection(“……”);

?第三步，执行SQL。核心对象：PreparedStatement

?第四步，处理结果。核心对象：ResultSet

?第五步，释放资源。

本章前部分，新建了个人信息数据库表，现在需要查询北京地区动感地带用户数量。需要使用JDBC访问Oracle数据库实现。

示例1.8

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.Date;

 

 

public class PagingEmpInfo {

public static void main(String[] args) {

showPagingEmp(2,5);

}

/**

 * 分页查询员工信息

 * @param pageNum 第几页

 * @param pageSize  每页记录数

 * */

public static void showPagingEmp(int pageNum,int pageSize){

Connection con = null;

PreparedStatement stmt = null;

ResultSet rs = null;

//数据库连接信息

String driverClass = "oracle.jdbc.driver.OracleDriver";

String url = "jdbc:oracle:thin:@localhost:1521:orcl";

String user = "scott";

String password = "tiger";

try {

//加载驱动

Class.forName(driverClass);

} catch (ClassNotFoundException e) {

e.printStackTrace();

}

try {

//建立数据库连接

con = DriverManager.getConnection(url,user,password);

//准备将要执行的SQL语句，？号表示占位符，执行是会被具体值替代

String sql  = "select e.* from "+

"(select rownum r,hiredate from emp) e"+

" where r> ? and r<?";

//创建语句对象

System.out.println(sql);

stmt = con.prepareStatement(sql);

//替换占位符

int begin = (pageNum-1)*pageSize;

int end = pageNum*pageSize+1;

stmt.setInt(1,begin);

stmt.setInt(2,end);

//执行SQL语句，得到结果集

rs = stmt.executeQuery();

//处理数据

while(rs.next()){

int rownum = rs.getInt(1);

String ename = rs.getString(2);

String job = rs.getString(3);

Date hiredate = rs.getDate(4);

System.out.println(rownum+"t"+ename+"t"+

job+"t"+hiredate);

}

} catch (SQLException e) {

e.printStackTrace ();

}finally {

//释放数据库连接

if(con != null){

try {

con.close ();

} catch (SQLException e) {

e.printStackTrace ();

}

}

}

 

}

}

执行结果如图1.1.24所示

图1.1.24北京动感地带用户数

从示例1.78可以看出，使用JDBC访问Oracle数据库的步骤与访问SQLServer的步骤相同，只是具体连接参数不同。数据库驱动类名称字符串为“oracle.jdbc.driver.OracleDriver”，数据库连接字符串为“jdbc:oracle:thin:@localhost:1521:orcl”，其中“localhost”是Oracle服务器IP地址或者机器名，“1521”是监听端口，“orcl”是数据库实例示例名称。

?本章总结

?Oracle 11g实例启动必须启动OracleOraDb11g_home1TNSListener，OracleServiceSID服务

?Oracle内存可分为系统全局区(SGA)和程序全局区(PGA)

?SYS用户是Oracle中的超级用户，SYSTEM是Oracle中默认的管理员，它拥有DBA权限，SCOTT是Oracle数据库的一个示范帐户

?Oracle管理工具主要包括SQL*PLUS、PL/SQL Developer和Oracle Enterprise Manager

?使用JDBC访问Oracle数据库

?rownum是一个伪列，它会根据返回的记录生成一个序列化的数字，rowid就是唯一标志记录物理位置的一个id,内容是当前行位于哪个文件，块，行的详细信息。

?Oracle 支持的数据类型多达几十种。其中，常用数据类型大致可以分为字符类型、数字类型、日期类型、大对象类型等

?序列（Sequence）是Oracle提供的的用于产生一组等间隔整型数值的数据库对象，可以通过在插入语句中使用序列值来实现主键自增。Oracle中的CREATE SEQUENCE命令用于创建序列

任务实训部分

1：使用SQL命令创建数据表

训练技能点

?能够正确使用SQL Plus登录到Oracle数据库

?熟练使用Oracle数据类型

?熟练使用CREATE TABLE命令创建表

需求说明

在Oracle中使用SQL命令创建一个联系人表，表结构如下所示。

表1-2-1 联系人表

数据类型

约束

id

number

主键，自增

name

varchar2

非空

address

varchar2

phone

varchar2

实现步骤

（1）启动相关Oracle服务

（2）打开SQL Plus登录到Oracle数据库

（3）使用CREATE TABLE命令按照表1-2-1创建表

2：创建序列

训练技能点

?为联系人表的id字段创建序列

?在insert语句中使用序列

需求说明

联系人表的id字段是自动增长列，所以在Oracle中需要创建序列，要求从1开始，每次自增1。

实现步骤

（1）使用CREATE SEQUENCE命令创建序列

（2）使用insert命令向联系人表中插入测试数据

3：使用PL/SQL Developer工具

训练技能点

?使用PL/SQL Developer工具登录Oracle数据库

?使用PL/SQL Developer工具创建表

?使用PL/SQL Developer工具创建序列

?使用PL/SQL Developer工具插入测试数据

需求说明

前两个实训任务都是通过SQL Plus工具实现的，现在要求把联系人表删掉并使用PL/SQL Developer工具实现前两个实训任务

4：JDBC连接Oracle数据库

训练技能点

?JDBC连接Oracle数据库

?JDBC查询数据

需求说明

使用JDBC连接联系人表，并查询表中的所有数据显示到JSP页面上

实现步骤

（1）创建DAO类，在该类中实现两个方法：连接数据库和查询数据

（2）在JSP页面中调用DAO类把查询到的数据显示到页面上

（3）显示页面时使用分页方式

巩固练习

一、选择题

1.在使用Oracle数据库时至少需要启动（）服务。

A.OracleOraDb11g_home1TNSListener

B.OracleServiceSID

C.OracleDBConsoleSID

D.OracleJobSchedulerSID

2.下列（）不是Oracle默认的用户。

A.system

B.sys

C.scott

D.sa

3.关于jdbc:oracle:thin:@localhost:1521:orcl说法正确的是（）。

A.1521是Oracle的默认端口号

B.orcl是表名

C.localhost表示应用程序所在的机器

4.下列（）用于支持OEM服务。

A.OracleOraDb11g_home1TNSListener

B.OracleServiceSID

C.OracleDBConsoleSID

D.OracleJobSchedulerSID

5.下列关于序列的说法正确的是（）。

A.序列一旦创建，就可以立即使用CURRVAL列

B.在引用序列的CURRVAL列前，必须引用过一次NEXTVAL列

C.可以修改序列中的起始值

D.序列的初始值可以从0开始

二、上机练习

在Oracle中使用SQL Plus创建新闻表news，表结构如下所示。

表1-3-1 新闻表

约束

id

number

主键，自增

title

varchar2

非空

content

varchar2

author

varchar2

创建好后在PL/SQL Developer工具中插入测试数据。

Oracle扩展进阶

Sqlplus常用命令

1．显示当前用户

show user

2．建立连接

connect scott/tiger

connect sys/admin as sysdba

3．断开连接

disconnect

4．退出sqlplus

exit

quit

5．执行sql 文件

Start 或者@d:/aa.sql

6．编辑上一次的SQL语句

ed

7．保存输入的代码

Spool

spool d:/b.sql

spool off

8．设置行大小(linesize) 默认80字符

set linesize 150

9．显示行大小

show linesize

10．设置页大小,显示页大小

set pagesize 500

show pagesize

11．select * from scott.emp

12．desc user_tables

13．select table_name from user_tables;

16．SELECT TABLE_NAME FROM USER_TABLES WHERE TABLE_NAME = ‘T1’

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!