mysql数据库查询操作

xunge 0

— 使用常量
select 10,name from student;

— 排序order by 默认升序、降序desc
select score from score where courseid=3 order by score;
select score from score where courseid=3 order by score desc;

— limit 限制显示的行数

— 查询出成绩表中分数排名前3的分数
select score from score order by score desc limit 3; — 显示前3行

— 查询出成绩表中分数排名前3-6的分数
select score from score order by score desc limit 2,4; — 从3开始向后取4位置

— 统计count(字段)
— distinct(字段) 或者 distinct 字段 去重
select count(distinct(id)) from score where courseid=3;

select * from score;

— 练习
— 1、统计出学号小于等于10以内参加了考试的人数
select count(distinct(id)) from score where id<=10;

— 2、统计出课程3成绩大于60的人数
select count(id) from score where courseid=3 and score>60;

— 3、查询出分数表中排名倒数前5的分数
select score from score order by score limit 5;

— 4、查询出学号为3的学生参加的考试科目数
select count(distinct(courseid)) from score where id=3;

— 5、查询出分数表中有多少个科目
select count(distinct(courseid)) from score;

— 带条件的查询
— 1、比较符>、>=、<、<=…
— 2、指定范围区间between and
select score from score where score between 60 and 80;
select score from score where score>=60 and score<=80;

— 3、 模糊查询:like、not like、in、not in
— %任意字符、_单个字符
select name from student where name not like ‘谭%’;
select name from student where name like ‘谭_’;
select * from student where address in (‘长沙’,’四川’);

— 模糊查询扩展符,只能使用rlike
— ^张 以xx开头、飞$ 以xx结尾
select name from student where name rlike ‘^张’;
select name from student where name rlike ‘飞$’;

— 4、是否为空:is null、is not null
select * from score where id=3 and score is null;

— 5、多条件: and 、or
select * from score where id<=10 and score>80;
select * from score where id<=10 or score>80;

— 多表联查
— 查询出参加了考试的学生姓名、id、分数
— 1.where 的方式
select student.name,student.id,score.score
from student, score
where student.id=score.id;
select name,st.id,score
from student st, score sc — 取别名
where st.id=sc.id;
— 2.左表 inner join 右表 on 字段链接 *内联
select name,st.id,score
from student st
inner join score sc
on st.id=sc.id;
— 3.子查询 [不建议使用,效率最低]
— 查询出参加了考试的学生姓名、id
select name,id
from student
where id in(select id from score);

— 练习
— 1.查询出分数大于等于60分的学生姓名、分数、地址
select name,score,address
from student st, score sc
where st.id=sc.id
having score>=60;
select name,score,address
from student st
inner join score sc
on st.id=sc.id
having score>=60;
— 2.查询出地址是长沙的学生姓名、分数、课程号
select name,score,courseid
from student st, score sc
where st.id=sc.id and address=’长沙’;
select name,score,courseid
from student st
inner join score sc
on st.id=sc.id
and address=’长沙’;
— 3.查询出每个学生的姓名、最高分、课程号
select name,max(score),courseid
from student st, score sc
where st.id=sc.id
group by st.id;
select name,max(score),courseid
from student st
inner join score sc
on st.id=sc.id
group by st.id;
— 4.查询出平均分大于等于70的学生姓名、学号、平均分
select name,st.id,avg(score)
from student st, score sc
where st.id=sc.id
group by st.id
having avg(score)>=70;
select name,st.id,avg(score)
from student st
inner join score sc
on st.id=sc.id
group by st.id
having avg(score)>=70;
— 5.查询出参加了考试的学生姓名、分数、课程名称
select name,score,coursename
from student st, score sc,course co
where st.id=sc.id and sc.courseid=co.courseid;
select name , score, coursename
from student st
inner join score sc
on st.id = sc.id
inner join course co
on sc.courseid = co.courseid;


发表评论