Alter table. изменение таблиц в базе данных mysql

PostgreSQL. Добавляем not null constraints в большие таблицы

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

Одной из команд, с которой надо быть осторожным на таблицах с большим количеством записей, является добавление not null constraint на столбец. При добавлении данного constraint PostgreSQL приобретает access exclusive lock на таблицу, в результате чего другие сессии не могут временно даже читать таблицу; затем БД проверяет, что в столбце действительно ни одного null нет, и только после этого вносятся изменения. Под катом я рассмотрю различные варианты, как можно добавить not null constraint , лоча таблицу на минимально возможное время или даже не лоча ее совсем.

Вариант 2: замена столбца identity при использовании операции SWITCH

секционированием таблицдокументации Microsoft

• Обе таблицы должны существовать к моменту применения операции SWITCH. Таблица, из которой будет перемещаться секция (исходная таблица) и таблица, в которую будет перещаться секция (целевая таблица) должны существовать в базе данных до выполнения операции переключения.
• Принимающая секция должна существовать и быть пустой. Добавляете ли вы таблицу как секцию в уже существующую секционированную таблицу, или перемещаете секцию из одной секционированной таблицы в другую, секция, которая принимает новую секцию, должна существовать и она должна быть пустой.
• Принимающая несекционированная таблица должна существовать и быть пустой. Если вы переназначаете секцию для формирования одной несекционированной таблицы, таблица, которая получает новую секцию, должна существовать и быть пустой несекционированной таблицей.
• Секции должны быть определены на одном и том же столбце. Если вы переключаете секцию из одной секционированной таблицы в другую, обе таблицы должны быть секционированы по одному и тому же столбцу.
• Источник и целевая таблицы должны находиться в одной и той же файловой группе. Источник и целевая таблица в операторе ALTER TABLE…SWITCH должны находиться в одной и той же файловой группе, и их столбцы с большими данными должны храниться в одной и той же файловой группе. Любые соответствующие индексы, секции индексов или секции индексированных представлений должны находиться в одной и той же файловой группе. Однако эта файловая группа может отличаться от группы, содержащей соответствующие таблицы или другие соответствующие индексы.

Очистка и воссоздание артефактов из Варианта 1

Листинг 15: чистка и воссоздание таблиц примера

USE tempdb;
GO
DROP TABLE Sample2;
DROP TABLE Sample;
DROP SEQUENCE Sample_SequenceNumber;
GO
CREATE TABLE Sample (ID int identity(1,1) NOT NULL, 
      SampleName varchar(30) NOT NULL,
      CONSTRAINT PK_Sample_ID PRIMARY KEY CLUSTERED(ID ASC));
INSERT INTO Sample(SampleName) 
   VALUES ('First'),
          ('Second'),
          ('Third');
CREATE TABLE Sample2 (ID int,
CONSTRAINT FK_Sample2_ID FOREIGN KEY (ID) REFERENCES Sample(ID));
GO
USE tempdb;
GO
DECLARE @NewStartValue int;
DECLARE @IncrementValue int = 1;
DECLARE @CMD nvarchar(1000);
SELECT @NewStartValue = MAX(ID) + @IncrementValue FROM Sample;
SET @CMD = 'CREATE SEQUENCE Sample_SequenceNumber AS INT START WITH ' +
           RTRIM(CAST(@NewStartValue as CHAR)) + 
           ' INCREMENT BY ' + RTRIM(CAST(@IncrementValue AS CHAR));
EXEC sp_executesql @CMD
GO

Шаг 1: создание новой таблицы, которая использует порядковый номер

Листинг 16: создание новой таблицы

USE tempdb;
GO
CREATE TABLE Sample_New (
   ID int NOT NULL DEFAULT NEXT VALUE FOR Sample_SequenceNumber, 
   SampleName varchar(30) NOT NULL,
   CONSTRAINT PK_Sample_New_ID PRIMARY KEY CLUSTERED(ID ASC)
 );
 GO 

Переключение таблиц

Листинг 17: переключение таблиц

USE tempdb;
GO
ALTER TABLE Sample SWITCH TO Sample_New;
SELECT * FROM Sample_New;

Строки в таблице Sample_New

Удаление старой таблицы и переименование новой

Листинг 18: удаление старой и переименование новой

USE tempdb;
GO
DROP TABLE Sample
EXEC sp_rename N'Sample_New',N'Sample';

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

На основании таблицы departments ниже, переименовать таблицу departments в depts.

Oracle PL/SQL

CREATE TABLE departments
( department_id number(10) not null,
department_name varchar2(50) not null,
CONSTRAINT departments_pk PRIMARY KEY (department_id)
);

Решение для практического упражнения №1:

Следующий оператор Oracle/PLSQL ALTER TABLE переименует таблицу departments в depts:

Oracle PL/SQL

ALTER TABLE departments
RENAME TO depts;

SQL Пример ALTER TABLE

Посмотрите на таблицу «Persons»:

Теперь мы хотим добавить столбец с именем «DateOfBirth» в таблицу «Persons».

Мы используем следующую инструкцию SQL:

ALTER TABLE Persons
ADD DateOfBirth date;

Обратите внимание, что новый столбец «DateOfBirth» имеет тип date и будет содержать дату.
Тип данных указывает, какой тип данных может содержать столбец.
Для получения полного описания всех типов данных, доступных в MS Access, MySQL и SQL Server,
перейдите к нашему полному Справочнику типов данных. Таблица «Persons» теперь будет выглядеть так:. Таблица «Persons» теперь будет выглядеть так:

Таблица «Persons» теперь будет выглядеть так:

Аргументы

‘object_name’

Текущее полное или неквалифицированное имя объекта пользователя или типа данных. Если объект, который требуется переименовать, является столбцом в таблице, object_name должен находиться в формате table.column или schema.table.column. Если объект, который необходимо переименовать, является индексом, object_name должен находиться в форме table.index или schema.table.index. Если объект, который требуется переименовать, является ограничением, object_name должен быть в форме schema.constraint.

Кавычки необходимы, только если указан объект с полным именем. Если предоставлено полное имя таблицы, включая имя базы данных, в качестве последнего должно использоваться имя текущей базы данных. object_name имеет значение nvarchar(776) без значения по умолчанию.

‘new_name’

Новое имя указанного объекта. new_name должно быть однокомпонентным именем и соответствовать правилам для идентификаторов. newname имеет значение sysname без значения по умолчанию.

Примечание

Имена триггеров не могут начинаться с символов # или ##.

‘object_type’

Тип переименовываемого объекта. object_type имеет значение varchar(13) со значением по умолчанию NULL и может быть одним из этих значений.

‘COLUMN’

Область применения: Azure Synapse Analytics

В (предварительная версия) для Azure Synapse Analytics column является обязательным параметром, указывающим, что тип объекта, который необходимо переименовать, является столбцом. Это varchar(13) без значения по умолчанию и всегда должен быть включен в инструкцию (предварительная версия). Столбец можно переименовать, только если он не является столбцом распределения.

Переименование столбца таблицы в Microsoft SQL Server

Переименовать столбец таблицы в Microsoft SQL Server можно как в графическом конструкторе SSMS, так и на языке T-SQL. Способ с использованием конструктора, я думаю, понятен, т.е. нужно просто открыть конструктор, указать новое имя столбца и сохранить изменения. Если Вы не знаете, как работать с конструктором, то прочитайте вышеупомянутую статью про изменение таблиц и принцип станет понятен. А вот способ с использованием языка T-SQL не так очевиден, ведь столбцы в таблицах переименовываются с помощью системной хранимой процедуры sp_rename.

Системная хранимая процедура sp_rename

sp_rename — системная хранимая процедура, которая позволяет изменять имя пользовательского объекта базы данных. Таким объектом может выступать таблица, индекс, столбец таблицы или псевдоним типа данных.

• @objname – Текущее имя объекта. В случае со столбцом это имя указывается в формате «таблица.столбец»;
• @newname – Новое имя объекта;
• @objtype – Тип объекта. В случае переименования таблиц параметр указывать не требуется. Может принимать следующие значения:
  • COLUMN – переименование столбцов;
  • INDEX – переименование индексов;
  • USERDATATYPE — переименование пользовательских типов данных;
  • DATABASE — переименование базы данных.

  Примечание!

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

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

  Кроме того, для переименования объектов, конечно же, требуются соответствующие разрешения на изменения.

  Пример переименования столбца в таблице с помощью процедуры sp_rename

  Для примера давайте переименуем столбец Price таблицы Goods на ProductPrice. Таблица расположена в базе данных TestDB. В качестве SQL сервера у меня выступает — Microsoft SQL Server 2017 Express.

  

  

  

  

  

  

  

  

  

  

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

  Рассмотрим простую возможность переименования таблиц в MySQL.

  Для переименования таблиц, в MySQL существует команда RENAME TABLE , синтаксис которой имеет вид:

  RENAME TABLE старое_имя_таблицы TO новое_имя_таблицы;

  Таким образом, к примеру, для переименования таблицы с прежним именем accounts в таблицу с именем users, выполним следующую команду:

  Чтобы переименовать таблицу в SQL Server, используйте sp_rename команду:

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

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

  При использовании sp_rename, который работает как в приведенных выше ответах, проверьте также, какие объекты затрагиваются после переименования, которые ссылаются на эту таблицу, потому что их тоже нужно изменить

  Я взял пример кода для табличных зависимостей в блоге Пинала Дейва здесь

  Таким образом, все эти зависимые объекты должны быть обновлены также

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

  Я подключаюсь к базе данных MS Access из внешнего приложения .Net. Мне нужно использовать SQL для обновления схемы базы данных. Часть обновления требует переименования таблицы, которая является частью отношения «один ко многим».

  Я понимаю, что фактически невозможно переименовать таблицу в MS Access с помощью SQL. Мои исследования привели меня к следующему решению.

  Кажется, что это правильно работает для таблиц, которые не являются частью отношений. Однако, если таблица относится к отношению, тогда я получаю следующее исключение при запуске DROP TABLE SQL.

  Есть ли способ через SQL, чтобы я мог обновить отношения в MS Access, чтобы указать на новую таблицу, которая была создана, чтобы можно было удалить старую таблицу?

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

Синтаксис

Синтаксис 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;

Для MySQL и MariaDB.

PgSQL

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

Для SQL Server.

PgSQL

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

Для PostgreSQL.

PgSQL

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

Description

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

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

При добавлении индекса к столбцу (или набору столбцов), имеющему повторяющиеся значения, будет выдана ошибка, и выполнение оператора будет остановлено. Чтобы подавить ошибку и принудительно создать индексы , отбрасывая дубликаты, можно указать параметр IGNORE . Это может быть полезно, если столбец (или набор столбцов) должен быть УНИКАЛЬНЫМ, но содержит повторяющиеся значения; однако этот метод не позволяет контролировать, какие строки сохраняются, а какие удаляются

Также обратите внимание, что допускается, но игнорируется в операторах

Этот оператор также можно использовать для переименования таблицы. Подробнее см. ТАБЛИЦА ПЕРЕИМЕНОВАНИЙ .

Когда создается индекс, механизм хранения может использовать в процессе настраиваемый буфер. Увеличение буфера ускоряет создание индекса. Aria и MyISAM выделяют буфер, размер которого определяется параметрами или , которые также используются для REPAIR TABLE . InnoDB выделяет три буфера, размер которых определяется .

Агрегатные функции в SQL

Для получения итоговых значений и вычисления выражений используются агрегатные функции в sql:

Все агрегатные функции возвращают единственное значение.

Функции , и применимы к любым типам данных.

Важно: при работе с агрегатными функциями в SQL используется служебное слово AS

Пример БД «Институт»: Получить значение самой большой зарплаты среди учителей, вывести итог как «макс_зп»

Решение: 

SELECT MAX(zarplata) AS макс_зп
  FROM teachers;

Результаты:

Рассмотрим более сложный пример использования агрегатных функций в sql.

Пример: БД . Найти имеющееся в наличии количество компьютеров, выпущенных производителем Америка

Решение: 

1
2
3
4
5
6
7
8

SELECT COUNT( * ) 
FROM  `pc` 
WHERE  `Номер` 
IN (
  SELECT  `Номер` 
  FROM product
  WHERE Производитель =  "Америка"
)

SQL As 2_3. . Вывести общее количество продуктов, странами-производителями которых является Россия

SQL As 2_4. . Вывести среднюю цену на ноутбуки

Agr func 2_2. . Вывести минимальную и максимальную зарплату учителей

Agr func 2_3. . Выберите название курса, уроки по которому не проводились и не запланированы проводиться. Дополните код:

1
2
3
4
5
6

SELECT  `title` 
FROM  `courses` 
WHERE  `title` NOT 
IN (
   ...
)

Agr func 2_4. . Измените предыдущее задание: Посчитайте количество тех курсов, уроки по которым не проводились и не запланированы проводиться. Выводите результат с именем «нет_уроков»

Задание 2_3. . Вывести год рождения самого младшего студента, назвать поле «Младший»

Задание 2_4. . Посчитать количество всех студентов группы 101. Назвать поле «Группа101»

Предложение GROUP BY в SQL

Оператор в sql обычно используется совместно с агрегатными функциями.

Агрегатные функции выполняются над всеми результирующими строками запроса. Если запрос содержит оператор , каждый набор строк, заданных в предложении GROUP BY, составляет группу, и агрегатные функции выполняются для каждой группы отдельно.

Рассмотрим пример с таблицей :

Пример:

1. Выдавать количество проведенных уроков учителем Иванов из таблицы (порядковый номер Иванова ()).

SELECT COUNT(tid) AS Иванов 
FROM lessons
WHERE tid=1

Результат:

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

SELECT course, COUNT( tid ) AS Иванов
FROM lessons
WHERE tid =1
GROUP BY course

Результат:

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

При этом стоит учесть, что при группировке по полю, содержащему -значения, все такие записи попадут в одну группу.

SQL group by 2_5. . Для различных типов принтеров определить их среднюю стоимость и количество (т.е. отдельно по лазерным, струйным и матричным). Использовать агрегатные функции . Результат должен выглядеть так:

SQL group by 2_5. . Посчитать количество уроков, проведенных одним и тем же учителем. Результат должен выглядеть так:

Задание 2_5. . Вывести количество человек в каждой группе и количество человек на каждом курсе из таблицы . Назвать вычисляемые поля «кол_во_в_гр» и «кол_во_на_курс»

Оператор Having SQL

Предложение в SQL необходимо для проверки значений, которые получены с помощью агрегатной функции после группировки (после использования ). Такая проверка не может содержаться в предложении WHERE.

Пример: БД Компьютерный магазин. Посчитать среднюю цену компьютеров с одинаковой скоростью процессора. Выполнить подсчет только для тех групп, средняя цена которых меньше 30000.

Решение: 

SELECT AVG(`Цена`) ,`Скорость`
FROM  `pc` 
GROUP BY `Скорость` 
HAVING AVG(`Цена`) <30000

Результат:

Важно: В операторе нельзя использовать псевдоним (например, сред_цена), используемый для именования значений агрегатной функции.

Having SQL 2_6

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

Having SQL 2_6.
Посчитать количество уроков, проведенных одним и тем же учителем. Выдавать значение только для тех учителей, у которых уроков больше двух.

Задание 2_6. . Получить количество учеников каждой группы при условии, что курс не меньше 3-го

Далее

Добавление/изменение/удаление PRIMARY KEY и INDEX.

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

Если включена системная переменная , попытка удалить первичный ключ приведет к ошибке.

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

Инструкция удаляет индекс. Это расширение MySQL для стандартного SQL. Чтобы посмотреть имена индексов таблицы, необходимо использовать команду .

Некоторые механизмы хранения позволяют указать тип индекса при их создании. Синтаксис .

Инструкция переименовывает индекс. Это расширение MySQL для стандартного SQL. Содержимое таблицы остается неизменным. Параметр должен быть именем существующего индекса в таблице, который не удаляется тем же оператором . Параметр это новое имя индекса, которое не может дублировать имя индекса в результирующей таблице после применения изменений. Ни одно из имен индексов не может иметь имя .

Если вы используете ALTER TABLE для таблицы MyISAM, все неуникальные индексы создаются в отдельном пакете (как для REPAIR TABLE). Это должно сделать ALTER TABLE намного быстрее, когда у вас много индексов.

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

Как изменить имя и тип данных у колонки в таблице 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 CHANGE COLUMN old_column_name new_column_name new_data_type;

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

<текстареа 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 books CHANGE COLUMN creation_date entry_date DATE;

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

<текстареа 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 MODIFY COLUMN column_name new_data_type;

Ограничения запроса ALTER​

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

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

Если возможностей запроса не хватает для нужного изменения таблицы, вы можете создать новую таблицу, скопировать туда данные с помощью запроса , затем поменять таблицы местами с помощью запроса , и удалить старую таблицу. В качестве альтернативы для запроса , можно использовать инструмент clickhouse-copier.

Запрос блокирует все чтения и записи для таблицы. То есть если на момент запроса выполнялся долгий , то запрос сначала дождётся его выполнения. И в это время все новые запросы к той же таблице будут ждать, пока завершится этот .

Для таблиц, которые не хранят данные самостоятельно (типа Merge и Distributed), всего лишь меняет структуру таблицы, но не меняет структуру подчинённых таблиц. Для примера, при ALTER-е таблицы типа , вам также потребуется выполнить запрос для таблиц на всех удалённых серверах.

Отличия изменения данных и изменения структуры

Такие запросы как выбор (SELECT), изменение данных (UPDATE), добавление данных (INSERT), удаление (DELETE, TRUNCATE) требуется выполнять очень часто. И почти всегда их приходится генерировать программно из кода приложения

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

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

Все изменения структуры производятся с уже созданной и сохраненной таблицей. К операциям измененяющим структуру относятся следующие операции:

• Добавление или удаление поля;
• Добавление или удаление индекса;
• Добавление или удаление первичного ключа;
• Добавление внешнего ключа с ограничениями;
• Изменение имени поля;
• Изменение порядка следования полей;
• Изменение определения поля (тип, размер, значение по умолчанию, допустимость значения NULL);
• Изменение имени таблицы.

Операцию изменения структуры таблицы нет необходимости совершать из кода программы, так как она разовая. Структуру таблиц, когда это требуется, меняет администратор баз данных. Это можно сделать либо при помощи менеджера (HeidiSQL, PhpMyadmin и т.д.), либо из командной строки при помощи команды ALTER TABLE.