Lecture "advanced SQL" ---------------------- Thomas Padron-McCarthy (Thomas.Padron-McCarthy@tech.oru.se), 18 september 2003 1. Superkort repetition 2. Aggregatfunktioner med "group by", "having" och "where" 3. Explicita joinar 4. Yttre join 5. count kan räkna antingen rader eller värden 6. Flera joinar i samma fråga, inkl mimer-bugg-exemplet (men rätt) 7. Vyer 8. insert, update, delete 9. Join med sig själv 10. Recursive closure 11. Relationsalgebra 12. Ännu mer SQL 1. Superkort repetition ----------------------- select A, B, C from T1, T2, T3 where VILLKOR for each t1 in T1 { for each t2 in T2 { for each t3 in T3 { if VILLKOR print A, B, C } } } 2. Aggregatfunktioner med "group by", "having" och "where" ---------------------------------------------------------- worker +-----+-------+--------+------+ | wno | wname | salary | at | +-----+-------+--------+------+ | 1 | Bob | 1000 | 10 | | 2 | Liz | 2000 | 10 | | 3 | Sam | 1000 | 30 | +-----+-------+--------+------+ (1) Låt stå. Exemplet. office +-----+-----------+ | ono | oname | +-----+-----------+ | 10 | Gnesta | | 20 | Moskva | | 30 | Pyongyang | +-----+-----------+ (2) Låt stå. Exemplet. SQL-kod för att skapa tabeller: create table worker (wno integer, wname varchar(3), salary integer, at integer, foreign key (at) references office (ono), primary key (wno)); insert into worker (wno, wname, salary, at) values (1, 'Bob', 1000, 10); insert into worker (wno, wname, salary, at) values (2, 'Liz', 2000, 10); insert into worker (wno, wname, salary, at) values (3, 'Sam', 1000, 30); create table office (ono integer, oname varchar(10), primary key (ono)); insert into office (ono, oname) values (10, 'Gnesta'); insert into office (ono, oname) values (20, 'Moskva'); insert into office (ono, oname) values (30, 'Pyongyang'); (Note: "at" is a reserved word in Mimer, and perhaps in standard SQL. Replace with for example "location".) Vi kan koppla ihop de två tabellerna så man ser tydligt vem som jobbar var: select * from worker, office where at = ono; +-----+-------+--------+------+-----+-----------+ | wno | wname | salary | at | ono | oname | +-----+-------+--------+------+-----+-----------+ | 1 | Bob | 1000 | 10 | 10 | Gnesta | | 2 | Liz | 2000 | 10 | 10 | Gnesta | | 3 | Sam | 1000 | 30 | 30 | Pyongyang | +-----+-------+--------+------+-----+-----------+ (3) (4) Låt stå. Återkommer. Utvikning: Vi har kopplat ihop de två tabellerna med en join, under villkoret at = ono. Moskva försvann ur resultatet, för ingen arbetare jobbar i Moskva. (Relationsalgebrauttryck med fjärilssymbolen.) +-----+-------+--------+------+ +-----+-----------+ | wno | wname | salary | at | | ono | oname | +-----+-------+--------+------+ +-----+-----------+ | 1 | Bob | 1000 | 10 | - | 10 | Gnesta | | 2 | Liz | 2000 | 10 | / | 20 | Moskva | | 3 | Sam | 1000 | 30 | - | 30 | Pyongyang | +-----+-------+--------+------+ +-----+-----------+ (5) Låt stå. Återkommer. Aggregatfunktioner: select sum(salary) from worker; +-------------+ | sum(salary) | +-------------+ | 4000 | +-------------+ select avg(salary) from worker; +-------------+ | avg(salary) | +-------------+ | 1333.3333 | +-------------+ Man kan lägga på en where-sats. FÖRST körs frågan med where-satsen, SEN sker summeringen: select avg(salary) from worker where wname = 'Bob' or wname = 'Sam'; +-------------+ | avg(salary) | +-------------+ | 1000.0000 | +-------------+ (Det kan vara en mer komplicerad fråga, med flera tabeller. Vi ska se exempel på det sen.) Ett annat villkor: Bara de som jobbar på kontoret i Gnesta (nummer 10). select avg(salary) from worker where at = 10; +-------------+ | avg(salary) | +-------------+ | 1500.0000 | +-------------+ Vi kan ta båda kontoren, i Gnesta (10) och Pyongyang (30). SQL-operationen UNION slår ihop två tabeller (= mängder av rader). select avg(salary) from worker where at = 10 union select avg(salary) from worker where at = 30; +-------------+ | avg(salary) | +-------------+ | 1500.0000 | | 1000.0000 | +-------------+ (6) Låt stå. Återkommer. (select avg(salary) from worker where at = 10) union (select avg(salary) from worker where at = 30); Andra mängdoperationer i SQL förutom UNION: INTERSECTION (= snitt) EXCEPT (= skillnad, minus) Inte samma sak som select avg(salary) from worker where at = 10 or at = 30; +-------------+ | avg(salary) | +-------------+ | 1333.3333 | +-------------+ select avg(salary) from worker where at = 10 and at = 30; +-------------+ | avg(salary) | +-------------+ | NULL | +-------------+ NULL, eftersom medelvärdet av noll tal är odefinierat. Men jobbigt om vi vill ha genomsnittslönen för vart och ett av kontoren. Enklare med SQL-konstruktionen GROUP BY: select avg(salary) from worker group by at; +-------------+ | avg(salary) | +-------------+ | 1500.0000 | | 1000.0000 | +-------------+ (6) Samma som tidigare. Två problem: ordning, dubletter (men SQL behåller dubletterna) select at, avg(salary) from worker group by at; +------+-------------+ | at | avg(salary) | +------+-------------+ | 10 | 1500.0000 | | 30 | 1000.0000 | +------+-------------+ Man kan fortfarande ha ett WHERE-villkor, som görs FÖRST, FÖRE aggregatfunktionen: select at, sum(salary) from worker where wname = 'Bob' or wname = 'Sam' group by at; +------+-------------+ | at | sum(salary) | +------+-------------+ | 10 | 1000 | | 30 | 1000 | +------+-------------+ (7) Låt stå. Återkommer. Se det som att FÖRST körs frågan med WHERE-villkoret: select * from worker where wname = 'Bob' or wname = 'Sam'; +-----+-------+--------+------+ | wno | wname | salary | at | +-----+-------+--------+------+ | 1 | Bob | 1000 | 10 | | 3 | Sam | 1000 | 30 | +-----+-------+--------+------+ Man kan ta med bara vissa av grupperna: select at, avg(salary) from worker group by at; +------+-------------+ | at | avg(salary) | +------+-------------+ | 10 | 1500.0000 | | 30 | 1000.0000 | +------+-------------+ select at, avg(salary) from worker group by at having avg(salary) > 1000; +------+-------------+ | at | avg(salary) | +------+-------------+ | 10 | 1500.0000 | +------+-------------+ (Ja, 1000 är inte mer än 1000.) Man kan ha WHERE, GROUP BY och HAVING: select at, avg(salary) from worker where wname = 'Bob' or wname = 'Sam' group by at having avg(salary) > 1000; +------+-------------+ | at | avg(salary) | +------+-------------+ Tom tabell?? Ja, ingen av grupperna hade mer än 1000 i genomsnittslön: select at, avg(salary) from worker where wname = 'Bob' or wname = 'Sam' group by at; +------+-------------+ | at | avg(salary) | +------+-------------+ | 10 | 1000.0000 | | 30 | 1000.0000 | +------+-------------+ (7) Samma som tidigare. FÖRST frågan (med where-villkoret som "väljer ut rader"), SEN aggregatfunktionerna (ev i grupper), SIST having-villkoret "väljer ut grupper" Vi kan ha fler än en tabell i den där frågan som körs först: select * from worker, office where at = ono; +-----+-------+--------+------+-----+-----------+ | wno | wname | salary | at | ono | oname | +-----+-------+--------+------+-----+-----------+ | 1 | Bob | 1000 | 10 | 10 | Gnesta | | 2 | Liz | 2000 | 10 | 10 | Gnesta | | 3 | Sam | 1000 | 30 | 30 | Pyongyang | +-----+-------+--------+------+-----+-----------+ (3) Samma som tidigare. select oname, avg(salary) from worker, office where at = ono group by oname; +-----------+-------------+ | oname | avg(salary) | +-----------+-------------+ | Gnesta | 1500.0000 | | Pyongyang | 1000.0000 | +-----------+-------------+ Igen: Se det som att man kör den vanliga "select * from ... where ..." FÖRST, och SEN kör man aggregatfunktionerna. Annat exempel, sum i stället för avg: select oname, sum(salary) from worker, office where at = ono group by oname; +-----------+-------------+ | oname | sum(salary) | +-----------+-------------+ | Gnesta | 3000 | | Pyongyang | 1000 | +-----------+-------------+ Problem: Moskva försvann. (Ingen jobbar i Moskva, men vi HAR ett kontor där.) Kanske ännu värre om man vill ha en lista över hur många anställda varje kontor har: select oname, count(*) from worker, office where at = ono group by oname; +-----------+----------+ | oname | count(*) | +-----------+----------+ | Gnesta | 2 | | Pyongyang | 1 | +-----------+----------+ Hur ska man få med Moskva? Vi behöver nu prata om explicita joinar! 3. Explicita joinar ------------------- Med frågan +-----+-------+--------+------+-----+-----------+ | wno | wname | salary | at | ono | oname | +-----+-------+--------+------+-----+-----------+ | 1 | Bob | 1000 | 10 | 10 | Gnesta | | 2 | Liz | 2000 | 10 | 10 | Gnesta | | 3 | Sam | 1000 | 30 | 30 | Pyongyang | +-----+-------+--------+------+-----+-----------+ (4) Samma som tidigare. Vi har ju kopplat ihop de två tabellerna med en join, under villkoret at = ono: +-----+-------+--------+------+ +-----+-----------+ | wno | wname | salary | at | | ono | oname | +-----+-------+--------+------+ +-----+-----------+ | 1 | Bob | 1000 | 10 | - | 10 | Gnesta | | 2 | Liz | 2000 | 10 | / | 20 | Moskva | | 3 | Sam | 1000 | 30 | - | 30 | Pyongyang | +-----+-------+--------+------+ +-----+-----------+ (5) Samma som tidigare. Det här kan man skriva med en explicit join i from-listan: select * from (worker join office on at = ono); Man kan se det som att först beräknas joinen och blir en ny tabell, och sen ingår den i from-listan precis som en vanlig tabell. Vi kan också lägga på ett where-villkor som vanligt, till exempel för att få dem som jobbar i Gnesta: select * from worker, office where at = ono and oname = 'Gnesta'; +-----+-------+--------+------+-----+--------+ | wno | wname | salary | at | ono | oname | +-----+-------+--------+------+-----+--------+ | 1 | Bob | 1000 | 10 | 10 | Gnesta | | 2 | Liz | 2000 | 10 | 10 | Gnesta | +-----+-------+--------+------+-----+--------+ Alternativt, med en explicit join: select * from (worker join office on at = ono) where oname = 'Gnesta'; Alltså: FÖRST skapas "tabellerna i from-listan", SEN körs frågan Vi ska se senare hur man kan koppla ihop fler än två tabeller med explicita joinar, eller en blandning av explicita och implicita joinar. Notera att när vi joinar två tabeller, vare sig implicit select * from worker, office where at = ono; eller explicit select * from (worker join office on at = ono); så försvinner de rader som inte går att koppla ihop med en annan rad. Kontoret i Moskva försvinner. 4. Yttre joinar --------------- I en vanlig join försvinner de rader som inte går att koppla ihop med en annan rad. I en YTTRE JOIN behåller man dem. select * from (worker right outer join office on at = ono); +------+-------+--------+------+-----+-----------+ | wno | wname | salary | at | ono | oname | +------+-------+--------+------+-----+-----------+ | 1 | Bob | 1000 | 10 | 10 | Gnesta | | 2 | Liz | 2000 | 10 | 10 | Gnesta | | NULL | NULL | NULL | NULL | 20 | Moskva | | 3 | Sam | 1000 | 30 | 30 | Pyongyang | +------+-------+--------+------+-----+-----------+ (8) Låt stå. Exemplet. HÖGER-ytter-join = behåller alla rader i den HÖGRA tabellen. Vänster, höger och full yttre join. (Relationsalgebrauttryck med tre olika fjärilssymboler.) Nu kan vi beräkna summor och genomsnitt: select avg(salary) from (worker right outer join office on at = ono); +-------------+ | avg(salary) | +-------------+ | 1333.3333 | +-------------+ Men (1000 + 2000 + 0 + 1000) / 4 är ju 1000? Nej, NULL är inte 0, utan betyder att det inte är något värde alls. Grupperat: select oname, avg(salary) from (worker right outer join office on at = ono) group by oname; +-----------+-------------+ | oname | avg(salary) | +-----------+-------------+ | Gnesta | 1500.0000 | | Moskva | NULL | | Pyongyang | 1000.0000 | +-----------+-------------+ Man kan ha flera aggregatfunktioner i samma resultat: select oname, avg(salary), sum(salary) from (worker right outer join office on at = ono) group by oname; +-----------+-------------+-------------+ | oname | avg(salary) | sum(salary) | +-----------+-------------+-------------+ | Gnesta | 1500.0000 | 3000 | | Moskva | NULL | NULL | | Pyongyang | 1000.0000 | 1000 | +-----------+-------------+-------------+ Opraktiskt att få NULL i summan, till exempel om SQL-frågan körs inifrån ett C-program och C-programmet ska jobba vidare med resultatet. Använd funktionen COALESCE: select oname, avg(salary), coalesce(sum(salary), 0) from (worker right outer join office on at = ono) group by oname; +-----------+-------------+--------------------------+ | oname | avg(salary) | coalesce(sum(salary), 0) | +-----------+-------------+--------------------------+ | Gnesta | 1500.0000 | 3000 | | Moskva | NULL | 0 | | Pyongyang | 1000.0000 | 1000 | +-----------+-------------+--------------------------+ 5. count kan räkna antingen rader eller värden ---------------------------------------------- Lokalkontoren och deras arbetare: select * from (worker right outer join office on at = ono); +------+-------+--------+------+-----+-----------+ | wno | wname | salary | at | ono | oname | +------+-------+--------+------+-----+-----------+ | 1 | Bob | 1000 | 10 | 10 | Gnesta | | 2 | Liz | 2000 | 10 | 10 | Gnesta | | NULL | NULL | NULL | NULL | 20 | Moskva | | 3 | Sam | 1000 | 30 | 30 | Pyongyang | +------+-------+--------+------+-----+-----------+ (8) Samma som tidigare. count(*) räknar rader: select oname, count(*) from (worker right outer join office on at = ono) group by oname; +-----------+----------+ | oname | count(*) | +-----------+----------+ | Gnesta | 2 | | Moskva | 1 | | Pyongyang | 1 | +-----------+----------+ count(X) räknar antalet värden i kolumnen X. NULL-värden räknas inte! select oname, count(ono) from (worker right outer join office on at = ono) group by oname; +-----------+------------+ | oname | count(ono) | +-----------+------------+ | Gnesta | 2 | | Moskva | 1 | | Pyongyang | 1 | +-----------+------------+ select oname, count(wno) from (worker right outer join office on at = ono) group by oname; +-----------+------------+ | oname | count(wno) | +-----------+------------+ | Gnesta | 2 | | Moskva | 0 | | Pyongyang | 1 | +-----------+------------+ (9a) (9b) (9c) Låt stå. Återkommer. 6. Flera joinar i samma fråga, inkl mimer-bugg-exemplet (men rätt) ------------------------------------------------------------------ Vi har också projekt som de anställda kan jobba på. project +-----+-----------+ | pno | pname | +-----+-----------+ | 100 | Apollo | | 200 | Manhattan | | 300 | Zork | +-----+-----------+ (10) Låt stå. Exemplet. Många-till-många-samband mellan anställda och projekt. Det blir en tabell: works +--------+---------+ | worker | project | +--------+---------+ | 1 | 100 | | 1 | 200 | | 2 | 200 | +--------+---------+ (11) Låt stå. Exemplet. SQL-kod för att skapa tabeller: create table project (pno integer, pname varchar(10), primary key (pno)); insert into project (pno, pname) values (100, 'Apollo'); insert into project (pno, pname) values (200, 'Manhattan'); insert into project (pno, pname) values (300, 'Zork'); create table works (worker integer, project integer, primary key (worker, project), foreign key (worker) references worker(wno), foreign key (project) references project(pno)); insert into works (worker, project) values (1, 100); insert into works (worker, project) values (1, 200); insert into works (worker, project) values (2, 200); Vem jobbar var? select * from worker, works, project where wno = worker and project = pno; +-----+-------+--------+------+--------+---------+-----+-----------+ | wno | wname | salary | at | worker | project | pno | pname | +-----+-------+--------+------+--------+---------+-----+-----------+ | 1 | Bob | 1000 | 10 | 1 | 100 | 100 | Apollo | | 1 | Bob | 1000 | 10 | 1 | 200 | 200 | Manhattan | | 2 | Liz | 2000 | 10 | 2 | 200 | 200 | Manhattan | +-----+-------+--------+------+--------+---------+-----+-----------+ So Bob works on the Apollo project and on the Manhattan project. Liz also works onthe Manhattan project. Sam works nowhere. Noone works on the Zork project. Två (vanliga, inre) joinar, som kopplar ihop tre tabeller: +-----+-------+--------+------+ +--------+---------+ +-----+-----------+ | wno | wname | salary | at | | worker | project | | pno | pname | +-----+-------+--------+------+ +--------+---------+ +-----+-----------+ | 1 | Bob | 1000 | 10 | - | 1 | 100 | - | 100 | Apollo | | 2 | Liz | 2000 | 10 | \ | 1 | 200 | - | 200 | Manhattan | | 3 | Sam | 1000 | 30 | \ | 2 | 200 | / | 300 | Zork | +-----+-------+--------+------+ +--------+---------+ +-----+-----------+ Here are three queries that do the same thing, but with explicit joins: select * from (worker join works on wno = worker) join project on project = pno; select * from worker join (works join project on project = pno) on wno = worker; select * from worker join works on wno = worker join project on project = pno; Man kan kombinera, och ha en explicit och en implicit join: select * from (worker join works on wno = worker), project where project = pno; select * from worker, (works join project on project = pno) where wno = worker; Men man får se upp när man har en YTTRE join. Låt oss säga att jag vill ha med alla arbetarna i resultatet, även de som inte arbetar på något project (dvs Sam), med NULL-värden i de övriga kolumnerna: +-----+-------+--------+------+--------+---------+------+-----------+ | wno | wname | salary | at | worker | project | pno | pname | +-----+-------+--------+------+--------+---------+------+-----------+ | 1 | Bob | 1000 | 10 | 1 | 100 | 100 | Apollo | | 1 | Bob | 1000 | 10 | 1 | 200 | 200 | Manhattan | | 2 | Liz | 2000 | 10 | 2 | 200 | 200 | Manhattan | | 3 | Sam | 1000 | 30 | NULL | NULL | NULL | NULL | +-----+-------+--------+------+--------+---------+------+-----------+ Vi provar: select * from (worker left outer join works on wno = worker), project where project = pno; +-----+-------+--------+------+--------+---------+-----+-----------+ | wno | wname | salary | at | worker | project | pno | pname | +-----+-------+--------+------+--------+---------+-----+-----------+ | 1 | Bob | 1000 | 10 | 1 | 100 | 100 | Apollo | | 1 | Bob | 1000 | 10 | 1 | 200 | 200 | Manhattan | | 2 | Liz | 2000 | 10 | 2 | 200 | 200 | Manhattan | +-----+-------+--------+------+--------+---------+-----+-----------+ Ingen Sam! Som vanligt kan man byta ut en implicit join mot en explicit, så frågan är ekvivalent med select * from (worker left outer join works on wno = worker) join project on project = pno; Dvs, först räknar vi ut den yttre joinen: select * from (worker left outer join works on wno = worker); +-----+-------+--------+------+--------+---------+ | wno | wname | salary | at | worker | project | +-----+-------+--------+------+--------+---------+ | 1 | Bob | 1000 | 10 | 1 | 100 | | 1 | Bob | 1000 | 10 | 1 | 200 | | 2 | Liz | 2000 | 10 | 2 | 200 | | 3 | Sam | 1000 | 30 | NULL | NULL | +-----+-------+--------+------+--------+---------+ Sen joinar vi den (med en vanlig inre join) med tabellen project: project +-----+-----------+ | pno | pname | +-----+-----------+ | 100 | Apollo | | 200 | Manhattan | | 300 | Zork | +-----+-----------+ Joinvillkoret är project = pno, men project är NULL i resultatet av den yttre joinen! Inga rader i project matchar. (Och skulle inte göra det även om det fanns en rad där pno är NULL, för NULL = NULL är falskt i SQL. Värre ändå: NULL <> NULL är OCKSÅ falskt i SQL. Kom ihåg: NULL är inte ett värde. Man kan säga att SQL har trevärd logik: sant, falskt och ett värde som betyder "ogiltigt".) Prova med en yttre join med project: select * from (worker left outer join works on wno = worker) left outer join project on project = pno; +-----+-------+--------+------+--------+---------+------+-----------+ | wno | wname | salary | at | worker | project | pno | pname | +-----+-------+--------+------+--------+---------+------+-----------+ | 1 | Bob | 1000 | 10 | 1 | 100 | 100 | Apollo | | 1 | Bob | 1000 | 10 | 1 | 200 | 200 | Manhattan | | 2 | Liz | 2000 | 10 | 2 | 200 | 200 | Manhattan | | 3 | Sam | 1000 | 30 | NULL | NULL | NULL | NULL | +-----+-------+--------+------+--------+---------+------+-----------+ Rätt svar! Man skulle också kunna göra så här, med FÖRST en vanlig join mellan works och project, och SEN vänster-ytter-joinar vi worker med resultatet av den inre joinen: select * from worker left outer join (works join project on project = pno) on worker = wno; Krångligt? Ja, det tycker Mimer också, och gör fel. (MySQL gör också fel. PostgreSQL och Microsoft SQL Server gör rätt.) 7. Vyer ------- Förenkla krångliga frågor. create view wp as select * from works join project on project = pno; select * from wp; +--------+---------+-----+-----------+ | worker | project | pno | pname | +--------+---------+-----+-----------+ | 1 | 100 | 100 | Apollo | | 1 | 200 | 200 | Manhattan | | 2 | 200 | 200 | Manhattan | +--------+---------+-----+-----------+ select * from worker left outer join wp on wno = worker; +-----+-------+--------+------+--------+---------+------+-----------+ | wno | wname | salary | at | worker | project | pno | pname | +-----+-------+--------+------+--------+---------+------+-----------+ | 1 | Bob | 1000 | 10 | 1 | 100 | 100 | Apollo | | 1 | Bob | 1000 | 10 | 1 | 200 | 200 | Manhattan | | 2 | Liz | 2000 | 10 | 2 | 200 | 200 | Manhattan | | 3 | Sam | 1000 | 30 | NULL | NULL | NULL | NULL | +-----+-------+--------+------+--------+---------+------+-----------+ Rätt svar, även i Mimer! Vyer är ofta bra för krångliga aggregatfunktioner. Vem har högst lön? Otillåten syntax: select * from worker where salary = max(salary); Rätt: select * from worker where salary = (select max(salary) from worker); +-----+-------+--------+------+ | wno | wname | salary | at | +-----+-------+--------+------+ | 2 | Liz | 2000 | 10 | +-----+-------+--------+------+ Vilket lokalkontor har flest arbetare? select oname, count(wno) from (worker right outer join office on at = ono) group by oname; +-----------+------------+ | oname | count(wno) | +-----------+------------+ | Gnesta | 2 | | Moskva | 0 | | Pyongyang | 1 | +-----------+------------+ (9a) Samma som tidigare. Skapa en vy av detta: create view officestaff as select oname, count(wno) from (worker right outer join office on at = ono) group by oname; Fel: "count(wno)" är inte ett tillåtet kolumnnamn. Bättre: create view officestaff as select oname, count(wno) as number from (worker right outer join office on at = ono) group by oname; select * from officestaff; +-----------+--------+ | oname | number | +-----------+--------+ | Gnesta | 2 | | Moskva | 0 | | Pyongyang | 1 | +-----------+--------+ (9b) Samma som tidigare. Eller: create view officestaff(location, number) as select oname, count(wno) from (worker right outer join office on at = ono) group by oname; select * from officestaff; +-----------+--------+ | location | number | +-----------+--------+ | Gnesta | 2 | | Moskva | 0 | | Pyongyang | 1 | +-----------+--------+ (9c) Samma som tidigare. Sen använder vi vyn av joinen som om den var en tabell, på samma sätt som när vi ville veta vem som hade högst lön: select * from officestaff where number = (select max(number) from officestaff); +-----------+--------+ | location | number | +-----------+--------+ | Gnesta | 2 | +-----------+--------+ I en del SQL-dialekter kan man definiera vyer i from-listan, som bara gäller i just den frågan. 8. insert, update, delete ------------------------- insert into worker values (4, 'Tom', 3000, 20); insert into worker (wno, wname, at) values (5, 'Zeb', 20); select * from worker; +-----+-------+--------+------+ | wno | wname | salary | at | +-----+-------+--------+------+ | 1 | Bob | 1000 | 10 | | 2 | Liz | 2000 | 10 | | 3 | Sam | 1000 | 30 | | 4 | Tom | 3000 | 20 | | 5 | Zeb | NULL | 20 | +-----+-------+--------+------+ Man kan ta resultatet av en SQL-fråga och stoppa in i en tabell: insert into worker (wno, wname, salary) select ono, 'Foo', ono + 4711 from office; select * from worker; +-----+-------+--------+------+ | wno | wname | salary | at | +-----+-------+--------+------+ | 1 | Bob | 1000 | 10 | | 2 | Liz | 2000 | 10 | | 3 | Sam | 1000 | 30 | | 4 | Tom | 3000 | 20 | | 5 | Zeb | NULL | 20 | | 10 | Foo | 4721 | NULL | | 20 | Foo | 4731 | NULL | | 30 | Foo | 4741 | NULL | +-----+-------+--------+------+ Höj Bobs lön med 3 procent: update worker set salary = salary * 1.03 where wname = 'Bob'; select * from worker; +-----+-------+--------+------+ | wno | wname | salary | at | +-----+-------+--------+------+ | 1 | Bob | 1030 | 10 | | 2 | Liz | 2000 | 10 | | 3 | Sam | 1000 | 30 | | 4 | Tom | 3000 | 20 | | 5 | Zeb | NULL | 20 | | 10 | Foo | 4721 | NULL | | 20 | Foo | 4731 | NULL | | 30 | Foo | 4741 | NULL | +-----+-------+--------+------+ Höj lönen med 3 procent för alla som jobbar på vårt kontor i Moskva: update worker set salary = salary * 1.03 where at in (select ono from office where oname = 'Moskva'); select * from worker; +-----+-------+--------+------+ | wno | wname | salary | at | +-----+-------+--------+------+ | 1 | Bob | 1030 | 10 | | 2 | Liz | 2000 | 10 | | 3 | Sam | 1000 | 30 | | 4 | Tom | 3090 | 20 | | 5 | Zeb | NULL | 20 | | 10 | Foo | 4721 | NULL | | 20 | Foo | 4731 | NULL | | 30 | Foo | 4741 | NULL | +-----+-------+--------+------+ Om man vill höja ALLAS lön med 10 procent, utelämnar man where-villkoret. Då uppdateras alla rader: update worker set salary = salary * 1.10; Avskeda alla med högre lön än 3000, eller som har NULL i lönekolumnen. Kom ihåg att "NULL = NULL" är falskt, precis som "NULL <> NULL", så "salary = null" skulle inte fungera. Vi använder "is null". delete from worker where salary > 3000 or salary is null; select * from worker; +-----+-------+--------+------+ | wno | wname | salary | at | +-----+-------+--------+------+ | 1 | Bob | 1133 | 10 | | 2 | Liz | 2200 | 10 | | 3 | Sam | 1100 | 30 | +-----+-------+--------+------+ Om man vill ta bort ALLA rader, utelämnar man where-villkoret. Då raderas alla rader: delete from worker; MIMER/DB error -10106 in function EXECUTE UPDATE or DELETE operation invalid because the referenced table SYSADM.WORKER does not satisfy a referential constraint Ja just ja. works-tabellen refererar till en del arbetare, så dem får man inte ta bort. Mimer upprätthåller referensintegriteten. 9. Join med sig själv --------------------- Vilka arbetare har lika lön som någon annan? select w1.wname, w1.salary from worker as w1, worker as w2 where w1.salary = w2.salary; +-------+--------+ | wname | salary | +-------+--------+ | Bob | 1000 | | Sam | 1000 | | Liz | 2000 | | Bob | 1000 | | Sam | 1000 | +-------+--------+ Alla, varav en del två gånger? Jo, w1 och w2 kan "peka" på samma rad: select w1.wname, w1.salary, w2.wname, w2.salary from worker as w1, worker as w2 where w1.salary = w2.salary; +-------+--------+-------+--------+ | wname | salary | wname | salary | +-------+--------+-------+--------+ | Bob | 1000 | Bob | 1000 | | Sam | 1000 | Bob | 1000 | | Liz | 2000 | Liz | 2000 | | Bob | 1000 | Sam | 1000 | | Sam | 1000 | Sam | 1000 | +-------+--------+-------+--------+ Bättre: select w1.wname, w1.salary from worker as w1, worker as w2 where w1.salary = w2.salary and w1.wno <> w2.wno; +-------+--------+ | wname | salary | +-------+--------+ | Sam | 1000 | | Bob | 1000 | +-------+--------+ 10. Recursive closure --------------------- Se avsnittet 14.5 "Transitivt hölje med en lagrad procedur" i boken 11. Relationsalgebra -------------------- Se kapitel 10 "Relationsalgebra" i boken, eller http://www.databasteknik.se/webbkursen/relalg-lecture/index.html 12. Ännu mer SQL ---------------- EXISTS NOT EXISTS ANY SOME ALL scope rules for names in nested SQL queries correlated nested queries UNIQUE Tas upp på andra ställen i kursen: GRANT, REVOKE COMMIT, ROLLBACK