createdb.sql

来自「软件工程 卡耐基 数据库 ssd7 ex1」· SQL 代码 · 共 45 行

CREATE TABLE PUBLISHER
(
  PublisherID           INTEGER       NOT NULL,    
  Name                  VARCHAR(25)   NOT NULL,
  Address               VARCHAR(25)  NOT NULL,

  PRIMARY KEY(PublisherID)
  	
);

CREATE TABLE BOOK
(
  PrimaryAuthor        VARCHAR(25)    NOT NULL,  
  Title                VARCHAR(25)    NOT NULL,
  ISBN                 VARCHAR(25)    NOT NULL,
  PublisherID          INTEGER        NOT NULL,
  Edition              INTEGER        NOT NULL,
  DateofPublication    DATE           NOT NULL,    
  Price                DECIMAL(4,2)   NOT NULL,
  BookDescription      VARCHAR(100)    NOT NULL,

  PRIMARY KEY(ISBN),
  UNIQUE(BookDescription),
  FOREIGN KEY(PublisherID) REFERENCES PUBLISHER(PublisherID)  --注意要把PUBLISHER写在前面；
);

INSERT INTO PUBLISHER
VALUES (13001,'Thomas Connolly','Beijing');
INSERT INTO PUBLISHER
VALUES (13002,'Lippman','Xian');
INSERT INTO PUBLISHER
VALUES (10005,'Mechanism','Beijing');
INSERT INTO PUBLISHER
VALUES (10007,'Langman','Beijing');

INSERT INTO BOOK
VALUES ('Thomas Connolly','Database Systems','7-5053-9418-5',13001,3,'2005-9-2',79.00,'hsjsfhgfshgfs');
INSERT INTO BOOK
VALUES ('Lippman','Operating Systems','978-711-10915-6-1',13077,2,'2002-01-01',48.00,'Computer Operation Systems');
INSERT INTO BOOK
VALUES ('James F.Kurose','Computer Networking','538-7-111-16505-7',13002,3,'2006-5-6',55.00,'A Top-Down Approach Featuring the Internet, Third Edition');
INSERT INTO BOOK
VALUES ('Stanley','C++ Primer','978-711-51455-4-3',13066,4,'2006-03-01',50.00,'C++');