Mysql create table sql query

Mysql create table sql query

Blog Bustle 26/02/2020

We use create table sql query to create new table in database. We create tables inside database to store data.    

Points to remember:

1. First we define table name.

2. After that we define column names and their datatype.

3. We can define length or values of each datatype.

4. We can also set default length or value of column.

Mysql create table sql query syntax

CREATE TABLE table_name(
  column_1 datatype,
  column_2 datatype,
  column_3 datatype,
  .....
);

Mysql create table sql query syntax with primary key

CREATE TABLE table_name(
  column_1 datatype,
  column_2 datatype,
  column_3 datatype,
  .....
  column_n datatype,
  PRIMARY KEY (one or more column's name)
);

What is Primary Key?

We can make one or more columns of a table a primary key.

Only one primary key can exist in one table.

Primary key uniquely identifies each row or record of table.

Primary key, which contains unique value, can not have null value.

For example, i am going to create employee table in student database and mysql create table sql query will be

create table employee(
  id int auto_increment, 
  name varchar(50) not null, 
  mobile varchar(15) not null, 
  email varchar(15) not null, 
  address text,
  date datetime, 
  primary key(id)
);

mysql create table sql query

In this mysql create table sql query, you can write 'id int not null auto_increment' in place of 'id int auto_increment'. Both are correct because 'id' is primary key.

Column 'id' is set to auto increment. It means its default value is 1. Everytime its value will increment by 1 when new record is inserted into table. 

In this employee table, id is primary key. Column id datatype is integer. Its value can not be null. int, varchar, text and datetime are datatype. Integer datatype default length is 11. Varchar datatype default length is 255. Column name and email maximum length is 50. Column mobile maximum length is 15.

Copy table using create table

We can create copy of any existing table using create table.

Syntax

create table new_table_name as
select column_1, column_2, .... from existing_table_name 
where .... ;

For example, if we want to copy all data of subject table to subjects table then mysql create table sql query will be

create table subjects
select * from subject;

Other important links

Insert query in mysql

Update query in mysql

Delete query in mysql

Select query in mysql

Difference between sql and mysql

Mysql create table sql query searches:

Tags: #mysql