cs186 Lecture1 --Relational Model & SQL

course website

Relational Algebra

Six basic operators

• select $\sigma$
• project $\Pi$
• union $\cup$
• set difference $-$
• Cartesian product(笛卡尔积) $\times$
• rename $\rho$

Additional operations

• set intersection $\cap$
• natural join $\bowtie$
• assignment $\leftarrow$
• outerjoin ⟗ ⟕ ⟖

SQL

SQL (sequel/structured query language)

Create

• Database
  • set of named Relations
• Relation
  • Schema(description/metadata):unique attribute names,atomic types
  • Instance:can change very often
• Attribute(Column,Field)
• Tuple(Record,Row)

Create

• A:attribute name
• D:domain type
  • char(n)
  • varchar(n) Variable length character strings
  • int
  • numeric(p,d) p位有效数字小数点后d位
  • float(n) n位有效数字

create table table_name(A1 D1,A2 D2,...)

Table modification

drop table r;
alter table r add A D;
alter table r drop A;

Queries

select [distinct] <column expression list> from 
<table1 [as t1],table2 [as t2],...>
[where <predicate>]
[group by <column list>]
[having <predicate>]
[order by <column list>]
[limit integer];

• Order:

  • Order by–lexicographic ordering
  • desc for descending,asc for ascending

• Aggregates:

  avg,sum,count,min,max

• Group by:

  categorize the data(合并单元格) by a list of columns

• Having:

  predicate filters groups

Function

(1)Column Names:

• Sailors.sid, sname, bid (if无歧义,可去掉range)

(2)Aliases

• Table Aliases: from sailors as S, reserves as R
  • needed when the same table used multiple times(“self join”)
• Column Aliases: select x.sname as sname2

(3)Arithmetic expressions anywhere you like

(4)String comparisons – wildcard characters

• where s.sname like ‘B_%’
  • _:a single character
  • %:zero or more characters

(5)Combining predicates

,随处可用

• Where clause
  • Boolean logic: and, or, and not
• set operation:(回忆set数据结构)
  • union,intersect,except
• multiset semantics
  • union:sum of cardinalities
  • intersection all:min of cardinalities
  • except all:difference of cardinalities

(6)Nested queries

• (not)in and exists
• op(<,>,=) any and all

嵌套内部可以使用外部变量

example:

select S.name from sailors S
where S.sid in (
	select R.sid from Reserve R
	where R.bid=103
);

(7)Join variants

select [distinct] <column expression list> from 
table_name
[natural|{left|right|full}{outer}]join table_name
on <qualification_list>
where ...

(8)Views

• view
  • create view view_name as (select_statement)
• from
  • from b,(select_statement)
• with
  • with Reds (bid,scount) as (select_statement)

Modification

(1)deletion

delete from r where P;

(2)insert

insert into course 
values('cs','database',4);

• 不考虑顺序:
  • insert into course(departname,title,credits) values();
• values可用select clause替换

(3)update

update instructor
set salary=salary*1.5
where ...

Two set

1. salary =select…
2. case end

set salary =case
				when pred1 then result1
				when pred2 then result2
				else result0
			end
			

Null

• x op null is null
• where null: 不输出(关于是否为null必须考虑boolean logic)
• aggregates ignore null-value inputs

Integrity constraints

• not null(after A1 D1)
• primary key(A1,A2,…) or A1 D1 primary key
• foreign key(A1,A2,…) references relation_name
• unique(A1,A2,…) A1, A2,…form a candidate key
• check(semester in (‘Fall’, ‘Winter’, ‘Spring’, ‘Summer’))