Kurs MySQL

Kurs MySQL

Tematyka kursu została podzielona na tematy zgodne z listą i znajduje się na FORUM w dziale 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 usługa startuje zaraz po instalacji. By uruchomić klienta w konsoli dla Windowsa wchodzimy do lokalizacji z mysql i wpisujemy mysql – p – u root i podajemy hasło, a dla linuxa w konsoli wpisujemy mysql – p – u root i podajemy hasło w obu systemach. (Oczywiście możemy użyć środowiska graficznego które jest dostępne w serwerze XAMPP, ale ja polecam HeidiSQL lub Navicat for MySQL)

Pliki do ćwiczeń dane.txt oraz sql.sql (patrz ANEKS 1 i 2 na końcu kursu)

1. Wpisujemy pierwszą komendę - czyli tworzymy bazę danych 'szkolnaDB':

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 select database();

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

2. 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, że kolumny w obrębie tabeli muszą mieć unikalne nazwy, nie mogą posiadać w nazwie liter polskich oraz muszą mieć 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, każdy rekord automatycznie podczas insertu otrzymuje 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ą się 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ą się 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ć wynik działania polecenia CREATE TABLE wpisujemy polecenie DESCRIBE:

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)

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.

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

INSERT INTO nazwa_tabeli (kolumny) VALUES (wartości)

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ąc polecenia

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

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

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

mysqlimport --local dane dane.txt

4. 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:

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

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

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

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

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 "nie"

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

Możliwe jest też filtrowanie po fragmencie tekstu w kolumnie/kolumnach służy do tego polecenie LIKE w MySQL poprzez znak % (polecenia) oznaczamy dowolny tekst, a znak „_” (podkreślnik) oznacza jeden dowolny znak.

Przykład użycia polecenia OR w MySQL

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 zapytania, używamy do tego polecenia DISITINCT w MySQL (czyli odfiltrowanie powtarzających się duplikatów).

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

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

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

6. Sortowanie wyników zapytania klauzula ORDER BY

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:

Sortować możemy rosnąco ASC (domyślnie) lub malejąco DESC.

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:

7. Łączenie kolumn w MySql dzięki funkcji CONCAT

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

8. Funkcje Mysql

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)

9. Gdy chcemy wskazać kolumny dla których chcemy obliczyć wartości należy użyć klauzuli GROUP BY - czyli tzw. grupowania wyników.

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 niepowtarzają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)

ANEKSY
______________________________________________________________
ANEKS 1 plik txt (skopiuj poniższe dane do notatnika i zapisz jako dane.txt)

Stefan Kawa k@op.pl Polna Warszawa 1
Marek Edza e@o.pl Sienna Warszawa 1
Ewa Knaz e@op.pl Marszalka Warszawa 1
______________________________________________________________

ANEKS 2 plik SQL (skopiuj poniższe dane do notatnika i zapisz jako sql.sql)

use szkolnaDB
describe dane;
______________________________________________________________

Pobierz w pdf

PRZEGLĄD PODSTAWOWEJ SKŁADNI JĘZYKA MySQL

Główna forma zapytania SELECT

DISTINCT oznacza nie powtarzające się wiersze.

SELECT [DISTINCT] nazwy(a)_kolumny /*

FROM nazwa_tabeli;

Selekcja wierszy z tabeli z warunkiem

SELECT [DISTINCT] nazwy(a)_kolumny

FROM nazwa_tabeli

WHERE nazwa_kolumny operator_porównania wartość;

Uporządkowanie wierszy

SELECT [DISTINCT] nazwy(a)_kolumny /*

FROM nazwa_tabeli

ORDER BY nazwy(a)_kolumn(y) / numer(y)_kolumn(y) [ASC/DESC-rosnąco/malejąco];

Operatory porównania

= równe
< mniejsze niż
!= nie równe
<= mniejsze niż lub równe
> większe niż
>= większe niż lub równe

Warunek iloczynowy (AND)

SELECT nazwy(a)_kolumn(y)

FROM nazwa_tabeli

WHERE warunek AND warunek ;

Alternatywny warunek zapytania

SELECT nazwy(a)_kolumn(y)

FROM nazwa_tabeli

WHERE warunek OR warunek ;

Wyszukiwania zakresowe (BETWEEN)

SELECT nazwy(a)_kolumn(y)

FROM nazwa_tabeli

WHERE nazwa_kolumny

[NOT] BETWEEN wartość_1 AND wartość_2 ;

SELECT nazwy(a)_kolumn(y)

FROM nazwa_tabeli

WHERE wartość

[NOT] BETWEEN nazwa_kolumny_1 AND nazwa_kolumny_2

Poszukiwanie wzorców znakowych (LIKE)

SELECT nazwy(a)_kolumn(y)

FROM nazwa_tabeli

WHERE nazwa_kolumny

[NOT] LIKE "napis" ;

Wyszukiwanie wartości NULL

SELECT nazwy(a)_kolumn(y)

FROM nazwa_tabeli

WHERE nazwa_kolumny IS [NOT] NULL ;

Operatory porównania zbioru (IN)

SELECT nazwy(a)_kolumn(y)

FROM nazwa_tabeli

WHERE nazwa_kolumny

[NOT] IN (wartość_1, wartość_2, .....) ;

Funkcje wbudowane

SELECT funkcja_wbudowana [DISTINCT] nazwa_kolumny

FROM nazwa_tabeli

[WHERE warunek] ;

Funkcje wbudowane w SQL

AVG
SUM
MIN
MAX
COUNT (może być użyta razem z *

Obliczenia

SELECT nazwy(a)_kolumn(y), wyrażenie arytmetyczne

FROM nazwa_tabeli

[WHERE warunek]

[ORDER BY nazwy(a)_kolumn(y) / *] ;

Operatory arytmetyczne

+ dodawanie
- odejmowanie
* mnożenie
/ dzielenie

Podzapytania

Zapytanie główne:

SELECT nazwy(a)_kolumn(y)

FROM nazwa_tabeli

WHERE nazwa_kolumny

operator_porównania / operator_porównania zbioru

pod zapytanie:

(SELECT nazwa_kolumny

FROM nazwa_tabeli

[WHERE warunek] );

Grupowanie

SELECT nazwy(a)_kolumn(y), funkcja_wbudowana(argument)

FROM nazwa_tabeli

[WHERE warunek]

GROUP BY nazwy(a)_kolumn(y)

[HAVING warunek]

[ORDER BY nazwy(a)_kolumny / numer(y)_kolumn(y) [ASC/DESC]] ;

Złączanie tabel

SELECT nazwy(a)_kolumn(y)

FROM nazwa_tabeli, nazwa_tabeli [,nazwa_tabeli, .....]

WHERE warunek_złączenia

[AND / OR warunek]

[ORDER BY nazwy(a)_kolumny / numer(y)_kolumn(y)] ;

Operowanie danymi

Wstawianie wierszy do tabeli

INSERT INTO nazwa_tabeli

[(nazwa_kolumny_1, nazwa_kolumny_2, ....)]

VALUES (wartość_1, wartość_2, ...);

INSERT INTO nazwa_tabeli

[(nazwa_kolumny_1, nazwa_kolumny_2, ....)]

VALUES (wartość_1, wartość_2, ...)

SELECT nazwy(a)_kolumn(y)

FROM nazwa_tabeli

WHERE warunek;

Aktualizacja danych

UPDATE nazwa_tabeli / nazwa_perspektywy

SET nazwa_kolumny_1=wartość / wyrażenie_arytmetyczne,

..........

nazwa_kolumny_n=wartość / wyrażenie_arytmetyczne

[WHERE warunek];

Kasowanie wierszy z tabeli

DELETE FROM nazwa_tabeli

[WHERE warunek];

Definiowanie danych, tworzenie tablic

CREATE TABLE nazwa_tabeli

(nazwa_kolumny_1 typ_danych [NOT NULL]

nazwa_kolumny_2 typ_danych [NOT NULL]

...........

nazwa_kolumny_n typ_danych [NOT NULL];

CREATE TABLE Klienci
(ID_Klienta Number(4) PRIMARY KEY,
Imie Varchar2(20) NOT NULL,
Nazwisko Varchar2(20) NOT NULL,
Status Varchar2(10) DEFAULT 'Aktualny');

Definiowanie danych, zmiana tablic

ALTER TABLE nazwa_tabeli
ADD nazwa_kloumny typ_danych;
ALTER TABLE Klienci
ADD (Telefon Varchar2(10));

DROP TABLE nazwa_tabeli;

Definiowanie danych, tworzenie indeksu

CREATE [UNIQUE] INDEX nazwa_indeksu

ON nazwa_tabeli (nazwy(a)_kolumny [ASC/DESC]);

Definiowanie danych, usuwanie indeksu

DROP INDEX (nazwa_indeksu);

Definiowanie danych, tworzenie synonimu

CREATE SYNONYM nazwa_synonimu

FOR nazwa_tabeli / nazwa_perspektywy;

Definiowanie danych, usuwanie synonimu

DROP SYNONYM nazwa_synonimu;

Definiowanie danych, tworzenie perspektywy

CREATE VIEW nazwa_perspektywy

AS instrukcja_zapytania_w_SQL ;

Definiowanie danych, usuwanie perspektywy

DROP VIEW nazwa_perspektywy;

Administrowanie danymi

Przyznanie uprawnienia:

GRANT ALL / SELECT / UPDATE / INSERT / DELETE / INDEX / ALTER

dla obiektu:

ON nazwy(a)_tabel(i) / nazwa_perspektyw(y)

dla użytkownika:

TO nazwy(a)_użytkowników(a);

Usunięcie uprawnień

REVOKE ALL / SELECT / UPDATE / INSERT / DELETE / INDEX / ALTER

ON nazwy(a)_tabel(i) / nazwa_perspektyw(y)

TO nazwy(a)_użytkowników(a);

_______________________________________________________________________________

Kurs MySQL

Panel komentarzy i postów: