Mysql запрос на добавление записи в таблицу. Основы T-SQL. DML. Ограничение на группировки HAVING

Оставьте комментарий 6,950

Сводные таблицы

Для анализа списков данных (таблиц данных) в Excel 2007, которые имеют множество строк или записей, часто используются такие средства как сводные таблицы. Сводные таблицы значительно упрощают просмотр, обработку и обобщение данных в списках Excel 2007.

Для того чтобы программа Excel работала с введенными данными как с таблицей данных, а не с простым набором данных, необходимо ее отформатировать как таблицу. Для этого щелкните на кнопке Таблица, расположенной во вкладке Вставка. В диалоговом окне Создание таблицы (рис. 1) укажите предполагаемый диапазон, в котором будет размещаться таблица, и установите флажок Таблица с заголовками.

Рис. 1.

К данному диапазону будет применено форматирование, т.е. применен экспресс-стиль таблицы, заданный по умолчанию, при этом активизируются контекстные инструменты под общим названием "Работа с таблицами", которые входят в контекстную вкладку Конструктор. Для форматирования таблицы можно также применить средство "Форматировать как таблицу" на вкладке "Главная".

Рис. 2.

Для построения отчета по этой таблице целесообразно применить мощное средство "Сводная таблица". Для применения этого средства к спискам данных или к таблицам данных необходимо активизировать одну из ячеек таблицы данных, например ячейку таблицы "Остатки товаров на складе". Затем щелкнуть кнопку "Сводная таблица", которая находится на вкладке "Вставка" в группе "Таблица" (рисунок 3).

Рис. 3.

В открывшемся окне диалога "Создание сводной таблицы" требуется выбрать (выделить) таблицу или диапазон и указать, куда следует поместить отчет (желательно на новый лист), затем щелкнуть ОК. Откроется специальный мастер сводных таблиц (рисунок 4).

Рис. 4.

В левой части рабочего листа отображается изображение отчета (Сводная Таблица1), а в правой части листа расположены инструменты для создания сводной таблицы: четыре пустых областей и список полей. Для построения отчета надо в правой части перетащить требуемые поля в соответствующие области сводной таблицы: "Фильтр отчета", "Название столбцов", "Название строк" и "Значения".

Например, если выбрать поля: № склада, Наименование, Цена (грн.) и перетащить их в соответствующие области: "Название столбцов", "Название строк" и "Значения", то в правой части они будут отображаться в этих областях. При этом в левой части рабочего листа будет построена сводная таблица или отчет (рис. 5).

Рис. 5.

Следует отметить, что в области "Значения" выполняются какие-либо математические вычисления, например, суммирование (Сумма по полю Цена). Чтобы изменить тип вычислений, надо в области "Значения" щелкнуть левой кнопкой мыши по полю "Сумма по полю Цена" и в открывшемся меню выбрать команду "Параметры полей значений", затем в окне диалога "Параметры поля значений" выбрать требуемую функцию и щелкнуть на кнопке ОК.

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

Средства Excel для анализа данных и решения задач оптимизации

Мощными средствами анализа данных Excel 2007 являются:

анализ "что – если", к которым относятся: подбор параметров и диспетчер сценариев;
надстройка "Поиск решения" (надстройка Solver).

Средства анализ "что – если" помещены на вкладке "Данные" в группе "Работа с данными", а "Поиск решений" на вкладке "Данные" в группе "Analysis".

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

Программа "Поиск решений" предназначена для решения сложных систем уравнений, линейных и нелинейных задач оптимизации. В основе надстройка Solver лежат итерационные методы.

При выполнении сложных аналитических задач по статистике (к примеру, корреляционного и дисперсионного анализа, расчетов по алгоритму Фурье, создания прогностической модели) пользователи часто интересуются, как добавить анализ данных в Excel. Обозначенный пакет функций предоставляет разносторонний аналитический инструментарий, полезный в ряде профессиональных сфер. Но он не относится к инструментам, включенным в Эксель по умолчанию и отображающимся на ленте. Выясним, как включить анализ данных в Excel 2007, 2010, 2013.

Для Excel 2010, 2013

Рассмотрим анализ данных в Excel: как включить и чем будет отличаться процедура активации для других версий. В большинстве вариантов программы процедура выполняется одинаково. Поэтому последовательность действий, изложенная в разделе, подходит для большинства версий, в том числе для выпусков 2013 и 2016 годов.

Включение блока инструментов

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

зайдите во вкладку «Файл», расположенную в верхней части ленты интерфейса;
с левой стороны открывающегося меню найдите раздел «Параметры Эксель» и кликните по нему;
просмотрите левую часть окошка, откройте категорию надстроек (вторая снизу в списке), выберите соответствующий пункт;
в выпавшем диалоговом меню найдите пункт «Управление», кликните по нему мышью;
клик вызовет на экран диалоговое окно, выберите раздел надстроек, если выставлено значение, отличное от «Надстройки Excel», поменяйте его на обозначенное;
нажмите на экранную кнопку «Перейти» в разделе надстроек. В правой части выпадет список надстроек, которые устанавливает программа.

Активация

Рассмотрим, как активировать аналитические функции, предоставляемые надстройкой пакета:

В перечне надстроек, выпавшем после последовательного выполнения предыдущих операций, пользователю надлежит поставить знак птички напротив раздела «Пакет анализа».
Выбрав активацию пакета, необходимо нажать клавишу «Ок», расположенную в верхней правой части диалогового окна.
После нажатия кнопки пакет появляется на ленте функций. Для получения доступа к нему в интерфейсе программы выбирается вкладка «Данные». В правой части меню «Раздел анализа». Там пользователь найдет иконку опции «Анализ данных».

Запуск функций группы «Анализ данных»

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

операции с выборками;
построение гистограммы – разновидности столбчатой диаграммы, демонстрирующей разброс разных значений некоторого параметра в виде столбцов, площади которых соотносятся друг с другом так же, как удельные веса разных групп в рассмотренной выборе;
генерация случайных чисел;
порядковое и процентное ранжирование;
вариации регрессионного, дисперсионного, корреляционного, ковариационного анализа;
анализ по алгоритму Фурье;
экспоненциальное сглаживание – метод математических преобразований, преследующих цель выявления некоторого тренда или тенденции во временном ряду. Метод применяется для построения прогнозов.

Чтобы применить ту или иную опцию, действуют по нижеприведенному алгоритму:

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

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

Для Excel 2007

Алгоритм, как включить анализ данных в Excel 2007, отличается от остальных тем, что в самом начале (для выхода на параметры Excel) вместо кнопки «Файл» пользователь нажимает четырехцветный символ Microsoft Office. В остальном же последовательность операций идентична приведенной для других версий.

Данная же статья посвящена наполнению таблиц БД данными, то есть будем изучать SQL-команды для вставки новых записей. Следует сказать, что запись в таблицу можно сделать двумя способами:

Способ №1.
Попробуем внести запись о новой стране в таблицу countries. Синтаксис добавления будет следующим:
INSERT INTO имя_таблицы (поле_1, поле_2, …) VALUES (Значение_1, Значение_2, …); Исходя из нашей структуры таблицы, SQL-запрос будет таким:
INSERT INTO countries (country_name, acronym_name) VALUES ("Russia", "RU"); Вот так мы внесли в нашу таблицу запись о стране «Россия». Здесь должно быть все понятно и просто, главное внимательно смотреть какие поля указываете в первых скобках, и в таком же порядке прописывать значения во вторых.

Способ №2.
Второй способ, на мой взгляд, немного проще, поскольку вы видите что, и какому полю присваиваете. Поверьте, если в таблице огромное количество столбцов, то очень просто перепутать или недосмотреть порядок полей в первых, и порядок значений во вторых скобках. Синтаксис второго метода такой:
INSERT INTO имя_таблицы SET поле_1 = Значение_1, поле_2 = Значение_2, … ; Давайте на данном примере внесем какую-нибудь информацию в табличку persons , поскольку там побольше полей, и сразу будет чувствоваться преимущество второго способа:
INSERT INTO persons SET first_name="Ivan", last_name="Dulin", registration_date="2012-06-14", country="1"; Теперь в нашей табличке есть такие данные:

Вы, наверное, заметили, что мы не указали значение для age, а оно оказалось заполненным... Все правильно – для данного поля мы устанавливали значение по умолчанию как 25. Так что теперь наш Ivan Dulin числиться в базе с возрастом 25 лет. Возможно, не самый удачный пример делать значение по умолчанию для поля возраста, но можно такие свойства устанавливать к таким полям, как, к примеру, рейтинг пользователя сайта, или количество просмотров страницы. Для них изначально ставится значение 0.

Следует еще обратить внимание на формат даты в MySQL: YYYY-MM-DD. Если его не придерживаться, то ваши записи просто не будут внесены в таблицу.

Как видим, поле profession заполнено как NULL, это пустое значение. Для данного поля мы задавали свойство «не обязательное к заполнению (NULL)» .

Как дополнение, рассмотрим еще такую команду:
LOAD DATA LOCAL INFILE "D:\zapros.txt" INTO TABLE persons; Как вы думаете, что мы сейчас сделали?! А сделали мы следующее: добавили данные в таблицу persons из файла zapros.txt , который находится на диске D. Информация в файле должна быть следующей структуры:

Структура данных файла должна соответствовать таким требованиям:

Каждая новая запись должна описываться с новой строчки
Данные должны быть указаны для абсолютно всех полей. Как вы можете заметить, для поля id мы указали значение NULL, ведь оно у нас автоинкрементное, так что MySQL сам внесет нужное значение.
Поля разделяются друг от друга символом табуляции (клавиша Tab).
Введенная информация должна соответствовать типам данных конкретного поля. То есть, например, дата в формате ГГГГ-ММ-ДД, целое число для integer и т.д.

Таким образом, вы научились вносить новые данные в таблицы своей БД. Для закрепления изученного материала, внесите самостоятельно такие данные:

Таблица professions:

Таблица persons:

first_name	last_name	age	registration_date	country	profession
Leonid	Bilak	45	2012-06-20	2	1
Yuri	Nazarov	22	2012-06-21	3	1
Alla	Savenko	25	2012-06-22	2	3
Irina	Nikolaeva	31	2012-06-22	1	3

Обязательно добавьте эти записи, поскольку они нам понадобятся для изучения оператора SELECT (выбора информации с БД), который мы рассмотрим в следующей (четвертой) статье по изучению SQL-запросов.

Как видим, поле profession заполнено как NULL, это пустое значение. Для данного поля .

Структура данных файла должна соответствовать таким требованиям:

Каждая новая запись должна описываться с новой строчки
Данные должны быть указаны для абсолютно всех полей. Как вы можете заметить, для поля id мы указали значение NULL, ведь оно у нас автоинкрементное, так что MySQL сам внесет нужное значение.
Поля разделяются друг от друга символом табуляции (клавиша Tab).
Введенная информация должна соответствовать типам данных конкретного поля. То есть, например, дата в формате ГГГГ-ММ-ДД, целое число для integer и т.д.

Таблица professions:

Таблица persons:

first_name	last_name	age	registration_date	country	profession
Leonid	Bilak	45	2012-06-20	2	1
Yuri	Nazarov	22	2012-06-21	3	1
Alla	Savenko	25	2012-06-22	2	3
Irina	Nikolaeva	31	2012-06-22	1	3

Запросы на изменение используются для добавления (INSERT INTO), удаления (DELETE) и обновления (UPDATE) записей в таблицах.

Команда INSERT INTO

Команда INSERT INTO может быть использована для добавления одной или нескольких записей в конец таблицы.

Синтаксис команды INSERT INTO имеет следующий вид:
INSERT INTO Название_Таблицы [(Название_Полей)] VALUES (Значения_данных);

Например, поместим в таблицу "Группы" запись, содержащую данные (1, БФ-16а, 1, 1) в соответствующие столбцы (КодГруппы, Название, Курс, Семестр).

Для этого запрос в формате:
INSERT INTO Группы (КодГруппы, Название, Курс, Семестр) VALUES (1, БФ-16а, 1, 1);
введем на бланке запроса, выполнив команду "Конструктор запросов" на вкладке "Создание", в режиме "Режим SQL" из меню Вид.

Сохраняем запрос с именем "Добав_1_записи". В результате в «Области переходов» появится несвязанный объект - «Добав_1_записи» (Рис.1.).

Рис. 1.

После сохранения запроса необходимо выполнить этот запрос, щелкая на пиктограмме «Выполнить». В результате выполнения команды «Добав_1_записи» в пустой таблице появится первая запись (Рис. 2.)

Рис. 2.

Команда UPDATE

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

Синтаксис команды UPDATE имеет следующий вид:
UPDATE Название_Таблицы SET Название_Столбца = Новое_Значение WHERE Условие_Отбора;

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

Создадим запрос на изменение записи в таблице "Группы" базы данных sql_training_st.mdb.

Например, изменим уже существующую запись для первого ключевого поля id в столбце "Название" таблицы "Группы".

Запрос в формате:
UPDATE Группы SET Название ="БФ-16б" WHERE ID=1;

Сохраняем запрос с именем "Изменение_записи_1". В результате в «Области переходов» появится объект - «Изменение_записи_1» (Рис. 3.).

Рис. 3.

После сохранения запроса необходимо выполнить этот запрос, щелкая на пиктограмме «Выполнить». В результате выполнения команды «Изменение_записи_1» появится окно диалога, в котором необходимо ввести значение параметра ID=1 и нажать кнопку ОК. После этих действий в таблице "Группы" изменится запись в поле "Название" с БФ-16а на БФ-16б (Рис. 4.).

Рис. 4.

Команда DELETE

Команда DELETE предназначена для удаления записей из таблицы.

Формат запроса на удаление записей:
DELETE FROM Название_Таблицы WHERE Условие_Отбора;

Создадим запрос на изменение записи в таблице "Группы" базы данных sql_training_st.mdb.

Например, удалим существующую запись для первого ключевого поля id во всех столбцах (полях) таблицы "Группы".

Для этого запрос в формате:
DELETE FROM Группы WHERE ID=1;
создаем на бланке запроса, выполнив команду "Конструктор запросов" на вкладке "Создание", в режиме "Режим SQL" из меню Вид.

Сохраняем запрос с именем "Удаление_записи_1". В результате в «Области переходов» появится объект - «Удаление_записи_1» (Рис. 5.).

Рис. 5.

После сохранения запроса необходимо выполнить этот запрос, щелкая на пиктограмме «Выполнить». В результате выполнения команды «Удаление_записи_1» появится окно диалога, в котором необходимо ввести значение параметра ID=1 и нажать кнопку ОК. После этих действий откроется окно диалога "Подтвердите удаление записей". После подтверждения в таблице "Группы" будет удалены данные во всех полях, т.е. удалена запись (Рис. 6.).

Рис. 6.

Следует отметить, что для удаления данных в конкретных полях таблицы целесообразно воспользоваться командой UPDATE и заменить значения в требуемых полях на Null. Если необходимо удалить таблицу в целом, то следует использовать оператор DROP TABLE (синтаксис оператора: DROP TABLE Название_Таблицы;).

Восстановим первую запись в таблице "Группы", выполнив команду «Добав_1_записи», в результате восстановим первую запись (Рис. 7.).

Рис. 7.