浅谈select语句

---

创建三个表

create table _user (
            ID int CONSTRAINT pk_user primary key,
            Name varchar(10) 
            )
create table product (
            ID int CONSTRAINT pk_product primary key,
            Name varchar(10), 
            price int,
            comment varchar(10)
            )
create table subscribe  (
            ID int primary key,         
            _user_id int CONSTRAINT fk_subscribe_user FOREIGN KEY references _user(ID)
                            ON UPDATE CASCADE
                            ON DELETE CASCADE ,
            product_id int CONSTRAINT fk_subscribe_product FOREIGN KEY references product(ID)
                            ON UPDATE CASCADE
                            ON DELETE CASCADE ,
            _Time int 
            )
insert into _user
 select     1,'刘德华' union all
 select     2,'周润发' union all
 select     3,'张惠妹' ;

insert into _user
 select     1,'刘德华' union all
 select     2,'周润发' union all
 select     3,'张惠妹' ;

insert into product
 select     1,'首映专区',30,'包月' union all
 select     2,'看大片',20,'包月' union all
 select     3,'全能包',199,'包年' ;

insert into subscribe
 select     1,1,1,20155 union all
 select     2,1,2,20156 union all
 select     3,2,3,20158 ;

对应的答案

select u.name,p.name,s._time from
(_user as u right join subscribe as s on u.id = s._user_id) 
left join product as p on s.id = p.id;

select p.name,count(s.product_id)as 次数,
p.price*count(s.product_id) as preice
from subscribe as s right join product as p on s.product_id = p.id 
group by p.name,s.product_id,p.price;

select ID,Name from
(select u.ID,u.Name,s._user_id from  _user as u 
left join subscribe as s on u.ID =s._user_id)as A 
where  _user_id IS NULL;

delete from subscribe where _user_id = 1

---