博主不想写字并向你仍来了一堆代码
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 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)
原标题:SQL必知必会1
关键词:sql