星空网 > 软件开发 > 数据库

SQL必知必会1

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

 

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

sql
*特别声明:以上内容来自于网络收集,著作权属原作者所有,如有侵权,请联系我们: admin#shaoqun.com (#换成@)。
相关文章
我的浏览记录
最新相关资讯
海外公司注册 | 跨境电商服务平台 | 深圳旅行社 | 东南亚物流