MySQL – tvorba a úpravy tabulek Pavel Lasák
Stránka 1
1 Tabulky tvorba
1.1.1 Syntaxe CREATE TABLE
CREATE [ TEMPORARY ] TABLE [IF NOT EXISTS] table_name
(
column1 datatype [ NULL | NOT NULL ]
[ DEFAULT default_value ]
[ AUTO_INCREMENT ]
[ UNIQUE KEY | PRIMARY KEY ]
[ COMMENT 'string' ],
column2 datatype [ NULL | NOT NULL ]
[ DEFAULT default_value ]
[ AUTO_INCREMENT ]
[ UNIQUE KEY | PRIMARY KEY ]
[ COMMENT 'string' ],
...
| [CONSTRAINT [constraint_name]] PRIMARY KEY [ USING BTREE | HASH ]
(index_col_name, ...)
| [INDEX | KEY] index_name [ USING BTREE | HASH ] (index_col_name, ...)
| [CONSTRAINT [constraint_name]] UNIQUE [ INDEX | KEY ]
[ index_name ] [ USING BTREE | HASH ] (index_col_name, ...)
| {FULLTEXT | SPATIAL} [ INDEX | KEY] index_name (index_col_name, ...)
| [CONSTRAINT [constraint_name]]
FOREIGN KEY index_name (index_col_name, ...)
REFERENCES another_table_name (index_col_name, ...)
[ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
[ ON DELETE { RESTRICT | CASCADE | SET NULL | NO ACTION } ]
[ ON UPDATE { RESTRICT | CASCADE | SET NULL | NO ACTION } ]
| CHECK (expression)
{ENGINE | TYPE} = engine_name
| AUTO_INCREMENT = value
| AVG_ROW_LENGTH = value
| [DEFAULT] CHARACTER SET = charset_name
| CHECKSUM = {0 | 1}
| [DEFAULT] COLLATE = collation_name
| COMMENT = 'string'
| DATA DIRECTORY = 'absolute path'
| DELAY_KEY_WRITE = { 0 | 1 }
| INDEX DIRECTORY = 'absolute path'
| INSERT_METHOD = { NO | FIRST | LAST }
| MAX_ROWS = value
| MIN_ROWS = value
| PACK_KEYS = {0 | 1 | DEFAULT}
| PASSWORD = 'string'
| RAID_TYPE = { 1 | STRIPED | RAIDO }
RAID_CHUNKS = value
RAID_CHUNKSIZE = value
| ROW_FORMAT = {DEFAULT | DYNAMIC | FIXED | COMPRESSED}
| UNION = (table1, ... )
);
MySQL – tvorba a úpravy tabulek Pavel Lasák
Stránka 2
1.1.1 Syntaxe ALTER TABLE
ALTER TABLE tbl_name
[alter_specification [, alter_specification] ...]
[partition_options]
alter_specification:
table_options
| ADD [COLUMN] col_name column_definition
[FIRST | AFTER col_name ]
| ADD [COLUMN] (col_name column_definition,...)
| ADD {INDEX|KEY} [index_name]
[index_type] (index_col_name,...) [index_option] ...
| ADD [CONSTRAINT [symbol]] PRIMARY KEY
[index_type] (index_col_name,...) [index_option] ...
| ADD [CONSTRAINT [symbol]]
UNIQUE [INDEX|KEY] [index_name]
[index_type] (index_col_name,...) [index_option] ...
| ADD FULLTEXT [INDEX|KEY] [index_name]
(index_col_name,...) [index_option] ...
| ADD SPATIAL [INDEX|KEY] [index_name]
(index_col_name,...) [index_option] ...
| ADD [CONSTRAINT [symbol]]
FOREIGN KEY [index_name] (index_col_name,...)
reference_definition
| ALGORITHM [=] {DEFAULT|INPLACE|COPY}
| ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
| CHANGE [COLUMN] old_col_name new_col_name column_definition
[FIRST|AFTER col_name]
| LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}
| MODIFY [COLUMN] col_name column_definition
[FIRST | AFTER col_name]
| DROP [COLUMN] col_name
| DROP PRIMARY KEY
| DROP {INDEX|KEY} index_name
| DROP FOREIGN KEY fk_symbol
| DISABLE KEYS
| ENABLE KEYS
| RENAME [TO|AS] new_tbl_name
| RENAME {INDEX|KEY} old_index_name TO new_index_name
| ORDER BY col_name [, col_name] ...
| CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
| [DEFAULT] CHARACTER SET [=] charset_name [COLLATE [=] collation_name]
| DISCARD TABLESPACE
| IMPORT TABLESPACE
| FORCE
| {WITHOUT|WITH} VALIDATION
| ADD PARTITION (partition_definition)
| DROP PARTITION partition_names
| DISCARD PARTITION {partition_names | ALL} TABLESPACE
| IMPORT PARTITION {partition_names | ALL} TABLESPACE
| TRUNCATE PARTITION {partition_names | ALL}
| COALESCE PARTITION number
| REORGANIZE PARTITION partition_names INTO (partition_definitions)
| EXCHANGE PARTITION partition_name WITH TABLE tbl_name [{WITH|WITHOUT} VALIDATION]
| ANALYZE PARTITION {partition_names | ALL}
| CHECK PARTITION {partition_names | ALL}
| OPTIMIZE PARTITION {partition_names | ALL}
| REBUILD PARTITION {partition_names | ALL}
| REPAIR PARTITION {partition_names | ALL}
| REMOVE PARTITIONING
| UPGRADE PARTITIONING
index_col_name:
col_name [(length)] [ASC | DESC]
index_type:
USING {BTREE | HASH}
index_option:
KEY_BLOCK_SIZE [=] value
| index_type
| WITH PARSER parser_name
| COMMENT 'string'
table_options:
table_option [[,] table_option] ... (see CREATE TABLE options)
partition_options:
(see CREATE TABLE options)
MySQL – tvorba a úpravy tabulek Pavel Lasák
Stránka 3
1.1.2 Datové typy sloupců
MySQL – tvorba a úpravy tabulek Pavel Lasák
Stránka 4
1.1.3 Omezení
1.1.3.1 Integritiní
• UNIQUE •
DEFAULT vychozi_hodnota -
1.1.3.2 Ostatní
• AUTO_INCREMENT•
BINARY •
FULLTEXT INDEX •
INDEX –
• NOT NULL •
NULL
• PRIMARY KEY •
UNSIGNED•
ZEROFILL – podpora končí
1.1.4 Typy databázi
Pro pokročilejší správu tabulek (například restrikce) musíte změnit typ tabulky
• INNODB - uzamykání tabulky je vykonáváno na úrovni řádků; před použitím je nutná
kompilace MySQL s podporou INNODB
• MYISAM - standard MySQL od verze 3.23.0
• ISAM - standardní typ tabulky ve starších databázích; dnes nahrazen typem MYISAM
• ERGE - formát vhodný pro spojení MYISAM tabulek se stejně nadefinovanými poli
• HEAP - tabulka tohoto typu je uložena pouze v paměti (může být velmi rychlá), má ale řadu
omezení
• BDB - typ tabulky podobný INNODB; zatím ve fázi testování
1.1.5 Restrikce
▪ UPDATE – změna záznamu
▪ DELETE – smazaní záznamu
▪ UPDATE
o ON UPDATE RESTRICT - cizí klíč se nemůže změnit na požadavek změny odkazu, tudíž
se změna neprovede. Neboli nemůžeme změnit Id u zákazníka.
o ON UPDATE CASCADE – pokud se změní tak se změní i odkazy. Tj. pokud změníme Id
u zákazníka tak se změní i u kontaktu
▪ DELETE
o ON DELETE CASCADE - pokud cizí klíč ztratí referenci, tak se dany záznam se smaže
o ON DELETE RESTRICT - záznam s cizím klíčem se nemůže smazat na požadavek
odkazu, tudíž se odkaz ani záznam s cizím klíčem nesmaže.
o ON DELETE SET NULL - pokud je odkaz smazán, tak se cizí klíč nastaví na NULL (pouze,
pokud podle definice může byt sloupec NULL) , Např. při smazání „místnosti“ dojde k
„uvolnění nábytku“. Ten zůstane zachován, ale již nebude přiřazen k žádné místnosti.