SQL必知必會1-13 讀書筆記_ZenDei技術網路在線

博主不想寫字並向你仍來了一堆代碼 1-6 SQL——結構化查詢語言，Structured Query Language；基本按列查詢：高級一點的過濾查詢，WHERE,ORDER,IN,NOT,AND,OR,BETWEEN,NULL: 通配符，%,_,[]（實測MySQL我這裡不支持。。。）不要 ...

博主不想寫字並向你仍來了一堆代碼

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