Obrazek użytkownika cytrynek

Podstawy tworzenia i obsługi bazy danych MySQL na zrzutach

Treść: 

Zmiana nazwy Tabeli

RENAME TABLE Błędna_nazwa_tabeli TO Poprawna_nazwa_tabeli;

lub

mysql> ALTER TABLE Uzytkownicy RENAME Users;

 

 

Zmiana nazwy Kolumny

ALTER TABLE bazadanych.tabela CHANGE Błędna_Nazwa_Kolumny Poprawna_Nazwa_Kolumny VARCHAR(25) NOT NULL;

 

USE BazaDanych;

ALTER TABLE tabela CHANGE Błędna_Nazwa_Kolumny Poprawna_Nazwa_Kolumny VARCHAR(25) NOT NULL;

 

Dodanie nowej kolumny

ALTER TABLE nazwa_tabeli ADD nazwa_kolumny datatype;

 

Usunięcie kolumny

ALTER TABLE nazwa_tabeli DROP COLUMN nazwa_kolumny;

 

Zmiana typu kolumny

ALTER TABLE nazwa_tabeli ALTER COLUMN nazwa_kolumny typ

ALTER TABLE uzytkownicy ALTER COLUMN data_urodzenia date

 

Usunięcie indeksu tabeli

DROP INDEX table_name.index_name

 

Usunięcie Tabeli

DROP TABLE nazwa_tabeli;

 

Usunięcie bazy danych

DROP DATABASE nazwa_bazyDanych;

 

Usunięcie danych z tabeli (bez usuwania tabeli)

TRUNCATE TABLE nazwa_tabeli;

 

Aktualizacja danych w tabeli

UPDATE nazwa_tabeli SET nazwaKolumnyPierwszej=value1,nazwaKolumnyDrugiej=value2,... WHERE jakas_Kolumna=jakas_Wartość;

 

UPDATE uzytkownicy SET nazwisko='Walentowski', imie='Adrian', data_ur='1990-02-21' WHERE ID_user=23;

Dodanie 1 kolumny

mysql> ALTER TABLE Uzytkownicy ADD wiek INT;

 

Dodanie kilku kolumn

mysql> ALTER TABLE Uzytkownicy ADD (rangaID INT, pseudo VARCHAR(35), facebook VARCHAR(100), googleplusVARCHAR(100), zainteresowaniaID VARCHAR(20));

 

Dodanie kolumny w określonej pozycji

mysql> ALTER TABLE Uzytkownicy ADD plec BOOL AFTER pseudo;

 

Modyfikacja istniejącej kolumny / kolumn

mysql> ALTER TABLE Uzytkownicy MODIFY plec char(1);

 

Zmiana nazwy kolumny

mysql> ALTER TABLE Uzytkownicy CHANGE zainteresowaniaID zaintID VARCHAR(25);

 

Usunięcie kolumny

mysql> ALTER TABLE Uzytkownicy DROP COLUMN facebook, DROP COLUMN googleplus, DROP COLUMN zainteresowaniaID;

 

Dodanie autonumerowania (auto increment) dla tabeli

alter table Uzytkownicy MODIFY COLUMN  userID INT(6) AUTO_INCREMENT;

 

Instrukcja REPLACE działa dokładnie tak samo, jak instrukcja INSERT, z tym że wyszukuje klucza podstawowego (w tym przypadku 2) i zmienia zawartość podanych pól

mysql> REPLACE INTO Uzytkownicy (imię, nazwisko, userID) VALUES ("Jacek", "Nowak", 2);

 

Składnia instrukcji tworzącej indeks jest bardzo prosta. Index tworzy się w celu przyspieszenia wyszukiwania zawartości tabeli

CREATE INDEX user_index ON Uzytkownicy (nazwisko);

 

mysql> SELECT * FROM Uzytkownicy WHERE imię = "Marek" AND nazwisko = "Wielki";

 

mysql> SELECT * FROM Uzytkownicy WHERE imię = "Marek" OR imię = "Jacek";

 

mysql> SELECT * FROM Uzytkownicy WHERE NOT telefon = 618546234;

 

Zamiast podawać kilku wyrażeń logicznych:

mysql> SELECT * FROM Uzytkownicy WHERE kID=1 OR kID=2 OR kID=3;

Można posłużyć się słowem kluczowym IN:

mysql> SELECT * FROM Uzytkownicy WHERE kID IN (1,2,3);

 

 

Zamiast stosowania operatorów :

mysql> SELECT * FROM Uzytkownicy WHERE kID >= 2 AND kID <= 4;

Można wykorzystać możliwości BETWEEN:

mysql> SELECT * FROM Uzytkownicy WHERE kID BETWEEN 2 AND 4;

 

mysql> SELECT imię FROM Uzytkownicy;

mysql> SELECT DISTINCT imię FROM Uzytkownicy; // Unikalne rekordy

 

mysql> SELECT * FROM Uzytkownicy ORDER BY telefon;

 

mysql> SELECT * FROM Uzytkownicy ORDER BY nazwisko ASC;

 

mysql> SELECT * FROM Uzytkownicy ORDER BY nazwisko DESC;

 

mysql> SELECT * FROM Uzytkownicy LIMIT 2;

 

mysql> SELECT * FROM Uzytkownicy LIMIT 1, 2;

// rekord początkowy 1 (numeracja od 0) 2 - liczba rekordów

 

 

Zliczenie liczby znaków w komórce

mysql> SELECT CHAR_LENGTH(nazwisko) FROM Uzytkownicy;

 

Połączenie funkcji - Sortowanie + limitowanie

mysql> SELECT * FROM Uzytkownicy ORDER BY nazwisko ASC LIMIT 2;

 

Tworzenie tabel tymczasowych – TEMPORARY TABLE

Bardzo przydatna funkcja do tworzenia tablic aktywnych tylko w czasie aktualnej sesji. Po zakończeniu sesji tablica zostaje usunięta.

CREATE TEMPORARY TABLE dane (

kolumna1 typ,

kolumna2 typ,

kolumna3 typ,

...

kolumnaN typ,

);

Tworzenie kopii tabel (bez zawartości) - LIKE CREATE TABLE student LIKE klient;

 

Tworzenie kopii tabel (wraz z zawartością) – AS + SELECT

CREATE TABLE student AS (SELECT * FROM klient);

 

Wyrażenia regularne - filtrowanie, wyszukiwanie danych

Operator Type

Examples

Description

Literal Characters
Match a character exactly

a A y 6 % @

Letters, digits and many special
characters match exactly

\$ \^ \+ \\ \?

Precede other special characters
with a \ to cancel their regex special meaning

\n \t \r

Literal new line, tab, return

\cJ \cG

Control codes

\xa3

Hex codes for any character

Anchors and assertions

^

Field starts with

$

Field ends with

[[:<:]]

Word starts with

[[:>:]]

Word ends with

Character groups
any 1 character from the group

[aAeEiou]

any character listed from [ to ]

[^aAeEiou]

any character except aAeEio or u

[a-fA-F0-9]

any hex character (0 to 9 or a to f)

.

any character at all

[[:space:]]

any space character (space \n \r or \t)

[[:alnum:]]

any alphanumeric character (letter or digit)

Counts
apply to previous element

+

1 or more ("some")

*

0 or more ("perhaps some")

?

0 or 1 ("perhaps a")

{4}

exactly 4

{4,}

4 or more

{4,8}

between 4 and 8

Add a ? after any count to turn it sparse (match as few as possible) rather than have it default to greedy

Alternation

|

either, or

Grouping

( )

group for count and save to variable

 

 

mysql> SELECT nazwisko FROM Uzytkownicy

WHERE nazwisko regexp 'owa';

 

Operator logiczny OR

mysql> SELECT nazwisko FROM Uzytkownicy

WHERE nazwisko regexp 'o|k';

 

Operator ^ wskazuje na pierwszą literę

mysql> SELECT imię FROM Uzytkownicy WHERE imię regexp '^J';

 

Operator $ wskazuje na ostatnią literę

mysql> SELECT imię FROM Uzytkownicy WHERE imię regexp 'k$';

 

Operator . wskazuje na jedną literę

mysql> SELECT imię FROM Uzytkownicy WHERE imię regexp '^J.n$'; //Jan

 

Operator .+ wskazuje na dowolną liczbę liter

mysql> SELECT imię FROM Uzytkownicy WHERE imię regexp '.+k$'; //Jarek, Janek, Marek

 

Umieszczenie szukanego znaku przed operatorem + reprezentuje dowolną liczbę

tego znaku w przeszukiwanych komórkach

mysql> SELECT telefon FROM Uzytkownicy WHERE telefon regexp '^355+'; //3555746, 355865483838

 

 

Operator {} umożliwia określenie liczby poszukiwanego znaku

mysql> SELECT telefon FROM Uzytkownicy WHERE telefon regexp '^6185{6}'; //618555555

 

mysql> SELECT telefon FROM Uzytkownicy WHERE telefon regexp '^6185{1,6}'; // {1,6} to zakres

 

Operator [] wskazuje na szukanie podanych znaków, a operator + wskazuje na ich

dowolną liczbę od 1-...

 

mysql> SELECT email FROM Uzytkownicy WHERE email regexp '[ja]+'; //jola@o2.pl, jkowal@o2.pl

 

operator {} wskazuje na ich konkretną liczbę lub zakres

 

mysql> SELECT email FROM Uzytkownicy WHERE email regexp '^j[k]{1}.+l$';

 

mysql> SELECT imię FROM Uzytkownicy WHERE imię regexp '^[a-z]+$' //zakres szukanych znaków zaczyna się od małych liter

 

mysql> SELECT imię FROM Uzytkownicy WHERE imię regexp '^[^j]+$'; // wyszukiwanie poza literą 'j'

 

Praktyczny przykład - sprawdzenie poprawności wprowadzonego adresu email

mysql> SELECT email FROM Uzytkownicy WHERE email regexp '^[A-Za-z0-9_]+@[A-Za-z0-9\-]+\.[A-Za-z0-9.\-]+$';

 

 

 

W tej części kursu zajmiemy się łączeniem tabel. Oto najprostszy przykład oparty o INNER JOIN:

Wynikiem poniszego zapytania będzie 12 miesięcy, a z pawiej strony "null", LEFT JOIN nakazuje wybranie wszystkich rekordów z pierwszej tabeli, a z drugiej tylko tych dla których warunek miesiac = miesciac_k jest spełniony

 

 

SELECT
ilosc,
miesiac
koszt
FROM
t_dane inner join t_koszty on
miesiacInt = miesiac_k
WHERE
miesiac = 'styczeń'
go
 
 

 

W tym przypadku jeśli spojrzymy na tabele 2. wynikiem zapytanie będzie 11 miesięcy, right join każe wybrać wszystkie rekordy z drugiej tabeli, a z pierwszej tylko te spełniające warunek miesiac = miesciac_k

 

SELECT
miesiac,
miesiac_k
t_dane right join t_koszty on
miesiacInt = miesiac_k
 
 

Łączenie kilku tabel nie różni się od połączenie dwóch ze sąbą jedyne o czym należy pamiętać to fakt by wskazać alias lub nazwę tabeli z której pochodzi kolumna jeśli kolumny w poszczegulnych tabelach nazywają się tak samo. 

 
SELECT
a.miesiac,
b.koszt,
c.koszt,
d.koszt
from
t_dane a left join t_koszty b on a.miesiacInt = b.miesiac_k
left join t_koszty c on a.miesiacInt+1 = b.miesiac_k
left join t_koszty d on a.miesiacInt+2 = d.miesiac_k
 
 
 
 
mysql> select Wypozyczenia.NR_Klienta, Klienci.Imie, Samochody.Marka, Miejsca.Ulica from Wypozyczenia left join Klienci on Wypozyczenia.NR_Klienta=Klienci.NR_Klienta left join Samochody on Wypozyczenia.NR_Samochodu=Samochody.NR_Samochodu left join Miejsca on Wypozyczenia.NR_Miejsca=Miejsca.NR_Miejsca;
+------------+----------+-------+-----------+
| NR_Klienta | Imie     | Marka | Ulica     |
+------------+----------+-------+-----------+
|          1 | Grzegorz | Audi  | Orzechowa |
|          2 | Konrad   | Ford  | Bukowa    |
+------------+----------+-------+-----------+
2 rows in set (0.00 sec)