Things to Know about MySQL
Mike Chirico (mchirico@users.sourceforge.net)
Copyright (GPL) 2004
Last Updated: Fri May 7 11:00:23 EDT 2004
The latest version of this document can be found at:
http://prdownloads.sourceforge.net/souptonuts/README_mysql.txt?download
TIP 1:
Find out who is doing what, and kill the process if needed. This example kills
Id 657
mysql> show processlist;
show processlist;
+-----+------+-----------+---------+---------+-------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+------+-----------+---------+---------+-------+-------+------------------+
| 657 | prog | localhost | weather | Sleep | 28619 | | NULL |
| 782 | prog | localhost | weather | Sleep | 853 | | NULL |
| 785 | prog | localhost | NULL | Query | 0 | NULL | show processlist |
+-----+------+-----------+---------+---------+-------+-------+------------------+
3 rows in set (0.00 sec)
mysql>kill 657
Or, from the command line, to kill process 782
[root@third-fl-71 mysql]# mysqladmin processlist
+-----+------+-----------+---------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+------+-----------+---------+---------+------+-------+------------------+
| 782 | prog | localhost | weather | Sleep | 2676 | | |
| 785 | prog | localhost | | Sleep | 1823 | | |
| 793 | root | localhost | | Query | 0 | | show processlist |
+-----+------+-----------+---------+---------+------+-------+------------------+
[root@third-fl-71 mysql]#
[root@third-fl-71 mysql]# mysqladmin kill 782
Note, the following can also be helpful
mysql> show status;
or
mysql> show status\G
also
mysql> show innodb status;
TIP 2:
Clean up binary log files. For a default install they may be in
/usr/local/var/
with names ending in -bin.000001,-bin.000002,..
mysql> reset master;
reset master;
Query OK, 0 rows affected (0.02 sec)
TIP 3:
Can the order of the columns in a create statement make a difference? YES
create table t (
a int,
b int,
timeUpdate timestamp,
timeEnter timestamp );
The first timestamp will always be the "automatically generated" time. So
if the record is updated, or inserted, this time gets changed. If the
order is changed, "timeEnter" is before "timeUpdate", then, "timeEnter"
would get updated. First timestamp column updates automatically.
Note, in the table above timeEnter will only get updated if passed a null
value.
insert into t (a,b,timeEnter) values (1,2,NULL);
Hints: Need mm-dd-yyyy hh:mm:ss format?
select a,b,DATE_FORMAT(timeUpdate,'%m-%d-%Y %T'),DATE_FORMAT(timeEnter,'%m-%d-%Y %T') from t;
+------+------+---------------------------------------+--------------------------------------+
| a | b | DATE_FORMAT(timeUpdate,'%m-%d-%Y %T') | DATE_FORMAT(timeEnter,'%m-%d-%Y %T') |
+------+------+---------------------------------------+--------------------------------------+
| 3 | 2 | 04-15-2004 19:14:36 | 04-15-2004 19:15:07 |
| 3 | 2 | 04-15-2004 19:14:39 | 04-15-2004 19:15:07 |
| 5 | 5 | 00-00-0000 00:00:00 | 04-15-2004 19:15:53 |
| 1 | 2 | 00-00-0000 00:00:00 | 04-15-2004 19:20:15 |
+------+------+---------------------------------------+--------------------------------------+
4 rows in set (0.00 sec)
TIP 4:
Connect, create table and select with Perl (Linux). First the DBI module is needed, which
can be installed from the system prompt as follows:
# perl -MCPAN -e shell
cpan> install DBI
cpan> install DBD::mysql
The following is an example program:
#! /usr/bin/perl -w
# Copyright (GPL) Mike Chirico mchirico@users.sourceforge.net
#
# Program does the following:
# o connects to mysql
# o creates perlTest if it doesn't exist
# o inserts records
# o selects and displays records
#
# This program assumes DBI
#
# perl -MCPAN -e shell
# cpan> install DBI
# cpan> install DBD::mysql
#
#
#
#
#
use strict;
use DBI;
# You will need to change the following:
# o database
# o user
# o password
my $database="yourdatabase";
my $user="user1";
my $passwd="hidden";
my $count = 0;
my $tblcreate= "
CREATE TABLE IF NOT EXISTS perlTest (
pkey int(11) NOT NULL auto_increment,
a int,
b int,
c int,
timeEnter timestamp(14),
PRIMARY KEY (pkey)
) ";
my $insert= "
insert into perlTest (a,b,c)
values (1,2,3),(4,5,6),(7,8,9)";
my $select="
select a,b,c from perlTest ";
my $dsn = "DBI:mysql:host=localhost;database=${database}";
my $dbh = DBI->connect ($dsn, $user, $passwd)
or die "Cannot connect to server\n";
my $s = $dbh->prepare($tblcreate);
$s->execute();
$s = $dbh->prepare($insert);
$s->execute();
$s = $dbh->prepare($select);
$s->execute();
while(my @val = $s->fetchrow_array())
{
print " $val[0] $val[1] $val[2]\n";
++$count;
}
$s->finish();
$dbh->disconnect ( );
exit (0);
TIP 5:
Remove duplicate entires. Assume the following table and data.
CREATE TABLE IF NOT EXISTS dupTest (
pkey int(11) NOT NULL auto_increment,
a int,
b int,
c int,
timeEnter timestamp(14),
PRIMARY KEY (pkey)
);
insert into dupTest (a,b,c) values (1,2,3),(1,2,3),
(1,5,4),(1,6,4);
mysql> select * from dupTest;
select * from dupTest;
+------+------+------+------+---------------------+
| pkey | a | b | c | timeEnter |
+------+------+------+------+---------------------+
| 1 | 1 | 2 | 3 | 2004-04-16 10:55:35 |
| 2 | 1 | 2 | 3 | 2004-04-16 10:55:35 |
| 3 | 1 | 5 | 4 | 2004-04-16 10:55:35 |
| 4 | 1 | 6 | 4 | 2004-04-16 10:55:35 |
+------+------+------+------+---------------------+
4 rows in set (0.00 sec)
mysql>
Note, the first two rows contains duplicates in columns a and b. It contains
other duplicates; but, leave the other duplicates alone.
mysql> ALTER IGNORE TABLE dupTest ADD UNIQUE INDEX(a,b);
mysql> select * from dupTest;
select * from dupTest;
+------+------+------+------+---------------------+
| pkey | a | b | c | timeEnter |
+------+------+------+------+---------------------+
| 1 | 1 | 2 | 3 | 2004-04-16 11:11:42 |
| 3 | 1 | 5 | 4 | 2004-04-16 11:11:42 |
| 4 | 1 | 6 | 4 | 2004-04-16 11:11:42 |
+------+------+------+------+---------------------+
3 rows in set (0.00 sec)
TIP 6:
Show status information on a table. Note, if the database was started
with --safe-show-database or --skip-show-database some of these commands
may not work. Note the "\G" option my provide a nicer format.
Show the create statement:
mysql> show create table dupTest\G
show create table dupTest\G
*************************** 1. row ***************************
Table: dupTest
Create Table: CREATE TABLE `dupTest` (
`pkey` int(11) NOT NULL auto_increment,
`a` int(11) default NULL,
`b` int(11) default NULL,
`c` int(11) default NULL,
`timeEnter` timestamp NOT NULL,
PRIMARY KEY (`pkey`),
UNIQUE KEY `a` (`a`,`b`)
) TYPE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
TIP 7:
Transactions: Not all table types support transactions. BDB and INNODB type do support transactions.
Assuming the server has NOT been started with --skip-bdb or --skip-innodb the following should work:
mysql> create table tran_test (a int, b int) type = InnoDB;
mysql> begin;
mysql> insert into tran_test (a,b) values (1,2);
mysql> select * from tran_test;
select * from tran_test;
+------+------+
| a | b |
+------+------+
| 1 | 2 |
+------+------+
1 row in set (0.00 sec)
mysql> rollback;
mysql> select * from tran_test;
select * from tran_test;
Empty set (0.00 sec)
Summary: rollback undoes everything and commit will save.
TIP 8:
MERGE: Several tables can be merged into one.
CREATE TABLE log_01 (
pkey int(11) NOT NULL auto_increment,
a int,
b varchar(12),
timeEnter timestamp(14),
PRIMARY KEY (pkey)
) type=MyISAM;
CREATE TABLE log_02 (
pkey int(11) NOT NULL auto_increment,
a int,
b varchar(12),
timeEnter timestamp(14),
PRIMARY KEY (pkey)
) type=MyISAM;
CREATE TABLE log_summary (
pkey int(11) NOT NULL auto_increment,
a int,
b varchar(12),
timeEnter timestamp(14),
PRIMARY KEY (pkey)
) type=MERGE UNION(log_01,log_02) INSERT_METHOD=LAST;
mysql> insert into log_01 (a,b) values (1,'log1');
mysql> insert into log_02 (a,b) values (1,'log2');
mysql> select * from log_summary;
select * from log_summary;
+------+------+------+---------------------+
| pkey | a | b | timeEnter |
+------+------+------+---------------------+
| 1 | 1 | log1 | 2004-04-16 11:59:55 |
| 1 | 1 | log2 | 2004-04-16 12:00:08 |
+------+------+------+---------------------+
2 rows in set (0.00 sec)
Reference:
http://dev.mysql.com/doc/mysql/en/MERGE.html
TIP 9:
Updating foreign keys in a multiuser environment. Using LAST_INSERT_ID().
The LAST_INSERT_ID() is unique to the login session. This allows updating
of foreign keys.
CREATE TABLE keytest (
pkey int(11) NOT NULL auto_increment,
ptext text,
ptype int(11),
timeEnter timestamp(14),
PRIMARY KEY (pkey)
);
CREATE TABLE foreignkeytest (
pkey int(11) NOT NULL auto_increment,
pkeykeytest int(11) NOT NULL,
ptext text,
ptype int(11),
timeEnter timestamp(14),
PRIMARY KEY (pkey)
);
mysql> insert into keytest(ptext,ptype) values ('one',1);
mysql> select LAST_INSERT_ID() from keytest;
select LAST_INSERT_ID() from keytest;
+------------------+
| last_insert_id() |
+------------------+
| 1 |
+------------------+
1 row in set (0.03 sec)
mysql> insert into foreignkeytest (ptext,pkeykeytest) values ('one',LAST_INSERT_ID());
Note: If your session didn't update any records, LAST_INSERT_ID() will be zero. Never
assume LAST_INSERT_ID()+1 will be the next record. If another session inserts a record,
this value may be taken. You are assured that this value will be unique to the "session".
TIP 10:
Monitor port 3306:
tcpdump -i eth0 -nN -vvv -xX -s 1500 port 3306 and host not 192.168.1.102
The -s is length of each packet. This monitors all traffic on port 3306 excluding
the good client 192.168.1.102. The out will be in the following format:
[root@third-fl-71 root]# tcpdump -i eth0 -nN -vvv -xX -s 1500 port 3306 and host not 192.168.1.102
tcpdump: listening on eth0
13:05:01.857705 192.168.1.103.4131 > 192.168.1.71.mysql: S [tcp sum ok] 2542149285:2542149285(0) win 16384 (DF) (ttl 128, id 2873, len 48)
0x0000 4500 0030 0b39 4000 8006 6b90 c0a8 0167 E..0.9@...k....g
0x0010 c0a8 0147 1023 0cea 9786 1ea5 0000 0000 ...G.#..........
0x0020 7002 4000 ebe7 0000 0204 05b4 0101 0402 p.@.............
13:05:04.863552 192.168.1.103.4131 > 192.168.1.71.mysql: S [tcp sum ok] 2542149285:2542149285(0) win 16384 (DF) (ttl 128, id 2878, len 48)
TIP 11:
Create a C or C++ API
Download the following:
http://prdownloads.sourceforge.net/souptonuts/mysql_select-0.0.1.tar.gz?...
Also reference:
http://dev.mysql.com/downloads/other/plusplus/
TIP 12:
Connect from Java
#
# mysql-connector-java-3.0.11-stable-bin.jar or later must be downloaded
# from:http://dev.mysql.com/downloads/connector/j/3.0.html
#
# Compile:
# javac Connect.java
#
# Run:
# java Connect
import java.sql.*;
public class Connect
{
public static void main (String[ ] args)
{
Connection conn = null;
String url = "jdbc:mysql://localhost/";
String userName = "root";
String password = "";
try
{
Class.forName ("com.mysql.jdbc.Driver").newInstance ( );
conn = DriverManager.getConnection (url, userName, password);
System.out.println ("Connected");
}
catch (Exception e)
{
System.err.println ("Cannot connect to server");
}
finally
{
if (conn != null)
{
try
{
conn.close ( );
System.out.println ("Disconnected");
}
catch (Exception e) { /* ignore close errors */ }
}
}
}
}
TIP 13:
Print defaults for the current client connection
$ /usr/local/bin/my_print_defaults client mysql
--port=3306
--socket=/tmp/mysql.sock
--no-auto-rehash
TIP 14:
Quick Status:
mysql> \s
--------------
/usr/local/bin/mysql Ver 14.3 Distrib 4.1.1-alpha, for pc-linux (i686)
Connection id: 642
Current database:
Current user: prog@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 4.1.1-alpha-log
Protocol version: 10
Connection: Localhost via UNIX socket
Client characterset: latin1_swedish_ci
Server characterset: latin1_swedish_ci
UNIX socket: /tmp/mysql.sock
Uptime: 1 day 15 hours 24 min 38 sec
Threads: 1 Questions: 4896 Slow queries: 0
Opens: 14 Flush tables: 1 Open tables: 8 Queries per second avg: 0.035
--------------
TIP 15:
"error: 1016: Can't open file:" If it's from an orphaned file, not in the database
but on disk, then, the disk file may need to be deleted.
myisamchk can help with damaged files. It's best to stop the database.
# su -
# mysqladmin shutdown
# cd /usr/local/var/database
# myisamchk *
# /etc/init.d/mysql restart
TIP 16:
Finding records that do not match between two tables.
CREATE TABLE bookreport (
b_id int(11) NOT NULL auto_increment,
s_id int(11) NOT NULL,
report varchar(50),
PRIMARY KEY (b_id)
);
CREATE TABLE student (
s_id int(11) NOT NULL auto_increment,
name varchar(15),
PRIMARY KEY (s_id)
);
insert into student (name) values ('bob');
insert into bookreport (s_id,report)
values ( last_insert_id(),'A Death in the Family');
insert into student (name) values ('sue');
insert into bookreport (s_id,report)
values ( last_insert_id(),'Go Tell It On the Mountain');
insert into student (name) values ('doug');
insert into bookreport (s_id,report)
values ( last_insert_id(),'The Red Badge of Courage');
insert into student (name) values ('tom');
To find the sudents where are missing reports:
select s.name from student s
left outer join bookreport b on s.s_id = b.s_id
where b.s_id is null;
+------+
| name |
+------+
| tom |
+------+
1 row in set (0.00 sec)
Ok, next suppose there is an orphan record in
in bookreport. First delete a matching record
in student:
delete from student where s_id in (select max(s_id) from bookreport);
Now, how to find which one is orphaned:
select * from bookreport b left outer join
student s on b.s_id=s.s_id where s.s_id is null;
+------+------+--------------------------+------+------+
| b_id | s_id | report | s_id | name |
+------+------+--------------------------+------+------+
| 4 | 4 | The Red Badge of Courage | NULL | NULL |
+------+------+--------------------------+------+------+
1 row in set (0.00 sec)
To clean things up (Note in 4.1 you can't do subquery on
same table in a delete so it has to be done in 2 steps):
select @t_sid:=b.s_id from bookreport b left outer join
student s on b.s_id=s.s_id where s.s_id is null;
delete from student where s_id=@t_sid;
But, functions do work in delete. For instance the
following is possible:
delete from student where s_id=max(s_id);
It just a problem when joining the table where the
delete will occur with another table. Another
option is two create a second temp table and
locking the first one.
TIP 17:
Getting a random roll of the dice:
CREATE TABLE dice (
d_id int(11) NOT NULL auto_increment,
roll int,
PRIMARY KEY (d_id)
);
insert into dice (roll) values (1);
insert into dice (roll) values (2);
insert into dice (roll) values (3);
insert into dice (roll) values (4);
insert into dice (roll) values (5);
insert into dice (roll) values (6);
select roll from dice order by rand() limit 1;
TIP 18:
Creating and using your own password file.
This assumes MySQL has been configured with SSL support [ ./configure --with-openssl ]
CREATE TABLE password (
p_id int(11) NOT NULL auto_increment,
id varchar(20),
valid enum('y','n'),
password BLOB,
timeEnter timestamp,
PRIMARY KEY (p_id)
);
insert into password (id,password)
values ('bob',des_encrypt('secret','somekey'));
insert into password (id,password)
values ('tom',des_encrypt('password','somekey'));
insert into password (id,password)
values ('kate',des_encrypt('desmark','somekey'));
insert into password (id,password)
values ('tim',des_encrypt('tim','somekey'));
insert into password (id,password)
values ('sue',des_encrypt('SUM','somekey'));
insert into password (id,password)
values ('john',des_encrypt('dotgo86','somekey'));
insert into password (id)
values ('randal');
mysql> select id,des_decrypt(password,'somekey') from password;
+--------+---------------------------------+
| id | des_decrypt(password,'somekey') |
+--------+---------------------------------+
| bob | secret |
| tom | password |
| kate | desmark |
| tim | tim |
| sue | SUM |
| john | dotgo86 |
| randal | NULL |
+--------+---------------------------------+
7 rows in set (0.00 sec)
Note the bad passwords in the file ('secret','password', and
password is the same as the id.
The following update statement will fill in the
value for valid:
update password set valid =
COALESCE(
concat(
substring('y',1,
1-sign(abs(
find_in_set( lcase( des_decrypt(password,'somekey'))
,concat('password,secret,simple,',lcase(id),',test'))))
),
substring('n',1,
sign(abs(
find_in_set( lcase( des_decrypt(password,'somekey'))
,concat('password,secret,simple,',lcase(id),',test'))))
)
)
,'n');
Which gives the following:
mysql> select id,valid from password;
select id,valid from password;
+--------+-------+
| id | valid |
+--------+-------+
| bob | n |
| tom | n |
| kate | y |
| tim | n |
| sue | y |
| john | y |
| randal | n |
+--------+-------+
7 rows in set (0.00 sec)
To understand it, try taking the following select apart:
select
COALESCE(
concat(
substring('y',1,
1-sign(abs(
find_in_set( lcase( des_decrypt(password,'somekey'))
,concat('password,secret,simple,',lcase(id),',test'))))
),
substring('n',1,
sign(abs(
find_in_set( lcase( des_decrypt(password,'somekey'))
,concat('password,secret,simple,',lcase(id),',test'))))
)
)
,'n')
as valid
from password;
TIP 19:
Order in the count:
create table a (a varchar(10));
insert into a values ('a'),('a'),('b'),('c');
Note
select a,count(a) as count from a group by a order by count;
+------+-------+
| a | count |
+------+-------+
| b | 1 |
| c | 1 |
| a | 2 |
+------+-------+
3 rows in set (0.00 sec)
but
See above name the function ------v
select a,count(a) from a group by a order by count(a);
ERROR 1111 (HY000): Invalid use of group function
TIP 20:
Minimum configure tip:
./configure --with-openssl --enable-thread-safe-client
The --with-openssl is very helpful for creating your own
password file. Also, if doing C API, having thread safe
calls "could" come in handly...it's what I use.
TIP 21:
Getting 1 matching row from an OUTER join
CREATE TABLE parent (
pkey int(11) NOT NULL auto_increment,
pname varchar(5),
timeEnter timestamp,
PRIMARY KEY (pkey)
);
CREATE TABLE child (
ckey int(11) NOT NULL auto_increment,
pkey int(11) NOT NULL,
cname varchar(5),
timeEnter timestamp,
PRIMARY KEY (ckey)
);
insert into parent(pname) values ('A');
select @a_lid:=last_insert_id();
insert into child(pkey,cname) values (@a_lid,'a1');
insert into child(pkey,cname) values (@a_lid,'a2');
insert into child(pkey,cname) values (@a_lid,'a3');
insert into parent(pname) values ('B');
select @a_lid:=last_insert_id();
insert into child(pkey,cname) values (@a_lid,'b1');
insert into child(pkey,cname) values (@a_lid,'b2');
insert into parent(pname) values ('C');
mysql> select p.*,c.cname,count(c.pkey) as number
from parent p left outer join child c on p.pkey=c.pkey
where c.pkey is not null group by c.pkey;
+------+-------+---------------------+-------+--------+
| pkey | pname | timeEnter | cname | number |
+------+-------+---------------------+-------+--------+
| 5 | A | 2004-04-28 09:56:59 | a1 | 3 |
| 6 | B | 2004-04-28 09:56:59 | b1 | 2 |
+------+-------+---------------------+-------+--------+
2 rows in set (0.01 sec)
For comparison, here is a listing of all the matching data:
mysql> select * from parent p left outer join child c on p.pkey=c.pkey where c.pkey is not null;
select * from parent p left outer join child c on p.pkey=c.pkey where c.pkey is not null;
+------+-------+---------------------+------+------+-------+---------------------+
| pkey | pname | timeEnter | ckey | pkey | cname | timeEnter |
+------+-------+---------------------+------+------+-------+---------------------+
| 5 | A | 2004-04-28 09:56:59 | 7 | 5 | a1 | 2004-04-28 09:56:59 |
| 5 | A | 2004-04-28 09:56:59 | 8 | 5 | a2 | 2004-04-28 09:56:59 |
| 5 | A | 2004-04-28 09:56:59 | 9 | 5 | a3 | 2004-04-28 09:56:59 |
| 6 | B | 2004-04-28 09:56:59 | 10 | 6 | b1 | 2004-04-28 09:56:59 |
| 6 | B | 2004-04-28 09:56:59 | 11 | 6 | b2 | 2004-04-28 09:56:59 |
+------+-------+---------------------+------+------+-------+---------------------+
5 rows in set (0.00 sec)
mysql>
TIP 22:
Getting a virtual row count.
Assume the following table:
CREATE TABLE student (
s_id int(11) NOT NULL auto_increment,
name varchar(15),
PRIMARY KEY (s_id)
);
Add some rows and delete (just to convice you this works in all cases)
insert into student (name) values ('tom');
insert into student (name) values ('bob');
insert into student (name) values ('sue');
insert into student (name) values ('mike');
insert into student (name) values ('joe');
insert into student (name) values ('zoe');
insert into student (name) values ('harpo');
delete from student where name = 'bob';
Now, note mc is the row count...independent of s_id;
select a.name,sum(1) as mc
from student a, student b
where a.s_id <= b.s_id
group by a.s_id, a.name order by mc;
+-------+------+
| name | mc |
+-------+------+
| harpo | 1 |
| zoe | 2 |
| joe | 3 |
| mike | 4 |
| sue | 5 |
| tom | 6 |
+-------+------+
6 rows in set (0.00 sec)
mysql>
TIP 23:
Computing running and sliding aggregates. Assume the following table
and data:
CREATE TABLE dice (
d_id int(11) NOT NULL auto_increment,
roll int,
PRIMARY KEY (d_id)
);
insert into dice (roll) values (1);
insert into dice (roll) values (2);
insert into dice (roll) values (3);
insert into dice (roll) values (4);
insert into dice (roll) values (5);
insert into dice (roll) values (6);
mysql> select a.roll, sum(b.roll) from dice a, dice b
where b.d_id <= a.d_id group by a.d_id, a.roll;
+------+-------------+
| roll | sum(b.roll) |
+------+-------------+
| 1 | 1 |
| 2 | 3 |
| 3 | 6 |
| 4 | 10 |
| 5 | 15 |
| 6 | 21 |
+------+-------------+
6 rows in set (0.00 sec)
Note, it's also possible to get a running average by changing the
sum to avg as follows:
mysql> select a.roll, avg(b.roll) from dice a, dice b
where b.d_id <= a.d_id group by a.d_id, a.roll;
+------+-------------+
| roll | avg(b.roll) |
+------+-------------+
| 1 | 1.0000 |
| 2 | 1.5000 |
| 3 | 2.0000 |
| 4 | 2.5000 |
| 5 | 3.0000 |
| 6 | 3.5000 |
+------+-------------+
6 rows in set (0.00 sec)
LONG WINDED TIPS:
LONG WINDED TIP 1: (May want to skip, since it's a long example)
SQL Characteristic Functions: Do it without "if", "case", or "GROUP_CONCAT". There's little
use for this now; but, it's fast and provides a lot of control.
The simple secret, and it's also why they work in almost all databases, is the
following functions:
o sign (x) returns -1,0, +1 for values x < 0, x = 0, x > 0 respectively
o abs( sign( x) ) returns 0 if x = 0 else, 1 if x > 0 or x < 0
o 1-abs( sign( x) ) complement of the above, since this returns 1 only if x = 0
Quick example: sign(-1) = -1, abs( sign(-1) ) = 1, 1-abs( sign(-1) ) = 0
Data for full example:
CREATE TABLE exams (
pkey int(11) NOT NULL auto_increment,
name varchar(15),
exam int,
score int,
PRIMARY KEY (pkey)
);
insert into exams (name,exam,score) values ('Bob',1,75);
insert into exams (name,exam,score) values ('Bob',2,77);
insert into exams (name,exam,score) values ('Bob',3,78);
insert into exams (name,exam,score) values ('Bob',4,80);
insert into exams (name,exam,score) values ('Sue',1,90);
insert into exams (name,exam,score) values ('Sue',2,97);
insert into exams (name,exam,score) values ('Sue',3,98);
insert into exams (name,exam,score) values ('Sue',4,99);
mysql> select * from exams;
+------+------+------+-------+
| pkey | name | exam | score |
+------+------+------+-------+
| 1 | Bob | 1 | 75 |
| 2 | Bob | 2 | 77 |
| 3 | Bob | 3 | 78 |
| 4 | Bob | 4 | 80 |
| 5 | Sue | 1 | 90 |
| 6 | Sue | 2 | 97 |
| 7 | Sue | 3 | 98 |
| 8 | Sue | 4 | 99 |
+------+------+------+-------+
8 rows in set (0.00 sec)
mysql> select name,
sum(score*(1-abs(sign(exam-1)))) as exam1,
sum(score*(1-abs(sign(exam-2)))) as exam2,
sum(score*(1-abs(sign(exam-3)))) as exam3,
sum(score*(1-abs(sign(exam-4)))) as exam4
from exams group by name;
+------+-------+-------+-------+-------+
| name | exam1 | exam2 | exam3 | exam4 |
+------+-------+-------+-------+-------+
| Bob | 75 | 77 | 78 | 80 |
| Sue | 90 | 97 | 98 | 99 |
+------+-------+-------+-------+-------+
2 rows in set (0.00 sec)
Note, the above pivot table was created with one select statement
You may think IF's would be clean bug WATCH OUT!
Look what the following gives (INCORRECT !!):
mysql> select name,
if(exam=1,score,null) as exam1,
if(exam=2,score,null) as exam2,
if(exam=3,score,null) as exam3,
if(exam=4,score,null) as exam4
from exams group by name;
+------+-------+-------+-------+-------+
| name | exam1 | exam2 | exam3 | exam4 |
+------+-------+-------+-------+-------+
| Bob | 75 | NULL | NULL | NULL |
| Sue | 90 | NULL | NULL | NULL |
+------+-------+-------+-------+-------+
2 rows in set (0.00 sec)
mysql> select name,
sum(score*(1-abs(sign(exam-1)))) as exam1,
sum(score*(1-abs(sign(exam-2)))) as exam2,
sum(score*(1-abs(sign(exam-3)))) as exam3,
sum(score*(1-abs(sign(exam-4)))) as exam4,
sum(score*(1-abs(sign(exam- 2)))) - sum(score*(1-abs(sign(exam- 1)))) as delta_1_2,
sum(score*(1-abs(sign(exam- 3)))) - sum(score*(1-abs(sign(exam- 2)))) as delta_2_3,
sum(score*(1-abs(sign(exam- 4)))) - sum(score*(1-abs(sign(exam- 3)))) as delta_3_4
from exams group by name;
+------+-------+-------+-------+-------+-----------+-----------+-----------+
| name | exam1 | exam2 | exam3 | exam4 | delta_1_2 | delta_2_3 | delta_3_4 |
+------+-------+-------+-------+-------+-----------+-----------+-----------+
| Bob | 75 | 77 | 78 | 80 | 2 | 1 | 2 |
| Sue | 90 | 97 | 98 | 99 | 7 | 1 | 1 |
+------+-------+-------+-------+-------+-----------+-----------+-----------+
2 rows in set (0.00 sec)
Above delta_1_2 shows the difference between the first and second exams, with the numbers
being positive because both Bob and Sue improved their score with each exam. Calculating
the deltas here shows it's possible to compare two rows, not columns which is easily done
with the standard SQL statements but rows in the original table.
mysql>select name,
sum(score*(1-abs(sign(exam-1)))) as exam1,
sum(score*(1-abs(sign(exam-2)))) as exam2,
sum(score*(1-abs(sign(exam-3)))) as exam3,
sum(score*(1-abs(sign(exam-4)))) as exam4,
sum(score*(1-abs(sign(exam- 2)))) - sum(score*(1-abs(sign(exam- 1)))) as delta_1_2,
sum(score*(1-abs(sign(exam- 3)))) - sum(score*(1-abs(sign(exam- 2)))) as delta_2_3,
sum(score*(1-abs(sign(exam- 4)))) - sum(score*(1-abs(sign(exam- 3)))) as delta_3_4,
sum(score*(1-abs(sign(exam- 2)))) - sum(score*(1-abs(sign(exam- 1)))) +
sum(score*(1-abs(sign(exam- 3)))) - sum(score*(1-abs(sign(exam- 2)))) +
sum(score*(1-abs(sign(exam- 4)))) - sum(score*(1-abs(sign(exam- 3)))) as TotalIncPoints
from exams group by name;
+------+-------+-------+-------+-------+-----------+-----------+-----------+----------------+
| name | exam1 | exam2 | exam3 | exam4 | delta_1_2 | delta_2_3 | delta_3_4 | TotalIncPoints |
+------+-------+-------+-------+-------+-----------+-----------+-----------+----------------+
| Bob | 75 | 77 | 78 | 80 | 2 | 1 | 2 | 5 |
| Sue | 90 | 97 | 98 | 99 | 7 | 1 | 1 | 9 |
+------+-------+-------+-------+-------+-----------+-----------+-----------+----------------+
2 rows in set (0.00 sec)
TotalIncPoints shows the sum of the deltas.
select name,
sum(score*(1-abs(sign(exam-1)))) as exam1,
sum(score*(1-abs(sign(exam-2)))) as exam2,
sum(score*(1-abs(sign(exam-3)))) as exam3,
sum(score*(1-abs(sign(exam-4)))) as exam4,
sum(score*(1-abs(sign(exam- 2)))) - sum(score*(1-abs(sign(exam- 1)))) as delta_1_2,
sum(score*(1-abs(sign(exam- 3)))) - sum(score*(1-abs(sign(exam- 2)))) as delta_2_3,
sum(score*(1-abs(sign(exam- 4)))) - sum(score*(1-abs(sign(exam- 3)))) as delta_3_4,
sum(score*(1-abs(sign(exam- 2)))) - sum(score*(1-abs(sign(exam- 1)))) +
sum(score*(1-abs(sign(exam- 3)))) - sum(score*(1-abs(sign(exam- 2)))) +
sum(score*(1-abs(sign(exam- 4)))) - sum(score*(1-abs(sign(exam- 3)))) as TotalIncPoints,
(sum(score*(1-abs(sign(exam-1)))) +
sum(score*(1-abs(sign(exam-2)))) +
sum(score*(1-abs(sign(exam-3)))) +
sum(score*(1-abs(sign(exam-4)))))/4 as AVG
from exams group by name;
+------+-------+-------+-------+-------+-----------+-----------+-----------+----------------+-------+
| name | exam1 | exam2 | exam3 | exam4 | delta_1_2 | delta_2_3 | delta_3_4 | TotalIncPoints | AVG |
+------+-------+-------+-------+-------+-----------+-----------+-----------+----------------+-------+
| Bob | 75 | 77 | 78 | 80 | 2 | 1 | 2 | 5 | 77.50 |
| Sue | 90 | 97 | 98 | 99 | 7 | 1 | 1 | 9 | 96.00 |
+------+-------+-------+-------+-------+-----------+-----------+-----------+----------------+-------+
2 rows in set (0.00 sec)
It's possible to combine Total Increasing Point TotalIncPoints with AVG. In fact, it's possible to combine
all of the example cuts of the data into one SQL statement, which provides additional options for displaying
data on your page
select name,
sum(score*(1-abs(sign(exam-1)))) as exam1,
sum(score*(1-abs(sign(exam-2)))) as exam2,
sum(score*(1-abs(sign(exam-3)))) as exam3,
sum(score*(1-abs(sign(exam-4)))) as exam4,
(sum(score*(1-abs(sign(exam-1)))) +
sum(score*(1-abs(sign(exam-2)))))/2 as AVG1_2,
(sum(score*(1-abs(sign(exam-2)))) +
sum(score*(1-abs(sign(exam-3)))))/2 as AVG2_3,
(sum(score*(1-abs(sign(exam-3)))) +
sum(score*(1-abs(sign(exam-4)))))/2 as AVG3_4,
(sum(score*(1-abs(sign(exam-1)))) +
sum(score*(1-abs(sign(exam-2)))) +
sum(score*(1-abs(sign(exam-3)))) +
sum(score*(1-abs(sign(exam-4)))))/4 as AVG
from exams group by name;
+------+-------+-------+-------+-------+--------+--------+--------+-------+
| name | exam1 | exam2 | exam3 | exam4 | AVG1_2 | AVG2_3 | AVG3_4 | AVG |
+------+-------+-------+-------+-------+--------+--------+--------+-------+
| Bob | 75 | 77 | 78 | 80 | 76.00 | 77.50 | 79.00 | 77.50 |
| Sue | 90 | 97 | 98 | 99 | 93.50 | 97.50 | 98.50 | 96.00 |
+------+-------+-------+-------+-------+--------+--------+--------+-------+
2 rows in set (0.00 sec)
Exam scores are listing along with moving averages...again it's all with one
select statement.
Nice
Very good content, it helped me while I was conceptless.