Useful Code Snippets / Tutorials – MySQL

MySQL Tools

• phpMyAdmin – without a doubt the leading script for managing your MySQL databases. It can do almost anything you want and more.
• automysqlbackup.sh – A script to take daily, weekly and monthly backups of your MySQL databases.
• BigDump: Staggered MySQL Dump Importer – a script to stagger the import of large and very large MySQL Dumps to avoid problems with web-servers that impose hard runtime limits and those in safe mode. The script executes only a small part of the huge dump and restarts itself. The next session starts where the last was stopped.

MySQL Tips, SQL Code, Etc.

• How can I backup a MySQL database?
  You can manually backup your MySQL database by using phpMyAdmin. To automate the backup process, try using AutoMySQLBackup. To backup via a shell command line, do this:

  mysqldump -u username -p dbname > dumpname.sql

  You will be prompted for your password and the command will create a file called dumpname.sql. If you’re not doing this locally then you want to do this:

  mysqldump -h source.host.name -u username -p dbname > dumpname.sql

  Now, to put this back into a database, do this:

  mysql -u username -p dbname < dumpname.sql <

  And in case you’re putting this in a new database, first create one like this:

  mysqladmin -u username -p newdbname

  And remember, if you’re not doing this locally, add the -h stuff.

• Add some text to an entry (row, field):

  UPDATE table SET colname=CONCAT('text to add',colname) WHERE ___

• Select based on a field that can have a NULL value

  SELECT * FROM table WHERE not isnull(field)

• Display the content of a field and the count (# rows) for each distinct entry

  SELECT field, count(*) FROM table WHERE field='something'

• How do I read the last record and only the last record from a table?
  This should work if you have an auto increment id for each record.

  SELECT * FROM table ORDER BY id DESC LIMIT 1

  if you have an id field… try this …

  SELECT MAX(id) from table

• How can I get the number of occurences?

  SELECT field,
COUNT(field) AS NumOccurrences
FROM table
WHERE field='value'
GROUP BY field
HAVING ( COUNT(field) > 0 )

  Note: The field in the WHERE clause can be the same as the SELECT clause or different

• How can I get the number of distinct values in a table?

  SELECT count(id field) AS num, field FROM table GROUP BY field;

  or

  SELECT COUNT(DISTINCT field) FROM table WHERE 1;

  or

  select COUNT(*) field FROM (SELECT DISTINCT table) WHERE 1;

  REF: Stack Overflow

• How can I find all duplicates in a database column?
  Depending on your SQL syntax, something like this might work:
  SELECT count(*), colname from table GROUP by colname HAVING count(*) > 1
  or similarly,
  SELECT field, COUNT(field) AS NumOccurrences FROM table GROUP BY field HAVING ( COUNT(field) > 1 )
  or if trying to find a duplicate combination of multiple columns,
  SELECT col1, col2, col3, count(col3) FROM table GROUP BY col1, col2, col3 HAVING COUNT(col3)>1
• SQL Joins
  The cornerstone of the relational database system is its multiple tables and the ability to associate those tables with one another so you can combine data from different but related tables. Most of the time these associations are intuitive and work just as you expect. However, problems can arise when one of these associations returns unexpected results. In other words, the records you get aren’t the records you know you should be seeing. Fortunately, you can dynamically relate different tables by applying what’s known as a join. Technically, a join is the operation used when selecting data to create these relationships at retrieval time. What that means to you is that a join determines which records are selected and which aren’t. The article Getting the Right Data with SQL Joins will introduce you to several types of joins supported by SQL and show you how to correctly apply them to get the data you need. I recommend you read the article, but if you are just looking to refresh your memory of the basic syntax, below is what it would look like for a three-table join:

  SELECT __ FROM table1 join table2
ON table1.primarykey = table2.foreignkey join table3
ON table2.primarykey = table3.foreignkey

  The first join applies to table1 and table2, the second join applies to table2 and table3. You might hear table2 referred to as the intermediate or translation table because it’s the connection table for the other two tables. In a really complex statement, you might have several intermediate tables. Continue to add joins in this manner until your statement includes all the relationships and tables necessary to get just the right results.

  REF: What’s the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN? and What is the difference between Left, Right, Outer and Inner Joins?

MySQL Tools

• phpMyAdmin – without a doubt the leading script for managing your MySQL databases. It can do almost anything you want and more.
• automysqlbackup.sh – A script to take daily, weekly and monthly backups of your MySQL databases.
• BigDump: Staggered MySQL Dump Importer – a script to stagger the import of large and very large MySQL Dumps to avoid problems with web-servers that impose hard runtime limits and those in safe mode. The script executes only a small part of the huge dump and restarts itself. The next session starts where the last was stopped.

Like this content? Why not share it?