---------- not_null.txt ----------
create table sm_emp_null
(EmpID char(10) primary key,
Name varchar2(10) NOT NULL,
salary number(8,2) NOT NULL,
TelNo char(8));
insert into sm_emp_null values('000000001','Tom',100,Null);
insert into sm_emp_null values('000000002',null,100,Null);
---------- unique.txt ----------
/*
* 范例名称：UNIQUE
* 文件名称：unique.txt
*/
--什么含义呢？认为相同姓名，雇佣日期的雇员是同一个人，不再输入
--雇员，id,姓，名，雇佣日期
create table plsql101_person(
person_code varchar2(3),
first_name varchar2(15),
last_name varchar2(20),
hire_date date);
ALTER TABLE plsql101_person
ADD CONSTRAINT person_unique UNIQUE (
first_name,
last_name,
hire_date
)
;
INSERT INTO plsql101_person VALUES (
'01', '张', '飞', '01-6月-03');
--第二条如何呢？
INSERT INTO plsql101_person VALUES (
'02', '张', '飞', '01-6月-03');
---------- check.txt ----------
/*
* 范例名称：CHECK
* 文件名称：check.txt
*/
--工资必须 CREATE TABLE sm_emp_check(
empid VARCHAR2(3),
name VARCHAR2(10),
salary NUMBER(8,2) CHECK (salary
INSERT INTO sm_emp_check VALUES('001', 'JACK', 100);
INSERT INTO sm_emp_check VALUES('002', 'JACK2', 101);
INSERT INTO sm_emp_check VALUES('003', 'JACK3', 10000);
--练习结果：
CREATE TABLE sm_emp_check(
empid VARCHAR2(3),
name VARCHAR2(10),
salary NUMBER(8,2) CHECK (salary >100 and salary INSERT INTO sm_emp_check VALUES('001', 'JACK', 100);
INSERT INTO sm_emp_check VALUES('002', 'JACK2', 101);
INSERT INTO sm_emp_check VALUES('003', 'JACK3', 10000);
INSERT INTO sm_emp_check VALUES('003', 'JACK3', null);
--采购日期必须在2000-06-30以后
ALTER TABLE plsql101_purchase
ADD ( CONSTRAINT reasonable_date
CHECK(purchase_date IS NOT NULL
AND TO_CHAR(purchase_date, 'YYYY-MM-DD') >= '2000-06-30'
)
)
;
INSERT INTO plsql101_purchase VALUES (
'Small Widget', 'GA', '28-2月-00', 10);
---------- constraint_all.txt ----------
/*
* 范例名称：CONSTRAINT综合练习
* 文件名称：constraint_all.txt
*/
--清除plsql101_product的约束
alter table plsql101_product drop constraint positive_quantity;
alter table plsql101_product drop constraint valid_price;
alter table plsql101_product drop constraint reasonable_date
名称 产品库存表 :plsql101_product
------------------------------------------------
PRODUCT_NAME 产品名称
PRODUCT_PRICE 产品单价
QUANTITY_ON_HAND 库存数
LAST_STOCK_DATE 最后进货日期
--要求产品单价在0--10000,且必须确定。
ALTER TABLE plsql101_product ADD (
CONSTRAINT valid_price CHECK(
product_price IS NOT NULL
AND
product_price BETWEEN 0 AND 99
)
)
;
--要求产品库存〉0，且必须有确定值。
ALTER TABLE plsql101_product ADD (
CONSTRAINT positive_quantity CHECK(
quantity_on_hand IS NOT NULL
AND
quantity_on_hand >=0
)
)
;
--要求产品最后进货日在2001/12/31以后
ALTER TABLE plsql101_product ADD (
CONSTRAINT reasonable_date CHECK(
TO_CHAR(last_stock_date, 'YYYY-MM-DD') >= '2001-12-31'
)
)
;
INSERT INTO plsql101_product VALUES (
'Small Widget', NULL, 1, '28-2月-02');
--price是null
--price>99
INSERT INTO plsql101_product VALUES (
'Small Widget', 99.5, 1, '28-2月-02');
INSERT INTO plsql101_product VALUES (
'Small Widget', 1.95, -5, '28-2月-02');
INSERT INTO plsql101_product VALUES (
'Small Widget', 1.95, 1, '30-10月-2001');
--测试null是否可以输入last_stock_date。
INSERT INTO plsql101_product VALUES (
'Anodized Framifier', 49, 5, NULL)
;
-- 练习: 要求产品最后采购日在2000-06-30以后:-----------!!!不用！-----------
ALTER TABLE plsql101_purchase ADD (
CONSTRAINT reasonable_date CHECK(
purchase_date IS NOT NULL
AND
TO_CHAR(purchase_date, 'YYYY-MM-DD') >= '2000-06-30'
)
)
;
INSERT INTO plsql101_purchase VALUES (
'Small Widget', 10, '28-2月-00', 'GA');
-- 练习: 要求产品最后进货日在2001/12/31以后,
ALTER TABLE plsql101_product ADD (
CONSTRAINT reasonable_stock_date CHECK(
TO_CHAR(last_stock_date, 'YYYY-MM-DD') >= '2001-12-31'
)
)
;
--测试null是否可以输入。
INSERT INTO plsql101_product VALUES (
'Anodized Framifier', 49, 5, NULL)
;
INSERT INTO plsql101_product VALUES (
'Spring-Loaded Pit Puller', 49, 5, '30-DEC-01')
;
-----------------------------------------------------------------------
---------- disable_enable.txt ----------
/*
* 范例名称：启用和禁止已有的约束条件
* 文件名称：disable_enable.txt
*/
--如已建立reasonable_date constraint则不执行！
ALTER TABLE plsql101_product ADD (
CONSTRAINT reasonable_date CHECK(
TO_CHAR(last_stock_date, 'YYYY-MM-DD') >= '2001-12-31'
)
)
;
INSERT INTO plsql101_product VALUES (
'Red Snaphoo', 1.95, 10, '30-12月-01')
;
ALTER TABLE plsql101_product DISABLE CONSTRAINT reasonable_date;
INSERT INTO plsql101_product VALUES (
'Red Snaphoo', 1.95, 10, '30-12月-01')
;
ALTER TABLE plsql101_product ENABLE CONSTRAINT reasonable_date;
--ERROR 位于第 1 行:
--ORA-02293: 无法验证 (SCOTT.REASONABLE_DATE) - 违反检查约束条件
--已经在table 有错误数据。rollbak可以吗？
UPDATE plsql101_product
SET last_stock_date = '31-12月-01'
WHERE last_stock_date = '30-12月-01';
ALTER TABLE plsql101_product ENABLE CONSTRAINT reasonable_date;
---------- alter_null.txt ----------
/*
* 范例名称：更改和删除已有的约束条件
* 文件名称：alter_null.txt
*/
ALTER TABLE plsql101_person MODIFY (first_name NULL);
/*
--雇员，id,姓，名，雇佣日期
create table plsql101_person(
person_code varchar2(3),
first_name varchar2(15),
last_name varchar2(20),
hire_date date);
*/
--是什么实际含义？必须要求姓，名都知道！不许李某
ALTER TABLE plsql101_person MODIFY (first_name NOT NULL);
ALTER TABLE plsql101_person MODIFY (last_name NULL);
---------- drop_constraint.txt ----------
/*
* 范例名称：更改和删除已有的约束条件
* 文件名称：drop_constraint.txt
*/
INSERT INTO plsql101_product VALUES (
'Blue Snaphoo', 1.95, 10, '30-12月-01')
;
ALTER TABLE plsql101_product DROP CONSTRAINT reasonable_date;
INSERT INTO plsql101_product VALUES (
'Blue Snaphoo', 1.95, 10, '30-12月-01')
;
---------- primary_key.txt ----------
/*
* 范例名称：表的主键
* 文件名称：primary_key.txt
*/
--首先需运行insert_data.sql准备数据
--@d:\insert_data.sql
ALTER TABLE plsql101_product
ADD PRIMARY KEY (product_name);
ALTER TABLE plsql101_person
ADD PRIMARY KEY (person_code);
ALTER TABLE plsql101_purchase
ADD PRIMARY KEY (product_name,
salesperson,
purchase_date
)
;
--when create table
CREATE TABLE plsql101_product2 (
product_name VARCHAR2(25) PRIMARY KEY,
product_price NUMBER(4,2),
quantity_on_hand NUMBER(5,0),
last_stock_date DATE
)
;
CREATE TABLE plsql101_product3 (
product_name VARCHAR2(25),
product_price NUMBER(4,2),
quantity_on_hand NUMBER(5,0),
last_stock_date DATE,
PRIMARY KEY (product_name)
)
;
---------- foreign_key.txt ----------
/*
* 范例名称：FOREIGN KEY
* 文件名称：foreign_key.txt
*/
--必须首先执行primary_key.txt
ALTER TABLE plsql101_purchase
ADD CONSTRAINT plsql101_purchase_fk_product
FOREIGN KEY (product_name)
REFERENCES plsql101_product;
--当REFERENCES 的表没有PK
--ERROR 位于第 4 行:
--ORA-02268: 引用的表不具有主关键字
--再次建立
ALTER TABLE plsql101_purchase
ADD CONSTRAINT plsql101_purchase_fk_product
FOREIGN KEY (product_name)
REFERENCES plsql101_product;
--子表中有，父表中没有的纪录
select plsql101_product.product_name,plsql101_purchase.product_name
from plsql101_product,plsql101_purchase
where plsql101_product.product_name(+)=plsql101_purchase.product_name
--删除子表孤儿纪录，再次建立
delete from plsql101_purchase where product_name='Round Snaphoo';
ALTER TABLE plsql101_purchase
ADD CONSTRAINT plsql101_purchase_fk_product
FOREIGN KEY (product_name)
REFERENCES plsql101_product;
--ok
/***************************************************************
-- 练习: 也可以用update改变子表（plsql101_purchase）的值 creating the FK constraint
UPDATE plsql101_purchase
SET product_name = 'Round Chrome Snaphoo'
WHERE product_name = 'Round Snaphoo';
ALTER TABLE plsql101_purchase
ADD CONSTRAINT plsql101_purchase_fk_product
FOREIGN KEY (product_name)
REFERENCES plsql101_product;
**************************/
-- 练习: Attempting to enter a child record without a matching
-- 插入子表(plsql101_purchase)
INSERT INTO plsql101_purchase VALUES (
'Small Widgee', 'CA', '17-7月-03', 1)
;
--ERROR 位于第 1 行:
--ORA-02291: 违反完整约束条件 (SCOTT.PLSQL101_PURCHASE_FK_PRODUCT) - 未找到父项关键字
--为什么呢？Small Widgee与Small Widget不同
-- Corrected version
--子表insert的纪录必须满足fk约束，在父表对应字段有相应值
INSERT INTO plsql101_purchase VALUES (
'Small Widget', 'CA', '17-7月-03', 1)
;
--
UPDATE plsql101_purchase
SET product_name = 'Small Widget_11'
WHERE product_name = 'Small Widget';
ERROR 位于第 1 行:
ORA-02291: 违反完整约束条件 (SCOTT.PLSQL101_PURCHASE_FK_PRODUCT) - 未找到父项关键字
UPDATE plsql101_purchase
SET product_name = 'Round Chrome Snaphoo'
WHERE product_name = 'Small Widget';
--OK.select * from plsql101_PRODUCT,product_name字段有Round Chrome Snaphoo
-- 为plsql101_purchase建立FOREIGN KEY ,通过salesperson字段
--与plsql101_person建立关联
ALTER TABLE plsql101_purchase
ADD CONSTRAINT plsql101_purchase_fk_person
FOREIGN KEY (salesperson)
REFERENCES plsql101_person;
---------- multi_table.txt ----------
/*
* 范例名称：多表查询
* 文件名称：multi_table.txt
*/
--重新生成数据,运行insert_data.txt
名称 plsql101_product 商品信息表
--------------------
PRODUCT_NAME 商品名称
PRODUCT_PRICE 商品单价
QUANTITY_ON_HAND 商品库存
LAST_STOCK_DATE 商品最后采购日期
名称 采购信息表plsql101_purchase
----------------------------
PRODUCT_NAME 商品名称
SALESPERSON 经手人(编号)
PURCHASE_DATE 采购日期
QUANTITY 采购数量
名称 人员表plsql101_person
--------------------
PERSON_CODE 人员编号
FIRST_NAME 姓
LAST_NAME 名
HIRE_DATE 雇佣日期
--查询采购信息和对应经手人的各人信息：图形分析：补充3_product.ppt
SELECT plsql101_purchase.product_name,
plsql101_person.last_name,
plsql101_person.first_name,
plsql101_purchase.quantity
FROM plsql101_purchase,
plsql101_person
WHERE plsql101_person.person_code = plsql101_purchase.salesperson
;
---------- multi_table_j.txt ----------
/*
* 范例名称：多表查询的笛卡尔积
* 文件名称：multi_table_j.txt
*/
SELECT plsql101_purchase.product_name,
plsql101_person.last_name,
plsql101_person.first_name,
plsql101_purchase.quantity
FROM plsql101_purchase,
plsql101_person
;
---------- join.txt ----------
/*
* 范例名称：JOIN
* 文件名称：join.txt
*/
--采购的商品，此商品的单价，采购总量，对应经手人是谁：
--
SELECT plsql101_purchase.product_name,
plsql101_product.product_price,
plsql101_purchase.quantity,
plsql101_person.last_name
FROM plsql101_product,
plsql101_person,
plsql101_purchase
WHERE plsql101_product.product_name = plsql101_purchase.product_name
and
plsql101_person.person_code = plsql101_purchase.salesperson
;
-- 采购日期，数量，商品最后（上次）采购日期，对应经手人是谁：
SELECT plsql101_purchase.purchase_date,
plsql101_purchase.quantity,
plsql101_product.last_stock_date,
plsql101_person.last_name
FROM plsql101_product,
plsql101_person,
plsql101_purchase
WHERE plsql101_product.product_name = plsql101_purchase.product_name
and
plsql101_person.person_code = plsql101_purchase.salesperson
;
---------- outer_join.txt ----------
/*
* 范例名称：外部连接outer join
* 文件名称：outer_join.txt
*/
SELECT product_name FROM plsql101_product ORDER BY product_name;
--注意：可以在from 语句中为表名 指定别名。
--在select,where中 用 别名.字段名 代替 表名.字段名
--如： plsql101_product prod ：为plsql101_product表指定别名为prod
--商品名称，此商品的单价，采购日期，数量：采购的详细信息
SELECT prod.product_name,
prod.product_price,
purc.purchase_date,
purc.quantity
FROM plsql101_product prod,
plsql101_purchase purc
WHERE prod.product_name = purc.product_name
ORDER BY prod.product_name;
--注意：(+)在字段后面:
--商业含义：商品名称，此商品的单价，采购日期，数量
--以及在商品表中但从未采购过的商品
SELECT prod.product_name,
prod.product_price,
purc.purchase_date,
purc.quantity
FROM plsql101_product prod,
plsql101_purchase purc
WHERE prod.product_name = purc.product_name (+)
ORDER BY prod.product_name;
