6 запросов для MySQL

Искусство в построении запросов с использованием Structured Query Language (язык структурированных запросов) заключается в создании правильных, эффективных вопросов и команд для базы данных. В запросах SELECT можно использовать ключевые слова JOIN, WHERE и HAVING для уточнения результатов, GROUP BY для комбинирования результатов в легко анализируемые блоки, и UNION для комбинирования результатов нескольких запросов. Команды INSERT, DELETE и UPDATE могут использовать JOIN. Запрос INSERT … SELECT вставляет результаты в в другую таблицу. В запросах DELETE и UPDATE можно использовать ключевое слово WHERE, чтобы указать область действия.

Ниже приводятся 6 запросов для MySQL.
1. Возраст в годах

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

	SELECT DATE_FORMAT(FROM_DAYS(TO_DAYS(now()) - TO_DAYS(@dateofbirth)), '%Y') + 0;

2. Разница между двумя датами

Находим разницу между двумя датами, выраженную в секундах, минутах, часах или днях. Если dt1 и dt2 значения дат в формате ‘yyyy-mm-dd hh:mm:ss’, то количество секунд между dt1 и dt2 вычисляется:
 	UNIX_TIMESTAMP( dt2 ) - UNIX_TIMESTAMP( dt1 )

Чтобы получить количество минут, делим вычисленное значение на 60, для вычисления количества часов - делим на 3600, а для определения количества дней - делим на 3600*24.


3. Выводим значение, которое встречается в столбце N раз
 	SELECT id
 	FROM tbl
 	GROUP BY id
 	HAVING COUNT(*) = N;

4. Вычисляем количество рабочих дней между двумя датами

Простейшим способом вычислить количество рабочих дней между двумя датами является использование таблицы календаря calendar со столбцами даты d и логического флага праздников holiday для всех дней во всех возможных годах. Затем используется соответствующий запрос, который возвращает количество рабочих дней между двумя датами Start и Stop включительно:
 	SELECT COUNT(*)
 	FROM calendar
 	WHERE d BETWEEN Start AND Stop
 	  AND DAYOFWEEK(d) NOT IN(1,7)
 	  AND holiday=0;

5. Находим основной ключ для таблицы
 	SELECT k.column_name
 	FROM information_schema.table_constraints t
 	JOIN information_schema.key_column_usage k
 	USING (constraint_name,table_schema,table_name)
 	WHERE t.constraint_type='PRIMARY KEY'
 	  AND t.table_schema='db'
 	  AND t.table_name='tbl'

6. Определяем, насколько велика таблица
 	SELECT
 	  table_schema AS 'Db Name',
 	  Round( Sum( data_length + index_length ) / 1024 / 1024, 3 ) AS 'Db Size (MB)',
 	  Round( Sum( data_free ) / 1024 / 1024, 3 ) AS 'Free Space (MB)'
 	FROM information_schema.tables
 	GROUP BY table_schema ;

Добавлено: 17 Августа 2013 06:32:06 Добавил: Андрей Ковальчук

Введение в хранимые процедуры MySQL 5

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

Хранимая процедура - это способ инкапсуляции повторяющихся действий. В хранимых процедурах можно объявлять переменные, управлять потоками данных, а также применять другие техники программирования.

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

Разделение логики с другими приложениями. Хранимые процедуры инкапсулируют функциональность; это обеспечивает связность доступа к данным и управления ими между различными приложениями.
Изоляция пользователей от таблиц базы данных. Это позволяет давать доступ к хранимым процедурам, но не к самим данным таблиц.
Обеспечивает механизм защиты. В соответствии с предыдущим пунктом, если вы можете получить доступ к данным только через хранимые процедуры, никто другой не сможет стереть ваши данные через команду SQL DELETE.
Улучшение выполнения как следствие сокращения сетевого трафика. С помощью хранимых процедур множество запросов могут быть объединены.

Против

Повышение нагрузки на сервер баз данных в связи с тем, что большая часть работы выполняется на серверной части, а меньшая - на клиентской.
Придется много чего подучить. Вам понадобится выучить синтаксис MySQL выражений для написания своих хранимых процедур.
Вы дублируете логику своего приложения в двух местах: серверный код и код для хранимых процедур, тем самым усложняя процесс манипулирования данными.
Миграция с одной СУБД на другую (DB2, SQL Server и др.) может привести к проблемам.

Инструмент, в котором я работаю, называется MySQL Query Browser, он достаточно стандартен для взаимодействия с базами данных. Инструмент командной строки MySQL - это еще один превосходный выбор. Я рассказываю вам об этом по той причине, что всеми любимый phpMyAdmin не поддерживает выполнение хранимых процедур.

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

Ограничитель - это символ или строка символов, который используется для указания клиенту MySQL, что вы завершили написание выражения SQL. Целую вечность ограничителем был символ точки с запятой. Тем не менее, могут возникнуть проблемы, так как в хранимой процедуре может быть несколько выражений, каждое из которых должно заканчиваться точкой с запятой. В этом уроке я использую строку “//” в качестве ограничителя.
Шаг 2: Как работать с хранимыми процедурами
Создание хранимой процедуры

 	DELIMITER // 
 	   
 	CREATE PROCEDURE `p2` () 
 	LANGUAGE SQL 
 	DETERMINISTIC 
 	SQL SECURITY DEFINER 
 	COMMENT 'A procedure' 
 	BEGIN 
 	    SELECT 'Hello World !'; 
 	END// 

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

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

4 характеристики хранимой процедуры:

Language: в целях обеспечения переносимости, по умолчанию указан SQL.
Deterministic: если процедура все время возвращает один и тот же результат, и принимает одни и те же входящие параметры. Это для репликации и процесса регистрации. Значение по умолчанию - NOT DETERMINISTIC.
SQL Security: во время вызова идет проверка прав пользователя. INVOKER - это пользователь, вызывающий хранимую процедуру. DEFINER - это “создатель” процедуры. Значение по умолчанию - DEFINER.
Comment: в целях документирования, значение по умолчанию - ""

Вызов хранимой процедуры

Чтобы вызвать хранимую процедуру, необходимо напечатать ключевое слово CALL, а затем название процедуры, а в скобках указать параметры (переменные или значения). Скобки обязательны.
 	CALL stored_procedure_name (param1, param2, ....) 
 	   
 	CALL procedure1(10 , 'string parameter' , @parameter_var);

Изменение хранимой процедуры

В MySQL есть выражение ALTER PROCEDURE для изменения процедур, но оно подходит для изменения лишь некоторых характеристик. Если вам нужно изменить параметры или тело процедуры, вам следует удалить и создать ее заново.
Удаление хранимой процедуры
DROP PROCEDURE IF EXISTS p2;

Это простая команда. Выражение IF EXISTS отлавливает ошибку в случае, если такой процедуры не существует.
Шаг 3: Параметры

Давайте посмотрим, как можно передавать в хранимую процедуру параметры.

CREATE PROCEDURE proc1 (): пустой список параметров
CREATE PROCEDURE proc1 (IN varname DATA-TYPE): один входящий параметр. Слово IN необязательно, потому что параметры по умолчанию - IN (входящие).
CREATE PROCEDURE proc1 (OUT varname DATA-TYPE): один возвращаемый параметр.
CREATE PROCEDURE proc1 (INOUT varname DATA-TYPE): один параметр, одновременно входящий и возвращаемый.

Естественно, вы можете задавать несколько параметров разных типов.
Пример параметра IN
 	   DELIMITER // 
 	   
 	CREATE PROCEDURE `proc_IN` (IN var1 INT) 
 	BEGIN 
 	    SELECT var1 + 2 AS result; 
 	END// 

Пример параметра OUT
 	DELIMITER // 
 	   
 	CREATE PROCEDURE `proc_OUT` (OUT var1 VARCHAR(100)) 
 	BEGIN 
 	    SET var1 = 'This is a test'; 
 	END // 

Пример параметра INOUT
 	DELIMITER // 
 	   
 	CREATE PROCEDURE `proc_INOUT` (OUT var1 INT) 
 	BEGIN 
 	    SET var1 = var1 * 2; 
 	END //

Шаг 4: Переменные

Сейчас я научу вас создавать переменные и сохранять их внутри процедур. Вы должны объявлять их явно в начале блока BEGIN/END, вместе с их типами данных. Как только вы объявили переменную, вы можете использовать ее там же, где переменные сессии, литералы или имена колонок.

Синтаксис объявления переменной выглядит так:
 	DECLARE varname DATA-TYPE DEFAULT defaultvalue; 

Давайте объявим несколько переменных:
 	DECLARE a, b INT DEFAULT 5; 
 	   
 	DECLARE str VARCHAR(50); 
 	   
 	DECLARE today TIMESTAMP DEFAULT CURRENT_DATE; 
 	   
 	DECLARE v1, v2, v3 TINYINT;

Работа с переменными

Как только вы объявили переменную, вы можете задать ей значение с помощью команд SET или SELECT:
 	DELIMITER // 
 	   
 	CREATE PROCEDURE `var_proc` (IN paramstr VARCHAR(20)) 
 	BEGIN 
 	    DECLARE a, b INT DEFAULT 5; 
 	    DECLARE str VARCHAR(50); 
 	    DECLARE today TIMESTAMP DEFAULT CURRENT_DATE; 
 	    DECLARE v1, v2, v3 TINYINT;     
 	   
 	    INSERT INTO table1 VALUES (a); 
 	    SET str = 'I am a string'; 
 	    SELECT CONCAT(str,paramstr), today FROM table2 WHERE b >=5; 
 	END // 

Шаг 5: Структуры управления потоками

MySQL поддерживает конструкции IF, CASE, ITERATE, LEAVE LOOP, WHILE и REPEAT для управления потоками в пределах хранимой процедуры. Мы рассмотрим, как использовать IF, CASE и WHILE, так как они наиболее часто используются.
Конструкция IF

С помощью конструкции IF, мы можем выполнять задачи, содержащие условия:
	DELIMITER // 
 	   
 	CREATE PROCEDURE `proc_IF` (IN param1 INT) 
 	BEGIN 
 	    DECLARE variable1 INT; 
 	    SET variable1 = param1 + 1; 
 	   
 	    IF variable1 = 0 THEN 
 	        SELECT variable1; 
 	    END IF; 
 	   
 	    IF param1 = 0 THEN 
 	        SELECT 'Parameter value = 0'; 
 	    ELSE 
 	        SELECT 'Parameter value <> 0'; 
 	    END IF; 
 	END // 

Конструкция CASE

CASE - это еще один метод проверки условий и выбора подходящего решения. Это отличный способ замены множества конструкций IF. Конструкцию можно описать двумя способами, предоставляя гибкость в управлении множеством условных выражений.
 	DELIMITER // 
 	   
 	CREATE PROCEDURE `proc_CASE` (IN param1 INT) 
 	BEGIN 
 	    DECLARE variable1 INT; 
 	    SET variable1 = param1 + 1; 
 	   
 	    CASE variable1 
 	        WHEN 0 THEN 
 	            INSERT INTO table1 VALUES (param1); 
 	        WHEN 1 THEN 
 	            INSERT INTO table1 VALUES (variable1); 
 	        ELSE 
 	            INSERT INTO table1 VALUES (99); 
 	    END CASE; 
 	   
 	END // 

или:
 	DELIMITER // 
 	   
 	CREATE PROCEDURE `proc_CASE` (IN param1 INT) 
 	BEGIN 
 	    DECLARE variable1 INT; 
 	    SET variable1 = param1 + 1; 
 	   
 	    CASE 
 	        WHEN variable1 = 0 THEN 
 	            INSERT INTO table1 VALUES (param1); 
	        WHEN variable1 = 1 THEN 
 	            INSERT INTO table1 VALUES (variable1); 
 	        ELSE 
 	            INSERT INTO table1 VALUES (99); 
 	    END CASE; 
 	   
 	END //

Конструкция WHILE

Технически, существует три вида циклов: цикл WHILE, цикл LOOP и цикл REPEAT. Вы также можете организовать цикл с помощью техники программирования “Дарта Вейдера”: выражения GOTO. Вот пример цикла:
 	DELIMITER // 
 	   
 	CREATE PROCEDURE `proc_WHILE` (IN param1 INT) 
 	BEGIN 
 	    DECLARE variable1, variable2 INT; 
 	    SET variable1 = 0; 
 	   
 	    WHILE variable1 < param1 DO 
 	        INSERT INTO table1 VALUES (param1);  	        SELECT COUNT(*) INTO variable2 FROM table1; 
 	        SET variable1 = variable1 + 1; 
 	    END WHILE; 
 	END // 

Шаг 6: Курсоры

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

MySQL поддерживает курсоры в хранимых процедурах. Вот краткий синтаксис создания и использования курсора.
 	DECLARE cursor-name CURSOR FOR SELECT ...;       /*Объявление курсора и его заполнение */ 
 	DECLARE  CONTINUE HANDLER FOR NOT FOUND          /*Что делать, когда больше нет записей*/ 
 	OPEN cursor-name;                                /*Открыть курсор*/ 
 	FETCH cursor-name INTO variable [, variable];    /*Назначить значение переменной, равной текущему значению столбца*/ 
 	CLOSE cursor-name;/*Закрыть курсор*/
 


В этом примере мы проведем кое-какие простые операции с использованием курсора:
 	DELIMITER // 
 	   
 	CREATE PROCEDURE `proc_CURSOR` (OUT param1 INT) 
 	BEGIN 
 	    DECLARE a, b, c INT; 
 	    DECLARE cur1 CURSOR FOR SELECT col1 FROM table1; 
 	    DECLARE CONTINUE HANDLER FOR NOT FOUND SET b = 1; 
 	    OPEN cur1; 
 	   
 	    SET b = 0; 
 	    SET c = 0; 
 	   
 	    WHILE b = 0 DO 
	        FETCH cur1 INTO a; 
 	        IF b = 0 THEN 
 	            SET c = c + a; 
 	    END IF; 
 	    END WHILE; 
 	   
 	    CLOSE cur1; 
 	    SET param1 = c; 
 	   
 	END //

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

Не чувствительный: открывшийся однажды курсор не будет отображать изменения в таблице, происшедшие позже. В действительности, MySQL не гарантирует то, что курсор обновится, так что не надейтесь на это.
Доступен только для чтения: курсоры нельзя изменять.
Без перемотки: курсор способен проходить только в одном направлении - вперед, вы не сможете пропускать строки, не выбирая их.

Заключение

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

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

Добавлено: 16 Августа 2013 11:07:57 Добавил: Андрей Ковальчук

Некоторые аспекты использования пользовательских функций в предложениях SQL

"Старый конь борозды не испортит". "В чем преимущество склероза ?. В том, что все время узнаешь новое"

Уже надоело говорить о том, что ровным счетом ничего нового в этой жизни не происходит, а большинство нового - это хорошо забытое старое. Вот еще пример. Один автор этой статьи пожилой , но относительно молодой фоксист , начавший работать только с FXP2.6 под виндами, а второй -один из старейших клиперистов. Поэтому и приемы разные. У первого в основном новые методы ФОКСА, у второго вечные истины dbase. Оказывается, что старые рецепты часто дают много лучшие решения, чем новомодные навороты, зная которые , очень трудно использовать старые приемы, хотя и читал теорию и потенциально их знаешь. Потом, черт возьми, вечно попадаешь на агрессивную рекламу Микрософт с описанием новых, все более мощных методов. И часто веришь всему, что они говорят. Но вот берем тривиальные задачи, суем их на новую кухню и ждем вкусного пирога. А вместо него иногда - одна вонь.

Речь пойдет в основном о SQL select с предложениями outer left/right join, union и использовании групповых функций.

В одной из предыдущих статей мы уже упоминали о том, что генератор запросов и представлений view дает неправильный код с этими предложениями, если имеем несколько join.

Пример такого ошибочного кода:

LEFT OUTER JOIN dbf_s!valuta; 
ON int(pd.valuta_id) == int(Valuta.valuta_id) ; 
ON int(pd.kv_izg) == int(Valuta_a.valuta_id) ; 
ON pd.shpz_id = Sc_shpz.shpz_id ; 
ON pd.nc_id = bc_ac.nc_id ; 
ON pd.country_id = country.country_id ; 
ON pd.um_id = Sc_ed.um_id ; 
LEFT OUTER JOIN dbf_s!valuta Valuta_a ; 
LEFT OUTER JOIN dbf_s!Sc_shpz; 
LEFT OUTER JOIN dbf_s!bc_ac; 
LEFT OUTER JOIN dbf_s!Country ; 
LEFT OUTER JOIN dbf_s!Sc_ed 
А нужно- 
LEFT OUTER JOIN dbf_s!valuta; 
ON int(pd.valuta_id) == int(Valuta.valuta_id) ; 
LEFT OUTER JOIN dbf_s!valuta Valuta_a ; 
ON int(pd.kv_izg) == int(Valuta_a.valuta_id) ; 
LEFT OUTER JOIN dbf_s!Sc_shpz; 
ON pd.shpz_id = Sc_shpz.shpz_id ; 
LEFT OUTER JOIN dbf_s!bc_ac; 
ON pd.nc_id = bc_ac.nc_id ; 
LEFT OUTER JOIN dbf_s!Country ; 
ON pd.country_id = country.country_id ; 
LEFT OUTER JOIN dbf_s!Sc_ed ; 
ON pd.um_id = Sc_ed.um_id

Ну это не беда. "Кто предупрежден - тот вооружен".

Беда в другом - генератор позволяет к этому добавить предложение where, где можно использовать любые из уже использованных таблиц.

Вот тут-то (особенно если условие where в нем касается присоединенных таблиц) результат может быть и вовсе неверен. А как такое условие не добавить, если нужно взять товар за определенный интервал времени? Или цену товара из прайс листа, соответствующую нужному учетному периоду? Этого в join никак не засунешь.

Без outer join, казалось бы, тоже нельзя - ссылка на любой справочник может быть пустой, а строчку товара терять нежелательно. Поэтому условия из outer join не получится перенести в where . Ждать, когда Микрософт исправит ошибки - так наши клиенты ждать нас не будут и быстренько слиняют к более удачливым фирмам или программным продуктам. Поэтому, хочешь не хочешь, а давай правильный результат.

Сначала мы разбили запрос на два -один с outer join , второй с where. Работает весьма неплохо и при наличии нужных индексов достаточно быстро. Однако, иногда получается надо писать уж слишком много веток по разным условиям. Число SQL и их сложность растет и растет. Более того, приходится включать вторичные справочники с еще одним outer join на таблицу, которая сама уже висит на outer join. Вероятность ошибки в результате слишком велика.

Вот тут то на помощь приходят старые, но от этого, не менее эффективные методы. А именно: использование пользовательских функций. Идея состоит в том, чтобы убрать из предложения Select SQL все таблицы, которые привязаны к внешним объединениям, а искать нужные ссылки в этих таблицах в пользовательских функциях. Функция должна быть такая, что при наличии ссылки - получить ее значение, а при отсутствии вернуть "пустышку" нужного типа. То есть сделать работу outer join самим. Что для этого нужно: имя ключа, по которому в справочнике ищется справка, имя справочной таблицы, поле из справочника, имя тэга, по которому ведется поиск.
Некоторые хитрости.

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

Второе.

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

Вот пример такой функции
Lparameters pr_id,mydat 
Local retcr 

If order('val_course')!='datcur' 
Set order to datcur in val_course 
Endif 
retcr=0 
If empty(mydat) 
set exact off 
If seek(str(pr_id,3),'val_course') 
retcr=course 
Endif 
Else 
If set('NEAR')='OFF' 
Set near on 
Endif 
=seek(str(pr_id,3)+dtos(mydat),'val_course') 
If pr_id=val_course.valuta_id 
retcr=val_course.course 
Endif 
Set near off 
Endif 
Return retcr: 

Function getref 
Lparameters fld,pr_id,tb,tg 

Local fl,tpt,rt,rtt,lnn, fl,fll 

fl=tb+'.'+alltrim(fld) 
fll=tb+'.'+tg 
*** Если совпал ключ, можно не искать, иначе - ищем 
If not empty(pr_id) and ; 
(eval(fll)=pr_id or seek(pr_id,tb,tg)) 
rtt=eval(fl) 
Return rtt 
Else 
rt=eval(fl) 
tpt=type('rt') 
Do case 
Case tpt='N' 
rtt=0 
Case tpt='C' 
Lnn=len(eval(fl)) 
rtt=pad('',lnn) 
Case tpt='D' 
rtt=ctod('..') 
Case tpt='L' 
rtt=.f. 
Endcase 
Return rtt 
Endif 

Lparameters pr_id,mydat 

Local retcr 

If order('val_course')!='datcur' 
Set order to datcur in val_course 
Endif 
retcr=0 
If empty(mydat) 
set exact off 
If seek(str(pr_id,3),'val_course') 
retcr=course 
Endif 
Else 
If set('NEAR')='OFF' 
Set near on 
Endif 
=seek(str(pr_id,3)+dtos(mydat),'val_course') 
If pr_id=val_course.valuta_id 
retcr=val_course.course 
Endif 
Set near off 
Endif 
Return retcr

Пример использования. Пусть у нас был запрос SQL следующего вида:
Select doc.num,val from doc LEFT OUTER JOIN valuta; 
ON doc.valuta_id= Valuta.valuta_id

Теперь пишем
Select doc.num getref('val','doc.valuta_id','valuta','valuta_id) as val from doc 

Здесь конечно получилось более громоздко, чем в исходном примере, зато можно добавлять еще кучу полей из справочников и кучу справочников и не боятся при этом использовать предложение where в этом же sql

Второй пример сложнее.

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

Решение первое (неправильное):
Select val,max(dat),cour from valuta, vcour where valuta.valuta_id=vcour.valuta_id union ;

Select val, ctod('..'), 0 as cour from valuta where valuta_id not in ( select valuta_id from vcour) 

Беда в том, что функция max, работая по всему диапазону записей, дату-то дает правильную, зато курс - из последней по счету записи. Если курс вводился не в хронологическом порядке - курс получается не последней даты, а последней записи, хотя сама дата в выборке - последняя.

Решение в стиле Microsoft - правильное, но не оптимальное.
Select max(dat)as dt,valuta_id from ; 
vcour group by valuta_id into cursor qr 
Select distinct val,cour,dat from ; 
valuta,vcour,qr ; 
where valuta.valuta_id=vcour.valuta_id ; 
and qr.dt=vcour.dat and ; 
qr.valuta_id=valuta.valuta_id ; 
union ; 
Select distinct val,1 as cour,ctod('..') as dat from valuta where ; 
valuta.valuta_id not in (select distinct valuta_id from vcour); 
into cursor vcou 

Решение по дедовским заветам. Самое быстрое.

Создадим в таблице курса валют композитный индекс datcur с выражением
STR(valuta_id,3)+DTOS(dat)

Сделаем функцию Getvcr
Lparameters pr_id,mydat 

Local retcr 

If order('vcour')!='datcur' 
Set order to datcur in vcour 
Endif 
retcr=0 
If empty(mydat) 
set exact off 
If seek(str(pr_id,3),'vcour') 
retcr=vcour.cour 
Endif 
Else 
If set('NEAR')='OFF' 
Set near on 
Endif 
=seek(str(pr_id,3)+dtos(mydat),'vcour') 
If pr_id=vcour.valuta_id 
retcr=vcour.cour 
Endif 
Set near off 
Endif 
Return retcr


Теперь достаточно записать Select val,getvcr(valuta.valuta_id) as cour from valuta - и все.

А хотите курс на конкретную дату - укажите ее во втором параметре.

Так и вспоминается анекдот про двух бычков, старого и молодого. Молодой все норовил быстро побежать к коровкам. Нам же по нраву принцип старого бычка- пойдем медленно медленно, но поимеем все , что хотели и может даже обгоним кого и помоложе.

Добавлено: 16 Августа 2013 02:44:37 Добавил: Андрей Ковальчук

Не используйте условия IF в MySQL запросах

При изучении почти всех языков, начинающим разработчикам говорят: "не используйте абсолютные переходы goto", кроме языка Perl, что означает "старайтесь не использовать" или "используйте, но крайне редко". Тоже я могу сказать и про IF в MySQL: "старайтесь не использовать конструкцию IF в MySQL запросах".

Разрабатывая внутреннюю программу для фирмы на php+mysql в силу обстоятельств мне пришлось прибегнуть к такой конструкции при запросе в MySQL:

SELECT
t1.*, t2.*, t3.* 
FROM
t1, t2, t3
WHERE
IF (t1.field1 = 0,
t1.field2 = t3.field2,
t1.field3 = t2.field3
)
GROUP BY t1.field3
ORDER BY DESC

Что, на первый взгляд, спасало положение, но я проверял это на 5, максимумна 10 записях в таблице. При введение в эксплуотацию этого кода при количестве записей 200 и выше, сервер MySQL просто "вешался". Страница генерировалась от 60 до 250 секунд. Сначала я грешил что я использую слишком много запросов в внешней базе данных на другом хосте, но даже исключение этих запросов не помогло.
Как я понял дело заключается в механизме IF, т.е. код просит пройти все записи таблицы t1, где поле field1 = 0
и выбрать данные или из t2 или из t3. Получается очень долгий запрос.

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

Добавлено: 16 Августа 2013 02:42:16 Добавил: Андрей Ковальчук

Команды MySQL

Консоль MySQL позволяет вводить как инструкции SQL, так и служебные команды MySQL, предназначеные для администрирования и использования таблиц в базах данных MySQL. К основным командам относятся SHOW, USE и CREATE.

Рассмотрим каждую из них отдельно.

SHOW

Эта команда предназначена для просмотра доступных баз данных и таблиц в конкретных базах данных. Для просмотра списка баз данных необходимо ввести такую команду:

SHOW DATABASES;

Если, например, существуют две базы данных - mysql и test (эти две базы как правило создаются автоматически при инсталляции сервера), то MySQL ответит примерно так:

+----------+
| Database |
+----------+
| mysql |
| test |
+----------+

Для просмотра списка таблиц используется эта же команда в таком виде:
 SHOW TABLES;

Эта команда выдаст список таблиц в текущей базе данных:

+-----------------+
| Tables in mysql |
+-----------------+
| test |
| mysql |
+-----------------+

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

CREATE

Эта команда позволяет создавать новые базы данных. Как было сказано выше, по умолчанию сервер создает две базы данных - mysql и test. Для удобства стоит создавать новую базу данных для каждой отдельной задачи, потому что легче работать с набором таблиц, которые связаны между собой (даже если эта связь только смысловая). Создадим новую базу данных при помощи этой команды:
CREATE DATABASE staff

После этой операции будет создана новая база данных не содержащая никаких таблиц, а команда SHOW DATABASES выдаст следующее:

+----------+
| Database |
+----------+
| mysql |
| test |
| staff |
+----------+

USE

Как уже было сказано выше, в один и тот же момент только одна база данных может быть активна. Команда USE служит для выбора этой активной базы. Для перехода к некоторой базе данных необходимо выполнить следующую операцию:
USE staff 

Стоит отметить, что при создании новой базы данных (см. выше), новая база не становится активной автоматически - для ее использования необходимо сделать это самостоятельно.

SQL

Нижеследующее ознакомит вас с методами извлечения и занесения данных в таблицы при помощи языка SQL.

Просмотр данных

Наипростейшей командой является следующая:
SELECT * FROM mysql;

Эта команда подразумевает, что в активной базе данных существует таблица mysql, из которой она просто извлекает все данные и выводит их на экран.

Замечание: На SQL-команды распространяется одно ограничение, которое не имеет отношения к командам MySQL - инструкции SQL обязательно должны заканчиваться точкой с запятой. Это позволяет вводит многострочные запросы к бызе данных, что не требуется для MySQL-команд, которые как правило лаконичные и короткие.

Как вы уже наверное поняли, команда SELECT служит для просмотра данных таблиц. Ее упрощенный синтаксис таков:
SELECT <список полей> FROM <список названий таблиц> [WHERE <список условий>] [ORDER BY <список полей>];

В списке поля может быть как один элемент, так и несколько; кроме того возможно указание символа "*" (), который говорит, что следует показать все поля таблицы. Часть WHERE ... является необязательной и позволяет выбрать из таблицы строки, удовлетворяющие определенному(ым) условию(ям). Раздел ORDER BY ... служит для сортировки полученных данных по определенным полям.

Вот пример более сложного запроса, демонстрирующий сказанное выше. Допустим таблица staff содержит информацию о сотрудниках некоторой организации. Этот запрос находит записи о тех из них, кто работает больше двух лет и кого не зовут Иваном.
SELECT name, project, works_since
FROM staff
WHERE
name > 'Иван' AND
works_since '1998-04-26';

Рассмотрим этот запрос. Первая строка велит MySQL показать содержимое только полей name, project и works_since. Вторая строка указывает на таблицу, в которой следует искать эти данные. Оставшаяся часть запроса указывает критерии выбора - имя не должно быть 'Иван', а дата быть более ранней, чем 26-е апреля 1998-го года. Вот пример вывода после такой операции:

+----------+--------------+-------------+
| name | project | works_since |
+----------+--------------+-------------+
| Fred | Secret data | 1997-01-01 |
| Jonathan | Blue apples | 1997-06-01 |
+----------+--------------+-------------+

Для показа данных одного поля может использоваться такой запрос:
SELECT project FROM staff ORDER BY project;

+-------------+
| project |
+-------------+
| Blue apples |
| Blue apples |
| Cornichons |
| Secret data |
| Secret data |
| Secret data |
+-------------+

Как видите, этот запрос просто выбирает значения из нужного поля из каждой строки и выводит их, заодно сортируя по (единственному) полю project, поэтому в результатах встречаются повторения. Для устранения их используется инструкция DISTINCT:
SELECT DISTINCT project FROM staff ORDER BY project; 

+-------------+
| project |
+-------------+
| Blue apples |
| Cornichons |
| Secret data |
+-------------+

SQL позволяет производить некоторые вычисления и получать некоторую описательную информацию при помощи агрегатных функций. Среди этих функций - COUNT, SUM, MAX, MIN и AVG.

COUNT - вычисляет количество найденых строк
SUM - находит сумму значений в найденых строках
MAX - находит найбольшее среди найденых значений
MIN - находит наименьшее среди найденых значений
AVG - находит среднее значение от найденых

Используются эти функции как элементы списка таблиц в запросе с аргументом - названием поля. Вот несколько примеров.
SELECT COUNT (project) FROM staff;
SELECT MAX (projects_done) FROM staff;
SELECT AVG (project_done) FROM staff 

Эти запросы находят количество выполняемых проектов, наибольшее количество проектов, выполняемое одним человеком и среднее количество проектов, в которых участвуют работники соответственно.

SQL также располагает средствами работы с регулярными выражениями (работе с которыми в Perl вы можете ознакомиться, прочтя соответствующую статью на нашем сайте). Для сравнения значения с регулярным выражением используется оператор LIKE, а для конструирования простеших выражений - символы '_' (произвольный символ) и '%' (произвольное количество произвольных символов). Пример. Этот запрос находит все имена, которые начинаются с 'Jo':
SELECT name FROM staff
WHERE name LIKE 'Jo%n';

+----------+
| name |
+----------+
| Jonathan |
| John |
+----------+

MySQL также позволяет производить более развитое сравнение с шаблоном при помощи оператора REGEXP и средств построения шаблона теми же методами, что используются в Perl (см. Регулярные выражения в Perl).
SELECT name, project FROM staff
WHERE project REGEXP "[bB]";

Этот запрос выведет все строки, в которых название проекта содержит букву 'b' вне зависимости от регистра.

И последнее по порядку, но не по значению - использование более чем одной таблицы. Особенность заключается в том, что используемые таблицы могут иметь общие поля, которыми они связаны. Для того, чтобы точно указывать, о поле какой таблицы идет речь, используется запись типа staff.project, где перед точкой стоит название таблицы, а после нее - название поля. Второй вариант - назначения псевдонимов (алиасов) таблицам для большего удобства. Для этого в списке таблиц название каждой таблицы указывается вместе с псевдонимом - например stuff x, projects y.
SELECT x.name, x.project, x.description
FROM staff x, projects x
WHERE
x.project = x.project_name; 

В этом примере объединяются таблицы staff и projects, причем выводятся имя сотрудника, проект над которым он работает и описание этого проекта.

+----------+-------------+------------------------------+
| name | project | description |
+----------+-------------+------------------------------|
| Jonathan | Blue apples | Worldwide apple distribution |
| Fred | Secret data | Secret data |
| John | Secret data | Secret data |
+----------+-------------+------------------------------+

Объединив все это в один запрос получим следующее:
SELECT DISTINCT y.project_name, y.description, COUNT(x.name), FROM staff x, projects yWHERE
y.project_name = x.project AND
y.project_name > 'Secret data' AND
y.project_name LIKE "Wo__d%"
ORDER BY
project_name; 

+--------------+--------------------------+-------------+
| project_name | description | COUNT(name) |
+--------------+--------------------------+-------------+
| Blue apples | Worldwide apple delivery | 3 |
+--------------+--------------------------+-------------+

Этот запрос показывает названия проектов, описания их и количество сотрудников, которые заняты в них для тех проектов, которые не являются секретными и описания которых начинаются с 'Wo', следом за которыми идут еще два символа, после чего - 'd' и дальше что угодно, и сортирует единственное значение по полю project_name.

Редактирование данных

Редактирование данных - это добавление, удаление и изменение их. Для выполнения этих операций используются команды INSERT, DELETE и UPDATE соответственно.

Команда INSERT служит для вставки строк в таблицы. Вот как может выглядеть такой запрос:
INSERT INTO staff VALUES ("Robert", "1980-05-07", "2000-04-26",
"$100", "Secret data");

Возможна также вставка отдельных значений. В таком случае необходимо указать, в какие поля стоит вставлять данные, причем оставшиеся поля будут незаполнены - при выполнении запросов SELECT они будут представлены как NULL - специальное начение, означающее, что данное отсутствует.
INSERT INTO staff(name, date_of_birth) VALUES ("Jack", "1977-07-29");

SELECT * FROM staff
WHERE name = "Jack"; 

+------+---------------+-------------+---------+---------+
| name | date_of_birth | works_since | project | sallary |
+------+---------------+-------------+---------+---------+
| Jack | 1997-07-29 | NULL | NULL | NULL |
+------+---------------+-------------+---------+---------+

Удаление данных производится командой DELETE. Для этого нужно указать таблицу и (необязательно) условия, которым должны удовлетворять строки, которые следует удалить. Если условия опущены, эта команда, как и SELECT, проделает эту операцию надо всеми строками - то есть очистит таблицу.
DELETE FROM staff
WHERE
name = "Jack"; 

Изменение данных производится при помощи команды UPDATE. Необходимо указать, значения каких полей следует изменить, а также (опять же необязательно) условия, которым должны удовлетворять строки, которые следует обновить.
UPDATE staff
SET
works_since = "2000-04-26"
sallary = "$200"
project = "Secret data"
WHERE
name = "Jack"; 

Этот запрос устанавливает дату принятия на работу, зарплату и название проекта всем Джекам, которые находятся в базе данных.

Это небольшая часть языка SQL, необходимая для работы с MySQL, в частности при программировании для Web. Для более полной информации обратитесь к официальным спецификациям SQL или к документации MySQL. Примечание для тех, кто знаком со стандартом ANSI SQL 92: на данный момент MySQL поддерживает укороченную версию этого стандарта, в которую не входит выполнения подзапросов а также некоторые другие функции.

Добавлено: 15 Августа 2013 10:57:21 Добавил: Андрей Ковальчук