CREATE TABLE tb_card (

id INT PRIMARY KEY AUTO_INCREMENT,

CODE VARCHAR(18)

);

INSERT INTO tb_card (CODE) VALUES('');

CREATE TABLE tb_person (

id INT PRIMARY KEY AUTO_INCREMENT,

NAME VARCHAR(18),

sex VARCHAR(18),

age INT,

card_id INT UNIQUE,

FOREIGN KEY (card_id) REFERENCES tb_card (id)

);

INSERT INTO tb_person (NAME,sex,age,card_id) VALUES('jack','男',23,1);

CREATE TABLE tb_clazz(

id INT PRIMARY KEY AUTO_INCREMENT,

CODE VARCHAR(18),

NAME VARCHAR(18)

);

INSERT INTO tb_clazz(CODE,NAME) VALUES('j1601','Java就业班');

CREATE TABLE tb_student(

id INT PRIMARY KEY AUTO_INCREMENT,

NAME VARCHAR(18),

sex VARCHAR(18),

age INT,

clazz_id INT,

FOREIGN KEY (clazz_id) REFERENCES tb_clazz(id)

);

INSERT INTO tb_student(NAME,sex,age,clazz_id) VALUES('jack','男',23,1);

INSERT INTO tb_student(NAME,sex,age,clazz_id) VALUES('rose','女',18,1);

INSERT INTO tb_student(NAME,sex,age,clazz_id) VALUES('tom','男',21,1);

INSERT INTO tb_stuedent(NAME,sex,age,clazz_id) VALUES('alice','女',20,1);

#创建用户表

CREATE TABLE tb_user_1(

id INT PRIMARY KEY AUTO_INCREMENT,

username VARCHAR(18),

loginname VARCHAR(18),

PASSWORD VARCHAR(18),

phone VARCHAR(18),

address VARCHAR(18)

);

#插入用户表测试数据

INSERT INTO tb_user_1(username,loginname,PASSWORD,phone,address)

VALUES ('杰克','jack','','','广州');

#创建商品表

 CREATE TABLE tb_article(

 id INT PRIMARY KEY AUTO_INCREMENT,

 NAME VARCHAR(21),

 price DOUBLE,

 remark VARCHAR(18)

 );

 #插入商品表测试数据

 INSERT INTO tb_article(NAME,price,remark)

 VALUES('疯狂Java讲义',108.9,'李刚老师经典著作');

 INSERT INTO tb_article(NAME,price,remark)

 VALUES('疯狂Android讲义',99.9,'李刚老师经典著作');

 INSERT INTO tb_article(NAME,price,remark)

 VALUES('疯狂iOS讲义',89.9,'李刚老师经典著作');

 INSERT INTO tb_article(NAME,price,remark)

 VALUES('SpringMVC+MyBatis企业开发',69.9,'肖文吉老师经典著作');

 SELECT * FROM tb_article;

 #创建订单表

 CREATE TABLE tb_order(

  id INT PRIMARY KEY AUTO_INCREMENT,

  CODE VARCHAR(32),

  total DOUBLE,

  user_id INT,

  FOREIGN KEY (user_id) REFERENCES tb_user_1(id)

  );

  #输入订单表测试数据

  INSERT INTO tb_order(CODE,total,user_id)

  VALUES('6aa3fa359ff14619b77fab599094a2d',388.6,1);

  INSERT INTO tb_order(CODE,total,user_id)

  VALUES('6aa3fa359ff14619b77fab599094b3c',217.8,1);

  #创建中间表

  CREATE TABLE tb_item(

  order_id INT,

  article_id INT,

  amount INT,

  PRIMARY KEY (order_id,article_id),

  FOREIGN KEY (order_id) REFERENCES tb_order(id),

  FOREIGN KEY (article_id) REFERENCES tb_article(id)

  );

#创建插入中间表数据

  INSERT INTO tb_item(order_id,article_id,amount)

  VALUES (1,1,1);

  INSERT INTO tb_item(order_id,article_id,amount)

  VALUES (1,2,1);

  INSERT INTO tb_item(order_id,article_id,amount)

  VALUES (1,3,2);

  INSERT INTO tb_item(order_id,article_id,amount)

  VALUES (2,4,2);

  INSERT INTO tb_item(order_id,article_id,amount)

  VALUES (2,1,1);