MySQL
Connect mysql host
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 - backup database
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
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
User creation and grants to select and execute function
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';
- 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
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
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