数据库 MySQL使用

数据格式

在 MySQL 中，数据以表的形式存储，每个表由若干列组成，每列有特定的数据类型。以下是一个示例表的结构：

1
2
3
4
5
6
7
8
9

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,  -- 主键，唯一标识每行，自动递增
    name VARCHAR(50) NOT NULL,          -- 用户名，最大长度50字符，不能为空
    age INT,                            -- 用户年龄
    DECIMAL

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP -- 创建时间，默认为当前时间
);
  • 数据类型
数据类型 描述 示例
INT 整型数据，通常用于存储计数或ID id INT AUTO_INCREMENT
VARCHAR 可变长度字符串，需指定最大长度 name VARCHAR(50)
DECIMAL 高精度定点数，适用于金额等数据 price DECIMAL(10, 2)
DATE 日期类型，格式为 YYYY-MM-DD birth_date DATE
TIMESTAMP 时间戳，存储日期和时间 created_at TIMESTAMP
BOOLEAN 布尔型数据,true或false is_active BOOLEAN
  • 设计要点

主键（Primary Key）：
唯一标识表中的每一行,通常使用 AUTO_INCREMENT 来生成唯一的主键值。

外键（Foreign Key）：
用于在不同表之间建立关系，确保数据一致性。
示例：

1
2
3
4
5
CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

默认值（Default）：

使用 DEFAULT 为某列设置默认值，避免插入数据时遗漏该列。
示例：

1
2
3
4
5
CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) DEFAULT 0.00
);

非空约束（NOT NULL）：
确保某列不能为空，避免数据缺失。

唯一约束（UNIQUE）：
保证某列的值在整个表中唯一。
示例：

1
2
3
4
CREATE TABLE employees (
    emp_id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(100) UNIQUE
);

数据库管理

  • 显示所有数据库

    1
    2

    SHOW DATABASES;

  • 创建数据库

    1
    2

    CREATE DATABASE 数据库名;

  • 切换数据库

    1
    2

    USE 数据库名;

  • 删除数据库

    1
    2

    DROP DATABASE 数据库名;

表管理

  • 显示所有表
    1
    2

    SHOW TABLES;
  • 创建表
    1
    2
    3
    4
    5

    CREATE TABLE 表名 (
        列名 数据类型 [约束条件],
        ...
    );
    示例：
1
2
3
4
5
6
7

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    stock INT DEFAULT 0
);
  • 删除表
    1
    2

    DROP TABLE 表名;

数据操作

  • 插入数据
    1
    2

    INSERT INTO 表名 (列名1, 列名2, ...) VALUES (值1, 值2, ...);
    示例：
1
2

INSERT INTO users (name, age) VALUES ('Alice', 25);
  • 查询数据
    查询所有数据：
    1
    2

    SELECT * FROM 表名;
    查询指定条件的数据：
    1
    2

    SELECT * FROM 表名 WHERE 条件;
    示例：
1
2

SELECT * FROM users WHERE age > 20;
  • 更新数据
    1
    2

    UPDATE 表名 SET 列名1 = 新值1, 列名2 = 新值2, ... WHERE 条件;

示例：

1
2

UPDATE users SET age = 30 WHERE name = 'Alice';
  • 删除数据
    1
    2

    DELETE FROM 表名 WHERE 条件;
    示例：
1
2

DELETE FROM users WHERE name = 'Alice';

操作符

  • 比较操作符
操作符 含义
= 等于
< 小于
<= 小于等于
> 大于
>= 大于等于
<> 或 != 不等于

示例：

1
2

SELECT * FROM users WHERE age >= 18;
  • 逻辑操作符
操作符 含义
AND 与
OR 或
NOT 非

示例：

1
2

SELECT * FROM users WHERE age > 20 AND name = 'Alice';
  • 模糊匹配

LIKE：匹配特定模式
%：匹配任意数量的字符
_：匹配单个字符

示例：

1
2

SELECT * FROM users WHERE name LIKE 'A%';

用户管理

  • 显示用户
    1
    2

    SELECT user FROM mysql.user;
  • 创建用户
    1
    2

    CREATE USER '用户名'@'主机' IDENTIFIED BY '密码';
    示例：
1
2

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password123';
  • 删除用户
    1
    2

    DROP USER '用户名'@'主机';
    示例：
1
2

DROP USER 'newuser'@'localhost';
  • 分配权限
    1
    2

    GRANT 权限 ON 数据库名.表名 TO '用户名'@'主机';
    示例：
1
2

GRANT SELECT, INSERT ON testdb.* TO 'newuser'@'localhost';
  • 显示用户权限
    1
    2

    SHOW GRANTS FOR '用户名'@'主机';
    示例：
1
2

SHOW GRANTS FOR 'newuser'@'localhost';
  • 撤销权限
    1
    2

    REVOKE 权限 ON 数据库名.表名 FROM '用户名'@'主机';
    示例：
1
2

REVOKE INSERT ON testdb.* FROM 'newuser'@'localhost';

常见操作

  • 备份数据库
    1
    2

    mysqldump -u 用户名 -p 数据库名 > 数据库名.sql
  • 恢复数据库
    1
    2

    mysql -u 用户名 -p 数据库名 < 数据库名.sql