使用 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 |
+------------------------------+
分析: 使用完全表名是指在表名前面加上数据库名称，可以避免命令冲突
