CREATE DATABASE TB;

USE TB;

//创建表格
CREATE TABLE USER
(
		UID INT(10) NOT NULL AUTO_INCREMENT,
		NAME VARCHAR(20) NOT NULL,
		PRIMARY KEY(UID)
)ENGINE=INNODB DEFAULT CHARSET=gb2312;

CREATE TABLE QUESTION
(
		QID INT(10) NOT NULL AUTO_INCREMENT,
		UID INT(10),
		TITLE VARCHAR(20),
		CONTENT VARCHAR(100) NOT NULL,
		TIME VARCHAR(15) NOT NULL,
		FOREIGN KEY(UID) REFERENCES USER(UID),
		PRIMARY KEY(QID)
)ENGINE=INNODB DEFAULT CHARSET=gb2312;

CREATE TABLE ANSWER
(
		AID INT(10) NOT NULL AUTO_INCREMENT,
		QID INT(10),
		UID INT(10),
		CONTENT VARCHAR(200) NOT NULL,
		TIME VARCHAR(15) NOT NULL,
		FOREIGN KEY(QID) REFERENCES QUESTION(QID),
		FOREIGN KEY(UID) REFERENCES USER(UID),
		PRIMARY KEY(AID,QID)
)ENGINE=INNODB DEFAULT CHARSET=gb2312;

//插入数据
INSERT INTO USER(UID,NAME) VALUES
(1,'张三'),
(2,'李四'),
(3,'王五');

INSERT INTO QUESTION(QID,UID,TITLE,CONTENT,TIME) VALUES
(1,1,'计算','1+1=?','2014-3-2'),
(2,1,'天气','今天晴天吗？','2014-3-7'),
(3,2,'日期','明天星期几？','2014-3-9'),
(4,3,'天气','后天多少度？','2014-4-5');

INSERT INTO ANSWER(AID,QID,UID,CONTENT,TIME) VALUES
(1,2,2,'今天晴天','2014-3-7'),
(2,2,3,'今天晴天','2014-3-7'),
(1,1,2,'1+1=2','2014-3-10'),
(2,1,3,'1+1=2','2014-3-17'),
(1,3,2,'今天星期二','2014-3-11'),
(1,4,1,'后天20度','2014-4-5');

SELECT * FROM USER

SELECT * FROM QUESTION

SELECT * FROM ANSWER