SqlÓï¾äѧϰ±Ê¼Ç(2) ´´½¨Êý¾Ý±í

use RetalDB--±íʾÔÚÊý¾Ý¿âRetalDBÖнøÐеIJÙ×÷
go
if exists (select * from sysobjects where name='tb_user')
 drop table tb_user
go
--´´½¨¿Í»§±ítb_user
create table tb_user
(
 user_id int primary key,--Ö¸¶¨ÎªÖ÷¼üʱ£¬´ËÁÐĬÈÏΪ·Ç¿Õ£¬Ö¸¶¨¹ý¶à¸öÏÞÖÆÌõ¼þʱ²»ÓÓ'”¸ô¿ª
 user_name varchar(20) not null,
 user_point int not null,
)
--´´½¨Ó°µúÀàÐͱítb_MovieType
if exists (select *from sysobjects where name='tb_movie_type')
 drop table tb_movie_type
go
create table tb_movie_type
(
 type_id int primary key,--Ö¸¶¨Ö÷¼ü
 type_name varchar(20) not null,
 type_english_name varchar(20) not null
)
--´´½¨Ó°µú±í
if exists (select * from sysobjects where name='tb_movie')
 drop table tb_movie
go
create table tb_movie
(
 movie_id int primary key,
 movie_name varchar(20) not null,
 movie_price float,
 movie_type_id int not null,
 movie_detail varchar(255)
 --Ìí¼ÓÍâ¼üÔ¼Êø
 constraint FK_movie_id foreign key(movie_type_id) references tb_movie_type(type_id)--ÒýÓÃӰƬÀàÐͱíÖеÄtype_id×Ö¶Î
)
--´´½¨×âÁÞÐÅÏ¢±í
if exists (select * from sysobjects where name='tb_retal')
 drop table tb_retal
go
create table tb_retal
(
 id int identity(1,1) primary key,--ÐÅÏ¢±àºÅ
 user_id int not null,--Íâ¼ü£¬ÒýÓÿͻ§±íÖеÄÖ÷¼üuser_id
 movie_id int not null, --Íâ¼ü£¬ÒýÓÃӰƬ±íÖеÄmovie_id 
 rent_time datetime not null  default(getdate()),--×â³öʱ¼ä£¬ÒÔ²åÈëÊý¾ÝʱµÄʱ¼äΪĬÈÏÖµ
 return_time datetime,--¹é»¹Ê±¼ä
 rent_fee float default(0)--×â½ð
 
 constraint FK_user_id foreign key(user_id) references tb_user(user_id),
 constraint FK_movie_id foreign key(movie_id) references tb_movie(movie_id)
)
´´½¨ºÃµÄ±íÈçͼËùʾ£º£¨ºóÀ´ÎªÁ˱£Ö¤tb_retalÖÐÊý¾ÝµÄΨһÐÔ£¬½«user_id,movie_id,rent_timeÕâ3¸ö¼üÉèÖóÉÁËÁªºÏÖ÷¼ü£¬¼´Í¬Ò»¸ö¿Í»§²»¿ÉÄÜÔÚͬһʱ¼ä×âÁËͬһÕÅÓ°µú¶à´Î£©