3 使用MySQL

3.2 选择数据库

在你最初连接到MySQL时，没有任何数据库打开供你使用。在你能执行任意数据库操作前，需要选择一个数据库。为此，可使用USE关键字。

mysql> USE crashcourse;
Database changed

3.3 了解数据库和表

不知道可使用的数据库名？SHOW DATABASES; 可返回可用数据库的一个列表。

mysql> SHOW DATABASES;
+-------------+
| Database    |
+-------------+
| databse1    |
| databse2    |
+-------------+

获得一个数据库内的表的列表？SHOW TABLES; 返回当前选择的数据库内可用表的列表。

mysql> SHOW TABLES;
+-----------------------+
| Tables_in_testdata    |
+-----------------------+
| table001              |
| table002              |
+-----------------------+

SHOW也可以用来显示表列

mysql> SHOW COLUMNS FROM table001;
+--------+---------+---------+---------+---------+---------------+
| Field  | Type    | Null    | Key     | Default | Extra         |
+--------+---------+---------+---------+---------+---------------+
| id     | int(11) | NO      | PRI     | NULL    |auto_increment |
+--------+---------+---------+---------+---------+---------------+
mysql> DESCRIBE table001;
+--------+---------+---------+---------+---------+---------------+
| Field  | Type    | Null    | Key     | Default | Extra         |
+--------+---------+---------+---------+---------+---------------+
| id     | int(11) | NO      | PRI     | NULL    |auto_increment |
+--------+---------+---------+---------+---------+---------------+

SHOW COLUMNS要求给出一个表名，它对每个字段返回一行，行中包含字段名、数据类型、是否允许NULL、键信息、默认值以及其他信息（如auto_increment）。

SHOW STATUS;用于显示广泛的服务器状态信息。

SHOW CREATE DATABASE 和 SHOW CREATE TABLE，分别用来显示创建特定数据库或表的MySQL语句；

MariaDB [testdatabase]> show create database testdatabase;
+--------------+-----------------------------------------------------------------------------------------------------+
| Database     | Create Database                                                                                     |
+--------------+-----------------------------------------------------------------------------------------------------+
| testdatabase | CREATE DATABASE `testdatabase` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci */ |
+--------------+-----------------------------------------------------------------------------------------------------+
MariaDB [testdatabase]> show create table tasks;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                              
                                                                                                                                    
                                                                                                                       |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tasks | CREATE TABLE `tasks` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(50) NOT NULL,
  `Result` tinyint(1) DEFAULT 0,
  `Fault` int(11) DEFAULT 0,
  `Correct` int(11) DEFAULT 0,
  `Description` varchar(100) DEFAULT NULL,
  `Parent` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

SHOW GRANTS，用来显示授予用户（所有用户或特定用户）的安全权限；

MariaDB [testdatabase]> SHOW GRANTS;
+----------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@localhost                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` IDENTIFIED BY PASSWORD '*DE0A26A6E94640F86D1505CF278C382693A1D00A' WITH GRANT OPTION |
| GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION                                                                          |
+----------------------------------------------------------------------------------------------------------------------------------------+

SHOW ERRORS 和 SHOW WARNINGS，用来显示服务器错误或警告消息。