Gdzieś na sieci swego czasu wygrzebałem, a że często korzystam więc wrzucam, może komuś też się przyda
SELECT
SELECT x,y,x FROM tabxyz;
SELECT x*12,y/30 FROM tabxyz;
SELECT x*12 AS “alias ze spacjami” FROM tabxyz; //dzieki ‘ ‘ mozna uzywac spacji wewnatrz aliasow
mySQL) ‘ ‘ <- do aliasow
SELECT ‘tekst1′||x||’tekst2′||y FROM tabxyz;
mySQL) SELECT CONCAT(‘tekst1′,x,’ ma ‘, y) FROM tabxyz;
SELECT x+NVL(y,0) FROM tabxyz;
mySQL) nie ma NVL
SELECT DISTINCT x,y FORM tabxyz; //eliminowanie duplikatow z tych wybranych krotek
SELECT x,y,z FROM tabxyz ORDER BY x DESC,y ASC,2; //DESC(descending) ASC(ascending)
SELECT x,y FROM tabxyz WHERE x>400 AND y!=’leo’; // Operatory: =,!=,<>,>,>=,<,<=
u) != a <> sa rownowazne
SELECT y FROM tabxyz WHERE y LIKE ‘_M%’; // _ to dowolny znak, % to dowolny ciag znakow
SELECT x FROM tabxyz WHERE x IS NULL; //nie ma nawet wartosci 0
mySQL) trzeba poslugiwac sie IS NULL / IS NOT NULL a nie mozna jak w Oracle x!=NULL
SELECT y FROM tabxyz WHERE y NOT IN(‘tekst1′,’tekst2′);
NOT BETWEEN … AND …, NOT IN, NOT LIKE, IS NOT NULL
u) wszystkim dodajemy not z przodu z wyjatkiem IS NULL -> IS NOT NULL dla poprawnosci jezyka
u) BETWEEN … AND … dziala jak zbior domkniety.
AND, OR, NOT //trzeba pamietac ze NULL nam troszke miesza bo np. TRUE OR NULL = TRUE.
U) AND ma najwyzszy priorytet zatem czasami trzeba uzyc nawiasow.
FUNKCJE
SELECT LOWER(y), UPPER(‘tekst1′), INITCAP(z) FROM tabxyz; //INITCAP pierwsza litera duza
SELECT LPAD(y,20,’*'),RPAD(z,24,’123′) FROM tabxyz; // ostatnim argumentem jest ciag ew. znak lub domyslna spacja
//uzupelnia z LEFT,RIGHT do dlugosci n o podany znak (domyslnie spacja) czyli nie trzeba podac
mySQL) nie ma INITCAP, a LPAD i RPAD wymaga 3 argumentu.
SELECT SUBSTR(y,3,4),INSTR(z,’tekst1′),LENGTH(z) FROM tabxyz;
//SUBSTR(y,n,m) wycina z ciagu y pod pozycji n,m znakow (te pozostana)
//np.SUBSTR(nazwisko,1,2) zostana 2pierwsze litery ciagu nazwisko
//INSTR(z,’tekst1′,[m,n]) podaje pierwsze(n-te) wystapienie tekstu w ciagu z od pierwszej (m-tej) pozycji
SELECT LTRIM(y,’zb_znakow’),RTRIM(z) FROM tabxyz; //usuwa z lewej,prawej podane znaki, domyslnie spacja
mySQL)tylko jeden argument czyli usunie spacaje z lewej/prawej
SELECT TRANSLATE(y,’ABC’,'XYZ’) FROM tabxyz; //zamiana w ciagu y znakow na odpowiadajace im nowe znaki A->X,B->Y
mySQL) nie ma TRANSLATE
SELECT x,REPLACE(z,’stary’,'nowy’) FROM tabxyz; //zamieni kazde wystapienie ciagu 1 na ciag 2 w ciagu z
SELECT ROUND(123.456,1),TRUNC(12.34,1),FLOOR(1.5),CEIL(1.5) FROM dual; //zaokragla,obcina,podloga,sufit
mySQL) nie ma TRUNC
SELECT POWER(2,16),SQRT(16),ABS(-5),MOD(7,4),SIGN(7) FROM dual;//MOD=%wC, SIGN(x) -1 gdy x<0 x=0 to 0 else 1
SELECT SYSDATE FROM dual; //data systemowa
mySQL) nie ma pseudokolumny SYSDATE w dual
SELECT MONTHS_BETWEEN(SYSDATE,z) FROM tabxyz; //liczba miesiecy pomiedzy datami (z typu DATE)
SELECT ADD_MONTHS(SYSDATE,2) FROM dual; //zwraca date + n miesiecy
SELECT NEXT_DAY(SYSDATE,’WTOREK’) FROM dual; //zwraca kolejna date z wtorkiem po podanej
SELECT LAST_DAY(DATE ’1982-09-17′) FROM dual; //zwraca date^ ostatniego dnia w tym miesiacu podanej daty
mySQL) z tych 4 wyzej jest tylko LAST_DAY
SELECT TO_CHAR(SYSDATE,’SCC YYYY MONTH DAY HH24 MI SS’) FROM dual; //konwersja do char
SCC -stulecie, YYYY -rok,BC AD -wsk ery,MM -m-c(MONTH nazwa), D DD DDD dzien~(DAY nazwa),AM PM -wsk pory,HH
(HH24),MI,SS
SELECT TO_NUMBER(‘-12,23′) FROM dual;
SELECT TO_DATE(’82_09_17′,’YY_MM_DD’) FROM dual;
SELECT NVL2(x,7,0) FROM tabxyz; //operator ?wC jesli x<>NULL to 7 else 0
mySQL) brak NVL,NVL2 i funkcji konwersji TO_…
SELECT GREATEST(x,NVL(y,0)),LEAST(z) FROM tabxyz; //zwraca najwieksza, najmniejsza wartosc z listy argumnentow
SELECT x,y,CASE WHEN x>10 THEN ‘tekst1′ ELSE UPPER(y) END FROM tabxyz;
//CASE WHEN warunek1 THEN wartosc1 WHEN warunek2 THEN wartosc2 [ ELSE wartosc ] END
//zatem mimo braku NVL w mySQL mozna tak:
mysql> SELECT nazwisko,placa_pod+( CASE WHEN placa_dod IS NULL THEN 0 ELSE placa_dod END) AS ‘NVL’ FROM Pracownicy;
SELECT x,DECODE(x,’tekst1′,’***’,'tekst2′,’###’,'@@@’) FROM tabxyz;
//DECODE(wyrazenie,S1,W1,S2,W2,D) jesli wyrazenie =S1 to W1 jesli =S2 to W2 else D
mySQL) brak DECODE (cos swiruje)
AVG,COUNT,MAX,MIN,SUM,VARIANCE,STDDEV np. SELECT COUNT (*),MAX(x) FROM tabxyz WHERE z=20;
SELECT y,AVG(z) FROM tabxyz GROUP BY y; // lub GROUP BY x,y; czyli w ramach grupowania x grupuj y;
SELECT x,COUNT(*) FROM tabxyz GROUP BY x HAVING COUNT(*)>=2; //Having dziala jak WHERE tylko ze tu dla grupy
np. select id_zesp,sum(placa_pod) from pracownicy where etat in(‘PROFESOR’,'ASYSTENT’) group by id_zesp having
sum(placa_pod)>3000
JOIN`y
SELECT x,q FROM tabx,tabq;
SELECT x,q FROM tabx CROSS JOIN tabq;
//te polaczenia sa sobie rownowazne – iloczyn kartezjanski
//Teraz polaczenia równooeciowe i naturalne:
SELECT p.nazwa,z.adres FROM tabnazw p, tabadres z WHERE p.id=z.id;
SELECT p.nazwa,z.adres FROM tabnazw p JOIN tabadres z ON p.id=z.id;
//Naturalne w Oracle daja w wyniku tylko raz atrybut polaczeniowy
SELECT nazwa,adres FROM tabnazw NATURAL JOIN tabadres;
SELECT nazwa,adres FROM tabnazw JOIN tabadres USING(id_zesp);
//Polaczenie nierownosciowe
SELECT x,lowx,upx FROM tabx JOIN tablu ON x BETWEEN lowx AND upx;
SELECT x,lowx,upx FROM tabx,tablu WHERE x BETWEEN lowx AND upx;
DESCRIBE etaty;
//Polaczenia zewnetrzne OUTER JOIN
SELECT p.x, z.y FROM tabx p,taby z WHERE p.id=z.id(+);
//tam gdzie plusik to tam dodajemy nulle na wpadek braku odpowiednika dla tego bez plusika
SELECT p.x, z.y FROM tabx p LEFT OUTER JOIN taby z ON p.id=z.id;
//jesli w p nie bedzie odpowiednika w z to bedzie p z nullem
SELECT p.x, z.y FROM tabx p RIGHT OUTER JOIN taby z ON p.id=z.id;
//jesli w z nie bedzie odpowiednika w tabeli p to ta krotka bedzie z nullem
SELECT p.x, z.y FROM tabx p FULL OUTER JOIN taby z ON p.id=z.id;
SELECT x,y FROM tabx FULL OUTER JOIN taby USING(id); //naturalne zewnetrzne
//FULL OUTER pozwala na zarowno uzupelnianie niepasujacych krotek z lewej i prawej tabeli polaczenia.
//Polaczenie zwrotne SELF JOIN
SELECT p.x,z.y FROM tabx p,taby z WHERE p.id_szefa=z.id_prac;
SELECT p.x,z.y FROM tabx p JOIN taby z ON p.id_szefa=z.id_prac;
//dziala jak sprzezenie xwrotne czyli polaczenie ze soba
//Operatory zbiorowe //VERTICAL JOIN
UNION – suma zbiorow,eliminuje duplikaty
UNION ALL – suma zbiorow, nie eliminuje duplikatow
INTERSECT – czesc wspolna zbiorow,eliminuje duplikaty
MINUS – roznica zbiorow, eliminuje duplikaty
mySQL) nie ma MINUS i INTERSECT
SELECT x FROM tabx WHERE y>=30 MINUS SELECT x FROM tabx WHERE y<=20;
//czyli bedzie to zbior krotek x ktore maja y miedzy 21 a 30
SELECT x FROM tabx UNION SELECT y FROM taby;
//powstanie 1kolunowa tabelka zawierajaca wszystkie krotki x i krotki y
SubSelecT`y
SELECT x,y FROM tabxy WHERE y=(SELECT MIN(y) FROM tabxy);
//zwroci 1krotke x,y (to z najmniejsza wartoscia y)
SELECT x,y,z FROM tabxyz WHERE (y,z)=(SELECT ‘wartosc y’,MAX(z) FROM tabxyz WHERE y=’wartosc y’);
//Uwaga bo select ma zwrocic 1 wartosc wiec mozna tez tak:
SELECT x,y,z FROM tabxyz WHERE (y,z)=(SELECT y,MAX(z) FROM tabxyz WHERE y=’wartosc y’ GROUP BY y);
//Teraz zwracamy wiecej wartosci:
SELECT x,y,z FROM tabxyz WHERE (y,z) IN (SELECT y,MIN(Z) FROM tabxyz GROUP BY y);
//Podzapytanie da krotki o kazdej wartosci y z min(z) a calosc bedzie z odpowiadajacym x.
TODO: ANY ALL
//Wielopoziomowe podzapytanie
SELECT x,y,x FROM tabxyz WHERE y > (SELECT AVG(y) FROM tabxyz WHERE z=(SELECT z FROM tabz WHERE z=’wartosc z’));
//Czyli jesli srednia y dla konkretnej 1 wartosci z bedzie porownana z y zapytania zewnetrznego.
//Podzapytania skorelowane
SELECT p.x FROM tabxyz p WHERE p.y > (SELECT AVG(y) FROM tabxyz WHERE id=p.id);
//czyli kiedy w podzapytaniu chcemy sie odwolac do krotki z zapytania zewnetrznego to mamy zapytanie skorelowane.
SELECT x FROM tabxyz WHERE EXISTS (SELECT * FROM tabq);
//wypisze krotki x o ile istnieje cos w tabq
SELECT p.x FROM tabxyz p WHERE NOT EXISTS (SELECT idq FROM tabq WHERE idq=p.id);
//Podzapytania w SELECT
SELECT x,(SELECT MAX(Y) FROM tabxyz) FROM tabxyz;
SELECT p.X,(SELECT MAX(Y) FROM tabxyz WHERE x=p.x) FROM tabxyz;
WITH x_y AS (SELECT x,y FROM tabx NATURAL JOIN taby) SELECT * FROM x_y WHERE y>10;
//czyli taki skrot do danego selecta.
mySQL) nie ma WITH
mySQL) nie ma zapytan hierarchicznych
CREATE table
oracle) CREATE TABLE newtable ( id NUMBER(4) PRIMARY KEY, name CHAR(20) UNIQUE NOT NULL, salary NUMBER(10,2) );
mySQL) CREATE TABLE newtable (id INT(4) PRIMARY KEY,name CHAR(20) UNIQUE NOT NULL, salary DECIMAL(10,2) );
U!) NUMBER(3,2) oznaczac bedzie 3cyfrowa liczbe w tym 2 po przecinku, czyli 3.14 jest ok, ale 12.56 juz nie
CREATE TABLE newtable ( value NUMBER(2) DEFAULT 0, value2 NUMBER(5) CHECK (value2 BETWEEN 10 AND 10000) );
CREATE TABLE newtable ( id NUMBER(2) PRIMARY KEY, id2 NUMBER(2) REFERENCES othertable(id2_inother) );
CREATE TABLE newtable ( id2 NUMBER(2) REFERENCES othertable(id2_inother) ON DELETE CASCASE );
CREATE TABLE newtable ( id2 NUMBER(2) REFERENCES othertable(id2_inother) ON DELETE SET NULL );
CREATE TABLE newtable ( id NUMBER(2) CONSTRAINT klucz_podst PRIMARY KEY, value DATE DEFAULT SYSDATE );
//Tworzenie relacje przez podzapytanie
CREATE TABLE newtable AS SELECT id,name AS “nazwisko”,value FROM tabx WHERE value>10;
CREATE TABLE newtable(id,nazwisko,value) AS SELECT id,name AS “nazwisko”,value FROM tabx WHERE name IN(‘aaa’,'abc’);
//Modyfikowanie tabel
ALTER TABLE tabx ADD newattr VARCHAR2(10) DEFAULT ‘hhehe’;
ALTER TABLE tabx MODIFY attr NUMBER(2,1) NOT NULL;
ALTER TABLE tabx MODIFY ‘attr’ ‘new_nazwa’ NUMBER(2,1) NOT NULL UNIQUE;
ALTER TABLE tabx DROP COLUMN(value);
ALTER TABLE tabx DROP CONSTRAINT(value);
//ON/OFF constraint
ALTER TABLE tabx DISABLE CONSTARINT ograniczenie1;
ALTER TABLE tabx DISABLE PRIMARY KEY;
ALTER TABLE tabx ENABLE CONSTARAITN ograniczenie2;
//Zmiana nazwy relacji
RENAME nazwa TO nowa_nazwa;
//Komentarze
COMMENT ON TABLE tabx IS ‘komentarz1′;
COMMENT ON COLUMN tabx.attr1 IS ‘komentarz_kolumny1′;
SELECT * FROM USER_TAB_COMMENTS;
SELECT * FROM USER_COL_COMMENTS;
DROP TABLE nazwa;
DROP TABLE nazwa CASCADE CONSTRAINTS;
SELECT * FROM user_constraints;
SELECT * FROM USER_TABLES;
MODIFY/INSERT INTO TABLES
INSERT INTO tabx VALUES ( 110, ‘Routing Protocol’,0);
INSERT INTO tabx(id_projektu,nazwa,fundusz) VALUES ( 110, ‘Routing Protocol’,0); //to jest bezpieczniejsze
SQL*Plus) INSERT INTO tabx(id_projektu,nazwa,fundusz) VALUES ( &ident,’&opis_projektu’,&fundusz);
//w tym rozwiazaniu poprosie o wpisanie w postaci formularza
INSERT INTO tabx(id_projektu,nazwa,fundusz) SELECT id,nazwa,value*6 FROM taby WHERE value>23;
INSERT INTO tabx DEFAULT VALUES;
INSERT INTO tabx VALUES (1,’abc’,0),(2,’cde’,200); // jednym zamachem wstawi 2krotki (cos ostatnio w oracle sie nie
dalo)
//Modyfikacja krotek:
UPDATE tabx SET nazwa=’nowa nazwa!’ WHERE id=1;
UPDATE tabq SET etat=’DYREKTOR’,placa=placa*2 WHERE nazwisko=’ja’;
UPDATE tabx p SET (p.nazwa,p.fundusz)=(SELECT nazwy,1.5*MAX(fundusz) FROM tabq WHERE nazwy=p.nazwa) WHERE p.xyz=100;
UPDATE tabx p SET (p.value)=(SELECT SUM(cosik) FROM tabq WHERE qaz=p.qaz) WHERE EXISTS (SELECT * FROM tabv WHERE
iv=p.id;
DELETE FROM tabx WHERE nazwa=’kowalski’;
DELETE FROM tabx wHERE nazwa IN (‘abc’,'def’);
DELETE FROM tabx p WHERE p.value < SELECT cos FROM taq WHERE iq=p.id;
UPDATE (SELECT x,y FROM tabxy NATURAL JOIN tabz WHERE z=’wartosc zeta2′) SET y=y*100 WHERE x=10;
//Sekwencje
CREATE SEQUENCE nazwa_seq START WITH 100 INCREMENT BY 5;
ALTER SEQUENCE nazwa_seq START WITH 100 INCREMENT BY 5 MAXVALUE 200; //modyfikacja sekwencji
select * from user_sequences;
INSERT INTO tabx VALUES(nazwa_seq.NEXTVAL,’tekst1′,123);
DROP SEQUENCE nazwa_seq;
VIEWS
CREATE OR REPLACE VIEW nowa_persp(id,nazwa,value) AS SELECT id_x,opis,z FROM tabx WHERE id_x=20;
//Niemodyfikowalne perspektywy: Kazda krotka kazdej relacji laczonej wystepuje wielokrotnie
CREATE OR REPLACE VIEW nowa_persp(etat,srednia,maks) AS SELECT etat,AVG(x),MAX(z) FROM tabx GROUP BY etat;
CREATE OR REPLACE VIEW (id1,id2,nazwa,x,y) AS SELECT p.id,z.id2,p.opis,p.x,e.y FROM tab p JOIN tab z
ON p.id=z.id2 JOIN tabyq e ON p.x BETWEEN q_min AND q_max) WHERE p.opis IN (‘aa’,'bb’);
//Modyfikowalna
CREATE OR REPLACE VIEW prac_zesp (id, nazwisko, nazwa) AS SELECT id_zesp, nazwisko, nazwa FROM pracownicy JOIN
zespoly USING (id_zesp);
//ograniczenia add
CREATE OR REPLACE VIEW prac_minimum (id, nazwisko, placa, etat) AS SELECT id_prac, nazwisko, placa_pod, etat FROM
pracownicy WHERE placa_pod < 1000 WITH CHECK OPTION CONSTRAINT za_wysoka_placa;
SELECT ROWNUM, T.rnum, T.nazwisko, T.etat, T.pensja FROM ( SELECT ROWNUM AS rnum, nazwisko, etat, placa_pod AS pensja
FROM pracownicy ORDER BY pensja DESC ) T WHERE ROWNUM <= 3;
np.select X.rnum, X.nazwisko, X.placa_pod from (select ROWNUM as rnum, T.nazwisko, T.placa_pod from (select
nazwisko,placa_pod from pracownicy order by placa_pod desc) T)X where X.rnum>4 and X.rnum<11 ;
ALTER VIEW nazwa_perspektywy [COMPILE] [ADD | MODIFY | DROP CONSTRAINT ograniczenie];
DROP VIEW nazwa_perspektywy [CASCADE CONSTRAINTS];
//to czy jest modyfikowalna persypektywa mozna latwo zweryfikowac:
select * from user_updatable_columns
