SQL操作

/*
创建表creating tables：CREATE TABLE
tips:
·specify the table name and field names;
·the type(domain) of each field is specified.
*/
CREATE TABLE Students(
		sid CHAR(20),
		name CHAR(30),
		login CHAR(10),
		age INTEGER,
		gpa REAL
)


/*
插入记录add tuples：INSERT
tips: 对于INTO子句中的字段名列表，在字段的次序正确的情况下，可以将之省略。
*/
INSERT
INTO Students(sid,name,login,age,gpa)
VALUES (53688,'Smith','smith@ee',18,3.2)

/*
删除记录delete tuples: DELETE
*/
DELETE
FROM Students S
WHERE S.name='Smith'

/*
delete all records
*/
DELETE
FROM Students

/*
修改已有数据的字段值: UPDATE
tips: 
·Note that the WEHERE statement is evalueated before the SET statement;
·An update statement may affect more than one record.
*/
UPDATE Student S
SET S.gpa=S.gpa-0.1
WHERE S.gpa?=3.3

/*
销毁或者改变关系Destroying and Altering relations
注意DROP和delete all records的区别
*/
DROP TABLE Students	--不仅销毁了表，还销毁了records和schema

/*
增加一个属性add a column
*/
ALTER TABLE Students
ADD year INTEGER

/*
删除一个属性delete a column
*/
ALTER TABLE Students
DROP year

/*
指定码约束primary and candidate keys in SQL: UNIQUE
通过添加CONSTRAINT来命名一个约束，
这样，当违反了约束时，系统能够返回约束名，以用于对错误定位。
*/
CREATE TABLE Students(
		sid CHAR(20),
		name CHAR(30),
		login CHAR(10),
		age INTEGER,
		gpa REAL,
		UNIQUE (name,age),
		CONSTRAINT StudentKey PRIMARY KEY(sid)
)

/*
为什么要用别名alias
*/
SELECT *
FROM Students S1, Students S2
WHERE S1.gpa<S2.gpa;

--看看和以下句子的区别
SELECT *
FROM Students S1, Students S2	--结果是叉积

/*
指定外码约束
*/
CREATE TABLE Enrolled
(
sid CHAR(20), cid CHAR(20), grade CHAR(2),
PRIMARY KEY (sid,cid),	--联合主键
FOREIGN KEY(sid) REFERENCES Students
)

/*
查询语句SELECT
*/
SELECT *
FROM students S
WHERE S.age<18

SELECT S.name,S.login
FROM Students S
WHERE S.age<18 AND S.gpa>=3.3

SELECT S.name,E.cid
FROM Students S, Enrolled E
WHERE S.id=E.sid AND E.grade='A'
/*
参照完整性referential integrity in SQL
*/
CREATE TABLE Enrolled(
sid CHAR(20),
cid CHAR(20),
grade CHAR(2),
PRIMARY KEY (sid,cid),
FOREIGN KEY (sid)
REFERENCES Students ON DELETE CASCADE ON UPDATE SET DEFAULT
)
/*
删除视图view
*/
DROP VIEW

/*将relationship转化为table
*/
CREATE TABLE Works_In(
ssn CHAR(20),
did INTEGER,
since DATE,
PRIMARY KEY(ssn,did),
FOREIGN KEY(ssn) REFERENCES Employees,
FOREIGN KEY(did) REFERENCES Departments
)

/*
指定参与约束，用NOT NULL
*/
CREATE TABLE Dept_Mgr(
did INTEGER,
dname CHAR(20),
budget REAL,
ssn CHAR(20) NOT NULL,
since DATE,
PRIMARY KEY(did),
FOREIGN KEY(ssn) REFERENCES Employees ON DELETE NO ACTION
)

/*
orderiing output
有序输出，默认是升序，关键词是ASC，DESC，放在column后面
*/
SELECT lname, fname, income
FROM customer
ORDER BY lname, fname

/*
JOIN操作
natural join
*/
SELECT *
FROM sailors join reservers ON
sailors.sid=reserves.sid

SELECT *
FROM sailors natural join reservers

/*
corrrelated queries相关嵌套查询
Find the SINs, ages and incomes, of customers who have an account at the Lonsdale
*/
SELECT C.osin, C.age, C.income
FROM Customer C
WHERE EXISTS
(
SELECT *
FROM Account A, Owns O
WHERE C.osin=O.osin AND A.accnum=O.accnum AND A.brname='Lonsdale'
)