YouTip

## Basic Commands | Operation | Command | | --- | --- | | Connect to MySQL database | `mysql -u username -p` | | Show all databases | `SHOW DATABASES;` | | Select a database | `USE databasename;` | | Show all tables | `SHOW TABLES;` | | Show table structure | `DESCRIBE tablename;` or `SHOW COLUMNS FROM tablename;` | | Create a new database | `CREATE DATABASE databasename;` | | Delete a database | `DROP DATABASE databasename;` | | Create a new table | `CREATE TABLE tablename (column1 datatype , column2 datatype , ...);` | | Delete a table | `DROP TABLE tablename;` | | Insert data | `INSERT INTO tablename (column1, column2, ...) VALUES (value1, value2, ...);` | | Query data | `SELECT column1, column2, ... FROM tablename WHERE condition;` | | Update data | `UPDATE tablename SET column1 = value1, column2 = value2, ... WHERE condition;` | | Delete data | `DELETE FROM tablename WHERE condition;` | | Create user | `CREATE USER 'username'@'host' IDENTIFIED BY 'password';` | | Grant privileges to user | `GRANT privileges ON databasename.* TO 'username'@'host';` | | Flush privileges | `FLUSH PRIVILEGES;` | | Show current user | `SELECT USER();` | | Exit MySQL | `EXIT;` | ## Database Related Commands The following are commands related to MySQL database operations, including creating, deleting, and modifying databases: | Operation | Command | | --- | --- | | **Create database** | `CREATE DATABASE databasename;` | | **Delete database** | `DROP DATABASE databasename;` | | **Modify database charset and collation** | `ALTER DATABASE databasename DEFAULT CHARACTER SET charset DEFAULT COLLATE collation;` | | **Show all databases** | `SHOW DATABASES;` | | **Show database detailed information** | `SHOW CREATE DATABASE databasename;` | | **Select database** | `USE databasename;` | | **Show database status information** | `SHOW STATUS;` | | **Show database error information** | `SHOW ERRORS;` | | **Show database warning information** | `SHOW WARNINGS;` | | **Show database tables** | `SHOW TABLES;` | | **Show table structure** | `DESC tablename;` `DESCRIBE tablename;` `SHOW COLUMNS FROM tablename;` `EXPLAIN tablename;` | | **Create table** | `CREATE TABLE tablename (column1 datatype , column2 datatype , ...);` | | **Delete table** | `DROP TABLE tablename;` | | **Modify table structure** | `ALTER TABLE tablename ADD columnname datatype ;` `ALTER TABLE tablename DROP columnname;` `ALTER TABLE tablename MODIFY columnname datatype ;` | | **Show table creation SQL** | `SHOW CREATE TABLE tablename;` | ## Data Table Related Commands The following are common commands related to MySQL data tables, including creating, modifying, deleting tables and viewing table structure and data: | Operation | Command | | --- | --- | | **Create table** | `CREATE TABLE tablename (column1 datatype , column2 datatype , ...);` | | **Delete table** | `DROP TABLE tablename;` | | **Modify table structure** | Add column: `ALTER TABLE tablename ADD columnname datatype ;` Drop column: `ALTER TABLE tablename DROP columnname;` Modify column: `ALTER TABLE tablename MODIFY columnname datatype ;` Rename column: `ALTER TABLE tablename CHANGE oldcolumnname newcolumnname datatype ;` | | **Show table structure** | `DESC tablename;` `DESCRIBE tablename;` `SHOW COLUMNS FROM tablename;` `EXPLAIN tablename;` | | **Show table creation SQL** | `SHOW CREATE TABLE tablename;` | | **Show all data in table** | `SELECT * FROM tablename;` | | **Insert data** | `INSERT INTO tablename (column1, column2, ...) VALUES (value1, value2, ...);` | | **Update data** | `UPDATE tablename SET column1 = value1, column2 = value2, ... WHERE condition;` | | **Delete data** | `DELETE FROM tablename WHERE condition;` | | **Show table indexes** | `SHOW INDEX FROM tablename;` | | **Create index** | `CREATE INDEX indexname ON tablename (columnname);` | | **Delete index** `DROP INDEX indexname ON tablename;` | | **Show table constraints** | `SHOW CREATE TABLE tablename;` (constraint information will be included in the table creation SQL) | | **Show table statistics** | `SHOW TABLE STATUS LIKE 'tablename';` | ## MySQL Transaction Related Commands The following are common commands related to MySQL transactions: | Operation | Command | | --- | --- | | **Start transaction** | `START TRANSACTION;` or `BEGIN;` | | **Commit transaction** | `COMMIT;` | | **Rollback transaction** | `ROLLBACK;` | | **Show current transaction status** | `SHOW ENGINE INNODB STATUS;` (can view InnoDB storage engine transaction status) | | **Lock table for transaction operations** | `LOCK TABLES tablename WRITE;` or `LOCK TABLES tablename READ;` | | **Release locked tables** | `UNLOCK TABLES;` | | **Set transaction isolation level** | `SET TRANSACTION ISOLATION LEVEL READ COMMITTED;` `SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;` `SET TRANSACTION IS