MySQL Command Encyclopedia
Basic Commands
| Operation | Command |
|---|---|
| Connect to a MySQL database | mysql -u 用户名 -p |
| View all databases | SHOW DATABASES; |
| Select a database | USE 数据库名; |
| View all tables | SHOW TABLES; |
| View table structure | DESCRIBE 表名;orSHOW COLUMNS FROM 表名; |
| Create a new database | CREATE DATABASE 数据库名; |
| Drop a database | DROP DATABASE 数据库名; |
| Create a new table | CREATE TABLE 表名 (列名1 数据类型 [约束], 列名2 数据类型 [约束], ...); |
| Drop a table | DROP TABLE 表名; |
| Insert data | INSERT INTO 表名 (列1, 列2, ...) VALUES (值1, 值2, ...); |
| Query data | SELECT 列1, 列2, ... FROM 表名 WHERE 条件; |
| Update data | UPDATE 表名 SET 列1 = 值1, 列2 = 值2, ... WHERE 条件; |
| Delete data | DELETE FROM 表名 WHERE 条件; |
| Create user | CREATE USER '用户名'@'主机' IDENTIFIED BY '密码'; |
| Grant privileges to user | GRANT 权限 ON 数据库名.* TO '用户名'@'主机'; |
| Flush privileges | FLUSH PRIVILEGES; |
| View current user | SELECT USER(); |
| Exit MySQL | EXIT; |
Database Related Commands
The following are commands related to MySQL database operations, including creating, dropping, and modifying databases:
| Operation | Command |
|---|---|
| Create database | CREATE DATABASE 数据库名; |
| Drop database | DROP DATABASE 数据库名; |
| Modify database character set and collation | ALTER DATABASE 数据库名 DEFAULT CHARACTER SET 编码格式 DEFAULT COLLATE 排序规则; |
| View all databases | SHOW DATABASES; |
| View database details | SHOW CREATE DATABASE 数据库名; |
| Select database | USE 数据库名; |
| View database status information | SHOW STATUS; |
| View database error information | SHOW ERRORS; |
| View database warning information | SHOW WARNINGS; |
| View tables in the database | SHOW TABLES; |
| View table structure | DESC 表名;DESCRIBE 表名;SHOW COLUMNS FROM 表名;EXPLAIN 表名; |
| Create table | CREATE TABLE 表名 (列名1 数据类型 [约束], 列名2 数据类型 [约束], ...); |
| Drop table | DROP TABLE 表名; |
| Modify table structure | ALTER TABLE 表名 ADD 列名 数据类型 [约束];ALTER TABLE 表名 DROP 列名;ALTER TABLE 表名 MODIFY 列名 数据类型 [约束]; |
| View the CREATE SQL of the table | SHOW CREATE TABLE 表名; |
Data Table Related Commands
The following are common commands related to MySQL data tables, including creating, modifying, and dropping tables, as well as viewing table structure and data:
| Operation | Command |
|---|---|
| Create table | CREATE TABLE 表名 (列名1 数据类型 [约束], 列名2 数据类型 [约束], ...); |
| Drop table | DROP TABLE 表名; |
| Modify table structure | Add column:ALTER TABLE 表名 ADD 列名 数据类型 [约束];Drop column: ALTER TABLE 表名 DROP 列名;Modify column: ALTER TABLE 表名 MODIFY 列名 数据类型 [约束];Rename column: ALTER TABLE 表名 CHANGE 旧列名 新列名 数据类型 [约束]; |
| View table structure | DESC 表名;DESCRIBE 表名;SHOW COLUMNS FROM 表名;EXPLAIN 表名; |
| View the CREATE SQL of the table | SHOW CREATE TABLE 表名; |
| View all data in the table | SELECT * FROM 表名; |
| Insert data | INSERT INTO 表名 (列1, 列2, ...) VALUES (值1, 值2, ...); |
| Update data | UPDATE 表名 SET 列1 = 值1, 列2 = 值2, ... WHERE 条件; |
| Delete data | DELETE FROM 表名 WHERE 条件; |
| View table indexes | SHOW INDEX FROM 表名; |
| Create index | CREATE INDEX 索引名 ON 表名 (列名); |
| Drop index | DROP INDEX 索引名 ON 表名; |
| View table constraints | SHOW CREATE TABLE 表名;(Constraint information will be included in the CREATE TABLE SQL) |
| View table statistics | SHOW TABLE STATUS LIKE '表名'; |
MySQL Transaction Related Commands
The following are common commands related to MySQL transactions:
| Operation | Command |
|---|---|
| Start transaction | START TRANSACTION;orBEGIN; |
| Commit transaction | COMMIT; |
| Rollback transaction | ROLLBACK; |
| View current transaction status | SHOW ENGINE INNODB STATUS;(You can view the transaction status of the InnoDB storage engine) |
| Lock tables for transaction operations | LOCK TABLES 表名 WRITE;orLOCK TABLES 表名 READ; |
| Unlock tables | UNLOCK TABLES; |
| Set transaction isolation level | SET TRANSACTION ISOLATION LEVEL READ COMMITTED;SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; |