MySQL基础

发表于 更新于 分类于 本文字数： 1.6k 阅读时长 ≈ 6 分钟
MySQL基础知识, 持续更新中...

SQL

SQL通用语法

  1. SQL语句可以单行或多行书写，以分号结尾。
  2. SQL语句可以使用空格/缩进来增强语句的可读性。
  3. MySQL数据库的SQL语句不区分大小写，关键字建议使用大写。
  4. 注释：
    • 单行注释：–注释内容 或 #注释内容(MySQL特有)
    • 多行注释：/*注释内容*/

DDL(数据定义语言)

  1. 数据库操作：
  • SHOW DATABASES;
  • CREAT DATABASE 数据库名;
  • USE 数据库名;
  • SELECT DATABASE();
  • DROP DATABASE 数据库名;
  1. 表操作：
  • SHOW TABLES;
  • CREATE TABLE 表名 (字段1 数据类型,字段2 数据类型,…);
  • DESC 表名;
  • SHOW CREATE TABLE 表名;
  • ALTER TABLE 表名 ADD 字段 数据类型;
  • DROP TABLE 表名;

DML(数据操作语言)

  • 添加数据：INSERT INTO 表名 (字段1,字段2,…) VALUES (值1,值2,…),(值1,值2,…);
1
2
3
4
INSERT INTO students (student_id, name, age)
VALUES
   (1001, '张三', 20),
   (1002, '李四', 22);
  • 修改数据：UPDATE 表名 SET 字段1=值1,字段2=值2,… WHERE 条件;
1
2
3
UPDATE students 
SET name = '李四', age = 22 
WHERE student_id = 1001;
  • 删除数据：DELETE FROM 表名 WHERE 条件;
1
2
DELETE FROM students 
WHERE student_id >= 1005;

DQL(数据查询语言)

SELECT
  字段列表   ->   字段名 [AS] 别名
FROM
  表名
WHERE
  条件(分组前过滤)   ->   比较符号, 逻辑符号, like, between and, in,
GROUP BY
  分组字段列表
HAVING
  分组后条件列表(分组后过滤)
ORDER BY
  排序列表   ->   升序 ASC; 降序 DESC
LIMIT
  分页条件(起始索引; 获取条数)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT
    customer_id AS 客户ID,
    product_category AS 产品类别,
    SUM(amount) AS total_amount  -- 计算每组的总金额，并为结果列起别名
FROM
    orders
WHERE
    order_date > '2024-01-01'    -- 分组前过滤：仅筛选2024年后的订单
GROUP BY
    customer_id, product_category  -- 按客户和产品类别分组
HAVING
    total_amount > 5000           -- 分组后过滤：仅保留总金额超过5000的分组
ORDER BY
    total_amount DESC             -- 按总金额降序排序
LIMIT
    5 OFFSET 0;                  -- 分页：返回前5条记录

DCL(数据控制语言)

  1. 用户管理：
  • CREATE USER ‘用户名‘@‘主机名’ IDENTIFIED BY ‘密码’;
  • ALTER USER ‘用户名‘@‘主机名’ IDENTIFIED WITH mysql_native_password BY ‘新密码’;
  • DROP USER ‘用户名‘@‘主机名’;
  1. 权限控制：
  • SHOW GRANTS FOR ‘用户名‘@‘主机名’;
  • GRANT 权限列表 ON 数据库名.表名 TO ‘用户名‘@‘主机名’;
  • REVOKE 权限列表 ON 数据库名.表名 FROM ‘用户名‘@‘主机名’;

函数

字符串函数

  • CONCAT: 连接字符串

    CONCAT(‘a’,’b’,’c’) == ‘abc’

  • LOWER, UPPER: 大小写转换

    LOWER(‘ABC’) == ‘abc’

  • LPAD, RPAD: 左/右填充字符串

    LPAD(‘abc’,5,’*’) == ‘**abc’

  • TRIM: 去除字符串首尾空格

    TRIM(‘ abc ‘) == ‘abc’

  • SUBSTRING: 截取字符串

    SUBSTRING(‘Hello MySQL’,1,5) == ‘Hello’

数值函数

  • CEIL: 向上取整

    CEIL(3.14) == 4

  • FLOOR: 向下取整

    FLOOR(3.14) == 3

  • ROUND: 四舍五入

    ROUND(3.14) == 3

  • MOD: 取余数

    MOD(5,2) == 1

  • RAND: 随机数

    RAND()

日期函数

  • CURDATE: 当前日期
1
2
SELECT CURDATE(); -- 输出：2025-03-10  
INSERT INTO orders (product, order_date) VALUES ('Laptop', CURDATE()); -- 插入当前日期
  • CURTIME: 当前时间
1
2
SELECT CURTIME(); -- 输出：14:30:45  
UPDATE logs SET login_time = CURTIME() WHERE user_id = 1001; -- 记录登录时间
  • NOW: 当前日期和时间
1
2
3
4
5
SELECT NOW(); -- 输出：2025-03-10 14:30:45  
CREATE TABLE events (  
    event_id INT,  
    event_time DATETIME DEFAULT NOW()  -- 默认插入当前时间戳
);
  • YEAR: 获取年份
1
2
SELECT YEAR('2025-03-10'); -- 输出：2025  
SELECT YEAR(order_date) AS order_year, COUNT(*) FROM orders GROUP BY order_year; -- 按年份统计订单
  • MONTH: 获取月份
1
2
SELECT MONTH('2025-03-10'); -- 输出：3  
SELECT MONTH(sale_date) AS month, SUM(revenue) FROM sales GROUP BY month; -- 按月统计收入
  • DAY: 获取日期
1
2
SELECT DAY('2025-03-10'); -- 输出：10  
SELECT DAY(login_date) AS day, COUNT(user_id) FROM logins GROUP BY day; -- 分析每日活跃用户
  • DATE_ADD, DATE_SUB: 日期加减
1
2
3
4
5
6
-- 加7天  
SELECT DATE_ADD('2025-03-10', INTERVAL 7 DAY); -- 输出：2025-03-17  
-- 减3个月  
SELECT DATE_SUB('2025-03-10', INTERVAL 3 MONTH); -- 输出：2024-12-10  
-- 计算会员到期时间  
SELECT user_id, DATE_ADD(subscribe_date, INTERVAL 1 YEAR) AS expire_date FROM subscriptions
  • DATEDIFF: 日期差
1
2
3
SELECT DATEDIFF('2025-03-10', '2025-02-01'); -- 输出：37  
-- 分析项目周期  
SELECT project_id, DATEDIFF(end_date, start_date) AS duration FROM projects
  • DATE_FORMAT: 日期格式化
1
2
3
4
-- 格式化为 "年-月-日 时:分:秒"  
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s'); -- 输出：2025-03-10 14:30:45  
-- 生成中文格式日期  
SELECT DATE_FORMAT(NOW(), '%Y年%m月%d日 %H时') AS formatted_date; -- 输出：2025年03月10日 14时

流程函数

  • IF: 条件判断, 例: IF(条件,真值,假值)
  • IFNULL: 判断是否为NULL, 例: IFNULL(字段,’默认值’)
  • CASE […] WHEN… THEN… ELSE… END: 多条件判断, 例: CASE WHEN 条件1 THEN 真值1 WHEN 条件2 THEN 真值2 ELSE 假值 END

约束

  • NOT NULL: 非空约束, 例: 字段名 INT NOT NULL
  • UNIQUE: 唯一约束, 例: 字段名 INT UNIQUE
  • PRIMARY KEY: 主键约束, 例: 字段名 INT PRIMARY KEY
  • FOREIGN KEY: 外键约束, 建立两个表的连接, 确保数据的一致性和完整性, 例: 字段名 INT FOREIGN KEY REFERENCES 表名(字段名)
  • CHECK: 检查约束, 例: 字段名 INT CHECK(字段名 > 0)
  • DEFAULT: 默认值约束, 例: 字段名 INT DEFAULT 0

多表查询

  1. 多表关系：
    • 一对多：在多的一方设置外键，关联一的一方的主键
    • 多对多：建立中间表，中间表包含两个外键，关联两张表的主键
    • 一对一：用于表结构拆分，在其中任何一方设置外键(UNIQUE)，关联另一方的主键
  2. 多表查询：
    • 内连接：
      • 隐式：SELECT * FROM 表1,表2 WHERE 表1.字段 = 表2.字段;
      • 显示：SELECT * FROM 表1 INNER JOIN 表2 ON 表1.字段 = 表2.字段;
    • 外连接：
      • 左外：SELECT * FROM 表1 LEFT OUTER JOIN 表2 ON 表1.字段 = 表2.字段;
      • 右外：SELECT * FROM 表1 RIGHT OUTER JOIN 表2 ON 表1.字段 = 表2.字段;
    • 自连接：SELECT * FROM 表1 t1,表1 t2 WHERE t1.字段 = t2.字段;
    • 子查询：SELECT * FROM 表1 WHERE 字段 IN (SELECT 字段 FROM 表2);

事务

  1. 事务简介：
    • 事务是一组操作的集合，这组操作，要么全部执行成功，要么全部执行失败
  2. 事务操作：
    • 开启事务：SET AUTOCOMMIT = 0;
    • 提交事务：COMMIT;
    • 回滚事务：ROLLBACK;
  3. 事务的四大特性:
    • 原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）
  4. 并发事务问题：
    • 脏读：一个事务读取了另一个事务未提交的数据
    • 不可重复读：一个事务读取了另一个事务修改的数据
    • 幻读：一个事务新增了数据，另一个事务读取了新增的数据
  5. 隔离级别：
    • READ UNCOMMITTED：读未提交，允许脏读、不可重复读、幻读
    • READ COMMITTED：读已提交，允许脏读、不可重复读，允许幻读
    • REPEATABLE READ：可重复读，不允许脏读、不可重复读，允许幻读
    • SERIALIZABLE：串行化，不允许脏读、不可重复读、幻读