MySQL笔记二

MySQL笔记二

自然连接

SELECT name,course_id
from instructor,teaches
where instructor.ID=teaches.ID

上述语句等价于

select name,course_id
from instructor natrual join teaches;

结果如下：

自然连接存在的问题：
有可能把同名但是不相干的属性连接在一起
自然连接是在笛卡尔乘积的基础上，选择出值相等的

重命名

对字段重命名

select ID,name,salary/12 as monthly_salary
from instructor;

对表进行重命名，e.g:find the name of all instructor who have a higher salary than some instructor in ‘Comp. Sci.’

select distinct T.name
from instructor as T,instructor as S
where T.salary>S.salary ans S.dept_name='Comp. Sci.';

上述语句相当于对instructor做两个副本，一个为T,一个是S,选择S表中的dept_name等于"Comp. Sci."并且T.salary>S.salary的名字

字符串操作

模糊查询，查询instructor中名字包含’in’的instructor的姓名

select name 
from instructor
where name like '%in%';

排序

SELECT distinct name 
from instructor 
order by name;

默认是升序"asc",降序为"desc"

select distinct name
from instructor
order by name desc;

按多个元组排序

SELECT * 
from instructor
order by dept_name,name

上述语句先按dept_name升序排序，当dept_name相等时，再按name升序排序。
如果我们想按dept_name升序，name降序，语句如下：

SELECT * from instructor
order by dept_name,name desc;

where 从句

选择薪水在90000和100000之间的instructor名字

SELECT name
from instructor
where salary between 90000 and 100000;

元组比较

SELECT name,course_id
from instructor,teaches
where (instructor.ID,dept_name) =(teaches.ID,'Biology');

集合操作

求并集UNION

SELECT course_id from section where semester='Fall' and year=2009
union
select course_id from section where semester='Spring' and year=2010;

求交集

select course_id from section where semester='Fall' and year=2009
    -> intersect
    -> select course_id from section where semester='Spring' and year=2010;

MySQL不支持intersect,所以会报错
求补集

select course_id from section where semester='Fall' and year=2009
    -> except
    -> select course_id from section where semester='Spring' and year=2010;

MySQL也不支持except
查询薪水最大的instructor 的薪水

select distinct salary
    -> from instructor
    ->except 
    select distinct T.salary
    -> from instructor as T,instructor as S
    -> where T.salary<S.salary;

或者

select max(salary)
from instructor;

空值

查找salary是空的instructor

SELECT * 
from instructor 
where salary is null;

聚合操作（Aggregate Function）

查找计算机科学学院的平均薪水

select avg(salary)
from instructor
where dept_name='Comp. Sci.';

选择在Spring 2010开课的老师的数量

select count(distinct ID)
from teaches 
where semester='Spring' and year=2010;

Group By （分组）

查找每个院系的平均年薪

select dept_name,avg(salary)
from isntructor
group by dept_name;

取出后按dept_name降序

SELECT dept_name,avg(salary)
from instructor
group by dept_name
order by dept_name desc;

没有参与分组的字段一定要出现在group by之后

SELECT dept_name,ID,avg(salary)
from instructor
order by dept_name;

Having 从句

选择平均薪水大于42000

select dept_name,avg(salary)
from instructor
group by dept_name
having avg(salary)>42000;

where不能用于group by函数
当没有group by时，having和where效果一样

嵌套子查询(Nested Subqueries)

find course offered in fall 2009 and in spring 2010

select distinct course_id
from section
where semester='Fall' and year=2009 
and course_id in (select course_id 
from section
where semester='Spring' and year=2010);

先找出Spring 2010的课程的course_id的集合，在从该集合中找出semester=‘Fall’ and year=2009的course_id
find courses offered in Fall 2009 and not in Spring 2010

select course_id
from section
where semester='Fall' and year =2009
and course_id not in (select course_id
from section
where semester='Spring' and year=2010);

通过嵌套子查询可以帮助我们解决MySQL不支持intersect和except的问题
5