PHP, MySQL, Drupal, .htaccess, Robots.txt, Phponwebsites: drop
drop - phponwebsites.com
Showing posts with label drop. Show all posts

12 Feb 2014

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, 

6 Feb 2014

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:



remove auto increment from column in mysql using alter, drop and change mysql command
                        
                       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:

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

Related Post:

10 Jan 2014

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.

delete in mysql


The mysql query for delete paricular row:

                   DELETE FROM table_name WHERE column_name = condition

Consider the following example:
                     
Delete query in mysql

                  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:

delete particular row in mysql using delete command

                 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:

delete table in mysql


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


Truncate mysql command

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.


Drop table in mysql

The mysql query for 'DROP' as follows as:

                  DROP TABLE table_name 

Consider following example. The tables in databases like this:
Drop table in mysql database

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

              DROP TABLE table1 

Now you'll get output like this:
mysql drop

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

19 Dec 2013

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.

delete column in mysql table using DROP command


                          Suppose you've to delete the column 'Image'. The mysql query is:
                     Alter table table1 drop column Image

Now your table look like this:

drop column from mysql table

     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:

Delete multiple column at a time in mysql table

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

Related Post: