SQL примитивы

Редакция автор: stain

Обычно в базах данных содержатся три вида данных:

  • 1. Контент
  • 2. Комментарии, обсуждения, и т.п.
  • 3. Данные для Авторизации пользователей.

Каждый вид находится в своих таблицах. SQL в командах анализирует БОЛЬШИЕ и маленькие буквы, как одно и тоже (SQL is case insensitive. It is common practice to write all SQL commands in upper-case).

SHOW - нужна для анализа баз таблиц, в командной строке, мало полезна. Показать все базы SHOW DATABASES;, все таблицы базы dbname SHOW TABLES IN dbname;, базу указывать необязательно если вы уже в ее контексте. Показать структуру таблицы SHOW tablename FROM dbname;

SELECT - выборка, это то чем мы будем постоянно оперировать SELECT column_list FROM table_name;, column_list список полей таблицы данные из которых мы хотим получить разделяются запятыми, если вам нужны все поля, то нужно написать знак * вместо перечисления всех колонок SELECT * FROM table_name;.
SELECT DISTINCT - но когда полей много, и нам надо исключить лишь пару, а остальные нужны, то к оператору SELECT добавляется дескриптор DISTINCT и уже за ним перечисляются поля таблицы которые мы хотим исключить из запроса SELECT DISTINCT column_name1, column_name2 FROM table_name;.
SELECT LIMIT - когда нам надо сделать выборку только первых или отсортированных первых строк из таблицы, мы добавляем такой дескриптор как LIMIT и после него указываем желаемое количество строк в сформированном запросе SELECT column list FROM table_name LIMIT [number of records];. Для того чтобы выбрать не с первой записи а с конкретной, например для постраничного вывода всей простыни после дескриптора LIMIT указываете начиная с какой записи формировать запрос, и сколько записей выдавать, эти два параметра должны разделяться запятой SELECT ID, FirstName, LastName, City FROM customers LIMIT 3, 4; В данном примере цифра 3 указывает на 4 тую строку, так как в SQL внутренняя нумерация строк начинается с нулевой.

Когда вы делаете запрос сразу из нескольких баз, и в них есть одинаковые поля, чтобы запрос был корректным, необходимо указывать имя таблицы перед название поля, разделяя их точкой SELECT customers.city, storage.city FROM customers, storage;. (насчет объединения баз и разделения их запятой пока не уверен)

SELECTORDER BY - cортировка по полю указанному после двусложного дескриптора ORDER BY SELECT * FROM customers ORDER BY FirstName;. Порядок сортировки по умолчанию алфавитный ASC его антипод DESC, чтобы отсортировать в обратном порядке, после поля по которому сортируем надо написать дескриптор DESC SELECT * FROM customers ORDER BY FirstName DESC;. Если вам надо отсортировать по нескольким полям, то укажите их через запятую ... ORDER BY FirstName, LastName;

WHERE - условия выборки. SELECT column_list FROM table_name WHERE condition;
Вариантам выборки условий способствуют эти:
where-clauses
пример условия не ординарным дескриптором что сидело на поле между а и б:
SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2;
Если мы ищем в текстовом поле таблицы, то надо наши условия опускать экранировать в одинарными кавычками.
SELECT ID, FirstName, LastName, City FROM customers WHERE City = 'New York';

Логические операторы AND, OR, IN, NOT.
and-or-in-not

SELECT ID, FirstName, LastName, Age FROM customers WHERE Age >= 30 AND Age <= 40;
Отдельного упоминания заслуживает IN - Если хоть один то TRUE
SELECT * FROM customers WHERE City IN ('New York', 'Los Angeles', 'Chicago');.
Удачный пример совмещения NOT IN
SELECT * FROM customers WHERE City NOT IN ('New York', 'Los Angeles', 'Chicago');
Для использования различных условий вместе помещайте возмутителей спокойствия в скобки
SELECT * FROM customers WHERE City = 'New York' AND (Age=30 OR Age=35);.

CONCAT - связывает текстовые переменные типа поиска в гугл SELECT CONCAT(FirstName, ', ' , City) FROM customers;
AS - виртуальное поле, а вот и то для чего нам пригодится CONCAT, позволяет связывать в результат запроса поля таблицы и возвращать их как одно виртуальное целое поле.
SELECT CONCAT(FirstName,', ', City) AS new_column FROM customers;
В данном примере все понятно :-), новое поле в данном случае называется new_column, и в нем будет 'FirstName, City'.

Арифметика + - / *
SELECT ID, FirstName, LastName, Salary+500 AS Salary FROM employees;
В данном запросе мы видим, что можно, формировать вычисленные данные от реальных, также здесь показано что AS не только создает виртуальные поля таблицы, но и может притворяться реальным полем. Самое время начислить нам на счета зарплату.

Функции.
UPPER() - Делает ПРОПИСНЫМИ результат выборки или того куда вы это подставите в запросе, естественно нет смысла применять функции к дескрипторам SQL.
LOWER() - Делает строчными результат выборки.
SQRT () - Так и не смог он в тот вечер понять реальное применение этого в жизни, если не касаться науки и высших сил.
SELECT Salary, SQRT(Salary) FROM employees;
В данном случае мы выдаем в выборке зарплату, и ее квадратный корень, вопрос зачем расскажут нам курсы бухгалтера (ищем срочно!).
AVG() - Выдаст нам в выборке среднее значение по столбцу. Полезно.
SUM() - Вычисляет общую сумму выбранного столбца таблицы SELECT SUM(Salary) FROM employees;
MIN() - Возвращает самое маленькое значение. SELECT MIN(Salary) AS Salary FROM employees;

Подзапросы. Вот оно!

SELECT FirstName, Salary FROM employees 
WHERE  Salary > (SELECT AVG(Salary) FROM employees) 
ORDER BY Salary DESC;

Enclose the subquery in parentheses.
Also, note that there is no semicolon at the end of the subquery, as it is part of our single query.
Подзапросы не должны оканчиваться точкой с запятой, они поглощены скобками.
подзапрос

LIKE - Контекстный запрос по части поля таблицы
SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern;
SELECT * FROM employees WHERE LastName LIKE '%s';
SQL pattern matching enables you to use "_" to match any single character and "%" to match an arbitrary number of characters (including zero characters).
% - любая строка
A% -Ищем совпадения где начальная буква а
%x -Ищем совпадения где последняя буква x
_ - нижний символ подчеркивания любой один символ
_2017%z - Вернет все варианты где первый символ любой, после него 4 символа 2017, далее любая строка, и обязательно заканчивается символом z, например y2017xyz.

Объединение таблиц.

SELECT customers.ID, customers.Name, orders.Name, orders.Amount
FROM customers, orders
WHERE customers.ID=orders.Customer_ID
ORDER BY customers.ID;

Сокращаем имена таблиц в запросе через дескриптор AS

SELECT ct.ID, ct.Name, ord.Name, ord.Amount
FROM customers AS ct, orders AS ord
WHERE ct.ID=ord.Customer_ID
ORDER BY ct.ID;

Способы присоединения таблиц
INNER JOIN - в эту выборку попадают только поля существующие в обеих таблицах.

SELECT column_name(s)
FROM table1 INNER JOIN table2 
ON table1.column_name=table2.column_name;

OUTER JOIN - Такое объединение вернет данные из обеих таблиц (совпадающие по условию объединения) ПЛЮС дополнит выборку оставшимися данными из внешней таблицы, которые по условию не подходят, заполнив недостающие данные значением NULL.(OUTER можно упускать)
LEFT JOIN - указывает что "внешней" таблицей будет находящаяся слева
RIGHT JOIN - вернет полный список департаментов (правая таблица) и сопоставленных пользователей..

SELECT u.id, u.name, d.name AS d_name
FROM users u
RIGHT OUTER JOIN departments d ON u.d_id = d.id

Note the ON keyword for specifying the inner join condition.
If no match is found for a particular row, NULL is returned.

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

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

Добавление строк в таблицы базы данных
INSERT INTO table_name VALUES (value1, value2, value3,...);
данные вставляются в ячейки по порядку, поэтому значения лучше не путать.
Чтобы избежать путаницы, или если вы заполняете в таблице не все поля, можно предварительно описать поля в запросе.
INSERT INTO table_name (column1, column2, column3, ...,columnN) VALUES (value1, value2, value3,...valueN);

Обновление строк в базе.
UPDATE table_name SET column1=value1, column2=value2 WHERE condition;

Удаление строк из базы.
DELETE FROM table_name WHERE condition;

Создать таблицу в базе.

CREATE TABLE Users
(
   UserID int,
   FirstName varchar(100), 
   LastName varchar(100),
   City varchar(100)
); 

Типы данных
The most common data types:
Numeric
INT -A normal-sized integer that can be signed or unsigned.
FLOAT(M,D) - A floating-point number that cannot be unsigned. You can optionally define the display length (M) and the number of decimals (D).
DOUBLE(M,D) - A double precision floating-point number that cannot be unsigned. You can optionally define the display length (M) and the number of decimals (D).

Date and Time
DATE - A date in YYYY-MM-DD format.
DATETIME - A date and time combination in YYYY-MM-DD HH:MM:SS format.
TIMESTAMP - A timestamp, calculated from midnight, January 1, 1970
TIME - Stores the time in HH:MM:SS format.

String Type
CHAR(M) - Fixed-length character string. Size is specified in parenthesis. Max 255 bytes.
VARCHAR(M) - Variable-length character string. Max size is specified in parenthesis.
BLOB - "Binary Large Objects" and are used to store large amounts of binary data, such as images or other types of files.
TEXT - Large amount of text data.

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

CREATE TABLE Users
(
   UserID int,
   FirstName varchar(100),
   LastName varchar(100),
   City varchar(100),
   PRIMARY KEY(UserID)
); 

SQL constraints are used to specify rules for table data.

The following are commonly used SQL constraints:
NOT NULL - Indicates that a column cannot contain any NULL value./*Поле не может быть пустым, тоесть поле является обязательным на уровне базы данных.*/
UNIQUE - Does not allow to insert a duplicate value in a column. The UNIQUE constraint maintains the uniqueness of a column in a table. More than one UNIQUE column can be used in a table.
PRIMARY KEY - Enforces the table to accept unique data for a specific column and this constraint create a unique index for accessing the table faster.
CHECK - Determines whether the value is valid or not from a logical expression./*при вставке проверка содержимого на логическую корректность*/
DEFAULT - While inserting data into a table, if no value is supplied to a column, then the column gets the value set as DEFAULT. /*установка значения по умолчанию*/

Автоинкримент
UserID int NOT NULL AUTO_INCREMENT, PRIMARY KEY (UserID)

Изменение таблиц в базе данных(предварительно делайте бекапы.)
ALTER TABLE command is used to add, delete, or modify columns in an existing table.
ALTER TABLE People ADD DateOfBirth date;
ADD - добавить поле или таблицу.
DROP - удалить поле или таблицу.
CHANGE - изменить поле. ALTER TABLE People CHANGE FirstName name varchar(100);
RENAME - переименовать таблицу. RENAME TABLE People TO Users;
Удаляем всю таблицу. DROP TABLE People;

VIEW - Создание отображения виртуальной таблицы.

CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition;

Views allow us to:
- Structure data in a way that users or classes of users find natural or intuitive.
- Restrict access to the data in such a way that a user can see and (sometimes) modify exactly what they need and no more.
- Summarize data from various tables and use it to generate reports.
A view always shows up-to-date data! The database engine uses the view's SQL statement to recreate the data each time a user queries a view.

Изменение виртуальной таблицы.

CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition;

Удаляется виртуальная таблица также как и обычная.

Tagged as:

Categorized in: coding

Комментариев: 0

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *

Этот сайт использует Akismet для борьбы со спамом. Узнайте как обрабатываются ваши данные комментариев.