Contents
-
MySQL
- Connect mysql host
- Dynamic SQL , conditional where
- MySql workbench Slackware64 14.1
- mysqldump - backup database
- Error MariaDB 5.5 mysqldump
- Show users
- Create function
- User creation and grants to select and execute function
- Most used commands
- Trigger before insert to generate unique id
- Install on CentOS 6.6
- Create test table
- Install MariaDB debian buster
- Create view
- Explain
- Current date
MySQL
Connect mysql host
Toggle line numbers
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
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
Toggle line numbers
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
Toggle line numbers
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
Toggle line numbers
1 select host,user,password from mysql.user;
Create function
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 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
Toggle line numbers
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
Toggle line numbers
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
Toggle line numbers
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
Create view
Explain
The output from EXPLAIN shows ALL in the type column when MySQL uses a full table scan to resolve a query.
Toggle line numbers
1 explain select * from tablex;
Current date
Toggle line numbers
1 select DATE_FORMAT(NOW(),'%Y-%m-%dT%TZ');