Сущности (DDL - data definition language)
Базы данных
показать все | show databases; |
создать
| create database db_name;
create database db_name CHARACTER SET utf8 COLLATE utf8_general_ci;
|
убить | DROP {DATABASE | SCHEMA} [IF EXISTS] db_name |
переименовать | RENAME {DATABASE | SCHEMA} db_name TO new_db_name; |
Пользователи
http://www.mysql.ru/docs/man/Adding_users.htmlсоздать пользователя и выдать права:GRANT ALL PRIVILEGES ON dbname.* TO compwiki@localhost IDENTIFIED BY 'my_password' WITH GRANT OPTION;
определить пароль для root-пользователя (если пароль не был определён)
:mysqladmin -u root password NEWPASSWORDсменить пароль root:mysqladmin -u root -p'oldpassword' password newpass
Пароли для внешки и для локалхоста могут отличаться. Чтобы
сменить пароль для внешки:
UPDATE mysql.user SET Password=PASSWORD('newpasswd') WHERE User='root';
FLUSH PRIVILEGES;
Таблицы
показать список:
show tables
переименовать:rename table name1 to name2
копировать таблицу
create table db1.table_nm select * from db2.table_nm;
Типы таблиц (engine)
Проверить
доступные engine таблиц в СУБД из консоли:
show engine;
engine и внешние ключиMyISAM не поддерживает
InnoDB поддерживает
Колонки/столбцы
изменить описаниеalter table table_nm modify column column_nm int unsigned;
удалить колонкуalter table table_name drop column_name
переименоватьalter table table_nm change column old_name new_name int(11);
http://dev.mysql.com/doc/refman/5.1/en/alter-table.html
изменить порядок
ALTER TABLE TableName change ColumnNameToMove longtext AFTER ColumnNameToPutAfter
http://dev.mysql.com/doc/refman/5.0/en/change-column-order.htmlВнешние ключи
Оказывается, что внешние ключи
всё-таки нужны.
alter table table_nm
add constraint fk_table_nm__column_nm foreign key (column_name)
references table2_nm ( table2_id )
on update [restrict|cascade|set null]
on delete [restrict|cascade|set null];
restrict - запретить удаление строки из таблицы table2_nm, если на неё есть ссылка из текущей таблицы table_nm
cascade - соответственно изменить ссылающиеся значения (или удалить ссылающиеся строки)
set null - установить пустую ссылку
Стоит обратить внимание, что не все типы таблиц поддерживают внешние ключи.
Add constraint
alter table mw_class add constraint fk_mw_class__object_id foreign key (object_id)
references mw_object (object_id) on delete restrict on update cascade;
Drop constraintalter table mw_gui_object DROP foreign key fk_mw_gui_object__object_id;
Узнать, какие
внешние ключи ссылаются на таблицу можно через: information_schema → KEY_COLUMN_USAGE → REFERECES_TABLE_NAME
Ошибки при создании внешних ключей
1. Проверить, что у полей одинаковая пометка unsigned.
Уникальные ключи/индексы
переименовать уникальный ключ
нельзя. надо сначала удалить ключ, а потом добавить его снова.
удалениеDROP INDEX index_name ON tbl_name;либо
alter table table_nm drop index index_name;если уникальный ключ используется внешиним ключом, то удалить такой уникальный ключ нельзя.
добавлениеalter table mw_read_rule add index uk_mw_read_rule__class_id_rr_nm (class_id, rr_nm); # индекс
alter table mw_string_prop add unique key uk_mw_string_prop__str_id_prop_nm_lang_id (str_id, prop_nm, lang_id); # уникальный ключ
Процедуры
создать:
CREATE PROCEDURE proc_name (IN var1 VARCHAR(100), IN var2 VARCHAR(100)) # видимо, должны быть ещё OUT параметры
BEGIN
.....;
.....;
END
вызвать:
call proc_name( "vasia", "masia");
Функции
Функция имеет только входящие параметры. На выходе только одно значение - то, что возваращает сама функция.
drop function if exists newColumnPos;
delimiter ;;
create function newColumnPos( tableId int unsigned, pos int unsigned)
returns int unsigned
DETERMINISTIC # это слово указывает, что функция будет возвращать одно и то же значение каждый раз при одинаковых входных параметрах
# если это не так, то надо указывать reads sql data, ещё есть вариант NO SQL
begin
return 0;
end;;
delimiter ;
Триггеры
Триггер - это обработчик события "изменение таблицы" (вставка/изменение/удаление строк)
drop trigger tau_mw_gui_string_1;
create trigger tau_mw_gui_string_1 after update
on mw_gui_string_var FOR EACH ROW update mw_gui_lang set ts ='1998.01.01' where lang_id=NEW.lang_id
Чтобы запретить вставку или обновление по триггеру, то надо в триггере вызвать exception.
Обработки пользовательских exception-ов в MySQL нет. Чтобы вызвать exception надо произвести действие, которое вызывает системный exception.
Например, вставка в несуществующую таблицу или вставка null в not null поле.
drop trigger if exists tbi_mw_enum_item;
delimiter ;;
create trigger tbi_mw_enum_item before insert
on mw_enum_item FOR EACH ROW
begin
if ( exists( select * from mw_enum_item where
enum_id = NEW.enum_id and enum_key=NEW.enum_key and p1=NEW.p1 and p2=NEW.p2 and p3=NEW.p3))
then
call raise_app_error('ERROR_CODE', 'table_nm', 'ERROR_DATA'); # самопальная функция, которая генерит исключение в триггерах
end if;
end;;
delimiter ;
обозначения внутри триггера:
| new | вставленная/изменённая запись |
old
| удалённая запись/запись до изменения
|
Переменные
Пользовательские переменные (переменные сессии)
Эти переменные храняться до завершения соединения с сервером.
Установить значение переменной:
set @a = 1;
прочитать преременную (значение должно быть определено в рамках текущей сессии):
select @a;
Переменные хранимого кода
Эти переменные создаются внутри хранимого кода, имеют ограниченную область видимости и имеют определённый тип данных.
declare i int default 1;
raise_app_error
CREATE PROCEDURE raise_app_error(IN p_error_code VARCHAR(100), IN p_error_table VARCHAR(100), IN p_error_data VARCHAR(100))
BEGIN
set @error_code = p_error_code; # эти параметры можно потом где-то просмотреть (наверное, в следующем селекте в том же коннекшене)
set @error_table = p_error_table;
set @error_data = p_error_data;
insert into mysql_app_error values (null); # это поле у таблицы not null
END
Операторы
Перечисленные ниже операторы можно использовать только в хранимо коде (в коде процедур, функций, тирггеров).
if
if ( exists( select * from mw_enum_item where
enum_id = NEW.enum_id and enum_key=NEW.enum_key and p1=NEW.p1 and p2=NEW.p2 and p3=NEW.p3))
then
enum_id = null;
[elseif ...... then ..... ]
[else ...... ]
end if;
for
while
WHILE i < char_lenght(str) DO
insert into ttt(pole1) values (substring( str, i ));
end while;
repeat until
Полезные функции
nextHi
drop function if exists nextHi;
delimiter ;;
create function nextHi( hi varchar(100) )
returns varchar(100) DETERMINISTIC
begin
declare pos INT; # позиция последней точки в строке
declare i INT; # номер текущего чайлда
if hi is null then
return '01';
end if;
if position( "." in hi ) then
set pos = (select LENGTH(hi) - POSITION("." in REVERSE(hi))+1);
else
set pos = 0;
end if;
set i = (select CONVERT( SUBSTRING( hi, pos+2 ), UNSIGNED INTEGER)+1);
return concat( "", SUBSTRING( hi, 1, pos ), concat("", length(i)-1, i ));
end;;
delimiter ;
// вроде бы более правильный вариант:drop function if exists nextHi;
delimiter ;;
create function nextHi( hi varchar(100) )
returns varchar(100) DETERMINISTIC
begin
declare c varchar(100); # строковое представление текущего чайлда
declare i INT; # номер текущего чайлда
if hi is null then
return '01';
end if;
set c = substring_index(hi,'.',-1);
set i = substring(c,2)+1;
return concat( "", left( hi, length(hi)-length(c) ), length(i)-1, i );
end;;
delimiter ;
select nextHi2('01.03.04.115'), nextHi2(''), nextHi2('01'), nextHi2('09');
