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.`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.
Comments
Post a Comment