创建表
老师表 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
- 根据某个字段修改状态;
UPDATE 单据表 SET STATUS = 'INIT' WHERE ID = '1';
- 多表修改,根据单据表的交易号修改支付表的状态;
UPDATE 单据表,支付表 SET STATUS = 'INIT'
WHERE 支付表.PAY_NO =
(SELECT 单据表.PAY_NO FROM 单据表 WHERE 单据表.TRADE_NO = '2024');
删除 DELETE
- 根据某个字段修改状态;
DELETE FROM 单据表 WHERE ID = '1';
- 多表修改,根据单据表的交易号修改支付表的状态;
DELETE FROM 单据表,支付表
WHERE 支付表.PAY_NO =
(SELECT 单据表.PAY_NO FROM 单据表 WHERE 单据表.TRADE_NO = '2024');