MySQL: Difference between revisions

From vwiki
Jump to navigation Jump to search
m (Added "CREATE / ALTER Tables")
m (→‎INSERT / UPDATE Rows: Added "UPDATE ... SELECT")
Line 96: Line 96:
<source lang="mysql">INSERT INTO table (id, make, model) SELECT id, 'DELL', 'PE1950' FROM hosts WHERE ip=INET_ATON('159.104.7.171');</source>
<source lang="mysql">INSERT INTO table (id, make, model) SELECT id, 'DELL', 'PE1950' FROM hosts WHERE ip=INET_ATON('159.104.7.171');</source>


<br>'''UPDATE ... SELECT'''<br>
The syntax is not <code>UPDATE ... SELECT</code>, but it performs the function you'd expect from such a query, although its a little mis-leading.  In the example below the <code>plat</code> table is updated from the <code>os</code> table.
<source lang="mysql">UPDATE plat,os SET plat.osid=os.osid WHERE os.os_supplied='Microsoft(R) Windows(R) Server 2003, Standard Edition';</source>


== SELECT Rows ==
== SELECT Rows ==

Revision as of 11:00, 12 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.

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


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

Software Packages