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;
ALTER TABLE hware ADD COLUMN cpu_core TINYINT UNSIGNED AFTER cpu_num;
ALTER TABLE hware CHANGE COLUMN cpu_num cpu_sock TINYINT UNSIGNED;
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');
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');