Osnovni ukazi SQL - seznam poizvedb in izjav v zbirki podatkov, ki bi jih morali poznati
SQL je kratica za jezik strukturiranih poizvedb. Ukazi SQL so navodila, ki se uporabljajo za komunikacijo z bazo podatkov za izvajanje nalog, funkcij in poizvedb s podatki.
Ukaze SQL lahko uporabljamo za iskanje po zbirki podatkov in za izvajanje drugih funkcij, kot je ustvarjanje tabel, dodajanje podatkov v tabele, spreminjanje podatkov in spuščanje tabel.
Tu je seznam osnovnih ukazov SQL (včasih imenovani klavzuli), ki jih morate vedeti, če boste delali z SQL.
SELECT in FROM
SELECTDel poizvedbo določa, kateri stolpci podatkov, ki kažejo v rezultatih. Obstajajo tudi možnosti, ki jih lahko uporabite za prikaz podatkov, ki niso stolpci tabele.
Spodnji primer kaže trije stolpci SELECTed FROMna "študent" tabelo in eno izračunan stolpec. V zbirki podatkov so shranjeni študentski ID, ime in priimek študenta. Stolpca Ime in Priimek lahko združimo, da ustvarimo izračunani stolpec FullName.
SELECT studentID, FirstName, LastName, FirstName + ' ' + LastName AS FullName FROM student;+-----------+-------------------+------------+------------------------+ | studentID | FirstName | LastName | FullName | +-----------+-------------------+------------+------------------------+ | 1 | Monique | Davis | Monique Davis | | 2 | Teri | Gutierrez | Teri Gutierrez | | 3 | Spencer | Pautier | Spencer Pautier | | 4 | Louis | Ramsey | Louis Ramsey | | 5 | Alvin | Greene | Alvin Greene | | 6 | Sophie | Freeman | Sophie Freeman | | 7 | Edgar Frank "Ted" | Codd | Edgar Frank "Ted" Codd | | 8 | Donald D. | Chamberlin | Donald D. Chamberlin | | 9 | Raymond F. | Boyce | Raymond F. Boyce | +-----------+-------------------+------------+------------------------+ 9 rows in set (0.00 sec)USTVARI TABELO
CREATE TABLEnaredi samo tisto, kar se sliši: ustvari tabelo v zbirki podatkov. Določite lahko ime tabele in stolpce, ki naj bodo v tabeli.
CREATE TABLE table_name ( column_1 datatype, column_2 datatype, column_3 datatype );ALTER TABLE
ALTER TABLEspremeni strukturo tabele. Tukaj je opisano, kako dodate stolpec v zbirko podatkov:
ALTER TABLE table_name ADD column_name datatype;PREVERITE
CHECKOmejitev se uporablja omejiti obseg vrednosti, ki jih lahko damo v kolono.
Če določite CHECKomejitev za en stolpec, to dovoljuje le nekatere vrednosti za ta stolpec. Če CHECKv tabeli določite omejitev, lahko vrednosti v določenih stolpcih omeji na podlagi vrednosti v drugih stolpcih v vrstici.
Naslednji SQL ustvari CHECKomejitev v stolpcu »Starost«, ko je ustvarjena tabela »Osebe«. Za CHECKomejitev zagotavlja, da ne more imeti vsako osebo pod 18 let.
CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, CHECK (Age>=18) );Če želite omogočiti poimenovanje CHECKomejitve in določiti CHECKomejitev za več stolpcev, uporabite naslednjo sintakso SQL:
CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, City varchar(255), CONSTRAINT CHK_Person CHECK (Age>=18 AND City="Sandnes") );KJE
(AND ,OR , IN, BETWEENIn LIKE)
WHEREKlavzula se uporablja, da se omeji število vrstic vrnjenih.
Kot primer, najprej bomo vam pokaže SELECTizjavo in rezultate , ne da bi z WHEREizjavo. Nato bomo dodali WHEREizjavo, ki uporablja vseh pet zgornjih kvalifikacij.
SELECT studentID, FullName, sat_score, rcd_updated FROM student;+-----------+------------------------+-----------+---------------------+ | studentID | FullName | sat_score | rcd_updated | +-----------+------------------------+-----------+---------------------+ | 1 | Monique Davis | 400 | 2017-08-16 15:34:50 | | 2 | Teri Gutierrez | 800 | 2017-08-16 15:34:50 | | 3 | Spencer Pautier | 1000 | 2017-08-16 15:34:50 | | 4 | Louis Ramsey | 1200 | 2017-08-16 15:34:50 | | 5 | Alvin Greene | 1200 | 2017-08-16 15:34:50 | | 6 | Sophie Freeman | 1200 | 2017-08-16 15:34:50 | | 7 | Edgar Frank "Ted" Codd | 2400 | 2017-08-16 15:35:33 | | 8 | Donald D. Chamberlin | 2400 | 2017-08-16 15:35:33 | | 9 | Raymond F. Boyce | 2400 | 2017-08-16 15:35:33 | +-----------+------------------------+-----------+---------------------+ 9 rows in set (0.00 sec)Zdaj bomo ponovili SELECTpoizvedbo, vendar bomo vrnjene vrstice omejili z uporabo WHEREstavka.
STUDENT studentID, FullName, sat_score, recordUpdated FROM student WHERE (studentID BETWEEN 1 AND 5 OR studentID = 8) AND sat_score NOT IN (1000, 1400);+-----------+----------------------+-----------+---------------------+ | studentID | FullName | sat_score | rcd_updated | +-----------+----------------------+-----------+---------------------+ | 1 | Monique Davis | 400 | 2017-08-16 15:34:50 | | 2 | Teri Gutierrez | 800 | 2017-08-16 15:34:50 | | 4 | Louis Ramsey | 1200 | 2017-08-16 15:34:50 | | 5 | Alvin Greene | 1200 | 2017-08-16 15:34:50 | | 8 | Donald D. Chamberlin | 2400 | 2017-08-16 15:35:33 | +-----------+----------------------+-----------+---------------------+ 5 rows in set (0.00 sec)NADGRADNJA
Za posodobitev zapisa v tabeli uporabite UPDATEstavek.
S WHEREpogojem določite, katere zapise želite posodobiti. Naenkrat je mogoče posodobiti enega ali več stolpcev. Sintaksa je:
UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;Tu je primer posodabljanja imena zapisa z Id 4:
UPDATE Person SET Name = “Elton John” WHERE Id = 4;Stolpce v tabeli lahko posodobite tudi z uporabo vrednosti iz drugih tabel. Uporabite JOINstavek za pridobivanje podatkov iz več tabel. Sintaksa je:
UPDATE table_name1 SET table_name1.column1 = table_name2.columnA table_name1.column2 = table_name2.columnB FROM table_name1 JOIN table_name2 ON table_name1.ForeignKey = table_name2.KeyTu je primer posodabljanja upravitelja vseh zapisov:
UPDATE Person SET Person.Manager = Department.Manager FROM Person JOIN Department ON Person.DepartmentID = Department.IDSKUPINA PO
GROUP BY omogoča kombiniranje vrstic in združevanje podatkov.
Tu je sintaksa GROUP BY:
SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name;IMAJO
HAVINGvam omogoča filtriranje podatkov, združenih v GROUP BYstavku, tako da uporabnik dobi omejen nabor zapisov za ogled.
Tu je sintaksa HAVING:
SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name HAVING COUNT(*) > value;AVG ()
»Povprečje« se uporablja za izračun povprečja številskega stolpca iz nabora vrstic, ki jih vrne stavek SQL.
Tu je sintaksa za uporabo funkcije:
SELECT groupingField, AVG(num_field) FROM table1 GROUP BY groupingFieldTu je primer uporabe študentske tabele:
SELECT studentID, FullName, AVG(sat_score) FROM student GROUP BY studentID, FullName;AS
AS omogoča preimenovanje stolpca ali tabele z uporabo vzdevka.
SELECT user_only_num1 AS AgeOfServer, (user_only_num1 - warranty_period) AS NonWarrantyPeriod FROM server_tableRezultat je rezultat, kot je prikazano spodaj.
+-------------+------------------------+ | AgeOfServer | NonWarrantyPeriod | +-------------+------------------------+ | 36 | 24 | | 24 | 12 | | 61 | 49 | | 12 | 0 | | 6 | -6 | | 0 | -12 | | 36 | 24 | | 36 | 24 | | 24 | 12 | +-------------+------------------------+AS lahko uporabite tudi za dodelitev imena tabeli, da olajšate sklicevanje v združitvah.
SELECT ord.product, ord.ord_number, ord.price, cust.cust_name, cust.cust_number FROM customer_table AS cust JOIN order_table AS ord ON cust.cust_number = ord.cust_numberRezultat je rezultat, kot je prikazano spodaj.
+-------------+------------+-----------+-----------------+--------------+ | product | ord_number | price | cust_name | cust_number | +-------------+------------+-----------+-----------------+--------------+ | RAM | 12345 | 124 | John Smith | 20 | | CPU | 12346 | 212 | Mia X | 22 | | USB | 12347 | 49 | Elise Beth | 21 | | Cable | 12348 | 0 | Paul Fort | 19 | | Mouse | 12349 | 66 | Nats Back | 15 | | Laptop | 12350 | 612 | Mel S | 36 | | Keyboard| 12351 | 24 | George Z | 95 | | Keyboard| 12352 | 24 | Ally B | 55 | | Air | 12353 | 12 | Maria Trust | 11 | +-------------+------------+-----------+-----------------+--------------+NAROČITE PO
ORDER BY gives us a way to sort the result set by one or more of the items in the SELECT section. Here is an SQL sorting the students by FullName in descending order. The default sort order is ascending (ASC) but to sort in the opposite order (descending) you use DESC.
SELECT studentID, FullName, sat_score FROM student ORDER BY FullName DESC;COUNT
COUNT will count the number of rows and return that count as a column in the result set.
Here are examples of what you would use COUNT for:
- Counting all rows in a table (no group by required)
- Counting the totals of subsets of data (requires a Group By section of the statement)
This SQL statement provides a count of all rows. Note that you can give the resulting COUNT column a name using “AS”.
SELECT count(*) AS studentCount FROM student; DELETE
DELETE is used to delete a record in a table.
Be careful. You can delete all records of the table or just a few. Use the WHERE condition to specify which records you want to delete. The syntax is:
DELETE FROM table_name WHERE condition;Here is an example deleting from the table Person the record with Id 3:
DELETE FROM Person WHERE Id = 3;INNER JOIN
JOIN, also called Inner Join, selects records that have matching values in two tables.
SELECT * FROM A x JOIN B y ON y.aId = x.IdLEFT JOIN
A LEFT JOIN returns all rows from the left table, and the matched rows from the right table. Rows in the left table will be returned even if there was no match in the right table. The rows from the left table with no match in the right table will have null for right table values.
SELECT * FROM A x LEFT JOIN B y ON y.aId = x.IdRIGHT JOIN
A RIGHT JOIN returns all rows from the right table, and the matched rows from the left table. Opposite of a left join, this will return all rows from the right table even where there is no match in the left table. Rows in the right table that have no match in the left table will have null values for left table columns.
SELECT * FROM A x RIGHT JOIN B y ON y.aId = x.Id FULL OUTER JOIN
A FULL OUTER JOIN returns all rows for which there is a match in either of the tables. So if there are rows in the left table that do not have matches in the right table, those will be included. Also, if there are rows in the right table that do not have matches in the left table, those will be included.
SELECT Customers.CustomerName, Orders.OrderID FROM Customers FULL OUTER JOIN Orders ON Customers.CustomerID=Orders.CustomerID ORDER BY Customers.CustomerNameINSERT
INSERT is a way to insert data into a table.
INSERT INTO table_name (column_1, column_2, column_3) VALUES (value_1, 'value_2', value_3);LIKE
LIKE se uporablja v WHEREali HAVING(kot del GROUP BY) za omejevanje izbranih vrstic na postavke, če je v stolpcu določen vzorec znakov.
Ta SQL bo izbral študente, ki se FullNamezačnejo z »Monique« ali končajo z »Greene«.
SELECT studentID, FullName, sat_score, rcd_updated FROM student WHERE FullName LIKE 'Monique%' OR FullName LIKE '%Greene'; +-----------+---------------+-----------+---------------------+ | studentID | FullName | sat_score | rcd_updated | +-----------+---------------+-----------+---------------------+ | 1 | Monique Davis | 400 | 2017-08-16 15:34:50 | | 5 | Alvin Greene | 1200 | 2017-08-16 15:34:50 | +-----------+---------------+-----------+---------------------+ 2 rows in set (0.00 sec)Vi lahko postavite NOTpred LIKEizključiti vrstice z nizom vzorec, namesto da jih izberete. Ta SQL izključuje zapise, ki vsebujejo “cer Pau” in “Ted” v stolpcu FullName.
SELECT studentID, FullName, sat_score, rcd_updated FROM student WHERE FullName NOT LIKE '%cer Pau%' AND FullName NOT LIKE '%"Ted"%';+-----------+----------------------+-----------+---------------------+ | studentID | FullName | sat_score | rcd_updated | +-----------+----------------------+-----------+---------------------+ | 1 | Monique Davis | 400 | 2017-08-16 15:34:50 | | 2 | Teri Gutierrez | 800 | 2017-08-16 15:34:50 | | 4 | Louis Ramsey | 1200 | 2017-08-16 15:34:50 | | 5 | Alvin Greene | 1200 | 2017-08-16 15:34:50 | | 6 | Sophie Freeman | 1200 | 2017-08-16 15:34:50 | | 8 | Donald D. Chamberlin | 2400 | 2017-08-16 15:35:33 | | 9 | Raymond F. Boyce | 2400 | 2017-08-16 15:35:33 | +-----------+----------------------+-----------+---------------------+ 7 rows in set (0.00 sec)
