数据库期末复习之初级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 query

Multy Relation Query（Join）

<