Create Table in MySQL database

Here in this mysql tutorial, you will learn how to work with table in MySql database, create table, update table, delete table, truncate table etc, basically learn how to work with table structure and data type.

Things to learn in MySQL Table

In this tutorial you will learn about MySql Table data handling following points

How to create Table in MySQL
Alter Table in MySQL
Truncate, Delete, Drop Table
MySql table data types
Mysql table export

MySQL table syntax example

Before creating any object in database, you need to login with database credential like user name and password, if the user name is associated (has permission) with multiple databases, then you need to select the database you want to work with by using following statement.

use databaseName;

    
create TABLE  tbBookmark (
  bid integer  NOT NULL auto_increment,
  dbase varchar(255) NOT NULL default '',
  username varchar(255) NOT NULL default '',
  label varchar(255) COLLATE utf8_general_ci NOT NULL default '',
  querytext text NOT NULL,
  primary key(bid)
)

Things to Learn

You can set auto increment column at the time of table creation
```
bid integer  NOT NULL auto_increment
```
You can set default value of any column like example below
```
username varchar(255) NOT NULL default ''
```
To set the primary key column, at the end of all column declaration, just say primary key(columnName)

To view how many tables are there in database just execute the following command.

SHOW TABLES;

To check if the table is created with right data types for each column and field length etc. use describe command.

DESCRIBE tbbookmark;

Alter Table in MySQL database

Alter statement allow you to add or rename any column name in mysql table of current database.

Here in example below we are adding a new column name GeoLocation in tbBookmark table, optionally we want the column to be added after column user

ALTER TABLE tbBookmark  
ADD GeoLocation varchar(500)
AFTER user ;

Note: AFTER user is optional, otherwise any new column would be added to end by default;

Delete Data from Table in MySQL database

To delete data from mysql table, you just need to write following statement

Delete from TableName;
Delete from tablename where column1value='myvalue';

Remember to specify where clauses before executing delete statement; otherwise all data will be deleted from table

Note: delete statement will delete the record from database table, but will not change the index sequence, means if you had 10 data in table and you have deleted all of them by using delete statement, next time when you insert any data in that table, the index number will be 11 not 1, that’s the difference between delete and truncate.

Truncate Table in MySQL database

Truncate command will delete all the data from table, and set the index to 1 , means after truncating data when you insert new record , the new index number will be one.

Note: you can’t use where clause with truncate statement.

If there is any foreign key referenced in that table, then it may not allow you to execute truncate command, in that case you need to disable the foreign key constant first.

Truncate table TableName;

Drop Table in MySQL database

Drop command will delete the table object with data, means data structure you created for that specific table, will be lost permanently

Drop table TableName;

MySQL Tutorial | SQL Course | SQL Interview Questions

Learn MySQL Online

MySql Database Development Tutorials.