mysql手写sql 建库建表示例

示例1 ： 

1-- 整个项目的数据库脚本
2-- 开始创建一个数据库
3CREATE DATABASE seckill;
4-- 使用数据库
5USE seckill;
6-- 创建秒杀库存表
7
8CREATE TABLE seckill(
9  `seckill_id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '商品库存ID',
10  `name` VARCHAR(120) NOT NULL COMMENT '商品名称',
11  `number` INT NOT NULL COMMENT '库存数量',
12  `start_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP() COMMENT '秒杀开启的时间',
13  `end_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP() COMMENT '秒杀结束的时间',
14  `create_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP() COMMENT '创建的时间',
15  PRIMARY KEY (seckill_id),
16  KEY idx_start_time(start_time),
17  KEY idx_end_time(end_time),
18  KEY idx_create_time(create_time)
19)ENGINE =InnoDB AUTO_INCREMENT=1000 DEFAULT CHARSET=utf8 COMMENT='秒杀库存表';
20
21-- 插入初始化数据
22
23insert into
24  seckill(name,number,start_time,end_time)
25values
26  ('1000元秒杀iphone6',100,'2016-5-22 00:00:00','2016-5-23 00:00:00'),
27  ('500元秒杀iPad2',200,'2016-5-22 00:00:00','2016-5-23 00:00:00'),
28  ('300元秒杀小米4',300,'2016-5-22 00:00:00','2016-5-23 00:00:00'),
29  ('200元秒杀红米note',400,'2016-5-22 00:00:00','2016-5-23 00:00:00');
30
31-- 秒杀成功明细表
32-- 用户登录相关信息
33create table success_killed(
34  `seckill_id` BIGINT NOT NULL COMMENT '秒杀商品ID',
35  `user_phone` BIGINT NOT NULL COMMENT '用户手机号',
36  `state` TINYINT NOT NULL DEFAULT -1 COMMENT '状态标示:-1无效 0成功 1已付款',
37  `create_time` TIMESTAMP NOT NULL COMMENT '创建时间',
38  PRIMARY KEY (seckill_id,user_phone), /*联合主键*/
39  KEY idx_create_time(create_time)
40)ENGINE =InnoDB DEFAULT CHARSET =utf8 COMMENT ='秒杀成功明细表';