mysql建立简单豆瓣影人数据库

CREATE TABLE Artist (

       id INT auto_increment PRIMARY KEY,

       theNAME VARCHAR ( 255 ) NOT NULL,

       birthday date NOT NULL,

       sex VARCHAR ( 255 ) NOT NULL,

       IMDBnumber VARCHAR ( 255 ) NOT NULL,

       introduction VARCHAR ( 100 ) NOT NULL

);

INSERT INTO Artist ( theNAME, birthday, sex, IMDBnumber, introduction )

VALUES

       ( 'Julie Andrews', '1935-10-1', 'female', 'nm0000267', '1965年获得奥斯卡影后' );

INSERT INTO Artist ( theNAME, birthday, sex, IMDBnumber, introduction )

VALUES

       ( '巩俐', '1965-12-31', 'female', 'nm0000084', '26届金像奖最佳女主' );

INSERT INTO Artist ( theNAME, birthday, sex, IMDBnumber, introduction )

VALUES

       ( 'Isablelle Huppert', '1953-03-16', 'female', 'nm0001376', '第42届法国凯撒电影奖最佳女主角' );

INSERT INTO Artist ( theNAME, birthday, sex, IMDBnumber, introduction )

VALUES

       ( 'Colin Firth', '1960-09-10', 'male', 'nm0000147', '凭借《国王的演讲》获得奥斯卡影帝' );

INSERT INTO Artist ( theNAME, birthday, sex, IMDBnumber, introduction )

VALUES

       ( 'Audrey Hepburn', '1929-05-04', 'female', 'nm0000030', '凭借《罗马假日》获得第26届奥斯卡影后' );

CREATE TABLE Movie (

       id INT auto_increment PRIMARY KEY,

       theNAME VARCHAR ( 255 ) NOT NULL,

       releasedate date NOT NULL,

       duration VARCHAR ( 255 ) NOT NULL,

       LANGUAGE VARCHAR ( 255 ) NOT NULL,

       introduction VARCHAR ( 100 ) NOT NULL

);

INSERT INTO Movie ( theNAME, releasedate, duration, LANGUAGE, introduction )

VALUES

       ( '欢乐满人间','1964-08-27', '139min', 'English', '豆瓣评分8.2，喜剧、奇幻、歌舞片，是小说改编电影' );

INSERT INTO Movie ( theNAME, releasedate, duration, LANGUAGE, introduction )

VALUES

       ( '霸王别姬','1993-07-26', '171min', '中文', '豆瓣评分9.6，张国荣、张丰毅、巩俐主演' );

INSERT INTO Movie (theNAME, releasedate, duration, LANGUAGE, introduction )

VALUES

       ( '她', '2016-05-21', '130min', '法语', '豆瓣评分7.7，戛纳电影节首映法语惊悚片' );

INSERT INTO Movie ( theNAME, releasedate, duration, LANGUAGE, introduction )

VALUES

       ( '国王的演讲', '2012-02-24', '118min', '英文', '豆瓣评分8.4，剧情、传记、历史片' );

INSERT INTO Movie ( theNAME, releasedate, duration, LANGUAGE, introduction )

VALUES

       ( '罗马假日', '1953-08-20', '118min', '英文', '豆瓣评分9.0，奥黛丽赫本凭借《罗马假日》获得第26届奥斯卡影后' );

CREATE TABLE Participation (

       id INT auto_increment PRIMARY KEY,

       artistid INT ( 255 ) NOT NULL,

       movieid INT ( 255 ) NOT NULL,

       rolename VARCHAR ( 255 ) NOT NULL,

       FOREIGN KEY(artistid) REFERENCES Artist (id),

       FOREIGN KEY(Movieid) REFERENCES Movie (id)

);

INSERT INTO Participation ( artistid, movieid, rolename )

VALUES

       ( '1', '1', 'mary poppins' );

INSERT INTO Participation ( artistid, movieid, rolename )

VALUES

       ( '2', '2', '菊仙' );

INSERT INTO Participation ( artistid, movieid, rolename )

VALUES

       ( '3', '3', 'Michelle' );

INSERT INTO Participation ( artistid, movieid, rolename )

VALUES

       ( '4', '4', 'King George' );

INSERT INTO Participation ( artistid, movieid, rolename )

VALUES

       ( '5', '5', 'Princess Ann' );

      

ALTER TABLE Movie ADD COLUMN Rating VARCHAR ( 200 );

UPDATE Movie SET Rating = 8.2 WHERE theNAME = "欢乐满人间";

UPDATE Movie SET Rating = 9.6 WHERE theNAME = '霸王别姬';

UPDATE Movie SET Rating = 7.7 WHERE theNAME = '她';

UPDATE Movie SET Rating = 8.4 WHERE theNAME = '国王的演讲';

UPDATE Movie SET Rating = 9.0 WHERE theNAME = '罗马假日';