MySQL is a relational database management system (RDBMS) that runs as a server providing multi-user access to a number of databases. MySQL is primarily an RDBMS and therefore ships with no graphic user interface (GUI) tools to administer the databases or manage the data inside it.

Users can readily use the included command-line tools or download MySQL frontends from various parties that have developed software and web applications to manage MySQL database. An administrator GUI pack also exists on the official MySQL website which may require extra java modules for connectivity.

This article assumes that you already have an idea on what MySQL databases are and how to use them. Please make sure that you already have a working backup copy of your current database when using the following tips.

When moving data between two tables which you already have structures intact, you can do them using Insert / Select pair.

INSERT INTO `userrecords` (`id`, `name`, `email`,  `password`, `refferer`, `joindate`) SELECT `memberid`, `username`, `useremail`, `userpass`, `userrefferer`, SYSDATE() FROM `db.members`;

You can display the process list easily but it will only show all process that you have access for view and you can only kill them if you own them or you have access for KILL.

SHOW PROCESSLIST;

When connections or queries hangs up or starting to consume a large amount of memory or processor resource, it is always wise to kill (stop) the process.

The syntax to kill the process was KILL #processid (replace the #processid with the actual ID returned by SHOW PROCESSLIST;)

KILL #processid;

You can easily get the total points for a given student pulled from a score table and rank the students total score either ascending or descending. Most people are having a hard time on getting the totals from a score table using 1 query. The example given was displaying the result on descending order.

SELECT SUM(`resultpoints`) AS `sumpoints`, `studentid` FROM `results` GROUP BY `studentid` ORDER BY `sumpoints` DESC

On some cases we only want to know how many points was recorded for a certain person. We can’t always deny the fact that when dealing with score tables we are also dealing with mean, median and averages. The proper query to get the count of scores was almost the same except this time we are going to use COUNT command instead of SUM.

SELECT COUNT(`resultpoints`) AS `countpoints`, `studentid` FROM `results` GROUP BY `studentid` ORDER BY `countpoints` DESC

We can also get the sum and the number of scores in a single query.

SELECT SUM(`resultpoints`) AS `sumpoints`, COUNT(`resultpoints`) AS `countpoints`, `studentid` FROM `results` GROUP BY `studentid` ORDER BY `countpoints` DESC

The article says shortcut, there must be a lazy man approach if we want to get an average and the answer is a big yes. With one query we can easily pull the average, the sum and the total items and arrange them.

SELECT SUM(`resultpoints`) AS `sumpoints`, COUNT(`resultpoints`) AS `countpoints`, (SUM(`resultpoints`)/COUNT(`resultpoints`)) AS `average`, `studentid` FROM `results` WHERE `countpoints` > 0  GROUP BY `studentid` ORDER BY ` average` DESC

A condition was added to exclude those records that have 0 score counts (no scores recorded) to avoid division by zero.