MySQL 学习之二:MySQL函数

最新推荐文章于 2022-06-03 12:14:39 发布
最新推荐文章于 2022-06-03 12:14:39 发布
阅读量249 收藏
点赞数
分类专栏： MySQL 文章标签： MySQL sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/wml00876/article/details/101544600
版权

Concat( , , …) 字段拼接函数
注: 需要指定一个或多个字段, 之间用 逗号分隔 ;

mysql> SELECT Concat(vend_name, '(', vend_country, ')')
    -> FROM test.vendors
    -> ORDER BY vend_name;
+-------------------------------------------+
| Concat(vend_name, '(', vend_country, ')') |
+-------------------------------------------+
| ACME(USA)                                 |
| Anvils R Us(USA)                          |
| Furball Inc.(USA)                         |
| Jet Set(England)                          |
| Jouets Et Ours(France)                    |
| LT Supplies(USA)                          |
+-------------------------------------------+
6 rows in set (0.05 sec)

TRIM / RTRIM / LTRIM //过滤指定的字符串
例 :

mysql> SELECT TRIM('  bar   '); 
        -> 'bar' 
mysql> SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx');   --删除指定的首字符 x
        -> 'barxxx' 
mysql> SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx');      --删除指定的首尾字符 x
         -> 'bar'
mysql> SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz');  --删除指定的尾字符 x 
        -> 'barx' 
 mysql> SELECT LTRIM('  barbar'); 		  --去除左空格
        -> 'barbar'
 mysql> SELECT RTRIM('barbar   ');  	 -- 去除右空格 
		-> 'barbar'

AS // 使用别名, 重新命名表 表列名

mysql> SELECT Concat(RTrim(vend_name), '(', RTrim(vend_country), ')')
    -> AS vend_titile
    -> FROM test.vendors
    -> ORDER BY vend_name;
+------------------------+
| vend_titile            |
+------------------------+
| ACME(USA)              |
| Anvils R Us(USA)       |
| Furball Inc.(USA)      |
| Jet Set(England)       |
| Jouets Et Ours(France) |
| LT Supplies(USA)       |
+------------------------+
6 rows in set (0.07 sec)

字段执行算数运算
注 : ＋ － ＊ ／

原始数据 :
mysql> SELECT prod_id, quantity, item_price
    -> FROM test.orderitems
    -> WHERE order_num = 20005;
+---------+----------+------------+
| prod_id | quantity | item_price |
+---------+----------+------------+
| ANV01   |       10 |       5.99 |
| ANV02   |        3 |       9.99 |
| TNT2    |        5 |      10.00 |
| FB      |        1 |      10.00 |
+---------+----------+------------+
4 rows in set (0.02 sec)

执行运算显示运算结果, 通过AS 修改 创建列名
mysql> SELECT prod_id, quantity, item_price, quantity* item_price AS expanded_price
    -> FROM test.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.10 sec)

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
时间范围检索 :
注 : 当要检索 时间时尽量不要使用 WHERE order_date = ‘2005-09-01’ 这种格式, 当表内时间为: 2005-09-01 13:01:15 时, 就会匹配失败. 这时使用 Date() 函数就可以正常匹配了. 因为, Date() 函数只匹配字段中的日期部分.
如果需要检索 时间时,则使用 Time() 函数是不错的选择;
类似情况：Day(), Month(), Year()… …

mysql> SELECT  cust_id, order_num, order_date
    -> FROM test.orders
    -> WHERE Date(order_date) BETWEEN '2005-09-01' AND '2005-09-30';
+---------+-----------+---------------------+
| cust_id | order_num | order_date          |
+---------+-----------+---------------------+
|   10001 |     20005 | 2005-09-01 00:00:00 |
|   10003 |     20006 | 2005-09-12 00:00:00 |
|   10004 |     20007 | 2005-09-30 00:00:00 |
+---------+-----------+---------------------+
3 rows in set (0.00 sec)

mysql> SELECT cust_id, order_num, order_date
    -> FROM test.orders
    -> WHERE Year(order_date) = '2005' AND Month(order_date) = '9';
+---------+-----------+---------------------+
| cust_id | order_num | order_date          |
+---------+-----------+---------------------+
|   10001 |     20005 | 2005-09-01 00:00:00 |
|   10003 |     20006 | 2005-09-12 00:00:00 |
|   10004 |     20007 | 2005-09-30 00:00:00 |
+---------+-----------+---------------------+
3 rows in set (0.02 sec)

在这里插入图片描述
在这里插入图片描述
AVG() // 求部分平均值

mysql> SELECT AVG(prod_price) AS avg_price
    -> FROM test.products
    -> WHERE vend_id = 1003;
+-----------+
| avg_price |
+-----------+
| 13.212857 |
+-----------+
1 row in set (0.00 sec)

COUNT() // 总行数
注: SELECT COUNT(*) 返回表总行数

mysql> SELECT COUNT(cust_email) AS num_cust
    -> FROM test.customers
    -> ;
+----------+
| num_cust |
+----------+
|        3 |
+----------+
1 row in set (0.00 sec)

SUM() //求和

mysql> SELECT SUM(item_price*quantity) AS sum_price
    -> FROM test.orderitems
    -> WHERE order_num = 20005;
+-----------+
| sum_price |
+-----------+
|    149.87 |
+-----------+
1 row in set (0.03 sec)

DISTINCT 参数 // 不同值
注: DISTINCT 使用时,必须使用列名;
注：下例中 DISTINCT 作为 AVG的参数 限定只对不同价格进行取平均值;
另：DISTINCT 不能用于 COUNT(*) , 因此不能使用 COUNT(DISTINCT), 会报错 ;

mysql> SELECT AVG(DISTINCT prod_price) AS avg_price
    -> FROM test.products
    -> WHERE vend_id = 1003;
+-----------+
| avg_price |
+-----------+
| 15.998000 |
+-----------+
1 row in set (0.04 sec)

组合使用

mysql> SELECT COUNT(*) AS num_items,
    -> MIN(prod_price) AS price_min,
    -> MAX(prod_price) AS price_max,
    -> AVG(prod_price) AS price_avg
    -> FROM test.products;
+-----------+-----------+-----------+-----------+
| num_items | price_min | price_max | price_avg |
+-----------+-----------+-----------+-----------+
|        14 |      2.50 |     55.00 | 16.133571 |
+-----------+-----------+-----------+-----------+
1 row in set (0.00 sec)

GROUP BY //分组数据
注： WITH ROLLUP

mysql> SELECT * FROM test.products;
+---------+---------+----------------+------------+----------------------------------------------------------------+
| prod_id | vend_id | prod_name      | prod_price | prod_desc                                                      |
+---------+---------+----------------+------------+----------------------------------------------------------------+
| ANV01   |    1001 | .5 ton anvil   |       5.99 | .5 ton anvil, black, complete with handy hook                  |
| ANV02   |    1001 | 1 ton anvil    |       9.99 | 1 ton anvil, black, complete with handy hook and carrying case |
| ANV03   |    1001 | 2 ton anvil    |      14.99 | 2 ton anvil, black, complete with handy hook and carrying case |
| DTNTR   |    1003 | Detonator      |      13.00 | Detonator (plunger powered), fuses not included                |
| FB      |    1003 | Bird seed      |      10.00 | Large bag (suitable for road runners)                          |
| FC      |    1003 | Carrots        |       2.50 | Carrots (rabbit hunting season only)                           |
| FU1     |    1002 | Fuses          |       3.42 | 1 dozen, extra long                                            |
| JP1000  |    1005 | JetPack 1000   |      35.00 | JetPack 1000, intended for single use                          |
| JP2000  |    1005 | JetPack 2000   |      55.00 | JetPack 2000, multi-use                                        |
| OL1     |    1002 | Oil can        |       8.99 | Oil can, red                                                   |
| SAFE    |    1003 | Safe           |      50.00 | Safe with combination lock                                     |
| SLING   |    1003 | Sling          |       4.49 | Sling, one size fits all                                       |
| TNT1    |    1003 | TNT (1 stick)  |       2.50 | TNT, red, single stick                                         |
| TNT2    |    1003 | TNT (5 sticks) |      10.00 | TNT, red, pack of 10 sticks                                    |
+---------+---------+----------------+------------+----------------------------------------------------------------+
14 rows in set (0.03 sec)

mysql> SELECT vend_id, COUNT(*) AS num_prods
    -> FROM test.products
    -> GROUP BY vend_id;
+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
|    1001 |         3 |
|    1002 |         2 |
|    1003 |         7 |
|    1005 |         2 |
+---------+-----------+


mysql> SELECT vend_id, COUNT(*) AS num_prods
    -> FROM test.products
    -> GROUP BY vend_id WITH ROLLUP;
+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
|    1001 |         3 |
|    1002 |         2 |
|    1003 |         7 |
|    1005 |         2 |
|    NULL |        14 |
+---------+-----------+
5 rows in set (0.00 sec)

HAVING // 过滤分组
注：

  1. HAVING 支持所以 WHERE操作符 如 > < = 等。
  2. WHERE 与 HAVING 区别：WHERE 过滤指定的行而不是分组， 在数据分组前进行过滤， HAVING 在数据分组后进行过滤。

下例中： 只过滤订单数 >=2的订单；

mysql> SELECT *FROM test.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.09 sec)

mysql> SELECT cust_id, COUNT(*) AS orders
    -> FROM test.orders
    -> GROUP BY cust_id
    -> HAVING COUNT(*) >= 2;
+---------+--------+
| cust_id | orders |
+---------+--------+
|   10001 |      2 |
+---------+--------+
1 row in set (0.04 sec)

WHERE 、 GROUP BY 、 HAVING 结合使用
释： 过滤价格 >=10, 同时 id有两个以上的供应商个数；

mysql> SELECT * FROM test.products WHERE prod_price >= 10;
+---------+---------+----------------+------------+----------------------------------------------------------------+
| prod_id | vend_id | prod_name      | prod_price | prod_desc                                                      |
+---------+---------+----------------+------------+----------------------------------------------------------------+
| ANV03   |    1001 | 2 ton anvil    |      14.99 | 2 ton anvil, black, complete with handy hook and carrying case |
| DTNTR   |    1003 | Detonator      |      13.00 | Detonator (plunger powered), fuses not included                |
| FB      |    1003 | Bird seed      |      10.00 | Large bag (suitable for road runners)                          |
| JP1000  |    1005 | JetPack 1000   |      35.00 | JetPack 1000, intended for single use                          |
| JP2000  |    1005 | JetPack 2000   |      55.00 | JetPack 2000, multi-use                                        |
| SAFE    |    1003 | Safe           |      50.00 | Safe with combination lock                                     |
| TNT2    |    1003 | TNT (5 sticks) |      10.00 | TNT, red, pack of 10 sticks                                    |
+---------+---------+----------------+------------+----------------------------------------------------------------+
7 rows in set (0.02 sec)

mysql> SELECT vend_id, COUNT(*) AS num_prods
    -> FROM test.products
    -> WHERE prod_price >= 10
    -> GROUP BY vend_id
    -> HAVING COUNT(*) >=2;
+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
|    1003 |         4 |
|    1005 |         2 |
+---------+-----------+
2 rows in set (0.00 sec)

在这里插入图片描述
一般在使用GROUP BY 子句时，应该也给出ORDER BY 子句；

mysql> SELECT *FROM test.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.05 sec)

mysql> SELECT order_num, SUM(quantity* item_price) AS ordertotal
    -> FROM test.orderitems
    -> GROUP BY order_num
    -> HAVING SUM(quantity* item_price) >= 50
    -> ORDER BY ordertotal;
+-----------+------------+
| order_num | ordertotal |
+-----------+------------+
|     20006 |      55.00 |
|     20008 |     125.00 |
|     20005 |     149.87 |
|     20007 |    1000.00 |
+-----------+------------+
4 rows in set (0.00 sec)

SELECT 子句及其顺序
在这里插入图片描述

子句查询

跳转到联结表写法

mysql> SELECT cust_name, cust_contact
    -> FROM test.customers
    -> WHERE cust_id IN(SELECT cust_id
    ->                  FROM test.orders
    ->                  WHERE order_num IN(SELECT order_num
    ->                                     FROM test.orderitems
    ->                                     WHERE prod_id = 'TNT2'));
+----------------+--------------+
| cust_name      | cust_contact |
+----------------+--------------+
| Coyote Inc.    | Y Lee        |
| Yosemite Place | Y Sam        |
+----------------+--------------+
2 rows in set (0.16 sec)

使用子查询创建计算字段：

mysql> SELECT * FROM test.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 *FROM test.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.06 sec)

mysql> SELECT cust_name,
    ->        cust_state,
    ->        (SELECT COUNT(*)
    ->          FROM test.orders
    ->          WHERE test.orders.cust_id = customers.cust_id) AS orders
    -> FROM test.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.02 sec)

联结表

mysql> SELECT vend_name, prod_name, prod_price
    -> FROM test.vendors, test.products
    -> WHERE vendors.vend_id = products.vend_id
    -> ORDER BY vend_name, prod_name;
+-------------+----------------+------------+
| vend_name   | prod_name      | prod_price |
+-------------+----------------+------------+
| ACME        | Bird seed      |      10.00 |
| ACME        | Carrots        |       2.50 |
| ACME        | Detonator      |      13.00 |
| ACME        | Safe           |      50.00 |
| ACME        | Sling          |       4.49 |
| ACME        | TNT (1 stick)  |       2.50 |
| ACME        | TNT (5 sticks) |      10.00 |
| Anvils R Us | .5 ton anvil   |       5.99 |
| Anvils R Us | 1 ton anvil    |       9.99 |
| Anvils R Us | 2 ton anvil    |      14.99 |
| Jet Set     | JetPack 1000   |      35.00 |
| Jet Set     | JetPack 2000   |      55.00 |
| LT Supplies | Fuses          |       3.42 |
| LT Supplies | Oil can        |       8.99 |
+-------------+----------------+------------+
14 rows in set (0.14 sec)

INNER JOIN … … ON… … // 内部联结（等值联结）；
注：效果相当于 FROM xx_tables, xx_tables WHERE xx_field = xx_field;
联结优先使用 INNER JOIN … ON 语法

mysql> SELECT vend_name, prod_name, prod_price
    -> FROM test.vendors INNER JOIN test.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 |11
| 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 vend_name, prod_name, prod_price
    -> FROM test.vendors, test.products
    -> WHERE 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 prod_name, vend_name, prod_price, quantity
    -> FROM test.orderitems, test.products, test.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.06 sec)
联结表写法
注：SQL操作可有不同方法实现；

跳转到子句查询写法

mysql> SELECT cust_name, cust_contact
    -> FROM test.customers, test.orders, test. orderitems
    -> WHERE customers.cust_id = orders.cust_id
    -> AND orders.order_num = orderitems.order_num
    -> AND prod_id = 'TNT2';
+----------------+--------------+
| cust_name      | cust_contact |
+----------------+--------------+
| Coyote Inc.    | Y Lee        |
| Yosemite Place | Y Sam        |
+----------------+--------------+
2 rows in set (0.00 sec)

使用表别名

mysql> SELECT cust_name, cust_contact
    -> FROM test.customers AS c, test.orders AS o, test.orderitems AS oi
    -> WHERE c.cust_id = o.cust_id
    -> AND oi.order_num = o.order_num
    -> AND prod_id = 'TNT2';
+----------------+--------------+
| cust_name      | cust_contact |
+----------------+--------------+
| Coyote Inc.    | Y Lee        |
| Yosemite Place | Y Sam        |
+----------------+--------------+
2 rows in set (0.00 sec)

自然联结

mysql> SELECT c.*, o.order_num, o.order_date,
    ->          oi.prod_id, oi.quantity, oi.item_price
    -> FROM test.customers AS c, test.orders AS o, test.orderitems AS oi
    -> WHERE c.cust_id = o.cust_id
    -> AND oi.order_num = o.order_num
    -> AND prod_id = 'FB';
+---------+-------------+----------------+-----------+------------+----------+--------------+--------------+-----------------+-----------+---------------------+---------+----------+------------+
| cust_id | cust_name   | cust_address   | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email      | order_num | order_date          | prod_id | quantity | item_price |
+---------+-------------+----------------+-----------+------------+----------+--------------+--------------+-----------------+-----------+---------------------+---------+----------+------------+
|   10001 | Coyote Inc. | 200 Maple Lane | Detroit   | MI         | 44444    | USA          | Y Lee        | ylee@coyote.com |     20005 | 2005-09-01 00:00:00 | FB      |        1 |      10.00 |
|   10001 | Coyote Inc. | 200 Maple Lane | Detroit   | MI         | 44444    | USA          | Y Lee        | ylee@coyote.com |     20009 | 2005-10-08 00:00:00 | FB      |        1 |      10.00 |
+---------+-------------+----------------+-----------+------------+----------+--------------+--------------+-----------------+-----------+---------------------+---------+----------+------------+
2 rows in set (0.00 sec)

左外联与右外联

https://www.cnblogs.com/withscorpion/p/9454490.html

带聚集函数的联结

mysql> SELECT customers.cust_name,
    ->          customers.cust_id,
    ->          COUNT(orders.order_num) AS num_ord
    -> FROM test.customers INNER JOIN test.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.06 sec)

组合查询

UNION 关键字

注：

  1. UNION必须由两条或以上SELECT语句组成，语句之间用UNION关键字分隔；
  2. UNION中每个查询必须包含相同的列、表达式或聚集函数（各个列不需要以相同的次序列出）。
  3. 列数据类型必须兼容：类型不必完全相同，但必须 是DBMS可以隐含地转换（不同的数值类型或不同的日期类型）
mysql> SELECT vend_id, prod_id, prod_price
    -> FROM test.products
    -> WHERE prod_price <=5
    -> UNION
    -> SELECT vend_id, prod_id, prod_price
    -> FROM test.products
    -> WHERE vend_id IN(1001, 1002);
+---------+---------+------------+
| vend_id | prod_id | prod_price |
+---------+---------+------------+
|    1003 | FC      |       2.50 |
|    1002 | FU1     |       3.42 |
|    1003 | SLING   |       4.49 |
|    1003 | TNT1    |       2.50 |
|    1001 | ANV01   |       5.99 |
|    1001 | ANV02   |       9.99 |
|    1001 | ANV03   |      14.99 |
|    1002 | OL1     |       8.99 |
+---------+---------+------------+
8 rows in set (0.08 sec)
另一种写法：
mysql> SELECT vend_id , prod_id , prod_price
    -> FROM test.products
    -> WHERE prod_price <=5
    -> OR vend_id IN(1001, 1002);
+---------+---------+------------+
| vend_id | prod_id | prod_price |
+---------+---------+------------+
|    1001 | ANV01   |       5.99 |
|    1001 | ANV02   |       9.99 |
|    1001 | ANV03   |      14.99 |
|    1003 | FC      |       2.50 |
|    1002 | FU1     |       3.42 |
|    1002 | OL1     |       8.99 |
|    1003 | SLING   |       4.49 |
|    1003 | TNT1    |       2.50 |
+---------+---------+------------+
8 rows in set (0.00 sec)

UNION ALL //包含重复行

mysql> SELECT vend_id, prod_id, prod_price
    -> FROM products
    -> WHERE prod_price <= 5
    -> UNION ALL
    -> SELECT vend_id, prod_id, prod_price
    -> FROM ;
ERROR 1046 (3D000): No database selected
mysql> SELECT vend_id, prod_id, prod_price
    -> FROM test.products
    -> WHERE prod_price <= 5
    -> UNION ALL
    -> SELECT vend_id, prod_id, prod_price
    -> FROM test.products
    -> WHERE vend_id IN (1001,1002);
+---------+---------+------------+
| vend_id | prod_id | prod_price |
+---------+---------+------------+
|    1003 | FC      |       2.50 |
|    1002 | FU1     |       3.42 |
|    1003 | SLING   |       4.49 |
|    1003 | TNT1    |       2.50 |
|    1001 | ANV01   |       5.99 |
|    1001 | ANV02   |       9.99 |
|    1001 | ANV03   |      14.99 |
|    1002 | FU1     |       3.42 |
|    1002 | OL1     |       8.99 |
+---------+---------+------------+
9 rows in set (0.00 sec)

组合查询结果进行排序

mysql> SELECT vend_id, prod_id, prod_price
    -> FROM test.products
    -> WHERE prod_price <= 5
    -> UNION
    -> SELECT vend_id, prod_id, prod_price
    -> FROM test.products
    -> WHERE vend_id IN (1001, 1002)
    -> ORDER BY vend_id, prod_price;
+---------+---------+------------+
| vend_id | prod_id | prod_price |
+---------+---------+------------+
|    1001 | ANV01   |       5.99 |
|    1001 | ANV02   |       9.99 |
|    1001 | ANV03   |      14.99 |
|    1002 | FU1     |       3.42 |
|    1002 | OL1     |       8.99 |
|    1003 | FC      |       2.50 |
|    1003 | TNT1    |       2.50 |
|    1003 | SLING   |       4.49 |
+---------+---------+------------+
8 rows in set (0.00 sec)

全文搜索

Match( xx_field ) Against(’ text ')
注：索引之后使用Match() 和Against() 函数执行全文本搜索， Match() 指定被搜索的列，Against() 指定要使用的搜索表达式。

  1. 传递给Match() 的值必须与 FULLTEXT() 定义中的相同；
  2. 如果指定多个列，必须依次列出（次序保证正确）；
  3. 除非使用BINARY方式，否则全文本搜索不区分大小写；
mysql> SELECT note_text
    -> FROM test.productnotes
    -> WHERE Match(note_text) Against('rabbit');
+----------------------------------------------------------------------------------------------------------------------+
| note_text                                                                                                            |
+----------------------------------------------------------------------------------------------------------------------+
| Customer complaint: rabbit has been able to detect trap, food apparently less effective now.                         |
| Quantity varies, sold by the sack load.
All guaranteed to be bright and orange, and suitable for use as rabbit bait. |
+----------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.03 sec)

是查询扩展

mysql> SELECT note_text
-> FROM test.productnotes
-> WHERE Match(note_text)Against(‘anvils’ WITH QUERY EXPANSION);
没用的阿_吉
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
mysql 数据查询
看清所以看轻
04-30 696
在MySQL数据库中查询数据通用的 SELECT 语法： 查询语句中你可以使用一个或者多个表，表之间使用逗号(,)分割，并使用WHERE语句来设定查询条件。 SELECT 命令可以读取一条或者多条记录。 使用星号（*）来代替其他字段，SELECT语句会返回表的所有字段数据 使用 WHERE 语句来包含任何条件。 使用 LIMIT 属性来设定返回的记录数。 通过OFFSET指定SELECT语句开始查...
Mysql导入文件出现错误ERROR 1046 (3D000)：no database selected
just a moment
06-04 1万+
终端执行 source 导入 sql 文件时，出现：ERROR 1046 (3D000)：no database selected 1、首先查看一下所有的数据库，如果你的数据库名为mydb，则输入命令： show databases; 2、如果显示的表中mydb不存在，就先创建mydb，继续输入命令： create database mydb; 3、再选中这个数据库mydb： use mydb; 4、最后再执行source命令，就没有出现错误了 ...
mysql ERROR 1046 (3D000) at line 22: No database selected解决方法
janet1100的博客
09-24 3658
再导入sql文件时报错： mysql ERROR 1046 (3D000) at line 22: No database selected解决方法 从错误来看 没有database 被选择， 打开sql文件，查看22行，是 在此句前面加上一句，use database（你自己db的名称） save，重新导入即可。 ...
MySql 优化技巧
Domando
08-24 733
来源：ZDNet China软件技术专区　　本文从八个方面来讲解如何全新优化MySQL数据库性能。 1、选取最适用的字段属性　 　MySQL可以很好的支持大数据量的存取，但是一般说来，数据库中的表越小，在它上面执行的查询也就会越快。因此，在创建表的时候，为了获得更好的性 能，我们可以将表中字段的宽度设得尽可能小。例如，在定义邮政编码这个字段时，如果将其设置为CHAR(255),显然给数据库增加了不
mysql解决超库存问题
qq_32923295的博客
01-06 1484
mysql解决超库存问题
MySQL函数：数据库编程的瑞士军刀
最新发布
07-22
- **易于使用**：MySQL提供了简单直观的界面和丰富的文档，便于用户学习和使用。 - **可扩展性**：MySQL支持从小型应用到大型企业级应用的扩展。 - **社区支持**：由于其广泛的使用，MySQL拥有一个活跃的开发者社区...
MySQL学习笔记：MySQL安装，MySQL数据类型，MySQL事务，MySQL函数
01-13
MySQL 学习笔记 MySQL 安装 MySQL 的安装非常简单，首先需要下载 MySQL 的安装包，下载路径是 https://dev.mysql.com/downloads/mysql/，可以选择要在什么平台使用 MySQL，然后点击下载。下载后直接解压到一个...
MySQL学习笔记：组合查询
01-19
本文为本人学习书籍《MySQL必知必会》笔记系列，欢迎持续关注，有问题随时留言评论，一起探讨学习~ 17组合查询 17.1 使用UNION操作符将多条SELECT语句组合成一个结果集。 查找Price1>5000的商品 SELECT id,name,...
Mysql函数手册.rar_MySQL函数手册_VZI_mysql 函数手册
09-15
7. 位操作函数：如BIT_AND()、BIT_OR()和BIT_XOR()用于进行二进制位运算。 8. 信息函数：如DATABASE()返回当前数据库名，USER()获取当前用户名，VERSION()显示MySQL版本信息。 手册还可能包含关于聚合函数（如...
MySQL与Oracle差异比较之三 函数
09-09
1. 数字函数： - `ROUND()`：在MySQL和Oracle中，`ROUND()`函数用于对数字进行四舍五入，两个数据库的用法相同，例如`ROUND(1.23456, 4)`都会返回1.2346。 - `ABS()`：两个数据库都提供了`ABS()`函数来获取数字的...
MySQL 必知必会 学习笔记三
ahaha413525642的博客
08-20 540
MySQL
mysql 条件判断查询语句_mysql数据库条件判断查询语句的常见写法说明
weixin_39760434的博客
01-19 500
大家都知道只有的了mysql 5后才出现了存储过程，这些用法，那么我们要在sql 中用if else while这些，就得用存过程或函数来实例了。 mysql DELIMITER // mysql CREATE FUNCTION myFunction (quantity INT(10)) RETURNS INT(10) - BEGIN - - WHILE quantity MO大家都知道只有的了m...
MySQL必知必会笔记（三）SELECT语句 计算字段 数据处理函数 汇总函数 分组数据 子查询
wdt3385的专栏
12-10 1437
第十章  创建计算字段     计算字段         存储在表中的数据一般不是应用程序所需要的格式。我们需要直接从数据库中检索出转换、计算或格式化的数据。而不只是检索出数据，然后再到应用程序或报告程序中区格式化。         这就发挥了计算字段的作用了。与前面的字段不同，计算字段并不实际存在于数据库中。计算字段是运行时在SELECT 语句中创建的。
MySQL必知必会十二：汇总数据
神薯片
05-26 3541
12.1 聚集函数 聚集函数（ aggregate function） 运行在行组上，计算和返回单个值的函数。 函 数 说 明 AVG() 返回某列的平均值 COUNT() 返回某列的行数 MAX() 返回某列的最大值 MIN() 返回某列的最小值 SUM() 返回某列值之和 12.1.1 AVG()函数 AVG()通过对表中行数计数并计算特定列值之和，求得该列的平均值。 AVG()可用来返回所有列的平均值，也可以用来返回特定列或行的平均值。下面的例子使用AVG()返回p
MySQL必知必会：汇总、分组、 嵌套查询
dangfulin的博客
09-03 1457
一，汇总数据 1，聚集函数 经常需要汇总数据而不是直接使用它们实际检索出来的结果，为此，MySQL给出了5个聚集函数。使用这些函数， MySQL查询可用于检索数据，以便分析和报表生成。 聚集函数（ aggregate function） 运行在行组上，计算和返回单个值的函数. AVG() 返回某列的平均值 COUNT() 返回某列的行数 MAX() 返回某列的最大值 MIN() 返回某列的最小值 SUM() 返回某列值之和 1，AVG()函数： AVG()通过对表中行数计数并计算特定列值之和，求得该列的
mysql学习知识点总结
ningmoudufanchen的博客
09-29 558
文章目录mysql学习知识点总结mysql一些特点运算符选择语句SELECT子句WHERE子句LIKE比较原始可以用正则，效果和LIKE一样，不过更强大NULL缺失值ORDER　BY子句，排序，DESC表示降序LIMIT限定查询的结果数量内连接INNER JOIN注意跨数据库连接自连接多表连接复合连接条件隐式连接语法外连接多表外连接自连接同理语句简化自然连接、交叉连接联合列属性插入单行插入多行插入分层行：子母表复制表更新单行更新多行更新时用子查询删除行聚合函数group by 子句单列分组多列分组havin
MySQL的数据查询详解
weixin_47019016的博客
12-12 3516
MySQL查询数据一、基本查询语句 数据库管理系统的一个最重要的功能就是数据查询，数据查询不应只是简单查询数据库中存储的数据，还应该根据需要对数据进行筛选，以及确定数据以什么样的格式显示。MySQL提供了功能强大、灵活的语句来实现这些操作。 一、基本查询语句 mysql> create table fruits -> ( -> f_id char(10) not null, -> s_id int not null, -> f_name char(255) not n
Mysql的IO介绍及原因详解
热门推荐
qq_44969643的博客
06-03 2万+
一、IO操作 二、IO成本 三、IO分类 四、Mysql网络层IO（网络IO） 五、Mysql存储IO（磁盘IO）在计算机系统中I/O就是输入（Input）和输出(Output)的意思，针对不同的操作对象，可以划分为磁盘I/O模型，网络I/O模型，内存映射I/O, Direct I/O、数据库I/O等，只要具有输入输出类型的交互系统都可以认为是I/O系统，也可以说I/O是整个操作系统数据交换与人机交互的通道，这个概念与选用的开发语言没有关系，是一个通用的概念。返回顶部目录寻址时间（网络IO没有这一步） 磁头
MySQL函数学习：从字符串到进制转换
"MySQL函数大全及用法示例.pdf" MySQL数据库中包含一系列函数，用于处理各种数据类型和任务。本资源聚焦于介绍和演示这些函数的使用，非常适合初学者学习和参考。以下是一些主要的字符串函数及其用法： 1. ASCII...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值