= MySQL =

== Dynamic SQL , conditional where ==
{{{#!highlight sql
SELECT first_name, last_name, subsidiary_id, employee_id
  FROM employees
 WHERE ( subsidiary_id    = :sub_id OR :sub_id IS NULL )
   AND ( employee_id      = :emp_id OR :emp_id IS NULL )
   AND ( UPPER(last_name) = :name   OR :name   IS NULL )


select * from mysql.user
where (user=NULL or NULL is null) 
and (host=NULL or NULL is null)

select * from mysql.user
where (user='root' or 'root' is null) 
and (host=NULL or NULL is null)

select * from mysql.user
where (user='root' or 'root' is null) 
and (host='127.0.0.1' or '127.0.0.1' is null)
}}}

== MySql workbench Slackware64 14.1 ==
 * wget http://dev.mysql.com/get/Downloads/MySQLGUITools/mysql-workbench-community-6.1.7-1.el6.x86_64.rpm
 * cp ~/Downloads/mysql-workbench-community-6.1.7-1.el6.x86_64.rpm /tmp
 * cd /tmp
 * rpm2tgz mysql-workbench-community-6.1.7-1.el6.x86_64.rpm
 * installpkg mysql-workbench-community-6.1.7-1.el6.x86_64.tgz 

== mysqldump ==
 * mysqldump -h hostx -u userx -ppassx dbname > /tmp/dump.sql

== Error MariaDB 5.5 mysqldump ==
 * mysqldump: Couldn't execute 'SET OPTION SQL_QUOTE_SHOW_CREATE=1': Unknown system variable 'OPTION' (1193)
 * cp /usr/bin/mysqldump /tmp/msd
 * vim /tmp/msd
 * Escape, 
 * /SET OPTION
 * R
 * replace option with spaces
 * Escape
 * :wq

Use /tmp/msd -h hostx -u userx -ppassx dbname > /tmp/dump.sql

== Show users ==
{{{#!highlight sql
select host,user,password from mysql.user;
}}}

== Create function ==
{{{#!highlight sql
DELIMITER $$
DROP FUNCTION IF EXISTS hello;
CREATE FUNCTION hello() RETURNS TEXT LANGUAGE SQL
BEGIN
  RETURN 'Hello';
END;
$$
DELIMITER ;
-- test function
select hello();
}}}

== User creation and grants to select and execute function ==
{{{#!highlight sql
create user 'userx'@'%' identified by 'passwordx';
grant select on dbx.tablex to 'userx'@'%';
grant execute on function dbx.hello to 'userx'@'%';
}}}