一聚教程网:一个值得你收藏的教程网站

最新下载

极速时时彩是不是骗局:MySQL子查询操作实例详解

时间:2018-07-04 09:39:14 编辑:猪哥 来源:转载

本文实例总结了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);

any some关键字的子查询

SELECT num1
FROM tbl1
WHERE num1 > ANY (SELECT num2 FROM tbl2);

all关键字的子查询

SELECT num1
FROM tbl1
WHERE num1 > ALL (SELECT num2 FROM tbl2);

exists关键字的子查询

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

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

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

in关键字的子查询

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 fruits
WHERE s_id =
(SELECT s1.s_id from suppliers AS s1 WHERE s1.s_city = 'Tianjin');

<>所有非

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

定义两个表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);

【例.53】返回tbl2表的所有 num2 列,然后将 tbl1 中的 num1 的值与之进行比较,只要大于 num2的任何值为符合查询条件的结果

SELECT num1
FROM tbl1
WHERE num1 > ANY (SELECT num2 FROM tbl2);

【例.54】返回tbl1表的中比tbl2表num2 列所有值都大的值

SELECT num1
FROM tbl1
WHERE num1 > ALL (SELECT num2 FROM tbl2);

【例.55】查询表suppliers表中是否存在s_id=107的供应商,如果存在则查询fruits表中的记录

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

【例.56】查询表suppliers表中是否存在s_id=107的供应商,如果存在则查询fruits表中的f_price大于10.20的记录

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

【例.57】查询表suppliers表中是否存在s_id=107的供应商,如果不存在则查询fruits表中的记录

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

【例.58】在orderitems表中查询订购f_id为c0的订单号,并根据订单号查询具有订单号的客户c_id

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

【例.59】与前一个例子语句类似,但是在SELECT语句中使用NOT IN操作符

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

【例.60】在suppliers表中查询s_city等于Tianjin的供应商s_id,然后在fruits表中查询所有该供应商提供的水果的种类

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

【例.61】在suppliers表中查询s_city等于Tianjin的供应商s_id,然后在fruits表中查询所有非该供应商提供的水果的种类,SQL语句如下:

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

文章评论

热门栏目

  • “游、购、娱”持续升温 中国经济内生动力表现强劲 2019-02-22
  • 殷之光朗诵《我骄傲我是中国人》 2019-02-22
  • 天津检察机关依法决定对窦华港涉嫌受贿案移送审查起诉 2019-02-21
  • 个税法迎第七次大修 起征点调至每年6万元 2019-02-20
  • 价值-热门标签-华商生活 2019-02-19
  • 互联网论坛社区服务管理规定 2019-02-18
  • 合肥一批干部任前公示 朱海舟拟提名为包河区副区长人选 2019-02-18
  • 首趟辽宁援疆旅游专列907名游客进疆抵达沙湾县 2019-02-17
  • 第13期全人代第1回会議政協第13期全国委員会第1回会議 2019-02-17
  • 20年前的伤情鉴定还管用吗? 2019-02-16
  • 2018中国汽车品牌发展峰会在京召开 2019-02-16
  • 陕北共产党发起人——李子洲 2019-02-15
  • 消委会教你如何挑选家居服 2019-02-14
  • 陕西卫计委党组书记胡志强被查,系山西省委原书记胡富国长子 2019-02-14
  • 我发现从五+年代农业用化肥农药,在六+年代几百年长的柿树几乎死光。没人研究! 2019-02-13
  • 600| 75| 888| 103| 436| 558| 582| 115| 673| 653|