To create table in mysql following syntax is used
1 2 3 4 | create Table table_ name(column _name1 data_ type, column _name2 data_ type, column_ name3 data type, ……..); |
For example,
1 2 3 | create table student(id int, first_name varchar(30),last_name varchar(30), semester varchar(20),address varchar(80)); |
if table created without any error then it will show following statement
Result:
1 | Query OK, 0 rows affected (1.44 sec) |
This query will result a table with the above fields. The first column will hold number while other columns are type of varchar.
Create Table in Mysql with primary key
1 2 3 4 5 6 7 8 9 10 11 12 13 | create table student( id bigint(20) unsigned NOT NULL AUTO_INCREMENT, fname varchar(100), lname varchar(30), address varchar(80), mobile_no varchar(12), email_id varchar(100), city varchar(50), add_date timestamp DEFAULT CURRENT_TIMESTAMP, primary key(id), unique key(mobile_no), unique key(email_id) )ENGINE=InnoDB DEFAULT CHARSET=utf8; |
Above mysql create statement create a student table with primary key id,
with auto increment field value and two unique keys mobile_no and email_id,
at the time of creating user add_date is added CURRENT_TIMESTAMP as default value.
This table also uses mysql create table auto_increment
MySql create table if not exists
Syntax of create table if not exist isCREATE TABLE IF NOT EXISTS table_name(............);
To modity the student table with CREATE TABLE IF NOT EXISTS the query is as below
1 2 3 4 5 6 7 8 9 10 11 12 13 | CREATE TABLE IF NOT EXISTS student( id bigint(20) unsigned NOT NULL AUTO_INCREMENT, fname varchar(100), lname varchar(30), address varchar(80), mobile_no varchar(12), email_id varchar(100), city varchar(50), add_date timestamp DEFAULT CURRENT_TIMESTAMP, primary key(id), unique key(mobile_no), unique key(email_id) )ENGINE=InnoDB DEFAULT CHARSET=utf8 |