Thursday, 12 March 2020

A Colourful Way to Understand MySQL Queries

Image: 4 levels in MySQL Queries

Note: The examples here are from NetApp OnCommand Workflow Information.

To the uninitiated, MySQL queries can look confusing. Really, they are quite simple. Essentially there are four levels of data.

Level 1 = Database
Level 2 = Table / Table from a table
Level 3 = Column Heading / Returned Column Heading
Level 4 = Cell Data

The following require specific values (i.e. the Database must exist, the Table must exist, the Column Heading must exist, the Cell Data must exist):
Database Table Column Heading Cell Data

These two you can make up names in the SQL Query to suit your purpose (i.e. if you want to use Banana, that’s fine):
Table from a table Returned Column Heading

To demonstrate these, we have a couple of examples, one basic, one slightly advanced.

Basic Sample MySQL Query

In the following example:
We want to return a table of information from the vserver table with contents of the `name` column only, and we rename the name column to SVM.
We’re looking at the database cm_storage and table vserver.
We only want to return rows in the table where the vserver table `type` column has cell contents 'data'.
... and where the vserver table `admin_state` column has cell contents 'running'.

SELECT
    vserver.`name` AS SVM
FROM
    cm_storage.vserver
WHERE
    vserver.`type` = 'data'
    AND vserver.`admin_state` = 'running'

And below is the result of running this query.

Image: Basic Sample MySQL Query: Result

Advanced Sample MySQL Query

In the following example:

We want to return a table of information with...
... from the anno1 table-from-a-table the column `value` under column heading as 'Alias'
... from the vserver table the column `name` under column heading 'SVM'
... from the anno2 table-from-a-table the column `value` under column heading 'Type'

We’re looking at the database cm_storage and table vserver.

Since the above table does not have all the information we want, we do some joins...

JOIN 1) We create the table vs_anno1 from the table cm_storage.vserver_annotation
And match the table vserver column id with table vs_anno1 column vserver_id.

Image: To help visualize the above. For vserver.id = ‘7’ we have 4 rows of vserver.annotation where vserver_id = ‘7’.

JOIN 2) We create the table anno1 from the table cm_storage.annotation
And match the table vs_anno1 column annotation_id with table anno1 column id.

Image: To help visualize the above. We have 4 rows where vserver_annotation has annotation_id 2942, and annotation.id 2942 gives one row.
JOIN 3 & 4) Rinse and repeat of the above.

We only want to return information rows from the cm_storage.vserver table where...
... from table anno1 column `name` there is an 'Alias' (vserver has an annotation called Alias)
... from table vserver column `type` = 'data'
... from table anno2 column `name` there is 'DataProtectionType' (vserver has an annotation called DataProtectionType)
... from table anno2 column `value` (for DataProtectionType) = 'Primary'
... from table vserver column admin_state = 'running'.

Finally order by column 1.

SELECT
    anno1.`value` AS 'Alias',
    vserver.`name` AS 'SVM',
    anno2.`value` AS 'Type'
FROM
    cm_storage.vserver
JOIN
    cm_storage.vserver_annotation vs_anno1
    ON vserver.id = vs_anno1.vserver_id
JOIN
    cm_storage.annotation anno1
    ON vs_anno1.annotation_id = anno1.id
JOIN
    cm_storage.vserver_annotation vs_anno2
    ON vserver.id = vs_anno2.vserver_id
JOIN
    cm_storage.annotation anno2
    ON vs_anno2.annotation_id = anno2.id
WHERE
    anno1.`name` = 'Alias'
    AND vserver.`type` = 'data'
    AND anno2.`name` = 'DataProtectionType'
    AND anno2.`value` = 'Primary'
    AND vserver.admin_state = 'running'
ORDER BY
1

Image: Advanced Sample MySQL Query: Result

Conclusion

Hopefully the samples above will help you to understand the MySQL queries you encounter a little better. Understanding the levels, and then accessing the database tables to see the available fields, should lead to a knowledge of how the MySQL query works, and what is it looking for.

No comments:

Post a Comment