
You can also define some columns normally and add other columns from a SELECT.
You can also create columns in the normal way and assign them some values
using the query, this is done to force a certain type or other field
characteristics. The columns that are not named in the query will be placed
before the others. For example:

CREATE TABLE test (a INT NOT NULL, b CHAR(10)) ENGINE=MyISAM
  SELECT 5 AS b, c, d FROM another_table;

Remember that the query just returns data. If you want to use the same
indexes, or the same columns attributes ([NOT] NULL, DEFAULT, AUTO_INCREMENT)
in the new table, you need to specify them manually. Types and sizes are not
automatically preserved if no data returned by the SELECT requires the full
size, and VARCHAR could be converted into CHAR. The CAST() function can be
used to forcee the new table to use certain types.

Aliases (AS) are taken into account, and they should always be used when you
SELECT an expression (function, arithmetical operation, etc).

If an error occurs during the query, the table will not be created at all.

If the new table has a primary key or UNIQUE indexes, you can use the IGNORE
or REPLACE keywords to handle duplicate key errors during the query. IGNORE
means that the newer values must not be inserted an identical value exists in
the index. REPLACE means that older values must be overwritten.

If the columns in the new table are more than the rows returned by the query,
the columns populated by the query will be placed after other columns. Note
that if the strict SQL_MODE is on, and the columns that are not names in the
query do not have a DEFAULT value, an error will raise and no rows will be
copied.

Concurrent inserts are not used during the execution of a CREATE ... SELECT.

If the table already exists, an error similar to the following will be
returned:

ERROR 1050 (42S01): Table 't' already exists

If the IF NOT EXISTS clause is used and the table exists, a note will be
produced instead of an error.

To insert rows from a query into an existing table, INSERT ... SELECT can be
used.

Column Definitions
------------------

create_definition:
 { col_name column_definition | index_definition | period_definition | CHECK
(expr) }
column_definition:
 data_type
  [NOT NULL | NULL] [DEFAULT default_value | (expression)]
  [ON UPDATE [NOW | CURRENT_TIMESTAMP] [(precision)]]
  [AUTO_INCREMENT] [ZEROFILL] [UNIQUE [KEY] | [PRIMARY] KEY]
  [INVISIBLE] [{WITH|WITHOUT} SYSTEM VERSIONING]
  [COMMENT 'string'] [REF_SYSTEM_ID = value]
  [reference_definition]
 | data_type [GENERATED ALWAYS]
 AS { { ROW {START|END} } | { (expression) [VIRTUAL | PERSISTENT | STORED] } }
   [UNIQUE [KEY]] [COMMENT 'string']
constraint_definition:
 CONSTRAINT [constraint_name] CHECK (expression)
Note: Until MariaDB 10.4, MariaDB accepts the shortcut format with a
REFERENCES clause only in ALTER TABLE and CREATE TABLE statements, but that
syntax does nothing. For example:

CREATE TABLE b(for_key INT REFERENCES a(not_key));

MariaDB simply parses it without returning any error or warning, for
compatibility with other DBMS's. Before MariaDB 10.2.1 this was also true for
CHECK constraints. However, only the syntax described below creates foreign
keys.

From MariaDB 10.5, MariaDB will attempt to apply the constraint. See Foreign
Keys examples.

Each definition either creates a column in the table or specifies and index or
constraint on one or more columns. See Indexes below for details on creating
indexes.

Create a column by specifying a column name and a data type, optionally
followed by column options. See Data Types for a full list of data types
allowed in MariaDB.

NULL and NOT NULL
-----------------

Use the NULL or NOT NULL options to specify that values in the column may or
may not be NULL, respectively. By default, values may be NULL. See also NULL
Values in MariaDB.

DEFAULT Column Option
---------------------

MariaDB starting with 10.2.1
----------------------------
The DEFAULT clause was enhanced in MariaDB 10.2.1. Some enhancements include

* BLOB and TEXT columns now support DEFAULT.
* The DEFAULT clause can now be used with an expression or function.

Specify a default value using the DEFAULT clause. If you don't specify DEFAULT
then the following rules apply:

* If the column is not defined with NOT NULL, AUTO_INCREMENT or TIMESTAMP, an
explicit DEFAULT NULL will be added.
Note that in MySQL and in MariaDB before 10.1.6, you may get an explicit
DEFAULT for primary key parts, if not specified with NOT NULL.

The default value will be used if you INSERT a row without specifying a value
for that column, or if you specify DEFAULT for that column. Before MariaDB
10.2.1 you couldn't usually provide an expression or function to evaluate at
insertion time. You had to provide a constant default value instead. The one
exception is that you may use CURRENT_TIMESTAMP as the default value for a
TIMESTAMP column to use the current timestamp at insertion time.

CURRENT_TIMESTAMP may also be used as the default value for a DATETIME

From MariaDB 10.2.1 you can use most functions in DEFAULT. Expressions should
have parentheses around them. If you use a non deterministic function in
DEFAULT then all inserts to the table will be replicated in row mode. You can
even refer to earlier columns in the DEFAULT expression (excluding
AUTO_INCREMENT columns):

CREATE TABLE t1 (a int DEFAULT (1+1), b int DEFAULT (a+1));