2,187
edits
Difference between revisions of "MySQL"
Jump to navigation
Jump to search
→CREATE / ALTER Tables: Seperated into sub-sections, and added "Keys / Indexes"
(→IP Addresses: Elaborated a bit) |
(→CREATE / ALTER Tables: Seperated into sub-sections, and added "Keys / Indexes") |
||
| Line 99: | Line 99: | ||
== CREATE / ALTER Tables == | == CREATE / ALTER Tables == | ||
=== CREATE === | |||
<source lang="mysql"> | <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 hware (hid INT AUTO_INCREMENT, make VARCHAR(64), model VARCHAR(64), cpu_num TINYINT UNSIGNED, mem SMALLINT UNSIGNED, sn VARCHAR(64), PRIMARY KEY (hid)); | ||
| Line 106: | Line 106: | ||
</source> | </source> | ||
=== RENAME === | |||
<source lang="mysql"> | <source lang="mysql"> | ||
RENAME TABLE hware to hardware; | RENAME TABLE hware to hardware; | ||
</source> | </source> | ||
=== ALTER === | |||
<source lang="mysql"> | <source lang="mysql"> | ||
ALTER TABLE hware ADD UNIQUE KEY hid; # Add additional index/key (using existing column) | ALTER TABLE hware ADD UNIQUE KEY hid; # Add additional index/key (using existing column) | ||
ALTER TABLE hware ADD FULLTEXT(name); | 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 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 MODIFY COLUMN mem INT UNSIGNED; # Modify column type | ||
| Line 123: | Line 123: | ||
</source> | </source> | ||
=== DELETE === | |||
<source lang="mysql"> | <source lang="mysql"> | ||
DELETE FROM hware; # Delete the contents if the hware table | DELETE FROM hware; # Delete the contents if the hware table | ||
</source> | </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 | |||
* '''Unique''' - optional, useful to enforce uniqueness in a table and stop duplicate entries. | |||
* '''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 / UPDATE Rows == | ||