SQL - Data v Metadata, and the various stages of data selection

MySQL is a system for managing data ... so it's natural for it to manage its own configuration data too in the same way. However, there are a few elements which have to be kept outside those internal tables - such as the data about which port number the daemon is to listen on, and the directory name (file path) for the databases to be stored.

There are defaults values built in to each of the MySQL processes which will be used in the absence of any other settings. But they are overridden as follows:

• The file /etc/my.cnf - a system wide file that contains settings for MySQL server and client programs for all user accounts

• Files ~username/.my.cnf - settings for all server and client programs on a "per user" basis. So there's (potentaiily) a file called .my.cnf in every user's home directory, containing instructions for all client and servers run by that user which will override any conflicing directives in /etc/my.cnf, and defaults.

• From the command line - specific MySQL settings can be made for individual processes, irrespective of defaults and overrides in /etc/mt.cnf and ~username/.my.cnf

Data v Metadata

Database engines look after data - and they also look after the structure of how that data is held. That's known as the Metadata. So the metadata is "how" and the data is "what".

The diagram shows the separation between data and metadata. As a comparison on this week's MySQL course, I equated the metadata to the cages and enclosures at a zoo, and the data to the animals themselves. SQL instructions too differ between those that run on the data and metadata ... to look at data, you'll use a SELECT, but to look at metadata, you'll use SHOW. And so it goes on.

Access to a database - authentication elements

Access to a database - authentication elements

MySQL databases are accessed through logins - unlike web servers which are often open to all (at least at a protocol level), users of MySQL need to establish credentials. Not only is there the traditional user name and password associated with an account (and the authorisation or denial of a request), but there's an additonal layer that can be used with is the remote location from which the connection is being made.

By using the remote location on database accounts, MySQL can be locked down to respond to requests only from the subnet on which the server islocated, or indeed purely from other processes on the very host on which it is running. Very often these restrictions will be backed up by firewall settings that would (in any case) prevent information on MySQL ports getting diretly to the database server - but of course it's better to have two levels of access control in place than none at all.

Trowbridge, inverted

Even when I'm running a course during the week, other things are going on too. Early on Thursday morning, I had to drop off some papers in our neighbouring town of Trowbridge. Looking briefly around Trowbridge (and carefully pushing the camera's shutter button when there was no traffic), I marvelled at some of the hidden beauty that there's to see in our area, even in towns which are not renowned for the tourist attraction and great beauty.

Stages in an SQL select statement

There are quite a few steps from data being extracted from database tables to it being returned to the user who made the SQL request, and each of the steps may be specified by a clause on a SELECT command.

• Tables are JOINED to link columns in one table to columns in another table - linking rows, if you like.

• Records are filtered with a WHERE clause, in which individual records that don't meet required criteria.

• If there's a need to link a whole series of rows into a single row, it's done by a GROUP clause. You may require, for example to know how many soldiers are based at a certain barracks rather than all their names!

• The HAVING clause allows for further records to be eliminated after they have been grouped - so that (following on from the previous example) we could eliminate all barracks with less than 20 soldiers based there.

• the remaining records are now SORTed into order

• and finally a LIMIT is applied if required so that only the first / top records are returned.

(written 2011-04-29) 20a2

Associated topics are indexed under

S154 - MySQL - Designing an SQL Database System
  [3494] Databases - when to treat the rules as guidelines - (2011-10-23)
  [3361] Blowing our own trumpet - MySQL resources - (2011-07-18)
  [2749] Delegate Question - defining MySQL table relationships as you create the tables - (2010-05-02)
  [2204] Images in a database? How big is a database? (MySQL) - (2009-05-28)
  [2085] MySQL - licensing issues, even with using the name - (2009-03-16)
  [2053] What a difference a MySQL Index made - (2009-02-25)
  [1771] More HowTo diagrams - MySQL, Tomcat and Java - (2008-08-24)
  [1575] Database design for a shopping application (MySQL) - (2008-03-15)
  [1423] MySQL - table design and initial testing example - (2007-11-06)
  [945] Code quality counts - (2006-11-26)
  [937] Display an image from a MySQL database in a web page via PHP - (2006-11-22)
  [918] Databases needn't be frightening, hard or expensive - (2006-11-08)
  [666] Database design - get it right from first principles - (2006-04-02)
  [515] MySQL - an FAQ - (2005-12-03)
  [494] MySQL - a score of things to remember - (2005-11-12)
  [375] Oops - I got my initial database design wrong - (2005-07-12)
  [361] Binary Large Objects or bars - (2005-06-27)
  [59] MySQL - Pivot tables - (2004-09-22)

S161 - Data Access and Security in MySQL
  [2647] Removing duplicates from a MySQL table - (2010-02-22)
  [2263] Mysqldump fails as a cron job - a work around - (2009-06-30)
  [1131] MySQL - Password security (authentication protocol) - (2007-04-02)
  [947] What is an SQL injection attack? - (2006-11-27)
  [647] Checking for MySQL errors - (2006-03-15)
  [535] MySQL permissions and privileges - (2005-12-20)
  [401] What is an SQL injection attack? - (2005-08-02)
  [193] The wrong MySQL - (2005-01-29)
  [192] Current MySQL and PHP paths and upgrades - (2005-01-28)

S157 - More MySQL commands
  [3061] Databases - why data is split into separate tables, and how to join them - (2010-11-20)
  [2645] Optimising and caching your MySQL enquiries - (2010-02-22)
  [2644] Counting rows in joined MySQL tables - (2010-02-22)
  [2643] Relating tables with joins in MySQL - (2010-02-21)
  [2448] MySQL - efficiency and other topics - (2009-10-10)
  [2259] Grouping rows for a summary report - MySQL and PHP - (2009-06-27)
  [2110] MySQL - looking for records in one table that do NOT correspond to records in another table - (2009-03-31)
  [1904] Ruby, Perl, Linux, MySQL - some training notes - (2008-11-23)
  [1735] Finding words and work boundaries (MySQL, Perl, PHP) - (2008-08-03)
  [1574] Joining MySQL tables revisited - finding nonmatching records, etc - (2008-03-15)
  [1331] MySQL joins revisited - (2007-09-03)
  [1235] Outputting numbers as words - MySQL with Perl or PHP - (2007-06-17)
  [1213] MySQL - the order of clauses and the order of actions - (2007-06-01)
  [673] Helicopter views and tartans - (2006-04-06)
  [591] Key facts - SQL and MySQL - (2006-02-04)
  [581] Saving a MySQL query results to your local disc for Excel - (2006-01-29)
  [572] Giving the researcher power over database analysis - (2006-01-22)
  [567] Combining similar rows from a MySQL database select - (2006-01-17)
  [517] An occasional chance, and reducing data to manageable levels - (2005-12-04)
  [513] MySQL - JOIN or WHERE to link tables correctly? - (2005-12-01)
  [502] SELECT in MySQL - choosing the rows you want - (2005-11-22)
  [449] Matching in MySQL - (2005-09-24)
  [279] Getting a list of unique values from a MySQL column - (2005-04-14)
  [159] MySQL - Optimising Selects - (2004-12-21)
  [158] MySQL - LEFT JOIN and RIGHT JOIN, INNER JOIN and OUTER JOIN - (2004-12-20)

5772

Back to
Files or Databases? MySQL, SQLite, or Oracle?

Previous and next
or
Horse's mouth home

Forward to
The importance of feedback

Some other Articles

Small scale improvement - big scale gain. And they CAN be done with local knowledge
Wanted - a look to the future
Melksham Car Parking - current charges and limits
The importance of feedback
SQL - Data v Metadata, and the various stages of data selection
Files or Databases? MySQL, SQLite, or Oracle?
Baby Pictures
Reybridge, Easter Monday
Easter on the Canal - near Melksham, Wiltshire
Alternative Vote (AV) - cutting the crap

4090 posts, page by page

Link to page ... 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82 at 50 posts per page

This is a page archived from The Horse's Mouth at http://www.wellho.net/horse/ - the diary and writings of Graham Ellis. Every attempt was made to provide current information at the time the page was written, but things do move forward in our business - new software releases, price changes, new techniques. Please check back via our main site for current courses, prices, versions, etc - any mention of a price in "The Horse's Mouth" cannot be taken as an offer to supply at that price.

Link to Ezine home page (for reading).
Link to Blogging home page (to add comments).

You can Add a comment or ranking to this page

Public Training Courses

Running regularly at our UK training Centre.
[Schedule] - [About] - [Book]

Like this? ??