Типы данных и операторы определения данных SQL

В данном конспекте рассмотрим основные типы данных SQL а также операторы операторы определения данных (Data Definition Language, DDL), которые позволяют создавать, изменять и удалять объекты базы данных (таблицы, представления и т.п.)

Типы данных SQL

В SQL существуют следующие четыре группы типов данных:

Числовые:

Целые:

TINYINT[] - 1 байт
SMALLINT[] - 2 байта
MEDIUMINT[] - 3 байта
INT[] - 4 байта
BIGINT[] - 8 байт

В квадратных скобках указывается количество выводимых символов

С плавающей точкой:

FLOAT[M, D] - 4 байта
DOUBLE[M, D] (REAL, DOUBLE PRECISION) - 8 байт
DECIMAL[M, D] (DEC, NUMERIC) - M + 2 байта

В квадратных скобках указывается количество под мантиссу и экспоненту

Бинарные:

BOOLEAN - 1 байт (алиас для tinyint)
BIT

Строковые:

CHAR(M) - M - кол-во символов, строка фиксированной длины
VARCHAR(M) - строка переменной длины
TINYBLOB, TINYTEXT - 2⁸ - 1 символов
BLOB, TEXT - 2¹⁶ - 1 символов
MEDIUMBLOB, MEDIUMTEXT - 2²⁴ - 1 символов
LONGBLOB, LONGTEXT - 2³² - 1 символов
ENUM(‘value1’, ‘value2’…) - 1 или 2 байта, 65536 допустимых значений
SET(‘value1’, ‘value2’…) - 1,2,3,4 или 8 байт, 64 элемента может принимать несколько значений из этого множества

Главное различие char и varchar: для char сразу выделяется место под максимальное количество символов, что повышает эффективность поиска но увеличивает расход дискового пространнства.

Календарные:

DATE - 3 байта, от “1000-01-01” до “9999-12-31”
TIME - 3 байта, от “-828:59:59” до “828:59:59”
DATETIME - 8 байт, от “1000-01-01 00:00:00” до “9999-12-31 59:59:59”
TIMESTAMP - 4 байта, от “1970-01-01 00:00:00” до “2038-12-31 59:59:59” хранится количество секунд от 1970 года
YEAR - 1 байт, YEAR(4) c 1901 до 2155; YEAR(2) c 1970 до 2069

NULL:

при конкатенации cтрок с NULL все превращается в NULL.
любые проверки операторами <, > =, <> дают false
COUNT(поле) - считает кол-во не NULL строк, хотя COUNT(*) считает все строки включая NULL

Оператор CREATE

Общая сигнатура такова:

  CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
      (create_definition,...)
      [table_options]
      [partition_options]

Где 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]

Где 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

Оператор ALTER

Общая сигнатура такова:

  ALTER [ONLINE|OFFLINE] [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] ...

Ограничения (CONSTRAINT) на значения данных

Составной первичный ключ:

  CREATE TABEL table_name (
      firstname CHAR(10) NOT NULL,
      lastname CHAR(10) NOT NULL,
      city CHAR(10),
      PRIMARY KEY (firstname, lastname)
  );

Проверка вводимых значений CHECK:

  CREATE TABEL table_name (
      id INTEGER PRIMARY KEY,
      firstname CHAR(10) NOT NULL,
      comm DECIMAL,
      city CHAR(10),
      CHECK(comm < .15 OR city = 'Barcelona')
  );

Именование ограничений и удаление их:

  CREATE TABEL table_name (
      id INTEGER PRIMARY KEY,
      firstname CHAR(10) NOT NULL,
      comm DECIMAL,
      city CHAR(10),
      CONSTRAINT LuckyBarcelona CHECK(comm < .15 OR city = 'Barcelona')
  );

CONSTRAINT — является не обязательным;
Использование:
ALTER TABLE table_name DROP CONSTRAINT LuckyBarcelona

Добавление внешнего ключа через alter table:

  ALTER TABLE Salespeople ADD FOREIGN KEY (column) REFERENCES Customers (column);

Действия выполняемые по ссылке:

CASCADE: - п и UPDATE — значение внешнего ключа заменяется новым значением; - п и DELETE — строки внеш. Ключа будут удалены.

SET NULL — значения внешнего ключа будут установлены в NULL.

SET DEFAULT — будет установлено зн-е по дефолту для внешнего ключа.

NO ACTION — значение по дефолту, зн-е не меняется но если есть ссылки оператор игнорируется.

Пример:

  CREATE TABEL table_name (
      id INTEGER PRIMARY KEY,
      firstname CHAR(10) NOT NULL,
      snum INTEGER REFERENCES other_table
          ON UPDATE CASCADE ON DELETE NO ACTION);

Geek Notes

заметки/статьи/переводы на темы программирования, алгоритмов и etc.