mysql 课本实训操作

菜鸟xy..

已于 2024-08-30 16:15:01 修改

阅读量1.3k

点赞数 11

文章标签： mysql android 数据库

于 2024-08-30 16:09:09 首次发布

本文链接：https://blog.youkuaiyun.com/2302_81688883/article/details/141720325

版权

bookstore 结构加实训

create database bookstore
default character set gb2312
collate gb2312_chinese_ci;

CREATE TABLE book (
图书编号 char(20) NOT NULL PRIMARY KEY,
图书类别 varchar(20) not NULL DEFAULT '计算机',
书名 varchar(40) NOT NULL,
作者 char(10) NOT NULL,
出版社 varchar(20) NOT NULL,
出版时间 date NOT NULL,
单价 float(5,2) NOT NULL,
数量 int(0) NULL DEFAULT NULL,
折扣 float(3,2) NULL DEFAULT NULL
)ENGINE=InnoDB;

CREATE TABLE members (
用户号 char(18) NOT NULL,
姓名 char(10) NOT NULL,
性别 char(2) NOT NULL,
密码 char(6) NOT NULL,
联系电话 varchar(20) NOT NULL,
注册时间 datetime(0) NULL DEFAULT NULL,
PRIMARY KEY (`用户号`)
) ENGINE = InnoDB;

CREATE TABLE sell (
订单号 int(0) NOT NULL PRIMARY KEY,
用户号 char(18) NOT NULL,
图书编号 char(20) NOT NULL,
订购册数 int(0) NOT NULL,
订购单价 float(5, 2) NOT NULL,
订购时间 datetime(0) NOT NULL,
是否发货 varchar(10) NULL DEFAULT '不确定',
是否收货 varchar(10) NULL DEFAULT '不确定',
是否结清 varchar(10) NULL DEFAULT '不确定'
) ENGINE = InnoDB ;

数据：

book表

INSERT INTO `book` (`图书编号`, `图书类别`, `书名`, `作者`, `出版社`, `出版时间`, `单价`, `数量`, `折扣`)
VALUES
('TP.00001', '网页设计', '计算机基础', '李华', '人民邮电出版社', '2012-06-26', 27, NULL, NULL),

('TP.00002', '计算机', 'JavaScript网站制作', '谢为民', '中国青年出版社', '2010-08-16', 33, 60, 0.8),

('TP.00003', '网页设计', 'PHP网站制作', '林小红', '清华大学出版社', '2011-10-16', 23.5, 3, 0.8),

('TP.00004', '计算机', '计算机应用基础', '陆大强', '清华大学出版社', '2011-10-16', 45, 45, 0.8),

('TP.00005', '计算机', '计算机网络技术', '林力辉', '清华大学出版社','2011-10-16', 25.5, 45, 0.8),

('TP.00006', '计算机', '计算机文化基础', '林华忠', '清华大学出版社', '2011-10-16', 45.5, 45, 0.8),
('TP.00007', '网页设计', 'ASP网站制作','胡莉惠', '中国青年出版社', '2010-08-16', 30.5, 50, 0.8),

('TP.00008', '网页设计', 'PHP_MySQL网站制作', '王大卫', '中国青年出版社', '2010-08-16', 33.25, 50, 0.8),

('TP.00009', '数据库技术', '网络数据库', '张小刚', '北京大学出版社', '2011-08-02', 28, NULL, NULL),

('TP.00010', '网页设计', '网页程序设计','刘辉', '清华大学出版社', '2011-02-15', 25, NULL, NULL),

('TP.00011', '数据库技术', 'MYSQL数据库','李刚', '北京大学出版社', '2013-01-26', 20, 500, 0.8),

('TP.00012', '网页设计', 'Dreamwearer_8网站制作', '鲍嘉', '中国青年出版社', '2010-08-16', 33.2, 50, 0.8);

members表

INSERT INTO `members` (`用户号`, `姓名`, `性别`, `密码`, `联系电话`, `注册时间`)
VALUES
('A0012', '赵宏宇', '男', '080100', '13601234123', '2007-03-04 18:23:45'),

('A3013', '张凯', '男', '080100', '13611320001', '2007-01-15 09:12:23'),

('B0022', '王林', '男', '080100', '12501234123', '2007-01-12 08:12:30'),

('B2023', '李小冰', '女', '080100', '13651111081', '2007-01-18 08:57:18'),

('C0132', '张莉', '女', '123456', '13822555432', '2012-09-23 00:00:00'),

('C0138', '李华', '女', '123456', '13822551234', '2013-08-23 00:00:00'),

('D1963', '张三', '男', '222222', '51985523', '2007-01-23 08:15:45');

sell表
INSERT INTO `sell` (`订单号`, `用户号`, `图书编号`, `订购册数`, `订购单价`, `订购时间`, `是否发货`, `是否收货`, `是否结清`)
VALUES
('1', 'D1963', 'TP.00001', 4, 25, '2013-08-26 12:25:03', '已发货', '已收货', '已结清'),

('10', 'C0132', 'TP.00002', 10, 27, '2013-08-01 12:13:49', NULL, NULL, NULL),

('11', 'C0132', 'TP.00003', 30, 25, '2013-08-01 12:13:49', '已发货', '已收货', '已结清'),

('12', 'C0132', 'TP.00004', 40, 28, '2013-08-01 12:13:49', NULL, NULL, NULL),

('13', 'C0132', 'TP.00005', 13, 20, '2013-08-01 12:13:49', '已发货', NULL, NULL),

('14', 'C0138', 'TP.00006', 10, 23.5, '2013-08-01 12:13:49', '已发货', '已收货', '已结清'),

('15', 'C0138', 'TP.00007', 133, 33.5, '2013-08-01 12:13:49', NULL, NULL, NULL),

('16', 'C0138', 'TP.00007', 43, 30, '2013-08-01 12:13:49', '已发货', NULL, NULL),

('2', 'D1963', 'TP.00008', 3, 31.5, '2013-08-05 12:25:12', '已发货', NULL, NULL),

('3', 'D1963', 'TP.00009', 6, 23.45, '2013-03-26 12:25:23', '已发货', '已收货', NULL),

('4', 'B2023', 'TP.00010', 7, 37.1, '2009-02-17 00:00:00', '已发货', '已收货', '已结清'),

('5', 'A3013', 'TP.00011', 7, 65.6, '2010-02-01 00:00:00', NULL, NULL, NULL),

('6', 'A3013', 'TP.00012', 4, 89, '2009-08-20 00:00:00', NULL, NULL, NULL),

('7', 'C0138', 'TP.00012', 6, 23, '2013-03-19 12:25:32', '已发货', '已收货', NULL),

('8', 'C0138', 'TP.00011', 5, 45.5, '2010-02-02 00:00:00', NULL, NULL, NULL),

('9', 'C0132', 'TP.00012', 6, 23, '2013-08-12 18:23:35', '已发货', '已收货', NULL);

商业示例 pestore 库表结构

创建数据库

create database Petstore
default character set gb2312
collate gb2312_chinese_ci;

建表：

CREATE TABLE account
(
userid char(6) NOT NULL,
fullname   varchar(10) NOT NULL,
passward   varchar(20) NOT NULL,
sex   char(2) NOT NULL,
   address    varchar(40) DEFAULT NULL,
   email    varchar(20) DEFAULT NULL,
   phone     varchar(11) NOT NULL,
PRIMARY KEY ( userid )
)

CREATE TABLE category
(
catid   char(10) NOT NULL,
catname   varchar(20) DEFAULT NULL,
cades   text,
PRIMARY KEY ( catid )
)

CREATE TABLE   lineitem (
orderid int(11) NOT NULL,
   itemid char(10) NOT NULL,
quantity int(11) NOT NULL,
   unitprice decimal(10,2) NOT NULL,
PRIMARY KEY ( orderid , itemid )
)

CREATE TABLE orders
(
   orderid int(11) NOT NULL AUTO_INCREMENT,
   userid char(6) NOT NULL,
orderdate datetime NOT NULL,
totalprice decimal(10,2) DEFAULT NULL,
   status tinyint(1) DEFAULT NULL,
PRIMARY KEY ( orderid )
)

CREATE TABLE product
(
   productid char(10) NOT NULL,
   catid char(10) NOT NULL,
   name varchar(30) DEFAULT NULL,
descn   text,
   listprice   decimal(10,2) DEFAULT NULL,
   unitcost decimal(10,2) DEFAULT NULL,
   qty int(11) NOT NULL,
PRIMARY KEY ( productid )
)

数据插入:

/* `account` */

INSERT INTO account VALUES ('u0001', '刘晓和', '123456', '男', '广东深圳市', 'liuxh@163.com', '13512345678');
INSERT INTO account VALUES ('u0002', '张嘉庆', '123456', '男', '广东深圳市', 'zhangjq@163.com', '13512345679');
INSERT INTO account VALUES ('u0003', '罗红红', '123456', '女', '广东深圳市', 'longhh@163.com', '13512345689');
INSERT INTO account VALUES ('u0004', '李昊华', '123456', '女', '广东广州市', 'lihh@163.com', '13812345679');
INSERT INTO account VALUES ('u0005', '吴美霞', '123456', '女', '广东珠海市', 'wumx@163.com', '13512345879');
INSERT INTO account VALUES ('u0006', '王天赐', '123456', '男', '广东中山市', 'wangtc@163.com', '13802345679');

/* `category` */

INSERT INTO category VALUES ('01', '鸟类', '');
INSERT INTO category VALUES ('02', '猫', '');
INSERT INTO category VALUES ('03', '狗', '');
INSERT INTO category VALUES ('04', '鱼', '');
INSERT INTO category VALUES ('05', '爬行类', '');

/* `lineitem` */

INSERT INTO lineitem VALUES (20130411, 'FI-SW-01', 10, 18.50);
INSERT INTO lineitem VALUES (20130411, 'FI-SW-02', 12, 16.50);
INSERT INTO lineitem VALUES (20130412, 'K9-BD-01', 2, 120.00);
INSERT INTO lineitem VALUES (20130412, 'K9-PO-02', 1, 220.00);
INSERT INTO lineitem VALUES (20130413, 'K9-DL-01', 1, 130.00);
INSERT INTO lineitem VALUES (20130414, 'RP-SN-01', 2, 125.00);
INSERT INTO lineitem VALUES (20130415, 'AV-SB-02', 2, 50.00);

/* `orders` */

INSERT INTO orders VALUES (20130411, 'u0001', '2013-04-11 15:07:34', 500.00, 0);
INSERT INTO orders VALUES (20130412, 'u0002', '2013-04-09 15:08:11', 305.60, 0);
INSERT INTO orders VALUES (20130413, 'u0003', '2013-04-15 15:09:00', 212.40, 0);
INSERT INTO orders VALUES (20130414, 'u0003', '2013-04-16 15:09:30', 120.45, 1);
INSERT INTO orders VALUES (20130415, 'u0004', '2013-04-02 15:10:05', 120.30, 0);

/* `product` */
INSERT INTO product VALUES ('AV-CB-01', '05', '亚马逊鹦鹉', '75 岁以上高龄的好伙伴', 50.00, 60.00, 100);
INSERT INTO product VALUES ('AV-SB-02', '05', '燕雀', '非常好的减压宠物', 45.00, 50.00, 98);
INSERT INTO product VALUES ('FI-FW-01', '01', '锦鲤', '来自日本的淡水鱼', 45.50, 45.50, 300);

最低0.47元/天解锁文章