To create a database, execute the following statement in your MySQL query browser.
CREATE DATABASE test;
Lets create a table under the schema test:
CREATE TABLE `test`.`demo` (
`slno` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(45) NOT NULL,
`email` VARCHAR(45) NOT NULL,
`phone` VARCHAR(45) NOT NULL,
PRIMARY KEY (`slno`)
);
`test`.`demo` - indicates demo table is created under the schema demo. Schema is nothing but database.
UNSIGNED - indicates the column allows only nonnegative numbers.
NOT NULL - is a constraint which indicates the column does not accept null values. Which means the column should always contain a value.
AUTO_INCREMENT - indicates a number to be generated automatically when a new record is inserted into a table.
PRIMARY KEY - is a constraint which indicates the column must contain UNIQUE values, and cannot contain NULL values.
Integer and varchar are the datatypes of that column.
Constraints: It is used to specify some rules for the data in a table.
Example: Not null, primary key, unique, index, default etc..
Now lets study unique and index constraint.
UNIQUE : The UNIQUE constraint checks that all values in a column are different.
Now let's create a unique constraint for a column email and phone of the table demo by using the following statement.
ALTER TABLE demo
ADD CONSTRAINT ac_demo UNIQUE (email,phone);
The above statement indicates that the email and phone columns should be unique.
Index: It is used to fetch the data more quickly. They are just used to speed up searches/queries.
For example, if you want to select the data based on the name then create the index for the column name.The syntax for creating indexing is:
CREATE INDEX idx_name
ON demo(name);
where demo is the table name,idx_name is the index name and name is the column name.
