Download   Report
  1. No category

Vaja 3

MySQL
FMF delavnica
VAJE
Vid Podpečan
Inštitut Jožef Stefan
Vaja 3
SQL poizvedbe
1. Izvajanje SQL stavkov iz tekstovnih datotek
● predpogoj:
○
v MySQL smo prijavljeni kot uporabnik “fmf”
○
narejeno imamo bazo fmf_imepriimek, do katere imamo vse pravice in smo nanjo
priklopljeni (ukaz use imebaze)
● v direktoriju /home/student sta dve datoteki
○
○
bbc.sql
-- BBC podatki o državah
movies.sql -- IMDB podatki o filmih
bbc.sql
movies.sql
DROP TABLE IF EXISTS bbc;
DROP TABLE IF EXISTS actor, movie, casting CASCADE;
CREATE TABLE bbc(
name VARCHAR(50) NOT NULL
,region VARCHAR(60)
,area DECIMAL(10)
,population DECIMAL(11)
,gdp DECIMAL(14)
,PRIMARY KEY (name)
);
CREATE TABLE actor(
id INTEGER NOT NULL
, name VARCHAR(50)
, PRIMARY KEY (id)
);
insert into bbc values ('Afghanistan','South Asia',652225,26000000,NULL);
insert into bbc values ('Albania','Europe',28728,3200000,6656000000);
insert into bbc values ('Algeria','Middle East',2400000,32900000,75012000000);
….
….
CREATE TABLE movie(
id INTEGER NOT NULL
, title VARCHAR(100)
, year DECIMAL(4)
, score FLOAT
, votes INTEGER
, director INTEGER
, PRIMARY KEY (id)
, FOREIGN KEY (director) REFERENCES actor(id)
);
….
….
….
2. Izvajanje SQL stavkov iz tekstovnih datotek
● v mysql ukazni vrstici izvedite ukaza
○
○
source ~/bbc.sql
source ~/movies.sql
● ta ukaza izvedeta vse SQL stavke, ki se nahajajo v datotekah
● uporabite ukaz show tables;
○
če je šlo vse v redu: 4 nove tabele
■
■
države: tabela bbc
tri povezane tabele: actor, movie, casting
mysql> show tables;
+--------------------+
| Tables_in_fmf_vidp |
+--------------------+
| actor
|
| bbc
|
| casting
|
| movie
|
| osebe
|
| racuni
|
+--------------------+
● uporabite ukaz DESCRIBE imeTabele za vse tabele
○
oglejte si, kakšni stolpci sestavljajo tabele, kaj so primarni in tuji ključi
Grafični vmesnik phpMyAdmin
● za lažje delo z SQL poizvedbami lahko uporabite grafični vmesnik
○
○
http://vihar.ijs.si/phpmyadmin/
podatki za prijavo so enaki kot za ukazno vrstico
● na levi izberite svojo bazo, na desni pa zavihek SQL
3. Tabela bbc, naloga 1
mysql> describe bbc;
+------------+---------------+------+-----+---------+-------+
| Field
| Type
| Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| name
| varchar(50)
| NO
| PRI | NULL
|
|
| region
| varchar(60)
| YES | MUL | NULL
|
|
| area
| decimal(10,0) | YES |
| NULL
|
|
| population | decimal(11,0) | YES |
| NULL
|
|
| gdp
| decimal(14,0) | YES |
| NULL
|
|
+------------+---------------+------+-----+---------+-------+
● izpišite imena držav, ki se začnejo na črko A
○
nasvet: osnovna oblika stavka select...from...where ter operator LIKE
● izpišite jih še v abecednem in obratnem abecednem redu glede na
ime
○
nasvet: order by...
4. Tabela bbc, naloga 2
mysql> describe bbc;
+------------+---------------+------+-----+---------+-------+
| Field
| Type
| Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| name
| varchar(50)
| NO
| PRI | NULL
|
|
| region
| varchar(60)
| YES | MUL | NULL
|
|
| area
| decimal(10,0) | YES |
| NULL
|
|
| population | decimal(11,0) | YES |
| NULL
|
|
| gdp
| decimal(14,0) | YES |
| NULL
|
|
+------------+---------------+------+-----+---------+-------+
● izpišite imena držav, ki imajo populacijo večjo kot 108 , gdp pa večji
kot 1012
○
nasvet: osnovna oblika stavka select...from...where ter dva pogoja, povezana z AND
5. Tabela bbc, naloga 3
mysql> describe bbc;
+------------+---------------+------+-----+---------+-------+
| Field
| Type
| Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| name
| varchar(50)
| NO
| PRI | NULL
|
|
| region
| varchar(60)
| YES | MUL | NULL
|
|
| area
| decimal(10,0) | YES |
| NULL
|
|
| population | decimal(11,0) | YES |
| NULL
|
|
| gdp
| decimal(14,0) | YES |
| NULL
|
|
+------------+---------------+------+-----+---------+-------+
● izpišite imena vseh regij, urejena po abecedi (brez ponovitev!)
○
nasvet: osnovna oblika stavka select...from, uporaba distinct ter order by
6. Tabela bbc, naloga 4
mysql> describe bbc;
+------------+---------------+------+-----+---------+-------+
| Field
| Type
| Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| name
| varchar(50)
| NO
| PRI | NULL
|
|
| region
| varchar(60)
| YES | MUL | NULL
|
|
| area
| decimal(10,0) | YES |
| NULL
|
|
| population | decimal(11,0) | YES |
| NULL
|
|
| gdp
| decimal(14,0) | YES |
| NULL
|
|
+------------+---------------+------+-----+---------+-------+
● izpišite, koliko držav ima vsak kontinent
○
nasvet: osnovna oblika stavka select...from, uporaba group by ter count
7. Tabele actor, movie, casting, naloga 1
mysql> describe actor;
+-------+-------------+-----+
| Field | Type
| Key |
+-------+-------------+-----+
| id
| int(11)
| PRI |
| name | varchar(50) | MUL |
+-------+-------------+-----+
mysql> describe movie;
+----------+--------------+-----+
| Field
| Type
| Key |
+----------+--------------+-----+
| id
| int(11)
| PRI |
| title
| varchar(100) | MUL |
| year
| decimal(4,0) |
|
| score
| float
|
|
| votes
| int(11)
|
|
| director | int(11)
| MUL |
+----------+--------------+-----+
mysql> describe casting;
+---------+---------+-----+
| Field
| Type
| Key |
+---------+---------+-----+
| movieid | int(11) | PRI |
| actorid | int(11) | PRI |
| ord
| int(11) | MUL |
+---------+---------+-----+
● združite tabele actor, movie in casting ter
○
izpišite imena vseh filmov, v katerih je igrala Julia Roberts
8. Tabele actor, movie, casting, naloga 2
mysql> describe actor;
+-------+-------------+-----+
| Field | Type
| Key |
+-------+-------------+-----+
| id
| int(11)
| PRI |
| name | varchar(50) | MUL |
+-------+-------------+-----+
mysql> describe movie;
+----------+--------------+-----+
| Field
| Type
| Key |
+----------+--------------+-----+
| id
| int(11)
| PRI |
| title
| varchar(100) | MUL |
| year
| decimal(4,0) |
|
| score
| float
|
|
| votes
| int(11)
|
|
| director | int(11)
| MUL |
+----------+--------------+-----+
mysql> describe casting;
+---------+---------+-----+
| Field
| Type
| Key |
+---------+---------+-----+
| movieid | int(11) | PRI |
| actorid | int(11) | PRI |
| ord
| int(11) | MUL |
+---------+---------+-----+
● združite tabele actor, movie in casting ter
○ izpišite število filmov po letu 1998, v katerih je imela Julia Roberts glavno vlogo
○
nasveti
■
■
glavno vlogo pove stolpec casting.ord, ki mora imeti vrednost 1
uporabite count
9. Tabele actor, movie, casting, naloga 3
mysql> describe actor;
+-------+-------------+-----+
| Field | Type
| Key |
+-------+-------------+-----+
| id
| int(11)
| PRI |
| name | varchar(50) | MUL |
+-------+-------------+-----+
mysql> describe movie;
+----------+--------------+-----+
| Field
| Type
| Key |
+----------+--------------+-----+
| id
| int(11)
| PRI |
| title
| varchar(100) | MUL |
| year
| decimal(4,0) |
|
| score
| float
|
|
| votes
| int(11)
|
|
| director | int(11)
| MUL |
+----------+--------------+-----+
mysql> describe casting;
+---------+---------+-----+
| Field
| Type
| Key |
+---------+---------+-----+
| movieid | int(11) | PRI |
| actorid | int(11) | PRI |
| ord
| int(11) | MUL |
+---------+---------+-----+
● združite tabele actor, movie in casting ter
○
○
izpišite naslove filmov (urejene po abecedi), kjer je bil glavni igralec hkrati tudi režiser
nasvet: movie.director je tuji ključ in kaže na actor.id

abcdocz.com

© Copyright 2024