Understanding SQL

Lethal`

Lethal`

Lethal`

Soldato
Joined
25 Oct 2006
Posts
5,395
I'm in the process of trying to get to grips with MySQL and actually use it.

I have been supplied some code to create some tables in a database. I understand most of it, but not all...

First table:
Code: 
CREATE TABLE IF NOT EXISTS `players` ( 
		  `pid` varchar(32) NOT NULL, 
		  `firstname` varchar(32) NOT NULL, 
		  `lastname` varchar(32) NOT NULL, 
		  `lastip` varchar(32) NOT NULL, 
		  `connections` int(11) NOT NULL default 1, 
		  `lastconnect` timestamp NOT NULL default CURRENT_TIMESTAMP, 
		  PRIMARY KEY (`pid`) 
		) ENGINE=MyISAM DEFAULT CHARSET=latin1 ;

Second table:
Code: 
CREATE TABLE IF NOT EXISTS `playernames` ( 
		  `pid` varchar(32) NOT NULL, 
		  `name` varchar(32) NOT NULL, 
		  `last` timestamp NOT NULL default CURRENT_TIMESTAMP, 
		  CONSTRAINT PRIMARY KEY (`pid`, `name`) 
		) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Third table:
Code: 
CREATE TABLE IF NOT EXISTS `playerips` ( 
		  `pid` varchar(32) NOT NULL, 
		  `ip` varchar(32) NOT NULL, 
		  `last` timestamp NOT NULL default CURRENT_TIMESTAMP, 
		  CONSTRAINT PRIMARY KEY (`pid`, `ip`) 
		) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

In the second and third table it has CONSTRAINT before PRIMARY KEY, what is the purpose of this? What does it do?

Thanks for any help :)
 
Hmm, in a book on PHP/MySQL that I have it has some SQL to create a table with more than one primary key, but it only uses:
Code: 
primary key (orderid, isbn)
 
Okay, thanks. It also has this SQL to insert entries into the database:
Code: 
"INSERT INTO players (pid, firstname, lastname, lastip) VALUES ('%s', '%s', '%s', '%s') ON DUPLICATE KEY UPDATE lastname = VALUES(lastname), lastip = VALUES(lastip), connections = connections + 1, lastconnect = NOW()", auth, clientNameBuffer, clientNameBuffer, ipaddr
(The formatting values are there, as it is part of a formatting function.)

I'm having trouble understanding the "ON DUPLICATE KEY" command, I realise what it is supposed to do but don't quite understand how it is working. Could someone explain this for me?

Also, is there a good tutorial about DUPLICATE KEY that someone has a link to?
 
Thanks Jestar, another question...

Here are the SQL queries to insert data into the other two tables:

playernames:
Code: 
INSERT INTO playernames (pid, name, last) VALUES ('%s', '%s', NOW()) ON DUPLICATE KEY UPDATE last = NOW()", auth, clientNameBuffer

playerips:
Code: 
INSERT INTO playerips (pid, ip, last) VALUES ('%s', '%s', NOW()) ON DUPLICATE KEY UPDATE last = NOW()", auth, ipaddr

To make it easier to understand, these three queries are ran when a player joins a game server.

When the first query (in the post above) is ran it doesn't add a new row if it finds the player's ID already there, it just updates there lastname, lastip, connections and lastconnect. When the second query runs it adds a new row even if their name is different, but if the name is the same it will just update the last time value. Similar concept for the third query.

What makes the first query behave in a different way to the second and third?
 
You must log in or register to reply here.
Back
Top Bottom