Give initial auto increment value while table creation

When we create a table we can give a auto increment value to a primary key filed. This field is auto incremented by one for newly inserted value. To start auto increment from specified number AUTO_INCREMENT = value is used while creating table. Example:

If you have already created a table and then want … Read more

Finding all column name of table

Suppose we have a table user and we want to fetch all its column name.For this we use INFORMATION_SCHEMA. we will use following query to fetch all column name of table

  Above query will fetch all columns of user table available in social database. Read More Setting innodb_lock_wait_timeout Set time zone in mysql … Read more

Drop command to remove a column from a table.

suppose we have a column address in table employee.

To see the description of table we use desc employee that will produce following result here we can see the description of table.

if we want to eliminate the column address from employee table then we use Drop command. Syntax:

Drop query

Read more

Date and Time

There are various pre defined function for data and time we will see them one by one. If you want to show current time then we use CURTIME().It returns time in HH:MM:SS string format

below is HHMMSS in numeric format

To select current data we use CURDATE().It returns data in YYYY-MM-DD string format. … Read more

Drop command: To eliminate a column from a table.

To eliminate a column from table we use drop command. Suppose we have table employee with an extra field Address and we want to eliminate this.

To remove this column we use Drop query. Syntax:

creating a new user in mysql

Here we are creating user with name manish and password for manish is password;

Creating a user and force user to change password at first login for that PASSWORD EXPIRE is used.

Creating a user and expiring the password after 180 days, PASSWORD EXPIRE INTERVAL 180 DAY is used

Creating a user … Read more

How to add new column in a table?

To insert new column in a table we use ALTER query. Syntax:

For example, if we consider the table employee we can write the query as:

by this query we see that a new column E_City has been added in table Employee.Further you can also insert values in column city by using update … Read more

Changing primary key of table

We have a table basic profile with following table structure

we don’t want to use it as primary key so first we removed its auto increment the we will remove it from primary key queries for these are

If you want to make primary key to some other column than we can … Read more

Alter table command to add foreign key

We have two tables company and location. First table manages company details and second table stores location detail of company. one company can span in multiple location. Initially we have created table for company and location as below.

checking the description of both table

Now We find that one company can … Read more

Drop foreign key constraints

Some times we have to drop the foreign key constraint that already exists, for that we have to first get the foreign key constraint name. To see detailed description of created table we use following query.

Result of above query is

Here we can see that company_id column is referring company tables id … Read more