一 写在开头
1.1 本节内容本节的主要内容是MySQL的基本操作（来自MySQL 5.7官方文档）。1.2 工具准备一台装好了mysql的ubuntu 16.04 LTS机器。二 MySQL的连接与断开2.1 连接与断开MySQL服务MySQL提供了一个Linux命令行程序mysql，利用它我们可以连接到MySQL并执行SQL操作。想要查看mysql自带帮助文档，请使用以下命令。mysql --help
2.2.1 使用以下命令连接到MySQL
mysql -h localhost -u root -p
命令详解：
-h后面参数表示主机名。如果MySQL安装在了本地，后面的参数可以使用localhost表示本地。也可以省略-h参数。-u后面的参数表示使用的账户名。可以使用root用户，前提是你得记得安装MySQL时输入的root用户密码。-p参数表示需要输入密码。一般来说-p参数是必须的。2.1.2 使用quit命令退出mysql命令行quit
SELECT VERSION(), CURRENT_DATE();+-----------+----------------+| VERSION() | CURRENT_DATE() |+-----------+----------------+| 5.7.21 | 2018-02-14 |+-----------+----------------+1 row in set (0.06 sec)
注意：SQL语句后面有个分号，quit命令后没有分号。
3.2 使用MySQL做简单计算SELECT SIN(PI()/2), (4+1)*5;+-------------+---------+| SIN(PI()/2) | (4+1)*5 |+-------------+---------+| 1 | 25 |+-------------+---------+1 row in set (0.05 sec)
SELECT VERSION(); SELECT NOW();+-----------+| VERSION() |+-----------+| 5.7.21 |+-----------+1 row in set (0.00 sec)+---------------------+| NOW() |+---------------------+| 2018-02-14 15:11:56 |+---------------------+1 row in set (0.00 sec)
SELECT USER(),CURRENT_DATE();+----------------+----------------+| USER() | CURRENT_DATE() |+----------------+----------------+| root@localhost | 2018-02-14 |+----------------+----------------+1 row in set (0.05 sec)
注意：在MySQL中，识别一条SQL命令的标志是分号。所以我们可以把一条命令写在多行中。
3.5 撤销当前输入mysql> SELECT-> USER()-> \cmysql>
注意：c是小写的。
四 数据库的创建和使用4.1 查看当前系统中的数据库SHOW DATABASES;+--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema || sys |+--------------------+4 rows in set (0.15 sec)
注意：SHOW DATEBASES;只会将当前用户具有的SHOW DATABASES命令权限的数据库打印出来。
4.2 使用（进入)数据库USE sysReading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changed
注意：USE和前面的QUIT一样，后面是不接分号的。与之前命令不一样的是USE命令必须写在单独一行中，不能跨越多行。
4.3 创建一个数据库
CREATE DATABASE menagerie;Query OK, 1 row affected (0.07 sec)
注意：在Linux下数据库名是大小写敏感的。
4.4 在连接MySQL时就指定使用menagerie数据库
mysql -u root -p menagerie
SELECT DATABASE();+------------+| DATABASE() |+------------+| menagerie |+------------+1 row in set (0.00 sec)
SELECT TABLES;Empty set (0.00 sec)
注意：刚刚创建的数据库中是没有表的。
4.7 创建表格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.15 sec)mysql> SHOW TABLES;+---------------------+| Tables_in_menagerie |+---------------------+| pet |+---------------------+1 row in set (0.01 sec)
4.8 查看表的信息
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)
INSERT INTO petVALUES ('Fluffy', 'Harold', 'cat', 'f', '1993-02-04', NULL);INSERT INTO petVALUES ('Claws', 'Gwen', 'cat', 'm', '1994-03-17', NULL);INSERT INTO petVALUES ('Buffy', 'Harold', 'dog', 'f', '1989-05-13', NULL);INSERT INTO petVALUES ('Fang', 'Benny', 'dog', 'm', '1990-08-27', NULL);INSERT INTO petVALUES ('Bowser', 'Diane', 'dog', 'm', '1979-08-31', '1995-07-29');INSERT INTO petVALUES ('Chirpy', 'Gwen', 'bird', 'f', '1998-09-11', NULL);INSERT INTO petVALUES ('Whistler', 'Gwen', 'bird', NULL, '1997-12-09', NULL);INSERT INTO petVALUES ('Slim', 'Benny', 'snake', 'm', '1996-04-29', NULL);INSERT INTO petVALUES ('Puffball', 'Diane', 'hamster', 'f', '1999-03-30', NULL); 第二步：使用下列命令执行sql文件，随后查看表的内容。
mysql> source ~/pet.sqlQuery OK, 1 row affected (0.13 sec)Query OK, 1 row affected (0.01 sec)Query OK, 1 row affected (0.02 sec)Query OK, 1 row affected (0.01 sec)Query OK, 1 row affected (0.01 sec)Query OK, 1 row affected (0.00 sec)Query OK, 1 row affected (0.00 sec)Query OK, 1 row affected (0.00 sec)Query OK, 1 row affected (0.01 sec)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 | 1998-09-11 | NULL || Whistler | Gwen | bird | NULL | 1997-12-09 | NULL || Slim | Benny | snake | m | 1996-04-29 | NULL || Puffball | Diane | hamster | f | 1999-03-30 | NULL |+----------+--------+---------+------+------------+------------+9 rows in set (0.00 sec)
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 | 1998-09-11 | NULL || Whistler | Gwen | bird | NULL | 1997-12-09 | NULL || Slim | Benny | snake | m | 1996-04-29 | NULL || Puffball | Diane | hamster | f | 1999-03-30 | NULL |+----------+--------+---------+------+------------+------------+9 rows in set (0.00 sec)
mysql> UPDATE pet SET birth = '1989-08-31' WHERE name = 'Bowser';Query OK, 1 row affected (0.17 sec)Rows matched: 1 Changed: 1 Warnings: 0mysql> SELECT birth FROM pet WHERE name = 'Bowser';+------------+| birth |+------------+| 1989-08-31 |+------------+1 row in set (0.00 sec)
mysql> DELETE FROM pet;Query OK, 9 rows affected (0.08 sec)mysql> SELECT * FROM pet;Empty set (0.00 sec)
5.4 选择特定行
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)
5.5 根据条件选择
mysql> SELECT * FROM pet WHERE birth >= '1998-1-1';+----------+-------+---------+------+------------+-------+| name | owner | species | sex | birth | death |+----------+-------+---------+------+------------+-------+| Chirpy | Gwen | bird | f | 1998-09-11 | NULL || Puffball | Diane | hamster | f | 1999-03-30 | NULL |+----------+-------+---------+------+------------+-------+2 rows in set (0.00 sec)
5.6 根据组合条件选择5.6.1
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.04 sec)
5.6.2
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.00 sec)
注意：AND优先级高于OR。
5.7 选择特定列5.7.1mysql> 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 || Whistler | 1997-12-09 || Slim | 1996-04-29 || Puffball | 1999-03-30 |+----------+------------+9 rows in set (0.00 sec)
mysql> SELECT owner FROM pet;+--------+| owner |+--------+| Harold || Gwen || Harold || Benny || Diane || Gwen || Gwen || Benny || Diane |+--------+9 rows in set (0.00 sec)
mysql> SELECT DISTINCT owner FROM pet;+--------+| owner |+--------+| Harold || Gwen || Benny || Diane |+--------+4 rows in set (0.05 sec)
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.00 sec)
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 || Whistler | 1997-12-09 || Chirpy | 1998-09-11 || Puffball | 1999-03-30 |+----------+------------+9 rows in set (0.00 sec)
5.10 使用DESC关键字进行降序排列
mysql> SELECT name, birth FROM pet ORDER BY birth DESC;+----------+------------+| name | birth |+----------+------------+| Puffball | 1999-03-30 || Chirpy | 1998-09-11 || Whistler | 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)
5.11 对多个列使用不同的排序顺序
mysql> SELECT name, species, birth FROM pet ORDER BY species, birth DESC;+----------+---------+------------+| name | species | birth |+----------+---------+------------+| Chirpy | bird | 1998-09-11 || Whistler | 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 || Puffball | hamster | 1999-03-30 || Slim | snake | 1996-04-29 |+----------+---------+------------+9 rows in set (0.00 sec)
六 常用的计算函数6.1 TIMESTAMPDIFF()函数6.1.1 TIMESTAMPDIFF()
mysql> SELECT name, birth, CURDATE(), -> TIMESTAMPDIFF(YEAR, birth, CURDATE()) AS age -> FROM pet;+----------+------------+------------+------+| name | birth | CURDATE() | age |+----------+------------+------------+------+| Fluffy | 1993-02-04 | 2018-02-15 | 25 || Claws | 1994-03-17 | 2018-02-15 | 23 || Buffy | 1989-05-13 | 2018-02-15 | 28 || Fang | 1990-08-27 | 2018-02-15 | 27 || Bowser | 1989-08-31 | 2018-02-15 | 28 || Chirpy | 1998-09-11 | 2018-02-15 | 19 || Whistler | 1997-12-09 | 2018-02-15 | 20 || Slim | 1996-04-29 | 2018-02-15 | 21 || Puffball | 1999-03-30 | 2018-02-15 | 18 |+----------+------------+------------+------+9 rows in set (0.05 sec)
mysql> SELECT name, birth, CURDATE(), -> TIMESTAMPDIFF(YEAR, birth, CURDATE()) AS age -> FROM pet ORDER BY name;+----------+------------+------------+------+| name | birth | CURDATE() | age |+----------+------------+------------+------+| Bowser | 1989-08-31 | 2018-02-15 | 28 || Buffy | 1989-05-13 | 2018-02-15 | 28 || Chirpy | 1998-09-11 | 2018-02-15 | 19 || Claws | 1994-03-17 | 2018-02-15 | 23 || Fang | 1990-08-27 | 2018-02-15 | 27 || Fluffy | 1993-02-04 | 2018-02-15 | 25 || Puffball | 1999-03-30 | 2018-02-15 | 18 || Slim | 1996-04-29 | 2018-02-15 | 21 || Whistler | 1997-12-09 | 2018-02-15 | 20 |+----------+------------+------------+------+9 rows in set (0.00 sec)
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)
6.2 MONTH()函数
6.2.1 MONTH()mysql> SELECT name, birth, MONTH(birth) FROM pet;+----------+------------+--------------+| name | birth | MONTH(birth) |+----------+------------+--------------+| Fluffy | 1993-02-04 | 2 || Claws | 1994-03-17 | 3 || Buffy | 1989-05-13 | 5 || Fang | 1990-08-27 | 8 || Bowser | 1989-08-31 | 8 || Chirpy | 1998-09-11 | 9 || Whistler | 1997-12-09 | 12 || Slim | 1996-04-29 | 4 || Puffball | 1999-03-30 | 3 |+----------+------------+--------------+9 rows in set (0.00 sec)
注意：受上面结果的启发，YEAR(birth)，DAYOFMONTH(birth)的结果是什么呢？
6.2.2 找到5月出生的动物mysql> SELECT name, birth FROM pet WHERE MONTH(birth) = 5;+-------+------------+| name | birth |+-------+------------+| Buffy | 1989-05-13 |+-------+------------+1 row in set (0.05 sec)
6.2.3 DATE_ADD()
mysql> SELECT name, birth FROM pet -> WHERE MONTH(birth) = MONTH(DATE_ADD(CURDATE(), INTERVAL 1 MONTH));+----------+------------+| name | birth |+----------+------------+| Claws | 1994-03-17 || Puffball | 1999-03-30 |+----------+------------+2 rows in set (0.08 sec)
6.3 如何处理为空值
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)
七 模式匹配7.1 匹配规则_（下划线）- 表示单个字符%（百分号） - 表示任意个字符注意：模式匹配时是大小写敏感的，匹配使用的运算符是LIKE和NOT LIKE。不能使用数学运算符。7.2 小例子7.2.1 找到以字母b开头的宠物
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)
7.2.2 找到以fy结尾的宠物
mysql> SELECT * FROM pet WHERE name LIKE '%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> SELECT * FROM pet WHERE name LIKE '%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 || Whistler | Gwen | bird | NULL | 1997-12-09 | NULL |+----------+-------+---------+------+------------+------------+3 rows in set (0.00 sec)
7.2.4 找到名字为5个字符的宠物
mysql> SELECT * FROM pet WHERE name LIKE '_____';+-------+--------+---------+------+------------+-------+| 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)
八 行计数8.1 COUNT()函数8.1.1 共有多少宠物
mysql> SELECT COUNT(*) FROM pet;+----------+| COUNT(*) |+----------+| 9 |+----------+1 row in set (0.04 sec)
8.1.2 每个主人共有几只宠物
mysql> SELECT owner, COUNT(*) FROM pet GROUP BY owner;+--------+----------+| owner | COUNT(*) |+--------+----------+| Benny | 2 || Diane | 2 || Gwen | 3 || Harold | 2 |+--------+----------+4 rows in set (0.05 sec)
8.1.3 每个物种共有几只宠物
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)
8.1.4 每种性别共有几只宠物
mysql> SELECT sex, COUNT(*) FROM pet GROUP BY sex;+------+----------+| sex | COUNT(*) |+------+----------+| NULL | 1 || f | 4 || m | 4 |+------+----------+3 rows in set (0.00 sec)
8.1.5 每种物种和性别共有几只宠物
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)
8.1.6 只考虑狗和猫的情况下，每种物种和性别共有几只宠物
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.06 sec)
8.1.7 排除性别未知的宠物情况下，每种物种和性别共有几只宠物
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)
九 使用多个表9.1 创建一个event表以记录宠物的其他信息
mysql> CREATE TABLE event -> ( -> name VARCHAR(20), -> date DATE, -> type VARCHAR(15), -> remark VARCHAR(255) -> );Query OK, 0 rows affected (0.49 sec)
9.2 往表中插入数据
INSERT INTO eventVALUES ('Fluffy', '1995-05-15', 'litter', '4 kittens, 3 female, 1 male');INSERT INTO eventVALUES ('Buffy', '1993-06-23', 'litter', '5 puppies, 2 female, 3 male');INSERT INTO eventVALUES ('Buffy', '1994-06-19', 'litter', '3 puppies, 3 female');INSERT INTO eventVALUES ('Chirpy', '1999-03-21', 'vet', 'needed beak straightened');INSERT INTO eventVALUES ('Slim', '1997-08-03', 'vet', 'broken rib');INSERT INTO eventVALUES ('Bowser', '1991-10-12', 'kennel', NULL);INSERT INTO eventVALUES ('Fang', '1991-10-12', 'kennel', NULL);INSERT INTO eventVALUES ('Fang', '1998-08-28', 'birthday', 'Gave him a new chew toy');INSERT INTO eventVALUES ('Claws', '1998-03-17', 'birthday', 'Gave him a new flea collar');INSERT INTO eventVALUES ('Whistler', '1998-12-09', 'birthday', 'First birthday'); 9.3 多表连接9.3.1
mysql> SELECT pet.name -> , -> (YEAR(date)-YEAR(birth)) - (RIGHT(date, 5) < RIGHT(birth, 5)) AS age, -> 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.16 sec)
9.3.2
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.00 sec)
十 获取数据库信息10.1
| 语句 | 功能 |
| SHOW DATABASES; | 显示MySQL Server所管理的数据库 |
| SELECT DATABASE(); | 当前选择的是哪个数据库 |
| SHOW TABLES; | 当前数据库中有哪些表 |
| DESCRIBE pet; | 显示pet表的表结构 |