数据库复习笔记（二）SQL语法

DDL - Data Definition Language

CREATE (1) Table - define table name, attributes, types

​ (2) View - define user view of data

​ (3) Index - create index on nominated attributes

DROP (1) Table - delete table, attributes and values

​ (2) View - delete user view(s)

​ (3) Index - delete index, indexes

​ Some DBMS have an ALTER command to vary attribute characteristics.

键的声明

声明含单个属性的键：在属性后面加PRIMARY KEY 或 UNIQUE

指明为UNIQUE的属性，允许有空值，但不允许多个空值（除了SQL SERVER 以外的大型数据库都是允许 UNIQUE约束有多个空值的。）；指明为PRIMARY KEY 的属性不允许有空值。

声明含多个属性的键：单独作为表的一个元素。

PRIMARY KEY 仅有一个； UNIQUE 可以有多个。

外键

关键字：REFERENCES

含单个属性的外键：在属性后面加 REFERENCES \ (\)

含多个属性的外键：单独作为表的一个元素。FOREIGN KEY ( \ ) REFERENCES \ ( \ )

被参照的属性必须是其他表的PRIMARY KEY 或 UNIQUE

更新操作

例：

CREATE TABLE Beers (
	name	CHAR(20) PRIMARY KEY,
	manf	CHAR(20) );
CREATE TABLE Sells (
	bar	CHAR(20),
	beer	CHAR(20),
	price	REAL,
	FOREIGN KEY(beer) REFERENCES 	Beers(name));

对Beers进行修改，会有三种情况：Default (Reject)，Cascade，Set NULL

其他

CREATE TABLE Sells (
	bar   CHAR(20),
	beer  VARCHAR(20)  DEFAULT 'HouseBeer',
	price REAL	     NOT NULL,
	PRIMARY KEY (bar,beer)
 );
 
ALTER TABLE Bars ADD phone CHAR(16) DEFAULT 'unlisted';
ALTER TABLE Bars DROP COLUMN license;
ALTER TABLE Sells ADD CHECK(…);

DML - Data Manipulation Language

SQL Queries

重命名 SELECT name AS beer (“AS” could be omitted)

select语句中可加表达式：SELECT bar, beer, price*120 AS priceInYen

在每一行增加一个固定的值：SELECT drinker, ‘likes Bud’ AS whoLikesBud

当字符串中有单引号时，改成双引号：WHERE bar = ‘Joe’’s Bar’ AND beer = ‘Bud’;

=，<>，<，>，<=，>=

AND, OR, NOT

格式：Attribute LIKE pattern (或NOT LIKE)

% stands for any string

_ stands for any one character

空值（未知或不可用）

比较的三种结果： TRUE, FALSE, UNKNOWN，任何值与NULL比较，结果为UNKNOWN，where筛选语句只留下结果为TRUE的

•TRUE = 1, FALSE = 0, and UNKNOWN = ½.

•AND = MIN; OR = MAX, NOT(x) = 1-x

输出排序：

–ORDER BY \

–ASC (升序，默认值)，DESC (降序)

Multi-relation Queries

别名：FROM Beers b1, Beers b2

WHERE b1.manf = b2.manf AND b1.name < b2.name;

子查询

in / not in 会去重

例：已知关系模式 R(a,b)和 S(b,c):

Q1: SELECT a FROM R,S WHERE R.b=S.b;

Q2: SELECT a FROM R WHERE b IN (SELECT b FROM S);

Q1的结果总是包含Q2的结果

“EXISTS(relation)” is true if the relation is nonempty.

例：

Find the beers that are the unique beer by their manufacturer.

SELECT  name
FROM    Beers b1
WHERE NOT EXISTS  (SELECT * FROM    Beers
WHERE manf = b1.manf AND name <> b1.name);

x = ANY( \ ) is true if and only if x equals at least one tuple in the relation.

–That is, x is a member of the relation (x IN \)

等号可以被替换：x > ANY(\ ) means x is larger than at least one tuple in the relation.

–Note tuples must have one component only.

x <> ALL( \ ) is true if and only if for every tuple t in the relation, x is not equal to t.

–That is, x is not a member of the relation (x NOT IN \).

<>可以被替换

•x >= ALL( \ ) means there is no tuple larger than x in the relation.

–Note tuples must have one component only.

这里注意：之前找最大值的时候喜欢用排序后 select top 1 的方法，但现在看来，top 1只能选出第一行数据，却不能选出并列的第一名的数据，练习中需要改正，或者尝试使用max(),min()

例：选择出预算最小的项目

select projectname
from project
where budget<=ALL (select budget from project)

易错点：

Q1: SELECT a FROM R WHERE b>= ANY (SELECT d FROM S WHERE c>10);

Q2: SELECT a FROM R WHERE b>= ALL (SELECT d FROM S WHERE c>10);

当子查询为空时，Q1和Q2结果不一样，上面为空，下面为所有！

—–

Union, Intersection, Difference 对子查询进行集合操作，要求子查询的结果表具有相同的属性和属性类型，查询用括号括起来。

关于去重：包和集合，select-from-where 结果是 bag，union,intersection, or difference 是 set，集合在select之后会自动加 distinct（去重）

聚集操作：sum, avg, min, max, count, count(*)

空值在任何聚集操作中都被忽视，除了count(*)

所以当group by 的属性为空时，该元组被过滤

GROUP BY (attribute list); –Select-from-where-group by

例：Find, for each drinker, the average price of Bud at the bars they frequent.

SELECT 	drinker, AVG(price) FROM Frequents, Sells
WHERE  	beer = 'Bud' AND Frequents.bar = Sells.bar
GROUP BY drinker;

不是每个在frequent中出现的drinker，都会在结果关系中出现。有可能他常去的酒吧都不售卖bud啤酒，经过选择运算之后，就被过滤掉了。

若select中有聚集运算，那么就不能出现group by中没有的属性。

HAVING clauses are selections on groups.Having中不以聚集形式出现的属性只能是group by的属性。

我平时对于需要在分组后筛选的属性，习惯于把他们加在group by后面，应该学习一下下面这个例子的写法：

Find the average price of those beers that are either served in at least 3 bars or manufactured by Busch.

SELECT 	beer, AVG(price) FROM 	Sells GROUP BY 	beer
HAVING 	COUNT(*) >= 3 OR beer IN (SELECT name FROM Beers
WHERE manf = 'Busch');

如果写成：

SELECT beer, AVG(price) FROM Sells GROUP BY beer,manf
HAVING COUNT(*) >= 3 OR manf = 'Busch';

如果啤酒和厂商是多对多，count(*)会数少了，数据条数也会变多。

---

Q1: SELECT DISTINCT a FROM R WHERE b>10;

Q2: SELECT a FROM R WHERE b>10 GROUP BY a;

Q1和Q2产生相同的结果。

R(A,B,C):

Q1: SELECT DISTINCT * FROM R;

Q2: SELECT * FROM R GROUP BY A,B,C;

Q1和Q2产生相同的结果。

More SQL

Modification 增删改

•INSERT INTO relation VALUES (list of values);

•INSERT INTO relation (subquery);

•DELETE FROM relation WHERE condition;

•UPDATE relation SET assignments WHERE condition;

例：

•Create a table of all Sally’s potential buddies, i.e., the people who frequent bars that Sally also frequents.

CREATE TABLE PotBuddies(name char(30));
INSERT INTO PotBuddies
(SELECT   DISTINCT d2.drinker
FROM    Frequents d1, Frequents d2
WHERE   d1.drinker = 'Sally' AND
d2.drinker <> 'Sally' AND
d1.bar = d2.bar);

基于属性的检查 check

只在插入和更新时起作用，在被参照表删除或更新时，不起作用，区别于外键约束。删除元组时不检查，可能违反约束，区别于assertion。

断言 ASSERTION

总是为真。例：

No bar may charge an avg. of more than $5 for beer.

CREATE ASSERTION NoRipoffBars
CHECK (NOT EXISTS(
			SELECT bar
			FROM Sells
			GROUP BY bar
			HAVING 5.0 < AVG(price)));

There cannot be more bars than drinkers.

CREATE ASSERTION FewBar
CHECK( (SELECT COUNT(*) FROM Bars) <=
		        (SELECT COUNT(*) FROM Drinkers));

Trigger

•Whenever a new tuple is inserted into Sells:

–If the beer mentioned is not in Beers, then insert it (with a null manufacturer).

CREATE TRIGGER BeerTrig
AFTER INSERT ON Sells -- INSERT can be DELETE or UPDATE OF <attr>.
FOR EACH ROW --FOR EACH ROW can be omitted
WHEN(:new.beer NOT IN (SELECT name FROM Beers))
	BEGIN
		INSERT INTO Beers(name) VALUES(:new.beer);
	END;

存储过程

一组为了完成特定功能的Transact-SQL语句集合。

域完整性约束

sql sever的三种机制：缺省值、规则、用户定义的数据类型

局部变量

DECLARE @avg_budget int,@extra_budget int
	SET @extra_budget=15000
	SELECT @avg_budget=AVG(budget) FROM project
	IF (SELECT budget
	      FROM project
	      WHERE projectno='p1')<@avg_budget
	BEGIN
		UPDATE project
		SET budget=budget+@extra_budget
		WHERE projectno='p1'
		PRINT 'Budget for p1 increased by 15000'
	END
	ELSE 
		PRINT 'Budget for p1 unchanged'

视图

CREATE VIEW v_count(projectno,countproject)
    AS SELECT projectno,COUNT(*)
          FROM workson
          GROUP BY projectno;

执行CREATE VIEW语句只是把视图的定义存入数据字典，并不执行SELECT语句

SQL Authorization

授权

GRANT <list of privileges>
		ON <relation or other object>
		TO <list of authorization ID’s>;
		
-- 如果想要接受者可以传递特权，使用：
WITH GRANT OPTION

GRANT SELECT, UPDATE(price) ON Sells TO sally;
-- Sally has the right to issue any query on Sells and can update the price component only
GRANT UPDATE ON Sells TO sally WITH GRANT OPTION;
-- Sally can not only update any attribute of Sells, but can grant to others the privilege UPDATE ON Sells.

收权

REVOKE <list of privileges>
		ON <relation or other object>
		FROM <list of authorization ID’s>;
--这些用户可能还是可以使用该特权，因为他们可能从其他地方获得授权

两种方式：RESTRICT, CASCADE

授权图

节点

user/privilege/option/isOwner

对于特权，不同属性上的操作是不同的节点，可传递和不可传递特权的用户是不同的节点；节点中AP表示A用户有P特权。P*表示P with grant option。P 表示P的源头，AP 表示A is the owner of the object on which P is a privilege.

边

X->Y，X用来授权Y

•When A grants P to B, we draw an edge from AP * or AP ** to BP.

–Or to BP * if the grant is with grant option.

•If A grants a subprivilege Q of P (say UPDATE(a) on R when P is UPDATE ON R) then the edge goes to BQ or BQ *, instead.

•Fundamental rule: user C has privilege Q as long as there is a path from XQ (the origin of privilege Q ) to CQ, CQ *, or CQ.

–Remember that XQ could be CQ.

•If A revokes P from B with the CASCADE option, delete the edge from AP to BP.

If A uses RESTRICT, and there is an edge from BP to anywhere, then reject the revocation and make no change to the graph.

•Having revised the edges, we must check that each node has a path from some ** node, representing ownership.

•Any node with no such path represents a revoked privilege and is deleted from the diagram.

例题

Initially, user A is the owner of relation R, and no other user holds privileges on R. The following are executed:

by A: GRANT UPDATE ON R TO B

by A: GRANT UPDATE(a) ON R TO C WITH GRANT OPTION

by C: GRANT UPDATE(a) ON R TO B WITH GRANT OPTION

by A: REVOKE UPDATE(a) ON R FROM C CASCADE

Which of the following best describes the status of B’s privileges on R?

(a) B can update any attribute of R except a but cannot grant that privilege.

(b) B has no privileges on R and cannot grant any.

(c) B can update any attribute of R except a, but can grant others the privilege to update R:a .

(d) B can perform any update on R but cannot grant that privilege.

Consider a database with relation R and users Alice, Bob, Carol, and Dave. Alice owns relation R. The following sequence of operations takes place:

Alice: GRANT SELECT ON R TO Bob WITH GRANT OPTION

Alice: GRANT SELECT ON R TO Carol WITH GRANT OPTION

Carol: GRANT SELECT ON R TO Bob WITH GRANT OPTION

Bob: GRANT SELECT ON R TO Dave WITH GRANT OPTION

Carol: GRANT SELECT ON R TO Dave

Dave: GRANT SELECT ON R TO Carol WITH GRANT OPTION

Alice: REVOKE SELECT ON R FROM Bob CASCADE

After these statements are executed, which of the following statements is true?

(a) Dave has the SELECT ON R privilege, but without the grant option.

(b) Dave has the SELECT ON R privilege with the grant option.

(c) Dave does not have the SELECT ON R privilege.

(d) Dave has the grant option for the SELECT ON R privilege, but does not have the privilege itself.