ZAPYTANIA SQL XAMPPPHPMYADMIN Opracowa Arkadiusz Waliczek Utwrz baz
ZAPYTANIA SQL – XAMPPPHPMYADMIN Opracował: Arkadiusz Waliczek
Utwórz bazę danych o nazwie Pracownicy CREATE DATABASE Pracownicy;
Utwórz tabele pracownicy oraz stanowiska połączone ze sobą ralacją 1 - wielu • Idp, imie, nazwisko, miasto – tabela pracownicy • Ids, pensja, nazwa – stanowiska
Tworzenie tabeli „pracownicy” CREATE TABLE pracownicy( idp int not null AUTO_INCREMENT PRIMARY KEY, imie varchar(50), nazwisko varchar(50), miasto text )
Tworzenie tabeli „stanowiska” CREATE TABLE stanowiska( ids int not null AUTO_INCREMENT PRIMARY KEY, pensja decimal(10, 2), nazwa varchar(255), Idp int )
Tworzenie Relacji • ALTER TABLE stanowiska ADD FOREIGN KEY(idp) REFERENCES pracownicy(idp);
Dodawanie danych do tabel • INSERT INTO pracownicy(imie, nazwisko, miasto) VALUES ("Arkadiusz", "Waliczek", "Pszczyna"); • INSERT INTO pracownicy(imie, nazwisko, miasto) VALUES ("Monika", "Czeszo", "Pszczyna"); • INSERT INTO pracownicy(imie, nazwisko, miasto) VALUES ("Dariusz", "Walek", "Pszczyna"); • INSERT INTO pracownicy(imie, nazwisko, miasto) VALUES ("Zofia", "Koba", "Piasek"); • INSERT INTO pracownicy(imie, nazwisko, miasto) VALUES ("Maria", "Paliczek", "Rybnik"); • INSERT INTO pracownicy(imie, nazwisko, miasto) VALUES ("Arkadiusz", "Ziebura", "Rybnik");
Dodawanie do tabel • INSERT INTO stanowiska(pensja, nazwa, idp) VALUES (15000. 15, "Nauczyciel", 1); • INSERT INTO stanowiska(pensja, nazwa, idp) VALUES (2500. 25, "Nauczyciel", 2); • INSERT INTO stanowiska(pensja, nazwa, idp) VALUES (4500. 18, "Nauczyciel", 3); • INSERT INTO stanowiska(pensja, nazwa, idp) VALUES (25000. 15, "Programista", 4); • INSERT INTO stanowiska(pensja, nazwa, idp) VALUES (3000. 15, "Kucharz", 5); • INSERT INTO stanowiska(pensja, nazwa, idp) VALUES (5000. 65, "Kierowca", 6);
Wprowadzenie – ogólna postać Zapytań • SELECT [DISTINCT] < lista kolumn/wyrażeń > FROM <lista tablic> [WHERE < warunek> ] • [GROUP BY < lista kolumn> ] • [HAVING < warunek> ] [ORDER BY < lista kolumn/numerów> ] • [ ] instrukcja może wystąpić lub nie !!!
Opis słów kluczowych • SELECT - określa, które kolumny z tablic podanych we frazie FROM mają zostać włączone do wyniku (projekcja). • Dodatkowo, mogą zostać włączone wyrażenia. DISTINCT - powoduje wyeliminowanie duplikatów (wierszy powtarzających się). • FROM - określa tablice, na jakich działa instrukcja SELECT. • WHERE - określa warunki wyboru wierszy z tablic wymienionych we frazie FROM (selekcja).
Cd. Słowa kluczowe • GROUP BY - powoduje wiązanie wierszy wynikowych w grupy o jednakowych wartościach we wskazanych kolumnach, a następnie redukowanie tych grup do pojedynczych wierszy. • HAVING - określa warunki wyboru dla wierszy powstałych w wyniku działania frazy GROUP BY. • ORDER BY - porządkuje wiersze wynikowe rosnąco lub malejąco według wartości wskazanych kolumn: – ASC – rosnąco DESC - malejąco
Zapytania proste • SELECT [DISTINCT] < lista kolumn/wyrażeń> FROM < nazwa tablicy> [ WHERE < warunek> ] • Uwaga: < lista kolumn> może przybrać formę * (co oznacza wybór wszystkich kolumn).
Zapytania proste • Przykład 1 Pokaż identyfikatory i nazwiska wszystkich pracowników. SELECT nazwisko, miesto FROM pracownicy; • Przykład 2 Pokaż nazwy wszystkich miast, skąd pochodzą pracownicy. SELECT DISTINCT miasto FROM pracownicy; • Uwaga: Bez słowa DISTINCT nazwy miast mogłyby się powtarzać.
Zapytania proste • Przykład 3 Pokaż imiona i nazwiska pracowników, którzy pochodzą z Pszczyny. • SELECT imie, nazwisko FROM pracownicy WHERE miasto = ‘Pszczyna’;
Zapytania proste • Przykład 4 Pokaż imiona i nazwiska i pensje wszystkich pracowników. • SELECT imie, nazwisko, pensja FROM pracownicy, stanowiska WHERE pracownicy. idp=stanowiska. idp; • Relacja: { klucz_obcy=nazwa_tabeli. klucz_podstawowy } Z jakiej tabeli mogą pochodzić atrybuty: Imie, nazwisko, pencja ? ? ?
Wyrażenia • Operatory języka SQL występujące w wyrażeniach we frazie SELECT, WHERE i in. : • Operatory arytmetyczne: ** ^ potęgowanie * / mnożenie, dzielenie + - dodawanie, odejmowanie Operatory logiczne: NOT AND OR Operatory porównania: = > < >= <= <> Operatory specjalne: IN BETWEEN LIKE MATCHES Operatory porównania z wartością pustą: IS NULL IS NOT NULL
Funkcje agregujące • COUNT() - zwraca liczbę wierszy wybranych w zapytaniu. • AVG() - oblicza średnią arytmetyczną w kolumnie numerycznej. • SUM() - sumuje wartości kolumn numerycznych. • MIN() - znajduje wartość minimalną w kolumnie znakowej, numerycznej lub daty. • MAX() - znajduje wartość maksymalną w kolumnie znakowej, numerycznej lub daty. • COUNT(*) - zwraca liczbę wierszy w tablicy wynikowej. • COUNT(DISTINCT < nazwa kolumny>) - zwraca liczbę różnych wartości we wskazanej kolumnie.
Przykłady zapytań • Przykład 1 Ilu pracowników pochodzi z Pszczyny? • SELECT COUNT(*) FROM pracownicy WHERE miasto = ‘Pszczyna’; • Przykład 2 Z ilu różnych miast pochodzą pracownicy? SELECT COUNT(DISTINCT miasto) FROM pracownicy;
SUM([DISTINGT]<nazwa kolumny>) • Przykład 1 Podaj sumę zarobków wszystkich pracowników. • SELECT SUM(pensja) FROM pracownicy, stanowiska WHERE stanowisko=stanowiska. id_stanowiska; • Przykład 2 Zmodyfikuj poprzednie zapytanie. Zmień nazwę kolumny zawierającej efekt działania funkcji. • SELECT SUM(pensja) AS „Suma zarobków” FROM pracownicy, stanowiska WHERE stanowisko=stanowiska. id_stanowiska;
AVG([DISTINGT]<nazwa kolumny>) • Przykład 1 Podaj średnie wynagrodzenie wszystkich pracowników. • SELECT AVG(pensja) FROM pracownicy, stanowiska WHERE stanowisko=stanowiska. id_stanowiska; • Przykład 2 Zmodyfikuj poprzednie zapytanie. Zmień nazwę kolumny zawierającej efekt działania funkcji. • SELECT AVG(pensja) AS „Średnie wynagrodzenie” FROM pracownicy, stanowiska WHERE stanowisko=stanowiska. id_stanowiska;
MAX([DISTINGT]<nazwa kolumny>) • Przykład 1 Znajdź największą pensję pracownika. • SELECT MAX(pensja) FROM pracownicy, stanowiska WHERE stanowisko=stanowiska. id_stanowiska; • Przykład 2 Zmodyfikuj poprzednie zapytanie. Zmień nazwę kolumny zawierającej efekt działania funkcji. • SELECT MAX(pensja) AS „Najwyższe wynagrodzenie” FROM pracownicy, stanowiska WHERE stanowisko=stanowiska. id_stanowiska;
MIN([DISTINGT]<nazwa kolumny>) • Przykład 1 Znajdź najmniejszą pensję pracownika. • SELECT MIN(pensja) FROM pracownicy, stanowiska WHERE stanowisko=stanowiska. id_stanowiska; • Przykład 2 Zmodyfikuj poprzednie zapytanie. Zmień nazwę kolumny zawierającej efekt działania funkcji. • SELECT MIN(pensja) AS „Minimalne wynagrodzenie” FROM pracownicy, stanowiska WHERE stanowisko=stanowiska. id_stanowiska;
OPERATORY SPECJALNE • IN - sprawdza, czy wartość w kolumnie jest równa jednej z wartości określonej na liście wartości lub będących wynikiem instrukcji SELECT. • Przykład: WHERE miasto = ‘Gdansk’ OR miasto = ‘Gdynia’ OR miasto = ‘Sopot’ jest równoważne: WHERE miasto IN (‘Gdansk’, ‘Gdynia’, ‘Sopot’)
OPERATORY SPECJALNE • BETWEEN - sprawdza, czy wartość w kolumnie jest zawarta w przedziale wartości. • Przykład: WHERE pensja>=3000 AND pensja<=5000 • jest równoważne: • WHERE pensja BETWEEN 3000 AND 5000
OPERATORY SPECJALNE • LIKE - porównuje zawartość kolumny znakowej z łańcuchem znaków, który może zawierać symbole wieloznaczne. • Symbole wieloznaczne: _ (podkreślenie) - pasuje do dowolnego pojedynczego znaku, % (procent) - pasuje do dowolnej liczby dowolnych znaków. Przykład Pokaż wszystkich pracowników o nazwiskach zaczynających się na literę C. SELECT nazwisko FROM pracownicy WHERE nazwisko LIKE ‘C%’;
OPERATORY SPECJALNE • CONTAINS - sprawdza, czy wartość w kolumnie znakowej zawiera wskazany łańcuch. • Przykład: Pokaż wszystkich pracowników, których nazwisko zawiera łańcuch ‘ore’: • SELECT nazwisko FROM pracownicy WHERE nazwisko CONTAINS ‘ore’; • Uwaga: Łańcuch użyty w operatorze CONTAINS może zawierać znaki specjalne oznaczające OR i AND.
SELECT porządkowanie wyników • Bez frazy ORDER BY wyniki zapytania ukazują się w porządku nieokreślonym (w porządku wstawienia ich do tablicy). • ORDER BY < nazwa kolumny/numer> [ASC/DESC] • 1. Numer kolumny we frazie ORDER BY oznacza pozycję kolumny na liście wyboru instrukcji SELECT. Trzeba go użyć, jeśli porządkujemy wg wartości wyrażenia. • 2. Domyślnie przyjmowany jest porządek rosnący (ASC). • Przykład 1 Pokaż alfabetycznie dane o pracownikach (nazwisko, imie, pesel). SELECT nazwisko, imie, pesel FROM pracownicy ORDER BY nazwisko ASC;
SELECT - grupowanie • GROUP BY < nazwa kolumny> [HAVING < warunek>] • Fraza ta powoduje powiązanie ze sobą wierszy wynikowych z instrukcji SELECT w grupy wierszy, w których wskazane we frazie GROUP BY kolumny mają tę samą wartość. Następnie każda grupa jest redukowana do pojedynczego wiersza. W tym wierszu występują kolumny z frazy GROUP BY oraz ew. kolumny będące wynikiem funkcji agregujących. Funkcje te wówczas działają na każdej grupie osobno. Następnie eliminowane są wiersze nie spełniające warunku HAVING.
SELECT - grupowanie • Każda kolumna występująca we frazie GROUP BY musi wystąpić na liście wyboru instrukcji SELECT, i na odwrót. • W warunku HAVING może wystąpić funkcja agregująca (ale nie w warunku WHERE!). • Przykład Policz ilość pracowników w każdym mieście. • • SELECT miasto, COUNT(*) FROM pracownicy GROUP BY miasto;
- Slides: 29