mysql（二），

1 外键的创建（多对多）学生表，课程表，和成绩表之间的关系

代码块
CREATE DATABASE ec14;
USE ec14;

CREATE TABLE student(
id int PRIMARY key auto_increment,
stu_name varchar(10) not null
);

CREATE TABLE subj(
id int PRIMARY key auto_increment,
sub_name varchar(10)
);

CREATE TABLE score(
id int PRIMARY key ,
stu_id int,
sub_id int,
score int not null,
CONSTRAINT stu_fk FOREIGN key (stu_id) REFERENCES student(id) on DELETE CASCADE on UPDATE CASCADE,
CONSTRAINT sub_fk FOREIGN key (sub_id ) REFERENCES subj (id) on DELETE CASCADE on UPDATE CASCADE
);

select  from  score;

2.表查询的执行顺序（重点中的重点）非常重要

from——where——group by——having——select——distinct——order by——limit

代码块
select * from student where class='ec14' group by gender having  age>18    order by name desc  LIMIT 5

1.找到表:from

2.拿着where指定的约束条件，去文件/表中取出一条条记录

4.将分组的结果进行having过滤

5.执行select

6.去重distinct

7.将结果按条件排序：order by(asc/desc)

8.limit前多少条

3 关于分组group by的知识点

分组之后只能查询分组的字段，如果想查询组内的其它字段的信息，必须要借助聚合函数

max()

min()

avg()

sum()

count()

代码块
1.查询岗位名以及岗位包含的所有员工名字
select job_name ,GROUP_CONCAT(name) from employee GROUP BY job_name;

2.查询平均薪水大于10000的岗位及岗位平均薪资
select  job_name, AVG(salary) from employee GROUP BY job_name having avg(salary)>10000;

3.查询平均薪水大于10000的岗位和岗位平均薪资,并按照岗位薪资降序排列
select avg(salary) ,job_name  from employee GROUP BY job_name HAVING avg(salary)>10000 ORDER BY avg(salary) DESC;

4.查询雇员表所有信息，先按照年龄升序排列，再按照id降序排列
select * from employee ORDER BY age asc,id desc;