MyBatis的关联映射和动态SQL-白红宇

MyBatis的关联映射和动态SQL

阅读量：5270 次

发布时间：2019-06-14

本文共 2746 字，大约阅读时间需要 9 分钟。

CREATE TABLE tb_card (id INT PRIMARY KEY AUTO_INCREMENT,CODE VARCHAR(18));INSERT INTO tb_card (CODE) VALUES('432801198009191038');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','123456','13920001616','广州');

#创建商品表 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);

转载于:https://www.cnblogs.com/ZHONGZHENHUA/p/8052573.html

你可能感兴趣的文章