第二大题 1.SELECT m_name FROM movie WHERE m_star='吴京'; 2.SELECT m_name,m_type FROM filmSession JOIN movie ON filmSession.movie_id =movie.movie_id WHERE session_id=3; 3.SELECT m_name,startTime FROM member JOIN ticket ON member.'user_id'=ticket.'user_id' JOIN filmSession ON ticket. ‘session_id’=filmSession.’session_id’ JOIN movie ON filmSession.’movie_id’=movie. ‘movie_id’ WHERE u_name=’李勇’ ORDER BY startTime; 4.SELECT m_name,m_type,m_company FROM filmSession JOIN movie ON filmSession.movie_id=movie.movie_id WHERE startTime BETWEEN ‘2022-11-08’ AND ‘2022-11-10’; 5.SELECT u_name,u_tel FROM member JOIN ticket ON member.’user_id’=ticket.’user_id’ GROUP BY ticket. ‘user_id’ HAVING COUNT(*)>=3; 6.SELECT session_id,startTime FROM filmSession WHERE NOT EXISTS( SELECT * FROM member WHERE NOT EXIXTS( SELECT * FROM ticket WHERE ticket.’session_id’=filmSession.’session_id’ AND ticket.’user_id’=member.’user_id’ ) ); 7，CREATE VIEW user_film AS SELECT member.user_id,u_name,m_name,startTime FROM member JOIN ticket ON member.’user_id’=ticket. ‘user_id’ JOIN filmSession ON ticket.’session_id’=filmsession.’session_id’ JOIN movie ON filmSession.’movie_id’=movie.'movie_id';

8, CREATE USER ‘u1’@’localhost’ IDENTIFIED BY ‘123456’; GRANT SELECT ON movie TO ‘u1’@’localhost’;

第三大题 1CREATE TABLE filmReview( user_id INT, movie_id INT, review CHAR(50), PRIMARY KEY(user_id,movie_id), FOREIGN KEY (user_id) REFERENCES member(user_id), FOREIGN KEY (movie_id) REFERENCES movie(movie_id) );

2tianjiaziduan ALTER TABLE filmReview ADD COLUMN words INT;

3,,INSERT INTO filmReview VALUES(3,2,’非常热血，我爱我国’,8);

4,, gengxinshuju update filmReview set words=9 where user_id=3 and movie_id=2;

第四大题 1 DELIMITER CREATE PROCEDURE pro_select(IN username CHAR(10)) READS SQL DATA BEGIN SELECT *FROM member WHERE u_name LIKE CONCAT(‘%’,username,’%’); END

 
DELIMITER ;
CALL pro_select(‘立’);

2
DELIMITER $$
CREATE FUNCTION fun_sum(username CHAR(10)) RETURNS INT
READS SQL DATA
BEGIN
  DECLARE sum_pri INT;
  SELECT SUM(price) INTO sum_pri FROM MEMBER JOIN ticket ON member.user_id=ticket.user_id JOIN filmSession ON ticket.session_ id=filmSession.session_id 
  WHERE member.u_name=user_name;
  RETURN sum_pri;
END

DELIMITER : SELECT fun_sum('李勇);

3 DELIMITER CREATE TRIGGER tri_remain AFTER INSERT ON ticket FOR EACH ROW BEGIN UPDATE filmSession SET remain=remain-1 WHERE session_id=new.session_id; END ss DELIMITER ; INSERT INTO ticket VALUES(12,5,1,'5排6排');