Kurs MySQL

Prace z MySQL rozpoczynamy od instalacji serwera MySql, możemy go pobrać ze strony mysql.com lub w linux sudo apt-get install mysql-server. Po instalacji należy wprowadzić nazwę użytkownika oraz hasło. W linux serwer uruchamiamy przy użyciu polecenia /etc/init.d/mysql start logujemy się wpisując mysql -p -u nazwa_usera. W Windows możemy usługa startuje zaraz po instalacji. By uruchomić klienta w pisujemy w konsoli dla Windowsa Wchodzimy do lokalizacji z mysql i wpisujemy mysql – p – u root i podajemy hasło dla linuxa w konsoli wpisujemy mysql – p – u root i podajemy hasło w obu systemach. (Oczywiście możemy użyć środowiska graficznego polecam Navicat for MySQL)

Pliki do pobrania do ćwiczeń dane.txt oraz sql.sql

Wpisujemy pierwszą komende:

mysql> create database szkolnaDB;

Wynikiem będzie:
Query OK, 1 row affected (0.00 sec)

W ten sposób utworzyliśmy bazę danych w której będziemy mogli tworzyć tabele.

By wyświetlić bazy istniejące na naszym serwerze wpisujemy polecenie slect datadase();

mysql> select database();
+------------+
| database() |
+------------+
| NULL |
+------------+
1 row in set (0.00 sec)

Tworzenie tabeli w MySQL (CREATE TABLE MySQL).

Tabele w MySQL tworzymy przy użyciu polecenia CREATE TABLE nazwa (kolumny typ danych constraint). Pamiętać należy, żę kolumny w obrębie tabeli muszą mieć uniklane nazwy oraz zdefiniowany typ danych.
Przed wykonaniem kodu należy przełączyć się na bazę szkoleniową poleceniem


Use szkolnaDB;


Przykład tworzenie tabeli MySql:

create table dane
(
imie varchar(15) not null,
nazwisko varchar(20) not null,
email varchar(50) null,
ulica varchar(50) null,
miasto varchar(50) null,
data_ur date null,
aktywny int default 1
) ;

Atrybuty kolumn w tabeli MySQL:
NOT NULL - powoduje, że wartość w danym polu nie może być null
AUTO_INCREMENT - auto numerowanie w kolumnie, kążdy rekord automatycznie podczas insertu odtrzymuje kolejny numer.
Uwaga: ten atrybut może być użyty tylko z zawężeniem PRIMARY KEY
PRIMARY KEY - powoduje, że dane w kolumnie nie mogą sie powtarzać, służy do identyfikacji rekordu.
FOREIGN KEY - odwołanie do klucza głównego z innej tabeli.
UNIQUE - powoduje, że dane w kolumnie nie mogą sie powtarzać.
DEFAULT - domyślna wartość dla pola, w przypadku nie podania wartości dla kolumny w rekordzie zostanie zainsertowana wartość default
UNSIGNED - powoduje, że kolumna nie może przechowywać wartości na minusie przy czym zakres pozostaje taki sam, działa tylko dla typów przechowujących liczby całkowite
ZEROFILL - czyli zerowe wypełnienie, w przypadku gdy ilość liczb w polu będzie mniejsza niż ta zadeklarowana przy tworzeniu kolumny wartość pola będzie automatycznie "dopełniana" zerami na początku, działa tylko dla typów przechowujących liczby całkowite, automatycznie tworzy atrybut UNSIGNED
By obejrzeć wink działania polecenie CREATE TABLE wpisujemy polecenie:

CREATE TABLE nazwa_tabeli (musi być zapisana ciągiem bez spacji ewentualnie w apostrofach można stosować przerwy w nazwach tabel)
(id(nazwa kolumny) TINYINT(typ danych) AUTO_INCREMENT (autonumerowanie)
PRIMARY KEY(id) (klucz tabeli)
) TYPE=MyISAM; (typ silnika bazy danych jaki chcemy wykorzystać, opcjonalne)



mysql> describe dane;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| imie | varchar(15) | NO | | NULL | |
| nazwisko | varchar(20) | NO | | NULL | |
| email | varchar(50) | YES | | NULL | |
| ulica | varchar(50) | YES | | NULL | |
| miasto | varchar(50) | YES | | NULL | |
| data_ur | date | YES | | NULL | |
| aktywny | int(11) | YES | | 1 | |
+----------+-------------+------+-----+---------+-------+
7 rows in set (0.00 sec)


Polecenia możemy również wywoływać z pliku, tworzymy plik sql.sql z kodem:

describe dane;


Następnie uruchamiamy go poleceniem:


mysql> \. sql.sql


Wynik będzie taki sam jak powyżej.

Dodawanie rekordów do tabeli w MySQL (INSERT INTO)



INSERT INTO nazwa_tabeli (kolumny) VALUES (wartosci)

Aby dodać nowy rekord do tabeli należy użyć polecenia:


mysql> insert into dane (imie,nazwisko,email,ulica,miasto,data_ur)
-> values('Mariusz','Kowalski','mk@op.pl','Polna','Warszawa','1980-02-25');
Query OK, 1 row affected (0.00 sec)



W wyniku polecenia pojawi się nowy rekord w tabeli dane. Oczywiście są inne sposoby dodania rekordu czyli inna składnia INSERT INTO.


insert into dane select 'Mariusz','Kowalski','mk@op.pl','Polna','Warszawa','1980-02-25',1

Ładowanie danych z pliku w MySQL.

Można również załadować dane z pliku używająć polcenia


mysql>load data local infile ”dane.txt” into table dane;


Kolumny dla tego polecenia powinny być oddzielone tabulatory a wiersze znakami końca wiersza.

Inny sposób to rogram do ładowania danych w MySQL.

mysqlimport --local dane dane.txt


Pobieranie danych z bazy MySQL instrukcja SELECT



Aby pobrać dane z bazy MySQL wpisujemy polecenie SELECT. Składnia tego polecenia to:

SELECT
[ALL | DISTINCT | DISTINCTROW ]
select_expr [, select_expr ...]
[FROM table_nazwa
[WHERE filtry]
[GROUP BY {nazwa_kolumny | expr | position}
[ASC | DESC], ... [WITH ROLLUP]]
[HAVING filtry_agregaty]
[ORDER BY {nazwa_kolumny | expr | position}
[ASC | DESC], ...]
[LIMIT {[offset,] ilosc_wierszy | ilosc_wierszy OFFSET offset}]

Przykład użycia polecenia SELECT w MySQL:


mysql> select * from dane;
+---------+----------+----------+-----------+----------+------------+---------+
| imie | nazwisko | email | ulica | miasto | data_ur | aktywny |
+---------+----------+----------+-----------+----------+------------+---------+
| Mariusz | Kowalski | mk@op.pl | Polna | Warszawa | 1980-02-25 | 1 |
| Stefan | Kawa | k@op.pl | Polna | Warszawa | 0000-00-00 | 0 |
| Marek | Edza | e@o.pl | Sienna | Warszawa | 0000-00-00 | 0 |
| Ewa | Knaz | e@op.pl | Marszalka | Warszawa | 0000-00-00 | 0 |
+---------+----------+----------+-----------+----------+------------+---------+
4 rows in set (0.00 sec)



Jeśli chcemy wybrać konkretne kolumny po poleceniu SELECT w MySQL wpisujemy nazwę kolumn lub kolumny.


mysql> select email from dane;
+----------+
| email |
+----------+
| mk@op.pl |
| k@op.pl |
| e@o.pl |
| e@op.pl |
+----------+
4 rows in set (0.00 sec)




mysql> select ulica,miasto from dane;
+-----------+----------+
| ulica | miasto |
+-----------+----------+
| Polna | Warszawa |
| Polna | Warszawa |
| Sienna | Warszawa |
| Marszalka | Warszawa |
+-----------+----------+
4 rows in set (0.00 sec)


Użycie filtrów, kryteriów w MySQL(Klauzula WHERE w MySQL)




Filtrowanie wyniku zapytanie odbywa się poprzez podanie warunków po poleceniu WHERE.


mysql> select * from dane where ulica='Polna';
+---------+----------+----------+-------+----------+------------+---------+
| imie | nazwisko | email | ulica | miasto | data_ur | aktywny |
+---------+----------+----------+-------+----------+------------+---------+
| Mariusz | Kowalski | mk@op.pl | Polna | Warszawa | 1980-02-25 | 1 |
| Stefan | Kawa | k@op.pl | Polna | Warszawa | 0000-00-00 | 0 |
+---------+----------+----------+-------+----------+------------+---------+
2 rows in set (0.00 sec)


Można użyć oczywiście większej ilości filtrów należy wtedy łączyć warunki używając AND.


mysql> select * from dane where ulica='Polna' and aktywny = 0;
+--------+----------+---------+-------+----------+------------+---------+
| imie | nazwisko | email | ulica | miasto | data_ur | aktywny |
+--------+----------+---------+-------+----------+------------+---------+
| Stefan | Kawa | k@op.pl | Polna | Warszawa | 0000-00-00 | 0 |
+--------+----------+---------+-------+----------+------------+---------+
1 row in set (0.00 sec)


Przy filtrowaniu danych możemy stosować operatory arytmetyczne, porównania i logiczne.
Operatory arytmetyczne:

+ dodawanie
- odejmowanie
* mnożenie
/ dzielenie
Operatory porównania:
< mniejsze niż
<= mniejsze niż lub równe
= równe
!= lub <> nierówne
>= większe niż lub równe
> większe niż
Operatory logiczne:
AND logiczne „i”
OR logiczne „lub”
NOT logiczne przeczenie

Możemy korzyść również polecenia In pozwala ono na zdefiniowanie wartości jakie nas interesują dla kolumny.


mysql> select * from dane where ulica in('polna','Sienna');
+---------+----------+----------+--------+----------+------------+---------+
| imie | nazwisko | email | ulica | miasto | data_ur | aktywny |
+---------+----------+----------+--------+----------+------------+---------+
| Mariusz | Kowalski | mk@op.pl | Polna | Warszawa | 1980-02-25 | 1 |
| Stefan | Kawa | k@op.pl | Polna | Warszawa | 0000-00-00 | 0 |
| Marek | Edza | e@o.pl | Sienna | Warszawa | 0000-00-00 | 0 |
+---------+----------+----------+--------+----------+------------+---------+
3 rows in set (0.00 sec)


Możliwe jest też filtrowanie po fragmencie tekstu w kolumnie kolumnach służy do tego polcenie LIKE w MySQL poprzez znak % oznaczamy dowolny tekst,a „_” oznacza jeden dowolny znak.


mysql> select * from dane where ulica like 'P%';
+---------+----------+----------+-------+----------+------------+---------+
| imie | nazwisko | email | ulica | miasto | data_ur | aktywny |
+---------+----------+----------+-------+----------+------------+---------+
| Mariusz | Kowalski | mk@op.pl | Polna | Warszawa | 1980-02-25 | 1 |
| Stefan | Kawa | k@op.pl | Polna | Warszawa | 0000-00-00 | 0 |
+---------+----------+----------+-------+----------+------------+---------+
2 rows in set (0.00 sec)


Przykład użycia polcenia OR w MySQL


mysql> select * from dane where ulica like 'P%' or aktywny =0;
+---------+----------+----------+-----------+----------+------------+---------+
| imie | nazwisko | email | ulica | miasto | data_ur | aktywny |
+---------+----------+----------+-----------+----------+------------+---------+
| Mariusz | Kowalski | mk@op.pl | Polna | Warszawa | 1980-02-25 | 1 |
| Stefan | Kawa | k@op.pl | Polna | Warszawa | 0000-00-00 | 0 |
| Marek | Edza | e@o.pl | Sienna | Warszawa | 0000-00-00 | 0 |
| Ewa | Knaz | e@op.pl | Marszalka | Warszawa | 0000-00-00 | 0 |
+---------+----------+----------+-----------+----------+------------+---------+
4 rows in set (0.00 sec)


Tu trzeba zaznaczyć że przy kilku warunkach z OR i AND trzeba je łączyć w nawiasach.

Czasami niezbędne jest usunięcie duplikatów z zapytanie, używamy do tego polecenia DISITINCT w MySQL.


mysql> select distinct ulica from dane;
+-----------+
| ulica |
+-----------+
| Polna |
| Sienna |
| Marszalka |
+-----------+
3 rows in set (0.00 sec)


Celem ograniczenia ilości wierszy w MySQL używamy polecenia LIMIT ilość_rekordów (działa jak TOP w t-sql).


mysql> select * from dane limit 1;
+---------+----------+----------+-------+----------+------------+---------+
| imie | nazwisko | email | ulica | miasto | data_ur | aktywny |
+---------+----------+----------+-------+----------+------------+---------+
| Mariusz | Kowalski | mk@op.pl | Polna | Warszawa | 1980-02-25 | 1 |
+---------+----------+----------+-------+----------+------------+---------+
1 row in set (0.00 sec)


Ważne jest również fakt, że w filtrach WHERE jak i w SELECT możemy używać funkcji w MySQL jak i obliczeń.


mysql> select 2*2 as wynik ;
+-------+
| wynik |
+-------+
| 4 |
+-------+
1 row in set (0.00 sec)



mysql> select * from dane where aktywny+2=2;
+--------+----------+---------+-----------+----------+------------+---------+
| imie | nazwisko | email | ulica | miasto | data_ur | aktywny |
+--------+----------+---------+-----------+----------+------------+---------+
| Stefan | Kawa | k@op.pl | Polna | Warszawa | 0000-00-00 | 0 |
| Marek | Edza | e@o.pl | Sienna | Warszawa | 0000-00-00 | 0 |
| Ewa | Knaz | e@op.pl | Marszalka | Warszawa | 0000-00-00 | 0 |
+--------+----------+---------+-----------+----------+------------+---------+
3 rows in set (0.00 sec)



sortowanie wyników zapytania



Sortownie wyniku zapytania w mysql odbywa się poprzez użycie klauzuli ORDER BY.
Klauzule ORDER BY stosujemy, jako ostatni element kwerendy w mysql.

Przykład sortowanie mysql:
mysql> select * from dane order by imie;
+---------+----------+----------+-----------+----------+------------+---------+
| imie | nazwisko | email | ulica | miasto | data_ur | aktywny |
+---------+----------+----------+-----------+----------+------------+---------+
| Ewa | Knaz | e@op.pl | Marszalka | Warszawa | 0000-00-00 | 0 |
| Marek | Edza | e@o.pl | Sienna | Warszawa | 0000-00-00 | 0 |
| Mariusz | Kowalski | mk@op.pl | Polna | Warszawa | 1980-02-25 | 1 |
| Stefan | Kawa | k@op.pl | Polna | Warszawa | 0000-00-00 | 0 |
+---------+----------+----------+-----------+----------+------------+---------+
4 rows in set (0.00 sec)


Sortować możęmy rosnąco ASC lub malejąco DESC.


mysql> select * from dane order by imie desc
-> ;
+---------+----------+----------+-----------+----------+------------+---------+
| imie | nazwisko | email | ulica | miasto | data_ur | aktywny |
+---------+----------+----------+-----------+----------+------------+---------+
| Stefan | Kawa | k@op.pl | Polna | Warszawa | 0000-00-00 | 0 |
| Mariusz | Kowalski | mk@op.pl | Polna | Warszawa | 1980-02-25 | 1 |
| Marek | Edza | e@o.pl | Sienna | Warszawa | 0000-00-00 | 0 |
| Ewa | Knaz | e@op.pl | Marszalka | Warszawa | 0000-00-00 | 0 |
+---------+----------+----------+-----------+----------+------------+---------+
4 rows in set (0.00 sec)


Sortować możemy po kolumnie, kolumnach lub wyrażeniu np. używając funkcji lub działania. Dopuszczalne jest również podanie nr kolumny po której chcemy sortować.

Przykład:

mysql> select * from dane order by 1, aktywny+1,concat(imie,nazwisko);
+---------+----------+----------+-----------+----------+------------+---------+
| imie | nazwisko | email | ulica | miasto | data_ur | aktywny |
+---------+----------+----------+-----------+----------+------------+---------+
| Ewa | Knaz | e@op.pl | Marszalka | Warszawa | 0000-00-00 | 0 |
| Marek | Edza | e@o.pl | Sienna | Warszawa | 0000-00-00 | 0 |
| Mariusz | Kowalski | mk@op.pl | Polna | Warszawa | 1980-02-25 | 1 |
| Stefan | Kawa | k@op.pl | Polna | Warszawa | 0000-00-00 | 0 |
+---------+----------+----------+-----------+----------+------------+---------+
4 rows in set (0.00 sec)


Łączenie kolumn w MySql



Łączenie kolumn w Mysql odbywa się po przez użycie funkcji CONCAT.

mysql> select concat(imie,' ',nazwisko) from dane;
+---------------------------+
| concat(imie,' ',nazwisko) |
+---------------------------+
| Mariusz Kowalski |
| Stefan Kawa |
| Marek Edza |
| Ewa Knaz |
+---------------------------+
4 rows in set (0.00 sec)


Funkcje Mysql


W MySql posiada kilka funkcji do agregowania danych i prowadzenia statystyk są to:

Count(*) – oblicz ilość rekordów w wyniku
AVG – średnia
MIN – wartość minimalna w kolumnie
MAX – wartość maksymalna
SUM – suma wierszy w kolumnie

Przykład użycia:

mysql> select count(*) from dane;
+----------+
| count(*) |
+----------+
| 4 |
+----------+
1 row in set (0.00 sec)


Gdy chcemy wskazać kolumny dla których wartość chcemy obliczyć wartości należy użyć klauzuli GROUP BY.

mysql> select count(*),ulica from dane group by ulica;
+----------+-----------+
| count(*) | ulica |
+----------+-----------+
| 1 | Marszalka |
| 2 | Polna |
| 1 | Sienna |
+----------+-----------+
3 rows in set (0.00 sec)

Gdy chcemy obliczać statystyki dla kolumn poza funkcją należy używać klauzuli group by w innym przypadku program zgłosi błąd.

Możliwe jest również obliczenie statystyk dla nie powtarzających się rekordów używając DISTINCT:

mysql> select count(*),count(ulica),count(distinct ulica),ulica from dane group by ulica;
+----------+--------------+-----------------------+-----------+
| count(*) | count(ulica) | count(distinct ulica) | ulica |
+----------+--------------+-----------------------+-----------+
| 1 | 1 | 1 | Marszalka |
| 2 | 2 | 1 | Polna |
| 1 | 1 | 1 | Sienna |
+----------+--------------+-----------------------+-----------+
3 rows in set (0.00 sec)





SQL-KURSY.pl poleca:

Rozpocznij kolonizacje egzoplanety

Książki
ksikaksikaksikaksikaksika

Copyright 2010-2011 Mariusz Kujawski adres mariuszhk@op.pl

obob ob

Valid HTML 4.01 Transitional