MySQL: Difference between revisions

From vwiki
Jump to navigation Jump to search
m (→‎Data Types: Added "Numbers")
m (→‎Insert / Update Rows: Added "INSERT ... SELECT")
Line 74: Line 74:


== Insert / Update Rows ==
== Insert / Update Rows ==
'''Basic Examples'''
<source lang="mysql">INSERT INTO hosts (name, ping_ok) VALUES ('ServerA', 1) ON DUPLICATE KEY UPDATE ping_ok=1;
<source lang="mysql">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'; </source>
UPDATE hosts SET ping_ok=0, reason='Time Out' WHERE name='ServerA'; </source>
<br>'''INSERT ... SELECT'''<br>
Used when you want you want include data from another table in an <code>INSERT</code> statement (eg you want to reference a unique ID in another table)
<source lang="mysql">INSERT INTO table (id, make, model) SELECT id, 'DELL', 'PE1950' FROM hosts WHERE ip=INET_ATON('159.104.7.171');</source>


== Software Packages ==
== Software Packages ==

Revision as of 13:17, 11 February 2010

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.

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');

Software Packages