Modifying and Updating Tables in MySQL

Modifying :

Definition: The modify command is used when we have to resize a column in a table. By using this command we have to apply some changes on the result set field. This command allows more or less characters than before.

Syntax :alter table modify [column name] VARCHAR(5) ;

Also Known As: It also known as the Modify Column, Resize Column into a table in a database.

Examples:  alter table people modify name VARCHAR(35);





Add a new column to db.

alter table [table name] add column [new column name] varchar (20);

Delete a column.

alter table [table name] drop column [column name];

Change column name:

alter table [table name] change [old column name] [new column name] varchar (50);

Make a unique column so you get no dupes.

alter table [table name] add unique ([column name]);

Make a column bigger.

alter table [table name] modify [column name] VARCHAR(4);

Delete unique from table.

alter table [table name] drop index [column name];

Updating :

It is necessary to change the data in our database, so these commands are helpful for update a table.

Example : UPDATE products SET product_id = 7, product_name='headphone', c_id=6,price=1201;




To update info already in a table.
UPDATE [table name] SET Select_priv = 'Y',Insert_priv = 'Y',Update_priv = 'Y' where [field name] = 'user';

Delete a row(s) from a table.
DELETE from [table name] where [field name] = 'something';

Update database permissions/privilages.
FLUSH PRIVILEGES;

Load a CSV file into a table.
LOAD DATA INFILE '/tmp/filename.csv' replace INTO TABLE [table name] FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (field1,field2,field3);