Pamata SQL komandas - datu bāzu vaicājumu un paziņojumu saraksts, kas jums jāzina

SQL nozīmē Strukturēta vaicājumu valoda. SQL komandas ir instrukcijas, ko izmanto, lai sazinātos ar datu bāzi, lai veiktu uzdevumus, funkcijas un vaicājumus ar datiem.

SQL komandas var izmantot, lai meklētu datu bāzē un veiktu citas funkcijas, piemēram, tabulu izveidi, datu pievienošanu tabulām, datu modificēšanu un tabulu nomešanu.

Šeit ir saraksts ar pamata SQL komandām (dažreiz sauktām par klauzulām), kas jums jāzina, vai strādājat ar SQL.

SELECT un FROM

SELECTDaļa vaicājuma nosaka, kura kolonnas datiem, lai parādītu rezultātos. Ir arī iespējas, kuras varat izmantot, lai parādītu datus, kas nav tabulas kolonna.

Turpmāk rāda piemērs trīs slejas SELECTed FROMuz "students", galdu un vienu aprēķināto kolonnu. Datu bāzē tiek glabāti studenta ID, vārds un uzvārds. Mēs varam apvienot slejas Vārds un Uzvārds, lai izveidotu aprēķināto kolonnu Pilns nosaukums.

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)

IZVEIDOT GALDA

CREATE TABLEdara tikai to, kā izklausās: datu bāzē izveido tabulu. Jūs varat norādīt tabulas nosaukumu un kolonnas, kurām vajadzētu būt tabulā.

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

MAINĪT TABULU

ALTER TABLEmaina tabulas struktūru. Lūk, kā jūs pievienotu kolonnu datu bāzei:

ALTER TABLE table_name ADD column_name datatype;

PĀRBAUDIET

CHECKIerobežojums tiek izmantots, lai ierobežotu vērtību diapazonu, ko var novietot kolonnā.

Ja CHECKvienā kolonnā definējat ierobežojumu, šai kolonnai ir atļautas tikai noteiktas vērtības. Ja CHECKtabulai definējat ierobežojumu, tas var ierobežot atsevišķu kolonnu vērtības, pamatojoties uz vērtībām citās rindas kolonnās.

Šī SQL izveido CHECKierobežojumu kolonnā “Vecums”, kad tiek izveidota tabula “Personas”. Par CHECKierobežojums nodrošina, ka jums nevar būt jebkura persona ir jaunāka par 18 gadiem.

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

Lai atļautu piešķirt CHECKierobežojumu nosaukumam un ierobežojuma definēšanai CHECKvairākām kolonnām, izmantojiet šādu SQL sintaksi:

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

KUR

(AND ,OR , IN, BETWEEN, Un LIKE)

WHEREKlauzula tiek izmantots, lai ierobežotu skaitu rindu atpakaļ.

Piemēram, vispirms mēs parādīs SELECTpaziņojumu un rezultātus bez ar WHEREpaziņojumu. Tad mēs pievienosim WHEREpaziņojumu, kurā izmantoti visi pieci iepriekš minētie kvalifikatori.

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)

Tagad mēs atkārtosim SELECTvaicājumu, bet mēs ierobežosim atgrieztās rindas, izmantojot WHEREpaziņojumu.

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)

ATJAUNINĀT

Lai atjauninātu ierakstu tabulā, izmantojiet UPDATEpaziņojumu.

Izmantojiet WHEREnosacījumu, lai norādītu, kurus ierakstus vēlaties atjaunināt. Vienlaikus ir iespējams atjaunināt vienu vai vairākas kolonnas. Sintakse ir:

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

Šeit ir piemērs, kā atjaunināt ieraksta nosaukumu ar ID 4:

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

Varat arī atjaunināt tabulas kolonnas, izmantojot citu tabulu vērtības. Izmantojiet JOINklauzulu, lai iegūtu datus no vairākām tabulām. Sintakse ir:

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

Šeit ir visu ierakstu pārvaldnieka atjaunināšanas piemērs:

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

GRUPA PĒC

GROUP BY ļauj apvienot rindas un apkopot datus.

Šeit ir sintakse GROUP BY:

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

IR

HAVINGļauj filtrēt datus, kas apkopoti ar GROUP BYklauzulu, lai lietotājs iegūtu ierobežotu ierakstu kopu, lai tos skatītu.

Šeit ir sintakse HAVING:

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

AVG ()

“Vidējais” tiek izmantots, lai aprēķinātu skaitliskās kolonnas vidējo vērtību no rindu kopas, ko atdod SQL priekšraksts.

Funkcijas izmantošanas sintakse:

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

Šeit ir piemērs, izmantojot studentu tabulu:

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

AS

AS ļauj pārdēvēt kolonnu vai tabulu, izmantojot aizstājvārdu.

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

Tā rezultātā produkcija ir šāda.

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

Varat arī izmantot AS, lai piešķirtu tabulai vārdu, lai būtu vieglāk atsaukties savienojumos.

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

Tā rezultātā produkcija ir šāda.

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

SAKĀRTOT PĒC

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  tiek izmantots WHEREvai HAVING(kā daļa no GROUP BY), lai ierobežotu atlasītās rindas līdz vienumiem, ja kolonnā ir noteikts rakstzīmju modelis.

Šī SQL atlasīs studentus, kuri FullNamesākas ar “Monique” vai beidzas ar “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)

Varat ievietot NOTiepriekš, LIKElai izslēgtu rindas ar virknes rakstu, nevis tās atlasītu. Šajā SQL tiek izslēgti ieraksti, kas kolonnā FullName satur “cer Pau” un “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)