使用 MySQL
3.2 选择数据库
使用数据库:
输入: USE 数据库名;
输出: Database changed
分析: 不返回任何结果，显示某种形式的通知
例如: 使用 crashcourse 数据库
use crashcourse;
3.3 了解数据库和表
列出所有的数据库:
输入: SHOW DATABASES;
输出:
+--------------------+
| Database |
+--------------------+
| coldfusion |
| crashcourse |
| flex |
| forta |
| information_schema |
| mysql |
| performance_schema |
| study |
| sys |
| test |
+--------------------+
分析: 返回可用数据库的一个列表
列出当前数据库中的所有表:
输入: SHOW TABLES;
输出:
+------------------------------------------------------+
| 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 |
| 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 |
+------------------------------------------------------+
分析: 返回当前选择的数据库内可用表的列表
显示指定表的列信息:
输入: SHOW COLUMNS FROM user;
或者是: DESCRIBE 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 | |
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+
分析: SHOW columns 要求给出一个表名（例如 user），它对每个字段返回一行，行中包含字段名、数据类型、是否允许 NULL，键信息、默认值以及其他信息
其他支持 SHOW 的语句:
1. SHOW STATUS 用来显示广泛的服务器状态信息
2. SHOW CREATE DATABASE 数据库名 和 SHOW CREATE TABLE 表名 分别用来显示创建特定数据库或表的 mysql 语句，包括表的结构、索引等
3. SHOW GRANTS 用来显示授予用户（所有用户或特定用户）的安全权限
4. SHOW ERRORS 和 SHOW WARNINGS 用来显示服务器错误或警告消息
5. SHOW PROCESSLIST; 用来显示当前正在执行的进程列表
6. SHOW VARIABLES; 用来显示数据库系统的配置变量
3.4 数据库的创建
输入: CREATE DATABASE <数据库名>;
例如: CREATE DATABASE clc;
输出: Query OK, 1 row affected (0.08 sec)
检索数据
4.1 SELECT 语句
用途是从一个或多个表中检索信息
4.2 检索单个列
输入: SELECT plugin FROM user;
输出:
+-----------------------+
| plugin |
+-----------------------+
| mysql_native_password |
| caching_sha2_password |
| caching_sha2_password |
| caching_sha2_password |
+-----------------------+
分析: 上述语句利用 SELECT 语句从 user 表中检索一个名为 plugin 的列
4.3 检索多个列
输入: SELECT Host,plugin FROM user;
输出:
+-----------+-----------------------+
| Host | plugin |
+-----------+-----------------------+
| % | mysql_native_password |
| localhost | caching_sha2_password |
| localhost | caching_sha2_password |
| localhost | caching_sha2_password |
+-----------+-----------------------+
分析: 与上一个例子一样，列名之间用逗号分隔
4.4 检索所有列
输入: SELECT * FROM user;
输出:
+-----------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------------------+--------------------------+----------------------------+---------------+-------------+-----------------+----------------------+-----------------------+------------------------------------------------------------------------+------------------+-----------------------+-------------------+----------------+------------------+----------------+------------------------+---------------------+--------------------------+-----------------+
| Host | User | SELECT_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | SHOW_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | SHOW_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin | authentication_string | password_expired | password_last_changed | password_lifetime | account_locked | Create_role_priv | Drop_role_priv | Password_reuse_history | Password_reuse_time | Password_require_current | User_attributes |
+-----------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------------------+--------------------------+----------------------------+---------------+-------------+-----------------+----------------------+-----------------------+------------------------------------------------------------------------+------------------+-----------------------+-------------------+----------------+------------------+----------------+------------------------+---------------------+--------------------------+-----------------+
| % | root | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | NULL | NULL | NULL | 0 | 0 | 0 | 0 | mysql_native_password | *AE47E29BB2E9F8F1EEB97F5274458B7CBBFDDA41 | N | 2024-01-03 15:26:21 | NULL | N | Y | Y | NULL | NULL | NULL | NULL |
| localhost | mysql.infoschema | Y | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | NULL | NULL | NULL | 0 | 0 | 0 | 0 | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | N | 2024-01-03 15:22:18 | NULL | Y | N | N | NULL | NULL | NULL | NULL |
| localhost | mysql.session | N | N | N | N | N | N | N | Y | N | N | N | N | N | N | N | Y | N | N | N | N | N | N | N | N | N | N | N | N | N | | NULL | NULL | NULL | 0 | 0 | 0 | 0 | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | N | 2024-01-03 15:22:18 | NULL | Y | N | N | NULL | NULL | NULL | NULL |
| localhost | mysql.sys | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | | NULL | NULL | NULL | 0 | 0 | 0 | 0 | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | N | 2024-01-03 15:22:18 | NULL | Y | N | N | NULL | NULL | NULL | NULL |
+-----------+------------------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------------------+--------------------------+----------------------------+---------------+-------------+-----------------+----------------------+-----------------------+------------------------------------------------------------------------+------------------+-----------------------+-------------------+----------------+------------------+----------------+------------------------+---------------------+--------------------------+-----------------+
分析: 如果给定一个通配符（*），则返回表中所有列
4.5 检索不同的行
输入: SELECT plugin FROM user;
输出:
+-----------------------+
| plugin |
+-----------------------+
| mysql_native_password |
| caching_sha2_password |
| caching_sha2_password |
| caching_sha2_password |
+-----------------------+
输入: SELECT DISTINCT plugin FROM user;
输出:
+-----------------------+
| plugin |
+-----------------------+
| mysql_native_password |
| caching_sha2_password |
+-----------------------+
分析: SELECT DISTINCT plugin 告诉 MySQL 只返回不同（唯一）的行，因此只返回两行。如果使用 distinct 关键字必须放在列名前面
4.6 限制结果
输入: SELECT priv FROM global_grants LIMIT 12;
输出:
+----------------------------+
| priv |
+----------------------------+
| SYSTEM_USER |
| BACKUP_ADMIN |
| CLONE_ADMIN |
| CONNECTION_ADMIN |
| PERSIST_RO_VARIABLES_ADMIN |
| SESSION_VARIABLES_ADMIN |
| SYSTEM_USER |
| SYSTEM_VARIABLES_ADMIN |
| SYSTEM_USER |
| APPLICATION_PASSWORD_ADMIN |
| AUDIT_ADMIN |
| BACKUP_ADMIN |
+----------------------------+
分析: 此语句使用 SELECT 语句检索单个列，LIMIT 12 指示 MySQL 返回不多于两行
输入: SELECT priv FROM global_grants LIMIT 3,8;
输出:
+----------------------------+
| priv |
+----------------------------+
| CONNECTION_ADMIN |
| PERSIST_RO_VARIABLES_ADMIN |
| SESSION_VARIABLES_ADMIN |
| SYSTEM_USER |
| SYSTEM_VARIABLES_ADMIN |
| SYSTEM_USER |
| APPLICATION_PASSWORD_ADMIN |
| AUDIT_ADMIN |
+----------------------------+
分析: LIMIT 3,8 指示 MySQL 返回从行 3 开始的 8 行，第一个数为开始位置，第二个数为要检索的行数
注意: 第一行为行 0
输入: SELECT priv FROM global_grants LIMIT 8 OFFSET 3;
输出:
+----------------------------+
| priv |
+----------------------------+
| CONNECTION_ADMIN |
| PERSIST_RO_VARIABLES_ADMIN |
| SESSION_VARIABLES_ADMIN |
| SYSTEM_USER |
| SYSTEM_VARIABLES_ADMIN |
| SYSTEM_USER |
| APPLICATION_PASSWORD_ADMIN |
| AUDIT_ADMIN |
+----------------------------+
分析: LIMIT 的另一种代替语法，意思为读取 8 行从第 3 行开始
4.7 使用完全限定的表名
输入: SELECT server_cost.cost_name FROM server_cost;
输出:
+------------------------------+
| cost_name |
+------------------------------+
| disk_temptable_create_cost |
| disk_temptable_row_cost |
| key_compare_cost |
| memory_temptable_create_cost |
| memory_temptable_row_cost |
| row_evaluate_cost |
+------------------------------+
分析: 使用完全表名是指在列名前面加上表名称，可以避免命令冲突
输入: SELECT server_cost.cost_name FROM mysql.server_cost;
输出:
+------------------------------+
| cost_name |
+------------------------------+
| disk_temptable_create_cost |
| disk_temptable_row_cost |
| key_compare_cost |
| memory_temptable_create_cost |
| memory_temptable_row_cost |
| row_evaluate_cost |
+------------------------------+
分析: 使用完全表名是指在表名前面加上数据库名称，可以避免命令冲突
