Põhilised SQL-käsud - andmebaasipäringute ja -lausete loend, mida peaksite teadma

SQL tähistab struktureeritud päringukeelt. SQL-i käsud on juhised, mida kasutatakse andmebaasiga suhtlemiseks, et täita andmetega ülesandeid, funktsioone ja päringuid.

SQL-i käske saab kasutada andmebaasist otsimiseks ja muude funktsioonide tegemiseks, näiteks tabelite loomiseks, tabelitesse andmete lisamiseks, andmete muutmiseks ja tabelite kukutamiseks.

Siin on nimekiri põhilistest SQL-käskudest (mida mõnikord nimetatakse ka klausliteks), mida peaksite teadma, kas kavatsete SQL-iga töötada.

SELECT ja FROM

SELECTOsa päringu määrab, milliseid veerge andmete näidata tulemusi. On ka valikuid, mida saate rakendada andmete kuvamiseks, mis pole tabeli veerg.

Järgnev näide demonstreerib kolm veergu SELECTed FROM"õpilane" laud ja üks arvutatud veerus. Andmebaasi salvestatakse õpilase ID, eesnimi ja perekonnanimi. Me saame veerud Eesnimi ja Perekonnanimi kombineerida, et luua arvutatud veerg 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)

LOE TABEL

CREATE TABLEteeb just nii, nagu see kõlab: see loob andmebaasi tabeli. Saate määrata tabeli nime ja veerud, mis peaksid tabelis olema.

CREATE TABLE table_name ( column_1 datatype, column_2 datatype, column_3 datatype );

MUUDA TABEL

ALTER TABLEmuudab tabeli struktuuri. Veergu andmebaasi lisamiseks toimige järgmiselt.

ALTER TABLE table_name ADD column_name datatype;

KONTROLLIMA

CHECKKitsendus kasutatakse piirata Väärtusulatuse mida saab asetada kolonnis.

Kui määrate CHECKühele veerule piirangu, lubab see selle veeru jaoks ainult teatud väärtusi. Kui määrate CHECKtabelile piirangu, võib see piirata teatud veergude väärtusi rea muude veergude väärtuste põhjal.

Järgmine SQL loob CHECKveerus „Vanus” piirangu, kui tabel „Isikud” luuakse. CHECKPiirang tagab, et te ei saa olla isik, alla 18-aastased.

CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, CHECK (Age>=18) );

CHECKPiirangu nimetamise lubamiseks ja piirangu määratlemiseks CHECKmitmel veerul kasutage järgmist SQL-i süntaksit:

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") );

KUS

(AND ,OR , IN, BETWEENNing LIKE)

WHEREKlausel kasutatakse piirata ridade arvu tagastatakse.

Näiteks esimese näitame teile SELECTavalduse ja tulemused ilmaWHERE avaldusega. Siis lisame WHEREavalduse, mis kasutab kõiki viit ülaltoodud kvalifikatsiooni.

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)

Nüüd kordame SELECTpäringut, kuid piirame WHEREavalduse abil tagastatud ridu .

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)

UUENDAMINE

Kirje värskendamiseks tabelis kasutate UPDATElauset.

Kasutage WHEREseisukorras määrata, millised andmed, mida soovite värskendada. Korraga on võimalik värskendada ühte või mitut veergu. Süntaks on:

UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;

Siin on näide kirje nime värskendamisest ID-ga 4:

UPDATE Person SET Name = “Elton John” WHERE Id = 4;

Tabeli veerge saate värskendada ka teiste tabelite väärtuste abil. Kasutage JOINklauslit andmete saamiseks mitmest tabelist. Süntaks on:

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.Key

Siin on näide kõigi kirjete halduri värskendamisest:

UPDATE Person SET Person.Manager = Department.Manager FROM Person JOIN Department ON Person.DepartmentID = Department.ID

GRUPP JÄRGI

GROUP BY võimaldab teil ridu ja andmeid koondada.

Siin on süntaks GROUP BY:

SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name;

VÕIMALIK

HAVINGvõimaldab teil filtreerida GROUP BYklausliga koondatud andmeid nii, et kasutaja saaks vaadata piiratud kirjete kogumit.

Siin on süntaks HAVING:

SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name HAVING COUNT(*) > value;

AVG ()

"Keskmist" kasutatakse arvväärtuse keskmise arvutamiseks SQL-i lausega tagastatud ridade hulgast.

Siin on funktsiooni kasutamise süntaks:

SELECT groupingField, AVG(num_field) FROM table1 GROUP BY groupingField

Siin on näide õpilaslaua kasutamisest:

SELECT studentID, FullName, AVG(sat_score) FROM student GROUP BY studentID, FullName;

AS

AS võimaldab veeru või tabeli ümber nimetada varjunime abil.

SELECT user_only_num1 AS AgeOfServer, (user_only_num1 - warranty_period) AS NonWarrantyPeriod FROM server_table

Selle tulemuseks on väljund nagu allpool.

+-------------+------------------------+ | AgeOfServer | NonWarrantyPeriod | +-------------+------------------------+ | 36 | 24 | | 24 | 12 | | 61 | 49 | | 12 | 0 | | 6 | -6 | | 0 | -12 | | 36 | 24 | | 36 | 24 | | 24 | 12 | +-------------+------------------------+

Samuti saate AS-i abil tabelile nime määrata, et hõlbustada liitumistes viitamist.

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_number

Selle tulemuseks on väljund nagu allpool.

+-------------+------------+-----------+-----------------+--------------+ | 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 | +-------------+------------+-----------+-----------------+--------------+

TELLI

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.Id

LEFT 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.Id

RIGHT 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.CustomerName

INSERT

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  kasutatakse WHEREvõi HAVING(osana GROUP BY), et piirata valitud ridu üksustega, kui veerus on teatud tähemärk.

See SQL valib õpilased, kelle FullNamealgus algab tähega “Monique” või lõpeb “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)

Võite paigutada NOTenne, LIKEet stringide mustriga read välja jätta, selle asemel et neid valida. See SQL välistab kirjed, mis sisaldavad veerus FullName märke „cer Pau” ja „Ted”.

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)