MySQL VIEW

 How to work View In MySql:

INNER Join Syntax:

Create View Syntax:

Code:

CREATE TABLE personal(

id INT NOT NULL,

name VARCHAR(50) NOT NULL,

    percentage INT NOT NULL,

age INT NOT NULL,

gender VARCHAR(1) NOT NULL,

city INT NOT NULL,

    courses INT NOT NULL,

    PRIMARY KEY (id),

FOREIGN KEY (city) REFERENCES City (cid),

    FOREIGN KEY (courses) REFERENCES Courses (course_id)  

);

INSERT INTO personal(id,name,percentage,age,gender,city,courses)

VALUES

(1,"Aqib","45","13","M",1,1),

(2,"Khadija","56","21","F",2,2),

(3,"Salman Khan","62","20","M",1,1),

(4,"Aqeela","47","18","F",3,1),

(5,"Saqib","74","22","M",1,3),

(6,"Abraham","64","21","M",2,2),

(7,"Shahid","52","20","M",1,3);

VIEW Examples

/* Create view */
CREATE VIEW studentdata
AS
SELECT id,name,course_name FROM students s
INNER JOIN courses c ON s.courses = c.course_id;


/* show view */
SELECT * FROM studentdata;


/* Alter view */
ALTER VIEW studentdata
AS
SELECT id,name,course_name,cityname FROM students s
INNER JOIN courses c ON s.courses = c.course_id
INNER JOIN city ci ON s.city = ci.cid;


/* Another way to alter */
CREATE OR REPLACE VIEW studentdata
AS
SELECT id,name,course_name,cityname FROM students s
INNER JOIN courses c ON s.courses = c.course_id
INNER JOIN city ci ON s.city = ci.cid;


/* rename view */
RENAME TABLE studentdata
TO studentcourse;


/* show view */
SELECT * FROM studentcourse;


/* Delete/Drop view */
DROP VIEW studentcourse;