MySQL: Data Types

  • NSHIMIYUMWUNGELI Antoine Marie Zacharie
  • technology
MySQL: Data Types

String Datatypes

The following are the String Datatypes in MySQL:

Numeric Datatypes

The following are the Numeric Datatypes in MySQL:

Date/Time Datatypes

The following are the Date/Time Datatypes in MySQL:

Large Object (LOB) Datatypes

The following are the LOB Datatypes in MySQL:

Data Type Syntax Maximum Size Explanation TINYBLOB Maximum size of 255 bytes. BLOB(size) Maximum size of 65,535 bytes. Where size is the number of characters to store (size is optional and was introduced in MySQL 4.1) MEDIUMBLOB Maximum size of 16,777,215 bytes. LONGTEXT Maximum size of 4GB or 4,294,967,295 characters.

MySQL: CREATE TABLE Statement

This MySQL tutorial explains how to use the MySQL CREATE TABLE statement with syntax and examples.

Description

The MySQL CREATE TABLE statement allows you to create and define a table.

Syntax

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

CREATE TABLE table_name
( 
  column1 datatype [ NULL | NOT NULL ],
  column2 datatype [ NULL | NOT NULL ],
  ...
);

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

CREATE [ TEMPORARY ] TABLE [IF NOT EXISTS] table_name
( 
  column1 datatype [ NULL | NOT NULL ]
                   [ DEFAULT default_value ]
                   [ AUTO_INCREMENT ]
                   [ UNIQUE KEY | PRIMARY KEY ]
                   [ COMMENT 'string' ],

  column2 datatype [ NULL | NOT NULL ]
                   [ DEFAULT default_value ]
                   [ AUTO_INCREMENT ]
                   [ UNIQUE KEY | PRIMARY KEY ]
                   [ COMMENT 'string' ],
  ...

  | [CONSTRAINT [constraint_name]] PRIMARY KEY [ USING BTREE | HASH ] (index_col_name, ...)

  | [INDEX | KEY] index_name [ USING BTREE | HASH ] (index_col_name, ...)

  | [CONSTRAINT [constraint_name]] UNIQUE [ INDEX | KEY ] 
        [ index_name ] [ USING BTREE | HASH ] (index_col_name, ...)

  | {FULLTEXT | SPATIAL} [ INDEX | KEY] index_name (index_col_name, ...)

  | [CONSTRAINT [constraint_name]] 
        FOREIGN KEY index_name (index_col_name, ...)
        REFERENCES another_table_name (index_col_name, ...)
        [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
        [ ON DELETE { RESTRICT | CASCADE | SET NULL | NO ACTION } ]
        [ ON UPDATE { RESTRICT | CASCADE | SET NULL | NO ACTION } ]

  | CHECK (expression)

    {ENGINE | TYPE} = engine_name
  | AUTO_INCREMENT = value
  | AVG_ROW_LENGTH = value
  | [DEFAULT] CHARACTER SET = charset_name
  | CHECKSUM = {0 | 1}
  | [DEFAULT] 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, ... )
);
Parameters or Arguments

The data type for the column and can be one of the following:

Optional. It is the following syntax:

column_name [ (length) ] [ ASC | DESC ]

Note: There can only be one column in a table that is set as AUTO_INCREMENT and this column must be the primary key.

Example

Let's look at a MySQL CREATE TABLE example.

CREATE TABLE contacts
( contact_id INT(11) NOT NULL AUTO_INCREMENT,
  last_name VARCHAR(30) NOT NULL,
  first_name VARCHAR(25),
  birthday DATE,
  CONSTRAINT contacts_pk PRIMARY KEY (contact_id)
);

This MySQL CREATE TABLE example creates a table called contacts which has 4 columns and one primary key:

  • The first column is called contact_id which is created as an INT datatype (maximum 11 digits in length) and can not contain NULL values. It is set as an AUTO_INCREMENT field which means that it is an autonumber field (starting at 1, and incrementing by 1, unless otherwise specified.)
  • The second column is called last_name which is a VARCHAR datatype (maximum 30 characters in length) and can not contain NULL values.
  • The third column is called first_name which is a VARCHAR datatype (maximum 25 characters in length) and can contain NULL values.
  • The fourth column is called birthday which is a DATE datatype and can contain NULL values.
  • The primary key is called contacts_pk and is set to the contact_id column.

Next, let's create a table that has a DEFAULT VALUE.

CREATE TABLE suppliers
( supplier_id INT(11) NOT NULL AUTO_INCREMENT,
  supplier_name VARCHAR(50) NOT NULL,
  account_rep VARCHAR(30) NOT NULL DEFAULT 'TBD',
  CONSTRAINT suppliers_pk PRIMARY KEY (supplier_id)
);

This MySQL CREATE TABLE example creates a table called suppliers which has 3 columns and one primary key:

  • The first column is called supplier_id which is created as an INT datatype (maximum 11 digits in length) and can not contain NULL values. It is set as an AUTO_INCREMENT field.
  • The second column is called supplier_name which is a VARCHAR datatype (maximum 50 characters in length) and can not contain NULL values.
  • The third column is called account_rep which is a VARCHAR datatype (maximum 30 characters in length) and can not contain NULL values. If no value is provided for this column, the DEFAULT VALUE will be 'TBD'.
  • The primary key is called suppliers_pk and is set to the supplier_id column.
Pour être informé des derniers articles, inscrivez vous :
uburenganzira bwose bw'uru rubuga bwihariwe na Nshimiyumwungeri Antoine Marie Zacharie © 2014 -  Hébergé par Overblog