ulvis.paste.net

Paste Search Dynamic
Recent pastes
users
  1. --1
  2. select USERS.UIDD,USERS.NAME,USERS.SEX,USERS.BYEAR,USERS.CITY
  3. from USERS,FOLLOW
  4. where
  5.         FOLLOW.UIDFLED = USERS.UIDD and
  6.         FOLLOW.UIDD in(
  7.                         select USERS.UIDD
  8.                         from USERS
  9.                         where USERS.NAME = '张三'
  10.                 )
  11. order by USERS.BYEAR desc,USERS.UIDD asc;
  12.  
  13. --2
  14. select MBLOG.BID,MBLOG.TITLE,USERS.NAME
  15. from USERS,MBLOG
  16. where MBLOG.UIDD = USERS.UIDD and
  17.           MBLOG.BID not in(
  18.                         select THUMB.BID
  19.                         from THUMB
  20.  
  21.                 )
  22. order by MBLOG.TITLE;
  23.  
  24. --3
  25. select distinct MBLOG.BID
  26. from USERS,MBLOG,TOPDAY
  27. where TOPDAY.BID = MBLOG.BID and MBLOG.UIDD = USERS.UIDD and
  28.                 USERS.BYEAR>2000 and USERS.CITY = '武汉市';
  29.  
  30. --4
  31. select UIDD
  32. from USERS
  33. where not exists
  34.         (
  35.                 select *
  36.                 from LABEL
  37.                 where not exists(
  38.                                 select *
  39.                                 from SUB
  40.                                 where USERS.UIDD=UIDD and LID = LABEL.LID
  41.                         )
  42.                 )
  43.  
  44. --5
  45. select UIDD,BYEAR,CITY
  46. from USERS
  47. where BYEAR not between 1970 and 2010
  48.  
  49. --6
  50. select CITY,COUNT(CITY) as COUNTT
  51. from USERS
  52. group by CITY
  53.  
  54. --7
  55. select CITY,BYEAR,COUNT(UIDD) as COU
  56. from USERS
  57. group by BYEAR,CITY
  58. order by CITY asc , COU desc
  59.  
  60. --8
  61. select BID
  62. from THUMB
  63. group by BID
  64. having COUNT(*)>10
  65.  
  66. --9
  67. select  THUMB.BID
  68. from THUMB,MBLOG
  69. where THUMB.BID=MBLOG.BID and THUMB.UIDD in(
  70.         select UIDD
  71.         from USERS
  72.         where USERS.BYEAR>2000
  73. )
  74. group by THUMB.BID
  75. having COUNT(THUMB.UIDD)>10
  76.  
  77. --10
  78. select BID,COUNT(*) as COU
  79. from TOPDAY
  80. where BID in (
  81.         select  THUMB.BID
  82.         from THUMB,MBLOG
  83.         where THUMB.BID=MBLOG.BID and THUMB.UIDD in(
  84.         select UIDD
  85.         from USERS
  86.         where USERS.BYEAR>2000
  87. )
  88. group by THUMB.BID
  89. having COUNT(THUMB.UIDD)>10
  90.         )
  91. group by BID
  92.  
  93.  
  94. --11
  95. select  distinct UIDD
  96. from SUB,LABEL
  97. where SUB.LID=LABEL.LID and LABEL.LNAME in('文学','艺术','哲学','音乐')
  98.  
  99. --12--
  100. select *
  101. from MBLOG
  102. --WHERE TITLE LIKE '%最多地铁站%' AND TITLE LIKE '%\_华中科技大学%' ESCAPE '\';
  103.  
  104.  
  105. --13
  106. select A.UIDD,A.UIDFLED
  107. from FOLLOW A, FOLLOW B
  108. where A.UIDD = B.UIDFLED and A.UIDFLED = B.UIDD and A.UIDD<A.UIDFLED
  109.  
  110. --14
  111. select distinct A.UIDD
  112. from FRIENDS A
  113. where not exists(
  114.         select *
  115.         from FRIENDS B
  116.         where B.UIDD = 5 and not exists(
  117.                         select *
  118.                         from FRIENDS C
  119.                         where C.FUID = B.FUID and A.UIDD = C.UIDD
  120.                 )
  121. )
  122.  
  123. --15
  124. select distinct TOPDAY.BID,MBLOG.TITLE,B_L.LID
  125. from TOPDAY,MBLOG left outer join B_L on(MBLOG.BID = B_L.BID)
  126. where TOPDAY.BID = MBLOG.BID and TYEAR = 2019 and TMONTH = 4 and TDAY = 20
  127.  
  128. --16
  129. select A.UIDD,B.UIDD
  130. from FRIENDS A,FRIENDS B
  131. where A.UIDD != B.UIDD and A.FUID=B.FUID and A.UIDD<B.UIDD
  132. group by A.UIDD,B.UIDD
  133. having COUNT(*)>=3
  134.  
  135. --17
  136. create view TOPTEN (BID,TITLE,UIDD,NAME,ZAN)
  137. as
  138. select  distinct TOPDAY.BID,MBLOG.TITLE,MBLOG.UIDD,USERS.NAME,DIANZAN.ZAN
  139. from (select BID,COUNT(*) as ZAN
  140.                                                   from THUMB
  141.                                                   group by BID) as DIANZAN(BID,ZAN),TOPDAY,MBLOG,USERS
  142. where TOPDAY.BID = MBLOG.BID and MBLOG.UIDD = USERS.UIDD and MBLOG.BID = DIANZAN.BID
  143.  
  144.  
  145.  
  146.  
  147.  
  148.  
  149.  
  150.  
  151.  
  152.  
  153.  
  154. --1.2 (1)
  155. insert into MBLOG
  156. values (31,'1111',1,2019,4,1,'2222');
  157.  
  158. update MBLOG
  159. set PYEAR = 2018
  160. where BID = 31;
  161.  
  162. delete
  163. from MBLOG
  164. where BID=31;
  165.  
  166. --1.2 (2)
  167. create table FANS_3(
  168.         FANS_UIDD INT primary key,
  169.         FANS_NAME VARCHAR(10),
  170.         FANS_SEX CHAR(2),
  171.         FANS_BYEAR INT,
  172.         FANS_CITY VARCHAR(20)
  173.         )
  174.  
  175. insert
  176. into FANS_3
  177. select *
  178. from USERS
  179. where UIDD in (
  180.         select UIDD
  181.         from FOLLOW
  182.         where UIDFLED = 3
  183. )
  184.  
  185. --1.2(3)
  186. create trigger THUS on THUMB
  187. instead of insert
  188. as
  189.         declare @UID1 int, @UID2 int, @UID3 int, @BID1 int
  190.         select @UID1 = S.UIDD, @UID2 = S.BID from inserted S
  191.         select @UID3 = UIDD, @BID1 = BID from MBLOG where BID = @UID2
  192.         if(@UID1 = @UID3)
  193.                 begin
  194.                         print '点赞失败';
  195.                 end
  196.         else
  197.                 begin
  198.                         insert into THUMB values(@UID1,@UID2)
  199.                 end
  200.  
  201.  
  202.  
  203. --1.2(4)
  204. create table FANS_1(
  205.         FANS1_UIDD INT ,
  206.         FANS1_NAME VARCHAR(10),
  207.         FANS1_SEX CHAR(2),
  208.         FANS1_BYEAR INT,
  209.         FANS1_CITY VARCHAR(20)
  210.         )
  211.  
  212. insert into FANS_1
  213. values (1,N'张三',N'男',1969,N'北京市');
  214.  
  215. update FANS_1
  216. set FANS1_NAME = '222'
  217. where FANS1_UIDD = 1;
  218.  
  219. delete
  220. from FANS_1
  221. where FANS1_UIDD=1;
  222.  
  223.  
  224.  
  225. create table Student
  226. (
  227.         Sno CHAR(9)  primary key,
  228.         Sname CHAR(20) unique,
  229.         Ssex CHAR(2),
  230.         Sage SMALLINT,
  231.         Sdept CHAR(20)
  232. );
  233.  
  234. create table Course
  235. (
  236.         Cno CHAR(4) primary key,
  237.         Cname CHAR(40) not null,
  238.         Cpno CHAR(4),
  239.         Ccredit SMALLINT,
  240.         foreign key (Cpno) references Course(Cno)
  241. );
  242.  
  243. create table SC
  244. (
  245.         Sno CHAR(9),
  246.         Cno CHAR(4),
  247.         Grade SMALLINT,
  248.         primary key (Sno,Cno),
  249.         foreign key (Sno) references Student(Sno),
  250.         foreign key (Cno) references Course(Cno)
  251. );
  252.  
  253. insert into Student
  254. values('201215123','张三','男',20,'cs')
  255.  
  256. insert into Course
  257. values('1314','美术','1122',5)
  258. insert into Course
  259. values('1122','音乐','1314',4)
  260.  
  261. insert into SC
  262. values('201215123','1314',90)
  263.  
  264. --管理员
  265. create LOGIN management with  PASSWORD='123456', DEFAULT_DATABASE = lab2
  266. create USER management for LOGIN management with DEFAULT_SCHEMA=[dbo]
  267. exec sp_addrolemember 'db_owner', 'management'
  268.  
  269. --学生
  270. create LOGIN student with  PASSWORD='123456', DEFAULT_DATABASE = lab2
  271. create USER student for LOGIN student with DEFAULT_SCHEMA=[dbo]
  272. grant select, update on SC to student
  273. grant select on Course to student
  274.  
  275. --教师
  276. create LOGIN teacher with  PASSWORD='123456', DEFAULT_DATABASE = lab2
  277. create USER teacher for LOGIN teacher with DEFAULT_SCHEMA=[dbo]
  278. grant select on SC to teacher
  279. grant update(Grade) on SC to teacher
  280. grant select on Course to teacher
  281. grant select on Student to teacher
  282.  
Parsed in 0.050 seconds