Scripturi și tabel de date
CREATE TABLE goods(id bigint NOT NULL, caracterul nume variabil(127) NOT NULL, caracterul de descriere variază(255) NOT NULL, prețul numeric(16,2) NOT NULL, caracterul articolul variază(20) NOT NULL, marcaj temporal act_time NU NULL , disponibilitate boolean NOT NULL, CONSTRAINT pk_goods PRIMARY KEY (id)); INSERT INTO bunuri (id, nume, descriere, preț, articol, act_time, disponibilitate) VALORI (1, „Papuci”, „Moale”, 100,00, „TR-75”, (ts „2017-01-01 01:01: 01.01"), TRUE); INSERT INTO bunuri (id, nume, descriere, preț, articol, act_time, disponibilitate) VALORI (2, „Pernă”, „Alb”, 200.00, „PR-75”, (ts „2017-01-02 02:02: 02.02"), TRUE); INSERT INTO bunuri (id, nume, descriere, preț, articol, act_time, disponibilitate) VALORI (3, „Blanket”, „Down”, 300.00, „ZR-75”, (ts „2017-01-03 03:03: 03.03"), TRUE); INSERT INTO bunuri (id, nume, descriere, preț, articol, act_time, disponibilitate) VALORI (4, „Fătă de pernă”, „Gri”, 400,00, „AR-75”, (ts „2017-01-04 04:04: 04.04"), FALSE); INSERT INTO bunuri (id, nume, descriere, preț, articol, act_time, disponibilitate) VALORI (5, „Foaie”, „Mătase”, 500,00, „BR-75”, (ts „2017-01-05 05:05: 05.05"), FALSE);
Exemplu cu ghilimele duble
SELECTAȚI numele „Numele produsului” DIN mărfuri
Pseudo masă
SELECTAȚI mock.nickname „Pseudonim”, (CAZ WHEN mock.huff THEN „Yes” ELSE „Nu” END) „Onense?” FROM (SELECT name AS nickname, disponibilitate AS huff FROM goods) mock
În exemplul nostru, simularea este o pseudo-tabelă (uneori numită masă virtuală). Desigur, ele nu sunt menite deloc să distorsioneze adevăratul sens. Un exemplu este acesta.
Exemplu de constructor de bloc de date
SELECTAȚI numele „Numele produsului”, prețul „Prețul” FROM (VALORI („Papuci”, 100,00), („Pernă”, 200,00)) CA bunuri (nume, preț)
Numele produsului | Preţ |
Papuci | 100.00 |
Pernă | 200.00 |
In sectiune DIN cuvânt cheie folosit VALORI, urmat de datele din paranteze, rând cu rând. Ideea este că nu selectăm deloc datele din niciun tabel, ci pur și simplu le creăm din mers, „numim” tabel, numim coloanele și apoi le folosim la discreția noastră. Acest lucru s-a dovedit a fi extrem de util atunci când se testează diferite cazuri de interogare SQL, când nu există date pentru unele tabele (în baza de date locală), iar scrierea inserării este prea leneșă sau uneori foarte dificilă, din cauza relației dintre tabele și restricții .
Exemplu de comparare a blocurilor de date
SELECT * FROM bunuri WHERE (nume, preț, disponibilitate) = ("Fătă de pernă", 400,00, FALSE) -- sau echivalentul acestuia SELECT * FROM bunuri WHERE nume = "Fătă de pernă" AND preț = 400,00 ȘI disponibilitate = FALSE
După cum se poate vedea din exemplu, compararea blocurilor de date este similară cu compararea element cu element sens_ 1 _block_1 = valoare_ 1 _bloc_2, valoare_ 2 _block_1 = valoare_ 2 _bloc_2, valoare_ 3 _block_1 = valoare_ 3 _bloc_2 folosind ŞIîntre ei.
Exemplu cu EXCEPT
De fapt, datele din al doilea set sunt excluse din primul set de valori.
De fapt, are loc intersecția primului set de valori și a celui de-al doilea set.
Asta e tot, mulțumesc pentru atenție.
Lecția va acoperi subiectul utilizării operațiilor de interogare de unire, intersecție și diferență. Exemple de utilizare Interogare SQL Unire, Există și utilizarea cuvintelor cheie SOME, ANY și All. Funcțiile șirurilor acoperite
Puteți efectua operațiunile de unire, diferență și produs cartezian pe un set. Aceleași operații pot fi folosite și în interogările sql (efectuați operații cu interogări).
Un cuvânt special este folosit pentru a combina mai multe interogări UNIUNE.
Sintaxă:
< запрос 1 >UNIRE[TOATE]< запрос 2 > <запрос 1>UNIUNE<запрос 2>
Interogarea Union SQL este utilizată pentru a combina rândurile de ieșire ale fiecărei interogări într-un singur set de rezultate.
Dacă este folosit parametrul ALL, apoi toate liniile de ieșire duplicat sunt salvate. Dacă parametrul lipsește, în setul de rezultate rămân doar rânduri unice.
Puteți combina orice număr de interogări împreună.
Utilizarea operatorului UNION necesită îndeplinirea mai multor condiții:
Exemplu: Afișați prețurile pentru computere și laptopuri, precum și numerele acestora (adică, descărcați din două tabele diferite într-o singură interogare)
1 2 3 4 5 6 | SELECTAȚI `Număr` , `Preț` FROM PC UNION SELECTați `Număr` , `Preț` DIN notebook ORDER BY `Preț` |
SELECTAȚI `Număr` , `Preț` FROM PC UNION SELECTați `Număr` , `Preț` DIN notebook ORDER BY `Preț`
Rezultat:
Să ne uităm la un exemplu mai complex cu o îmbinare interioară:
Exemplu: Găsiți tipul de produs, numărul și prețul computerelor și laptopurilor
1 2 3 4 5 6 7 8 | SELECTARE produs. `Tip` , pc. `Număr` , `Preț` FROM pc INNER JOIN produs PE pc. `Număr` = produs. `Număr` produs UNION SELECT. `Type` , caiet. `Număr` , `Preț` FROM notebook INNER JOIN produs PE notebook. `Număr` = produs. `Număr` COMANDAȚI DUPĂ `Preț` |
SELECT produs.`Tip` , buc.`Număr` , `Preț` FROM pc INNER JOIN produs ON pc.`Număr` = produs.`Număr` UNION SELECT produs.`Tip` , caiet.`Număr` , `Preț` FROM notebook INNER JOIN produs PE notebook.`Number` = produs.`Number` ORDER BY `Pret`
Rezultat:
SQL Union 1. Găsiți producătorul, numărul piesei și prețul tuturor laptopurilor și imprimantelor
SQL Union 2. Găsiți numerele și prețurile tuturor produselor produse de producătorul Rusia
SQL are facilități pentru efectuarea de operații de intersecție și diferență pe interogări - clauza INTERSECT (intersecție) și clauza EXCEPT (diferență). Aceste clauze funcționează într-un mod similar cu modul în care funcționează UNION: setul de rezultate include numai acele rânduri care sunt prezente în ambele interogări - INTERSECT , sau numai acele rânduri ale primei interogări care lipsesc în a doua - EXCEPTĂ . Dar problema este că multe SGBD-uri nu acceptă aceste propuneri. Dar există o cale de ieșire - folosind predicatul EXISTĂ.
Predicatul EXISTS evaluează la TRUE dacă subinterogarea returnează cel puțin câteva rânduri, în caz contrar EXISTS evaluează la FALS. Există și un predicat NU EXISTĂ, care face invers.
În mod obișnuit, EXISTS este utilizat în subinterogări dependente (de exemplu, IN).
EXISTĂ (subinterogare tabel)
Exemplu: Găsiți producători de computere care produc și laptopuri
SELECTARE DISTINCT Producător FROM produs AS pc_product WHERE Tip = "Computer" ȘI EXISTĂ (SELECTARE Producător FROM produs WHERE Tip = "Laptop" AND Manufacturer = pc_product.Manufacturer)
Rezultat:
Găsiți acei producători de computere care nu produc imprimante
Cuvintele cheie SOME și ANY sunt sinonime, așa că puteți utiliza oricare dintre ele în interogarea dvs. Rezultatul unei astfel de interogări va fi o coloană de valori.
Sintaxă:
< выражение>< оператор сравнения>UNELE |< подзапрос> ) <выражение><оператор сравнения>ORICE (<подзапрос>)
UNELE | ORICE (
Exemplu: Găsiți furnizori de computere ale căror numere nu sunt la vânzare (adică nu sunt în tabelul PC)
Date sursă tabel:
Rezultat:
În exemplu, predicatul Number = ANY(SELECT Number FROM pc) va returna valoarea TRUE atunci când Numărul din interogarea principală se găsește în lista de Numbers of table pc (returnată de subinterogare). În plus, NOT este folosit. Setul de rezultate va consta dintr-o coloană - Producător. Pentru a preveni afișarea de mai multe ori a unui producător, a fost introdus cuvântul de serviciu DISTINCT.
Acum să ne uităm la utilizarea cuvântului cheie ALL:
Exemplu: Găsiți numerele și prețurile laptopurilor care costă mai mult decât orice computer
Important: Este de remarcat faptul că, în general, o interogare cu ANY returnează un set de valori. Prin urmare, utilizarea unei subinterogări într-o clauză WHERE fără operatorii EXISTS , IN , ALL și ANY, care produc o valoare booleană, poate duce la o eroare de rulare a interogării.
Exemplu: Găsiți numerele și prețurile computerelor al căror cost depășește costul minim al laptopurilor
Această interogare este corectă deoarece expresia scalară Preț este comparată cu o subinterogare care returnează o singură valoare
Funcția LEFT reduce numărul de caractere specificat de al doilea argument din stânga unui șir:
STÂNGA (<строка>,<число>)
Funcția RIGHT returnează numărul specificat de caractere la dreapta dintr-o expresie șir:
CORECT(<строка>,<число>)
Exemplu: Tipăriți primele litere ale numelor tuturor producătorilor
SELECTAȚI DISTINCT LEFT(`Producător`, 1) DIN `produs`
Rezultat:
Exemplu: Tipăriți numele producătorilor care încep și se termină cu aceeași literă
Sintaxă:
SELECTAȚI `nume` , REPLACE(`nume` , "a", "aa") FROM `profesori`
În acest tutorial veți învăța cum să utilizați EXCEPT operator în SQL Server(Transact-SQL) cu sintaxă și exemple.
Instrucțiunea SQL Server EXCEPT(Transact-SQL) este folosit pentru a returna toate rândurile din prima instrucțiune SELECT care nu sunt returnate de a doua instrucțiune SELECT. Fiecare instrucțiune SELECT va defini un set de date. Operatorul EXCEPT va prelua toate înregistrările din primul set de date și apoi va elimina toate înregistrările din al doilea set de date din rezultate.
Explicaţie: Interogarea EXCEPT va returna înregistrări în zona umbrită gri. Acestea sunt înregistrări care există în SELECT 1 și nu în SELECT 2.
Fiecare instrucțiune SELECT dintr-o interogare EXCEPT trebuie să aibă același număr de câmpuri în seturile de rezultate cu tipuri de date similare.
EXCEPTĂ sintaxa instrucțiunii în SQL Server (Transact-SQL):
expresiile sunt coloanele sau calculele pe care doriți să le comparați între două instrucțiuni SELECT. Nu trebuie să fie aceleași câmpuri în fiecare instrucțiune SELECT, dar coloanele corespunzătoare trebuie să fie de tipuri de date similare.
tabele - tabele din care doriți să obțineți înregistrări. Trebuie să existe cel puțin un tabel listat în clauza FROM.
Condiții WHERE - opțional. Condiții care trebuie îndeplinite pentru înregistrările selectate.
Să ne uităm la un exemplu de instrucțiune EXCEPT în SQL Server (Transact-SQL) care returnează un singur câmp cu același tip de date.
De exemplu:
Transact-SQL
SELECTAȚI ID-ul_produsului DIN produse CU EXCEPȚIE SELECTAȚI ID-ul produsului DIN inventarul;
SELECT ID_produs DIN produse SELECT ID_produs DIN inventar ; |
Acest exemplu de instrucțiune EXCEPT returnează toate valorile product_id care sunt în tabelul de produse și nu în tabelul de inventar. Aceasta înseamnă că, dacă o valoare product_id există în tabelul de produse și există și în tabelul de inventar, valoarea product_id nu va apărea în rezultatele interogării EXCEPT.
În continuare, să ne uităm la un exemplu de interogare EXCEPT în SQL Server (Transact-SQL) care returnează mai mult de o coloană.
De exemplu:
Transact-SQL
În acest exemplu, interogarea EXCEPT returnează înregistrări din tabelul de contacte cu numele contact_id, last_name și first_name, care nu se potrivesc cu valoarea employee_id, last_name și first_name din tabelul angajați.
Există o singură regulă importantă de reținut atunci când utilizați instrucțiunea EXCEPT.
Ordinea, numărul și tipurile de date ale coloanelor trebuie să fie aceleași în toate interogările.
Conform standardului ANSI, operatorii de set UNION și EXCEPT au aceeași prioritate, dar operatorul INTERSECT este executat înaintea altor operatori de set. Vă recomandăm să controlați în mod explicit prioritatea operatorului folosind paranteze. Aceasta este, în general, o practică foarte bună.
Conform standardului ANSI, puteți utiliza o singură clauză ORDER BY într-o interogare. Introduceți-l la sfârșitul ultimei instrucțiuni SELECT. Pentru a evita ambiguitatea în denumirea coloanelor și a tabelelor, asigurați-vă că atribuiți același alias tuturor coloanelor din tabel care se potrivesc. De exemplu:
SELECT au_lname AS „nume”, au_fname AS „prenume” FROM autori EXCEPTĂ SELECT emp_lname AS „nume”, emp_fname AS „prenume” FROM angajați ORDER BY nume, prenume;
În plus, deoarece fiecare listă de coloane poate specifica coloane cu tipuri de date compatibile corespunzător, diferitele platforme RDBMS pot avea opțiuni diferite pentru a trata coloanele de lungimi diferite. De exemplu, dacă coloana au_lname din prima interogare din exemplul anterior este semnificativ mai lungă decât coloana emp_lname din a doua interogare, atunci diferite platforme pot avea reguli diferite pentru a determina lungimea rezultatului final. Dar, în general, platformele vor alege o dimensiune mai lungă (și mai puțin restrictivă) pentru rezultat.
Fiecare RDBMS poate avea propriile reguli pentru utilizarea unui nume de coloană dacă numele din listele de coloane sunt diferite. În general, sunt folosite numele coloanelor primei interogări.
Tipurile de date nu trebuie să fie identice, dar trebuie să fie compatibile. De exemplu, tipurile CHAR și VARCHAR sunt compatibile. În mod implicit, setul de rezultate din fiecare coloană va fi dimensiunea corespunzătoare celui mai mare tip din fiecare poziție particulară. De exemplu, o interogare care preia date din coloanele care conțin valori de tip VARCHAR(IO) și VARCHAR(15) ar folosi tipul și dimensiunea VARCHAR(15).
Nicio platformă nu acceptă clauza CORRESPONDANT )) EXCEPTĂ
(SELECTARE statemenr.2 | VALUES (expressionl, expression2 [, ...])) CU EXCEPȚIA
Vă permite să specificați una sau mai multe coloane specificate manual care sunt incluse în setul de rezultate final. (Acesta se numește un constructor de rând.) Clauza VALUES trebuie să specifice exact atâtea coloane câte interogările instrucțiunilor EXCEPT. Deși instrucțiunea EXCEPT DISTINCT nu este acceptată, echivalentul funcțional este EXCEPT. Clauza CORRESPONDANT nu este acceptată. În plus, tipurile de date LONG VARCHAR, LONG VARGRAPHIC, BLOB, CLOB, DBCLOB, DATALINK și tipurile de structură nu sunt utilizate în clauza EXCEPT, dar pot fi utilizate în clauza EXCEPT ALL.
Dacă setul de rezultate are o coloană care are același nume în toate instrucțiunile SELECT, atunci numele respectiv este folosit ca nume final pentru coloana returnată de instrucțiune. Dacă o anumită coloană este denumită diferit în instrucțiuni SELECT diferite, atunci trebuie să redenumiți coloana în toate interogările folosind aceeași clauză alias AS în toate.
Dacă o singură interogare folosește mai mulți operatori pentru a lucra cu seturi de date, cel inclus în paranteze este executat mai întâi. După aceasta, ordinea de execuție va fi de la stânga la dreapta. Cu toate acestea, toate instrucțiunile INTERSECT sunt executate înaintea instrucțiunilor UNION și EXCEPT. De exemplu:
SELECT empno FROM angajat WHERE workdept LIKE "E%" EXCEPT SELECT empno FROM emp_act WHERE projno IN (TF1000", TF2000", -AD3110")) UNION VALUES ("AA0001"), ("AB0002"), ("AC0003") ;
În exemplul de mai sus, ID-urile tuturor angajaților care lucrează în departamentul care începe cu „E” sunt preluate din tabelul angajaților, apoi ID-urile celor angajați în proiectele IF1000, IF200 și AD3110 sunt eliminate din tabelul conturi angajaților (emp_act ). În cele din urmă, sunt adăugate trei ID-uri suplimentare - AA0001, AB0002 și AC0003 folosind operatorul de set UNION.
MySQL nu acceptă operatorul EXCEPT. Alternativ, puteți utiliza operatorii NOT IN sau NOT EXISTS.
Operatorul INTERSECT preia rânduri identice din seturile de rezultate ale uneia sau mai multor interogări. În unele privințe, operatorul INTERSECT este foarte asemănător cu INNER JOIN.
INTERSECT aparține clasei de operatori pentru lucrul cu seturi de date (operator set). Alți astfel de operatori includ EXCEPT și UNION. Toți operatorii de set sunt utilizați pentru a manipula simultan seturile de rezultate a două sau mai multe interogări, de unde și numele lor.
Nu există restricții tehnice privind numărul de interogări în operatorul INTERSECT. Sintaxa generală este următoarea.
INTERSECT
] INTERSECT
Sunt incluse rânduri duplicat din toate seturile de rezultate.
DISTINCT
Rândurile duplicat sunt eliminate din toate seturile de rezultate înainte ca compararea să fie efectuată de operatorul INTERSECT. Coloanele cu valori goale (NULL) sunt considerate duplicate. Dacă nu este specificat nici cuvântul cheie ALL, nici DISTINCT, se presupune implicit DISTINCT.
CORESPUNZĂTOR
Specifică faptul că vor fi returnate numai coloanele care au același nume în ambele interogări, chiar dacă în ambele interogări este folosit caracterul wildcard (*).
Specifică că vor fi returnate numai coloanele numite, chiar dacă interogările întâlnesc alte coloane cu nume care se potrivesc. Această clauză trebuie utilizată împreună cu cuvântul cheie CORRESPONDING.
Există o singură regulă importantă de reținut atunci când lucrați cu operatorul INTERSECT.
Ordinea și numărul de coloane trebuie să fie aceleași în toate interogările. Tipurile de date ale coloanelor corespunzătoare trebuie, de asemenea, să fie compatibile.
De exemplu, tipurile CHAR și VARCHAR sunt compatibile. În mod implicit, setul de rezultate din fiecare coloană va fi dimensiunea corespunzătoare celui mai mare tip din fiecare poziție particulară.
Nicio platformă nu acceptă clauza CORRESPONDANT.
Conform standardului ANSI, operatorul INTERSECT are o prioritate mai mare decât alți operatori setați, deși prioritatea unor astfel de operatori este tratată diferit pe platforme diferite. Puteți controla în mod explicit prioritatea operatorului folosind paranteze. În caz contrar, SGBD-ul le poate executa în ordine de la stânga la dreapta sau de la primul până la ultimul.
Conform standardului ANSI, puteți utiliza o singură clauză ORDER BY într-o interogare. Introduceți-l la sfârșitul ultimei instrucțiuni SELECT. Pentru a evita ambiguitatea în denumirea coloanelor și a tabelelor, asigurați-vă că atribuiți același alias tuturor coloanelor din tabel care se potrivesc. De exemplu:
Pe platformele care nu acceptă operatorul INTERSECT, îl puteți înlocui cu o subinterogare FULL JOIN.
SELECT a.au_lname AS „nume”, a.au_fname AS „prenume” FROM autori AS a INTERSECT SELECT e.emp_lname AS „nume”, e.emp_fname AS „prenume” FROM angajați AS e ORDER BY nume, prenume;
Deoarece tipurile de date ale coloanelor din diferite instrucțiuni INTERSECT pot fi compatibile, diferitele platforme RDBMS pot avea opțiuni diferite pentru a trata coloanele de lungimi diferite. De exemplu, dacă coloana aujname din prima interogare din exemplul anterior este semnificativ mai lungă decât coloana empjname din a doua interogare, atunci diferite platforme pot avea reguli diferite pentru a determina lungimea rezultatului final. Dar, în general, platformele vor alege o dimensiune mai lungă (și mai puțin restrictivă) pentru rezultat.
Fiecare RDBMS poate avea propriile reguli pentru utilizarea unui nume de coloană dacă numele din listele de coloane sunt diferite. În mod obișnuit, sunt folosite numele coloanelor primei interogări.
Platforma DB2 acceptă cuvintele cheie ANSI INTERSECT și INTERSECT ALL plus clauza opțională VALUES.
(instrucțiune._SELECT_7 | VALORI (expresie7 [, ...])) INTERSECT
] (instrucțiune_SCJ_2 | VALORI (expr2 [, ...])) INTERSECT
Deși instrucțiunea INTERSECT DISTINCT nu este acceptată, echivalentul funcțional este INTERSECT. Clauza CORRESPONDANT nu este acceptată.
În plus, tipurile de date LONG VARCHAR, LONG VARGRAPHIC, BLOB, CLOB, DBCLOB, DATALINK și structura nu sunt utilizate în clauza INTERSECT, dar pot fi utilizate în clauza INTERSECT ALL.
Dacă setul de rezultate are o coloană care are același nume în toate instrucțiunile SELECT, atunci numele respectiv este folosit ca nume final pentru coloana returnată de instrucțiune. Cu toate acestea, dacă interogările folosesc nume diferite pentru o coloană, DB2 va genera un nume nou pentru coloana rezultată. După aceasta, devine inutilizabil în clauzele ORDER BY și FOR UPDATE.
Dacă o singură interogare folosește mai mulți operatori pentru a lucra cu seturi de date, cel inclus în paranteze este executat mai întâi. După aceasta, ordinea de execuție va fi de la stânga la dreapta. Cu toate acestea, toate instrucțiunile INTERSECT sunt executate înaintea instrucțiunilor UNION și EXCEPT, de exemplu:
SELECT empno FROM angajat WHERE workdept LIKE "E%" INTERSECT (SELECT empno FROM emp_act WHERE projno IN ("IF1000", "IF2000", "AD3110") UNION VALUES ("AA0001"), ("AB0002"), ("AC0003") "))
Exemplul de mai sus preia ID-urile tuturor angajaților care lucrează în departamentul care începe cu „E” din tabelul de angajați. Cu toate acestea, ID-urile sunt preluate numai dacă există și în tabelul de cont de angajat numit emp_act și participă la proiectele IF1000, IF200 și AD3110.