目前的情況是一邊學Java，一邊學習MySQL。看的是MySQL提供的文檔，這篇博文是學習的總結。（2016-4-3）
本文最後更新時間：2016-4-5
NOTE：關於MySQL的安裝文檔在這裡
1.查看help信息
shell> mysql --help
2.連接到MySQL服務程序
shell> mysql -h host -u user -p
host：運行著MySQL服務的主機名。如果是在你本機上安裝了，那-h host這部分可以不寫。也即執行mysql -u user -p就可以。如果硬要寫，那麼host寫成localhost，表明MySQL服務運行在本地主機上。比如下面這個登陸的例子。
user：表示你以user這個賬戶登陸到MySQL服務。MySQL在安裝過程中會自動創建root賬戶，密碼為創建過程中你輸入的那個密碼。如果安裝過程沒有輸入，則root賬戶密碼默認為空。（至少在我的ubuntu機器上是這樣的一個情況，windows下如何我不知道。如果你在windos下操作，那可能和這有出路。我的建議是：如果你是計科學生，現在拋棄windos還為時不晚。）
在登陸前，請先確保MySQL服務正在運行。使用sudo service mysql status來查看MySQL的運行狀態。比如在我的機器上的查詢過程如下：
wallace@zhenghong-E430:~$ sudo service mysql status
[sudo] password for wallace:
* MySQL Community Server 5.7.11 is running
wallace@zhenghong-E430:~$
使用root賬戶登陸MySQL，如下：
wallace@zhenghong-E430:~$ mysql -h localhost -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.11 MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
3.斷開MySQL的連接
mysql> QUIT
3.2-輸入查詢語句1：查看MySQL版本和當前數據
mysql> SELECT VERSION(), CURRENT_DATE;
+-----------+--------------+
| VERSION() | CURRENT_DATE |
+-----------+--------------+
| 5.7.11 | 2016-04-03 |
+-----------+--------------+
1 row in set (0.00 sec)
mysql>
2：輸入簡單的表達式
mysql> SELECT SIN(PI()/4), (4+1)*5
-> ;
+--------------------+---------+
| SIN(PI()/4) | (4+1)*5 |
+--------------------+---------+
| 0.7071067811865475 | 25 |
+--------------------+---------+
1 row in set (0.04 sec)
mysql>
3：在同一行輸入多個查詢語句（用分號隔開）
mysql> SELECT VERSION(); SELECT NOW();
+-----------+
| VERSION() |
+-----------+
| 5.7.11 |
+-----------+
1 row in set (0.00 sec)
+---------------------+
| NOW() |
+---------------------+
| 2016-04-03 23:09:58 |
+---------------------+
1 row in set (0.00 sec)
mysql>
4：將一句查詢語句寫在多行中（因為MySQL判定一條查詢語句時看的是分號而不是輸入的行數，所以你可以這麼干）
mysql> SELECT
-> USER()
-> ,
-> CURRENT_DATE;
+----------------+--------------+
| USER() | CURRENT_DATE |
+----------------+--------------+
| root@localhost | 2016-04-03 |
+----------------+--------------+
1 row in set (0.04 sec)
mysql>
5：輸入“\c”來清空你當前以輸入的語句
3.3-創建並使用數據庫1：查看當前已創建了哪些數據庫
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.09 sec)
mysql>
2：access到已創建好的數據庫中（USE和QUIT一樣，不需要使用分號來結束語句，所以USE必須寫在一行中））
mysql> USE sys
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql>
1：創建數據庫
mysql> CREATE DATABASE test;
Query OK, 1 row affected (0.04 sec)
mysql>
2：讓你創建的test數據庫成為current database
mysql> USE test
Database changed
mysql>
3：如何讓你在登陸MySQL服務時就將current database切換到你設置的那個？
答案：在登陸命令的後面加上數據庫名就好了，比如下面的例子
shell> mysql -h host -u user -p test
4：查看current database是哪個
mysql> SELECT DATABASE()
-> ;
+------------+
| DATABASE() |
+------------+
| test |
+------------+
1 row in set (0.00 sec)
mysql>
5：查看current database裡的表有哪些
mysql> SHOW TABLES
-> ;
Empty set (0.00 sec)
mysql>
Empty set表示current database裡還沒有表
6：建表
mysql> CREATE TABLE pet (
-> name VARCHAR(20),
-> owner VARCHAR(20),
-> species VARCHAR(20),
-> sex CHAR(1),
-> birth DATE,
-> death DATE
-> );
Query OK, 0 rows affected (0.92 sec)
mysql>
7：MySQL支持的數據類型
VARCHAR：是CHAR類型，但它尤其適合用在同一個表中的不同記錄的同一個屬性值長度不一樣的情況。比如上表的name屬性，它不是定長的。它可以是petty，長度為5；也可以是tom，長度為3。
CHAR：（未完待續）
DATE：（未完待續）
8：更改表
ALTER TABLE
9：查看表的詳細信息
mysql> DESCRIBE pet;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name | varchar(20) | YES | | NULL | |
| owner | varchar(20) | YES | | NULL | |
| species | varchar(20) | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
| birth | date | YES | | NULL | |
| death | date | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
6 rows in set (0.03 sec)
mysql>
10：向表中插入數據
從文件中導入數據到表格中 假設home下有一個pet.txt，裡面已經輸入了記錄
mysql> LOAD DATA LOCAL INFILE '/home/wallace/pet.txt' INTO TABLE pet;
Query OK, 2 rows affected, 5 warnings (0.06 sec)
Records: 2 Deleted: 0 Skipped: 0 Warnings: 5
mysql>
使用INSERT語句插入
mysql> INSERT INTO pet
-> VALUES ('Puffball', 'Diane', 'hamseter', 'f', '1999-03-30', NULL);
Query OK, 1 row affected (0.06 sec)
mysql>
11：在表中檢索信息
SELECT what_to_select
FROM which_table
WHERE conditions_to_satisfy;
mysql> SELECT * FROM pet;
+--------+--------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+--------+--------+---------+------+------------+------------+
| Fluffy | Harold | cat | f | 1993-02-04 | NULL |
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
| Fang | Benny | dog | m | 1990-08-27 | NULL |
| Bowser | Diane | dog | m | 1979-08-31 | 1995-07-29 |
| Chirpy | Gwen | bird | f | 1996-09-11 | NULL |
| Whistl | Gwen | bird | NULL | 1997-12-09 | NULL |
| Slim | Benny | snake | m | 1996-04-29 | NULL |
| Puff | Diane | hamster | f | 1999-03-30 | NULL |
+--------+--------+---------+------+------------+------------+
9 rows in set (0.00 sec)
mysql>
12：如何修改表中的數據項
- （如果你是從文本中導入的數據，而那個文本還存在）修改文本pet.txt到正確的情況。刪除表格中的數據，然後再重新LOAD
使用update語句修改
mysql> UPDATE pet SET birth = '1989-08-31' WHERE name = 'Bowser' ;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql>
13：選擇特定的某些行
mysql> SELECT * FROM pet WHERE name = 'Bowser' ;
+--------+-------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+--------+-------+---------+------+------------+------------+
| Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 |
+--------+-------+---------+------+------------+------------+
1 row in set (0.00 sec)
mysql>
mysql> SELECT * FROM pet WHERE birth >= '1998-1-1' ;
+--------+-------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+--------+-------+---------+------+------------+-------+
| Chirpy | Gwen | bird | f | 1998-09-11 | NULL |
| Puff | Diane | hamster | f | 1999-03-30 | NULL |
+--------+-------+---------+------+------------+-------+
2 rows in set (0.00 sec)
mysql>
mysql> SELECT * FROM pet WHERE species = 'dog' AND sex = 'f' ;
+-------+--------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+-------+--------+---------+------+------------+-------+
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
+-------+--------+---------+------+------------+-------+
1 row in set (0.00 sec)
mysql>
mysql> SELECT * FROM pet WHERE species = 'snake' OR species = 'bird' ;
+--------+-------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+--------+-------+---------+------+------------+-------+
| Chirpy | Gwen | bird | f | 1998-09-11 | NULL |
| Whistl | Gwen | bird | NULL | 1997-12-09 | NULL |
| Slim | Benny | snake | m | 1996-04-29 | NULL |
+--------+-------+---------+------+------------+-------+
3 rows in set (0.00 sec)
mysql> SELECT * FROM pet
-> WHERE (species = 'cat' AND sex = 'm')
-> OR
-> (species = 'dog' AND sex = 'f') ;
+-------+--------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
+-------+--------+---------+------+------------+-------+
2 rows in set (0.01 sec)
mysql>
14：選擇特定的某些列
mysql> SELECT name, birth FROM pet;
+--------+------------+
| name | birth |
+--------+------------+
| Fluffy | 1993-02-04 |
| Claws | 1994-03-17 |
| Buffy | 1989-05-13 |
| Fang | 1990-08-27 |
| Bowser | 1989-08-31 |
| Chirpy | 1998-09-11 |
| Whistl | 1997-12-09 |
| Slim | 1996-04-29 |
| Puff | 1999-03-30 |
+--------+------------+
9 rows in set (0.00 sec)
mysql>
mysql> SELECT owner FROM pet;
+--------+
| owner |
+--------+
| Harold |
| Gwen |
| Harold |
| Benny |
| Diane |
| Gwen |
| Gwen |
| Benny |
| Diane |
+--------+
9 rows in set (0.04 sec)
mysql>
15：去掉重復記錄
mysql> SELECT DISTINCT owner FROM pet;
+--------+
| owner |
+--------+
| Harold |
| Gwen |
| Benny |
| Diane |
+--------+
4 rows in set (0.02 sec)
mysql>
mysql> SELECT name, species, birth FROM
-> pet
-> WHERE species = 'dog' OR
-> species = 'cat' ;
+--------+---------+------------+
| name | species | birth |
+--------+---------+------------+
| Fluffy | cat | 1993-02-04 |
| Claws | cat | 1994-03-17 |
| Buffy | dog | 1989-05-13 |
| Fang | dog | 1990-08-27 |
| Bowser | dog | 1989-08-31 |
+--------+---------+------------+
5 rows in set (0.01 sec)
mysql>
16：對輸出結果對行進行排序（DESC表示降序排列，MySQL默認是升序排列的）
mysql> SELECT name, birth FROM pet ORDER BY birth;
+--------+------------+
| name | birth |
+--------+------------+
| Buffy | 1989-05-13 |
| Bowser | 1989-08-31 |
| Fang | 1990-08-27 |
| Fluffy | 1993-02-04 |
| Claws | 1994-03-17 |
| Slim | 1996-04-29 |
| Whistl | 1997-12-09 |
| Chirpy | 1998-09-11 |
| Puff | 1999-03-30 |
+--------+------------+
9 rows in set (0.02 sec)
mysql>
mysql> SELECT name, birth FROM pet ORDER BY birth DESC;
+--------+------------+
| name | birth |
+--------+------------+
| Puff | 1999-03-30 |
| Chirpy | 1998-09-11 |
| Whistl | 1997-12-09 |
| Slim | 1996-04-29 |
| Claws | 1994-03-17 |
| Fluffy | 1993-02-04 |
| Fang | 1990-08-27 |
| Bowser | 1989-08-31 |
| Buffy | 1989-05-13 |
+--------+------------+
9 rows in set (0.00 sec)
mysql>
按照species升序排列，birth降序排列的順序來排列
mysql> mysql> SELECT name, species, birth FROM pet ORDER BY species, birth DESC;
+--------+---------+------------+
| name | species | birth |
+--------+---------+------------+
| Chirpy | bird | 1998-09-11 |
| Whistl | bird | 1997-12-09 |
| Claws | cat | 1994-03-17 |
| Fluffy | cat | 1993-02-04 |
| Fang | dog | 1990-08-27 |
| Bowser | dog | 1989-08-31 |
| Buffy | dog | 1989-05-13 |
| Puff | hamster | 1999-03-30 |
| Slim | snake | 1996-04-29 |
+--------+---------+------------+
9 rows in set (0.00 sec)
mysql>
17：日期計算
mysql> SELECT name, birth, CURDATE(),
-> TIMESTAMPDIFF(YEAR, birth, CURDATE()) AS age
-> FROM pet;
+--------+------------+------------+------+
| name | birth | CURDATE() | age |
+--------+------------+------------+------+
| Fluffy | 1993-02-04 | 2016-04-04 | 23 |
| Claws | 1994-03-17 | 2016-04-04 | 22 |
| Buffy | 1989-05-13 | 2016-04-04 | 26 |
| Fang | 1990-08-27 | 2016-04-04 | 25 |
| Bowser | 1989-08-31 | 2016-04-04 | 26 |
| Chirpy | 1998-09-11 | 2016-04-04 | 17 |
| Whistl | 1997-12-09 | 2016-04-04 | 18 |
| Slim | 1996-04-29 | 2016-04-04 | 19 |
| Puff | 1999-03-30 | 2016-04-04 | 17 |
+--------+------------+------------+------+
9 rows in set (0.01 sec)
mysql>
mysql> SELECT name, birth, CURDATE(),
-> TIMESTAMPDIFF(YEAR, birth, CURDATE()) AS age
-> FROM pet ORDER BY name;
+--------+------------+------------+------+
| name | birth | CURDATE() | age |
+--------+------------+------------+------+
| Bowser | 1989-08-31 | 2016-04-04 | 26 |
| Buffy | 1989-05-13 | 2016-04-04 | 26 |
| Chirpy | 1998-09-11 | 2016-04-04 | 17 |
| Claws | 1994-03-17 | 2016-04-04 | 22 |
| Fang | 1990-08-27 | 2016-04-04 | 25 |
| Fluffy | 1993-02-04 | 2016-04-04 | 23 |
| Puff | 1999-03-30 | 2016-04-04 | 17 |
| Slim | 1996-04-29 | 2016-04-04 | 19 |
| Whistl | 1997-12-09 | 2016-04-04 | 18 |
+--------+------------+------------+------+
9 rows in set (0.00 sec)
mysql>
mysql> SELECT name, birth, CURDATE(), TIMESTAMPDIFF(YEAR, birth, CURDATE()) AS age FROM pet ORDER BY age DESC;
+--------+------------+------------+------+
| name | birth | CURDATE() | age |
+--------+------------+------------+------+
| Buffy | 1989-05-13 | 2016-04-04 | 26 |
| Bowser | 1989-08-31 | 2016-04-04 | 26 |
| Fang | 1990-08-27 | 2016-04-04 | 25 |
| Fluffy | 1993-02-04 | 2016-04-04 | 23 |
| Claws | 1994-03-17 | 2016-04-04 | 22 |
| Slim | 1996-04-29 | 2016-04-04 | 19 |
| Whistl | 1997-12-09 | 2016-04-04 | 18 |
| Chirpy | 1998-09-11 | 2016-04-04 | 17 |
| Puff | 1999-03-30 | 2016-04-04 | 17 |
+--------+------------+------------+------+
9 rows in set (0.00 sec)
mysql>
數據不為空的寫法
mysql> SELECT name, birth, death, TIMESTAMPDIFF(YEAR, birth, death) AS age FROM pet WHERE death IS NOT NULL ORDER BY age;
+--------+------------+------------+------+
| name | birth | death | age |
+--------+------------+------------+------+
| Bowser | 1989-08-31 | 1995-07-29 | 5 |
+--------+------------+------------+------+
1 row in set (0.00 sec)
mysql>
MySQL提供的日期函數：YEAR(), MONTH(), DAYOFMONTH()
這個例子應該可以讓你明白它們的含義
mysql> SELECT name, birth, YEAR(birth), MONTH(birth), DAYOFMONTH(birth) FROM pet;
+--------+------------+-------------+--------------+-------------------+
| name | birth | YEAR(birth) | MONTH(birth) | DAYOFMONTH(birth) |
+--------+------------+-------------+--------------+-------------------+
| Fluffy | 1993-02-04 | 1993 | 2 | 4 |
| Claws | 1994-03-17 | 1994 | 3 | 17 |
| Buffy | 1989-05-13 | 1989 | 5 | 13 |
| Fang | 1990-08-27 | 1990 | 8 | 27 |
| Bowser | 1989-08-31 | 1989 | 8 | 31 |
| Chirpy | 1998-09-11 | 1998 | 9 | 11 |
| Whistl | 1997-12-09 | 1997 | 12 | 9 |
| Slim | 1996-04-29 | 1996 | 4 | 29 |
| Puff | 1999-03-30 | 1999 | 3 | 30 |
+--------+------------+-------------+--------------+-------------------+
9 rows in set (0.00 sec)
mysql>
查找5月份出生的狗狗
mysql> SELECT name, birth FROM pet WHERE MONTH(birth) = '5';
+-------+------------+
| name | birth |
+-------+------------+
| Buffy | 1989-05-13 |
+-------+------------+
1 row in set (0.00 sec)
mysql>
mysql> SELECT name, birth FROM pet
-> WHERE MONTH(birth) = MONTH(DATE_ADD(CURDATE(), INTERVAL 1 MONTH));
+-------+------------+
| name | birth |
+-------+------------+
| Buffy | 1989-05-13 |
+-------+------------+
1 row in set (0.00 sec)
mysql>
再來一個例子，你就明白了
mysql> SELECT name, birth FROM pet WHERE MONTH(birth) = MONTH(DATE_ADD(CURDATE(), INTERVAL 30 DAY));
+-------+------------+
| name | birth |
+-------+------------+
| Buffy | 1989-05-13 |
+-------+------------+
1 row in set (0.00 sec)
mysql>
MOD函數
mysql> SELECT name, birth FROM pet WHERE MONTH(birth) = MOD(MONTH(CURDATE()), 12) + 1;
+-------+------------+
| name | birth |
+-------+------------+
| Buffy | 1989-05-13 |
+-------+------------+
1 row in set (0.00 sec)
mysql>
18：數據為空如何操作
mysql> SELECT 1 IS NULL, 1 IS NOT NULL;
+-----------+---------------+
| 1 IS NULL | 1 IS NOT NULL |
+-----------+---------------+
| 0 | 1 |
+-----------+---------------+
1 row in set (0.00 sec)
mysql>
記住一句話：When doing an ORDER BY, NULL values are presented first if you do ORDER BY … ASC and last if you do ORDER BY … DESC.
In MySQL, 0 or NULL means false and anything else means true
19：模式匹配
記住下面五點：
1：_（下劃線）代表一個任一字符。%（百分號）代表任意個字符
2：相等和不想等用LIKE和NOT LIKE表示
3：模式匹配是大小寫不敏感的
4：使用REGEXP（或者RLIKE）和NOT REGEXP（或者NOT RLIKE）來連接正則表達式。
5：幾個擴展的正則表達式用法
- “.”（點號）匹配單個字符
[…]匹配在方括號內的任何一個字符。比如，[abc]匹配“a”，“b”或“c“
怎樣匹配某個范圍內的字符呢？答案：[a-z]匹配所有的字母；[0-9]匹配所有的數字
”“匹配任意個寫在它前面的字符（包括0個）。比如”x“匹配任意長度的只含x的字符串；[0-9]匹配任意長度的數字串；”.*“匹配任意字符串
英文原句：To anchor a pattern so that it must match the beginning or end of the value being tested, use “^” at the beginning or “$” at the end of the pattern.
例如：To find names beginning with “b”, use “^” to match the beginning of the name:
mysql> SELECT * FROM pet
-> WHERE name LIKE 'b%' ;
+--------+--------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+--------+--------+---------+------+------------+------------+
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
| Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 |
+--------+--------+---------+------+------------+------------+
2 rows in set (0.00 sec)
mysql>
例如：To find names ending with “fy”, use “$” to match the end of the name:
mysql> SELECT * FROM pet WHERE name REGEXP 'fy$';
+--------+--------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+--------+--------+---------+------+------------+-------+
| Fluffy | Harold | cat | f | 1993-02-04 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
+--------+--------+---------+------+------------+-------+
2 rows in set (0.00 sec)
mysql>
例如：To find names containing a “w”, use this query:
mysql> SELECT * FROM pet WHERE name REGEXP 'w';
+--------+-------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+--------+-------+---------+------+------------+------------+
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Bowser | Diane | dog | m | 1989-08-31 | 1995-07-29 |
| Whistl | Gwen | bird | NULL | 1997-12-09 | NULL |
+--------+-------+---------+------+------------+------------+
3 rows in set (0.00 sec)
mysql>
例如：To find names containing exactly five characters, use “^” and “$” to match the beginning and end of the name, and five instances of “.” in between:
mysql> SELECT * FROM pet WHERE name REGEXP '^.....$' ;
+-------+--------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
+-------+--------+---------+------+------------+-------+
2 rows in set (0.00 sec)
mysql>
NOTE：你可以在正則表達式中使用{n}來重復寫在它前面的字符n次。比如，你可以這樣完成上面的例子
mysql> SELECT * FROM pet WHERE name REGEXP '^.{5}$' ;
+-------+--------+---------+------+------------+-------+
| name | owner | species | sex | birth | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen | cat | m | 1994-03-17 | NULL |
| Buffy | Harold | dog | f | 1989-05-13 | NULL |
+-------+--------+---------+------+------------+-------+
2 rows in set (0.00 sec)
mysql>
19：統計行數
mysql> SELECT COUNT(*) FROM pet;
+----------+
| COUNT(*) |
+----------+
| 9 |
+----------+
1 row in set (0.01 sec)
mysql>
20：GROUP BY語句
mysql> SELECT owner, COUNT(*) FROM pet GROUP BY owner;
+--------+----------+
| owner | COUNT(*) |
+--------+----------+
| Benny | 2 |
| Diane | 2 |
| Gwen | 3 |
| Harold | 2 |
+--------+----------+
4 rows in set (0.02 sec)
mysql>
有沒有發現什麼？
如果你要統計的屬性是主鍵的話（因為主鍵是唯一的），那也就沒必要統計了，肯定只有一條。但如果你要統計的屬性不是主鍵，那意味著可能有多條屬性值是一樣的記錄。比如你要統計A屬性，你就SELECT A，然後再GROUP BY A一下就行了。看看人家英文原文是怎麼表達的：The preceding query uses GROUP BY to group all records for each owner.
mysql> SELECT species, COUNT(*) FROM pet GROUP BY species;
+---------+----------+
| species | COUNT(*) |
+---------+----------+
| bird | 2 |
| cat | 2 |
| dog | 3 |
| hamster | 1 |
| snake | 1 |
+---------+----------+
5 rows in set (0.01 sec)
mysql>
mysql> SELECT sex, COUNT(*) FROM pet GROUP BY sex;
+------+----------+
| sex | COUNT(*) |
+------+----------+
| NULL | 1 |
| f | 4 |
| m | 4 |
+------+----------+
3 rows in set (0.00 sec)
mysql>
注意下面這個GROUP BY 2個屬性的
mysql> SELECT species, sex, COUNT(*) FROM pet GROUP BY species, sex;
+---------+------+----------+
| species | sex | COUNT(*) |
+---------+------+----------+
| bird | NULL | 1 |
| bird | f | 1 |
| cat | f | 1 |
| cat | m | 1 |
| dog | f | 1 |
| dog | m | 2 |
| hamster | f | 1 |
| snake | m | 1 |
+---------+------+----------+
8 rows in set (0.00 sec)
mysql>
一個更加復雜的例子
mysql> SELECT species, sex, COUNT(*) FROM pet WHERE species = 'dog' OR species = 'cat' GROUP BY species, sex;
+---------+------+----------+
| species | sex | COUNT(*) |
+---------+------+----------+
| cat | f | 1 |
| cat | m | 1 |
| dog | f | 1 |
| dog | m | 2 |
+---------+------+----------+
4 rows in set (0.00 sec)
mysql>
mysql> SELECT species, sex, COUNT(*) FROM pet
-> WHERE sex IS NOT NULL
-> GROUP BY species, sex;
+---------+------+----------+
| species | sex | COUNT(*) |
+---------+------+----------+
| bird | f | 1 |
| cat | f | 1 |
| cat | m | 1 |
| dog | f | 1 |
| dog | m | 2 |
| hamster | f | 1 |
| snake | m | 1 |
+---------+------+----------+
7 rows in set (0.00 sec)
mysql>
接下來是幾個比較難的查詢，涉及到了在多個表中進行查詢的情況
21：如何同時在兩個表中SELECT
mysql> SELECT pet.name,
-> (YEAR(date)-YEAR(birth)) - (RIGHT(date, 5) remark
-> FROM pet INNER JOIN event
-> ON pet.name = event.name
-> WHERE event.type = 'litter' ;
+--------+------+-----------------------------+
| name | age | remark |
+--------+------+-----------------------------+
| Fluffy | 2 | 4 kittens, 3 female, 1 male |
| Buffy | 4 | 5 puppies, 2 female, 3 male |
| Buffy | 5 | 3 puppies, 3 female |
+--------+------+-----------------------------+
3 rows in set (0.03 sec)
mysql>
關於上面這個查詢語句，有以下需要注意的地方：
There are several things to note about this query:
? The FROM clause joins two tables because the query needs to pull information from both of them.
? When combining (joining) information from multiple tables, you need to specify how records in one table can be matched to records in the other. This is easy because they both have a name column. The query uses an ON clause to match up records in the two tables based on the name values. The query uses an INNER JOIN to combine the tables. An INNER JOIN permits rows from either table to appear in the result if and only if both tables meet the conditions specified in the ON clause.
In this example, the ON clause specifies that the name column in the pet table must match the name
column in the event table. If a name appears in one table but not the other, the row will not appear
in the result because the condition in the ON clause fails.
? Because the name column occurs in both tables, you must be specific about which table you mean
when referring to the column. This is done by prepending the table name to the column name.
下面是一個表格自己和自己相連的情況
mysql> SELECT p1.name, p1.sex, p2.name, p2.sex, p1.species
-> FROM pet AS p1 INNER JOIN pet AS p2
-> ON p1.species = p2.species AND p1.sex = 'f'
-> AND p2.sex = 'm' ;
+--------+------+--------+------+---------+
| name | sex | name | sex | species |
+--------+------+--------+------+---------+
| Fluffy | f | Claws | m | cat |
| Buffy | f | Fang | m | dog |
| Buffy | f | Bowser | m | dog |
+--------+------+--------+------+---------+
3 rows in set (0.35 sec)
mysql>
這個例子其實就是配對——從這9只狗中挑出那些可以進行繁殖的那些狗狗。
具體的做法你可以想象成兩個相同的pet表進行兩層的for循環。也就是從第一個pet表中的第一個狗狗Fluffy開始依次和另一個相同的pet表中的從第一個到最後一個進行判定，看它們這兩個狗狗是否滿足這兩個條件：1，這兩個狗的品種相同；2，第一個表中的狗也即是前面的那只狗的sex是f，並且後面一只狗的sex為m。如果滿足這兩個條件，則配對成功，它們將會在結果中出現，否則不出現。
3.4-獲取數據庫和數據庫中的表格的信息1：查看當前正在被操作的數據庫是哪個？也就是查看current database是哪個
mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| test |
+------------+
1 row in set (0.05 sec)
mysql>
2：查看current database裡有哪些表格
mysql> SHOW TABLES;
+----------------+
| Tables_in_test |
+----------------+
| event |
| pet |
+----------------+
2 rows in set (0.00 sec)
mysql>
3：查看某個表格的結構是怎樣的？
mysql> DESCRIBE pet;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name | varchar(20) | YES | | NULL | |
| owner | varchar(20) | YES | | NULL | |
| species | varchar(20) | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
| birth | date | YES | | NULL | |
| death | date | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
mysql>
到這裡已經走完了MySQL文檔中第3章Tutorial的大部分的內容了。剩下的3.5， 3.6， 3.7明天再來吧。深夜了都。
