SQL语句

SQL

• Instructor(ID, name, dept_name, salary)
• Deptment(dept_name, building, budget)
• Student(ID, name, dept_name, tot_cred)
• Course(course_ID, title, dept_name, credts)
• Takes(ID, Course_ID, semester, year, grade)

---

Create

create table Student(
	ID char(5),
	name varchar(20),
	dept_name varchar(20),
	tot_cred int,
	primary key (ID),
	foreign key (dept_name) references Department
)

create table Takes(
	ID char(5),
	Course_ID varchar(20),
	semester varchar(20),
	years int,
	grade int,
	primary key (ID,Course_ID,semester,years,grade),
	foreign key (ID) references Student,
	foreign key (Course_ID) references Course
)

---

Select

// 普通查询
select * from Instructor
where salary > 20000
order by salary

// 聚集
select dept_name, count(*)
from Student
group by dept_name
having avg(tot_cred) > 60

// 嵌套查询 1 
select * from Takes
where Course_ID IN (
			select Course_id from Course
			where dept_name='CS' and credits > 2
		)

// 嵌套查询 2
select course_id
from section as S
where semester = ’Fall’ and year= 2009 and exists (
							select * from section as T
							where semester = ’Spring’ and year= 2010 and S.course_id = T.course_id);

---

Insert

insert into 
Student
values(ID,name,dept_name,salary)

---

Update

update Instructor 
set name = "瓜娃娃"
where id = 18899174

---

Delete

delete from Instructor
where id = 18899174

---

Set operate

并、交、补

(select * from course where dept_name='CS')
union
(select * from course where dept_name='Communication')


(select * from course where  credts > 2)
intersect
(select * from course where  credts < 4)


(select * from course where  credts > 2)
except
(select * from course where  credts < 4)

---

join

// 自然连接
course natural full outer join prereq
// 左连接
course left outer join prereq on course.course_id = prereq.course_id
// 右连接
course natural right outer join prereq;

---

Grant & Revoke

Grant aLl priviliges on Student to user1,user2
// 权限 select、update、delete、insert
Grant select on Student to user1

Revoke aLl priviliges on Student from user1,user2
// 权限 select、update、delete、insert
Revoke select on Student from user1

---

Role

create role DB_manager

---

View

create view faculty as
	select ID, name, dept_name
	from instructor

---

Procedure

// 定义存储过程 create procedure func_name() begin ... end
create procedure func(in input varchar(20),out output varchar(20))
	begin
		select count(*) into output
		from instructor
		where instructor.dept_name = func.input
	end
	
// 声明变量 declare
declare output integer;

// 调用 call
call func("CS building", output);

---

Trigger

create trigger trigger_name before / after of tableName on (Attribute)
referencing new row as nrow
referencing old row as orow
for each row
when //condition
	begin atomic
		...
	end;

// example：
create trigger credits_earned after update of takes on (grade)
referencing new row as nrow
referencing old row as orow
for each row
when nrow.grade <> ’F’ and nrow.grade is not null
and (orow.grade = ’F’ or orow.grade is null)
	begin atomic
		update student
		set tot_cred= tot_cred +
								(select credits
								from course
								where course.course_id= nrow.course_id)
		where student.id = nrow.id;
	end;

Cursor

SQL是集合处理方式。
一般的C、C++、Java、C#、python（宿主语言）是单记录处理方式。
那么宿主语言和SQL如何协调？游标cursor的引入！！！

游标：

• 一个数据缓冲区，存放SQL语句的结果。
• 每个游标有对应的结果。
• 用户通过游标逐一的获取记录，赋予主变量，然后交予宿主语言处理。

declare cursor_v cursor for
	select ID, name from student
	where tot_cred > 80

---

Index

普通索引：查找到满足条件的第一个记录后，需要查找下一个记录，直到结束。

唯一索引：由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索

create index index_name on student(name)

create unique index index_name on instructor(ID)