《MySQL必知必会》—— 3.使用mysql （连接数据库、选择数据库、显示数据库和表的相关信息）（转载）

连接MySQL数据库
mysql -h host -u user -p -P port
host是服务端的IP地址，可选的，默认为localhost
user是登录的用户名，可选的，如果允许匿名用户登录，可以直接键入"mysql"登录
port是服务端的端口号，可选的，默认3306
-p代表是否使用密码登录，由于有些账户是没有密码的
https://www.cnblogs.com/progor/p/8733504.html
连接：

[root@host]# mysql -u root -p
Enter password:******

退出：exit 或者quit

mysql> exit
Bye

选择数据库
USE 数据库名;

如：
USE crashcourse;

成功的话，命令行中会显示 “Database changed”，Workbench中会加粗你选择的数据库。

必须先使用US打开数据库，才能读取其中的数据。

了解数据库和表
显示所有的数据库列表
SHOW DATABASES;

mysql> SHOW DATABASES;
±-------------------+
| Database |
±-------------------+
| crashcourse |
| information_schema |
| mysql |
| performance_schema |
| sakila |
| sys |
| world |
±-------------------+
7 rows in set (0.00 sec)
显示当前选择的数据库中可用表的列表
SHOW TABLES;

mysql> SHOW TABLES;
±----------------------+
| Tables_in_crashcourse |
±----------------------+
| customers |
| orderitems |
| orders |
| productnotes |
| products |
| vendors |
±----------------------+
6 rows in set (0.00 sec)
显示某个表的列信息，包括字段名、数据类型、是否允许为NULL、键信息、默认值以及其他信息（如cust_id字段的auto_increment）
SHOW COLUMNS FROM customers;
或者
DESCRIBE customers;

mysql> SHOW COLUMNS FROM customers;
±-------------±----------±-----±----±--------±---------------+
| Field | Type | Null | Key | Default | Extra |
±-------------±----------±-----±----±--------±---------------+
| cust_id | int | NO | PRI | NULL | auto_increment |
| cust_name | char(50) | NO | | NULL | |
| cust_address | char(50) | YES | | NULL | |
| cust_city | char(50) | YES | | NULL | |
| cust_state | char(5) | YES | | NULL | |
| cust_zip | char(10) | YES | | NULL | |
| cust_country | char(50) | YES | | NULL | |
| cust_contact | char(50) | YES | | NULL | |
| cust_email | char(255) | YES | | NULL | |
±-------------±----------±-----±----±--------±---------------+
9 rows in set (0.00 sec)
自动增量auto_increment：用于 某些表列需要唯一值的情况。Mysql自动为每个行分配下一个可用编号。

其他：

SHOW STATUS; 显示服务器状态信息
SHOW GRANTS; 显示授予用户的安全权限，显示某用户则用FOR限定 SHOW GRANTS FOR user；
SHOW CREATE DATABASE db_name;
SHOW CREATE TABLE tbl_name; 显示创建特定数据库或表的语句；
SHOW ERRORS [LIMIT [offset,] row_count];
SHOW WARNINGS [LIMIT [offset,] row_count]; 显示服务器错误或警告信息
HELP SHOW;

mysql> SHOW GRANTS;
±-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost |
±-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON . TO root@localhost WITH GRANT OPTION |
| GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,GROUP_REPLICATION_ADMIN,INNODB_REDO_LOG_ARCHIVE,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON . TO root@localhost WITH GRANT OPTION |
| GRANT PROXY ON ‘’@’’ TO ‘root’@‘localhost’ WITH GRANT OPTION |
±-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql>
mysql> SHOW CREATE TABLE customers;
±----------±-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
±----------±-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| customers | CREATE TABLE customers (
cust_id int NOT NULL AUTO_INCREMENT,
cust_name char(50) NOT NULL,
cust_address char(50) DEFAULT NULL,
cust_city char(50) DEFAULT NULL,
cust_state char(5) DEFAULT NULL,
cust_zip char(10) DEFAULT NULL,
cust_country char(50) DEFAULT NULL,
cust_contact char(50) DEFAULT NULL,
cust_email char(255) DEFAULT NULL,
PRIMARY KEY (cust_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
±----------±-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SHOW CREATE DATABASE crashcourse;
±------------±--------------------------------------------------------------------------------------------------------------------------------------+
| Database | Create Database |
±------------±--------------------------------------------------------------------------------------------------------------------------------------+
| crashcourse | CREATE DATABASE crashcourse /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci / /!80016 DEFAULT ENCRYPTION=‘N’ */ |
±------------±--------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

原文链接：https://blog.csdn.net/youshiwukong1524/article/details/105904201