正文
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 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='';
+----------+
| 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)
