Wickt42's Tech Blog HomeArchivesTagsCategoriesSlides
mysql
|MySQLmysql数据库入门

mysql端口号3306

mysql数据库操作

  • CREATE DATABASE 数据库名;： 创建一个新数据库。
    • 示例： CREATE DATABASE my_first_db;
  • SHOW DATABASES;： 查看当前服务器上所有数据库。
  • USE 数据库名;： 选择/切换到要操作的数据库。 执行表操作前必须先 USE。
    • 示例： USE my_first_db;
  • DROP DATABASE 数据库名;： 删除一个数据库及其所有内容。

mysql表操作

CREATE TABLE 表名 (列名1 数据类型1 [约束], 列名2 数据类型2 [约束], ...);： 创建一张新表。需要定义列名和每列的数据类型。

  • 常用数据类型:
    • INT： 整数（如用户ID、年龄）
    • VARCHAR(长度)： 可变长度字符串（如姓名、邮箱，VARCHAR(50) 表示最长50个字符）
    • CHAR(长度)： 固定长度字符串（如国家代码 CHAR(2)）
    • DATE： 日期（YYYY-MM-DD）
    • DATETIME / TIMESTAMP： 日期和时间（YYYY-MM-DD HH:MM:SS）
    • DECIMAL(M, D)： 精确小数（如价格 DECIMAL(10, 2) 表示共10位，小数占2位）
    • TEXT： 长文本（如文章内容）
  • 常用约束:
    • PRIMARY KEY： 主键，唯一标识一行记录，不能为空（NULL）。一张表通常有一个主键（如 ID INT PRIMARY KEY）。
    • AUTO_INCREMENT： 自动递增，常用于主键（如 ID INT PRIMARY KEY AUTO_INCREMENT）。
    • NOT NULL： 该列的值不能为空。
    • UNIQUE： 该列的值在表中必须唯一（如邮箱）。
    • DEFAULT 默认值： 设置列的默认值（如 注册日期 DATE DEFAULT CURRENT_DATE）。
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL, -- 实际存储应为加密后的哈希值
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    description TEXT,
    stock INT NOT NULL DEFAULT 0
);
  • SHOW TABLES;： 查看当前数据库中所有的表。
  • DESCRIBE 表名; 或 DESC 表名;： 查看表的结构（有哪些列，数据类型，约束等）。
  • DROP TABLE 表名;： 删除一张表及其所有数据。
  • ALTER TABLE 表名 ...： 修改现有表结构（如添加列、修改列类型、删除列、添加约束等）。初学者先了解，熟练基础 CRUD 后再深入。
    • 示例： ALTER TABLE users ADD COLUMN age INT; (给 users 表添加一个 age 列)

mysql数据操作

插入数据

INSERT INTO 表名 (列1, 列2, 列3, ...) VALUES (值1, 值2, 值3, ...);


INSERT INTO users (username, email, password) VALUES ('john_doe', 'john@example.com', 'hashed_password123');		# 指定列


INSERT INTO products (name, price, description) VALUES ('Laptop', 999.99, 'High performance laptop');			# 省略自增主键 id 和默认时间 created_at


INSERT INTO products (name, price) VALUES
('Mouse', 29.99),
('Keyboard', 59.99),
('Monitor', 199.99);
# 插入多行

查询数据

SELECT 列1, 列2, ... FROM 表名 [WHERE 条件] [ORDER BY 列名 [ASC|DESC]] [LIMIT 数量];
  • SELECT * FROM 表名;： 查询该表所有列的所有数据。
  • 选择特定列： SELECT username, email FROM users;
  • WHERE 子句 (过滤条件):
    • =： 等于 (WHERE id = 1;)
    • >， <， >=， <=： 大于、小于等 (WHERE price > 50;)
    • <> 或 !=： 不等于 (WHERE username <> 'admin';)
    • BETWEEN ... AND ...： 在某个范围内（包含边界） (WHERE price BETWEEN 10 AND 100;)
    • LIKE： 模糊匹配 (WHERE email LIKE '%@gmail.com'; - % 代表任意字符任意长度)
    • IN (...)： 在某个值列表中 (WHERE id IN (1, 3, 5);)
    • AND / OR： 组合多个条件 (WHERE price > 50 AND stock > 0;)
    • IS NULL / IS NOT NULL： 检查空值 (WHERE description IS NOT NULL;)
  • ORDER BY 子句 (排序):
    • ORDER BY price; (默认升序 ASC)
    • ORDER BY created_at DESC; (按注册日期降序，最新在前)
    • ORDER BY price ASC, name DESC; (先按价格升序，价格相同再按名称降序)
  • LIMIT 子句 (限制返回行数):
    • LIMIT 10; (返回前10行)
    • LIMIT 5, 10; 或 LIMIT 10 OFFSET 5; (跳过前5行，返回接下来的10行 - 常用于分页)
SELECT name, price FROM products
WHERE price > 50 AND stock > 0
ORDER BY price DESC
LIMIT 5;
-- 查询库存大于0且价格大于50的产品名称和价格，按价格从高到低排序，只取最贵的5个

更新数据

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

UPDATE 语句必须包含 WHERE 条件，否则会更新表中所有记录

# 更新单个用户密码
UPDATE users SET password = 'new_hashed_pass' WHERE id = 1;


# 给所有库存小于5的产品加价10%
UPDATE products SET price = price * 1.1 WHERE stock < 5;

在执行 UPDATE 前，先用 SELECT 语句带上相同的 WHERE 条件检查一下会影响到哪些行。确认无误后再执行 UPDATE。

删除数据

DELETE FROM 表名 WHERE 条件;

DELETE 语句必须包含 WHERE 条件，否则会删除表中所有记录

DELETE FROM users WHERE id = 10;
  • 优先考虑逻辑删除：增加一个状态列（如 is_deleted TINYINT(1) DEFAULT 0），用 UPDATE 标记为删除 (SET is_deleted = 1)，而不是物理删除。查询时过滤掉 is_deleted=1 的记录。
  • 必须在执行 DELETE 前，先用 SELECT * FROM 表名 WHERE 条件; 确认要删除的行。
  • TRUNCATE TABLE 表名;： 更快地删除表中所有数据，并重置自增计数器。不能带 WHERE 条件。 通常只用于清空测试数据。

扩展

聚合函数与分组（group by）

  • 用于对一组数据进行计算并返回单个值。
  • 常用聚合函数：
    • COUNT()： 计数 (COUNT(*) 所有行数, COUNT(列名) 非 NULL 值的行数)
    • SUM()： 求和
    • AVG()： 平均值
    • MIN()： 最小值
    • MAX()： 最大值
  • GROUP BY： 将结果集按一个或多个列分组，然后对每个组应用聚合函数。
  • HAVING： 对 GROUP BY 分组后的结果进行过滤（类似于 WHERE，但作用于分组后的结果）。
-- 统计每个产品类别的平均价格 (假设有个 category_id 列)
SELECT category_id, AVG(price) AS avg_price
FROM products
GROUP BY category_id
HAVING avg_price > 100; -- 只显示平均价格大于100的类别


-- 统计用户总数
SELECT COUNT(*) AS total_users FROM users;

表连接

  • 关系型数据库的核心！用于从多个相关联的表中组合数据。

  • 内连接 (INNER JOIN 或 JOIN): 返回两个表中匹配的行。最常用。

    SELECT orders.order_id, customers.customer_name, orders.order_date
    FROM orders
    INNER JOIN customers ON orders.customer_id = customers.customer_id;
    -- 获取订单ID、客户名称和订单日期（只显示有匹配客户的订单）

  • 左外连接 (LEFT JOIN 或 LEFT OUTER JOIN): 返回左表 (FROM 后的表) 的所有行，即使右表中没有匹配的行。如果右表无匹配，则右表列显示为 NULL。

    SELECT customers.customer_name, orders.order_id
    FROM customers
    LEFT JOIN orders ON customers.customer_id = orders.customer_id;
    -- 获取所有客户及其订单ID（即使该客户没有订单）

  • 右外连接 (RIGHT JOIN): 与左连接相反（较少使用，通常可用左连接重写）。

  • 理解连接条件 (ON): ON 子句指定如何连接两个表（通常是主键 = 外键）。

基础函数

  • 字符串函数:
    • CONCAT(str1, str2, ...)： 连接字符串 (SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;)
    • LENGTH(str) / CHAR_LENGTH(str)： 字符串长度（字节/字符）
    • UPPER(str) / LOWER(str)： 转大写/小写
    • SUBSTRING(str, start, length)： 截取子字符串
    • TRIM(str)： 去除两端空格
  • 日期时间函数:
    • NOW() / CURRENT_TIMESTAMP： 当前日期和时间
    • CURDATE()： 当前日期
    • CURTIME()： 当前时间
    • DATE(日期时间)： 提取日期部分
    • DATEDIFF(date1, date2)： 计算两个日期相差的天数
    • DATE_ADD(date, INTERVAL expr unit) / DATE_SUB： 日期加减 (SELECT DATE_ADD(NOW(), INTERVAL 1 DAY);)
  • 数值函数:
    • ROUND(number, decimals)： 四舍五入
    • FLOOR(number) / CEIL(number)： 向下/向上取整
    • ABS(number)： 绝对值
    • RAND()： 生成随机数 (0 到 1)

mysql自带数据库

information_schema（元数据仓库）

  • 核心作用：提供MySQL实例中所有数据库、表、列、索引、权限等元数据的只读视图。例如：
    • TABLES 表存储所有表的结构信息
    • COLUMNS 表记录字段名、数据类型等
    • 查询示例：SELECT TABLE_NAME, COLUMN_NAME FROM COLUMNS WHERE TABLE_SCHEMA='your_db'; 137
  • 特点：数据动态生成于内存，不占用物理存储，不可修改。

关键表结构

表名 核心字段 典型查询场景
SCHEMATA SCHEMA_NAME（数据库名） 列出所有数据库：SELECT SCHEMA_NAME FROM SCHEMATA;
TABLES TABLE_SCHEMA, TABLE_NAME, ENGINE, TABLE_ROWS, AVG_ROW_LENGTH 查询指定库的表信息： SELECT * FROM TABLES WHERE TABLE_SCHEMA='mydb';
COLUMNS COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT, COLUMN_COMMENT 获取表结构： SELECT * FROM COLUMNS WHERE TABLE_NAME='users';
STATISTICS INDEX_NAME, SEQ_IN_INDEX, COLUMN_NAME, NON_UNIQUE 查看索引详情： SELECT * FROM STATISTICS WHERE TABLE_SCHEMA='mydb' AND TABLE_NAME='orders';

权限与约束

表名 核心信息
TABLE_PRIVILEGES 用户表级权限（SELECT/INSERT等）
COLUMN_PRIVILEGES 字段级精细权限
REFERENTIAL_CONSTRAINTS 外键约束关系（MySQL 8.0+ 强化）
KEY_COLUMN_USAGE 主键/唯一键约束字段

高级对象

表名 描述
ROUTINES 存储过程与函数（ROUTINE_TYPE 区分）
TRIGGERS 触发器定义及事件类型
EVENTS 定时任务（事件调度器）
VIEWS 视图定义及检查选项

mysql（核心安全与配置库）

  • 存储内容：
    • 用户账户（user 表）及全局权限（db、tables_priv 等表）
    • 密码哈希、权限规则、存储过程、事件定义
    • 时区配置、插件信息等147
  • 关键性：MySQL启动时首先读取此库验证用户权限，删除将导致所有用户无法登录。

performance_schema（性能监控引擎）

  • 功能：实时采集底层资源消耗数据，包括：
    • 线程活动（threads 表）
    • 锁等待（events_waits）
    • SQL执行统计（events_statements_summary）
    • 内存使用（memory_summary）179
  • 特点：数据存储在内存表，重启后清空。需配置启用（默认部分开启）。

sys（性能诊断助手）

  • 定位：基于 information_schema 和 performance_schema 的友好视图库，简化监控。
    • 提供预设查询：如 schema_table_lock_waits（表锁阻塞分析）
    • 常用视图：session（当前会话资源消耗）、statement_analysis（慢SQL排名）137
  • 依赖：MySQL 5.7+ 默认存在，5.6需手动导入。

test（可选测试库，部分版本已弃用）

  • 用途：初始安装时用于示例操作，无重要数据。
  • 现状：MySQL 8.0+ 默认不创建，若存在建议删除以避免安全风险
Author: wickt42
Link: http://example.com/2025/08/16/mysql/
Copyright Notice: All articles in this blog are licensed under CC BY-NC-SA 4.0 unless stating additionally.
MySQL