Tartalomjegyzék

< MySQL

MySQL DDL

Bevezetés

A DDL a Data Definition Language szavak rövidítése, amelyet adatdefiníciós nyelvnek fordíthatunk. A DDL az SQL nyelvben használt parancsok egy részét értjük.

A következő SQL parancsok tartoznak ide:

Adatbázis létrehozása

Létrehozása

create database db_nev;

Teljes szintaxis

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
    [create_specification] ...
 
create_specification:
    [DEFAULT] CHARACTER SET [=] charset_name
  | [DEFAULT] COLLATE [=] collation_name

Példa

CREATE DATABASE zoldZrt;
CREATE DATABASE aranyBt 
CHARACTER SET utf8
COLLATE utf8_hungarian_ci

Ellenőrzés: 

USE aranyBt
SHOW VARIABLES LIKE "char%database";
SHOW VARIABLES LIKE "coll%database";

vagy: 

USE aranyBt
SELECT @@character_set_database;
SELECT @@collation_database;

Adatbázis módosítása

Teljes szintaxis

ALTER {DATABASE | SCHEMA} [db_name]
    alter_specification ...
ALTER {DATABASE | SCHEMA} db_name
    UPGRADE DATA DIRECTORY NAME
 
alter_specification:
    [DEFAULT] CHARACTER SET [=] charset_name
  | [DEFAULT] COLLATE [=] collation_name

Példa

ALTER DATABASE zoldBolt COLLATE=latin1_swedish_ci

vagy 

ALTER DATABASE zoldBolt COLLATE=latin2_hungarian_ci

vagy 

ALTER DATABASE zoldBolt COLLATE=utf8_hungarian_ci

Adatbázis törlése

Teljes szintaxis

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

Példa

DROP DATABASE zoldZrt

Tábla létrehozása

Létrehozás

CREATE TABLE szemelyek (
   az INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
   nev VARCHAR(100),
   leiras text,
   telepules VARCHAR(100),
   enabled BOOLEAN
)

A mezők alapértelmezett értéke: 

telepules VARCHAR(100) DEFAULT NULL,
eletkor INT DEFAULT -1,

Szintaxis

CREATE TABLE [IF NOT EXISTS] tablaNev
(mező_meghatározások, ...)
tábla_beállítások

Teljes szintaxis

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...)
    [table_options]
    [partition_options]
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    [(create_definition,...)]
    [table_options]
    [partition_options]
    select_statement
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    { LIKE old_tbl_name | (LIKE old_tbl_name) }
create_definition:
    col_name column_definition
  | [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)
      [index_option] ...
  | {INDEX|KEY} [index_name] [index_type] (index_col_name,...)
      [index_option] ...
  | [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY]
      [index_name] [index_type] (index_col_name,...)
      [index_option] ...
  | {FULLTEXT|SPATIAL} [INDEX|KEY] [index_name] (index_col_name,...)
      [index_option] ...
  | [CONSTRAINT [symbol]] FOREIGN KEY
      [index_name] (index_col_name,...) reference_definition
  | CHECK (expr)
 
column_definition:
    data_type [NOT NULL | NULL] [DEFAULT default_value]
      [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]
      [COMMENT 'string']
      [COLUMN_FORMAT {FIXED|DYNAMIC|DEFAULT}]
      [STORAGE {DISK|MEMORY|DEFAULT}]
      [reference_definition]
 
data_type:
    BIT[(LENGTH)]
  | TINYINT[(LENGTH)] [UNSIGNED] [ZEROFILL]
  | SMALLINT[(LENGTH)] [UNSIGNED] [ZEROFILL]
  | MEDIUMINT[(LENGTH)] [UNSIGNED] [ZEROFILL]
  | INT[(LENGTH)] [UNSIGNED] [ZEROFILL]
  | INTEGER[(LENGTH)] [UNSIGNED] [ZEROFILL]
  | BIGINT[(LENGTH)] [UNSIGNED] [ZEROFILL]
  | REAL[(LENGTH,decimals)] [UNSIGNED] [ZEROFILL]
  | DOUBLE[(LENGTH,decimals)] [UNSIGNED] [ZEROFILL]
  | FLOAT[(LENGTH,decimals)] [UNSIGNED] [ZEROFILL]
  | DECIMAL[(LENGTH[,decimals])] [UNSIGNED] [ZEROFILL]
  | NUMERIC[(LENGTH[,decimals])] [UNSIGNED] [ZEROFILL]
  | DATE
  | TIME[(fsp)]
  | TIMESTAMP[(fsp)]
  | DATETIME[(fsp)]
  | YEAR
  | CHAR[(LENGTH)]
      [CHARACTER SET charset_name] [COLLATE collation_name]
  | VARCHAR(LENGTH)
      [CHARACTER SET charset_name] [COLLATE collation_name]
  | BINARY[(LENGTH)]
  | VARBINARY(LENGTH)
  | TINYBLOB
  | BLOB
  | MEDIUMBLOB
  | LONGBLOB
  | TINYTEXT [BINARY]
      [CHARACTER SET charset_name] [COLLATE collation_name]
  | TEXT [BINARY]
      [CHARACTER SET charset_name] [COLLATE collation_name]
  | MEDIUMTEXT [BINARY]
      [CHARACTER SET charset_name] [COLLATE collation_name]
  | LONGTEXT [BINARY]
      [CHARACTER SET charset_name] [COLLATE collation_name]
  | ENUM(value1,value2,value3,...)
      [CHARACTER SET charset_name] [COLLATE collation_name]
  | SET(value1,value2,value3,...)
      [CHARACTER SET charset_name] [COLLATE collation_name]
  | spatial_type
 
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'
 
reference_definition:
    REFERENCES tbl_name (index_col_name,...)
      [MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
      [ON DELETE reference_option]
      [ON UPDATE reference_option]
 
reference_option:
    RESTRICT | CASCADE | SET NULL | NO ACTION
 
table_options:
    table_option [[,] table_option] ...
 
table_option:
    ENGINE [=] engine_name
  | AUTO_INCREMENT [=] VALUE
  | AVG_ROW_LENGTH [=] VALUE
  | [DEFAULT] CHARACTER SET [=] charset_name
  | CHECKSUM [=] {0 | 1}
  | [DEFAULT] COLLATE [=] collation_name
  | COMMENT [=] 'string'
  | CONNECTION [=] 'connect_string'
  | DATA DIRECTORY [=] 'absolute path to directory'
  | DELAY_KEY_WRITE [=] {0 | 1}
  | INDEX DIRECTORY [=] 'absolute path to directory'
  | INSERT_METHOD [=] { NO | FIRST | LAST }
  | KEY_BLOCK_SIZE [=] VALUE
  | MAX_ROWS [=] VALUE
  | MIN_ROWS [=] VALUE
  | PACK_KEYS [=] {0 | 1 | DEFAULT}
  | PASSWORD [=] 'string'
  | ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}
  | STATS_AUTO_RECALC [=] {DEFAULT|0|1}
  | STATS_PERSISTENT [=] {DEFAULT|0|1}
  | STATS_SAMPLE_PAGES [=] VALUE
  | TABLESPACE tablespace_name [STORAGE {DISK|MEMORY|DEFAULT}]
  | UNION [=] (tbl_name[,tbl_name]...)
 
partition_options:
    PARTITION BY
        { [LINEAR] HASH(expr)
        | [LINEAR] KEY [ALGORITHM={1|2}] (column_list)
        | RANGE{(expr) | COLUMNS(column_list)}
        | LIST{(expr) | COLUMNS(column_list)} }
    [PARTITIONS num]
    [SUBPARTITION BY
        { [LINEAR] HASH(expr)
        | [LINEAR] KEY [ALGORITHM={1|2}] (column_list) }
      [SUBPARTITIONS num]
    ]
    [(partition_definition [, partition_definition] ...)]
 
partition_definition:
    PARTITION partition_name
        [VALUES 
            {LESS THAN {(expr | value_list) | MAXVALUE} 
            | 
            IN (value_list)}]
        [[STORAGE] ENGINE [=] engine_name]
        [COMMENT [=] 'comment_text' ]
        [DATA DIRECTORY [=] 'data_dir']
        [INDEX DIRECTORY [=] 'index_dir']
        [MAX_ROWS [=] max_number_of_rows]
        [MIN_ROWS [=] min_number_of_rows]
        [TABLESPACE [=] tablespace_name]
        [NODEGROUP [=] node_group_id]
        [(subpartition_definition [, subpartition_definition] ...)]
 
subpartition_definition:
    SUBPARTITION logical_name
        [[STORAGE] ENGINE [=] engine_name]
        [COMMENT [=] 'comment_text' ]
        [DATA DIRECTORY [=] 'data_dir']
        [INDEX DIRECTORY [=] 'index_dir']
        [MAX_ROWS [=] max_number_of_rows]
        [MIN_ROWS [=] min_number_of_rows]
        [TABLESPACE [=] tablespace_name]
        [NODEGROUP [=] node_group_id]
 
select_statement:
    [IGNORE | REPLACE] [AS] SELECT ...   (SOME valid SELECT statement)

Példa

CREATE TABLE Vevok2 (
    az INT NOT NULL PRIMARY KEY,
    nev CHAR(50),
    kapcsolat VARCHAR(50),
    varos VARCHAR(50),
    cim VARCHAR(100),
    tel text,
    email VARCHAR(100)
    )

Tábla létrehozása már létező tábla alapján

CREATE TABLE Munkasok LIKE Dolgozok;

Tábla létrehozása összetett kulcs alapján

Összetett kulcs létrehozása: 

CREATE TABLE szemely(
    szemelyiSzam CHAR(13),
    orszagKod CHAR(2),
    nev VARCHAR(100),
    info text,
    PRIMARY KEY(szemelyiSzam, orszagKod)
    )

Mindkét oszlopmeghatározás végére írva nem működik.

Tábla módosítása

Teljes szintaxis

ALTER [IGNORE] 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
  | 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
  | ADD PARTITION (partition_definition)
  | DROP PARTITION partition_names
  | TRUNCATE PARTITION {partition_names | ALL}
  | COALESCE PARTITION NUMBER
  | REORGANIZE PARTITION partition_names INTO (partition_definitions)
  | EXCHANGE PARTITION partition_name WITH TABLE tbl_name
  | 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
 
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)

Egy új mező táblához adása

ALTER TABLE megrendelesek ADD szallitoAz INT

Elsődleges kulcs módosítása

ALTER TABLE tabla1 MODIFY id INTEGER NOT NULL AUTO_INCREMENT

Idegenkulcs kezelése

Idegenkulcs hozzáadása: 

ALTER TABLE Szemelyek 
ADD 
FOREIGN KEY(beosztasAz)
REFERENCES Bosztasok(az);

Megadhatjuk az idegenkulcs nevét: 

ALTER TABLE Szemelyek 
ADD
CONSTRAINT `FK_Nev`
FOREIGN KEY(beosztasAz)
REFERENCES Bosztasok(az);

Ellenőrzés: 

SHOW CREATE TABLE Szemelyek;

Idegenkulcs törlése: 

ALTER TABLE DROP FOREIGN KEY fk_name;

Idegenkulcs meghatározásának cseréje, egyetlen utasítással: 

ALTER TABLE táblanév
DROP FOREIGN KEY `fk_nev`,
 
ADD CONSTRAINT `fk_nev2` 
FOREIGN KEY (`idegenkulcsAz`)
REFERENCES `Masik_tabla` (`az`)
ON DELETE CASCADE;

Idegenkulcs másként: 

ALTER TABLE Tablanev ADD PRIMARY KEY (fid)

Összetett kulcs megadása: 

ALTER TABLE Tablanev ADD PRIMARY KEY (fid, vid)

Boolean típus alapértelmezéssel: 

ALTER TABLE dolgozok
ADD enabled BOOLEAN
NOT NULL DEFAULT TRUE after `fizetes`;

Tábla törlése

Szintaxis

DROP TABLE Tablanev

Teljes szintaxis

DROP [TEMPORARY] TABLE [IF EXISTS]
    tbl_name [, tbl_name] ...
    [RESTRICT | CASCADE]

Példa

DROP TABLE megrendelesek;

Linkek