sql_bak.sql

这是一个DELPHI7应用案例开发篇有配套程序种子光盘

CREATE TABLE tb_book (
BookSerial Char(30) NOT NULL PRIMARY KEY,
BookName Char(30) NOT NULL,
Publisher	Char(20),
PubDate	Date,
Author1	Char(20),
Author2	Char(20),
Author3	Char(20),
Author4	Char(20),
Author5	Char(20),
SellPrice	Decimal(6,2),
BookCover	Blob,
Abstract	Text,
Content	Text,
Memoir	Text
);

CREATE TABLE tb_buy1(
  BuySerial	INT NOT NULL,
  BookSerial	Char(30) NOT NULL,
  BookCount	Decimal(5) NOT NULL,
  TotalPrice Decimal(6,2) NOT NULL,
  PRIMARY KEY(BuySerial, BookSerial)
);

CREATE TABLE tb_buy2(
  BuySerial	INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  BuyDate	Date,
  Memoir	Text
);

CREATE TABLE tb_store(
  BookSerial	Char(30) NOT NULL PRIMARY KEY,
  BookCount	Decimal(5) NOT NULL,
  Memoir	Text
);

CREATE TABLE tb_customer(
  CustomerSerial	INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  ID	Char(20) NOT NULL,
  Passwd	Char(128) NOT NULL,
  Name	Char(20) NOT NULL,
  Age	Decimal(3),
  Gender	Char(1),
  Address	Char(30) NOT NULL,
  Zip	Char(10) NOT NULL,
  Phone	Char(15) NOT NULL,
  Email Char(30) NOT NULL,
  Mobile	Char(15),
  BankCard	Char(20),
  CardType	Char(5),
  Memoir	Text
);

CREATE TABLE tb_sell1(
  SellSerial	INT NOT NULL,
  BookSerial	Char(30) NOT NULL,
  Count	Decimal(5) NOT NULL,
  DisCount	Decimal(3) DEFAULT 100,
  PRIMARY KEY(SellSerial, BookSerial)
);

CREATE TABLE tb_sell2(
  SellSerial	INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  CustomerSerial Char(20) NOT NULL,
  PayType	Char(1) NOT NULL,
  OrderDate	DateTime,
  SellDate	Date,
  Memoir	Text
);

CREATE TABLE tb_order1(
  OrderSerial	INT NOT NULL PRIMARY KEY,
  BookSerial	Char(30) NOT NULL,
  BookCount	Decimal(5) NOT NULL,
  Discount Decimal(3) DEFAULT 100
);

CREATE TABLE tb_order2(
  OrderSerial	INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  CustomerSerial	Char(20) NOT NULL,
  PayType	Char(1) NOT NULL,
  OrderDate	DateTime,
  OrderState	Char(1),
  Memoir	Text
);

CREATE TABLE tb_user(
  ID	Char(20) NOT NULL PRIMARY KEY,
  Passwd	Char(16),
  Priv	Char(1) NOT NULL DEFAULT 2,
  Name	Char(20),
  Age	Decimal(3),
  Gender	Char(1),
  Address	Char(30),
  Zip	Char(10),
  Phone	Char(15),
  Email	Char(30)
);

CREATE FULLTEXT INDEX index_fulltext ON tb_book (Abstract, Content,
  Memoir);

SELECT * FROM tb_book WHERE MATCH (Abstract, Content) AGAINST ('Delphi');

ALTER TABLE tb_book CHANGE COLUMN BookCover BookCover MEDIUMBLOB;

UPDATE tablename SET pay_back=pay_back+'relative change';

INSERT INTO tb_user (ID, Passwd, Priv) VALUES ('ganzhi','password',1);

CREATE INDEX iPrice ON tb_book (SellPrice);

CREATE INDEX iPubDate ON tb_book (PubDate);

CREATE INDEX iBookName on tb_book (BookName);

CREATE TEMPORARY TABLE tb_Carter(
  BookSerial Char(30) NOT NULL PRIMARY KEY,
  BookCount	Decimal(5) NOT NULL
);

SELECT tb_book.BookSerial AS BookSerial, 
tb_book.BookName AS BookName, 
tb_book.SellPrice AS SellPrice,
tb_Carter.BookCount AS BookCount, 
((tb_book.SellPrice)*(tb_Carter.BookCount)) AS TotalPrice
FROM tb_Carter, tb_book 
WHERE tb_Carter.BookSerial=tb_Book.BookSerial;