极客教程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 帐户来创建新数据库，并为我们的新数据库的测试帐户授予权限。
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测试帐户并更改为 world 数据库。 我们准备工作。
检查数据库
在本节中，我们将大致看一下 world 数据库的表。
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表具有四千多行，因此我们无法在一个屏幕中看到它们。 我们可以使用 pager 命令以较少的程序显示数据。 我们可以使用光标键或向下翻页，向上翻页键浏览数据。 如果我们想使用默认设置，只需点击不带任何参数的寻呼机即可。
$ mysql -u user12 -p world -e "SELECT * FROM city" > city
Enter password:
$ ls -sh city
144K city
mysql命令工具可以非交互方式使用。 我们在-e选项之后指定 SQL 语句，然后将结果重定向到城市文件。 现在我们可以使用任何文本编辑器来显示数据。
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运算符。 我们发现了 13 个城市，人口在 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)
如前所述，世界数据库已过时。 乔治·布什（George Bush）不再是美国总统。
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)
我们从数据库中删除该表。