Scribd
Upload
58 views

SQL Syntax

The document describes the SQL commands CREATE TABLE, ALTER TABLE, INSERT, UPDATE, DELETE, SELECT, and subqueries/UNION. CREATE TABLE is used to create a new table with specified columns, data types, constraints, and other properties. ALTER TABLE modifies existing tables by adding, deleting, or changing columns and constraints. INSERT adds new rows to a table. UPDATE modifies existing rows in a table. DELETE removes rows from a table. SELECT retrieves data from one or more tables. It can include aggregate functions, joins between multiple tables, subqueries, and set operations with UNION.

Uploaded by

shehryar iftikhar
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
58 views

SQL Syntax

The document describes the SQL commands CREATE TABLE, ALTER TABLE, INSERT, UPDATE, DELETE, SELECT, and subqueries/UNION. CREATE TABLE is used to create a new table with specified columns, data types, constraints, and other properties. ALTER TABLE modifies existing tables by adding, deleting, or changing columns and constraints. INSERT adds new rows to a table. UPDATE modifies existing rows in a table. DELETE removes rows from a table. SELECT retrieves data from one or more tables. It can include aggregate functions, joins between multiple tables, subqueries, and set operations with UNION.

Uploaded by

shehryar iftikhar
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 2

CREATE TABLE CREATE TABLE

CREATE TABLE tbl_name ( col_name data_type modifiers, ... )

column definition further


definitions further definitions — constraints:
data_type:
• bit: BIT(#bits)
• [CONSTRAINT [symbol]] PRIMARY KEY (col_name, ...)
• integer: BOOLEAN, TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT
• fixed point real: DECIMAL(#digits, #decimals) or NUMERIC(#d,#d) • [CONSTRAINT [symbol]] UNIQUE KEY [index_name] (col_name, ...)
• floating point real: FLOAT or DOUBLE
• date and time: DATE, TIME, DATETIME, YEAR • [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (col_name, ...)
• string: CHAR(#chars), VARCHAR(#chars)
• string value from a given list: ENUM('value1', 'value2', ...)
REFERENCES tbl_name (col_name, ...)
[ON DELETE
modifiers: {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
[NOT NULL | NULL] [AUTO_INCREMENT] [UNIQUE [KEY]] [[PRIMARY] KEY] [ON UPDATE
[DEFAULT default_value] [COMMENT 'string'] {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]

modifiers for derived attributes:


[GENERATED ALWAYS] AS (expr) [VIRTUAL | STORED] 1 2
[NOT NULL | NULL] [UNIQUE [KEY]] [[PRIMARY] KEY] [COMMENT 'string']

ALTER TABLE ALTER TABLE and CREATE VIEW


ALTER TABLE tbl_name [specification [, specification] ...]

specification:
specification — continued:
ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name]
| RENAME {INDEX|KEY} old_index_name TO new_index_name
| ADD [COLUMN] (col_name column_definition, ...)
| RENAME [TO|AS] new_tbl_name
| ADD {INDEX|KEY} ...
| ADD [CONSTRAINT [symbol]] PRIMARY KEY ...
| DROP [COLUMN] col_name
| ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] ...
| DROP {INDEX|KEY} index_name
| ADD [CONSTRAINT [symbol]] FOREIGN KEY ...
| DROP PRIMARY KEY

| ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT} | DROP FOREIGN KEY fk_symbol
| CHANGE [COLUMN] old_col_name new_col_name new_column_definition
[FIRST|AFTER col_name]
CREATE VIEW view_name [(column_name, ...)]
| MODIFY [COLUMN] col_name column_definition
AS SELECT ...
[FIRST | AFTER col_name] 3 4
INSERT, UPDATE, DELETE SELECT and aggregate functions

SELECT [ALL | DISTINCT]


INSERT [INTO] tbl_name [(col_name [, col_name] ...)] { * | [columnExpression [AS newName]] [,...] }
{VALUES | VALUE} (value_list) [, (value_list)] ...
FROM TableName [alias] [, ...]
[WHERE condition]
UPDATE table_name [GROUP BY columnList] [HAVING condition]
SET col_name = value [, col_name = value] ... [ORDER BY columnList]
[WHERE where_condition]

Aggregate functions:
DELETE FROM tbl_name [WHERE where_condition] COUNT(…), SUM(…), AVG(…), MIN(…), MAX(…)

5 6

SELECT — multi-table Subqueries and UNION

Alternative forms of FROM Subqueries:


… { WHERE | HAVING } columnExpression
{ = | > | < | >= | <= | <> | != | LIKE | [NOT] IN }
FROM TableName1 [alias] [LEFT | RIGHT] JOIN ( SELECT ... ) …
{ TableName2 [alias] | (TableName2 [alias], ...) }
… { WHERE | HAVING } columnExpression
ON condition { = | > | < | >= | <= | <> | != }
[ ANY | SOME | ALL ] ( SELECT ... ) …
FROM TableName1 [alias] [LEFT | RIGHT] JOIN
… { WHERE | HAVING } [NOT] EXISTS ( SELECT ... ) …
{ TableName2 [alias] | (TableName2 [alias], ...) }
USING (column1, ...) Union:
(SELECT ...)
FROM TableName1 [alias] UNION [ALL | DISTINCT]
NATURAL [LEFT | RIGHT] JOIN (SELECT ...)
[UNION [ALL | DISTINCT] (SELECT ...)]
{ TableName2 [alias] | (TableName2 [alias], ...) }
ORDER BY ... 8

You might also like