Differences between revisions 1 and 16 (spanning 15 versions)

MySQL

Connect mysql host

   1 mysql -h localhost -u userx dbx -ppasswordx
   2 mysql -h localhost -u userx dbx -p # asks for password later
   3

Dynamic SQL , conditional where

   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

   1 select host,user,password from mysql.user;

Create function

   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();
  11 set sql_mode='ansi';
  12 show create function hello;

User creation and grants to select and execute function

   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

   1 help contents;
   2 help show;
   3 help create;
   4 show databases;
   5 use mysql; -- select database
   6 show tables;
   7 desc user; -- describe table user
   8 set sql_mode=ansi; -- set sql mode to ansi
   9 show create table user; -- show DDL to create table user
  10 show grants; -- show grants for current user
  11 show privileges; -- show privileges to be used in grants
  12

Trigger before insert to generate unique id

   1 CREATE TRIGGER T_BEFORE_INSERT_TABLEX BEFORE UPDATE ON dbx.TABLEX FOR EACH ROW SET NEW.UUID = uuid() ;

Install on CentOS 6.6

yum install mysql
yum install mysql-server
service mysqld start
mysql
create user 'userx'@'%' identified by 'passwordx';
grant all on test.* to 'userx'@'%';

Create test table

   1 CREATE TABLE MyGuests ( id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
   2 firstname VARCHAR(30) NOT NULL, lastname VARCHAR(30) NOT NULL, email VARCHAR(50), reg_date TIMESTAMP )
   3 
   4 insert into MyGuests (firstname,lastname,email,reg_date) values('AA','BB','xyz@test.com','2015-02-13T00:00:00');
   5 
   6 select * from MyGuests;

-  ⇤ ← Revision 1 as of 2014-07-29 00:21:26 → 
  Size: 298
  Editor: bl15-116-145
  Comment:
+   ← Revision 16 as of 2015-02-13 17:41:48 → ⇥
  Size: 3368
  Editor: static-wan-bl2-240-198-rev
  Comment:
-Deletions are marked like this.
+Additions are marked like this.
 Line 3:
-== Dynamic SQL where ==
{{{
+== Connect mysql host ==
{{{#!highlight bash
mysql -h localhost -u userx dbx -ppasswordx
mysql -h localhost -u userx dbx -p # asks for password later
}}}

== Dynamic SQL , conditional where ==
{{{#!highlight sql
-Line 10:
+Line 16:
+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)
-Line 11:
+Line 30:
+== 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();
set sql_mode='ansi';
show create function 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'@'%';
}}}

== Most used commands ==
{{{#!highlight sql
help contents;
help show;
help create;
show databases;
use mysql; -- select database
show tables;
desc user; -- describe table user
set sql_mode=ansi; -- set sql mode to ansi
show create table user; -- show DDL to create table user
show grants; -- show grants for current user
show privileges; -- show privileges to be used in grants
}}}

== Trigger before insert to generate unique id ==
{{{#!highlight sql
CREATE TRIGGER T_BEFORE_INSERT_TABLEX BEFORE UPDATE ON dbx.TABLEX FOR EACH ROW SET NEW.UUID = uuid() ;
}}}

== Install on CentOS 6.6 ==
 * yum install mysql
 * yum install mysql-server
 * service mysqld start
 * mysql
 * create user 'userx'@'%' identified by 'passwordx';
 * grant all on test.* to  'userx'@'%';

== Create test table ==
{{{#!highlight sql
CREATE TABLE MyGuests ( id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL, lastname VARCHAR(30) NOT NULL, email VARCHAR(50), reg_date TIMESTAMP )

insert into MyGuests (firstname,lastname,email,reg_date) values('AA','BB','xyz@test.com','2015-02-13T00:00:00');

select * from MyGuests;
}}}