Лабораторная работа №1
SQL : ИЗВЛЕЧЕНИЕ ДАННЫХ - команда SELECT
Цель работы:
· использование операторов IN, BETWEEN, LIKE, IS NULL.
Задание №1. Создать запрос на выборку в режиме SQL всех значений полей ИМЯ и ФАМИЛИЯ из таблицы СТУДЕНТЫ.
SELECT ИМЯ, ФАМИЛИЯ
FROM СТУДЕНТЫ;
Задание №2 . Создать запрос на выборку в режиме SQL всех столбцов таблицы СТУДЕНТЫ.
SELECT *
FROM СТУДЕНТЫ;
Задание №3. Создать запрос на выборку в режиме SQL названий городов, где проживают студенты, сведения о которых находятся в таблице ЛИЧНЫЕ ДАННЫЕ.
SELECT DISTINCT ГОРОД
FROM [ЛИЧНЫЕ ДАННЫЕ];
Задание №4. Создать запрос на выборку в режиме SQL, выполняющий выборку имен всех студентов с фамилией Иванов, сведения о которых находятся в таблице СТУДЕНТЫ.
SELECT ФАМИЛИЯ, ИМЯ
FROM СТУДЕНТЫ
WHERE ФАМИЛИЯ="Иванов";
Задание №5 . Создать запрос на выборку в режиме SQL для получения имен и фамилий студентов, обучающихся в группе УИТ-22 на бюджетной форме обучения.
SELECT ФАМИЛИЯ, ИМЯ
FROM СТУДЕНТЫ
WHERE ГРУППА="УИТ-22" AND БЮДЖЕТ=true;
Задание №6 . Создать запрос в режиме SQL. на выборку из таблицы СДАЧА ЭКЗАМЕНОВ сведении о студентах, имеющих оценки только 4 и 5.
SELECT *
FROM [ СДАЧА ЭКЗАМЕНОВ ]
WHERE ОЦЕНКА IN (4,5);
Задание №7. Coздать зanpoc и режиме SQL на выборку сведений о студентах, имеющих экзаменационную оценку 3 по предмету ИОСУ.
SELECT *
FROM [ СДАЧА ЭКЗАМЕНОВ ]
WHERE ПРЕДМЕТ =" ИОСУ " And ОЦЕНКА Not In (4,5);
Задание №8. Создать запрос в режиме SQL на выборку записей о предметах, часы которых находятся в пределах между 100 и 130.
SELECT *
FROM ПРЕДМЕТЫ
WHERE ЧАСЫ BETWEEN 100 AND 130;
Задание №9. Создать запрос в режиме SQL на выборку из таблицы СТУДЕНТЫ сведений о студентах, фамилии которых начинаются, например, на букву «С».
SELECT *
FROM СТУДЕНТЫ
WHERE ФАМИЛИЯ LIKE " С *";
Вывод: В ходе лабораторной работы познакомились с SQL-инструкциями, научились в Access создавать простейшие SQL-запросы с помощью команды SELECT используя операторы IN, BETWEEN, LIKE.
Запрос SQL - это запрос, создаваемый при помощи инструкций SQL. Язык SQL (Structured Query Language) используется при создании запросов, а также для обновления и управления реляционными базами данных, такими как базы данных Microsoft Access.
Когда пользователь создает запрос в режиме конструктора запроса, Microsoft Access автоматически создает эквивалентную инструкцию SQL. Есть ряд запросов, которые можно сделать только в режиме SQL. Опытным программистам часто проще сразу писать выражение на SQL, чем формировать запрос.
Вид запроса в конструкторе:
При сложных расчетах для получения результата приходится последовательно делать несколько запросов. Ясно, что эти действия должны производиться автоматически без участия пользователя.
Для этого используются макросы, состоящие из нескольких последовательно выполняемых команд.
Вычисления в запросах, возможности создания и редактирования формул.
Для полей из указанных в схеме запроса таблиц можно указать любые вычисления.
Чтобы произвести вычисления, в запрос необходимо добавить дополнительные вычисляемые поля, значения которых рассчитываются на базе значений других полей запроса.
Итоговые запросы, группировка, итоговые функции.
Итоговый запрос создается с помощью режима - Сводный запрос.
Можно использовать три таблицы, включая связующую таблицу.
При этом можно с любом месте запроса вызвать контекстное меню (правая кнопка мыши) и выбрать признак «групповые операции».
В бланке запроса появится новая строка Группировка.
Итоговые функции: в поле, по которому мы хотим посчитать итоги, выбрать из списка функцию «Sum» (сумма), чтобы просуммировать все значения выбранных полей. Функция «Подсчет» посчитает количество значений поля. информация редактирование microsoft
Запрос - это обращение к СУБД для выполнения каких-либо операций с данными: выбора части данных из общего объема, добавление вычислимых полей, массовое изменение данных и т. д.
В запросе можно:
Типы запросов:
Запросы используются в качестве источников записей для форм и отчетов. В большинстве своем и в формах, и в отчетах перед выдачей нужно отобрать часть данных по каким-либо условиям и отсортировать данные. Это делается с помощью запросов. Запрос может храниться отдельно или быть привязан к форме или отчету.
В Microsoft Access есть несколько типов запросов.
Запросы в Access являются основным инструментом выборки, обновления и обработки данных в таблицах базы данных. Access в соответствии с концепцией реляционных баз данных для выполнения запросов использует язык структурированных запросов SQL (Structured Query Language). С помощью инструкций языка SQL реализуется любой запрос в Access.
Основным видом запроса является запрос на выборку. Результатом выполнения этого запроса является новая таблица, которая существует до закрытия запроса. Записи формируются путем объединения записей таблиц, на которых построен запрос. Способ объединения записей таблиц указывается при определении их связи в схеме данных или при создании запроса. Условия отбора, сформулированные в запросе, позволяют фильтровать записи, составляющие результат объединения таблиц.
В Access может быть создано несколько видов запроса:
Запросы в Access в режиме конструктора содержат схему данных, отображающую используемые таблицы, и бланк запроса, в котором конструируется структура таблицы запроса и условия выборки записей (рис. 4.1).
С помощью запроса можно выполнить следующие виды обработки данных:
Запросы в Access служат источниками записей для других запросов, форм, отчетов. С помощью запроса можно собрать полные сведения для формирования некоторого документа предметной области из нескольких таблиц, далее использовать его для создания формы - электронного представления этого документа. Если форма или отчет создаются мастером на основе нескольких взаимосвязанных таблиц, то для них в качестве источника записей автоматически формируется запрос.
Для закрепления смотрим видеоурок.
Схема связей таблиц
Описание таблиц
m_category - категории товаров
m_income - приход товаров
m_outcome - расход товаров
m_product - справочник, описание товаров
m_supplier - справочник ; информация о поставщиках
m_unit - справочник; единицы измерения
Для практической проверки примеров приведенных в данном учебном материале, необходимо иметь в наличие следующее программное обеспечение:
Microsoft Access 2003 или новей.
Для перехода в режим редактирования полей таблицы, на верхней панели выберите режим Конструктора:
Для вывода результата запроса SQL, щелкните дважды на названии запроса на панели слева:
Для того чтобы перейти в режим редактирования запроса SQL , на верхней панели выберите режим SQL:
Запрос SQL Q001. Пример запроса SQL для получения только нужных полей в нужной последовательности:
SELECT dt, product_id, amount
Запрос SQL Q002. В этом примере запроса SQL символ звездочки (*) использован для вывода всех столбцов таблицы m_product, иначе говоря, для получения всех полей отношения m_product:
SELECT *
FROM m_product;
Запрос SQL Q003. Инструкция DISTINCT используется для исключения повторяющихся записей и получения множества уникальных записей:
SELECT DISTINCT product_id
Запрос SQL Q004. Инструкция ORDER BY используется для сортировки (упорядочивания) записей по значениям определенного поля. Имя поля указывается за инструкцией ORDER BY:
SELECT *
FROM m_income
Запрос SQL Q005. Инструкция ASC используется как дополнение к инструкции ORDER BY и служит для определения сортировки по возрастанию. Инструкция DESC используется как дополнение к инструкции ORDER BY и служит для определения сортировки по убыванию. В случае, когда ни ASC, ни DESC не указаны, подразумевается наличие ASC (default):
SELECT *
FROM m_income
Запрос SQL Q006. Для отбора необходимых записей из таблицы пользуются различными логическими выражениями, которые выражают условие отбора. Логическое выражение приводится после инструкции WHERE. Пример получения из таблицы m_income всех записей , для которых значение amount больше 200:
SELECT *
FROM m_income
Запрос SQL Q007. Для выражения сложных условий пользуются логическими операциями AND (конъюнкция), OR (дизъюнкция) и NOT (логическое отрицание). Пример получения из таблицы m_outcome всех записей, для которых значение amount равно 20 и значение price больше или равно 10:
Price
Запрос SQL Q008. Для объединения данных двух или более таблиц пользуются инструкциями INNER JOIN, LEFT JOIN, RIGHT JOIN. В следующем примере извлекаются поля dt, product_id, amount, price из таблицы m_income и поле title из таблицы m_product. Запись таблицы m_income соединяется с записью таблицы m_product при равенстве значения m_income.product_id значению m_product.id:
Запрос SQL Q009. В этом запросе SQL нужно обратить внимание на две вещи: 1) искомый текст заключен в одинарные кавычки ("); 2) дата приведена в формате #Месяц/День/Год#, что верно для MS Access. В других системах формат написания даты может быть другим. Пример вывода информации о поступлении молока 12-го июня 2011 года. Обратите внимание на формат даты #6/12/2011#:
SELECT dt, product_id, title, amount, price
Запрос SQL Q010. Инструкция BETWEEN используется для проверки принадлежности некоторому диапазону значений. Пример запроса SQL, выводящий информацию о товарах, поступивших между 1-м и 30-м июнем 2011 года:
SELECT *
FROM m_income INNER JOIN m_product
Запрос Q011. Выводится информация о товарах из таблицы m_product, коды которых есть и в таблице m_income:
SELECT *
FROM m_product
Запрос Q012. Выводится список товаров из таблицы m_product, кодов которых нет в таблице m_outcome:
SELECT *
FROM m_product
Запрос Q013. В этом запросе SQL выводится уникальный список кодов и названий товаров, коды которых есть в таблице m_income, но которых нет в таблице m_outcome:
SELECT DISTINCT product_id, title
Запрос Q014. Выводится из таблицы m_category уникальный список категорий, названия которых начинаются на букву М:
SELECT DISTINCT title
Запрос Q015. Пример выполнения арифметических операций над полями в запросе и переименования полей в запросе (alias). В этом примере для каждой записи о расходе товара подсчитываются сумма расхода = количество*цена и размер прибыли, при предположении, что прибыль составляет 7 процентов от суммы продаж:
Запрос Q016. Проанализировав и упростив арифметические операции , можно увеличить скорость выполнения запроса:
SELECT dt, product_id, amount, price, amount*price AS outcome_sum,
Запрос Q017. При помощи инструкции INNER JOIN можно объединить данные нескольких таблиц. В следующем примере, в зависимости от значения ctgry_id, каждой записи таблицы m_income, сопоставляется название категории из таблицы m_category, к которой принадлежит товар:
SELECT c.title, b.title, dt, amount, price, amount*price AS income_sum
Запрос Q018. Такие функции как SUM - сумма, COUNT - количество, AVG – среднее арифметическое значение, MAX – максимальное значение, MIN – минимальное значение называются агрегатными функциями. Они принимают множество значений , и после их обработки возвращают единственное значение. Пример подсчета суммы произведения полей amount и price при помощи агрегатной функции SUM:
SELECT SUM(amount*price) AS Total_Sum
Запрос Q019. Пример использования нескольких агрегатных функций:
SELECT Sum(amount) AS Amount_Sum, AVG(amount) AS Amount_AVG,
Запрос Q020. В этом примере подсчитана сумма всех товаров с кодом 1, оприходованных в июне 2011 года:
SELECT Sum(amount*price) AS income_sum
Запрос Q021. Следующий запрос SQL вычисляет на какую сумму было продано товаров, имеющих код 4 или 6:
SELECT Sum(amount*price) as outcome_sum
Запрос Q022. Вычисляется на какую сумму было продано 12 июня 2011 года товаров, имеющих код 4 или 6:
SELECT Sum(amount*price) AS outcome_sum
Запрос Q023. Задача такова. Вычислить на какую общую сумму было оприходовано товаров категории "Хлебобулочные изделия".
Для решения этой задачи нужно оперировать тремя таблицами: m_income, m_product и m_category, потому что:
Для решения данной задачи воспользуемся следующим алгоритмом:
Запрос Q024.
Задачу вычисления общей суммы оприходованных товаров категории "Хлебобулочные изделия" решим следующим алгоритмом:
- каждой записи таблицы m_income, в зависимости от значения его product_id, из таблицы m_category, сопоставить название категории ;
- выделить записи, для которых категория равна "Хлебобулочные изделия";
- вычислить сумму прихода = количество*цена.
FROM (m_product AS a INNER JOIN m_income AS b ON a.id=b.product_id)
WHERE c.title="Хлебобулочные изделия";
Запрос Q025. В этом примере вычисляется сколько наименований товаров было израсходовано:
SELECT COUNT(product_id) AS product_cnt
Запрос Q026. Инструкция GROUP BY используется для группировки записей. Обычно записи группируются по значению одного или нескольких полей, и относительно каждой группы применяется какая-либо агрегатная операция. Например, следующий запрос состявляет отчет о продаже товаров. То есть генерируется таблица , в которой будут названия товаров и сумма, на которую они проданы:
SELECT title, SUM(amount*price) AS outcome_sum
Запрос Q027. Отчет о продажах по категориям. То есть генерируется таблица, в которой будут названия категорий товаров, общая сумма, на которую проданы товары данных категорий, и средняя сумма продаж. Функция ROUND использована для округления среднего значения до сотой доли (второй знак после разделителя целой и дробной частей):
SELECT c.title, SUM(amount*price) AS outcome_sum,
Запрос Q028. Вычисляется для каждого товара общее и среднее количество его поступлений и выводит информацию о товарах, общее количество поступления которых не менее 500:
SELECT product_id, SUM(amount) AS amount_sum,
Запрос Q029. В этом запросе вычисляется для каждого товара сумма и среднее значение его поступлений, осуществленных во втором квартале 2011 года. Если общая сумма прихода товара не менее 1000, то отображается информация об этом товаре:
SELECT title, SUM(amount*price) AS income_sum
Запрос Q030.
В некоторых случаях нужно сопоставлять каждой записи некоторой таблицы каждую запись другой таблицы ; что называется декартовым произведением. Таблица, образующаяся в результате такого соединения, называется таблицей Декарта. Например, если некоторая таблица А имеет 100 записей и таблица В имеет 15 записей, то их таблица Декарта будет состоять из 100*15=150 записей. Следующий запрос соединяет каждую запись таблицы m_income с каждой записью таблицы m_outcome:
FROM m_income, m_outcome;
Запрос Q031. Пример группирования записей по двум полям. Следующий запрос SQL вычисляет по каждому поставщику сумму и количество поступивщих от него товаров:
Запрос Q032. Пример группирования записей по двум полям. Следующий запрос вычисляет для каждого поставщика сумму и количество его продуктов, проданных нами:
SELECT supplier_id, product_id, SUM(amount) AS amount_sum,
Запрос Q033. В этом примере два вышеприведенных запроса (q031 и q032) использованы как подзапросы. Результаты этих запросов методом LEFT JOIN объединены в один отчет. Следующий запрос выводит отчет о количестве и сумме поступивщих и реализованных продуктов по каждому поставщику. Следует обратить внимание на то, что если какой-то товар уже поступил, но еще не реализован, то клетка outcome_sum для этой записи будет пустой. , что данный запрос служит только примером использования относительно сложных запросов в качестве подзапроса. Производительность данного запроса SQL при большом объеме данных сомнительна:
SELECT *
FROM
Запрос Q034. В этом примере два вышеприведенных запроса (q031 и q032) использованы как подзапросы. Результаты этих запросов методом RIGTH JOIN объединены в один отчет. Следующий запрос выводит отчет о сумме платежей каждого клиента по использованным им платежным системам и сумме сделанных им инвестиций. Следующий запрос выводит отчет о количестве и сумме поступивщих и реализованных продуктов по каждому поставщику. Следует обратить внимание на то, что если какой-то товар уже реализован, но еще не поступил, то клетка income_sum для этой записи будет пустой. Наличие таких пустых клеток является показателем ошибки в учете продаж, так как до продажы сначала необходимо, чтобы соответствующий товар поступил:
SELECT *
FROM
Запрос Q035. Выводится отчет о сумме доходов и расходов по продуктам. Для этого создается список продуктов по таблицам m_income и m_outcome, затем для каждого продукта из этого списка вычисляется сумма его приходов по таблице m_income и сумма его расходов по таблице m_outcome:
SELECT product_id, SUM(in_amount) AS income_amount,
Запрос Q036. Функция EXISTS возвращает значение TRUE, если переданное ей множество содержит элементы. Функция EXISTS возвращает значение FALSE , если переданное ей множество пустое, то есть не содержит элементов. Следующий запрос выводит коды товаров, которые содержатся как в таблице m_income, так и в таблице m_outcome:
SELECT DISTINCT product_id
Запрос Q037. Выводятся коды товаров, которые содержатся как в таблице m_income, так и в таблице m_outcome:
SELECT DISTINCT product_id
Запрос Q038. Выводятся коды товаров, которые содержатся как в таблице m_income, но не содержатся в таблице m_outcome:
SELECT DISTINCT product_id
Запрос Q039. Выводится список товаров, сумма продаж которых максимальная. Алгоритм таков. Для каждого товара вычисляется сумма его продаж. Затем, определяется максимум этих сумм. Затем, для каждого товара снова вычисляется сумма его продаж, и выводятся код и сумма продаж товаров , сумма продаж которых равна максимальной:
SELECT product_id, SUM(amount*price) AS amount_sum
Запрос Q040. Зарезервированное слово IIF (условный оператор) используется для оценки логического выражения и выполнения того или иного действия в зависимости от результата (TRUE или FALSE). В следующем примере поставка товара считается «малой», если количество меньше 500. В противном случае, то есть количество поступления больше или равно 500, поставка считается «большой»:
SELECT dt, product_id, amount,
Запрос SQL Q041. В случае, когда оператор IIF используется несколько раз, удобнее заменить его оператором SWITCH. Оператор SWITCH (оператор множественного выбора) используется для оценки логического выражения и выполнения того или иного действия в зависимости от результата. В следующем примере поставленная партия считается «малой», если количество товара в партии меньше 500. В противном случае, то есть если количество товара больше или равно 500, партия считается «большой»:
SELECT dt, product_id, amount,
Запрос Q042.
В следующем запросе если количество товара в поступившей партии меньше 300, то партия считается «малой». В противном случае, то есть если условие amount SELECT dt, product_id, amount,
IIF(amount IIF(amount FROM m_income;
Запрос SQL Q043.
В следующем запросе если количество товара в поступившей партии меньше 300, то партия считается «малой». В противном случае, то есть если условие amount SELECT dt, product_id, amount,
SWITCH(amount amount amount>=1000,"большая") AS mark
FROM m_income;
Запрос SQL Q044. В следующем запросе продажи разделяются на три группы: малые (до 150), средние (от150 до 300), большие (300 и более). Далее, для каждой группы вычисляется итоговая сумма:
SELECT Category, SUM(outcome_sum) AS Ctgry_Total
Запрос SQL Q045.
Функция DateAdd используется для прибавления дней, месяцев или лет к данной дате и получения новой даты. Следующий запрос:
1) к дате из поля dt прибавляет 30 дней и отображает новую дату в поле dt_plus_30d;
2) к дате из поля dt прибавляет 1 месяц и отображает новую дату в поле dt_plus_1m:
SELECT dt, dateadd("d",30,dt) AS dt_plus_30d, dateadd("m",1,dt) AS dt_plus_1m
Запрос SQL Q046. Функция DateDiff предназначена для вычисления разницы между двумя датами в различных единицах (днях, месяцах или годах). Следующий запрос вычисляет разницу между датой в поле dt и текущей датой в днях , месяцах и годах:
SELECT dt, DateDiff("d",dt,Date()) AS last_day,
Запрос SQL Q047. Вычисляются количество дней со дня поступления товара (таблица m_income) до текущей даты с помощью функции DateDiff и сопоставляется срок годности (таблица m_product):
Запрос SQL Q048. Вычисляются количество дней со дня поступления товара до текущей даты, затем проверяется превышает ли это количество срок годности:
SELECT a.id, product_id, dt, lifedays,
Запрос SQL Q049. Вычисляются количество месяцев со дня поступления товара до текущей даты. В столбце month_last1 вычисляется абсолютное количество месяцев, в столбце month_last2 вычисляется количество полных месяцев:
SELECT dt, DateDiff("m",dt,Date()) AS month_last1,
Запрос SQL Q050. Выводится поквартальный отчет о количестве и сумме оприходованных товаров за 2011 год:
SELECT kvartal, SUM(outcome_sum) AS Total
Запрос Q051. Следующий запрос помогает выяснить , удалось ли пользователям ввести в систему информацию о расходе товара на сумму большую, чем сумма прихода товара:
SELECT product_id, SUM(in_sum) AS income_sum, SUM(out_sum) AS outcome_sum
SELECT COUNT(*) as N, b.id, b.product_id, b.amount, b.price
Запрос Q053. Выводится пятерка лидеров среди продуктов по сумме продаж. Вывод первых пяти записей осуществляется с помощью инструкции TOP:
SELECT TOP 5, product_id, sum(amount*price) AS summa
Запрос Q054. Выводится пятерка лидеров среди продуктов по сумме продаж, и нумерует строки в результате:
SELECT COUNT(*) AS N, b.product_id, b.summa
Запрос Q055. Следующий SQL-запрос показывает использование математических функций COS, SIN, TAN, SQRT, ^ и ABS в MS Access SQL:
SELECT (select count(*) from m_income) as N, 3.1415926 as pi, k,
UPDATE m_income SET price = price*1.1
Запрос U002. Следующий SQL-запрос на обновление увеличивает в таблице m_income на 22 единицы количество всех товаров , названия которых начинаются со слова "Масло":
UPDATE m_income SET amount = amount+22
Запрос U003. Следующий SQL-запрос на изменение в таблице m_outcome снижает на 2 процента цены на все товары, производителем которых является ООО "Сладкое":
UPDATE m_outcome SET price = price*0.98
Каждый веб-разработчик должен знать SQL, чтобы писать запросы к базам данных. И, хотя, phpMyAdmin никто не отменял, зачастую необходимо испачкать руки, чтобы написать низкоуровневый SQL.
Именно поэтому мы подготовили краткий экскурс по основам SQL. Начнем же!
Для создания таблиц предназначена инструкция CREATE TABLE . В качестве аргументов должно быть задано название столбцов, а также их типы данных.
Создадим простую таблицу по имени month . Она состоит из 3 колонок:
Вот как будет выглядеть соответствующий SQL запрос:
CREATE TABLE months (id int, name varchar(10), days int);
Также при создании таблиц целесообразно добавить первичный ключ для одной из колонок. Это позволит держать записи уникальными и ускорит запросы на выборку. Пусть в нашем случае уникальным будет название месяца (столбец name )
CREATE TABLE months (id int, name varchar(10), days int, PRIMARY KEY (name));
Тип данных | Описание |
---|---|
DATE | Значения даты |
DATETIME | Значения даты и времени с точностью до минты |
TIME | Значения времени |
Теперь давайте заполнять нашу таблицу months полезной информацией. Добавление записей в таблицу производится через инструкцию INSERT . Есть два способа записи этой инструкции.
Первый способ не указать имена столбцов, куда будут вставлены данные, а указать только значения.
Этот способ записи прост, но небезопасен, поскольку нет гарантии, что по мере расширения проекта и редактировании таблицы, столбцы будут располагаться в том же порядке, что и ранее. Безопасный (и в тоже время более громоздкий) способ записи инструкции INSERT требует указания как значений, так и порядка следования столбцов:
Здесь первое значение в списке VALUES соответствует первому указанному имени столбца и т.д.
Инструкция SELECT - наш лучший друг, когда мы хотим получить данные из базы данных. Она используется очень часто, так что отнеситесь к этому разделу очень внимательно.
Самый простое использование инструкции SELECT - запрос, который возвращает все столбцы и строки из таблицы (например, таблицы по имени characters ):
SELECT * FROM "characters"
Символ звездочка (*) означает, что мы хотим получить данные из всех столбцов. Так базы данных SQL обычно состоят из более чем одной таблицы, то требуется обязательно указывать ключевое слово FROM , следом за которым через пробел должно следовать название таблицы.
Иногда мы не хотим получить данные не из всех столбцов в таблице. Для этого, вместо звездочки (*) мы должны через запятую записать имена желаемых столбцов.
SELECT id, name FROM month
Кроме того, во многих случаях мы хотим, чтобы полученные результаты были отсортированы в определенном порядке. В SQL мы делаем это с помощью ORDER BY . Он может принимать опциональный модификатор – ASC (по-умолчанию) сортирующий по возрастанию или DESC , сортирующий по убыванию:
SELECT id, name FROM month ORDER BY name DESC
При использовании ORDER BY убедитесь, что оно будет последним в инструкции SELECT . В противном случае будет выдано сообщение об ошибке.
Вы узнали, как выбрать из базы данных с помощью SQL запроса строго определенные столбцы, но что если нам нужно получить еще и определенные строки? На помощь здесь приходит условие WHERE , позволяющее нам фильтровать данные в зависимости от условия.
В этом запросе мы выбираем только те месяцы из таблицы month , в которых больше 30 дней с помощью оператора больше (>).
SELECT id, name FROM month WHERE days > 30
Ранее мы использовали фильтрацию данных с использованием одного критерия. Для более сложной фильтрации данных можно использовать операторы AND и OR и операторов сравнения (=,<,>,<=,>=,<>).
Здесь мы имеем таблицу, содержащую четыре самых продаваемых альбомов всех времен. Давайте выберем те из них, которые классифицируются как рок и у которых менее 50 миллионов проданных копий. Это можно легко сделать путем размещения оператора AND между этими двумя условиями.
WHERE также поддерживает несколько специальных команд, позволяя быстро проверять наиболее часто используемые запросы. Вот они:
Например, если мы хотим выбрать альбомы с поп и соул музыкой, мы можем использовать IN("value1","value2") .
SELECT * FROM albums WHERE genre IN ("pop","soul");
Если мы хотим получить все альбомы, изданные между 1975 и 1985годами, мы должны записать:
SELECT * FROM albums WHERE released BETWEEN 1975 AND 1985;
SQL напичкан с функциями, которые делают разные полезные вещи. Вот некоторые из наиболее часто используемых:
Чтобы получить самый последний год в нашей таблице мы должны записать такой SQL запрос:
SELECT MAX(released) FROM albums;
В предыдущем пункте мы научились делать простые расчеты с данными. Если мы хотим использовать результат от этих расчетов, нам не обойтись без вложенных запросов. Допустим, мы хотим вывести artist , album и release year для старейшего альбома в таблице.
Мы знаем, как получить эти конкретные столбцы:
SELECT artist, album, released FROM albums;
Мы также знаем, как получить самый ранний год:
SELECT MIN(released) FROM album;
Все, что нужно сейчас, - это объединить два запроса с помощью WHERE:
SELECT artist,album,released FROM albums WHERE released = (SELECT MIN(released) FROM albums);
В более сложных базах данных существует несколько таблиц, связанных друг с другом. Например, ниже представлены две таблицы о видеоиграх (video_games ) и разработчиков видеоигр (game_developers ).
В таблице video_games есть колонка разработчик (developer_id ), но в ней содержится целое число, а не имя разработчика. Это число представляет собой идентификатор (id ) соответствующего разработчика из таблицы разработчиков игр (game_developers ), связывая логически два списка, что позволяет нам использовать информацию, хранящуюся в них обоих одновременно.
Если мы хотим создать запрос, который возвращает все, что нужно знать об играх, мы можем использовать INNER JOIN для связи колонок из обеих таблиц.
SELECT video_games.name, video_games.genre, game_developers.name, game_developers.country FROM video_games INNER JOIN game_developers ON video_games.developer_id = game_developers.id;
Это самый простой и наиболее распространенный тип JOIN . Есть несколько других вариантов, но они применимы к менее частым случаям.
Если вы посмотрите на предыдущий пример, то вы заметите, что существуют две колонки называемые name . Это сбивает с толку, так что давайте установим псевдоним одного из повторяющихся столбцов, например, name из таблицы game_developers будет называться developer .
Мы также можем сократить запрос задав псевдонимы имен таблиц: video_games назовем games , game_developers - devs :
SELECT games.name, games.genre, devs.name AS developer, devs.country FROM video_games AS games INNER JOIN game_developers AS devs ON games.developer_id = devs.id;
Часто мы должны изменить данные в некоторых строках. В SQL это делается с помощью инструкции UPDATE . Инструкция UPDATE состоит из:
Ниже приведена таблица tv_series с сериалами с их рейтингом. Однако, в таблицу закралась маленькая ошибка: хотя сериал Игра престолов и описывается как комедия, он на самом деле ей не является. Давайте исправим это!
Данные таблицы tv_series UPDATE tv_series SET genre = "драма" WHERE id = 2;Удаление строки таблицы с помощью SQL - это очень простой процесс. Все, что вам нужно, - это выбрать таблицу и строку, которую нужно удалить. Давайте удалим из предыдущего примера последнюю строку в таблице tv_series . Делается это с помощью инструкции >DELETE
DELETE FROM tv_series WHERE id = 4
Будьте осторожными при написании инструкции DELETE и убедитесь, что условие WHERE присутствует, иначе все строки таблицы будут удалены!
Если мы хотим, чтобы удалить все строки, но оставить саму таблицу, то воспользуйтесь командой TRUNCATE:
TRUNCATE TABLE table_name;
В случае, когда мы на самом деле хотим, чтобы удалить и данные, и саму таблицу, то нам пригодится команда DROP:
DROP TABLE table_name;
Будьте очень осторожны с этими командами. Их нельзя отменить!/p>
На этом мы завершаем наш учебник по SQL! Мы многое о чем не рассказали, но то, что вы уже знаете, должно быть достаточно, чтобы дать вам несколько практических навыков в вашей веб-карьере.