#Mysql learning Notes

How to connect to a mysql database from command line:

Important options while connecting:
-h : host
-u : username
-p: ask for passoword after pressing enter if don’t mention the password immediately after it without any spacing

mysql -h host -u user -p

or

mysql -h host -u user -pmypassword

If we add an space after -p then the program will think it as a database

we can also pass on the database name to use it:

mysq -h host -u user -p database_name

close mysql prompt

quit

We don’t need any semecolon after quit statement

Some basic mysql command and functions that you should know.
Shows the current user:

SELECT USER();

Shows the current time:

SELECT NOW(), CURRENT_TIMESTAMP(); 
SELECT CURRENT_TIME(), CURRENT_DATE();

Shows the current version:

SELECT VERSION();

SHOW LIST OF ALL DATABASES:

show databases;

Work on a specific database

use statement doesn’t need a semicolon at the end of the statement.

use test

Date Calaculation

Calaculate time difference

SELECT name, birth, CURDATE(), TIMESTAMPDIFF(YEAR, birth.CURDATE()) AS age FROM pet;

Get month / year/ day

SELECT name, birth FROM pet WHERE MONTH(birth) = 12

Inteval

SELECT CURDATE() + INTERVAL 1 DAY

Comparing something with null To compare something with null we need to use IS Keyword.

SELECT 1 IS NULL;

Results: false

Pattern Matching:

Select name that starts with B:

SELECT * FROM pet WHERE name LIKE 'B%';

Select name that ends with B:

SELECT * FROM pet WHERE name LIKE '%B';

Select name that contains B:

SELECT * FROM pet WHERE name LIKE '%B%';

Select all names with only 3 charachters:

SELECT * FROM pet WHERE name LIKE '___';

Select using regexp

SELECT * FROM pet WHERE REGEXP_LIKE(name, '^.{5}$`)

MANAGING USERS AND THIER PERMISSIONS

Grant access to a full database to a user: mysql GRANT ALL ON test.* TO 'username@host';