select distinct 列*from 表1 innser|left|right join 表2 on 表1与表2的关系where ...group by ... (5个聚合函数,avg,max,sum,min,count)having ...order by ... (排序desc|asc)limit start,count
1.关系
- 创建成绩表scores，结构如下
- id
- 学生
- 科目
- 成绩
- 思考：学生列应该存什么信息呢？
- 答：学生列的数据不是在这里新建的，而应该从学生表引用过来，关系也是一条数据；根据范式要求应该存储学生的编号，而不是学生的姓名等其它信息
- 同理，科目表也是关系列，引用科目表中的数据
2.外键约束
- 思考：怎么保证关系列数据的有效性呢？任何整数都可以吗？
- 答：必须是学生表中id列存在的数据，可以通过外键约束进行数据的有效性验证
- 为stuid添加外键约束
alter table scores add constraint stu_sco foreign key(stuid) references students(id);
- 在创建表时可以直接创建约束
create table scores(id int primary key auto_increment,stuid int,subid int,score decimal(5,2),foreign key(stuid) references students(id),foreign key(subid) references subjects(id));
mysql> create table scores( -> id int auto_increment primary key not null, -> score decimal(4,1), -> stuid int, -> subid int, -> foreign key(stuid) references students(id), -> foreign key(subid) references subjects(id));
- 添加数据
mysql> insert into scores values(0,100,1,1);Query OK, 1 row affected (0.01 sec)mysql> select * from scores;+----+-------+-------+-------+| id | score | stuid | subid |+----+-------+-------+-------+| 1 | 100.0 | 1 | 1 |+----+-------+-------+-------+
mysql> insert into scores values(0,100,10,10);ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`py31`.`scores`, CONSTRAINT `scores_ibfk_2` FOREIGN KEY (`subid`) REFERENCES `subjects` (`id`))
3.外键的级联操作（）逻辑删除
- 在删除students表的数据时，如果这个id值在scores中已经存在，则会抛异常
- 推荐使用逻辑删除，还可以解决这个问题
- 可以创建表时指定级联操作，也可以在创建表后再修改外键的级联操作
级联操作的类型包括：
- restrict（限制）：默认值，抛异常
- cascade（级联）：如果主表的记录删掉，则从表中相关联的记录都将被删除
- set null：将外键设置为空
- no action：什么都不做
alter table scores add constraint stu_sco foreign key(stuid) references students(id) on delete cascade;
4.连接查询
- 问：查询每个学生每个科目的分数
- 分析：学生姓名来源于students表，科目名称来源于subjects，分数来源于scores表，怎么将3个表放到一起查询，并将结果显示在同一个结果集中呢？
- 答：当查询结果来源于多张表时，需要使用连接查询
- 关键：找到表间的关系，当前的关系是
- students表的id---scores表的stuid
- subjects表的id---scores表的subid
- 则上面问题的答案是：
select students.sname,subjects.stitle,scores.scorefrom scoresinner join students on scores.stuid=students.idinner join subjects on scores.subid=subjects.id;
1)查询成绩
mysql> select * from scores;+----+-------+-------+-------+| id | score | stuid | subid |+----+-------+-------+-------+| 1 | 100.0 | 1 | 1 || 3 | 100.0 | 3 | 2 || 4 | 100.0 | 4 | 5 || 5 | 94.0 | 3 | 5 || 6 | 94.0 | 7 | 5 || 7 | 92.0 | 7 | 5 || 8 | 92.0 | 8 | 5 || 9 | 72.0 | 8 | 5 |+----+-------+-------+-------+
2）连接查询
学生表 - 班级表郭靖 python 100student.name subjects.titlescores.scoreselect students.name,subjects.title,scores.score from scores inner join students on scores.stuid=students.idinner join subjects on scores.subid=subjects.id;
+-----------+---------+-------+| name | title | score |+-----------+---------+-------+| 腾旭 | python | 100.0 || 网易 | linux | 100.0 || 小米 | mysqlDB | 100.0 || 网易 | mysqlDB | 94.0 || QQ | mysqlDB | 94.0 || QQ | mysqlDB | 92.0 || 腾讯云 | mysqlDB | 92.0 || 腾讯云 | mysqlDB | 72.0 |+-----------+---------+-------+
- 表A inner join 表B：表A与表B匹配的行会出现在结果中
select *from students inner join scores on students.id=scores.stuid
- 表A left join 表B：表A与表B匹配的行会出现在结果中，外加表A中独有的数据，未对应的数据使用null填充
select *from students left join scores on students.id=scores.stuid
mysql> mysql> select * from students left join scores on students.id=scores.stuid;+----+-----------+--------+---------------------+----------+------+-------+-------+-------+| id | name | gender | birthday | isDelete | id | score | stuid | subid |+----+-----------+--------+---------------------+----------+------+-------+-------+-------+| 1 | 腾旭 | | 1999-09-09 00:00:00 | | 1 | 100.0 | 1 | 1 || 3 | 网易 | | NULL | | 3 | 100.0 | 3 | 2 || 4 | 小米 | | NULL | | 4 | 100.0 | 4 | 5 || 3 | 网易 | | NULL | | 5 | 94.0 | 3 | 5 || 7 | QQ | | NULL | | 6 | 94.0 | 7 | 5 || 7 | QQ | | NULL | | 7 | 92.0 | 7 | 5 || 8 | 腾讯云 | | NULL | | 8 | 92.0 | 8 | 5 || 8 | 腾讯云 | | NULL | | 9 | 72.0 | 8 | 5 || 2 | 腾旭 | | 1990-02-02 00:00:00 | | NULL | NULL | NULL | NULL || 6 | 酷狗 | | 2017-02-13 00:00:00 | | NULL | NULL | NULL | NULL || 9 | 华为 | | NULL | | NULL | NULL | NULL | NULL || 10 | 京东 | | NULL | | NULL | NULL | NULL | NULL || 11 | 微博 | | NULL | | NULL | NULL | NULL | NULL || 12 | 微信 | | NULL | | NULL | NULL | NULL | NULL |+----+-----------+--------+---------------------+----------+------+-------+-------+-------+
- 表A right join 表B：表A与表B匹配的行会出现在结果中，外加表B中独有的数据，未对应的数据使用null填充
select *from scores right join studentson students.id=scores.stuid
+------+-------+-------+-------+----+-----------+--------+---------------------+----------+| id | score | stuid | subid | id | name | gender | birthday | isDelete |+------+-------+-------+-------+----+-----------+--------+---------------------+----------+| 1 | 100.0 | 1 | 1 | 1 | 腾旭 | | 1999-09-09 00:00:00 | || 3 | 100.0 | 3 | 2 | 3 | 网易 | | NULL | || 4 | 100.0 | 4 | 5 | 4 | 小米 | | NULL | || 5 | 94.0 | 3 | 5 | 3 | 网易 | | NULL | || 6 | 94.0 | 7 | 5 | 7 | QQ | | NULL | || 7 | 92.0 | 7 | 5 | 7 | QQ | | NULL | || 8 | 92.0 | 8 | 5 | 8 | 腾讯云 | | NULL | || 9 | 72.0 | 8 | 5 | 8 | 腾讯云 | | NULL | || NULL | NULL | NULL | NULL | 2 | 腾旭 | | 1990-02-02 00:00:00 | || NULL | NULL | NULL | NULL | 6 | 酷狗 | | 2017-02-13 00:00:00 | || NULL | NULL | NULL | NULL | 9 | 华为 | | NULL | || NULL | NULL | NULL | NULL | 10 | 京东 | | NULL | || NULL | NULL | NULL | NULL | 11 | 微博 | | NULL | || NULL | NULL | NULL | NULL | 12 | 微信 | | NULL | |+------+-------+-------+-------+----+-----------+--------+---------------------+----------+
5.练习
- 查询学生的姓名、平均分
mysql> select stuid,avg(score) from scores group by stuid;+-------+------------+| stuid | avg(score) |+-------+------------+| 1 | 100.00000 || 3 | 97.00000 || 4 | 100.00000 || 7 | 93.00000 || 8 | 82.00000 |+-------+------------+
mysql> select stuid,avg(score) from scores inner join students on scores.stuid=students.id group by stuid;+-------+------------+| stuid | avg(score) |+-------+------------+| 1 | 100.00000 || 3 | 97.00000 || 4 | 100.00000 || 7 | 93.00000 || 8 | 82.00000 |+-------+------------+
mysql> select name,stuid,avg(score) from scores inner join students on scores.stuuid=students.id group by stuid;+-----------+-------+------------+| name | stuid | avg(score) |+-----------+-------+------------+| 腾旭 | 1 | 100.00000 || 网易 | 3 | 97.00000 || 小米 | 4 | 100.00000 || QQ | 7 | 93.00000 || 腾讯云 | 8 | 82.00000 |+-----------+-------+------------+5 rows in set (0.00 sec)
mysql> select name,avg(score) from scores inner join students on scores.stuid=students.id group by stuid;+-----------+------------+| name | avg(score) |+-----------+------------+| 腾旭 | 100.00000 || 网易 | 97.00000 || 小米 | 100.00000 || QQ | 93.00000 || 腾讯云 | 82.00000 |+-----------+------------+
mysql> select name,avg(score) from scores inner join students on scores.stuid=students.id group by stuid order by avg(score) desc;+-----------+------------+| name | avg(score) |+-----------+------------+| 小米 | 100.00000 || 腾旭 | 100.00000 || 网易 | 97.00000 || QQ | 93.00000 || 腾讯云 | 82.00000 |+-----------+------------+
- 查询男生的姓名、总分
select students.sname,avg(scores.score)from scoresinner join students on scores.stuid=students.idwhere students.gender=1group by students.sname;
查询男生的姓名、总分students.gender=1students.name sum(scores.score)
建立连接:students.id=scores.stuid 使用sum 聚合 --》 分组 group by 姓名：每个人的总分
select name,sum(score) from studentsinner join scores on students.id=scores.stuidwhere gender=1 group by name group by students.id (如果同名)
- 查询科目的名称、平均分
select subjects.stitle,avg(scores.score)from scoresinner join subjects on scores.subid=subjects.idgroup by subjects.stitle;
查询科目的名称、平均分subjects.tilteavg(scores.score)建立连接:subjects.id=scores.subid使用avg ---》 group by subjects.title select subjects.title,avg(scores.score)from scoresinner join subjects on subjects.id=scores.subidgroup by subjects.title
- 查询未删除科目的名称、最高分、平均分
select subjects.stitle,avg(scores.score),max(scores.score)from scoresinner join subjects on scores.subid=subjects.idwhere subjects.isdelete=0group by subjects.stitle;
查询未删除科目的名称、最高分、平均分where subjects.isDelete=0subjects.tiltemax(scores.score)avg(scores.score)建立连接subjects.id=scores.subidmax avg --> group by subjects.tilteselect subjects.title,max(scores.score),avg(scores.score)from subjectsinner join scores on subjects.id=scores.subidwhere subjects.isDelete=0group by subjects.title