Mysql命令大全

SHOW DATABASES;

查看所有数据库

SHOW DATABASES;

CREATE DATABASE db_name;

创建数据库

CREATE DATABASE testdb;

DROP DATABASE db_name;

删除数据库

DROP DATABASE testdb;

USE db_name;

选择数据库

USE testdb;

ALTER DATABASE db_name CHARACTER SET utf8mb4;

修改数据库字符集

ALTER DATABASE testdb CHARACTER SET utf8mb4;

SHOW TABLES;

查看数据库中的表

SHOW TABLES;

DESCRIBE table_name; 或 DESC table_name;

查看表结构

DESCRIBE users;

CREATE TABLE table_name (...);

创建表

CREATE TABLE users(id INT PRIMARY KEY, name VARCHAR(50));

DROP TABLE table_name;

删除表

DROP TABLE users;

ALTER TABLE table_name ADD column_name type;

添加列

ALTER TABLE users ADD age INT;

ALTER TABLE table_name DROP column_name;

删除列

ALTER TABLE users DROP age;

ALTER TABLE table_name MODIFY column_name new_type;

修改列类型

ALTER TABLE users MODIFY name VARCHAR(100);

RENAME TABLE old_name TO new_name;

重命名表

RENAME TABLE users TO user_info;

SELECT * FROM table;

查询所有数据

SELECT * FROM users;

SELECT column1, column2 FROM table;

查询指定列

SELECT id, name FROM users;

INSERT INTO table (col1,col2) VALUES (val1,val2);

插入数据

INSERT INTO users(id,name) VALUES (1,'Alice');

UPDATE table SET col1=val1 WHERE condition;

更新数据

UPDATE users SET name='Bob' WHERE id=1;

DELETE FROM table WHERE condition;

删除数据

DELETE FROM users WHERE id=1;

TRUNCATE TABLE table;

清空表数据

TRUNCATE TABLE users;

CREATE INDEX idx_name ON table(column);

创建普通索引

CREATE INDEX idx_name ON users(name);

CREATE UNIQUE INDEX idx_name ON table(column);

创建唯一索引

CREATE UNIQUE INDEX idx_uid ON users(id);

DROP INDEX idx_name ON table;

删除索引

DROP INDEX idx_name ON users;

CREATE USER 'user'@'host' IDENTIFIED BY 'password';

创建用户

CREATE USER 'jack'@'localhost' IDENTIFIED BY '123456';

GRANT ALL PRIVILEGES ON db.* TO 'user'@'host';

授权

GRANT ALL PRIVILEGES ON testdb.* TO 'jack'@'localhost';

REVOKE ALL PRIVILEGES ON db.* FROM 'user'@'host';

收回权限

REVOKE ALL PRIVILEGES ON testdb.* FROM 'jack'@'localhost';

FLUSH PRIVILEGES;

刷新权限

FLUSH PRIVILEGES;

mysqldump -u user -p db_name > backup.sql

数据库备份

mysqldump -u root -p testdb > testdb.sql

mysql -u user -p db_name < backup.sql

数据库恢复

mysql -u root -p testdb < testdb.sql

mysqldump -u user -p --all-databases > all.sql

备份所有数据库

mysqldump -u root -p --all-databases > all.sql

EXPLAIN SELECT ...;

查看 SQL 执行计划

EXPLAIN SELECT * FROM users WHERE id=1;

SHOW INDEX FROM table;

查看表索引

SHOW INDEX FROM users;

SHOW STATUS;

查看服务器状态

SHOW STATUS;

SHOW VARIABLES;

查看配置参数

SHOW VARIABLES LIKE '%timeout%';

START TRANSACTION;

开始事务

START TRANSACTION;

COMMIT;

提交事务

COMMIT;

ROLLBACK;

回滚事务

ROLLBACK;

SET autocommit=0;

关闭自动提交

SET autocommit=0;

SHOW PROCESSLIST;

查看当前连接

SHOW PROCESSLIST;

KILL process_id;

杀死连接

KILL 123;

SHOW TABLE STATUS;

查看表信息

SHOW TABLE STATUS LIKE 'users';

OPTIMIZE TABLE table;

优化表

OPTIMIZE TABLE users;

ALTER TABLE table ENGINE=InnoDB;

修改存储引擎

ALTER TABLE users ENGINE=InnoDB;