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

17 Feb 2014

Reset auto increment initial value in mysql

                     Auto_ increment is used to increase by 1 while insert a new data to mysql table. The auto increment column must be defined as key. By default auto_increment is start with 1. Suppose you want to auto_increment starts with 'N' values. Then you can change the auto_increment initial value in mysql. IT is possible. First you create table with auto_increment. Then use following mysql query :

                   Alter table table_name AUTO_INCREMENT=N

Where, N can be any numbers like 100,1000,10000...


Create table with auto_increment in mysql:


                      You can add auto_increment while creating new table in mysql. The mysql query is:

                       CREATE TABLE table1 
                               (No int(5) NOT NULL AUTO_INCREMENT PRIMARY KEY, 
                                 Game_name varchar(15), Image varchar(15)) 

Now the table is created with auto increment. If you add any values, then auto_increment starts with 1 in mysql. Consider following example. You add 3 values to mysql table. ie,

                   insert into table1 values('','sample1','sample1.jpg')
                   insert into table1 values('','sample3','sample2.jpg')
                   insert into table1 values('','sample3','sample3.jpg')

Now your table look like this:


reset auto increment initial value in mysql



Change auto_increment initial value in mysql:    

                 
                                 You change auto_increment initial value to 100. The mysql query is:

                   Alter table table1 AUTO_INCREMENT=100

Now you insert data to your mysql table. Then it look like this:


change auto increment initial value in mysql

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:

26 Jan 2014

Add auto_increment to existing column in mysql

                     Auto_ increment is used to increase by 1 while insert a new data to mysql table. The auto increment column must be defined as key.


Create table with auto_increment in mysql:


                      You can add auto_increment while creating new table. The mysql query is:

                       CREATE TABLE table1 
                               (No int(5) NOT NULL AUTO_INCREMENT PRIMARY KEY, 
                                 Game_name varchar(15), Image varchar(15)) 
Now the table is created with auto increment.


Add auto_increment to existing column in mysql:


                      You created a table in which the column with specific definitions in mysql. You created table column without auto increment. Then how can you add it to existing column in mysql.  The following mysql query is used to add auto increment to existing column in mysql.
Mysql Query:
                         Alter table table_name modify column_name datatype(length) AUTO_INCREMENT PRIMARY KEY


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


Add auto increment to existing column in mysql


                         Now we are going to add auto increment to column 'No' in mysql table 'table1'.  The mysql query is:
                     Alter table table1 modify No int(5) AUTO_INCREMENT PRIMARY KEY

Now your structure look like this:


Add auto increment to column in mysql


     Now you can see the column name 'No' is auto increment. Suppose  your mysql query like below:

     Alter table table1 modify No int(5) AUTO_INCREMENT

Then you'll get error like this:


mysql error while adding auto increment in mysql


                                 So you should be add 'AUTO_INCREMENT' with 'PRIMARY KEY' in mysql table.

Related Post:

23 Jan 2014

Add primary key to existing column in mysql

                      Primary key is used to avoid duplicate entries in mysql table. A mysql table can have only one primary key.


Create table with primary key in mysql:

                                                The mysql query for create table with primary key is:

                       CREATE TABLE table_name(column_name datatype(length), 
                             PRIMARY KEY(column_name))

For example,
                       CREATE TABLE table1(No int(5) datatype(length), ,Game_name varchar(15),                                     Image varchar(15), PRIMARY KEY(No))

Now the table column 'No' is primary key in this table.

How to add primary key to existing column in mysql:

                         
                              You can add primary while creating new table. But can you add primary key to existing column in mysql? Yes, you can add primary key to existing column in mysql. The mysql query is:
                     
                       Alter table table_name add PRIMARY KEY(column_name)


Consider the example:
 table1 is name of this table.

changing column size in mysql at phponwebsites

When you click the structure of table in your database, it is look like this:


add primary key in mysql


                    There is no index defined.  Suppose you've to add primary key to column 'Game_name'. On that time you can add the primary key. The mysql query is:

                     Alter table table1 add PRIMARY KEY(Game_name)

Now you click the structure of table in mysql database, the index is defined as below:

add primary key existing column in mysql

                 Now your Game_name column  is changed from NULL into NOT NULL with primary key. Because the primary key should not be null. Only one primary key in a mysql table. A mysql table haven't more than one primary key.

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

19 Jan 2014

Change column with NOT NULL to NULL in mysql

                       You created a table in which the column with 'NOT NULL'. Suppose you want to change mysql column definition from 'NOT NULL' to 'NULL'. Then how can you change it in mysql. The following mysql query is used to change column from 'NOT NULL' to 'NULL' in mysql.
Mysql Query:
                         Alter table table_name 
                            change column_name column_name datatype(length) definition


Change column NOT NULL to NULL in mysql:


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


change column definition in mysql

       
                Where, the column name 'No' is NOT NULL. You can see the Null column value is No in above mysql table at first row.  Now we are going to change column 'NOT NULL' to 'NULL' in mysql table 'table1'.  The mysql query is:
                 
                           Alter table table1 change No No int(5) NULL

Now your structure look like this:


change column NOT NULL to NULL in mysql


                            Now you can see the column name 'No' is NULL.


Change column NULL to NOT NULL in mysql:


                                         Similarly you can change the column definition from NULL to NOT NULL. The mysql query as follows as:

                                            Alter table table1 change No No int(5) NOT NULL

                        Now the column 'No' changed NULL to NOT NULL.

Related Post:

29 Dec 2013

Change column datatype in mysql

                       You created a table with column in specific datatype. Sometimes you need to change datatype of column. At the time, you can modify the datatype of field. It is possible in mysql. The following mysql query is used for this:
Mysql Query:
                         Alter table table_name modify column_name datatype(length)


Consider the example:
 table1 is name of this table.

changing column size in mysql at phponwebsites

When you click the structure of table in your database, it is look like this:

Structure of table in mysql

                          Suppose you've to change datatype of column 'Game_name'. On that time you can change the field type. The mysql query is:

                     Alter table table1 modify Game_name char(15)

Now your structure look like this:

change column field in mysql

     Now your Game_name column type is changed from varchar into char.


Changing the multiple column datatype at the time in mysql:


                      You can change multiple column datatype at a time in mysql. The mysql query as follow as:
Mysql Query:
                          Alter table table1
                                    modify No varchar(5),
                                    modify Game_name char(15),
                                    modify Image char(15)

                          Now your output look like this:

change multiple column datatype at a time in mysql

                          Now you will get output column 'No' with varchar, 'Game_name' with char and 'Image' with char in your mysql table.

Datatypes in mysql:

datatypes in mysql

27 Dec 2013

Move columns in mysql table

                       You can change the column name and change the column size in mysql. But can you rearrange the column order in mysql table. Yes, you can rearrange it in mysql. It can be done by 'ALTER', 'CHANGE' and 'AFTER' mysql command. The mysql query for move the column in mysql table is:

                   " ALTER TABLE table_name CHANGE column_name column_name datatype(length) AFTER column_name"

                       You can also done by 'MODIFY' mysql command. The mysql query as follows as:

                   " ALTER TABLE table_name MODIFY column_name datatype(length) AFTER column_name"

Consider the following example: table1 is the name of table.
                   
rearrange columns in mysql

                       Now the column 'name' is moved after column 'img'. The mysql query as follows as:

                   " ALTER TABLE table1 CHANGE name name varchar(30) AFTER img"

Now you'll get output like this:

move column using MODIFY mysql command

The column name 'name' is placed after 'img' in mysql table 'table1'.


Move  multiple columns at the time in mysql:


                             You can move multiple columns at the time in mysql. The column name order id, name ,img in table1 is changed into img, name ,id. The mysql query as follows as:

                         "ALTER TABLE table1 CHANGE id id int(5) AFTER img,
                                         CHANGE name name varchar(30) AFTER img"

Now you'll get output like this:

move multiple columns at a time in mysql using CHANGE command

25 Dec 2013

Changing the column size in mysql

                       You created a table with specific size of column. Sometimes you need to increase the size of column, if the size is not enough. At the time, you can modify the size of field. It is possible in mysql. The following mysql query is used for this:
Mysql Query:
                         Alter table table_name modify column_name datatype(length)


Consider the example:
 table1 is name of this table.

changing column size in mysql at phponwebsites



When you click the structure of table in your database. It is look like this:

srtucture of column in mysql


                          Suppose you've to add more characters to column Game_name. On that time you can change the field size. The mysql query is:
                     Alter table table1 modify Game_name varchar(40)  

Now your structure look like this:

change column size in mysql table

     Now your Game_name column size is changed into 40.


Changing the multiple column size at the time in mysql:


                      You can change multiple column size at a time in mysql. The mysql query as follow as:
Mysql Query:
                          Alter table table1
                                    modify Game_name varchar(50),
                                    modify Image varchar(40)

                          Now your output look like this:

changing multiple column size at a time in Mysql at phponwebsites

                          Now you will get output as Game_name with 50 size and Image with 40 size.

Related Post:

22 Dec 2013

Changing column name in mysql table

                       You created a table with specific column name. Sometimes you need to change the column name. At the time, you can change the field name. It is possible in mysql using by CHANGE mysql command. The following mysql query is used for this:
Mysql Query:
                         Alter table table_name change old_column_name  new_column_name datatype(length)


Consider the example:
 table1 is name of this table.

changing column name in mysql at phponwebsites


                          Suppose you've to change the column name. The mysql query is:
                     Alter table table1 change Game_name  name varchar(40)

Now your table look like this:

change column name using CHANGE mysql command

     Now the column name 'Game_name' is changed into 'name'.


Changing the multiple column name at the time in mysql:


                      You can change multiple column name at a time in mysql. The mysql query as follow as:
Mysql Query:
                          Alter table table1
                                    change No id int(5),
                                    modify Image img varchar(40)

                          Now your output look like this:

change multiple column name at the time in mysql at phponwebsites

                          Now you will get output as No to id and Image to img.

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:

17 Dec 2013

Add column after specific field in mysql

                       When you try to add add new column to your table, by default the column should be added to end of your table. But you can add it wherever you want in a table. It is possible in mysql.
Myql Query:
                     alter table tablename add column columnname datatype(length) AFTER specific field name

Add column after particular field in mysql:

               
                    Consider a example. Table1 is name of table. Normally the table look this this:

add column Mysql at phponwebsites


Now you are going to add column after specific column. The mysql query is:
     alter table Table1 add column Game_Tag varchar(30)  AFTER Game_Name

The output look like this:


add column after specific field in Mysql at phponwebsites


Now you will get column 'Game_Tag' is after 'Game_Name'.


Add column at first in mysql:


              Now you are going to add column before specific field. The mysql query is:
    alter table Table1 add column Game_Tag varchar(30) FIRST

The output look like this:

add column first in Mysql at phponwebsites

Now you'll get output as column 'Game_Tag' is present first of your table.


Add multiple columns after specific field in mysql:


               You can add multiple columns after particular column in mysql. The mysql query is:
     alter table Table1
        add column Game_Tag varchar(30),
        add column count int(10),
        add column category varchar(30)
              AFTER Image
The output look like this:


add multiple columns after specific field in Mysql at phponwebsites

13 Dec 2013

Copy values from one column to another within table in mysql

                      Mostly we had copied only particular values from one table to another. Similarly we had copied only particular values within a table. Now the question is rise. That is, can copy the whole column values to another column in same table? Is it possible in mysql?. Yes it is possible in mysql. We can copy the all values in one column to another within a table.
                     Consider a following example:
                                   Table1 is a table name.
                                               
Mysql at phponwebsites
                   
 Now you need to add another column with named as 'Author'. The mysql query is:
                     Alter table Table add column Author varchar(30)

add column in Mysql at phponwebsites

                     You need to copy the Game_Name column values to Author column. The mysql query as follows as:
                              
                               Update tablename set column2=column1

That means, Update Table1 set Author=Game_Name.

Now You will get following as a output:

copy values from one column to another in same table Mysql at phponwebsites

          
                             Now you will get the output both the Game_name and Author column values are same.


Copy values from one column to another except some values within table:


                           Suppose you have to copy all values in a column except some value to another column within a table. It can be done by following mysql query:

                           Update Table1 set Game_Name=Author where Game_Name!='sample1'

Your output look like this:

copy only selected values from one column to another in Mysql at phponwebsites

Now you'll get all values from one column to another except some value.

Related Post: