数据库实验2

create table USER_(UID int primary key,
NAME char(14) not null,
SEX char(2),
BYEAR int,
CITY char(20));

 

create table LABEL_(LID int primary key,

LNAME char(20) not null);

create table MBLOG_(BID int primary key,
TITLE char(40)not null,
UID int ,
PYEAR int,
PMONTH int,
PDAY int,
CONT char(400),
foreign key(UID) references USER_(UID)
);

create table B_L(BID int,
LID int,
primary key(BID,LID),
foreign key(BID)references MBLOG_(BID),
foreign key(LID)references LABEL_(LID) );

 

create table FOLLOW(UID int ,
UIDFLED int,
primary key(UID,UIDFLED),
foreign key(UID) references USER_(UID),
foreign key(UIDFLED)references USER_(UID)
);

 

create table FRIENDS(UID int ,
FUID int,
primary key(UID,FUID),
foreign key(UID) references USER_(UID),
foreign key(FUID) references USER_(UID),

);

 

create table SUB(UID int ,
LID int,
primary key(UID,LID),
foreign key(UID) references USER_(UID),
foreign key(LID) references LABEL_(LID),
);

 

create table THUMB(UID int,
BID int,
primary key(UID,BID),
foreign key(UID) references USER_(UID),
foreign key(BID) references MBLOG_(BID),

);

create table TOPDAY(TYEAR int,
TMONTH int,
TDAY int,
BID int,
TNO int,
primary key(TYEAR,TMONTH,TDAY,TNO),
foreign key(BID) references MBLOG_(BID),
);

 往表中插入数据

use WeiBo_DataBase;
go
insert into MBLOG_(BID,TITLE,UID,PYEAR,PMONTH,PDAY,CONT)values(500,'美',3,2019,5,5,'cxk');
insert into MBLOG_ values(500,'CXK',42,2019,5,5,'鸡你太美');

delete from MBLOG_ where BID=500;  //把刚刚插入的哪一行给删除了

select * from MBLOG_ where BID=500; 

 

update MBLOG_ set TITLE ='律师函'where BID=500;

 use WeiBo_DataBase;

use WeiBo_DataBase;
go
CREATE TRIGGER tgr_THUMB_insteadOf3
on THUMB
instead of insert
as
declare @UID int,@BID int;
select @UID = UID,@BID =BID FROM inserted X
if(@UID IN (SELECT Y.UID FROM MBLOG_ Y ,THUMB X where Y.BID=X.BID AND Y.UID = X.UID))
begin
　　　　PRINT '博文作者不能点赞自己的博文！'
　　　　PRINT '插入/更新 点赞关系失败！'
　　　　ROLLBACK
end

//创建触发器

//检验触发器

insert into THUMB values(36,3);

 

 

    DROP TRIGGER tgr_THUMB_insteadOf ; //删除触发器

 

Select * from sysobjects where xtype='TR' //显示触发器的具体信息

 

//1，张三

　　

先插入一点数据

insert into FOLLOW VALUES (1,2);
insert into FOLLOW VALUES (1,3);
insert into FOLLOW VALUES (1,4);
insert into FOLLOW VALUES (1,17);

insert into FOLLOW VALUES (1,21);
insert into FOLLOW VALUES (1,23);
insert into FOLLOW VALUES (1,44);

update USER_
SET BYEAR=2007
where UID=17;
update USER_
SET BYEAR=2007
where UID=23;

USE WeiBo_DataBase;
GO
select BID,TITLE,NAME from USER_,MBLOG_ x where USER_.UID = x.UID and not exists /* 2 */
    (select * from thumb y where x.BID=y.BID) order by title; 

 

 

USE WeiBo_DataBase;
go
select * from TOPDAY x where x.BID in(
                                    select z.BID from MBLOG_ z
                                    where z.UID in(
                                    select y.UID from USER_ y
                                    where     y.BYEAR>2000 AND y.CITY='武汉'
                                    ));

 

 

USE WeiBo_DataBase;
go
select UID 
from USER_ U
where  NOT EXISTS
(
SELECT *
FROM SUB X
WHERE NOT EXISTS
(
SELECT * 
FROM LABEL_ Y
WHERE U.UID=X.UID AND Y.LID=Y.LID
));

 

 5.

USE WeiBo_DataBase;
go
SELECT UID,BYEAR,CITY 
FROM USER_ X
WHERE  X.BYEAR<1970 OR X.BYEAR>2010;

//一个判定条件

USE WeiBo_DataBase;
go
SELECT UID,BYEAR,CITY
FROM USER_ X
WHERE X.BYEAR NOT BETWEEN 1970 AND 2010 ;

 

6.

USE WeiBo_DataBase;
go
select CITY,COUNT(*)
from USER_
group by CITY;

 

 7.

USE WeiBo_DataBase;
go
select CITY,BYEAR,COUNT(CITY) num 
FROM USER_
GROUP BY CITY,BYEAR
order by CITY, num desc;

8）查找被点赞数超过10的博文ID号；

USE WeiBo_DataBase;
go
select BID ,COUNT(UID) TIMES
FROM THUMB
GROUP BY BID having COUNT(UID)>10;

 

 9.查找被2000年后出生的用户点赞数超过10的博文ID号；

USE WeiBo_DataBase;
GO
select X.BID ,count(Y.BID)
from MBLOG_ X,THUMB Y,USER_ Z

group by X.BID having Z.BYEAR>2000 AND count(Y.BID)>10 AND Z.UID=X.UID;

10

11

12）查找标题中包含了“最多地铁站”和“_华中科技大学”两个词的博文基本信息；

USE WeiBo_DataBase;
go
select * from MBLOG_
where TITLE like '最多地铁站%'
union
select * from MBLOG_
where TITLE like '华中科技大学';

13 查找所有相互关注的用户对的两个ID号，要求不能使用嵌套查询

USE WeiBo_DataBase;
go
select X.UID,y.UID from FOLLOW X,FOLLOW Y
where  X.UIDFLED = Y.UID and X.UID = Y.UIDFLED and X.UID<Y.UID;

14 查找好友圈包含了5号用户好友圈的用户ID；

use WEIBO;
go
select X.UID 
from FRIENDS X
where X.UID=5 and not exists (
select *
from FRIENDS Y
where not exists (
select * 
from  FRIENDS Z
where X.FUID = Z.FUID and Y.UID = Z.UID
));

 

15）查找2019年4月20日每一篇头条博文的ID号、标题以及该博文的每一个分类ID，要求即使该博文没有任何分类ID也要输出其ID号、标题；

USE WEIBO;
GO
SELECT X.BID,X.TITLE,Y.LID 
FROM MBLOG_ X ,B_L Y
WHERE PYEAR=2019 AND PMONTH = 4 AND PDAY =20 AND X.BID=Y.BID;

 

16）查找至少有3名共同好友的所有用户对的两个ID号。

 

USE WEIBO;
GO
select X.UID,Y.UID,count(*)'共同好友'
FROM FRIENDS X, FRIENDS Y
WHERE X.FUID=Y.FUID AND X.UID<Y.UID
group by X.UID,Y.UID
having count(*)>=3;

 

 

 

17）创建视图：查阅DBMS内部函数，创建一个显示当日热度排名前十的微博信息的视图，其中的属性包括：博文ID、博文标题、发表者ID、发表者姓名、被点赞数。

USE WEIBO;
go
create view IS_DBMS
as 
    select  x.BID,x.TITLE,x.UID,z.NAME,count(a.BID) 被点赞数  from MBLOG_ x,TOPDAY y, USER_ z,THUMB a where x.BID=y.BID and x.uid = z.UID and a.BID = x.bid 
    and y.TDAY = datepart(day,getdate()) and y.TMONTH =datepart(MONTH,getdate()) and y.TYEAR = datepart(YEAR,getdate())  group by  x.BID,x.TITLE,x.UID,z.NAME ;

 

 

use WEIBO;
go
select count(X.BID),count(Z.BID)
from TOPDAY X,USER Y,THUMB Z,MBLOG_ A
where Z.BID = X.BID and A.BID=X.BID and Y.