Laboratorium 4 (Administracja MySQL), 07.05.2018
Marek Szymański, Jakub Legutko

5. Przegladanie bazy danych Serwer C:\Users\lab\Desktop\sql-tools\server\bin
mysql -u root -p --port 6033

b) USE uczelnia;
	Database changed
c) DESCRIBE przydzialy;
	3 rows in set (0.00 sec)
// opisuje tabele przydzialy - pokazuje jakie typy przechowuje, klucz, dane, wartosci domyslne
d) select * from przydzialy;
	243 rows in set (0.00 sec)
//wyswietla zawartosc tabeli

6.
a) mysqladmin -u root -p --port 6033 password root_sql;
//komende nalezy wykonac na wylogowanym koncie

b) mysql -u root -p --port 6033
//W Enter password:root_sql

c)
	i.) show databases;

mysql> +--------------------+
    -> | Database           |
    -> +--------------------+
    -> | information_schema |
    -> | mysql              |
    -> | uczelnia           |
    -> | university         |
    -> +--------------------+
    -> 4 rows in set (0.00 sec)
//wyswietla dostepne bazy na serwerze

	ii.) show processlist;
//pokazuje liste procesow
+----+------+-----------------+------+---------+------+-------+------------------+
| Id | User | Host            | db   | Command | Time | State | Info             |
+----+------+-----------------+------+---------+------+-------+------------------+
|  3 | root | localhost:54045 | NULL | Query   |    0 | NULL  | show processlist |
+----+------+-----------------+------+---------+------+-------+------------------+
1 row in set (0.00 sec)

	iii.) use labbd;
ERROR 1049 (42000): Unknown database 'labbd'
//zwrocono blad, gdyz nie ma takiej bazy w serwerze (patrz punkt [6c i.])

	iv.) show create table pracownicy;
(W PRZYPADKU LABBD)
ERROR 1046 (3D000): No database selected
//zwrocono blad, gdyz nie wybrano bazy na ktorej ma zostac utworzona tabela pracownicy

d) opisany przy wywolywaniu punktow

e) CREATE DATABASE labbd;
//Query OK, 1 row affected (0.00 sec), utworzono baze danych labbd

 mysql> USE labbd;
//Database changed

mysql> \. C:\Users\lab\Desktop\sql-tools\server\bin\labbd.sql
//utworzono obiekty za pomoca pliku labbd.sql

f) show tables from labbd;
+------------------+
| Tables_in_labbd  |
+------------------+
| funkcje          |
| instytuty        |
| kierunki         |
| oceny            |
| pracownicy       |
| przedmioty       |
| przydzialy       |
| rozklady         |
| sale             |
| studenci         |
| tematy           |
| typy_przedmiotow |
| wyplaty          |
| zespoly          |
+------------------+
14 rows in set (0.00 sec)
//wyswietlono liste tabel w bazie labbd

show columns from funkcje;
+---------------+----------+------+-----+---------+-------+
| Field         | Type     | Null | Key | Default | Extra |
+---------------+----------+------+-----+---------+-------+
| KOD_FUNKCJI   | char(3)  | NO   |     | NULL    |       |
| NAZWA_FUNKCJI | char(30) | NO   |     | NULL    |       |
+---------------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
//wyswietlono kolumny z tabeli, ich typ, klucz i wartosc domyslna

============================================================================
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
			II. OPERACJE NA BAZIE DANYCH
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
============================================================================
1. Ładowanie danych
a) mysql> CREATE DATABASE Lab_sql;
b) mysql> \. C:\Users\lab\Desktop\sql-tools\lab_mysql_2011\crcasdat.sql
c) mysql> \. C:\Users\lab\Desktop\sql-tools\lab_mysql_2011\casdat_dump.sql
d) Ładowanie danych okreslono za pomoca dwoch dodatkowych zmiennych do przechowywania sekund,
	na podstawie roznicy czasu pomiedzy rozpoczeciem a zakonczeniem ladowania pliku wyznaczono jego, który wynosił 32 sekundy.
e) mysql> select * from casdat into outfile 'casdat.dump';
	Query OK, 76211 rows affected (0.07 sec)
//utworzono plik wyjsciowy casdat.dump w folderze glownym bazy
f) mysql> truncate casdat;
	Query OK, 0 rows affected (0.00 sec)
g) mysql> load data infile 'casdat.dump' into table casdat
Query OK, 76211 rows affected (0.12 sec)
Records: 76211  Deleted: 0  Skipped: 0  Warnings: 0

h) Wszystkie kroki przebiegły prawidłowo, kasowanie rekordów trwa praktycznie natychmiastowo, natomiast ich ładowanie zajmuje już więcej czasu.

2. Typy wyliczeniowe

a)
mysql> use Lab_sql;
Database changed

create table dyzury(
ido int,
dzien ENUM ('poniedzialek','wtorek','sroda','czwartek','piatek','sobota','niedziela'));
//Query OK, 0 rows affected (0.01 sec)

b)
mysql> insert into dyzury values (2,'wtorek');
Query OK, 1 row affected (0.00 sec)

mysql> insert into dyzury values (5,'sroda');
Query OK, 1 row affected (0.00 sec)

mysql> insert into dyzury values (6,'brakdnia');
Query OK, 1 row affected, 1 warning (0.00 sec)

//WYNIK:
mysql> select * from dyzury;
+------+--------+
| ido  | dzien  |
+------+--------+
|    2 | wtorek |
|    5 | sroda  |
|    6 |        |
+------+--------+
3 rows in set (0.00 sec)

c)mysql> insert into dyzury (dzien) values ('wtorek,sroda');
Query OK, 1 row affected, 1 warning (0.00 sec)

d)mysql> insert into dyzury values (5,5);
Query OK, 1 row affected (0.00 sec)

e)//WYNIK:
mysql> select * from dyzury;
+------+--------+
| ido  | dzien  |
+------+--------+
|    2 | wtorek |
|    5 | sroda  |
|    6 |        |
| NULL |        |
|    5 | piatek |
+------+--------+
5 rows in set (0.00 sec)

f) utworzenie tabeli dyzury z kolumna dzien poprzez enum pozwala wprowadzac dzien za pomoca cyfry co widzimy w podpunkcie d, wszystkie komendy wykonano pomyslnie.
Z powodu reprezentacji wyliczeniowej po wykonaniu punktu c, nie dodano dnia (wtorek,środa). Aby dodać tę wartość należałoby kolumnę dzień przedstawiać w postaci binarnej (SET zamiast ENUM)
Co udowadnia, że działanie komend jest zgodne z informacjami zamieszczonymi na stronie z dokumentacją dev.mysql.com

g)
create table dyzury_set(
ido int,
dzien SET ('poniedzialek','wtorek','sroda','czwartek','piatek','sobota','niedziela'));
//Query OK, 0 rows affected (0.04 sec)

h)
mysql> insert into dyzury_set (dzien) values ('wtorek,sroda');
Query OK, 1 row affected (0.00 sec)

mysql> insert into dyzury_set values (5,5);
Query OK, 1 row affected (0.00 sec)

mysql> select * from dyzury_set;
+------+--------------------+
| ido  | dzien              |
+------+--------------------+
| NULL | wtorek,sroda       |
|    5 | poniedzialek,sroda |
+------+--------------------+
2 rows in set (0.00 sec)

i)
mysql> insert into dyzury_set values (13,'poniedzialek,aaa');
Query OK, 1 row affected, 1 warning (0.00 sec)

j)
mysql> select * from dyzury_set;
+------+--------------------+
| ido  | dzien              |
+------+--------------------+
| NULL | wtorek,sroda       |
|    5 | poniedzialek,sroda |
|   13 | poniedzialek       |
+------+--------------------+
3 rows in set (0.00 sec)

k) update dyzury_set set dzien='sobota' where find_in_set('wtorek', dzien);
//Query OK, 1 row affected (0.00 sec)
//Rows matched: 1  Changed: 1  Warnings: 0

PRZED ZMIANĄ:
mysql> select * from dyzury_set;
+------+--------------------+
| ido  | dzien              |
+------+--------------------+
| NULL | wtorek,sroda       |
|    5 | poniedzialek,sroda |
|   13 | poniedzialek       |
+------+--------------------+
3 rows in set (0.00 sec)

PO ZMIANIE:
mysql> select * from dyzury_set;
+------+--------------------+
| ido  | dzien              |
+------+--------------------+
| NULL | sobota             |
|    5 | poniedzialek,sroda |
|   13 | poniedzialek       |
+------+--------------------+
3 rows in set (0.00 sec)

i) Wykonanie komendy insert into dyzury_set values (5,5), spowodowało wpisanie do kolumny dni poniedzialek,sroda - co jest poprawne, gdyż teraz dni są numerowane binarnie więc 5 binarnie to (101) - pierwszy z prawej to poniedziałek (1), drugi wtorek(0) [dlatego nie wpisany], trzeci środa(1), i pozostałe dni również mają wartość 0 dlatego nie były wpisane.
Podpunkt c, został w tym przypadku wykonany i dodany do tabeli poprawnie ze względu na reprezentację kolumny dzien w postaci binarnej (teraz SET zamiast ENUM)
Co udowadnia, że działanie komend jest zgodne z informacjami zamieszczonymi na stronie z dokumentacją dev.mysql.com
============================================================================
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
			III. Silniki (storage engine)
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
============================================================================
1.
mysql> use labbd;
mysql> SHOW TABLE STATUS WHERE NAME='dyzury';
//1 row in set (0.00 sec)
//zwrocono wykorzystywany silnik: MyISAM

2.
Pliki tworzone dla kazdej tablicy:
- *.frm
- *.MYD
- *.MYI

3.
mysql> ALTER TABLE studenci ENGINE=InnoDB;
Query OK, 66 rows affected (0.14 sec)
Records: 66  Duplicates: 0  Warnings: 0

4. Pliki zmieniły się, istnieje teraz tylko jeden plik: studenci.frm, nie ma plików o rozszerzeniu MYD i MYI

5.
============================
	DLA MyISAM
============================
// zmienilo plec

============================
	DLA InnoDB
============================
// zmienilo plec

6. //W obu jest teraz plec K

7.,8. Polecenie rollback zadziałało tylko dla InnoDB i ustawiło płeć z powrotem na M

9. rollback;
//brak dodatkowych zmian

10.
mysql> ALTER TABLE studenci ENGINE=Memory;
Query OK, 66 rows affected (0.07 sec)
Records: 66  Duplicates: 0  Warnings: 0

11. Silnik Memory nie obsługuje transakcji, sprawdziliśmy to poprzez wpisanie wcześniejszych operacji dla poprzednich silników, rollback nie działa dla tego silnika.

12.,13. Po restartcie tabela wyczyściła się - brak rekordu

============================================================================
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
			IV. Optymalizacja zapytań
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
============================================================================
1.
//kasowanie rekordow
mysql> truncate casdat;
Query OK, 0 rows affected (0.02 sec)

//zaladowanie danych
mysql> load data infile 'casdat_z1.dump' into table casdat;
Query OK, 4821 rows affected (0.02 sec)
Records: 4821  Deleted: 0  Skipped: 0  Warnings: 0

Rozwiązanie 1:
//WYNIK: 115 rows in set (1.90 sec)

Rozwiązanie 2:
//WYNIK: 101 rows in set (0.05 sec)

3. Zapytanie 2 wykonuje się znacznie szybciej, powodem jest brak zagniezdzonego polecenia select, dodatkowo rozwiazanie drugie wykorzystuje polecenie join.

4. create index cas2 on casdat(cas)
Rozwiązanie 1:
//WYNIK: 115 rows in set (0.02 sec)

Rozwiązanie 2:
//WYNIK: 101 rows in set (0.06 sec)

5. Czas wykonania poprzez rozwiązanie 1 znacznie się skrócił, jest szybsze od rozwiązania 2. Rozwiązanie 2 wydłużyło się nieznacznie bo o 0.01 niż poprzednio, możemy więc stwierdzić, że rozwiązanie 2 wykonywało się podobnie w obu przypadkach. Natomiast Rozwiązanie 1 zadziałało znacznie szybciej po założeniu indeksu.

6. Rozwiązanie 2 zwróciło mniej oddczynników niż pierwsze - jak widzimy rozwiązanie to wykorzystuje złączenie co powoduje, że część wyników zostaje również odrzucona po dokonaniu tego złączenia.

7. Liczba pomijanych odczynników jest równa 14. Do uzyskania identycznych odczynników należy nastepująco zmodyfikować rozwiązanie:
select c1.cas from
casdat c1 left join casdat c2 on c1.cas=c2.cas and c2.type='rent'
where c1.type='sent'
group by c1.cas
having count(distinct c1.data)>count(distinct c2.data);

8. z indeksem
//WYNIK: 115 rows in set (0.01 sec)

bez indeksu
//WYNIK: 115 rows in set (11.26 sec)