SQL学习，Mysql数据库常用表及数据备份。

 

1、Student表及表数据备份

CREATE TABLE student (
  st_id varchar(64) NOT NULL DEFAULT '' COMMENT '编号',
  st_name varchar(255) DEFAULT '' COMMENT '姓名',
  st_age int(3) DEFAULT NULL COMMENT '年龄',
  st_address varchar(255) DEFAULT NULL COMMENT '地址',
  st_sex varchar(8) DEFAULT NULL COMMENT '性别',
  st_status int(2) DEFAULT NULL COMMENT '状态, 0-正常， 1-异常. ',
  create_date datetime DEFAULT NULL COMMENT '创建时间',
  update_date datetime DEFAULT NULL COMMENT '修改时间',
  PRIMARY KEY (st_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='学生表';


INSERT INTO student (st_id, st_name, st_age, st_address, st_sex, st_status, create_date, update_date) VALUES ('001', '侯耀华', '77', '北京市-海淀区', '男', NULL, '2019-06-10 10:56:21', NULL);
INSERT INTO student (st_id, st_name, st_age, st_address, st_sex, st_status, create_date, update_date) VALUES ('002', '郭德纲', '45', '河北省-保定市', '男', '0', '2019-06-09 16:56:21', NULL);
INSERT INTO student (st_id, st_name, st_age, st_address, st_sex, st_status, create_date, update_date) VALUES ('003', '曹云金', '33', '河南省-洛阳市', '男', '-5', '2019-06-10 13:22:21', NULL);
INSERT INTO student (st_id, st_name, st_age, st_address, st_sex, st_status, create_date, update_date) VALUES ('004', '岳云鹏', '31', '河南省-周口市', '男', '0', '2019-06-08 09:35:27', NULL);
INSERT INTO student (st_id, st_name, st_age, st_address, st_sex, st_status, create_date, update_date) VALUES ('005', '宋丹丹', '52', '黑龙江-佳木斯', '女', '0', '2019-06-09 07:35:27', NULL);
INSERT INTO student (st_id, st_name, st_age, st_address, st_sex, st_status, create_date, update_date) VALUES ('006', '倪妮', '31', '江苏省-南京市', '女', '-5', '2019-06-08 04:16:41', NULL);
INSERT INTO student (st_id, st_name, st_age, st_address, st_sex, st_status, create_date, update_date) VALUES ('007', '江流儿', '445', '明朝-汴梁人氏', '男', '0', '1798-06-11 15:11:47', NULL);
INSERT INTO student (st_id, st_name, st_age, st_address, st_sex, st_status, create_date, update_date) VALUES ('008', '赵丽颖', '31', '河南省-平顶山市', '女', '0', '2019-06-06 03:24:51', NULL);
INSERT INTO student (st_id, st_name, st_age, st_address, st_sex, st_status, create_date, update_date) VALUES ('user_002', '吴玉', '19', '福建省-龙岩市', '女', NULL, '2019-06-08 10:14:02', NULL);
INSERT INTO student (st_id, st_name, st_age, st_address, st_sex, st_status, create_date, update_date) VALUES ('user_003', '上官飞', '20', '河北省-廊坊市', '男', NULL, '2019-06-07 10:14:06', NULL);

 

2、商品表、品牌表、类型表，   这3张表可进行多表关联，使用Kettle进行数据抽取。

-- 商品表
CREATE TABLE ecs_goods (
  goods_id bigint(16) NOT NULL,
  goods_name varchar(64) DEFAULT NULL,
  cat_id bigint(16) DEFAULT NULL,
  brand_id bigint(16) DEFAULT NULL,
  PRIMARY KEY (goods_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8  COMMENT='商品表';


INSERT INTO ecs_goods (goods_id, goods_name, cat_id, brand_id) VALUES ('10010', '华为_荣耀', '40010', '30010');
INSERT INTO ecs_goods (goods_id, goods_name, cat_id, brand_id) VALUES ('10011', 'VIVO_手机', '40010', '30015');
INSERT INTO ecs_goods (goods_id, goods_name, cat_id, brand_id) VALUES ('10012', '华为_程控式交换机', '40011', '30010');
INSERT INTO ecs_goods (goods_id, goods_name, cat_id, brand_id) VALUES ('10013', '海尔_半自动洗衣机', '40016', '30014');
INSERT INTO ecs_goods (goods_id, goods_name, cat_id, brand_id) VALUES ('10014', '公牛_8排插插板', '40017', '30013');
INSERT INTO ecs_goods (goods_id, goods_name, cat_id, brand_id) VALUES ('10015', '小米_红米9', '40010', '30012');
INSERT INTO ecs_goods (goods_id, goods_name, cat_id, brand_id) VALUES ('10016', 'OPOP手机', '40010', '30016');
INSERT INTO ecs_goods (goods_id, goods_name, cat_id, brand_id) VALUES ('10017', '美的空调', '40013', '30017');
INSERT INTO ecs_goods (goods_id, goods_name, cat_id, brand_id) VALUES ('10018', '小米电视', '40015', '30012');
INSERT INTO ecs_goods (goods_id, goods_name, cat_id, brand_id) VALUES ('10019', '海尔电视', '40015', '30014');
INSERT INTO ecs_goods (goods_id, goods_name, cat_id, brand_id) VALUES ('10020', '格力空调', '40013', '30011');
INSERT INTO ecs_goods (goods_id, goods_name, cat_id, brand_id) VALUES ('10021', '海尔空调', '40013', '30014');


-- 品牌表
CREATE TABLE ecs_brand (
  brand_id bigint(16) NOT NULL,
  brand_name varchar(64) DEFAULT NULL,
  remark varchar(2000) DEFAULT NULL,
  PRIMARY KEY (brand_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='品牌表';


INSERT INTO ecs_brand (brand_id, brand_name, remark) VALUES ('30010', '华为', '测试数据');
INSERT INTO ecs_brand (brand_id, brand_name, remark) VALUES ('30011', '格力', NULL);
INSERT INTO ecs_brand (brand_id, brand_name, remark) VALUES ('30012', '小米', NULL);
INSERT INTO ecs_brand (brand_id, brand_name, remark) VALUES ('30013', '公牛', NULL);
INSERT INTO ecs_brand (brand_id, brand_name, remark) VALUES ('30014', '海尔', NULL);
INSERT INTO ecs_brand (brand_id, brand_name, remark) VALUES ('30015', 'VIVO', NULL);
INSERT INTO ecs_brand (brand_id, brand_name, remark) VALUES ('30016', 'OPOP', NULL);
INSERT INTO ecs_brand (brand_id, brand_name, remark) VALUES ('30017', '美的', NULL);


-- 类型表
CREATE TABLE ecs_category (
  cat_id bigint(16) NOT NULL,
  cat_name varchar(64) DEFAULT NULL,
  remark varchar(2000) DEFAULT NULL,
  PRIMARY KEY (cat_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='类型表';


INSERT INTO ecs_category (cat_id, cat_name, remark) VALUES ('40010', '手机', '测试数据');
INSERT INTO ecs_category (cat_id, cat_name, remark) VALUES ('40011', '交换机', NULL);
INSERT INTO ecs_category (cat_id, cat_name, remark) VALUES ('40012', '电脑', NULL);
INSERT INTO ecs_category (cat_id, cat_name, remark) VALUES ('40013', '空调', NULL);
INSERT INTO ecs_category (cat_id, cat_name, remark) VALUES ('40015', '电视机', NULL);
INSERT INTO ecs_category (cat_id, cat_name, remark) VALUES ('40016', '洗衣机', NULL);
INSERT INTO ecs_category (cat_id, cat_name, remark) VALUES ('40017', '排插', NULL);

 

        多表关联语句如下所示：

SELECT goods_id,
       goods_name,
       cat_name,
       brand_name
FROM ecs_goods eg 
INNER JOIN ecs_category ec ON eg.cat_id = ec.cat_id
INNER JOIN ecs_brand eb ON eg.brand_id = eb.brand_id
ORDER BY brand_name;