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

最新推荐文章于 2024-11-02 16:18:56 发布
最新推荐文章于 2024-11-02 16:18:56 发布
阅读量175 收藏
点赞数
文章标签： 数据库
原文链接：http://www.cnblogs.com/andy1202go/p/5737144.html
版权

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

 

1-6

SQL——结构化查询语言，Structured Query Language；

基本按列查询：

mysql> SELECT prod_id,prod_name,prod_desc
    -> FROM products
    -> ORDER BY prod_id;
+---------+---------------------+-----------------------------------------------------------------------+
| prod_id | prod_name           | prod_desc                                                             |
+---------+---------------------+-----------------------------------------------------------------------+
| BNBG01  | Fish bean bag toy   | Fish bean bag toy, complete with bean bag worms with which to feed it |
| BNBG02  | Bird bean bag toy   | Bird bean bag toy, eggs are not included                              |
| BNBG03  | Rabbit bean bag toy | Rabbit bean bag toy, comes with bean bag carrots                      |
| BR01    | 8 inch teddy bear   | 8 inch teddy bear, comes with cap and jacket                          |
| BR02    | 12 inch teddy bear  | 12 inch teddy bear, comes with cap and jacket                         |
| BR03    | 18 inch teddy bear  | 18 inch teddy bear, comes with cap and jacket                         |
| RGAN01  | Raggedy Ann         | 18 inch Raggedy Ann doll                                              |
| RYL01   | King doll           | 12 inch king doll with royal garments and crown                       |
| RYL02   | Queen doll          | 12 inch queen doll with royal garments and crown                      |
+---------+---------------------+-----------------------------------------------------------------------+
mysql> SELECT prod_id,prod_name,prod_desc
    -> FROM products
    -> ORDER BY prod_price,prod_name;
+---------+---------------------+-----------------------------------------------------------------------+
| prod_id | prod_name           | prod_desc                                                             |
+---------+---------------------+-----------------------------------------------------------------------+
| BNBG02  | Bird bean bag toy   | Bird bean bag toy, eggs are not included                              |
| BNBG01  | Fish bean bag toy   | Fish bean bag toy, complete with bean bag worms with which to feed it |
| BNBG03  | Rabbit bean bag toy | Rabbit bean bag toy, comes with bean bag carrots                      |
| RGAN01  | Raggedy Ann         | 18 inch Raggedy Ann doll                                              |
| BR01    | 8 inch teddy bear   | 8 inch teddy bear, comes with cap and jacket                          |
| BR02    | 12 inch teddy bear  | 12 inch teddy bear, comes with cap and jacket                         |
| RYL01   | King doll           | 12 inch king doll with royal garments and crown                       |
| RYL02   | Queen doll          | 12 inch queen doll with royal garments and crown                      |
| BR03    | 18 inch teddy bear  | 18 inch teddy bear, comes with cap and jacket                         |
+---------+---------------------+-----------------------------------------------------------------------+
9 rows in set (0.00 sec)

mysql> SELECT prod_id,prod_name,prod_desc
    -> FROM products
    -> ORDER BY prod_price,prod_name DESC;
+---------+---------------------+-----------------------------------------------------------------------+
| prod_id | prod_name           | prod_desc                                                             |
+---------+---------------------+-----------------------------------------------------------------------+
| BNBG03  | Rabbit bean bag toy | Rabbit bean bag toy, comes with bean bag carrots                      |
| BNBG01  | Fish bean bag toy   | Fish bean bag toy, complete with bean bag worms with which to feed it |
| BNBG02  | Bird bean bag toy   | Bird bean bag toy, eggs are not included                              |
| RGAN01  | Raggedy Ann         | 18 inch Raggedy Ann doll                                              |
| BR01    | 8 inch teddy bear   | 8 inch teddy bear, comes with cap and jacket                          |
| BR02    | 12 inch teddy bear  | 12 inch teddy bear, comes with cap and jacket                         |
| RYL02   | Queen doll          | 12 inch queen doll with royal garments and crown                      |
| RYL01   | King doll           | 12 inch king doll with royal garments and crown                       |
| BR03    | 18 inch teddy bear  | 18 inch teddy bear, comes with cap and jacket                         |
+---------+---------------------+-----------------------------------------------------------------------+

 高级一点的过滤查询，WHERE,ORDER,IN,NOT,AND,OR,BETWEEN,NULL:

mysql> SELECT prod_id,prod_name,prod_desc
    -> FROM products
    -> WHERE prod_price IS NULL;
Empty set (0.00 sec)

mysql> SELECT prod_id,prod_name,prod_desc
    -> FROM products
    -> WHERE (vend_id <> 'DLL01' OR  prod_id LIKE 'BR%') AND prod_price BETWEEN 3 AND 10;
+---------+--------------------+--------------------------------------------------+
| prod_id | prod_name          | prod_desc                                        |
+---------+--------------------+--------------------------------------------------+
| BR01    | 8 inch teddy bear  | 8 inch teddy bear, comes with cap and jacket     |
| BR02    | 12 inch teddy bear | 12 inch teddy bear, comes with cap and jacket    |
| RYL01   | King doll          | 12 inch king doll with royal garments and crown  |
| RYL02   | Queen doll         | 12 inch queen doll with royal garments and crown |
+---------+--------------------+--------------------------------------------------+
4 rows in set (0.00 sec)

mysql> SELECT prod_id,prod_name,prod_desc
    -> FROM products
    -> WHERE vend_id IN ('DLL01','BRS01')
    -> ORDER BY prod_name;
+---------+---------------------+-----------------------------------------------------------------------+
| prod_id | prod_name           | prod_desc                                                             |
+---------+---------------------+-----------------------------------------------------------------------+
| BR02    | 12 inch teddy bear  | 12 inch teddy bear, comes with cap and jacket                         |
| BR03    | 18 inch teddy bear  | 18 inch teddy bear, comes with cap and jacket                         |
| BR01    | 8 inch teddy bear   | 8 inch teddy bear, comes with cap and jacket                          |
| BNBG02  | Bird bean bag toy   | Bird bean bag toy, eggs are not included                              |
| BNBG01  | Fish bean bag toy   | Fish bean bag toy, complete with bean bag worms with which to feed it |
| BNBG03  | Rabbit bean bag toy | Rabbit bean bag toy, comes with bean bag carrots                      |
| RGAN01  | Raggedy Ann         | 18 inch Raggedy Ann doll                                              |
+---------+---------------------+-----------------------------------------------------------------------+
7 rows in set (0.00 sec)

mysql> SELECT prod_id,prod_name,prod_desc
    -> FROM products
    -> WHERE vend_id NOT IN ('DLL01','BRS01')
    -> ORDER BY prod_name;
+---------+------------+--------------------------------------------------+
| prod_id | prod_name  | prod_desc                                        |
+---------+------------+--------------------------------------------------+
| RYL01   | King doll  | 12 inch king doll with royal garments and crown  |
| RYL02   | Queen doll | 12 inch queen doll with royal garments and crown |
+---------+------------+--------------------------------------------------+
2 rows in set (0.00 sec)

 

通配符，%,_,[]（实测MySQL我这里不支持。。。）

不要过分使用通配符（和*一样）

不要把通配符放在搜索的开始处

mysql> SELECT prod_id,prod_name,prod_desc
    -> FROM products
    -> WHERE prod_name LIKE '__ inch teddy bear' OR prod_name LIKE 'Fish%';
+---------+--------------------+-----------------------------------------------------------------------+
| prod_id | prod_name          | prod_desc                                                             |
+---------+--------------------+-----------------------------------------------------------------------+
| BR02    | 12 inch teddy bear | 12 inch teddy bear, comes with cap and jacket                         |
| BR03    | 18 inch teddy bear | 18 inch teddy bear, comes with cap and jacket                         |
| BNBG01  | Fish bean bag toy  | Fish bean bag toy, complete with bean bag worms with which to feed it |
+---------+--------------------+-----------------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql> SELECT *
    -> FROM customers;
+------------+---------------+----------------------+-----------+------------+----------+--------------+--------------------+-----------------------+
| cust_id    | cust_name     | cust_address         | cust_city | cust_state | cust_zip | cust_country | cust_contact       | cust_email            |
+------------+---------------+----------------------+-----------+------------+----------+--------------+--------------------+-----------------------+
| 1000000001 | Village Toys  | 200 Maple Lane       | Detroit   | MI         | 44444    | USA          | John Smith         | sales@villagetoys.com |
| 1000000002 | Kids Place    | 333 South Lake Drive | Columbus  | OH         | 43333    | USA          | Michelle Green     | NULL                  |
| 1000000003 | Fun4All       | 1 Sunny Place        | Muncie    | IN         | 42222    | USA          | Jim Jones          | jjones@fun4all.com    |
| 1000000004 | Fun4All       | 829 Riverside Drive  | Phoenix   | AZ         | 88888    | USA          | Denise L. Stephens | dstephens@fun4all.com |
| 1000000005 | The Toy Store | 4545 53rd Street     | Chicago   | IL         | 54545    | USA          | Kim Howard         | NULL                  |
+------------+---------------+----------------------+-----------+------------+----------+--------------+--------------------+-----------------------+
5 rows in set (0.00 sec)

mysql> SELECT cust_contact
    -> FROM customers
    -> WHERE cust_contact LIKE '[JM]%'
    -> ORDER BY cust_id;
Empty set (0.00 sec)

mysql> SELECT cust_contact
    -> FROM customers
    -> WHERE cust_contact LIKE '[JM]%'
    -> ORDER BY cust_contact;
Empty set (0.00 sec)

mysql> SELECT cust_contact
    -> FROM customers
    -> WHERE cust_contact LIKE '[^JM]%'
    -> ORDER BY cust_contact;
Empty set (0.00 sec)

 

 

MySQL的计算字段：CONCAT(),+-*/,AS，TRIM()

别个DB可能是+或者||，更好读？？不是很懂，反正MySQL不得行

mysql> SELECT vend_name + '('+vend_country+')'
    -> FROM vendors
    -> ORDER BY vend_name;
+----------------------------------+
| vend_name + '('+vend_country+')' |
+----------------------------------+
|                                0 |
|                                0 |
|                                0 |
|                                0 |
|                                0 |
|                                0 |
+----------------------------------+
6 rows in set, 24 warnings (0.00 sec)

mysql> SELECT vend_name + '|' + vend_country + '|'
    -> FROM vendors
    -> ORDER BY vend_name;
+--------------------------------------+
| vend_name + '|' + vend_country + '|' |
+--------------------------------------+
|                                    0 |
|                                    0 |
|                                    0 |
|                                    0 |
|                                    0 |
|                                    0 |
+--------------------------------------+
6 rows in set, 24 warnings (0.00 sec)

mysql> SELECT * FROM vendors;
+---------+-----------------+-----------------+------------+------------+----------+--------------+
| vend_id | vend_name       | vend_address    | vend_city  | vend_state | vend_zip | vend_country |
+---------+-----------------+-----------------+------------+------------+----------+--------------+
| BRS01   | Bears R Us      | 123 Main Street | Bear Town  | MI         | 44444    | USA          |
| BRE02   | Bear Emporium   | 500 Park Street | Anytown    | OH         | 44333    | USA          |
| DLL01   | Doll House Inc. | 555 High Street | Dollsville | CA         | 99999    | USA          |
| FRB01   | Furball Inc.    | 1000 5th Avenue | New York   | NY         | 11111    | USA          |
| FNG01   | Fun and Games   | 42 Galaxy Road  | London     | NULL       | N16 6PS  | England      |
| JTS01   | Jouets et ours  | 1 Rue Amusement | Paris      | NULL       | 45678    | France       |
+---------+-----------------+-----------------+------------+------------+----------+--------------+
6 rows in set (0.00 sec)

mysql> SELECT vend_name || ' ( '||vend_country || ' )  '
    -> FROM vendors
    -> ORDER BY vend_name;
+--------------------------------------------+
| vend_name || ' ( '||vend_country || ' )  ' |
+--------------------------------------------+
|                                          0 |
|                                          0 |
|                                          0 |
|                                          0 |
|                                          0 |
|                                          0 |
+--------------------------------------------+
6 rows in set, 24 warnings (0.00 sec)

mysql> SELECT CONCAT(vend_name,vend_country)
    -> FROM vendors
    -> ORDER BY vend_name;
+--------------------------------+
| CONCAT(vend_name,vend_country) |
+--------------------------------+
| Bear EmporiumUSA               |
| Bears R UsUSA                  |
| Doll House Inc.USA             |
| Fun and GamesEngland           |
| Furball Inc.USA                |
| Jouets et oursFrance           |
+--------------------------------+
6 rows in set (0.00 sec)

mysql> SELECT CONCAT(vend_name,'(',vend_country,')')
    -> FROM vendors
    -> ORDER BY vend_name;
+----------------------------------------+
| CONCAT(vend_name,'(',vend_country,')') |
+----------------------------------------+
| Bear Emporium(USA)                     |
| Bears R Us(USA)                        |
| Doll House Inc.(USA)                   |
| Fun and Games(England)                 |
| Furball Inc.(USA)                      |
| Jouets et ours(France)                 |
+----------------------------------------+
6 rows in set (0.00 sec)

mysql> SELECT vend_name,
    ->        CONCAT(vend_address,',',vend_city,',',vend_state,',',vend_country) AS VendorsInfo,
    ->        vend_zip
    -> FROM vendors
    -> ORDER BY vend_zip,vend_name DESC;
+-----------------+-----------------------------------+----------+
| vend_name       | VendorsInfo                       | vend_zip |
+-----------------+-----------------------------------+----------+
| Furball Inc.    | 1000 5th Avenue,New York,NY,USA   | 11111    |
| Bear Emporium   | 500 Park Street,Anytown,OH,USA    | 44333    |
| Bears R Us      | 123 Main Street,Bear Town,MI,USA  | 44444    |
| Jouets et ours  | NULL                              | 45678    |
| Doll House Inc. | 555 High Street,Dollsville,CA,USA | 99999    |
| Fun and Games   | NULL                              | N16 6PS  |
+-----------------+-----------------------------------+----------+
6 rows in set (0.00 sec)

mysql> SELECT prod_id,quantity,item_price,
    ->       quantity*item_price AS sum_price
    -> FROM orderitems
    -> WHERE order_num=20008;
+---------+----------+------------+-----------+
| prod_id | quantity | item_price | sum_price |
+---------+----------+------------+-----------+
| RGAN01  |        5 |       4.99 |     24.95 |
| BR03    |        5 |      11.99 |     59.95 |
| BNBG01  |       10 |       3.49 |     34.90 |
| BNBG02  |       10 |       3.49 |     34.90 |
| BNBG03  |       10 |       3.49 |     34.90 |
+---------+----------+------------+-----------+
5 rows in set (0.00 sec)

 

使用数据处理函数：CURDATE(),YEAR(),UPPER(),TRIM(),ABS()

不同的DBMS的函数不是很一样，所以，使用函数的SQL代码移植性不好，如果一定要用，记得写清楚注释：

mysql> SELECT CONCAT(prod_name,'+',CURDATE())
    -> FROM products;
+---------------------------------+
| CONCAT(prod_name,'+',CURDATE()) |
+---------------------------------+
| 8 inch teddy bear+2016-08-04    |
| 12 inch teddy bear+2016-08-04   |
| 18 inch teddy bear+2016-08-04   |
| Fish bean bag toy+2016-08-04    |
| Bird bean bag toy+2016-08-04    |
| Rabbit bean bag toy+2016-08-04  |
| Raggedy Ann+2016-08-04          |
| King doll+2016-08-04            |
| Queen doll+2016-08-04           |
+---------------------------------+
9 rows in set (0.00 sec)

mysql> SELECT order_num
    -> FROM orders
    -> WHERE YEAR(order_date)=2004;
+-----------+
| order_num |
+-----------+
|     20005 |
|     20006 |
|     20007 |
|     20008 |
|     20009 |
+-----------+
5 rows in set (0.00 sec)

mysql> SELECT UPPER(price_name) AS Name,prod_price
    -> FROM products
    -> ORDER BY prod_name;
ERROR 1054 (42S22): Unknown column 'price_name' in 'field list'
mysql> SELECT UPPER(prod_name) AS Name,prod_price
    -> FROM products
    -> ORDER BY prod_name;
+---------------------+------------+
| Name                | prod_price |
+---------------------+------------+
| 12 INCH TEDDY BEAR  |       8.99 |
| 18 INCH TEDDY BEAR  |      11.99 |
| 8 INCH TEDDY BEAR   |       5.99 |
| BIRD BEAN BAG TOY   |       3.49 |
| FISH BEAN BAG TOY   |       3.49 |
| KING DOLL           |       9.49 |
| QUEEN DOLL          |       9.49 |
| RABBIT BEAN BAG TOY |       3.49 |
| RAGGEDY ANN         |       4.99 |
+---------------------+------------+
9 rows in set (0.00 sec)

 

 

聚集函数：COUNT(),AVG(),MAX(),MIN(),SUM():

mysql> SELECT UPPER(prod_name) AS P_Name,AVG(prod_price) AS P_Price
    -> FROM products
    -> WHERE prod_price BETWEEN 2 AND 10;
+-------------------+----------+
| P_Name            | P_Price  |
+-------------------+----------+
| 8 INCH TEDDY BEAR | 6.177500 |
+-------------------+----------+
1 row in set (0.00 sec)

mysql> SELECT UPPER(prod_name) AS P_Name,AVG(prod_price) AS P_Price
    -> FROM products;
+-------------------+----------+
| P_Name            | P_Price  |
+-------------------+----------+
| 8 INCH TEDDY BEAR | 6.823333 |
+-------------------+----------+
1 row in set (0.00 sec)

mysql> SELECT AVG(prod_price) AS P_Price
    -> FROM products;
+----------+
| P_Price  |
+----------+
| 6.823333 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) AS num_cust
    -> FROM customers;
+----------+
| num_cust |
+----------+
|        5 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) AS Items,
    ->        AVG(DISTINCT prod_price) AS price_avg,
    ->        MAX(prod_price) AS price_max,
    ->        MIN(prod_price) AS price_min
    -> FROM products;
+-------+-----------+-----------+-----------+
| Items | price_avg | price_max | price_min |
+-------+-----------+-----------+-----------+
|     9 |  7.490000 |     11.99 |      3.49 |
+-------+-----------+-----------+-----------+
1 row in set (0.00 sec)

 

 

分组：GROUP BY,HAVING;注意各种语句的顺序问题SELECT->FROM->WHERE->GROUP BY->HAVING->ORDER BY

 mysql> SELECT prod_name,COUNT(*) AS num_prod 
-> FROM products
    -> GROUP BY vend_id;
+-------------------+----------+
| prod_name         | num_prod |
+-------------------+----------+
| 8 inch teddy bear |        3 |
| Fish bean bag toy |        4 |
| King doll         |        2 |
+-------------------+----------+
3 rows in set (0.00 sec)

mysql> SELECT prod_price,COUNT(*) AS num_price
    -> FROM products
    -> GROUP BY prod_price;
+------------+-----------+
| prod_price | num_price |
+------------+-----------+
|       3.49 |         3 |
|       4.99 |         1 |
|       5.99 |         1 |
|       8.99 |         1 |
|       9.49 |         2 |
|      11.99 |         1 |
+------------+-----------+
6 rows in set (0.00 sec)

mysql> SELECT prod_price,COUNT(*) AS num_price
    -> FROM products
    -> GROUP BY prod_price
    -> HAVING COUNT(*)>=2
    -> ORDER BY prod_price DESC;
+------------+-----------+
| prod_price | num_price |
+------------+-----------+
|       9.49 |         2 |
|       3.49 |         3 |
+------------+-----------+
2 rows in set (0.00 sec)

mysql> SELECT prod_name,COUNT(*) AS num_prod
    -> FROM products
    -> WHERE prod_price>=3
    -> GROUP BY prod_name
    -> HAVING COUNT(*)>=2
    -> ORDER BY prod_price DESC;
Empty set (0.00 sec)

mysql> SELECT prod_name,prod_price,COUNT(*) AS num_prod
    -> FROM products
    -> GROUP BY prod_name;
+---------------------+------------+----------+
| prod_name           | prod_price | num_prod |
+---------------------+------------+----------+
| 12 inch teddy bear  |       8.99 |        1 |
| 18 inch teddy bear  |      11.99 |        1 |
| 8 inch teddy bear   |       5.99 |        1 |
| Bird bean bag toy   |       3.49 |        1 |
| Fish bean bag toy   |       3.49 |        1 |
| King doll           |       9.49 |        1 |
| Queen doll          |       9.49 |        1 |
| Rabbit bean bag toy |       3.49 |        1 |
| Raggedy Ann         |       4.99 |        1 |
+---------------------+------------+----------+
9 rows in set (0.00 sec)

mysql> SELECT prod_name,prod_price,COUNT(*) AS num_prod
    -> FROM products
    -> GROUP BY prod_price;
+--------------------+------------+----------+
| prod_name          | prod_price | num_prod |
+--------------------+------------+----------+
| Fish bean bag toy  |       3.49 |        3 |
| Raggedy Ann        |       4.99 |        1 |
| 8 inch teddy bear  |       5.99 |        1 |
| 12 inch teddy bear |       8.99 |        1 |
| King doll          |       9.49 |        2 |
| 18 inch teddy bear |      11.99 |        1 |
+--------------------+------------+----------+
6 rows in set (0.00 sec)

 

11-使用子查询：始终记得由内向外，由特殊到一般

mysql> SELECT order_num
    -> FROM orderitems
    -> WHERE prod_id='RGAN01';
+-----------+
| order_num |
+-----------+
|     20007 |
|     20008 |
+-----------+
2 rows in set (0.04 sec)

mysql> SELECT cust_id
    -> FROM orders
    -> WHERE order_num IN (20007,20008);
+------------+
| cust_id    |
+------------+
| 1000000004 |
| 1000000005 |
+------------+
2 rows in set (0.02 sec)

mysql> SELECT cust_id
    -> FROM orders
    -> WHERE order_num IN(SELECT order_num)
    ->                  ;
+------------+
| cust_id    |
+------------+
| 1000000001 |
| 1000000001 |
| 1000000003 |
| 1000000004 |
| 1000000005 |
+------------+
5 rows in set (0.03 sec)

mysql> SELECT cust_id
    -> FROM orders
    -> WHERE order_num IN(SELECT order_num
    ->                    FROM orderitems
    ->                    WHERE prod_id='RGAN01');
+------------+
| cust_id    |
+------------+
| 1000000004 |
| 1000000005 |
+------------+
2 rows in set (0.00 sec)

mysql> SELECT COUNT(*)
    -> FROM orders
    -> WHERE cust_id='1000000001';
+----------+
| COUNT(*) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT cust_name,cust_state,
    ->       (SELECT COUNT(*)
    ->        FROM orders
    ->        WHERE orders.cust_id=customers.cust_id) AS orders
    -> FROM customers
    -> ORDER BY cust_name;
+---------------+------------+--------+
| cust_name     | cust_state | orders |
+---------------+------------+--------+
| Fun4All       | IN         |      1 |
| Fun4All       | AZ         |      1 |
| Kids Place    | OH         |      0 |
| The Toy Store | IL         |      1 |
| Village Toys  | MI         |      2 |
+---------------+------------+--------+
5 rows in set (0.00 sec)

mysql> SELECT cust_name,cust_state,
    ->       (SELECT COUNT(*)
    ->        FROM orders
    ->        WHERE cust_id=cust_id) AS orders
    -> FROM customers
    -> ORDER BY cust_name;
+---------------+------------+--------+
| cust_name     | cust_state | orders |
+---------------+------------+--------+
| Fun4All       | IN         |      5 |
| Fun4All       | AZ         |      5 |
| Kids Place    | OH         |      5 |
| The Toy Store | IL         |      5 |
| Village Toys  | MI         |      5 |
+---------------+------------+--------+
5 rows in set (0.00 sec)

 

 

12-联结表：注意和子查询的关系，INNER JOIN

mysql> SELECT vend_name,prod_name,prod_price
    -> FROM vendors,products
    -> WHERE vendors.vend_id=products.vend_id;
+-----------------+---------------------+------------+
| vend_name       | prod_name           | prod_price |
+-----------------+---------------------+------------+
| Bears R Us      | 8 inch teddy bear   |       5.99 |
| Bears R Us      | 12 inch teddy bear  |       8.99 |
| Bears R Us      | 18 inch teddy bear  |      11.99 |
| Doll House Inc. | Fish bean bag toy   |       3.49 |
| Doll House Inc. | Bird bean bag toy   |       3.49 |
| Doll House Inc. | Rabbit bean bag toy |       3.49 |
| Doll House Inc. | Raggedy Ann         |       4.99 |
| Fun and Games   | King doll           |       9.49 |
| Fun and Games   | Queen doll          |       9.49 |
+-----------------+---------------------+------------+
9 rows in set (0.00 sec)

mysql> SELECT vend_name,prod_name,prod_price
    -> FROM vendors,products;
+-----------------+---------------------+------------+
| vend_name       | prod_name           | prod_price |
+-----------------+---------------------+------------+
| Bear Emporium   | Fish bean bag toy   |       3.49 |
| Bears R Us      | Fish bean bag toy   |       3.49 |
| Doll House Inc. | Fish bean bag toy   |       3.49 |
| Fun and Games   | Fish bean bag toy   |       3.49 |
| Furball Inc.    | Fish bean bag toy   |       3.49 |
| Jouets et ours  | Fish bean bag toy   |       3.49 |
| Bear Emporium   | Bird bean bag toy   |       3.49 |
| Bears R Us      | Bird bean bag toy   |       3.49 |
| Doll House Inc. | Bird bean bag toy   |       3.49 |
| Fun and Games   | Bird bean bag toy   |       3.49 |
| Furball Inc.    | Bird bean bag toy   |       3.49 |
| Jouets et ours  | Bird bean bag toy   |       3.49 |
| Bear Emporium   | Rabbit bean bag toy |       3.49 |
| Bears R Us      | Rabbit bean bag toy |       3.49 |
| Doll House Inc. | Rabbit bean bag toy |       3.49 |
| Fun and Games   | Rabbit bean bag toy |       3.49 |
| Furball Inc.    | Rabbit bean bag toy |       3.49 |
| Jouets et ours  | Rabbit bean bag toy |       3.49 |
| Bear Emporium   | 8 inch teddy bear   |       5.99 |
| Bears R Us      | 8 inch teddy bear   |       5.99 |
| Doll House Inc. | 8 inch teddy bear   |       5.99 |
| Fun and Games   | 8 inch teddy bear   |       5.99 |
| Furball Inc.    | 8 inch teddy bear   |       5.99 |
| Jouets et ours  | 8 inch teddy bear   |       5.99 |
| Bear Emporium   | 12 inch teddy bear  |       8.99 |
| Bears R Us      | 12 inch teddy bear  |       8.99 |
| Doll House Inc. | 12 inch teddy bear  |       8.99 |
| Fun and Games   | 12 inch teddy bear  |       8.99 |
| Furball Inc.    | 12 inch teddy bear  |       8.99 |
| Jouets et ours  | 12 inch teddy bear  |       8.99 |
| Bear Emporium   | 18 inch teddy bear  |      11.99 |
| Bears R Us      | 18 inch teddy bear  |      11.99 |
| Doll House Inc. | 18 inch teddy bear  |      11.99 |
| Fun and Games   | 18 inch teddy bear  |      11.99 |
| Furball Inc.    | 18 inch teddy bear  |      11.99 |
| Jouets et ours  | 18 inch teddy bear  |      11.99 |
| Bear Emporium   | Raggedy Ann         |       4.99 |
| Bears R Us      | Raggedy Ann         |       4.99 |
| Doll House Inc. | Raggedy Ann         |       4.99 |
| Fun and Games   | Raggedy Ann         |       4.99 |
| Furball Inc.    | Raggedy Ann         |       4.99 |
| Jouets et ours  | Raggedy Ann         |       4.99 |
| Bear Emporium   | King doll           |       9.49 |
| Bears R Us      | King doll           |       9.49 |
| Doll House Inc. | King doll           |       9.49 |
| Fun and Games   | King doll           |       9.49 |
| Furball Inc.    | King doll           |       9.49 |
| Jouets et ours  | King doll           |       9.49 |
| Bear Emporium   | Queen doll          |       9.49 |
| Bears R Us      | Queen doll          |       9.49 |
| Doll House Inc. | Queen doll          |       9.49 |
| Fun and Games   | Queen doll          |       9.49 |
| Furball Inc.    | Queen doll          |       9.49 |
| Jouets et ours  | Queen doll          |       9.49 |
+-----------------+---------------------+------------+
54 rows in set (0.03 sec)

mysql> SELECT vend_name,prod_name,prod_price
    -> FROM vendors INNER JOIN products
    ->   ON vendors.vend_id=products.vend_id;
+-----------------+---------------------+------------+
| vend_name       | prod_name           | prod_price |
+-----------------+---------------------+------------+
| Bears R Us      | 8 inch teddy bear   |       5.99 |
| Bears R Us      | 12 inch teddy bear  |       8.99 |
| Bears R Us      | 18 inch teddy bear  |      11.99 |
| Doll House Inc. | Fish bean bag toy   |       3.49 |
| Doll House Inc. | Bird bean bag toy   |       3.49 |
| Doll House Inc. | Rabbit bean bag toy |       3.49 |
| Doll House Inc. | Raggedy Ann         |       4.99 |
| Fun and Games   | King doll           |       9.49 |
| Fun and Games   | Queen doll          |       9.49 |
+-----------------+---------------------+------------+
9 rows in set (0.00 sec)

mysql> SELECT cust_name,cust_contact
    -> FROM customers,orders,orderitems
    -> WHERE orders.cust_id=customers.cust_id
    ->   AND orderitems.order_num=customers.order_num
    ->   AND prod_id='RGAN01';
ERROR 1054 (42S22): Unknown column 'customers.order_num' in 'where clause'
mysql> SELECT cust_name,cust_contact
    -> FROM customers,orders,orderitems
    -> WHERE orders.cust_id=customers.cust_id
    ->   AND orderitems.order_num=orders.order_num
    ->   AND prod_id='RGAN01';
+---------------+--------------------+
| cust_name     | cust_contact       |
+---------------+--------------------+
| Fun4All       | Denise L. Stephens |
| The Toy Store | Kim Howard         |
+---------------+--------------------+
2 rows in set (0.00 sec)

mysql> SELECT cust_name,cust_contact
    -> FROM customers
    -> WHERE cust_id IN (SELECT cust_id
    ->   FROM orders
    ->   WHERE order_num IN (SELECT order_num
    ->     FROM orderitems
    ->     WHERE prod_id='RGAN01'));
+---------------+--------------------+
| cust_name     | cust_contact       |
+---------------+--------------------+
| Fun4All       | Denise L. Stephens |
| The Toy Store | Kim Howard         |
+---------------+--------------------+
2 rows in set (0.00 sec)

 

 

13-高级联结：表别名（防止歧义），自联结，INNER JOIN,LEFT/RIGHT JOIN（需要那些没有关联行的行）（PS.MySQL中好像没有FULL OUTER JOIN和*=表示）

mysql> SELECT C.cust_id,C.cust_name,COUNT(*)
    -> FROM customers AS C,orders AS O
    -> WHERE C.cust_id=O.cust_id;
+------------+--------------+----------+
| cust_id    | cust_name    | COUNT(*) |
+------------+--------------+----------+
| 1000000001 | Village Toys |        5 |
+------------+--------------+----------+
1 row in set (0.02 sec)

mysql> SELECT C.cust_id,C.cust_name,COUNT(*)
    -> FROM customers AS C,orders AS O
    -> WHERE C.cust_id=O.cust_id
    -> GROUP BY O.cust_name;
ERROR 1054 (42S22): Unknown column 'O.cust_name' in 'group statement'
mysql> SELECT C.cust_id,C.cust_name,COUNT(*)
    -> FROM customers AS C,orders AS O
    -> WHERE C.cust_id=O.cust_id
    -> GROUP BY O.cust_id;
+------------+---------------+----------+
| cust_id    | cust_name     | COUNT(*) |
+------------+---------------+----------+
| 1000000001 | Village Toys  |        2 |
| 1000000003 | Fun4All       |        1 |
| 1000000004 | Fun4All       |        1 |
| 1000000005 | The Toy Store |        1 |
+------------+---------------+----------+
4 rows in set (0.00 sec)

mysql> SELECT prod_id,prod_name,COUNT(*)
    -> FROM products;
+---------+-------------------+----------+
| prod_id | prod_name         | COUNT(*) |
+---------+-------------------+----------+
| BNBG01  | Fish bean bag toy |        9 |
+---------+-------------------+----------+
1 row in set (0.00 sec)

mysql> SELECT prod_id,prod_name,COUNT(*)
    -> FROM products
    -> GROUP BY prod_name;
+---------+---------------------+----------+
| prod_id | prod_name           | COUNT(*) |
+---------+---------------------+----------+
| BR02    | 12 inch teddy bear  |        1 |
| BR03    | 18 inch teddy bear  |        1 |
| BR01    | 8 inch teddy bear   |        1 |
| BNBG02  | Bird bean bag toy   |        1 |
| BNBG01  | Fish bean bag toy   |        1 |
| RYL01   | King doll           |        1 |
| RYL02   | Queen doll          |        1 |
| BNBG03  | Rabbit bean bag toy |        1 |
| RGAN01  | Raggedy Ann         |        1 |
+---------+---------------------+----------+
9 rows in set (0.00 sec)

mysql> SELECT P.prod_id,P.prod_name,COUNT(*)
    -> FROM products,orderitems
    -> WHERE orderitems.prod_id=products.prod_id
    -> GROUP BY prod_name
    -> ORDER BY prod_id;
ERROR 1054 (42S22): Unknown column 'P.prod_id' in 'field list'
mysql> SELECT P.prod_id,P.prod_name,COUNT(*)
    -> FROM products AS P,orderitems AS O
    -> WHERE orderitems.prod_id=products.prod_id
    -> GROUP BY prod_name
    -> ORDER BY prod_id;
ERROR 1054 (42S22): Unknown column 'orderitems.prod_id' in 'where clause'
mysql> SELECT P.prod_id,P.prod_name,COUNT(*)
    -> FROM products AS P,orderitems AS O
    -> WHERE O.prod_id=P.prod_id
    -> GROUP BY P.prod_name
    -> ORDER BY P.prod_id;
+---------+---------------------+----------+
| prod_id | prod_name           | COUNT(*) |
+---------+---------------------+----------+
| BNBG01  | Fish bean bag toy   |        3 |
| BNBG02  | Bird bean bag toy   |        3 |
| BNBG03  | Rabbit bean bag toy |        3 |
| BR01    | 8 inch teddy bear   |        2 |
| BR02    | 12 inch teddy bear  |        1 |
| BR03    | 18 inch teddy bear  |        4 |
| RGAN01  | Raggedy Ann         |        2 |
+---------+---------------------+----------+
7 rows in set (0.00 sec)

mysql> SELECT P.prod_id,P.prod_name,SUM(O.quantity)
    -> FROM products AS P,orderitems AS O
    -> WHERE O.prod_id=P.prod_id
    -> GROUP BY P.prod_name
    -> ORDER BY P.prod_id;
+---------+---------------------+-----------------+
| prod_id | prod_name           | SUM(O.quantity) |
+---------+---------------------+-----------------+
| BNBG01  | Fish bean bag toy   |             360 |
| BNBG02  | Bird bean bag toy   |             360 |
| BNBG03  | Rabbit bean bag toy |             360 |
| BR01    | 8 inch teddy bear   |             120 |
| BR02    | 12 inch teddy bear  |              10 |
| BR03    | 18 inch teddy bear  |             165 |
| RGAN01  | Raggedy Ann         |              55 |
+---------+---------------------+-----------------+
7 rows in set (0.00 sec)

mysql> SELECT C.cust_id,O.order_num
    -> FROM customers AS C,orders AS O
    -> ;
+------------+-----------+
| cust_id    | order_num |
+------------+-----------+
| 1000000001 |     20005 |
| 1000000002 |     20005 |
| 1000000003 |     20005 |
| 1000000004 |     20005 |
| 1000000005 |     20005 |
| 1000000001 |     20009 |
| 1000000002 |     20009 |
| 1000000003 |     20009 |
| 1000000004 |     20009 |
| 1000000005 |     20009 |
| 1000000001 |     20006 |
| 1000000002 |     20006 |
| 1000000003 |     20006 |
| 1000000004 |     20006 |
| 1000000005 |     20006 |
| 1000000001 |     20007 |
| 1000000002 |     20007 |
| 1000000003 |     20007 |
| 1000000004 |     20007 |
| 1000000005 |     20007 |
| 1000000001 |     20008 |
| 1000000002 |     20008 |
| 1000000003 |     20008 |
| 1000000004 |     20008 |
| 1000000005 |     20008 |
+------------+-----------+
25 rows in set (0.00 sec)

mysql> SELECT C.cust_id,O.order_num
    -> FROM customers AS C LEFT OUTER JOIN orders AS O
    ->   ON C.cust_id=O.cust_id;
+------------+-----------+
| cust_id    | order_num |
+------------+-----------+
| 1000000001 |     20005 |
| 1000000001 |     20009 |
| 1000000002 |      NULL |
| 1000000003 |     20006 |
| 1000000004 |     20007 |
| 1000000005 |     20008 |
+------------+-----------+
6 rows in set (0.00 sec)

mysql> SELECT C.cust_id,O.order_num
    -> FROM customers AS C RIGHT OUTER JOIN orders AS O
    ->   ON C.cust_id=O.cust_id;
+------------+-----------+
| cust_id    | order_num |
+------------+-----------+
| 1000000001 |     20005 |
| 1000000001 |     20009 |
| 1000000003 |     20006 |
| 1000000004 |     20007 |
| 1000000005 |     20008 |
+------------+-----------+
5 rows in set (0.00 sec)

mysql> SELECT C.cust_id,O.cust_id
    -> FROM customers AS C,orders AS O
    -> WHERE C.cust_id *= O.cust_id;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '= O.cust_id' at line 3
mysql> SELECT C.cust_id,O.order_num
    -> FROM customers AS C FULL OUTER JOIN orders AS O
    ->   ON C.cust_id=O.cust_id;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FULL OUTER JOIN orders AS O
  ON C.cust_id=O.cust_id' at line 2
mysql> SELECT C.cust_id,O.order_num
    -> FROM customers AS C LEFT OUTER JOIN orders AS O
    ->   ON C.cust_id=O.cust_id;
+------------+-----------+
| cust_id    | order_num |
+------------+-----------+
| 1000000001 |     20005 |
| 1000000001 |     20009 |
| 1000000002 |      NULL |
| 1000000003 |     20006 |
| 1000000004 |     20007 |
| 1000000005 |     20008 |
+------------+-----------+
6 rows in set (0.00 sec)

mysql>  SELECT P.prod_id,P.prod_name,SUM(O.quantity)
    ->     -> FROM products AS P,orderitems AS O
    ->     -> WHERE O.prod_id=P.prod_id
    ->     -> GROUP BY P.prod_name
    ->     -> ORDER BY P.prod_id;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '> FROM products AS P,orderitems AS O
    -> WHERE O.prod_id=P.prod_id
    -> GRO' at line 2
mysql> SELECT P.prod_id,P.prod_name,SUM(O.quantity)
    -> FROM products AS P LEFT OUTER JOIN orderitems AS O
    ->   ON O.prod_id=P.prod_id
    -> GROUP BY P.prod_name
    -> ORDER BY P.prod_id;
+---------+---------------------+-----------------+
| prod_id | prod_name           | SUM(O.quantity) |
+---------+---------------------+-----------------+
| BNBG01  | Fish bean bag toy   |             360 |
| BNBG02  | Bird bean bag toy   |             360 |
| BNBG03  | Rabbit bean bag toy |             360 |
| BR01    | 8 inch teddy bear   |             120 |
| BR02    | 12 inch teddy bear  |              10 |
| BR03    | 18 inch teddy bear  |             165 |
| RGAN01  | Raggedy Ann         |              55 |
| RYL01   | King doll           |            NULL |
| RYL02   | Queen doll          |            NULL |
+---------+---------------------+-----------------+
9 rows in set (0.00 sec)

 

转载于:https://www.cnblogs.com/andy1202go/p/5737144.html

weixin_30640291
关注
mysql学习笔记-SELECT基础语句
sinat_41395907的博客
03-05 298
查询单个列 语句：SELECT prod_name FROM Products; 输出结果（结果是无序的）： prod_name Fish bean bag toy Bird bean bag toy Rabbit bean bag toy 8 inch teddy bear 12 inch teddy bear 18 inch teddy bear Raggedy Ann King doll Queen doll 上述语句用SELECT语句从Products表中检索一个名为prod_name的列。所要查询
mysql必知必会样例表_《SQL必知必会》笔记1-样例表说明及建表语句
weixin_42467457的博客
02-18 1248
该笔记整理自Ben Forta著，钟鸣、刘晓霞译，人民邮电出版社出版的《SQL必知必会》第4版，具体内容有部分省略。关于存储过程的部分内容，结合了《SQL必知必会》和《MySQL必知必会》。代码测试环境：Win7-64bit、MySQL 5.7、MySQL Workbench 6.3。1 样例表解释说明书中所用的表是一个玩具经销商使用的订单录入系统的组成部分，包括：管理供应商Vendors。管理...
工作中用到的sql汇总：
凝眸伏笔的博客
07-24 3999
学习资源来自：《Mysql必知必会》该书简单明了，适合我这种大白，像是小学老师授课一样。在做学习笔记的时候，结合本身专业，将其对应扩展，便于迁移到实际应用中，将固定的知识同实践相结合去理解各个知识点。 最前面是工作中较为常用的语句，后面是看书总结的语法知识。 一、LIKE 使用通配符% 来匹配city字段中包含的字母。 SELECT * FROM Persons WHERE City L...
Laravel 4性能调试–调试SQL查询
张永新的BLOG
11-09 1373
Laravel 3 had a cool profiler bundle called Anbu, written by Daylee Rees. It was written as a bundle but Taylor, creator of Laravel, liked it so it was eventually included in the Laravel core. Larav
laravel 技巧
weixin_30578677的博客
08-29 382
Eloquent 1.Automatic model validation class Post extends Eloquent { public static $autoValidate = true; protected static $rules = array(); protected static function boot() { ...
Day-2：检索数据
weixin_30795127的博客
03-15 96
1、SELECT语句：使用SELECT检索数据，必须至少给出两条信息-想选择什么，以及从什么地方选择。 　　注：关键字（keyword）:作为SQL组成部分的保留字，关键字不能用作表或者列的名字。 输入：------------------SELECT prod_name FROM Products; 输出: prod_name ---------------------- Fi...
SQL第2课__LIMIT和OFFSET
茨时代べ 的博客
03-23 403
SELECT prod_name FROM products; Fish bean bag toy Bird bean bag toy Rabbit bean bag toy 8 inch teddy bear 12 inch teddy bear 18 inch teddy bear Raggedy Ann King doll Queen doll SELECT prod_name FROM...
SQL快速入门 | 春松客服
Chatopera
04-27 1201
目录1.SQL简介SQL介绍SQL组件SQL历史MySQL历史前提准备2.SQL基本语法3.延伸阅读开源智能客服系统 1.SQL简介 SQL介绍 Structured Query Language简称SQL，是一种操作数据库的语言。它包括数据库创建，删除，获取行，修改行等。SQL是结构化查询语言，它是一种用于存储，处理和检索关系数据库中存储的数据的计算机语言。 SQL是关系数据库系统的标准语言。所...
以下php哪个标记错误,php – 标记聚合数据索引进程未知错误
weixin_42302638的博客
04-01 184
我似乎在Magento上有索引问题.所有可用的索引索引都很好,除了一个：Tag Aggregation Data通过以下错误编制索引时,我无法完全看出问题所在.Tag Aggregation Data index process unknown error:exception 'PDOException' with message 'SQLSTATE[42S22]: Column not foun...
oceanbase V4.2.2社区版集群离线部署
king_harry的专栏
11-01 656
版本，选择是上传文件，包含oceanbase-ce、oceanbase-ce-libs、oceanbase-ce-utils。关闭时展示，为 OBProxy 集群的访问地址，仅用于生成租户的连接串，不影响实际使用，需要自主配置负载均衡，如果是 VIP 地址，还需要您自主申请并绑定到 OBProxy Server。集群配置选项卡—更多配置—更改系统内存保留选项，默认是30G，如果测试机内存只有16G，则需要调小，本测试调整为2G，这样ocp-server安装不会失败。自定义待管理的集群的名称。
基于vue框架的的汇生活家居商城的设计与实现bdjlq（程序+源码+数据库+调试部署+开发环境）系统界面在最后面。
h2345678的博客
10-29 1348
项目功能：商品分类,商品信息,用户。
Redis实战-利用Lua解决批量插入防重方案
葵续浅笑的博客
11-01 936
本文介绍了以Lua脚本为基础设计的防重和幂等方案。
JAVA 插入 JSON 对象到 PostgreSQL
qq_52143611的博客
10-30 828
在现代软件开发中，由于 JSON 数据的轻量和通用性，处理 JSON 数据已经变得无处不在。PostgreSQL凭借其对 JSON 的强大支持，为存储和查询 JSON 数据提供了出色的平台。为了将JSON数据保存到PostgreSQL数据库中，我们可以将JSON数据转换为PostgreSQL的JSONB类型数据，然后将其存储在数据库中。在Java中，我们可以使用JDBC或者基于ORM框架的方式来实现这一功能。
JAVA中的MVC三层架构
楠寻寻的博客
10-30 999
MVC（Model-View-Controller）是一种软件设计模式，用于将应用程序分为主要的部分：模型（Model）、视图（View）和控制器（Controller）。这种架构有助于提高代码的可维护性和可扩展性。Spring框架通过@Controller@Service和注解清晰地划分了MVC三层架构的各个层次，使得开发人员能够更高效地构建和维护复杂的Web应用。POJO类：用于表示业务实体，通常包含属性和相关的getter/setter方法。DAO类：用于封装对数据库的操作，提供数据访问的接口。
第2章 Android App开发基础
L念安dd的博客
10-29 1079
本章介绍基于Android系统的App开发常识，包括以下几个方面：App开发与其他软件开发有什么不一 样，App工程是怎样的组织结构又是怎样配置的，App开发的前后端分离设计是如何运作实现的，App的活动页面是如何创建又是如何跳转的。
真·香！深度体验 zCloud 数据库云管平台 -- DBA日常管理篇
weixin_54551388的博客
11-01 372
点击蓝字 关注我们zCloud 作为一款业界领先的数据库云管平台，通过云化自治的部署能力、智能巡检和诊断能力、知识即代码的沉淀能力，为DBA的日常管理工作带来了革新式的简化与优化。经过一周的深度体验，今天笔者与您深入探讨 zCloud 在数据库用户、参数以及日志管理方面的具体应用，看它如何为DBA的日常管理工作带来价值。数据库用户管理“常在河边走哪有不湿鞋”，作为DBA或多或少都曾遇到过因权限配置...
数据库 二
最新发布
wjj20040809的博客
11-02 452
表与表的关系：核心表mysql/oracle、SQLServer(微软)SQL。
SpringSession源码分析
lkr_lkr的博客
11-01 203
默认对常规Session的理解和使用，如何使用Set-Cookie。
电科金仓（人大金仓）更新授权文件（致命错误: XX000: License file expired.）
轩小川
10-30 1807
问题：电科金仓（人大金仓）数据库链接异常，重启失败，查看日志如下：致命错误: XX000: License file expired.位置: PostmasterMain, postmaster.c:725。
MySQL入门指南：必知必会表格操作与基础概念
在这个关于"mysql表mysql必知必会读书笔记"的文章中，作者分享了他们阅读《mySQL必知必会》这本书时的学习笔记，强调了对数据库管理系统的理解，特别是针对初学者的重要概念。 首先，文章提到了DBMS（Database ...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值