MySQL:查询基本语法与思想

一、from子查询的作用

from后面接的是一个关系，select-from-where查询得到的也是一个关系，因此可以用from后接子查询。注意该子查询是一个临时关系，只在该语句里有效。

找出在所有系中工资总额最大的系：

select max(tot_salary)
from (select dept_name,sum(salary)
      from instructor
      group by dept_name) as dept_total(dept_name,tot_salary;

我们发现，from子查询可以在你处理后的表上进行操作，即，如果你需要一个处理后的表得到的信息进行操作，也许你可以直接用from子查询直接使用这个表。

eg.

①从2009年秋季开设的所有课程段中，找出最多的选课人数

select max(count_people)
from (select course_id,sec_id,semester,year,count(ID) as count_people
      from takes natural join section
      where year=2009 and semester="Fall"
      group by course_id,sec_id,semester,year) ;

二、with子句的作用

 with子句的作用同from子查询的作用有些类似，但是with子句可以一个子句对一个表进行多次操作，并在整条语句中使用。

不一定需要后面的属性名，属性名与查询语句中的select是一致的。

with子句产生的临时关系是不会随着产生该临时关系的关系变化而变化的。

找出所有工资总额大于所有系平均工资总额的系：

with dept_total(dept_name,value) as
     (select dept_name,sum(salary)
      from instructor
      group by dept_name),
     dept_total_avg(value) as
     (select avg(value)
      from dept_total)
select dept_name
from dept_total,dept_total_avg
where dept_total.value>dept_total_avg.value; //where直接比较一定是在一个关系中，所以要进行笛卡尔积
//否则 用 where dept_total.value > all (select value from dept_total_avg);

我们发现一个with子句可以定义多个临时关系，并且，之后出现的临时关系可以直接使用之前出现的临时关系。

with子句中的as和其他情况不一样，with子句中先对临时关系的名字和属性重命名，再写出具体的关系。

例如as出现的情况：

select 1 as number_one//生成一列instructor元组个数的1，属性名为number_one
from instructor;

select dept_name,(select count(*) from instructor) as sum_instuctors
from instructor;

select max(sum_salary)
from (select dept_name,sum(salary)
      from instructor
      group by dept_name) as dept_total_salary(dept_name,sum_salary);


select max(sum_salary)
from (select dept_name,sum(salary) as sum_salary
      from instructor
      group by dept_name);

三、连接表达式join

基础知识：

①单独的join和笛卡尔积是一样的

②join··· using ()

在连接时，按照using括号中的属性进行连接，与natural join自然连接不同的是，它可以自己选择属性进行连接，并且进行连接后的属性只出现一次

③on条件

在连接时，按照On给出的条件进行元组筛选，但是它并不会使得相同的属性输出只有一次，它会原封不动的输出。

连接条件词 natural 、using、on 只能三选一或不选。

④on条件和where条件的区别

（1）on条件是连接条件词

（2）on条件和where条件看起来是一样的，但实际运用过程中 用on条件进行连接筛选，用where对筛选后的元组进行筛选。

外连接 outer join

①外连接不与连接条件词匹配，否则就和笛卡尔积的输出是一样的，相当于连接恒真，即都符合连接条件，每两个元组之间都可以连接起来。

②外连接的工作原理是，先按照条件连接词进行一次内连接，然后把对应外连接一方的表未被匹配的项加入到内连接后的表中，空缺项填null

③包括

left outer join

right outer join

full outer join

select *
from (select * from student where dept_name='Comp.Sci')
     natural full outer join
     (select * from takes where semester='Spring' and year =2023);

四、视图

视图是一个“虚关系”，它并不进行计算和存储，而是在使用的时候才通过执行查询被计算出来。

然而在使用时，可以将其作为一个关系（所以视图也可以用来定义视图），它拥有的属性就是定义视图时的那些属性。我们使用视图进行查询时，由于是查询语句所以一定不会对原关系进行修改。

ps：物化视图是定义视图的时候存储了这个关系的，会根据一定的规律根据定义视图的关系进行更新物化视图。

（1）视图的定义

视图的定义与with很像，但是不同的是，视图只能一次创建一个，并且视图后面的查询语句可以不接（），也可以用括号括起来，但是with语句一定得用括号括起来、

1°普通定义（在视图定义时查询语句中有select的属性顺序，此视图的属性顺序与之一致）

create view v as select-from-where查询语句
v是视图的名字
如：
create view instructor_Comp_name as
select name
 from   instructor
 where  dept_name='Comp.Sci.';

2°显式定义属性名

create view instructor_avg_salary(avg_salary) as
select avg(salary)
 from instructor;

视图更新：

视图更新会同时更新定义视图的关系。

然而一般来说，只有满足以下条件 才称视图是可更新的：

①不存在笛卡尔积，from中只含有一个关系

②select中不包含distinct、聚集函数和表达式

③不存在聚集的任何问题如group by，having

④不在select中的原关系的属性必须可以为空。

换句话说，视图必须是一个关系最纯粹的子部分。