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

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

新老客户大回馈,云服务器低至5折

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

 

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)

 

标签:

版权申明:本站文章部分自网络,如有侵权,请联系:west999com@outlook.com
特别注意:本站所有转载文章言论不代表本站观点,本站所提供的摄影照片,插画,设计作品,如需使用,请与原作者联系,版权归原作者所有

上一篇:mysql max_allowed_packet自动重置为1024 终结解决

下一篇:mysql多实例安装脚本