Primary key in mysql

                         The primary key in mysql is used to identify the unique values in table. It don't allow duplicate entries in mysql table. The primary key column should be NOT NULL. It don't allow null values in mysql table. The mysql table can have only one primary key.

How to create primary key in mysql:

                         The mysql query for  create primary key is:

         Create table table_name (column_name datatype(length), primary key(column_name))

For example,
                        create table table1(name varchar(30), address varchar(100), primary key(name))

Where the column 'name' is primary key.

How to add primary key to existing table in mysql:

                       You created table without primary like this:

                

                   create table table1(name varchar(30),address varchar(100))

                     But you need to add primary key in order to remove duplicate entries in mysql. At the time you can add primary key to existing table in mysql. Following mysq query is used for add primary key.

                 Alter table table_name add primary key(column_name)

For example,

                 Alter table table1 add primary key(num)   

How to remove primary key in mysql:

                You created table with primary. But you need not primary key for particular table. Then how can remove primary key from table in mysql. The following mysql query is used to drop the primary key from table in mysql.

                    alter table table_name drop primary key

For example,

               

                      alter table table1 drop primary key 

          where, 

                       The primary key is removed from table 'table1'. Now you can add duplicate values to your mysql table.

Related Post:

distinct in mysql
Order by in mysql
Group by multiple columns in mysql
How to use order by together with group by in mysql
Difference between delete truncate drop in mysql
how to use order by before group by in mysql
How to reset auto increment initial value in mysql

Remove auto_increment from column in mysql

                       You created a table in which the column with specific definitions in mysql. You created table column with auto increment. To add auto increment to existing column, visit  add-auto-increment-to-existing-column in mysql. Then how can you remove it from column in mysql.  The following mysql query is used to remove auto increment from column in mysql.

Mysql Query:

                         Alter table table_name DROP PRIMARY KEY,
                          change column_name column_name datatype(length) definition

Consider the example:
The structure of table1 in database new look like this:

                        
                       Now we are going to remove auto increment from column 'No' in mysql table 'table1'.  The mysql query is:

                     Alter table table1 DROP PRIMARY KEY,
                      change No No int(5) NOT NULL

Now your structure look like this:

 
               Now you can see the column name 'No' is normal. There is no auto increment.

Related Post:

Change column name in mysql
Change column size in mysql table
Delete column in mysql table
Add column after specific field in mysql table
Move columns in mysql table

change column datatype in mysql
Change table name in mysql
Add primary key to existing column in mysql
how to add auto increment to existing column in mysql
How to reset auto increment initial value in mysql
How to use order by before group by in mysql
Select distinct values in mysql

Difference between the delete, truncate and drop in mysql

DELETE in MySql:

                     1. The mysql command 'DELETE' is used to delete space allocated by mysql server and the structure of table remains same.

                     2. It is removed rows.

                     3. It can be used by either 'WHERE' clause or without it in mysql.

                     4. The data can be roll backed, if it is removed by 'DELETE' mysql command. If the transaction is used which is yet committed before delete the row, then you can roll backed again.

The mysql query for delete paricular row:

                   DELETE FROM table_name WHERE column_name = condition

Consider the following example:
                     

                  table1 is name of this table. We are going to delete particular row in mysql using 'DELETE' mysql command. The mysql query as follows as:

                 DELETE FROM table1 WHERE id = 3

Now you'll get output like this:

                 You can delete whole rows in table. But the structure of table is present in mysql database.
The mysql query for delete table is:

                DELETE FROM table1    

                 Now the mysql table values are deleted from mysql database. Suppose you click table1 in mysql database, then you'll get output in database like this:

TRUNCATE in MySql: 

                  1. Remove rows from mysql table but the structure of table remains same.

                  2. The data cannot be roll backed if it is deleted by 'TRUNCATE' mysql command.

                  3. It can be used by only without 'WHERE' clause in mysql.

The mysql query for 'TRUNCATE' as follows as:

                  TRUNCATE TABLE table_name

DROP in MySql: 

                 1.Remove whole table from mysql database.

                 2. The data cannot be roll backed if it is dropped by 'DROP' mysql command.

The mysql query for 'DROP' as follows as:

                  DROP TABLE table_name 

Consider following example. The tables in databases like this:

Now drop a table 'table1' using 'DROP' mysql command.

              DROP TABLE table1 

Now you'll get output like this:

                Now the table 'table1' removed from mysql table list
Related Post:

distinct multiple columns in mysql
Order by multiple columns in mysql
Group by multiple columns in mysql
How to use order by together with group by in mysql
how to use order by before group by in mysql
Primary key in mysql

Delete column in mysql table

                       You created a table with number of columns. Sometimes you don't need to specific column. At the time, you can delete the column from mysql table. It is possible in mysql using by DROP mysql command. The following mysql query is used for this:

Mysql Query:

                         Alter table table_name drop column column_name

Consider the example:
 table1 is name of this table.

                          Suppose you've to delete the column 'Image'. The mysql query is:

                     Alter table table1 drop column Image

Now your table look like this:

     Now the column name 'Image' is deleted from your mysql table.

Deleting the multiple column at the time in mysql:

                      You can delete multiple column at a time in mysql. The mysql query as follow as:

Mysql Query:

                          Alter table table1

                                    drop column Game_name,

                                    drop column Image

                          Now your output look like this:

                          Now you will get your mysql table with only column 'No'.

Related Post:

Add column after specific field in mysql 
Change column name in mysql
Change column size in mysql table
Change column size in mysql table
Change column datatype in mysql
Change table name in mysql
Change column NOT NULL to NULL in mysql
Add primary key to existing column in mysql
how to add auto increment to existing column in mysql
How to remove auto increment from column in mysql