1. Skapa en tabell
För att skapa en tabell loggar jag in som olle, går in i databasen olles_db och använder CREATE TABLE.
Nedan skapar jag tabellen animal:
[olle@dev1]$ /usr/local/mysql/bin/mysql -u olle -p
Enter password: star
mysql> use olles_db;
mysql> CREATE TABLE animal(
-> id INT NOT NULL AUTO_INCREMENT,
-> name VARCHAR(50),
-> weight INT,
-> birth DATE,
-> timestamp TIMESTAMP,
-> PRIMARY KEY (id)
-> );
|
Det finns några detaljer värda att beskriva noggrannare här:
| Detaljer | | Beskrivning |
|---|
| AUTO_INCREMENT | | Detta innebär att kolumnen id automatist får ett unikt värde. Den första raden som skapas i tabellen kommer att få id=1, och nästa 2 o.s.v. Detta är en bekväm finess som man inte hittar i t. ex. Oracle. |
| NOT NULL | | Om en kolumn har NOT NULL så måste ett värde sättas varje gång du gör INSERT. |
| TIMESTAMP | | Denna kolumn håller reda på när raden skapades. När du gör INSERT ska du låtsas som kolumnen inte finns, den får sin tidsstämpel ändå. Jag brukar lägga en timestamp i varje tabell, ibland kan det vara guld värt att veta NÄR något hände. |
| PRIMARY KEY(id) | | Gör kolumnen id till primär nyckel. |
2. Visa tabeller
Man ser vilka tabeller som finns i en databas genom att skriva:
mysql> SHOW TABLES;
+--------------------+
| Tables_in_olles_db |
+--------------------+
| animal |
+--------------------+
|
Vill man se vilka kolumner tabellen består av skriver man:
mysql> DESCRIBE animal;
+-----------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------------+------+-----+---------+----------------+
| id | int(11) | | PRI | NULL | auto_increment |
| name | varchar(50) | YES | | NULL | |
| weight | int(11) | YES | | NULL | |
| birth | date | YES | | NULL | |
| timestamp | timestamp(14) | YES | | NULL | |
+-----------+---------------+------+-----+---------+----------------+
|
3. Ändra en tabell
Det är vanligt att man vill lägga till en ny kolumn i en tabell. Då använder man ALTER TABLE:
mysql> ALTER TABLE animal ADD species VARCHAR(50);
mysql> DESCRIBE animal;
+-----------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------------+------+-----+---------+----------------+
| id | int(11) | | PRI | NULL | auto_increment |
| name | varchar(50) | YES | | NULL | |
| weight | int(11) | YES | | NULL | |
| birth | date | YES | | NULL | |
| timestamp | timestamp(14) | YES | | NULL | |
| species | varchar(50) | YES | | NULL | |
+-----------+---------------+------+-----+---------+----------------+
|
Om man sedan ångrar sig och vill ta bort kolumnen går det bra med ALTER TABLE igen:
mysql> ALTER TABLE animal DROP species;
mysql> DESCRIBE animal;
+-----------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------------+------+-----+---------+----------------+
| id | int(11) | | PRI | NULL | auto_increment |
| name | varchar(50) | YES | | NULL | |
| weight | int(11) | YES | | NULL | |
| birth | date | YES | | NULL | |
| timestamp | timestamp(14) | YES | | NULL | |
+-----------+---------------+------+-----+---------+----------------+
|
4. Ta bort tabell
Du tar bort en tabell på följande sätt:
mysql> DROP TABLE animal;
mysql> SHOW TABLES;
Empty set (0.00 sec)
|
5. Syntax för CREATE TABLE
Med CREATE TABLE skapar man tabeller. Texten visar syntaxen för en korrekt CREATE TABLE-sats.
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)]
[table_options] [select_statement]
create_definition:
col_name type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT]
[PRIMARY KEY] [reference_definition]
or PRIMARY KEY (index_col_name,...)
or KEY [index_name] (index_col_name,...)
or INDEX [index_name] (index_col_name,...)
or UNIQUE [INDEX] [index_name] (index_col_name,...)
or FULLTEXT [INDEX] [index_name] (index_col_name,...)
or [CONSTRAINT symbol] FOREIGN KEY [index_name] (index_col_name,...)
[reference_definition]
or CHECK (expr)
type:
TINYINT[(length)] [UNSIGNED] [ZEROFILL]
or SMALLINT[(length)] [UNSIGNED] [ZEROFILL]
or MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL]
or INT[(length)] [UNSIGNED] [ZEROFILL]
or INTEGER[(length)] [UNSIGNED] [ZEROFILL]
or BIGINT[(length)] [UNSIGNED] [ZEROFILL]
or REAL[(length,decimals)] [UNSIGNED] [ZEROFILL]
or DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL]
or FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL]
or DECIMAL(length,decimals) [UNSIGNED] [ZEROFILL]
or NUMERIC(length,decimals) [UNSIGNED] [ZEROFILL]
or CHAR(length) [BINARY]
or VARCHAR(length) [BINARY]
or DATE
or TIME
or TIMESTAMP
or DATETIME
or TINYBLOB
or BLOB
or MEDIUMBLOB
or LONGBLOB
or TINYTEXT
or TEXT
or MEDIUMTEXT
or LONGTEXT
or ENUM(value1,value2,value3,...)
or SET(value1,value2,value3,...)
index_col_name:
col_name [(length)]
reference_definition:
REFERENCES tbl_name [(index_col_name,...)]
[MATCH FULL | MATCH PARTIAL]
[ON DELETE reference_option]
[ON UPDATE reference_option]
reference_option:
RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT
table_options:
TYPE = {BDB | HEAP | ISAM | InnoDB | MERGE | MRG_MYISAM | MYISAM }
or AUTO_INCREMENT = #
or AVG_ROW_LENGTH = #
or CHECKSUM = {0 | 1}
or COMMENT = "string"
or MAX_ROWS = #
or MIN_ROWS = #
or PACK_KEYS = {0 | 1 | DEFAULT}
or PASSWORD = "string"
or DELAY_KEY_WRITE = {0 | 1}
or ROW_FORMAT= { default | dynamic | fixed | compressed }
or RAID_TYPE= {1 | STRIPED | RAID0 } RAID_CHUNKS=# RAID_CHUNKSIZE=#
or UNION = (table_name,[table_name...])
or INSERT_METHOD= {NO | FIRST | LAST }
or DATA DIRECTORY="absolute path to directory"
or INDEX DIRECTORY="absolute path to directory"
select_statement:
[IGNORE | REPLACE] SELECT ... (Some legal select statement)
|
6. Syntax för ALTER TABLE
Med alter table förändrar du en tabell. Texten visar syntaxen för en korrekt ALTER TABLE-sats.
ALTER [IGNORE] TABLE tbl_name alter_spec [, alter_spec ...]
alter_specification:
ADD [COLUMN] create_definition [FIRST | AFTER column_name ]
or ADD [COLUMN] (create_definition, create_definition,...)
or ADD INDEX [index_name] (index_col_name,...)
or ADD PRIMARY KEY (index_col_name,...)
or ADD UNIQUE [index_name] (index_col_name,...)
or ADD FULLTEXT [index_name] (index_col_name,...)
or ADD [CONSTRAINT symbol] FOREIGN KEY index_name (index_col_name,...)
[reference_definition]
or ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
or CHANGE [COLUMN] old_col_name create_definition
[FIRST | AFTER column_name]
or MODIFY [COLUMN] create_definition [FIRST | AFTER column_name]
or DROP [COLUMN] col_name
or DROP PRIMARY KEY
or DROP INDEX index_name
or DISABLE KEYS
or ENABLE KEYS
or RENAME [TO] new_tbl_name
or ORDER BY col
or table_options
|
7. Syntax för DROP TABLE
Med DROP TABLE tar man bort en tabell. Texten visar syntaxen för en korrekt DROP TABLE-sats.
DROP TABLE [IF EXISTS] tbl_name [, tbl_name,...] [RESTRICT | CASCADE]
|
