SQL-.
SQL
SQL Structured Query Language, . SQL , , .
SQL- : , . ( ), SQL.

SELECT FROM
SELECT
, . , SQL .
3 , Student ( SELECT
FROM
) . ID (studentID), (FirstName) (LastName) . (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)
CREATE TABLE
CREATE TABLE
. , .
CREATE TABLE table_name ( column_1 datatype, column_2 datatype, column_3 datatype );
ALTER TABLE
ALTER TABLE
. :
ALTER TABLE table_name ADD column_name datatype;
CHECK
CHECK
, .
CHECK
, , . CHECK
, .
Persons CHECK
(Age). , 18 .
CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, CHECK (Age>=18) );
CHECK
CHECK
:
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') );
WHERE (AND, OR, IN, BETWEEN LIKE)
WHERE
.
, , SELECT
WHERE
. WHERE
, 5 .
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)
SELECT
, WHERE
.
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)
UPDATE
UPDATE
.
WHERE
, . . :
UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;
( Name) Id 4:
UPDATE Person SET Name = Elton John WHERE Id = 4;
, . , JOIN
. :
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
(Manager) :
UPDATE Person SET Person.Manager = Department.Manager FROM Person JOIN Department ON Person.DepartmentID = Department.ID
GROUP BY
GROUP BY
.
GROUP BY
:
SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name;
HAVING
HAVING
, GROUP BY
. , .
HAVING
:
SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name HAVING COUNT(*) > value;
AVG()
AVG
, , , SQL.
:
SELECT groupingField, AVG(num_field) FROM table1 GROUP BY groupingField
Student:
SELECT studentID, FullName, AVG(sat_score) FROM student GROUP BY studentID, FullName;
AS
AS
.
SELECT user_only_num1 AS AgeOfServer, (user_only_num1 - warranty_period) AS NonWarrantyPeriod FROM server_table
.
+-------------+------------------------+ | AgeOfServer | NonWarrantyPeriod | +-------------+------------------------+ | 36 | 24 | | 24 | 12 | | 61 | 49 | | 12 | 0 | | 6 | -6 | | 0 | -12 | | 36 | 24 | | 36 | 24 | | 24 | 12 | +-------------+------------------------+
, AS JOIN.
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
.
+-------------+------------+-----------+-----------------+--------------+ | 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 | +-------------+------------+-----------+-----------------+--------------+
ORDER BY
ORDER BY
SELECT
. (FullName) . (ASC
), DESC
.
SELECT studentID, FullName, sat_score FROM student ORDER BY FullName DESC;
COUNT
COUNT
.
COUNT:
- ( Group by)
- ( Group By)
SQL . , COUNT
AS
.
SELECT count(*) AS studentCount FROM student;
DELETE
DELETE
.
! , . WHERE
, . :
DELETE FROM table_name WHERE condition;
Person Id 3:
DELETE FROM Person WHERE Id = 3;
INNER JOIN
JOIN
, , , .
SELECT * FROM A x JOIN B y ON y.aId = x.Id
LEFT JOIN
LEFT JOIN
. . , null
.
SELECT * FROM A x LEFT JOIN B y ON y.aId = x.Id
RIGHT JOIN
RIGHT JOIN
. , , . , null
.
SELECT * FROM A x RIGHT JOIN B y ON y.aId = x.Id
FULL OUTER JOIN
FULL OUTER JOIN
, . , , . .
SELECT Customers.CustomerName, Orders.OrderID FROM Customers FULL OUTER JOIN Orders ON Customers.CustomerID=Orders.CustomerID ORDER BY Customers.CustomerName
INSERT
INSERT
.
INSERT INTO table_name (column_1, column_2, column_3) VALUES (value_1, 'value_2', value_3);
LIKE
LIKE
WHERE
HAVING
( GROUP BY
) , .
SQL , FullName Monique 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)
LIKE
NOT
, , , , . SQL , FULL NAME cer Pau 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)