MoinMoin Logo
  • Comments
  • Immutable Page
  • Menu
    • Navigation
    • RecentChanges
    • FindPage
    • Local Site Map
    • Help
    • HelpContents
    • HelpOnMoinWikiSyntax
    • Display
    • Attachments
    • Info
    • Raw Text
    • Print View
    • Edit
    • Load
    • Save
  • Login

Navigation

  • Start
  • Sitemap

Upload page content

You can upload content for the page named below. If you change the page name, you can also upload content for another page. If the page name is empty, we derive the page name from the file name.

File to load page content from
Page name
Comment

Revision 11 as of 2014-10-22 21:19:19
  • MySQL

MySQL

Dynamic SQL , conditional where

Toggle line numbers
   1 SELECT first_name, last_name, subsidiary_id, employee_id
   2   FROM employees
   3  WHERE ( subsidiary_id    = :sub_id OR :sub_id IS NULL )
   4    AND ( employee_id      = :emp_id OR :emp_id IS NULL )
   5    AND ( UPPER(last_name) = :name   OR :name   IS NULL )
   6 
   7 
   8 select * from mysql.user
   9 where (user=NULL or NULL is null) 
  10 and (host=NULL or NULL is null)
  11 
  12 select * from mysql.user
  13 where (user='root' or 'root' is null) 
  14 and (host=NULL or NULL is null)
  15 
  16 select * from mysql.user
  17 where (user='root' or 'root' is null) 
  18 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

Toggle line numbers
   1 select host,user,password from mysql.user;

Create function

Toggle line numbers
   1 DELIMITER $$
   2 DROP FUNCTION IF EXISTS hello;
   3 CREATE FUNCTION hello() RETURNS TEXT LANGUAGE SQL
   4 BEGIN
   5   RETURN 'Hello';
   6 END;
   7 $$
   8 DELIMITER ;
   9 -- test function
  10 select hello();

User creation and grants to select and execute function

Toggle line numbers
   1 create user 'userx'@'%' identified by 'passwordx';
   2 grant select on dbx.tablex to 'userx'@'%';
   3 grant execute on function dbx.hello to 'userx'@'%';

Most used commands

Toggle line numbers
   1 show databases;
   2 use mysql; -- select database
   3 show tables;
   4 desc user; -- describe table user
   5 set sql_mode=ansi; -- set sql mode to ansi
   6 show create table user; -- show DDL to create table user
   7 show grants; -- show grants for current user
   8 
  • MoinMoin Powered
  • Python Powered
  • GPL licensed
  • Valid HTML 4.01