MYSQL查询
基本命令
查询所有数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| auth_book |
| flask |
| jing_dong |
| mysql |
| name_list |
| performance_schema |
| sys |
| temp |
| test |
+--------------------+
12 rows in set (0.00 sec)
复制代码使用数据库
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
复制代码查询所有表
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| event |
| person |
| score |
| shirt |
| shop |
| t1 |
| user |
+----------------+
7 rows in set (0.00 sec)
复制代码查询语句
-
查询某个字段
name是表中的字段名，user是标的名字
mysql> select name from user;
+-------+
| name |
+-------+
| Ada2 |
| Bob2 |
| Mike2 |
+-------+
3 rows in set (0.00 sec)
复制代码- 查询所有字段的所有信息
mysql> select * from t1;
+------+-------+------+
| year | month | day |
+------+-------+------+
| 2000 | 01 | 01 |
| 2000 | 01 | 20 |
| 2000 | 01 | 30 |
| 2000 | 02 | 02 |
| 2000 | 02 | 23 |
| 2000 | 02 | 23 |
+------+-------+------+
6 rows in set (0.00 sec)
复制代码- 显示多个字段
mysql> select month, year from t1;
+-------+------+
| month | year |
+-------+------+
| 01 | 2000 |
| 01 | 2000 |
| 01 | 2000 |
| 02 | 2000 |
| 02 | 2000 |
| 02 | 2000 |
+-------+------+
6 rows in set (0.00 sec)
mysql> select month, day from t1;
+-------+------+
| month | day |
+-------+------+
| 01 | 01 |
| 01 | 20 |
| 01 | 30 |
| 02 | 02 |
| 02 | 23 |
| 02 | 23 |
+-------+------+
6 rows in set (0.00 sec)
复制代码查看表结构
mysql> desc score;
+---------------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| chinese_score | int(11) | YES | UNI | NULL | |
| math_score | int(11) | YES | | NULL | |
| user_id | int(11) | YES | MUL | NULL | |
+---------------+---------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
复制代码score是要查询的表名称
出重
mysql> select distinct month from t1;
+-------+
| month |
+-------+
| 01 |
| 02 |
+-------+
2 rows in set (0.00 sec)
复制代码限制查询结果
- 防止太多
mysql> select * from t1;
+------+-------+------+
| year | month | day |
+------+-------+------+
| 2000 | 01 | 01 |
| 2000 | 01 | 20 |
| 2000 | 01 | 30 |
| 2000 | 02 | 02 |
| 2000 | 02 | 23 |
| 2000 | 02 | 23 |
+------+-------+------+
6 rows in set (0.00 sec)
mysql> select * from t1 limit 3, 3;
+------+-------+------+
| year | month | day |
+------+-------+------+
| 2000 | 02 | 02 |
| 2000 | 02 | 23 |
| 2000 | 02 | 23 |
+------+-------+------+
3 rows in set (0.00 sec)
mysql> select * from t1 limit 2, 5;
+------+-------+------+
| year | month | day |
+------+-------+------+
| 2000 | 01 | 30 |
| 2000 | 02 | 02 |
| 2000 | 02 | 23 |
| 2000 | 02 | 23 |
+------+-------+------+
4 rows in set (0.00 sec)
复制代码limit 3, 3;第一个3代表开始的行数，第二个3代表显示的行数，在 行 数 不 够 时limit中 指 定 要 检 索 的 行 数 为 检 索 的 最 大 行 数。
使 用 完 全 限 定 的 表 名
mysql> select t1.year, t1.day from t1;
+------+------+
| year | day |
+------+------+
| 2000 | 01 |
| 2000 | 20 |
| 2000 | 30 |
| 2000 | 02 |
| 2000 | 23 |
| 2000 | 23 |
+------+------+
6 rows in set (0.00 sec)
复制代码mysql> select t1.year, t1.day from test.t1;
+------+------+
| year | day |
+------+------+
| 2000 | 01 |
| 2000 | 20 |
| 2000 | 30 |
| 2000 | 02 |
| 2000 | 23 |
| 2000 | 23 |
+------+------+
6 rows in set (0.01 sec)
复制代码排 序 检 索 数 据
mysql> select day from t1 order by day;
+------+
| day |
+------+
| 01 |
| 02 |
| 20 |
| 23 |
| 23 |
| 30 |
+------+
6 rows in set (0.01 sec)
mysql> select * from t1 order by day;
+------+-------+------+
| year | month | day |
+------+-------+------+
| 2000 | 01 | 01 |
| 2000 | 02 | 02 |
| 2000 | 01 | 20 |
| 2000 | 02 | 23 |
| 2000 | 02 | 23 |
| 2000 | 01 | 30 |
+------+-------+------+
6 rows in set (0.01 sec)
复制代码- 多个数据排序
mysql> select * from t1 order by month, day;
+------+-------+------+
| year | month | day |
+------+-------+------+
| 2000 | 01 | 01 |
| 2000 | 01 | 20 |
| 2000 | 01 | 30 |
| 2000 | 02 | 02 |
| 2000 | 02 | 23 |
| 2000 | 02 | 23 |
+------+-------+------+
6 rows in set (0.00 sec)
复制代码mysql> select * from t1 order by day desc;
+------+-------+------+
| year | month | day |
+------+-------+------+
| 2000 | 01 | 30 |
| 2000 | 02 | 23 |
| 2000 | 02 | 23 |
| 2000 | 01 | 20 |
| 2000 | 02 | 02 |
| 2000 | 01 | 01 |
+------+-------+------+
6 rows in set (0.00 sec)
# desc倒序
复制代码过滤数据
WHERE语句
mysql> select * from t1 where month=01;
+------+-------+------+
| year | month | day |
+------+-------+------+
| 2000 | 01 | 01 |
| 2000 | 01 | 20 |
| 2000 | 01 | 30 |
+------+-------+------+
3 rows in set (0.01 sec)
复制代码WHERE子句操作符
= 等于
<> 不等于
!= 不等于
< 小于
<= 小于等于
> 大于
>= 大于等于
BETWEEN 在指定的两个值之间
空值（NULL）监测
select * from t1 where month is null;
复制代码数据过滤
OR与AND
mysql> select prod_name, prod_price from products where vend_id = 1002 or vend_id = 1003 and prod_price >= 10;
+----------------+------------+
| prod_name | prod_price |
+----------------+------------+
| Detonator | 13.00 |
| Bird seed | 10.00 |
| Fuses | 3.42 |
| Oil can | 8.99 |
| Safe | 50.00 |
| TNT (5 sticks) | 10.00 |
+----------------+------------+
6 rows in set (0.01 sec)
复制代码可以看出prod_id出现**大于10**的，优先考虑and
IN 操 作 符
mysql> SELECT prod_name, prod_price FROM products WHERE vend_id IN (1002,1003) ORDER BY prod_name;
+----------------+------------+
| prod_name | prod_price |
+----------------+------------+
| Bird seed | 10.00 |
| Carrots | 2.50 |
| Detonator | 13.00 |
| Fuses | 3.42 |
| Oil can | 8.99 |
| Safe | 50.00 |
| Sling | 4.49 |
| TNT (1 stick) | 2.50 |
| TNT (5 sticks) | 10.00 |
+----------------+------------+
9 rows in set (0.01 sec)
# IN和Or相同使用更简洁
mysql> SELECT prod_name, prod_price FROM products WHERE vend_id = 1002 OR vend_id = 1003 ORDER BY prod_name;
+----------------+------------+
| prod_name | prod_price |
+----------------+------------+
| Bird seed | 10.00 |
| Carrots | 2.50 |
| Detonator | 13.00 |
| Fuses | 3.42 |
| Oil can | 8.99 |
| Safe | 50.00 |
| Sling | 4.49 |
| TNT (1 stick) | 2.50 |
| TNT (5 sticks) | 10.00 |
+----------------+------------+
9 rows in set (0.01 sec)
复制代码NOT 操 作 符
mysql> SELECT prod_name, prod_price FROM products WHERE vend_id NOT IN (1002,1003) ORDER BY prod_name;
+--------------+------------+
| prod_name | prod_price |
+--------------+------------+
| .5 ton anvil | 5.99 |
| 1 ton anvil | 9.99 |
| 2 ton anvil | 14.99 |
| JetPack 1000 | 35.00 |
| JetPack 2000 | 55.00 |
+--------------+------------+
5 rows in set (0.02 sec)
复制代码用 通 配 符 进 行 过 滤
LIKE 操 作 符
-
百 分 号（%） 通 配 符
最 常 使 用 的 通 配 符 是 百 分 号（%）。 在 搜 索 串 中，% 表 示 任 何 字 符 出 现 任 意 次 数。 例 如， 为 了 找 出 所 有 以 词 jet 起 头 的 产 品， 可 使 用 以 下 SELECT 语 句：
mysql> SELECT prod_id, prod_name FROM products WHERE prod_name LIKE 'jet%'; +---------+--------------+ | prod_id | prod_name | +---------+--------------+ | JP1000 | JetPack 1000 | | JP2000 | JetPack 2000 | +---------+--------------+ 2 rows in set (0.00 sec) mysql> SELECT prod_id, prod_name FROM products WHERE prod_name LIKE '% anvil%'; +---------+--------------+ | prod_id | prod_name | +---------+--------------+ | ANV01 | .5 ton anvil | | ANV02 | 1 ton anvil | | ANV03 | 2 ton anvil | +---------+--------------+ 3 rows in set (0.00 sec) 复制代码
区 分 大 小 写 根 据 MySQL 的 配 置 方 式， 搜 索 可 以 是 区 分 大 小 写 的。 如 果 区 分 大 小 写，' jet%' 与 JetPack 1000 将 不 匹 配。重 要 的 是 要 注 意 到， 除 了 一 个 或 多 个 字 符 外，% 还 能 匹 配 0 个 字 符。% 代 表 搜 索 模 式 中 给 定 位 置 的0 个、 1 个 或 多 个 字 符。
下 划 线（_） 通 配 符
下 划 线 的 用 途 与% 一 样， 但 下-划 线 只 匹 配 **单 个 字 符 **而 不 是 多 个 字 符。
mysql> SELECT prod_id, prod_name FROM products WHERE prod_name LIKE '_ ton anvil';
+---------+-------------+
| prod_id | prod_name |
+---------+-------------+
| ANV02 | 1 ton anvil |
| ANV03 | 2 ton anvil |
+---------+-------------+
2 rows in set (0.00 sec)
复制代码使用 正 则 表 达 式
mysql> SELECT prod_name FROM products WHERE prod_name REGEXP '.000' ORDER BY prod_name;
+--------------+
| prod_name |
+--------------+
| JetPack 1000 |
| JetPack 2000 |
+--------------+
2 rows in set (0.01 sec)
复制代码拼接字段
mysql> select concat(year, '-', month, '-', day)as 'year-month-day' from t1;
+----------------+
| year-month-day |
+----------------+
| 2000-01-01 |
| 2000-01-20 |
| 2000-01-30 |
| 2000-02-02 |
| 2000-02-23 |
| 2000-02-23 |
+----------------+
6 rows in set (0.01 sec)
复制代码使 用 数 据 处 理 函 数
mysql> select * from user;
+----+-------+
| id | name |
+----+-------+
| 1 | Ada2 |
| 2 | Bob2 |
| 3 | Mike2 |
+----+-------+
3 rows in set (0.01 sec)
mysql> select id, upper(name) from user; # 将name的值全部大写显示
+----+-------------+
| id | upper(name) |
+----+-------------+
| 1 | ADA2 |
| 2 | BOB2 |
| 3 | MIKE2 |
+----+-------------+
3 rows in set (0.00 sec)
复制代码| 函数 | 说明 |
|---|---|
| left( ) | 返 回 串 左 边 的 字 符 |
| length( ) | 返回串（并不仅仅是字符串）的长度 |
| Locate( ) | 找 出 串 的 一 个 子 串 |
| Lower( ) | 将串转为小写 |
| LTrim() | 去 掉 串 左 边 的 空 格 |
| Right() | 返 回 串 右 边 的 字 符 |
| RTrim() | 去 掉 串 右 边 的 空 格 |
| Soundex() | 返 回 串 的 SOUNDEX 值 |
| SubString() | 返 回 子 串 的 字 符 |
| Upper() | 将 串 转 换 为 大 写 |
mysql> select name from user where length(name)=5;
+-------+
| name |
+-------+
| Mike2 |
+-------+
1 row in set (0.01 sec)
mysql> select name from user where length(name)=4;
+------+
| name |
+------+
| Ada2 |
| Bob2 |
+------+
2 rows in set (0.00 sec)
复制代码添加日期
| 函数 | 说明 |
|---|---|
| AddDate() | 增 加 一 个 日 期（ 天、 周 等） |
| AddTime() | AddTime() 增 加 一 个 时 间（ 时、 分 等） |
| AddTime() | 增 加 一 个 时 间（ 时、 分 等） |
| CurTime() | CurTime() 返 回 当 前 时 间 |
| Date() | Date() 返 回 日 期 时 间 的 日 期 部 分 |
| DateDiff() | 计 算 两 个 日 期 之 差 |
| Date_Add() | 高 度 灵 活 的 日 期 运 算 函 数 |
| Date_Format() | 返 回 一 个 格 式 化 的 日 期 或 时 间 串 |
| Day() | 返 回 一 个 日 期 的 天 数 部 分 |
| DayOfWeek() | 对 于 一 个 日 期， 返 回 对 应 的 星 期 几 |
| Hour() | 返 回 一 个 时 间 的 小 时 部 分 |
| Minute() | 返 回 一 个 时 间 的 分 钟 部 分 |
| Month() | 返 回 一 个 日 期 的 月 份 部 分 |
| Now() | 返 回 当 前 日 期 和 时 间 |
| Second() | 返 回 一 个 时 间 的 秒 部 分 |
| Time() | 返 回 一 个 日 期 时 间 的 时 间 部 分 |
| Year() | Year() 返 回 一 个 日 期 的 年 份 部 分 |
聚合函数
avg平均值
mysql> select avg(day) from t1;
+----------+
| avg(day) |
+----------+
| 16.5000 |
+----------+
1 row in set (0.00 sec)
复制代码count计数
mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
| 6 |
+----------+
1 row in set (0.00 sec)
复制代码max最大值
mysql> select max(day) from t1;
+----------+
| max(day) |
+----------+
| 30 |
+----------+
1 row in set (0.01 sec)
复制代码min最小值
mysql> select min(day) from t1;
+----------+
| min(day) |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)
复制代码sum求总数
mysql> select sum(day) from t1;
+----------+
| sum(day) |
+----------+
| 99 |
+----------+
1 row in set (0.00 sec)
复制代码分组数据
group by
mysql> select * from shop;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
| 0001 | A | 3.45 |
| 0001 | B | 3.99 |
| 0002 | A | 10.99 |
| 0003 | B | 1.45 |
| 0003 | C | 1.69 |
| 0003 | D | 1.25 |
| 0004 | D | 19.95 |
+---------+--------+-------+
7 rows in set (0.00 sec)
mysql> select article,count(dealer), avg(price) from shop group by article;
+---------+---------------+------------+
| article | count(dealer) | avg(price) |
+---------+---------------+------------+
| 0001 | 2 | 3.720000 |
| 0002 | 1 | 10.990000 |
| 0003 | 3 | 1.463333 |
| 0004 | 1 | 19.950000 |
+---------+---------------+------------+
4 rows in set (0.01 sec)
复制代码having
having与where的区别在于having用于分组的条件过滤，其他情况下having可以实现where的所有功能
mysql> select article,count(dealer), avg(price) from shop group by article having count(dealer)>1;
+---------+---------------+------------+
| article | count(dealer) | avg(price) |
+---------+---------------+------------+
| 0001 | 2 | 3.720000 |
| 0003 | 3 | 1.463333 |
+---------+---------------+------------+
2 rows in set (0.02 sec)
复制代码分组排序
mysql> select article,count(dealer), avg(price) as price from shop group by article having count(dealer)>1 order by price;
+---------+---------------+----------+
| article | count(dealer) | price |
+---------+---------------+----------+
| 0003 | 3 | 1.463333 |
| 0001 | 2 | 3.720000 |
+---------+---------------+----------+
2 rows in set (0.00 sec)
复制代码SELECT语句顺序
| 子句 | 说明 | 是否使用 |
|---|---|---|
| select | 要 返 回 的 列 或 表 达 式 | 是 |
| from | 从 中 检 索 数 据 的 表 | 仅 在 从 表 选 择 数 据 时 使 用 |
| where | 行级过滤 | 否 |
| group by | 分组说明 | 仅在安分组计算分组聚集时使用 |
| having | 组级过滤 | 否 |
| order by | 输出排序顺序 | 否 |
| limit | 要检索数据行数 | 否 |
子查询
利用子查询过滤
mysql> SELECT cust_id FROM orders WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id = 'TNT2');
+---------+
| cust_id |
+---------+
| 10001 |
| 10004 |
+---------+
复制代码作为计算字段使用子查询
SELECT cust_name, cust_state, (SELECT COUNT(*) FROM orders WHERE cust_id = cust_id) AS orders FROM customers ORDER BY cust_name;
+----------------+------------+--------+
| cust_name | cust_state | orders |
+----------------+------------+--------+
| Coyote Inc. | MI | 5 |
| E Fudd | IL | 5 |
| Mouse House | OH | 5 |
| Wascals | IN | 5 |
| Yosemite Place | AZ | 5 |
+----------------+------------+--------+
复制代码联结表
内联结
mysql> SELECT vend_name, prod_name, prod_price FROM vendors INNER JOIN products ON vendors.vend_id = products.vend_id;
+-------------+----------------+------------+
| vend_name | prod_name | prod_price |
+-------------+----------------+------------+
| Anvils R Us | .5 ton anvil | 5.99 |
| Anvils R Us | 1 ton anvil | 9.99 |
| Anvils R Us | 2 ton anvil | 14.99 |
| LT Supplies | Fuses | 3.42 |
| LT Supplies | Oil can | 8.99 |
| ACME | Detonator | 13.00 |
| ACME | Bird seed | 10.00 |
| ACME | Carrots | 2.50 |
| ACME | Safe | 50.00 |
| ACME | Sling | 4.49 |
| ACME | TNT (1 stick) | 2.50 |
| ACME | TNT (5 sticks) | 10.00 |
| Jet Set | JetPack 1000 | 35.00 |
| Jet Set | JetPack 2000 | 55.00 |
+-------------+----------------+------------+
14 rows in set (0.00 sec)
# 会显示所有满足条件的数据
mysql> select * from score inner join user where score.user_id = user.id;
+----+---------------+------------+---------+----+-------+
| id | chinese_score | math_score | user_id | id | name |
+----+---------------+------------+---------+----+-------+
| 1 | 98 | 100 | 1 | 1 | Ada2 |
| 2 | 85 | 78 | 2 | 2 | Bob2 |
| 3 | 90 | 91 | 3 | 3 | Mike2 |
+----+---------------+------------+---------+----+-------+
复制代码联 结 多 个 表
SQL 对 一 条 SELECT 语 句 中 可 以 联 结 的 表 的 数 目 没 有 限 制。 创 建 联 结 的 基 本 规 则 也 相 同。 首 先 列 出 所 有 表， 然 后 定 义 表 之 间 的 关 系。
mysql> SELECT prod_name, vend_name, prod_price, quantity FROM orderitems, products, vendors WHERE products.vend_id = vendors.vend_id AND orderitems.prod_id = products.prod_id AND order_num = 20005;
+----------------+-------------+------------+----------+
| prod_name | vend_name | prod_price | quantity |
+----------------+-------------+------------+----------+
| .5 ton anvil | Anvils R Us | 5.99 | 10 |
| 1 ton anvil | Anvils R Us | 9.99 | 3 |
| TNT (5 sticks) | ACME | 10.00 | 5 |
| Bird seed | ACME | 10.00 | 1 |
+----------------+-------------+------------+----------+
4 rows in set (0.01 sec)
复制代码自联结
SELECT prod_id, prod_name FROM products WHERE vend_id = (SELECT vend_id FROM products WHERE prod_id = 'DTNTR');
复制代码mysql> SELECT p1. prod_id, p1. prod_name FROM products AS p1, products AS p2 WHERE p1. vend_id = p2. vend_id AND p2. prod_id = 'DTNTR';
+---------+----------------+
| prod_id | prod_name |
+---------+----------------+
| DTNTR | Detonator |
| FB | Bird seed |
| FC | Carrots |
| SAFE | Safe |
| SLING | Sling |
| TNT1 | TNT (1 stick) |
| TNT2 | TNT (5 sticks) |
+---------+----------------+
7 rows in set (0.00 sec)
复制代码left/right/inner join
mysql> select * from score left join user on score.user_id = user.id; # 以左边为参考，右边没有的为NULL
+----+---------------+------------+---------+------+-------+
| id | chinese_score | math_score | user_id | id | name |
+----+---------------+------------+---------+------+-------+
| 1 | 98 | 100 | 1 | 1 | Ada2 |
| 2 | 85 | 78 | 2 | 2 | Bob2 |
| 3 | 90 | 91 | 3 | 3 | Mike2 |
| 4 | 35 | 100 | 4 | 4 | Tom |
+----+---------------+------------+---------+------+-------+
4 rows in set (0.00 sec)
mysql> select * from score right join user on score.user_id = user.id; # 以右边的为参考，左边没有的为NULL
+------+---------------+------------+---------+----+-------+
| id | chinese_score | math_score | user_id | id | name |
+------+---------------+------------+---------+----+-------+
| 1 | 98 | 100 | 1 | 1 | Ada2 |
| 2 | 85 | 78 | 2 | 2 | Bob2 |
| 3 | 90 | 91 | 3 | 3 | Mike2 |
| 4 | 35 | 100 | 4 | 4 | Tom |
| NULL | NULL | NULL | NULL | 5 | Mecal |
+------+---------------+------------+---------+----+-------+
5 rows in set (0.00 sec)
mysql> select * from user left outer join score on user.id = score.user_id;# 以左边为参考，右边没有的为NULL
+----+-------+------+---------------+------------+---------+
| id | name | id | chinese_score | math_score | user_id |
+----+-------+------+---------------+------------+---------+
| 1 | Ada2 | 1 | 98 | 100 | 1 |
| 2 | Bob2 | 2 | 85 | 78 | 2 |
| 3 | Mike2 | 3 | 90 | 91 | 3 |
| 4 | Tom | 4 | 35 | 100 | 4 |
| 5 | Mecal | NULL | NULL | NULL | NULL |
+----+-------+------+---------------+------------+---------+
5 rows in set (0.00 sec)
mysql> select * from user left join score on user.id = score.user_id; # 以左边为参考，右边没有的为NULL
+----+-------+------+---------------+------------+---------+
| id | name | id | chinese_score | math_score | user_id |
+----+-------+------+---------------+------------+---------+
| 1 | Ada2 | 1 | 98 | 100 | 1 |
| 2 | Bob2 | 2 | 85 | 78 | 2 |
| 3 | Mike2 | 3 | 90 | 91 | 3 |
| 4 | Tom | 4 | 35 | 100 | 4 |
| 5 | Mecal | NULL | NULL | NULL | NULL |
+----+-------+------+---------------+------------+---------+
5 rows in set (0.00 sec)
mysql> select * from user inner join score on user.id = score.user_id; # 返回两边的共同的
+----+-------+----+---------------+------------+---------+
| id | name | id | chinese_score | math_score | user_id |
+----+-------+----+---------------+------------+---------+
| 1 | Ada2 | 1 | 98 | 100 | 1 |
| 2 | Bob2 | 2 | 85 | 78 | 2 |
| 3 | Mike2 | 3 | 90 | 91 | 3 |
| 4 | Tom | 4 | 35 | 100 | 4 |
+----+-------+----+---------------+------------+---------+
4 rows in set (0.00 sec)
复制代码mysql> select * from customers; # customers表
+---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+
| cust_id | cust_name | cust_address | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email |
+---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+
| 10001 | Coyote Inc. | 200 Maple Lane | Detroit | MI | 44444 | USA | Y Lee | ylee@coyote.com |
| 10002 | Mouse House | 333 Fromage Lane | Columbus | OH | 43333 | USA | Jerry Mouse | NULL |
| 10003 | Wascals | 1 Sunny Place | Muncie | IN | 42222 | USA | Jim Jones | rabbit@wascally.com |
| 10004 | Yosemite Place | 829 Riverside Drive | Phoenix | AZ | 88888 | USA | Y Sam | sam@yosemite.com |
| 10005 | E Fudd | 4545 53rd Street | Chicago | IL | 54545 | USA | E Fudd | NULL |
+---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+
5 rows in set (0.00 sec)
mysql> select * from orders; # orders表
+-----------+---------------------+---------+
| order_num | order_date | cust_id |
+-----------+---------------------+---------+
| 20005 | 2005-09-01 00:00:00 | 10001 |
| 20006 | 2005-09-12 00:00:00 | 10003 |
| 20007 | 2005-09-30 00:00:00 | 10004 |
| 20008 | 2005-10-03 00:00:00 | 10005 |
| 20009 | 2005-10-08 00:00:00 | 10001 |
+-----------+---------------------+---------+
5 rows in set (0.00 sec)
mysql> SELECT customers.cust_id, orders.order_num FROM customers RIGHT OUTER JOIN orders ON orders.cust_id = customers.cust_id;
+---------+-----------+
| cust_id | order_num |
+---------+-----------+
| 10001 | 20005 |
| 10001 | 20009 |
| 10003 | 20006 |
| 10004 | 20007 |
| 10005 | 20008 |
+---------+-----------+
5 rows in set (0.00 sec)
mysql> SELECT customers.cust_id, orders.order_num FROM customers inner JOIN orders ON orders.cust_id = customers.cust_id;
+---------+-----------+
| cust_id | order_num |
+---------+-----------+
| 10001 | 20005 |
| 10001 | 20009 |
| 10003 | 20006 |
| 10004 | 20007 |
| 10005 | 20008 |
+---------+-----------+
5 rows in set (0.00 sec)
复制代码使 用 带 聚 集 函 数 的 联 结
mysql> SELECT customers.cust_name, customers.cust_id, COUNT( orders.order_num) AS num_ord FROM customers INNER JOIN orders ON customers.cust_id = orders.cust_id GROUP BY customers.cust_id;
+----------------+---------+---------+
| cust_name | cust_id | num_ord |
+----------------+---------+---------+
| Coyote Inc. | 10001 | 2 |
| Wascals | 10003 | 1 |
| Yosemite Place | 10004 | 1 |
| E Fudd | 10005 | 1 |
+----------------+---------+---------+
4 rows in set (0.00 sec)
复制代码使用left查询出订单的数量
mysql> SELECT customers.cust_name, customers.cust_id, COUNT( orders.order_num) AS num_ord FROM customers LEFT OUTER JOIN orders ON customers.cust_id = orders.cust_id GROUP BY customers.cust_id;
+----------------+---------+---------+
| cust_name | cust_id | num_ord |
+----------------+---------+---------+
| Coyote Inc. | 10001 | 2 |
| Mouse House | 10002 | 0 |
| Wascals | 10003 | 1 |
| Yosemite Place | 10004 | 1 |
| E Fudd | 10005 | 1 |
+----------------+---------+---------+
5 rows in set (0.00 sec)
# 排序
mysql> SELECT customers.cust_name, customers.cust_id, COUNT( orders.order_num) AS num_ord FROM customers LEFT OUTER JOIN orders ON customers.cust_id = orders.cust_id GROUP BY customers.cust_id order by num_ord;
+----------------+---------+---------+
| cust_name | cust_id | num_ord |
+----------------+---------+---------+
| Mouse House | 10002 | 0 |
| E Fudd | 10005 | 1 |
| Wascals | 10003 | 1 |
| Yosemite Place | 10004 | 1 |
| Coyote Inc. | 10001 | 2 |
+----------------+---------+---------+
5 rows in set (0.01 sec)
复制代码
扫一扫
2788
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
