deepin(linux)自建数据库代码集合记录。

Posted on | In |

目的是为啦以后同步到云服务器

  • 当然你也可以从本地导出处后上传到云服务器。
  • 在导入到到云服务器的数据库。
  • 我就是熟悉熟悉数据库操作。

create database how2java

table:category_

1
2
3
4
5
CREATE TABLE category_ (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(30),
PRIMARY KEY (id)
) DEFAULT CHARSET=UTF8;

table：drom_

1
2
3
4
5
create table dorm_(
id int(30) NOT NULL AUTO_INCREMENT,
type varchar(30),
primary key (id)
) DEFAULT CHARSET=UTF8;

create database Ticket 数据库文档说明

table:trian

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
create table train(
trid int ,
trty varchar(20) ,
trss varchar(50) ,
trsa varchar(50) ,
trst varchar(50) ,
trat varchar(50) ,
trcc int(30) ,
trsc int(30) ,
trkm int(30) ,
sname varchar(50)
)ENGINE InnoDB DEFAULT CHARSET=UTF8;
//主键约束
alter table train add constraint tm_train_id primary key (trid);
//id自增长
alter table train change trid trid int auto_increment;
//外键约束
alter table train add constraint fk_train_station foreign key (sname) references station(sname);
1
insert into train values (null, '和谐号', '武汉', '无锡','2018-7-3|11:00','2018-7-3|17:00',8,4,200,null);

table:ticket

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
create table ticket(
tiid int(30),
tiss varchar(50) ,
tias varchar(50) ,
tist varchar(30) ,
tiat varchar(30) ,
tipr int(30) ,
titp char(10) ,
tity char(10) ,
tino varchar(30) ,
sname varchar(50)
)ENGINE InnoDB DEFAULT CHARSET=UTF8;
//主键约束
alter table ticket add constraint tm_ticket_id primary key (tiid);
//id自增长
alter table ticket change tiid tiid int auto_increment;
//外键约束
alter table ticket add constraint fk_ticket_station foreign key (sname) references station(sname);
1
insert into ticket values (null, '无锡', '武汉', '2018-7-3|11:00','2018-7-3|17:00',260,'硬座','动车票','06-15',null);


table:seller

1
2
3
4
5
6
7
8
9
10
11
12
13
create table seller(
slid int(50),
slna varchar(50) ,
slpa varchar(50) ,
sname varchar(50)
)ENGINE InnoDB DEFAULT CHARSET=UTF8;

//主键约束
alter table seller add constraint tm_seller_id primary key (slid);
//id自增长
alter table seller change slid slid int auto_increment;
//外键约束
alter table seller add constraint fk_seller_station foreign key (sname) references station(sname);
1
insert into seller values (null,'王思聪' ,'666666',null);

table:station

1
2
3
4
5
6
7
8
9
10
11
12
create table station(
sname varchar(50),
scna  varchar(50) ,
spr varchar(12),
slid int(50)
)ENGINE InnoDB DEFAULT CHARSET=UTF8;
//主键约束
alter table station add constraint tm_station_id primary key (sname);
//id自增长

//外键约束
alter table station add constraint fk_station_seller foreign key (slid) references seller(slid);
1
insert into station values ('武汉站','武汉' ,'湖北',1);

table:sale

1
2
3
4
5
6
7
8
9
10
11
12
13
14
create table sale(
tiid int(8),
slid int(50) ,
sname varchar(50),
slti varchar(30)
)ENGINE InnoDB DEFAULT CHARSET=UTF8;
//主键约束
alter table sale add constraint tm_sale_id primary key (tiid);
//id自增长
alter table sale change tiid tiid int auto_increment;
//外键约束
alter table sale add constraint fk_sale_seller foreign key (slid) references seller(slid);

alter table sale add constraint fk_sale_station foreign key (sname) references station(sname);
1
insert into sale values (null,1 ,'武汉站','2018-7-3|17:00');

table:back

1
2
3
4
5
6
7
8
9
10
11
create table back(
tiid int(8) ,
slid int(50) ,
backprice varchar(30)
)ENGINE InnoDB DEFAULT CHARSET=UTF8;
//主键约束
alter table back add constraint tm_back_id primary key (tiid);
//id自增长
alter table back change tiid tiid int auto_increment;
//外键约束
alter table back add constraint fk_back_seller foreign key (slid) references seller(slid);
1
insert into back values (null,1 ,'520');

  • 为啦多表查询方便 先插station 再插trian表
    1
    2
    3
    4
    5
    6
    insert into station values ('无锡南站','无锡','江苏',1);
    insert into train values (null, '和谐号', '太原', '无锡','2018-7-3|11:00','2018-7-3|17:00',8,4,200,'无锡南站');
    //以上失败  因为springboot没有用string获取对象 而是id
    //写back station 表的多表查询
    insert into station values ('无锡南站','无锡','江苏',4);
    insert into back values (7,4,'500');

微信点餐数据库sell

create database sell;

table:product_category

  • create_time timestamp not null default current_timestamp comment ‘创建时间’, (数据库版本原因，在默认或更新时间戳时只能有一个时间戳列)
  • defalut 只有一个 放在所有timestamp字段的最前面 不然报错。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
-- 类目
create table `product_category` (
    `category_id` int not null auto_increment,
    `category_name` varchar(64) not null comment '类目名字',
    `category_type` int not null comment '类目编号',
    `create_time` timestamp not null default current_timestamp comment '创建时间',
    `update_time` timestamp not null  comment '修改时间',
    primary key (`category_id`)
);

-- 商品
create table `product_info` (
    `product_id` varchar(32) not null,
    `product_name` varchar(64) not null comment '商品名称',
    `product_price` decimal(8,2) not null comment '单价',
    `product_stock` int not null comment '库存',
    `product_description` varchar(64) comment '描述',
    `product_icon` varchar(512) comment '小图',
    `product_status` tinyint(3) DEFAULT '0' COMMENT '商品状态,0正常1下架',
    `category_type` int not null comment '类目编号',
    `create_time` timestamp not null default current_timestamp comment '创建时间',
    `update_time` timestamp not null  comment '修改时间',
    primary key (`product_id`)
);

-- 订单
create table `order_master` (
    `order_id` varchar(32) not null,
    `buyer_name` varchar(32) not null comment '买家名字',
    `buyer_phone` varchar(32) not null comment '买家电话',
    `buyer_address` varchar(128) not null comment '买家地址',
    `buyer_openid` varchar(64) not null comment '买家微信openid',
    `order_amount` decimal(8,2) not null comment '订单总金额',
    `order_status` tinyint(3) not null default '0' comment '订单状态, 默认为新下单',
    `pay_status` tinyint(3) not null default '0' comment '支付状态, 默认未支付',
    `create_time` timestamp not null default current_timestamp comment '创建时间',
    `update_time` timestamp not null  comment '修改时间',
    primary key (`order_id`),
    key `idx_buyer_openid` (`buyer_openid`)
);

-- 订单商品
create table `order_detail` (
    `detail_id` varchar(32) not null,
    `order_id` varchar(32) not null,
    `product_id` varchar(32) not null,
    `product_name` varchar(64) not null comment '商品名称',
    `product_price` decimal(8,2) not null comment '当前价格,单位分',
    `product_quantity` int not null comment '数量',
    `product_icon` varchar(512) comment '小图',
    `create_time` timestamp not null default current_timestamp comment '创建时间',
    `update_time` timestamp not null  comment '修改时间',
    primary key (`detail_id`),
    key `idx_order_id` (`order_id`)
);

-- 卖家(登录后台使用, 卖家登录之后可能直接采用微信扫码登录，不使用账号密码)
create table `seller_info` (
    `id` varchar(32) not null,
    `username` varchar(32) not null,
    `password` varchar(32) not null,
    `openid` varchar(64) not null comment '微信openid',
    `create_time` timestamp not null default current_timestamp comment '创建时间',
    `update_time` timestamp not null  comment '修改时间',
    primary key (`id`)
) comment '卖家信息表';