SQL je kratica za jezik strukturiranih upita. SQL naredbe su upute koje se koriste za komunikaciju s bazom podataka za izvršavanje zadataka, funkcija i upita s podacima.
SQL naredbe mogu se koristiti za pretraživanje baze podataka i za obavljanje drugih funkcija poput stvaranja tablica, dodavanja podataka u tablice, mijenjanja podataka i ispuštanja tablica.
Evo popisa osnovnih SQL naredbi (koje se ponekad nazivaju klauzule) koje biste trebali znati hoćete li raditi s SQL-om.
ODABERI i OD
SELECTDio upita određuje koji kolone podataka za prikaz u rezultatima. Postoje i opcije koje možete primijeniti za prikaz podataka koji nisu stupac tablice.
Primjer u nastavku pokazuje tri stupca SELECTed FROMje „student” stol i jednu izračunati stupac. Baza podataka pohranjuje studentID, FirstName i LastName učenika. Možemo kombinirati stupce Ime i Prezime kako bismo stvorili izračunat stupac Puno ime.
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)IZRADI TABELU
CREATE TABLEradi upravo ono kako zvuči: stvara tablicu u bazi podataka. Možete odrediti naziv tablice i stupce koji bi trebali biti u tablici.
CREATE TABLE table_name ( column_1 datatype, column_2 datatype, column_3 datatype );ALTER TABELA
ALTER TABLEmijenja strukturu tablice. Evo kako biste dodali stupac u bazu podataka:
ALTER TABLE table_name ADD column_name datatype;ČEK
CHECKOgraničenje se koristi za ograničavanje raspona vrijednosti koji se može smjestiti u koloni.
Ako definirate CHECKograničenje za jedan stupac, to omogućuje samo određene vrijednosti za ovaj stupac. Ako definirate CHECKograničenje na tablici, to može ograničiti vrijednosti u određenim stupcima na temelju vrijednosti u drugim stupcima u retku.
Sljedeći SQL stvara CHECKograničenje na stupcu "Dob" kada se kreira tablica "Osobe". U CHECKograničenje osigurava da ne može imati bilo koju osobu ispod 18 godina.
CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, CHECK (Age>=18) );Da biste omogućili imenovanje CHECKograničenja i za definiranje CHECKograničenja na više stupaca, upotrijebite sljedeću SQL sintaksu:
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") );GDJE
(AND ,OR , IN, BETWEEN, I LIKE)
WHEREKlauzula se koristi za ograničavanje broja vraćenih redaka.
Kao primjer, prvo ćemo vam pokazati SELECTizjave i rezultate bez jednog WHEREgubitka. Tada ćemo dodati WHEREizjavu koja koristi svih pet kvalifikacija gore.
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)Sada ćemo ponoviti SELECTupit, ali ograničit ćemo retke vraćene pomoću WHEREizraza.
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)AŽURIRANJE
Da biste ažurirali zapis u tablici, koristite UPDATEizjavu.
Upotrijebite WHEREuvjet da odredite koje zapise želite ažurirati. Moguće je ažurirati jedan ili više stupaca odjednom. Sintaksa je:
UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;Evo primjera ažuriranja naziva zapisa pomoću Id 4:
UPDATE Person SET Name = “Elton John” WHERE Id = 4;Također možete ažurirati stupce u tablici koristeći vrijednosti iz drugih tablica. Koristite JOINklauzulu za dobivanje podataka iz više tablica. 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.KeyEvo primjera ažuriranja upravitelja svih zapisa:
UPDATE Person SET Person.Manager = Department.Manager FROM Person JOIN Department ON Person.DepartmentID = Department.IDGRUPIRAJ PO
GROUP BY omogućuje kombiniranje redaka i agregiranje podataka.
Evo sintakse GROUP BY:
SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name;IMAJUĆI
HAVINGomogućuje filtriranje podataka prikupljenih GROUP BYklauzulom tako da korisnik dobiva ograničeni skup zapisa za prikaz.
Evo sintakse HAVING:
SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name HAVING COUNT(*) > value;AVG ()
“Prosjek” se koristi za izračunavanje prosjeka numeričkog stupca iz skupa redaka koje vraća SQL izraz.
Evo sintakse za upotrebu funkcije:
SELECT groupingField, AVG(num_field) FROM table1 GROUP BY groupingFieldEvo primjera korištenja studentske tablice:
SELECT studentID, FullName, AVG(sat_score) FROM student GROUP BY studentID, FullName;KAO
AS omogućuje vam preimenovanje stupca ili tablice pomoću zamjenskog imena.
SELECT user_only_num1 AS AgeOfServer, (user_only_num1 - warranty_period) AS NonWarrantyPeriod FROM server_tableTo rezultira izlazom kao u nastavku.
+-------------+------------------------+ | AgeOfServer | NonWarrantyPeriod | +-------------+------------------------+ | 36 | 24 | | 24 | 12 | | 61 | 49 | | 12 | 0 | | 6 | -6 | | 0 | -12 | | 36 | 24 | | 36 | 24 | | 24 | 12 | +-------------+------------------------+AS također možete koristiti za dodjeljivanje imena tablici kako biste olakšali pozivanje u pridruživanjima.
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_numberTo rezultira izlazom kao u nastavku.
+-------------+------------+-----------+-----------------+--------------+ | 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 | +-------------+------------+-----------+-----------------+--------------+NARUČ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 is used in a WHERE or HAVING (as part of the GROUP BY) to limit the selected rows to the items when a column has a certain pattern of characters contained in it.
This SQL will select students that have FullName starting with “Monique” or ending with “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)You can place NOT before LIKE to exclude the rows with the string pattern instead of selecting them. This SQL excludes records that contain “cer Pau” and “Ted” in the FullName column.
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)