（六）设计客户表

会员等级表 

CREATE TABLE `t_level`  (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
  `level` varchar(200) NOT NULL COMMENT '等级',
  `discount` decimal(10, 2) UNSIGNED NOT NULL COMMENT '折扣',
  PRIMARY KEY (`id`)
)COMMENT = '会员登记表';

 

客户表 

 

CREATE TABLE `t_customer`  (
  `id` int UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
  `username` varchar(200) NOT NULL COMMENT '用户名',
  `password` varchar(2000) NOT NULL COMMENT '密码（AES加密）',
  `wechat` varchar(200) NULL COMMENT '微信号',
  `tel` char(11) NULL COMMENT '电话号',
  `level_id` int UNSIGNED NULL COMMENT '会员等级',
  `create_time` timestamp NOT NULL DEFAULT NOW() COMMENT '注册时间',
  `last_update_time` timestamp NOT NULL DEFAULT NOW() COMMENT '最近修改时间',
  PRIMARY KEY (`id`),
  INDEX `idx_username`(`username`),
  UNIQUE INDEX `unq_username`(`username`)
) COMMENT = '客户表';

 

客户收货地址表

CREATE TABLE `t_customer_address`  (
  `id` int UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
  `customer_id` int UNSIGNED NOT NULL COMMENT '客户ID',
  `name` varchar(200) NOT NULL COMMENT '收货人姓名',
  `tel` varchar(20) NOT NULL COMMENT '收货人电话',
  `address` varchar(200) NOT NULL COMMENT '收货地址',
  `prime` tinyint(1) UNSIGNED NOT NULL COMMENT '是否为缺省收货地址',
  PRIMARY KEY (`id`)
) COMMENT = '收货地址表';

AES加密 (16进制)
HEX(AES_ENCRYPT( 'abc123', 'tsthza' ))

AES解密
AES_DECRYPT(UNHEX('6961DA669ED27FF430CABFB0657D0B3D'),'tsthza')


INSERT INTO `t_customer` (`username`, `password`, wechat, tel)
VALUES ('tsthzang',HEX(AES_ENCRYPT( 'abc123', 'tsthza' )),'tsthzang','15912345678');