Home Accessibility Courses Twitter The Mouth Facebook Resources Site Map About Us Contact

Well House Consultants
You are on the site of Well House Consultants who provide Open Source Training Courses and business hotel accommodation. You are welcome to browse and use our resources subject to our copyright statement and to add in links from your pages to ours.
Other subject areas - resources
Java Resources
Well House Manor Resources
Perl Resources
Python Resources
PHP Resources
Object Orientation and General topics
MySQL Resources
Linux / LAMP / Tomcat Resources
Well House Consultants Resources
Extras Resources
C and C++ Resources
Ruby Resources
Tcl/Tk Resources
Web and Intranet Resources
MySQL module S157
More MySQL commands
Exercises, examples and other material relating to training module S157. This topic is presented on public course MySQL

Related technical and longer articles
Copying databases and tables in MySQL
MySQL joins - using left join and right join to find orphan rows
Left Joins to link three or more tables
When to denormalise your MySQL data
MySQL Extras
Solution Centre - all article listing
Solution Centre - all article listing

Articles and tips on this subjectupdated
4481Extracting 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 ...
3270SQL - 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 ...
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 ...
3061Databases - 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 ...
2643Relating 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 ...
2644Counting 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 ...
2645Optimising 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 ...
2647Removing 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. ...
2448MySQL - 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, ...
2259Grouping 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 ...
2110MySQL - 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) ...
1904Ruby, 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 ...
1735Finding 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 ...
1574Joining 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 | +--------------------------+------------------+-----+ | ...
1331MySQL joins revisited
Sameer writes: "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, ...
1235Outputting 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), ...
1213MySQL - 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 ...
581Saving 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 ...
513MySQL - 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) ...
515MySQL - 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 ...
517An 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 ...
567Combining 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 ...
591Key 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 ...
673Helicopter views and tartans
"Can we have more helicopter views?" asked a client from whom we're running a series of courses - by which they mean overviews that put all the elements of the technologies in place on a diagram - showing how tomcat relates to MySQL and how JBoss and Struts and EJBs fit in to the picture. For sure ...
Examples from our training material
agents   SQL statements for Agent and Property demo
agents.tab   Estate agent details - Trowbridge, Melksham, etc
homes.tab   Houses for sale - Trowbridge, Melksham, etc
perlit   6 tables ordering system model - perl program
rz   6 tables ordering system model - running output
seeder   Data for join v left join demonstration
shopper   6 tables ordering system model - Design and test in SQL
Background information
Some modules are available for download as a sample of our material or under an Open Training Notes License for free download from http://www.training-notes.co.uk.
Topics covered in this module
Complex joins.
Left right and straight join.
Inner and outer joins.
Unique and regular indexes.
Multiple field indexes.
Fulltext indexes.
Optimising queries.
Join order and what to index.
Selection efficiency.
Locking issues.
Commit and Rollback.
Complete learning
If you are looking for a complete course and not just a information on a single subject, visit our Listing and schedule page.

Well House Consultants specialise in training courses in Python, Perl, PHP, and MySQL. We run Private Courses throughout the UK (and beyond for longer courses), and Public Courses 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. Also available is the Opentalk Forum for discussion of technical questions.

You can Add a comment or ranking to this page

© WELL HOUSE CONSULTANTS LTD., 2015: Well House Manor • 48 Spa Road • Melksham, Wiltshire • United Kingdom • SN12 7NY
PH: 01144 1225 708225 • FAX: 01144 1225 899360 • EMAIL: info@wellho.net • WEB: http://www.wellho.net • SKYPE: wellho

PAGE: http://www.wellho.net/resources/S157.html • PAGE BUILT: Thu Sep 18 11:03:17 2014 • BUILD SYSTEM: WomanWithCat