MYSQL学习日记(三)

1. 多表关联查询

1. 内连接

标准语法：

select * from 表1 inner join 表2 on  关联条件;

两个表中所有符合关联条件的两条数据，就拼接成一个数据，作为本次查询的一条结果。

select * from 表1 
inner join 表2 on  关联条件1
inner join 表3 on  关联条件2
...

所有表中，符合这些关联条件的数据，会组合成一条数据，作为查询的结果

# 1. 查询学生的信息及所在班级名称
SELECT * FROM student INNER JOIN clazz ON student.class_id = clazz.id;
SELECT * FROM student s INNER JOIN clazz c ON s.class_id = c.id;

# 2. 查询科目信息及代课老师姓名
SELECT * FROM `subject` s INNER JOIN teacher t ON s.teacher_id = t.id;

#3. 查询学生每门科目的成绩     学生的信息    科目名称   分数
SELECT * FROM student s 
INNER JOIN score sc ON s.id = sc.student_id
INNER JOIN `subject` su ON sc.subject_id = su.id;

#4. 查询学生每门科目的成绩     学生的信息    科目名称   分数   代课老师姓名
SELECT * FROM student s 
INNER JOIN score sc ON s.id = sc.student_id
INNER JOIN `subject` su ON sc.subject_id = su.id
INNER JOIN teacher t ON su.teacher_id = t.id;



# 5. 显示指定类的数据
SELECT s.`name`, s.`code`, su.`name`, sc.score, t.`name`  
FROM student s 
INNER JOIN score sc ON s.id = sc.student_id
INNER JOIN `subject` su ON sc.subject_id = su.id
INNER JOIN teacher t ON su.teacher_id = t.id;

# 6. 查询学生姓名， 编号，所在班级，  科目名称， 分数， 代课老师

SELECT s.`name`, s.`code`, c.`name`, su.`name`, sc.score, t.`name`  
FROM student s 
INNER JOIN score sc ON s.id = sc.student_id
INNER JOIN `subject` su ON sc.subject_id = su.id
INNER JOIN teacher t ON su.teacher_id = t.id
INNER JOIN clazz c ON s.class_id = c.id;

简单写法：

select * from
表1,表2,表3,...
where 
关联条件1 and 关联条件2 and 关联条件3 and ...

# 简化写法
SELECT s.`name`, s.`code`, c.`name`, su.`name`, sc.score, t.`name`    
FROM student s, score sc, `subject` su, teacher t, clazz c   # 关联的表
WHERE 																											 # 关联条件
s.id = sc.student_id and sc.subject_id = su.id 								
and su.teacher_id = t.id and s.class_id = c.id;

#把上面5个sql使用简话的写法 重写一遍
SELECT * FROM student s, clazz c where s.class_id = c.id;

SELECT * FROM `subject` s, teacher t WHERE s.teacher_id = t.id;

SELECT * FROM student s , score sc,`subject` su 
WHERE s.id = sc.student_id AND sc.subject_id = su.id;

SELECT * FROM student s, score sc, `subject` su, teacher t
WHERE s.id = sc.student_id and sc.subject_id = su.id AND su.teacher_id = t.id;

SELECT s.`name`, s.`code`, su.`name`, sc.score, t.`name`  
FROM student s, score sc, `subject` su, teacher t
WHERE  s.id = sc.student_id and sc.subject_id = su.id AND su.teacher_id = t.id;

1.1 条件查询

在进行多表内连接查询时，也可以对数据进行条件过滤

select * from
表1,表2,表3,...
where 
关联条件1 and 关联条件2 and 关联条件3 and ...             -- 这里是表关联条件
and 过滤条件1  and/or 过滤条件2  and/or ... 			 -- 这里是数据过滤条件

# 查询性别为男的同学信息   学生主键  姓名  学号  所在班级
SELECT s.id, s.`name`, s.`code`, c.`name`, s.gender
FROM student s, clazz c
WHERE
class_id = c.id			 # 表的关联条件
AND s.gender = 1;    # 数据的过滤条件


#查询王仁老师所带科目     科目id   科目名称   代课老师
SELECT s.id, s.`name`,t.`name`
FROM `subject` s, teacher t
WHERE s.teacher_id = t.id
AND t.`name` = '王仁';


#1. 查询姓王的同学， 姓名  学号  所在班级
#2. 查询学号包含00的同学， 姓名  学号  所在班级，科目，成绩
#3. 查询学习了 陶知 所带科目的学生， 姓名  学号  所在班级，科目，成绩，代课老师
SELECT s.`name`, s.`code`, c.`name`
FROM student s, clazz c
WHERE s.class_id = c.id
AND s.`name` LIKE '王%';

SELECT s.`name`, s.`code`, c.`name`, su.`name`, sc.score
FROM student s, clazz c, `subject` su, score sc
WHERE s.class_id = c.id AND s.id = sc.student_id
AND sc.subject_id = su.id
AND s.`code` LIKE '%00%';


SELECT s.`name`, s.`code`, c.`name`, su.`name`, sc.score
FROM student s, clazz c, `subject` su, score sc, teacher t
WHERE s.class_id = c.id AND s.id = sc.student_id
AND sc.subject_id = su.id AND su.teacher_id = t.id
AND t.`name` = '陶知';

-- 查询有不及格成绩的学生  姓名 学号
SELECT DISTINCT s.`name`, s.`code`
FROM student s, score sc
WHERE s.id = sc.student_id
AND sc.score <60;

-- 查询学习java的女同学的成绩，按照倒叙排列    姓名 学号 科目名 成绩
SELECT s.`name`, s.`code`, c.`name`, su.`name`, sc.score
FROM student s, clazz c, `subject` su, score sc, teacher t
WHERE s.class_id = c.id AND s.id = sc.student_id
AND sc.subject_id = su.id AND su.teacher_id = t.id
AND su.`name` = 'java' AND s.gender = 0
ORDER BY sc.score DESC;

2. 聚合函数

• count()
• max()
• min()
• avg()
• sum()

2.1 count()

count() 计算条数

count(*)
count(列名)

# 1. 查询学生表有多少数据
SELECT count(*) FROM student;
# 2. 查询 code列 不为 null的条数
SELECT count(`code`) FROM student;

2.2 max()

max() 计算一列的最大值

max(列名) 

2.3 min()

max() 计算一列的最小值

min(列名) 

2.4 avg()

avg() 计算一列的平均值

avg(列名)

2.5 sum()

sum() 计算一列的总和

sum(列名)

# 1. 查询学生表有多少数据
SELECT count(*) FROM student;

#2. 查询 code列 不为 null的条数
SELECT count(`code`) FROM student;

#3. 计算score表   score列 的最大值
SELECT max(score) FROM score;

#4. 计算score表 score列 的最小值
SELECT min(score) FROM score;

#5. 计算score表 score列 的平均值
SELECT avg(score) FROM score;

#6. 计算score表 score列 的和
SELECT sum(score) FROM score;

-- 1. 李恺学了几门科目
-- 2. 李恺成绩的最高分
-- 3. 李恺成绩的最低分
-- 4. 李恺总分
-- 5. 李恺平均分
-- 6. java科目的最高分
-- 7. java科目的最低分
-- 8. java科目的平均分

SELECT * FROM student s, score sc 
WHERE s.id = sc.student_id
AND s.`name` = '李恺';

SELECT count(*), max(sc.score), min(sc.score), avg(sc.score), sum(sc.score)
FROM student s, score sc 
WHERE s.id = sc.student_id
AND s.`name` = '冰冰';

SELECT count(*), max(sc.score), min(sc.score), avg(sc.score), sum(sc.score)
FROM `subject` s, score sc 
WHERE s.id = sc.subject_id
AND s.`name` = '心学';