Frequent SQL commands

This is my SQL cheat-sheet for MYSQL. Most of the commands are well known and popular. I don't like the idea of knowing by heart something that can be found easily, it's proofed, and to lazy to write it down instead of coping it.

Select

--select everything from a table
SELECT * FROM table_name

--select a column
SELECT column FROM table_name

--select with clause
SELECT * FROM table_name where id < 5

--select  only 10 records
SELECT * FROM table_name LIMIT 10

--select sorted by id from biggest to smallest
SELECT * FROM table_name ORDER BY id DESC

SELECT  NOW(),CURDATE(),CURTIME() from DUAL
--2017-07-05 17:21:08; 2017-07-05; 17:21:08

--add 15 days to date
SELECT DATE_ADD(t1.DATE, INTERVAL 15 DAY) from mydb.table t1

--get records that match today date
SELECT * FROM mydb.table t1 WHERE DATE(t1.date) = DATE(NOW())

Joins

--inner join
SELECT * FROM table1 t1 INNER JOIN table2 t2 ON  t1.key = t2.key

--left join
SELECT * FROM table1 t1 LEFT JOIN table2 t2 ON  t1.key = t2.key

--left join ( null )
SELECT * FROM table1 t1 LEFT JOIN table2 t2 ON  t1.key = t2.key WHERE t2.key is null

--right join
SELECT * FROM table1 t1 RIGHT JOIN table2 t2 ON  t1.key = t2.key

--right join ( null )
SELECT * FROM table1 t1 RIGHT JOIN table2 t2 ON  t1.key = t2.key WHERE t1.key is null

--outer join
--standard sql
SELECT * FROM table1 t1 FULL OUTER JOIN table2 t2 ON  t1.key = t2.key

--mysql version
SELECT * FROM table1 t1 LEFT OUTER JOIN table2 t2 ON  t1.key = t2.key
UNION
SELECT * FROM table1 t1 RIGHT OUTER JOIN table2 t2 ON  t1.key = t2.key


--outer join ( null )
--standard sql
SELECT * FROM table1 t1 FULL OUTER JOIN table2 t2 ON  t1.key = t2.key WHERE t1.key is null or t2.key is null

--mysql version
SELECT * FROM table1 t1 LEFT JOIN table2 t2 ON  t1.key = t2.key WHERE t2.key is null
union all
SELECT * FROM table1 t1 RIGHT JOIN table2 t2 ON  t1.key = t2.key WHERE t1.key is null

inner			left			left null			right			right null			Outer			Outer null
t1	t2		t1	t2		t1	t2		t1	t2		t1	t2		t1	t2		t1	t2
3	3		1			1			3	3			5		1			1
4	4		2			2			4	4			6		2			2
			3	3						5					3	3			5
			4	4						6					4	4			6
																5
																6

Self Join

SELECT distinct t2.key
FROM table1  t1
INNER JOIN 
 table1 t2 
 ON t1.key=t2.parent
WHERE t1.key = 1;

Create User

CREATE USER 'user'@'%' IDENTIFIED BY 'pass';
CREATE USER 'user'@'%' IDENTIFIED BY PASSWORD '*HASH';

mysql> select password('tida123');
+-------------------------------------------+
| password('tida123')                       |
+-------------------------------------------+
| *AF63CFEA4EC006E3490BBFB0FB81DC0AF2921348 |
+-------------------------------------------+

mysql> CREATE USER tida IDENTIFIED BY password '*AF63CFEA4EC006E3490BBFB0FB81DC0AF2921348';

Grant Provileges

--DBA user
GRANT ALL PRIVILEGES ON schema.* TO 'mydb'@'%' WITH GRANT OPTION;

--Normal user
grant all privileges on schema.* to mydb@localhost identified by 'pass';

--other
GRANT ALL ON *.* to user@localhost IDENTIFIED BY 'pass';
GRANT ALL ON *.* to user@'%' IDENTIFIED BY 'pass';

SHOW GRANTS FOR 'user'@'localhost';
SHOW GRANTS FOR 'user'@'%';

Remove Privileges

REVOKE ALL PRIVILEGES ON user.* FROM 'mydb'@'localhost';

REVOKE ALL PRIVILEGES ON *.*   FROM 'user'@'%';

REVOKE ALL PRIVILEGES ON `mydb`.*   FROM 'user'@'%';

Show Logs path

SHOW VARIABLES LIKE "general_log%";

Enable or disable logging

SET GLOBAL general_log = 'OFF';
SET GLOBAL general_log = 'ON';

Query against meta data

select * from mysql.user where User = 'user';