MySQL DML

Работа с данными (DML - data manipulation language)

1. Select
1. dual
2. regexp, rlike
3. Вложенный select
4. Сортировка средствами базы
5. Связь многие ко многим
6. Группировка
2. Update
1. Update из второй (виртуальной) таблицы
1. Изменение сортировки строк по полю pos
3. Insert
1. Копировать данные из одной таблицы в другую
2. Вставка из виртуальной таблицы
4. Replace
5. Встроенные функции
1. Работа со строками
2. Дата и время
6. Типы данных
1. Бинарные данные (blob и longblob)
7. Lock-и

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;