SQL Syntax (MySQL)
CREATE / ALTER Tables
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 TABLE hware to hardware;
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 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_INCREMENTid. 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
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('220.127.116.11'); # 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('18.104.22.168') ON DUPLICATE KEY UPDATE id = VALUES(id);
UPDATE ... SELECT
table table is being updated from the
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
UPDATE plat,os SET plat.osid=os.osid WHERE os.os_supplied='Microsoft(R) Windows(R) Server 2003, Standard Edition';
AS renames the column heading for a query result.
SELECT INET_NTOA(ip) AS ip, name FROM hosts;
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;
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
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'
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);
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 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 (