网上书店管理系统数据库MySQL

1、新建数据库Create database BookData;Use BookData;新建表B1create table Tbl_Customer(Customer decimal not null,Customer_Name varchar(10),Address varchar(20),City varchar(20),State varchar(20),Zip varchar(6),Referred decimal);插入数据insert into tbl_customer values(1001,'张三','白下区','南京','

2、江苏','210000',null );insert into tbl_customer values(1002,'李四','徐汇区','上海','上海','110000', null);insert into tbl_customer values(1003,'王五','朝阳区','北京','北京','110000',null);insert into tbl_customer values(1004,'

3、;赵六','北大街','无锡','江苏','110000', null);insert into tbl_customer values(1005,'李三','平江路','苏州','江苏','110000', nuII);insert into tbl_customer values(1006,'陈三','升州路','南京','江苏','110000', null)

4、;insert into tbl_customer values(1007,'朱三','三牌楼','合肥','安徽','110000',1003);新建表B2create table Tbl_Books(Isbn varchar(10) not null,Title varchar(300),Pubdate Date,Pubid decimal(2),Costs decimal(5,2),Retail decimal(5,2),Categorys varchar(12);插入数据insert into tbl_bo

5、oks values('98',' 一天中 10 分钟的身体成分','2001-1-21',4,'健康');insert into tbl_books values('03','米奇的复仇','2001-12-12',1,22,'家庭生活');insert into tbl_books values('10','用牙签造车','2002-08-18',2,59,'儿童');insert into tbl_

6、books values('13','数据库的实现','1999-06-04' ,3,31,55,'计算机');insert into tbl_books values('90','用蘑菇烹饪','2000-02-28' ,4,'烹饪');insert into tbl_books values('68','数据库法宝','2001-11-30' ,3,47,75,'计算机');新建表B3create t

7、able Tbl_Orders(Opderj decimal(4) not n ull,Customer decimal(4),Orderdate Date, ShipdateDate , Shipstreet varchar(18) ,Shipcity varchar(15),Shipstate varchar(2),Shipzip varchar(6);修改数据长度时用alter table表名modify字段名称字段类型插入数据insert into Tbl_Orders values(1000,1005, '2003-03-31' , '2003-04-02&#

8、39;,'奥体大街','南京','江苏','210019');in sert in to Tbl_Ordersvalues(1001,1010, '2003-03-31','2003-04-01','和会街.，'南京','江苏','210001');in sert in to Tbl_Ordersvalues(1002,1011, '2003-03-31','2003-04-01','南京路.，'

9、上海','上海','201100');in sert in to Tbl_Ordersvalues(1003,1001, '2003-04-01','2003-04-01','南京路.，'上海','上海','201100');in sert in to Tbl_Ordersvalues(1004,1020, '2003-04-01','2003-04-05','欧风街'、,'无锡','江苏'

10、;,'204002');新建表B4create table Tbl_Oraderitems(orders decimal(4) not null,ltemdecimal(2) not null,lsbnvarchar(10),qua ntity decimal(3);插入数据insert into Tbl_Oraderitems values(1000,1,'90',1);in sert into Tbl_Oraderitems values(1001,1,'01',1);in sert into Tbl_Oraderitems values(1

11、001,2,'20',1);in sert into Tbl_Oraderitems values(1002,1,'32',2);in sert into Tbl_Oraderitems values(1003,1,'32',1);MySQL语句可以放在同一行；但是 DB2不可以新建表B5create table Tbl_Author(Authorld varchar(4) not n ull, name varchar(20);插入数据insert into Tbl_Author values('s100','薛明

12、9;);insert into Tbl_Author values('j100','陈洪');insert into Tbl_Author values('a100','张二');insert into Tbl_Author values('m100','刘虎');insert into Tbl_Author values('k100','陈康');新建表B6create table Tbl_BookAuthor(Isb n varchar(10) not n ull

13、,Authorld varchar(4) not n ull);drop table Tbl_BookAuthor插入数据in sert into Tbl_BookAuthor values('98','s100');in sert into Tbl_BookAuthor values('98','p100');in sert into Tbl_BookAuthor values('03','j100');in sert into Tbl_BookAuthor values('10'

14、,'k100');in sert into Tbl_BookAuthor values('13','p105');in sert into Tbl_BookAuthor values('13','a100');in sert into Tbl_BookAuthor values('13','a105');in sert into Tbl_BookAuthor values('90','b100');in sert into Tbl_BookAuthor

15、 values('68','a100');in sert into Tbl_BookAuthor values('92','w100');in sert into Tbl_BookAuthor values('92','w105');in sert into Tbl_BookAuthor values('21','j100');in sert into Tbl_BookAuthor values('20','r100');in sert

16、 into Tbl_BookAuthor values('20','f100');in sert into Tbl_BookAuthor values('20','b100');in sert into Tbl_BookAuthor values('09','s100');in sert into Tbl_BookAuthor values('91','r100');in sert into Tbl_BookAuthor values('01',

17、9;s100');in sert into Tbl_BookAuthor values('01','w100');in sert into Tbl_BookAuthor values('90','w105');新建表B7,Con tactcreate table Tbl_Publisher(Pubid varchar(10) not n ull,Name varchar(23) Varchar(15),Phone varchar(12);插入数据insert into Tbl_Publisher values('1

18、','新华出版社','程明','000-714-8321');insert into Tbl_Publisher values('2','扬子出版社','张译','0');insert into Tbl_Publisher values（'3','人民教育出版社','园艺','044-714-8321'）;insert into Tbl_Publisher values（'4','北京大学

19、出版社','郑爽','000-564-8321'）;insert into Tbl_Publisher values（'5','机械工业出版社','陆华','000-714-8751'）;新建表B8create table Tbl_Promoti on（ geft varchar（100）,m in retail decimal（5,2）,maxretail decimal（5,2）; 插入数据insert into Tbl_Promotion values（'书签',0,1

20、2）;insert into Tbl_Promotion values（'书签题题',25）;insert into Tbl_Promotion values（'图书圭寸面',56）;insert into Tbl_Promotion values（'免费购物券',;select * from Tbl_Customer;iVSQl> seJect * from IM.Custoner；1 REfepwd !1011002180310Q4）00510061002BtsllB区区区街路路楼 下C阳盍州牌白徐朝北平升三江上北江江江安! s n -

21、I Hi- H u tH亠香亠京肥南上北零南台:210000 ! ；110060 ! 110300 I ! 11Q6Q8 !0 00 0i 11 1NULL ! NULL !NULL INULL !HULL ! HULL : 1（J03 ! Customer ! Custonei'_Naine ! Addpess ! City I StJite I Ziprows in set <0-00 ?ec>select * from Tbl_Books;mys=c|L、select * f i*om Tbl_Books:_l4I Title! Pubdftte! Pub id !

22、CxK !I Ca! 10598311?8 康 ii 3401140733庭生活iI 4781341710I 一天中辺分钟的身体成分!米奇的复优!屈牙签造车2001-01-2丄 i2001-12-1Z i2002-08-18 !4 i IS.75 i 30.?51 i 11.20It；S843172113當机:! 3437212490饪! 39S713t4&8 km!斗一一 1 1一'i数据库的实现!用蘑菇烹饪!数据库法宝19?9-0&-04 :2000-02-28 !2001-11-30 !4 ! 12.0 !19.953 ! 47.! 7S.I3Bi*ous： In

23、 醫吕上 CB. 09select * from Tbl_Orders;pi争昏qL> select * Fr(?n Thl-Orders; 0t(1ul客 i Cu&ton&p i Ord&rdatc ShipdAe e I Shipsip I4-f-|-9 Shipstreet i ShipGittj Shipstatloaa i ! 21001?Isol i i 210O011002 ； ! 2011001003 ! :1004 : ! 2940021305 ： 2003-03-311010 ； 2003-B3-311011 I 2003-03-311001

24、 : 2003-04-011020 i 2003-04-01pawe in set (0.80 eec>select * from Tbl_Oraderitems ;:2003-04-02E 2003-04-01:2003-04-01:20G3-04-01:2003-04-05:奥体大街:南京路=南京路S欧冈街s南京；上海!上海i无锡江苏江苏上海上悔江苏i quantity1S00 :1 11 3437212450 !1 1 !1001 :1 !1 72473310Q1 11 ；1001 ；2 11 247174832S 11 1 11002 :1 1；8843174832 :! 2 1

25、1003 :1 !1 8843174832 11 1 !叫卜+音rows in set<0.00 sec>TiysqlL select * Fi*oin Tbl-Ot'aclei?items I4-4* orders ! Item Isbnselect * from Tbl_Author;mysql> select * fi*o Tbl_fiuthoj*；+晋4-i Authop 1d i name !一 0 0 0 0 0 -一 0 0 0 0 0 - 11111 -s J a n k - - 卄 n t 1 " 1 4 H 4十；：:1+- 一明洪二虎康一 一 陈一 _卄I鼻yows in set <0.03 sec)select * from Tbl_BookAuthor;lmsql>