数据库操作1 - 数据准备

1. 数据准备

-- 1. 创建数据库；
create database if not exists dblearn2002 default character set utf8;

-- 2. 创建三张数据表；
-- 新建 user1 表
create table if not exists user1 (
  id smallint unsigned primary key auto_increment,
  user_name varchar(40),
  role varchar(40)
) default character set utf8;
-- 新建 user2 表
create table if not exists user2 (
  id smallint unsigned primary key auto_increment,
  user_name varchar(40),
  role varchar(40)
) default character set utf8;
-- 新建 user_kills 表
create table if not exists user_kills (
  id smallint unsigned primary key auto_increment,
  user_id smallint unsigned,
  timestr timestamp default CURRENT_TIMESTAMP,
  kills smallint unsigned
) default character set utf8;

-- 3. 向三张数据表中插入数据；
-- 插入 user1 表数据
insert into user1(user_name, role) values ('路飞', '草帽の船长');
insert into user1(user_name, role) values ('娜美', '草帽航海士');
insert into user1(user_name, role) values ('女帝', '路飞の爱妻');
insert into user1(user_name, role) values ('乔巴', '草帽の船医');
-- 插入 user2 表数据
insert into user2(user_name, role) values ('女帝', '王下七武海');
insert into user2(user_name, role) values ('红发', '四皇成员一');
insert into user2(user_name, role) values ('帝奇', '四皇成员一');
insert into user2(user_name, role) values ('百兽', '四皇成员一');
insert into user2(user_name, role) values ('玲玲', '四皇成员一');
-- 插入 user_kills 表数据
insert into user_kills(user_id, timestr, kills) values (2, timestamp('2020-01-20'), 10);
insert into user_kills(user_id, timestr, kills) values (2, timestamp('2020-02-21'), 2);
insert into user_kills(user_id, timestr, kills) values (2, timestamp('2020-02-25'), 12);
insert into user_kills(user_id, timestr, kills) values (4, timestamp('2020-01-20'), 3);
insert into user_kills(user_id, timestr, kills) values (4, timestamp('2020-02-21'), 5);
insert into user_kills(user_id, timestr, kills) values (2, timestamp('2020-02-26'), 1);
insert into user_kills(user_id, timestr, kills) values (3, timestamp('2020-01-21'), 20);
insert into user_kills(user_id, timestr, kills) values (2, timestamp('2020-02-22'), 10);
insert into user_kills(user_id, timestr, kills) values (3, timestamp('2020-02-27'), 17);

数据库表如下：

数据表.png user1表.png user2表.png user kill表.png