Инструкция языка sql order by определяет - Большая энциклопедия инструкций и руководств

В этом учебном материале вы узнаете, как использовать SQL оператор ORDER BY с синтаксисом и примерами.

Описание

SQL оператор ORDER BY используется для сортировки записей в наборе результатов запроса SELECT.

Синтаксис

Синтаксис для оператора ORDER BY в SQL.

SELECT expressions
FROM tables
[WHERE conditions]
ORDER BY expression [ ASC | DESC ];

Параметры или аргумент

expressions: Столбцы или расчеты, которые вы хотите получить
tables: Таблицы, из которых вы хотите получить записи. В предложении FROM должна быть указана хотя бы одна таблица
WHERE conditions: Необязательный. Условия, которые должны быть выполнены для записей, которые будут выбраны
ASC: Необязательный. ASC сортирует результирующий набор в порядке возрастания по expressions. Это поведение по умолчанию, если модификатор не указан.
DESC: Необязательный. DESC сортирует результирующий набор в порядке убывания по expressions

Примечание

Если модификатор ASC или DESC не указан в предложении ORDER BY, результаты будут отсортированы по expressions в порядке возрастания. Это эквивалентно ORDER BY expressions ASC

Пример — сортировка результатов по возрастанию

Чтобы отсортировать результаты в порядке возрастания, вы можете указать атрибут ASC. Если после поля в предложении ORDER BY не указано значение (ASC или DESC), порядок сортировки по умолчанию будет соответствовать возрастающему. Давайте рассмотрим это дальше.
В этом примере у нас есть таблица customers со следующими данными:

customer_id	first_name	last_name	favorite_website
4000	Justin	Bieber	google.com
5000	Selena	Gomez	bing.com
6000	Mila	Kunis	yahoo.com
7000	Tom	Cruise	oracle.com
8000	Johnny	Depp	NULL
9000	Russell	Crowe	google.com

Введите следующий SQL оператор.

SELECT *

FROM customers

ORDER BY last_name;

Будет выбрано 6 записей. Вот результаты, которые вы должны получить.

customer_id	first_name	last_name	favorite_website
4000	Justin	Bieber	google.com
9000	Russell	Crowe	google.com
7000	Tom	Cruise	oracle.com
8000	Johnny	Depp	NULL
5000	Selena	Gomez	bing.com
6000	Mila	Kunis	yahoo.com

В этом примере будут возвращены все записи из таблицы customers, отсортированные по полю last_name в порядке возрастания, и будет эквивалентен следующему SQL предложению ORDER BY.

SELECT *

FROM customers

ORDER BY last_name ASC;

Большинство программистов пропускают атрибут ASC при сортировке в порядке возрастания.

Пример — сортировка результатов по убыванию

При сортировке набора результатов в порядке убывания вы используете атрибут DESC в предложении ORDER BY. Давайте внимательнее посмотрим.
В этом примере у нас есть таблица suppliers со следующими данными:

supplier_id	supplier_name	city	state
100	Yandex	Moscow	Russian
200	Google	Lansing	Michigan
300	Oracle	Redwood City	California
400	Bing	Redmond	Washington
500	Yahoo	Sunnyvale	Washington
600	DuckDuckGo	Paoli	Pennsylvania
700	Qwant	Paris	France
800	Facebook	Menlo Park	California
900	Electronic Arts	San Francisco	California

Введите следующий SQL оператор.

SELECT *

FROM suppliers

WHERE supplier_id > 40

ORDER BY supplier_id DESC;

Будет выбрано 5 записей. Вот результаты, которые вы должны получить.

supplier_id	supplier_name	city	state
900	Electronic Arts	San Francisco	California
800	Facebook	Menlo Park	California
700	Qwant	Paris	France
600	DuckDuckGo	Paoli	Pennsylvania
500	Yahoo	Sunnyvale	Washington

В этом примере будет отсортирован набор результатов по полю supplier_id в порядке убывания.

Пример — сортировка результатов по относительной позиции

Вы также можете использовать SQL оператор ORDER BY для сортировки по относительной позиции в наборе результатов, где первое поле в наборе результатов равно 1, второе поле равно 2, третье поле равно 3 и т.д.
В этом примере у нас есть таблица products со следующими данными:

product_id	product_name	category_id
1	Pear	50
2	Banana	50
3	Orange	50
4	Apple	50
5	Bread	75
6	Sliced Ham	25
7	Kleenex	NULL

Теперь введите следующий SQL оператор.

SELECT product_id,

product_name

FROM products

WHERE product_name <> ‘Bread’

ORDER BY 1 DESC;

Будет выбрано 6 записей. Вот результаты, которые вы должны получить.

product_id	product_name
7	Kleenex
6	Sliced Ham
4	Apple
3	Orange
2	Banana
1	Pear

В этом примере результаты сортируются по полю product_id в порядке убывания, поскольку поле product_id находится в позиции № 1 в наборе результатов и будет эквивалентно следующему SQL оператору ORDER BY.

SELECT product_id,

product_name

FROM products

WHERE product_name <> ‘Bread’

ORDER BY product_id DESC;

Пример — использование атрибутов ASC и DESC

При сортировке набора результатов с помощью SQL предложения ORDER BY вы можете использовать атрибуты ASC и DESC в одном операторе SELECT.
В этом примере давайте использовать ту же таблицу products, что и в предыдущем примере.

product_id	product_name	category_id
1	Pear	50
2	Banana	50
3	Orange	50
4	Apple	50
5	Bread	75
6	Sliced Ham	25
7	Kleenex	NULL

Теперь введите следующий SQL оператор.

SELECT *

FROM products

WHERE product_id <>

ORDER BY category_id DESC,

product_name ASC;

Будет выбрано 6 записей. Вот результаты, которые вы должны получить.

product_id	product_name	category_id
5	Bread	75
4	Apple	50
2	Banana	50
3	Orange	50
1	Pear	50
6	Sliced Ham	25

В этом примере возвращаются записи, отсортированные по полю category_id в порядке убывания, а вторичная сортировка — по полю product_name в порядке возрастания.

Источник

customer_id cust_name city grade salesman_id

———— ———— ———- ———- ————

3002 Nick Rimando New York 100 5001

3005 Graham Zusi California 200 5002

3001 Brad Guzan London 5005

3004 Fabian Johns Paris 300 5006

3007 Brad Davis New York 200 5001

3009 Geoff Camero Berlin 100 5003

3008 Julian Green London 300 5002

3003 Jozy Altidor Moscow 200 5007

Источник

The ORDER BY statement in SQL is used to sort the fetched data in either ascending or descending according to one or more columns. In this article, we will discuss different ways of using Order By in SQL.

Here are some basic rules of Order By statement in SQL.

By default ORDER BY sorts the data in ascending order.
We can use the keyword DESC to sort the data in descending order and the keyword ASC to sort in ascending order.

Sort According To One Column

To sort in ascending or descending order we can use the keywords ASC or DESC respectively.

Syntax

SELECT * FROM table_name ORDER BY column_name ASC | DESC

//Where

table_name: name of the table.

column_name: name of the column according to which the data

is needed to be arranged.

ASC: to sort the data in ascending order.

DESC: to sort the data in descending order.

| : use either ASC or DESC to sort in ascending or descending order//

Sort According To Multiple Columns

To sort in ascending or descending order we can use the keywords ASC or DESC respectively. To sort according to multiple columns, separate the names of columns by the (,) operator.

Syntax

SELECT * FROM table_name ORDER BY column1 ASC|DESC , column2 ASC|DESC

Query

CREATE TABLE students (
  roll_no INT NOT NULL,
  age INT NOT NULL,
  name VARCHAR(50) NOT NULL,
  address VARCHAR(100) NOT NULL,
  phone VARCHAR(20) NOT NULL,
  PRIMARY KEY (roll_no)
);
INSERT INTO students (roll_no, age, name, address, phone)
VALUES 
  (1, 18, 'Shubham Thakur', '123 Main St, Mumbai', '9876543210'),
  (2, 19, 'Aman Chopra', '456 Park Ave, Delhi', '9876543211'),
  (3, 20, 'Naveen Tulasi', '789 Broadway, Ahmedabad', '9876543212'),
  (4, 21, 'Aditya arpan', '246 5th Ave, Kolkata', '9876543213'),
  (5, 22, 'Nishant Jain', '369 3rd St, Bengaluru', '9876543214');

Output

output

students Table

Now consider the above database table and find the results of different queries.

Sort According To a Single Column

In this example, we will fetch all data from the table Student and sort the result in descending order according to the column ROLL_NO.

Query

SELECT * FROM students ORDER BY ROLL_NO DESC;

Output

output

In the above example, if we want to sort in ascending order we have to use ASC in place of DESC.

Sort According To Multiple Columns

In this example, we will fetch all data from the table Student and then sort the result in ascending order first according to the column Age. and then in descending order according to the column ROLL_NO.

Query

SELECT * FROM students ORDER BY Age ASC , ROLL_NO DESC;

Output

output

In the above output, we can see that first the result is sorted in ascending order according to Age. There are multiple rows of having the same Age. Now, sorting further this result-set according to ROLL_NO will sort the rows with the same Age according to ROLL_NO in descending order.

Note:

ASC is the default value for the ORDER BY clause. So, if we don't specify anything 
after the column name in the ORDER BY clause, the output will be sorted in ascending order by default.

Query

SELECT * FROM students ORDER BY Age , ROLL_NO DESC;

Output

output

Sorting By Column Number (instead of name)

An integer that identifies the number of the column in the SelectItems in the underlying query of the SELECT statement. Column number must be greater than 0 and not greater than the number of columns in the result table. In other words, if we want to order by a column, that column must be specified in the SELECT list.

The rule checks for ORDER BY clauses that reference select list columns using the column number instead of the column name. The column numbers in the ORDER BY clause impair the readability of the SQL statement. Further, changing the order of columns in the SELECT list has no impact on the ORDER BY when the columns are referred to by names instead of numbers.

Syntax

Order by Column_Number asc/desc

Here we take an example to sort a database table according to column 1 i.e Roll_Number. For this a query will be:

Query

CREATE TABLE studentinfo
( Roll_no INT,
NAME VARCHAR(25),
Address VARCHAR(20),
CONTACTNO BIGINT NOT NULL,
Age INT ); INSERT INTO studentinfo
VALUES (7,'ROHIT','GHAZIABAD',9193458625,18),
(4,'DEEP','RAMNAGAR',9193458546,18),
(1,'HARSH','DELHI',9193342625,18),
(8,'NIRAJ','ALIPUR',9193678625,19),
(5,'SAPTARHI','KOLKATA',9193789625,19),
(2,'PRATIK','BIHAR',9193457825,19),
(6,'DHANRAJ','BARABAJAR',9193358625,20),
(3,'RIYANKA','SILIGURI',9193218625,20);
SELECT Name, Address
FROM studentinfo
ORDER BY 1

Output

output

Conclusion

In SQL, The results of a SELECT statement are sorted using the ORDER BY clause . The results can be sorted using either a single column or many columns. Although ascending is the default sort order, you can select descending order by using the DESC keyword.

Источник

Summary: This tutorial shows you how to use the SQL ORDER BY clause to sort the result set based on specified criteria in ascending or descending orders.

Introduction to SQL `ORDER BY` clause

The ORDER BY is an optional clause of the SELECT statement. The ORDER BY clause allows you to sort the rows returned by the SELECT clause by one or more sort expressions in ascending or descending order.

The following shows the syntax of the ORDER BY clause:

SELECT 
    select_list
FROM
    table_name
ORDER BY 
    sort_expression [ASC | DESC];Code language: SQL (Structured Query Language) (sql)

In this syntax:

First, place the ORDER BY clause after the FROM clause. The database will evaluate the SELECT statement with the ORDER BY * clause in the following order: FROM > SELECT > ORDER BY.
Second, specify a sort expression after the ORDER BY clause. The sort expression specifies the sort criteria.
Third, use ASC option to sort the result set by the sort expression in ascending order and DESC to sort the result set by the sort expression in the descending order.

Note that the ORDER BY clause uses the ASC option by default if you don’t either ASC or DESC.

The ORDER BY clause also allows you to sort the result set by multiple expressions. In this case, you need to use a comma to separate two sort expressions:

SELECT 
    select_list
FROM
    table_name
ORDER BY 
    sort_expression_1 [ASC | DESC],
    sort_expression_2 [ASC | DESC];Code language: CSS (css)

In this syntax, the ORDER BY clause sorts the result set by the sort_expression_1 first, and then sorts the sorted result set by the sort_expression_2.

Note that if you don’t specify the ORDER BY clause, the SELECT statement will not sort the result set. It means that the rows in the result set don’t have a specific order.

SQL `ORDER BY` clause examples

We’ll use the employees table in the sample database for the demonstration.

1) Using SQL `ORDER BY` clause to sort values in one column example

The following SELECT statement returns the data from the employee id, first name, last name, hire date, and salary column of the employees table:

SELECT
	employee_id,
	first_name,
	last_name,
	hire_date,
	salary
FROM
	employees;Code language: SQL (Structured Query Language) (sql)

Try It

+-------------+-------------+-------------+------------+----------+
| employee_id | first_name  | last_name   | hire_date  | salary   |
+-------------+-------------+-------------+------------+----------+
|         100 | Steven      | King        | 1987-06-17 | 24000.00 |
|         101 | Neena       | Kochhar     | 1989-09-21 | 17000.00 |
|         102 | Lex         | De Haan     | 1993-01-13 | 17000.00 |
|         103 | Alexander   | Hunold      | 1990-01-03 |  9000.00 |
|         104 | Bruce       | Ernst       | 1991-05-21 |  6000.00 |
|         105 | David       | Austin      | 1997-06-25 |  4800.00 |
|         106 | Valli       | Pataballa   | 1998-02-05 |  4800.00 |
|         107 | Diana       | Lorentz     | 1999-02-07 |  4200.00 |
|         108 | Nancy       | Greenberg   | 1994-08-17 | 12000.00 |
|         109 | Daniel      | Faviet      | 1994-08-16 |  9000.00 |
|         110 | John        | Chen        | 1997-09-28 |  8200.00 |
...

As you can see clearly from the output, the rows do not have any order.

The following example uses the ORDER BY clause to sort employees by first names in alphabetical order:

SELECT
	employee_id,
	first_name,
	last_name,
	hire_date,
	salary
FROM
	employees
ORDER BY
	first_name;Code language: SQL (Structured Query Language) (sql)

Try It

+-------------+-------------+-------------+------------+----------+
| employee_id | first_name  | last_name   | hire_date  | salary   |
+-------------+-------------+-------------+------------+----------+
|         121 | Adam        | Fripp       | 1997-04-10 |  8200.00 |
|         115 | Alexander   | Khoo        | 1995-05-18 |  3100.00 |
|         103 | Alexander   | Hunold      | 1990-01-03 |  9000.00 |
|         193 | Britney     | Everett     | 1997-03-03 |  3900.00 |
|         104 | Bruce       | Ernst       | 1991-05-21 |  6000.00 |
|         179 | Charles     | Johnson     | 2000-01-04 |  6200.00 |
|         109 | Daniel      | Faviet      | 1994-08-16 |  9000.00 |
|         105 | David       | Austin      | 1997-06-25 |  4800.00 |
|         114 | Den         | Raphaely    | 1994-12-07 | 11000.00 |
|         107 | Diana       | Lorentz     | 1999-02-07 |  4200.00 |
...

The ORDER BY sorts the rows by the values in the first_name column.

2) Using SQL `ORDER BY` clause to sort values in multiple columns example

The following example uses the ORDER BY clause to sort the employees by the first name in ascending order and the last name in descending order:

SELECT
	employee_id,
	first_name,
	last_name,
	hire_date,
	salary
FROM
	employees
ORDER BY
	first_name,
	last_name DESC;Code language: SQL (Structured Query Language) (sql)

Try It

+-------------+-------------+-------------+------------+----------+
| employee_id | first_name  | last_name   | hire_date  | salary   |
+-------------+-------------+-------------+------------+----------+
|         121 | Adam        | Fripp       | 1997-04-10 |  8200.00 |
|         115 | Alexander   | Khoo        | 1995-05-18 |  3100.00 |
|         103 | Alexander   | Hunold      | 1990-01-03 |  9000.00 |
|         193 | Britney     | Everett     | 1997-03-03 |  3900.00 |
|         104 | Bruce       | Ernst       | 1991-05-21 |  6000.00 |
|         179 | Charles     | Johnson     | 2000-01-04 |  6200.00 |
|         109 | Daniel      | Faviet      | 1994-08-16 |  9000.00 |
|         105 | David       | Austin      | 1997-06-25 |  4800.00 |
|         114 | Den         | Raphaely    | 1994-12-07 | 11000.00 |
|         107 | Diana       | Lorentz     | 1999-02-07 |  4200.00 |
|         118 | Guy         | Himuro      | 1998-11-15 |  2600.00 |
...

In this example, the ORDER BY clause sorts the result set by the first name in ascending order, then it sorts the sorted result set by the last name in descending order.

Notice the change in position of two employees: Alexander Khoo and Alexander Hunold in the result set.

3) Using SQL `ORDER BY` clause to sort values in a numeric column example

The following example uses the ORDER BY clause to sort employees by salary from high to low:

SELECT
	employee_id,
	first_name,
	last_name,
	hire_date,
	salary
FROM
	employees
ORDER BY
	salary DESC;Code language: SQL (Structured Query Language) (sql)

Try It

+-------------+-------------+-------------+------------+----------+
| employee_id | first_name  | last_name   | hire_date  | salary   |
+-------------+-------------+-------------+------------+----------+
|         100 | Steven      | King        | 1987-06-17 | 24000.00 |
|         101 | Neena       | Kochhar     | 1989-09-21 | 17000.00 |
|         102 | Lex         | De Haan     | 1993-01-13 | 17000.00 |
|         145 | John        | Russell     | 1996-10-01 | 14000.00 |
|         146 | Karen       | Partners    | 1997-01-05 | 13500.00 |
|         201 | Michael     | Hartstein   | 1996-02-17 | 13000.00 |
|         205 | Shelley     | Higgins     | 1994-06-07 | 12000.00 |
|         108 | Nancy       | Greenberg   | 1994-08-17 | 12000.00 |
|         114 | Den         | Raphaely    | 1994-12-07 | 11000.00 |
...

4) Using SQL `ORDER BY` to sort by dates example

Besides the character and numeric data, you can use the ORDER BY clause to sort rows by date. For example, the following statement uses the ORDER BY clause to sort the employees by values in the hire_date column from:

SELECT
	employee_id,
	first_name,
	last_name,
	hire_date,
	salary
FROM
	employees
ORDER BY
	hire_date;Code language: SQL (Structured Query Language) (sql)

Try It

+-------------+-------------+-------------+------------+----------+
| employee_id | first_name  | last_name   | hire_date  | salary   |
+-------------+-------------+-------------+------------+----------+
|         100 | Steven      | King        | 1987-06-17 | 24000.00 |
|         200 | Jennifer    | Whalen      | 1987-09-17 |  4400.00 |
|         101 | Neena       | Kochhar     | 1989-09-21 | 17000.00 |
|         103 | Alexander   | Hunold      | 1990-01-03 |  9000.00 |
|         104 | Bruce       | Ernst       | 1991-05-21 |  6000.00 |
|         102 | Lex         | De Haan     | 1993-01-13 | 17000.00 |
|         203 | Susan       | Mavris      | 1994-06-07 |  6500.00 |
|         204 | Hermann     | Baer        | 1994-06-07 | 10000.00 |
...

To see the employees who joined the company from lastest to earliest, you sort the employees by the hire dates in descending order:

SELECT
	employee_id,
	first_name,
	last_name,
	hire_date,
	salary
FROM
	employees
ORDER BY
	hire_date DESC;Code language: SQL (Structured Query Language) (sql)

Try It

+-------------+-------------+-------------+------------+----------+
| employee_id | first_name  | last_name   | hire_date  | salary   |
+-------------+-------------+-------------+------------+----------+
|         179 | Charles     | Johnson     | 2000-01-04 |  6200.00 |
|         113 | Luis        | Popp        | 1999-12-07 |  6900.00 |
|         119 | Karen       | Colmenares  | 1999-08-10 |  2500.00 |
|         178 | Kimberely   | Grant       | 1999-05-24 |  7000.00 |
|         107 | Diana       | Lorentz     | 1999-02-07 |  4200.00 |
|         118 | Guy         | Himuro      | 1998-11-15 |  2600.00 |
|         126 | Irene       | Mikkilineni | 1998-09-28 |  2700.00 |
|         177 | Jack        | Livingston  | 1998-04-23 |  8400.00 |
|         176 | Jonathon    | Taylor      | 1998-03-24 |  8600.00 |
...

Summary

Use the ORDER BY clause to sort rows returned by the SELECT clause.
Use the ASC option to sort rows in ascending order and DESC option to sort rows in descending order.

Was this tutorial helpful ?

Источник

Доброго времени суток, друзья!

Изучение настоящей шпаргалки не сделает вас мастером SQL, но позволит получить общее представление об этом языке программирования и возможностях, которые он предоставляет. Рассматриваемые в шпаргалке возможности являются общими для всех или большинства диалектов SQL.

Для более полного погружения в SQL рекомендую изучить эти руководства по MySQL и PostgreSQL от Метанита. Они хороши тем, что просты в изучении и позволяют быстро начать работу с названными СУБД.

Официальная документация по MySQL.

Официальная документация по PostreSQL (на русском языке).

Свежий туториал по SQL от Codecamp.

Свежая шпаргалка по SQL в формате PDF.

При обнаружении ошибок, опечаток и неточностей, не стесняйтесь писать мне в личку.

Содержание

Что такое SQL?
Почему SQL?
Процесс SQL
Команды SQL
Что такое таблица?
Что такое поле?
Что такое запись или строка?
Что такое колонка?
Что такое NULL?
Ограничения
Целостность данных
Нормализация БД
Синтаксис SQL
Типы данных
Операторы
Выражения
Создание БД
Удаление БД
Выбор БД
Создание таблицы
Удаление таблицы
Добавление колонок
Выборка полей
Предложение WHERE
Операторы AND и OR
Обновление полей
Удаление записей
Предложения LIKE и REGEX
Предложение TOP/LIMIT/ROWNUM
Предложения ORDER BY и GROUP BY
Ключевое слово DISTINCT
Соединения
Предложение UNION
Предложение UNION ALL
Синонимы
Индексы
Обновление таблицы
Очистка таблицы
Представления
HAVING
Транзакции
Временные таблицы
Клонирование таблицы
Подзапросы
Последовательности

Что такое SQL?

SQL — это язык структурированных запросов (Structured Query Language), позволяющий хранить, манипулировать и извлекать данные из реляционных баз данных (далее — РБД, БД).

↥ Наверх

Почему SQL?

SQL позволяет:

получать доступ к данным в системах управления РБД
описывать данные (их структуру)
определять данные в БД и управлять ими
взаимодействовать с другими языками через модули SQL, библиотеки и предваритальные компиляторы
создавать и удалять БД и таблицы
создавать представления, хранимые процедуры (stored procedures) и функции в БД
устанавливать разрешения на доступ к таблицам, процедурам и представлениям

↥ Наверх

Процесс SQL

При выполнении любой SQL-команды в любой RDBMS (Relational Database Management System — система управления РБД, СУБД, например, PostgreSQL, MySQL, MSSQL, SQLite и др.) система определяет наилучший способ выполнения запроса, а движок SQL определяет способ интерпретации задачи.

В данном процессе участвует несколького компонентов:

диспетчер запросов (Query Dispatcher)
движок оптимизации (Optimization Engines)
классический движок запросов (Classic Query Engine)
движок запросов SQL (SQL Query Engine) и т.д.

Классический движок обрабатывает все не-SQL-запросы, а движок SQL-запросов не обрабатывает логические файлы.

↥ Наверх

Команды SQL

Стандартными командами для взаимодействия с РБД являются CREATE, SELECT, INSERT, UPDATE, DELETE и DROP. Эти команды могут быть классифицированы следующим образом:

DDL — язык определения данных (Data Definition Language)

DML — язык изменения данных (Data Manipulation Language)

DCL — язык управления данными (Data Control Language)

Обратите внимание: использование верхнего регистра в названиях команд SQL — это всего лишь соглашение, большинство СУБД нечувствительны к регистру. Тем не менее, форма записи инструкций, когда названия команд пишутся большими буквами, а названия таблиц, колонок и др. — маленькими, позволяет быстро определять назначение производимой с данными операции.

↥ Наверх

Что такое таблица?

Данные в СУБД хранятся в объектах БД, называемых таблицами (tables). Таблица, как правило, представляет собой коллекцию связанных между собой данных и состоит из определенного количества колонок и строк.

Таблица — это самая распространенная и простая форма хранения данных в РБД. Вот пример таблицы с пользователями (users):

↥ Наверх

Что такое поле?

Каждая таблица состоит из небольших частей — полей (fields). Полями в таблице users являются userId, userName, age, city и status. Поле — это колонка таблицы, предназначенная для хранения определенной информации о каждой записи в таблице.

Обратите внимание: вместо userId и userName можно было бы использовать id и name, соответственно. Но при работе с несколькими объектами, содержащими свойство id, бывает сложно понять, какому объекту принадлежит идентификатор, особенно, если вы, как и я, часто прибегаете к деструктуризации. Что касается слова name, то оно часто оказывается зарезервизованным, т.е. уже используется в среде, в которой выполняется код, поэтому я стараюсь его не использовать.

↥ Наверх

Что такое запись или строка?

Запись или строка (record/row) — это любое единичное вхождение (entry), существующее в таблице. В таблице users 5 записей. Проще говоря, запись — это горизонтальное вхождение в таблице.

↥ Наверх

Что такое колонка?

Колонка (column) — это вертикальное вхождение в таблице, содержащее всю информацию, связанную с определенным полем. В таблице users одной из колонок является city, которая содержит названия городов, в которых проживают пользователи.

↥ Наверх

Что такое нулевое значение?

Нулевое значение (NULL) — это значение поля, которое является пустым, т.е. нулевое значение — это значение поля, не имеющего значения. Важно понимать, что нулевое значение отличается от значения 0 и от значения поля, содержащего пробелы (`). Поле с нулевым значением - это такое поля, которое осталось пустым при создании записи. Также, следует учитывать, что в некоторых СУБД пустая строка (») — этоNULL`, а в некоторых — это разные значения.

↥ Наверх

Ограничения

Ограничения (constraints) — это правила, применяемые к данным. Они используются для ограничения данных, которые могут быть записаны в таблицу. Это обеспечивает точность и достоверность данных в БД.

Ограничения могут устанавливаться как на уровне колонки, так и на уровне таблицы.

Среди наиболее распространенных ограничений можно назвать следующие:

NOT NULL — колонка не может иметь нулевое значение
DEFAULT — значение колонки по умолчанию
UNIQUE — все значения колонки должны быть уникальными
PRIMARY KEY — первичный или основной ключ, уникальный идентификатор записи в текущей таблице
FOREIGN KEY — внешний ключ, уникальный идентификатор записи в другой таблице (таблице, связанной с текущей)
CHECK — все значения в колонке должны удовлетворять определенному условию
INDEX — быстрая запись и извлечение данных

Любое ограничение может быть удалено с помощью команды ALTER TABLE и DROP CONSTRAINT + название ограничения. Некоторые реализации предоставляют сокращения для удаления ограничений и возможность отключать ограничения вместо их удаления.

↥ Наверх

Целостность данных

В каждой СУБД существуют следующие категории целостности данных:

целостность объекта (Entity Integrity) — в таблице не должно быть дубликатов (двух и более строк с одинаковыми значениями)
целостность домена (Domain Integrity) — фильтрация значений по типу, формату или диапазону
целостность ссылок (Referential integrity) — строки, используемые другими записями (строки, на которые в других записях имеются ссылки), не могут быть удалены
целостность, определенная пользователем (User-Defined Integrity) — дополнительные правила

↥ Наверх

Нормализация БД

Нормализация — это процесс эффективной организации данных в БД. Существует две главных причины, обуславливающих необходимость нормализации:

предотвращение записи в БД лишних данных, например, хранения одинаковых данных в разных таблицах
обеспечение «оправданной» связи между данными

Нормализация предполагает соблюдение нескольких форм. Форма — это формат структурирования БД. Существует три главных формы: первая, вторая и, соответственно, третья. Я не буду вдаваться в подробности об этих формах, при желании, вы без труда найдете необходимую информацию.

↥ Наверх

Синтаксис SQL

Синтаксис — это уникальный набор правил и рекомендаций. Все инструкции SQL должны начинаться с ключевого слова, такого как SELECT, INSERT, UPDATE, DELETE, ALTER, DROP, CREATE, USE, SHOW и т.п. и заканчиваться точкой с запятой (;) (точка с запятой не входит в синтаксис SQL, но ее наличия, как правило, требуют консольные клиенты СУБД для обозначения окончания ввода команды). SQL не чувствителен к регистру, т.е. SELECT, select и SeLeCt являются идентичными инструкицями. Исключением из этого правила является MySQL, где учитывается регистр в названии таблицы.

Примеры синтаксиса

-- выборка
SELECT col1, col2, ...colN
FROM tableName;

SELECT DISTINCT col1, col2, ...colN
FROM tableName;

SELECT col1, col2, ...colN
FROM tableName
WHERE condition;

SELECT col1, col2, ...colN
FROM tableName
WHERE condition1 AND|OR condition2;

SELECT col2, col2, ...colN
FROM tableName
WHERE colName IN (val1, val2, ...valN);

SELECT col1, col2, ...colN
FROM tableName
WHERE colName BETWEEN val1 AND val2;

SELECT col1, col2, ...colN
FROM tableName
WHERE colName LIKE pattern;

SELECT col1, col2, ...colN
FROM tableName
WHERE condition
ORDER BY colName [ASC|DESC];

SELECT SUM(colName)
FROM tableName
WHERE condition
GROUP BY colName;

SELECT COUNT(colName)
FROM tableName
WHERE condition;

SELECT SUM(colName)
FROM tableName
WHERE condition
GROUP BY colName
HAVING (function condition);

-- создание таблицы
CREATE TABLE tableName (
  col1 datatype,
  col2 datatype,
  ...
  colN datatype,
  PRIMARY KEY (одна или более колонка)
);

-- удаление таблицы
DROP TABLE tableName;

-- создание индекса
CREATE UNIQUE INDEX indexName
ON tableName (col1, col2, ...colN);

-- удаление индекса
ALTER TABLE tableName
DROP INDEX indexName;

-- получение описания структуры таблицы
DESC tableName;

-- очистка таблицы
TRUNCATE TABLE tableName;

-- добавление/удаление/модификация колонок
ALTER TABLE tableName ADD|DROP|MODIFY colName [datatype];

-- переименование таблицы
ALTER TABLE tableName RENAME TO newTableName;

-- вставка значений
INSERT INTO tableName (col1, col2, ...colN)
VALUES (val1, val2, ...valN)

-- обновление записей
UPDATE tableName
SET col1 = val1, col2 = val2, ...colN = valN
[WHERE condition];

-- удаление записей
DELETE FROM tableName
WHERE condition;

-- создание БД
CREATE DATABASE [IF NOT EXISTS] dbName;

-- удаление БД
DROP DATABASE [IF EXISTS] dbName;

-- выбор БД
USE dbName;

-- завершения транзакции
COMMIT;

-- отмена изменений
ROLLBACK;

↥ Наверх

Типы данных

Каждая колонка, переменная и выражение в SQL имеют определенный тип данных (data type). Основные категории типов данных:

Точные числовые

Приблизительные числовые

Дата и время

Строковые символьные

Строковые символьные (юникод)

Бинарные

Смешанные

↥ Наверх

Операторы

Оператор (operators) — это ключевое слово или символ, которые, в основном, используются в инструкциях WHERE для выполнения каких-либо операций. Они используются как для определения условий, так и для объединения нескольких условий в инструкции.

В дальнейших примерах мы будем исходить из предположения, что переменная a имеет значение 10, а b — 20.

Арифметические

Операторы сравнения

Логические операторы

↥ Наверх

Выражения

Выражение (expression) — это комбинация значений, операторов и функций для оценки (вычисления) значения. Выражения похожи на формулы, написанные на языке запросов. Они могут использоваться для извлечения из БД определенного набора данных.

Базовый синтаксис выражения выглядит так:

SELECT col1, col2, ...colN
FROM tableName
WHERE [condition|expression];

Существуют различные типы выражений: логические, числовые и выражения для работы с датами.

Логические

Логические выражения извлекают данные на основе совпадения с единичным значением.

SELECT col1, col2, ...colN
FROM tableName
WHERE выражение для поиска совпадения с единичным значением;

Предположим, что в таблице users имеются следующие записи:

Выполняем поиск активных пользователей:

SELECT * FROM users WHERE status = active;

Результат:

Числовые

Используются для выполнения арифметических операций в запросе.

SELECT numericalExpression as operationName
[FROM tableName
WHERE condition];

Простой пример использования числового выражения:

SELECT (10 + 5) AS addition;

Результат:

Существует несколько встроенных функций, таких как count(), sum(), avg(), min(), max() и др. для выполнения так называемых агрегирующих вычислений данных таблицы или колонки.

SELECT COUNT(*) AS records FROM users;

Результат:

AVG — вычисляет среднее значение
SUM — вычисляет сумму значений
MIN — вычисляет наименьшее значение
MAX — вычисляет наибольшее значение
COUNT — вычисляет количество записей в таблице

Также существует несколько встроенных функций для работы со строками:

CONCAT — объединение строк
LENGTH — возвращает количество символов в строке
TRIM — удаляет пробелы в начале и конце строки
SUBSTRING — извлекает подстроку из строки
REPLACE — заменяет подстроку в строке
LOWER — переводит символы строки в нижний регистр
UPPER — переводит символы строки в верхний регистр и т.д.

с числами:

ROUND — округляет число
TRUNCATE — обрезает дробное число до указанного количества знаков после запятой
CEILING — возвращает наименьшее целое число, которое больше или равно текущему значению
FLOOR — возвращает наибольшее целое число, которое меньше или равно текущему значению
POWER — возводит число в указанную степень
SQRT — возвращает квадратный корень числа
RAND — генерирует случайное число с плавающей точкой в диапазоне от 0 до 1

Выражения для работы с датами

Эти выражения, как правило, возвращают текущую дату и время.

SELECT CURRENT_TIMESTAMP;

Результат:

CURRENT_TIMESTAMP — это и выражение, и функция (CURRENT_TIMESTAMP()). Другая функция для получения текущей даты и времени — NOW().

Другие функции для получения текущей даты и времени:

CURDATE/CURRENT_DATE — возвращает текущую дату
CURTIME/CURRENT_TIME — возвращает текущее время и т.д.

Функции для разбора даты и времени:

DAYOFMONTH(date) — возвращает день месяца в виде числа
DAYOFWEEK(date) — возвращает день недели в виде числа
DAYOFYEAR(date) — возвращает номер дня в году
MONTH(date) — возвращает месяц
YEAR(date) — возвращает год
LAST_DAY(date) — возвращает последний день месяца в виде даты
HOUR(time) — возвращает час
MINUTE(time) — возвращает минуты
SECOND(time) — возвращает секунды и др.

Функции для манипулирования датами:

DATE_ADD(date, interval) — выполняет сложение даты и определенного временного интервала
DATE_SUB(date, interval) — выполняет вычитание из даты определенного временного интервала
DATEDIFF(date1, date2) — возвращает разницу в днях между двумя датами
TO_DAYS(date) — возвращает количество дней с 0-го дня года
TIME_TO_SEC(time) — возвращает количество секунд с полуночи и др.

Для форматирования даты и времени используются функции DATE_FORMAT(date, format) и TIME_FORMAT(date, format), соответственно.

↥ Наверх

Создание БД

Для создания БД используется инструкция CREATE DATABASE.

CREATE DATABASE dbName;
-- или
CREATE DATABASE IF NOT EXISTS dbName;

Условие IF NOT EXISTS позволяет избежать получения ошибки при попытке создания БД, которая уже существует.

Название БД должно быть уникальным в пределах СУБД.

Создаем БД testDB:

CREATE DATABASE testDB;

Получаем список БД:

SHOW DATABASES;

Результат:

↥ Наверх

Удаление БД

Для удаления БД используется инструкция DROP DATABASE.

DROP DATABASE dbName;
-- или
DROP DATABASE IF EXISTS dbName;

Условие IF EXISTS позволяет избежать получения ошибки при попытке удаления несуществующей БД.

Удаляем testDB:

DROP DATABASE testDB;

Обратите внимание: при удалении БД уничтожаются все данные, которые в ней хранятся, так что будьте предельно внимательны при использовании данной команды.

Проверяем, что БД удалена:

SHOW DATABASES;

Для получения списка таблиц используется инструкция SHOW TABLES.

Результат:

↥ Наверх

Выбор БД

При наличии нескольких БД, перед выполнением каких-либо операций, необходимо выбрать БД. Для этого используется инструкция USE.

USE dbName;

Предположим, что мы не удаляли testDB. Тогда мы можем выбрать ее так:

USE testDB;

↥ Наверх

Создание таблицы

Создание таблицы предполагает указание названия таблицы и определение колонок таблицы и их типов данных. Для создания таблицы используется инструкция CREATE TABLE.

CREATE TABLE tableName (
  col1 datatype,
  col2 datatype,
  ...
  colN datatype,
  PRIMARY KEY (хотя бы одна колонка)
);

Для создания таблицы путем копирования другой таблицы используется сочетание CREATE TABLE и SELECT.

Пример создания таблицы users, где первичным ключом являются идентификаторы пользователей, а поля для имени и возраста пользователя не могут быть нулевыми:

CREATE TABLE users (
  userId INT,
  userName VARCHAR(20)  NOT NULL,
  age INT           NOT NULL,
  city VARCHAR(20),
  status VARCHAR(8),
  PRIMARY KEY (id)
);

Проверяем, что таблица была создана:

DESC users;

Результат:

↥ Наверх

Удаление таблицы

Для удаления таблицы используется инструкция DROP TABLE.

Обратите внимание: при удалении таблицы, навсегда удаляются все хранящиеся в ней данные, индексы, триггеры, ограничения и разрешения, так что будьте предельно внимательны при использовании данной команды.

Удаляем таблицу users:

DROP TABLE users;

Теперь, если мы попытаемся получить описание users, то получим ошибку:

DESC users;
-- ERROR 1146 (42S02): Table 'testDB.users' doesn't exist

↥ Наверх

Добавление колонок

Для добавления в таблицу колонок используется инструкция INSERT INTO.

INSERT INTO tableName (col1, col2, ...colN)
VALUES (val1, val2, ...valN);

Названия колонок можно не указывать, однако, в этом случае значения должны перечисляться в правильном порядке.

INSERT INTO tableName VALUES (val1, val2, ...valN);

Во избежание ошибок, рекомендуется всегда перечислять названия колонок.

Предположим, что мы не удаляли таблицу users. Заполним ее пользователями:

INSERT INTO users (userId, userName, age, city, status)
VALUES (1, 'Igor', 25, 'Moscow', 'active');

INSERT INTO users (userId, userName, age, city, status)
VALUES (2, 'Vika', 26, 'Ekaterinburg', 'inactive');

INSERT INTO users (userId, userName, age, city, status)
VALUES (3, 'Elena', 27, 'Ekaterinburg', 'active');

В таблицу можно добавлять несколько строк за один раз.

INSERT INTO users (userId, userName, age, city, status)
VALUES
(1, 'Igor', 25, 'Moscow', 'active'),
(2, 'Vika', 26, 'Ekaterinburg', 'inactive'),
(3, 'Elena', 27, 'Ekaterinburg', 'active');

Также, как было отмечено, при добавлении строки названия полей можно опускать:

INSERT INTO users
VALUES (4, 'Oleg', 28, 'Moscow', 'inactive');

Результат:

Заполнение таблицы с помощью другой таблицы

INSERT INTO tableName [(col1, col2, ...colN)]
  SELECT col1, col2, ...colN
  FROM anotherTable
  [WHERE condition];

↥ Наверх

Выборка полей

Для выборки полей из таблицы используется инструкция SELECT. Она возвращает данные в виде результирующей таблицы (результирующего набора, result-set).

SELECT col1, col2, ...colN
FROM tableName;

Для выборки всех полей используется такой синтаксис:

SELECT * FROM tableName;

Произведем выборку полей userId, userName и age из таблицы users:

SELECT userId, userName, age FROM users;

Результат:

↥ Наверх

Предложение `WHERE`

Предложение WHERE используется для фильтрации возвращаемых данных. Оно используется совместно с SELECT, UPDATE, DELETE и другими инструкциями.

SELECT col1, col2, ...col2
FROM tableName
WHERE condition;

Условие (condition), которому должны удовлетворять возвращаемые записи, определяется с помощью операторов сравнения или логических операторов типа >, <, =, NOT, LIKE и т.д.

Сделаем выборку полей userId, userName и age активных пользователей:

SELECT userId, userName, age
FROM users
WHERE status = 'active';

Результат:

Сделаем выборку полей userId, age и city пользователя с именем Vika.

SELECT userId, age, city
FROM users
WHERE userName = 'Vika';

Результат:

Обратите внимание: строки в предложении WHERE должны быть обернуты в одинарные кавычки (''), а числа, напротив, указываются как есть.

↥ Наверх

Операторы `AND` и `OR`

Конъюнктивный оператор AND и дизъюнктивный оператор OR используются для соединения нескольких условий при фильтрации данных.

AND

SELECT col1, col2, ...colN
FROM tableName
WHERE condition1 AND condition2 ...AND conditionN;

Возвращаемые записи должны удовлетворять всем указанным условиям.

Сделаем выборку полей userId, userName и age активных пользователей старше 26 лет:

SELECT userId, userName, age
FROM users
WHERE status = active AND age > 26;

Результат:

OR

SELECT col1, col2, ...colN
FROM tableName
WHERE condition1 OR condition2 ...OR conditionN;

Возвращаемые записи должны удовлетворять хотя бы одному условию.

Сделаем выборку тех же полей неактивных пользователей или пользователей, младше 27 лет:

SELECT userId, userName, age
FROM users
WHERE status = inactive OR age < 27;

Результат:

↥ Наверх

Обновление полей

Для обновления полей используется инструкция UPDATE ... SET. Эта инструкция, обычно, используется в сочетании с предложением WHERE.

UPDATE tableName
SET col1 = val1, col2 = val2, ...colN = valN
[WHERE condition];

Обновим возраст пользователя с именем Igor:

UPDATE users
SET age = 30
WHERE username = 'Igor';

Если в данном случае опустить WHERE, то будет обновлен возраст всех пользователей.

↥ Наверх

Удаление записей

Для удаления записей используется инструкция DELETE. Эта инструкция также, как правило, используется в сочетании с предложением WHERE.

DELETE FROM tableName
[WHERE condition];

Удалим неактивных пользователей:

DELETE FROM users
WHERE status = 'inactive';

Если в данном случае опустить WHERE, то из таблицы users будут удалены все записи.

↥ Наверх

Предложения `LIKE` и `REGEX`

LIKE

Предложение LIKE используется для сравнения значений с помощью операторов с подстановочными знаками. Существует два вида таких операторов:

проценты (%)
нижнее подчеркивание (_)

% означает 0, 1 или более символов. _ означает точно 1 символ.

SELECT col1, col2, ...colN FROM tableName
WHERE col LIKE 'xxx%'
-- или
WHERE col LIKE '%xxx%'
-- или
WHERE col LIKE '%xxx'
-- или
WHERE col LIKE 'xxx_'
-- и т.д.

Примеры:

Сделаем выборку неактивных пользователей:

SELECT * FROM users
WHERE status LIKE 'in%';

Результат:

Сделаем выборку пользователей 30 лет и старше:

SELECT * FROM users
WHERE age LIKE '3_';

Результат:

REGEX

Предложение REGEX позволяет определять регулярное выражение, которому должна соответствовать запись.

SELECT col1, col2, ...colN FROM tableName
WHERE colName REGEXP регулярное выражение;

В регулярное выражении могут использоваться следующие специальные символы:

^ — начало строки
$ — конец строки
. — любой символ
[символы] — любой из указанных в скобках символов
[начало-конец] — любой символ из диапазона
| — разделяет шаблоны

Сделаем выборку пользователей с именами Igor и Vika:

SELECT * FROM users
WHERE userName REGEXP 'Igor|Vika';

Результат:

↥ Наверх

Предложение `TOP`/`LIMIT`/`ROWNUM`

Данные предложения позволяют извлекать указанное количество или процент записей с начала таблицы. Разные СУБД поддерживают разные предложения.

SELECT TOP number|percent col1, col2, ...colN
FROM tableName
[WHERE condition];

Сделаем выборку первых трех пользователей:

SELECT TOP 3 * FROM users;

Результат:

В mysql:

SELECT * FROM users
LIMIT 3, [offset];

Параметр offset (смещение) определяет количество пропускаемых записей. Например, так можно извлечь первых двух пользователей, начиная с третьего:

SELECT * FROM users
LIMIT 2, 2;

В oracle:

SELECT * FROM users
WHERE ROWNUM <= 3;

↥ Наверх

Предложения `ORDER BY` и `GROUP BY`

ORDER BY

Предложение ORDER BY используется для сортировки данных по возрастанию (ASC) или убыванию (DESC). Многие СУБД по умолчанию выполняют сортировку по возрастанию.

SELECT col1, col2, ...colN
FROM tableName
[WHERE condition]
[ORDER BY col1, col2, ...colN] [ASC | DESC];

Обратите внимание: колонки для сортировки должны быть указаны в списке колонок для выборки.

Сделаем выборку пользователей, отсортировав их по городу и возрасту:

SELECT * FROM users
ORDER BY city, age;

Результат:

Теперь выполним сортировку по убыванию:

SELECT * FROM users
ORDER BY city, age DESC;

Определим собственный порядок сортировки по убыванию:

SELECT * FROM users
ORDER BY (CASE city
  WHEN 'Ekaterinburg' THEN 1
  WHEN 'Moscow' THEN 2
ELSE 100 END) ASC, city DESC;

GROUP BY

Предложение GROUP BY используется совместно с инструкцией SELECT для группировки записей. Оно указывается после WHERE и перед ORDER BY.

SELECT col1, col2, ...colN
FROM tableName
WHERE condition
GROUP BY col1, col2, ...colN
ORDER BY col1, col2, ...colN;

Сгруппируем активных пользователей по городам:

SELECT city, COUNT(city) AS amount FROM users
WHERE status = active
GROUP BY city
ORDER BY city;

Результат:

↥ Наверх

Ключевое слово `DISTINCT`

Ключевое слово DISTINCT используется совместно с инструкцией SELECT для возврата только уникальных записей (без дубликатов).

SELECT DISTINCT col1, col2, ...colN
FROM tableName
[WHERE condition];

Сделаем выборку городов проживания пользователей:

SELECT DISTINCT city
FROM users;

Результат:

↥ Наверх

Соединения

Соединения (joins) используются для комбинации записей двух и более таблиц.

Предположим, что кроме users, у нас имеется таблица orders с заказами пользователей следующего содержания:

Сделаем выборку полей userId, userName, age и amount из наших таблиц посредством их соединения:

SELECT userId, userName, age, amount
FROM users, orders
WHERE users.userId = orders.userId;

Результат:

При соединении таблиц могут использоваться такие операторы, как =, <, >, <>, <=, >=, !=, BETWEEN, LIKE и NOT, однако наиболее распространенным является =.

Существуют разные типы объединений:

INNER JOIN — возвращает записи, имеющиеся в обеих таблицах
LEFT JOIN — возвращает записи из левой таблицы, даже если такие записи отсутствуют в правой таблице
RIGHT JOIN — возвращает записи из правой таблицы, даже если такие записи отсутствуют в левой таблице
FULL JOIN — возвращает все записи объединяемых таблиц
CROSS JOIN — возвращает все возможные комбинации строк обеих таблиц
SELF JOIN — используется для объединения таблицы с самой собой

↥ Наверх

Предложение `UNION`

Предложение/оператор UNION используется для комбинации результатов двух и более инструкций SELECT. При этом, возвращаются только уникальные записи.

В случае с UNION, каждая инструкция SELECT должна иметь:

одинаковый набор колонок для выборки
одинаковое количество выражений
одинаковые типы данных колонок и
одинаковый порядок колонок

Однако, они могут быть разной длины.

SELECT col1, col2, ...colN
FROM table1
[WHERE condition]

UNION

SELECT col1, col2, ...colN
FROM table2
[WHERE condition];

Объединим наши таблицы users и orders:

  SELECT userId, userName, amount, date
  FROM users
  LEFT JOIN orders
  ON users.useId = orders.userId
UNION
  SELECT userId, userName, amount, date
  FROM users
  RIGHT JOIN orders
  ON users.userId = orders.userId;

Результат:

↥ Наверх

Предложение `UNION ALL`

Предложение UNION ALL также используется для объединения результатов двух и более инструкций SELECT. При этом, возвращаются все записи, включая дубликаты.

SELECT col1, col2, ...colN
FROM table1
[WHERE condition]

UNION ALL

SELECT col1, col2, ...colN
FROM table2
[WHERE condition];

Существует еще два предложения, похожих на UNION:

INTERSECT — используется для комбинации результатов двух и более SELECT, но возвращаются только строки из первого SELECT, совпадающие со строками из второго SELECT
EXCEPT|MINUS — возвращаются только строки из первого SELECT, отсутствующие во втором SELECT

↥ Наверх

Синонимы

Синонимы (aliases) позволяют временно изменять названия таблиц и колонок. «Временно» означает, что новое название используется только в текущем запросе, в БД название остается прежним.

Синтаксис синонима таблицы:

SELECT col1, col2, ...colN
FROM tableName AS aliasName
[WHERE condition];

Синтаксис синонима колонки:

SELECT colName AS aliasName
FROM tableName
[WHERE condition];

Пример использования синонимов таблиц:

SELECT U.userId, U.userName, U.age, O.amount
FROM users AS U, orders AS O
WHERE U.userId = O.userId;

Результат:

Пример использования синонимов колонок:

SELECT userId AS user_id, userName AS user_name, age AS user_age
FROM users
WHERE status = active;

Результат:

↥ Наверх

Индексы

Создание индексов

Индексы — это специальные поисковые таблицы (lookup tables), которые используются движком БД в целях более быстрого извлечения данных. Проще говоря, индекс — это указатель или ссылка на данные в таблице.

Индексы ускоряют работу инструкции SELECT и предложения WHERE, но замедляют работу инструкций UPDATE и INSERT. Индексы могут создаваться и удаляться, не оказывая никакого влияния на данные.

Для создания индекса используется инструкция CREATE INDEX, позволяющая определять название индекса, индексируемые колонки и порядок индексации (по возрастанию или по убыванию).

К индексам можно применять ограничение UNIQUE для того, чтобы обеспечить их уникальность.

Синтаксис создания индекса:

CREATE INDEX indexName ON tableName;

Синтаксис создания индекса для одной колонки:

CREATE INDEX indexName
ON tableName (colName);

Синтакис создания уникальных индексов (такие индексы используются не только для повышения производительности, но и для обеспечения согласованности данных):

CREATE UNIQUE INDEX indexName
ON tableName (colName);

Синтаксис создания индексов для нескольких колонок (композиционный индекс):

CREATE INDEX indexName
ON tableName (col1, col2, ...colN);

Решение о создании индексов для одной или нескольких колонок следует принимать на основе того, какие колонки будут часто использоваться в запросе WHERE в качестве условия для сортировки строк.

Для ограничений PRIMARY KEY и UNIQUE автоматически создаются неявные индексы.

Удаление индексов

Для удаления индексов используется инструкция DROP INDEX:

DROP INDEX indexName;

Несмотря на то, что индексы предназначены для повышения производительности БД, существуют ситуации, в которых их использования лучше избегать.

К таким ситуациям относится следующее:

индексы не должны использоваться в маленьких таблицах
в таблицах, которые часто и в большом объеме обновляются или перезаписываются
в колонках, которые содержат большое количество нулевых значений
в колонках, над которыми часто выполняются операции

↥ Наверх

Обновление таблицы

Команда ALTER TABLE используется для добавления, удаления и модификации колонок существующей таблицы. Также эта команда используется для добавления и удаления ограничений.

Синтаксис:

-- добавление новой колонки
ALTER TABLE tableName ADD colName datatype;

-- удаление колонки
ALTER TABLE tableName DROP COLUMN colName;

-- изменение типа данных колонки
ALTER TABLE tableName MODIFY COLUMN colName newDatatype;

-- добавление ограничения `NOT NULL`
ALTER TABLE tableName MODIFY colName datatype NOT NULL;

-- добавление ограничения `UNIQUE`
ALTER TABLE tableName
ADD CONSTRAINT myUniqueConstraint UNIQUE (col1, col2, ...colN);

-- добавление ограничения `CHECK`
ALTER TABLE tableName
ADD CONSTRAINT myUniqueConstraint CHECK (condition);

-- добавление первичного ключа
ALTER TABLE tableName
ADD CONSTRAINT myPrimaryKey PRIMARY KEY (col1, col2, ...colN);

-- удаление ограничения
ALTER TABLE tableName
DROP CONSTRAINT myUniqueContsraint;

-- mysql
ALTER TABLE tableName
DROP INDEX myUniqueContsraint;

-- удаление первичного ключа
ALTER TABLE tableName
DROP CONSTRAINT myPrimaryKey;

-- mysql
ALTER TABLE tableName
DROP PRIMARY KEY;

Добавляем в таблицу users новую колонку — пол пользователя:

ALTER TABLE users ADD sex char(1);

Удаляем эту колонку:

ALTER TABLE users DROP sex;

↥ Наверх

Очистка таблицы

Команда TRUNCATE TABLE используется для очистки таблицы. Ее отличие от DROP TABLE состоит в том, что сохраняется структура таблицы (DROP TABLE полностью удаляет таблицу и все ее данные).

TRUNCATE TABLE tableName;

Очищаем таблицу users:

TRUNCATE TABLE users;

Проверяем, что users пустая:

SELECT * FROM users;
-- Empty set (0.00 sec)

↥ Наверх

Представления

Представление (view) — это не что иное, как инструкция, записанная в БД под определенным названием. Другими словами, представление — это композиция таблицы в форме предварительно определенного запроса.

Представления могут содержать все или только некоторые строки таблицы. Представление может быть создано на основе одной или нескольких таблиц (это зависит от запроса для создания представления).

Представления — это виртутальные таблицы, позволяющие делать следующее:

структурировать данные способом, который пользователи находят наиболее естественным или интуитивно понятным
ограничивать доступ к данным таким образом, что пользователь может просматривать и (иногда) модифицировать только то, что ему нужно и ничего более
объединять данные из нескольких таблиц для формирования отчетов

Создание представления

Для создания представления используется инструкция CREATE VIEW. Как было отмечено, представления могут создаваться на основе одной или нескольких таблиц, и даже на основе другого представления.

CREATE VIEW viewName AS
SELECT col1, col2, ...colN
FROM tableName
[WHERE condition];

Создаем представление для имен и возраста пользователей:

CREATE VIEW usersView AS
SELECT userName, age
FROM users;

Получаем данные с помощью представления:

SELECT * FROM usersView;

Результат:

WITH CHECK OPTION

WITH CHECK OPTION — это настройка инструкции CREATE VIEW. Она позволяет обеспечить соответствие всех UPDATE и INSERT условию, определенном в представлении.

Если условие не удовлетворяется, выбрасывается исключение.

CREATE VIEW usersView AS
SELECT userName, age
FROM users
WHERE age IS NOT NULL
WITH CHECK OPTION;

Обновление представления

Представление может быть обновлено при соблюдении следующих условий:

SELECT не содержит ключевого слова DISTINCT
SELECT не содержит агрегирующих функций
SELECT не содержит функций установки значений
SELECT не содержит операций установки значений
SELECT не содержит предложения ORDER BY
FROM не содержит больше одной таблицы
WHERE не содержит подзапросы
запрос не содержит GROUP BY или HAVING
вычисляемые колонки не обновляются
все ненулевые колонки из базовой таблицы включены в представление в том же порядке, в каком они указаны в запросе INSERT

Пример обновления возраста пользователя с именем Igor в представлении:

UPDATE usersView
SET age = 31
WHERE userName = 'Igor';

Обратите внимание: обновление строки в представлении приводит к ее обновлению в базовой таблице.

В представление могут добавляться новые строки с помощью команды INSERT. При выполнении этой команды должны соблюдаться те же правила, что и при выполнении команды UPDATE.

С помощью команды DELETE можно удалять строки из представления.

Удаляем из представления пользователя, возраст которого составляет 26 лет:

DELETE FROM usersView
WHERE age = 26;

Обратите внимание: удаление строки в представлении приводит к ее удалению в базовой таблице.

Удаление представления

Для удаления представления используется инструкция DROP VIEW:

DROP VIEW viewName;

Удаляем представление usersView:

DROP VIEW usersView;

↥ Наверх

`HAVING`

Предложение HAVING используется для фильтрации результатов группировки. WHERE используется для применения условий к колонкам, а HAVING — к группам, созданным с помощью GROUP BY.

HAVING должно указываться после GROUP BY, но перед ORDER BY (при наличии).

SELECT col1, col2, ...colN
FROM table1, table2, ...tableN
[WHERE condition]
GROUP BY col1, col2, ...colN
HAVING condition
ORDER BY col1, col2, ...colN;

↥ Наверх

Транзакции

Транзакция — это единица работы или операции, выполняемой над БД. Это последовательность операций, выполняемых в логическом порядке. Эти операции могут запускаться как пользователем, так и какой-либо программой, функционирующей в БД.

Транзакция — это применение одного или более изменения к БД. Например, при создании/обновлении/удалении записи мы выполняем транзакцию. Важно контролировать выполнение таких операций в целях обеспечения согласованности данных и обработки возможных ошибок.

На практике, запросы, как правило, не отправляются в БД по одному, они группируются и выполняются как часть транзакции.

Свойства транзакции

Транзакции имеют 4 стандартных свойства (ACID):

атомарность (atomicity) — все операции транзакции должны быть успешно завершены. В противном случае, транзакция прерывается, а все изменения отменяются (происходит откат к предыдущему состоянию)
согласованность (consistency) — состояние должно изменться в полном соответствии с операциями транзакции
изоляция или автономность (isolation) — транзакции не зависят друг от друга и не оказывают друг на друга никакого влияния
долговечность (durability) — результат звершенной транзакции должен сохраняться при поломке системы

Управление транзакцией

Для управления транзакцией используются следующие команды:

BEGIN|START TRANSACTION — запуск транзакции
COMMIT — сохранение изменений
ROLLBACK — отмена изменений
SAVEPOINT — контрольная точка для отмены изменений
SET TRANSACTION — установка характеристик текущей транзакции

Команды для управления транзакцией могут использоваться только совместно с такими запросами как INSERT, UPDATE и DELETE. Они не могут использоваться во время создания и удаления таблиц, поскольку эти операции автоматически отправляются в БД.

Удаляем пользователя, возраст которого составляет 26 лет, и отправляем изменения в БД:

BEGIN TRANSACTION
DELETE FROM users
WHERE age = 26;
COMMIT;

Удаляем пользователя с именем Oleg и отменяем эту операцию:

BEGIN
  DELETE FROM users
  WHERE username = 'Oleg';
ROLLBACK;

Контрольные точки создаются с помощью такого синтаксиса:

SAVEPOINT savepointName;

Возврат к контрольной точке выполняется так:

ROLLBACK TO savepointName;

Выполняем три запроса на удаление данных из users, создавая контрольные точки перед каждый удалением:

START TRANSACTION
SAVEPOINT sp1;
DELETE FROM users
WHERE age = 26;

SAVEPOINT sp2;
DELETE FROM users
WHERE userName = 'Oleg';

SAVEPOINT sp3;
DELETE FROM users
WHERE status = 'inactive';

Отменяем два последних удаления, возвращаясь к контрльной точке sp2, созданной после первого удаления:

ROLLBACK TO sp2;

Делаем выборку пользователей:

SELECT * FROM users;

Результат:

Как видим, из таблицы был удален только пользователь с возрастом 26 лет.

Для удаление контрольной точки используется команда RELEASE SAVEPOINT. Естественно, после удаления контрольной точки, к ней нельзя будет вернуться с помощью ROLLBACK TO.

Команда SET TRANSACTION используется для инициализации транзакции, т.е. начала ее выполнения. При этом, можно определять некоторые характеристики транзакции. Например, так можно определить уровень доступа транзакции (доступна только для чтения или для записи тоже):

SET TRANSACTION [READ WRITE | READ ONLY];

↥ Наверх

Временные таблицы

Некоторые СУБД поддерживают так называемые временные таблицы (temporary tables). Такие таблицы позволяют хранить и обрабатывать промежуточные результаты с помощью таких же запросов, как и при работе с обычными таблицами.

Временные таблицы могут быть очень полезными при необходимости хранения временных данных. Одной из главных особенностей таких таблиц является то, что они удаляются по завершении текущей сессии. При запуске скрипта временная таблица удаляется после завершения выполнения этого скрипта. При доступе к БД с помощью клиентской программы, такая таблица будет удалена после закрытия этой программы.

Временная таблица создается с помощью инструкции CREATE TEMPORARY TABLE, в остальном синтаксис создания таких таблиц идентичен синтаксису создания обычных таблиц.

Временная таблица удаляется точно также, как и обычная таблица, с помощью инструкции DROP TABLE.

↥ Наверх

Клонирование таблицы

Может возникнуть ситуация, когда потребуется получить точную копию существующей таблицы, а CREATE TABLE или SELECT окажется недостаточно в силу того, что мы хотим получить не только идентичную структуру, но также индексы, значения по умолчанию и т.д. копируемой таблицы.

В mysql, например, это можно сделать так:

вызываем команду SHOW CREATE TABLE для получения инструкции, выполненной при создании таблицы, включая индексы и прочее
меняем название таблицы и выполняем запрос. Получаем точную копию таблицы
опционально: если требуется содержимое копируемой таблицы, можно также использовать инструкции INSERT INTO или SELECT

↥ Наверх

Подзапросы

Подзапрос — это внутренний (вложенный) запрос другого запроса, встроенный (вставленный) с помощью WHERE или других инструкций.

Подзапрос используется для получения данных, которые будут использованы основным запросом в качестве условия для фильтрации возвращаемых записей.

Подзапросы могут использоваться в инструкциях SELECT, INSERT, UPDATE и DELETE, а также с операторами =, <, >, >=, <=, IN, BETWEEN и т.д.

Правила использования подзапросов:

они должны быть обернуты в круглые скобки
подзапрос должен содержать только одну колонку для выборки, если основной запрос не содержит несколько таких колонок, которые сравниваются в подзапросе
в подзапросе нельзя использовать команду ORDER BY, это можно сделать в основном запросе. В подзапросе для замены ORDER BY можно использовать GROUP BY
подзапросы, возвращающие несколько значений, могут использоваться только с операторами, которые работают с наборами значений, такими как IN
список SELECT не может содержать ссылки на значения, которые оцениваются (вычисляются) как BLOB, ARRAY, CLOB или NCLOB
подзапрос не может быть сразу передан в функцию для установки значений
команду BETWEEN нельзя использовать совместно с подзапросом. Тем не менее, в самомподзапросе указанную команду использовать можно

Подзапросы, обычно, используются в инструкции SELECT.

SELECT col1, col2, ...colN
FROM table1, table2, ...tableN
WHERE colName operator
  (SELECT col1, col2, ...colN
  FROM table1, table2, tableN
  [WHERE condition]);

Пример:

SELECT * FROM users
WHERE userId IN (
  SELECT userId FROM users
  WHERE status = 'active'
);

Результат:

Подзапросы могут использоваться в инструкции INSERT. Эта инструкция добавляет в таблицу данные, возвращаемые подзапросом. При этом, данные, возвращаемые подзапросом, могут быть модифицированы любыми способами.

INSERT INTO tableName col1, col2, ...colN
SELECT col1, col2, ...colN
FROM table1, table2, ...tableN
[WHERE operator [value]];

Подзапросы могут использоваться в инструкции UPDATE. При этом, данные из подзапроса могут использоваться для обновления любого количества колонок.

UPDATE tableName
SET col = newVal
[WHERE operator [value]
  (
    SELECT colName
    FROM tableName
    [WHERE condition]
  )
];

Данные, возвращаемые подзапросом, могут использоваться и для удаления записей.

DELETE FROM tableName
[WHERE operator [value]
  (
    SELECT colName
    FROM tableName
    [WHERE condition]
  )
];

↥ Наверх

Последовательности

Последовательность — это набор целых чисел (1, 2, 3 и т.д.), генерируемых автоматически. Последовательности часто используются в БД, поскольку многие приложения нуждаются в уникальных значениях, используемых для идентификации строк.

Приведенные ниже примеры рассчитаны на mysql.

Простейшим способом определения последовательности является использование AUTO_INCREMENT при создании таблицы:

CREATE TABLE tableName (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (id),
  -- другие строки
);

Для того, чтобы заново пронумеровать строки с помощью автоматически генерируемых значений (например, при удалении большого количества строк), можно удалить колонку, содержащую такие значения и создать ее заново. Обратите внимание: такая таблица не должна быть частью объединения.

ALTER TABLE tableName DROP id;
ALTER TABLE tableName
ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST,
ADD PRIMARY KEY (id);

По умолчанию значения, генерируемые с помощью AUTO_INCREMENT, начинаются с 1. Для того, чтобы установить другое начальное значение достаточно указать, например, AUTO_INCREMENT = 100 — в этом случае нумерация строк начнется со 100.

↥ Наверх

Источник

Описание

Синтаксис

Параметры или аргумент

Примечание

Пример — сортировка результатов по возрастанию

Пример — сортировка результатов по убыванию

Пример — сортировка результатов по относительной позиции

Пример — использование атрибутов ASC и DESC

Sort According To One Column

Syntax

Sort According To Multiple Columns

Syntax

Query

Output

Sort According To a Single Column

Query

Output

Sort According To Multiple Columns

Query

Output

Query

Output

Sorting By Column Number (instead of name)

Syntax

Query

Output

Conclusion

Introduction to SQL ORDER BY clause

SQL ORDER BY clause examples

1) Using SQL ORDER BY clause to sort values in one column example

2) Using SQL ORDER BY clause to sort values in multiple columns example

3) Using SQL ORDER BY clause to sort values in a numeric column example

4) Using SQL ORDER BY to sort by dates example

Summary

Содержание

Что такое SQL?

Почему SQL?

Процесс SQL

Команды SQL

Что такое таблица?

Что такое поле?

Что такое запись или строка?

Что такое колонка?

Что такое нулевое значение?

Ограничения

Целостность данных

Нормализация БД

Синтаксис SQL

Типы данных

Операторы

Выражения

Создание БД

Удаление БД

Выбор БД

Создание таблицы

Удаление таблицы

Добавление колонок

Выборка полей

Предложение WHERE

Операторы AND и OR

Обновление полей

Удаление записей

Предложения LIKE и REGEX

Предложение TOP/LIMIT/ROWNUM

Предложения ORDER BY и GROUP BY

Ключевое слово DISTINCT

Соединения

Предложение UNION

Предложение UNION ALL

Синонимы

Индексы

Обновление таблицы

Очистка таблицы

Представления

HAVING

Транзакции

Временные таблицы

Клонирование таблицы

Подзапросы

Последовательности

Introduction to SQL `ORDER BY` clause

SQL `ORDER BY` clause examples

1) Using SQL `ORDER BY` clause to sort values in one column example

2) Using SQL `ORDER BY` clause to sort values in multiple columns example

3) Using SQL `ORDER BY` clause to sort values in a numeric column example

4) Using SQL `ORDER BY` to sort by dates example

Предложение `WHERE`

Операторы `AND` и `OR`

Предложения `LIKE` и `REGEX`

Предложение `TOP`/`LIMIT`/`ROWNUM`

Предложения `ORDER BY` и `GROUP BY`

Ключевое слово `DISTINCT`

Предложение `UNION`

Предложение `UNION ALL`

`HAVING`