«SQL должен знать, должен знать» 4D Essence — главы с 8 по 13

база данных

«SQL должен знать, должен знать» сущность 4D

Эта статья является кратким изложением сути книги «SQL Must Know Must Know», которая помогает читателям быстро начать работу с SQL или MySQL.Основное содержание включает:

  • Основы базы данных
  • Операции, связанные с библиотечной таблицей
  • способ получения данных

В этой статье представлены главы с 8 по 13, пожалуйста, обратитесь к предыдущим главам.SQL необходимо знать, необходимо суммировать 1 - главы с 1 по 7

Совокупные данные

Агрегатная функция

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

функция эффект
AVG() Возвращает среднее значение столбца
COUNT() функция, которая возвращает столбец
MAX() Возвращает максимальное значение столбца
MIN() Возвращает минимальное значение столбца
SUM() Возвращает сумму значений столбца

1. Функция СРЕДН()

SELECT AVG(prod_price) AS avg_price   -- 求平均值
FROM Products;

Приведенное выше решает среднее значение всех строк, или вы можете указать конкретную строку для решения:

SELECT AVG(prod_price) AS avg_price   -- 求平均值
FROM Products
WHERE vend_id = 'DLLO1';   -- 指定特定的行

Примечание. Функция AVG() игнорирует строки со значением NULL.

2. Функция СЧЕТ()

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

  • count(*): Будет ли это нулевое значение (NULL) или ненулевое значение, оно будет подсчитано
  • count(column): подсчет определенного столбца, игнорируя значение NULL столбца в таблице.
SELECT COUNT(*) AS num_cust
FROM Customers;

num_cust
--------
5

SELECT COUNT(cust_email) AS num_cust
FROM Customers;

num_cust
--------
3

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

3. Функция МАКС()/МИН()

Возвращает максимальное или минимальное значение в указанном столбце

SELECT MAX(prod_price) AS MAX_price   -- 求最大值
SELECT MAX(prod_price) AS MIN_price   -- 求最小值
FROM Products;

Примечание. Две приведенные выше функции с наибольшим значением автоматически игнорируют строки со значениями NULL.

4. Функция СУММ()

Возвращает сумму (общую) указанных значений столбца

SELECT SUM(quantity) AS items_ordered
FROM OrderItems
WHERE order_num = 20005;

Функцию СУММ() также можно использовать для суммирования вычисляемых значений:

SELECT SUM(item_price * quantity) AS total_price   -- 返回所有物品的价钱之和
FROM OrderItems
WHERE order_num = 20005;

Примечание. Функция SUM() автоматически игнорирует строки со значениями NULL.

Агрегировать отдельные значения

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

  • Выполнить вычисление для всех строк, указав параметр ALL или не указывая параметр (поскольку ALL является поведением по умолчанию)
  • Включать только разные значения, указывать параметр DISTINCT, указывающий, что расчет выполняется после дедупликации

Примечание. Параметр ALL указывать не нужно, это поведение по умолчанию.

SELECT AVG(DISTINCT prod_price) AS avg_price   -- 去重之后再求平均值
FROM Products
WHERE vend_id = 'DLLO1';   -- 指定特定的行

Примечания:

1. DISTINCT нельзя использовать в COUNT(*), если указано имя столбца, DISTINCT можно использовать только в COUNT()

2. DISTINCT должен использовать имена столбцов и не может использоваться в вычислениях или выражениях.

3. DISTINCT не имеет значения для MAX() и MIN(), потому что максимальное значение одинаково независимо от того, рассматривается ли дедупликация или нет.

Объединение агрегатных функций

В предложение SELECT можно включить несколько агрегатных функций.

SELECT 
	AVG(prod_price) AS avg_price   -- 求平均值
	,MAX(prod_price) AS max_price   -- 求最大值
	,MIN(prod_price) AS min_price   -- 求最小值
	,COUNT(*) AS num_items   -- 物品的数目
	
FROM Products;

пакетные данные

Группировка использует два предложения:

  • GROUP BY()
  • HAVING()

Создать группы

Группировка устанавливается с помощью предложения GROUP BY предложения SELECT, см. пример:

SELECT 
	vend_id
	,COUNT(*) AS num_prods
FROM Products
GROUP BY vend_id;   -- 分组的列

Общие правила при использовании предложения GROUP BY:

  1. Предложение GROUP BY может содержать любое количество столбцов, а группы могут быть вложенными.
  2. Каждый столбец, указанный в предложении GROUP BY, является столбцом поиска или допустимым выражением (но не статистической функцией).
  3. Если вы используете выражение в SELECT, вы должны использовать то же выражение в предложении GROUP BY вместо использования псевдонима.
  4. За исключением агрегатных функций, каждый столбец в операторе SELECT должен быть указан в предложении GROUP BY.
  5. Если группировка содержит строки со значениями NULL, значения NULL будут возвращены как одна группа; если в столбце появится несколько значений NULL, они будут сгруппированы в одну группу.
  6. Предложение GROUP BY должно стоять после предложения WHERE и перед предложением ORDER BY.
  7. Относительные позиции можно использовать в предложении GROUP BY:GROUP BY 2, 1 означает группировку сначала по второму столбцу, затем по первому столбцу

группировка фильтров

В предложении WHERE вы указываете, что фильтруете строки, а не группы; на самом деле в WHERE нет понятия группировки. Используйте HAVING в SQL для группировки фильтров;

Примечания: WHERE фильтрует строки, HAVING фильтрует группы.

SELECT
	cust_id
	,COUNT(*) AS orders
FROM Orders
GROUP BY cust_id
HAVING COUNT(*) >= 2;    -- 过滤分组

Разница между WHERE и HAVING:

  • ГДЕ группировка до фильтрации данных, исключенных строк нет в статистике группировки
  • ИМЕЮЩАЯ фильтрацию после пакета данных
SELECT
	vend_id
	,COUNT(*) AS num_prods
FROM Products
WHERE prod_price >= 4   -- 分组前先执行，找出符合条件的数据
GROUP BY vend_id
HAVING COUNT(*) >= 2;  -- 分组后再执行，找出数目大于2的数据

группировка и сортировка

Различия между ORDER BY и GROUP BY:

ORDER BY GROUP BY
Отсортируйте полученный результат Сгруппировать строки, но выходные данные могут быть сгруппированы не в порядке
Можно использовать любой столбец (также можно использовать невыбранные столбцы) Возможны только столбцы выбора или столбцы выражений, и каждое выражение столбца выбора должно использоваться.
не обязательно требуется Если вы используете столбцы с агрегатными функциями, вы должны использовать 
SELECT
	order_num
	,COUNT(*) AS items
FROM OrderItems
GROUP BY order_num
HAVING COUNT(*) >= 3
ORDER BY items, order_num;   -- 先分组再过滤，最后排序输出

Порядок предложения SELECT

Вот краткое изложение соответствующего порядка предложений SELECT:

пункт инструкция Нужно ли использовать
SELECT столбец или выражение для возврата да
FROM таблица для извлечения данных из Используется только при выборе данных из таблицы
WHERE фильтрация на уровне строк нет
GROUP BY Группировка Описание Используется только при вычислении агрегатов по группам
HAVING Фильтрация на уровне группы нет
ORDER BY порядок сортировки вывода нет

Используйте подзапросы

Любая инструкция SELECT является запросом, а SQL также позволяет вкладывать запросы в запрос.

SELECT cust_id   -- 再根据子查询中的order_num找出符合要求的cust_id
FROM Orders
WHERE order_num IN (SELECT order_num   -- 先根据WHERE条件找出满足符合要求的order_num
                    FROM OrderItems
                    WHERE prod_id = 'RGAN01');

Примечание. Подзапросы всегда обрабатываются изнутри наружу.

SELECT Customers    -- 最后根据找出的cust_id查询Customers
FROM cust_id IN(SELECT cust_id   -- 再根据子查询中的order_num找出符合要求的cust_id
                FROM Orders
                WHERE order_num IN (SELECT order_num   -- 先根据WHERE条件找出满足符合要求的order_num
                                    FROM OrderItems
                                    WHERE prod_id = 'RGAN01'));

Использовать подзапросы как вычисляемые поля

Другой способ использования подзапросов — создание вычисляемых полей.

SELECT 
	cust_name
	,cust_state
	,(SELECT COUNT(*)   -- 将子查询作为一个计算字段输出：统计每个cust_id的数量
    FROM Orders
    WHERE Orders.cust_id = Customers.cust_id) AS orders   -- Orders.cust_id = Customers.cust_id 使用完全限定列名来避免歧义
FROM Customers
ORDER BY cust_name;

соединительный стол

Наиболее мощной функцией SQL является использование таблиц соединения (join) в процессе запроса данных.

Создать ссылку

Соединение создается путем указания того, какие таблицы объединять и как они соединяются.

SELECT
	vend_name,
	prod_name,
	prod_price
FROM Vendors, Products
WHERE Vendors.vend_id = Products.vend_id;    -- 指定联结条件

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

Примечания. Соединение, возвращающее декартово произведение, также известное как перекрестное соединение.

внутреннее соединение

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

SELECT
	vend_name,
	prod_name,
	prod_price
FROM Vendors
INNER JOIN Products   -- 内联结
ON Vendors.vend_id = Products.vend_id;    -- 指定联结条件

Присоединяйтесь к нескольким столам

SELECT
	vend_name,
	prod_name,
	prod_price
FROM OrderItems, Products, Vendors
WHERE Products.vend_id = Vendors.vend_id   -- 多个表的联结
AND OrderItems.prod_id = Products.prod_id
AND order_num = 20007;

Мы достигаем результатов подзапроса, присоединяя:

-- 子查询
SELECT Customers    -- 最后根据找出的cust_id查询Customers
FROM cust_id IN(SELECT cust_id   -- 再根据子查询中的order_num找出符合要求的cust_id
                FROM Orders
                WHERE order_num IN (SELECT order_num   -- 先根据WHERE条件找出满足符合要求的order_num
                                    FROM OrderItems
                                    WHERE prod_id = 'RGAN01'));
                                    
-- 内联结
SELECT 
	cust_name,
	cust_contact
FROM Customers, Orders, OrderItems
WHERE Customers.cust_id = Orders.cust_id   -- 多个表联结查询
AND OrderItems.order_num = Orders.order_num
AND prod_id = 'RGAN01'

Создание расширенных объединений

Использовать псевдонимы таблиц

Таблицы могут иметь псевдонимы в операторах SQL:

SELECT 
	cust_name,
	cust_contact
FROM Customers AS C, Orders AS O, OrderItems AS OI   -- 取别名，看上去更简洁
WHERE C.cust_id = O.cust_id   -- 多个表联结查询
AND OI.order_num = O.order_num
AND prod_id = 'RGAN01'

Используйте разные типы соединений

Введите 3 различных соединения:

  • самостоятельно присоединиться
  • естественное соединение
  • внешнее соединение

1. Самостоятельное присоединение

-- 子查询
SELECT cust_id,cust_name,cust_contact
FROM Customers
WHERE cust_name = (SELECT cust_name
                   FROM Customers
                   WHERE cust_contact = 'Jim Jones');
                   
-- 内联结
SELECT c1.cust_id, c2.cust_name, c1.cust_contact
FROM Customers AS c1,  Customers AS c2   -- 相同的表使用两次
WHERE c1.cust_name = c2.cust_name
AND c2.cust_contact = 'Jim Jones';

Таблица Customers используется дважды выше, во избежание двусмысленности, для их различения необходимо использовать разные псевдонимы.

2. Естественная связь

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

SELECT 
	C.*
	,O.order_num
	,O.order_date
	,OI.prod_id
	,OI.quantity
	,OI.item_price
FROM Customers AS C, Orders AS O, OrderItems AS OI 
WHERE C.cust_id = O.cust_id   -- 多个表联结查询
AND OI.order_num = O.order_num
AND prod_id = 'RGAN01'

3. Внешняя ссылка

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

  • Подсчитайте количество заказов, размещенных каждым покупателем, включая тех, кто еще не разместил заказ
  • Перечислите все продукты и количество заказов, включая продукты, которые никто не заказан
  • Рассчитать средний размер продаж, включая тех клиентов, которые еще не сделали заказ

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

SELECT 
	C.cust_id
	,O.order_num
FROM Customers AS C
LEFT OUTER JOIN Orders AS O   -- 外连接
ON Customers.cust_id = Orders.cust_id

Приведенный выше код представляет записи, содержащие все строки слева; если он находится справа, используйте RIGHT OUTER. Таким образом, на самом деле есть две формы внешних соединений, которые взаимозаменяемы между собой.

  • левое внешнее соединение
  • правое внешнее соединение

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

SELECT 
	C.cust_id
	,O.order_num
FROM Customers AS C
FULL OUTER JOIN Orders AS O   -- 外连接
ON Customers.cust_id = Orders.cust_id

Соединения с агрегатными функциями

Получить всех клиентов и количество заказов каждого клиента:

SELECT
	C.cust_id
	,COUNT(O.order_num) AS num_ord   -- 使用聚集函数统计订单数
FROM Customers AS C
INNER JOIN Orders   
ON C.cust_id = O.cust_id   -- 关联两个表
GROUP BY Customers.cust_id   -- 分组

Использование соединений и условий соединения

Подводя итог точкам подключения и использования:

  1. Обратите внимание на тип используемых соединений: обычно используйте внутренние соединения, иногда эффективны внешние соединения.
  2. Обязательно используйте правильные условия соединения, иначе будут возвращены неверные данные.
  3. Не забудьте предоставить условия связи, в противном случае он возвращает декартовому продукту
  4. Объединение может содержать несколько таблиц, и вы даже можете использовать разные типы соединений для разных таблиц. Позаботьтесь о проверке каждого соединения
Категории