MySQL 快速教程
最后修改于 2023 年 1 月 10 日
MySQL 快速教程将快速向您介绍 MySQL 数据库的基础知识。
世界数据库
MySQL 文档 网站提供了一些示例数据库。我们将使用一个世界数据库。数据已过时，但这对于我们的测试目的来说并不重要。
$ wget http://downloads.mysql.com/docs/world.sql.gz
我们使用 wget 工具下载压缩文件。
$ ls -sh world.sql.gz 92K world.sql.gz
压缩文件大约有 92KB。
$ gunzip world.sql.gz
我们解压该文件。我们有一个 world.sql 文件。
$ ls -hs world.sql 392K world.sql
未压缩的文件有 392KB。
$ mysql -uroot -p
我们使用 root 帐户连接到服务器。我们需要 root 帐户来创建一个新数据库，并为我们的测试帐户提供对新数据库的权限。
mysql> CREATE DATABASE world;
世界数据库已创建。
mysql> USE world;
我们切换到世界数据库。现在，世界数据库是当前数据库。
mysql> source world.sql
我们通过执行 world.sql SQL 脚本来构建世界数据库的表。这需要一些时间。
mysql> GRANT ALL ON world.* TO 'user12'@'localhost';
我们将世界数据库的所有对象的权限授予 user12。
mysql> quit Bye $ mysql -u user12 -p Enter password: mysql> USE world;
我们退出连接。使用 user12 测试帐户重新连接并切换到世界数据库。我们准备好工作了。
检查数据库
在本节中，我们将总览世界数据库的表。
mysql> SHOW TABLES; +-----------------+ | Tables_in_world | +-----------------+ | city | | country | | countrylanguage | +-----------------+ 3 rows in set (0,00 sec)
我们使用 SHOW TABLES 语句显示所有可用的表。有三个。
mysql> DESCRIBE city; +-------------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+----------+------+-----+---------+----------------+ | ID | int(11) | NO | PRI | NULL | auto_increment | | Name | char(35) | NO | | | | | CountryCode | char(3) | NO | MUL | | | | District | char(20) | NO | | | | | Population | int(11) | NO | | 0 | | +-------------+----------+------+-----+---------+----------------+ 5 rows in set (0,00 sec)
使用 DESCRIBE 语句，我们可以看到 City 表的表结构。我们看到列名及其数据类型。以及其他重要信息。
mysql> SHOW CREATE TABLE city;
如果我们想找出创建 City 表的 SQL，我们将发出 SHOW CREATE TABLE city 语句。
$ mysqldump -u root -p world city > city.sql
这里我们使用 mysqldump 工具备份 city 表。
mysql> DROP TABLE city; mysql> SHOW TABLES; +-----------------+ | Tables_in_world | +-----------------+ | country | | countrylanguage | +-----------------+ 2 rows in set (0,00 sec)
我们使用 DROP TABLE 语句删除 city 表。后续语句验证该表已删除。
mysql> source city.sql mysql> SHOW TABLES; +-----------------+ | Tables_in_world | +-----------------+ | city | | country | | countrylanguage | +-----------------+ 3 rows in set (0,00 sec)
我们从备份中重新创建 city 表。 source 命令执行备份 city.sql 脚本。
查询
查询用于从数据库表中查找数据。
限制数据输出
数据库的表中可能有成千上万行。它们不能全部显示在屏幕上。我们可以使用 LIMIT 子句控制要显示的行数。
mysql> SELECT Id, Name, Population FROM city limit 10; +----+----------------+------------+ | Id | Name | Population | +----+----------------+------------+ | 1 | Kabul | 1780000 | | 2 | Qandahar | 237500 | | 3 | Herat | 186800 | | 4 | Mazar-e-Sharif | 127800 | | 5 | Amsterdam | 731200 | | 6 | Rotterdam | 593321 | | 7 | Haag | 440900 | | 8 | Utrecht | 234323 | | 9 | Eindhoven | 201843 | | 10 | Tilburg | 193238 | +----+----------------+------------+ 10 rows in set (0,00 sec)
在上面的查询中，我们显示了 City 表的五个列中的三列。表中有很多行。我们将查询限制为前 10 行。
mysql> SELECT Id, Name, Population FROM city limit 15, 5; +----+-------------------+------------+ | Id | Name | Population | +----+-------------------+------------+ | 16 | Haarlem | 148772 | | 17 | Almere | 142465 | | 18 | Arnhem | 138020 | | 19 | Zaanstad | 135621 | | 20 | ´s-Hertogenbosch | 129170 | +----+-------------------+------------+ 5 rows in set (0,00 sec)
LIMIT 子句后可以跟两个数字。第一个是偏移量，第二个是要显示的行数。我们的查询显示第 16-20 行。
mysql> pager less PAGER set to 'less' mysql> SELECT * FROM city; +------------------------------------+------------+ | Name | Population | +------------------------------------+------------+ | Kabul | 1780000 | | Qandahar | 237500 | | Herat | 186800 | ... :
由于 city 表有 4000 多行，我们无法在一个屏幕上看到它们。我们可以使用分页命令以较少的程序显示数据。我们可以使用光标键或 Page Down、Page Up 键浏览数据。如果我们想回到默认设置，只需在没有任何参数的情况下点击分页器即可。
$ mysql -u user12 -p world -e "SELECT * FROM city" > city Enter password: $ ls -sh city 144K city
mysql 命令工具可以以非交互方式使用。我们在 -e 选项后指定 SQL 语句，并将结果重定向到 city 文件。现在我们可以使用任何文本编辑器来显示数据。
COUNT()、MAX()、MIN() 函数
COUNT()、MAX()、MIN() 是 MySQL 聚合函数，用于从聚合数据中计算某个值。
mysql> SELECT COUNT(Id) AS '# of cities' FROM city; +-------------+ | # of cities | +-------------+ | 4079 | +-------------+ 1 row in set (0,00 sec)
表中共有 4079 个城市。我们使用内置的 COUNT() 函数来找出行数。
mysql> SELECT Name, Population FROM city
-> WHERE Population = (SELECT Max(Population) FROM city);
+-----------------+------------+
| Name | Population |
+-----------------+------------+
| Mumbai (Bombay) | 10500000 |
+-----------------+------------+
1 row in set (0,08 sec)
上面的查询显示了表中人口最多的城市。 SQL 是一种特殊类型的查询，称为子查询。外部查询使用内部查询返回的数据。内部查询由括号括起来。
mysql> SELECT Name, Population FROM city
-> WHERE Population = (SELECT Min(Population) FROM city);
+-----------+------------+
| Name | Population |
+-----------+------------+
| Adamstown | 42 |
+-----------+------------+
1 row in set (0,02 sec)
此子查询显示了表中人口最少的城市。
使用 WHERE 子句选择特定行
WHERE 子句可用于过滤结果。它提供了一个选择标准，仅从数据中选择特定行。
mysql> SELECT Name, Population FROM city
-> WHERE Population > 1000000;
+--------------------------+------------+
| Name | Population |
+--------------------------+------------+
| Kabul | 1780000 |
| Alger | 2168000 |
| Luanda | 2022000 |
| Buenos Aires | 2982146 |
| La Matanza | 1266461 |
| Córdoba | 1157507 |
| Yerevan | 1248700 |
| Sydney | 3276207 |
...
上面的 SQL 语句返回人口超过一百万的所有城市。
mysql> SELECT Name FROM city WHERE Name LIKE 'Kal%'; +-------------+ | Name | +-------------+ | Kalookan | | Kalyan | | Kalemie | | Kallithea | | Kalisz | | Kaliningrad | | Kaluga | +-------------+ 7 rows in set (0,00 sec)
在这里，我们选择所有以“Kal”开头的城市名称。我们已在表中找到七个城市。我们可以使用 LIKE 子句在列中查找特定模式。
mysql> SELECT Name, Population FROM city WHERE ID IN (5, 23, 432, 2021); +------------+------------+ | Name | Population | +------------+------------+ | Amsterdam | 731200 | | Dordrecht | 119811 | | Eunápolis | 96610 | | Jining | 265248 | +------------+------------+ 4 rows in set (0,05 sec)
此 SQL 代码返回 ID 为 5、23、432 和 2021 的行的城市及其人口。
mysql> SELECT * FROM city WHERE Name = 'Bratislava'; +------+------------+-------------+------------+------------+ | ID | Name | CountryCode | District | Population | +------+------------+-------------+------------+------------+ | 3209 | Bratislava | SVK | Bratislava | 448292 | +------+------------+-------------+------------+------------+ 1 row in set (0,00 sec)
使用上面的 SQL 语句，我们选择一个特定城市（即布拉迪斯拉发）的所有列。
mysql> SELECT Name, Population FROM city
-> WHERE Population BETWEEN 670000 AND 700000;
+----------------+------------+
| Name | Population |
+----------------+------------+
| Teresina | 691942 |
| Natal | 688955 |
| Bandar Lampung | 680332 |
| Gwalior | 690765 |
| Kermanshah | 692986 |
| Palermo | 683794 |
| Toronto | 688275 |
| Huainan | 700000 |
| Jixi | 683885 |
| Antananarivo | 675669 |
| Chihuahua | 670208 |
| Kano | 674100 |
| Tunis | 690600 |
+----------------+------------+
13 rows in set (0,03 sec)
假设我们想找出人口介于两个特定值之间的城市。有一个 BETWEEN 运算符。我们找到了十三个人口在 670,000 和 700,000 之间的城市。
排序数据
可以使用 ORDER BY 子句对数据进行排序。
mysql> SELECT Name, Population FROM city
-> ORDER BY Population DESC LIMIT 10;
+-------------------+------------+
| Name | Population |
+-------------------+------------+
| Mumbai (Bombay) | 10500000 |
| Seoul | 9981619 |
| São Paulo | 9968485 |
| Shanghai | 9696300 |
| Jakarta | 9604900 |
| Karachi | 9269265 |
| Istanbul | 8787958 |
| Ciudad de México | 8591309 |
| Moscow | 8389200 |
| New York | 8008278 |
+-------------------+------------+
10 rows in set (0,03 sec)
我们找到了人口最多的十个城市。我们按人口从最多到最少的顺序对数据进行排序。我们使用 LIMIT 子句限制输出。
mysql> SELECT Name, Population FROM city
-> ORDER BY Population ASC LIMIT 10;
+---------------------+------------+
| Name | Population |
+---------------------+------------+
| Adamstown | 42 |
| West Island | 167 |
| Fakaofo | 300 |
| Città del Vaticano | 455 |
| Bantam | 503 |
| Yaren | 559 |
| The Valley | 595 |
| Alofi | 682 |
| Flying Fish Cove | 700 |
| Kingston | 800 |
+---------------------+------------+
10 rows in set (0,02 sec)
在这里，我们得到了人口最少的城市。这次我们按升序对数据进行排序。为此，我们使用 ASC 关键字。
mysql> SELECT Name, Population FROM city ORDER By Name LIMIT 10; +------------------------+------------+ | Name | Population | +------------------------+------------+ | A Coruña (La Coruña) | 243402 | | Aachen | 243825 | | Aalborg | 161161 | | Aba | 298900 | | Abadan | 206073 | | Abaetetuba | 111258 | | Abakan | 169200 | | Abbotsford | 105403 | | Abeokuta | 427400 | | Aberdeen | 213070 | +------------------------+------------+ 10 rows in set (0.01 sec)
在上面的 SQL 语句中，我们按城市名称对数据进行排序，并获得前十个城市。
分组数据
GROUP BY 子句用于将具有相同值的数据库记录组合成单个记录。它通常与聚合函数一起使用。
mysql> SELECT District, SUM(Population) FROM city
-> WHERE District = 'New York' GROUP BY District;
+----------+-----------------+
| District | SUM(Population) |
+----------+-----------------+
| New York | 8958085 |
+----------+-----------------+
1 row in set (0,09 sec)
上面的 SQL 语句返回了纽约区城镇的总人口数，这些城镇在我们的数据库中列出。
mysql> SELECT Name, District FROM city WHERE District = 'New York'; +-----------+----------+ | Name | District | +-----------+----------+ | New York | New York | | Buffalo | New York | | Rochester | New York | | Yonkers | New York | | Syracuse | New York | | Albany | New York | +-----------+----------+ 6 rows in set (0,00 sec)
之前的数字是这六个城市的总和。
mysql> SELECT District, SUM(Population) FROM city
-> WHERE CountryCode = 'USA' GROUP BY District
-> HAVING SUM(Population) > 3000000;
+------------+-----------------+
| District | SUM(Population) |
+------------+-----------------+
| Arizona | 3178903 |
| California | 16716706 |
| Florida | 3151408 |
| Illinois | 3737498 |
| New York | 8958085 |
| Texas | 9208281 |
+------------+-----------------+
6 rows in set (0,28 sec)
我们选择人口超过 300 万的所有地区。当我们处理数据组时，我们使用 HAVING 子句而不是 WHERE 子句。
更新、删除和插入数据
接下来，我们将关注更新、删除和插入数据。
mysql> SELECT Name, HeadOfState FROM country WHERE Name = 'United States'; +---------------+----------------+ | Name | HeadOfState | +---------------+----------------+ | United States | George W. Bush | +---------------+----------------+ 1 row in set (0,12 sec)
正如我们已经说过的那样，世界数据库已经过时。乔治·布什不再是美国总统了。
mysql> UPDATE country SET HeadOfState = 'Donald Trump'
-> WHERE Name = 'United States';
使用 UPDATE 语句，我们将行更改为实际数据。
mysql> SELECT Name, HeadOfState FROM country WHERE Name = 'United States'; +---------------+--------------+ | Name | HeadOfState | +---------------+--------------+ | United States | Donald Trump | +---------------+--------------+ 1 row in set (0,02 sec)
我们已经成功更新了该行。
mysql> CREATE TABLE toptencities engine=MEMORY SELECT * FROM city LIMIT 10;
我们在内存中创建一个临时表。它将包含 city 表的前十个城市。
mysql> SELECT * FROM toptencities; +----+----------------+-------------+---------------+------------+ | ID | Name | CountryCode | District | Population | +----+----------------+-------------+---------------+------------+ | 1 | Kabul | AFG | Kabol | 1780000 | | 2 | Qandahar | AFG | Qandahar | 237500 | | 3 | Herat | AFG | Herat | 186800 | | 4 | Mazar-e-Sharif | AFG | Balkh | 127800 | | 5 | Amsterdam | NLD | Noord-Holland | 731200 | | 6 | Rotterdam | NLD | Zuid-Holland | 593321 | | 7 | Haag | NLD | Zuid-Holland | 440900 | | 8 | Utrecht | NLD | Utrecht | 234323 | | 9 | Eindhoven | NLD | Noord-Brabant | 201843 | | 10 | Tilburg | NLD | Noord-Brabant | 193238 | +----+----------------+-------------+---------------+------------+ 10 rows in set (0,00 sec)
这是 toptencities 表的内容。
mysql> DELETE FROM toptencities WHERE ID IN (2, 4, 6, 8, 10);
使用 DELETE FROM 语句和 WHERE 子句，我们从 toptencities 表中删除每隔一行。
mysql> SELECT * FROM toptencities; +----+-----------+-------------+---------------+------------+ | ID | Name | CountryCode | District | Population | +----+-----------+-------------+---------------+------------+ | 1 | Kabul | AFG | Kabol | 1780000 | | 3 | Herat | AFG | Herat | 186800 | | 5 | Amsterdam | NLD | Noord-Holland | 731200 | | 7 | Haag | NLD | Zuid-Holland | 440900 | | 9 | Eindhoven | NLD | Noord-Brabant | 201843 | +----+-----------+-------------+---------------+------------+ 5 rows in set (0.00 sec)
表中还剩五行。
mysql> TRUNCATE TABLE toptencities; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM toptencities; Empty set (0,00 sec)
我们使用 TRUNCATE 语句删除表中的所有行。没有剩余数据。
mysql> INSERT INTO toptencities VALUES(1, 'Kabul', 'AFG', 'Kabol', 1780000); mysql> SELECT * FROM toptencities;; +----+-------+-------------+----------+------------+ | ID | Name | CountryCode | District | Population | +----+-------+-------------+----------+------------+ | 1 | Kabul | AFG | Kabol | 1780000 | +----+-------+-------------+----------+------------+ 1 row in set (0.00 sec)
使用 INSERT INTO 语句，我们将一行插入到表中。
mysql> DROP TABLE toptencities; Query OK, 0 rows affected (0,06 sec)
我们从数据库中删除该表。
在本章中，我们快速介绍了 MySQL 数据库的一些基础知识。我们将在后续章节中更详细地介绍。