概述
本章，您将了解基础使用与数据导入。
数据导入之后，方便后面文档的演示以及说明。
SQL 语法
在 MySQL 中，基础的 SQL 语法规则：
- 默认情况下，在 GNU/Linux 中，表名、库名区分大小写，字段名称与关键字不区分大小写
-
默认以
;作为分隔符，表示 SQL 语句的结束，当然，你可以指定其他的分隔符 -
当键入的语句特别长时，可使用回车键进行换行，例如：
Mysql > select * -> from db; -
注释
- 单行注释以
#或者--开头 - 多行注释则用
/* */包裹注释内容
- 单行注释以
初识
先将 MySQL 服务启动：
Shell > /usr/local/mysql8/bin/mysqld_safe --user=mysql &使用命令行进行连接：
# 在前面的文档中，我们已经创建了 jimcat8 这个用户
## 如您所见，我们来到了 MySQL 的交互终端
Shell > /usr/local/mysql8/bin/mysql -h 192.168.100.20 -u jimcat8 --password="RockyStar8.10!"
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.4.6 Source distribution
Copyright (c) 2000, 2025, Oracle and/or its affiliates.
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>基础使用：
# 显示所有的库
Mysql > show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.01 sec)
# 使用指定库
Mysql > use mysql;
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 > show tables from mysql;
+------------------------------------------------------+
| Tables_in_mysql |
+------------------------------------------------------+
| columns_priv |
| component |
| db |
| default_roles |
| engine_cost |
| func |
| general_log |
| global_grants |
| gtid_executed |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| ndb_binlog_index |
| password_history |
| plugin |
| procs_priv |
| proxies_priv |
| replication_asynchronous_connection_failover |
| replication_asynchronous_connection_failover_managed |
| replication_group_configuration_version |
| replication_group_member_actions |
| role_edges |
| server_cost |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+------------------------------------------------------+
38 rows in set (0.00 sec)
# 显示 MySQL 当前的基本信息
Mysql > status;
--------------
/usr/local/mysql8/bin/mysql Ver 8.4.6 for Linux on x86_64 (Source distribution)
Connection id: 9
Current database: mysql
Current user: [email protected]
SSL: Cipher in use is TLS_AES_128_GCM_SHA256
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 8.4.6 Source distribution
Protocol version: 10
Connection: 192.168.100.20 via TCP/IP
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: utf8mb4
Conn. characterset: utf8mb4
TCP port: 3306
Binary data as: Hexadecimal
Uptime: 11 min 44 sec
Threads: 2 Questions: 56 Slow queries: 0 Opens: 177 Flush tables: 3 Open tables: 96 Queries per second avg: 0.079
--------------
# 查看表的结构信息
Mysql > desc mysql.user;
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host | char(255) | NO | PRI | | |
| User | char(32) | NO | PRI | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Reload_priv | enum('N','Y') | NO | | N | |
| Shutdown_priv | enum('N','Y') | NO | | N | |
| Process_priv | enum('N','Y') | NO | | N | |
| File_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Show_db_priv | enum('N','Y') | NO | | N | |
| Super_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Repl_slave_priv | enum('N','Y') | NO | | N | |
| Repl_client_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Create_user_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
| Create_tablespace_priv | enum('N','Y') | NO | | N | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |
| ssl_cipher | blob | NO | | NULL | |
| x509_issuer | blob | NO | | NULL | |
| x509_subject | blob | NO | | NULL | |
| max_questions | int unsigned | NO | | 0 | |
| max_updates | int unsigned | NO | | 0 | |
| max_connections | int unsigned | NO | | 0 | |
| max_user_connections | int unsigned | NO | | 0 | |
| plugin | char(64) | NO | | caching_sha2_password | |
| authentication_string | text | YES | | NULL | |
| password_expired | enum('N','Y') | NO | | N | |
| password_last_changed | timestamp | YES | | NULL | |
| password_lifetime | smallint unsigned | YES | | NULL | |
| account_locked | enum('N','Y') | NO | | N | |
| Create_role_priv | enum('N','Y') | NO | | N | |
| Drop_role_priv | enum('N','Y') | NO | | N | |
| Password_reuse_history | smallint unsigned | YES | | NULL | |
| Password_reuse_time | smallint unsigned | YES | | NULL | |
| Password_require_current | enum('N','Y') | YES | | NULL | |
| User_attributes | json | YES | | NULL | |
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+
51 rows in set (0.00 sec)数据导入
在 这个链接 中，MySQL 官方为我们提供了示例数据库，我这里使用的是 world.sql 这个文件：
Shell > wget https://downloads.mysql.com/docs/world-db.tar.gz
# 解压并提取文件
Shell > tar -vxf world-db.tar.gz -C /usr/local/src/
world-db/
world-db/world.sql
# 导入数据库
Shell > /usr/local/mysql8/bin/mysql -h 192.168.100.20 -u jimcat8 --password="RockyStar8.10!"
Mysql > source /usr/local/src/world-db/world.sql;
Mysql > show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| world |
+--------------------+
5 rows in set (0.00 sec)
Mysql > use world;
Mysql > show tables from world;
+-----------------+
| Tables_in_world |
+-----------------+
| city |
| country |
| countrylanguage |
+-----------------+
3 rows in set (0.00 sec)如你所见，这个 world 库中有三张表，它们的结构为：
Mysql > desc city;
+-------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+----------------+
| ID | int | NO | PRI | NULL | auto_increment |
| Name | char(35) | NO | | | |
| CountryCode | char(3) | NO | MUL | | |
| District | char(20) | NO | | | |
| Population | int | NO | | 0 | |
+-------------+----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
Mysql > desc country;
+----------------+---------------------------------------------------------------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+---------------------------------------------------------------------------------------+------+-----+---------+-------+
| Code | char(3) | NO | PRI | | |
| Name | char(52) | NO | | | |
| Continent | enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') | NO | | Asia | |
| Region | char(26) | NO | | | |
| SurfaceArea | decimal(10,2) | NO | | 0.00 | |
| IndepYear | smallint | YES | | NULL | |
| Population | int | NO | | 0 | |
| LifeExpectancy | decimal(3,1) | YES | | NULL | |
| GNP | decimal(10,2) | YES | | NULL | |
| GNPOld | decimal(10,2) | YES | | NULL | |
| LocalName | char(45) | NO | | | |
| GovernmentForm | char(45) | NO | | | |
| HeadOfState | char(60) | YES | | NULL | |
| Capital | int | YES | | NULL | |
| Code2 | char(2) | NO | | | |
+----------------+---------------------------------------------------------------------------------------+------+-----+---------+-------+
15 rows in set (0.00 sec)
Mysql > desc countrylanguage;
+-------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| CountryCode | char(3) | NO | PRI | | |
| Language | char(30) | NO | PRI | | |
| IsOfficial | enum('T','F') | NO | | F | |
| Percentage | decimal(4,1) | NO | | 0.0 | |
+-------------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)city 表的字段说明
| 字段名称 | 说明 |
|---|---|
| ID | 城市的 ID 号 |
| name | 城市的名称 |
| CountryCode | 国家的代码 |
| District | 所属地区 |
| Population | 人口数量 |
默认情况下，在 GNU/Linux 中，表名、库名区分大小写，字段名称与关键字不区分大小写
country 表的字段说明
| 字段名称 | 说明 |
|---|---|
| Code | 国家代码 |
| Name | 国家名称 |
| Continent | 国家所属的大洲 |
| Region | 所属区域 |
| SurfaceArea | 表面积 |
| IndepYear | 国家独立时间 |
| Population | 人口数量 |
| LifeExpectancy | 人口的平均寿命 |
| GNP | 国民生产总值 |
| GNPOld | 旧的国民生产总值 |
| LocalName | 本地的国家名称 |
| GovernmentForm | 政府形式 |
| HeadOfState | 国家元首 |
| Capital | 资本金 |
| Code2 | 国家代码 |
countrylanguage 表的字段说明
| 字段名称 | 说明 |
|---|---|
| CountryCode | 国家代码 |
| Language | 国家使用的语言 |
| IsOfficial | 是否使用方言 |
| Percentage | 语言占有的百分比 |
最后
了解了这些表信息，下一章，我们将进入到 DQL (Data Query Language) 的内容。
版权声明:「自由转载-保持署名-非商业性使用-禁止演绎 3.0 国际」（CC BY-NC-ND 3.0）
用一杯咖啡支持我们，我们的每一篇[文档]都经过实际操作和精心打磨，而不是简单地从网上复制粘贴。期间投入了大量心血，只为能够真正帮助到您。
暂无评论
