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