Mysql история запросов. Структурированный язык запросов SQL в PHPMyAdmin

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

• Создание базы данных в PHPMyAdmin. Работа с данными используя PHP, MySQL и HTML
• Информационная система оценивания эффективности и результативности труда сотрудника лаборатории
• Разработка информационной системы для начальника нефтяной базы
• Создание информационной системы для диспетчера автосервиса
• Обзор функциональных возможностей программного средства для управления производственным циклом предприятия

phpMyAdmin - это программа написанная на PHP и предназначенная для управления сервером MySQL через всемирную сеть. phpMyAdmin поддерживает широкий набор операций над MySQL, наиболее часто используемые операции поддерживаются с помощью пользовательского интерфейса (управление базами данных, таблицами, полями, связями, индексами, пользователями, правами, и т. д.), одновременно вы можете напрямую выполнить любой SQL запрос.

Запросы - это объект базы данных, который служит для извлечения данных из таблиц и предоставления их пользователю в удобном виде. Особенность запросов состоит в том, что они черпают данные из базовых таблиц и создают на их основе временную таблицу. Применение запросов позволяет избежать дублирования данных в таблицах и обеспечивает максимальную гибкость при поиске и отображении данных в базе данных.

SQL-запросы - запросы, строящиеся при помощи унифицированного набора инструкций SQL (Structured Query Language - структурированный язык запросов). SQL в полной мере нельзя отнести к традиционным языкам программирования, где бы вы вводили переменные, описывали тип и многое другое, он содержит только набор стандартных операторов доступа к данным, хранящимся в базе данных.

Язык SQL делиться четыре части:

• Операторы определения данных (Data Definition Language, DDL): create (“создать”), alter (“изменить”), drop (“удалить”).
• Операторы манипуляции данными (Data Manipulation Language, DML): select (“выбрать”), insert (“вставить”), update (“обновить”) и delete (“удалить”).
• Операторы определения доступа к данным (Data Control Language, DCL): grant (“предоставить”) и revoke (“лишить”).
• Операторы управления транзакциями (Transaction Control Language, TCL): commit (“завершить”), rollback (“откатить”), savepoint (“сохранить”).

Мы же рассмотрим, одну команду языка SQL – команду выборки данных SELECT , так благодаря этой команде можно организовать все возможные запросы.

Синтаксис оператора SELECT

SELECT <список полей, которые вы хотите отразить в запросе> |*

FROM <список таблиц, с которыми вы работаете>

[WHERE <предикат-условие выборки или неявное внутреннее соединение таблиц>]

[GROUP BY <список полей группировки> [HAVING <предикат-условия для группировки>]]

[ORDER BY <список полей, по которым упорядочивает вывод> ]

Информация, заключенная в квадратные скобки, как обычно является необязательной.

Рассмотрим несколько примеров запросов в phpMyAdmin, для этого воспользуемся предыдущей базой данных «Komp _ texnika ». В данной базе мы уже создали таблицу «product » (товар), в которой отразили соответствующие свойства сущности: product _ ID (первичный ключ), maker (производитель), model (модель), god _ sozd (год создания) и type _ ID (тип продукта, является внешним ключом) (см. Рис.1).

Так же в данной базе данных, мы добавили еще одну сущность «laptop » (тип товара), в которой отразили соответствующие свойства сущности: type _ ID (первичный ключ), type (тип), OS (операционная система), processor (процессор), pamjat (память), zvet (цвет), ves (вес) и zena (цена) (см. Рис.2).

Теперь прежде чем прейти к составлению запросов, необходимо установить связь между сущностями (таблицами). Для этого будем работать с таблицей «laptop », а именно выберем пункт «Связи » (см. Рис.3).

После чего откроется страница, где необходимо указать поля, которые отвечают за связь между таблицами (см. Рис.4). Также связь можно установить другим способом, используя «Дизайнер » (см. Рис.4)

Таким образом, установив связи между таблицами, мы можем перейти к созданию запросов, перейдя верхнем меню «SQL ».

Запрос 1 . Вывести информацию имеющейся техники за 2012 выпуска (см. Таблицу 1).

Таблица 1

Для реализации данного запроса мы использовали оператор select – для вывода информации из поля maker (производитель) и model (модель) , from – для описания таблицы из которой нужно выдать информацию; предикат where – для условия отбора строк из таблицы результата, то есть выдает те данные, у которых год создания (god _ sozd ) равнялся 2012 ; order by – для сортировки данным к полю maker (производитель) .

Запрос 2. Вывести информацию о количестве технике имеющихся производителей (см. Таблицу 2).

Таблица 2

с ount является одной из так называемых «агрегирующих функций », которая считает количество не пустых кортежей, являются мощным инструментом конструирования запросов, в данном случае вы сгруппировали вывод данных по maker (производителям) .

Запрос 3 . Вывести общую стоимость ноутбуков по имеющимся производителям, отсортировав по сумме по возрастанию (см. Таблицу 3).

Таблица 3

В данном запросе необходимы данные из двух таблицы, для этого нам необходимо объединить таблицы «product » (товар) и «laptop » (тип товара), что бы это сделать использовали предикат join с указание объединения полей: первичного ключа таблицы laptop . type _ ID и внешнего ключа таблицы product . type _ ID . Так же обратите внимание, что при работе с двумя и более таблиц, в операторе select , при описании полей необходима, указывать имя таблицы данного поля, например laptop . type . sum , так же как и count относится к «агрегирующим функциям », которая считает сумму значений кортежей, в нашем случае сумму стоимости техники.

Запрос 4. Вывести данные о ноутбуках с операционной системой Windows-8, со стоимостью не больше 25 тысяч (см. Таблицу 4).

Таблица 4

Запрос 5. Выведите информацию о товаре: maker (производитель), model (модель), pamjat (память), processor (процессор) и zena (цена), по категории «podhodit » и «not podhodit » (см. Таблицу 5).

Таблица 5

Результат:

Программа : Select case when (laptop.pamjat = 1000) and (laptop.processor >=2600) then "podhodit" else "not podhodit" end as itog, product.maker, product.model, laptop.pamjat as pamjat_v_Gb, laptop.processor as proc_v_Mgz, laptop.zena from product join laptop on laptop.type_ID=product.type_ID where laptop.type like "l%" order by 1 desc, 6

с ase … end – предикат выбора, используется для распределения товара по категориям, в соответствии с критериями. like "1%" – предикат, который применяется для сопоставления строк с образцом. Образец представляет собой строку, в которой могут использоваться два специальных символа: «% » и «_ », где «% » сопоставляется с любой строкой (возможно, пустой) и «_ » сопоставляется с одним символом, любые другие символы, отличные от специальных, могут быть сопоставлены только со специальными символами и сами с собой. order by [ asc | desc ] – предназначен для сортировки результатов запросов [возрастанию (стоит по умолчанию) | по убыванию], сортировка возможна как для одного поля, так и более.

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

Список литературы

1. Аннотация к phpMyAdmin [Электронный ресурс] / URL: http://php-myadmin.ru/about/notice.html
2. Запросы к базе данных и их использование. Виды запросов. Технология создания [Электронный ресурс] / URL: http://www.yaklass.ru/materiali?mode=cht&chtid=514
3. Островский С.Л. Основы web-программирования для школьного «сайтостроительства». Лекция 5. PHP+MySQL [Текст]/ С.Л. Островский // Информатика. - 2008.-№21.-с.2-15

Журналы событий — первый и самый простой инструмент для определения статуса системы и выявления ошибок. Основных логов в MySQL четыре:

• Error Log — стандартный лог ошибок, которые собираются во время работы сервера (в том числе start и stop);
• Binary Log — лог всех команд изменения БД, нужен для репликации и бэкапов;
• General Query Log — основной лог запросов;
• Slow Query Log — лог медленных запросов.

Лог ошибок

Этот журнал содержит все ошибки, которые произошли во время работы сервера, включая критические ошибки, а также остановки, включения сервера и предупреждения (warnings). С него нужно начать в случае сбоя системы. По умолчанию все ошибки выводятся в консоль (stderr), также можно записывать ошибки в syslog (по умолчанию в Debian) или отдельный лог-файл:

Log_error=/var/log/mysql/mysql_error.log

# Ошибки будут писаться в mysql_error.log

Рекомендуем держать этот журнал включенным для быстрого определения ошибок. А для понимания, что значит та или иная ошибка, в MySQL присутствует утилита perror :

Shell> perror 13 64 OS error code 13: Permission denied OS error code 64: Machine is not on the network

# Объясняет значения кодов ошибок

Бинарный (он же двоичный) лог

В бинарный лог записываются все команды изменения базы данных, пригодится для репликации и восстановления.

Включается так:

Log_bin = /var/log/mysql/mysql-bin.log expire_logs_days = 5 max_binlog_size = 500M

# Указывает расположение, срок жизни и максимальный размер файла

Учтите, что если вы не собираетесь масштабировать систему и реализовывать отказоустойчивость, то бинарный лог лучше не включать. Он требователен к ресурсам и снижает производительность системы.

Лог запросов

В этом журнале содержатся все полученные SQL-запросы, информация о подключениях клиентов. Может пригодиться для анализа индексов и оптимизации, а также выявления ошибочных запросов:

General_log_file = /var/log/mysql/mysql.log general_log = 1

# Включает лог и указывает расположение файла

Также его можно включить/отключить во время работы сервера MySQL:

SET GLOBAL general_log = "ON"; SET GLOBAL general_log = "OFF";

# Для применения не нужно перезагружать сервер

Лог медленных запросов

Журнал пригодится для определения медленных, то есть неэффективных запросов. Подробнее читайте в этой статье .

Просмотр логов

Для просмотра логов на Debian (Ubuntu) нужно выполнить:

# Лог ошибок tail -f /var/log/syslog #Лог запросов tail -f /var/log/mysql/mysql.log # Лог медленных запросов tail -f /var/log/mysql/mysql-slow.log

# Если логи не указаны отдельно, то находятся в /var/lib/mysql

Ротация логов

Не забывайте сжимать (архивировать, ротировать) файлы логов, чтобы они занимали меньше места на сервере. Для этого используйте утилиту logrotate , отредактировав файл конфигурации /etc/logrotate.d/mysql-server :

# - I put everything in one block and added sharedscripts, so that mysql gets # flush-logs"d only once. # Else the binary logs would automatically increase by n times every day. # - The error log is obsolete, messages go to syslog now. /var/log/mysql.log /var/log/mysql/mysql.log /var/log/mysql/mysql-slow.log { daily rotate 7 missingok create 640 mysql adm compress sharedscripts postrotate test -x /usr/bin/mysqladmin || exit 0 # If this fails, check debian.conf! MYADMIN="/usr/bin/mysqladmin --defaults-file=/etc/mysql/debian.cnf" if [ -z "`$MYADMIN ping 2>/dev/null`" ]; then # Really no mysqld or rather a missing debian-sys-maint user? # If this occurs and is not an error please report a bug. #if ps cax | grep -q mysqld; then if killall -q -s0 -umysql mysqld; then exit 1 fi else $MYADMIN flush-logs fi endscript }

# Сжимает и архивирует нужные логи, очищает файлы

DDL Log

MySQL также ведет лог языка описания данных. В него собираются данные операций типа DROP_TABLE and ALTER_TABLE. Лог используется для восстановления после сбоев, которые произошли во время выполнения таких операций. DDL Log — бинарный файл, не предназначенный для чтения пользователем, поэтому не модифицируйте и не удаляйте его.

Самое главное

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

Вопрос: История запросов ms sql

Как посмотреть история запросов, (delete, update...)?

Ответ:
профайлер самый тормознутый аудит, который можно придумать. Extended Events можно настроить под нужды и он тоже умеет выгружать и тп, но любой из этих вариантов требует ресурсы.

Вопрос: История выполнения запросов

Подскажите, можно ли где-нибудь в MS SQL посмотреть кто и какие запросы выполнял в конкретной БД?

Ответ: iluxa1810 , эта информация доступна только частично. Попробуй запрос вида:

Код T-SQL

1 2 3 4 SELECT deqs.last_execution_time AS [ Time ] , dest.TEXT AS [ Query] FROM sys.dm_exec_query_stats AS deqs CROSS APPLY sys.dm_exec_sql_text (deqs.sql_handle ) AS dest ORDER BY deqs.last_execution_time DESC

Для полной истории запросов нужно делать протоколирование или использовать утилиты вроде SQL Profiler.

Вопрос: Sql запрос возврата предыдущих значений в БД

Всем привет!

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

Может есть какой то sql запрос или может какой то скрипт... я был бы очень благодарен.

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

Заметили не сразу и восстанавливать старую копию базы было поздно т.к. уже новые заказы есть в магазине.

Ответ: Poetiq ,
InnoDB умеет хранить историю запросов.

Bash

1 ~/ .mysql_history

Накатываете последний бэкап и повторяете команды
Ну и вообщемто перед каждой интеграцией стоит делать бекап, а еще лучше тестировать на тестовом сервере

Вопрос: Запрос sql по поиску из нескольких таблиц

Добрый день. Помогите пожалуйста понять, могу ли я сделать так или нужно переделать базу или такой поиск не возможно реализовать.
Пишу на delphi rad xe10.
Я пишу в таблицу Работники инф о работнике в одной строке,в размеры одежды тоже одна строка, а историю что ему выдавали (много строк и отличие только по ID_rab).

Хочу сделать поиск
что бы при запросе по фамилии или таб номеру мне выдавало инф:
О рабочем из первой табл в dbgrid1,
его размерах из второй табл в dbgrid2
и историю относящуюся к этому работнику из табл 3 (там может быть много строк имеющие только отличие по ID_rab) в dbgrid3

В первой таблице:

во второй:

SQL

1 2 3 4 5 6 7 8 9 10 11 CREATE TABLE Размер ( ID_razmOdejdi INT NOT NULL IDENTITY (1 , 1 ) , Одежды VARCHAR (4 ) , ID_rab INT , PRIMARY KEY (ID_razmOdejdi) , FOREIGN KEY (ID_rab) REFERENCES Работники, ) ;

в третей:

SQL

1 2 3 4 5 6 7 8 9 10 11 CREATE TABLE История ( ID_story INT NOT NULL IDENTITY (1 , 1 ) , Наим_средств VARCHAR (15 ) , ID_rab INT , PRIMARY KEY (ID_story) , FOREIGN KEY (ID_rab) REFERENCES Работники, ) ;

Сделать сразу 3 ADOQuery с разными запросами?
Я не знаю как сделать что бы вывело информацию из другой таблице которая относиться к первой
Если бы можно было сделать так: ввел я в поиск человека его нашло и по его ID_rab нашло бы информацию в других таблицах и при этом вывести в 3 разных dbgrid.

Ответ:

Сообщение от Rogzar

А если удалить полностью всю информацию об сотруднике его размерах, истории и положено, это можно сделать через один запрос?

Если на таблице Работники разрешить каскадное удаление, то удаление строки в ней приведет к удалению данных из подчиненных таблиц. Иначе придется делать несколько DELETE запросов.

Добавлено через 14 часов 31 минуту
Подобный код уязвим к атакам под названием. Следует использовать запросы с параметрами.

Вопрос: Одним запросом - реально?

Добрый день, Господа!
Подскажите, как рационально решить следующую задачу...
Имеются три таблицы: товары (их реально немного, где-то 60-80 штук), история продаж и остатки...

В упрощенном виде структура следующая:
goods :
id
name

order_history :
id
s_date
good_id
price

balance :
id
s_date
order_id
count

Все три таблицы связаны по ID (goods.id - order_history.good_id, order_history.id - balance.order_id).
Задача - на любую произвольную дату получить суммовой остаток всех товаров! То есть на каждый товар я должен получить ближайшую по дате запись из баланса и истории продаж (из баланса соответственно количество, из истории продаж - цену).

Понятно, что можно в цикле перебирать все товары по ID и в каждой итерации выполнять SQL запрос, но как то это... не нравится мне такой вариант! :) Есть ли возможность решить задачу изящно - одним запросом?

Буду благодарен за любые идеи!

Ответ: Не вижу противнопоказаний. Если приведёшь в порядок структуру.

Тупой!
Внес некоторую избыточность в БД

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

Вопрос: Вложенный запрос, помогите совместить несколько простых

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

И так есть несколько запросов, которые выполняются по порядку:
$sql2 = SELECT url FROM list WHERE `group` = "1" AND `proekt`= "1" GROUP BY url ORDER BY COUNT(*) DESC LIMIT 3 - скриншот
Выбираю список URL в количестве 3х штук затем собраю их в вид для запроса

$return=array();
$maxcount=mysql_query($sql2);
while($url = mysql_fetch_array($maxcount)) {
$return = $url;
}
foreach ($return as $url1) { //собираю из массива строчку урлов для Мускула
if ($frag2==0) { $urls = "`url`=".""".$url1."""; $frag2=1; } else $urls .= " OR `url` = "".$url1.""";
}

В данном случае $urls = `url` = "http://www.MosDance.com/" OR `url`="http://raisky.com/" и т.д.

Вывожу список ключей относящихся к этим урлам
select `key` from (SELECT `key` , COUNT(*) AS total FROM list WHERE `group` = "1" AND `proekt`= "1" AND (".$urls.") GROUP BY `key` ORDER BY `total` DESC) as t2 WHERE t2.total >=3

Собственно вопрос основной - как это дело сократить? Как правильно собрать урл, так чтобы можно было избавиться от php срипта-костыля, который собирает URl в нужном порядке и сделать это все к примеру одним запросом в БД? в общем как с одного запроса более умного чем мои костыли, получить конечный результат?

Ответ:

Доброго дня!
Никогда не пишите COUNT(*). Лучше будет COUNT(`id`).
А так же не пишите RAND(*). Лучше писать конечно по другому: там комментарии есть в них функции а такой случай.
Все выше методы отбора записей из бд сильно грузят сервер mysql. Если у Вас конечно не 30 тысяч записей в бд у вас пройдет не так болезненно.

Что касается запроса:
Копните в сторону INNER JOIN. Или Скиньте мне дамп вашей таблицы, помогу Вам, если Вы расскажете более подробно задачу, так как то что я прочел - лень вникать в весь сыр бор=)

-----
- уроки по программированию тута. Интересные фишки тоже там=)

Вопрос: Запрос из двух таблиц по нескольким критериям

Добрый вечер!

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

Запрос должен формироваться на основе всех полей и записей первой таблицы, и курса валюты из второй таблицы. Причем должно быть совпадение всех 3-х критериев отбора: наименование валюты, дата и вид курса.

Заранее спасибо!

Ответ:
Вам будет удобнее в конструкторе запроса.

Разобрался как сделать иннер джой через SQL, на первый взгляд просто. Но есть вопрос:
1. В таблице транзакций есть поле "Вид транзакции"
2. Запрос, который я делаю, сначала вытаскивает для каждой записи таблицы транзакции "Вид курса валюты" из таблицы "Виды транзакций"
3. Запрос вытаскивает из таблицы с курсами по полученному в п. 2 критерию "Вид курса".

Другими словами: сначала объединяются 2 таблицы (транзакций и видов транзакций"), а затем за счет общей информации к ним добавляются данные из таблицы "Курсы валют".

Вопрос: Узнать, на какой записи с таблицы запрос завершается с ошибкой

Столкнулся с ситуацией, когда запрос к таблице фактов возвращает ошибку, а отловить записи в справочнике, которые приводят к ошибке (вопрос в датах) не могу
Сам запрос возвращает все записи с таблицы фактов, плюс вычисляемое поле с справочника. Если бы справочник был заполнен правильно, вложенный запрос вернул бы одно значение на одну запись таблицы фактов. Но по факту в справочнике в записях ошибки, которые выловить не могу.
Может можно как-то просмотреть, на какой записи таблицы фактов возникает ошибка?

Ответ: Не смешно. Ибо уже давно было (дословно):
Ну, как только бред-детектор сработал - так сразу и прибежал... Что не так?
Перед тем как героически писать супер-запросы (на все случаи жизни) надо включать мозги - ну, хотя бы на пару минут...
Ну, вот лично Ваших "страданий" хватило аккурат на бредовый запрос, место которому - мусорная корзина.
И Вам "прилетел тапок" только за его семантику и синтаксис...
Здравая мысль!
А как умоете, берете с полки (ну, хотя бы!) - и "учиться, учиться и учиться" (с). Там точно есть про совместное использование GROUP BY и HAVING...

Вопрос: Программы для профилирования запросов

Приветствую!

Подскажите, кто что (программы, IDE и прочее) использует для разбора запросов, в частности интересует инструменты анализа запросов для их оптимизации. Где можно посмотреть план запроса: индексы запроса, Full scan и прочее.

11 ) | DATE | DATE | driver_name | VARCHAR (100 ) | percent | INT (11 ) | sum_recieved_from_client | DECIMAL (9 , 2 ) | sum_tariff_for_driver | DECIMAL (9 , 2 ) | summa_for_payed | DECIMAL (9 , 2 ) | payed_status | tinyint(1 ) | percent_company | INT (11 )

В поле "payed_status" хранится статус оплаты. Нужно в запросе типа:

получить все данные. Надо так, если "payed_status = 1", то вместо числа, которое в поле "summa_for_payed" всегда получать "0".

Если в запросе добавлю "and payed_status not like "1"", то получится, что вывода этой строки вообще не будет получено, но надо получить всё, только с уже изменённым полем "summa_for_payed" в зависимости от состояния "payed_status"

Добавлено через 25 минут
Добавлю, так же надо, чтобы во время запроса с командой sum(summa_for_payed) за определённую дату, получать сумму, имея ввиду значение поля "payed_status", а точнее чтобы где "payed_status = 1", то и в сумме поля "summa_for_payed" считались как "0" если "payed_status = 1"

Ответ: Да, я думал так, но проблема вот в чём, если я обнулю строку, и если вдруг по-ошибке был выбран не тот пользователь, то возвратить назад не смогу, так как стёрся его старый долг, поэтому думал просто по этому индикатору и определять оплату, и если надо, снова возвратить его назад, видя его сумму долга. Ну и вообще для истории лучше было бы видеть его долг который он закрыл.

Добавлено через 34 минуты
Дорогие, гуру.
Вот нашёл что можно сделать так

SQL

1 SELECT SUM (IF (`payed_status` = 1 , 0 , `summa_for_payed` ) ) FROM mytable

Несколько запросов сделал, вроде то что надо, но может вы подскажите, нет никаких подводных камней в таком запросе, это то что мне надо? Стоит делать так или ещё поискать другие решения? Вот в голову ещё приходят тригеры. может как-то с ними? Что посоветуете. Ещё думал изменить значения, если 0 то оплачено если 1 то нет, и тогда в выборе умножать число на индикатор, и если он нулевой то просто получу ноль. если один то получу само число. просто лишние вычисления... Ваше мнение?