Alter table оператор

CREATE TABLE Examples

Alright, now it’s time to look at some examples of creating a table.

These CREATE TABLE examples cover all of the topics I’ve mentioned in this article. It usually helps to see examples with data and real names, rather than syntax.

Each of the examples demonstrates the SQL using syntax for Oracle, SQL Server, MySQL, and PostgreSQL.

Example 1 – Basic Table

This example uses a CREATE TABLE statement that creates a simple table with a couple of columns.

Database	SQL
Oracle
SQL Server
MySQL
PostgreSQL

Example 2 – Large Table

This CREATE TABLE example includes many different columns and data types.

Database	SQL
Oracle
SQL Server
MySQL
PostgreSQL

Example 3 – Large Table with NOT NULL and DEFAULT

This CREATE TABLE example is similar to Example 2 but includes several NOT NULL constraints and some default values.

Database	SQL
Oracle
SQL Server
MySQL
PostgreSQL

Note: Prior to MySQL v8.0.13, using a function or expression (such as CURRENT_DATE) as a column’s default value was not supported. From the MySQL documentation:

As of 8.0.13 this now works. You can set CURRENT_DATE as the default for a date column.

Example 4 – Inline Primary Key

This example of the CREATE TABLE statement uses a primary key that is defined inline.

The syntax is the same for all databases, only the data types of the columns are different.

Database	SQL
Oracle
SQL Server
MySQL
PostgreSQL

Example 5 – Inline Primary Key and Foreign Key

This CREATE TABLE example uses an inline primary key and inline foreign key.

The syntax is the same for all databases, only the data types of the columns are different.

Database	SQL
Oracle
SQL Server
MySQL
PostgreSQL

Example 6 – Out of Line Primary Key and Foreign Key

This Oracle CREATE TABLE example declares a primary key and foreign key out of line (at the end of the column declarations).

The syntax is the same for all databases, only the data types of the columns are different.

Database	SQL
Oracle
SQL Server
MySQL
PostgreSQL

Example 7 – More Constraints

This example uses CREATE TABLE to declare a table with a primary key, foreign key, unique constraint, and check constraint.

Database	SQL
Oracle
SQL Server
MySQL
PostgreSQL

Example 8 – Create Table as Select with All Columns

This example uses the Create Table as Select to create a table from another table, using all columns.

The syntax is the same for Oracle, SQL Server, MySQL, and PostgreSQL.

Example 9 – Create Table as Select with Some Columns

This example uses the Create Table as Select to create a table from another table, using only some of the columns.

The syntax is the same for Oracle, SQL Server, MySQL, and PostgreSQL.

Example 10 – Create Table as Select with No Data

This example uses the Create Table as Select to create a table from another table, but no data is added to the new table.

The syntax is the same for Oracle, SQL Server, MySQL, and PostgreSQL.

SQL PRIMARY KEY on ALTER TABLE

To create a PRIMARY KEY constraint on the «ID» column when the table is already created, use the following SQL:

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Persons
ADD PRIMARY KEY (ID);

To allow naming of a PRIMARY KEY constraint, and for defining a PRIMARY KEY constraint on multiple columns, use the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Persons
ADD CONSTRAINT PK_Person PRIMARY KEY (ID,LastName);

Note: If you use the ALTER TABLE statement to add a primary key, the primary key column(s) must
already have been declared to not contain NULL values (when the table was first created).

Create a MySQL Database Using MySQLi and PDO

The CREATE DATABASE statement is used to create a database in MySQL.

The following examples create a database named «myDB»:

Example (MySQLi Object-oriented)

<?php$servername = «localhost»;$username = «username»;$password = «password»;// Create connection$conn = new mysqli($servername, $username, $password);
// Check connection
if ($conn->connect_error) {
  die(«Connection failed: » . $conn->connect_error);}
// Create database
$sql = «CREATE DATABASE myDB»;if ($conn->query($sql) === TRUE) {  echo «Database created successfully»;} else {  echo «Error creating database: » . $conn->error;}$conn->close();
?>

Note: When you create a new database, you must only specify
the first three arguments to the mysqli object (servername, username and
password).Tip: If you have to use a specific port,
add an empty string for the database-name argument, like this: new mysqli(«localhost»,
«username», «password», «», port)

Example (MySQLi Procedural)

<?php$servername = «localhost»;$username = «username»;$password = «password»;// Create connection
$conn = mysqli_connect($servername, $username, $password);
// Check connection
if (!$conn) {
  die(«Connection failed: » . mysqli_connect_error());}// Create database
$sql = «CREATE DATABASE myDB»;
if (mysqli_query($conn, $sql)) {  echo «Database created successfully»;} else {  echo «Error creating database: » . mysqli_error($conn);}mysqli_close($conn);
?>

Note: The following PDO example create a database named «myDBPDO»:

Example (PDO)

<?php$servername = «localhost»;$username = «username»;
$password = «password»;try {  $conn = new PDO(«mysql:host=$servername», $username, $password);
  // set the PDO error mode to exception  $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  $sql = «CREATE DATABASE myDBPDO»;  // use exec() because no results are returned
  $conn->exec($sql);  echo «Database created successfully<br>»;} catch(PDOException $e)
{  echo $sql . «<br>» . $e->getMessage();
}$conn = null;
?>

Tip: A great benefit of PDO is that it has exception class to handle any problems that may
occur in our database queries. If an exception is thrown within the try{ } block,
the script stops executing and flows directly to the first catch(){ } block. In the catch block above we echo the SQL statement and
the generated error message.

SQL PRIMARY KEY on CREATE TABLE

The following SQL creates a PRIMARY KEY on the «ID» column when the «Persons» table is created:

MySQL:

CREATE TABLE Persons
(
    ID int NOT NULL,
   
LastName varchar(255) NOT NULL,
   
FirstName varchar(255),
   
Age int,
   
PRIMARY KEY (ID)
);

SQL Server / Oracle / MS Access:

CREATE TABLE Persons
(
    ID int NOT NULL PRIMARY KEY,
   
LastName varchar(255) NOT NULL,
   
FirstName varchar(255),
   
Age int
);

To allow naming of a PRIMARY KEY constraint, and for defining a PRIMARY KEY constraint on multiple columns, use the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

CREATE TABLE Persons
(
    ID int NOT NULL,
   
LastName varchar(255) NOT NULL,
   
FirstName varchar(255),
   
Age int,
   
CONSTRAINT PK_Person PRIMARY KEY (ID,LastName)
);

Note: In the example above there is only ONE PRIMARY KEY (PK_Person).
However, the VALUE of the primary key is made up of TWO COLUMNS (ID + LastName).

Syntax

In its simplest form, the syntax for the CREATE TABLE statement in MySQL is:

CREATE TABLE table_name
( 
  column1 datatype ,
  column2 datatype ,
  ...
);

However, the full syntax for the MySQL CREATE TABLE statement is:

CREATE  TABLE  table_name
( 
  column1 datatype 
                   
                   
                   
                   ,

  column2 datatype 
                   
                   
                   
                   ,
  ...

  | ] PRIMARY KEY  (index_col_name, ...)

  |  index_name  (index_col_name, ...)

  | ] UNIQUE  
          (index_col_name, ...)

  | {FULLTEXT | SPATIAL}  index_name (index_col_name, ...)

  | ] 
        FOREIGN KEY index_name (index_col_name, ...)
        REFERENCES another_table_name (index_col_name, ...)
        
        
        

  | CHECK (expression)

    {ENGINE | TYPE} = engine_name
  | AUTO_INCREMENT = value
  | AVG_ROW_LENGTH = value
  |  CHARACTER SET = charset_name
  | CHECKSUM = {0 | 1}
  |  COLLATE = collation_name
  | COMMENT = 'string'
  | DATA DIRECTORY = 'absolute path'
  | DELAY_KEY_WRITE = { 0 | 1 }
  | INDEX DIRECTORY = 'absolute path'
  | INSERT_METHOD = { NO | FIRST | LAST }
  | MAX_ROWS = value
  | MIN_ROWS = value
  | PACK_KEYS = {0 | 1 | DEFAULT}
  | PASSWORD = 'string'
  | RAID_TYPE = { 1 | STRIPED | RAIDO }
       RAID_CHUNKS = value
       RAID_CHUNKSIZE = value
  | ROW_FORMAT = {DEFAULT | DYNAMIC | FIXED | COMPRESSED}
  | UNION = (table1, ... )
);

Oracle CREATE TABLE Errors and Exceptions

These are some of the errors and exceptions that may appear when you’re creating a table. As a database developer, you’ll get these kinds of errors all the time!

Exception: ORA-00955: name is already used by an existing object

Reason: You’re attempting to create a table with a name that is already being used.

Resolution: Use a different name for your table, or drop the table with the existing name. Or, use a different schema for your table if that’s applicable.

Exception: ORA-02260: table can have only one primary key

Reason: You’re attempting to add a primary key to a table that already has one.

Resolution: Review your SQL CREATE TABLE statement and remove all but one PRIMARY KEY definition.

Exception: ORA-02267: column type incompatible with referenced column type

Reason: The column you’re referring to is not compatible with the column you’re defining it on. This could happen when defining a foreign key.

Resolution: Make sure the data types are the same for both columns. And, make sure you’re not referring to the same table for the foreign key (it should be a different table).

Exception: ORA-00904: : invalid identifier

Reason: There are many reasons for this error, but it’s usually a syntax error.

Resolution: Check that you have all of your commas and brackets in the right places in your statement.

Exception: ORA-00957: duplicate column name

Reason: You have two columns with the same name.

Resolution: Rename one of your columns to make sure it is not a duplicate. Also, check that your commas and brackets are all in the right places.

Пример WITH с функцией

Для примеров оператора WITH необходимо создать следующую тестовую таблицу.

Oracle PL/SQL

DROP TABLE t1 PURGE;

CREATE TABLE t1 AS
SELECT 1 AS id
FROM dual
CONNECT BY level <= 1000000;

— Сбор статистики на CTAS больше не требуется в 12c,
— при условии, что запрос выдается не-SYS пользователем.
— EXEC DBMS_STATS.gather_table_stats (USER, ‘t1’);

1 2 3 4 5 6 7 8 9 10

DROPTABLEt1PURGE;   CREATETABLEt1AS SELECT1ASid FROMdual CONNECTBYlevel<=1000000;   — Сбор статистики на CTAS больше не требуется в 12c, — при условии, что запрос выдается не-SYS пользователем. — EXEC DBMS_STATS.gather_table_stats (USER, ‘t1’);

В этом операторе WITH раздел объявления может использоваться для определения функций PL/SQL, как показано ниже.

Oracle PL/SQL

WITH
FUNCTION with_function(p_id IN NUMBER) RETURN NUMBER IS
BEGIN
RETURN p_id;
END;
SELECT with_function(id)
FROM t1
WHERE rownum = 1

—результат
WITH_FUNCTION(ID)
——————
1

1 2 3 4 5 6 7 8 9 10 11 12 13

WITH FUNCTIONwith_function(p_idINNUMBER)RETURNNUMBERIS BEGIN RETURNp_id; END; SELECTwith_function(id) FROMt1 WHERErownum=1   —результат WITH_FUNCTION(ID) —————— 1

С точки зрения разрешения имен функций, определенных в разделе объявлений PL/SQL оператора WITH, имеют приоритет над объектами с тем же именем, определенным на уровне схемы.

CREATE TABLE AS SELECT (CTAS)

SQL allows you to create a table based on a SELECT statement.

This is a very useful feature of the database.

It’s great for development – if you want to copy an existing table and use it for testing or development purposes without changing the real table. Or, if you want a similar table with the same structure.

The syntax for using this command is:

It’s also referred to as CTAS.

You specify the table_name for your new table, as well as the SELECT query to base it from.

If you want all records to be copied to the new table, you could specify SELECT * FROM old_table.

You can use a WHERE clause to restrict the values to be copied across.

Alternatively, you can create your table like normal, without the AS SELECT, and use an INSERT INTO SELECT to populate the records into the table.

Create private temporary table statement

To create a new private temporary table, you use the statement:

In this syntax:

First, specify the name of the temporary table, which follows the naming rule mentioned above.

Second, specify a list of columns with their definitions.

Third, use the clause to indicate whether the table is transaction-specific or session-specific:

• The option creates a private temporary table that is transaction-specific. At the end of the transaction, Oracle drops both table definition and data.
• The option creates a private temporary table that is session-specific. Oracle removes all data and drops the table at the end of the session.

By default, Oracle uses if you omit the option.

CREATE TABLE AS SELECT Without Copying Data

You can also use the CREATE TABLE AS SELECT to copy a table’s structure without any of the data.

It’s easier than trying to generate a create table script from an existing table.

To do this, you just adjust your WHERE clause in the SELECT query to ensure there are no rows returned.

How do you guarantee this?

By adding something that is clearly evaluated to FALSE.

A common example is WHERE 1=0. Or WHERE 1=2. Really, anything involving two numbers that are not equal will work.

So, the SELECT statement will return the column names and data types, but no data. When the table is created, it will have no data in it.

The syntax for this would be:

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 CREATE INDEX Statement

The CREATE INDEX statement is used to create indexes in tables.

Indexes are used to retrieve data from the database more quickly than
otherwise. The users cannot see the indexes, they are just used to speed up searches/queries.

Note: Updating a table with indexes takes more time than updating a table without (because the indexes also need an update).
So, only create indexes on columns that will be frequently searched against.

CREATE INDEX Syntax

Creates an index on a table. Duplicate values are allowed:

CREATE INDEX index_name
ON table_name (column1, column2, …);

CREATE UNIQUE INDEX Syntax

Creates a unique index on a table. Duplicate values are not allowed:

CREATE UNIQUE INDEX index_name
ON table_name (column1, column2, …);

Note: The syntax for creating indexes varies among different databases. Therefore: Check the syntax for creating indexes in your database.

Create Table Primary Key Syntax

You can specify a primary key on a table when you create in two ways:

1. Next to the column data type when you declare it on the same line (an inline constraint)
2. At the end of all column declarations (an out of line constraint)

The method you choose would be dependent on your team’s standards and whether or not you need to have two or more columns in your primary key (you can only specify one column in an inline constraint).

To declare a primary key inline using a CREATE TABLE statement:

That’s it. You just add the words PRIMARY KEY after the data type. They are separate words – there’s no underscore or hyphen between them.

The out of line method of declaring a primary key in a Create Table command is a bit different.

You need to add it after all of your column definitions. You also need to start with the word CONSTRAINT.

Then, give the primary key a name. You only need to do this for out of line constraints. In this example, I’ve given it the name of “pk_tbl1”, to indicate that it is a primary key, and the “tbl1” would be the name of the table.

Inside the brackets after the word PRIMARY KEY, you specify the column names. If there is more than one, separate them by a comma.

MySQL CREATE TABLE syntax

The statement allows you to create a new table in a database.

The following illustrates the basic syntax of the  statement:

Let’s examine the syntax in greater detail.

First, you specify the name of the table that you want to create after the  keywords. The table name must be unique within a database. The is optional. It allows you to check if the table that you create already exists in the database. If this is the case, MySQL will ignore the whole statement and will not create any new table.

Second, you specify a list of columns of the table in the section, columns are separated by commas.

Third, you can optionally specify the storage engine for the table in the clause. You can use any storage engine such as InnoDB and MyISAM. If you don’t explicitly declare a storage engine, MySQL will use InnoDB by default.

InnoDB became the default storage engine since MySQL version 5.5. The InnoDB storage engine brings many benefits of a relational database management system such as ACID transaction, referential integrity, and crash recovery. In the previous versions, MySQL used MyISAM as the default storage engine.

The following shows the syntax for a column’s definition:

Here are the details:

• The specifies the name of the column. Each column has a specific data type and optional size e.g., 
• The  constraint ensures that the column will not contain . Besides the constraint, a column may have additional constraint such as CHECK, and UNIQUE.
• The specifies a default value for the column.
• The  indicates that the value of the column is incremented by one automatically whenever a new row is inserted into the table. Each table has a maximum one  column.

After the column list, you can define table constraints such as UNIQUE, CHECK, PRIMARY KEY and FOREIGN KEY.

For example, if you want to set a column or a group of columns as the primary key, you use the following syntax:

Is There A CREATE TABLE IF NOT EXISTS Command?

Some databases have CREATE TABLE IF NOT EXISTS, others don’t.

• Oracle: No, but there is a workaround.
• SQL Server: No, but there is a workaround.
• MySQL: Yes, there is.
• PostgreSQL: Yes, there is

As mentioned in this StackOverflow answer:

Oracle CREATE TABLE IF NOT EXISTS Equivalent

To check if a table exists before creating it, you’ll need to write a PL/SQL block. There are a few ways you can check:

1. You can attempt to create a table, and catch the error that appears (ORA-00955: name is already in use by an existing object).
2. You can query the USER_TABLES view to find a count where the table name matches, and check if the value is > 0.
3. You can drop the table and then create it again.

I’ve written about finding a list of tables in Oracle here.

PostgreSQL CREATE TABLE IF NOT EXISTS

In PostgreSQL 9.1, this feature exists. You can simply add IF NOT EXISTS to the CREATE TABLE statement:

For earlier versions, one workaround is to use a function. But if you are creating a function to create a table if it doesn’t exist, perhaps there’s a better approach to your problem you can take.

MySQL CREATE TABLE statement examples

Let’s take some examples of creating new tables.

1) MySQL simple example

The following statement creates a new table named :

The tasks table has the following columns:

• The is an auto-increment column. If you use the statement to insert a new row into the table without specifying a value for the column, MySQL will automatically generate a sequential integer for the starting from 1.
• The column is a variable character string column whose maximum length is 255. It means that you cannot insert a string whose length is greater than 255 into this column. The constraint indicates that the column does not accept . In other words, you have to provide a non-NULL value when you insert or update this column.
• The and are columns. Because these columns do not have the constraint, they can store . The start_date column has a default value of the current date. In other words, if you don’t provide a value for the start_date column when you insert a new row, the start_date column will take the current date of the database server.
• The and are the columns which do not allow .
• The column is a column that accepts .
• The is a column that accepts the current time as the default value.

The is the primary key column of the table. It means that the values in the column will uniquely identify rows in the table.

Once you execute the statement to create the table, you can view its structure by using the statement:

This picture shows the database diagram of the table:

2) MySQL with a foreign key primary key example

Suppose each task has a checklist or to-do list. To store checklists of tasks, you can create a new table named as follows:

The table has a primary key that consists of two columns. Therefore, we used a table constraint to define the primary key:

In addition, the is the foreign key column that references to the column of the table , we used a foreign key constraint to establish this relationship:

You will learn more about the foreign key constraint in the subsequent tutorial.

This picture illustrates the table and its relationship with the table:

In this tutorial, you have learned how to use MySQL statement to create a new table in the database.

• Was this tutorial helpful?

Create a MySQL Table Using MySQLi and PDO

The CREATE TABLE statement is used to create a table in MySQL.

Notes on the table above:

The data type specifies what type of data the column can hold. For a complete
reference of all the available data types, go to our
Data Types reference.

After the data type, you can specify other optional attributes for each
column:

• NOT NULL — Each row must contain a value for that column, null values are not allowed
• DEFAULT value — Set a default value that is added when no other value is passed
• UNSIGNED — Used for number types, limits the stored data to positive numbers and zero
• AUTO INCREMENT — MySQL automatically increases the value of the field by 1 each time a new record is added
• PRIMARY KEY — Used to uniquely identify the rows in a table. The column with PRIMARY KEY setting is often an ID number, and is often used with AUTO_INCREMENT

Each table should have a primary key column (in this case: the «id» column).
Its value must be unique for each record in the table.

The following examples shows how to create the table in PHP: