Mysql查询语法（读mysql必知必会有感）

来源：互联网发布：手机数据恢复软件免费版编辑：程序博客网时间：2024/06/05 12:46

通过看Mysql必知必会，发现里面讲的表的查询挺详细的，以下是我的看后的总结。

里面全是Mysql 的查询语句。从最基础的到难的。

先看看数据库的结构。

MariaDB [CargoWarehouse]> show tables;+--------------------------+| Tables_in_CargoWarehouse |+--------------------------+| customers                || orderitems               || orders                   || productnotes             || products                 || vendors                  |+--------------------------+6 rows in set (0.00 sec)

分别是：

customers表，存储着所有客户的信息

orderitems表，每个订单的实际物品

orders表，存储着顾客的订单

productnotes表，存储与特定产品相关的注释

products表，包含着产品目录

venders表，存储着销售产品的供应商

一.最简单的select检索

1.查products中的产品名字

MariaDB [CargoWarehouse]> select prod_name from products;+----------------+| prod_name      |+----------------+| .5 ton anvil   || 1 ton anvil    || 2 ton anvil    || Detonator      || Bird seed      || Carrots        || Fuses          || JetPack 1000   || JetPack 2000   || Oil can        || Safe           || Sling          || TNT (1 stick)  || TNT (5 sticks) |+----------------+14 rows in set (0.00 sec)

2.查看都有哪些值（相同重复的只显示一遍）

如果不用distinct的话，会显示所有的id值。包括重复的。（本来一共14行呢）

MariaDB [CargoWarehouse]> select distinct vend_id from products;+---------+| vend_id |+---------+|    1001 ||    1002 ||    1003 ||    1005 |+---------+4 rows in set (0.00 sec)

3.限制结果

这个是显示输出从第4行开始的6行（也就是4,5,6,7,8,9行）

MariaDB [CargoWarehouse]> select vend_id from products limit 3,6;+---------+| vend_id |+---------+|    1002 ||    1002 ||    1003 ||    1003 ||    1003 ||    1003 |+---------+6 rows in set (0.00 sec)

二.排序检索数据

1.倒序输出

因为默认就是升序（ASC）的，所以我只介绍降序（DESC）的（以价格的降序来输出）

order by 是以谁的顺序去排整张表

MariaDB [CargoWarehouse]> select prod_id,prod_price,prod_name from products order by prod_price desc limit 6;+---------+------------+----------------+| prod_id | prod_price | prod_name      |+---------+------------+----------------+| JP2000  |      55.00 | JetPack 2000   || SAFE    |      50.00 | Safe           || JP1000  |      35.00 | JetPack 1000   || ANV03   |      14.99 | 2 ton anvil    || DTNTR   |      13.00 | Detonator      || TNT2    |      10.00 | TNT (5 sticks) |+---------+------------+----------------+6 rows in set (0.00 sec)

2.和distinct 限制一起使用

MariaDB [CargoWarehouse]> select distinct vend_id from products order by prod_price desc;+---------+| vend_id |+---------+|    1005 ||    1003 ||    1001 ||    1002 |+---------+4 rows in set (0.00 sec)

三.数据过滤

1.where过滤（注意order by的位置）

MariaDB [CargoWarehouse]> select prod_name,prod_price from products where prod_price >= 10 order by prod_price desc;+----------------+------------+| prod_name      | prod_price |+----------------+------------+| JetPack 2000   |      55.00 || Safe           |      50.00 || JetPack 1000   |      35.00 || 2 ton anvil    |      14.99 || Detonator      |      13.00 || Bird seed      |      10.00 || TNT (5 sticks) |      10.00 |+----------------+------------+7 rows in set (0.00 sec)

2.不匹配检查

用 <> 不匹配检查，也可以使用!= （建议用这个）

MariaDB [CargoWarehouse]> select vend_id,prod_name from products where vend_id <> 13 limit 5;+---------+--------------+| vend_id | prod_name    |+---------+--------------+|    1001 | .5 ton anvil ||    1001 | 1 ton anvil  ||    1001 | 2 ton anvil  ||    1003 | Detonator    ||    1003 | Bird seed    |+---------+--------------+5 rows in set (0.00 sec)

3.在哪个范围之内

MariaDB [CargoWarehouse]> select vend_id,prod_name,prod_price from products where prod_price between 5 and 10;+---------+----------------+------------+| vend_id | prod_name      | prod_price |+---------+----------------+------------+|    1001 | .5 ton anvil   |       5.99 ||    1001 | 1 ton anvil    |       9.99 ||    1003 | Bird seed      |      10.00 ||    1002 | Oil can        |       8.99 ||    1003 | TNT (5 sticks) |      10.00 |+---------+----------------+------------+5 rows in set (0.00 sec)

或者使用 prod_price > 5 and prod_price < 10

这里的与或逻辑操作。and ，or

当然还可以使用IN（推荐使用）

MariaDB [CargoWarehouse]> select vend_id,prod_name,prod_price from products where prod_price in (5,10);+---------+----------------+------------+| vend_id | prod_name      | prod_price |+---------+----------------+------------+|    1003 | Bird seed      |      10.00 ||    1003 | TNT (5 sticks) |      10.00 |+---------+----------------+------------+2 rows in set (0.00 sec)

4.检查空值

MariaDB [CargoWarehouse]> select cust_id from customers where cust_email is null;+---------+| cust_id |+---------+|   10002 ||   10005 |+---------+2 rows in set (0.00 sec)

5.通配符进行过滤（LIKE）

这里 % 就好像是linux通配符里的* 。表示任何字符出现任意次数

这里就是jet开头的就行了

MariaDB [CargoWarehouse]> 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)

这里是以s开头e结尾

MariaDB [CargoWarehouse]> select prod_name from products where prod_name like 's%e';+-----------+| prod_name |+-----------+| Safe      |+-----------+1 row in set (0.00 sec)

这里的‘_’就相当于linux的通配符‘?’表示只匹配单个字符

MariaDB [CargoWarehouse]> 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)

6.正则表达式进行过滤

正则匹配是用regexp进行匹配,也可以使用rlike

用过sed正则都知道，正则是很强大的。

‘.’一个点表示的是单一一个字符。和通配的_一样。

MariaDB [CargoWarehouse]> 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.00 sec)

其中任意一个都可以

| 表示或的意思

MariaDB [CargoWarehouse]> select prod_name    -> from products    -> where prod_name regexp '1000|2000'    -> order by prod_name;+--------------+| prod_name    |+--------------+| JetPack 1000 || JetPack 2000 |+--------------+2 rows in set (0.00 sec)

[] 里面任意一个

MariaDB [CargoWarehouse]> select prod_name from products     -> where prod_name regexp '[123]';+---------------+| prod_name     |+---------------+| 1 ton anvil   || 2 ton anvil   || JetPack 1000  || JetPack 2000  || TNT (1 stick) |+---------------+5 rows in set (0.00 sec)

其中\\ 表示转义

其中声明一下。以下字符代表的是前面的重复的

* 前面的字符重复0次或者无数次

+ 前面的字符重复1次或者无数次

{n} 前面的字符重复n次

{n,}前面的字符重复至少n次

{n,m}前面的字符重复n到m次

MariaDB [CargoWarehouse]> select prod_name    -> from products    -> where prod_name regexp '[[:digit:]]{2,}'    -> order by prod_name;+--------------+| prod_name    |+--------------+| JetPack 1000 || JetPack 2000 |+--------------+2 rows in set (0.00 sec)

定位元字符

^ 文本的开始

$ 文本的结尾

[[:<:]] 词的开始

[[:>:]] 词的结尾

MariaDB [CargoWarehouse]> select prod_name    -> from products    -> where prod_name regexp 'vil[[:>:]]'    -> order by prod_name;+--------------+| prod_name    |+--------------+| .5 ton anvil || 1 ton anvil  || 2 ton anvil  |+--------------+3 rows in set (0.00 sec)

四.创建计算字段

1.拼接一个字段

这里使用了concat函数，将两个字段合并成了一个字段输出。比如将产品名字后直接跟价格输出

还可以使用as给新组成的字段起一个新的名字。

MariaDB [CargoWarehouse]> select concat(prod_name,'   (',prod_price,')')    -> as products    -> from products    -> order by prod_price desc;+--------------------------+| products                 |+--------------------------+| JetPack 2000   (55.00)   || Safe   (50.00)           || JetPack 1000   (35.00)   || 2 ton anvil   (14.99)    || Detonator   (13.00)      || TNT (5 sticks)   (10.00) || Bird seed   (10.00)      || 1 ton anvil   (9.99)     || Oil can   (8.99)         || .5 ton anvil   (5.99)    || Sling   (4.49)           || Fuses   (3.42)           || Carrots   (2.50)         || TNT (1 stick)   (2.50)   |+--------------------------+14 rows in set (0.00 sec)

2.执行算数计算

直接使用*将两个数字字段乘起来。最好再使用一个as给该新的字段起一个名字

MariaDB [CargoWarehouse]> select prod_id,quantity,item_price,quantity*item_price as expanded_price    -> from orderitems    -> where order_num = 20005;+---------+----------+------------+----------------+| prod_id | quantity | item_price | expanded_price |+---------+----------+------------+----------------+| ANV01   |       10 |       5.99 |          59.90 || ANV02   |        3 |       9.99 |          29.97 || TNT2    |        5 |      10.00 |          50.00 || FB      |        1 |      10.00 |          10.00 |+---------+----------+------------+----------------+4 rows in set (0.00 sec)

3.使用函数

使用now（）函数返回当前的操作时间。

MariaDB [CargoWarehouse]> select now() as operate_time,prod_id,item_price    -> from orderitems;+---------------------+---------+------------+| operate_time        | prod_id | item_price |+---------------------+---------+------------+| 2017-01-11 14:14:55 | ANV01   |       5.99 || 2017-01-11 14:14:55 | ANV02   |       9.99 || 2017-01-11 14:14:55 | TNT2    |      10.00 || 2017-01-11 14:14:55 | FB      |      10.00 || 2017-01-11 14:14:55 | JP2000  |      55.00 || 2017-01-11 14:14:55 | TNT2    |      10.00 || 2017-01-11 14:14:55 | FC      |       2.50 || 2017-01-11 14:14:55 | FB      |      10.00 || 2017-01-11 14:14:55 | OL1     |       8.99 || 2017-01-11 14:14:55 | SLING   |       4.49 || 2017-01-11 14:14:55 | ANV03   |      14.99 |+---------------------+---------+------------+11 rows in set (0.00 sec)

五.使用数据处理函数

1.文本处理函数

下面只介绍几个简单的函数

upper（）将字符串转换为大写的

lower（）将字符串转为小写的

substring（）找出串中的字符内容

sounddex（）返回发音相似的串

我觉得其他的都还好理解。就最后一个需要说一下。soundex（）返回的发音相似的串。

比如我现在想找一个姓李的顾客。但不知道到底是Lee还是Lie。这时候就可以使用soundex（）了

MariaDB [CargoWarehouse]> select cust_name,cust_contact    -> from customers    -> where soundex(cust_contact) = soundex('Y Lie');+-------------+--------------+| cust_name   | cust_contact |+-------------+--------------+| Coyote Inc. | Y Lee        |+-------------+--------------+1 row in set (0.00 sec)

2.时间处理函数

adddate（）增加一个日期

addtime（）增加一个时间

curtime（）当前时间

curdate（）当前日期

date（）返回一个日期的日期部分

dateoff（）计算两个时间之差

year（）返回一个时间的年的部分

month（）返回一个时间的月的部分

day（）返回一个时间的天的部分

hour（）返回一个时间的小时部分

minute（）返回一个时间的分钟的部分

Database changedMariaDB [CargoWarehouse]> select cust_id,order_date     -> from orders    -> where date(order_date) = '2005-09-01';+---------+---------------------+| cust_id | order_date          |+---------+---------------------+|   10001 | 2005-09-01 00:00:00 |+---------+---------------------+1 row in set (0.00 sec)

3.数学处理函数

abs（）绝对值

pi（）π值

rand（）返回一个随机数

sqrt（）返回平方根

4.聚集函数

avg（）返回一列的平均值

count（）返回一列的行数

max（）返回一列的最大那个

min（）不说了

sum（）返回一列的之和

例如返回平均数

MariaDB [CargoWarehouse]> select avg(prod_price) as avg_price    -> from products;+-----------+| avg_price |+-----------+| 16.133571 |+-----------+1 row in set (0.00 sec)

计算客户的总数

MariaDB [CargoWarehouse]> select count(*) as num_cust from customers;+----------+| num_cust |+----------+|        5 |+----------+1 row in set (0.00 sec)

MariaDB [CargoWarehouse]> select     -> count(prod_name) as count,    -> min(prod_price) as min_price,    -> max(prod_price) as max_price,    -> sum(prod_price) as all_price,    -> avg(prod_price) as avg_price    -> from products;+-------+-----------+-----------+-----------+-----------+| count | min_price | max_price | all_price | avg_price |+-------+-----------+-----------+-----------+-----------+|    14 |      2.50 |     55.00 |    225.87 | 16.133571 |+-------+-----------+-----------+-----------+-----------+1 row in set (0.00 sec)

六.分组数据

1.分组

首先试想一下。如果我想查看供货商id为1003都供了几种货。

那么就是

MariaDB [CargoWarehouse]> select count(*) as num_prods    -> from products where vend_id = 1003;+-----------+| num_prods |+-----------+|         7 |+-----------+1 row in set (0.00 sec)

但是我要是查看所有供货商都供了多少货物怎么办呢。这下就需要分组了。

group by

MariaDB [CargoWarehouse]> select vend_id,count(*) as num_prods    -> from products group by vend_id;+---------+-----------+| vend_id | num_prods |+---------+-----------+|    1001 |         3 ||    1002 |         2 ||    1003 |         7 ||    1005 |         2 |+---------+-----------+4 rows in set (0.00 sec)

2.分组选择

以上就是以vend_id为基础分组，那么怎么选择在供货商里供货大于等于3的供货商。

好像有个where可以选择。但在这里不再是where了，这时就是having了。

MariaDB [CargoWarehouse]> select vend_id,count(*) as num_prods    -> from products group by vend_id    -> having count(*) >= 3;+---------+-----------+| vend_id | num_prods |+---------+-----------+|    1001 |         3 ||    1003 |         7 |+---------+-----------+2 rows in set (0.00 sec)

MariaDB [CargoWarehouse]> select vend_id,sum(prod_price) as vend_price     -> from products    -> group by vend_id;+---------+------------+| vend_id | vend_price |+---------+------------+|    1001 |      30.97 ||    1002 |      12.41 ||    1003 |      92.49 ||    1005 |      90.00 |+---------+------------+4 rows in set (0.00 sec)

3.分组选择过滤group by 和 where相遇

首先看看这种情况出现的时候。

如果我想查看每个供货商提供货物的数量，但那些货物价值小于10美元的货物就算了不统计了，但是提供货物提供的2个以下也不统计。那么怎么查询呢。

MariaDB [CargoWarehouse]> select vend_id,count(*) as num_prods    -> from products    -> where prod_price >= 10    -> group by vend_id    -> having count(*) >= 2    -> order by vend_id desc;+---------+-----------+| vend_id | num_prods |+---------+-----------+|    1005 |         2 ||    1003 |         4 |+---------+-----------+2 rows in set (0.00 sec)

4.下面检索总计订单价格大于等于50的订单的订单号和总计订单价格

MariaDB [CargoWarehouse]> select order_num,sum(item_price*quantity) as total_price    -> from orderitems    -> group by order_num    -> having sum(item_price*quantity) >= 50;+-----------+-------------+| order_num | total_price |+-----------+-------------+|     20005 |      149.87 ||     20006 |       55.00 ||     20007 |     1000.00 ||     20008 |      125.00 |+-----------+-------------+4 rows in set (0.00 sec)

七.使用子查询

1.使用子查询进行过滤

首先说一下什么情况下使用子查询吧。

比如说我要列出所有订购物品id为TNT2的所有客户。

那么需要注意了，客户的id在orders这个表里，而物品的id在ordersitems这个表里。

来看看表的结构。

MariaDB [CargoWarehouse]> select * from 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)MariaDB [CargoWarehouse]> select * from orderitems;+-----------+------------+---------+----------+------------+| order_num | order_item | prod_id | quantity | item_price |+-----------+------------+---------+----------+------------+|     20005 |          1 | ANV01   |       10 |       5.99 ||     20005 |          2 | ANV02   |        3 |       9.99 ||     20005 |          3 | TNT2    |        5 |      10.00 ||     20005 |          4 | FB      |        1 |      10.00 ||     20006 |          1 | JP2000  |        1 |      55.00 ||     20007 |          1 | TNT2    |      100 |      10.00 ||     20008 |          1 | FC      |       50 |       2.50 ||     20009 |          1 | FB      |        1 |      10.00 ||     20009 |          2 | OL1     |        1 |       8.99 ||     20009 |          3 | SLING   |        1 |       4.49 ||     20009 |          4 | ANV03   |        1 |      14.99 |+-----------+------------+---------+----------+------------+11 rows in set (0.01 sec)

唯一可以联系这两张表的就是订单号order_num了，只有先查到物品id的订单号，再在

orders这个表通过订单号去查，都有哪些顾客买了物品。

MariaDB [CargoWarehouse]> select cust_id from orders    -> where order_num in     -> (select order_num from orderitems    -> where prod_id = 'TNT2' );+---------+| cust_id |+---------+|   10001 ||   10004 |+---------+2 rows in set (0.00 sec)

这下就查出了客户id为10001和10004买了prod_id 为 TNT2 的货物。

2.作为计算字段的子查询

显示customers表中的每名顾客的订单总数，订单则在orders表中。
先来看看这两张表。

MariaDB [CargoWarehouse]> select * from 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)MariaDB [CargoWarehouse]> select * from 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)

查询方法如下：

MariaDB [CargoWarehouse]> select cust_name,cust_state,    -> (select count(*)     -> from orders    -> where orders.cust_id = customers.cust_id) as orders    -> from customers    -> order by cust_name;+----------------+------------+--------+| cust_name      | cust_state | orders |+----------------+------------+--------+| Coyote Inc.    | MI         |      2 || E Fudd         | IL         |      1 || Mouse House    | OH         |      0 || Wascals        | IN         |      1 || Yosemite Place | AZ         |      1 |+----------------+------------+--------+5 rows in set (0.00 sec)

select count(*) from orders where orders.cust_id = customers.cust_id

这句就是查询订单里，每个客户的订单量和以orders分组的区别如下。

MariaDB [CargoWarehouse]> select count(*) from orders group by cust_id ;+----------+| count(*) |+----------+|        2 ||        1 ||        1 ||        1 |+----------+4 rows in set (0.00 sec)

八.联结表

联结表其实就是一下查好几张表，就是这么简单。

显示编号为20005的订单中的物品。

订单为20005的订单物品有很多信息，如产品名字，供应商，产品价格，数量等等信息。这些信息分别在orderitems，products，vendors。

MariaDB [CargoWarehouse]> 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.00 sec)

九.组合查询

组合查询更简单，就是将两个查询的查询结果合到一起显示了

MariaDB [CargoWarehouse]> select prod_name,prod_price    -> from products where vend_id=1001    -> union    -> select prod_name,prod_price    -> from products where prod_price > 10;+--------------+------------+| prod_name    | prod_price |+--------------+------------+| .5 ton anvil |       5.99 || 1 ton anvil  |       9.99 || 2 ton anvil  |      14.99 || Detonator    |      13.00 || JetPack 1000 |      35.00 || JetPack 2000 |      55.00 || Safe         |      50.00 |+--------------+------------+7 rows in set (0.00 sec)

十.全文本搜索

这个也很简单，只不过有一点，需要在创建的时候指明FULLTEXT（被查字段）和MyISAM存储引擎。

举个简单的例子。

MariaDB [CargoWarehouse]> select note_text    -> from productnotes    -> where Match(note_text) Against('orange');+----------------------------------------------------------------------------------------------------------------------+| note_text                                                                                                            |+----------------------------------------------------------------------------------------------------------------------+| Quantity varies, sold by the sack load.All guaranteed to be bright and orange, and suitable for use as rabbit bait. |+----------------------------------------------------------------------------------------------------------------------+1 row in set (0.03 sec)

其中match是搜索哪一列（前提是创建时指定FULLTEXT（）），Against是要搜索的字符串。

1 0