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

   1 wget http://dev.mysql.com/get/Downloads/MySQLGUITools/mysql-workbench-community-6.1.7-1.el6.x86_64.rpm
   2 cp ~/Downloads/mysql-workbench-community-6.1.7-1.el6.x86_64.rpm /tmp
   3 cd /tmp
   4 rpm2tgz mysql-workbench-community-6.1.7-1.el6.x86_64.rpm
   5 installpkg mysql-workbench-community-6.1.7-1.el6.x86_64.tgz

mysqldump - backup database

   1 mysqldump -h hostx -u userx -ppassx dbname > /tmp/dump.sql
   2 mysqldump -h 127.0.0.1 -u userx -ppass -P 3306 --ssl db_exampleDB > backup_exampleDB_20210524.sql
   3 # restore DB 
   4 mysql -h 127.0.0.1 -u root -p???????? mysql < 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 
   2 BEFORE UPDATE ON dbx.TABLEX 
   3 FOR EACH ROW SET NEW.UUID = uuid() ;

Install on CentOS 6.6

   1 yum install mysql
   2 yum install mysql-server
   3 service mysqld start
   4 mysql
   5 create user 'userx'@'%' identified by 'passwordx';
   6 create user 'userx'@'localhost' identified by 'passwordx';
   7 grant all on test.* to  'userx'@'%';
   8 grant all on test.* to  'userx'@'localhost';
   9 show grants for 'userx'@'%';
  10 show grants for 'userx'@'localhost';

Create test table

   1 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 )
   2 insert into MyGuests (firstname,lastname,email,reg_date) values('AA','BB','xyz@test.com','2015-02-13T00:00:00');
   3 insert into MyGuests (firstname,lastname,email,reg_date) values('CC','DD','aaaaxyz@test.com','2015-02-13T14:00:00');
   4 select * from MyGuests;

Install MariaDB debian buster

Install

   1 apt-get update
   2 apt install mariadb-server
   3 mysql_secure_installation
   4 # define root pass
   5 su
   6 mysql
   7 GRANT ALL ON *.* TO 'admin'@'localhost' IDENTIFIED BY '????????' WITH GRANT OPTION;
   8 FLUSH PRIVILEGES;
   9 create database testdb;
  10 show databases;
  11 exit

SpringBoot JPA application.properties

spring.datasource.url=jdbc:mariadb://localhost:3306/testdb
spring.datasource.driverClassName=org.mariadb.jdbc.Driver
spring.datasource.username=admin
spring.datasource.password=????????
spring.jpa.database-platform=org.hibernate.dialect.MariaDBDialect
spring.jpa.hibernate.ddl-auto=create-drop
spring.jpa.show-sql=true

pom.xml

   1 <dependency>
   2         <groupId>org.mariadb.jdbc</groupId>
   3         <artifactId>mariadb-java-client</artifactId>
   4         <version>1.4.4</version>
   5 </dependency>

Create view

   1 create or replace view v_test as 
   2   select fielda, fieldb  
   3   from v_test2 where fieldId = tablex.fieldId
   4   union
   5   select fielda, fieldb  
   6   from v_test3 where fieldId = tablez.fieldId;

Explain

https://dev.mysql.com/doc/refman/8.0/en/table-scan-avoidance.html

The output from EXPLAIN shows ALL in the type column when MySQL uses a full table scan to resolve a query.

   1 explain select * from tablex;

Current date

   1 select DATE_FORMAT(NOW(),'%Y-%m-%dT%TZ');

-  ⇤ ← Revision 13 as of 2014-10-22 21:30:26 → 
  Size: 2602
  Editor: 73
  Comment:
+   ← Revision 34 as of 2024-03-14 22:50:09 → ⇥
  Size: 5399
  Editor: vitor
  Comment:
-Deletions are marked like this.
+Additions are marked like this.
 Line 1:
+<<TableOfContents(2)>>
-Line 32:
+Line 34:
- * 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
+{{{#!highlight bash
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 
}}}
-Line 38:
+Line 42:
-== mysqldump ==
 * mysqldump -h hostx -u userx -ppassx dbname > /tmp/dump.sql
+== mysqldump - backup database ==
{{{#!highlight bash
mysqldump -h hostx -u userx -ppassx dbname > /tmp/dump.sql
mysqldump -h 127.0.0.1 -u userx -ppass -P 3306 --ssl db_exampleDB > backup_exampleDB_20210524.sql
# restore DB 
mysql -h 127.0.0.1 -u root -p???????? mysql < dump.sql
}}}
-Line 96:
+Line 105:
+== 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 ==
{{{#!highlight bash
yum install mysql
yum install mysql-server
service mysqld start
mysql
create user 'userx'@'%' identified by 'passwordx';
create user 'userx'@'localhost' identified by 'passwordx';
grant all on test.* to  'userx'@'%';
grant all on test.* to  'userx'@'localhost';
show grants for 'userx'@'%';
show grants for 'userx'@'localhost';
}}}

== 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');
insert into MyGuests (firstname,lastname,email,reg_date) values('CC','DD','aaaaxyz@test.com','2015-02-13T14:00:00');
select * from MyGuests;
}}}

== Install MariaDB debian buster ==
=== Install ===
{{{#!highlight bash
apt-get update
apt install mariadb-server
mysql_secure_installation
# define root pass
su
mysql
GRANT ALL ON *.* TO 'admin'@'localhost' IDENTIFIED BY '????????' WITH GRANT OPTION;
FLUSH PRIVILEGES;
create database testdb;
show databases;
exit
}}}

=== SpringBoot JPA application.properties ===
{{{
spring.datasource.url=jdbc:mariadb://localhost:3306/testdb
spring.datasource.driverClassName=org.mariadb.jdbc.Driver
spring.datasource.username=admin
spring.datasource.password=????????
spring.jpa.database-platform=org.hibernate.dialect.MariaDBDialect
spring.jpa.hibernate.ddl-auto=create-drop
spring.jpa.show-sql=true
}}}

=== pom.xml ===
{{{#!highlight xml
<dependency>
 <groupId>org.mariadb.jdbc</groupId>
 <artifactId>mariadb-java-client</artifactId>
 <version>1.4.4</version>
</dependency>
}}}

== Create view ==
{{{#!highlight sql
create or replace view v_test as 
  select fielda, fieldb  
  from v_test2 where fieldId = tablex.fieldId
  union
  select fielda, fieldb  
  from v_test3 where fieldId = tablez.fieldId;
}}}

== Explain ==
 * https://dev.mysql.com/doc/refman/8.0/en/table-scan-avoidance.html
The output from EXPLAIN shows ALL in the type column when MySQL uses a full table scan to resolve a query.
{{{#!highlight sql
explain select * from tablex;
}}}
== Current date ==
{{{#!highlight sql
select DATE_FORMAT(NOW(),'%Y-%m-%dT%TZ');
}}}