1. Bazy danych
a) tworzenie bazy
CREATE DATABASE baza;
GRANT ALL PRIVILEGES ON baza.* TO 'user'@'localhost' IDENTIFIED BY 'user';
GRANT ALL PRIVILEGES ON baza.* TO 'user'@'%' IDENTIFIED BY 'user';
USE baza;
b) usuwanie bazy
DROP DATABASE baza;
DROP USER 'user'@'localhost';
DROP USER 'user'@'%';
c) wypisz istniejące bazy / tabele
SHOW DATABASES;
SHOW TABLES;
2. Tabele
a) tworzenie tabeli
CREATE TABLE dom
(
nr INT AUTO_INCREMENT PRIMARY KEY,
imie VARCHAR(10) NOT NULL,
nazwisko CHAR(8) NOT NULL,
wiek INT CHECK (wiek<200)
waga INT CHECK (waga>0 AND waga<200)
);
b) sprawdzanie struktury
DESCRIBE dom;
c) usuwanie tabeli
DROP TABLE dom;
d) podgląd utworzonej tabeli
SELECT * FROM dom;
lub
SELECT nr, imie, nazwisko, wiek, waga FROM dom;
e) zmiana nazwy tabeli
ALTER TABLE dom
RENAME TO mieszkancy;
f) zmienianie kolumn
- wstawianie kolumny na końcu
ALTER TABLE dom
ADD wzrost INT;
- wstawianie kolumny na początku
ALTER TABLE dom
ADD wzrost INT FIRST;
- wstawianie kolumny po innej
ALTER TABLE dom
ADD wzrost INT AFTER waga;
- kasowanie kolumny
ALTER TABLE dom
DROP wzrost;
- zmiana nazwy i właściwości kolumny
ALTER TABLE dom
CHANGE wzrost wysokość DEC(1,2);
- zmiana tylko właściwości kolumny
ALTER TABLE dom
MODIFY wzrost DEC(1,2);
g) zmienianie wierszy
- wstawianie wierszy
INSERT INTO dom (imie, nazwisko, wiek, waga)
VALUES
(`Leszek', `Owczarek', 21, 60),
(`Monika', `Owczarek', 20, 58),
(`Kasia', `Owczarek', 6, 25);
- kasowanie wiersza
DELETE FROM dom
WHERE imie='Leszek';
- zmiana danych wierszu
UPDATE dom
SET waga=61 WHERE imie='Leszek';
3. Zapytania SELECT
- starsi niż 20 lat
SELECT * FROM dom
WHERE wiek >=20;
- różny od 20 lat
SELECT * FROM dom
WHERE wiek <>20;
- pomiędzy 20 a 21 lat
SELECT * FROM dom
WHERE wiek >=20 AND wiek <=21;
lub
SELECT * FROM dom
WHERE wiek BETWEEN 20 AND 21;
- tylko konkretne wartości
SELECT * FROM dom
WHERE wiek IN (20, 25, 30);
- komórki z niezerowym wiekiem
SELECT * FROM dom
WHERE wiek IS NOT NULL;
- wszyscy którzy mają na imię leszek (wielkość znaków bez znaczenia)
SELECT * FROM dom
WHERE imie='Leszek';
lub
SELECT * FROM dom
WHERE imie LIKE 'Leszek';
- wszyscy którzy mają na imię Leszek (wielkość znaków ma znaczenie)
SELECT * FROM dom
WHERE imie LIKE BINARY 'Leszek';
- wszyscy którzy mają imię zaczynające się na L
SELECT * FROM dom
WHERE imie LIKE 'L%';
- wszyscy którzy mają 6-literowe imię kończące się na k
SELECT * FROM dom
WHERE imie LIKE '_____k';
4. Funkcje agregujące
- podaj najstarszego
SELECT MAX(wiek) FROM dom;
- podaj najmlodszego
SELECT MIN(wiek) FROM dom;
- podaj średni wiek
SELECT AVG(wiek) FROM dom;
- podaj sumę wieków wszystkich ludzi
SELECT SUM(wiek) FROM dom;
- podaj ile ludzi ma podany wiek w tabeli
SELECT COUNT(wiek) FROM dom;
- podaj ilość ludzi powyżej 20 roku życia
SELECT COUNT(wiek) FROM dom
WHERE wiek>20;
lub
SELECT COUNT(wiek<20) FROM dom;
5. Wyświetlanie
a) zmiana sposobu wyświetlania wyników
- pokaż 2 pierwsze
SELECT * FROM dom
LIMIT 2;
- pomiń 2 pierwsze, pokaż 3 następne:
SELECT * FROM dom
LIMIT 2,3;
- zmiana nazwy wyświetlanej kolumny
SELECT wiek AS „lata zycia” FROM dom;
- wyświetlenie 0 zamiast NULL w pustych komórkach
SELECT nr, imie, nazwisko, IFNULL(wiek,0) AS wiek, waga FROM dom;
- wyświetlenie bez duplikatów (unikalna para imienia i nazwiska)
SELECT DISTINCT imie, nazwisko FROM dom;
b) sortowanie
- od max do min:
SELECT * FROM dom
ORDER BY wiek DESC;
- od min do max:
SELECT * FROM dom
ORDER BY wiek ASC;
- według 2 danych:
SELECT * FROM dom
ORDER BY wiek ASC, waga DESC;
c) grupowanie
w SELECT może być tylko to co GROUP BY + funkcje agregujące
SELECT waga, COUNT(*) FROM dom
GROUP BY waga ASC;
zamiast WHERE daje się HAVING
SELECT waga, COUNT(*) FROM dom
GROUP BY waga ASC
HAVING waga>40;
6. Łączenie tabel
- podaj wiek i oceny ludzi
SELECT dom.imie, dom.nazwisko, dom.wiek, oceny.ocena FROM dom, oceny
WHERE dom.nr=oceny.nr;
- podaj wiek i ocenę najmłodszej osoby
SELECT dom.wiek, oceny.ocena FROM dom, oceny
WHERE dom.nr=oceny.nr
HAVING dom.wiek=(
SELECT min(wiek) FROM dom
);
7. Typy danych:
Int, Smallint, Bigint - całkowite
Decimal(m,n) - z przecinkiem
Float - rzeczywiste
Char(n) - ciąg znaków o stałej długości
Varchar(n) - ciąg znaków o zmiennej długości
TEXT - dłuższy ciąg znaków
BLOB - dłuższy binarny ciąg znaków
YEAR - rok
Date - data
Time - godzina