MySQL操作匯總（單表創(chuàng)建查詢）

簡介

數(shù)據(jù)庫增刪改查操作當(dāng)中担败，查詢是使用最廣泛、最頻繁的操作镰官。即從表中檢索一個或多個數(shù)據(jù)列的操作提前。我們可以通過一些圖形化工具來進(jìn)行信息查詢，比如通過Navicat之類的工具泳唠。但是最多的還是通過編寫SQL語句來查詢狈网，這樣可以方便地嵌入到程序當(dāng)中。SQL語句是由簡單的英文單詞構(gòu)成的笨腥。這些單詞稱為關(guān)鍵字拓哺，每個SQL語句都是由一個或多個關(guān)鍵字構(gòu)成的。大概脖母，最經(jīng)常使用的SQL語句就是SELECT語句了士鸥。它的用途是從一個或者多個表中檢索信息。

準(zhǔn)備工作

一個MySQL數(shù)據(jù)庫服務(wù)端可能包含多個數(shù)據(jù)庫谆级。每個數(shù)據(jù)庫包含一到多張表烤礁。一張表是由行和列構(gòu)成。我們可以使用以下指令來顯示服務(wù)器中存在的數(shù)據(jù)庫肥照。

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| girls              |
| halo               |
| information_schema |
| iotat              |
| myemployees        |
| myflixdb           |
| mysql              |
| performance_schema |
| personinfo         |
| south              |
| sys                |
+--------------------+
11 rows in set (0.00 sec)

為了執(zhí)行查詢操作脚仔，我們先創(chuàng)建一個單獨的數(shù)據(jù)庫用于測試，再往里面添加相應(yīng)的表舆绎。以商品信息表為例鲤脏，表格內(nèi)容如下圖，接下來我們展示如何創(chuàng)建這張表吕朵，并且插入相應(yīng)的數(shù)據(jù)猎醇。

商品信息表

創(chuàng)建一個數(shù)據(jù)庫

使用CREATE DATABASE databaseName來創(chuàng)建創(chuàng)建一個數(shù)據(jù)庫，使用DROP DATABASE databaseName來刪除一個數(shù)據(jù)庫边锁。也可以使用IF EXISTS或者IF NOT EXISTS來做這些之類姑食，如下：

mysql> CREATE DATABASE southwind;
Query OK, 1 row affected (0.03 sec)
   
mysql> DROP DATABASE southwind;
Query OK, 0 rows affected (0.11 sec)
   
mysql> CREATE DATABASE IF NOT EXISTS southwind;
Query OK, 1 row affected (0.01 sec)
   
mysql> DROP DATABASE IF EXISTS southwind;
Query OK, 0 rows affected (0.00 sec)

注意DROP或者DELETE命令是不可逆的，謹(jǐn)慎使用茅坛。

使用數(shù)據(jù)庫

使用命令USE databaseName來將一個數(shù)據(jù)庫設(shè)置為默認(rèn)數(shù)據(jù)庫音半。你可以使用tableName來指定使用默認(rèn)數(shù)據(jù)庫中的一張表。但是如果要使用不在默認(rèn)數(shù)據(jù)庫中的表贡蓖，那么可以使用databaseName.tableName來指定曹鸠，即需要說明表是來自于哪一個數(shù)據(jù)庫。在我們的例子中斥铺，我們創(chuàng)建了一個名為southwind的數(shù)據(jù)庫彻桃，并且添加了一個名為products的表。如果我們使用USE southwind來將southwind設(shè)置為默認(rèn)的表晾蜘，那么我們可以直接通過products來引用這張表邻眷。

創(chuàng)建和刪除一張表

我們可以使用CREATE TABLE tableName和DROP TABLE tableName來創(chuàng)建和刪除表眠屎。要創(chuàng)建一張表，我們需要定義表中所有的列肆饶，通過提供列名改衩、類型、以及屬性。接下來，我們在southwind數(shù)據(jù)庫中創(chuàng)建名為products的表信轿。

mysql> CREATE DATABASE southwind;
Query OK, 1 row affected (0.01 sec)
mysql> USE southwind;
Database changed
 
-- Show the current (default) database
mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| southwind  |
+------------+
 
-- Show all the tables in the current database.
-- "southwind" has no table (empty set).
mysql> SHOW TABLES;
Empty set (0.00 sec)

創(chuàng)建表格products：

mysql> CREATE TABLE IF NOT EXISTS products (
         productID    INT UNSIGNED  NOT NULL AUTO_INCREMENT,
         productCode  CHAR(3)       NOT NULL DEFAULT '',
         name         VARCHAR(30)   NOT NULL DEFAULT '',
         quantity     INT UNSIGNED  NOT NULL DEFAULT 0,
         price        DECIMAL(7,2)  NOT NULL DEFAULT 99999.99,
         PRIMARY KEY  (productID)
       );
Query OK, 0 rows affected (0.08 sec)
 
-- Show all the tables to confirm that the "products" table has been created
mysql> SHOW TABLES;
+---------------------+
| Tables_in_southwind |
+---------------------+
| products            |
+---------------------+
 
-- Describe the fields (columns) of the "products" table
mysql> DESCRIBE products;
+-------------+------------------+------+-----+------------+----------------+
| Field       | Type             | Null | Key | Default    | Extra          |
+-------------+------------------+------+-----+------------+----------------+
| productID   | int(10) unsigned | NO   | PRI | NULL       | auto_increment |
| productCode | char(3)          | NO   |     |            |                |
| name        | varchar(30)      | NO   |     |            |                |
| quantity    | int(10) unsigned | NO   |     | 0          |                |
| price       | decimal(7,2)     | NO   |     | 99999.99   |                |
+-------------+------------------+------+-----+------------+----------------+

我們一共定義了5個列阴孟，分別為productID, productCode, name, quantity, price，這些類型描述如下：

productID是INT類型的 -- 非負(fù)整數(shù)
productCode是CHAR(3)類型的，即由3個字符組成的固定長度的字母數(shù)字字符串。
name是VARCHAR(30) - 是一個邊長的字符串，最多30個字符洽胶。
quantity 也是 INT UNSIGNED 類型的
price是DECIMAL(10,2) - 一個包含2個小數(shù)點的小數(shù)

屬性NOT NULL代表這一列不能包含NULL值。我們將productID這一列當(dāng)成所謂的主鍵往湿。主鍵的值必須是唯一的妖异。每張表都應(yīng)該包含一個主鍵，這確保每一行都能與其他行區(qū)分開來领追。當(dāng)然主鍵也不一定只包含一列他膳，我們也可以同時設(shè)置多列共同充當(dāng)主鍵。

插入行

現(xiàn)在我們創(chuàng)建好了prodcuts表绒窑，接下來我們需要插入一下數(shù)據(jù)到表中去棕孙。我們將第一條記錄的productID設(shè)置為1001，對于剩下的記錄些膨，我們直接將productID設(shè)為'NULL'蟀俊，因為我們設(shè)置了自增的屬性，故其余的記錄的編號會自動增加订雾。數(shù)據(jù)插入如下：

mysql> INSERT INTO products VALUES (1001, 'PEN', 'Pen Red', 5000, 1.23);
Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO products VALUES
    ->          (NULL, 'PEN', 'Pen Blue',  8000, 1.25),
    -> (NULL, 'PEN', 'Pen Black', 2000, 1.25);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT INTO products (productCode, name, quantity, price) VALUES
    ->          ('PEC', 'Pencil 2B', 10000, 0.48),
    ->          ('PEC', 'Pencil 2H', 8000, 0.49);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT INTO products (productCode, name) VALUES ('PEC', 'Pencil HB');
Query OK, 1 row affected (0.00 sec)

mysql>  INSERT INTO products values (NULL, NULL, NULL, NULL, NULL);
ERROR 1048 (23000): Column 'productCode' cannot be null
mysql> SELECT * FROM products;
+-----------+-------------+-----------+----------+----------+
| productID | productCode | name      | quantity | price    |
+-----------+-------------+-----------+----------+----------+
|      1001 | PEN         | Pen Red   |     5000 |     1.23 |
|      1002 | PEN         | Pen Blue  |     8000 |     1.25 |
|      1003 | PEN         | Pen Black |     2000 |     1.25 |
|      1004 | PEC         | Pencil 2B |    10000 |     0.48 |
|      1005 | PEC         | Pencil 2H |     8000 |     0.49 |
|      1006 | PEC         | Pencil HB |        0 | 99999.99 |
+-----------+-------------+-----------+----------+----------+
6 rows in set (0.00 sec)

mysql> DELETE FROM products WHERE productID = 1006;
Query OK, 1 row affected (0.01 sec)

數(shù)據(jù)庫查詢操作

數(shù)據(jù)庫查詢操作是最通用肢预、最重要也是最復(fù)雜的任務(wù)，即使用SELECT語句從數(shù)據(jù)庫查詢出符合用戶要求的數(shù)據(jù)洼哎，SELECT語句有以下語法：

-- List all the rows of the specified columns
SELECT column1Name, column2Name, ... FROM tableName
   
-- List all the rows of ALL columns, * is a wildcard denoting all columns
SELECT * FROM tableName
  
-- List rows that meet the specified criteria in WHERE clause
SELECT column1Name, column2Name,... FROM tableName WHERE criteria
SELECT * FROM tableName WHERE criteria

舉個例子烫映，從商品表中查詢出name和price列：

mysql> SELECT name, price FROM products;
+-----------+-------+
| name      | price |
+-----------+-------+
| Pen Red   |  1.23 |
| Pen Blue  |  1.25 |
| Pen Black |  1.25 |
| Pencil 2B |  0.48 |
| Pencil 2H |  0.49 |
+-----------+-------+
5 rows in set (0.00 sec)

mysql> SELECT * FROM products;
+-----------+-------------+-----------+----------+-------+
| productID | productCode | name      | quantity | price |
+-----------+-------------+-----------+----------+-------+
|      1001 | PEN         | Pen Red   |     5000 |  1.23 |
|      1002 | PEN         | Pen Blue  |     8000 |  1.25 |
|      1003 | PEN         | Pen Black |     2000 |  1.25 |
|      1004 | PEC         | Pencil 2B |    10000 |  0.48 |
|      1005 | PEC         | Pencil 2H |     8000 |  0.49 |
+-----------+-------------+-----------+----------+-------+
5 rows in set (0.00 sec)

SELECT語句也可以不搭配表使用，舉個例子噩峦，我們可以使用SELECT語句來對表達(dá)式或者內(nèi)置函數(shù)進(jìn)行求值：

mysql> SELECT 1+1;
+-----+
| 1+1 |
+-----+
|   2 |
+-----+
1 row in set (0.00 sec)
 
mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2012-10-24 22:13:29 |
+---------------------+
1 row in set (0.00 sec)

// Multiple columns
mysql> select 1+1, now();
+-----+---------------------+
| 1+1 | now()               |
+-----+---------------------+
|   2 | 2021-03-09 20:02:46 |
+-----+---------------------+
1 row in set (0.00 sec)

比較運算符

對于數(shù)值類型的數(shù)據(jù)锭沟，我們可以使用表達(dá)式操作符，包含=, <>或者!=, >, >=, <=,等识补。舉個例子族淮，我們需要篩選出price>1，quantitiy<=500的數(shù)據(jù)。

mysql> SELECT name, price FROM products WHERE price < 1.0;
+-----------+-------+
| name      | price |
+-----------+-------+
| Pencil 2B |  0.48 |
| Pencil 2H |  0.49 |
+-----------+-------+
2 rows in set (0.00 sec)

mysql> SELECT name, quantity FROM products WHERE quantity <= 2000;
+-----------+----------+
| name      | quantity |
+-----------+----------+
| Pen Black |     2000 |
+-----------+----------+
1 row in set (0.00 sec)

mysql> SELECT name, price FROM products WHERE productCode = 'PEN';
                                      -- String values are quoted
+-----------+-------+
| name      | price |
+-----------+-------+
| Pen Red   |  1.23 |
| Pen Blue  |  1.25 |
| Pen Black |  1.25 |
+-----------+-------+
3 rows in set (0.00 sec)

字符串模式匹配 LIKE

對于字符串祝辣，除了使用全匹配操作符比如 '='和'<>'贴妻，也可以使用操作符LIKE或者NOT LIKE加上通配符的方式來進(jìn)行模式匹配。通配符_可以匹配任意單個字符较幌，%可以匹配任意數(shù)量字符揍瑟，舉個例子：

'abc%'匹配以abc開頭的字符串
'%xyz' 匹配以xyz結(jié)尾的字符串
'%aaa%' 匹配包含aaa的字符串
'___' 匹配只包含3個字符的字符串
'a_b%' 匹配字符串以a開頭，隨后跟了一個單個字符乍炉，接下來跟一個字符b，最后是任意個字符

下面是幾個示例：

-- "name" begins with 'PENCIL'
mysql> SELECT name, price FROM products WHERE name LIKE 'PENCIL%';
+-----------+-------+
| name      | price |
+-----------+-------+
| Pencil 2B |  0.48 |
| Pencil 2H |  0.49 |
+-----------+-------+
 
-- "name" begins with 'P', followed by any two characters, 
--   followed by space, followed by zero or more characters
mysql> SELECT name, price FROM products WHERE name LIKE 'P__ %';
+-----------+-------+
| name      | price |
+-----------+-------+
| Pen Red   |  1.23 |
| Pen Blue  |  1.25 |
| Pen Black |  1.25 |
+-----------+-------+

數(shù)值運算操作符

可以使用數(shù)值運算操作符來對數(shù)值類型的數(shù)據(jù)進(jìn)行操作滤馍，主要有以下操作符：

邏輯運算操作符

可以使用布爾值操作符AND, OR, XOR來組合多個條件岛琼。例子如下：

mysql> SELECT * FROM products WHERE quantity >= 5000 AND name LIKE 'Pen %';
+-----------+-------------+----------+----------+-------+
| productID | productCode | name     | quantity | price |
+-----------+-------------+----------+----------+-------+
|      1001 | PEN         | Pen Red  |     5000 |  1.23 |
|      1002 | PEN         | Pen Blue |     8000 |  1.25 |
+-----------+-------------+----------+----------+-------+
 
mysql> SELECT * FROM products WHERE quantity >= 5000 AND price < 1.24 AND name LIKE 'Pen %';
+-----------+-------------+---------+----------+-------+
| productID | productCode | name    | quantity | price |
+-----------+-------------+---------+----------+-------+
|      1001 | PEN         | Pen Red |     5000 |  1.23 |
+-----------+-------------+---------+----------+-------+
   
mysql> SELECT * FROM products WHERE NOT (quantity >= 5000 AND name LIKE 'Pen %');
+-----------+-------------+-----------+----------+-------+
| productID | productCode | name      | quantity | price |
+-----------+-------------+-----------+----------+-------+
|      1003 | PEN         | Pen Black |     2000 |  1.25 |
|      1004 | PEC         | Pencil 2B |    10000 |  0.48 |
|      1005 | PEC         | Pencil 2H |     8000 |  0.49 |
+-----------+-------------+-----------+----------+-------+

IN、 NOT IN

使用IN操作符來判斷一個元素是否存在或者不存在于一個集合中巢株，這個比起ANR 和OR表達(dá)式更加簡潔槐瑞。例子：

mysql> SELECT * FROM products WHERE name IN ('Pen Red', 'Pen Black');
+-----------+-------------+-----------+----------+-------+
| productID | productCode | name      | quantity | price |
+-----------+-------------+-----------+----------+-------+
|      1001 | PEN         | Pen Red   |     5000 |  1.23 |
|      1003 | PEN         | Pen Black |     2000 |  1.25 |
+-----------+-------------+-----------+----------+-------+

BETWEEN, NOT BETWEEN

檢查某個元素是否在某個范圍之內(nèi)，使用BETWEEN...AND...語法阁苞。例子：

mysql> SELECT * FROM products 
       WHERE (price BETWEEN 1.0 AND 2.0) AND (quantity BETWEEN 1000 AND 2000);
+-----------+-------------+-----------+----------+-------+
| productID | productCode | name      | quantity | price |
+-----------+-------------+-----------+----------+-------+
|      1003 | PEN         | Pen Black |     2000 |  1.25 |
+-----------+-------------+-----------+----------+-------+

IS NULL困檩， IS NOT NULL

NULL是一個特殊值，代表沒有值那槽，缺失值或者未知值等悼沿。你可以通過IS NULL或者IS NOT NULL來檢查一個列是否包含NULL。例子：

mysql> SELECT * FROM products WHERE productCode IS NULL;
Empty set (0.00 sec)

ORDER BY排序

ORDER BY關(guān)鍵字對選擇的列進(jìn)行排序骚灸，使用以下語法：

  SELECT ... FROM tableName
  WHERE criteria
  ORDER BY columnA ASC|DESC, columnB ASC|DESC, ...

選擇的列會根據(jù)columnA進(jìn)行排序糟趾，默認(rèn)是升序排列，也可以通過指定DESC指定降序排列甚牲。例子如下：

mysql> SELECT * FROM products WHERE name LIKE 'Pen %' ORDER BY price DESC;
+-----------+-------------+-----------+----------+-------+
| productID | productCode | name      | quantity | price |
+-----------+-------------+-----------+----------+-------+
|      1002 | PEN         | Pen Blue  |     8000 |  1.25 |
|      1003 | PEN         | Pen Black |     2000 |  1.25 |
|      1001 | PEN         | Pen Red   |     5000 |  1.23 |
+-----------+-------------+-----------+----------+-------+
 
-- Order by price in descending order, followed by quantity in ascending (default) order
mysql> SELECT * FROM products WHERE name LIKE 'Pen %' ORDER BY price DESC, quantity;
+-----------+-------------+-----------+----------+-------+
| productID | productCode | name      | quantity | price |
+-----------+-------------+-----------+----------+-------+
|      1003 | PEN         | Pen Black |     2000 |  1.25 |
|      1002 | PEN         | Pen Blue  |     8000 |  1.25 |
|      1001 | PEN         | Pen Red   |     5000 |  1.23 |
+-----------+-------------+-----------+----------+-------+

LIMIT

SELECT語句可能會選出很多行义郑，我們可以使用LIMIT關(guān)鍵字來指定要顯示的行的數(shù)量：

-- Display the first two rows
mysql> SELECT * FROM products ORDER BY price LIMIT 2;
+-----------+-------------+-----------+----------+-------+
| productID | productCode | name      | quantity | price |
+-----------+-------------+-----------+----------+-------+
|      1004 | PEC         | Pencil 2B |    10000 |  0.48 |
|      1005 | PEC         | Pencil 2H |     8000 |  0.49 |
+-----------+-------------+-----------+----------+-------+

也可以指定從多少行開始，顯示多少行丈钙，語法為LIMIT s,l非驮，其中s代表起始行，l代表長度雏赦。例子：

mysql> SELECT * FROM products ORDER BY price LIMIT 2, 1;
+-----------+-------------+---------+----------+-------+
| productID | productCode | name    | quantity | price |
+-----------+-------------+---------+----------+-------+
|      1001 | PEN         | Pen Red |     5000 |  1.23 |
+-----------+-------------+---------+----------+-------+

AS 別名

可以使用AS關(guān)鍵字來為一行劫笙、一個表定義一個別名。這個別名會用來顯示名稱喉誊，也可以用來簡化SQL編寫邀摆。例子：

mysql> SELECT productID AS ID, productCode AS Code,
              name AS Description, price AS `Unit Price`  -- Define aliases to be used as display names
       FROM products
       ORDER BY ID;  -- Use alias ID as reference
+------+------+-------------+------------+
| ID   | Code | Description | Unit Price |
+------+------+-------------+------------+
| 1001 | PEN  | Pen Red     |       1.23 |
| 1002 | PEN  | Pen Blue    |       1.25 |
| 1003 | PEN  | Pen Black   |       1.25 |
| 1004 | PEC  | Pencil 2B   |       0.48 |
| 1005 | PEC  | Pencil 2H   |       0.49 |
+------+------+-------------+------------+

CONCAT

CONCAT用來將若干列連接起來。例子：

mysql> SELECT CONCAT(productCode, ' - ', name) AS `Product Description`, price FROM products;
+---------------------+-------+
| Product Description | price |
+---------------------+-------+
| PEN - Pen Red       |  1.23 |
| PEN - Pen Blue      |  1.25 |
| PEN - Pen Black     |  1.25 |
| PEC - Pencil 2B     |  0.48 |
| PEC - Pencil 2H     |  0.49 |
+---------------------+-------+

DISTINCT

一列可能有多個相同的值伍茄，可以使用DISTINCT關(guān)鍵字來進(jìn)行數(shù)據(jù)去重栋盹。例子：

-- Without DISTINCT
mysql> SELECT price FROM products;
+-------+
| price |
+-------+
|  1.23 |
|  1.25 |
|  1.25 |
|  0.48 |
|  0.49 |
+-------+
   
-- With DISTINCT on price
mysql> SELECT DISTINCT price AS `Distinct Price` FROM products;
+----------------+
| Distinct Price |
+----------------+
|           1.23 |
|           1.25 |
|           0.48 |
|           0.49 |
+----------------+
 
-- DISTINCT combination of price and name
mysql> SELECT DISTINCT price, name FROM products;
+-------+-----------+
| price | name      |
+-------+-----------+
|  1.23 | Pen Red   |
|  1.25 | Pen Blue  |
|  1.25 | Pen Black |
|  0.48 | Pencil 2B |
|  0.49 | Pencil 2H |
+-------+-----------+

GROUP BY分組

GROUP BY允許用戶對具有公共值的多條記錄進(jìn)行分組操作。在使用GROUP BY子句之前敷矫，需要知道一些重要的規(guī)定：

GROUP BY子句可以包含任意數(shù)目的列例获。這使得能對分組進(jìn)行嵌套汉额，為數(shù)據(jù)分組提供更細(xì)致的控制。
如果在GROUP BY子句中嵌套了分組榨汤，數(shù)據(jù)將在最后規(guī)定的分組上進(jìn)行匯總蠕搜。換句話說，在建立分組時收壕，指定的所有列都一起計算(所以不能從個別的列取回數(shù)據(jù))妓灌。
GROUP BY子句中列出的每個列都必須是檢索列或有效的表達(dá)式(但不能是聚集函數(shù))。如果在SELECT中使用表達(dá)式蜜宪，則必須在GROUP BY子句中指定相同的表達(dá)式虫埂。不能使用別名。
除聚集計算語句外圃验，SELECT語句中的每個列都必須在GROUP BY子句中給出掉伏。
如果分組列中具有NULL值，則NULL將作為一個分組返回澳窑。如果列中有多行NULL值斧散，它們將分為一組。
GROUP BY子句必須出現(xiàn)在WHERE子句之后摊聋，ORDER BY子句之前鸡捐。

GROUP BY函數(shù)本身沒有什么意義，它一般搭配聚合函數(shù)使用栗精，比如COUNT(),AVG(),SUM()等來產(chǎn)生分組之后的結(jié)果闯参。例子：

mysql> SELECT productCode, count(*) as number FROM products GROUP BY productCode;
+-------------+--------+
| productCode | number |
+-------------+--------+
| PEN         |      3 |
| PEC         |      2 |
+-------------+--------+
2 rows in set (0.01 sec)

mysql> SELECT productCode, COUNT(*) AS count
    ->        FROM products
    ->        GROUP BY productCode
    ->        ORDER BY count ASC;
+-------------+-------+
| productCode | count |
+-------------+-------+
| PEC         |     2 |
| PEN         |     3 |
+-------------+-------+
2 rows in set (0.00 sec)

除了基本的COUNT()函數(shù)，還有許多其他的GROUP BY聚合函數(shù)悲立，比如AVG(),MAX(),MIN()以及SUM()鹿寨。例子如下：

mysql> SELECT productCode, MAX(price) AS `Highest Price`, MIN(price) AS `Lowest Price`
    ->        FROM products
    ->        GROUP BY productCode;
+-------------+---------------+--------------+
| productCode | Highest Price | Lowest Price |
+-------------+---------------+--------------+
| PEN         |          1.25 |         1.23 |
| PEC         |          0.49 |         0.48 |
+-------------+---------------+--------------+
2 rows in set (0.00 sec)

mysql> SELECT productCode, MAX(price), MIN(price),
              CAST(AVG(price) AS DECIMAL(7,2)) AS `Average`,
              CAST(STD(price) AS DECIMAL(7,2)) AS `Std Dev`,
              SUM(quantity)
       FROM products
       GROUP BY productCode;
       -- Use CAST(... AS ...) function to format floating-point numbers
+-------------+------------+------------+---------+---------+---------------+
| productCode | MAX(price) | MIN(price) | Average | Std Dev | SUM(quantity) |
+-------------+------------+------------+---------+---------+---------------+
| PEC         |       0.49 |       0.48 |    0.49 |    0.01 |         18000 |
| PEN         |       1.25 |       1.23 |    1.24 |    0.01 |         15000 |
+-------------+------------+------------+---------+---------+---------------+

HAVING語句

HAVING類似于WHERE，但是它可以與GROUP BY搭配使用薪夕，而WHERE只能作用于列上脚草。例子：

mysql> SELECT
    ->           productCode AS `Product Code`,
    ->           COUNT(*) AS `Count`,
    ->           CAST(AVG(price) AS DECIMAL(7,2)) AS `Average`
    ->        FROM products
    ->        GROUP BY productCode
    ->        HAVING Count >=3;
+--------------+-------+---------+
| Product Code | Count | Average |
+--------------+-------+---------+
| PEN          |     3 |    1.24 |
+--------------+-------+---------+
1 row in set (0.01 sec)

HAVING和WHERE的差別，這里有另一種理解方法原献，WHERE在數(shù)據(jù)分組前進(jìn)行過濾馏慨，HAVING在數(shù)據(jù)分組后進(jìn)行過濾。這是一個重要的區(qū)別姑隅，WHERE排除的行不包括在分組中写隶。這可能會改變計算值，從而影響HAVING子句中基于這些值過濾掉的分組讲仰。

UNION 組合查詢

多數(shù)SQL查詢都只包含從一個或多個表中返回數(shù)據(jù)的單條SELECT語句慕趴。MySQL也允許執(zhí)行多個查詢(多條SELECT語句)，并將結(jié)果作為單個查詢結(jié)果集返回。這些組合查詢通常稱為并(union)或復(fù)合查詢(compound query)冕房。有兩種基本情況躏啰，其中需要使用組合查詢:

在單個查詢中從不同的表返回類似結(jié)構(gòu)的數(shù)據(jù)
對單個表執(zhí)行多個查詢，按單個查詢返回數(shù)據(jù)

利用UNION操作符來組合數(shù)條SQL查詢耙册，并且將結(jié)果組合成單個結(jié)果集合给僵。例子：

mysql> SELECT productID, name, price from products
    -> WHERE name LIKE '%Black' AND price > 1.23
    -> UNION
    -> SELECT productID, name, price from products
    -> WHERE productID IN (1004, 1005);
+-----------+-----------+-------+
| productID | name      | price |
+-----------+-----------+-------+
|      1003 | Pen Black |  1.25 |
|      1004 | Pencil 2B |  0.48 |
|      1005 | Pencil 2H |  0.49 |
+-----------+-----------+-------+
3 rows in set (0.00 sec)

也可以對組合查詢的結(jié)果進(jìn)行排序。不過使用UNION組合查詢時详拙，只能使用一條ORDER BY子句帝际，它必須出現(xiàn)在最后一條SELECT語句之后。只能對結(jié)果集使用溪厘。例子：

mysql> SELECT productID, name, price from products
    -> WHERE name LIKE '%Black' AND price > 1.23
    -> UNION
    -> SELECT productID, name, price from products
    -> WHERE productID IN (1004, 1005)
    -> ORDER BY price ASC;
+-----------+-----------+-------+
| productID | name      | price |
+-----------+-----------+-------+
|      1004 | Pencil 2B |  0.48 |
|      1005 | Pencil 2H |  0.49 |
|      1003 | Pen Black |  1.25 |
+-----------+-----------+-------+
3 rows in set (0.00 sec)

UPDATE

如果想修改已經(jīng)存在的數(shù)據(jù)，可以使用UPDATE...SET命令畸悬，語法如下:

  UPDATE tableName SET columnName = {value|NULL|DEFAULT}, ... WHERE criteria

舉個例子：

mysql> UPDATE products SET price = price * 2;
Query OK, 5 rows affected (0.02 sec)
Rows matched: 5  Changed: 5  Warnings: 0

mysql> SELECT * FROM products;
+-----------+-------------+-----------+----------+-------+
| productID | productCode | name      | quantity | price |
+-----------+-------------+-----------+----------+-------+
|      1001 | PEN         | Pen Red   |     5000 |  2.46 |
|      1002 | PEN         | Pen Blue  |     8000 |  2.50 |
|      1003 | PEN         | Pen Black |     2000 |  2.50 |
|      1004 | PEC         | Pencil 2B |    10000 |  0.96 |
|      1005 | PEC         | Pencil 2H |     8000 |  0.98 |
+-----------+-------------+-----------+----------+-------+
5 rows in set (0.01 sec)

mysql> UPDATE products SET quantity = quantity - 100 WHERE name = 'Pen Red';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM products WHERE name = 'Pen Red';
+-----------+-------------+---------+----------+-------+
| productID | productCode | name    | quantity | price |
+-----------+-------------+---------+----------+-------+
|      1001 | PEN         | Pen Red |     4900 |  2.46 |
+-----------+-------------+---------+----------+-------+
1 row in set (0.00 sec)

mysql> UPDATE products SET quantity = quantity + 50, price = 1.23 WHERE name = 'Pen Red';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM products WHERE name = 'Pen Red';
+-----------+-------------+---------+----------+-------+
| productID | productCode | name    | quantity | price |
+-----------+-------------+---------+----------+-------+
|      1001 | PEN         | Pen Red |     4950 |  1.23 |
+-----------+-------------+---------+----------+-------+
1 row in set (0.00 sec)

注意如果在UPDATE的時候，不指定WHERE語句的話珊佣，那么默認(rèn)所有的行都會被更新蹋宦。

DELETE ROW

使用DELETE FROM命令來刪除一個表中的某些行，語法如下：

-- Delete all rows from the table. Use with extreme care! Records are NOT recoverable!!!
DELETE FROM tableName
-- Delete only row(s) that meets the criteria
DELETE FROM tableName WHERE criteria