程序代写代做代考 database cache SQL Slide 1

Slide 1

SQL (MySQL)

MySQL and Set

SQL discussed in previous lecture are for
Oracle.

However, mysql does not support the
following SQL operations!

minus / except

intersect

What should we do?
Write the query in an alternative way.

Use our knowledge in relational algebra!

SQL (MySQL)

Relational algebra

Revisit

Set Union ∪

Set difference (minus) –

Set Intersection ∩
Join

Division ÷

SQL (MySQL)

Union

x y

1 A

2 B

3 C

4 D

x y

1 A

3 C

x y

1 A

2 B

3 C

4 D

a b

(select * from a)
union
(select * from b);

x y

1 A

2 B

3 C

4 D

1 A

3 C

(select * from a)
union all
(select * from b);

SQL (MySQL)

Difference

x y

1 A

2 B

3 C

4 D

x y

1 A

3 C

a b

x y

2 B

4 D

select * from a
where (x,y) not in (select *

from b);

For every tuple in a, check that the tuple values does not exists in b;

For every tuple in a, check that it is not in b;

x y

2 B

4 D

select * from a
where not exists (select * from b

where b.x=a.x and b.y=a.y)

SQL (MySQL)

Intersection

x y

1 A

2 B

3 C

4 D

x y

1 A

3 C

a b

x y

1 A

3 C

select * from a
where (x,y) in (select * from b);

For every tuple in a, check that the tuple values also exists in b;

For every tuple in a, check that it is also in b;

x y

1 A

3 C

select * from a
where exists (select * from b

where b.x=a.x and b.y=a.y)

SQL (MySQL)

Join (recap)

Natural join

+—–+———+——+——+——-+——+——+

| pid | name | dept | rank | sal | cid | year |

+—–+———+——+——+——-+——+——+

| p1 | Adam | CS | asst | 6000 | c1 | 2011 |

| p1 | Adam | CS | asst | 6000 | c2 | 2012 |

| p2 | Bob | EE | asso | 8000 | c2 | 2012 |

+—–+———+——+——+——-+——+——+

SQL (MySQL)

Join

Natural join

+—–+———+——+——+——-+——+——+——+

| pid | name | dept | rank | sal | pid | cid | year |

+—–+———+——+——+——-+——+——+——+

| p1 | Adam | CS | asst | 6000 | p1 | c1 | 2011 |

| p1 | Adam | CS | asst | 6000 | p1 | c2 | 2012 |

| p2 | Bob | EE | asso | 8000 | p2 | c2 | 2012 |

+—–+———+——+——+——-+——+——+——+

select *

from PROF inner join TEACH

on PROF.pid = TEACH.pid;

SQL (MySQL)

Join

Left Outer Join (also check right outer join)

+—–+———+——+——+——-+——+——+——+

| pid | name | dept | rank | sal | pid | cid | year |

+—–+———+——+——+——-+——+——+——+

| p1 | Adam | CS | asst | 6000 | p1 | c1 | 2011 |

| p1 | Adam | CS | asst | 6000 | p1 | c2 | 2012 |

| p2 | Bob | EE | asso | 8000 | p2 | c2 | 2012 |

| p3 | Calvin | CS | full | 10000 | NULL | NULL | NULL |

| p4 | Dorothy | EE | asst | 5000 | NULL | NULL | NULL |

| p5 | Emily | EE | asso | 8500 | NULL | NULL | NULL |

+—–+———+——+——+——-+——+——+——+

select * from PROF left outer join TEACH on PROF.pid = TEACH.pid;

http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/
http://www.codeproject.com/KB/database/Visual_SQL_Joins/Visual_SQL_JOINS_orig.jpg

SQL (MySQL)

Division (revisit)

(select distinct y from T1)
minus
select distinct y from (

(select * from (select distinct y from T1), T2)
minus
(select * from T1)

)

S1 – S2 = {y, x} – {x} = {y}

y x
A 1
A 2
A 3
B 1
B 2
C 3
D 3

÷
x
1
2
3

y
A=

T1 T2

SQL (MySQL)

Division (MySQL)

MySQL
– does not support minus!
– But we can use not in / not exists.

S1 – S2 = {y, x} – {x} = {y}

y x
A 1
A 2
A 3
B 1
B 2
C 3
D 3

÷
x
1
2
3

y
A=

T1 T2

SQL (MySQL)

Division (MySQL: no minus)

select distinct y
from T1
where y not in (select distinct y

from ((select distinct y from T1) as Tt, T2)
where (y,x) not in (select * from T1)

);

S1 – S2 = {y, x} – {x} = {y}

y x
A 1
A 2
A 3
B 1
B 2
C 3
D 3

÷
x
1
2
3

y
A=

MySQL: Every derived table must have its own alias

T1 T2
S1 S2

SQL (MySQL)

Division (MySQL: no minus)

select distinct y
from T1
where y not in (select distinct y

from ((select distinct y from T1) as Tt, T2)
where (y,x) not in (select * from T1)

);

(select distinct y from T1)
minus
select distinct y from (

(select * from (select distinct y from T1), T2)
minus
(select * from T1)

)

Same colour shows same block
Rewrite using not in

If you understand the relational algebra, you understand the above.

SQL (MySQL)

Division (not exists)

select distinct y
from T1
where not exists ( select * from T2

where not exists ( select * from T1 as s
where s.y = T1.y and

s.x = T2.x
)

);

y x
A 1
A 2
A 3
B 1
B 2
C 3
D 3

÷
x
1
2
3

y
A=

T1 T2

SQL (MySQL)

Division – analysis

select distinct y
from T1
where not exists ( select * from T2

where not exists ( select * from T1 as s
where s.y = T1.y and

s.x = T2.x
)

);

y x
A 1
A 2
A 3
B 1
B 2
C 3
D 3

÷
x
1
2
3

y
A=

where clause – tuple filter

fo
r

lo
o

p
T1 T2

SQL (MySQL)

Division – analysis

select distinct y
from T1
where not exists ( select * from T2

where not exists ( select * from T1 as s
where s.y = T1.y and

s.x = T2.x
)

);

y x
A 1
A 2
A 3
B 1
B 2
C 3
D 3

÷
x
1
2
3

y
A=

where clause – tuple filter

fo
r

lo
o

p

fo
r

lo
o

p

T1 T2

SQL (MySQL)

Division – analysis

select distinct y
from T1
where not exists ( select * from T2

where not exists ( select * from T1 as s
where s.y = T1.y and

s.x = T2.x
)

);

y x
A 1
A 2
A 3
B 1
B 2
C 3
D 3

÷
x
1
2
3

y
A=

where clause – tuple filter

fo
r

lo
o

p

fo
r

lo
o

p

fo
r

lo
o

p

T1 T2

SQL (MySQL)

Division – analysis

select distinct y
from T1
where not exists ( select * from T2

where not exists ( select * from T1 as s
where s.y = T1.y and

s.x = T2.x
)

);

y x
A 1
A 2
A 3
B 1
B 2
C 3
D 3

÷
x
1
2
3

y
A=

where clause – tuple filter

T1 T2

SQL (MySQL)

Division – analysis

select distinct y
from T1
where not exists ( select * from T2

where not exists ( select * from T1 as s
where s.y = ‘A’ and

s.x = 1
)

);

y x
A 1
A 2
A 3
B 1
B 2
C 3
D 3

÷
x
1
2
3

y
A=

where clause – tuple filter

T1 T2

SQL (MySQL)

Division – analysis

select distinct y
from T1
where not exists ( select * from T2

where not exists ( select * from T1 as s
where s.y = ‘A’ and

s.x = 1
)

);

y x
A 1
A 2
A 3
B 1
B 2
C 3
D 3

÷
x
1
2
3

y
A=

where clause – tuple filter

y x
A 1

T1 T2

SQL (MySQL)

Division – analysis

select distinct y
from T1
where not exists ( select * from T2

where not exists ( select * from T1 as s
where s.y = ‘A’ and

s.x = 2
)

);

y x
A 1
A 2
A 3
B 1
B 2
C 3
D 3

÷
x
1
2
3

y
A=

where clause – tuple filter

T1 T2

SQL (MySQL)

Division – analysis

select distinct y
from T1
where not exists ( select * from T2

where not exists ( select * from T1 as s
where s.y = ‘A’ and

s.x = 2
)

);

y x
A 1
A 2
A 3
B 1
B 2
C 3
D 3

÷
x
1
2
3

y
A=

where clause – tuple filter

T1 T2

y x
A 2

SQL (MySQL)

Division – analysis

select distinct y
from T1
where not exists ( select * from T2

where not exists ( select * from T1 as s
where s.y = ‘A’ and

s.x = 2
)

);

y x
A 1
A 2
A 3
B 1
B 2
C 3
D 3

÷
x
1
2
3

y
A=

where clause – tuple filter

T1 T2

y x
A 2

SQL (MySQL)

Division – analysis

select distinct y
from T1
where not exists ( select * from T2

where not exists ( select * from T1 as s
where s.y = ‘A’ and

s.x = 2
)

);

y x
A 1
A 2
A 3
B 1
B 2
C 3
D 3

÷
x
1
2
3

y
A=

where clause – tuple filter

T1 T2

y x
A 3

SQL (MySQL)

Division – analysis

select distinct y
from T1
where not exists ( select * from T2

where not exists ( select * from T1 as s
where s.y = ‘A’ and

s.x = 2
)

);

y x
A 1
A 2
A 3
B 1
B 2
C 3
D 3

÷
x
1
2
3

y
A=

where clause – tuple filter

T1 T2

x
empty

SQL (MySQL)

Division – analysis

select distinct y
from T1
where not exists ( select * from T2

where not exists ( select * from T1 as s
where s.y = T1.y and

s.x = T2.x
)

);

y x
A 1
A 2
A 3
B 1
B 2
C 3
D 3

÷
x
1
2
3

y
A=

where clause – tuple filter

T1 T2

y x
A 1

SQL (MySQL)

Division – analysis

select distinct y
from T1
where not exists ( select * from T2

where not exists ( select * from T1 as s
where s.y = T1.y and

s.x = T2.x
)

);

y x
A 1
A 2
A 3
B 1
B 2
C 3
D 3

÷
x
1
2
3

y
A=

where clause – tuple filter

T1 T2

y x
A 2

SQL (MySQL)

Division – analysis

select distinct y
from T1
where not exists ( select * from T2

where not exists ( select * from T1 as s
where s.y = T1.y and

s.x = T2.x
)

);

y x
A 1
A 2
A 3
B 1
B 2
C 3
D 3

÷
x
1
2
3

y
A=

where clause – tuple filter

T1 T2

y x
A 3

SQL (MySQL)

Division – analysis

select distinct y
from T1
where not exists ( select * from T2

where not exists ( select * from T1 as s
where s.y = ‘A’ and

s.x = 2
)

);

y x
A 1
A 2
A 3
B 1
B 2
C 3
D 3

÷
x
1
2
3

y
A=

where clause – tuple filter

T1 T2

x
empty

SQL (MySQL)

Division – analysis

select distinct y
from T1
where not exists ( select * from T2

where not exists (select * from T1 as s
where s.y = T1.y and

s.x = T2.x
)

);

y x
A 1
A 2
A 3
B 1
B 2
C 3
D 3

÷
x
1
2
3

y
A=

where clause – tuple filter

T1 T2

y x
B 1

SQL (MySQL)

Division – analysis

select distinct y
from T1
where not exists ( select * from T2

where not exists (select * from T1 as s
where s.y = T1.y and

s.x = T2.x
)

);

y x
A 1
A 2
A 3
B 1
B 2
C 3
D 3

÷
x
1
2
3

y
A=

where clause – tuple filter

T1 T2

y x
B 2

SQL (MySQL)

Division – analysis

select distinct y
from T1
where not exists ( select * from T2

where not exists (select * from T1 as s
where s.y = T1.y and

s.x = T2.x
)

);

y x
A 1
A 2
A 3
B 1
B 2
C 3
D 3

÷
x
1
2
3

y
A=

where clause – tuple filter

T1 T2

y x
B 3

SQL (MySQL)

Division – analysis

select distinct y
from T1
where not exists ( select * from T2

where not exists (select * from T1 as s
where s.y = T1.y and

s.x = T2.x
)

);

y x
A 1
A 2
A 3
B 1
B 2
C 3
D 3

÷
x
1
2
3

y
A=

where clause – tuple filter

T1 T2

x
3

SQL (MySQL)

Division – analysis

select distinct y
from T1
where not exists ( select * from T2

where not exists (select * from T1 as s
where s.y = T1.y and

s.x = T2.x
)

);

y x
A 1
A 2
A 3
B 1
B 2
C 3
D 3

÷
x
1
2
3

y
A=

where clause – tuple filter

T1 T2

SQL (MySQL)

MySQL and Set

This slide is outside Exam Syllabus

There are at least three other techniques to
rewrite Division SQL queries in MySQL.

http://users.abo.fi/soini/divisionEnglish.pdf

See blackboard for a cache.

A clear pictorial explanation is also provided.

http://users.abo.fi/soini/divisionEnglish.pdf