#院系表
create table department(
 dept_name varchar(20),
 building varchar(15),
 budget numeric(12,2),
 primary key (dept_name)
);
#课目表
create table course(
 course_id varchar(7),
 title varchar(50),
 dept_name varchar(20),
 credits numeric(2,0),
 primary key (course_id),
 foreign key (dept_name) references department
);
#教师表
create table instructor(
 ID varchar(5),
 name varchar(20) not null,
 dept_name varchar(20),
 primary key (ID),
 foreign key (dept_name) references department
);
#排课表
create table section(
 course_id varchar(8),
 sec_id varchar(8) ,
 semester varchar(6),
 year numeric(4,0),
 building varchar(15),
 room_number varchar(7),
 time_slot_id varchar(4),
 primary key (course_id,sec_id,semester,year),
 foreign key (course_id) references course
);
#教师授课表
create table teaches(
 ID varchar(5),
 course_id varchar(8),
 sec_id varchar(8) ,
 semester varchar(6),
 year numeric(4,0),
 primary key (ID,course_id,sec_id,semester,year),
 foreign key (course_id,sec_id,semester,year) references section,
 foreign key(ID) references instructor
);

笛卡尔积

• 将第一个关系的每个元组与第二个关系的所有元组都进行连接。

自然连接（内连接）

• 自然连接和笛卡尔积运算都作用于两个关系，并产生一个关系作为结果。
• 自然连接考虑哪些在两个关系模式中都出现的属性上取值相同的元组对。
• instructor和teaches的自然连接的计算中只考虑这样元组对：来自instructor的元组和来自teaches的元组在共同属性ID上的取值相同。
• 属性出现顺序：显示两个关系模式中的共同属性（只出现一次，不会重复），然后是只出现第一个关系模式中的属性， 最后是只出现在第二个关系模式中的属性。

select name ,course_id 
from instructor ，teaches 
where instructor.ID = teaches.ID;

等于下面语句

select name ,course_id
form instructor natural join teaches; 

• 执行顺序：先执行from，在执行where，后执行select。

排列元组显示次序

select name ,salary
from instructor
where dept_name = 'Physics'
order by name desc, name asc ;

• 执行顺序：from、where、select、order by

集合运算

• 并运算：union （去重）、union all（不去重）

image.png

• 交运算：intersect（去重）、intersect all（不去重）

image.png

• 差运算：except (去重）、except all（不去重）

image.png

空值

• 空值给关系运算带来了特殊问题，包括：算术运算、比较运算、集合运算。
• 算术表达：算术表达式的任一输入为空，则该算术表达式（+、-、*或/）结果为空。
• 比较运算：空值的编辑运算结果为unknown，创建了除true和false之外的第三个逻辑。
  • where中谓词对比较结果使用and 、or、not的布尔运算，所以这些布尔运算的定义也被扩展到可以处理unknown的值。如果where子句谓词对一个元组计算出false或unknown，那么该元组不能被加到结果集中。
• and、or、not对unknown的运算
• is null

聚集函数

• 基本聚集
  avg ...
• 分组聚集
  group by

image.png

having子句

• 对分组限定条件比对元组限定条件更有用，例如：只对教室平均工资超过42000的系感兴趣，该条件并不针对单个元组，而是针对group by子句构成的分组。为表达这样的查询，使用SQL的having子句。
• having子句中的谓词在形成分组后才起作用，因此可以使用聚集函数。

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

外连接

• 左外连接
• 右外连接
• 全外连接

DDL

• 添加字段 category 默认值0

USE recommendation;
ALTER TABLE experiment ADD category TINYINT(1) DEFAULT 0 COMMENT '实验类别：单业务线、跨业务线' 

• 删除字段 category和name

USE recommendation;
ALTER TABLE experiment DROP COLUMN category  , name