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}
To list all the tables in that specific database –> 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!