SQL语法

SQL基本语法

• 另附：大学数据库关系
  

The create clause

create table instructor (
ID char(5),
name varchar(20),
dept_name varchar(20),
salary numeric(8,2));     --定点数，总共p位数字,d位在小数点右边

• 加上完整性约束:not null,primary key,foreign key
• unique\default\check\assertion

create table takes (
ID varchar(5),
course_id varchar(8),
sec_id varchar(8),
semester varchar(6),
year numeric(4,0),
grade varchar(2), 
primary key (ID, course_id, sec_id, semester, year) ,
foreign key (ID) references student,
foreign key (course_id, sec_id, semester, year) references section);

The insert clause

• 插入一行数据

insert into instructor values (‘10211’, ’Smith’, ’Biology’, 66000);

• 插入一行数据在指定的字段上

INSERT INTO Persons (LastName, Address)
VALUES ('Rasmussen', 'Storgt 67')

The delete clause

• 删除表中的数据

DELETE FROM Person WHERE LastName = 'Rasmussen'

The drop clause

• 删除表

DROP TABLE table_name

The alter clause

• 修改表,在已经存在的表中增加或者移除字段

• 增加字段

ALTER TABLE Person ADD City varchar(30)

• 移除字段

ALTER TABLE Person DROP COLUMN Address

The select clause

• select\into\from\where\group by\having\drder by
• and|or\between…and\distinct\all\join\as\into\like

select\from\distinct\all

• 找出所有教员的系名，并删除重复的

select distinct dept_name
from instructor

• 重复的不删除

select all dept_name
from instructor

as\where\and

select ID, name, salary/12 as monthly_salary
from instructor
where dept_name = 'Comp. Sci.' and salary > 80000

as

select S.supervisor 
from emp_super as T, emp_super as S
where T.peroson = ‘Bob’ and T.supervisor = S.p

like

select name
from instructor
where name like '%dar%'
-- like ‘100 \%' escape '\'

order by

select distinct name
from instructor
order by dept_name,name

group by\having

• 每个 Company 的 Amout 的合计在 10000 以上的数据

SELECT Company,SUM(Amount) FROM Sales
GROUP BY Company HAVING SUM(Amount)>10000

join

SELECT Employees.Name, Orders.Product
FROM Employees
INNER JOIN Orders
ON Employees.ID = Orders.ID

SELECT Employees.Name, Orders.Product
FROM Employees, Orders
WHERE Employees.ID = Orders.ID

The update clause

UPDATE Person
SET Address = 'Stien 12', City = 'Stavanger'
WHERE LastName = 'Rasmussen'

Aggregate Functions(聚集函数)

• avg\min\max\sum\count

Nested Subqueries(嵌套子查询)

• Find courses offered in Fall 2009 but not in Spring 2010

select distinct course_id
from section
where semester = 'Fall' and year= 2009 and 
course_id not in (select course_id
from section
where semester = 'Spring' and year= 2010);

t distinct course_id
from section
where semester = 'Fall' and year= 2009 and 
course_id not in (select course_id
from section
where semester = 'Spring' and year= 2010);

• exists,not exists,unique,with