Java mysql特殊形式的查詢語句詳解

-- 創建學員表
CREATE TABLE IF NOT EXISTS stu(
id TINYINT UNSIGNED AUTO_INCREMENT KEY COMMENT '編號',
username VARCHAR(20) NOT NULL UNIQUE COMMENT '學員名稱',
score TINYINT UNSIGNED NOT NULL COMMENT '學員考試分數'
)ENGINE=INNODB;

INSERT stu(username, score) VALUES('king', 95),
('queen', 75),
('zhangsan', 69),
('lisi', 78),
('wangwu', 87),
('zhaoliu', 88),
('tianqi', 98),
('ceshi', 99),
('tiancai', 50);

-- 創建分數級別表
CREATE TABLE IF NOT EXISTS `level`(
id TINYINT UNSIGNED AUTO_INCREMENT KEY COMMENT '編號',
score TINYINT UNSIGNED COMMENT '分數'
)ENGINE=INNODB;

INSERT `level`(score) VALUES(90), (80), (70);

SELECT * FROM user1 WHERE depId IN (1, 2, 3, 4);

SELECT * FROM user1 
WHERE depId IN (SELECT id FROM dep);

SELECT id, username, score FROM stu
WHERE score >= (SELECT score FROM `level` WHERE id = 1);

SELECT * FROM stu WHERE EXISTS (SELECT score FROM `level` WHERE id = 1);

-- ANY SOME ALL
SELECT * FROM stu WHERE score >= ANY(SELECT score FROM `level`);
SELECT * FROM stu WHERE score > SOME(SELECT score FROM `level`);
SELECT * FROM stu WHERE score > ALL(SELECT score FROM `level`);

-- 創建一個user3表，將表stu中的id, username的信息寫入user3中
CREATE TABLE user3(
id INT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20)
) SELECT id, username FROM stu;

-- 將user1表中的username信息插入到user3中
INSERT user3(username) SELECT username FROM user1;

SELECT 字段名稱，… FROM 表名1
UNION
SELECT 字段名稱，… FROM 表名2;

SELECT 字段名稱，… FROM 表名1
UNION ALL 
SELECT 字段名稱，… FROM 表名2;

-- 創建表cate
CREATE TABLE cate(
id SMALLINT UNSIGNED AUTO_INCREMENT KEY COMMENT '編號',
cateName VARCHAR(100) NOT NULL UNIQUE COMMENT '分類名稱',
pId SMALLINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '父id'
);

-- 插入頂級分類
INSERT cate(cateName, pId) VALUES('服裝', 0),
('數碼', 0),
('玩具', 0),
-- 插入服裝的子分類
('男裝', 1),
('女裝', 1),
('內衣', 1),  
-- 插入數碼的子分類
('電視', 2),
('冰箱', 2),
('洗衣機', 2),
-- 插入玩具的子分類
('愛馬仕', 3),
('LV', 3),
('GUCCI', 3),
-- 插入男裝的子分類
('夾克', 4),
('襯衫', 4),
('褲子', 4),
-- 插入電視的子分類
('液晶電視', 7),
('等離子電視', 7),
('背投電視', 7);

-- 查詢所有的分類信息，並且得到其父分類
SELECT s.id, s.cateName AS sCateName, p.cateName AS pCateName
FROM cate AS s
LEFT JOIN cate AS p
ON s.pId = p.id;