MySQL子查询操作实例详解

本文实例总结了MySQL子查询操作。分享给大家供大家参考，具体如下：

定义两个表tb1和tb2

CREATE table tbl1 ( num1 INT NOT NULL);CREATE table tbl2 ( num2 INT NOT NULL);

INSERT INTO tbl1 values(1), (5), (13), (27);INSERT INTO tbl2 values(6), (14), (11), (20);

SELECT num1FROM tbl1WHERE num1 > ANY (SELECT num2 FROM tbl2);

SELECT num1FROM tbl1WHERE num1 > ALL (SELECT num2 FROM tbl2);

SELECT * from fruitsWHERE EXISTS(SELECT s_name FROM suppliers WHERE s_id = 107);SELECT * from fruitsWHERE f_price>10.20 AND EXISTS(SELECT s_name FROM suppliers WHERE s_id = 107);SELECT * from fruitsWHERE NOT EXISTS(SELECT s_name FROM suppliers WHERE s_id = 107);

SELECT c_idFROM ordersWHERE o_num IN (SELECT o_num FROM orderitems WHERE f_id = 'c0');SELECT c_idFROM ordersWHERE o_num NOT IN (SELECT o_num FROM orderitems WHERE f_id = 'c0');

SELECT s_id, f_name FROM fruitsWHERE s_id =(SELECT s1.s_id from suppliers AS s1 WHERE s1.s_city = 'Tianjin');

SELECT s_id, f_name FROM fruitsWHERE s_id <>(SELECT s1.s_id from suppliers AS s1 WHERE s1.s_city = 'Tianjin');

CREATE table tbl1 ( num1 INT NOT NULL);CREATE table tbl2 ( num2 INT NOT NULL);

INSERT INTO tbl1 values(1), (5), (13), (27);INSERT INTO tbl2 values(6), (14), (11), (20);

SELECT num1FROM tbl1WHERE num1 > ANY (SELECT num2 FROM tbl2);

SELECT num1FROM tbl1WHERE num1 > ALL (SELECT num2 FROM tbl2);

SELECT * from fruitsWHERE EXISTS(SELECT s_name FROM suppliers WHERE s_id = 107);

SELECT * from fruitsWHERE f_price>10.20 AND EXISTS(SELECT s_name FROM suppliers WHERE s_id = 107);

SELECT * from fruitsWHERE NOT EXISTS(SELECT s_name FROM suppliers WHERE s_id = 107);

SELECT c_id FROM orders WHERE o_num IN(SELECT o_num FROM orderitems WHERE f_id = 'c0');

SELECT c_id FROM orders WHERE o_num NOT IN(SELECT o_num FROM orderitems WHERE f_id = 'c0');

SELECT s_id, f_name FROM fruitsWHERE s_id =(SELECT s1.s_id from suppliers AS s1 WHERE s1.s_city = 'Tianjin');

SELECT s_id, f_name FROM fruitsWHERE s_id <>(SELECT s1.s_id from suppliers AS s1 WHERE s1.s_city = 'Tianjin');

希望本文所述对大家MySQL数据库计有所帮助。

更多相关文章

随机推荐