数据库期末复习之初级SQL（双语）

---

Data Definition Language

Create

create table r ( A 1 D 1,
		 A 2 D 2,
		  ......, 
		  A n D n , 
		  (integrity-constraint1), 
		  ......,
		   (integrity-constraintk) )
# r is the name of the relation 
# each Ai is an attribute name in the schema of relation r 
# Di is the data type of values in the domain of attribute Ai

Create Table Extensions

Drop Table

Alter Table Constructs

alter table r add A D
alter table r drop A     

Data Manipulation Language

Deletion

delete from r where P; 

Insertion

insert into course values ( ‘C5-437' , 'Database System' , 'Comp. sci. ’ ,4) ; 
insert into student 
	select ID, name, dept_name, 0 
	from instructor；

Updates

update instructor 
	set salary = salary * 1.03 
	where salary > 100000;
update student S 
set tot_cred = (select sum(credits) 
		from takes natural join course 
		where S.ID= takes.ID and takes.grade <> 'F' and 
		takes.grade is not null); 

Basic Query Structure

Typical SQL query

select A1, A2, ..., An 
from r1, r2, ..., rm 
where P

The select Clause
The select clause list the attributes desired in the result of a query

• distinct：elimination of duplicates（去重）
• all：duplicates not be removed（default）

The where Clause
The where clause specifies conditions that the result must satisfy

The from Clause
The from clause lists the relations involved in the qu