3.Oracle数据库SQL开发之 创建store的schema

3.Oracle数据库SQL开发之 创建store的schema

 欢迎转载，转载请标明出处：http://blog.csdn.net/notbaron/article/details/49082329

1.  创建store模式

创建脚本在源码中有，地址http://download.csdn.net/detail/notbaron/9106673

脚本名字：store_schema.sql

通过sqlplus / as sysdba 连接

执行

SQL>@store_schema.sql

会创建store用户，密码store_password

也可以通过SQL_DEVELPER来创建，连接的时候先用SYS用户来连接，然后执行store_schema.sql中的代码即可。如下

图1

共花费9.966秒完成。

可能需要grant多一点权限，例如dba权限给store.

GRANTconnect, resource,dba TO store;

然后修改日期如下：

SQL>show parameter nls_date_language;

在CONNECT store/store_password;

这行代码后加入如下，不然插入有误：

altersession set nls_date_language='AMERICAN';

 

2.  Store_schema.sql语句解释

创建用户命令

CREATEUSER store IDENTIFIED BY store_password;

授权

GRANTconnect, resource TO store;

2.1         表

主要有六个表分别是

l  顾客详细信息

l  销售的产品类型

l  产品的详细信息

l  顾客购买产品的历史记录

l  员工的信息

l  工资等级

表名字分别是：Customers，product_types，products，purchases，employees，salary_grades

2.1.1             语句如下

2.1.2             Customers

存储顾客详细信息，

查看customers如下：

SQL> desc customers;

 Name                                             Null?   Type

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

 CUSTOMER_ID                                     NOT NULL NUMBER(38)

 FIRST_NAME                                NOT NULL VARCHAR2(10)

 LAST_NAME                                 NOT NULL VARCHAR2(10)

 DOB                                                          DATE

 PHONE                                                     VARCHAR2(12)

有名，姓，生日，电话号码，用户ID。

创建语句如下：

CREATE TABLE customers (

 customer_id INTEGER CONSTRAINT customers_pk PRIMARY KEY,

  first_nameVARCHAR2(10) NOT NULL,

 last_name VARCHAR2(10) NOT NULL,

  dobDATE,

 phone VARCHAR2(12)

);

用户ID ，customer_id是唯一整数，一个表中一列或多列表示表中的每行，这样的列称为主键（PRIMARY KEY）。CONSTRAINT 表示约束，也说名字为customers_pk

Dob存储客户端生日可以为空。

查看表中内容如下：

SQL> select * from customers;

CUSTOMER_ID FIRST_NAME LAST_NAME  DOB     PHONE

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

           1 John      Brown   01-JAN-65 800-555-1211

           2 Cynthia   Green     05-FEB-68 800-555-1212

           3 Steve     White     16-MAR-71 800-555-1213

           4 Gail      Black                  800-555-1214

           5 Doreen    Blue       20-MAY-70

2.1.3             product_types

销售的产品类型

创建命令如下：

CREATE TABLE product_types (

 product_type_id INTEGER CONSTRAINT product_types_pk PRIMARY KEY,

 name VARCHAR2(10) NOT NULL

);

其中product_type_id是主键，有唯一的整数值。

Name包含产品类型名称。

插入完毕后查询结果如下：

SQL>select * from product_types;

PRODUCT_TYPE_ID NAME

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

               1 Book

               2 Video

               3 DVD

               4 CD

               5 Magazine

 

2.1.4             products

产品的详细信息。

存储产品名称，产品介绍，产品价格。

创建命令如下：

CREATE TABLE products (

 product_id INTEGER CONSTRAINT products_pk PRIMARY KEY,

 product_type_id INTEGER

   CONSTRAINT products_fk_product_types

   REFERENCES product_types(product_type_id),

 name VARCHAR2(30) NOT NULL,

 description VARCHAR2(50),

 price NUMBER(5, 2)

);

其中product_id是主键

Product_type_id为每个产品都关联一种产品类型。该列是对product_types表中product_type_id列的引用，称为外键，因为引用了其他表的列。包含这个外键的表（products表）称为明细表(detail table)或子表（child table）.。被引用的表product_types表称为主表(master table)或父表（parent table）.这种主从关系或者父子关系。当添加一个新产品时，应该通过在product_type_id列中提供一个匹配的product_types.product_type_id值。

查看如下：

SQL> select * from products;

 

PRODUCT_ID PRODUCT_TYPE_ID NAME

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

DESCRIPTION                                                 PRICE

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

          1              1 Modern Science

A description of modern science                       19.95

 

          2              1 Chemistry

Introduction to Chemistry                                      30

 

          3              2 Supernova

A star explodes                                            25.99

 

 

PRODUCT_ID PRODUCT_TYPE_ID NAME

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

DESCRIPTION                                                 PRICE

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

          4              2 Tank War

Action movie about a future war                      13.95

 

          5              2 Z Files

Series on mysterious activities                          49.99

 

          6              2 2412: The Return

Aliens return                                                  14.95

 

 

PRODUCT_ID PRODUCT_TYPE_ID NAME

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

DESCRIPTION                                                 PRICE

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

          7              3 Space Force 9

Adventures of heroes                                            13.49

 

          8              3 From Another Planet

Alien from another planet lands on Earth                 12.99

 

          9              4 Classical Music

The best classical music                              10.99

 

 

PRODUCT_ID PRODUCT_TYPE_ID NAME

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

DESCRIPTION                                                 PRICE

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

         10              4 Pop 3

The best popular music                                        15.99

 

         11              4 Creative Yell

Debut album                                                  14.99

 

         12                My Front Line

Their greatest hits                                        13.49

12 rows selected.

其中product_type_id值为1 ，说明该产品是一本书。Product_type_id值来自于product_types表。

2.1.5             purchases

顾客购买产品的历史记录。需要存储：

产品ID,顾客ID，该顾客购买的产品数量。

创建命令如下：

CREATE TABLE purchases (

 product_id INTEGER

   CONSTRAINT purchases_fk_products

   REFERENCES products(product_id),

 customer_id INTEGER

   CONSTRAINT purchases_fk_customers

    REFERENCES customers(customer_id),

 quantity INTEGER NOT NULL,

 CONSTRAINT purchases_pk PRIMARY KEY (product_id, customer_id)

);

其中product_id存储所购买产品的ID，必须与products表中某一行的product_id列的值匹配。

Customer_id存储购买产品的顾客ID，必须与customers表中某一行的customer_id列的值匹配

Quantity存储所购买产品的数量。

有一个名为purchases_pk的主键约束，约束跨越了表中的两列：product_id和customer_id. 对于该表中的每行记录，这两列值的组合必须唯一。当主键有多列组成时，称为复合主键。

查看如下：

SQL> select * from purchases;

 

PRODUCT_ID CUSTOMER_ID QUANTITY

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

          1          1              1

          2          1              3

          1          4              1

          2          2              1

          1          3              1

          1          2              2

          2          3              1

          2          4              1

          3          3              1

 

9 rows selected.

2.1.6             employees

员工的信息，包括以下内容：员工ID，如果有上级管理者存储上级管理者员工，名，姓，职位，工资。

创建命令如下：

CREATE TABLE employees (

 employee_id INTEGER CONSTRAINT employees_pk PRIMARY KEY,

  manager_idINTEGER,

 first_name VARCHAR2(10) NOT NULL,

 last_name VARCHAR2(10) NOT NULL,

 title VARCHAR2(20),

 salary NUMBER(6, 0)

);

查询如下：

SQL>select * from employees;

 

EMPLOYEE_ID MANAGER_ID FIRST_NAMELAST_NAME  TITLE                SALARY

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

           1         James           Smith     CEO                         800000

           2       1 Ron        Johnson   Sales Manager              600000

           3       2 Fred       Hobbs     Salesperson         150000

           4       2 Susan    Jones     Salesperson          500000

2.1.7             salary_grades

工资等级，包括以下内容：工资等级ID，该级工资的最低工资，该级工资的最高工资。

创建命令如下：

CREATE TABLE salary_grades (

 salary_grade_id INTEGER CONSTRAINT salary_grade_pk PRIMARY KEY,

 low_salary NUMBER(6, 0),

 high_salary NUMBER(6, 0)

);

查询如下：

SQL>select * from salary_grades;

SALARY_GRADE_ID LOW_SALARY HIGH_SALARY

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

               1           1     250000

               2    250001     500000

               3    500001     750000

               4    750001     999999