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:

                         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:

     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:

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

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 remove auto increment from column in mysql
How to reset auto increment initial value in mysql

how to use order by before group by in mysql

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.

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

                    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:

                 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

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

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:

       
                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:

                            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:

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 remove auto increment from column in mysql
how to add auto increment to existing column in mysql

Rename table in mysql

                       You can change the column name in mysql. Can you change table name in mysql. Yes you can rename mysql table. The mysql query for rename table is:

                       RENAME TABLE old_table_name TO new_name

Consider the following example: The table list in mysql database 'new' is below.

                   
                              Now we are going to change the table name 'table1' to table4. The mysql query as follows as:

                       RENAME TABLE table1 TO table4

Now your table list should be like this:

                          Now the table name 'table1' is changed into 'table4' in mysql

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
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

Use order by together with group by in mysql

                       You can use both mysql command 'ORDER BY' and 'GROUP BY' separately. The mysql command 'ORDER BY'  is used to sort the column values and 'GROUP BY' is used to eliminate the duplicate values in column.
                        Can you use both 'OREDER BY' and 'GROUP BY' together in a mysql query?
Yes you can use both mysql command in a query.
                        Why you need to use both mysql command in a query?
If you want to eliminate duplicate values in column and also sort the column in a mysql table, then you've to use these two command in single mysql query. ie,

         SELECT * FROM table GROUP BY column_name ORDER BY column_name

You must use 'GROUP BY' before 'ORDER BY' in mysql. Because 'GROUP BY' get higher preference than 'ORDER BY' in mysql.

Select all values with one value is distinct in mysql table:  

                        You can use 'DISTINCT' mysql command for display unique values in only one column. But the situation, you need to select all columns but one column value is distinct in mysql. Then you've to use both 'ORDER BY' and 'GROUP BY' mysql command in a query. The 'ORDER BY' command should be placed after 'GROUP BY' in mysql query.

Consider the following table. table1 is name of this table.

                              Now we are going to get total count of unique values in column 'id' by 'GROUP BY' mysql command and also order by count in ascending order by 'ORDER BY' mysql command. The mysql query as follows as:

               SELECT id,  SUM(count) as count, date FROM table5 
                                  GROUP BY id ORDER BY count DESC

Now you'll get output like this:

                  You can see the output of the above mysql query which displays total count in descending order.

Related Post:

distinct in mysql
Order by in mysql
Group by in mysql
Difference between delete truncate drop in mysql
how to use order by before group by in mysql

Group by in mysql table

                       If your column in mysql table contains similar values, then you will use 'GROUP BY' mysql command to display unique values. It is used to eliminate the duplicate values in mysql table. It takes only  first row value from top to bottom in mysql table. The mysql query as follows as:

                       SELECT * FROM table_name GROUP BY field_name

Group by in Mysql:

                                      Consider the following table. table1 is name of this table.

                              Now we are going to get unique values in column 'id' by 'GROUP BY' mysql command like this:

                       SELECT * FROM table1 GROUP BY id 

Now you'll get output like this:

                           You can see the column 'id' values in mysql table 'table1' are unique. The id value 1 are present 1st, 2nd and 5th row. But it takes only the first row.

Group by multiple columns in mysql:

                           You can group the multiple columns in single mysql query.  The mysql query as follows as:

                       SELECT * FROM table1 GROUP BY id , count 

             Where,
                        - The column 'id' got the first preference to group.
                        - Then the column 'count' is grouped.
Your output like this:

                        You can see the mysql table 'table1' which is grouped by both id and count. But you can't the get unique values in both mysql columns.

Related Post:

distinct in mysql
Order by 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
Primary key in mysql

Order by in mysql table

                      If your column values in mysql table contains more rows, then you can't get the highest and lowest values. On that time, you can sort the column values ascending and descending by 'ORDER BY' mysql command with 'ASC' and 'DESC'. The mysql query for sort column values in mysql table as follwos as:
                       SELECT * FROM table_name ORDER BY field_name ASC( for ascending)
                       SELECT * FROM table_name ORDER BY field_name DESC( for descending)

Order by in Mysql:

                                      Consider the following table. table1 is name of this table.

                              Now we are going to sort column 'id' in ascending order. The mysql query for this:

                       SELECT * FROM table1 ORDER BY id ASC

Now you'll get output like this:

                           You can see the column 'id' values are sorting in ascending order.

Order by multiple columns in mysql:

                           You can sort the multiple columns in single mysql query. The mysql query as follows as:

                       SELECT * FROM table1 ORDER BY id ASC, date DESC 

             Where,
                        - The column 'id' got the first preference to sort
                        - Then the column 'date' is sort
Your output like this:

                        You can see the mysql table 'table1' which is sorted by id in ascending order and date in descending order.

Related Post:

distinct in mysql
Group by 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
Primary key in mysql

Distinct in mysql

                       The 'DISTINCT' mysql cmmand is used to eliminate duplicate values in a column. The mysql query as follows as:
                   
                      SELECT DISTINCT field_name FROM table_name

Distinct in Mysql:

                                     Consider the following table. table1 is name of this table.

                              Now we are going to get unique values in column 'id' by 'DISTINCT' mysql command like this:

                      SELECT DISTINCT id FROM table1

Now you'll get output like this:

                    You can see the table 'table1' which have unique values.

Distinct multiple column in Mysql:

                    You can use multiple column for 'DISTINCT' in a query. But you can't get answer as you want.

The mysql query as follows as:

                      SELECT DISTINCT id, count FROM table1

You'll get output like this:

                 You can see the table 'table1' which displays all values in column 'id' and 'count'.

Related Post:

order by in mysql
Group by 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 select distinct value in mysql
Primary key in mysql

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.

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

                          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:

     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:

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

Datatypes in mysql:

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 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

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.

                   

                       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:

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:

Now the order of table1 is changed like above output.

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
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

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.

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

                          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:

     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:

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

Related Post:

Add column after specific field in mysql table
Delete column in mysql table
Change column name in mysql
Change column datatype 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

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.

                          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:

     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:

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

Related Post:

Add column after specific field in mysql table
Delete column 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

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:

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:

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:

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:

Now you added three columns after column 'Image'.

Related Post:

Change column name in mysql
Change column size in mysql table
Delete column 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

Copy values from one database to another in mysql

                       All of you know, you can copy data within a table and between tables. But can you copy data between two database in mysql. Yes, you can done it in mysql. The following mysql query is used for do this.

Mysql Query:

                         INSERT INTO database2.table_name (column_name)

                              SELECT table_name.column_name FROM table_name

Copy data from one database to another in mysql:

                       Consider following example. You've two databases named with db1 and db2. Each database have a table. Table1 is presented in db1 and Table2 is present in db2.

The Table1 in db1 is look like this:

The Table2 in db2 look like this:

Now we copy data from database db1 to db2. The mysql query is:

                     
                     INSERT INTO db2.Table2(name,image)

                              SELECT Table1.name, Table2.name FROM Table1

Now you will get output as follow as:

                   Now you'll get values in Table2 as like as Table1 in db.

Copy data from one database to another with specific condition in mysql:

                  You can copy only specific values from one database to another in mysql using 'WHERE' condition in mysql. Consider a example: Now we are going to copy named with 'sample1' and 'sample2' from database db1. The mysql query is:

                     INSERT INTO db2.Table2(name,image)

                              SELECT Table1.name, Table2.name FROM Table1
                              WHERE Game_name!='sample3'

Now you'll get output as follow as:

Note:

          You can copy the values from one database to another using  this method only if two databases are in same server.

Related Post:

Copy values from one column to another within table in mysql
Copy values from one table to another in mysql

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.
                                               

                   
 Now you need to add another column with named as 'Author'. The mysql query is:
                     Alter table Table add column Author varchar(30)

                     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:

          

                             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:

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

Related Post:

Copy values from one table to another in same table using mysql
Copy values from one database to another in mysql

Copy values from one table to another in mysql

                       We can easily copy the values within a table. But can we copy the values from one table to another table?. Yes, you can done it in mysql. The mysql query is:

                       Insert into table2 (column_name1, column_name2) 
                               select column_name1, column_name2 from table1

Consider the example:
 Table1 is name of this table.

Table2 is name of this table.

Copy values from one table1 to table2 in mysql:

                         Now you have to add values from 'Table1' to 'Table2'. The following mysql query is used to do it.
                         Insert into Table2 (No,Game_name,Image)
                              select No,Game_Name, Image from Table1

The output of  'Table2' is:

Now you'll get values in 'Table2' which is same as 'Table1'.

Copy values from one table to another except some values in mysql:

                        If you want to some coulmn values, then you will add only required column name only in mysql query. Suppose you need only particular values. Then you use 'where' clause to get your answer. The mysql query as follow as:

                        

                        Insert into Table2 (No,Game_name,Image)

                              select No,Game_Name, Image from Table1  

                                where  Game_Name='sample1' and Game_Name='sample2'

Then the output look like this:

Now you will get values only named in sample1 and sample2.

Related Post:

Copy values from one column to another within table in mysql
Copy values from one database to another in mysql