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.
In this tutorial you will learn about MySql Table data handling following points
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.
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) )
bid integer NOT NULL auto_increment
username varchar(255) NOT NULL default ''
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 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;
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 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 command will delete the table object with data, means data structure you created for that specific table, will be lost permanently
Drop table TableName;