2022年网上书店管理系统数据库MySQL .pdf

新建数据库Create database BookData; Use BookData; 新建表 B1 create 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, 张三 ,白下区 ,南京 ,江苏 ,210000,null ); insert into tbl_customer values(1002, 李四 ,徐汇区 ,上海 ,上海 ,110000, null); insert into tbl_customer values(1003, 王五 ,朝阳区 ,北京 ,北京 ,110000,null); insert into tbl_customer values(1004, 赵六 ,北大街 ,无锡 ,江苏 ,110000, null); insert into tbl_customer values(1005, 李三 ,平江路 ,苏州 ,江苏 ,110000, null); insert into tbl_customer values(1006, 陈三 ,升州路 ,南京 ,江苏 ,110000, null); insert into tbl_customer values(1007, 朱三 ,三牌楼 ,合肥,安徽 ,110000,1003); 新建表 B2 create 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_books values(1059831198, 一天中 10分钟的身体成分,2001-1-21,4,18.75,30.95,健康 ); insert into tbl_books values(0401140733, 米奇的复仇 ,2001-12-12,1,14.2,22,家庭生活 ); insert into tbl_books values(4981341710, 用牙签造车 , 2002-08-18,2,37.8,59,儿童 ); insert into tbl_books values(8843172113, 数据库的实现, 1999-06-04 ,3,31,55,计算机 ); insert into tbl_books values(3437212490, 用蘑菇烹饪 , 2000-02-28 ,4,12.5,19.95, 烹饪 ); insert into tbl_books values(3957136468, 数据库法宝 , 2001-11-30 ,3,47,75,计算机 ); 新建表 B3 create table Tbl_Orders(Orders decimal(4)not null,Customer decimal(4),Orderdate Date, Shipdate Date , 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 , 奥体大街 , 南京 ,江苏,210019); insert into Tbl_Orders values(1001,1010, 2003-03-31 , 2003-04-01 , 和会街 , 南京 ,江苏,210001); insert into Tbl_Orders values(1002,1011, 2003-03-31 , 2003-04-01 , 南京路 , 上海 ,上海,201100); insert into Tbl_Orders values(1003,1001, 2003-04-01 , 2003-04-01 , 南京路 , 上海 ,上海,201100); insert into Tbl_Orders values(1004,1020, 2003-04-01 , 2003-04-05 , 欧风街 , 无锡 ,江苏,204002); 新建表 B4 create table Tbl_Oraderitems(orders decimal(4) not null,Item decimal(2) not null,Isbn varchar(10),quantity decimal(3); 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 1 页，共 5 页 - - - - - - - - - 插入数据insert into Tbl_Oraderitems values(1000,1,3437212490,1); insert into Tbl_Oraderitems values(1001,1,9247381001,1); insert into Tbl_Oraderitems values(1001,2,2491748320,1); insert into Tbl_Oraderitems values(1002,1,8843174832,2); insert into Tbl_Oraderitems values(1003,1,8843174832,1); MySQL 语句可以放在同一行；但是DB2 不可以新建表 B5 create table Tbl_Author(AuthorId varchar(4) not null,name varchar(20); 插入数据insert into Tbl_Author values(s100, 薛明 ); insert into Tbl_Author values(j100, 陈洪 ); insert into Tbl_Author values(a100, 张二 ); insert into Tbl_Author values(m100, 刘虎 ); insert into Tbl_Author values(k100, 陈康 ); 新建表 B6 create table Tbl_BookAuthor(Isbn varchar(10) not null,AuthorId varchar(4) not null); drop table Tbl_BookAuthor 插入数据insert into Tbl_BookAuthor values(1059831198,s100); insert into Tbl_BookAuthor values(1059831198,p100); insert into Tbl_BookAuthor values(0401140733,j100); insert into Tbl_BookAuthor values(4981341710,k100); insert into Tbl_BookAuthor values(8843172113,p105); insert into Tbl_BookAuthor values(8843172113,a100); insert into Tbl_BookAuthor values(8843172113,a105); insert into Tbl_BookAuthor values(3437212490,b100); insert into Tbl_BookAuthor values(3957136468,a100); insert into Tbl_BookAuthor values(1915762492,w100); insert into Tbl_BookAuthor values(1915762492,w105); insert into Tbl_BookAuthor values(9959789321,j100); insert into Tbl_BookAuthor values(2491748320,r100); insert into Tbl_BookAuthor values(2491748320,f100); insert into Tbl_BookAuthor values(2491748320,b100); insert into Tbl_BookAuthor values(0299282519,s100); insert into Tbl_BookAuthor values(8117949391,r100); insert into Tbl_BookAuthor values(0132149871,s100); insert into Tbl_BookAuthor values(9247381001,w100); insert into Tbl_BookAuthor values(2147428890,w105); 新建表 B7 create table Tbl_Publisher(Pubid varchar(10) not null,Name varchar(23) ,Contact Varchar(15),Phone varchar(12); 插入数据insert into Tbl_Publisher values(1, 新华出版社 ,程明 ,000-714-8321); 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 2 页，共 5 页 - - - - - - - - - insert into Tbl_Publisher values(2, 扬子出版社 ,张译 ,010-414-8321); insert into Tbl_Publisher values(3, 人民教育出版社,园艺 ,044-714-8321); insert into Tbl_Publisher values(4, 北京大学出版社,郑爽 ,000-564-8321); insert into Tbl_Publisher values(5, 机械工业出版社,陆华 ,000-714-8751); 新建表 B8 create table Tbl_Promotion(geft varchar(100),minretail decimal(5,2),maxretail decimal(5,2); 插入数据insert into Tbl_Promotion values( 书签 ,0,12); insert into Tbl_Promotion values( 书签题题 ,12.01,25); insert into Tbl_Promotion values( 图书封面 ,25.01,56); insert into Tbl_Promotion values( 免费购物券 ,56.01,999.99); select * from Tbl_Customer; select * from Tbl_Books; 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 3 页，共 5 页 - - - - - - - - - select * from Tbl_Orders; select * from Tbl_Oraderitems ；select * from Tbl_Author; 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 4 页，共 5 页 - - - - - - - - - select * from Tbl_BookAuthor; select * from Tbl_Publisher; select * from Tbl_Promotion; 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 5 页，共 5 页 - - - - - - - - -