Работа с данными (DML - data manipulation language)
Select
select column1 as field1 from table1можно исключить
as из запроса:
select column1 as field1 from table1
тернарный операторselect
if(0, "true", "false")
dual
dual - это виртуальная несуществующая таблица, которую можно использовать для заглушки:
select null colum1 from dual where false
Такой запрос может быть нужен чтобы сгенерировать названия колонок в запросе, который объединяет несколько таблиц через union.
regexp, rlike
Функция
RLIKE является синонимом для
REGEXP, она предусмотрена для совместимости с mSQL.
экранирование спец-символов требует двойного обратного слеша:
select * from comment where comment_hi rlike '^1\\.[^\\.]*$'
примеры:
| ^[0-9]+$ | последовательность цифр
|
|
|
Вложенный select
можно проверить, существуют ли строки, удовлетворяющие условию:
select t.codec_group_name, t.description, t.codec_group_id from rtu.mvts_codec_group t
where (not exists (select * from rtu.test_codec_group_comments t1 where t1.codec_group_id=t.codec_group_id))
exists ищет до первой строки, удовлетворяющей условию (т.е. работает быстрее, чем count)
Сортировка средствами базы
Рандомная сортировка
select * from my_table order by rand();
Кастомная сортировкаselect * from mw_attr where attr_id in (301,302,305,306)
order by case attr_id
when 301 then 1
when 305 then 2
when 306 then 3
when 302 then 100
end;
Выведет колонки отсортированными по весу.
Сортировка по логическому выражению
select * from action order by version='all' desc;
В этом запросе сначала будут выведены те строки, для которых условие выполняется.
Связь многие ко многим
Допустим, что есть таблица районов. Таблица предприятий. И таблица связей между районами и предприятиями.
Предприятие может иметь филиалы в нескольких районах. Очевидно, что связь многие ко многим через третью таблицу.
Теперь, положим, надо выбрать все районы, но каждый раз определять, есть ли филиал заданного предприятия в каждом районе.
Запрос будет выглядеть примерно так:
select district_id, district_nm,
(select firm_id from distict_firm t1 where firm_id=78 and t.district_id=t1.district_id) firm_id
from district t
ключевым моментом здесь является использование подзапроса, который должен вернуть ровно одну ячейку.
Группировка
Склеить значения varchar поля:
select group_concat(version separator '') from update_special group by object_type
Update
update первым параметром получает table_reference, что означает, что вместо имени таблицы можно использовать конструкции left/right/inner join.
update update_special t inner join update_path p on t.version=p.target_version and p.path_id<25
set t.path_id=p.path_id;
Update из второй (виртуальной) таблицы
update
table1 t1,
(
select * from (
select '01.01' gui_hi_old, '_01.02' gui_hi_new
union all
select '01.01.01' gui_hi_old, '_01.02.01' gui_hi_new
union all
select '01.02' gui_hi_old, '_01.01.01' gui_hi_new
union all
select '01.02.01' gui_hi_old, '_01.01' gui_hi_new
) t3 order by gui_hi_old desc
) t2
set
t1.gui_hi = t2.gui_hi_new
where
t1.gui_hi = t2.gui_hi_old
Изменение сортировки строк по полю pos
UPDATE my_table
SET pos = ELT(FIELD(pos, 1,2,3), 3,2,1)
WHERE pos IN (1,2,3);
Сортировка не прокатит, если поле pos является уникальным ключом
Insert
Копировать данные из одной таблицы в другую
1. insert into mw_role_set ( select 3, object_id, rights from mw_role_set where role_id=1);
2. insert into mw_string_prop ( str_id, prop_nm, lang_id, str_text )
select 930, 'MERATableColumn', t.lang_id, t.str_text from mw_string_prop
where str_id=928 and prop_nm='name'
При вставке даннных может возникнуть проблема пересечения ключей. Можно сделать обрабочик:
insert into my_table select * from my_table2
on duplicate key update
field = concat( values(field), my_table.field );
через values будет доступно значение из выборки по my_table2
Вставка из виртуальной таблицы
insert into test2 (t1_id, t2_item)
select 1, "description"
union
select 2 , "scription"
union
select 2, "unscription"
Replace
replace mw_user_default_aa set gui_hi='119.01', aa_id=3
Встроенные функции
Работа со строками
http://www.intuit.ru/department/database/mysql/10/concat
| cклеить строки
| select concat("str1", "str2");
|
| group_concat | склеить результаты подселекта в строку. имеет ограничение в количестве склеиваемых символов (где-то 1000). чтобы отменить ограничение:
set group_concat_max_len = 1000000; | select column0, (select group_concat(column1 separator "," ) from t2) from t1; |
replace()
| заменить подстроку
| SELECT REPLACE('www.mysql.com', 'w', 'Ww'); 'WwWwWw.mysql.com'
|
Дата и время
date_format
| получить время в заданном формате (как строку)
icom-studio.com.ua/blog/use_of_mysql_date_format.html
| select date_format(date_column, '%Y.%m.%d %H:%i:%S') from my_table;
|
now()
| текущее дата-время
|
|
curdate()
| текущая дата
|
|
| выбрать данные за указанный период
| select * from my_table where DATE_SUB(NOW(), INTERVAL 1 DAY) < column_nm
select * from my_table where DATE_SUB(NOW(), INTERVAL 24 HOUR) < column_nm
период может быть указан в виде дроби 1.3 DAY
|
Типы данных
Бинарные данные (blob и longblob)
blob и longblob - текстовые поля для хранения бинарных данных.
blob ограничен 64кб, longblob - 4.2Gb
Запрос получения данных (select) не может вернуть больше 16Mb (определяется конфигурацией MySQL). Нельзя записать (insert, update) данных больше, чем позволяет оперативная память компьютера. Поэтому, есть ограничения при работе с большими файлами (будем считать, что в blob хранятся файлы).
как строкиС полями blob можно работать точно так же, как со строками. Можно получить часть строки, узнать количество байт, добавить к строке.
select substring(f, 1, 10), length(f) from myTable where id=1;
update myTable set f=f+%s where id=1;
работа через файлы
select f from myTable where id=2 into dumpfile '/tmp/sss.avi';
update myTable set f=load_file('/tmp/xxx.avi') where id=1
Lock-и
Залочить таблицу:
lock tables debug_call write; # есть ещё read, но не знаю, зачем он нужен
после того, как хотя бы одна строка будет вставлена в базу, параллельные курсоры не смогут более читать из таблицы mvts_debug_call.
Разлочить таблицы, залоченные текущим курсором:
unlock tables;
Получить информацию о залоченных таблицах:
show open tables;
