首页 > > 网络编程 > Mysql >

SQL必知必会1-13 读书笔记

2018-06-23 13:24:01来源：未知阅读 ()

博主不想写字并向你仍来了一堆代码

1-6

SQL——结构化查询语言，Structured Query Language；

基本按列查询：

mysql> SELECT prod_id,prod_name,prod_desc
    -> FROM products
    -> ORDER BY prod_id;
+---------+---------------------+-----------------------------------------------------------------------+
| prod_id | prod_name           | prod_desc                                                             |
+---------+---------------------+-----------------------------------------------------------------------+
| BNBG01  | Fish bean bag toy   | Fish bean bag toy, complete with bean bag worms with which to feed it |
| BNBG02  | Bird bean bag toy   | Bird bean bag toy, eggs are not included                              |
| BNBG03  | Rabbit bean bag toy | Rabbit bean bag toy, comes with bean bag carrots                      |
| BR01    | 8 inch teddy bear   | 8 inch teddy bear, comes with cap and jacket                          |
| BR02    | 12 inch teddy bear  | 12 inch teddy bear, comes with cap and jacket                         |
| BR03    | 18 inch teddy bear  | 18 inch teddy bear, comes with cap and jacket                         |
| RGAN01  | Raggedy Ann         | 18 inch Raggedy Ann doll                                              |
| RYL01   | King doll           | 12 inch king doll with royal garments and crown                       |
| RYL02   | Queen doll          | 12 inch queen doll with royal garments and crown                      |
+---------+---------------------+-----------------------------------------------------------------------+

mysql> SELECT prod_id,prod_name,prod_desc
    -> FROM products
    -> ORDER BY prod_price,prod_name;
+---------+---------------------+-----------------------------------------------------------------------+
| prod_id | prod_name           | prod_desc                                                             |
+---------+---------------------+-----------------------------------------------------------------------+
| BNBG02  | Bird bean bag toy   | Bird bean bag toy, eggs are not included                              |
| BNBG01  | Fish bean bag toy   | Fish bean bag toy, complete with bean bag worms with which to feed it |
| BNBG03  | Rabbit bean bag toy | Rabbit bean bag toy, comes with bean bag carrots                      |
| RGAN01  | Raggedy Ann         | 18 inch Raggedy Ann doll                                              |
| BR01    | 8 inch teddy bear   | 8 inch teddy bear, comes with cap and jacket                          |
| BR02    | 12 inch teddy bear  | 12 inch teddy bear, comes with cap and jacket                         |
| RYL01   | King doll           | 12 inch king doll with royal garments and crown                       |
| RYL02   | Queen doll          | 12 inch queen doll with royal garments and crown                      |
| BR03    | 18 inch teddy bear  | 18 inch teddy bear, comes with cap and jacket                         |
+---------+---------------------+-----------------------------------------------------------------------+
9 rows in set (0.00 sec)

mysql> SELECT prod_id,prod_name,prod_desc
    -> FROM products
    -> ORDER BY prod_price,prod_name DESC;
+---------+---------------------+-----------------------------------------------------------------------+
| prod_id | prod_name           | prod_desc                                                             |
+---------+---------------------+-----------------------------------------------------------------------+
| BNBG03  | Rabbit bean bag toy | Rabbit bean bag toy, comes with bean bag carrots                      |
| BNBG01  | Fish bean bag toy   | Fish bean bag toy, complete with bean bag worms with which to feed it |
| BNBG02  | Bird bean bag toy   | Bird bean bag toy, eggs are not included                              |
| RGAN01  | Raggedy Ann         | 18 inch Raggedy Ann doll                                              |
| BR01    | 8 inch teddy bear   | 8 inch teddy bear, comes with cap and jacket                          |
| BR02    | 12 inch teddy bear  | 12 inch teddy bear, comes with cap and jacket                         |
| RYL02   | Queen doll          | 12 inch queen doll with royal garments and crown                      |
| RYL01   | King doll           | 12 inch king doll with royal garments and crown                       |
| BR03    | 18 inch teddy bear  | 18 inch teddy bear, comes with cap and jacket                         |
+---------+---------------------+-----------------------------------------------------------------------+

高级一点的过滤查询，WHERE,ORDER,IN,NOT,AND,OR,BETWEEN,NULL:

mysql> SELECT prod_id,prod_name,prod_desc
    -> FROM products
    -> WHERE prod_price IS NULL;
Empty set (0.00 sec)

mysql> SELECT prod_id,prod_name,prod_desc
    -> FROM products
    -> WHERE (vend_id <> 'DLL01' OR  prod_id LIKE 'BR%') AND prod_price BETWEEN 3 AND 10;
+---------+--------------------+--------------------------------------------------+
| prod_id | prod_name          | prod_desc                                        |
+---------+--------------------+--------------------------------------------------+
| BR01    | 8 inch teddy bear  | 8 inch teddy bear, comes with cap and jacket     |
| BR02    | 12 inch teddy bear | 12 inch teddy bear, comes with cap and jacket    |
| RYL01   | King doll          | 12 inch king doll with royal garments and crown  |
| RYL02   | Queen doll         | 12 inch queen doll with royal garments and crown |
+---------+--------------------+--------------------------------------------------+
4 rows in set (0.00 sec)

mysql> SELECT prod_id,prod_name,prod_desc
    -> FROM products
    -> WHERE vend_id IN ('DLL01','BRS01')
    -> ORDER BY prod_name;
+---------+---------------------+-----------------------------------------------------------------------+
| prod_id | prod_name           | prod_desc                                                             |
+---------+---------------------+-----------------------------------------------------------------------+
| BR02    | 12 inch teddy bear  | 12 inch teddy bear, comes with cap and jacket                         |
| BR03    | 18 inch teddy bear  | 18 inch teddy bear, comes with cap and jacket                         |
| BR01    | 8 inch teddy bear   | 8 inch teddy bear, comes with cap and jacket                          |
| BNBG02  | Bird bean bag toy   | Bird bean bag toy, eggs are not included                              |
| BNBG01  | Fish bean bag toy   | Fish bean bag toy, complete with bean bag worms with which to feed it |
| BNBG03  | Rabbit bean bag toy | Rabbit bean bag toy, comes with bean bag carrots                      |
| RGAN01  | Raggedy Ann         | 18 inch Raggedy Ann doll                                              |
+---------+---------------------+-----------------------------------------------------------------------+
7 rows in set (0.00 sec)

mysql> SELECT prod_id,prod_name,prod_desc
    -> FROM products
    -> WHERE vend_id NOT IN ('DLL01','BRS01')
    -> ORDER BY prod_name;
+---------+------------+--------------------------------------------------+
| prod_id | prod_name  | prod_desc                                        |
+---------+------------+--------------------------------------------------+
| RYL01   | King doll  | 12 inch king doll with royal garments and crown  |
| RYL02   | Queen doll | 12 inch queen doll with royal garments and crown |
+---------+------------+--------------------------------------------------+
2 rows in set (0.00 sec)

通配符，%,_,[]（实测MySQL我这里不支持。。。）

不要过分使用通配符（和*一样）

不要把通配符放在搜索的开始处

mysql> SELECT prod_id,prod_name,prod_desc
    -> FROM products
    -> WHERE prod_name LIKE '__ inch teddy bear' OR prod_name LIKE 'Fish%';
+---------+--------------------+-----------------------------------------------------------------------+
| prod_id | prod_name          | prod_desc                                                             |
+---------+--------------------+-----------------------------------------------------------------------+
| BR02    | 12 inch teddy bear | 12 inch teddy bear, comes with cap and jacket                         |
| BR03    | 18 inch teddy bear | 18 inch teddy bear, comes with cap and jacket                         |
| BNBG01  | Fish bean bag toy  | Fish bean bag toy, complete with bean bag worms with which to feed it |
+---------+--------------------+-----------------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql> SELECT *
    -> FROM customers;
+------------+---------------+----------------------+-----------+------------+----------+--------------+--------------------+-----------------------+
| cust_id    | cust_name     | cust_address         | cust_city | cust_state | cust_zip | cust_country | cust_contact       | cust_email            |
+------------+---------------+----------------------+-----------+------------+----------+--------------+--------------------+-----------------------+
| 1000000001 | Village Toys  | 200 Maple Lane       | Detroit   | MI         | 44444    | USA          | John Smith         | sales@villagetoys.com |
| 1000000002 | Kids Place    | 333 South Lake Drive | Columbus  | OH         | 43333    | USA          | Michelle Green     | NULL                  |
| 1000000003 | Fun4All       | 1 Sunny Place        | Muncie    | IN         | 42222    | USA          | Jim Jones          | jjones@fun4all.com    |
| 1000000004 | Fun4All       | 829 Riverside Drive  | Phoenix   | AZ         | 88888    | USA          | Denise L. Stephens | dstephens@fun4all.com |
| 1000000005 | The Toy Store | 4545 53rd Street     | Chicago   | IL         | 54545    | USA          | Kim Howard         | NULL                  |
+------------+---------------+----------------------+-----------+------------+----------+--------------+--------------------+-----------------------+
5 rows in set (0.00 sec)

mysql> SELECT cust_contact
    -> FROM customers
    -> WHERE cust_contact LIKE '[JM]%'
    -> ORDER BY cust_id;
Empty set (0.00 sec)

mysql> SELECT cust_contact
    -> FROM customers
    -> WHERE cust_contact LIKE '[JM]%'
    -> ORDER BY cust_contact;
Empty set (0.00 sec)

mysql> SELECT cust_contact
    -> FROM customers
    -> WHERE cust_contact LIKE '[^JM]%'
    -> ORDER BY cust_contact;
Empty set (0.00 sec)

MySQL的计算字段：CONCAT(),+-*/,AS，TRIM()

别个DB可能是+或者||，更好读？？不是很懂，反正MySQL不得行

mysql> SELECT vend_name + '('+vend_country+')'
    -> FROM vendors
    -> ORDER BY vend_name;
+----------------------------------+
| vend_name + '('+vend_country+')' |
+----------------------------------+
|                                0 |
|                                0 |
|                                0 |
|                                0 |
|                                0 |
|                                0 |
+----------------------------------+
6 rows in set, 24 warnings (0.00 sec)

mysql> SELECT vend_name + '|' + vend_country + '|'
    -> FROM vendors
    -> ORDER BY vend_name;
+--------------------------------------+
| vend_name + '|' + vend_country + '|' |
+--------------------------------------+
|                                    0 |
|                                    0 |
|                                    0 |
|                                    0 |
|                                    0 |
|                                    0 |
+--------------------------------------+
6 rows in set, 24 warnings (0.00 sec)

mysql> SELECT * FROM vendors;
+---------+-----------------+-----------------+------------+------------+----------+--------------+
| vend_id | vend_name       | vend_address    | vend_city  | vend_state | vend_zip | vend_country |
+---------+-----------------+-----------------+------------+------------+----------+--------------+
| BRS01   | Bears R Us      | 123 Main Street | Bear Town  | MI         | 44444    | USA          |
| BRE02   | Bear Emporium   | 500 Park Street | Anytown    | OH         | 44333    | USA          |
| DLL01   | Doll House Inc. | 555 High Street | Dollsville | CA         | 99999    | USA          |
| FRB01   | Furball Inc.    | 1000 5th Avenue | New York   | NY         | 11111    | USA          |
| FNG01   | Fun and Games   | 42 Galaxy Road  | London     | NULL       | N16 6PS  | England      |
| JTS01   | Jouets et ours  | 1 Rue Amusement | Paris      | NULL       | 45678    | France       |
+---------+-----------------+-----------------+------------+------------+----------+--------------+
6 rows in set (0.00 sec)

mysql> SELECT vend_name || ' ( '||vend_country || ' )  '
    -> FROM vendors
    -> ORDER BY vend_name;
+--------------------------------------------+
| vend_name || ' ( '||vend_country || ' )  ' |
+--------------------------------------------+
|                                          0 |
|                                          0 |
|                                          0 |
|                                          0 |
|                                          0 |
|                                          0 |
+--------------------------------------------+
6 rows in set, 24 warnings (0.00 sec)

mysql> SELECT CONCAT(vend_name,vend_country)
    -> FROM vendors
    -> ORDER BY vend_name;
+--------------------------------+
| CONCAT(vend_name,vend_country) |
+--------------------------------+
| Bear EmporiumUSA               |
| Bears R UsUSA                  |
| Doll House Inc.USA             |
| Fun and GamesEngland           |
| Furball Inc.USA                |
| Jouets et oursFrance           |
+--------------------------------+
6 rows in set (0.00 sec)

mysql> SELECT CONCAT(vend_name,'(',vend_country,')')
    -> FROM vendors
    -> ORDER BY vend_name;
+----------------------------------------+
| CONCAT(vend_name,'(',vend_country,')') |
+----------------------------------------+
| Bear Emporium(USA)                     |
| Bears R Us(USA)                        |
| Doll House Inc.(USA)                   |
| Fun and Games(England)                 |
| Furball Inc.(USA)                      |
| Jouets et ours(France)                 |
+----------------------------------------+
6 rows in set (0.00 sec)

mysql> SELECT vend_name,
    ->        CONCAT(vend_address,',',vend_city,',',vend_state,',',vend_country) AS VendorsInfo,
    ->        vend_zip
    -> FROM vendors
    -> ORDER BY vend_zip,vend_name DESC;
+-----------------+-----------------------------------+----------+
| vend_name       | VendorsInfo                       | vend_zip |
+-----------------+-----------------------------------+----------+
| Furball Inc.    | 1000 5th Avenue,New York,NY,USA   | 11111    |
| Bear Emporium   | 500 Park Street,Anytown,OH,USA    | 44333    |
| Bears R Us      | 123 Main Street,Bear Town,MI,USA  | 44444    |
| Jouets et ours  | NULL                              | 45678    |
| Doll House Inc. | 555 High Street,Dollsville,CA,USA | 99999    |
| Fun and Games   | NULL                              | N16 6PS  |
+-----------------+-----------------------------------+----------+
6 rows in set (0.00 sec)

mysql> SELECT prod_id,quantity,item_price,
    ->       quantity*item_price AS sum_price
    -> FROM orderitems
    -> WHERE order_num=20008;
+---------+----------+------------+-----------+
| prod_id | quantity | item_price | sum_price |
+---------+----------+------------+-----------+
| RGAN01  |        5 |       4.99 |     24.95 |
| BR03    |        5 |      11.99 |     59.95 |
| BNBG01  |       10 |       3.49 |     34.90 |
| BNBG02  |       10 |       3.49 |     34.90 |
| BNBG03  |       10 |       3.49 |     34.90 |
+---------+----------+------------+-----------+
5 rows in set (0.00 sec)

使用数据处理函数：CURDATE(),YEAR(),UPPER(),TRIM(),ABS()

不同的DBMS的函数不是很一样，所以，使用函数的SQL代码移植性不好，如果一定要用，记得写清楚注释：

mysql> SELECT CONCAT(prod_name,'+',CURDATE())
    -> FROM products;
+---------------------------------+
| CONCAT(prod_name,'+',CURDATE()) |
+---------------------------------+
| 8 inch teddy bear+2016-08-04    |
| 12 inch teddy bear+2016-08-04   |
| 18 inch teddy bear+2016-08-04   |
| Fish bean bag toy+2016-08-04    |
| Bird bean bag toy+2016-08-04    |
| Rabbit bean bag toy+2016-08-04  |
| Raggedy Ann+2016-08-04          |
| King doll+2016-08-04            |
| Queen doll+2016-08-04           |
+---------------------------------+
9 rows in set (0.00 sec)

mysql> SELECT order_num
    -> FROM orders
    -> WHERE YEAR(order_date)=2004;
+-----------+
| order_num |
+-----------+
|     20005 |
|     20006 |
|     20007 |
|     20008 |
|     20009 |
+-----------+
5 rows in set (0.00 sec)

mysql> SELECT UPPER(price_name) AS Name,prod_price
    -> FROM products
    -> ORDER BY prod_name;
ERROR 1054 (42S22): Unknown column 'price_name' in 'field list'
mysql> SELECT UPPER(prod_name) AS Name,prod_price
    -> FROM products
    -> ORDER BY prod_name;
+---------------------+------------+
| Name                | prod_price |
+---------------------+------------+
| 12 INCH TEDDY BEAR  |       8.99 |
| 18 INCH TEDDY BEAR  |      11.99 |
| 8 INCH TEDDY BEAR   |       5.99 |
| BIRD BEAN BAG TOY   |       3.49 |
| FISH BEAN BAG TOY   |       3.49 |
| KING DOLL           |       9.49 |
| QUEEN DOLL          |       9.49 |
| RABBIT BEAN BAG TOY |       3.49 |
| RAGGEDY ANN         |       4.99 |
+---------------------+------------+
9 rows in set (0.00 sec)

聚集函数：COUNT(),AVG(),MAX(),MIN(),SUM():

mysql> SELECT UPPER(prod_name) AS P_Name,AVG(prod_price) AS P_Price
    -> FROM products
    -> WHERE prod_price BETWEEN 2 AND 10;
+-------------------+----------+
| P_Name            | P_Price  |
+-------------------+----------+
| 8 INCH TEDDY BEAR | 6.177500 |
+-------------------+----------+
1 row in set (0.00 sec)

mysql> SELECT UPPER(prod_name) AS P_Name,AVG(prod_price) AS P_Price
    -> FROM products;
+-------------------+----------+
| P_Name            | P_Price  |
+-------------------+----------+
| 8 INCH TEDDY BEAR | 6.823333 |
+-------------------+----------+
1 row in set (0.00 sec)

mysql> SELECT AVG(prod_price) AS P_Price
    -> FROM products;
+----------+
| P_Price  |
+----------+
| 6.823333 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) AS num_cust
    -> FROM customers;
+----------+
| num_cust |
+----------+
|        5 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) AS Items,
    ->        AVG(DISTINCT prod_price) AS price_avg,
    ->        MAX(prod_price) AS price_max,
    ->        MIN(prod_price) AS price_min
    -> FROM products;
+-------+-----------+-----------+-----------+
| Items | price_avg | price_max | price_min |
+-------+-----------+-----------+-----------+
|     9 |  7.490000 |     11.99 |      3.49 |
+-------+-----------+-----------+-----------+
1 row in set (0.00 sec)

分组：GROUP BY,HAVING;注意各种语句的顺序问题SELECT->FROM->WHERE->GROUP BY->HAVING->ORDER BY

 mysql> SELECT prod_name,COUNT(*) AS num_prod 
   -> FROM products
    -> GROUP BY vend_id;
+-------------------+----------+
| prod_name         | num_prod |
+-------------------+----------+
| 8 inch teddy bear |        3 |
| Fish bean bag toy |        4 |
| King doll         |        2 |
+-------------------+----------+
3 rows in set (0.00 sec)

mysql> SELECT prod_price,COUNT(*) AS num_price
    -> FROM products
    -> GROUP BY prod_price;
+------------+-----------+
| prod_price | num_price |
+------------+-----------+
|       3.49 |         3 |
|       4.99 |         1 |
|       5.99 |         1 |
|       8.99 |         1 |
|       9.49 |         2 |
|      11.99 |         1 |
+------------+-----------+
6 rows in set (0.00 sec)

mysql> SELECT prod_price,COUNT(*) AS num_price
    -> FROM products
    -> GROUP BY prod_price
    -> HAVING COUNT(*)>=2
    -> ORDER BY prod_price DESC;
+------------+-----------+
| prod_price | num_price |
+------------+-----------+
|       9.49 |         2 |
|       3.49 |         3 |
+------------+-----------+
2 rows in set (0.00 sec)

mysql> SELECT prod_name,COUNT(*) AS num_prod
    -> FROM products
    -> WHERE prod_price>=3
    -> GROUP BY prod_name
    -> HAVING COUNT(*)>=2
    -> ORDER BY prod_price DESC;
Empty set (0.00 sec)

mysql> SELECT prod_name,prod_price,COUNT(*) AS num_prod
    -> FROM products
    -> GROUP BY prod_name;
+---------------------+------------+----------+
| prod_name           | prod_price | num_prod |
+---------------------+------------+----------+
| 12 inch teddy bear  |       8.99 |        1 |
| 18 inch teddy bear  |      11.99 |        1 |
| 8 inch teddy bear   |       5.99 |        1 |
| Bird bean bag toy   |       3.49 |        1 |
| Fish bean bag toy   |       3.49 |        1 |
| King doll           |       9.49 |        1 |
| Queen doll          |       9.49 |        1 |
| Rabbit bean bag toy |       3.49 |        1 |
| Raggedy Ann         |       4.99 |        1 |
+---------------------+------------+----------+
9 rows in set (0.00 sec)

mysql> SELECT prod_name,prod_price,COUNT(*) AS num_prod
    -> FROM products
    -> GROUP BY prod_price;
+--------------------+------------+----------+
| prod_name          | prod_price | num_prod |
+--------------------+------------+----------+
| Fish bean bag toy  |       3.49 |        3 |
| Raggedy Ann        |       4.99 |        1 |
| 8 inch teddy bear  |       5.99 |        1 |
| 12 inch teddy bear |       8.99 |        1 |
| King doll          |       9.49 |        2 |
| 18 inch teddy bear |      11.99 |        1 |
+--------------------+------------+----------+
6 rows in set (0.00 sec)

11-使用子查询：始终记得由内向外，由特殊到一般

mysql> SELECT order_num
    -> FROM orderitems
    -> WHERE prod_id='RGAN01';
+-----------+
| order_num |
+-----------+
|     20007 |
|     20008 |
+-----------+
2 rows in set (0.04 sec)

mysql> SELECT cust_id
    -> FROM orders
    -> WHERE order_num IN (20007,20008);
+------------+
| cust_id    |
+------------+
| 1000000004 |
| 1000000005 |
+------------+
2 rows in set (0.02 sec)

mysql> SELECT cust_id
    -> FROM orders
    -> WHERE order_num IN(SELECT order_num)
    ->                  ;
+------------+
| cust_id    |
+------------+
| 1000000001 |
| 1000000001 |
| 1000000003 |
| 1000000004 |
| 1000000005 |
+------------+
5 rows in set (0.03 sec)

mysql> SELECT cust_id
    -> FROM orders
    -> WHERE order_num IN(SELECT order_num
    ->                    FROM orderitems
    ->                    WHERE prod_id='RGAN01');
+------------+
| cust_id    |
+------------+
| 1000000004 |
| 1000000005 |
+------------+
2 rows in set (0.00 sec)

mysql> SELECT COUNT(*)
    -> FROM orders
    -> WHERE cust_id='1000000001';
+----------+
| COUNT(*) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

mysql> 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 |
+---------------+------------+--------+
| Fun4All       | IN         |      1 |
| Fun4All       | AZ         |      1 |
| Kids Place    | OH         |      0 |
| The Toy Store | IL         |      1 |
| Village Toys  | MI         |      2 |
+---------------+------------+--------+
5 rows in set (0.00 sec)

mysql> 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 |
+---------------+------------+--------+
| Fun4All       | IN         |      5 |
| Fun4All       | AZ         |      5 |
| Kids Place    | OH         |      5 |
| The Toy Store | IL         |      5 |
| Village Toys  | MI         |      5 |
+---------------+------------+--------+
5 rows in set (0.00 sec)

12-联结表：注意和子查询的关系，INNER JOIN

mysql> SELECT vend_name,prod_name,prod_price
    -> FROM vendors,products
    -> WHERE vendors.vend_id=products.vend_id;
+-----------------+---------------------+------------+
| vend_name       | prod_name           | prod_price |
+-----------------+---------------------+------------+
| Bears R Us      | 8 inch teddy bear   |       5.99 |
| Bears R Us      | 12 inch teddy bear  |       8.99 |
| Bears R Us      | 18 inch teddy bear  |      11.99 |
| Doll House Inc. | Fish bean bag toy   |       3.49 |
| Doll House Inc. | Bird bean bag toy   |       3.49 |
| Doll House Inc. | Rabbit bean bag toy |       3.49 |
| Doll House Inc. | Raggedy Ann         |       4.99 |
| Fun and Games   | King doll           |       9.49 |
| Fun and Games   | Queen doll          |       9.49 |
+-----------------+---------------------+------------+
9 rows in set (0.00 sec)

mysql> SELECT vend_name,prod_name,prod_price
    -> FROM vendors,products;
+-----------------+---------------------+------------+
| vend_name       | prod_name           | prod_price |
+-----------------+---------------------+------------+
| Bear Emporium   | Fish bean bag toy   |       3.49 |
| Bears R Us      | Fish bean bag toy   |       3.49 |
| Doll House Inc. | Fish bean bag toy   |       3.49 |
| Fun and Games   | Fish bean bag toy   |       3.49 |
| Furball Inc.    | Fish bean bag toy   |       3.49 |
| Jouets et ours  | Fish bean bag toy   |       3.49 |
| Bear Emporium   | Bird bean bag toy   |       3.49 |
| Bears R Us      | Bird bean bag toy   |       3.49 |
| Doll House Inc. | Bird bean bag toy   |       3.49 |
| Fun and Games   | Bird bean bag toy   |       3.49 |
| Furball Inc.    | Bird bean bag toy   |       3.49 |
| Jouets et ours  | Bird bean bag toy   |       3.49 |
| Bear Emporium   | Rabbit bean bag toy |       3.49 |
| Bears R Us      | Rabbit bean bag toy |       3.49 |
| Doll House Inc. | Rabbit bean bag toy |       3.49 |
| Fun and Games   | Rabbit bean bag toy |       3.49 |
| Furball Inc.    | Rabbit bean bag toy |       3.49 |
| Jouets et ours  | Rabbit bean bag toy |       3.49 |
| Bear Emporium   | 8 inch teddy bear   |       5.99 |
| Bears R Us      | 8 inch teddy bear   |       5.99 |
| Doll House Inc. | 8 inch teddy bear   |       5.99 |
| Fun and Games   | 8 inch teddy bear   |       5.99 |
| Furball Inc.    | 8 inch teddy bear   |       5.99 |
| Jouets et ours  | 8 inch teddy bear   |       5.99 |
| Bear Emporium   | 12 inch teddy bear  |       8.99 |
| Bears R Us      | 12 inch teddy bear  |       8.99 |
| Doll House Inc. | 12 inch teddy bear  |       8.99 |
| Fun and Games   | 12 inch teddy bear  |       8.99 |
| Furball Inc.    | 12 inch teddy bear  |       8.99 |
| Jouets et ours  | 12 inch teddy bear  |       8.99 |
| Bear Emporium   | 18 inch teddy bear  |      11.99 |
| Bears R Us      | 18 inch teddy bear  |      11.99 |
| Doll House Inc. | 18 inch teddy bear  |      11.99 |
| Fun and Games   | 18 inch teddy bear  |      11.99 |
| Furball Inc.    | 18 inch teddy bear  |      11.99 |
| Jouets et ours  | 18 inch teddy bear  |      11.99 |
| Bear Emporium   | Raggedy Ann         |       4.99 |
| Bears R Us      | Raggedy Ann         |       4.99 |
| Doll House Inc. | Raggedy Ann         |       4.99 |
| Fun and Games   | Raggedy Ann         |       4.99 |
| Furball Inc.    | Raggedy Ann         |       4.99 |
| Jouets et ours  | Raggedy Ann         |       4.99 |
| Bear Emporium   | King doll           |       9.49 |
| Bears R Us      | King doll           |       9.49 |
| Doll House Inc. | King doll           |       9.49 |
| Fun and Games   | King doll           |       9.49 |
| Furball Inc.    | King doll           |       9.49 |
| Jouets et ours  | King doll           |       9.49 |
| Bear Emporium   | Queen doll          |       9.49 |
| Bears R Us      | Queen doll          |       9.49 |
| Doll House Inc. | Queen doll          |       9.49 |
| Fun and Games   | Queen doll          |       9.49 |
| Furball Inc.    | Queen doll          |       9.49 |
| Jouets et ours  | Queen doll          |       9.49 |
+-----------------+---------------------+------------+
54 rows in set (0.03 sec)

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 |
+-----------------+---------------------+------------+
| Bears R Us      | 8 inch teddy bear   |       5.99 |
| Bears R Us      | 12 inch teddy bear  |       8.99 |
| Bears R Us      | 18 inch teddy bear  |      11.99 |
| Doll House Inc. | Fish bean bag toy   |       3.49 |
| Doll House Inc. | Bird bean bag toy   |       3.49 |
| Doll House Inc. | Rabbit bean bag toy |       3.49 |
| Doll House Inc. | Raggedy Ann         |       4.99 |
| Fun and Games   | King doll           |       9.49 |
| Fun and Games   | Queen doll          |       9.49 |
+-----------------+---------------------+------------+
9 rows in set (0.00 sec)

mysql> SELECT cust_name,cust_contact
    -> FROM customers,orders,orderitems
    -> WHERE orders.cust_id=customers.cust_id
    ->   AND orderitems.order_num=customers.order_num
    ->   AND prod_id='RGAN01';
ERROR 1054 (42S22): Unknown column 'customers.order_num' in 'where clause'
mysql> SELECT cust_name,cust_contact
    -> FROM customers,orders,orderitems
    -> WHERE orders.cust_id=customers.cust_id
    ->   AND orderitems.order_num=orders.order_num
    ->   AND prod_id='RGAN01';
+---------------+--------------------+
| cust_name     | cust_contact       |
+---------------+--------------------+
| Fun4All       | Denise L. Stephens |
| The Toy Store | Kim Howard         |
+---------------+--------------------+
2 rows in set (0.00 sec)

mysql> SELECT cust_name,cust_contact
    -> FROM customers
    -> WHERE cust_id IN (SELECT cust_id
    ->   FROM orders
    ->   WHERE order_num IN (SELECT order_num
    ->     FROM orderitems
    ->     WHERE prod_id='RGAN01'));
+---------------+--------------------+
| cust_name     | cust_contact       |
+---------------+--------------------+
| Fun4All       | Denise L. Stephens |
| The Toy Store | Kim Howard         |
+---------------+--------------------+
2 rows in set (0.00 sec)

13-高级联结：表别名（防止歧义），自联结，INNER JOIN,LEFT/RIGHT JOIN（需要那些没有关联行的行）（PS.MySQL中好像没有FULL OUTER JOIN和*=表示）

mysql> SELECT C.cust_id,C.cust_name,COUNT(*)
    -> FROM customers AS C,orders AS O
    -> WHERE C.cust_id=O.cust_id;
+------------+--------------+----------+
| cust_id    | cust_name    | COUNT(*) |
+------------+--------------+----------+
| 1000000001 | Village Toys |        5 |
+------------+--------------+----------+
1 row in set (0.02 sec)

mysql> SELECT C.cust_id,C.cust_name,COUNT(*)
    -> FROM customers AS C,orders AS O
    -> WHERE C.cust_id=O.cust_id
    -> GROUP BY O.cust_name;
ERROR 1054 (42S22): Unknown column 'O.cust_name' in 'group statement'
mysql> SELECT C.cust_id,C.cust_name,COUNT(*)
    -> FROM customers AS C,orders AS O
    -> WHERE C.cust_id=O.cust_id
    -> GROUP BY O.cust_id;
+------------+---------------+----------+
| cust_id    | cust_name     | COUNT(*) |
+------------+---------------+----------+
| 1000000001 | Village Toys  |        2 |
| 1000000003 | Fun4All       |        1 |
| 1000000004 | Fun4All       |        1 |
| 1000000005 | The Toy Store |        1 |
+------------+---------------+----------+
4 rows in set (0.00 sec)

mysql> SELECT prod_id,prod_name,COUNT(*)
    -> FROM products;
+---------+-------------------+----------+
| prod_id | prod_name         | COUNT(*) |
+---------+-------------------+----------+
| BNBG01  | Fish bean bag toy |        9 |
+---------+-------------------+----------+
1 row in set (0.00 sec)

mysql> SELECT prod_id,prod_name,COUNT(*)
    -> FROM products
    -> GROUP BY prod_name;
+---------+---------------------+----------+
| prod_id | prod_name           | COUNT(*) |
+---------+---------------------+----------+
| BR02    | 12 inch teddy bear  |        1 |
| BR03    | 18 inch teddy bear  |        1 |
| BR01    | 8 inch teddy bear   |        1 |
| BNBG02  | Bird bean bag toy   |        1 |
| BNBG01  | Fish bean bag toy   |        1 |
| RYL01   | King doll           |        1 |
| RYL02   | Queen doll          |        1 |
| BNBG03  | Rabbit bean bag toy |        1 |
| RGAN01  | Raggedy Ann         |        1 |
+---------+---------------------+----------+
9 rows in set (0.00 sec)

mysql> SELECT P.prod_id,P.prod_name,COUNT(*)
    -> FROM products,orderitems
    -> WHERE orderitems.prod_id=products.prod_id
    -> GROUP BY prod_name
    -> ORDER BY prod_id;
ERROR 1054 (42S22): Unknown column 'P.prod_id' in 'field list'
mysql> SELECT P.prod_id,P.prod_name,COUNT(*)
    -> FROM products AS P,orderitems AS O
    -> WHERE orderitems.prod_id=products.prod_id
    -> GROUP BY prod_name
    -> ORDER BY prod_id;
ERROR 1054 (42S22): Unknown column 'orderitems.prod_id' in 'where clause'
mysql> SELECT P.prod_id,P.prod_name,COUNT(*)
    -> FROM products AS P,orderitems AS O
    -> WHERE O.prod_id=P.prod_id
    -> GROUP BY P.prod_name
    -> ORDER BY P.prod_id;
+---------+---------------------+----------+
| prod_id | prod_name           | COUNT(*) |
+---------+---------------------+----------+
| BNBG01  | Fish bean bag toy   |        3 |
| BNBG02  | Bird bean bag toy   |        3 |
| BNBG03  | Rabbit bean bag toy |        3 |
| BR01    | 8 inch teddy bear   |        2 |
| BR02    | 12 inch teddy bear  |        1 |
| BR03    | 18 inch teddy bear  |        4 |
| RGAN01  | Raggedy Ann         |        2 |
+---------+---------------------+----------+
7 rows in set (0.00 sec)

mysql> SELECT P.prod_id,P.prod_name,SUM(O.quantity)
    -> FROM products AS P,orderitems AS O
    -> WHERE O.prod_id=P.prod_id
    -> GROUP BY P.prod_name
    -> ORDER BY P.prod_id;
+---------+---------------------+-----------------+
| prod_id | prod_name           | SUM(O.quantity) |
+---------+---------------------+-----------------+
| BNBG01  | Fish bean bag toy   |             360 |
| BNBG02  | Bird bean bag toy   |             360 |
| BNBG03  | Rabbit bean bag toy |             360 |
| BR01    | 8 inch teddy bear   |             120 |
| BR02    | 12 inch teddy bear  |              10 |
| BR03    | 18 inch teddy bear  |             165 |
| RGAN01  | Raggedy Ann         |              55 |
+---------+---------------------+-----------------+
7 rows in set (0.00 sec)

mysql> SELECT C.cust_id,O.order_num
    -> FROM customers AS C,orders AS O
    -> ;
+------------+-----------+
| cust_id    | order_num |
+------------+-----------+
| 1000000001 |     20005 |
| 1000000002 |     20005 |
| 1000000003 |     20005 |
| 1000000004 |     20005 |
| 1000000005 |     20005 |
| 1000000001 |     20009 |
| 1000000002 |     20009 |
| 1000000003 |     20009 |
| 1000000004 |     20009 |
| 1000000005 |     20009 |
| 1000000001 |     20006 |
| 1000000002 |     20006 |
| 1000000003 |     20006 |
| 1000000004 |     20006 |
| 1000000005 |     20006 |
| 1000000001 |     20007 |
| 1000000002 |     20007 |
| 1000000003 |     20007 |
| 1000000004 |     20007 |
| 1000000005 |     20007 |
| 1000000001 |     20008 |
| 1000000002 |     20008 |
| 1000000003 |     20008 |
| 1000000004 |     20008 |
| 1000000005 |     20008 |
+------------+-----------+
25 rows in set (0.00 sec)

mysql> SELECT C.cust_id,O.order_num
    -> FROM customers AS C LEFT OUTER JOIN orders AS O
    ->   ON C.cust_id=O.cust_id;
+------------+-----------+
| cust_id    | order_num |
+------------+-----------+
| 1000000001 |     20005 |
| 1000000001 |     20009 |
| 1000000002 |      NULL |
| 1000000003 |     20006 |
| 1000000004 |     20007 |
| 1000000005 |     20008 |
+------------+-----------+
6 rows in set (0.00 sec)

mysql> SELECT C.cust_id,O.order_num
    -> FROM customers AS C RIGHT OUTER JOIN orders AS O
    ->   ON C.cust_id=O.cust_id;
+------------+-----------+
| cust_id    | order_num |
+------------+-----------+
| 1000000001 |     20005 |
| 1000000001 |     20009 |
| 1000000003 |     20006 |
| 1000000004 |     20007 |
| 1000000005 |     20008 |
+------------+-----------+
5 rows in set (0.00 sec)

mysql> SELECT C.cust_id,O.cust_id
    -> FROM customers AS C,orders AS O
    -> WHERE C.cust_id *= O.cust_id;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '= O.cust_id' at line 3
mysql> SELECT C.cust_id,O.order_num
    -> FROM customers AS C FULL OUTER JOIN orders AS O
    ->   ON C.cust_id=O.cust_id;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FULL OUTER JOIN orders AS O
  ON C.cust_id=O.cust_id' at line 2
mysql> SELECT C.cust_id,O.order_num
    -> FROM customers AS C LEFT OUTER JOIN orders AS O
    ->   ON C.cust_id=O.cust_id;
+------------+-----------+
| cust_id    | order_num |
+------------+-----------+
| 1000000001 |     20005 |
| 1000000001 |     20009 |
| 1000000002 |      NULL |
| 1000000003 |     20006 |
| 1000000004 |     20007 |
| 1000000005 |     20008 |
+------------+-----------+
6 rows in set (0.00 sec)

mysql>  SELECT P.prod_id,P.prod_name,SUM(O.quantity)
    ->     -> FROM products AS P,orderitems AS O
    ->     -> WHERE O.prod_id=P.prod_id
    ->     -> GROUP BY P.prod_name
    ->     -> ORDER BY P.prod_id;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '> FROM products AS P,orderitems AS O
    -> WHERE O.prod_id=P.prod_id
    -> GRO' at line 2
mysql> SELECT P.prod_id,P.prod_name,SUM(O.quantity)
    -> FROM products AS P LEFT OUTER JOIN orderitems AS O
    ->   ON O.prod_id=P.prod_id
    -> GROUP BY P.prod_name
    -> ORDER BY P.prod_id;
+---------+---------------------+-----------------+
| prod_id | prod_name           | SUM(O.quantity) |
+---------+---------------------+-----------------+
| BNBG01  | Fish bean bag toy   |             360 |
| BNBG02  | Bird bean bag toy   |             360 |
| BNBG03  | Rabbit bean bag toy |             360 |
| BR01    | 8 inch teddy bear   |             120 |
| BR02    | 12 inch teddy bear  |              10 |
| BR03    | 18 inch teddy bear  |             165 |
| RGAN01  | Raggedy Ann         |              55 |
| RYL01   | King doll           |            NULL |
| RYL02   | Queen doll          |            NULL |
+---------+---------------------+-----------------+
9 rows in set (0.00 sec)