Exercises, examples and other material relating to training module S157. This topic is presented on public course
|Articles and tips on this subject||updated|
|4481||Extracting data from backups to restore selected rows from MySQL tables|
We take backups from our live server, using mysqldump for the databases, and tar / gzip for most of the file systems. And when we had a server failure which meant a reload (and a new underlying OS too), we were able to be back online in a couple of hours, with minimal data loss.
But our backups are ...
|3270||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 ...
|158||MySQL - LEFT JOIN and RIGHT JOIN, INNER JOIN and OUTER JOIN|
In a database such as MySQL, data is divided into a series of tables (the "why" is beyond what I'm writing today) which are then connected together in SELECT commands to generate the output required. I find when I'm running MySQL training, people often get confused between all the join flavours. Let ...
|3061||Databases - why data is split into separate tables, and how to join them|
If you're travelling from Chippenham to London by train, you may want to grab a quick coffee at the station before you set off - and where better than Steamers? And if you were traveling to Plymouth, Bristol, Southampton, Llandrindod Wells or Cheltenham, you would use Steamers too.
Let's assume I'm ...
|2643||Relating tables with joins in MySQL|
Relational Databases are stores of data in which that data can be matched using common characteristics. For example, I have a table of data about programming languages (which includes a columns for the name of the language and the author) and another table of data about our courses which includes the ...
|2644||Counting rows in joined MySQL tables|
Yesterday, I showed you how to join two database tables and get out all matching records, and records which are "orphans" too - records in the left hand table that don't match anything in the right hand table, and vice versa. Yesterday's archive is [here] and our long standing tutorial on joins which ...
|2645||Optimising and caching your MySQL enquiries|
If you want to get a list of all the different values in a column, but don't care how many times each occurs, you can apply the DISTINCT keyword on your SELECT. See [here]. The DISTINCTROW is a synonym of DISTINCT, and the default is ALL which you may also state.
MySQL Caches queries - so that if ...
|2647||Removing duplicates from a MySQL table|
Surely there's got to be an easy way to remove duplicate records from a MySQL table? With a well designed table that has a unique primary key, you can simply get rid of a few duplicates, but if there are a lot of 'em, it's time consuming. You could write some sort of complex delete based on a select. ...
|2448||MySQL - efficiency and other topics|
Following on from the last two days of MySQL training, you'll see a number of blog entries covering stored procedures, securing a mysql server, and exploring database requests in PHP.
Further technical diagrams can be found here ... including how to make your select queries efficient, table locking, ...
|2259||Grouping rows for a summary report - MySQL and PHP|
It's quite quick and easy to write a loop that goes through a mysql result set and displays the content on an HTML page (but remember security of data, size of resulting HTML page, and the need to consider special characters such as <). But what if you want a shorter report, grouping records together ...
|2110||MySQL - looking for records in one table that do NOT correspond to records in another table|
Q: Can you give an example where I can retrieve all the records of a table "users" where there are no corresponding records in other table "user_role"
A: No (sorry - I'm too busy to set up tables of these names for a demo) but I DO have an example that show you.
We have a table of agents (realtors) ...
|1904||Ruby, Perl, Linux, MySQL - some training notes|
We have just come to the end of a solid 12 days of training ... and we are just getting ready for the next week, with delegates arriving this evening. There's a lot going on behind the scenes, even over this weekend, with the hotel to be prepared as well as the training course. (Picture - our clean ...
|1735||Finding words and work boundaries (MySQL, Perl, PHP)|
If you're searching for the word "mile", you probably don't want the page that tells you that Sally Smiled at Harry. But you may want to find a Milestone, even if it is within quotes.
Regular Expressions are your friends!
In Perl style regular expressions (which also work in Python, and in PHP with ...
|1574||Joining MySQL tables revisited - finding nonmatching records, etc|
A join lets me connect two tables one to the right of the other. Here's an example. First table - estate agents:
mysql> select agent,town,aid from agents;
| agent | town | aid |
|1331||MySQL joins revisited|
"Nobody can clear JOINS as this manual has done. It is SUPERB.. Thanks. It helped me a lot to understand. I will appreciate if you can make much clear to the INNER AND SELF JOINS."
in his review of a previous "Horse's Mouth" posting I made [here], which gets hundreds of hits every day, ...
|1235||Outputting numbers as words - MySQL with Perl or PHP|
Oracle's in_char function lets you convert numbers into a text string, but it's an unusual facility to have as as built-in; there's no such facility (as far as I know) in MySQL but you can get around this easily enough within your controlling application - the Nums2Words module on the CPAN (in Perl), ...
|1213||MySQL - the order of clauses and the order of actions|
You specify your select command in the following order:
1. The columns you want to choose (SELECT)
2. Where you want to send the results (INTO)
3. The tables that contain the data (FROM)
4. How you want to connect those tables (JOIN, ON)
5. Which individual rows you want to choose (WHERE)
6. Bunching ...
|581||Saving a MySQL query results to your local disc for Excel|
Do you want to run a database query on a remote server and save the results into a text file on your LOCAL disc - i.e. the disc of the computer at which you're seated and not the disc of the server that's in a remote location?
It's not as easy as you think it should be. Because of the security risks ...
|513||MySQL - JOIN or WHERE to link tables correctly?|
MySQL tables can be joined using two different syntaxes - one that simply lists the tables to be joined and then uses a where clause to select how the join is done, and the second using an explicit join keyword. Here's an example of both syntaxes in use, linking a table of estate agents (realtors) ...
|515||MySQL - an FAQ|
We're becoming a serious MySQL resource, with many technical articles, examples, forum posts and blog entries answering those difficult-to-resolve issues you may have come across. You might like to bookmark (or link to) this page.
Eleven recent articles on MySQL ...
Flashbulb moments - facts that ...
|517||An occasional chance, and reducing data to manageable levels|
If there's a 1 in 5 (20%) chance of it being a dry day at the moment, a 20% chance of it not being freezing cold, and a 20% chance of me having a day that I'm not training or otherwise involved, then it follows (since the conditions aren't related) that there's just a 1 in 125 or less that 1% chance ...
|567||Combining similar rows from a MySQL database select|
If you're selecting data from a MySQL database and you want to report back the total or average for a column, you can use a function such as avg or sum on the column. If you want to choose only certain records to include in your sum or average, add a WHERE clause and if you want to produce a separate ...
|591||Key facts - SQL and MySQL|
Some quick ways to remember your SQL; here are some "flashbulb moments" for newcomers and occasional users of MySQL and other SQL databases.
A database daemon is a process that looks after a number of DATABASES each of which comprises a number of TABLES each of which comprises data arranges in ROWS ...
|159||MySQL - Optimising Selects|
If you're going to be doing a complex select in MySQL, how should you formulate it to make it run efficiently?
This sounds like a simple question, but is actually very complex!
Firstly, consider creating indexes on any columns that are likely to be used in the selection of joining of records; choose ...
Some modules are available for download
as a sample of our material or under an Open Training Notes License
for free download from [here]
Left right and straight join.
Inner and outer joins.
Unique and regular indexes.
Multiple field indexes.
Join order and what to index.
Commit and Rollback.
If you are looking for a complete course and not just a information on a single subject, visit our Listing and schedule
Well House Consultants specialise in training courses in
. We run
throughout the UK (and beyond for longer courses), and
at our training centre in Melksham, Wiltshire, England.
It's surprisingly cost effective to come on our public courses -
even if you live in a different
country or continent to us
We have a technical library of over 700 books on the subjects on which we teach.
These books are available for reference at our training centre.