4 какие функции используются в языке sql. Строковые функции SQL – примеры использования. Синтаксис оператора SELECT и порядок его обработки
В этой статье вы можете рассмотреть виды функций в языке SQL. Функции могут быть агрегатными или скалярными. Начнем…
COUNT()
COUNT() - функция возвращающая количество записей (строк) таблицы. Запись функции с указанием столбца вернет количество записей конкретного столбца за исключением NULL записей. Синтаксис функции:
COUNT(column_name);
Запись функции с указанием маски «*» вернет количество всех записей в таблице. Синтаксис:
COUNT(*);
Рассмотрим примеры. Имеется следующая таблица Universities:
ID | UniversityName | Students | Faculties | Professores | Location | Site |
1 | Perm State National Research University | 12400 | 12 | 1229 | Perm | psu.ru |
2 | Saint Petersburg State University | 21300 | 24 | 13126 | Saint-Petersburg | spbu.ru |
3 | Novosibirsk State University | 7200 | 13 | 1527 | Novosibirsk | nsu.ru |
4 | Moscow State University | 35100 | 39 | 14358 | Moscow | msu.ru |
5 | Higher School of Economics | 20335 | 12 | 1615 | Moscow | hse.ru |
6 | Ural Federal University | 57000 | 19 | 5640 | Yekaterinburg | urfu.ru |
7 | National Research Nuclear University | 8600 | 10 | 936 | Moscow | mephi.ru |
Пример 1. Вывести число записей таблицы, используя функцию COUNT:
SELECT COUNT(*)
FROM Universities; // выведит 7
Пример 2. Найти количество университетов расположенных в Москве, используя функцию COUNT:
SELECT COUNT(*) FROM Universities WHERE Location = "Moscow"; // выведит 3
AVG()
AVG() - функция возвращающая среднее значение столбца. Данная функция применима только для числовых столбцов. Синтаксис:
AVG(column_name);
Рассмотрим пример. Возьмем нашу таблицу Universities. Пример. Используя функцию AVG найти среднее число студентов (Students) всех университетов:
SELECT AVG(Students) FROM Universities; // выведит 23133
MIN()
MIN() - функция возвращающая минимальное значение столбца. Функция имеет следующий синтаксис:
MIN(column_name);
Рассмотрим пример. Возьмем нашу таблицу Universities. Пример. Используя функцию MIN найти минимальное значение столбца Professores:
SELECT MIN(Professores) FROM Universities; // выведит 936
MAX()
MAX() - функция возвращающая максимальное значение столбца таблицы. Функция имеет следующий синтаксис:
MAX(column_name);
Рассмотрим пример. Возьмем нашу таблицу Universities. Пример. Используя функцию MAX найти максимальное значение колонки Students:
SELECT MAX(Students) FROM Univerities; // выведит 57000
SUM()
SUM() - функция, возвращающая сумму значений столбца таблицы. Используется только для числовых столбцов. Синтаксис функции:
SUM ( expression);
Параметр ALL - является параметром по умолчанию. Считается сумма всех строк. При указании параметра DISTINCT - происходит подсчет только уникальных значений.
Рассмотрим примеры. Возьмем нашу таблицу Universities. Пример 1. Используя функцию SUM найти количество студентов (Students) всех университетов в таблице:
SELECT SUM(Students) FROM Universities; // выведит 161935
Пример 2. Используя функцию SUM произвести подсчет уникальных значений столбца Faculties:
SELECT SUM(DISTINCT Faculties) FROM Universities; // выведит 117 (в столбце Faculties присутствует 2 одинаковых записи, под ID 1 и 5. Значение столбца под ID 5 не суммируется.)
ROUND()
ROUND() - функция для округления десятичных чисел. Работает только с числовыми столбцами или произвольными вещественными числами. Синтаксис функции:
ROUND(expression, length);
expression - название столбца или столбцов, а так же вещественное число. length - указывает точность округления для числа.
Рассмотрим пример. Возьмём таблицу Planets:
ID | PlanetName | Radius | SunSeason | OpeningYear | HavingRings | Opener |
1 | Mars | 3396 | 687.58 | 1659 | No | Christiaan Huygens |
2 | Saturn | 60268 | 10759.22 | - | Yes | - |
3 | Neptune | 24764 | 60190.91 | 1846 | Yes | John Couch Adams |
4 | Mercury | 2439 | 115.88 | 1631 | No | Nicolaus Copernicus |
5 | Venus | 6051 | 243.56 | 1610 | No | Galileo Galilei |
Пример. Используя функцию ROUND округлить столбец SunSeason с точностью до одного знака после запятой:
SELECT ROUND(SunSeason, 1) FROM Planets;
UCASE()
UCASE() - функция, возвращающая значения столбца или столбцов в верхнем регистре букв. В СУБД MS SQL Server аналогом UCASE() является функция UPPER с тем же синтаксисом:
UCASE(column_name)
Рассмотрим пример. Возьмём нашу таблицу Planets. Пример. Вывести названия планет в верхнем регистре, у которых нет колец, используя функцию UCASE:
SELECT UCASE(PlanetName) FROM Planets WHERE HavingRings = "No";
LCASE()
LCASE() - функция, возвращающая значения столбца или столбцов в нижнем регистре букв. В СУБД MS SQL Server аналогом оператора SQL LCASE() является функция LOWER с тем же синтаксисом:
LCASE(column_name);
Рассмотрим пример. Возьмём нашу таблицу Planets. Пример. С помощью функции LCASE вывести в нижнем регистре названия планет, у которых есть кольца:
SELECT LCASE(PlanetName) FROM Planets WHERE HavingRings = "Yes";
LEN()
LEN() - функция, возвращающая длину значения в поле записи. Функция исключает из подсчета конечные пробелы. Синтаксис:
LEN(column_name);
Рассмотрим пример. Возьмём нашу таблицу Planets. Пример. Вывести первооткрывателя планеты (Opener) и длину названия открытым им планеты, с помощью функции LEN:
SELECT Opener, LEN(PlanetName) FROM Planets;
MID()
MID() - функция, выводящая определенное количество символов текстового поля таблицы. Синтаксис функции:
MID(colunm_name,start [,length]);
Параметр start задает позицию начального символа. Параметр length устанавливает количество символов для вывода начиная с позиции, указанной в параметре start.
Рассмотрим пример. Возьмём нашу старую таблицу Universities. Пример. Вывести первые 3 символа названия города (Location) с помощью функции MID:
SELECT MID(Location, 1, 3) FROM Universities;
NOW()
NOW() - функция, возвращающая системное время и дату. Синтаксис функции:
Рассмотрим пример. Возьмём нашу старую таблицу Universities. Пример. Вывести сколько на текущий момент студентов обучается в каждом университете. используя функцию NOW:
SELECT UniversityName, Students, NOW() AS CurDate FROM Universities;
Для закрепления материала выполните пример каждой функцию самостоятельно. Если будут вопросы пишите в комментариях и я обязательно на них отвечу.
Основные команды SQL, которые должен знать каждый программист
Язык SQL или Structured Query Language (язык структурированных запросов) предназначен для управления данными в системе реляционных баз данных (RDBMS). В этой статье будет рассказано о часто используемых командах SQL, с которыми должен быть знаком каждый программист. Этот материал идеально подойдёт для тех, кто хочет освежить свои знания об SQL перед собеседованием на работу. Для этого разберите приведённые в статье примеры и вспомните, что проходили на парах по базам данных.
Обратите внимание, что в некоторых системах баз данных требуется указывать точку с запятой в конце каждого оператора. Точка с запятой является стандартным указателем на конец каждого оператора в SQL. В примерах используется MySQL, поэтому точка с запятой требуется.
Настройка базы данных для примеров
Создайте базу данных для демонстрации работы команд. Для работы вам понадобится скачать два файла: DLL.sql и InsertStatements.sql . После этого откройте терминал и войдите в консоль MySQL с помощью следующей команды (статья предполагает, что MySQL уже установлен в системе):
Mysql -u root -p
Затем введите пароль.
Выполните следующую команду. Назовём базу данных «university»:
CREATE DATABASE university;
USE university;
SOURCE Может понадобиться создать ограничения для определённых столбцов в таблице. При создании таблицы можно задать следующие ограничения: Можно задать больше одного первичного ключа. В этом случае получится составной первичный ключ. Создайте таблицу «instructor»: CREATE TABLE instructor (ID CHAR(5),
name VARCHAR(20) NOT NULL,
dept_name VARCHAR(20),
salary NUMERIC(8,2),
PRIMARY KEY (ID),
FOREIGN KEY (dept_name) REFERENCES department(dept_name));
Можно просмотреть различные сведения (тип значений, является ключом или нет) о столбцах таблицы следующей командой: DESCRIBE При добавлении данных в каждый столбец таблицы не требуется указывать названия столбцов. INSERT INTO SELECT используется для получения данных из определённой таблицы: SELECT Следующей командой можно вывести все данные из таблицы: SELECT * FROM В столбцах таблицы могут содержаться повторяющиеся данные. Используйте SELECT DISTINCT для получения только неповторяющихся данных. SELECT DISTINCT Можно использовать ключевое слово WHERE в SELECT для указания условий в запросе: SELECT В запросе можно задавать следующие условия: Попробуйте выполнить следующие команды. Обратите внимание на условия, заданные в WHERE: SELECT * FROM course WHERE dept_name=’Comp. Sci.’;
SELECT * FROM course WHERE credits>3;
SELECT * FROM course WHERE dept_name="Comp. Sci." AND credits>3;
Оператор GROUP BY часто используется с агрегатными функциями, такими как COUNT , MAX , MIN , SUM и AVG , для группировки выходных значений. SELECT Выведем количество курсов для каждого факультета: SELECT COUNT(course_id), dept_name
FROM course
GROUP BY dept_name;
Ключевое слово HAVING было добавлено в SQL потому, что WHERE не может быть использовано для работы с агрегатными функциями. SELECT Выведем список факультетов, у которых более одного курса: SELECT COUNT(course_id), dept_name
FROM course
GROUP BY dept_name
HAVING COUNT(course_id)>1;
ORDER BY используется для сортировки результатов запроса по убыванию или возрастанию. ORDER BY отсортирует по возрастанию, если не будет указан способ сортировки ASC или DESC . SELECT Выведем список курсов по возрастанию и убыванию количества кредитов: SELECT * FROM course ORDER BY credits;
SELECT * FROM course ORDER BY credits DESC;
BETWEEN используется для выбора значений данных из определённого промежутка. Могут быть использованы числовые и текстовые значения, а также даты. SELECT Выведем список инструкторов, чья зарплата больше 50 000, но меньше 100 000: SELECT * FROM instructor
WHERE salary BETWEEN 50000 AND 100000;
Оператор LIKE используется в WHERE , чтобы задать шаблон поиска похожего значения. Есть два свободных оператора, которые используются в LIKE: Выведем список курсов, в имени которых содержится «to» , и список курсов, название которых начинается с «CS-»: SELECT * FROM course WHERE title LIKE ‘%to%’;
SELECT * FROM course WHERE course_id LIKE "CS-___";
С помощью IN можно указать несколько значений для оператора WHERE: SELECT Выведем список студентов с направлений Comp. Sci., Physics и Elec. Eng.: SELECT * FROM student
WHERE dept_name IN (‘Comp. Sci.’, ‘Physics’, ‘Elec. Eng.’);
JOIN используется для связи двух или более таблиц с помощью общих атрибутов внутри них. На изображении ниже показаны различные способы объединения в SQL. Обратите внимание на разницу между левым внешним объединением и правым внешним объединением: SELECT Выведем список всех курсов и соответствующую информацию о факультетах: SELECT * FROM course
JOIN department
ON course.dept_name=department.dept_name;
Выведем список всех обязательных курсов и детали о них: SELECT prereq.course_id, title, dept_name, credits, prereq_id
FROM prereq
LEFT OUTER JOIN course
ON prereq.course_id=course.course_id;
Выведем список всех курсов вне зависимости от того, обязательны они или нет: SELECT course.course_id, title, dept_name, credits, prereq_id
FROM prereq
RIGHT OUTER JOIN course
ON prereq.course_id=course.course_id;
View - это виртуальная таблица SQL, созданная в результате выполнения выражения. Она содержит строки и столбцы и очень похожа на обычную SQL-таблицу. View всегда показывает самую свежую информацию из базы данных. Создадим view , состоящую из курсов с 3 кредитами: Эти функции используются для получения совокупного результата, относящегося к рассматриваемым данным. Ниже приведены общеупотребительные агрегированные функции: Вложенные подзапросы - это SQL-запросы, которые включают выражения SELECT , FROM и WHERE , вложенные в другой запрос. Найдём курсы, которые преподавались осенью 2009 и весной 2010 годов: SELECT DISTINCT course_id
FROM section
WHERE semester = ‘Fall’ AND year= 2009 AND course_id IN (SELECT course_id
FROM section
WHERE semester = ‘Spring’ AND year= 2010);
Функции - это особый тип команды в наборе команд SQL, а каждый диалект имеет свою реализацию набора команд. В результате можно сказать, что функции - это команды, состоящие из одного слова и возвращающие одиночное значение. Значение функции может зависеть от входных параметров, как, например, в случае функции, вычисляющей среднее значение в списке значений в базе данных. Однако многие функции не используют никаких входных параметров например, функция, возвращающая текущее системное время - CURRENTJ1ME. Стандарт ANSI поддерживает несколько полезных функций. В этой главе приводится описание этих функций, а также подробное описание и примеры по каждой из платформ. Кроме того, у каждой платформы есть длинный перечень своих собственных, внутренних функций, которые выходят за пределы стандарта SQL. В этой главе приводятся параметры и описания всех внутренних функций каждой из платформ. Кроме того, большинство платформ имеют возможность создания пользовательских функций. За дополнительной информацией о пользовательских функциях обращайтесь к разделу «Инструкции CREATE/ALTER FUNCTION/PROCEDURE» Существуют разные способы классификации функций. В следующих подразделах описываются важные различия, позволяющие понять, как работают функции. Функции могут быть детерминированные и недетерминированные. Детерминированная функция всегда возвращает один и тот же результат при одном и том же наборе входных значений. Недетерминированные функции могут возвращать разные результаты при разных вызовах, даже если им передаются одинаковые входные значения. Почему так важно, чтобы при одинаковых входных параметрах получались одинаковые результаты? Это важно потому, что это определяет способ использования функций в представлениях, пользовательских функциях и хранимых процедурах. Ограничения на разных платформах могут быть разными, но иногда в этих объектах можно использовать только детерминированные функции. Например, SQL Server может создавать индекс по выражению в столбце, если только это выражение не содержит недетерминированных функций. Правила и ограничения на разных платформах разные, потому обращайтесь при использовании функций к документации производителей. Еще один способ классификации функций - по их возможности работы только с одной строкой, с коллекцией значений или с наборами строк. Агрегатные функции работают с коллекцией значений и возвращают одно суммарное значение. Скалярные функции возвращают одно значение, зависящее от скалярных входных аргументов. Некоторые скалярные функции, например CURRENTJTIME, не требуют никаких аргументов. Оконные функции можно считать сходными с агрегатными функциями в том, что они могут работать сразу с несколькими строками. Различие в том, каким образом указываются эти строки. Агрегатные функции работают с наборами строк, указанными в предложении GROUP BY. В случае оконных функций набор строк указывается при каждом вызове функции и разные вызовы функции в пределах одного запроса могут работать с разными наборами строк. Мы продолжаем изучение языка запросов SQL, и сегодня мы с Вами будем разговаривать о строковых функциях SQL
. Мы рассмотрим основные и часто используемые строковые функции, такие как: LOWER, LTRIM, REPLACE
и другие, все рассматривать мы будем, конечно же, на примерах. SELECT name || surname AS FIO FROM table
Или чтобы отделить пробелом введите т.е. две вертикальные черты объединяют два столбца в один, а чтобы отделить их пробелом я поставил между ними пробел (можно использовать любой символ, например тире или двоеточие
) в апострофах и объединил также двумя вертикальными чертами (в Transact-SQL вместо двух вертикальных черточек используется знак +
). Дальше идет также очень полезная функция, INITCAP
– которая возвращает значение в строке, в которой каждое слово начинается с заглавной буквы, а продолжается маленькими. Это нужно для того, если у Вас в той или иной колонке не соблюдают правила заполнения и для того чтобы вывести все это дело в красивом виде можно использовать данную функцию, например, у Вас в таблице записи в колонке name следующего вида: ИВАН иванов или петр петров, Вы применяете данную функцию. SELECT INITCAP (name) AS FIO FROM table
И у Вас получится вот так. Похожая функция, только возвращает все символы с заглавной буквы, это UPPER
. SELECT UPPER (name) AS FIO FROM table
Сразу рассмотрим обратную функцию. RPAD
– действие и синтаксис тот же что и у LPAD, только дополняются символы справа (в LPAD слева
). SELECT RPAD (name, 20, "-") AS name FROM table
Далее идет тоже в некоторых случаях полезная функция, LTRIM
– эта функция удаляет крайние левые символы, которые Вы укажите. Например, у Вас в базе есть колонка «город», в которой город указан в виде «г.Москва», а также есть города которые указанны в виде просто «Москва». Но Вам нужно вывести отчет только в виде «Москва» без «г.», но как это сделать, если есть и такие и такие? Вы просто указываете своего рода шаблон «г.» и если крайние левые символы начинаются с «г.», то эти символы просто не будут выводиться. SELECT LTRIM (city, "г.") AS gorod FROM table
Данная функция просматривает символы слева, если символов по шаблону нет в начале строки, то она возвращает исходное значение ячейки, а если есть, то удаляет их. Также давайте сразу рассмотрим обратную функцию. RTRIM
– то же самое что и LTRIM только символы ищутся справа. Примечание!
В Transact-SQL функции RTRIM и LTRIM удаляют пробелы справа и слева соответственно.
Теперь рассмотрим такую интересную функцию как REPLACE
– она возвращает строку, в которой все совпадения символов, заменяются на Ваши символы, которые Вы укажите. Для чего ее можно использовать, например, у Вас в базе есть колонки, в которых встречаются некие разделительные символы, допустим «/». Например, Иван/Иванов, а Вам хотелось бы вывести Иван-Иванов, то напишите SELECT REPLACE (name, "/", "-") FROM table
и у Вас произойдет замена символов. Данная функция заменяет только полное совпадение символов, если например Вы укажите «—» т.е. три тире она и будет искать только три тире, а каждое отдельное тире заменять не будет, в отличие от следующей функции. TRANSLATE
– строковая функция, которая заменяет все символы в строке, на те символы, которые Вы укажите. Исходя из названия функции, можно догадаться, что это полный перевод строки. Отличие данной функции от REPLACE в том, что она заменяет каждый символ, который Вы укажите, т.е. у Вас есть три символа, допустим абв и с помощью TRANSLATE Вы его можете заменить на abc таким образом у Вас а=a, б=b, в=c и по такому принципу будут заменяться все совпадения символов. А если Вы заменяли с помощью REPLACE, то у Вас искалось только полное совпадение символов абв расположенных подряд. SUBSTR
– данная функция, возвращает только тот диапазон символов, который Вы укажите. Другими словами, допустим, строка из 10 символов, а Вам все десять не нужны, а допустим, нужны только 3-8 (с третьего по восьмой
). С помощью данной функции Вы легко можете это сделать. Например, у Вас в базе есть какой-нибудь идентификатор, фиксированной длинны (типа: AA-BB-55-66-CC) и каждая комбинация символов что-то означает. И в один прекрасный момент Вам сказали вывести только 2 и 3 комбинацию символов, для этого вы пишите запрос следующего вида. SELECT SUBSTR (ident, "4", "8") FROM table
т.е. мы выводим все символы, начиная с 4 и заканчивая 8, и после этого запроса у Вас выведется вот это: Следующая функция также может пригодиться, это LENGTH
– которая просто на всего считает количество символов в строке. Например, Вам нужно узнать, сколько символов в каждой ячейки столбца допустим «name», таблица следующего вида. после этого запроса Вы получите вот это. Вот мы с Вами и рассмотрели основные строковые функции SQL. В следующих статьях мы продолжим изучение SQL. Будем учиться подводить итоги. Нет, это ещё не итоги изучения SQL, а итоги значений столбцов таблиц
базы данных. Агрегатные функции SQL действуют в отношении значений столбца с целью получения
единого результирующего значения. Наиболее часто применяются агрегатные функции SQL SUM, MIN, MAX,
AVG и COUNT. Следует различать
два случая применения агрегатных функций. Первый: агрегатные функции используются сами по себе и возвращают
одно результирующее значение. Второй: агрегатные функции используются с оператором SQL GROUP BY, то есть
с группировкой по полям (столбцам) для получения результирующих значений в каждой группе. Рассмотрим сначала случаи использования агрегатных
функций без группировки. Функция SQL SUM возвращает сумму значений столбца таблицы базы данных. Она может применяться только
к столбцам, значениями которых являются числа. Запросы SQL для получения результирующей суммы
начинаются так: SELECT SUM
(ИМЯ_СТОЛБЦА) ... После этого выражения следует FROM (ИМЯ_ТАБЛИЦЫ), а далее с помощью конструкции WHERE может быть
задано условие. Кроме того, перед именем столбца может быть указано DISTINCT, и это означает, что
учитываться будут только уникальные значения. По умолчанию же учитываются все значения (для этого
можно особо указать не DISTINCT, а ALL, но слово ALL не является обязательным). Пример 1.
Есть база данных фирмы с данными о её подразделениях
и сотрудниках. Таблица Staff помимо всего имеет столбец с данными о заработной плате сотрудников. Выборка
из таблицы имеет следующий вид (для увеличения картинки щёлкнуть по ней левой кнопкой мыши): Для получения суммы размеров всех заработных плат используем следующий запрос: SELECT SUM
(Salary)
FROM
Staff
Этот запрос вернёт значение 287664,63. А теперь . В упражнениях уже начинаем
усложнять задания, приближая их к тем, что встречаются на практике. Функция SQL MIN также действует в отношении столбцов, значениями которых являются числа и возвращает
минимальное среди всех значений столбца. Эта функция имеет синтаксис аналогичный синтаксису функции SUM. Пример 3.
База данных и таблица -
те же, что и в примере 1. Требуется узнать минимальную заработную плату сотрудников отдела с номером 42.
Для этого пишем следующий запрос: Запрос вернёт значение 10505,90. И вновь упражнение для самостоятельного решения
. В этом и некоторых
других упражнениях потребуется уже не только таблица Staff, но и таблица Org, содержащая данные о
подразделениях фирмы: Пример 4.
К таблице Staff добавляется таблица Org, содержащая данные
о подразделениях фирмы. Вывести минимальное количество лет, проработанных одним сотрудником в отделе,
расположенном в Бостоне. Аналогично работает и имеет аналогичный синтаксис функция SQL MAX, которая применяется, когда
требуется определить максимальное значение среди всех значений столбца. Пример 5.
Требуется узнать максимальную заработную плату сотрудников отдела с номером 42.
Для этого пишем следующий запрос: Запрос вернёт значение 18352,80 Пришло время упражнения для самостоятельного решения
. Пример 6.
Вновь работаем с двумя таблицами - Staff и Org.
Вывести название отдела и максимальное значение комиссионных, получаемых одним сотрудником в отделе,
относящемуся к группе отделов (Division) Eastern. Использовать JOIN (соединение таблиц)
. Указанное в отношении синтаксиса для предыдущих описанных функций верно и в отношении функции SQL AVG.
Эта функция возвращает среднее значение среди всех значений столбца. Пример 7.
База данных и таблица -
те же, что и в предыдущих примерах. Пусть требуется узнать средний трудовой стаж сотрудников отдела с номером 42.
Для этого пишем следующий запрос: Результатом будет значение 6,33 Пример 8.
Работаем с одной таблицей - Staff.
Вывести среднюю зарплату сотрудников со стажем от 4 до 6 лет. Функция SQL COUNT возвращает количество записей таблицы базы данных. Если в запросе указать
SELECT COUNT(ИМЯ_СТОЛБЦА) ..., то результатом будет количество записей без учёта тех записей,
в которых значением столбца является NULL (неопределённое). Если использовать в качестве аргумента
звёздочку и начать запрос SELECT COUNT(*) ..., то результатом будет количество всех записей (строк)
таблицы. Пример 9.
База данных и таблица -
те же, что и в предыдущих примерах. Требуется узнать число всех сотрудников, которые получают комиссионные. Число
сотрудников, у которых значения столбца Comm - не NULL, вернёт следующий запрос: SELECT COUNT
(Comm)
FROM
Staff
Результатом будет значение 11. Пример 10.
База данных и таблица -
те же, что и в предыдущих примерах. Если требуется узнать общее количество записей в таблице, то
применяем запрос со звёздочкой в качестве аргумента функции COUNT: SELECT COUNT
(*)
FROM
Staff
Результатом будет значение 17. В следующем упражнении для самостоятельного решения
потребуется
использовать подзапрос. Пример 11.
Работаем с одной таблицей - Staff.
Вывести число сотрудников в отделе планирования (Plains). Теперь рассмотрим применение агрегатных функций вместе с оператором SQL GROUP BY. Оператор SQL GROUP BY
служит для группировки результирующих значений по столбцам таблицы базы данных. На сайте есть урок,
посвящённый отдельно этому оператору
. Пример 12.
Есть база данных портала объявлений. В ней есть
таблица Ads, содержащая данные об объявлениях, поданных за неделю. Столбец Category содержит
данные о больших категориях объявлений (например, Недвижимость), а столбец Parts - о более мелких
частях, входящих в категории (например, части Квартиры и Дачи являются частями категории Недвижимость).
Столбец Units содержит данные о количестве поданных объявлений, а столбец Money - о денежных суммах,
вырученных за подачу объявлений. Используя оператор SQL GROUP BY, найти суммы денег, вырученных за подачу
объявлений в каждой категории. Пишем следующий запрос: SELECT
Category, SUM
(Money) AS
Money
FROM
Ads
GROUP BY
Category Пример 13.
База данных и таблица -
та же, что в предыдущем примере. Используя оператор SQL GROUP BY, выяснить, в какой части каждой категории было
подано наибольшее число объявлений. Пишем следующий запрос: SELECT
Category, Part, MAX
(Units) AS
Maximum
FROM
Ads
GROUP BY
Category Результатом будет следующая таблица: Итоговые и индивидуальные значения в одной таблице можно получить объединением
результатов запросов с помощью оператора UNION
. Реляционные базы данных и язык SQLКоманды для работы с базами данных
1. Просмотр доступных баз данных
SHOW DATABASES;
2. Создание новой базы данных
CREATE DATABASE;
3. Выбор базы данных для использования
USE 4. Импорт SQL-команд из файла.sql
SOURCE 5. Удаление базы данных
DROP DATABASE Работа с таблицами
6. Просмотр таблиц, доступных в базе данных
SHOW TABLES;
7. Создание новой таблицы
CREATE TABLE Ограничения целостности при использовании CREATE TABLE
Пример
8. Сведения о таблице
9. Добавление данных в таблицу
INSERT INTO 10. Обновление данных таблицы
UPDATE 11. Удаление всех данных из таблицы
DELETE FROM 12. Удаление таблицы
DROP TABLE Команды для создания запросов
13. SELECT
14. SELECT DISTINCT
15. WHERE
Пример
16. GROUP BY
Пример
17. HAVING
Пример
18. ORDER BY
Пример
19. BETWEEN
Пример
20. LIKE
SELECT Пример
21. IN
Пример
22. JOIN
Пример 1
Пример 2
Пример 3
23. View
Создание
CREATE VIEW Удаление
DROP VIEW Пример
24. Агрегатные функции
25. Вложенные подзапросы
Пример
Типы функций
Детерминированные и недетерминированные функции
Агрегатные и скалярные функции
Оконные функции
Функция INITCAP
Функция UPPER
Функция RPAD
Иван—————-
Сергей—————
Функция LTRIM
Функция RTRIM
Функция REPLACE
Функция TRANSLATE
Функция SUBSTR
Функция LENGTH – длина строки
4
6
7
Функция SQL SUM
Функция SQL MIN
Функция SQL MAX
Функция SQL AVG
Функция SQL COUNT
Агрегатные функции вместе с SQL GROUP BY (группировкой)
Category
Part
Units
Money
Транспорт
Автомашины
110
17600
Недвижимость
Квартиры
89
18690
Недвижимость
Дачи
57
11970
Транспорт
Мотоциклы
131
20960
Стройматериалы
Доски
68
7140
Электротехника
Телевизоры
127
8255
Электротехника
Холодильники
137
8905
Стройматериалы
Регипс
112
11760
Досуг
Книги
96
6240
Недвижимость
Дома
47
9870
Досуг
Музыка
117
7605
Досуг
Игры
41
2665