MYSQL ALTER AND UPDATE

 CREATING A TABLE OF DISTRICTS

CREATE TABLE districts(

    cid INT NOT NULL AUTO_INCREMENT,

    district_name VARCHAR(50) NOT NULL,

    PRIMARY KEY (cid)

);

INSERTING THE VALUE TO TABLE OF DISTRICTS

INSERT INTO districts(district_name)

VALUES('Agra'),

('Delhi'),

('Bhopal'),

('Jaipur'),

('Noida'),

('lucknow'),

('ballia');

select * from districts 

CREATING A TABLE OF CITIZENS

CREATE TABLE citizens(

id INT NOT NULL,

name VARCHAR(50) NOT NULL,

percentage_of_population INT NOT NULL,

age INT NOT NULL,

gender VARCHAR(1) NOT NULL,

district_name INT NOT NULL,

PRIMARY KEY (id),

    foreign key (district_name) REFERENCES districts(cid)

    );

    

select * from citizens

 INSERTING THE VALUE IN THE TABLE CITIZENS

INSERT INTO citizens(id,name,percentage_of_population,age,gender,district_name)

VALUES

(1,"Ramesh Kumar","45","13","M",1),

(2,"Saroja Kumari","56","21","F",2),

(3,"sarsvari devi ","62","20","M",1),

(4,"Juhi kumari","47","18","F",3),

(5,"Anil yadav","74","22","M",1),

(6,"Joy shanker","64","21","M",2),

(7,"sunil yadav ","52","20","M",1);

select * from citizens

select * from districts

 ALTER START HERE

By the help of alter command, we can add the columns to the table.

ALTER TABLE citizens

ADD Email varchar(255);

ALTER TABLE citizens

MODIFY Email varchar(255)

AFTER age;

ALTER TABLE citizens

MODIFY Email INT(10);

ALTER TABLE citizens

ADD UNIQUE (Email);

ALTER TABLE citizens

CHANGE Email Email_id varchar(255);

ALTER TABLE citizens

DROP COLUMN Email_id;

select * from citizens

ALTER TABLE citizens

RENAME citizenss;

select * from citizenss

ALTER TABLE citizenss

RENAME citizens;

select * from citizens

ALTER TABLE courses

AUTO_INCREMENT = 4;

UPDATE START HERE

By the update command, we set the new value in row.

UPDATE citizens SET percentage_of_population  = "100" WHERE id = 1;

select * from citizens

UPDATE citizens SET gender ="m" WHERE id = 2;

select * from citizens

UPDATE citizens SET age = 18 WHERE id IN (2,3);

select * from citizens