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)
