[[
✎ pl:dydaktyka:ztb:2011:projekty:sql:start
]]
aiWiki
Pokaż stronę
Ostatnie zmiany
Indeks
Zaloguj
Ta strona jest tylko do odczytu. Możesz wyświetlić źródła tej strony ale nie możesz ich zmienić.
====== Standardy SQL ====== SQL jest językiem zapytań wykorzystywanym do tworzenia i modyfikacji baz danych oraz dodawania i pobierania danych z baz danych. W 1986 SQL stał się standardem wspieranym przez ISO. W początkowych wersjach specyfikacja SQL dawała wiele swobody różnym implementacjom. Z czasem zaszła potrzeba ściślejszego zdefiniowania standardu tak, aby możliwa była współpraca z wieloma różnymi bazami danych. W ten sposób został określony standard SQL-92, który do dziś jest podstawą dla wielu rozwiązań komercyjnych. Formalną nazwą dla standardu SQL jest ISO/IEC 9075. Do tej pory zostało zatwierdzonych 6 wersji przy czym ostatnia aktualizacja miała miejsce w 2008 roku (formalna nazwa: ISO/IEC 9075:2008 lub SQL:2008). Poprzednie wersje to SQL:2003, SQL:99, SQL-92. Każde kolejne wydanie standardu zastępuje poprzednie, przez co funkcjonalności dostępne w różnych wersjach nie zawsze znajdują się w kolejnych. Standardy SQL:2003 oraz SQL:2008 zostały podzielone na części: * ISO/IEC 9075-1 Framework (SQL/Framework) * ISO/IEC 9075-2 Foundation (SQL/Foundation) * ISO/IEC 9075-3 Call Level Interface (SQL/CLI) * ISO/IEC 9075-4 Persistent Stored Modules (SQL/PSM) * ISO/IEC 9075-9 Management of External Data (SQL/MED) * ISO/IEC 9075-10 Object Language Bindings (SQL/OLB) * ISO/IEC 9075-11 Information and Definition Schemas (SQL/Schemata) * ISO/IEC 9075-13 Routines and Types using the Java Language (SQL/JRT) * ISO/IEC 9075-14 XML-related specifications (SQL/XML) ===== Standardy uwzględniane w przeprowadzanych badaniach ===== * SQL:2003 * SQL:2008 * (opcjonalnie) SQL:99 ===== Systemy DBMS ===== * Niekomercyjne * PostgreSQL * MySQL * Komercyjne * Oracle * Lekkie * SQLite ====== SQLite ====== Kompaktowy sytem zarządzania bazą danych obsługujący jezyk SQL. Jest dostępny w postaci małej biblioteki zaimplemntowanej w języku C. Silnik bazy danych w SQLite w przeciwieństwie do większości dostępnych rozwiązań nie potrzebuje być uruchamiany w osobnym procesie - co jest bardzo korzystnym rozwiązaniem szczególnie dla systemów wbudowanych. Biblioteka SQLite staje się integralną częścią aplikacji. SQLite używa dynamicznej i słabo typizowanej składni SQL. Pozwala na równoległe odczyty. Zapisy do bazy danych odbywają się pojedyńczo. Dostępne są również API w innych językach niż C: * ActionScript * Perl * PHP * Ruby * C<nowiki>++</nowiki> * Delphi * Python * Java * Tcl * Visual Basic * platformy .NET Dostępny jest też interfejs powłokowy. Baza może łączyć się przez ODBC. Baza SQLite przechowywana jest na dysku (lub w pamięci) w postaci B-drzew. Dla każdej tabeli i każdego indeksu istnieje osobne drzewo. SQLite oparty jest głównie na standardzie SQL:92 - implementuje większość jego funkcjonalności. Baza udostępnia transakcje ACID. ===== Obsługiwane funkcjonalności: ===== * klucze obce * widoki * transakcje * częściowo wyzwalacze(trigger) * definiowanie własnych funkcji * widoki ===== Niezaimplementowane elementy z standardu SQL:92 ===== * RIGHT and FULL OUTER JOIN * Pełne wsparcie dla ALTER TABLE - DROP COLUMN, ALTER COLUMN, ADD CONSTRAINT nie są wspierane * Pełne wsparcie dla wyzwalaczy(trigger) * Zapisywanie do widoków * GRANT and REVOKE - nie ma potrzeby implementacji dla wbudowanego silnika bazy danych ===== Przykłady nieobsługiwanych zapytań SQL ===== * Nie można usunąć kolumny <code sql>ALTER TABLE tabela1 DROP ShortName;</code> * Nie można usunąć ograniczeń <code sql>ALTER TABLE myTable DROP CONSTRAINT defPK</code> * Brak wsparcia dla funkcji i podzapytań w klauzulach ograniczeń <code sql>CHECK (field > SELECT MIN(field) FROM table)</code> * Brak obsługi EXTRACT dla typu TIMESTAMP <code sql>SELECT EXTRACT(YEAR FROM TIMESTAMP '2013-07-02');</code> * Brak obsługi STARTING WITH <code sql>WHERE <Column> STARTING WITH "Text"</code> ===== Zastosowania SQLite ===== SQLite jest bazą danych używaną w sytuacjach gdzie prostota ma większe znaczenie. Przykładowe zastosowania to między innymi: * Format pliku aplikacji * W urządzeniach mobilnych np. wykorzystywany przez system Android * Strony internetowe, które głównie odczytują dane z bazy * Wewnętrzne i tymczasowe bazy danych aplikacji. * Zamienniki dla komercyjnych produktów podczas testowania i prezentacji Możliwość utworzenia bazy w pamięci pozwala efektywnie wykonywać operacje wyszukiwania, czy filtrowania na dużych zbiorach danych. Może być lepszym rozwiązaniem niż standardowe operacje na kolekcjach oferowane przez dany język. Innym często spotykanym zastosowaniem jest sprawdzenie poprawności działania ORM dla modelu danych. SQLite pozwala na utworzenie tymczasowej bazy w pamięci, oraz przetestowanie odwzorowania. W trakcie budowy aplikacji może posłużyć za lokalną bazę danych wykorzystaną w testach jedostkowych. W przypadku aplikacji desktopowych plikowa baza danych może być wygodną alternatywą dla plików XML lub własnych formatów. ====== PostgreSQL ====== PostgreSql jest jednym z najpopularniejszych obiektowo-relacyjnych systemów zarządzania bazą danych typu OpenSource. Jendnym z założeń systemu jest zgodność ze standardem SQL. Dostępny jest na wielu platformach między innymi: * Linux * FreeBSD * Solaris * Windows * Mac OS X Rdzeń PostgreSQL pokrywa 1, 2, 9, 11 i 14 [[#Standardy SQL|część standardu SQL]]. Część 3 pokryta jest przez sterownik ODBC, a 13 implementuje plug-in PL/Java. PostgreSQL wspiera większość głównych cech SQL:2008. Dodatkowo system oferuje wiele dodatkowych funkcji. ===== Główne cechy PostgreSQL ===== * możliwość pisania procedur składowanych w różnych językach programowania * PL/SQL * PL/Python * SQL * wiele innych * obsługa wielu typów indeksów * B-drzewo * Hash * R-drzewo * wyzwalacze * MVCC. Mechanizm do zarządzania transacjami * reguły, czyli elementy aktywne stosowane do roszerzania widoków * możliwość zastosowania wielu obiektowych rozszerzeń ===== Niezaimplementowane funkcje standardu ===== Standard SQL definiuje pewien podzbiór funkcji (Core), które muszą być zaimplementowane, aby zachować z nim zgodność. Pozostałe cechy są opcjonalne. PostgreSQL obecnie nie wspiera 14 głównych (Core) funkcji. Dwie z nich odnoszą się do modułów działających po stronie klienta, przez co nie są wymagane jeśli implementacja zapewnia wbudowany język np(ECPG). Brakujące funkcjonalność: * przywilej USAGE (E081-09): dodanie domenom przywileju USAGE * Zapytania aktualizujące z podzapytaniami(E153): zakłada możliwość aktualizacji widoków, wymaga aby widoki były aktualizowane nawet jeśli zawierają podzapytanie w klauzuli WHERE * CREATE VIEW: WITH CHECK OPTION(F311-04): zakłada możliwość aktualizacji widoków, wymaga CHECK OPTION * Podstawowe oznaczanie(F812): zakłada że implementacja informuje albo ostrzega, że wykonywane zapytanie nie jest zgodne z standardem SQL. W przypadku PostgreSQL informuje o użyciu rozszerzenia * Rozłączne typy danych(S011): pozwala na definiowanie typów użytkownika na podstawie już istniejących np <code sql>CREATE TYPE new AS old;</code> * Proste wywoływanie procedur przez SQL(T321): * zezwalać na bezpośrednie zapytanie SQL jako ciało procedury, zamiast stosowania <code sql>AS $$ ... $$</code> * domyślnie <code sql>SQL LANGUAGE</code> * klauzula <code sql>SPECYFIC xyz</code> pozwala na jawne przypisywanie specyficznej nazwy do procedury, która może być poźniej wykorzystana jako alias * klauzule <code sql>DETERMINISTIC / NOT DETERMINISTIC</code> * klauzule <code sql>CONTAINS SQL / READS SQL DATA / MODIFIES SQL DATA</code> * usuwanie funkcji poprzez 'specyficzną nazwę' <code sql>DROP SPECIFIC FUNCTION specific_name;</code> * procedury składowane definiowane przez użytkownika(T321-02): nowa komenda <code slq>CREATE PROCEDURE </code> która robi to samo co <code sql>CREATE FUNCTION .. RETURNS void</code> oraz odpowiednio komenda <code sql>DROP PROCEDURE</code> * wyrażenie CALL(T321-04): dodaje nową komendę <code sql>CALL procname()</code> która działa tak samo jak <code sql>SELECT procname()</code> ale wymaga aby procname() nie zwracało wartości * wyrażenie RETURN(T321-05): nowa komenda RETURN, którą można wywołać wewnątrz funkcji SQL np. zamiast: <code sql>CREATE FUNCTION name(args) RETURNS type LANGUAGE SQL AS $$ SELECT something $$;</code> napiać: <code sql>CREATE FUNCTION name(args) RETURNS type LANGUAGE SQL RETURN something;</code> Powyższe brakujące funkcjonalności odnoszą się do głównych(Core) elementów standardu SQL. Poza wymaganymi cechami standard definiuje wiele dodatkowych funkcjonalności. Wykaz zarówno [[http://www.postgresql.org/docs/9.0/interactive/features-sql-standard.html|zaimplementowanych ]]jak i [[http://www.postgresql.org/docs/9.0/interactive/unsupported-features-sql-standard.html|niezaimplementowanych]] funkcji standardu SQL:2008 można znaleźć w oficjalnej dokumentacji PostgreSQL ====== MySQL ===== MySQL to jeden z najpopularniejszych systemów RDBMS. Dostępny jest nie tylko oprogramowanie darmowe, istnieją też licencje komercyjne. Bazy MySQL są wykorzystywane przez wiele projektów open source, takich jak Joomla, WordPress czy phpBB. MySQL jest częścią rozwiązania LAMP (Linux/Apache/MySQL/Perl/PHP/Python), które pozwala łatwo i niewielkim kosztem uruchomić serwer webowy. ===== Różnice między MySQL a standardem ===== ==== SELECT INTO TABLE ==== MySQL nie obsługuje polecenia SQL <code sql>SELECT ... INTO TABLE</code>W zamian należy użyć polecenia <code sql>INSERT ... INTO TABLE</code> Polecenie ''SELECT ... INTO'' jest obsługiwane w wypadku zmiennych użytkownika. ==== UPDATE ===== Jeśli w poleceniu zawierającym ''UPDATE'' znajdzie się odwołanie do kolumny z tabeli, która jest aktualizowana, to zostanie wykorzystana aktualna wartość kolumny, a nie wartość sprzed wykonania polecenia. Dla tabeli ''example'' ^example_column_1 ^example_column_2 ^ | 2 | 4 | | 3 | 2 | wykonanie polecenia <code sql>UPDATE example SET example_column_1 = 8, example_column_2 = example_column_1 + 2</code> da efekt w postaci ^example_column_1 ^example_column_2 ^ | 8 | 10 | | 8 | 10 | Według standardu SQL po takim poleceniu tabela powinna mieć postać ^example_column_1 ^example_column_2 ^ | 8 | 6 | | 8 | 4 | ==== Transakcje i operacje atomiczne ==== === InnoDB === Silnik InnoDB zapewnia pełną zgodność z ACID. Obsługa błędów w trakcie transakcji różni się nieco od opisanej w standardzie SQL. === MyISAM === Silnik MyISAM działa analogicznie do trybu <code>autocommit=1</code> Do programisty należy decyzja, czy bardziej odpowiada mu bezpieczeństwo przetwarzania transakcyjnego, czy wydajność operacji atomicznych. Decyzja może być podejmowana na poziomie poszczególnych tabel. Brak przetwarzania transakcyjnego można "obejść" stosując blokady <code sql>LOCK_TABLES</code> <code sql>UNLOCK_TABLES</code> oraz sprawdzając odpowiednie warunki, specyficzne dla przetwarzanych danych. ==== Klucze obce ==== Obecnie MySQL przy wykorzystaniu silnika InnoDB wspiera operacje związane z ograniczeniami dla kluczy obcych, m. in. takie jak ''CASCADE'', ''ON DELETE'', ''ON UPDATE''. W wypadku innych silników, MySQL rozpoznaje składnię ''FOREIGN KEY'' w poleceniu ''CREATE TABLE'', ale nie korzysta z niej, ani nie zapisuje nigdzie odpowiednich informacji. Planowane jest zaimplementowanie przechowywania tych informacji w pliku opisującym tabelę, tak aby można było je odzyskać korzystając z polecenia ''mysqldump'' albo z ODBC. W dalszej przyszłości planuje się wprowadzenie obsługi ograniczeń dla kluczy obcych w silniku MyISAM. ==== Komentarze ==== Standard SQL wykorzystuje komentarze w stylu C <code>/* komentarz */</code> MySQL obsługuje takie komentarze, co więcej, pozwala na wykonywanie kodu specyficznego dla MySQL umieszczonego w takich komentarzach, dzięki czemu można osadzać kod specyficzny dla MySQL w skryptach, które będą wykonywane również na innych serwerach baz danych. Standard SQL zezwala również na komentarze rozpoczynające się od ''<nowiki>--</nowiki>'' <code sql>--komentarz SQL</code> MySQL wykorzystuje komentarze rozpoczynające się od '#' <code sql>#komentarz MySQL</code> Wyrażenia rozpoczynające się od ''<nowiki>--</nowiki>'' są traktowane przez MySQL jako komentarze, ale tylko jeśli po symbolach ''<nowiki>--</nowiki>'' znajduje się np. spacja czy znak nowej linii. To wymaganie uzasadnione jest przypadkami takimi jak ten: Chcemy wykonać polecenie <code sql>UPDATE account SET credit=credit-payment</code> Jeżeli ''payment'' ma wartość ujemną, np. -1, po podstawieniu otrzymamy kod <code sql>UPDATE account SET credit=credit--1</code> Zgodnie ze standardem SQL ''<nowiki>--</nowiki>'' oznacza początek komentarza, czyli ostatecznie wykonałoby się polecenie <code sql>UPDATE account SET credit=credit</code> które nie zmieniłoby wartości kolumny ''credit''. Dodatkowo narzędzie ''mysql'' dla linii komend ignoruje linie rozpoczynające się od ''<nowiki>--</nowiki>'' ===== Rozszerzenia standardu SQL zaimplementowane w MySQL ===== Jak już napisano w sekcji dotyczącej obsługi [[pl:dydaktyka:ztb:2011:projekty:sql:start#komentarze|komentarzy]], MySQL pozwala na korzystanie z funkcjonalności specyficznych dla tej bazy danych w taki sposób, aby skrypty wykonywały się również na innych serwerach baz danych SQL. Kod specyficzny dla MySQL należy wstawić do komentarza w następujący sposób <code sql>/*! MySQL-specific code */</code> Wstawienie liczby zaraz za wykrzyknikiem spowoduje wykonanie takiego kodu tylko przez odpowiednie wersje serwera <code sql>/*!32302 some other MySQL-specific code */</code> Taki kod uruchomi się tylko na serwerach MySQL w wersji 3.23.02 lub wyższej. ==== Organizacja danych na dysku twardym ==== Każda baza danych jest mapowana przy pomocy odpowiedniej struktury plików i katalogów. Konsekwencjami takiego działania są: * wrażliwość na duże i małe litery w nazwach baz i tabel na systemach, które są wrażliwe na duże/małe litery * możliwość kopiowania, usuwania, przesuwania czy zmiany nazw tabel obsługiwanych przez silnik MyISAM; zaleca się zmianę nazw z poziomu serwera MySQL a nie systemu plików ==== Składnia języka ==== * stałe znakowe mogą być zawarte zarówno w podwójnych, jak i pojedynczych cudzysłowach; w trybe ''ANSI_QUOTES'' działają tylko pojedyncze * "\" jest traktowany jako 'escape symbo' * W wyrażeniach SQL można odwoływać się do tabel z innych baz danych przy pomocy składni ''inna_baza_danych.przykladowa_tabela'' ==== Składnia wyrażeń SQL ==== * polecenia ''ANALYZE TABLE'', ''CHECK TABLE'', ''OPTIMIZE TABLE'', ''REPAIR TABLE'' * polecenia ''CREATE DATABASE'', ''DROP DATABASE'', ''ALTER DATABASE'' * polecenie ''DO'' * polecenie ''EXPLAIN SELECT'' * polecenia ''FLUSH'' i ''RESET'' * polecenie ''SET'' * polecenie ''SHOW'', można również korzystać z zapytania ''SELECT'' i tabel ''INFORMATION_SCEHMA'' * polecenie ''LOAD DATA INFILE'', podobne do analogicznego polecenia z bazy danych Oracle * wykorzystanie polecenia ''RENAME TABLE'' * polecenie ''REPLACE'' zamiast łączenia poleceń ''DELETE'' oraz ''INSERT'' * polecenia ''CHANGE column'', ''DROP column'', ''DROP INDEX'', ''IGNORE'', ''RENAME'' w wyrażeniach ''ALTER TABLE''; możliwość wykorzystania wielu klauzul ''ADD'', ''ALTER'', ''DROP'' oraz ''CHANGE'' * wykorzystanie klauzul ''INDEX'' oraz ''KEY'' w poleceniu ''CREATE TABLE'' * polecenie ''TEMPORARY'' * możliwość usuwania wielu tabel w jednym poleceniu ''DROP TABLE'' * wykorzystanie ''ORDER BY'' oraz ''LIMIT'' w poleceniach ''UPDATE'' oraz ''DELETE'' * składnia ''INSERT INTO tabela SET kolumna = ...' * ''STRAIGHT_JOIN'' * ==== Typy danych ==== * ''MEDIUMINT'', ''SET'', ''ENUM'', dodatkowe odmiany typów danych ''BLOB'' oraz 'TEXT'' * atrybuty ''AUTO_INCREMENT'', ''BINARY'', ''NULL'', ''UNSIGNED'', ''ZEROFILL'' ==== Funkcje i operatory ==== * MySQL ma zaimplementowane aliasy dla wielu funkcji, w celu ułatwienia migracji z innych systemów RDBMS, np. wszystkie funkcje związane z napisami obsługują zarówno składnię SQL, jak i ODBC. * operatory ''||'' oraz ''&&'' są zawsze interpretowane jako alternatywa i koniunkcja * jeśli ''lista_wartosci'' zawiera więcej niż jeden element, można wykorzystać funkcję ''COUNT(DISTINCT lista_wartosci)'' * porównywanie napisów jest wrażliwe na aktualnie wybrany zestaw znaków; w celu porównywania według kodów znaków należy rzutować napisy na typ ''BINARY'' lub deklarować kolumny dla napisów z takim atrybutem * "%" jest synonimem ''MOD()''; jest to udogodnienie dla programistów C, zapewnia także zgodność z PostgreSQL * operatory porównania mogą być wykorzystywane w liście kolumn wyjściowych * operator ''LIKE'' obsługuje wartości liczbowe * operatory ''REGEXP'' i ''NOT REGEXP'' rozszerzające funkcjonalność domyślnych operatorów wyrażeń regularnych * funkcje ''CONCAT()'' i ''CHAR()'' mogą przyjmować dowolną liczbę argumentów (co najmniej jeden!) * funkcje ''BIT_COUNT()'', ''CASE'', ''ELT()'', ''FROM_DAYS()'', ''FORMAT()'', ''IF()'', ''PASSWORD()'', ''ENCRYPT()'', ''MD5()'', ''ENCODE()'', ''DECODE()'', ''PERIOD_ADD()'', ''PERIOD_DIFF()'', ''TO_DAYS()'', ''WEEKDAY()'' * funkcja ''TRIM()'' pozwala usuwać podciągi znaków, nie tylko pojedyncze znaki * funkcje ''STD()'', ''BIT_OR()'', ''BIT_AND()'', ''BIT_XOR()'' oraz ''GROUP_CONCAT()'' w wyrażaniu ''GROUP BY'' ====== Oracle ===== System zarządzania bazami danych Oracle był pierwszym komercyjnym RDBMS wspierającym język SQL. Aktualnie Oracle konkuruje z bazami Microsoft SQL Server oraz IBM DB2 UDB. Oracle w wersji 11g Release 2 zapewnia zgodność z większością elementów standardu SQL:2008. ===== Core SQL:2008 ===== Zgodność zapewniona jest przez następujące produkty: * Oracle Database server * Pro*C/C++, release 9.2.0 * Pro*COBOL, release 9.2.0 * Pro*Fortran, release 1.8.77 * SQL Module for Ada (Mod*Ada), release 9.2.0 * Pro*COBOL 1.8, release 1.8.77 * Pro*PL/I, release 1.6.28 * OTT (Oracle Type Translator) Zgodność z poszczególnymi punktami części Core jest zaimplementowana na jednym z 5 poziomów: * pełna zgodność * częściowa zgodność: funkcjonalności zgodne ze standardem mają taką samą składnię i semantykę jak opisana w standardzie * rozszerzona zgodność: obsługiwana jest semantyka ze standardu, ale funkcjonalność rozszerza działanie opisane w standardzie * dostarczenie równoważnej funkcjonalności: semantyka opisana w standardzie jest obsługiwana, ale przy pomocy innej składni * dostarczenie odpowiednika: ani semantyka, ani składnia ze standardu SQL nie są obsługiwane, dostarczona jest jednak funkcjonalność odpowiadająca opisanej w standardzie Szczegółowy opis implementacji poszczególnych punktów standardu Core SQL:2008 znajduje się w [[http://download.oracle.com/docs/cd/E14072_01/server.112/e10592/ap_standard_sql003.htm#g14847|dokumentacji producenta]] ===== Opcjonalne elementy SQL/Foundation:2008 ===== Podobnie jak w wypadku [[pl:dydaktyka:ztb:2011:projekty:sql:start#core_sql2008|Core SQL]], przepisywanie tabeli z [[http://download.oracle.com/docs/cd/E14072_01/server.112/e10592/ap_standard_sql004.htm#BJECCJHB|dokumentacji]] wydaje się być bezcelowe. ===== SQL/CLI:2008 ===== Sterownik ODBC firmy Oracle jest zgodny ze standardem SQL/CLI:2008 ===== SQL/PSM:2008 ===== Język Oracle PL/SQL zapewnia funkcjonalność równoważną do opisanej w standardzie SQL/PSM:2008. Możliwe są niewielkie różnice w składni, takie jak nazwy, czy też kolejność słów kluczowych. ===== SQL/MED:2008 ===== Baza Oracle nie jest zgodna z SQL/MED:2008 ===== SQL/OLB:2008 ===== Baza Oracle jest zgodna z SQL/OLB:1999. Prace nad zapewnieniem zgodności z SQL/OLB:2008 trwają. ===== SQL/JRT:2008 ===== Baza Oracle zapewnia zgodność ze standardem, jej możliwości są po części rozszerzeniem zbioru funkcjonalności zdefiniowanego w standardzie. ===== SQL/XML:2008 ===== Wg standardu typem danych dla XML jest ''XML''. W bazie Oracle jego odpowiednikiem jest ''XMLType''. Jest to jedyna różnica w stosunku do standardu i w takim wypadku (tj. gdy jedyna różnica, to nazwa typu danych) zwykło się uznawać standard za w pełni zaimplementowany. [[http://download.oracle.com/docs/cd/E14072_01/server.112/e10592/ap_standard_sql010.htm#BJEGCHBH|Szczegółowy opis wsparcia dla poszczególnych elementów standardu]] ==== Mapowanie danych ==== Opracowano na podstawie [[http://download.oracle.com/docs/cd/E14072_01/server.112/e10592/ap_standard_sql010.htm#BABIJEIG|dokumentacji producenta]]. ^Typ danych opisany w XML schema ^Typ danych w bazie Oracle^ | float | ''BINARY_FLOAT'' | | double | ''BINARY_DOUBLE'' | | pozostałe typy numeryczne | ''NUMBER'' | | dayTimeDuration | ''INTERVAL DAY TO SECOND'' | | yearMonthDuration | ''INTERVAL YEAR TO MONTH'' | | pozostałe typy dotyczące daty i czasu | ''TIMESTAMP WITH TIME ZONE'' | | string, normalizedString, untypedAtomic | ''VARCHAR2(4000)'' | ====== Porównanie implementacji SZBD z Standardem ====== ===== Widoki ===== ^Widoki ^^^ | Standard | SQL:2008 złożony zestaw reguł odnoszący się do akutalizowania widoków. Generalnie widok może być aktualizowany dopuki jego operacja aktualizacji dokonuje jednoznacznej zmiany \\ SQL-92 jest bardziej restrykcyjny. Widok nie może być aktualizowany jeśli dziedziczy po więcej niż jednej tabeli bazowej | | PostgreSQL | Posiada widoki. Nie pozwala na bezpośrednią aktualizacje do widoków. Oferuje niestandardowe rozwiązanie polegające na zastosowaniu reguł(rules) | | MySql | Zgodny z SQL-92 | | Oracle | zgodny z SQL-92 | ===== Złączenia ===== ^ Typ złączenia ^ PostgreSql ^ MySql ^ Oracle ^ | NATURAL joins | Tak | Tak | Tak | | Klauzula USING | Tak | Tak | Tak | | FULL joins | Tak | Nie | Tak | | Jawny CROSS JOIN | Tak | Tak | Tak | Uwagi: Złączenie typu FULL moża emulować poprzez połączenie złączeń LEFT i RIGHT ===== Operacje DDL ===== Założenia: Istniejąca tabela t1 ma być skopiowana do nowej tabeli t2 bez kopiowania danych. Tylko struktura tabeli jest kopiowana. ^ Kopiowanie struktury (DDL) ^^^ | Standard | Klauzula LIKE (opcjonala funkcja - T171) <code sql>CREATE TABLE t2 ( LIKE t1 )</code> System zarządzania bazą danych może wspierać rozszerzenie(T173) pozwalające na dokładniejsze specyfikowanie kopiowanych właściwości <code sql>CREATE TABLE t2 ( LIKE t1 INCLUDING IDENTITY INCLUDING DEFAULTS INCLUDING GENERATED )</code> Wyzwalacze, ograniczenia i inne niestandardowe cechy tabeli nie są kopiowane | | PostgreSql | Zgodny z podstawową fukcją standardu. Rozszerzenia wspierane są tylko częściowo. \\ PostgreSql nie pozala na kopiowanie struktury widoku poprzez użycie <code sql>CREATE TABLE ... (LIKE ...)</code>. Wymagana jest konstrukcja typu: <code sql>CREATE TABLE copytable AS SELECT * FROM viewname WHERE false</code> | | MySql | Wspiera podstawową funkcjonalność. Nie wspiera rozszerzeń. Nie pozwala na kopiowane struktury widoku do tabeli | | Oracle | Nie wspiera standardu. Pozwala na kopiowanie tabel poprzez użycie kunstrukcji <code sql>CREATE TABLE ... AS</code> z nieprawdziwym warunkiem w kklauzuli WHERE <code sql>CREATE TABLE t2 AS SELECT * FROM t1 WHERE 1<>1</code> | ===== Operacje DML ===== ==== SELECT ==== ^ Ustalanie porządku wyników ^^^ | Standard | Standardowo relacje są nieuporządkowane, ale wyniki zapytań mogą być zwracane do użytkownika w formie uporządkowanej, poprzez użycie kursora <code sql>DECLARE cursorname CURSOR FOR SELECT ... FROM ... WHERE ... ORDER BY column_name1,column_name2,...</code> Bazy danych mogą zawierać dodatkową definicję ORDER BY \\ Standard nie definiuje w jaki sposób powinny być porządkowane wartości NULL względem wartości nie będących NULL. Systemy baz danych mogą jednak dodatkowo oferować metody sortowania: <code sql>... ORDER BY ... NULLS FIRST or ... ORDER BY ... NULLS LAST</code> | | PostgreSql | Zezwala na definiowanie kursora oraz na użycie ORDER BY. \\ Domyślnie wartości NULL są traktowane jako wyższe od wartości nie będących NULL. To zachowanie może być zmienione poprzez zastosowanie NULLS FIRST lub NULLS LAST w wyrażeniu ORDER BY | | MySql | Zezwala na definiowanie kursora oraz na użycie ORDER BY. \\ Domyślnie wartości NULL są traktowane jako niższe od wartości nie będących NULL.| | Oracle | Zezwala na definiowanie kursora oraz na użycie ORDER BY. \\ Domyślnie wartości NULL są traktowane jako wyższe od wartości nie będących NULL. To zachowanie może być zmienione poprzez zastosowanie NULLS FIRST lub NULLS LAST w wyrażeniu ORDER BY. \\ Oracle traktuje puste stringi i NULL jako taką samą wartość. | ---- ^ Limitowanie wyników zapytań - proste ^^^ | Standard | Standard dostarcza 3 sposobów na wykonywanie prostego limitowania: \\ - Użycie FETCH FIRST <code sql>SELECT ... FROM ... WHERE ... ORDER BY ... FETCH FIRST n ROWS ONLY</code> - Użycie **Window function** ROW_NUMBER() OVER: <code sql>SELECT * FROM ( SELECT ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber, columns FROM tablename ) AS foo WHERE rownumber <= n</code> - użycie kursora <code sql>1. DECLARE cursor-name CURSOR FOR ... 2. OPEN cursor-name 3. FETCH ... 4. CLOSE cursor-name</code> | | PostgreSql | Wspiera wszystkie standardowe podejścia | | MySql | Nie wspiera standardu <code sql>SELECT columns FROM tablename ORDER BY key ASC LIMIT n</code> | | Oracle | Wspiera ROW_NUMBER, nie wspiera FETCH FIRST <code sql>SELECT * FROM ( SELECT ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber, columns FROM tablename ) WHERE rownumber <= n</code> | ---- ^ Limitowanie wyników zapytań - top(n) ^^^ | Standard | Standard definiuje dwa podejścia: \\ - Szybkie: \\ Sformułowanie zapytania top(n) z wykorzystaniem window function: <code sql>SELECT * FROM ( SELECT RANK() OVER (ORDER BY age ASC) AS ranking, person_id, person_name, age FROM person ) AS foo WHERE ranking <= 3</code> \\ - Wolne: \\ Jeśli SZBD nie wspiera OLAP <code sql>SELECT * FROM person AS px WHERE ( SELECT COUNT(*) FROM person AS py WHERE py.age < px.age ) < 3</code> | | PostgreSql | Wspiera szybki wariant standardu.| | MySql | Wspiera wolny wariant standardu. W praktyce w celu uzyskania odpowiedniej wydajności stosuje się rozwiązanie specyficzne dla MySql <code sql>SELECT * FROM person WHERE age <= COALESCE( -- note: no space between "COALESCE" and opening parenthesis ( SELECT age FROM person ORDER BY age ASC LIMIT 1 OFFSET 2 -- 2=n-1 ), ( SELECT MAX(age) FROM person ) )</code> | | Oracle | Wspiera szybki wariant standardu. Oracle nie obsługuje AS po podzapytaniach przez co rozwiązanie można zapisać w postaci: <code sql>SELECT * FROM ( SELECT RANK() OVER (ORDER BY age ASC) AS ranking, person_id, person_name, age FROM person ) WHERE ranking <= 3</code> | ---- ^ Limitowanie wyników zapytań - z offsetem ^^^ | Standard | Standard dostarcza 3 rozwiązania \\ - Używając OFFSET i FETCH FIRST <code sql>SELECT... FROM ... WHERE ... ORDER BY ... OFFSET skip ROWS FETCH FIRST n ROWS ONLY</code> - Używając window function <code sql>SELECT * FROM ( SELECT ROW_NUMBER() OVER (ORDER BY key ASC) AS rownum, columns FROM tablename ) AS foo WHERE rownum > skip AND rownum <= (n+skip)</code> - Używając kursora <code sql>1. DECLARE cursor-name CURSOR FOR ... 2. OPEN cursor-name 3. FETCH RELATIVE number-of-rows-to-skip ... 4. CLOSE cursor-name</code> | | PostgreSql | Wspiera wszystkie rozwiązania standardu | | MySql | Nie wspiera standardu. Alternatywne rozwiązanie: <code sql>SELECT columns FROM tablename ORDER BY key ASC LIMIT n OFFSET skip</code> | | Oracle | Wspiera ROW_NUMBER() <code sql>SELECT * FROM ( SELECT ROW_NUMBER() OVER (ORDER BY key ASC) AS rn, columns FROM tablename ) WHERE rn > skip AND rn <= (n+skip)</code> | ==== INSERT ==== ^ Wstawianie kilku wierszy w tym samym czasie ^^^ | Standard | <code sql>INSERT INTO tablename VALUES (0,'foo') , (1,'bar') , (2,'baz');</code> jest skróconą formą <code sql>INSERT INTO tablename VALUES (0,'foo'); INSERT INTO tablename VALUES (1,'bar'); INSERT INTO tablename VALUES (2,'baz');</code> | | PostgreSql | Zgodnie z standardem | | MySql | Zgodnie z standardem | | Oracle | <code sql>INSERT INTO tablename SELECT 0,'foo' FROM DUAL UNION ALL SELECT 1,'bar' FROM DUAL UNION ALL SELECT 2,'baz' FROM DUAL</code> | ===== Typy danych ===== ==== Boolean ==== | Standard | W standardzie typ BOOLEAN jest opcjonalny. Może przyjmować literały w postaci: \\ - TRUE \\ - FALSE \\ - UNKNOWN lub NULL \\ SZBD może interpretować NULL jako UNKNOWN, ale nie jest to dokładnie sprecyzowane. \\ Jest zdefiniowany TRUE > FALSE | | PostgreSql | Zgodnie z standardem. Traktuje NULL jako literał. Nie akceptuje UNKNOWN| | MySql | BOOLEAN jest aliasem typu TINYINT(1). Akceptuje literały TRUE i FALSE (odpowiednio 1 i 0 ) lub NULL. | | Oracle | Nie wspiera typu BOOLEAN | ==== Char ==== | Standard | - Powoduje błąd jeśli wstawiany łańcuch jest za długi. Jeśli znaki przekraczające długość są spacjami, działa poprawnie \\ - Wypełnia kolumnę typu CHAR spacjami jeśli wpisany łańcuch jest za krótki \\ - Wypełnia spacje w przypadku rzutowania i porównywania z innymi typami podobnymi do łańcucha znaków (VARCHAR) | | PostgreSql | Wypełnia kolumnę CHAR spacjami, ale usuwa je przed wykonaniem większości funkcji | | MySql | Usuwa spacje na końcu łańcucha znaków | | Oracle | Zgodnie z standardem. Powoduje błąd w przypadku, gdy znaki spacji przekraczają długość CHAR | ==== Data i czas ==== | Standard | Część głównych wymagań standardu. Zapisuje rok, miesiąc, dzień, godzina, minuta, sekunda(z dokładnością do 6 cyfr). Rozszerzenia pozwalają dodatkowo zapisać strefę czasową \\ Przykład TIMESTAMP <code sql>TIMESTAMP '2003-07-29 13:19:30' TIMESTAMP '2003-07-29 13:19:30.5'</code> Przykład TIMESTAMP WITH TIME ZONE <code sql>TIMESTAMP '2003-07-29 13:19:30+02:00' TIMESTAMP '2003-07-29 13:19:30.5+02:00'</code> | | PostgreSql | Zgodnie ze standardem z wyjątkiem: \\ W niektórych przypadkach TIMESTAMP '2003-08-23 01:02:03 +02:00' jest interpretowane jako TIMESTAMP WITHOUT TIME ZONE, a nie jak TIMESTAMP WITH TIME ZONE \\ Poprawny kod: <code sql>INSERT INTO tablename (columnname) VALUES (TIMESTAMP '2003-02-28 00:05:00')</code> Niepoprawny kod: <code sql>INSERT INTO tablename (columnname) VALUES (TIMESTAMP '2003-02-29 00:05:00')</code>| | MySql | MySql nie zapisuje ułamkowych części sekund oraz strefy czasowej( typ TIME akceptuje czas wraz z częścią ułamkową, ale nie zapisuje jej). \\ MySql posiada typ TIMESTAMP - jest to typ daty, który jest automatycznie aktualizowana do obecnej daty i godziny w przypadku spełnienia pewnych kryteriów. \\ MySql posiada typ DATETIEME - typ daty podobny do TIMESTAMP, ale nie jest automatycznie aktualizowany. \\ Domyślne sprawdzanie poprawności dat przez MySql jest słabej jakości. Dla pewności należy ręcznie sprawdzić poprawność zapisanych danych w DATETIME | | Oracle | Zgodnie ze standardem | ===== Funkcje SQL ===== ==== CHARACTER_LENGTH ==== | Standard | CHARACTER_LENGTH(argument) \\ Zwraca NUMERIC. Zwraca NULL jeśli wejście jest NULL. \\ Alias: CHAR_LENGTH. \\ Argument może być typy CHAR lub VARCHAR. \\ Część głównych wymagań standardu. \\ Powiązane funkcje: OCTET_LENGTH. | | PostgreSql | Zgodnie ze standardem. CHARACTER_LENGTH (i CHAR_LENGTH). \\ Usuwa końcowe spacje przed zliczaniem | | MySql | Posiada CHARACTER_LENGTH. \\ Aliasy: CHAR_LENGTH, LENGTH. \\ Usuwa spacje przed zliczaniem. | | Oracle | Nie posiada CHARACTER_LENGTH. Wprowadza funkcję LENGTH. Podobnie traktuje NULL i pusty łańcuch. \\ Nie usuwa spacji. | ==== SUBSTRING ==== | Standard | Standard definiuje dwa warianty funkcji SUBSTRING: \\ 1. Standardowa funkcja SUBSTRING pobiera znaki z łańcucha <code sql>SUBSTRING(input FROM start-position [FOR length])</code> Łańcuch rozpoczyna się od pozycji 1 \\ - start-position - numeryczna wartość \\ - length - jeśli nie jest podany przyjmuje nieskończoność. \\ Jeśli którykolwiek z argumentów jest NULL to wynikiem jest NULL. \\ Jeśli wartość atrybutu start-position jest mniejsza od 1 to przyjmuje on wartość 1 i wówczas argument length przyjmuje 1+abs(start-position) \\ \\ 2. Funkcja SUBSTRING z obsługą wyrażeń regularnych <code sql>SUBSTRING(input SIMILAR pattern ESCAPE escape-char)</code> String wzorca musi posiadać trzy części: Część która jest zgodna z łańcuchem przed wystąpieniem szukanej frazy oraz część, która ma być po szukanej frazie<code sql>SUBSTRING('abc' SIMILAR 'a#"b#"c' ESCAPE '#') </code>| | PostgreSql | Obsługuje trzy rodzaje metody SUBSTRING. \\ - Podstawowe rozwiązanie standardu \\ - z obsługą wyrażeń regularnych POSIX \\ - z obsługą wyrażeń regularnych Standardu. Zwraca NULL jeśli wzorzec nie pasuje | | MySql | Obsługuje podstawową wersję Standardu. Działanie funkcji różni się w przypadku ujemnych wartości pozycji startowej| | Oracle | Nie wspiera standardu. Wprowadza funkcję SUBSTR(input, start-pos[,length]) oraz wiele jej wariantów. Do obsługi wyrażeń regularnych używa funkcji REGEXP_SUBSTR | ==== TRIM ==== | Standard | TRIM(where characters FROM string_to_be_trimmed) \\ where - LEADING, TRAILING lub BOTH, pominięty oznacza BOTH \\ characters - znak który ma być usunięty, pominięty oznacza ' ' spację \\ Skrócona postać <code sql>TRIM(string_to_be_trimmed)</code> jest równoznaczna <code sql>TRIM(BOTH ' ' FROM string_to_be_trimmed)</code> TRIM(NULL) zwraca NULL | | PostgrSql | Zgodnie ze standardem | | MySql | Zgodnie ze standardem | | Oracle | Zgodnie ze standardem z wyjątkiem: \\ - Nie można usuwać wielu znaków \\ - Inaczej obsługuje NULL i pusty łańcuch znaków | ==== LOCALTIMESTAMP ==== | Standard | aktualny TIMESTAMP ( bez strefy czasowej ) uzyskiwany jest z funkcji LOCALTIMESTAMP <code sql>SELECT LOCALTIMESTAMP ... or SELECT LOCALTIMESTAMP(precision) ...</code> | | PostgrSql | Zgodnie ze standardem | | MySql | Zgodnie ze standardem | | Oracle | Zgodnie ze standardem | ==== Konkatenacja ==== | Standard | Dokonuje konkatenacji dwóch stringów przy użyciu operatora <nowiki>||</nowiki> <code sql>string1 || string2</code> Jeśli którykolwiek operand jest równy NULL to wynikiem jest NULL | | PostgrSql | Zgodnie ze standardem. Automatycznie rzutuje połączone wartości do odpowiednich typów | | MySql | Niezgodny ze standardem ( re definiuje <nowiki>||</nowiki> na OR. \\ Oferuje funkcję CONCAT(string, string). Automatycznie rzutuje połączone wartości do odpowiednich typów. Jeśli którykolwiek operand jest równy NULL to wynikiem jest NULL | | Oracle | Częściowo zgodnie ze standardem. Automatycznie rzutuje połączone wartości do odpowiednich typów. Niestandardowo obsługuję NULL i puste łańcuchy znaków. | ===== Operacje masowe ===== ^ TRUNCATE TABLE ^^^ | Standard | Standard SQL oferuje wyrażenie TRUNCATE TABLE tablename: \\ Usuwa wszystkie wiersze tabeli bez uruchamiania akcji wyzwalaczy. \\ Standard nie specyfikuje czy \\ - TRUNCATE TABLE może być umieszczany w transakcji z innymi operacjami \\ - TRUNCATE TABLE powinien automatycznie wykonywać COMMIT | | PostgreSql | Zgodnie ze standardem. Pozwala na umieszczanie w transakcji TRUNCATE TABLE razem z innymi operacjami. Nie Wywołuje COMMIT | | MySql | Posiada TRUNCATE TABLE, ale nie jest zgodny ze standardem. Zachowania zależy od storage engine (np. InnoDB) | | Oracle | Zgodnie ze standardem. TRUNCATE TABLE automatycznie wykonuje COMMIT | ====== Linki: ====== * Dokumentacja PostgreSQL 9.0 [[http://www.postgresql.org/docs/9.0/interactive/features.html]] * Dokumentacja MySQL 5.5 [[http://dev.mysql.com/doc/refman/5.5/en/compatibility.html]] * Dokumentacja SQLite [[http://www.sqlite.org/docs.html]] * Dokumentacja Oracle [[http://download.oracle.com/docs/cd/E14072_01/server.112/e10592/ap_standard_sql003.htm]] * Download i licencja Oracle [[http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html]]
pl/dydaktyka/ztb/2011/projekty/sql/start.txt
· ostatnio zmienione: 2019/06/27 15:50 (edycja zewnętrzna)
Pokaż stronę
Poprzednie wersje
Menadżer multimediów
Do góry