实验（答案）

--创建一个数据库LIBRARY
--sclaass(cno,depart),
--student(sno,sname,ssex,sbirthday)  
create database library
use library
create table sclass
(cno int primary key ,
 deparment nvarchar(50)  --varchar=8000,nvarchar=4000,DBO=O
)
use library
create table student(
sno int primary key ,
sname nvarchar(20) ,
ssex nvarchar(2) ,
sbirthday nvarchar(20) ,
cno int ,
foreign key(cno)  references sclass
)
use library
create table book(
bno int ,
bname nvarchar(30) ,
author nvarchar(50) ,
price nvarchar(30) ,
press nvarchar(30),
primary key(bno)
)
use library
create table borrow(
sno int ,
bno int ,
bdate nvarchar(30) ,
foreign key(sno) references student,
foreign key(bno) references book
)

insert into sclass values (0501,'计算机系')
insert into sclass values (0502,'计算机系')  --因为是整数，结果就会去掉前面的0
insert into sclass values (0510,'电子工程系') --插全部数据的写法
insert into sclass(cno, deparment) values (0511,'电子工程系')--插部分数据的写法

select * from sclass

insert into student values (1,'张任','男','1998-01-01','0501')
insert into student values (10,'许兵','男','1988-08-10','0510')
insert into student values (11,'张功','男','1988-06-02','0510')
insert into student values (12,'李华','男','1987-10-03','0510')
insert into student values (13,'马超','男','1989-02-03','0511')
insert into student values (14,'曾英','女','1987-03-06','0511')
insert into student values (2,'程华','男','1989-01-10','0501')
insert into student values (3,'张丽','女','1988-06-07','0502')
insert into student values (4,'王英','女','1987-12-10','0502')
insert into student values (5,'李静','男','1988-04-05','0502')
select * from student

--000000
--前两个0     中间两个0    后面两个0      简单的开发定义
--01国外      类别编号     书的编号
--00国内      00~99       
 
insert into book values (10011,'C程序设计','李洪',24,'清华大学出版社')
insert into book values (10012,'C程序设计','李洪',24,'清华大学出版社')
insert into book values (10013,'C习题解答','李洪',12,'清华大学出版社')

insert into book values (10020,'数据结构','徐华',29,'人民邮电出版社')
insert into book values (10021,'数据结构','徐华',30,'人民邮电出版社')
insert into book values (10023,'高等数学','王涛',30,'高等教育出版社')
insert into book values (10034,'软件工程','张明',34,'机械工业出版社')
insert into book values (20025,'信息学','张港',35,'清华大学出版社')
insert into book values (20026,'信息学','张港',35,'清华大学出版社')
insert into book values (20042,'电工学','王民',30,'人民邮电出版社')
insert into book values (20056,'操作系统','曾平',26,'清华大学出版社')
insert into book values (20057,'操作系统','曾平',26,'清华大学出版社')
insert into book values (20058,'操作系统','曾平',26,'清华大学出版社')
insert into book values (20067,'数字电路','徐汉',32,'高等教育出版社')
insert into book values (20080,'数据库原理','陈曼',32,'高等教育出版社')
insert into book values (20059,'网络工程','黄军',38,'高等教育出版社')
insert into book values (10058,'C程序设计','李洪',24,'清华大学出版社')

select * from book
select count(*) as 列数 from book

insert into borrow values (1,10020,'2007-12-05')
insert into borrow values (1,20025,'2007-11-08')
insert into borrow values (1,20059,'2008-04-11')
insert into borrow values (10,20056,'2008-02-05')
insert into borrow values (12,20067,'2008-03-06')
insert into borrow values (2,10011,'2007-10-02')
insert into borrow values (2,10013,'2008-04-03')
insert into borrow values (3,10034,'2008-04-10')
insert into borrow values (3,10058,'2008-04-11')
insert into borrow values (4,10012,'2008-04-06')
insert into borrow values (5,10023,'2008-02-03')

select * from borrow
select count(*) as 列数 from borrow

 --清华大学。韩顺平
---查询！
select * from sclass
select * from student
select * from book
select * from borrow

--作业--1 查询图书品种的数目
select * from book
select count(distinct bname) as 图书品种的总数目 from book

--2 查询总图书品种的数目
select bname as N'图书名',count(bname) as N'数目' from book group by bname

--3 查询各班的人数
select cno as N'班级号',count(cno) as N'人数' from student group by cno

--4 查询各系的人数
select a.deparment,count(b.cno) from sclass as a join student as b on
a.cno=b.cno group by a.deparment

select a.deparment,count(b.cno) from sclass as a, student as b where
a.cno=b.cno group by a.deparment

--5 查询借阅图书的学号、姓名、书名和结束日期
select a.sno,a.sname,b.bname,c.bdate from student as a join borrow as c
on a.sno=c.sno join book as b on b.bno=c.bno

select a.sno,a.sname,b.bname,c.bdate from student as a,borrow as c,
book as b where a.sno=c.sno and b.bno=c.bno

--6 查询借有图书的学生学号和姓名

select a.sno,a.sname from student as a where a.sno in (select c.sno
from borrow as c)

select distinct a.sno,a.sname from student as a join borrow as c on
a.sno=c.sno

--7 查询每个学生的借书数目
select a.sname as '借书人',count(c.sno) as '借书数' from student as a join borrow
as c on a.sno=c.sno group by a.sname

 

 

 

 

暂时只做了7个查询，插入的数据可能有个别的不同。。。。