- 4 years ago
- Zaid Bin Khalid
- 2047 Views
-
3
In This Session, We Will Learn and Create SQL Table, the SQL CREATE TABLE Statement is used to create a new table in a database.
Syntax
The syntax for creating a SQL Create Table has mentioned below.
CREATE TABLE table_name (
column1_name data_type constraints,
column2_name data_type constraints,
....
);
To easily understand the syntax, let’s make a table, Type the following command on MySQL Command-line tool and then press enter.
-- Syntax for MySQL Database
CREATE TABLE persons (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
birth_date DATE,
phone VARCHAR(15) NOT NULL UNIQUE
);
-- Syntax for SQL Server Database
CREATE TABLE persons (
id INT NOT NULL PRIMARY KEY IDENTITY(1,1),
name VARCHAR(50) NOT NULL,
birth_date DATE,
phone VARCHAR(15) NOT NULL UNIQUE
);
SQL CREATE TABLE Example Explained:
The mention above the following example will create a table for “Persons” that contains five columns followed by.
- id
- name
- birth_date
- phone
From the above example, it is noticed that each column is followed by a data type declaration. This specifies the type of data each column will store. The data type can be string, integer, date, etc.
Some data type can also be declared by the length of parameters that indicates the character storage of each column. Such as, the ID column is of type int and will hold an integer, Name columns are of type Varchar(50) and will hold 50 characters, birth_date have date and phone have Varchar(15) data type.
Important Note.
The data type of the
columns depends on the database system.
For example, MySQL and SQL Server Supported only INT data type for Integer values, However, the Other System like Oracle database Supported only NUMBER dataType.
The mention below table summarizes the most commonly used data types by MySQL.
| Data Type | Description |
|---|
| INT | To stores numeric values (range -2147483648 to 2147483647) |
| DECIMAL | Can store decimal values along with exact precision. |
| CHAR | Will store fixed-length strings (maximum size of 255 characters). |
| VARCHAR | Stores variable-length strings with a maximum size of 65,535 characters. |
| TEXT | Stores strings (maximum size of 65,535 characters). |
| DATE | Stores data values in such YYYY-MM-DD format. |
| DATETIME | Stores combined date/time values in the YYYY-MM-DD HH:MM:SS format. |
| TIMESTAMP | Stores timestamp values. TIMESTAMP values are stored as the number of seconds since the Unix epoch (‘1970-01-01 00:00:01’ UTC). |
Create Table If Not Exists
If you are trying to create a table that has a database already existed in the server then you will get an error message. In order to avoid such MySQL you can use such optional clause “IF Not Exists” as follows:
CREATE TABLE IF NOT EXISTS persons (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
birth_date DATE,
phone VARCHAR(15) NOT NULL UNIQUE
);
- 4 years ago
- Zaid Bin Khalid
- 2047 Views
-
3