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 use following query

above query will not make id1 as auto increment.
to make it a uto incremente we will execute following query insted of above

Now let us check the table description

We can also make composite primary key in existing table for that we use following query

This will make id1 and id2 primary key of table.

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 exists in multiple location so there is one to many relationship between company to location and to access location detail of company we have to create foreign key reference of company to location table.
We have already create location table so without deleted it again we will alter table to add foreign key refrence of company.
For adding new foreign key column we have to first add a column to location table then we will make it foreign key.
Before proceeding lets see the syntax of alter table command

Syntax

Checking location description you will find that a company_id field is added to location table and it is reffeering to company tables

To show detailed description you can use

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 field with the constraint name company_fk_id.

Now we have to drop this constraint for that we use ALTER TABLE as follows

Syntax

Here fk_symbol is foreign key constraint used during table creation here we given company_fk_id. If not given then it is system generated value.

Again checking show create table

So we have successfully removed foreign key reference.

Show mysql create table query

To see created table structure show create table command is used

Syntax

Example

Result

To avoid showing leading and trailing dots we use \G with command syntax is as below

Example

Result
*************************** 1. row ***************************
Table: student
Create Table: CREATE TABLE student (
id int(11) DEFAULT NULL,
first_name varchar(30) DEFAULT NULL,
last_name varchar(30) DEFAULT NULL,
semester varchar(20) DEFAULT NULL,
address varchar(80) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

MySql dump using java

To take mysql database backup we have Runtime.getRuntime().exec(executeCmd). executeCmd is command that will take backup of mysql database.
String executeCmd contains the mysqldump to backup database.
filePath variable contains the downloaded sql file.

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 to add some initial auto increment value then we have to alter the table as below.
auto increment will start from 1 by default.

To start auto increment from some specified value lets say 100 we use alter table

Syntax

Example

Set time zone in mysql

To set Time zone in GMT set time_zone is used with specified time_zone value

To check our change reflated on database use following query

Granting Privileges to users

After creating user. Privileges are granter to user to access specific database or to access all databases in mysql
GRANT ALL will give all privileges to user
To Grant All privileges to user following query is used

To provide all privileges on specific database following query is used

To provide all privileges on specific tabel following query is used

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 and never expire the password, PASSWORD EXPIRE NEVER is used

Creating a user and expire the password as defined in system variable default_password_lifetime
to use this PASSWORD EXPIRE DEFAULT is used

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

Here address is successfully removed.