szit

Ez az oldal csak olvasható. Megtekintheted a forrását, de nem változtathatod meg. Ha úgy gondolod, hogy ez helytelen, kérdezd az Adminisztrátorokat!
[[oktatas:adatbázis-kezelés:mysql|< MySQL]]

====== MySQL DDL ======

  * **Szerző:** Sallai András
  * Copyright (c) 2014, Sallai András
  * Licenc: [[https://creativecommons.org/licenses/by-sa/4.0/|CC BY-SA 4.0]]
  * Web: https://szit.hu
===== 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:
  * CREATE
  * DROP
  * ALTER


===== Adatbázis létrehozása =====


==== Létrehozása ====

create database db_nev;


==== Teljes szintaxis ====

<code sql>
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
    [create_specification] ...

create_specification:
    [DEFAULT] CHARACTER SET [=] charset_name
  | [DEFAULT] COLLATE [=] collation_name
</code>

==== Példa ====

<code sql>
create database zoldZrt;
</code>


<code sql>
create database aranyBt 
character set utf8
collate utf8_hungarian_ci
</code>


Ellenőrzés:

<code sql>
use aranyBt
show variables like "char%database";
show variables like "coll%database";
</code>

vagy:

<code sql>
use aranyBt
select @@character_set_database;
select @@collation_database;
</code>

===== Adatbázis módosítása =====
==== Teljes szintaxis ====

<code sql>
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
</code>
==== Példa ====

<code sql>
alter database zoldBolt collate=latin1_swedish_ci
</code>

vagy
<code sql>
ALTER DATABASE zoldBolt COLLATE=latin2_hungarian_ci
</code>

vagy
<code sql>
ALTER DATABASE zoldBolt COLLATE=utf8_hungarian_ci
</code>

===== Adatbázis törlése =====
==== Teljes szintaxis ====

<code sql>
DROP {DATABASE | SCHEMA} [IF EXISTS] db_name
</code>


==== Példa ====

<code sql>
DROP DATABASE zoldZrt
</code>

===== Tábla létrehozása =====
==== Létrehozás ====


<code sql>
create table szemelyek (
   az int not null primary key auto_increment,
   nev varchar(100),
   leiras text,
   telepules varchar(100),
   enabled boolean
)
</code>

A mezők alapértelmezett értéke:
<code sql>
telepules varchar(100) DEFAULT NULL,
</code>

<code sql>
eletkor int DEFAULT -1,
</code>


==== Szintaxis ====
<code sql>
CREATE TABLE [IF NOT EXISTS] tablaNev
(mező_meghatározások, ...)
tábla_beállítások
</code>

==== Teljes szintaxis ====

<code sql>
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...)
    [table_options]
    [partition_options]
</code>


<code sql>
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    [(create_definition,...)]
    [table_options]
    [partition_options]
    select_statement
</code>


<code sql>
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)
</code>
==== Példa ====

<code sql>
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)
    )
</code>


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

<code sql>
CREATE TABLE Munkasok LIKE Dolgozok;
</code>

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

Összetett kulcs létrehozása:

<code sql>
create table szemely(
    szemelyiSzam char(13),
    orszagKod char(2),
    nev varchar(100),
    info text,
    primary key(szemelyiSzam, orszagKod)
    )
</code>

Mindkét oszlopmeghatározás végére írva nem működik.
===== Tábla módosítása =====
==== Teljes szintaxis ====

<code sql>
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)
</code>


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

<code sql>
alter table megrendelesek add szallitoAz int
</code>

==== Elsődleges kulcs módosítása ====

<code sql>
alter table tabla1 modify id integer not null auto_increment
</code>

==== Idegenkulcs kezelése ====


Idegenkulcs hozzáadása:
<code sql>
alter table Szemelyek 
add 
foreign key(beosztasAz)
references Bosztasok(az);
</code>

Megadhatjuk az idegenkulcs nevét:
<code sql>
alter table Szemelyek 
add
CONSTRAINT `FK_Nev`
foreign key(beosztasAz)
references Bosztasok(az);
</code>


Ellenőrzés:
<code sql>
SHOW CREATE TABLE Szemelyek;
</code>


Idegenkulcs törlése:
<code sql>
ALTER TABLE DROP FOREIGN KEY fk_name;
</code>


Idegenkulcs meghatározásának cseréje, egyetlen utasítással:
<code sql>
ALTER TABLE táblanév
DROP FOREIGN KEY `fk_nev`,

ADD CONSTRAINT `fk_nev2` 
FOREIGN KEY (`idegenkulcsAz`)
REFERENCES `Masik_tabla` (`az`)
ON DELETE CASCADE;
</code>


Idegenkulcs másként:
<code sql>
alter table Tablanev add primary key (fid)
</code>


Összetett kulcs megadása:
<code sql>
alter table Tablanev add primary key (fid, vid)
</code>


Boolean típus alapértelmezéssel:
<code sql>
alter table dolgozok
add enabled boolean
not null default true after `fizetes`;
</code>

===== Tábla törlése =====
==== Szintaxis ====


<code sql>
drop table Tablanev
</code>


==== Teljes szintaxis ====

<code sql>
DROP [TEMPORARY] TABLE [IF EXISTS]
    tbl_name [, tbl_name] ...
    [RESTRICT | CASCADE]
</code>
==== Példa ====
<code sql>
DROP TABLE megrendelesek;
</code>

===== Linkek =====

  * http://dev.mysql.com/doc/refman/5.6/en/create-database.html (create database)
  * http://dev.mysql.com/doc/refman/5.6/en/alter-database.html (alter database)
  * http://dev.mysql.com/doc/refman/5.6/en/drop-database.html (drop database)
  * http://dev.mysql.com/doc/refman/5.6/en/create-table.html (create table)
  * http://dev.mysql.com/doc/refman/5.6/en/alter-table.html (alter table)
  * http://dev.mysql.com/doc/refman/5.6/en/drop-table.html (drop table)