MySQL

See the official MySQL documentation for further info.

Basic Commands

Command	Description
SHOW DATABASES;	Show databases on server
USE <db_name>;	Use / go into a database
SHOW TABLES;	Show tables in current database
DESCRIBE <tbl_name>;	Show the format of the table
CREATE DATABASE <db_name>;	Create a database

User Accounts

Create Users

To give full privileges to a user coming from any location use;

 GRANT ALL PRIVILEGES on <database>.* to '<user>'@'%' identified by '<password>' with grant option;

Display Users

To display all configured users;

SELECT CONCAT('SHOW GRANTS FOR \'', user ,'\'@\'', host, '\';') FROM mysql.user

Then use the displayed lines to see the detail of each user

Data Types

NULL

NULL means "no data", it doesn't mean zero. Therefore 0 <> NULL in an numerical field, and "NULL" <> NULL in a string field.

To set a field to NULL, use NULL without any quotes eg...

INSERT INTO table (col1, col2) VALUES ('data1', NULL);

Numbers

BOOL and BOOLEAN are synonyms for TINYINT(1) .

Type	Bytes	Min	Max
TINYINT	1	-128	127
TINYINT UNSIGNED	1	0	255
SMALLINT	2	-32768	32767
SMALLINT UNSIGNED	2	0	65535
MEDIUMINT	3	-8388608	8388607
MEDIUMINT UNSIGNED	3	0	16777215
INT	4	-2147483648	2147483647
INT UNSIGNED	4	0	4294967295
BIGINT	8	-9223372036854775808	9223372036854775807
BIGINT UNSIGNED	8	0	18446744073709551615

IP Addresses

IP addresses are most efficiently stored as an UNSIGNED INT, though obviously this isn't particularly human readable. MySQL will do the conversion between INT and dotted quad using the INET_ATON and INET_NTOA functions. For example;

SELECT INET_NTOA(ip) from ips;
INSERT INTO ips SET ip=INET_ATON('10.1.2.3');

Alternatively, use VARCHAR(15) to store as text.

CREATE / ALTER Tables

CREATE

CREATE TABLE hware (hid INT, make VARCHAR(64), model VARCHAR(64), cpu_num TINYINT UNSIGNED, cpu_mhz SMALLINT UNSIGNED, mem SMALLINT UNSIGNED, sn VARCHAR(64), PRIMARY KEY (hid));

ALTER

ALTER TABLE hware ADD UNIQUE KEY hid;                                         # Add key (using existing column)
ALTER TABLE hware ADD COLUMN cpu_core TINYINT UNSIGNED AFTER cpu_num;         # Add new column
ALTER TABLE hware MODIFY COLUMN mem INT UNSIGNED;                             # Modify column type
ALTER TABLE hware CHANGE COLUMN cpu_num cpu_sock TINYINT UNSIGNED;            # Change column name

INSERT / UPDATE Rows

Basic Examples

INSERT INTO hosts (name, ping_ok) VALUES ('ServerA', 1) ON DUPLICATE KEY UPDATE ping_ok=1;
UPDATE hosts SET ping_ok=0, reason='Time Out' WHERE name='ServerA';

INSERT ... SELECT
Used when you want you want include data from another table in an INSERT statement (eg you want to reference a unique ID in another table)

INSERT INTO table (id, make, model) SELECT id, 'DELL', 'PE1950' FROM hosts WHERE ip=INET_ATON('159.104.7.171');

UPDATE ... SELECT
The syntax is not UPDATE ... SELECT, but it performs the function you'd expect from such a query, although its a little mis-leading. In the example below the plat table is updated from the os table.

UPDATE plat,os SET plat.osid=os.osid WHERE os.os_supplied='Microsoft(R) Windows(R) Server 2003, Standard Edition';

SELECT Rows

AS

 SELECT INET_NTOA(ip) AS ip, name FROM hosts;

JOIN

SELECT make, model FROM table JOIN hosts ON table.id=hosts.id WHERE hosts.ip=INET_ATON('10.10.255.253');

Different types of join will yield differing results, depending how different rows match up see http://www.wellho.net/mouth/158_MySQL-LEFT-JOIN-and-RIGHT-JOIN-INNER-JOIN-and-OUTER-JOIN.html