第11讲——MySQL查询数据2

旺仔不是程序员
80 阅读9分钟

❤️持续创作，加速成长！开启掘金成长之旅！这是我参与「掘金日新计划 · 2 月更文挑战」的第 11 天，点击查看活动详情”

🎨 个人介绍

👉大家好，我是：旺仔不是程序员

👉认真分享技术，记录学习过程的点滴，如果我的分享能为你带来帮助，请支持我奥🍻

👉你的支持，是我每天更新的动力。

👉赞点：👍 留言：✍ 收藏：⭐

👉个人格言：想法一步一步的落实，才是你我前进最佳选择。

my.jpeg

1. 基本查新

1. 对于查询的概述

  1. 语法格式
 select 
   [all|distinct]
   <目标列的表达式1> [别名],
   <目标列的表达式2> [别名]...
 from <表名或视图名> [别名],<表名或视图名> [别名]...
 [where<条件表达式>]
 [group by <列名>  [having <条件表达式>]]
 [order by <列名> [asc|desc]]
 [limit <数字或者列表>];
  1. 简化版语法
 select *, 列名 from 表 where 条件
  1. select语句的执行顺序
 FROM
 <表名> # 选取表，将多个表数据通过笛卡尔积变成一个表。
 ON
 <筛选条件> # 对笛卡尔积的虚表进行筛选
 JOIN<join, left join, right join...>
 <join表> # 指定join，用于添加数据到on之后的虚表中，例如left join会将左表的剩余数据添加到虚表中
 WHERE
 <where条件> # 对上述虚表进行筛选
 GROUP BY
 <分组条件> # 分组
 <SUM()等聚合函数> # 用于having子句进行判断，在书写上这类聚合函数是写在having判断里面的
 HAVING
 <分组筛选> # 对分组后的结果进行聚合筛选
 SELECT
 <返回数据列表> # 返回的单列必须在group by子句中，聚合函数除外
 DISTINCT
 # 数据除重
 ORDER BY
 <排序条件> # 排序
 LIMIT
 <行数限制>

2. 查询分类

1. 使用集合函数查询

  1. COUNT()函数

    1. COUNT(*)计算表中总的行数，不管某列是否有数值或者为空值
    2. COUNT(字段名)计算指定列下总的行数，计算时将忽略空值的行
 # 查询customers表中总的行数，SQL语句如下：
 SELECT COUNT(*) AS cust_num 
 FROM customers;
 # 查询customers表中有电子邮箱的顾客的总数，SQL语句如下：
 SELECT COUNT(c_email) AS email_num
 FROM customers;
 # 在orderitems表中，使用COUNT()函数统计不同订单号中订购的水果种类，SQL语句如下：
 SELECT o_num, COUNT(f_id)
 FROM orderitems 
 GROUP BY o_num;

  1. SUM()函数

    1. SUM()是一个求总和的函数，返回指定列值的总和
    2. SUM()是一个求总和的函数，返回指定列值的总和
 # 在orderitems表中查询30005号订单一共购买的水果总量，SQL语句如下：
 SELECT SUM(quantity) AS items_total
 FROM orderitems
 WHERE o_num = 30005;
 # 在orderitems表中，使用SUM()函数统计不同订单号中订购的水果总量，SQL语句如下：
 SELECT o_num, SUM(quantity) AS items_total
 FROM orderitems
 GROUP BY o_num;

  1. AVG()函数

    1. AVG()函数通过计算返回的行数和每一行数据的和，求得指定列数据的平均值
    2. AVG()函数不会计算NULL的值
 # 在fruits表中，查询s_id=103的供应商的水果价格的平均值，SQL语句如下：
 SELECT AVG(f_price) AS avg_price
 FROM fruits
 WHERE s_id = 103;
 # 在fruits表中，查询每一个供应商的水果价格的平均值，SQL语句如下：
 SELECT s_id,AVG(f_price) AS avg_price
 FROM fruits
 GROUP BY s_id;

  1. MAX()函数

    1. MAX()返回指定列中的最大值
    2. MAX()函数除了用来找出最大的列值或日期值之外
    3. 还可以返回任意列中的最大值，包括返回字符类型的最大值
 # 在fruits表中查找市场上价格最高的水果，SQL语句如下：
 SELECT MAX(f_price) AS max_price FROM fruits;
 # 在fruits表中查找不同供应商提供的价格最高的水果，SQL语句如下：
 SELECT s_id, MAX(f_price) AS max_price
 FROM fruits
 GROUP BY s_id;
 # 在fruits表中查找f_name的最大值，SQL语句如下：
 SELECT MAX(f_name) FROM fruits;

  1. MIN()函数

    1. MIN()返回查询列中的最小值。
 # 在fruits表中查找市场上价格最低的水果，SQL语句如下：
 SELECT MIN(f_price) AS min_price FROM fruits;
 # 在fruits表中查找不同供应商提供的价格最低的水果，SQL语句如下：
 SELECT s_id, MIN(f_price) AS min_price
 FROM fruits
 GROUP BY s_id;

2. 多表查询

 # 内连接需要的表和数据
 # 为了演示的需要，首先创建数据表suppliers，SQL语句如下：
 CREATE TABLE suppliers
 (
   s_id      int      NOT NULL AUTO_INCREMENT,
   s_name    char(50) NOT NULL,
   s_city    char(50) NULL,
   s_zip     char(10) NULL,
   s_call    CHAR(50) NOT NULL,
   PRIMARY KEY (s_id)
 ) ;
 # 插入需要演示的数据，SQL语句如下：
 INSERT INTO suppliers(s_id, s_name,s_city,  s_zip, s_call)
 VALUES(101,'FastFruit Inc.','Tianjin','300000','48075'),
 (102,'LT Supplies','Chongqing','400000','44333'),
 (103,'ACME','Shanghai','200000','90046'),
 (104,'FNK Inc.','Zhongshan','528437','11111'),
 (105,'Good Set','Taiyuang','030000', '22222'),
 (106,'Just Eat Ours','Beijing','010', '45678'),
 (107,'DK Inc.','Zhengzhou','450000', '33332');
 # 在fruits表和suppliers表之间使用内连接查询。
 # 查询之前，查看两个表的结构：
 DESC fruits;
 ​
 DESC suppliers;
 ​
 # 外连接需要的表和数据
 CREATE TABLE orders
 (
   o_num  int      NOT NULL AUTO_INCREMENT,
   o_date datetime NOT NULL,
   c_id   int      NOT NULL,
   PRIMARY KEY (o_num)
 ) ;
 # 插入需要演示的数据，SQL语句如下：
 INSERT INTO orders(o_num, o_date, c_id)
 VALUES(30001, '2008-09-01', 10001),
 (30002, '2008-09-12', 10003),
 (30003, '2008-09-30', 10004),
 (30004, '2008-10-03', 10005),
 (30005, '2008-10-08', 10001);

  1. 内连接查询

    1. 显示主表和从表连接字段共有的数据
 # 由结果可以看到，fruits表和suppliers表中都有相同数据类型的字段s_id
 # 两个表通过s_id字段建立联系。接下来从fruits表中查询f_name、f_price字段
 # 从suppliers表中查询s_id、s_name，SQL语句如下：
 SELECT suppliers.s_id, s_name,f_name, f_price
 FROM fruits ,suppliers
 WHERE fruits.s_id = suppliers.s_id;
 ​
 # 在fruits表和suppliers表之间，使用INNER JOIN语法进行内连接查询，SQL语句如下：
 SELECT suppliers.s_id, s_name,f_name, f_price
 FROM fruits INNER JOIN suppliers
 ON fruits.s_id = suppliers.s_id;
 ​
 # 查询供应f_id= ‘a1’的水果供应商提供的其他水果种类，SQL语句如下：
 SELECT f1.f_id, f1.f_name
 FROM fruits AS f1, fruits AS f2
 WHERE f1.s_id = f2.s_id AND f2.f_id = 'a1';
 ​

  1. 外连接查询

    1. LEFT JOIN（左连接）：返回包括左表中的所有记录和右表中连接字段相等的记录
    2. RIGHT JOIN（右连接）：返回包括右表中的所有记录和左表中连接字段相等的记录
 # 在customers表和orders表中，查询所有客户，包括没有订单的客户，SQL语句如下：
 SELECT customers.c_id, orders.o_num
 FROM customers LEFT OUTER JOIN orders
 ON customers.c_id = orders.c_id;
 ​
 # 在customers表和orders表中，查询所有订单，包括没有客户的订单，SQL语句如下：
 SELECT customers.c_id, orders.o_num
 FROM customers RIGHT OUTER JOIN orders
 ON customers.c_id = orders.c_id;

  1. 复合条件连接查询

    1. 在连接查询的过程中，通过添加过滤条件限制查询的结果
 # 在customers表和orders表中，使用INNER JOIN语法查询customers表中ID为10001的客户的订单信息，SQL语句如下：
 SELECT customers.c_id, orders.o_num
 FROM customers INNER JOIN orders
 ON customers.c_id = orders.c_id AND customers.c_id = 10001;
 ​
 # 在fruits表和suppliers表之间，使用INNER JOIN语法进行内连接查询，并对查询结果排序，SQL语句如下：
 SELECT suppliers.s_id, s_name,f_name, f_price
 FROM fruits INNER JOIN suppliers
 ON fruits.s_id = suppliers.s_id
 ORDER BY fruits.s_id;

3. 子查询

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

  1. 带ANY、SOME关键字的子查询

    1. ANY和SOME关键字是同义词，表示满足其中任一条件
    2. 它们允许创建一个表达式对子查询的返回值列表进行比较
    3. 只要满足内层子查询中的任何一个比较条件，就返回一个结果作为外层查询的条件
 # 返回tbl2表的所有num2列，然后将tbl1中的num1的值与之进行比较
 # 只要大于num2的任何1个值，即为符合查询条件的结果。
 SELECT num1 FROM tbl1 WHERE num1 > ANY (SELECT num2 FROM tbl2);

  1. 带ALL关键字的子查询

    1. 需要同时满足所有内层查询的条件
 # 返回tbl1表中比tbl2表num2 列所有值都大的值，SQL语句如下：
 SELECT num1 FROM tbl1 WHERE num1 > ALL (SELECT num2 FROM tbl2);

  1. 带EXISTS关键字的子查询

    1. 如果至少返回一行，那么EXISTS的结果为true，此时外层查询语句将进行查询
    2. 如果子查询没有返回任何行，那么EXISTS返回的结果是false，此时外层语句将不进行查询
 # 查询suppliers表中是否存在s_id=107的供应商
 # 如果存在，则查询fruits表中的记录，SQL语句如下：
 SELECT * FROM fruits
 WHERE EXISTS
 (SELECT s_name FROM suppliers WHERE s_id = 107)
 ​
 # 查询suppliers表中是否存在s_id=107的供应商
 # 如果存在，则查询fruits表中的f_price大于10.20的记录，SQL语句如下：
 SELECT * FROM fruits
 WHERE f_price>10.20 AND EXISTS
 (SELECT s_name FROM suppliers WHERE s_id = 107)
 ​
 # 查询suppliers表中是否存在s_id=107的供应商，如果不存在则查询fruits表中的记录，SQL语句如下：
 SELECT * FROM fruits
 WHERE NOT EXISTS
 (SELECT s_name FROM suppliers WHERE s_id = 107)
  1. 带IN关键字的子查询
 # 在orderitems表中查询f_id为c0的订单号
 # 并根据订单号查询具有订单号的客户c_id，SQL语句如下：
 SELECT c_id FROM orders WHERE o_num IN
 (SELECT o_num  FROM orderitems WHERE f_id = 'c0');
 ​
 SELECT o_num  FROM orderitems WHERE f_id = 'c0';
 # 可以看到，符合条件的o_num列的值有两个：30003和30005，
 # 然后执行外层查询，在orders表中查询订单号等于30003或30005的客户c_id
 # 嵌套子查询语句还可以写为如下形式，实现相同的效果：
 SELECT c_id FROM orders WHERE o_num IN (30003, 30005);
 ​
 # 与前一个例子类似，但是在SELECT语句中使用NOT IN关键字，SQL语句如下：
 SELECT c_id FROM orders WHERE o_num NOT IN
 (SELECT o_num  FROM orderitems WHERE f_id = 'c0');
 ​
 SELECT * FROM orders;
  1. 带比较运算符的子查询
 # 在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')
 ​
 # 在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')

4. 合并查询结果

  1. 利用UNION关键字，可以给出多条SELECT语句，并将它们的结果组合成单个结果集
  2. 合并时，两个表对应的列数和数据类型必须相同
  3. 各个SELECT语句之间使用UNION或UNION ALL关键字分隔
  4. UNION不使用关键字ALL，执行的时候删除重复的记录，所有返回的行都是唯一的
 # 查询所有价格小于9的水果的信息，查询s_id等于101和103所有的水果的信息
 # 使用UNION连接查询结果，SQL语句如下：
 SELECT s_id, f_name, f_price 
 FROM fruits
 WHERE f_price < 9.0
 UNION 
 SELECT s_id, f_name, f_price 
 FROM fruits
 WHERE s_id IN(101,103);
 ​
 # 如前所述，UNION将多个SELECT语句的结果组合成一个结果集合。
 # 可以分开查看每个SELECT语句的结果：
 SELECT s_id, f_name, f_price
 FROM fruits
 WHERE f_price < 9.0;
 ​
 SELECT s_id, f_name, f_price
 FROM fruits
 WHERE s_id IN(101,103);
 ​
 ​
 # 查询所有价格小于9的水果的信息，查询s_id等于101和103的所有水果的信息，使用UNION ALL连接查询结果，SQL语句如下：
 SELECT s_id, f_name, f_price 
 FROM fruits
 WHERE f_price < 9.0
 UNION ALL
 SELECT s_id, f_name, f_price 
 FROM fruits
 WHERE s_id IN(101,103);
avatar
文章
阅读
粉丝