Sql server: alter table statement

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 Комментарии

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

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;

Практическое упражнение №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);

Rename column in table

Syntax

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

ALTER TABLE table_name
  CHANGE COLUMN old_name new_name 
    column_definition
    
table_name
The name of the table to modify.
old_name
The column to rename.
new_name
The new name for the column.
column_definition
The datatype and definition of the column (NULL or NOT NULL, etc). You must specify the column definition when renaming the column, even if it does not change.
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 rename a column in a MySQL table using the ALTER TABLE statement.

For example:

ALTER TABLE contacts
  CHANGE COLUMN contact_type ctype
    varchar(20) NOT NULL;

Oracle ALTER TABLE MODIFY column examples

First, create a new table named for the demonstration:

Second, insert some rows into the table:

Third, verify the insert operation by using the following statement:

A) Modify the column’s visibility

In Oracle Database 12c, you can define table columns as invisible or visible. Invisible columns are not available for the query like:

Or statement like:

in SQL*Plus.

However, you can query the invisible columns by specify them explicitly in the query:

By default, table columns are visible. You can define invisible column when you create the table or using column statement.

For example, the following statement makes the column invisible:

The following statement returns data from all columns of the table except the column:


This is because the column is invisible.

To change a column from invisible to visible, you use the statement below:

B) Allow or not allow null example

However, Oracle issued the following error:

Because when you changed a column from nullable to non-nullable, you must ensure that the existing data meets the new constraint.

Note that the function converts a string to lowercase.

And then change the column’s constraint:

Now, it is working as expected.

C) Widen or shorten the size of a column example

Suppose, we want to add international code to the phone numbers. Before doing it, we must widen the size of the phone column by using the following statement:

Now, we can update the phone numbers:

The following statement verifies the update:

To shorten the size of a column, you make sure that all data in the column fits the new size.

For example, we try to shorten the size of the column down to 12 characters:

Oracle Database issued the following error:

To fix this, first, we should remove the international code from the phone numbers:

The function replaces a substring by a new substring. In this case, it replaces the ‘+1-‘ by an empty string.

And then shorten the size of the column:

D) Modify virtual column

Suppose, we the full name in the following format:

To do this, we can change the expression of the virtual column as follows:

The following statement verifies the modification:

E) Modify the default value of a column

Let’s add a new column named to the table with default value 1.

Once you executed the statement, the values in the status column are set to 1 for all existing rows in the table.

To change the default value of the status column to 0, you use the following statement:

We can add a new row to the table to check whether the default value of the column is 0 or 1:

Query data from the table:

As you can see, the value in the status column for the account with id 4 is 0 as expected.

In this tutorial, you have learned how to use the Oracle column statement to change the definition of existing columns in a table.

  • Was this tutorial helpful?

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

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.

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 DateOfBirth
1 Hansen Ola Timoteivn 10 Sandnes  
2 Svendson Tove Borgvn 23 Sandnes  
3 Pettersen Kari Storgt 20 Stavanger  

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

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

Вы не можете использовать оператор 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.

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

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.

Modify column in table

Syntax

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

For Oracle, MySQL, MariaDB:

ALTER TABLE table_name
  MODIFY column_name column_type;

For SQL Server:

ALTER TABLE table_name
  ALTER COLUMN column_name column_type;

For PostgreSQL:

ALTER TABLE table_name
  ALTER COLUMN column_name TYPE column_definition;

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

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

For MySQL and MariaDB:

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

For SQL Server:

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

For PostgreSQL:

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:

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 column in table

Syntax

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

ALTER TABLE table_name
  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 a column in a MySQL table using the ALTER TABLE statement.

For example:

ALTER TABLE contacts
  MODIFY last_name varchar(50) NULL;

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

С этим читают