Основные операции с данными

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

На основе приведенной ниже таблицы customer измените столбец customer_name, чтобы он не допускал значений NULL, и измените столбец state на тип данных char(2).

PgSQL

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

1 2 3 4 5 6 7 8 9

CREATETABLEcustomers (customer_idintNOT NULL, customer_namechar(50), addresschar(50), citychar(50), statechar(25), zip_codechar(10), CONSTRAINTcustomers_pkPRIMARYKEY(customer_id) );

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

Следующий SQL оператор ALTER TABLE изменит столбцы customer_name и state соответственно в таблице customers.

PgSQL

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

1 2 3

ALTERTABLEcustomers MODIFY(customer_namechar(50)NOT NULL, statechar(2));

MySQL ALTER TABLE – Modify columns

1) Modify a column

Here is the basic syntax for modifying a column in a table:

It’s a good practice to view the attributes of a column before modifying it.

Suppose that you want to change the column a column with a maximum of 100 characters.

First, show the column list of the table:

Then, modify the column:

Finally, show the column list of the table to verify the change:

2) Modify multiple columns

The following statement allows you to modify multiple columns:

First, show the current columns of the table:

Second, use the statement to modify multiple columns:

In this example:

• First, modify the data type of the column from to
• Second, modify the column by setting the length to , removing the constraint, and changing its position to appear after the column.

Third, show the new column list of the table to verify the modifications:

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

На основе приведенной ниже таблицы employees удалите столбец salary.

PgSQL

CREATE TABLE employees
( employee_number int NOT NULL,
employee_name char(50) NOT NULL,
department_id int,
salary int,
CONSTRAINT employees_pk PRIMARY KEY (employee_number)
);

1 2 3 4 5 6 7

CREATETABLEemployees (employee_numberintNOT NULL, employee_namechar(50)NOT NULL, department_idint, salaryint, CONSTRAINTemployees_pkPRIMARYKEY(employee_number) );

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

Следующий SQL оператор ALTER TABLE удалит столбец salary из таблицы employees.

PgSQL

ALTER TABLE employees
DROP COLUMN salary;

1 2	ALTERTABLEemployees DROPCOLUMNsalary;

Altering (Changing) a Table Type

You can use a table type by using the TYPE clause along with the ALTER command. Try out the following example to change the testalter_tbl to MYISAM table type.

To find out the current type of a table, use the SHOW TABLE STATUS statement.

mysql> ALTER TABLE testalter_tbl TYPE = MYISAM;
mysql>  SHOW TABLE STATUS LIKE 'testalter_tbl'\G
*************************** 1. row ****************
           Name: testalter_tbl
           Type: MyISAM
     Row_format: Fixed
           Rows: 0
 Avg_row_length: 0
    Data_length: 0
Max_data_length: 25769803775
   Index_length: 1024
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2007-06-03 08:04:36
    Update_time: 2007-06-03 08:04:36
     Check_time: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

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

Синтаксис

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

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

Для MySQL и MariaDB.

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

Для PostgreSQL.

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

Пример

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

Для Oracle.

PgSQL

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

1 2 3

ALTERTABLEsupplier MODIFY(supplier_namechar(100)NOT NULL, citychar(75));

Для MySQL и MariaDB.

PgSQL

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

1 2 3

ALTERTABLEsupplier MODIFYsupplier_nameVARCHAR(100)NOT NULL, MODIFYcityVARCHAR(75);

Для PostgreSQL.

PgSQL

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

1 2 3 4

ALTERTABLEsupplier ALTERCOLUMNsupplier_nameTYPECHAR(100), ALTERCOLUMNsupplier_nameSETNOT NULL, ALTERCOLUMNcityTYPECHAR(75);

Sample Table

Let us create a sample table with sufficient data set from the following script.

1 2 3 4 5 6 7 8 9

IFEXISTS(SELECT1FROMSys.tableswhereName=’SampleTable’) DROPTABLESampleTable CREATETABLEdbo.SampleTable ( IDBIGINTIDENTITY(1,1)NOTNULLPRIMARYKEYCLUSTERED, DateTime4DATETIMEDEFAULTGETDATE(), Column1CHAR(1000)DEFAULT’MMMMMMMMMMMMMMMMM’, Column2CHAR(2000)DEFAULT’YYYYYYYYYYYYYYYYY’ )

The above script will create a sample table called SampleTable. Data fields are added so that the large size table will be created. Next, a large number of records were added by executing the following query multiple times.

1 2 3 4 5 6

SETNOCOUNTON INSERTINTOSampleTable (DateTime4,Column1,Column2) VALUES (GETDATE(),’XXXX’,’YYYY’) GO500000

After the above query is executed, 500,000 records are updated to the SampleTable. After executing the above query following is the table size and other parameters for the table.

This can be retrieved by sp_spaceused ‘SampleTable’

The following is the database size for data and the log file.

Let’s quickly go over the syntax of adding one column to an existing table by using ALTER TABLE ADD statement as shown below.

1 2	ALTERTABLEtbl_name ADDCol_namedata_typecol_constraint;

You can use the below statement to add column NewColumn1 to our table SampleTable.

1 2	ALTERTABLESampleTable ADDNewColumn1varchar(200)

Also, you can add multiple columns to a table using the single SQL Server ALTER TABLE statement as below.

1 2 3

ALTERTABLESampleTable ADDNewColumn2varchar(20), NewColumn3varchar(50)

When adding a column to a large table, typically you want to add the column and fill the added column with a default value. For example, if you want to add a status column, next is to fill the column with a default value.

Dropping, Adding or Repositioning a Column

If you want to drop an existing column i from the above MySQL table, then you will use the DROP clause along with the ALTER command as shown below −

mysql> ALTER TABLE testalter_tbl  DROP i;

A DROP clause will not work if the column is the only one left in the table.

To add a column, use ADD and specify the column definition. The following statement restores the i column to the testalter_tbl −

mysql> ALTER TABLE testalter_tbl ADD i INT;

After issuing this statement, testalter will contain the same two columns that it had when you first created the table, but will not have the same structure. This is because there are new columns that are added to the end of the table by default. So even though i originally was the first column in mytbl, now it is the last one.

mysql> SHOW COLUMNS FROM testalter_tbl;
+-------+---------+------+-----+---------+-------+
| Field |  Type   | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
|   c   | char(1) | YES  |     |   NULL  |       |
|   i   | int(11) | YES  |     |   NULL  |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

To indicate that you want a column at a specific position within the table, either use FIRST to make it the first column or AFTER col_name to indicate that the new column should be placed after the col_name.

Try the following ALTER TABLE statements, using SHOW COLUMNS after each one to see what effect each one has −

ALTER TABLE testalter_tbl DROP i;
ALTER TABLE testalter_tbl ADD i INT FIRST;
ALTER TABLE testalter_tbl DROP i;
ALTER TABLE testalter_tbl ADD i INT AFTER c;

The FIRST and AFTER specifiers work only with the ADD clause. This means that if you want to reposition an existing column within a table, you first must DROP it and then ADD it at the new position.

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;

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;

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

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

Синтаксис

Синтаксис RENAME A COLUMN в таблице в 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, которая была определена следующим образом:

PgSQL

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

1 2 3 4 5 6

CREATETABLEemployees (employee_idINTEGERPRIMARYKEYAUTOINCREMENT, last_nameVARCHARNOT NULL, first_nameVARCHAR, hire_dateDATE );

И мы хотели переименовать поле hire_date в start_date, мы могли бы сделать следующее:

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,
start_date DATE
);

INSERT INTO employees (employee_id, last_name, first_name, start_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, start_dateDATE );   INSERTINTOemployees(employee_id,last_name,first_name,start_date) SELECTemployee_id, last_name, first_name, hire_date FROM_employees_old;   COMMIT;   PRAGMAforeign_keys=on;

Этот пример переименует нашу существующую таблицу employees в _employees_old. Затем он создаст новую таблицу employees с полем hire_date с именем start_date. Затем он вставит все данные из таблицы _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

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