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

Введение
Учебная база данных SQL (LearningSQL)
Визуальные инструменты для работы с MySQL
Запрос (Query)
Команды вставления данных (Insert)
Команда обновления (Update)
Команда удалить данные (Delete)
SQL Functions
SQL Join
Подзапрос (Subquery)
Программирование database с MySQL

1. Введение

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

MySQL >= 5.6

Содержание включает:

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

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

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

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

Пример базы данных MySQL для обучения SQL

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

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

Установить базу данных MySQL в Windows

4. Запрос (Query)

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;

Результаты запроса:

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;

Резульататы запуска команд выше:

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 с другими условиями.

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;

Результаты запуска запросов:

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');

Результаты запроса:

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') --
);

Результаты запроса:

SQL Wildcard

Есть 2 специальных знака в SQL:

Знак %
Знак _

Значение:

% описывает 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 '%-__-%';

Результаты запроса:

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

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

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;

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.
...

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)

Команда 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
   );

Команда 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

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;

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;

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;

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;

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

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;

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;

Результаты запроса:

RIGHT OUTER JOIN (Или RIGHT JOIN)

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

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.

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');

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;

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" по ссылке:

Lập trình database với MySQL

No ADS

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

1. Введение

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

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

4. Запрос (Query)

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

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

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

8. SQL Functions

9. SQL Join

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

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

Руководства База данных MySQL

Руководства База данных MySQL

Новейшие статьи