Oracle добавить столбец в таблицу
Summary: in this tutorial, you will learn how to use the Oracle ALTER TABLE ADD column statement to add one or more columns to a table.
To add a new column to a table, you use the ALTER TABLE statement as follows:
In this statement:
- First, you specify the name of the table, which you want to add the new column, after the ALTER TABLE clause.
- Second, you specify the column name, data type, and its constraint.
Note that you cannot add a column that already exists in the table; trying to do so will cause an error. In addition, the ALTER TABLE ADD column statement adds the new column at the end of the table. Oracle provides no direct way to allow you to specify the position of the new column like other database systems such as MySQL.
In case you want to add more than one column, you use the following syntax:
In this syntax, you separate two columns by a comma.
Добавление колонки в таблицу
Добавим в таблицу сотрудников колонку для хранения дня рождения:
По умолчанию все строки таблицы будут иметь null в новой колонке. Но если при ее добавлении указать значение по-умолчанию, то все строки будут содержать его в новой колонке.
Добавим колонку notify_by_email , которая будет по-умолчанию содержать в себе “1”, если сотруднику нужно отправлять уведомления по почте, и “0”, если нет:
Посмотрим, как сейчас выглядят данные в таблице:
Как видно, каждая строка содержит “0” в колонке notify_by_email .
Нельзя добавить колонку NOT NULL в таблицу с данными без значения по-умолчанию.
В результате получим ошибку ORA-01758: table must be empty to add mandatory (NOT NULL) column .
Но если указать значение по-умолчанию, ошибки не будет:
Колонка добавляется без ошибок:
Syntax
To rename a table, the SQL ALTER TABLE syntax is:
For Oracle, MySQL, MariaDB, PostgreSQL and SQLite:
For SQL Server (using the stored procedure called sp_rename):
Rename table
Example
Let's look at an example that shows how to modify a column in an Oracle table using the ALTER TABLE statement.
This Oracle ALTER TABLE example will modify the column called customer_name to be a data type of varchar2(100) and force the column to not allow null values.
In a more complicated example, you could use the ALTER TABLE statement to add a default value as well as modify the column definition:
In this example, the ALTER TABLE statement would modify the column called city to be a data type of varchar2(75), the default value would be set to 'Seattle' and the column would be set to not allow null values.
Add column in table
Syntax
To ADD A COLUMN in a table, the Oracle ALTER TABLE syntax is:
Синтаксис
Синтаксис добавления нескольких столбцов в таблицу MySQL (с использованием оператора ALTER TABLE):
ALTER TABLE table_name
ADD new_column_name column_definition
[ FIRST | AFTER column_name ],
ADD new_column_name column_definition
[ FIRST | AFTER column_name ],
.
;
table_name - имя таблицы для изменения.
new_column_name - имя нового столбца для добавления в таблицу.
column_definition - тип данных и определение столбца (NULL или NOT NULL и т. д.).
FIRST | AFTER column_name - необязательный. Он сообщает MySQL, где в таблице создается столбец. Если этот параметр не указан, новый столбец будет добавлен в конец таблицы.
Example
Let's look at an example that shows how to rename a column in an Oracle table using the ALTER TABLE statement.
This Oracle ALTER TABLE example will rename the column called customer_name to cname.
Example
Let's look at an example that uses the ALTER TABLE statement to modify more than one column. In this example, we will modify two columns called supplier_name and city.
For MySQL and MariaDB:
Rename table
Description
The Oracle ALTER TABLE statement is used to add, modify, or drop/delete columns in a table. The Oracle ALTER TABLE statement is also used to rename a table.
Syntax
To modify a column in an existing table, the SQL ALTER TABLE syntax is:
For Oracle, MySQL, MariaDB:
Example
Let's look at an example that drops (ie: deletes) a column from a table.
This SQL ALTER TABLE example will drop the column called supplier_name from the table called supplier.
Syntax
Starting in Oracle 9i Release 2, you can now rename a column.
To RENAME A COLUMN in an existing table, the Oracle ALTER TABLE syntax is:
Modify column in table
Переименование таблицы
Следующий запрос переименует таблицу employees в emps :
Стоит отметить, что переименование таблицы не приведет к ошибке при наличии ссылок на нее. В нашем примере таблица успешно переименуется, несмотря на дочернюю таблицу emp_bonuses . Внешний ключ при этом никуда не девается, в таблицу emp_bonuses по-прежнему нельзя добавить значения, нарушающие условия внешнего ключа.
Example
Let's look at an example that renames a column in the supplier table from supplier_name to sname.
For Oracle (9i Rel2 and up) and PostgreSQL:
For SQL Server (using the stored procedure called sp_rename):
For MySQL and MariaDB:
In MySQL and MariaDB, you must specify the data type of the column when you rename it.
Rename column in table
(NEW in Oracle 9i Release 2)
Пример
Рассмотрим пример, который показывает, как добавить столбец в таблицу MySQL, используя оператор ALTER TABLE.
Например:
Этот MySQL пример ALTER TABLE добавит столбец с именем last_name в таблицу contacts . Он будет создан как столбец NOT NULL и появится в таблице после поля contact_id .
Удаление нескольких колонок в таблице
Удалим колонки emp_firstname и is_out из таблицы:
Удалять все колонки из таблицы нельзя, получим ошибку ORA-12983: cannot drop all columns in a table .
Syntax
To add multiple columns to an existing table, the SQL ALTER TABLE syntax is:
Изменение атрибута NOT NULL в колонке
Сделаем так, чтобы в колонку dept_id можно было сохранять null :
А теперь снова сделаем ее NOT NULL :
Нельзя изменить колонку на NOT NULL, если в ней уже содержатся NULL-значения.
Example
Let's look at an example that shows how to rename a table in Oracle using the ALTER TABLE statement.
This Oracle ALTER TABLE example will rename the customers table to contacts.
Based on the departments table below, rename the departments table to depts.
The following Oracle ALTER TABLE statement would rename the departments table to depts:
Based on the employees table below, add a column called bonus that is a number(6) datatype.
The following Oracle ALTER TABLE statement would add a bonus column to the employees table:
Based on the customers table below, add two columns - one column called contact_name that is a varchar2(50) datatype and one column called last_contacted that is a date datatype.
The following Oracle ALTER TABLE statement would add the contact_name and last_contacted columns to the customers table:
Based on the employees table below, change the employee_name column to a varchar2(75) datatype.
The following Oracle ALTER TABLE statement would change the datatype for the employee_name column to varchar2(75):
Based on the customers table below, change the customer_name column to NOT allow null values and change the state column to a varchar2(2) datatype.
The following Oracle ALTER TABLE statement would modify the customer_name and state columns accordingly in the customers table:
Based on the employees table below, drop the salary column.
The following Oracle ALTER TABLE statement would drop the salary column from the employees table:
Based on the departments table below, rename the department_name column to dept_name.
The following Oracle ALTER TABLE statement would rename the department_name column to dept_name in the departments table:
Уже созданные таблицы можно изменять. Для этого используется команда SQL ALTER . Данная команда относится к группе DDL.
Syntax
To add a column in a table, the ALTER TABLE syntax in SQL is:
Синтаксис
Синтаксис добавления столбца в таблицу MySQL (с использованием оператора ALTER TABLE):
table_name - имя таблицы для изменения.
new_column_name - имя нового столбца для добавления в таблицу.
column_definition - тип данных и определение столбца (NULL или NOT NULL и т. д.).
FIRST | AFTER column_name - необязательный. Он сообщает MySQL, где в таблице создается столбец. Если этот параметр не указан, то новый столбец будет добавлен в конец таблицы.
Описание
MySQL оператор ALTER TABLE используется для добавления, изменения или удаления столбцов в таблице. Оператор MySQL ALTER TABLE также используется для переименования таблицы.
Modify Multiple columns in table
Description
The SQL ALTER TABLE statement is used to add, modify, or drop/delete columns in a table. The SQL ALTER TABLE statement is also used to rename a table.
Удаление колонки из таблицы
Удалим только что добавленную колонку emp_lastname из таблицы:
Следует учитывать, что если на удаляемую колонку ссылаются строки из другой таблицы(посредством внешнего ключа), то удалить колонку не получится.
Убедимся в этом, создав таблицу emp_bonuses , которая будет ссылаться на колонку id в таблице employees :
Теперь попробуем удалить колонку id :
В результате мы получим ошибку ORA-12992: cannot drop parent key column , которая говорит о том, что удаляемая колонка является родительской для другой таблицы.
Example
Let's look at an example that renames a table called supplier to the new name vendor.
For Oracle, MySQL, MariaDB, PostgreSQL and SQLite:
For SQL Server (using the stored procedure called sp_rename):
Based on the departments table below, rename the departments table to depts.
The following SQL ALTER TABLE statement would rename the departments table to depts:
Based on the employees table below, add a column called salary that is an int datatype.
The following SQL ALTER TABLE statement would add a salary column to the employees table:
Based on the customers table below, add two columns - one column called contact_name that is a char(50) datatype and one column called last_contacted that is a date datatype.
The following SQL ALTER TABLE statement would add the contact_name and last_contacted columns to the customers table:
Based on the employees table below, change the employee_name column to a char(75) datatype.
The following SQL ALTER TABLE statement would change the datatype for the employee_name column to char(75):
Based on the customers table below, change the customer_name column to NOT allow null values and change the state column to a char(2) datatype.
The following SQL ALTER TABLE statement would modify the customer_name and state columns accordingly in the customers table:
Based on the employees table below, drop the salary column.
The following SQL ALTER TABLE statement would drop the salary column from the employees table:
Based on the departments table below, rename the department_name column to dept_name.
The following SQL ALTER TABLE statement would rename the department_name column to dept_name in the departments table:
This Oracle tutorial explains how to use the Oracle ALTER TABLE statement to add a column, modify a column, drop a column, rename a column or rename a table (with syntax, examples and practice exercises).
Пример
Рассмотрим пример, который показывает, как добавить несколько столбцов в таблицу MySQL, используя оператор ALTER TABLE.
Например:
This SQL tutorial explains how to use the SQL ALTER TABLE statement to add a column, modify a column, drop a column, rename a column or rename a table (with lots of clear, concise examples). We've also added some practice exercises that you can try for yourself.
Modify multiple columns in table
Add column in table
Example
Let's look at an example that shows how to add a column in an Oracle table using the ALTER TABLE statement.
This Oracle ALTER TABLE example will add a column called customer_name to the customers table that is a data type of varchar2(45).
In a more complicated example, you could use the ALTER TABLE statement to add a new column that also has a default value:
In this example, the column called city has been added to the customers table with a data type of varchar2(40) and a default value of 'Seattle'.
Переименование колонки
Переименуем колонку birthday в bd :
Example
Let's look at a SQL ALTER TABLE example that adds a column.
This SQL ALTER TABLE example will add a column called supplier_name to the supplier table.
Example
Let's look at an example of how to modify a column called supplier_name using the ALTER TABLE statement. Note that most databases have a slightly different syntax.
For MySQL and MariaDB:
Логическое удаление колонок
Удаление колонок в очень больших таблицах может занять достаточно большое количество времени. В таких случаях можно для начала пометить нужные колонки как неиспользуемые:
После выполнения данной команды Oracle удалит эти колонки логически, попросту пометив их как неиспользуемые. При запросе из таблицы они не будут видны, и в таблицу можно даже добавлять колонки с такими же названиями.
Чтобы удалить неиспользуемые колонки физически, используется следующий запрос:
Конечно, выполнять его желательно во время наименьшей нагрузки на сервер.
Rename column in table
Добавить столбец в таблицу
Syntax
To MODIFY MULTIPLE COLUMNS in an existing table, the Oracle ALTER TABLE syntax is:
Example
Let's look at SQL ALTER TABLE example that adds more than one column.
This SQL ALTER TABLE example will add two columns, supplier_name as a char(50) field and city as a char(45) field to the supplier table.
Syntax
To rename a column in an existing table, the SQL ALTER TABLE syntax is:
For Oracle and PostgreSQL:
For SQL Server (using the stored procedure called sp_rename):
For MySQL and MariaDB:
Oracle ALTER TABLE ADD column examples
Let’s create a table named members for the demonstration.
The following statement adds a new column named birth_date to the members table:
In this example, the birth_date column is a DATE column and it does not accept null.
Suppose, you want to record the time at which a row is created and updated. To do so, you need to add two columns created_at and updated_at as follows:
The data types of the created_at and updated_at columns are TIMESTAMP WITH TIME ZONE . These columns also do not accept null.
To check whether a column exists in a table, you query the data from the user_tab_cols view. For example, the following statement checks whether the members table has the first_name column.
This query comes in handy when you want to check whether a column exists in a table before adding it.
For example, the following PL/SQL block checks whether the members table has effective_date column before adding it.
If you execute the block at the first time, the effective_date column is appended at the end of the members table. However, once you execute it from the second time, you will see the following message:
which is what we expected.
In this tutorial, you have learned how to use the Oracle ALTER TABLE ADD column statement to add one or more column to an existing table.
В этом учебном пособии вы узнаете, как использовать MySQL оператор ALTER TABLE для добавления столбца, изменения столбца, удаления столбца, переименования столбца или переименования таблицы (с синтаксисом и примерами).
Syntax
To DROP A COLUMN in an existing table, the Oracle ALTER TABLE syntax is:
Modify column in table
Syntax
To MODIFY A COLUMN in an existing table, the Oracle ALTER TABLE syntax is:
Syntax
To RENAME A TABLE, the Oracle ALTER TABLE syntax is:
Example
Let's look at an example that shows how to drop a column in an Oracle table using the ALTER TABLE statement.
This Oracle ALTER TABLE example will drop the column called customer_name from the table called customers.
Add multiple columns in table
Подготовка данных
Тестировать будем на таблице employees . Изначально она будет состоять только из одной колонки id :
Drop column in table
Добавление нескольких колонок в таблицу
Чтобы добавить несколько колонок в таблицу, нужно просто перечислить их через запятую:
Add multiple columns in table
Syntax
To drop a column in an existing table, the SQL ALTER TABLE syntax is:
Изменение типа данных колонки
Изменим тип колонки dept_id с числового на строковый:
Здесь нужно обратить внимание на то, что при изменении типа мы не добавляли NOT NULL . В MODIFY мы должны указать действия, которые действительно что-то изменят. Колонка dept_id и так была not null , и при изменении типа это свойство не нужно указывать.
Если попробовать добавить not null , получим ошибку ORA-01442: column to be modified to NOT NULL is already NOT NULL :
Следует учитывать одну важную деталь при изменении типа данных - изменяемая колонка должна быть пуста.
Рассмотрим более подробно процесс изменения типа колонки, если в ней уже содержатся данные.
Спустя какое-то время мы решили, что не хотим использовать числовое поле для boolean значений. Вместо этого было решено использовать более понятный строковый тип.
Итак, для начала добавим колонку с нужным нам типом данных. Так как мы не можем назвать ее notify_by_email (такая уже есть на данный момент), то назовем ее notify_by_email_new :
После этого нужно заполнить эту колонку данными. Алгоритм прост - значение “1” в колонке notify_by_email должно быть перенесено как значение “Y” в колонку notify_by_email_new , а значение “0” нужно перенести в виде “N”. Так как при добавлении колонки мы указали значение по-умолчанию, то в таблице каждая строка содержит значение “N” в этой колонке. Все, что осталось - это изменить значение на “Y”, где notify_by_email равен 1:
Затем удаляем колонку notify_by_email :
Теперь можно переименовать notify_by_email_new в notify_by_email :
Смотрим на результат:
Syntax
To ADD MULTIPLE COLUMNS to an existing table, the Oracle ALTER TABLE syntax is:
Example
Let's look at an example that shows how to modify multiple columns in an Oracle table using the ALTER TABLE statement.
This Oracle ALTER TABLE example will modify both the customer_name and city columns. The customer_name column will be set to a varchar2(100) data type and not allow null values. The city column will be set to a varchar2(75) data type, its default value will be set to 'Seattle', and the column will not allow null values.
Добавить несколько столбцов в таблицу
Syntax
To modify multiple columns in an existing table, the SQL ALTER TABLE syntax is:
For MySQL and MariaDB:
Example
Let's look at an example that shows how to add multiple columns in an Oracle table using the ALTER TABLE statement.
This Oracle ALTER TABLE example will add two columns, customer_name as a varchar2(45) field and city as a varchar2(40) field with a default value of 'Seattle' to the customers table.
Drop column in table
Читайте также: