MySQL的一些常用命令:
- 显示所有数据库:
SHOW DATABASES;
- 创建新的数据库:
CREATE DATABASE mydatabase;
- 删除数据库:
DROP DATABASE mydatabase;
- 使用指定数据库:
USE mydatabase;
- 显示数据库中所有表格:
SHOW TABLES;
- 创建新表格:
CREATE TABLE mytable (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50)
);
- 删除表格:
DROP TABLE mytable;
- 插入记录:
INSERT INTO mytable (firstname, lastname, email)
VALUES ('John', 'Doe', 'johndoe@example.com');
- 显示记录:
SELECT * FROM mytable;
- 更新记录:
UPDATE mytable SET email='newemail@example.com' WHERE id=1;
- 删除记录:
DELETE FROM mytable WHERE id=1;
- 显示表格结构:
DESCRIBE mytable;
- 显示指定字段的数据:
SELECT firstname, lastname FROM mytable;
- 按条件筛选数据:
SELECT * FROM mytable WHERE firstname='John';
- 对筛选结果进行排序:
SELECT * FROM mytable ORDER BY lastname ASC;
- 对筛选结果进行分组:
SELECT COUNT(*), lastname FROM mytable GROUP BY lastname;
- 对筛选结果进行计算:
SELECT AVG(id) FROM mytable;
- 连接多个表格:
SELECT * FROM mytable1 JOIN mytable2 ON mytable1.id=mytable2.id;
- 创建索引以加速数据查询:
CREATE INDEX myindex ON mytable (lastname);
- 修改表格结构:
ALTER TABLE mytable ADD COLUMN age INT(3);
- 显示当前MySQL版本信息:
SELECT VERSION();
- 显示MySQL服务器的状态:
SHOW STATUS;
- 显示MySQL服务器的变量值:
SHOW VARIABLES;
- 更改MySQL服务器的全局变量设置:
SET GLOBAL max_connections=1000;
- 显示MySQL服务器上当前的进程列表:
SHOW PROCESSLIST;
- 杀死MySQL服务器上指定的进程:
KILL process_id;
- 导出数据到文件中:
SELECT * INTO OUTFILE 'data.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM mytable;
- 从文件中导入数据到MySQL数据库:
LOAD DATA INFILE 'data.csv' INTO TABLE mytable FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';
- 更改表格名称:
ALTER TABLE mytable RENAME TO newtable;
- 查找含有指定字段的表格:
SELECT table_name FROM information_schema.columns WHERE column_name='mycolumn';
- 查找和替换表格中的数据:
UPDATE mytable SET firstname = REPLACE(firstname, 'oldname', 'newname');
- 创建一个新的存储过程:
CREATE PROCEDURE myprocedure()
BEGIN
SELECT * FROM mytable;
END;
- 执行存储过程:
CALL myprocedure();
- 删除存储过程:
DROP PROCEDURE IF EXISTS myprocedure;
- 创建MySQL用户:
CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'mypassword';
- 授予MySQL用户权限:
GRANT SELECT, INSERT, UPDATE ON mydatabase.* TO 'myuser'@'localhost';
- 撤销MySQL用户的权限:
REVOKE ALL PRIVILEGES ON mydatabase.* FROM 'myuser'@'localhost';
- 删除MySQL用户:
DROP USER 'myuser'@'localhost';
- 开启二进制日志记录:
SET GLOBAL binlog_format = 'ROW';
- 显示二进制日志内容:
SHOW BINLOG EVENTS;
- 备份数据库:
mysqldump -u username -p mydatabase > backup.sql
该命令将mydatabase整个数据库备份到backup.sql文件中。
- 恢复数据库:
mysql -u username -p mydatabase < backup.sql
该命令将从backup.sql文件中恢复mydatabase整个数据库。
- 查看数据库大小:
SELECT table_schema AS "Database", SUM(data_length + index_length) / 1024 / 1024 AS "Size (MB)" FROM information_schema.TABLES GROUP BY table_schema;
该命令将显示每个数据库的大小(以MB为单位)。
- 显示MySQL错误日志:
SHOW VARIABLES LIKE 'log_error';
该命令将显示MySQL错误日志的位置。
- 压缩MySQL数据库:
mysqldump -u username -p mydatabase | gzip > backup.sql.gz
该命令将mydatabase数据库备份到backup.sql.gz压缩文件中。
- 解压MySQL数据库备份:
gunzip < backup.sql.gz | mysql -u username -p mydatabase
该命令将从backup.sql.gz文件中解压mydatabase数据库备份,并将其恢复到mydatabase数据库中。
- 查看当前MySQL连接数量:
SHOW STATUS WHERE `variable_name` = 'Threads_connected';
该命令将显示当前的MySQL连接数量。
- 查看正在执行的查询:
SELECT * FROM information_schema.processlist WHERE Command != 'Sleep' AND TIME >= 30 ORDER BY time DESC;
该命令将显示所有正在执行的MySQL查询,按照执行时间从长到短排序,并且仅显示执行时间超过30秒的查询。
- 查看未使用的索引:
SELECT DISTINCT t.table_schema, t.table_name FROM information_schema.tables t LEFT JOIN information_schema.statistics s ON s.table_schema=t.table_schema AND s.table_name=t.table_name AND s.index_name!='PRIMARY' WHERE s.table_schema IS NOT NULL AND s.index_name IS NULL;
该命令将显示所有未使用索引的MySQL表格。
- 查看MySQL服务器的版本和系统信息:
SHOW VARIABLES LIKE "%version%";
该命令将显示MySQL服务器的版本和系统信息。