Sstrutt: Initial creation - content from MySQL page (now category)

2014-03-16T22:07:23Z

Initial creation - content from MySQL page (now category)

New page

== CREATE / ALTER Tables ==
=== CREATE ===
<source lang="mysql">
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
</source>

=== RENAME ===
<source lang="mysql">
RENAME TABLE hware to hardware;
</source>

=== ALTER ===
<source lang="mysql">
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
</source>

=== DELETE ===
<source lang="mysql">
DELETE FROM hware; # Delete the contents if the hware table
</source>

=== 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 <code>AUTO_INCREMENT</code> 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 ===
<source lang="mysql">
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';
</source>

=== INSERT ... SELECT ===
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"># 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);</source>

=== UPDATE ... SELECT ===
Here the <code>table</code> table is being updated from the <code>hware</code> table.
<source lang="mysql">UPDATE table SET make=(SELECT make FROM hware WHERE id=5) WHERE tid=2;</source>

Alternatively, 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 ==
=== AS ===
'''<code>AS</code>''' renames the column heading for a query result.
<source lang="mysql"> SELECT INET_NTOA(ip) AS ip, name FROM hosts; </source>

=== ORDER BY ===
<source lang="mysql">
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)
</source>

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 <code>ISNULL(column)</code> for column your sorting by which has NULL values...
<source lang="mysql"> SELECT INET_NTOA(ip) AS ip, name FROM hosts ORDER BY ISNULL(ip), ip; </source>

=== JOIN ===
<source lang="mysql">
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
</source>

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 ===
<source lang="mysql">
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'
</source>

=== DISTINCT ===
Only selects completely distinct rows (ie on all columns), not to be confused with <code>GROUP BY</code> (see below).
<source lang="mysql">
SELECT DISTINCT * FROM hware JOIN notes ON (id);
</source>

=== GROUP BY ===
Select rows, grouped by a particular column (so effectively only shows one row for duplicates on that column)
<source lang="mysql">
SELECT * FROM hware JOIN notes ON (id) GROUP BY model;
</source>

== 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...
<source lang="mysql">
CREATE EVENT do_update_cluster_stats ON SCHEDULE EVERY 1 DAY STARTS TIMESTAMP(CURRENT_DATE, '23:30:00') DO CALL update_cluster_stats();
</source>

You may need to enable the event scheduler
* <code> SET GLOBAL event_scheduler = 1; </code>
* The event_scheduler should now be visible in the process list (<code>show processlist</code>)

[[Category:MySQL]]

SQL Syntax (MySQL) - Revision history

Sstrutt: Initial creation - content from MySQL page (now category)