MYSQL学习

-- 1. 指定字段插入（推荐，灵活性高）
insert into 表名 (字段1,字段2,...) value (数据1,数据2,...);

-- 2. 插入所有列数据（需按表中列的顺序填写数据）
insert into 表名 value(数据1,数据2,数据3,...);

-- 3. 批量插入多条数据
insert into 表名 
value (数据1-1,数据1-2,...),
      (数据2-1,数据2-2,...),
      (数据3-1,数据3-2,...);

-- 删除指定条件的数据（例：删除 student 表中姓名为“王刚”的记录）
delete from student where name='王刚';

-- 修改指定条件的数据（例：修改 student 表中姓名为“李白”的记录，将 age 改为 20）
update student set age=20 where name='李白';

-- 1. 查询表中所有数据（不推荐在工作环境使用）
select * from student;

-- 2. 查询指定列数据（例：查询 student 表的 student_id 和 student_name 列）
select student_id, student_name from student;

-- 使用 AS 给列起别名（若别名是关键字，可用着重号 `` 包裹）
select student_id as '学生ID', student_name as `name` from student;

-- 单条件查询（例：查询 student 表中姓名为“李四”的学生信息）
select student_id, student_name, birthday, gender from student 
where student_name='李四';

-- 多条件查询（AND 逻辑：同时满足）
select student_id, student_name, birthday, gender from student 
where student_name='李四' and student_id = 9;

-- 多条件查询（OR 逻辑：满足任一）
select student_id, student_name, birthday, gender from student 
where student_name='李四' or gender = '女';

-- 1. 匹配指定集合中的值（例：查询 student_id 为 1、3、8、10、12 的学生）
SELECT * FROM student WHERE student_id IN (1,3,8,10,12);

-- 2. NOT IN 排除指定集合（例：查询 student_id 不在 1、3、8、10、12 中的学生）
SELECT * FROM student WHERE student_id NOT IN (1,3,8,10,12);

-- 1. 姓名以“张”开头（例：张三、张伟、张小明等）
SELECT * FROM student WHERE student_name LIKE '张%';

-- 2. 姓名是 3 个字（例：张三丰、李四海等）
SELECT * FROM student where student_name LIKE '___';

-- 3. 姓名是 2 个字且以“张”开头（例：张三、张伟等）
SELECT * FROM student WHERE student_name LIKE '张_';

-- 4. 姓名中包含“张”（例：张三、李张三、张四等）
SELECT * FROM student WHERE student_name LIKE '%张%';

-- 5. 名字中没有“张”字的学生
SELECT * FROM student WHERE student_name NOT LIKE '%张%';

-- 解析：`\%` 表示“普通字符 %”，而非通配符 → 匹配“折扣 80%”“优惠 80%”等
SELECT product_name FROM product WHERE product_desc LIKE '%80\%'; 

-- 1. 单字段排序（例：按 student_id 降序查询）
SELECT * FROM student ORDER BY student_id DESC;

-- 2. 多字段排序（例：先按 student_name 升序，再按 student_id 降序）
-- 规则：先按第一个字段排序，第一个字段值相同时，再按第二个字段排序，以此类推。
SELECT * FROM student ORDER BY student_name ASC, student_id DESC;
-- 3. 排序字段可以是表达式或函数的计算结果，而非直接使用表中的列。(按 “字符串长度” 排序（使用 LENGTH() 函数）)
-- 按姓名长度升序排列（短名字在前，长名字在后）
SELECT student_name FROM student ORDER BY LENGTH(student_name) ASC;

-- 1. 查询前 3 条数据（起始索引默认 0，可省略）
select * FROM student LIMIT 3;  -- 等价于 LIMIT 0, 3

-- 2. 查询第 4-7 条数据（起始索引 3，查询 4 条）
select * from student limit 3, 4;

-- 3. 明确查询前 3 条（显式写起始索引 0，可读性更高）
select * from student limit 0, 3;

-- 格式1：UNION（去重合并）
SELECT 字段1, 字段2 FROM 表1 [WHERE 条件]
UNION
SELECT 字段1, 字段2 FROM 表2 [WHERE 条件];

-- 格式2：UNION ALL（保留重复合并）
SELECT 字段1, 字段2 FROM 表1 [WHERE 条件]
UNION ALL
SELECT 字段1, 字段2 FROM 表2 [WHERE 条件];

-- 错误：第一个查询返回2个字段，第二个返回1个字段
SELECT id, name FROM student
UNION
SELECT age FROM student;

-- 正确：id（int）与 num（int）兼容，name（varchar）与 title（varchar）兼容
SELECT id, name FROM student
UNION
SELECT num, title FROM course;

SELECT * FROM student WHERE age < 18 AND score > 90
UNION ALL  -- 已知两个条件无重叠，用 UNION ALL 提升性能
SELECT * FROM student WHERE age > 60 AND score < 60;

-- 例：查询 student 表中不重复的姓名（对结果集去重，非对表中字段去重）
SELECT DISTINCT name FROM student;

-- 场景1：查询有不及格成绩的学生信息（成绩 < 60 为不及格）
-- 方式一：多表关联 + DISTINCT 去重
SELECT DISTINCT * FROM student,score WHERE score.score < 60 and student.student_id = score.student_id;

-- 方式二：IN 子查询（更简洁）
SELECT student_name 
FROM student 
WHERE student_id IN (
    SELECT DISTINCT student_id 
    FROM score 
    WHERE score < 60
);

-- 场景2：查询总成绩最高的学生 ID（子查询作为临时表）
-- 注意：子查询作为表时，必须起别名（例：别名 a）
SELECT * 
FROM (
    SELECT student_id, SUM(score) as sumscore 
    FROM score 
    GROUP BY student_id 
) as a 
ORDER BY sumscore DESC 
LIMIT 1;

-- 1. 左连接（LEFT JOIN）
-- 规则：以左表（student）为主表，显示左表所有数据；右表（score）仅显示匹配关联条件的数据（不匹配则为 NULL）
SELECT s.*, sc.* 
FROM student s 
LEFT JOIN score sc 
ON s.student_id = sc.student_id;
-- 执行逻辑：先将 student 表中的每一行都加入到结果集中，然后在 score 表中查找与 student 表当前行 student_id 匹配的行，
-- 如果找到，就将对应的成绩字段值填入结果集；如果没找到，成绩字段就填入 NULL。

-- 2. 内连接（INNER JOIN）
-- 规则：仅显示两张表中“满足关联条件”的数据（无匹配则不显示）
SELECT s.*, sc.* 
FROM student s 
INNER JOIN score sc 
ON s.student_id = sc.student_id;
-- 执行逻辑：数据库会逐行检查 student 表和 score 表中的记录，
-- 只有当 student 表中的 student_id 与 score 表中的 student_id 相等时，才会将这两条记录对应的字段组合成一行加入到结果集中。

-- 3. 全外连接（FULL JOIN）
-- 说明：MySQL 不原生支持 FULL JOIN，需借助 UNION 合并左连接和右连接结果
SELECT s.*, sc.* 
FROM student s 
LEFT JOIN score sc 
ON s.student_id = sc.student_id
UNION
SELECT s.*, sc.* 
FROM student s 
RIGHT JOIN score sc 
ON s.student_id = sc.student_id;
-- 执行逻辑：先获取左连接的结果，再获取右连接的结果，最后通过 UNION 合并，去除重复行（如果使用 UNION ALL 则保留重复行）。

-- 1. 创建测试表
create table TEXT(
  id int,
  name VARCHAR(32)
);

-- 2. 开启事务
start TRANSACTION;

-- 3. 设置保存点 a
SAVEPOINT a;

-- 4. 执行 DML 操作（插入第一条数据）
INSERT INTO TEXT values(100,'tom');

-- 5. 设置保存点 b
SAVEPOINT b;

-- 6. 执行 DML 操作（插入第二条数据）
INSERT INTO TEXT values(200,'jack');

-- 7. 查看当前数据（此时数据未提交，仅当前会话可见）
select * from TEXT;  -- 结果：两条数据

-- 8. 回滚到保存点 b（撤销“插入 200,'jack'”的操作）
rollback to b;
select * from TEXT;  -- 结果：仅 100,'tom'

-- 9. 回滚到保存点 a（撤销“插入 100,'tom'”的操作）
rollback to a;
select * from TEXT;  -- 结果：空表

-- 10. （可选）提交事务（若执行 commit，后续无法回滚）
-- commit;

-- 1. 查看当前会话隔离级别
select @@transaction_isolation;

-- 2. 查看系统全局隔离级别
select @@global.transaction_isolation;

-- 3. 设置当前会话隔离级别（例：设置为“读未提交”）
set session transaction isolation level read uncommitted;

-- 4. 设置系统全局隔离级别（例：设置为“读未提交”）
set global transaction isolation level read uncommitted;