Руководство SQL для начинающих с MySQL

1- Введение

Данная статья руководство для начинающих в изучении SQL. Статья основана на:
  • MySQL >= 5.6

Содержание включает:
  • Язык SQL
  • Синтаксис SQL (Базовый синтаксис, стандарт для каждого вида DB)
  • Особый интаксис SQL у MySQL.

2- Учебная база данных SQL (LearningSQL)

Данная статья использует базу данных LearningSQL (версия  MySQL). Вам нужно создать ту базу данных для обучения (Вы потратите менее 5 минут на это).

Script создает базу данных и структуру данной базы данных вы можете посмотреть по ссылке:
  • TODO Link!

3- Визуальные инструменты для работы с MySQL

В данной статье я использую  MySQL Workbench это визуальный инструмент для работы. Вы можете посмотреть руководство установки  MySQL Comunity (После установки включает  MySQL Workbench) по ссылке:

4- Запрос (Query)

4.1- SQL Select

Команда  Select это базовая команда в  SQL, используется для запроса данных в таблице (Table).
  • PRODUCT_TYPE: Таблица данных видов продуктов (Service type of bank).
Это команда запроса данных в таблице  PRODUCT_TYPE
-- Query entire column of Product_Type table.

Select * From Product_Type;

-- The above statement and the following statement are equivalent.
-- Pty is alias (a alias of Product_Type table).

Select Pty.* From Product_Type Pty;

-- Query some columns.
Select Pty.Product_Type_Cd
     ,Pty.Name
From   Product_Type Pty;
Результаты запроса:
Запрос данных в таблице  EMPLOYEE:
-- Query 4 columns on the Employee table
-- Using emp as alias for Employee

Select Emp.Emp_Id
     ,Emp.First_Name
     ,Emp.Last_Name
     ,Emp.Dept_Id
From   Employee Emp;
Результаты запроса:

Создать Alias для столбца:

-- Using the concat (string1, string2) to  concatenate two strings together.
-- We have a new column, use 'as' to define column name for this column.

Select Emp.Emp_Id
   ,Emp.First_Name
   ,Emp.Last_Name
   ,Emp.Dept_Id
   ,Concat('EMP'
          ,Emp.Emp_Id) As Emp_No2  -- New column
From   Employee Emp;


-- Maybe not need 'as' in the definition Alias column.
Select Emp.Emp_Id
   ,Emp.First_Name
   ,Emp.Last_Name
   ,Emp.Dept_Id
   ,Concat('EMP'
          ,Emp.Emp_Id)  Emp_No2  -- New column
From   Employee Emp;
Результаты запроса:

4.2- SQL Distinct

Команда  distinct используется вместе с  Select, чтобы  select (выбрать) данные, пропуская совпадающие данные. Синтаксис:
Select distinct <column1>, <column2>  ....
Смотреть пример:
-- Query Product table.
Select Pro.Product_Cd
     ,Pro.Name
     ,Pro.Product_Type_Cd
From   Product Pro;

-- Query Product_Type in Product table.
Select Pro.Product_Type_Cd from Product Pro;

-- Distinct need to use to remove the duplicates.
Select Distinct Pro.Product_Type_Cd from Product Pro;
Резульататы запуска команд выше:

4.3- SQL Where

Where это команда которая ограничивает сферу поиска. Например вы хотите найти такие продукты услуги, как "Личные и бизнес кредиты".
  • Product_Type_Cd = 'LOAN'.
Вам нужно сделать запрос в таблице  PRODUCT, где есть  Product_Type_Cd= 'LOAN'.
-- Querying the Product table to find the type of products:
-- "Individual and Business Loans".
-- Corresponding column: Product_Type_Cd = 'LOAN'.

Select * From Product Pro Where Pro.Product_Type_Cd = 'LOAN';
Результаты запроса:
Другоей пример использования where с другими условиями.

4.4- SQL And Or (И, или)

And и Or это 2 оператора, использующихся в  where:
Например, вы хотите найти список работников с именем начинающимся на букву  'S' и работающих в Операционном отделе (Operations).
-- Query the employee whose first name starts with S.
Select Emp.Emp_Id
     ,Emp.First_Name
     ,Emp.Last_Name
     ,Emp.Dept_Id
From   Employee Emp
Where  Emp.First_Name Like 'S%';

-- Query the employee whose first name starts with S.
-- and work in Operation department.  Dept_Id  = 1.

Select Emp.Emp_Id
     ,Emp.First_Name
     ,Emp.Last_Name
     ,Emp.Dept_Id
From   Employee Emp
Where  Emp.First_Name Like 'S%'
And    Emp.Dept_Id = 1;
Результаты запуска запросов:
Ví dụ với câu lệnh Or.
-- Find the employees whose first name starts with S or P.
-- and work in Operation department.  Dept_Id  = 1.

Select Emp.Emp_Id
     ,Emp.First_Name
     ,Emp.Last_Name
     ,Emp.Dept_Id
From   Employee Emp
Where  (Emp.First_Name Like 'S%' Or Emp.First_Name Like 'P%')
And    Emp.Dept_Id = 1;
Результаты запуска запросов:

4.5- SQL IN (В диапазоне..)

Оператор  in в where используется в случае поиска значения определенного набора.
-- This command searches the employee named
-- Susan or  Paula or Helen.
Select Emp.Emp_Id
     ,Emp.First_Name
     ,Emp.Last_Name
     ,Emp.Dept_Id
From   Employee Emp
Where  Emp.First_Name In ('Susan'
                        ,'Paula'
                        ,'Helen');
Результаты запроса:

4.6- SQL Between (Между ...)

-- Find employees, with Emp_Id between 5 and 10.
Select Emp.Emp_Id
    ,Emp.First_Name
    ,Emp.Last_Name
    ,Emp.Start_Date
From   Employee Emp
Where  (Emp.Emp_Id Between 5 And 10);

-- The statement above is equivalent to:
Select Emp.Emp_Id
     ,Emp.First_Name
     ,Emp.Last_Name
     ,Emp.Start_Date
From   Employee Emp
Where  Emp.Emp_Id >= 5
And    Emp.Emp_Id <= 10;
Результаты запроса:
Команда  BETWEEN так же используется в ограничении времени. Например вы ищете всех работников, которые начали работать в определенный период:
  • 03-05-2002 ==> 09-08-2002  (dd-MM-yyyy)
-- This statement helps find employees who have begun working for a period of time
-- specify it in where statement.
-- For example,  03-05-2002 ==> 09-08-2002  (Format: dd-MM-yyyy)

Select Emp.Emp_Id
     ,Emp.First_Name
     ,Emp.Last_Name
     ,Emp.Start_Date
     , -- DATE_FORMAT is a function of MySQL .
       -- It may not exist in other DB.
       -- DATE_FORMAT convert Date type to text
       -- According to a certain format here is DD-MM-YYYY
      Date_Format(Emp.Start_Date
             ,'%d-%m-%Y') Start_Date_Vn
From   Employee Emp
Where
-- Str_To_Date is function of MySQL (It may not exist in other DB)
-- Str_To_Date convert Text to Date
-- According to a certain format here is DD-MM-YYYY
( --
 Emp.Start_Date Between Str_To_Date('03-05-2002'
                               ,'%d-%m-%Y') And
 Str_To_Date('09-08-2002'
        ,'%d-%m-%Y') --
);
Результаты запроса:

4.7- SQL Wildcard

Есть 2 специальных знака в SQL:
  1. Знак %
  2. Знак _
Значение:
  • % описывает 0, 1 или разные знаки.
  • _ описывает точно один знак.
Эти два знака обычно используются в условии LIKE.
-- Find Customers whose FED_ID is formatted:
-- The initial part is random, following by -, then two any characters, then -, and the last part is any.
-- Use two dashes (_) for illustrating two characters.
-- (Each dash (_) is a unique character).

Select Cus.Cust_Id
     ,Cus.Fed_Id
     ,Cus.Address
From   Customer Cus
where cus.fed_id like '%-__-%';
Результаты запроса:

4.8- SQL Like (Похоже на ...)

Эта команда знакома вам через примеры выше. 

4.9- SQL Order By

Запрос ( query) данных для набора результатов, может устроить не по-порядку, используем  Order by чтобы устроить по-порядку возвращенные результаты.
-- Syntax:

SELECT "column_name"
FROM "table_name"
[WHERE "condition"]
ORDER BY "column_name1" [ASC, DESC], "column_name2" [ASC, DESC];

-- Note:
-- ASC: ascending (default)
-- DESC: descending order..
Пример:
-- Arrange Product_Type_Cd in ascending order
-- Next, arrange Name in ascending order, too.

Select Pro.Product_Cd
    ,Pro.Product_Type_Cd
    ,Pro.Name
From   Product Pro
Order  By Pro.Product_Type_Cd Asc
       ,Pro.Name            Asc;
     
     
-- In Order BY, ASC is defaulted.
-- Thus, it is unnecessary to write ASC.
Select Pro.Product_Cd
    ,Pro.Product_Type_Cd
    ,Pro.Name
From   Product Pro
Order  By Pro.Product_Type_Cd
       ,Pro.Name;

-- Arrange Product_Type_Cd in descending order
-- Next, arrange Name in ascending order
Select Pro.Product_Cd
    ,Pro.Product_Type_Cd
    ,Pro.Name
From   Product Pro
Order  By Pro.Product_Type_Cd Desc
       ,Pro.Name            Asc;
Результаты запроса:
Order By всегда стоит сзади  where.
-- Find the employees whose first name starts with S.
-- Sort descending by start date of work.
Select Emp.Emp_Id
     ,Emp.First_Name
     ,Emp.Last_Name
     ,Emp.Start_Date
From   Employee Emp
Where  Emp.First_Name Like 'S%'
Order  By Emp.Start_Date Desc;
Результаты запроса:
-- Apply the order of column to Order by clause.
-- First_Name is the second column in Select clause.
-- Be able to use Order by 2 in lieu of Order by First_Name.

Select Emp.Emp_Id
    ,Emp.First_Name
    ,Emp.Last_Name
    ,Emp.Start_Date
From   Employee Emp
Order  By 2 Desc;

4.10- SQL Group By (Группировать по ...)

Для начала, нам нужно понять что такаое аггрегатные функции ( Aggregate Functions):
  • Sum: Функция возвращающая сумму
  • Avg: Функция возвращающая среднюю арифметическую
  • Count: Функция возвращающая количество элементов
  • Min: Функция находящая минимальное значение 
  • Max: Функция находящая максимальное значение 
Это некоторые общие аггрегатные функции ( Aggregate). Они могут участвовать в команде Group by.
-- Query Account table.

Select Acc.Account_Id
    ,Acc.Product_Cd
    ,Acc.Avail_Balance
    ,Acc.Pending_Balance
From   Account Acc;
Задается вопрос, вы хотите посмотреть сумму денег на счету, соответсвующая разной услуге ( Product_Cd). Это значит вам надо группировать на  Product_Cd.
Select Acc.Product_Cd
     ,Count(Acc.Product_Cd) As Count_Acc
     ,Sum(Acc.Avail_Balance) As Sum_Avail_Balance
     ,Avg(Acc.Avail_Balance) As Avg_Avail_Balance
From   Account Acc
Group  By Acc.Product_Cd;
Результаты запроса:
Так у вас есть оценка:
  • Есть 4 счета с услугами "Сберегательный вклад" (SAV) с суммой 1855.76 и в среднем каждый счет имеет 463.94.
  • ...

4.11- SQL Having

Having là một câu lệnh điều kiện của Group by.
Например вы хотите сгруппировать вид услуги ( Product_Cd) на таблице  Account, и отображать только тот вид, в котором участвующих > 3.
Select Acc.Product_Cd
     ,Count(Acc.Product_Cd) As Count_Acc 
     ,Sum(Acc.Avail_Balance) As Sum_Avail_Balance -- Sum available balance
     ,Avg(Acc.Avail_Balance) As Avg_Avail_Balance -- The average available balance
From   Account Acc
Group  By Acc.Product_Cd
Having Count(Acc.Product_Cd) > 3;
Результаты запуска примера:

Различать Where & Having

Вам нужно различать между  Where и  Having в одной команде.
  • Where это команда, которая фильтрует данные перед группировкой (Group)
  • Having это команда, которая фильтрует данные после группировки (Group)
Если вы хотите иметь общую информацию филиала банка (Таблица  BRANCH). Вы можете использовать  where, чтобы отфильтровать данные перед группировкой ( group).
Select Acc.Product_Cd
     ,Count(Acc.Product_Cd) As Count_Acc
     ,Sum(Acc.Avail_Balance) As Sum_Avail_Balance
     ,Avg(Acc.Avail_Balance) As Avg_Avail_Balance
From   Account Acc
-- Using WHERE to filter data before group
Where  Acc.Open_Branch_Id = 1
Group  By Acc.Product_Cd
-- Using HAVING to filter data after group
Having Count(Acc.Product_Cd) > 1;
Результаты запуска примера:

5- Команды вставления данных (Insert)

5.1- Команда Insert Into

Синтаксис:
-- Syntax of Insert Into:

INSERT INTO <table_name> ("column1", "column2", ...)
VALUES ("value1", "value2", ...);
Например вы добавляете транзакцию клиента в таблицу  ACC_TRANSACTION:
-- Insert a record in Acc_Transaction table
-- numeric value is automatically generated for Txn_ID.
-- Now() is function of MySQL, return system datetime
-- Now(): System datetime
Insert Into Acc_Transaction
  (Txn_Id
  ,Amount
  ,Funds_Avail_Date
  ,Txn_Date
  ,Txn_Type_Cd
  ,Account_Id
  ,Execution_Branch_Id
  ,Teller_Emp_Id)
Values
  (Null-- Txn_Id (automatically generated)
  ,100 -- Amount
  ,now() -- Funds_Avail_Date
  ,now() -- Txn_Date
  ,'CDT' -- Txn_Type_Cd
  ,2 -- Account_Id
  ,Null -- Execution_Branch_Id
  ,Null -- Teller_Emp_Id
   );

5.2- Команда Insert Into Select

Вы можете использовать команду  Select, чтобы предоставить данные для таблицы. Через команду  Insert Into ... Select.
-- Syntax of INSERT INTO .. SELECT

INSERT INTO "table1" ("column1", "column2", ...)
SELECT "column3", "column4", ...
FROM "table2";
Например:
-- Insert multi record in Acc_Transaction table
-- Use Select statement to provide data.

Insert Into Acc_Transaction
 (Txn_Id
 ,Txn_Date
 ,Account_Id
 ,Txn_Type_Cd
 ,Amount
 ,Funds_Avail_Date)
 Select Null -- Txn_Id (Tự sinh ra)
       ,Acc.Open_Date -- Txn_Date
       ,Acc.Account_Id -- Account_Id
       ,'CDT' -- Txn_Type_Cd
       ,200 -- Amount
       ,Acc.Open_Date -- Funds_Avail_Date
 From   Account Acc
 Where  Acc.Product_Cd = 'CD';

6- Команда обновления (Update)

-- Syntax of Update:

UPDATE "table_name"
SET "column_1" = "new value 1", "column_2"= "new value 2"
WHERE "condition";
Например вы хотите увеличить количество денег на счету клиента с  CUST_ID = 1 на  2%.
Команда Update:
-- Update, increase the amount of money in customers' account with CUST_ID = 1 by 2%.
Update Account Acc
Set    Acc.Avail_Balance   = Acc.Avail_Balance + 2 * Acc.Avail_Balance / 100
     ,Acc.Pending_Balance = Acc.Pending_Balance +
                            2 * Acc.Pending_Balance / 100
Where  Acc.Cust_Id = 1;
Сделать запрос, после  Update.

7- Команда удалить данные (Delete)

Синтаксис удаляет данные в таблице .
-- Syntax delete records in the table.

DELETE FROM "table_name"
WHERE "condition";
-- Delete two records in the Acc_Transaction table

Delete From Acc_Transaction Txn
Where  Txt.Txn_Id In (25
                   ,26);

8- SQL Functions

8.1- SQL Count

Count() это функция, считающая количество строк в  Query. Обычно используется вместе с  Group by.
-- Count the number of rows in the Account table

Select Count(Acc.Account_Id) From Account Acc;
Посчитать количество счетов имеющих транзакции с банком (Bảng Acc_Transaction)
-- Count the number of accounts having transaction with the bank

Select Count(distinct txn.Account_id) From Acc_Transaction txn;

Использовать с Group by:

Клиент может открыть несколько счетов, каждый счет соответствует продукту (услуге) банка. 
Вы хотите перечислить клиентов ( CUST_ID) с соответствующими счетами, которые они открыли.
-- Count the number of accounts opened for each customer....

Select Acc.Cust_Id
     ,Count(Acc.Account_Id)
From   Account Acc
Group  By Acc.Cust_Id;
 

8.2- SQL Sum

Sum() это функция которая возвращает сумму столбца в SQL.
-- Syntax:

SELECT SUM("column_name")
FROM "table_name";
Пример:
-- Find the sum of the money in customers' accounts with CUST_ID = = 1
Select Sum(Acc.Avail_Balance) From Account Acc Where Acc.Cust_Id = 1;

-- Use Group by.
-- Find the sum of the money in accounts owned by each customer.
Select Acc.Cust_Id
     ,Sum(Acc.Avail_Balance)
From   Account Acc
Group  By Acc.Cust_Id;

8.3- SQL AVG

AVG() это функция которая возвращает среднюю арифметическую столбца чисел.
-- Syntax:

SELECT AVG("column_name")
FROM "table_name";
Пример:
-- Find the average of money equivalent to each type of deposit.
Select Avg(Acc.Avail_Balance)
From   Account Acc
Where  Acc.Product_Cd = 'SAV';

-- Use Group by.
-- A customer can own one or more account.
-- Find the average of money in each account owned by each customer
-- (In the bank with Branch_ID = 1)
Select Acc.Cust_Id
    ,Avg(Acc.Avail_Balance)
From   Account Acc
Where  Acc.Open_Branch_Id = 1
Group  By Acc.Cust_Id;

8.4- SQL MIN

Min это функция, которая ищет минимальное значение в выбранном столбце чисел.
-- Syntax:

SELECT MIN ("column_name")
FROM "table_name";
Пример:
-- Find the minimum amount of deposit.
Select Min(Acc.Avail_Balance)
From   Account Acc
Where  Acc.Product_Cd = 'SAV';

-- Use Group by.
-- A customer can own one or more account.
-- Accounts can be opened in different branches.
-- Find the amount in the account, minimum for each branch
Select Acc.Open_Branch_Id
     ,Min(Acc.Avail_Balance)
From   Account Acc
Group  By Acc.Open_Branch_Id;

8.5- SQL MAX

MAX() это функция, которая возвращает максимальное значение в в выбранном столбце чисел. Используется как  MIN, можете посмотреть примеры в  MIN.
-- Syntax:

SELECT MAX("column_name")
FROM "table_name";

9- SQL Join

Например, вы смотрите на информацию работника в таблице EMPLOYEE. Вы увидите, что работник знает его/ее  ID отдела. Но это просто бессмысленное число.Чтобы узнать название отдела, вы должны смотреть в таблице  DEPARTMENT. Соединение этих 2 таблиц, чтобы получить полную информацию называется JOIN.
Есть 4 способа? чтобы соединить ( Join) 2 таблицы:
  • INNER JOIN   (JOIN)
  • LEFT OUTER JOIN  (LEFT JOIN)
  • RIGHT OUTER JOIN (RIGHT JOIN)
  • FULL OUTER JOIN    (OUTER JOIN)
  • CROSS JOIN

9.1- INNER JOIN (Или JOIN)

Ключевое слово  INNER JOIN выбирает все строки с двух таблиц, лишь бы совпадали данные столбцовдвух таблиц.

Синтаксис:

-- Syntax

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

-- INNER JOIN can replaced by JOIN
-- Same Meaning, and result.

SELECT column_name(s)
FROM table1
JOIN table2
ON table1.column_name=table2.column_name;
Пример:
-- INNER JOIN 2 table: EMPLOYEE and DEPARTMENT.

Select Emp.Emp_Id
     ,Emp.First_Name
     ,Emp.Last_Name
     ,Emp.Dept_Id
     ,Dep.Name Dept_Name
From   Employee Emp
Inner  Join Department Dep
On     Emp.Dept_Id = Dep.Dept_Id
Order  By Emp.Emp_Id;

MySQL's own syntax:

-- Other MySQL syntax to join two tables:

Select Emp.Emp_Id
  ,Emp.First_Name
  ,Emp.Last_Name
  ,Emp.Dept_Id
  ,Dep.Name Dept_Name
From   Employee   Emp
  ,Department Dep
Where  Emp.Dept_Id = Dep.Dept_Id
Order  By Emp.Emp_Id;

9.2- LEFT OUTER JOIN (Или LEFT JOIN)

Ключевое слово  LEFT OUTER JOIN возвращает все строки (rows) с таблицы слева (table1), и соответсвующие строки с таблицы справа (table2). Принимает данные  NULL в таблице 2 если не совпадают.
Смотрите иллюстрацию ниже:
Пример:
-- Customer LEFT OUTER JOIN Officer
-- LEFT OUTER JOIN Can be replaced by LEFT JOIN (meaning, and the result is the same)
Select Cus.Cust_Id
     ,Cus.Fed_Id
     ,Cus.State
     ,Ofc.Cust_Id As Ofc_Cust_Id
     ,Ofc.Officer_Id
     ,Ofc.Start_Date
     ,Ofc.Title
From   Customer Cus  -- Table1
Left   Outer Join Officer Ofc  -- Table2
On     Cus.Cust_Id = Ofc.Cust_Id;
Результаты запроса:

9.3- RIGHT OUTER JOIN (Или RIGHT JOIN)

RIGHT OUTER JOIN довольно похож на  LEFT OUTER JOIN:

9.4- FULL OUTER JOIN (Или OUTER JOIN)

FULL OUTER JOIN это комбинация  LEFT OUTER JOIN и  RIGHT OUTER JOIN
-- Syntax:  (FULL OUTER JOIN)
-- Or: FULL JOIN

SELECT columns
FROM table1
FULL [OUTER] JOIN table2
ON table1.column = table2.column;

10- Подзапрос (Subquery)

В MySQL, один  subquery это запрос в запросе. Вы можете создать подзапросы в ваших командах  SQL. Подзапросы могут находиться в предложении  WHERE, предложении  FROM, или предложении  SELECT.

10.1- Subquery в предложении Where

Чаще всего,  subquery находятся в предложении  WHERE. Эти подзапросы так же называются вложенными подзапросами.
Select Acc.Account_Id
     ,Acc.Open_Date
     ,Acc.Product_Cd
     ,Acc.Avail_Balance
From   Account Acc
Where  Acc.Cust_Id In
      (Select Cus.Cust_Id From Customer Cus Where Cus.Cust_Type_Cd = 'B');

10.2- Subquery в предложении From

Подзапрос может так же находиться в предложении  FROM. Они называются  inline views.
Select Cus.Cust_Id
     ,Cus.Address
     ,Cus.Fed_Id
     ,Acc2.Sum_Avail_Balance
From   Customer Cus
     , -- Define a inline view.
      (Select Acc.Cust_Id
             ,Sum(Acc.Avail_Balance) As Sum_Avail_Balance
       From   Account Acc
       Group  By Acc.Cust_Id) Acc2
Where  Cus.Cust_Id = Acc2.Cust_Id;

10.3- Subquery в предложении Select

Один  subquery так же может находиться в предложении  SELECT.
Select Cus.Cust_Id
     ,Cus.Address
     ,Cus.Fed_Id
     ,(Select Sum(Acc.Avail_Balance)
       From   Account Acc
       Where  Acc.Cust_Id = Cus.Cust_Id) As Sum_Avail_Balance
From   Customer Cus;
Секрет создания  subquery в предложении  select это  subquery должен возвратить единственное значение. Это причина почему такие функции, как  SUM, COUNT, MIN, или  MAX обычно используются в  subquery.

11- Программирование database с MySQL

Далее вы можете просмотреть статью "Программирование database с MySQL" по ссылке:
  • TODO Link!