I am at the moment learning a lot about MySQL by reading the book, Learning MySQL and I thought providing a list of queries could help beginners like myself.

Different ways to select databases, tables, rows and columns in MySQL

Do note, I will be updating the list occasionally. So if this list seems incomplete, you may either wait, provide suggestions per email or simply comment down below.

 

Just basics here;

listing out your databases  –> SHOW DATABASES(DB);

To focus on a specific database –> USE {DB_NAME}

specify database; select a database

To list all the tables in that specific database –> SHOW TABLES;

Show tables

Display the “column headers” of a table and their properties e.g. default value, type etc. –> SHOW COLUMNS FROM {TABLE(TB)_NAME}

Okay now onto SELECT Queries

To list all rows in a table e.g. job, showing ALL columns  –> SELECT * FROM {TB_NAME} the “*” is (I read somewhere) a wild card.

To list all rows of a table showing SOME columns –> SELECT {COLUMN_NAME}, {COLUMN_NAME2} FROM {TB_NAME}

the LIMIT indicates the number of rows to be selected /displayed

To select rows from a table in ANOTHER database –> SELECT * FROM {DB_NAME.TB_NAME}

Using operators to filter data –> SELECT * FROM {TB_NAME} WHERE {COLUMN_NAME} < or > or = or <> {VALUE} 

My favorite–> using LIKE

with like, you can filter rows based on column values that  fit the given criteria.

For instance, if I want only people with firstnames starting with s –> SELECT * FROM {TB_NAME} WHERE {COL_NAME} LIKE “s%”

what about ending with s ?SELECT * FROM {TB_NAME} WHERE {COL_NAME} LIKE “%s”

or just contains a given string ?  SELECT * FROM {TB_NAME} WHERE {COL_NAME} LIKE “%sus%”

AND and OR allows more conditions or for more selectivity

NOT filters out the rows that do not meet the next conditions

 

I update this list soon! SEE YA!

These might interest you:
%d bloggers like this: