数据库入门-1

关系代数学

• 选择select（$\sigma$）

1. 选择符合所给的谓词条件的元组

$$\sigma_{p} (r) = \left \{ t|t\in r\; and \; p(t) \right \}$$

p是条件谓词

$$\sigma_{deptname=”Physics”}(instructor)$$

• 投影project（$\prod$）

1. 选择表中对应属性名的元组

$$\prod_{A1,A2,A3…,Ak}(r)$$

A是属性名

$$\prod_{deptname,salary}(instructor)$$

• 取并集union（$\cup$）
• 减法set difference（-）
• 笛卡儿积Cartesian-Product（X）
• 重命名Rename
• 取交集Set-Intersection（$\bigcap $）
• 自然连接Natural-Join（$\triangleright\! \triangleleft $）

SQL查询语句

create table Student
  (Sno char(5) not null,
    Sname varchar(20) unique,
    Ssex char(1));

drop table student;
delete from student;

alter table student add time Datetime;
alter table drop time;

-- SQLServer不支持natural join
select * from instructor natural join teaches.
select name, course_id from instructor, teaches where instructor.ID = teaches.ID;

-- natural join 默认选择具有相同值的属性列进行连接 删掉重复的列
select name from instructor where name like '%char%' escape 'c';

--order by
select distinct name from instructor order by name
select distinct name from instructor order by name desc

-- between
select name from instructor where salary between 900000 and 10000000
select name from instructor where salary not between 900000 and 10000000

-- union 相当于并集
(select course_id from section where semester = 'Fall' and year = 2009)
union
(select course_id from section where semester = 'Spring' and year = 2010)

-- intersect 相当于交集
(select course_id from section where semester = 'Fall' and year = 2009)
intersect
(select course_id from section where semester = 'Spring' and year = 2010)

-- except 相当于减法
(select course_id from section where semester = 'Fall' and year = 2009)
except
(select course_id from section where semester = 'Spring' and year = 2010)

-- union intersect except 默认自动删除重复的行 加上all 取消删除 但是SQLServer不支持all这种写法
-- is null也是操作符
select name from instructor where salary is null

-- 聚合函数
avg() min() max() sum() count()
-- select 中出现聚集函数之外的属性必须在group by中也出现
select dept_name, avg (salary) from instructor group by dept_name;
-- having 中也可以出现where，但不能是select中的属性
select dept_name, avg (salary)
  from instructor
  where ID = 'Biology'
  group by dept_name
  having avg (salary) > 42000;

-- Subqueries嵌套子查询
-- in 和 not in/exis 和 not exis

-- in 和 intersect 有相同的效果
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);
-- 等价
(select course_id from section where semester = 'Fall' and year = 2009)
intersect
(select course_id from section where semester = 'Spring' and year = 2010)

--Find courses offered in Fall 2009 but not in Spring 2010
select distinct course_i from section
where semester = ’Fall’ and year= 2009 and course_id  not in
(select course_id from section where semester = ’Spring’ and year= 2010);

--查询与‘柳橙’在同一个系学习的学生。
select id,name,dept from student
where dept in
(select dept from student where name = '柳橙')

-- exist 有点搞不懂 exist或者not exist是把主查询的字段传到后边的查询中作为条件，返回值是TRUE或者FALSE。
EXISTS TRUE，那么就是查询条件成立，结果会显示出来。NOT EXISTS TRUE，则为FALSE，查询连接条件不成立。
select course_id from section as S
where semester = 'Fall' and year = 2009
and exists
(select * from section as Twhere semester = 'Spring' and year= 2010 and S.course_id= T.course_id)

-- not exist 和 except 一起使用
-- Find all students who have taken all courses offered in the Biology department.
select distinct S.ID, S.name from student as S
where not exists (
(select course_id from course where dept_name = 'Biology')
except
(select T.course_id from takes as T where S.ID = T.ID)
);

-- with 新建临时的表给下面的查询使用
-- Find all departments where the total salary is greater than the average of the total salary at all departments.
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

-- delete/update/insert
delete from instructor where dept_name = 'Finance';
update instructor set salary = salary * 0.1 where salary > 10000;
insert into coursevalues (’CS-437’, ’Database Systems’, ’Comp. Sci.’, 4);