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

最新下载

凤凰时时彩平台网址: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');

文章评论

热门栏目

  • 这是一个错误的观点! 2018-12-16
  • 在鲜花下虚掩着“坑” “电商专供”还能走多远 2018-12-16
  • 甘肃宣讲十九大:丝路春风三千里,陇原儿女添豪情 2018-12-15
  • 驻工商总局纪检组问责9名司局级干部 持续发力 2018-12-14
  • 韩国一姐探望男排猛将被喊在一起 金软景合影中国男朋友 2018-12-13
  • 江西省“放管服”改革再出实招 2018-12-12
  • 尊重和保障宗教信仰自由的中国实践 2018-12-12
  • 首都电力共产党员先锋 2018-12-11
  • 美联储加息几成定局 专家:中国央行大概率跟随加息 2018-12-10
  • 习近平与人民日报的10个故事 2018-12-10
  • 【理上网来·辉煌十九大】十九大报告谱写中国大国外交新篇章 2018-12-09
  • 世界杯期间 拉萨交警严查酒驾醉驾毒驾 2018-12-08
  • 第十六届中国经济论坛 2018-12-07
  • 共产主义既按需分配又按劳分配(原创) 2018-12-06
  • 财经 俄罗斯世界杯的经济账 2018-12-06
  • 766| 411| 313| 907| 187| 262| 924| 312| 347| 905|