1 外键的创建(多对多)学生表,课程表,和成绩表之间的关系 代码块 CREATE DATABASE ec14; USE ec14; CREATE TABLE student( id int PRIMARY key auto_in
代码块
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;
代码块
select * from student where class='ec14' group by gender having age>18 order by name desc LIMIT 5
找到表:from
拿着where指定的约束条件,去文件/表中取出一条条记录
将分组的结果进行having过滤
执行select
去重distinct
将结果按条件排序:order by(asc/desc)
limit前多少条
分组之后只能查询分组的字段,如果想查询组内的其它字段的信息,必须要借助聚合函数
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;
--结束END--
本文标题: mysql(二)
本文链接: https://www.lsjlt.com/news/3523.html(转载时请注明来源链接)
有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
下载Word文档到电脑,方便收藏和打印~
2024-05-14
2024-05-14
2024-05-14
2024-05-14
2024-05-14
2024-05-14
2024-05-14
2024-05-14
2024-05-14
2024-05-14
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
0