?? troda.sql
字號:
%% ============================================================
%% Database name: MASTER_MODEL
%% DBMS name: Sybase SQL Anywhere
%% Created on: 99-7-29 9:00
%% ============================================================
if exists(select 1 from sys.systable where table_name='BOOK_AUTHOR' and table_type='BASE') then
drop table BOOK_AUTHOR
end if;
if exists(select 1 from sys.systable where table_name='SALES_TRANSACT' and table_type='BASE') then
drop table SALES_TRANSACT
end if;
if exists(select 1 from sys.systable where table_name='BOOKS' and table_type='BASE') then
drop table BOOKS
end if;
if exists(select 1 from sys.systable where table_name='CUSTOMERS' and table_type='BASE') then
drop table CUSTOMERS
end if;
if exists(select 1 from sys.systable where table_name='AUTHORS' and table_type='BASE') then
drop table AUTHORS
end if;
if exists(select 1 from sys.systable where table_name='PUBLISHERS' and table_type='BASE') then
drop table PUBLISHERS
end if;
%% ============================================================
%% Table: PUBLISHERS
%% ============================================================
create table PUBLISHERS
(
PUBLISHERS_ID char(10) not null,
PUBLISHERS_FNAME varchar(30) not null,
primary key (PUBLISHERS_ID)
);
%% ============================================================
%% Table: AUTHORS
%% ============================================================
create table AUTHORS
(
AUTHOR_ID integer not null
default AUTOINCREMENT,
AUTHOR_NAME varchar(10) not null,
primary key (AUTHOR_ID)
);
%% ============================================================
%% Table: CUSTOMERS
%% ============================================================
create table CUSTOMERS
(
CUST_ID char(10) not null,
CUST_NAME varchar(30) not null,
CUST_ADDRESS varchar(30) ,
CUST_CITY varchar(20) ,
CUST_ZIP char(6) ,
CUST_PHONE varchar(18) ,
primary key (CUST_ID)
);
%% ============================================================
%% Table: BOOKS
%% ============================================================
create table BOOKS
(
ISBN char(13) not null,
PUBLISHERS_ID char(10) not null,
BOOK_TITLE varchar(30) not null,
BOOK_CATEGORY varchar(20) not null,
BOOK_SUMMARY long varchar ,
BOOK_PRICE decimal(6,2) not null,
BOOK_REBATE decimal(3,2) not null,
BOOK_STOCK_DATE date not null,
BOOK_STOCK_AMOUNT integer not null,
PUBLISH_DATE date ,
BOOK_COVER long binary ,
primary key (ISBN)
);
%% ============================================================
%% Table: SALES_TRANSACT
%% ============================================================
create table SALES_TRANSACT
(
TRANSACT_ID integer not null
default AUTOINCREMENT,
SALES_DATE date not null,
ISBN char(13) not null,
SALES_AMOUNT integer not null,
CUST_ID char(10) not null,
primary key (TRANSACT_ID)
);
%% ============================================================
%% Table: BOOK_AUTHOR
%% ============================================================
create table BOOK_AUTHOR
(
ISBN char(13) not null,
AUTHOR_ID integer not null,
primary key (ISBN, AUTHOR_ID)
);
alter table BOOKS
add foreign key FK_BOOKS_BOOK_PUBL_PUBLISHE (PUBLISHERS_ID)
references PUBLISHERS (PUBLISHERS_ID) on update restrict on delete restrict;
alter table SALES_TRANSACT
add foreign key FK_SALES_TR_CUSTRES_T_CUSTOMER (CUST_ID)
references CUSTOMERS (CUST_ID) on update restrict on delete restrict;
alter table SALES_TRANSACT
add foreign key FK_SALES_TR_BOOK_SALE_BOOKS (ISBN)
references BOOKS (ISBN) on update restrict on delete restrict;
alter table BOOK_AUTHOR
add foreign key FK_BOOK_AUT_BOOK_AUTH_BOOKS (ISBN)
references BOOKS (ISBN) on update restrict on delete restrict;
alter table BOOK_AUTHOR
add foreign key FK_BOOK_AUT_BOOK_AUTH_AUTHORS (AUTHOR_ID)
references AUTHORS (AUTHOR_ID) on update restrict on delete restrict;
%% ============================================================
%% 為表publishers錄入數據
%% ============================================================
INSERT INTO publishers VALUES (
'302',
'清華大學出版社');
INSERT INTO publishers VALUES (
'111',
'機械工業出版社');
INSERT INTO publishers VALUES (
'301',
'北京大學出版社');
INSERT INTO publishers VALUES (
'951',
'上海科學技術文獻出版社');
INSERT INTO publishers VALUES (
'505',
'電子工業出版社');
INSERT INTO publishers VALUES (
'906',
'內蒙古大學出版社');
%% ============================================================
%% 為表authors錄入數據
%% ============================================================
INSERT INTO authors VALUES (
1,
'（美）Harley Hahn');
INSERT INTO authors VALUES (
3,
'王朝陽');
INSERT INTO authors VALUES (
4,
'（美）JimO''Donnell Eric Ladd');
INSERT INTO authors VALUES (
5,
'呂睿烜');
INSERT INTO authors VALUES (
6,
'（美）Peter Kent');
INSERT INTO authors VALUES (
7,
'（美）Phil James');
INSERT INTO authors VALUES (
8,
'王佑中');
INSERT INTO authors VALUES (
9,
'梁春梅');
INSERT INTO authors VALUES (
10,
'創德工作室');
INSERT INTO authors VALUES (
11,
'侯俊耀');
INSERT INTO authors VALUES (
12,
'何 渝');
INSERT INTO authors VALUES (
13,
'（美）Novell公司');
INSERT INTO authors VALUES (
14,
'程 鵬');
INSERT INTO authors VALUES (
15,
'金 海');
INSERT INTO authors VALUES (
16,
'劉新宇');
INSERT INTO authors VALUES (
17,
'曹立明');
INSERT INTO authors VALUES (
18,
'賴才金');
INSERT INTO authors VALUES (
19,
'（美）科默（Comer,D.E.）');
INSERT INTO authors VALUES (
20,
'斐胡福');
INSERT INTO authors VALUES (
21,
'周世雄');
INSERT INTO authors VALUES (
22,
'（美）Barrle Sosinsky');
INSERT INTO authors VALUES (
23,
'Microsoft公司');
INSERT INTO authors VALUES (
30,
'王新梅');
INSERT INTO authors VALUES (
31,
'李勇');
INSERT INTO authors VALUES (
32,
'康博創作室');
INSERT INTO authors VALUES (
33,
'馮志強');
INSERT INTO authors VALUES (
29,
'溫為民');
%% ============================================================
%% 為表customers錄入數據
%% ============================================================
INSERT INTO customers VALUES (
'PUBLIC00',
'普通用戶',
'無',
'無',
'000000',
'0000');
INSERT INTO customers VALUES (
'NM000001',
'內蒙古農業發展銀行',
'呼和浩特烏蘭察布路18號',
'呼和浩特',
'010010',
'(0471)4939664');
INSERT INTO customers VALUES (
'SD000001',
'山東大學',
'山大學路17號',
'濟南市',
'010010',
'(0531)8940242');
INSERT INTO customers VALUES (
'LN000001',
'沈陽市財政局信息處',
'沈陽市和平區北京北街103號',
'沈陽市',
'110000',
'(024)22833660');
INSERT INTO customers VALUES (
'LN000002',
'大連科海電子',
'大連市濱洲路79號',
'大連市',
'116000',
'(0411)7951440');
INSERT INTO customers VALUES (
'BJ000001',
'北京華興電子書店',
'北京五一東路238號',
'北京市',
'100000',
'(010)62641574');
INSERT INTO customers VALUES (
'BJ000002',
'水利電力科學院',
'建國門外大街121號',
'北京市',
'100000',
'(010)85454124');
INSERT INTO customers VALUES (
'BJ000003',
'北京市兆維集團',
'北京市朝陽區九仙橋路2號',
'北京市',
'100000',
'(010)65413547');
INSERT INTO customers VALUES (
'BJ000004',
'中國共青團中央',
'北京市長安街48號',
'北京市',
'100000',
'(010)68451241');
INSERT INTO customers VALUES (
'TJ000001',
'天津市新華書店',
'天津市南開區東南角23號',
'天津市',
'300000',
'(022)24133774');
%% ============================================================
%% 為表books錄入數據
%% ============================================================
INSERT INTO books VALUES (
'7-302-00860-4',
'302',
'全球Internet網址薄（1998版）',
'計算機',
'本書包括全球最熱門、最有應用價值的Intrenet站點，是每一位在Internet這廣袤世界中探索求知的人們所必備的工具書和案頭卷。
上網必備，行動指南。',
56.00,
0.70,
'1998-10-12',
10,
'1998-05-03',
null);
INSERT INTO books VALUES (
'7-302-01034-X',
'302',
'中文FrontPage98網頁設計',
'計算機',
'本書介紹FrontPage98的有關內容，
作者以由淺入深、由低級到高級的順序為讀者展示了利用
中文FrontPage98進行網頁設計的全過程。全書包括大量簡單實用的例子，
稍加屐就可應用于讀者自己的頁面。',
73.00,
0.65,
'1999-04-14',
7,
'1999-03-10',
null);
INSERT INTO books VALUES (
'7-302-02621-1',
'302',
'中文Internet Explorer 4開發',
'計算機',
'本書是一本全面介紹Internet Explorer 4的著作。它從Internet Explorer 4的新特性入手，非常詳細由淺入深地講解這個軟件。全書共分５個部分：入門知識、深入學習、相關程序、Web廣播和Web管理員。本書適合各種
層次的使用者學習，圖文并茂，細致透徹。',
?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -