SQL Syntax (MySQL)

CREATE / ALTER Tables

CREATE

CREATE TABLE hware (hid INT AUTO_INCREMENT, make VARCHAR(64), model VARCHAR(64), cpu_num TINYINT UNSIGNED, mem SMALLINT UNSIGNED, sn VARCHAR(64), PRIMARY KEY (hid));
CREATE TABLE notes (nid INT UNSIGNED AUTO_INCREMENT, note VARCHAR(256), stamp TIMESTAMP DEFAULT current_timestamp ON UPDATE current_timestamp, PRIMARY KEY (nid));
CREATE TABLE hware2notes (hid INT, nid INT, PRIMARY KEY (hid, nid));                        # Primary key prevent duplicate rows

RENAME

RENAME TABLE hware to hardware;

ALTER

ALTER TABLE hware ADD UNIQUE KEY hid;                                         # Add additional index/key (using existing column)
ALTER TABLE hware ADD FULLTEXT name (name, description);                      # Add a FULLTEXT key (used for fuzzy searches)
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
ALTER TABLE hware DROP COLUMN cpu_sock;                                       # Drop (delete/remove) column from table
ALTER TABLE hware DROP KEY hid;                                               # Drop (delete/remove) hid index (not the column)
ALTER TABLE hware DROP PRIMARY KEY;                                           # Drop primary key from table

DELETE

DELETE FROM hware;                                         # Delete the contents if the hware table

Keys / Indexes

There are three different types of indexing available for a table, all of which can be made against one or more columns in your table

• Primary - compulsory, often on an AUTO_INCREMENT id. Must be unique for each row.
• Unique - optional, useful to enforce uniqueness in a table across columns not included in Primary Key
• FullText - optional, used to create a full text index, which you can later do fuzzy searches against.

All keys have a name, if you don't specify one at creation, the name of the first column is used.

INSERT / UPDATE Rows

Basic Examples

INSERT INTO hosts (name, ping_ok) VALUES ('ServerA', 1) ON DUPLICATE KEY UPDATE ping_ok=1;
INSERT IGNORE INTO hware2note (hid, nid);                 # Ignores errors returned if insert would violate primary/unique key duplication
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)

# Basic insert
INSERT INTO table (id, make, model) SELECT id, 'DELL', 'PE1950' FROM hosts WHERE ip=INET_ATON('159.104.7.171');
# With 'ON DUPLICATE', VALUES allows the result from the SELECT to re-used
INSERT INTO table (id, make, model) SELECT id, 'DELL', 'PE1950' FROM hosts WHERE ip=INET_ATON('159.104.7.171') 
    ON DUPLICATE KEY UPDATE id = VALUES(id);

UPDATE ... SELECT

Here the table table is being updated from the hware table.

UPDATE table SET make=(SELECT make FROM hware WHERE id=5) WHERE tid=2;

Alternatively, 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

AS renames the column heading for a query result.

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

ORDER BY

SELECT INET_NTOA(ip) AS ip, name FROM hosts ORDER BY ip;
SELECT INET_NTOA(ip) AS ip, name FROM hosts ORDER BY ip DESC;            # Reverse sorting order (descending)

NULL's can be a bit of pain as they tend to end up at the top, to force then to the bottom insert an additional ISNULL(column) for column your sorting by which has NULL values...

 SELECT INET_NTOA(ip) AS ip, name FROM hosts ORDER BY ISNULL(ip), ip;

JOIN

SELECT make, model FROM hware JOIN hosts ON table.id=hosts.id WHERE hosts.ip=INET_ATON('10.10.255.253');                 # Join with ON
SELECT make, model FROM hware JOIN hosts USING (id) WHERE hosts.ip=INET_ATON('10.10.255.253');                           # Join with USING
SELECT make, model, info FROM hware JOIN hosts USING (id) JOIN notes ON (id) WHERE hosts.ip=INET_ATON('10.10.255.253');  # Multiple join

Different types of join will yield differing results, depending how different rows match up. It can be a bit flummoxing to start with, but its actually fairly simple once you've got the basic idea straight in your head. The LEFT and RIGHT joins allow you control whether or not you want to see NULL's appearing in your results where rows don't always have counterparts in two tables you are JOIN'ing together.

See this site for a very clear and concise walk-through - http://www.wellho.net/mouth/158_MySQL-LEFT-JOIN-and-RIGHT-JOIN-INNER-JOIN-and-OUTER-JOIN.html

COUNT

SELECT COUNT(*) FROM hware WHERE make='IBM';                                                  # Counts number of rows in selection
SELECT make, COUNT(*) AS count FROM hware GROUP BY make;                                      # Counts number of occurrences of 'make'
SELECT COUNT(*) AS total, SUM(IF(model='x336',1,0)) AS x336 FROM hware WHERE make='IBM';      # Additional counts number of rows where model='x336'

DISTINCT

Only selects completely distinct rows (ie on all columns), not to be confused with GROUP BY (see below).

SELECT DISTINCT * FROM hware JOIN notes ON (id);

GROUP BY

Select rows, grouped by a particular column (so effectively only shows one row for duplicates on that column)

SELECT * FROM hware JOIN notes ON (id) GROUP BY model;

Events

Events are scheduled occurrences, either where you're running a simple command, or a stored procedure.

If you need to create a stored procedure, use the MySQL Workbench software (http://dev.mysql.com/downloads/workbench/), its a lot easier than creating via command line.

To create an event...

CREATE EVENT do_update_cluster_stats ON SCHEDULE EVERY 1 DAY STARTS TIMESTAMP(CURRENT_DATE, '23:30:00') DO CALL update_cluster_stats();

You may need to enable the event scheduler

• SET GLOBAL event_scheduler = 1;
• The event_scheduler should now be visible in the process list (show processlist)