Jumpshot! MySQL Query Basics / Views / Table Joins.

I personally found basic SQL queries pretty simple.  As soon as I left the safety of 'SELECT * FROM <database>.<table> it became very confusing - quickly.

When you are trying to master a new, confusing or intricate subject it is really important to practice, practice, practice! In other words to get good at SQL queries like bicycle riding do lots of bike riding and SQL queries, and make small incremental improvements!

Basic Query:

select * from mydatabase.mytable;

This will select all columns from the database: mydatabase from the table: my table

Basic Query Selecting Only Select Columns:

select name, age, dob from mydatabase.mytable;

This will select only the columns (name, age, dob) from database: mydatabase table: mytable;

• mysql will accept SELECT, or select.  Usually SQL writers will take the time to capitalize the commands so they differentiate from the tables, columns and databases.

Basic Query Selection With Filter Numeric:

select name, age, dob from mydatabase.mytable where age > 45;
# Alternately you may drop the database most often.
select name, age, dob from mytable where age > 45;

Basic Query Selection With String Filter:

• Note: SQL wildcard is the % (not the * like DOS!)

select name, age, dob from mytable where name like '%bob%';
# Another two part conditional 
select name, age, dob from mytable where name like '%bob%' or name like '%sue%';

This will return the three columns (name, age, dob)  if column name contains 'bob' or 'sue' anywhere in the column body.

Table Joins

• Table joins can be confusing, but as soon as you get it - it's pretty simple.
• Note each part of the diagram will make up a section of the SQL query and together it will give you a (long) but powerful query!
• Complex queries can take some time to run!

Part 1: List of Columns From All Tables:

SELECT table_a.col_a, table_a.col_b, table_a.col_c, table_x.col_x

• Please note you list ALL your columns from all your tables here so it can make a long query.  Where I was confused is I thought I listed just the first tables columns - but you have a potentially long list from all tables.

Part 2: Inner Join

• Let's just master a basic inner join for now

FROM table_a inner JOIN table_b

• This is effectively saying that 'we are pulling data from table_a and table_b

Part 3: On Selection

• This is where you specify an equality between two tables.  Instances can be table_a.age = table_b.age
• Equality in sql is a single =  (not == like C and other languages)

ON table_a.age = table_b.age

Part 4: Conditionals

• We can put filtering here, for example:

WHERE table_b.age > 45 AND table_a.age > 45;

Putting it all together:

SELECT table_a.col_a, table_a.col_b, table_a.col_c, table_x.col_x FROM table_a INNER JOIN table_b ON table_a.age = table_b.age WHERE table_b.age > 45 AND table_a.age > 45;

Paraphasing it.

• We want 4 columns (three from table_a and one from table_x)
• from table_a inner join table_b  (These are the two tables we are drawing from)
• on table-a.age = table_b.age  (This is the condition that must match for the formation of the new columns.
• where condition finally we filter the results so that the ages from both tables must be > 45.

Views:

• A View is a Virtual Table

Amazingly all one needs to do is add:

CREATE VIEW myview AS  [... rest of sql statement]

CREATE VIEW myview AS [SELECT table_a.col_a, table_a.col_b, table_a.col_c, table_x.col_x FROM table_a INNER JOIN table_b ON table_a.age = table_b.age WHERE table_b.age > 45 AND table_a.age > 45;]
# Take off the [ and ] they are just to demarac the above.

• Once a view is created you cannot call a 'create view twice.'  

To drop a view:

DROP VIEW IF EXISTS [target_view]

Compounding:

One then can see that they can make a pile of views which are effectively virtual tables, but layers of filtering and selection can be added.  

FROM VIEW myview SELECT table_a.col_a WHERE table_a.age > 60;

More Information for Joins Snapshotting from: https://ubiq.co/database-blog/update-view-mysql/