当前位置：首页 >数据库 > SQL必知必会1-13 读书笔记

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

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

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 waings (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 waings (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 waings (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 3mysql> 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 2mysql> 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 2mysql> 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)