Oskar Skibski, grupa nr 4, sala 2043

Bazy danych 2015/16

Laboratorium 1 (05.10.2015) - SQL / PODSTAWY

Na dzisiejszych zajęciach nauczymy się korzystać z SQL*Plusa oraz poznamy podstawy języka SQL.

Praca w SQL*Plus

Będziemy pracować w programie SQL*Plus pod Linuxem (windows: o:\orant\bin\sqlplus). Jako, że jest on bardzo toporny będziemy uruchamiać go z uzyciem rlwrap:

Bez tego nie moglibyśmy nawet przywołać poprzednio napisanej linijki strzałką w górę! Rlwrap pozwala także na autouzupełnianie na podstawie poprzednio napisanych słów (TAB) oraz wyszukiwanie wcześniej napisanych komend (CTRL-R).

Login jest zgodny z Państwa loginem pocztowym (czyli ma format: xy123456), hasło to x. Uwaga! Po UDANYM zalogowaniu program pisze, że hasło jest przeterminowane i prosi o jego zmianę. Jest to ważny moment w którym nie można spanikować.

Po zalogowaniu możemy przystąpić do pisania zapytań SQL. Zmęczeni pracą z programu wychodzimy komendą quit lub exit.

SQL*Plus ma parę przyjaznych opcji o których warto wiedzieć:

• aby tabelki się nie zawijały można w nim ustawić długość linii komendą set linesize 100
• pomoc do danej komendy możemy wyświetlić pisząc help komenda
• komenda desc nazwa_tabeli pokazuje opis tabeli
• można ustawić plik odpalany przy każdym logowaniu, np. taki: login.sql

Na dzisiejszych zajęciach będziemy pracować na bazie demobld.sql. Aby odpalić w SQL*Plusie zapytanie zapisane w pliku plik_do_zaladowania.sql przenosimy go do katalogu z którego uruchomiliśmy program i używamy komendy:

Dla plików z rozszerzeniem .sql dodanie rozszerzenia nie jest potrzebne.

SELECT * FROM emp;

Aby wybrać niektóre informacje z tabeli używamy polecenia SELECT:

SELECT kolumny FROM tabela WHERE warunki;

Przykładowo:

SELECT * FROM emp;
SELECT ename AS imie, sal AS pensja FROM emp WHERE job = 'MANAGER' ORDER BY pensja; 

Drugie zapytanie wybiera tylko managerów i sortuje ich podług pensji. Kolumnom można zmieniać nagłówki w wynikowej tabeli (jak powyżej) pisząc kolumna AS nowa_nazwa. Można też pominąć AS.

ORDER BY sortuje wiersze wyniku rosnąco względem podanej kolumny. Aby posortował malejąco należy dodać słowo DESC po nazwie kolumny. Można także podać więcej kolumn rozdzielając je przecinkami. Uwaga! Jeżeli sortowanie nie jest podane explicite wówczas nie możemy nic zakładać o kolejności wierszy.

Aby uniknąć powtórzeń identycznych wierszy listę kolumn poprzedzamy słowem DISTINCT:

SELECT DISTINCT deptno FROM emp; # wybiera numery departamentów

W liście kolumn po SELECT, ale także w warunkach po WHERE i ORDER możemy używać wbudowanych funkcji zarówno arytmetycznych (+, -, *, /, abs, ...) jak i napisowych (concat, lower, substr, ...). Cała lista funkcji znajduje się na stronie Oracle: funkcje SQL.

W warunkach możemy też używać operatorów porównania (<, >, =, <>, ...), spójników AND, OR, a także warunku IN i BETWEEN. Wygodną konstrukcją jest też LIKE pattern, która dopasowuje napis do prostego wyrażenia w którym % oznacza dowolny ciąg znaków, a _ oznacza jeden dowolny znak. Przykładowo:

SELECT * FROM emp WHERE job LIKE '%A_ER';

A skąd mamy wiedzieć jakie tabele mamy utworzone w bazie? Ich nazwy musimy oczywiście pobrać z tabeli, a dokładnie specjalnej tabeli user_tables, która przechowuje o nich informacje.

SELECT table_name FROM user_tables;

INSERT INTO dept VALUES (50, 'SUPPLY', 'SCRANTON');

Aby dodać nowe rekordy wykonujemy polecenie INSERT:

INSERT INTO tabela VALUES (wartość, wartość, ...);

Możemy dodać nowy rekord podając tylko niektóre wartości, wówczas pozostałe ustawią się domyślnie (bardzo częsty przypadek w realnych zastosowaniach w których definiujemy autoincrementujący się indeks):

INSERT INTO emp (empno, ename, job, sal, deptno) VALUES ('8000', 'HEISENBERG', 'CHEMIST', 10000, 50);

Ponadto możliwe jest wstawienie całego wyniku zapytania, pod warunkiem, że typy kolumn i ich ilość będą zgadzały się z tabelą do jakiej wstawiamy. Przykładowo jeżeli żony wszystkich sprzedawców zostały zatrudnione do rozwożenia pizzy możemy je wstawić wykonując zapytanie:

INSERT INTO emp (empno, ename, job, hiredate, sal, deptno) 
    (SELECT empno+1000, CONCAT('MRS.', ename), 'PIZZA GUY', sysdate, 500, 10 
            FROM emp WHERE job = 'SALESMAN');

UPDATE emp SET sal = sal/2;

Wiersze w tabeli modyfikuje polecenie UPDATE:

UPDATE tabela SET pole = wartość, pole = wartość, ... WHERE warunek;

Przykładowo:

UPDATE emp SET sal = sal/2;   # :(
UPDATE emp SET job = 'CLERK', deptno = 30 WHERE ename = 'JONES';

Warto zwrócić uwagę na to, że po prawej stronie równości także może występować nazwa kolumny.

DELETE FROM emp WHERE deptno = 30;

Kasowanie rekordów także jest bardzo proste, wystarczy podać nazwę tabeli i warunek na wiersze do skasowania.

DELETE FROM tabela WHERE warunek;

Przykładowo kiedy rozwiązujemy departament z Chicago (30) wykonujemy zapytania:

DELETE FROM emp WHERE deptno = 30;
DELETE FROM dept WHERE deptno = 30;

CREATE TABLE ...

Zapytanie tworzące tabelę w SQL ma nastepującą postać:

CREATE TABLE tabela (
  kolumna_1 typ_danych [dodatkowe warunki],
  kolumna_2 typ_danych [dodatkowe warunki],
  ...
  kolumna_n typ_danych [dodatkowe warunki],
  [dodatkowe warunki]
);

W Oracle SQL istnieje 5 podstawowych typów danych:

• CHAR(n) - typ napisowy o stałej długości n
• VARCHAR2(n) - typ napisowy o zmiennej długości nieprzekraczającej n
• NUMBER(n,k) - typ liczbowy zawierający n cyfr, z czego k znajduje się po kropce; przykładowo liczba 123,456 w polu NUMBER(5,2) zostanie zapisana jako 123,45
• DATE - typ daty i czasu
• LONG - typ bardzo długiego napisu (UWAGA! w oraclu long to nie duża liczba)

Na kolumnach można określić dodatkowe warunki. Przede wszystkim można określić że wartość w danej komórce musi być zdefiniowana - NOT NULL. Można też napisać że wartość (jeżeli jest zdefiniowana) musi być unikalna - UNIQUE. Bardzo ważne jest też określenie w tabeli klucza głównego - PRIMARY KEY - który pozwala unikalnie zidentyfikować dany wiersz. Jest on dzięki temu bardzo dobrym kandydatem na klucz zewnętrzny wiążacy z tą tabelą w innej tabeli.

Kolumnie tabeli można także ustawić inne warunki. Możemy na przykład ustawić wartość domyślną (zwykle jest to NULL) poleceniem DEFAULT, albo zupełnie dowolny test wartości poleceniem CHECK.

Zbierając powyższe informacje możemy dokładniej zdefiniować tabelę pracowników (warto porównać z aktualną).

CREATE TABLE emp (
  empno NUMBER(4) PRIMARY KEY,
  ename VARCHAR2(10) NOT NULL UNIQUE,
  job VARCHAR2(9) NOT NULL,
  mgr NUMBER(4),
  hiredate DATE DEFAULT '01-JAN-81',
  sal NUMBER(7,2) CHECK (sal > 500 AND sal < 5000),
  comm NUMBER(7,2) DEFAULT 0,
  deptno NUMBER(2) NOT NULL
);

Pod definicją kolumn możemy dodać dodatkowe warunki, na przykład unikalność pary kolumn (UNIQUE(job, deptno) oznacza, że na danym stanowisku w danym departamencie jest tylko jeden pracownik).

Jeżeli coś źle zdefiniujemy, np. ustawimy za krótkie pole tekstowe, możemy to zmienić poleceniem ALTER TABLE:

ALTER TABLE emp MODIFY (ename VARCHAR2(15));
ALTER TABLE emp ADD (dunno NUMBER(3));
ALTER TABLE emp DROP COLUMN dunno;

Tabelę kasuje polecenie DROP TABLE tabela.