Команда sql alter table - alter table add, alter table column, alter table oracle, alter table add column, sql alter table, alter column

Вставка из другой таблицы с условием INSERT INTO … SELECT … WHERE …

А теперь представим, что нам нужно вставить только те строки из table1, у которых столбец «c» равен 333. Тогда наш запрос будет выглядеть так

INSERT INTO table2 (a, b, c)
    SELECT a, b, c FROM table1 
    WHERE c = 333;

То есть мы просто вставляем данные в таблицу, которые выбрали из другой таблицы при помощи обычного SELECT запроса

Теперь представим, что у нас в таблице table2 — 4 столбца, а в table1 — 3. При этом четвертый столбец в table2 обязательный. Чтобы выйти из этой ситуации, нужно передать какое-нибудь подходящее значение в этот лишний столбец. У нас чисто абстрактная задача, поэтому давайте передадим туда просто единицу.

INSERT INTO table2 (a, b, c, d)
    SELECT a, b, c, 1 FROM table1 
    WHERE c = 333;

Теперь в столбец d у нас записалась единица и проблема решена.

Хранение, Производительность, и Соображения Параллелизма

В большинстве случаев, делает
временную копию исходной таблицы. MySQL ожидает других операций, которые изменяют таблицу, затем продолжается.
Это включает изменение в копию, удаляет исходную таблицу, и переименовывает новый. В то время как выполняется, исходная таблица читаема другими сеансами (с
исключением, отмеченным коротко). Обновления и записи к таблице, которые начинаются после работа начинается, останавливаются, пока новая таблица не готова,
затем автоматически перенаправляется к новой таблице без любых отказавших обновлений. Временная таблица
создается в каталоге базы данных новой таблицы. Это может отличаться от каталога базы данных исходной таблицы
для операции, которые переименовывают таблицу к различной базе данных.

Исключение, упомянутое ранее, является этим блочные чтения (не только пишет) в точке, где это готово установить новую версию таблицы файл, отбросьте старый файл, и очистите устаревшие структуры таблиц от таблицы
и табличных кэшей определения. В этой точке это должно получить монопольную блокировку. Чтобы сделать так, это
ожидает текущих читателей, чтобы закончиться, и блокирует новые чтения (и записи).

Для таблицы, можно убыстриться, индексируют воссоздание (самая медленная
часть процесса изменения), устанавливая системная переменная к высокому значению.

Для некоторых операций, оперативного возможно, который не требует временной таблицы:

Для без любых других опций MySQL
просто переименовывает любые файлы, которые соответствуют таблице не делая копию. (Можно также использовать оператор, чтобы переименовать таблицы. См. Раздел
13.1.26,» Синтаксис».) Любые полномочия,
предоставленные определенно для переименованной таблицы, не перемещаются на новое имя. Они должны быть
изменены вручную.
Изменения, которые изменяют только табличные метаданные и не табличные данные,
могут быть сразу сделаны, изменяя таблицу файл и не затрагивающее
табличное содержание. Следующие изменения являются быстрыми изменениями, которые могут быть пробиты:
- Переименование столбца.
- Изменение значения по умолчанию столбца.
- Изменение определения или столбец, добавляя новое перечисление или элементы
  множества до конца списка допустимых задействованных
  значений, пока размер хранения типа данных не изменяется. Например, добавляя элемент к a столбец, у которого есть 8 элементов, изменяет
  необходимое хранение на значение от 1 байта до 2 байтов; это потребует табличной копии.
  Добавление элементов в середине изменения нумерации причин списка существующих элементов,
  которое требует табличной копии.
с , , , , или не создает временных таблиц (кроме тех случаев, когда используемый с таблицы); однако, эти операции могут и действительно создавать
временные файлы раздела.

или операции для или разделы являются
непосредственными операциями или почти так. или операции для или разделы не копируют данные между всеми разделами, если или
использовался; это — эффективно то же самое как составление новой таблицы, хотя или работа является
выполняемым разделом разделом. операции копируют только
измененные разделы и не касаются неизменных.
Переименование индексирования.
Добавление или отбрасывание индексирования, для .

Можно вызвать работа, которая иначе не потребовала бы, чтобы табличная
копия использовала метод временной таблицы (как поддерживающийся в MySQL 5.0), устанавливая системная переменная к , или
определение как один из
пункты. Если есть конфликт между установка и
пункт со значением кроме , пункт
имеет приоритет. ( то же самое определение нет пункт вообще.)

Определение заставляет работу использовать оперативный метод для
пунктов и механизмов хранения, которые поддерживают это, и сбой с ошибкой иначе, таким образом избегая длинной
табличной копии, если Вы пытаетесь изменить таблицу, которая использует различный механизм хранения, чем Вы
ожидаете. См. Раздел 5.5, «Онлайновый DDL для Таблицы» для информации об онлайновом DDL для таблицы.

Можно управлять уровнем параллельного чтения и записи таблицы, в то время как это изменяется, используя пункт. Определение значения не по умолчанию для этого пункта позволяет Вам
требовать определенного количества параллельного доступа или исключительности во время изменить работы, и
останавливает работу, если требуемая степень блокировки не доступна. Параметры для пункт:

```
LOCK = DEFAULT
```
```
LOCK = NONE
```
```
LOCK = SHARED
```
```
LOCK = EXCLUSIVE
```

Можно также использовать чтобы выполнить «нуль» изменяют работу, которая восстанавливает таблицу. Ранее опция была распознана, но проигнорирована.

Вставка сразу нескольких строк с помощью INSERT INTO

Если нам нужно вставить несколько строк, то мы просто перечисляем группы значений через запятую выглядит это так:

INSERT INTO table1 (a, b, c) VALUES 
    (1, 222, 333), 
    (2, 555, 666),
    (3, 555, 333);

Таким образом мы вставили 3 строки в нашу таблицу table1. Их может быть и больше. В MySQL четкого предела нет, однако он все таки существует и зависит от параметра max_allowed_packet который ограничивает размер запроса. Если вы установите то размер запроса будет ограничен 500MB но делайте это в очень крайнем случае. Обычно всегда можно найти решение и разделить 1 большой запрос, на несколько более мелких и вставлять например не больше 1000 строк за один цикл.

Как создать новый столбец со значением по умолчанию вместо NULL

Вы также можете создать столбец со значением по умолчанию, используя ключевое слово default, за которым следует используемое значение. Тогда пользователи увидят это значение по умолчанию вместо того, чтобы пропущенные значения были заполнены NULL.

Предположим, что скоро у нас появятся международные пользователи, и мы хотим добавить столбец country. Все наши существующие пользователи из США, поэтому мы можем использовать это значение по умолчанию.

Наша таблица будет выглядеть так:

id	name	age	state	email	id_number	country
1	Paul	24	Michigan	paul@example.com	NULL	United States
2	Molly	22	New Jersey	molly@example.com	NULL	United States
3	Robert	19	New York	robert@example.com	NULL	United States

Как изменить тип данных столбца

В SQL Server можно изменить тип данных столбца с помощью выражения , как показано ниже:

ALTER TABLE имя_таблицы ALTER COLUMN имя_таблицы новый_тип_данных;

Однако MySQL не поддерживает синтаксис . Там используется альтернативное выражение , которое изменяет столбец:

ALTER TABLE имя_таблицы MODIFY имя_столбца новый_тип_данных;

Следующий оператор изменяет текущий тип данных столбца в таблице с на и длину с 20 на 15.

mysql> ALTER TABLE shippers MODIFY phone CHAR(15);

Аналогично можно использовать выражение для переключения допущения нулевых значений в столбце таблицы MySQL. Это реализуется при помощи повторного определения столбца и добавления ограничения или в конце, как показано ниже:

mysql> ALTER TABLE shippers MODIFY shipper_name CHAR(15) NOT NULL;

Как добавить колонку к таблице MySQL?

Синтаксис добавления колонки в таблицу:

<текстареа class=»crayon-plain print-no» data-settings=»dblclick» readonly=»» style=»-moz-tab-size:4; -o-tab-size:4; -webkit-tab-size:4; tab-size:4; font-size: 14px !important; line-height: 28px !important;»> ALTER TABLE table_name ADD column_name data_type attributes;

1	ALTERTABLEtable_nameADDcolumn_namedata_typeattributes;

Для примера добавим к таблице books колонку с именем discount, в которой будет хранится процент скидки на книги и amount для хранения количества книг.

Можно добавлять, удалять и модифицировать сразу несколько колонок за раз.

1	ALTERTABLEbooksADDdiscountTINYINTUNSIGNED,ADDamountSMALLINTUNSIGNED;

Каждая новая колонка добавляется в конец таблицы. Если вы хотите добавить новую колонку после определенной колонки, то используйте команду AFTER.

Добавим колонку shelf_position сразу после колонки price.

1	ALTERTABLEbooksADDshelf_positionVARCHAR(20)AFTERPrice;

Получение справки

Каждый раз когда мне нужно выполнить какую-нибудь редкую или сложную команду SQL, я лезу в справку, благо она всегда доступна через .

без аргументов покажет список доступных разделов. Это уже само по себе полезно, чтобы подсмотреть синтаксис или . Допустим, вы хотите удалить ограничение NOT NULL для столбца, но не помните точно как это делается через команду ALTER. поможет в этом и предоставит более чем подробную информацию.
psql использует пагинацию для большого вывода, так что там работает поиск. Наберите и увидите все упоминания, с помощью n и N можно переходить к следующему и предыдущему соответственно.
Поискав NULL, находится вот такая команда: . Теперь можно составить нужную команду, не заглядывая в Google.

Как видите, даёт справку только по SQL командам. Для внутренних команд psql, которые начинаются с обратного слэша, есть .

Практическое упражнение № 3

На основе приведенной ниже таблицы customers добавьте два столбца — один столбец с именем contact_name, который является типом данных char(50), и один столбец с именем last_contacted, у которого тип данных date.

PgSQL

CREATE TABLE customers
( customer_id int NOT NULL,
customer_name char(50) NOT NULL,
address char(50),
city char(50),
state char(25),
zip_code char(10),
CONSTRAINT customers_pk PRIMARY KEY (customer_id)
);

1
2
3
4
5
6
7
8
9

CREATETABLEcustomers
(customer_idintNOT NULL,

customer_namechar(50)NOT NULL,

addresschar(50),

citychar(50),

statechar(25),

zip_codechar(10),

CONSTRAINTcustomers_pkPRIMARYKEY(customer_id)
);

Решение для упражнения № 3

Следующий SQL оператор ALTER TABLE добавит столбцы contact_name и last_contacted в таблицу customers.

PgSQL

ALTER TABLE customers
ADD (contact_name char(50),
last_contacted date);

1
2
3

ALTERTABLEcustomers

ADD(contact_namechar(50),

last_contacteddate);

Изменить столбец в таблице

Синтаксис

Синтаксис SQL ALTER TABLE для изменения столбца в существующей таблице.

Для Oracle, MySQL, MariaDB.

ALTER TABLE table_name
MODIFY column_name column_type;

Для SQL Server.

ALTER TABLE table_name
ALTER COLUMN column_name column_type;

Для PostgreSQL.

ALTER TABLE table_name
ALTER COLUMN column_name TYPE column_definition;

Пример

Давайте посмотрим, как изменить столбец с именем supplier_name, используя команду ALTER TABLE

Обратите внимание, что в большинстве баз данных Синтаксис немного отличается.
Для Oracle

PgSQL

ALTER TABLE supplier
MODIFY supplier_name char(100) NOT NULL;

1 2	ALTERTABLEsupplier MODIFYsupplier_namechar(100)NOT NULL;

Для MySQL и MariaDB.

PgSQL

ALTER TABLE supplier
MODIFY supplier_name VARCHAR(100) NOT NULL;

1 2	ALTERTABLEsupplier MODIFYsupplier_nameVARCHAR(100)NOT NULL;

Для SQL Server.

PgSQL

ALTER TABLE supplier
ALTER COLUMN supplier_name VARCHAR(100) NOT NULL;

1 2	ALTERTABLEsupplier ALTERCOLUMNsupplier_nameVARCHAR(100)NOT NULL;

Для PostgreSQL.

PgSQL

ALTER TABLE supplier
ALTER COLUMN supplier_name TYPE CHAR(100),
ALTER COLUMN supplier_name SET NOT NULL;

1
2
3

ALTERTABLEsupplier

ALTERCOLUMNsupplier_nameTYPECHAR(100),

ALTERCOLUMNsupplier_nameSETNOT NULL;

SQL Учебник

SQL ГлавнаяSQL ВведениеSQL СинтаксисSQL SELECTSQL SELECT DISTINCTSQL WHERESQL AND, OR, NOTSQL ORDER BYSQL INSERT INTOSQL Значение NullSQL Инструкция UPDATESQL Инструкция DELETESQL SELECT TOPSQL MIN() и MAX()SQL COUNT(), AVG() и …SQL Оператор LIKESQL ПодстановочныйSQL Оператор INSQL Оператор BETWEENSQL ПсевдонимыSQL JOINSQL JOIN ВнутриSQL JOIN СлеваSQL JOIN СправаSQL JOIN ПолноеSQL JOIN СамSQL Оператор UNIONSQL GROUP BYSQL HAVINGSQL Оператор ExistsSQL Операторы Any, AllSQL SELECT INTOSQL INSERT INTO SELECTSQL Инструкция CASESQL Функции NULLSQL ХранимаяSQL Комментарии

Создание таблицы в PostgreSQL

Попробуйте создать тестовую таблицу. Для примера назовём её pg_equipment и поместим в неё данные о различном оборудовании для детских площадок. Введите следующее определение таблицы:

Чтобы просмотреть таблицу, введите в командную строку \d.

Эта команда выведет на экран таблицу и создаст последовательность согласно типу данных serial.

Изменение таблиц в PostgreSQL

Теперь попробуйте внести в таблицу изменения. Для этого используется следующий синтаксис:

К примеру, можно добавить в таблицу pg_equipment новый столбец.

Чтобы просмотреть новый столбец, введите:

Чтобы добавить значение по умолчанию, которое указывает, что оборудование следует рассматривать как рабочее, если не указано иное, используйте следующую команду:

Чтобы указать также, что значение не может быть нулём, используйте:

Чтобы переименовать столбец, введите:

Удалить столбец можно с помощью команды:

Чтобы переименовать всю таблицу, введите:

Удаление таблиц PostgreSQL

Чтобы удалить таблицу PostgreSQL, введите:

Если применить эту команду к таблице, которой не существует, появится следующее сообщение об ошибке:

Чтобы избежать этой ошибки, можно добавить в команду IF EXISTS; тогда таблица будет удалена, если она существует. В любом случае команда будет выполнена успешно.

В этот раз команда сообщает, что заданная таблица не найдена, но не возвращает ошибки.

Команда ALTER TABLE в MySQL: как добавить, удалить и изменить столбцы

Добавить в избранное

Главное меню » Базы данных » База данных MySQL » Команда ALTER TABLE в MySQL: как добавить, удалить и изменить столбцы

Использование и примеры ALTER TABLE

Во-первых, давайте создадим базу данных и таблицу, которые мы будем называть соответственно «mytest» и «andreyex». Вот как вы это сделаете:

Переименовать таблицу

Синтаксис для переименования таблицы:

Итак, чтобы изменить таблицу andreyex на «myusers», выполните следующие действия:

Добавление столбца и изменение свойств столбца

Синтаксис добавления столбца в таблицу:

Чтобы удалить столбец:

Здесь мы добавим строковый столбец «address», набрав:

Добавьте столбец TIMESTAMP с именем «date», введя следующую команду:

Добавьте индекс в столбец с именем «id», выполнив:

Также возможно сделать несколько дополнений одновременно:

Теперь наша таблица должна выглядеть так:

Изменить тип столбца

Синтаксис модификации столбцов:

Чтобы изменить поле адреса, чтобы разрешить более крупные строки:

Мы также можем объединить сразу несколько модификаций:

С помощью этой команды мы также изменили столбец с именем «name» на «lastname» и указали ему значения NULL.

Изменение значения столбца по умолчанию

Чтобы изменить значение по умолчанию для столбца, используйте синтаксис SET DEFAULT:

Теперь наша финальная таблица выглядит так:

Почему вы должны использовать ALTER TABLE?

Внесение изменений в структуру базы данных MySQL через командную строку и ALTER TABLE иногда может быть более быстрым и гибким, чем использование PHPMyAdmin или аналогичных инструментов графического интерфейса.

Команда предлагает еще больше возможностей, которые здесь мы не рассматривали. Для получения дополнительной информации ознакомьтесь с документацией по MySQL или задайте вопрос в разделе комментариев.

Если вы нашли ошибку, пожалуйста, выделите фрагмент текста и нажмите Ctrl+Enter.

Как переименовать столбец с помощью ALTER TABLE

Вы можете переименовать столбец с помощью приведенного ниже кода. Вы выбираете таблицу с помощью ALTER TABLE имя_таблицы, а затем указываете, какой столбец переименовать и во что переименовать, с помощью RENAME COLUMN old_name TO new_name.

Давайте посмотрим на ту же таблицу, которую мы использовали в предыдущем примере:

id	name	age	state	email	id_number	country
1	Paul	24	Michigan	paul@example.com	NULL	United States
2	Molly	22	New Jersey	molly@example.com	NULL	United States
3	Robert	19	New York	robert@example.com	NULL	United States

Чтобы избежать путаницы между столбцами id и id_number, давайте переименуем первый в user_id.

Сначала мы укажем таблицу с помощью ALTER TABLE users, а затем объявим имя столбца, чтобы оно изменилось на то, что мы хотим изменить, с помощью RENAME COLUMN id TO user_id.

После выполения запроса таблица будет выглядеть так:

user_id	name	age	state	email	id_number	country
1	Paul	24	Michigan	paul@example.com	NULL	United States
2	Molly	22	New Jersey	molly@example.com	NULL	United States
3	Robert	19	New York	robert@example.com	NULL	United States

Если вы используете инструмент рефакторинга базы данных для изменения имени столбца вместо использования ALTER TABLE, он будет управлять всеми зависимостями и обновлять их с новым именем столбца.

Если у вас небольшая база данных, возможно, вам не о чем беспокоиться, но об этом важно помнить

Переименовать столбец в таблице

Синтаксис

Синтаксис SQL ALTER TABLE для переименования столбца в существующей таблице.

Для Oracle и PostgreSQL.

ALTER TABLE table_name
RENAME COLUMN old_name TO new_name;

Для SQL Server (с использованием хранимой процедуры sp_rename).

sp_rename ‘table_name.old_column’, ‘new_name’, ‘COLUMN’;

Для MySQL и MariaDB.

ALTER TABLE table_name
CHANGE COLUMN old_name TO new_name;

Пример

Давайте рассмотрим пример, который переименовывает столбец в таблице supplier из supplier_name в sname.

Для Oracle (9i Rel2 и выше) и PostgreSQL.

PgSQL

ALTER TABLE supplier
RENAME COLUMN supplier_name TO sname;

1 2	ALTERTABLEsupplier RENAMECOLUMNsupplier_nameTOsname;

Для SQL Server (с использованием хранимой процедуры sp_rename).

PgSQL

sp_rename ‘supplier.supplier_name’, ‘sname’, ‘COLUMN’;

1	sp_rename’supplier.supplier_name’,’sname’,’COLUMN’;

Для MySQL и MariaDB.

PgSQL

ALTER TABLE supplier
CHANGE COLUMN supplier_name sname VARCHAR(100);

1 2	ALTERTABLEsupplier CHANGECOLUMNsupplier_namesnameVARCHAR(100);

В MySQL и MariaDB вы должны указать тип данных столбца при его переименовании.

Сводка

Существует два распространенных метода, которые можно использовать для удаления дублирующихся записей из SQL Server таблицы. Для демонстрации сначала создайте пример таблицы и данных:

Затем попробуйте следующие методы, чтобы удалить дублирующиеся строки из таблицы.

Способ 1

Запустите следующий сценарий:

Этот скрипт принимает следующие действия в данном порядке:

Перемещает один экземпляр любой дублирующейся строки в исходной таблице в дублирующую таблицу.
Удаляет все строки из исходной таблицы, которые также находятся в дублирующей таблице.
Перемещает строки в таблицу дубликатов обратно в исходную таблицу.
Сбрасывает таблицу дубликата.

Этот метод прост. Однако для создания дублирующей таблицы в базе данных необходимо иметь достаточно места. Этот метод также накладные расходы, так как данные перемещаются.

Кроме того, если в вашей таблице есть столбец IDENTITY, при восстановлении данных в исходной таблице необходимо использовать set IDENTITY_INSERT ON.

Способ 2

Функция ROW_NUMBER, которая была представлена в Microsoft SQL Server 2005 г., значительно упрощает эту операцию:

Этот скрипт принимает следующие действия в данном порядке:

Использует функцию для раздела данных на основе которых может быть один или несколько столбцов, разделенных запятой.
Удаляет все записи, которые получили значение больше 1. Это значение указывает на то, что записи являются дубликатами.

Из-за выражения скрипт не сортировать разделимые данные на основе каких-либо условий. Если в логике удаления дубликатов необходимо выбрать, какие записи удалять, а какие хранить в соответствии с порядком сортировки других столбцов, для этого можно использовать выражение ORDER BY.

Обновление (UPDATE)

Синтаксис:

> UPDATE <table> SET <field>='<value>’ WHERE <conditions>

* где table — имя таблицы; field — поле, для которого будем менять значение; value — новое значение; conditions — условие (без него делать update опасно — можно заменить все данные во всей таблице).

Обновление с использованием замены (REPLACE):

UPDATE <table> SET <field> = REPLACE(<field>, ‘<что меняем>’, ‘<на что>’);

Примеры:

UPDATE cities SET name = REPLACE(name, ‘Масква’, ‘Москва’);

UPDATE cities SET name = REPLACE(name, ‘Масква’, ‘Москва’) WHERE country = ‘Россия’;

UPDATE cities SET name = REPLACE(name, ‘Ма’, ‘Мо’) WHERE name = ‘Масква’;

Если мы хотим перестраховаться, результат замены можно сначала проверить с помощью SELECT:

SELECT REPLACE(name, ‘Ма’, ‘Мо’) FROM cities WHERE name = ‘Масква’;

Команда ALTER TABLE в MySQL

Команда ALTER TABLE используется для осуществления изменений таблицы:

Добавление колонок
Удаление колонок
Модификация колонок
Изменения имени таблицы
Изменения кодировки таблицы
Добавление и удаление ограничений

Для дальнейших примеров будем использовать таблицу books из базы данных Bookstore, которую создали в одном из предыдущих постов.

Чтобы просмотреть изменения колонок в таблице, воспользуйтесь командой:SHOW COLUMNS FROM table_name;

Перед началом работ выберем базу данных, с которой будем работать.

1	USEBookstore;

Как вставить значение из другой таблицы INSERT INTO … SELECT …

Допустим у нас есть еще одна таблица которая по структуре точно такая же как и первая. Нам в таблицу table2 нужно вставить все строки из table1.

Вставляем значения из table1 в таблицу table2:

INSERT INTO table2 (a, b, c) SELECT a, b, c FROM table1;

Вам следует позаботиться об уникальности ключей, если они есть в таблице, в которую мы вставляем. Например при дублировании PRIMARY KEY мы получим следующее сообщение об ошибке:

/* ERROR 1062 (23000): Duplicate entry '100' for key 'PRIMARY' */

Если вы делаете не какую-то единичную вставку при переносе данных, а где-то сохраните этот запрос, например в вашем PHP скрипте, то всегда перечисляйте столбцы.

Как не рекомендуется делать (без перечисления столбцов):

INSERT INTO table2 SELECT * FROM table1;

Если у вас со временем изменится количество столбцов в таблице, то запрос перестанет работать. При выполнении запроса MySQL в лучшем случае просто будет возвращать ошибку:

/* Ошибка SQL (1136): Column count doesn't match value count at row 1 */

Либо еще хуже: значения вставятся не в те столбцы.

Изменение поля

Зачем изменять уже существующее поле? На первый взгляд незачем, но в реальности оказывается, что это действительно нужно. Например, вы указали, что поле должно иметь размер 20 символов, но в определенный момент оказалось, что этого недостаточно и необходимо расширить размер поля. Для этого используется команда ALTER COLUMN. Но тут есть свои подводные камни. Попробуйте изменить размер имени или фамилии в созданной нами таблице Peoples:

ALTER TABLE Peoples 
ALTER COLUMN vcName NVARCHAR(40) NOT NULL

Я уверен, что вы увидите ошибку типа:

The object ‘DF__Peoples__vcName__72C60C4A’ is dependent on column ‘vcName’.
ALTER TABLE ALTER COLUMN vcName failed because one or more objects access this column

В первой строки говориться, что объект DF__Peoples__vcName__72C60C4A ссылается на колонку vcName. Во второй строке сообщения нам говорят, что команда не выполнена, потому что один или более объектов ссылаются на выбранное поле.

Выполним команду sp_help peoples, чтобы узнать, кто и зачем ссылается на выбранную таблицу:

Exec sp_help peoples

В результате мы увидим несколько таблиц с параметрами таблицы peoples. В самой последней можно увидеть указанное имя объекта в колонке constraint_name. Вот в чем проблема – на поле ссылается ограничение, которое проверяет наличие пустого значения, ведь мы указали для поля vcName свойство NOT NULL. Сначала необходимо удалить это ограничение, и только потом можно будет изменить размер поля:

ALTER TABLE Peoples 
DROP CONSTRAINT DF__Peoples__vcName__72C60C4A 

ALTER TABLE Peoples 
ALTER COLUMN vcName NVARCHAR(40) NOT NULL

На этом закончим рассматривать команду ALTER TABLE. У нее еще есть возможность включения и отключения триггеров, но мы эту тему пока опустим, потому что о триггерах мы еще абсолютно ничего не знаем.

Системные таблицы SQLite

В любой базе будут присутствовать несколько системных таблиц. Одна из них sqlite_master. Попробуйте выполнить запрос:

SELECT * FROM sqlite_master;
 
type   name   tbl_name  rootpage  sql
-------------------------------------
table  t1     t1        2         CREATE TABLE...
... и т.д.

Когда стоит задача узнать какие в базе есть таблицы, то достаточно сделать sql-запрос к sqlite_master.

Другая таблица — sqlite_sequence хранит последнее используемое значение автоинкрементов в таблицах (если они есть).

SELECT * FROM sqlite_sequence;
 
name    seq
-----------
pages   3
ststus  2
tb      5
tGr1    8

В редких случаях может понадобиться узнать следующий номер PRIMARY KEY. Проще всего это узнать в таблице sqlite_sequence.

Ещё одна системная таблица — sqlite_stat1 используется для сбора статистики по индексам и это может быть использовано для оптимизации запросов SQLite. Чтобы таблица появилась, нужно выполнить команду ANALYZE.

ANALYZE; -- для все базы
ANALYZE t1; -- только для определённой таблицы
SELECT * FROM sqlite_stat1; -- можно посмотреть данные

Результат работы ANALYZE используется планировщиком запросов SQLite, что позволяет ускорить их выполнение. Но есть нюанс — работать это будет, только если выполнить команду ANALYZE. Если её не сделать, то информация будет устаревшей или неточной. Я думаю, что ANALYZE будет полезна для профессиональных разработчиков баз данных, где большие объёмы критически сказываются на скорости выполнения запросов, да и сами запросы достаточно сложные.

Как изменить имя и тип данных у колонки в таблице MySQL?

Синтаксис смены имени и типа данных у колонки:

ALTER TABLE table_name CHANGE COLUMN old_column_name new_column_name new_data_type;

1	ALTERTABLEtable_nameCHANGECOLUMNold_column_namenew_column_namenew_data_type;

Сменим имя у колонки сreation_date на entry_date и тип данных c на .

ALTER TABLE books CHANGE COLUMN creation_date entry_date DATE;

1	ALTERTABLEbooksCHANGECOLUMNcreation_dateentry_dateDATE;

Если вы хотите сменить только тип данных, оставив имя колонки прежним, то воспользуйтесь следующим синтаксисом:

ALTER TABLE table_name MODIFY COLUMN column_name new_data_type;

1	ALTERTABLEtable_nameMODIFYCOLUMNcolumn_namenew_data_type;

Добавление новых полей

Для добавления нового поля или полей, необходимо после этого указать оператор ADD и через запятую перечислить добавляемые поля, как при создании таблицы данных. Следующий пример добавляет в таблицу поля для хранения фамилии, имени и отчества:

ALTER TABLE Peoples 
ADD 
 vcFamil VARCHAR(30) NOT NULL default '',
 vcName VARCHAR(30) NOT NULL default '',
 vcSurName VARCHAR(30) NULL

Фамилия и имя обязательно присутствуют у человека, а вот без отчества встречаются исключения. В основном это люди военных времен, когда было очень много беспризорных и сирот. Но не будем о грустном. Просто мы должны отразить в таблице то, что фамилия и имя не могут содержать нулевые значения, а поле для хранения отчества может содержать нулевые значения.

Если попытаться добавить поле, не позволяющее нулевые значения и не указать значение по умолчанию, то поле не будет добавлено. Вместо этого вы увидите сообщение об ошибке:

ALTER TABLE only allows columns to be added that can contain nulls or have a DEFAULT definition specified. Column ‘vcFamil’ cannot be added to table ‘Peoples’ because it does not allow nulls and does not specify a DEFAULT definition.

ALTER TABLE позволяет добавлять колонки только если они могут содержать нулевые значения, или имеют объявления значения по умолчанию (DEFAULT). Колонка «vcFamil» не может быть добавлена в таблицу Peoples, потому что не позволяет нулевых значений и не имеет значения по умолчанию.

Почему такое ограничение? Дело в том, что если добавить в таблицу, уже содержащую строки новое поле, то в существующих строках будет установлено в новой колонке нулевое значение или значение по умолчанию. Если поле не поддерживает нулевых значений и не указать значение по умолчанию, то, что вставлять в новое поле для существующих строк? Нулевое значение нельзя, а какое другое значение указать, сервер не знает. Вот из-за этого конфликта и произошло такое ограничение.

При добавлении фамилии и имени мы указываем значение по умолчанию – пустую строку (» – между кавычками ничего нет, даже пробела, т.е. пустая строка). Это лишний раз доказывает, что пустая строка и нулевое значение – это разные вещи и сервер воспринимает их по-разному. А для отчества у нас разрешены нулевые значения.

Конечно же, в данном случае теряется смысл от запрета нулевых значений для фамилии и имени, если есть значение по умолчанию. Если пользователь не укажет фамилию, то база данных воспримет это без ошибок и просто установит пустую строку, а это в принципе ошибка. Это серьезный недостаток, но его можно решить, если установить дополнительное ограничение на таблицу (CHECK), которое запретит пустые строки. Ограничение срабатывает только при добавлении строк, а на существующие не влияет, поэтому тут никаких конфликтов не будет. О изменении ограничений мы поговорим в разделе 1.4.3.

Копирование данных с помощью оператора SELECT

Чтобы не указывать данные построчно, вы можете скопировать несколько строк данных из одной таблицы и вставить их в другую с помощью запроса SELECT.

Синтаксис такой операции выглядит следующим образом:

INSERT INTO table_A (col_A1, col_A2, col_A3)
SELECT col_B1, col_B2, col_B3
FROM table_B;

Вместо ключевого слова VALUES, которое обычно идет за списком столбцов, в этом примере за ним следует оператор SELECT. Оператор SELECT в этом примере включает только оператор FROM, но он может обработать любой допустимый запрос.

Выполните следующую операцию CREATE TABLE, чтобы создать новую таблицу showroomEmployees

Обратите внимание, что столбцы этой таблицы имеют те же имена и типы данных, что и в таблице factoryEmployees, которую мы создали в предыдущем разделе:

CREATE TABLE showroomEmployees (

name varchar(30),

hourlyWage decimal(4,2),

startDate date

);

Вывод:

Query OK, 0 rows affected (0.02 sec)

Теперь вы можете загрузить в эту новую таблицу некоторые данные из таблицы factoryEmployees, которую создали ранее, включив запрос SELECT в INSERT INTO.

Если запрос SELECT возвращает то же количество столбцов в том же порядке, что и столбцы целевой таблицы, и они имеют совместимые (или совпадающие) типы данных, вы можете опустить список столбцов в операторе INSERT INTO:

INSERT INTO showroomEmployees

SELECT

factoryEmployees.name,

factoryEmployees.hourlyWage,

factoryEmployees.startDate

FROM factoryEmployees

WHERE name = 'Agnes';

Вывод выглядит так:

Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

Примечание: Каждому из столбцов, перечисленных в запросе SELECT этой операции, предшествует имя таблицы factoryEmployees и точка. Когда вы указываете имя таблицы при ссылке на такой столбец, это называется fully qualified column reference. В данном конкретном случае это делать не обязательно. На самом деле следующий оператор INSERT INTO даст тот же результат, что и предыдущий:

INSERT INTO showroomEmployees

SELECT

name,

hourlyWage,

startDate

FROM factoryEmployees

WHERE name = 'Agnes';

В примерах данного раздела для ясности используются полные (fully qualified) ссылки на столбцы, что на практике может стать хорошей и полезной привычкой. Подобные ссылки не только помогут упростить понимание и устранение неполадок в SQL, но и пригодятся в некоторых операциях, которые ссылаются на более чем одну таблицу (это, например, запросы, включающие предложения JOIN).

Оператор SELECT в этой операции включает опцию WHERE, благодаря которой запрос возвращает только строки из таблицы factoryEmployees, чей столбец name содержит значение Agnes. Поскольку в исходной таблице есть только одна такая строка, только именно она будет скопирована в таблицу showroomEmployees.

Выполните следующий проверочный запрос, чтобы извлечь все записи в таблице showroomEmployees:

SELECT * FROM showroomEmployees;

Вы получите такой результат:

+-------+------------+------------+
| name  | hourlyWage | startDate  |
+-------+------------+------------+
| Agnes |      26.50 | 2017-05-01 |
+-------+------------+------------+
1 row in set (0.00 sec)

Вы можете вставить несколько строк данных через любой запрос, который вернет более одной строки из исходной таблицы. Например, следующий запрос вернет каждую запись из БД factoryEmployees, в которой значение в столбце name не начинается с буквы J:

INSERT INTO showroomEmployees

SELECT

factoryEmployees.name,

factoryEmployees.hourlyWage,

factoryEmployees.startDate

FROM factoryEmployees

WHERE name NOT LIKE 'J%';

Вы получите:

Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

Запустите этот запрос еще раз, чтобы получить все записи в таблице showroomEmployees:

SELECT * FROM showroomEmployees;

Результат выглядит так:

+--------+------------+------------+
| name   | hourlyWage | startDate  |
+--------+------------+------------+
| Agnes  |      26.50 | 2017-05-01 |
| Agnes  |      26.50 | 2017-05-01 |
| Harry  |      26.50 | NULL       |
| Marie  |      27.88 | 2018-03-29 |
| Giles  |      26.50 | 2019-08-06 |
| Daphne |      32.45 | 2017-11-12 |
+--------+------------+------------+
6 rows in set (0.00 sec)

Обратите внимание на две одинаковые строки со значением Agnes. Каждый раз, когда вы запускаете INSERT INTO вместе с SELECT, SQL обрабатывает результаты запроса как новый набор данных

Если вы не наложите определенные ограничения на свою таблицу или не разработаете более детализированные запросы, при добавлении данных в таблицу они частично будут повторяться.

Как удалить столбец

Базовый синтаксис для удаления столбца из существующей таблицы выглядит следующим образом:

ALTER TABLE имя_таблицы DROP COLUMN имя_столбца;

Следующий оператор удалит наш недавно добавленный столбец из таблицы .

mysql> ALTER TABLE shippers DROP COLUMN fax;

После выполнения оператора, структура таблицы будет выглядеть так:

+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| shipper_id   | int         | NO   | PRI | NULL    | auto_increment |
| shipper_name | varchar(60) | NO   |     | NULL    |                |
| phone        | varchar(20) | NO   | UNI | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+