1) MySQL syntax for inserting a new row at middle in n-number of rows in MySql Table?

mysql sintax for insert a new row in middle rows or wherever we want without updating the existing row, but automatically increment the primary key (id)?

# Suppose you have a Table(i.e. Table_Name) , Which has 2-Column and 100-Rows or More all are filled with specific data. Now you want to insert a new row at the Position of 5. To create new table it takes too much time. So just follow these steps:-

START TRANSACTION;

UPDATE Table_Name SET id=id+1 where id >= n ORDER BY id DESC;

INSERT INTO Table_Name (id,name) VALUES (5,"Gopal Sharma");

COMMIT;

2) How to reset AUTO_INCREMENT in MySql ?

If you delete any row from MySql Table, and now your Table is showing like:-
Let you have a table having 96 rows in it, And you delete the row 96, Now new entry will be insert at 97 position. So in this case you are assigning your Table as AUTO_INCREMENT(Also if you assigned your First Column as Primary Key).
1,2.....93,94,95,97 // 97 is new entry.

So, Now change 97 => 96 (either from PhpMyAdmin or By Manually) and apply this query in your MySql:-

ALTER TABLE TABLE_NAME AUTO_INCREMENT = 1;

Now, if new entry is inserted then it will start from 97.

1,2,3........95,96,97,98 and so on.

Click Here

3) Apply "Order by" query on Perticular Row into MySql. So that Data will fetch accordingly.

If you have 3-columns and 50-rows and having First Column as "ID" (1,2,3..,23,25,24....48,49,50).

*23,25,24 :- These three Rows are disorderd. So to Place it into Orderly You apply this Query:-

SELECT * FROM TABLE_NAME ORDER BY ID;

After apply this query the resultant output will be in Order Manner.

23,24,25

4) How can you delete any column from mysql Tables ?

ALTER TABLE TABLE_NAME DROP COLUMN_NAME;

*Note:- If you want to delete multiple rows then use comma-operator as showing in example below:-

ALTER TABLE TABLE_NAME DROP COLUMN_NAME1, COLUMN_NAME2, COLUMN_NAME3, ...and so on.;

5) How to add columns at a specific position in existing table.

If you created many columns(around 70-80) and forget to one inbetween the 24-25-26. Then if you create and try to add that column, it will be placed as the last column of table table.

3.1) Normal Case:-

ALTER TABLE TABLE_NAME ADD COLUMN XYZ VARCHAR(30);

But you can give the exact location for you column by use

FIRST, AFTER COLUMN_NAME.

3.2) By use First :-

ALTER TABLE TABLE_NAME ADD COLUMN XYZ VARCHAR(30) FIRST;

3.3) By use After Column_name :-

ALTER TABLE TABLE_NAME ADD COLUMN XYZ VARCHAR(30) AFTER

COLUMN_NAME;

Warning

This conversion may result in alteration of data. For example, if you shorten a string column, values may be truncated. To prevent the operation from succeeding if conversions to the new data type would result in loss of data, enable strict SQL mode before using ALTER TABLE (see Section 6.1.7, “Server SQL Modes”).

6) Place an existing column at first position in mysql.

You can also place existing column at the first position in MySql.

ALTER TABLE TABLE_NAME MODIFY COLUMN XYZ VARCHAR(30) FIRST.

7)How to add AUTO_INCREMENT to an existing column.

ALTER TABLE TABLE_NAME MODIFY COLUMN_NAME INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT.

*Note:- That the auto_increment column must be part of the PRIMARY KEY or a UNIQUE KEY (and in almost 100% of the cases, it should be the only column that makes up the PRIMARY KEY)

8) ALTER TABLE - Rename a column

ALTER TABLE TABLE_NAME CHANGE COLUMN COLUMN_NAME_OLD COLUMN_NAME_NEW VARCHAR(30) NOT NULL;

*Note:- Varchar(30) i.e. datatype name is the same as previous or it can be change into others i.e. int, bigint, char etc..

9) How can you set Password for MySql Localhost through MySql Console.

SET PASSWORD FOR root@localhost=PASSWORD('Awesome123');

Click on Enter and write next command :-