Базы данных. Великий и ужасный select

ПРЕДЛОЖЕНИЯ SQL ВЫБОРКА - SELECT

SELECT [предикат] { * | таблица.* | [таблица.]поле_1

ПРЕДЛОЖЕНИЯ SQL ПРОСТЕЙШИЙ ВАРИАНТ SELECT’а

SELECT column_name FROM table_name;

ПРЕДЛОЖЕНИЯ SQL ПРОСТЕЙШИЙ ВАРИАНТ SELECT’а

Persons

SELECT LastName, FirstName FROM Persons;

ПРЕДЛОЖЕНИЯ SQL ПРОСТЕЙШИЙ ВАРИАНТ SELECT’а

SELECT * FROM Persons;

Persons

ПРЕДЛОЖЕНИЯ SQL АЛИАСЫ

SELECT column AS column_alias FROM table;

SELECT column FROM table

ПРЕДЛОЖЕНИЯ SQL АЛИАСЫ

Persons

SELECT LastName AS Family, FirstName AS Name FROM Persons;

ПРЕДЛОЖЕНИЯ SQL АЛИАСЫ

Persons

SELECT LastName, FirstName FROM Persons AS Employees

Employees

ПРЕДЛОЖЕНИЯ SQL SELECT без повторов

SELECT DISTINCT column_name(s) FROM table_name;

ПРЕДЛОЖЕНИЯ SQL SELECT без повторов

"Orders"

SELECT Company
FROM Orders;

SELECT DISTINCT Company
FROM Orders;

ПРЕДЛОЖЕНИЯ SQL SELECT с условием

SELECT column FROM table
WHERE column operator value;

ПРЕДЛОЖЕНИЯ SQL SELECT с условием

SELECT * FROM Persons WHERE City='Sandnes‘;

"Persons"

ПРЕДЛОЖЕНИЯ SQL SELECT с условием

SELECT * FROM Persons WHERE FirstName='Tove';

SELECT *

ПРЕДЛОЖЕНИЯ SQL SELECT с условием LIKE

SELECT column FROM table
WHERE column LIKE

ПРЕДЛОЖЕНИЯ SQL SELECT с условием LIKE

Store_Information

SELECT * FROM Store_Information
WHERE store_name LIKE

ПРЕДЛОЖЕНИЯ SQL SELECT с условием LIKE

SELECT * FROM Persons WHERE FirstName LIKE

ПРЕДЛОЖЕНИЯ SQL BETWEEN

SELECT column_name FROM table_name
WHERE column_name BETWEEN value1 AND value2;

ПРЕДЛОЖЕНИЯ SQL BETWEEN

SELECT * FROM Persons
WHERE LastName BETWEEN 'Hansen' AND 'Pettersen';

ПРЕДЛОЖЕНИЯ SQL BETWEEN

SELECT * FROM Persons
WHERE LastName NOT BETWEEN 'Hansen' AND

ПРЕДЛОЖЕНИЯ SQL ORDER BY

Orders

SELECT Company, OrderNumber
FROM Orders
ORDER BY Company;

ПРЕДЛОЖЕНИЯ SQL ORDER BY

Orders

SELECT Company, OrderNumber
FROM Orders
ORDER BY Company,

ПРЕДЛОЖЕНИЯ SQL ORDER BY

Orders

SELECT Company, OrderNumber
FROM Orders
ORDER BY Company DESC;

ПРЕДЛОЖЕНИЯ SQL ORDER BY

Orders

SELECT Company, OrderNumber
FROM Orders
ORDER BY Company DESC,

ПРЕДЛОЖЕНИЯ SQL ORDER BY

SELECT store_name, Sales, Date
FROM Store_Information
ORDER BY 2

ПРЕДЛОЖЕНИЯ SQL AND & OR

SELECT * FROM Persons
WHERE FirstName='Tove' AND

ПРЕДЛОЖЕНИЯ SQL AND & OR

SELECT * FROM Persons
WHERE firstname='Tove' OR

ПРЕДЛОЖЕНИЯ SQL AND & OR

SELECT * FROM Persons
WHERE (FirstName='Tove' OR

ПРЕДЛОЖЕНИЯ SQL AND & OR

Store_Information

SELECT store_name FROM Store_Information
WHERE Sales >

ПРЕДЛОЖЕНИЯ SQL ФУНКЦИИ

SELECT function(column) FROM table;

AVG – среднее значение в столбце
COUNT

ПРЕДЛОЖЕНИЯ SQL AVG

Persons

SELECT AVG(Age) FROM Persons

32.67

SELECT AVG(Age) FROM Persons

ПРЕДЛОЖЕНИЯ SQL COUNT

Store_Information

SELECT COUNT(store_name) FROM Store_Information;

ПРЕДЛОЖЕНИЯ SQL COUNT

Store_Information

SELECT COUNT(DISTINCT store_name) FROM Store_Information;

ПРЕДЛОЖЕНИЯ SQL MAX

SELECT MAX(Age) FROM Persons

Persons

45

ПРЕДЛОЖЕНИЯ SQL MIN

Persons

SELECT MIN(Age) FROM Persons

19

ПРЕДЛОЖЕНИЯ SQL SUM

Store_Information

SELECT SUM(Sales) FROM Store_Information;

$1500 + $250 + $300 +

Некоторые функции MS Access

Некоторые функции MS Access

Некоторые функции MS SQL Server

ПРЕДЛОЖЕНИЯ SQL SELECT IN

SELECT column_name FROM table_name
WHERE column_name IN (value1,value2,..);

SELECT *

где, expression — любое символьное выражение
pattern — шаблон, по которому будет происходить проверка выражения expression. Шаблон

Примеры оператора SQL LIKE

SELECT * FROM Universities WHERE Site LIKE '[k-o]%‘
SELECT

SELECT * FROM Universities WHERE Site LIKE '_ _ _ _.ru

SELECT * FROM Universities WHERE Site LIKE '[k-o]%'

SELECT * FROM Universities WHERE Location LIKE '_[^e-s]%'

SELECT supplier_city, supplier_state
FROM suppliers
WHERE supplier_name = 'Intel'
ORDER BY supplier_city DESC, supplier_state