UNI-FIX Computer

 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;


About the author of this programming example or tutorial:
Mike Chirico (mchirico@users.sourceforge.net) Copyright (c) 2004 (GPU Free Documentation License) Last Updated: Tue Jul 20 12:14:51 EDT 2004

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


About the author of this programming example or tutorial:
Mike Chirico (mchirico@users.sourceforge.net) Copyright (c) 2004 (GPU Free Documentation License) Last Updated: Tue Jul 20 12:14:51 EDT 2004

 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.

About the author of this programming example or tutorial:
Mike Chirico (mchirico@users.sourceforge.net) Copyright (c) 2004 (GPU Free Documentation License) Last Updated: Tue Jul 20 12:14:51 EDT 2004

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 may 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)


About the author of this programming example or tutorial:
Mike Chirico (mchirico@users.sourceforge.net) Copyright (c) 2004 (GPU Free Documentation License) Last Updated: Tue Jul 20 12:14:51 EDT 2004

The temporary tables could be very useful in some cases
to keep temporary data. The most important thing that
should be know for temporary tables is that they will
be deleted when the current client disconnects

You could try to create such a temporary table

CREATE TEMPORARY TABLE MyTemporaryTable SELECT * FROM MyRealTable WHERE id<10;

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;
or
mysql> show table status like '%';

The above gives you create time and other information.

About the author of this programming example or tutorial:
Mike Chirico (mchirico@users.sourceforge.net) Copyright (c) 2004 (GPU Free Documentation License) Last Updated: Tue Jul 20 12:14:51 EDT 2004