This gist covers various small chunks of MySQL commands which might be useful later... you know somewhere!

MySQL_small_snippets.md

This time, I am gonna write the small, new things which I learnt before but by the time forgot them. Following the course on MySQL I will be noting down the stuff. As you might have noticed it is in the gist format, so the notes will not be much comprehensive, but they will be in such a form that you can refer back again. Like a cheat sheet!

See which database you are in?

SELECT database();

See columns of a table with query!

SHOW COLUMNS FROM table_name;

-- or

DESC table_name;

When get warning after a query run, see warnings like this.

SHOW WARNINGS;

NOTE: This ↑ will only work immediately after the warning come out.

C.R.U.D.: Create, Read, Update, Delete.

Concat automatically with the symbol

-- INSTEAD of this boring old ↓
SELECT
  CONCAT(fname, ' ', lname, ' ', age)
FROM ...

-- Use CONCAT_WS (With Seperator)
SELECT
  CONCAT_WS(' ', fname, lname, age)
FROM ...

Really cool! (which is like ' '.join([...]))

The -1 also works in MySQL for substring!

SELECT SUBSTRING('Aayush Shah', -4)

Returns → 'Shah'

Refer the selected columns by number!

SELECT col_1, col_2, col_3
FROM table
ORDER BY 2; 
-- ↑ is equivalent to ORDER BY col_2

Slice the index!

SELECT *
FROM table
LIMIT 3, 5

Here LIMIT from_where, howmany_from_where does the indexing for us. The index starts with 0.

Check this cool group by USE CASE out: This my own gist

Write IF in the query

SELECT 
IF(condition, 'THEN THIS', 'IF NOT THIS') AS nickname
FROM table;

Simple and easy. Just a one liner.