MySQL基础常用命令有哪些

发布时间：2021-12-24 09:34:38 作者：小新
来源：亿速云阅读：180

# MySQL基础常用命令大全

本文全面介绍MySQL数据库的基础操作命令，涵盖数据库管理、表操作、数据增删改查、用户权限等核心内容，适合初学者系统学习。

## 一、数据库基本操作

### 1. 连接MySQL服务器

```sql
mysql -h 主机名 -u 用户名 -p

-h：指定MySQL服务器地址（本地可省略）
-u：指定用户名
-p：提示输入密码

示例：

mysql -u root -p

2. 查看数据库版本

SELECT VERSION();
-- 或
STATUS;

3. 显示所有数据库

SHOW DATABASES;

4. 创建数据库

CREATE DATABASE 数据库名 
[CHARACTER SET 字符集] 
[COLLATE 排序规则];

-- 示例
CREATE DATABASE shop 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

5. 选择数据库

USE 数据库名;

6. 删除数据库

DROP DATABASE [IF EXISTS] 数据库名;

7. 查看当前数据库

SELECT DATABASE();

二、数据表操作

1. 创建数据表

CREATE TABLE [IF NOT EXISTS] 表名 (
    列名1 数据类型 [约束条件] [COMMENT '注释'],
    列名2 数据类型 [约束条件],
    ...
    [PRIMARY KEY (列名)]
) [ENGINE=存储引擎] [CHARSET=字符集];

-- 示例
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password CHAR(32) NOT NULL,
    email VARCHAR(100),
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB CHARSET=utf8mb4;

2. 查看表结构

DESC 表名;
-- 或
DESCRIBE 表名;
-- 或
SHOW COLUMNS FROM 表名;

3. 查看建表语句

SHOW CREATE TABLE 表名;

4. 修改表结构

添加列

ALTER TABLE 表名 ADD 列名 数据类型 [约束条件];

修改列

ALTER TABLE 表名 MODIFY 列名 新数据类型 [约束条件];

重命名列

ALTER TABLE 表名 CHANGE 原列名 新列名 数据类型;

删除列

ALTER TABLE 表名 DROP COLUMN 列名;

5. 重命名表

RENAME TABLE 原表名 TO 新表名;
-- 或
ALTER TABLE 原表名 RENAME TO 新表名;

6. 删除表

DROP TABLE [IF EXISTS] 表名;

7. 清空表数据

TRUNCATE TABLE 表名;

三、数据操作命令

1. 插入数据

-- 完整插入
INSERT INTO 表名 VALUES (值1, 值2, ...);

-- 指定列插入
INSERT INTO 表名 (列1, 列2) VALUES (值1, 值2);

-- 批量插入
INSERT INTO 表名 VALUES 
(值1, 值2, ...),
(值1, 值2, ...),
...;

2. 查询数据

基础查询

SELECT 列1, 列2 FROM 表名 [WHERE 条件];

条件查询

SELECT * FROM users WHERE age > 18 AND status = 1;

排序

SELECT * FROM products ORDER BY price DESC, sales ASC;

分页

SELECT * FROM articles LIMIT 10 OFFSET 20;
-- 等价于
SELECT * FROM articles LIMIT 20, 10;

分组统计

SELECT department, COUNT(*) as emp_count 
FROM employees 
GROUP BY department 
HAVING emp_count > 5;

3. 更新数据

UPDATE 表名 SET 列1=值1, 列2=值2 [WHERE 条件];

-- 示例
UPDATE users SET password = MD5('123456') WHERE id = 100;

4. 删除数据

DELETE FROM 表名 [WHERE 条件];

-- 清空表
DELETE FROM 表名;

四、索引操作

1. 创建索引

-- 普通索引
CREATE INDEX 索引名 ON 表名(列名);

-- 唯一索引
CREATE UNIQUE INDEX 索引名 ON 表名(列名);

-- 复合索引
CREATE INDEX 索引名 ON 表名(列1, 列2);

2. 查看索引

SHOW INDEX FROM 表名;

3. 删除索引

DROP INDEX 索引名 ON 表名;

五、用户与权限管理

1. 创建用户

CREATE USER '用户名'@'主机' IDENTIFIED BY '密码';

-- 示例
CREATE USER 'dev'@'%' IDENTIFIED BY 'Dev123!';

2. 修改密码

ALTER USER '用户名'@'主机' IDENTIFIED BY '新密码';

3. 授权

GRANT 权限 ON 数据库.表 TO '用户'@'主机';

-- 示例
GRANT SELECT, INSERT ON shop.* TO 'dev'@'%';

4. 查看权限

SHOW GRANTS FOR '用户'@'主机';

5. 撤销权限

REVOKE 权限 ON 数据库.表 FROM '用户'@'主机';

6. 删除用户

DROP USER '用户名'@'主机';

六、事务控制

1. 开启事务

START TRANSACTION;
-- 或
BEGIN;

2. 提交事务

COMMIT;

3. 回滚事务

ROLLBACK;

4. 设置保存点

SAVEPOINT 保存点名;

5. 回滚到保存点

ROLLBACK TO SAVEPOINT 保存点名;

七、视图操作

1. 创建视图

CREATE VIEW 视图名 AS SELECT语句;

2. 修改视图

ALTER VIEW 视图名 AS SELECT语句;

3. 删除视图

DROP VIEW [IF EXISTS] 视图名;

八、存储过程和函数

1. 创建存储过程

DELIMITER //
CREATE PROCEDURE 过程名(参数列表)
BEGIN
    -- SQL语句
END //
DELIMITER ;

-- 示例
DELIMITER //
CREATE PROCEDURE get_user(IN uid INT)
BEGIN
    SELECT * FROM users WHERE id = uid;
END //
DELIMITER ;

2. 调用存储过程

CALL 过程名(参数);

3. 创建函数

DELIMITER //
CREATE FUNCTION 函数名(参数) RETURNS 返回类型
BEGIN
    -- 函数体
    RETURN 值;
END //
DELIMITER ;

4. 查看存储过程和函数

SHOW PROCEDURE STATUS;
SHOW FUNCTION STATUS;

九、触发器

1. 创建触发器

DELIMITER //
CREATE TRIGGER 触发器名 触发时机 触发事件
ON 表名 FOR EACH ROW
BEGIN
    -- 触发器逻辑
END //
DELIMITER ;

-- 示例
DELIMITER //
CREATE TRIGGER update_time 
BEFORE UPDATE ON users 
FOR EACH ROW
BEGIN
    SET NEW.update_time = NOW();
END //
DELIMITER ;

2. 查看触发器

SHOW TRIGGERS;

3. 删除触发器

DROP TRIGGER 触发器名;

十、备份与恢复

1. 备份数据库

mysqldump -u 用户名 -p 数据库名 > 备份文件.sql

2. 恢复数据库

mysql -u 用户名 -p 数据库名 < 备份文件.sql

十一、系统变量与状态

1. 查看系统变量

SHOW VARIABLES LIKE '%变量名%';

2. 修改系统变量

SET GLOBAL 变量名=值;
-- 或
SET SESSION 变量名=值;

3. 查看服务器状态

SHOW STATUS;

十二、实用技巧

1. 查看执行计划

EXPLN SELECT语句;

2. 导入导出数据

-- 导出
SELECT * INTO OUTFILE '/tmp/data.csv'
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n'
FROM 表名;

-- 导入
LOAD DATA INFILE '/tmp/data.csv'
INTO TABLE 表名
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';

3. 批量执行SQL文件

SOURCE /path/to/file.sql;

总结

本文详细介绍了MySQL数据库的基础操作命令，包括： - 数据库和表的创建与管理 - 数据的增删改查操作 - 索引、视图、存储过程等高级功能 - 用户权限管理与事务控制 - 数据库备份恢复技巧

掌握这些基础命令是进行MySQL数据库开发和管理的前提。建议读者结合实际操作练习，逐步深入理解MySQL的更多高级特性。

注意：实际使用时请根据MySQL版本调整语法，部分命令在不同版本中可能有差异。 “`