MySQL的一些查询练习

查询不重复的字段

mysql> select department from teacher; -- 不去重
mysql> select distinct department from teacher; -- 去重

查询区间

mysql> select * from score where degree between 60 and 80;
mysql> select * from score where degree > 60 and degree < 80;
mysql> select * from score where degree in (85,86,88); -- 特定选项

与或

mysql> select * from student where class = 95031 or sex = '女';
mysql> select * from student where class = 95031 and sex = '女';

升降序

mysql> select * from student order by class desc; -- 降序
mysql> select * from student order by class asc; -- 升序
mysql> select * from score order by c_no asc, degree desc; -- 一个升序一个降序

统计

计数

mysql> select count(*) from student where class = 95031; -- 计数

极值

mysql> select * from score where degree = (select max(degree) from score); -- 最高成绩
mysql> select * from score order by degree desc limit 0,1; -- 排序方法查询最高成绩 limit第一个参数表示从哪里开始，第二个参数表示条数

平均值

mysql> select avg(degree) from score where c_no = '3-105'; -- 查询平均成绩
mysql> select c_no,avg(degree) from score group by c_no; -- 使用分组查看每个班级的平均成绩

分组和筛选

where 用于分组前，group by 用于分组后。

mysql> select c_no,avg(degree),count(c_no) from score group by c_no -- 根据 c_no 分组
    -> having count(c_no) > 2 and c_no like '3%'; -- 筛选数量大于2且以3开头的课程

多表查询

简单判断

mysql> select name,c_no,degree from student,score
    -> where student.no = score.s_no; -- 查询所有人的班级和成绩

简单嵌套

mysql> select c_no,avg(degree) as degree -- 查询特定班级的所有学科的平均成绩
    -> from score
    -> where s_no in (select no from student where class = '95031')
    -> group by c_no;

复杂嵌套

-- 查询3-105课程成绩高于109号同学的所有人的信息
mysql> select * from student
    -> where student.no in
    -> (select student.no from student,score where
    -> score.c_no = '3-105' and no = s_no
    -> and degree>
    -> (select degree from score where s_no = '109' and c_no = '3-105')
    -> );

-- 查询张旭教授的课程的全部成绩
mysql> select * from score where
    -> c_no in (select no from course
    -> where t_no = (select no from teacher
    -> where name = '张旭'));

-- 查询每个成绩的排名
select a.Score ,
(select count(distinct b.score) from scores b where a.score<=b.score) Rank
from Scores a
order by Score desc;

查找连续出现3次的数字

select distinct l1.num
as ConsecutiveNums
from
Logs l1,Logs l2,Logs l3
where
l1.Id = l2.Id-1 and
l2.Id = l3.Id-1 and
l1.Num = l2.Num and
l2.Num = l3.Num
;

查找重复出现的元素

select Email
from Person
group by Email
having count(Email) > 1;

查找每个部门的最高工资的人

select Department.Name Department, Employee.Name Employee, Salary
from
Employee join Department
on Employee.DepartmentId = Department.Id
and (DepartmentId, Salary) in
(select DepartmentId, max(Salary)
from Employee
group by DepartmentId
);

交并补

Union求并集

mysql> select * from teacher where profession
    -> not in (select profession from teacher where department = "计算机系")
    -> and department = "电子工程系"
    -> union
    -> select * from teacher where profession
    -> not in (select prfession from teacher where department = "电子工程系")
    -> and department = "计算机系";

复制表查找

-- 选择成绩低于平均分的成绩信息
mysql> select * from score a where
    -> degree < (select avg(degree) from score b where
    -> a.c_no = b.c_no);

按条件分组查找

-- 查找男生多于1个的班级
mysql> select class from student
    -> where sex = '男'
    -> group by class having count(*)>1;

各种函数

IFNULL()

IFNULL(expression, alt_value)
-- 如果是NULL就返回alt_value 否则返回expression

日期函数

year(date) 返回年
month(date) 返回月
day(date) 返回日
now() 返回当前日期和时间
date(now()) 返回当前日期
time(now()) 返回当前时间
DATEDIFF(date1,date2) 返回两个日期相隔的天数

举例：查询和学号101或108的同学同一年出生的人

mysql> select * from student
    -> where year(birthday) in
    -> (select year(birthday) from student
    -> where no in (101,108));

any()表示部分

-- 选择3-105的分数至少大于任意一个3-245分数的成绩信息
mysql> select * from score where
    -> c_no = '3-105' and
    -> degree > any(select degree from score where
    -> c_no = '3-245')
    -> order by desc;

all()表示全部

-- 选择3-105的分数大于所有3-245分数的成绩信息
mysql> select * from score where
    -> c_no = '3-105' and
    -> degree > all(select degree from score where
    -> c_no = '3-245')
    -> order by desc;