【SQL Server】设计数据库、创建数据库和数据表

数据：

create database test
use test
create table c/*课表*/
(
cno varchar(6),
cn nvarchar(15),
score float,
ct int,
tn nvarchar(10)
)
insert into c (cno , cn, score, ct, tn)
values('K001', '计算机图形学' , 2.5, 40,'胡晶晶')
,('K002', '计算机应用基础' ,3, 48,'任泉')
,('K006', '数据结构' ,4, 64,'马跃先')
,('M001', '政治经济学' ,4, 64,'孔繁新')
,('S001', '高等数学' ,3, 48,'赵晓生')




use test
create table s/*学生表*/
(sno varchar(6),
sn nvarchar(10),
sex nchar(1) ,
class nvarchar(10),
date date,
tel varchar(10)
)
insert into s (sno, sn, sex, class, date, tel)
values('0433', '张艳', '女', '生物04', '1986/9/13', null)
,('0496', '李越', '男', '电子04', '1984/2/23', '1381290')
,('0529', '赵欣', '男', '会计05', '1984/1/27', '1350222')
,('0531', '赵治国', '男', '生物05', '1986/9/10', '1331256')
,('0538', '于兰兰', '女', '生物05', '1984/2/20', '1331200')
,('0591', '王丽丽', '女', '电子05', '1984/3/20', '1332080')
,('0592', '王海强', '男', '电子05', '1986/11/1', null)



use test
create table homework/*作业表*/
(cno  varchar(6),
sno  varchar(6),
w1 float,
w2 float,
w3 float
)

insert into  homework (cno ,sno,w1,w2,w3)
values ('K001', '0433', 60, 75, 75),
 ('K001', '0529', 70, 70, 60),
 ('K001', '0531', 70, 80, 80),
 ('K001', '0591', 80, 90, 90),
 ('K002', '0496', 80, 80, 90),
 ('K002', '0529', 70, 70, 85),
 ('K002', '0531', 80, 80, 80),
 ('K002', '0538', 65, 75, 85), 
 ('K002', '0592', 75, 85, 85),
 ('K006', '0531', 80, 80, 90),
 ('K006', '0591', 80, 80, 80),
 ('M001', '0496', 70, 70, 80),
 ('M001', '0591', 65, 75, 75),
 ('S001', '0531', 80, 80, 80),
 ('S001', '0538', 60, null, 80)

实验3：

select sno,sn,class
from s
 
select *
from c
 
select distinct class
from s
 
select ct
from c
where ct>60
 
select sno,sn,date
from s
where date like '%1986%'
 
select sno,cno
from homework
where w1>80 and w2 >80 and w3 > 80
 
select sno,sn,class
from s
where sn like '张%'
 
  select *
from s
where class like '%05' and sex ='男'
 
 select sno,cno
from homework
where w1 is null or w2  is null or w3 is  null
 
   select SUM(w1)
from homework
where (sno='0538')
 
select COUNT(sno)
from homework
where (cno='K001')
 
select COUNT(distinct class)
from s
 
select sno ,AVG(w1),AVG(w2),AVG(w3)
from homework
GROUP BY sno
having (COUNT(cno)>=3)
 
select s.sno,c.cn,s.sn
from homework,s,c
where (s.sno=homework.sno)and (c.cno=homework.cno)and (sn='于兰兰')

实验4：

select Y.*
from s as X, s as Y
where X.class = Y.class and X.sn = '赵治国'
/*1连接查询*/

select *
from s
where class =(select class from s
              where sn = '赵治国' )
 /*1子查询*/
 
select X.* 
from  c as X, c as Y
where X.ct > Y.ct and Y.cn = '计算机应用基础'
/*2连接查询*/

select *
from c
where ct>(select ct from c
           where cn = '计算机应用基础' )
/*2子查询*/


select s.sno,s.sn 
from s,homework 
where (s.sno = homework.sno) and ( homework.cno = 'KO02')
/*3连接查询*/


select s.sno,s.sn 
from s,homework
where (cno in(select cno from homework
              where cno = 'K002'))
/*4普通子查询*/




select sno,cno,w1,w2,w3
 from homework
where (sno not in (select sno 
                  from homework
                   where cno in ('KO01', 'MO01')))            
/*4*/



insert into s (sno,sn,sex,class)
values ('0593','张乐','男','电子05')

delete 
from s 
where sn='张乐'

update homework
  set w1 = 2*w1
  update homework
  set w2 = 2*w2
  update homework
  set w3 = 2*w3

实验5：

create view sub_1
as select sno,sn,sex,class,date
from s
where class = '电子05'

create view sub_2
as select s.sno,s.sn,c.cn,w1,w2,w3 from homework,c,s
where class ='生物05' and s.sno=homework.sno and homework.cno=c.cno

create view sub_3(sno,w1,w2,w3)
as select sno, AVG(w1),AVG(w2),AVG(w3) from homework
group by sno

alter view sub_2
as select homework.sno,sn,class,w1 from homework,s,c
where class ='生物05' and s.sno=homework.sno and homework.cno=c.cno

insert into sub_1(sno,sn,sex,class,date)
values ('0596','赵亦','男','电子05','1986/6/8')

update sub_1
set sex='女'
where (sn='赵亦') 

delete from sub_1
where sn='赵亦'

drop view sub_1