MySQL数据库和数据表的基本操作有哪些

发布时间：2021-10-22 09:18:56 作者：iii
来源：亿速云阅读：198

# MySQL数据库和数据表的基本操作指南

## 一、数据库基本操作

### 1. 创建数据库
```sql
CREATE DATABASE database_name;
-- 示例：创建名为shop的数据库
CREATE DATABASE shop;

创建数据库时可指定字符集和排序规则：

CREATE DATABASE database_name 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

2. 查看数据库

-- 查看所有数据库
SHOW DATABASES;

-- 查看特定数据库创建语句
SHOW CREATE DATABASE database_name;

3. 选择数据库

USE database_name;
-- 示例：使用shop数据库
USE shop;

4. 修改数据库

ALTER DATABASE database_name 
CHARACTER SET charset_name 
COLLATE collation_name;

5. 删除数据库

DROP DATABASE database_name;
-- 危险操作！会删除所有数据
DROP DATABASE IF EXISTS old_database;

二、数据表基本操作

1. 创建数据表

基本语法：

CREATE TABLE table_name (
    column1 datatype constraints,
    column2 datatype constraints,
    ...
);

示例（创建用户表）：

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    email VARCHAR(100) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

2. 查看数据表

-- 查看当前数据库所有表
SHOW TABLES;

-- 查看表结构
DESCRIBE table_name;
DESC table_name;  -- 简写

-- 查看建表语句
SHOW CREATE TABLE table_name;

3. 修改表结构

添加列

ALTER TABLE table_name 
ADD COLUMN column_name datatype constraints;

修改列

-- 修改数据类型
ALTER TABLE table_name 
MODIFY COLUMN column_name new_datatype;

-- 重命名列
ALTER TABLE table_name 
CHANGE COLUMN old_name new_name datatype;

删除列

ALTER TABLE table_name 
DROP COLUMN column_name;

重命名表

ALTER TABLE old_name RENAME TO new_name;
-- 或
RENAME TABLE old_name TO new_name;

4. 删除数据表

DROP TABLE table_name;
-- 安全删除
DROP TABLE IF EXISTS temp_table;

三、数据操作语言(DML)

1. 插入数据

-- 完整插入
INSERT INTO table_name (col1, col2, ...) 
VALUES (val1, val2, ...);

-- 批量插入
INSERT INTO users (username, email) 
VALUES 
    ('user1', 'user1@example.com'),
    ('user2', 'user2@example.com');

2. 查询数据

基础查询：

SELECT * FROM table_name;
SELECT col1, col2 FROM table_name;

条件查询：

SELECT * FROM products WHERE price > 100;
SELECT * FROM users WHERE username LIKE 'j%';

排序和限制：

SELECT * FROM orders ORDER BY order_date DESC LIMIT 10;

3. 更新数据

UPDATE table_name 
SET col1 = val1, col2 = val2 
WHERE condition;

-- 示例：修改用户密码
UPDATE users SET password = 'new_hash' WHERE id = 5;

4. 删除数据

DELETE FROM table_name WHERE condition;

-- 清空表（不可恢复）
TRUNCATE TABLE table_name;

四、约束操作

1. 主键约束

-- 创建时指定
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    ...
);

-- 添加主键
ALTER TABLE table_name 
ADD PRIMARY KEY (column_name);

2. 外键约束

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

3. 唯一约束

ALTER TABLE products 
ADD UNIQUE (product_code);

4. 检查约束(MySQL 8.0+)

CREATE TABLE employees (
    salary DECIMAL(10,2),
    CHECK (salary > 0)
);

五、索引操作

1. 创建索引

-- 单列索引
CREATE INDEX idx_name ON table_name (column_name);

-- 复合索引
CREATE INDEX idx_name ON users (last_name, first_name);

-- 唯一索引
CREATE UNIQUE INDEX idx_email ON users (email);

2. 查看索引

SHOW INDEX FROM table_name;

3. 删除索引

DROP INDEX index_name ON table_name;

六、视图操作

1. 创建视图

CREATE VIEW view_name AS
SELECT columns FROM tables WHERE conditions;

2. 使用视图

SELECT * FROM view_name;

3. 修改视图

ALTER VIEW view_name AS new_select_statement;

4. 删除视图

DROP VIEW IF EXISTS view_name;

七、存储过程和函数

1. 创建存储过程

DELIMITER //
CREATE PROCEDURE get_user(IN user_id INT)
BEGIN
    SELECT * FROM users WHERE id = user_id;
END //
DELIMITER ;

2. 调用存储过程

CALL get_user(1);

3. 创建函数

DELIMITER //
CREATE FUNCTION count_users() RETURNS INT
BEGIN
    DECLARE user_count INT;
    SELECT COUNT(*) INTO user_count FROM users;
    RETURN user_count;
END //
DELIMITER ;

八、事务控制

1. 事务基本操作

START TRANSACTION;
-- SQL语句
COMMIT;  -- 或 ROLLBACK;

2. 设置自动提交

SET autocommit = 0;  -- 关闭自动提交
SET autocommit = 1;  -- 开启自动提交

九、用户权限管理

1. 创建用户

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

2. 授权

GRANT ALL PRIVILEGES ON database.* TO 'user'@'localhost';

3. 撤销权限

REVOKE ALL PRIVILEGES ON database.* FROM 'user'@'localhost';

十、备份与恢复

1. 使用mysqldump备份

mysqldump -u username -p database_name > backup.sql

2. 恢复数据库

mysql -u username -p database_name < backup.sql

总结

本文详细介绍了MySQL数据库和数据表的基本操作，包括： 1. 数据库的创建、查看、修改和删除 2. 数据表的结构操作和内容操作 3. 各种约束和索引的使用 4. 视图、存储过程和事务控制 5. 用户权限管理和数据备份恢复

掌握这些基础操作是进行MySQL数据库开发和管理的前提，建议读者在实际环境中多加练习，逐步深入理解各种操作的细节和适用场景。 “`

这篇文章共计约2100字，采用Markdown格式编写，包含了MySQL数据库和数据表操作的全面指南，从基础操作到进阶功能都有涉及，并提供了大量实用的SQL示例代码。