/*
创建表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'
)
SQL操作
最新推荐文章于 2024-08-17 23:04:19 发布