坚持 学习

创建表

老师表 t_teacher

-- 创建老师表
create table t_teacher
(
    teacher_id int PRIMARY KEY,
    teacher_name varchar(10) null,
    teacher_subject varchar(10) null,
    teacher_class varchar(10) null
);

新增数据

INSERT INTO t_teacher VALUES ( 1, '李四', '语文', '七年级一班');
INSERT INTO t_teacher VALUES ( 2, '王二', '语文', '七年级二班');
INSERT INTO t_teacher VALUES ( 3, '张三', '数学', '七年级一班');
INSERT INTO t_teacher VALUES ( 4, '王五', '数学', '七年级二班');

学生表 t_student

-- 创建学生表
create table t_student
(
    student_id int PRIMARY KEY,
    student_name varchar(10) null,
    teacher_class varchar(10) null
);

新增数据

INSERT INTO t_student VALUES ( 1, '小龙', '七年级一班');
INSERT INTO t_student VALUES ( 2, '小华', '七年级一班');
INSERT INTO t_student VALUES ( 3, '小甲', '七年级二班');
INSERT INTO t_student VALUES ( 4, '小乙', '七年级二班');
INSERT INTO t_student VALUES ( 5, '小丙', '七年级二班');
INSERT INTO t_student VALUES ( 6, '乾坤', '七年级一班');

分数表 t_score

-- 创建分数表
create table t_score
(
    score_id int PRIMARY KEY,
    score_num int null,
    student_id int,
    teacher_id int
);

新增数据

INSERT INTO t_score VALUES ( 1, 96, 1 ,1);
INSERT INTO t_score VALUES ( 2, 32, 1 ,3);
INSERT INTO t_score VALUES ( 3, 60, 2 ,1);
INSERT INTO t_score VALUES ( 4, 60, 2 ,3);
INSERT INTO t_score VALUES ( 5, 78, 3 ,2);
INSERT INTO t_score VALUES ( 6, 67, 3 ,4);
INSERT INTO t_score VALUES ( 7, 92, 4 ,2);
INSERT INTO t_score VALUES ( 8, 81, 4 ,4);
INSERT INTO t_score VALUES ( 9, 75, 5 ,2);
INSERT INTO t_score VALUES ( 10, 73, 5 ,4);

查询 SELECT

单表查询

全表查询

--单查询
--查出学生表的数据
SELECT * FROM t_student;

--单条件查询，查出“七年级一班”有哪几位学生
SELECT * FROM t_student WHERE teacher_class = '七年级一班';

--条件查询，查出“七年级一班”有哪几位学生，只看学生名字
SELECT student_name FROM t_student WHERE teacher_class = '七年级一班';

多条件查询

--查出七年级一班的语文老师是谁？
SELECT * FROM t_teacher WHERE teacher_class = '七年级一班' AND teacher_subject = '语文';

多表查询

多表查询的关键就是先清楚多个表的关联字段，然后再获取再把两个连接起来就好了；

--查出 张三 老师班里都有哪些学生；
SELECT t_student.*,t_teacher.* FROM t_teacher,t_student
WHERE t_teacher.teacher_name = '张三'
AND t_teacher.teacher_class = t_student.teacher_class;

分组查询

--查出数学科目，哪位老师带领的班级的总分最高
SELECT t_teacher.teacher_name,SUM(t_score.score_num), t_score.teacher_id 
FROM t_score,t_teacher 
WHERE t_score.teacher_id = t_teacher.teacher_id
AND t_teacher.teacher_subject = '数学'
GROUP BY t_score.teacher_id;

排序查询

 --给学生的分数排序,到排序
SELECT t_student.student_name,t_student.teacher_class,t_score.score_num 
FROM t_score,t_student
WHERE t_score.student_id = t_student.student_id
ORDER BY t_score.score_num DESC;

 --正排序，默认排序,ASC可以不写
SELECT t_student.student_name,t_student.teacher_class,t_score.score_num 
FROM t_score,t_student
WHERE t_score.student_id = t_student.student_id
ORDER BY t_score.score_num;

in查询

--查出学生小龙和小花的分数
SELECT student.student_name,score.score_num FROM t_score score,t_student student 
WHERE score.student_id = student.student_id
AND score.student_id IN 
(SELECT student_id FROM t_student WHERE student_name IN ('小龙','小华'));

BETWEEN AND查询

--查出学生编号是1和2的两名学生
SELECT student_name FROM t_student WHERE student_id BETWEEN '1' AND '2';

LIKE查询

 --查出 七年级 的学生；
SELECT student_name,teacher_class FROM t_student WHERE teacher_class LIKE '七年级%';

左右连接

 --LEFT JOIN ON，以左表为主，查出以分数表为主的所有信息；不包括乾坤同学的数据；
SELECT student.*,score.* FROM t_score score LEFT JOIN t_student student 
ON score.student_id = student.student_id;

--RIGHT JOIN ON，以左表为主，查出以学生表为主的所有信息；包括乾坤同学；
SELECT student.*,score.* FROM t_score score RIGHT JOIN t_student student 
ON score.student_id = student.student_id;

IF函数

--score.score_num < 60是条件，如果满足条件，返回第一个结果集（‘不及格’），否则返回第二个结果集；
SELECT student.student_name,teacher.teacher_subject,IF( score.score_num < 60, '不及格', '及格')
FROM t_score score,t_student student,t_teacher teacher
WHERE score.student_id = student.student_id
AND score.teacher_id = teacher.teacher_id;

--加个别名,还给它起个名字，as后面跟着别名
IF( score.score_num < 60, '不及格', '及格') AS score_num
--AS 可以省略
IF( score.score_num < 60, '不及格', '及格') score_num

CASE WHEN 函数

--简单的CASE WHEN函数，WHEN后面的score.score_num < 60是条件，若满足条件，返回THEN后面的结果集，否则返回ELSE后面的结果集，END是结束，AS是别名；
SELECT student.student_name,teacher.teacher_subject,CASE WHEN score.score_num < 60 THEN '不及格' ELSE '及格' END AS score_num
FROM t_score score,t_student student,t_teacher teacher
WHERE score.student_id = student.student_id
AND score.teacher_id = teacher.teacher_id;

--CASE WHEN函数，多个条件时，可以多个WHEN，如下，大于等于90是优秀，
--大于等于75小于90是良好，大于等于60小于75是及格，否则不及格；
SELECT student.student_name,teacher.teacher_subject,score.score_num,
CASE 
  WHEN score.score_num >= 90 THEN '优秀' 
  WHEN score.score_num >= 75 AND score.score_num < 90 THEN '良好' 
  WHEN score.score_num >= 60 AND score.score_num < 75 THEN '及格'
  ELSE '不及格'
  END AS 是否及格
FROM t_score score,t_student student,t_teacher teacher
WHERE score.student_id = student.student_id
AND score.teacher_id = teacher.teacher_id;

比如想看下同一交易号，单据表的金额和支付表总金额是否一致，一致的话就是正常，不一致就提示错误；

select 单据表.trade_no,
CASE 
WHEN 单据表.amount = 支付表总和表.amount THEN '正常' 
ELSE '异常' 
END AS '金额校验',单据表.amount,支付表总和表.amount
from 单据表,
(select sum(amount) amount,trade_no from 支付表 group by trade_no) 支付表总和表 
where 单据表.trade_no = 支付表总和表.trade_no;

修改 UPDATE

1. 根据某个字段修改状态；

 UPDATE 单据表 SET STATUS = 'INIT' WHERE ID = '1';

2. 多表修改，根据单据表的交易号修改支付表的状态；

 UPDATE 单据表,支付表 SET STATUS = 'INIT'
 WHERE 支付表.PAY_NO = 
 (SELECT 单据表.PAY_NO FROM 单据表 WHERE 单据表.TRADE_NO = '2024');

删除 DELETE

1. 根据某个字段修改状态；

 DELETE FROM 单据表 WHERE ID = '1';

2. 多表修改，根据单据表的交易号修改支付表的状态；

DELETE FROM 单据表,支付表 
WHERE 支付表.PAY_NO = 
(SELECT 单据表.PAY_NO FROM 单据表 WHERE 单据表.TRADE_NO = '2024');