MySQL 快速教程

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)

我们从数据库中删除该表。

赞(0)

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址