-- 用戶表（user）
CREATE TABLE `user`(
	`id` INT AUTO_INCREMENT PRIMARY KEY COMMENT '用戶id(主鍵)',
	`username` VARCHAR(50) COMMENT '用戶姓名', 
	`age` CHAR(3) COMMENT '用戶年齡'
);

-- 訂單表（orders）
CREATE TABLE `orders`(
	`id` INT AUTO_INCREMENT PRIMARY KEY COMMENT '訂單id(主鍵)',
	`price` DOUBLE COMMENT '訂單價格',
	`user_id` INT COMMENT '用戶id(外鍵)'
);

-- 給已經存在的表添加外鍵，語法如下
-- alter table 表名 add constraint [外鍵名字] foreign key (外鍵字段) references 父表(主鍵字段);
ALTER TABLE orders ADD CONSTRAINT user_fk FOREIGN KEY (user_id) REFERENCES `user` (id);

-- 向user表中添加數據
INSERT INTO USER VALUES(1,'第一',11);
INSERT INTO USER VALUES(2,'小二',12);
INSERT INTO USER VALUES(3,'張三',33);
INSERT INTO USER VALUES(4,'李四',24);
INSERT INTO USER VALUES(5,'王五',17);
INSERT INTO USER VALUES(6,'趙六',36);
INSERT INTO USER VALUES(7,'七七',18);
INSERT INTO USER VALUES(8,'粑粑',NULL);

-- 向orders 表中插入數據
INSERT INTO orders VALUES(111,1314,3);
INSERT INTO orders VALUES(112,122,3);
INSERT INTO orders VALUES(113,15,4);
INSERT INTO orders VALUES(114,315,5);
INSERT INTO orders VALUES(115,1014,NULL);
INSERT INTO orders VALUES(116,666,6);
INSERT INTO orders VALUES(117,1111,1);
INSERT INTO orders VALUES(118,8888,NULL);

SELECT * FROM `user`,`orders`;

SELECT * FROM `user` AS u,`orders` AS o WHERE u.`id`=o.`user_id`;

SELECT * FROM `user` AS u,`orders` AS o WHERE u.`id`=o.`user_id`;

SELECT * FROM `user` u JOIN `orders` o ON u.`id`=o.`user_id` WHERE age >= 18;

SELECT * FROM `user` u RIGHT JOIN `orders` o ON u.`id`=o.`user_id`;

SELECT * FROM `user` u LEFT JOIN `orders` o ON u.`id`=o.`user_id`;

SELECT * FROM orders o WHERE o.`user_id` IN (
	SELECT u.`id` FROM `user` u WHERE u.`age` IN(
		SELECT MAX(u.`age`) FROM `user` u
	)
);