Managing tables in MySQL
You can create new table using below syntax.
CREATE TABLE `customer` (
`CustomerId` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`FirstName` varchar(30) NOT NULL,
`LastName` varchar(30) NOT NULL,
`EmailId` varchar(30) NOT NULL,
`AddressLine1` varchar(30) NOT NULL,
`AddressLine2` varchar(30) DEFAULT NULL,
`AreaName` varchar(30) NOT NULL,
`AreaCode` int(30) NOT NULL,
`CityName` varchar(30) NOT NULL,
`BirthDate` date DEFAULT NULL,
`MobileNo` varchar(20) NOT NULL,
`Sex` enum(‘m’,’f’) NOT NULL,
PRIMARY KEY (`CustomerId`),
UNIQUE KEY `CustomerId` (`CustomerId`),
UNIQUE KEY `EmailId` (`EmailId`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
SERIAL is an alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE.
CREATE TABLE `category` (
`CatId` int(20) unsigned NOT NULL AUTO_INCREMENT,
`CatName` varchar(30) NOT NULL,
`SubCatName` varchar(30) NOT NULL,
PRIMARY KEY (`CatId`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
CREATE TABLE `adverts` (
`AdId` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`CustomerId` bigint(20) unsigned NOT NULL,
`AdTitle` varchar(100) NOT NULL,
`AdDescription` varchar(1000) NOT NULL,
`CatId` int(20) unsigned NOT NULL,
`PostedDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`AdId`),
UNIQUE KEY `AdId` (`AdId`),
KEY `CustomerId_idx` (`CustomerId`),
KEY `CatId_idx` (`CatId`),
CONSTRAINT `CatId` FOREIGN KEY (`CatId`) REFERENCES `category` (`CatId`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `CustomerId` FOREIGN KEY (`CustomerId`) REFERENCES `customer` (`CustomerId`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
When trying to add a foreign key, you may get generic errno 150 message.
When trying to add foreign key constraint, ensure that below conditions are met.
-The two tables must have the same charset.
-Data types of columns being linked should be same.
-Collation types of columns being linked should be same.
-Values corresponding to the foreign key column should be available in parent table.
- Not NULL – values can not be null
- Unique – values can not be duplicate
- Primary Key – values can not be null and duplicate
- Foriegn Key – For each value for a foreign key column in the child table, there has to be a value in the primary table.
- Check – This is not supported in MySQL
- Default – We can set the default value for specific column.
- Auto Increment – This constraint means that values for this column will be automatically incremented when new record is inserted.
altering the table
Below query is used to modify the existing column in a table
ALTER TABLE Suburb MODIFY COLUMN name VARCHAR(60);
Below query is used to remove existing column from a table
ALTER TABLE Suburb DROP COLUMN city;
Below query is used to rename existing column from a table
ALTER TABLE TEMPTABLE CHANGE Name FirstName VARCHAR(30);
Below query is used to add new column column in a table
ALTER TABLE TEMPTABLE ADD LastName VARCHAR(30);
Dropping the table
Let us say you have a table with name SUBURB.
CREATE TABLE SUBURB(
id INT(20) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
city VARCHAR(30) NOT NULL,
code INT(6) NOT NULL,
We can drop above table using below syntax.
Drop table SUBURB;
Renaming the table
RENAME table T1 to T2
Please note that if you have any triggers, procedures or functions using old table name, you will have to update them as well.
Truncating the table
If we want to delete all records from the table, you can use below command.
delete from T1;
But above command is time consuming. So we use below command to clear the table.
TRUNCATE TABLE T1;
Difference between delete and truncate
1. Truncate requires the DROP privilege as it drops the table and creats new one with same name.
2. Truncate operation can not be rolled back.
3. Truncate does not invoke ON DELETE triggers.
4. It cannot be performed for InnoDB tables with parent-child foreign key relationships.
5. TRUNCATE is a DDL statement while DELETE is a DML statement.
6. Any AUTO_INCREMENT value is reset to its start value.
Getting table information
You can use below queries to get table information in MySQL.
SHOW COLUMNS FROM CUSTOMER;
You can use below command to generate the Create Table SQL query for any table say CUSTOMER.
SHOW CREATE TABLE CUSTOMER;
A storage engine
A storage engine is used to store the data in table.
You can use below query to view what all engines are available in MySQL server.
A MySQL has various types of storage engines as shown below.
- InnoDB – Supports transactions, row-level locking, foreign keys and save points
- MEMORY – Used for temporary tables
- BLACKHOLE – /dev/null storage engine – Nothing is stored.
- MyISAM – MyISAM storage engine
- CSV – CSV storage engine
- ARCHIVE – Archive storage engine
- PERFORMANCE_SCHEMA – Performance Schema engine
- FEDERATED – Federated MySQL storage engine
You can check the engine being used by any table using below query.
show table status in Deals where name =’customer’;
Below query shows the status of all tables in selected database (schema).
show table status;
Only InnoDB engine supports the transactions. So it is recommended to use this engine.
By default, InnoDB engine is used to create tables.
Collation of a table
Each table in MySQL can have different collation. Each column in a table can have different collation as well.
Collation is nothing but rules applied on the character set encodings.
SQL queries may return different output based on the collation used in the table.
By default, collations are latin1-default collation.
For example – below query will return the records where FirstName contains “AG” or “ag”
select * from temptable where FirstName LIKE “%AG%”;
To make it case sensitive, you can use COLLATE operator as shown in below query.
select * from temptable where FirstName COLLATE latin1_general_cs LIKE “%AG%”;
You can create temporary tables using below syntax. Temporary tables get wiped out as soon as session ends.
Create TEMPORARY TABLE xyz(
Cloning the table
You can also clone a table using below syntax. In below example, we have created new table pqr based on another table xyz.
Create table pqr select * from xyz;
To transfer the records from one table into another, you can use below syntax. Here we have inserted records into pqr from temptable.
Insert into pqr select * from temptable;