Alter table

Содержание:

Examples
SQL Справочник
Practice Exercise #3:
- Solution for Practice Exercise #3:
SQL Учебник
Rename column in table
- Syntax
- Example
Изменить столбец в таблице
- Синтаксис
- Пример
Add multiple columns in table
- Syntax
- Example
Practice Exercise #5:
- Solution for Practice Exercise #5:
Modify Multiple columns in table
- Syntax
- Example
SQL References
SQL ALTER TABLE Example
Практическое упражнение №3:
- Решение для практического упражнения №3:
Modify column in table
- Syntax
- Example
Add column in table
- Syntax
- Example
Practice Exercise #3:
- Solution for Practice Exercise #3:
Modify multiple columns in table
- Syntax
- Example
Add column in table
- Syntax
- Example
SQL Пример ALTER TABLE
Update with a Value
SQL References
SQL References
Adding a Column with a Default Constraints

Examples

To add a column of type to a table:

ALTER TABLE distributors ADD COLUMN address varchar(30);

To drop a column from a table:

ALTER TABLE distributors DROP COLUMN address RESTRICT;

To change the types of two existing columns in one operation:

ALTER TABLE distributors
    ALTER COLUMN address TYPE varchar(80),
    ALTER COLUMN name TYPE varchar(100);

To change an integer column containing Unix timestamps to via a clause:

ALTER TABLE foo
    ALTER COLUMN foo_timestamp SET DATA TYPE timestamp with time zone
    USING
        timestamp with time zone 'epoch' + foo_timestamp * interval '1 second';

The same, when the column has a default expression that won’t automatically cast to the new data type:

ALTER TABLE foo
    ALTER COLUMN foo_timestamp DROP DEFAULT,
    ALTER COLUMN foo_timestamp TYPE timestamp with time zone
    USING
        timestamp with time zone 'epoch' + foo_timestamp * interval '1 second',
    ALTER COLUMN foo_timestamp SET DEFAULT now();

To rename an existing column:

ALTER TABLE distributors RENAME COLUMN address TO city;

To rename an existing table:

ALTER TABLE distributors RENAME TO suppliers;

To rename an existing constraint:

ALTER TABLE distributors RENAME CONSTRAINT zipchk TO zip_check;

To add a not-null constraint to a column:

ALTER TABLE distributors ALTER COLUMN street SET NOT NULL;

To remove a not-null constraint from a column:

ALTER TABLE distributors ALTER COLUMN street DROP NOT NULL;

To add a check constraint to a table and all its children:

ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5);

To add a check constraint only to a table and not to its children:

ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5) NO INHERIT;

(The check constraint will not be inherited by future children, either.)

To remove a check constraint from a table and all its children:

ALTER TABLE distributors DROP CONSTRAINT zipchk;

To remove a check constraint from one table only:

ALTER TABLE ONLY distributors DROP CONSTRAINT zipchk;

(The check constraint remains in place for any child tables.)

To add a foreign key constraint to a table:

ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES addresses (address);

To add a foreign key constraint to a table with the least impact on other work:

ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES addresses (address) NOT VALID;
ALTER TABLE distributors VALIDATE CONSTRAINT distfk;

To add a (multicolumn) unique constraint to a table:

ALTER TABLE distributors ADD CONSTRAINT dist_id_zipcode_key UNIQUE (dist_id, zipcode);

To add an automatically named primary key constraint to a table, noting that a table can only ever have one primary key:

ALTER TABLE distributors ADD PRIMARY KEY (dist_id);

To move a table to a different tablespace:

ALTER TABLE distributors SET TABLESPACE fasttablespace;

To move a table to a different schema:

ALTER TABLE myschema.distributors SET SCHEMA yourschema;

To recreate a primary key constraint, without blocking updates while the index is rebuilt:

CREATE UNIQUE INDEX CONCURRENTLY dist_id_temp_idx ON distributors (dist_id);
ALTER TABLE distributors DROP CONSTRAINT distributors_pkey,
    ADD CONSTRAINT distributors_pkey PRIMARY KEY USING INDEX dist_id_temp_idx;

To attach a partition to a range-partitioned table:

ALTER TABLE measurement
    ATTACH PARTITION measurement_y2016m07 FOR VALUES FROM ('2016-07-01') TO ('2016-08-01');

To attach a partition to a list-partitioned table:

ALTER TABLE cities
    ATTACH PARTITION cities_ab FOR VALUES IN ('a', 'b');

To attach a partition to a hash-partitioned table:

ALTER TABLE orders
    ATTACH PARTITION orders_p4 FOR VALUES WITH (MODULUS 4, REMAINDER 3);

To attach a default partition to a partitioned table:

ALTER TABLE cities
    ATTACH PARTITION cities_partdef DEFAULT;

To detach a partition from a partitioned table:

SQL Справочник

Oracle / plsql: foreign keys with set null on delete

SQL Ключевые слова
ADD
ADD CONSTRAINT
ALTER
ALTER COLUMN
ALTER TABLE
ALL
AND
ANY
AS
ASC
BACKUP DATABASE
BETWEEN
CASE
CHECK
COLUMN
CONSTRAINT
CREATE
CREATE DATABASE
CREATE INDEX
CREATE OR REPLACE VIEW
CREATE TABLE
CREATE PROCEDURE
CREATE UNIQUE INDEX
CREATE VIEW
DATABASE
DEFAULT
DELETE
DESC
DISTINCT
DROP
DROP COLUMN
DROP CONSTRAINT
DROP DATABASE
DROP DEFAULT
DROP INDEX
DROP TABLE
DROP VIEW
EXEC
EXISTS
FOREIGN KEY
FROM
FULL OUTER JOIN
GROUP BY
HAVING
IN
INDEX
INNER JOIN
INSERT INTO
INSERT INTO SELECT
IS NULL
IS NOT NULL
JOIN
LEFT JOIN
LIKE
LIMIT
NOT
NOT NULL
OR
ORDER BY
OUTER JOIN
PRIMARY KEY
PROCEDURE
RIGHT JOIN
ROWNUM
SELECT
SELECT DISTINCT
SELECT INTO
SELECT TOP
SET
TABLE
TOP
TRUNCATE TABLE
UNION
UNION ALL
UNIQUE
UPDATE
VALUES
VIEW
WHERE

MySQL Функции
Функции строк
ASCII
CHAR_LENGTH
CHARACTER_LENGTH
CONCAT
CONCAT_WS
FIELD
FIND_IN_SET
FORMAT
INSERT
INSTR
LCASE
LEFT
LENGTH
LOCATE
LOWER
LPAD
LTRIM
MID
POSITION
REPEAT
REPLACE
REVERSE
RIGHT
RPAD
RTRIM
SPACE
STRCMP
SUBSTR
SUBSTRING
SUBSTRING_INDEX
TRIM
UCASE
UPPER
Функции чисел
ABS
ACOS
ASIN
ATAN
ATAN2
AVG
CEIL
CEILING
COS
COT
COUNT
DEGREES
DIV
EXP
FLOOR
GREATEST
LEAST
LN
LOG
LOG10
LOG2
MAX
MIN
MOD
PI
POW
POWER
RADIANS
RAND
ROUND
SIGN
SIN
SQRT
SUM
TAN
TRUNCATE
Функции дат
ADDDATE
ADDTIME
CURDATE
CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
CURTIME
DATE
DATEDIFF
DATE_ADD
DATE_FORMAT
DATE_SUB
DAY
DAYNAME
DAYOFMONTH
DAYOFWEEK
DAYOFYEAR
EXTRACT
FROM_DAYS
HOUR
LAST_DAY
LOCALTIME
LOCALTIMESTAMP
MAKEDATE
MAKETIME
MICROSECOND
MINUTE
MONTH
MONTHNAME
NOW
PERIOD_ADD
PERIOD_DIFF
QUARTER
SECOND
SEC_TO_TIME
STR_TO_DATE
SUBDATE
SUBTIME
SYSDATE
TIME
TIME_FORMAT
TIME_TO_SEC
TIMEDIFF
TIMESTAMP
TO_DAYS
WEEK
WEEKDAY
WEEKOFYEAR
YEAR
YEARWEEK
Функции расширений
BIN
BINARY
CASE
CAST
COALESCE
CONNECTION_ID
CONV
CONVERT
CURRENT_USER
DATABASE
IF
IFNULL
ISNULL
LAST_INSERT_ID
NULLIF
SESSION_USER
SYSTEM_USER
USER
VERSION

SQL Server функции
Функции строк
ASCII
CHAR
CHARINDEX
CONCAT
Concat with +
CONCAT_WS
DATALENGTH
DIFFERENCE
FORMAT
LEFT
LEN
LOWER
LTRIM
NCHAR
PATINDEX
QUOTENAME
REPLACE
REPLICATE
REVERSE
RIGHT
RTRIM
SOUNDEX
SPACE
STR
STUFF
SUBSTRING
TRANSLATE
TRIM
UNICODE
UPPER
Функции чисел
ABS
ACOS
ASIN
ATAN
ATN2
AVG
CEILING
COUNT
COS
COT
DEGREES
EXP
FLOOR
LOG
LOG10
MAX
MIN
PI
POWER
RADIANS
RAND
ROUND
SIGN
SIN
SQRT
SQUARE
SUM
TAN
Функции дат
CURRENT_TIMESTAMP
DATEADD
DATEDIFF
DATEFROMPARTS
DATENAME
DATEPART
DAY
GETDATE
GETUTCDATE
ISDATE
MONTH
SYSDATETIME
YEAR
Функции расширений
CAST
COALESCE
CONVERT
CURRENT_USER
IIF
ISNULL
ISNUMERIC
NULLIF
SESSION_USER
SESSIONPROPERTY
SYSTEM_USER
USER_NAME

MS Access функции
Функции строк
Asc
Chr
Concat with &
CurDir
Format
InStr
InstrRev
LCase
Left
Len
LTrim
Mid
Replace
Right
RTrim
Space
Split
Str
StrComp
StrConv
StrReverse
Trim
UCase
Функции чисел
Abs
Atn
Avg
Cos
Count
Exp
Fix
Format
Int
Max
Min
Randomize
Rnd
Round
Sgn
Sqr
Sum
Val
Функции дат
Date
DateAdd
DateDiff
DatePart
DateSerial
DateValue
Day
Format
Hour
Minute
Month
MonthName
Now
Second
Time
TimeSerial
TimeValue
Weekday
WeekdayName
Year
Другие функции
CurrentUser
Environ
IsDate
IsNull
IsNumeric

SQL ОператорыSQL Типы данныхSQL Краткий справочник

Practice Exercise #3:

Mysql substr() function

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.

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

Solution for Practice Exercise #3:

The following Oracle ALTER TABLE statement would add the contact_name and last_contacted columns to the customers table:

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

SQL Учебник

All (transact-sql)all (transact-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 Комментарии

Rename column in table

Syntax

To rename a column in an existing table, the SQL ALTER TABLE syntax is:

For Oracle and PostgreSQL:

ALTER TABLE table_name
  RENAME COLUMN old_name TO new_name;

For SQL Server (using the stored procedure called sp_rename):

sp_rename 'table_name.old_column', 'new_name', 'COLUMN';

For MySQL and MariaDB:

ALTER TABLE table_name
  CHANGE COLUMN old_name TO new_name;

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:

ALTER TABLE supplier
  RENAME COLUMN supplier_name TO sname;

For SQL Server (using the stored procedure called sp_rename):

sp_rename 'supplier.supplier_name', 'sname', 'COLUMN';

For MySQL and MariaDB:

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

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

Вы не можете использовать оператор ALTER TABLE для изменения столбца в SQLite. Вместо этого вам нужно будет переименовать таблицу, создать новую таблицу и скопировать данные в новую таблицу.

Синтаксис

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

PRAGMA foreign_keys=off;

BEGIN TRANSACTION;

ALTER TABLE table1 RENAME TO _table1_old;

CREATE TABLE table1 ( ( column1 datatype , column2 datatype , … );

INSERT INTO table1 (column1, column2, … column_n) SELECT column1, column2, … column_n FROM _table1_old;

COMMIT;

PRAGMA foreign_keys=on;

Пример

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

Например, если у нас была таблица employees, в которой был столбец с именем last_name, который был определен как тип данных CHAR:

PgSQL

CREATE TABLE employees
( employee_id INTEGER PRIMARY KEY AUTOINCREMENT,
last_name CHAR NOT NULL,
first_name VARCHAR,
hire_date DATE
);

1
2
3
4
5
6

CREATETABLEemployees
(employee_idINTEGERPRIMARYKEYAUTOINCREMENT,

last_nameCHARNOT NULL,

first_nameVARCHAR,

hire_dateDATE

);

И мы хотели изменить тип данных поля last_name на VARCHAR, мы могли бы сделать следующее:

PgSQL

PRAGMA foreign_keys=off;

BEGIN TRANSACTION;

ALTER TABLE employees RENAME TO _employees_old;

CREATE TABLE employees
( employee_id INTEGER PRIMARY KEY AUTOINCREMENT,
last_name VARCHAR NOT NULL,
first_name VARCHAR,
hire_date DATE
);

INSERT INTO employees (employee_id, last_name, first_name, hire_date)
SELECT employee_id,
last_name,
first_name,
hire_date
FROM _employees_old;

COMMIT;

PRAGMA foreign_keys=on;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

PRAGMAforeign_keys=off;

BEGINTRANSACTION;

ALTERTABLEemployeesRENAMETO_employees_old;

CREATETABLEemployees
(employee_idINTEGERPRIMARYKEYAUTOINCREMENT,

last_nameVARCHARNOT NULL,

first_nameVARCHAR,

hire_dateDATE

);

INSERTINTOemployees(employee_id,last_name,first_name,hire_date)

SELECTemployee_id,

last_name,

first_name,

hire_date

FROM_employees_old;

COMMIT;

PRAGMAforeign_keys=on;

Этот пример переименует нашу существующую таблицу employees в _employees_old. Затем он создаст новую таблицу employees с полем last_name, определенным как тип данных VARCHAR. Затем он вставит все данные из таблицы _employees_old в таблицу employees.

Add multiple columns in table

Syntax

The syntax to add multiple columns in a table in MySQL (using the ALTER TABLE statement) is:

ALTER TABLE table_name
  ADD new_column_name column_definition
    ,
  ADD new_column_name column_definition
    ,
  ...
;

table_name: The name of the table to modify.
new_column_name: The name of the new column to add to the table.
column_definition: The datatype and definition of the column (NULL or NOT NULL, etc).
FIRST | AFTER column_name: Optional. It tells MySQL where in the table to create the column. If this parameter is not specified, the new column will be added to the end of the table.

Example

Let’s look at an example that shows how to add multiple columns in a MySQL table using the ALTER TABLE statement.

For example:

ALTER TABLE contacts
  ADD last_name varchar(40) NOT NULL
    AFTER contact_id,
  ADD first_name varchar(35) NULL
    AFTER last_name;

This ALTER TABLE example will add two columns to the contacts table — last_name and first_name.

Practice Exercise #5:

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.

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

Solution for Practice Exercise #5:

The following Oracle ALTER TABLE statement would modify the customer_name and state columns accordingly in the customers table:

ALTER TABLE customers
  MODIFY (customer_name varchar2(50) NOT NULL,
          state varchar2(2));

Modify Multiple columns in table

Syntax

The syntax to modify multiple columns in a table in MySQL (using the ALTER TABLE statement) is:

ALTER TABLE table_name
  MODIFY column_name column_definition
    ,
  MODIFY column_name column_definition
    ,
  ...
;

table_name: The name of the table to modify.
column_name: The name of the column to modify in the table.
column_definition: The modified datatype and definition of the column (NULL or NOT NULL, etc).
FIRST | AFTER column_name: Optional. It tells MySQL where in the table to position the column, if you wish to change its position.

Example

Let’s look at an example that shows how to modify multiple columns in a MySQL table using the ALTER TABLE statement.

For example:

ALTER TABLE contacts
  MODIFY last_name varchar(55) NULL
    AFTER contact_type,
  MODIFY first_name varchar(30) NOT NULL;

This ALTER TABLE example will modify two columns to the contacts table — last_name and first_name.

SQL References

SQL Keywords
ADD
ADD CONSTRAINT
ALTER
ALTER COLUMN
ALTER TABLE
ALL
AND
ANY
AS
ASC
BACKUP DATABASE
BETWEEN
CASE
CHECK
COLUMN
CONSTRAINT
CREATE
CREATE DATABASE
CREATE INDEX
CREATE OR REPLACE VIEW
CREATE TABLE
CREATE PROCEDURE
CREATE UNIQUE INDEX
CREATE VIEW
DATABASE
DEFAULT
DELETE
DESC
DISTINCT
DROP
DROP COLUMN
DROP CONSTRAINT
DROP DATABASE
DROP DEFAULT
DROP INDEX
DROP TABLE
DROP VIEW
EXEC
EXISTS
FOREIGN KEY
FROM
FULL OUTER JOIN
GROUP BY
HAVING
IN
INDEX
INNER JOIN
INSERT INTO
INSERT INTO SELECT
IS NULL
IS NOT NULL
JOIN
LEFT JOIN
LIKE
LIMIT
NOT
NOT NULL
OR
ORDER BY
OUTER JOIN
PRIMARY KEY
PROCEDURE
RIGHT JOIN
ROWNUM
SELECT
SELECT DISTINCT
SELECT INTO
SELECT TOP
SET
TABLE
TOP
TRUNCATE TABLE
UNION
UNION ALL
UNIQUE
UPDATE
VALUES
VIEW
WHERE

MySQL Functions
String Functions
ASCII
CHAR_LENGTH
CHARACTER_LENGTH
CONCAT
CONCAT_WS
FIELD
FIND_IN_SET
FORMAT
INSERT
INSTR
LCASE
LEFT
LENGTH
LOCATE
LOWER
LPAD
LTRIM
MID
POSITION
REPEAT
REPLACE
REVERSE
RIGHT
RPAD
RTRIM
SPACE
STRCMP
SUBSTR
SUBSTRING
SUBSTRING_INDEX
TRIM
UCASE
UPPER

Numeric Functions
ABS
ACOS
ASIN
ATAN
ATAN2
AVG
CEIL
CEILING
COS
COT
COUNT
DEGREES
DIV
EXP
FLOOR
GREATEST
LEAST
LN
LOG
LOG10
LOG2
MAX
MIN
MOD
PI
POW
POWER
RADIANS
RAND
ROUND
SIGN
SIN
SQRT
SUM
TAN
TRUNCATE

Date Functions
ADDDATE
ADDTIME
CURDATE
CURRENT_DATE
CURRENT_TIME
CURRENT_TIMESTAMP
CURTIME
DATE
DATEDIFF
DATE_ADD
DATE_FORMAT
DATE_SUB
DAY
DAYNAME
DAYOFMONTH
DAYOFWEEK
DAYOFYEAR
EXTRACT
FROM_DAYS
HOUR
LAST_DAY
LOCALTIME
LOCALTIMESTAMP
MAKEDATE
MAKETIME
MICROSECOND
MINUTE
MONTH
MONTHNAME
NOW
PERIOD_ADD
PERIOD_DIFF
QUARTER
SECOND
SEC_TO_TIME
STR_TO_DATE
SUBDATE
SUBTIME
SYSDATE
TIME
TIME_FORMAT
TIME_TO_SEC
TIMEDIFF
TIMESTAMP
TO_DAYS
WEEK
WEEKDAY
WEEKOFYEAR
YEAR
YEARWEEK

Advanced Functions
BIN
BINARY
CASE
CAST
COALESCE
CONNECTION_ID
CONV
CONVERT
CURRENT_USER
DATABASE
IF
IFNULL
ISNULL
LAST_INSERT_ID
NULLIF
SESSION_USER
SYSTEM_USER
USER
VERSION

SQL Server Functions
String Functions
ASCII
CHAR
CHARINDEX
CONCAT
Concat with +
CONCAT_WS
DATALENGTH
DIFFERENCE
FORMAT
LEFT
LEN
LOWER
LTRIM
NCHAR
PATINDEX
QUOTENAME
REPLACE
REPLICATE
REVERSE
RIGHT
RTRIM
SOUNDEX
SPACE
STR
STUFF
SUBSTRING
TRANSLATE
TRIM
UNICODE
UPPER

Numeric Functions
ABS
ACOS
ASIN
ATAN
ATN2
AVG
CEILING
COUNT
COS
COT
DEGREES
EXP
FLOOR
LOG
LOG10
MAX
MIN
PI
POWER
RADIANS
RAND
ROUND
SIGN
SIN
SQRT
SQUARE
SUM
TAN

Date Functions
CURRENT_TIMESTAMP
DATEADD
DATEDIFF
DATEFROMPARTS
DATENAME
DATEPART
DAY
GETDATE
GETUTCDATE
ISDATE
MONTH
SYSDATETIME
YEAR

Advanced Functions
CAST
COALESCE
CONVERT
CURRENT_USER
IIF
ISNULL
ISNUMERIC
NULLIF
SESSION_USER
SESSIONPROPERTY
SYSTEM_USER
USER_NAME

MS Access Functions
String Functions
Asc
Chr
Concat with &
CurDir
Format
InStr
InstrRev
LCase
Left
Len
LTrim
Mid
Replace
Right
RTrim
Space
Split
Str
StrComp
StrConv
StrReverse
Trim
UCase

Numeric Functions
Abs
Atn
Avg
Cos
Count
Exp
Fix
Format
Int
Max
Min
Randomize
Rnd
Round
Sgn
Sqr
Sum
Val

Date Functions
Date
DateAdd
DateDiff
DatePart
DateSerial
DateValue
Day
Format
Hour
Minute
Month
MonthName
Now
Second
Time
TimeSerial
TimeValue
Weekday
WeekdayName
Year

Other Functions
CurrentUser
Environ
IsDate
IsNull
IsNumeric

SQL OperatorsSQL Data TypesSQL Quick Ref

SQL ALTER TABLE Example

Look at the «Persons» table:

ID	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger

Now we want to add a column named «DateOfBirth» in the «Persons» table.

We use the following SQL statement:

ALTER TABLE Persons
ADD DateOfBirth date;

Notice that the new column, «DateOfBirth», is of type date and is going to hold a
date. The data type specifies what type of data the column can hold. For a complete
reference of all the data types available in MS Access, MySQL, and SQL Server,
go to our complete Data Types reference.

The «Persons» table will now look like this:

ID	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger

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

На основании таблицы customers ниже, добавьте два столбца. Один столбец с названием contact_name и типом данных VARCHAR2 (50), второй столбец с названием last_contacted и типом данных DATE.

Oracle PL/SQL

CREATE TABLE customers
( customer_id number(10) not null,
customer_name varchar2(50) not null,
address varchar2(50),
city varchar2(50),
state varchar2(25),
zip_code varchar2(10),
CONSTRAINT customers_pk PRIMARY KEY (customer_id)
);

1
2
3
4
5
6
7
8
9

CREATETABLEcustomers
(customer_idnumber(10)notnull,

customer_namevarchar2(50)notnull,

addressvarchar2(50),

cityvarchar2(50),

statevarchar2(25),

zip_codevarchar2(10),

CONSTRAINTcustomers_pkPRIMARYKEY(customer_id)
);

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

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

Oracle PL/SQL

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

1
2
3

ALTERTABLEcustomers

ADD(contact_namevarchar2(50),

last_contacteddate);

Modify column in table

Syntax

To MODIFY A COLUMN in an existing table, the Oracle ALTER TABLE syntax is:

ALTER TABLE table_name
  MODIFY column_name column_type;

Example

Let’s look at an example that shows how to modify a column in an Oracle table using the ALTER TABLE statement.

For example:

ALTER TABLE customers
  MODIFY customer_name varchar2(100) NOT NULL;

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:

ALTER TABLE customers
  MODIFY city varchar2(75) DEFAULT 'Seattle' NOT NULL;

Add column in table

Syntax

The syntax to add a column in a table in MySQL (using the ALTER TABLE statement) is:

ALTER TABLE table_name
  ADD new_column_name column_definition
    ;

table_name: The name of the table to modify.
new_column_name: The name of the new column to add to the table.
column_definition: The datatype and definition of the column (NULL or NOT NULL, etc).
FIRST | AFTER column_name: Optional. It tells MySQL where in the table to create the column. If this parameter is not specified, the new column will be added to the end of the table.

Example

Let’s look at an example that shows how to add a column in a MySQL table using the ALTER TABLE statement.

For example:

ALTER TABLE contacts
  ADD last_name varchar(40) NOT NULL
    AFTER contact_id;

Practice Exercise #3:

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.

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)
);

Solution for Practice Exercise #3:

The following SQL ALTER TABLE statement would add the contact_name and last_contacted columns to the customers table:

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

Modify multiple columns in table

Syntax

To modify multiple columns in an existing table, the SQL ALTER TABLE syntax is:

For Oracle:

ALTER TABLE table_name
  MODIFY (column_1 column_type,
          column_2 column_type,
          ...
          column_n column_type);

For MySQL and MariaDB:

ALTER TABLE table_name
  MODIFY column_1 column_definition
    ,
  MODIFY column_2 column_definition
    ,
  ...
;

For PostgreSQL:

ALTER TABLE table_name
  ALTER COLUMN column_name TYPE column_definition,
  ALTER COLUMN column_name TYPE column_definition,
  ...
;

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 Oracle:

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

For MySQL and MariaDB:

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

For PostgreSQL:

Add column in table

Syntax

To ADD A COLUMN in a table, the Oracle ALTER TABLE syntax is:

ALTER TABLE table_name
  ADD column_name column_definition;

Example

Let’s look at an example that shows how to add a column in an Oracle table using the ALTER TABLE statement.

For example:

ALTER TABLE customers
  ADD customer_name varchar2(45);

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:

ALTER TABLE customers
  ADD city varchar2(40) DEFAULT 'Seattle';

SQL Пример ALTER TABLE

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

ID	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger

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

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

ALTER TABLE Persons
ADD DateOfBirth date;

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

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

ID	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger

Update with a Value

Let us update the same column with a different value and let us get the same stats.

Resource Type	Request Mode	Records Count
DATABASE	S	1
OBJECT	X	1

The above table shows that the table is exclusively locked which means that the table is not accessible during the update.

Let us look at the table size.

The table has grown by some value as shown in the above figure.

The following are the details for the query expenses captured from the SQL Profiler.

CPU	29,781
Reads	9,188,986
Writes	527,436
Duration Mille Seconds	1,113,142

Evidently, when updating a column for a large table, resource consumption is high.

The following is the database file sizes when the column is added and updated the values.

These stats show that there is a remarkable difference between adding a column with a default value and adding a column and updating the column with a value later. During the column update, the transaction log will grow and exclusive locking will be placed on the table prohibiting any reads or writes to the table.

What is the difference in these scenarios? Prior to the SQL Server 2012, when adding a column with default value will cause the same behavior. In SQL Server 2012 and onwards, this is no longer the situation, the column is added online to the table and no update occurs and it is only a metadata change.

SQL References

Advanced Functions
BIN
BINARY
CASE
CAST
COALESCE
CONNECTION_ID
CONV
CONVERT
CURRENT_USER
DATABASE
IF
IFNULL
ISNULL
LAST_INSERT_ID
NULLIF
SESSION_USER
SYSTEM_USER
USER
VERSION

Numeric Functions
ABS
ACOS
ASIN
ATAN
ATN2
AVG
CEILING
COUNT
COS
COT
DEGREES
EXP
FLOOR
LOG
LOG10
MAX
MIN
PI
POWER
RADIANS
RAND
ROUND
SIGN
SIN
SQRT
SQUARE
SUM
TAN

Date Functions
CURRENT_TIMESTAMP
DATEADD
DATEDIFF
DATEFROMPARTS
DATENAME
DATEPART
DAY
GETDATE
GETUTCDATE
ISDATE
MONTH
SYSDATETIME
YEAR

Advanced Functions
CAST
COALESCE
CONVERT
CURRENT_USER
IIF
ISNULL
ISNUMERIC
NULLIF
SESSION_USER
SESSIONPROPERTY
SYSTEM_USER
USER_NAME

MS Access Functions
String Functions
Asc
Chr
Concat with &
CurDir
Format
InStr
InstrRev
LCase
Left
Len
LTrim
Mid
Replace
Right
RTrim
Space
Split
Str
StrComp
StrConv
StrReverse
Trim
UCase

Numeric Functions
Abs
Atn
Avg
Cos
Count
Exp
Fix
Format
Int
Max
Min
Randomize
Rnd
Round
Sgn
Sqr
Sum
Val

Date Functions
Date
DateAdd
DateDiff
DatePart
DateSerial
DateValue
Day
Format
Hour
Minute
Month
MonthName
Now
Second
Time
TimeSerial
TimeValue
Weekday
WeekdayName
Year

Other Functions
CurrentUser
Environ
IsDate
IsNull
IsNumeric

SQL OperatorsSQL Data TypesSQL Quick Ref

SQL References

Advanced Functions
BIN
BINARY
CASE
CAST
COALESCE
CONNECTION_ID
CONV
CONVERT
CURRENT_USER
DATABASE
IF
IFNULL
ISNULL
LAST_INSERT_ID
NULLIF
SESSION_USER
SYSTEM_USER
USER
VERSION

Numeric Functions
ABS
ACOS
ASIN
ATAN
ATN2
AVG
CEILING
COUNT
COS
COT
DEGREES
EXP
FLOOR
LOG
LOG10
MAX
MIN
PI
POWER
RADIANS
RAND
ROUND
SIGN
SIN
SQRT
SQUARE
SUM
TAN

Date Functions
CURRENT_TIMESTAMP
DATEADD
DATEDIFF
DATEFROMPARTS
DATENAME
DATEPART
DAY
GETDATE
GETUTCDATE
ISDATE
MONTH
SYSDATETIME
YEAR

Advanced Functions
CAST
COALESCE
CONVERT
CURRENT_USER
IIF
ISNULL
ISNUMERIC
NULLIF
SESSION_USER
SESSIONPROPERTY
SYSTEM_USER
USER_NAME

MS Access Functions
String Functions
Asc
Chr
Concat with &
CurDir
Format
InStr
InstrRev
LCase
Left
Len
LTrim
Mid
Replace
Right
RTrim
Space
Split
Str
StrComp
StrConv
StrReverse
Trim
UCase

Numeric Functions
Abs
Atn
Avg
Cos
Count
Exp
Fix
Format
Int
Max
Min
Randomize
Rnd
Round
Sgn
Sqr
Sum
Val

Date Functions
Date
DateAdd
DateDiff
DatePart
DateSerial
DateValue
Day
Format
Hour
Minute
Month
MonthName
Now
Second
Time
TimeSerial
TimeValue
Weekday
WeekdayName
Year

Other Functions
CurrentUser
Environ
IsDate
IsNull
IsNumeric

SQL OperatorsSQL Data TypesSQL Quick Ref

Adding a Column with a Default Constraints

To achieve the above objective, the easiest way is to add a column with a default constraint. When the default column is added, the default value will be added to the table. The following script makes use of SQL Server ALTER TABLE ADD Column (Status in our case) statement to add a column named Status with default constraint.

1
2
3

ALTERTABLESampleTable

ADDStatusCHAR(5000)

DEFAULT’INC’WITHVALUES

When the column is added as above, the Status column will be added with the Value INC for all records.

From the profiler following statics are captured via SQL Profiler during the column addition with default values.

CPU
Reads	185
Writes
Duration Mille Seconds	65

You can see that the column is added to the table even less than one second and operation is very minimal cost.

The following are the locking stats during the column that are added with a constraint.

Resource Type	Resource Subtype	Request Mode	Records Count
DATABASE		S	1
DATABASE	DDL	S	1
KEY		X	11

METADATA	DATA_SPACE	Sch-S	1
OBJECT		IX	6
OBJECT		Sch-M	2

Please note that the following query should be executed in an open transaction in order to capture the above locking statistics.

1
2
3
4
5
6
7
8
9

SELECT

resource_type,

resource_subtype,

request_mode,

COUNT(*)FROMsys.dm_tran_locks

WHERErequest_session_id=@@SPID

GROUPBYresource_type,

resource_subtype,

request_mode

This shows that Table (Object) has intended Exclusive lock which means that the table is not exclusively locked during the addition of the column. Also, adding a column with default value has not taken even a one minute though it has 500,000 records.

Let us see the table size.

You will see that nothing has changed.

Let us see the file sizes of the database.

Nothing much has changed to the data file as well as for the log file. All of these results indicate that adding a column with a default constraint will result in only a metadata change.