SQL FOR DUMMIES

Invented, written and printed by Bambi

1. Podstawowe typy SQL wraz z przykładami

Ogólnie

a) Dla danych znakowych

■char(n) – ciąg n znaków o stałej długości (np. jeżeli kolumna ma określony typ np., char(25)

wpiszemy słowo „kot” – to i tak zostanie ono zapisane pomocą 25 znaków – uzupełnione spacjami);

■varchar(n) – ciąg n znaków o zmiennej długości (np. jeżeli kolumna ma określony typ

varchar(25) i wpiszemy słowo „kot” –zostanie ono zapisane za pomocą 3 znaków)

■varchar(max) – ciąg znaków o zmiennej długości do 2 GB

b) Dla danych liczbowych – liczby całkowite

■tinyint – liczba całkowita z zakresu [0 – 255], przechowywana w jednym bajcie;

■smallint – liczba całkowita z zakresu [–32768 – 32767], przechowywana na dwóch bajtach;

■int – liczba całkowita z zakresu [–2147483648 – 2147483647], przechowywana na czterech 4 bajtach;

■bigint – liczba całkowita z zakresu [–9223372036854775808 – 922337203685477580], przechowywana na ośmiu bajtach.

c)Dla danych liczbowych – liczby z ułamkiem

■real, float – do zapisywania liczb zmiennopozycyjnych;

■decimal, numeric – do zapisywania liczb zmiennopozycyjnych o określonej precyzji;

■money – do zapisywania liczb wyrażających kwoty pieniężne.

d)Dla danych– daty i czasu

■date – do zapisywania dat, np. 2009-08-22;

■time – do zapisywania czasu, np. 19:22:07.2345644;

■datetime – do zapisywania łącznie daty i czasu, np. 2009-08-22 19:22:07.2345644.

getdate-data dzisiejsza

e)Typy różne

■bit– do zapisywania wartości logicznych (true, false lub 0,1);

■varbinary(n) – do zapisywania danych binarnych o długości n bajtów;

■varbinary(max)

– do zapisywania danych binarnych o długości do 2 GB (np. obrazy, dźwięki )

■Timestamp – specjalny znacznik który automatycznie zmienia swoja wartość przy modyfikacji wiersza

Typy danych w praktyce i krótkie objaśnienie

Przykładowa tabela

CREATE TABLE seria_wydawnicza(

id_serii int AUTO_INCREMENT PRIMARY KEY,

seria varchar (50) NOT NULL,

uwagi varchar (200)

)

Tabela z ograniczeniem typu DEFAULT(domyślne dane, tu zakłada, że w twardej oprawie)

CREATE TABLE nosnik(

id_nosnika int AUTO_INCREMENT PRIMARY KEY,

nosnik varchar (50) NOT NULL DEFAULT 'Twarda oprawa',

uwagi varchar (250)

)

Kolumna id_serii została zdefiniowana jako klucz podstawowy tabeli. Dodatkowo wartości w tej kolumnie będą automatycznie generowane przez MYSQL-a. W kolumnie seria nie można przechowywać wartości Null, natomiast w kolumnie uwagi — tak.

1. Kolumna id_ksiazki int AUTO_INCREMENT PRIMARY KEY — definiujemy klucz podstawowy (a więc w kolumnie id_ksiazki nie będzie można przechowywać wartości NULL i powtarzających się wartości), dodatkowo wymuszając automatyczne generowanie wartości klucza.

2. Kolumna tytul varchar (50) NOT NULL — tytuł będzie wymagany, a jego maksymalny rozmiar to 50 znaków.

3. Kolumna isbn char (12) NOT NULL UNIQUE — ISBN będzie nie tylko wymagany, ale również unikatowy. Każda wartość w tej kolumnie będzie liczyła dokładnie 12 znaków.

4. Kolumna liczba_stron smallint NOT NULL CHECK (liczba_stron BETWEEN 50 AND 3000) — obowiązkowa liczba stron książki musi mieścić się w zakresie od 50 do 3000.

Tworzenie tabeli

Krótkie uwagi merytoryczne

NULL

NULL jako symbol reprezentujący brakujące, nieistotne lub nieznane wartości nie powinien być wstawiany do większości kolumn. Na przykład służąca do identyfikacji wierszy tabeli kolumna klucza podstawowego nie powinna zawierać wartości NULL — ponieważ niemożliwe jest odróżnienie jednej wartości NULL od drugiej, nieokreślony klucz podstawowy uniemożliwiałby jednoznaczne identyfikowanie wierszy.

Również kolumny przechowujące podstawowe atrybuty obiektu nie powinny zawierać wartości NULL. W innym przypadku informacje zapisane w bazie danych będą niekompletne i mało przydatne, na przykład niektóre dane towarów bez nazw i cen nie pozwolą przygotować oferty czy zestawienia rocznej sprzedaży.

Klucz podstawowy Primary Key

Tabela może mieć tylko jeden klucz podstawowy (PK — skrót od ang. Primary Key). Ponieważ jest on wykorzystywany do identyfikowania wierszy tej tabeli, wartości klucza podstawowego nie mogą być nieokreślone i muszą być niepowtarzalne.

Niepowtarzalność Unique

W przeciwieństwie do klucza podstawowego, który może być tylko jeden, ograniczenie niepowtarzalności (ang. Unique) możemy zdefiniować dla dowolnej liczby kolumn tabel. Większość serwerów bazodanowych automatycznie indeksuje kolumny, w których nie można zapisywać duplikatów danych.

Ograniczenie niepowtarzalności w odróżnieniu od ograniczenia klucza podstawowego nie uniemożliwia zapisywania wartości NULL. Serwery jednak w różny sposób traktują ją w ograniczeniu niepowtarzalności, na przykład serwer MySQL uznaje wartości NULL za różne i w konsekwencji w kolumnie z nałożonym ograniczeniem niepowtarzalności wartość NULL może się powtórzyć

Wartość domyślna DEFAULT

Każdej kolumnie tabeli można nadać jedno ograniczenie wartości domyślnej (ang. Default). Tego typu ograniczenia sprawdzane są tylko podczas wstawiania wierszy. Jeżeli użytkownik nie wstawi danych do kolumny z ograniczeniem wartości domyślnej, serwer bazodanowy zrobi to za niego.

Warunek logiczny CHECK

Najbardziej uniwersalnym zawężeniem jest warunek logiczny (ang. Check), który musi być prawdziwy, żeby operacja wstawiania, modyfikowania czy usuwania danych zakończyła się powodzeniem. Dla każdej kolumny tabeli można zdefiniować wiele warunków, można też tworzyć złożone warunki za pomocą operatorów algebry Boole’a: NOT, AND oraz OR. W ramach warunku logicznego niemożliwe jest odwoływanie się do tabel innych niż ta, dla której warunek został zdefiniowany, oraz używanie podzapytań.

Klucz obcy Foreign Key

Ograniczenie klucza obcego (ang. Foreign Key) pozwala na automatyczne sprawdzanie spójności danych przechowywanych w powiązanych ze sobą tabelach. Jeżeli tabele powiązane są związkiem typu „jeden do wielu” (np. jeden towar składa się z wielu części, ale ta sama część może być użyta tylko w jednym produkcie), do tabeli podrzędnej (w tym przypadku do tabeli Produkcja.Części) należy dodać kolumnę, w której zapisane zostaną identyfikatory towarów. Żeby dane były spójne, wartości klucza obcego:

1. muszą odpowiadać jednej z wartości powiązanego z nim klucza podstawowego (przy każdej części musi być zapisany identyfikator istniejącego towaru, w którym ta część jest użyta)

2. lub być nieokreślone (oznacza to, że dana część nie jest używana w żadnym z towarów).

Przykłady tworzenia tabel

1 2 3 4 5 6	CREATE TABLE t_nowa_tabela ( lp int primary key, miesiac smalldatetime check (miesiac > '01-01-2010'), region nvarchar(10) default '' )

Tworzenie autonumerowania w ms sql odbywa się poprzez dodanie IDENTITY(1,1) <pierwsza cyfra odnosi się do miejca, z którego autonumerowanie zaczynamy, kolejna, o ile autonumerujemy>

1 2 3 4	CREATE TABLE t_test ( id int IDENTITY(1,1), dane char(20) )

Przykład tworzenia klucza podstawowego

1 2 3 4	CREATE TABLE Klienci (ID integer PRIMARY KEY, Last_Name varchar(30), First_Name varchar(30));

Klucz obcy (dwa sposoby)

1 2 3 4 5	CREATE TABLE Zamowienia (zam_ID integer primary key, zam_Date datetime, klient_ID integer <foreign key> references Klienci(ID), kwota double(10,2));

Tworzenie kolumny z unikalnymi rekordami:

1 2 3 4 5 6	CREATE TABLE Customer (SID integer Unique, Last_Name varchar (30), First_Name varchar(30));

Jeśli chcemy by kolumn nie przyjmowała wartości null używamy:

1 2 3 4	CREATE TABLE klienci (ID integer NOT NULL, Last_Name varchar (30) NOT NULL, First_Name varchar(30));

Możemy również określić domyślną wartość w rekordzie oraz określić jakie wartości ma przyjmować dane pole w tabeli

1 2 3 4	CREATE TABLE t_test ( id int  not null, id_I int defalut 0 ) data smalldatetime CHECK (data > getdate()) )
Przykład tabeli

CREATE TABLE Agents

(

Id INT PRIMARY KEY NOT NULL,

FirstName VARCHAR(30),

SecondName VARCHAR(50),

EmploymentDate DATETIME2,

IsActive BIT,

ActivityArea GEOGRAPHY,

AWPersonalID UNIQUEIDENTIFIER DEFAULT NEWID()

)

Wprowadzanie danych

1. Uwagi ogólne

INSERT

Polecenie INSERT pozwala dodawać nowe dane do instancji bazy danych. Polecenie to występuje w dwóch postaciach:

INSERT INTO table_name SET a = value1, b = value2, ...;

INSERT INTO table_name VALUES (value1, value2, ...);

Pierwsza postać polecenia pozwala określić wartości poszczególnych atrybutów poprzez ich nazwę. W drugiej postaci wykorzystywana jest domyślna kolejność atrybutów, zdefiniowana w schemacie określonej relacji.

Przykład

Dla relacji Osoby(Imie, Nazwisko, PESEL), polecenia:

INSERT INTO Osoby SET Imie = "Jan", Nazwisko = "Kowalski", PESEL = 123654;

INSERT INTO Osoby VALUES ("Jan", "Kowalski", 123654);

powodują dodanie do bazy nowej informacji o osobie, której imię to “Jan”, nazwisko “Kowalski”, a pesel 123654.

1. Drugi sposób= na prezentacji

Rozwiązane zadania

1. Zadanie o BIBLIOTECE:

create database Biblioteka

use Biblioteka

create table Wydawnictwa(

id_wyd tinyint not null primary key identity(1,1),

nazwa_wyd varchar(50),

miasto nvarchar(50) DEFAULT 'Warszawa')

create table Książki(

id_książki tinyint not null primary key identity(1,1),

id_wyd tinyint not null foreign key references wydawnictwa(id_wyd),

tytuł varchar(50),

rok_wyd datetime not null check (year(getdate())-year(rok_wyd)<= 100),

nazwa_autora varchar(70),

status varchar(20) check ( status='dostępna' or status='wypożyczona'))

insert into Wydawnictwa values ('Ona', DEFAULT)

insert into Wydawnictwa values ('Czas', DEFAULT)

insert into Wydawnictwa values ('Kot', DEFAULT)

insert into Książki values (2, 'KONIE', '1980', 'KRIS', 'dostępna' )

insert into Książki values (1, 'Ostatnia pisenka', '1996', 'Tom', 'wypożyczona' )

select *

from Wydawnictwa w join Książki k on w.id_wyd=k.id_wyd

1. zadanie operacje

create database Operacje

use Operacje

create table Pacjenci(

id_pacj tinyint not null primary key identity(1,1),

imie varchar(50),

nazwisko varchar(50),

data_ur datetime not null check (year(getdate())-year(data_ur)>=18))

create table [Przeprowadzone zabiegi] (

id_op tinyint not null primary key identity(1,1),

id_pacj tinyint not null foreign key references Pacjenci(id_pacj),

data DATE default getDATE(),

opis varchar(100),

cena money check (cena > 0),

rezultat varchar(10) check ( rezultat='pozytywny' or rezultat ='negatywny'),

rok_wiz_kontrolna DATE default EOMONTH ( getdate(), 24 ))

select*

from [Przeprowadzone zabiegi] pz join Pacjenci p on pz.id_pacj=p.id_pacj

insert into Pacjenci values ('Jan', 'Kowalski','1960-05-12')

insert into Pacjenci values ('Ola', 'Sok','1940-12-31')

insert into Pacjenci values ('Tom', 'Pies','1994-01-02')

select*

from Pacjenci

insert into [Przeprowadzone zabiegi] values (1, '2003-05-13', 'wyrostek', '233', 'pozytywny', default)