(Microsoft PowerPoint - MySQL

Wydział Informatyki
Politechnika Białostocka
Plan wykładu
Zaawansowane bazy danych i
hurtownie danych
1. MySQL
2. Powtórzenie SQL
studia zaoczne II stopnia, sem. I
WYKŁAD 2:
MySQL: podstawowe obiekty
Powtórzenie SQL.
Agnieszka Oniśko, Małgorzata Krętowska
PHP a MySQL, 1/41
PHP a MySQL, 2/41
SQL (Structured Query Language)
Użytkownicy bazy danych
Administratorzy baz danych
• Definiowanie danych (DDL:Data Definition Language)
• Definiowanie zapytań (DQL: Data Query Language)
• Modyfikowanie danych (DML: Data Manipulation
Language)
• Sterowanie danymi (DCL: Data Control Language)
Baza danych
Programiści
Użytkownicy końcowi
PHP a MySQL, 3/41
PHP a MySQL, 4/41
MySQL: Tworzenie bazy danych
MySQL: Baza danych
SHOW DATABASES;
CREATE DATABASE | SCHEMA [IF NOT EXISTS]
nazwa_bazy
[[DEFAULT] CHARACTER SET [=] kodowanie]
| [DEFAULT] COLLATE [=] collation_name ;
mysql> SHOW CHARACTER SET LIKE 'latin%';
+---------+-----------------------------+-------------------+--------+
| Charset | Description
| Default collation | Maxlen |
+---------+-----------------------------+-------------------+--------+
| latin1 | cp1252 West European
| latin1_swedish_ci |
1 |
| latin2 | ISO 8859-2 Central European | latin2_general_ci |
1 |
| latin5 | ISO 8859-9 Turkish
| latin5_turkish_ci |
1 |
| latin7 | ISO 8859-13 Baltic
| latin7_general_ci |
1 |
+---------+-----------------------------+-------------------+--------+
+--------------------+
| Database
|
+--------------------+
| information_schema |
| mysql
|
| test
|
+--------------------+
Wynik po instalacji MySQL
USE nazwa_bazy; - ustawia daną bazę jako domyślną dla
późniejszych instrukcji
USE db1;
SELECT COUNT(*) FROM mytable;
# wybiera z db1.mytable
USE db2;
SELECT COUNT(*) FROM mytable;
# wybiera z db2.mytable
PHP a MySQL, 5/41
MySQL: Modyfikacja/usuwanie bazy danych
PHP a MySQL, 6/41
MySQL: Polecenia SHOW/DESCRIBE
DESCRIBE nazwa_tabeli [nazwa_kolumny | ‘wartość’];
DESCRIBE user ‘%priv’;
ALTER DATABASE nazwa_bazy
[[DEFAULT] CHARACTER SET [=] kodowanie]
| [DEFAULT] COLLATE [=] collation_name ;
SHOW DATABASES;
SHOW TABLES [FROM nazwa_bazy] [LIKE ‘wartość’];
SHOW GRANTS FOR użytkownik;
DROP DATABASE [IF EXISTS] nazwa_bazy;
SHOW TABLES FROM mysql LIKE ‘my%’;
SHOW COLUMNS FROM user LIKE ‘priv%’;
SHOW INDEX FROM user;
PHP a MySQL, 7/41
PHP a MySQL, 8/41
MySQL: Tworzenie użytkowników bazy
MySQL: Zmiana hasła użytkownika bazy
CREATE USER użytkownik
[IDENTIFIED BY [PASSWORD] ‘hasło']
[, użytkownik
[IDENTIFIED BY [PASSWORD] ‘hasło']] ...
SET PASSWORD [FOR użytkownik] = {
PASSWORD(‘hasło') | OLD_PASSWORD(‘hasło') |
‘zaszyfrowane_hasło' }
SET PASSWORD FOR nowy = PASSWORD(‘nowe_haslo');
CREATE USER nowy_użytkownik
IDENTIFIED BY ‘tutaj_haslo‘;
SET PASSWORD FOR 'bob'@'%.loc.gov' = PASSWORD('newpass');
Jest to równoznaczne z:
UPDATE mysql.user SET Password=PASSWORD('newpass')
WHERE User='bob' AND Host='%.loc.gov';
FLUSH PRIVILEGES;
PHP a MySQL, 9/41
MySQL: Instalacja
Utworzenie bazy
systemowej mysql
MySQL
Utworzenie bazy
testowej test
PHP a MySQL, 10/41
MySQL: information_schema
Utworzenie bazy
systemowej mysql
mysql
test
MySQL
Utworzenie użytkownika
systemowego root
Utworzenie bazy
testowej test
mysql
test
Utworzenie użytkownika
systemowego root
PHP a MySQL, 11/41
PHP a MySQL, 12/41
Bazy mysql i test
Wybrane obiekty bazy mysql
•
Baza systemowa mysql
•
•
Zawiera 23 tabele systemowe
Tabele systemowe przechowują informacje na temat:
– użytkowników bazy
– uprawnień przydzielonych użytkownikom bazy
Tabele kontrolujące dostęp do bazy
— user
— db
— host*
— columns_priv*
— tables_priv*
Baza testowa test
•
Nie zawiera żadnych tabel
PHP a MySQL, 13/41
MySQL: Rodzaje uprawnień
ALTER
ALTER ROUTINE
CREATE
CREATE ROUTINE
CREATE TEMPORARY TABLES
CREATE USER
CREATE VIEW
DELETE
DROP
EXECUTE
FILE
GRANT OPTION
INDEX
INSERT
LOCK TABLES
PHP a MySQL, 14/41
Rodzaje kolumn
PROCESS
RELOAD
SELECT
SHOW DATABASES
SHOW VIEW
SHUTDOWN
UPDATE
select_priv
insert_priv
update_priv
delete_priv
create_priv
alter_priv
drop_priv
index_priv
table_priv
column_priv
ssl_type
ssl_cipher
PHP a MySQL, 15/41
shutdown_priv
process_priv
file_priv
grant_priv
show_db_priv
reload_priv
create_tmp_tables_priv
lock_tables_priv
execute_priv
max_questions
max_updates
max_connections
max_user_connections
PHP a MySQL, 16/41
Tabela user: Kolumny
host
user
password
select_priv
insert_priv
update_priv
delete_priv
........
Tabela user: Kolumny
Kolumny określają, kto ma dostęp do
serwera bazy i z jakiego hosta
(Klucz główny (host, user))
ssl_type
ssl_cipher
.....
Kolumny określają rodzaj połączenia
max_questions
Kolumny określają uprawnienia
max_updates
dotyczące połącznia z bazą
max_connections
max_user_connections
Uprawnienia
PHP a MySQL, 17/41
PHP a MySQL, 18/41
Tabela db: Kolumny
Tabela db: Przykład
host
db
user
select_priv
insert_priv
update_priv
delete_priv
........
Kolumny określają, kto ma dostęp do
konkretnej bazy i z jakiego hosta
Klucz główny (host, db, user)
mysql> select user, password from user;
+------+-------------------------------------------+
| user | password
|
+------+-------------------------------------------+
| root | *70EADDEE29B2EDFA77EA589C9ED2D866FD9E79D5 |
+------+-------------------------------------------+
Uprawnienia
PHP a MySQL, 19/41
PHP a MySQL, 20/41
Tabela host: Kolumny
Tabela tables_priv: Kolumny
host
db
host
db
user
table_name
column_name
grantor
table_priv
column_priv
select_priv
insert_priv
update_priv
delete_priv
........
Kolumny określają, z jakiego hosta do
jakiej bazy mamy dostęp
Klucz główny (host, db)
Uprawnienia
Kolumny określają, kto ma
dostęp do tabeli
Klucz główny (host, db,
user, table_name)
SELECT host, db, user, table_name, table_priv, column_priv
FROM tables_priv;
PHP a MySQL, 21/41
MySQL: Nadawanie uprawnień
Tabela columns_priv: Kolumny
host
db
user
table_name
column_name
column_priv
PHP a MySQL, 22/41
Kolumny określają kto ma dostęp do
kolumn tabeli
Klucz główny (host, db, user, table_name,
column_name)
GRANT typ_uprawnienia [(lista_kolumn)] [, typ_uprawnienia
[(lista_kolumn)]] ...
ON [typ_obiektu] poziom_uprawnienia
TO użytkownik [IDENTIFIED BY [PASSWORD] ‘hasło']
[, użytkownik [IDENTIFIED BY [PASSWORD] 'hasło']] ...
[REQUIRE {NONE | ssl_option [[AND] ssl_option] ...}]
[WITH z_opcją [z_opcją] ...] ;
SELECT host, db, user, table_name, column_name, column_priv
FROM columns_priv;
PHP a MySQL, 23/41
PHP a MySQL, 24/41
MySQL: Nadawanie uprawnień
MySQL: Nadawanie uprawnień, przykład
typ_obiektu: TABLE | FUNCTION | PROCEDURE
poziom_uprawnienia: * | *.* | db_nazwa.* |
db_nazwa.nazwa_tabeli | nazwa_tabeli |
db_nazwa.nazwa_procedury
z_opcją:
GRANT OPTION |
MAX_QUERIES_PER_HOUR liczba |
MAX_UPDATES_PER_HOUR liczba |
MAX_CONNECTIONS_PER_HOUR liczba |
MAX_USER_CONNECTIONS liczba
GRANT SELECT, UPDATE, INSERT
ON test.tabela
TO ‘kowalski’@’localhost’;
GRANT SELECT, UPDATE (k1, k2)
ON test.tabela
TO ‘kowalski’@’localhost’;
ssl_option: SSL | X509 | CIPHER 'cipher' | ISSUER 'issuer' |
SUBJECT 'subject‘
GRANT SELECT, UPDATE (k1, k2)
ON test.tabela
TO ‘kowalski’@’localhost’
REQUIRE ssl
WITH MAX_UPDATES_PER_HOUR 20;
PHP a MySQL, 25/41
MySQL: Sprawdzanie uprawnień
PHP a MySQL, 26/41
MySQL: Sprawdzanie uprawnień
SELECT host, user, select_priv, update_priv
FROM user
WHERE user =‘kowalski’;
SHOW GRANTS
FOR ‘kowalski’@’localhost’;
SELECT host, db, user, table_name, table_priv, column_priv
FROM tables_priv
WHERE user =‘kowalski’;
SELECT host, db, user, table_name, column_name, column_priv
FROM columns_priv
WHERE user =‘kowalski’;
SELECT host, user, select_priv, insert_priv, update_priv,
delete_priv, create_priv, drop_priv
FROM user
WHERE user = ‘kowalski’;
PHP a MySQL, 27/41
PHP a MySQL, 28/41
MySQL: Odbieranie uprawnień
MySQL: Aktualizacja uprawnień
REVOKE typ_uprawinienia [(lista_kolumn)] [,
typ_uprawnienia [(lista_kolumn)]] ...
ON [typ_obiektu] {nazwa_tabeli | * | *.* | nazwa_bazy.*}
FROM użytkownik [, użytkownik] ...
FLUSH PRIVILEGES
REVOKE ALL PRIVILEGES, GRANT OPTION
FROM użytkownik [, użytkownik] ...
REVOKE UPDATE
ON test.tabela
FROM ‘kowalski’@’localhost’;
PHP a MySQL, 29/41
Baza ćwiczeniowa
PHP a MySQL, 30/41
Zapytania proste
SELECT [ALL | DISTINCT] wyrażenie [[AS] alias], ...
FROM nazwa_tabeli, ...
[WHERE warunek]
[ORDER BY wyrażenie ASC|DESC], ...];
PHP a MySQL, 31/41
31
PHP a MySQL, 32/41
Zapytania proste
Zapytania proste
[WHERE warunek]
SELECT [ALL | DISTINCT] wyrażenie [[AS] alias], ...
FROM nazwa_tabeli, ...
ALL- pokazuje wszystkie wiersze (domyślny)
DISTINCT - eliminuje powtarzające się wiersze
wyrażenie - nazwa kolumny lub wyrażenie zawierające nazwy kolumn,
zamiast wyrażenia może wystąpić znak '*’ oznaczający wszystkie
kolumny
alias - nazwa nadana wyrażeniu na liście SELECT, może mieć postać
identyfikatora prostego (napis złożony z liter, cyfr i znaków
podkreślenia) lub złożonego (dowolny napis ograniczony podwójnymi
cudzysłowami, np. zawierający spacje),
Operatory logiczne:
=; <; >; >=; <=
Operatory SQL:
BETWEEN ... AND ... - między dwiema wartościami (włącznie z nimi)
IN (lista) - zgodnie z jednym elementów listy
LIKE - zgodnie z zadanym wzorcem (interpretacja znaków typu "wildcard":
‘%’ zastępuje ciąg znaków, ‘_’ zastępuje jeden znak)
IS NULL - jest wartością NULL
Operator negacji:
NOT (NOT nazwa_kolumny = ...; NOT BETWEEN; NOT IN; NOT LIKE; IS
NOT NULL)
Kryteria złożone:
AND (i)
OR (lub)
PHP a MySQL, 33/41
Przykład
Wypisać pracowników pracujących na stanowisku
sprzedawcy lub informatyka.
select nazwisko, stanowisko from pracownik where
stanowisko in (‘SPRZEDAWCA’, ‘INFORMATYK’);
NAZWISKO
STANOWISKO
------------- ----------------Nazwisko1 SPRZEDAWCA
Nazwisko2 SPRZEDAWCA
Nazwisko11 INFORMATYK
Nazwisko12 INFORMATYK
Nazwisko14 SPRZEDAWCA
PHP a MySQL, 34/41
Złączenia tabel
SELECT [ALL | DISTINCT] wyrażenie [[AS] alias], ...
FROM tabela_1, tabela_2, ..., tabela_n
WHERE warunki_łączący_tabele
minimalna liczba łączących warunków = liczba tabel -1
PHP a MySQL, 35/41
PHP a MySQL, 36/41
Przykład
Przykład
Wypisać nazwiska i nazwy departamentów pracowników.
SELECT nazwisko, nazwa,
p.nr_departamentu as nr_z_pr,
d.nr_departamentu as nr_z_dep
FROM pracownik p, departament d;
NAZWISKO
NAZWA
NR_Z_DEP
SELECT nazwisko, nazwa, p.nr_departamentu
as nr_z_pr, d.nr_departamentu as nr_z_dep
FROM pracownik p, departament d
WHERE
p.nr_departamentu=d.nr_departamentu;
NR_Z_PR
NAZWISKO
------------- --------------- ---------- ----------
NAZWA
NR_Z_PR NR_Z_DEP
------------- --------------- ---------- ----------
Nazwisko1
Departament 1
1
1
Nazwisko1
Departament 1
1
1
Nazwisko2
Departament 1
1
1
Nazwisko9
Departament 1
1
1
Nazwisko3
Departament 1
2
1
Nazwisko8
Departament 1
1
1
Nazwisko4
Departament 1
2
1
Nazwisko14
Departament 1
1
1
Nazwisko5
Departament 1
3
1
Nazwisko2
Departament 1
1
1
Nazwisko6
Departament 1
3
1
Nazwisko13
Departament 2
2
2
Nazwisko7
Departament 1
3
1
Nazwisko4
Departament 2
2
2
Nazwisko3
Departament 2
2
2
Nazwisko11
Departament 2
2
2
Wypisać poziomy płac poszczególnych pracowników wraz z ich
zarobkami
SELECT nazwisko, pensja, nr_przedzialu
FROM pracownik, poziom_zarobkow
WHERE pensja between dolna_granica and gorna_granica;
Wynik działania zapytania:
Tabela poziom_zarobkow:
NAZWISKO
PENSJA NR_PRZEDZIALU
------------- ---------- ------------Nazwisko2
900
1
Nazwisko1
1000
1
Nazwisko14
1000
1
Nazwisko8
1200
1
Nazwisko9
1400
2
Nazwisko3
1500
3
Nazwisko4
2000
3
Nazwisko13
2000
3
Nazwisko5
3000
4
Nazwisko7
3500
5
Nazwisko11
3500
5
NR_PRZEDZIALU DOLNA_GRANICA
GORNA_GRANICA
------------- ------------- ------------1
700
1200
2
1201
1400
3
1401
2000
4
2001
3000
5
3001
9999
PHP a MySQL, 37/41
Funkcje grupowe
Przykład
• Funkcje grupowe są przeznaczone do działania na grupach wierszy.
Wynikiem działania funkcji grupowej jest pojedyncza wartość dla całej
grupy, a nie jedna wartość dla każdego wiersza.
• Przykłady funkcji:
–
–
–
–
–
–
–
–
PHP a MySQL, 38/41
AVG ([DISTINCT] wyrażenie)
COUNT ([DISTINCT] wyrażenie)
COUNT(*)
MAX ([DISTINCT] wyrażenie)
MIN ([DISTINCT] wyrażenie)
STDDEV ([DISTINCT] wyrażenie)
SUM ([DISTINCT] wyrażenie)
VARIANCE ([DISTINCT] wyrażenie)
• Wyznacz średni zarobek w firmie
– select avg(pensja) from pracownik;
• Ilu pracowników jest zatrudnionych w departamencie
numer 20?
– Select count(*) from pracownik where nr_departamentu=20;
• Wszystkie funkcje grupowe, z wyjątkiem count(*) ignorują
NULL.
PHP a MySQL, 39/41
PHP a MySQL, 40/41
Zapytania grupujące
Zasady wykonania zapytania grupującego
• Rozważ wszystkie kombinacje wierszy tabel
występujących w klauzuli FROM
• Do każdego wiersza zastosuj warunek WHERE
• Podziel wiersze na grupy
• Do każdej grupy zastosuj warunek w klauzuli
HAVING
• Dla każdego wiersza reprezenującego grupę
oblicz wartości wyrażeń po SELECT
SELECT lista pól
FROM tabele
WHERE warunki przed grupowaniem
GROUP BY pola grupujące
HAVING warunki po grupowaniu
PHP a MySQL, 41/41
Zapytania grupujące - ograniczenia
• Na liście wyboru polecenia SELECT używającego
grupowania wolno umieszczać tylko te kolumny,
które są przedmiotem działania klauzuli GROUP
BY chyba, że występują one wewnątrz funkcji
grupującej.
• Każda kolumna lub wyrażenie występujące na
liście SELECT, nie objęte funkcją grupową musi
być przedmiotem grupowania klauzulą GROUP
BY.
PHP a MySQL, 43/41
PHP a MySQL, 42/41