常用语句（一）create、insert into...values(...)、truncate、distinct、having、inner join ... on...

/*
drop database exercise;
truncate table student;
truncate table book;
alter table book drop column remark;
*/
create database exercise
go
use exercise
go
/*
------------------------------------------ 表student
*/
create table student(sno int not null,sname char(10),age int,sex int)
go
insert into student values(20130201,'张明',21,1);
insert into student values(20130202,'刘晓雅',19,0);
insert into student values(20130203,'程浩',22,1);
insert into student values(20130204,'柳华景',20,1);
insert into student values(20130205,'刘洋',24,1);
insert into student values(20130206,'张娅',23,0);
insert into student values(20130207,'凌之玲',21,0);
insert into student values(20130208,'王远思',20,1);
insert into student values(20130209,'丁雅欣',20,0);
insert into student values(20130210,'吴国宁',22,1);
/*
------------------------------------------ 表book
*/
/*
1,创建图书表book，包含以下列
  bid 图书编号 int not null
  bname 图书名 varchar(20) not null
  author 作者  varchar(20)
  publicCompany 出版社 varchar(50)
  publicTime 出版时间 datetime
  type 图书类别 int 
	1---文学 2--艺术 3--军事
  price 价格 decimal(6,2)
*/
create table book(bid int not null,bname varchar(20) not null,author varchar(20),publicCompany varchar(50),publictime datetime,type int,price decimal(6,2))
go
truncate table book
go
/*
alter table book drop column remark
go
*/
--2,插入10条测试数据
insert into book values(101,'我的祖国啊','刘清华','人民出版社','2003-03-16',1,'39.50');
insert into book values(102,'现代军事浅谈','张思远','国防出版社','2003-03-16',2,'45.00');
insert into book values(103,'人与社会','程思琪','社会出版社','2003-03-16',1,'33.50');
insert into book values(104,'中国的崛起','刘华','人民出版社','2003-03-16',1,'53.00');
insert into book values(105,'祖国我爱你','凌小雨','社会出版社','2003-03-16',1,'27.00');
insert into book values(106,'现代都市生活','吴宇森','社会出版社','2003-03-16',1,'59.00');
insert into book values(107,'世界博览','晨嘉园','艺术出版社','2003-03-16',2,'72.55');
insert into book values(108,'社会民生','张小雅','社会出版社','2003-03-16',1,'69.00');
insert into book values(109,'世界发展史','刘青','国防出版社','2003-03-16',3,'30.50');
insert into book values(110,'社会经济','吴昊','社会出版社','2003-03-16',1,'49.00');
--3,修改book表名称为ebook,给ebook增加一个新列名称为remark 类型为varchar(255)
alter table book add remark varchar(255)
go
--4,查询图书编号在101到108之间且作者姓张的图书记录按照价格升序排列
select * from book where bid between 101 and 108 and author like '张%' order by price
go
--5,查询图书名称含有 祖国的文学类图书 或者出版时间在2010年8月8日以前的图书记录
select * from book where (bname like '祖国' and type=1) or publictime<'2010-8-8'
go
--6,查询非军事且出版社为人民出版社的图书的数量以及所有这些图书总价
select count(*),SUM(price) from book where type<>3 and publicCompany='人民出版社'
go
--7,按类别查询图书的总数以及不同类别图书的最高价格和最低价格且最低价格不能低于49.99元的记录信息按照图书总数降序排列
select COUNT(*),MAX(price),MIN(price) from book group by type having MIN(price)>=49.99 order by COUNT(*) desc
go
--8,修改类别为军事的社会出版社出版的图书或者人民出版社在2005年5月5日以前出版的图书的remark信息为“该图书很不错”
update book set remark='该图书很不错' where type=3 and (publicCompany='社会出版社' or publictime<'2005-5-5')
go
--9,按出版社和作者分类查询图书的出版社、作者和图书数量，且查询出的数量不能少于3本，按照数量降序排列
select publicCompany,author,COUNT(*) from book group by publicCompany,author having COUNT(*)>=3 order by COUNT(*) desc
go
--10,查询不是人民出版社和社会出版社出版的书名中含有“自传”的作者姓名为两个字的图书记录信息
select author from book where publicCompany not in('人民出版社','社会出版社') and bname like '&自传&'
/*
------------------------------------------ 表borrowinfo
*/
/*
创建借书信息表borrowinfo
sno 学生学号 int
bid 图书编号 int
borrowtime 借书时间 datetime
returntime 还书时间 datetime
status  借书状态 int 1--未归还 2--已还 3--续借
remark  说明 varchar(255)
*/
create table borrowinfo(sno int,bid int,borrowtime datetime,returntime datetime,status int,remark varchar(255))
go
--1,插入10条借书信息
truncate table borrowinfo
go
/*
select * from borrowinfo
go
*/
insert into borrowinfo values(20130205,'101','2002-06-16 09:51:05','2002-06-16 09:51:05',1,null);
insert into borrowinfo values(20130202,'102','2011-07-23 12:49:45','2011-07-23 12:49:45',2,null);
insert into borrowinfo values(20130207,'103','1999-01-13 18:27:26','1999-01-13 18:27:26',1,null);
insert into borrowinfo values(20130205,'101','2000-10-31 06:35:08','2000-10-31 06:35:08',1,null);
insert into borrowinfo values(20130209,'102','2000-02-21 09:38:15','2001-02-21 09:38:15',3,null);
insert into borrowinfo values(20130204,'103','2001-09-16 10:56:36','2001-09-16 10:56:36',2,null);
insert into borrowinfo values(20130205,'102','2001-03-26 17:14:27','2001-03-26 17:14:27',1,null);
insert into borrowinfo values(20130210,'103','2002-04-29 15:24:29','2002-04-29 15:24:29',1,null);
insert into borrowinfo values(20130205,'101','2003-07-11 16:25:02','2003-07-11 16:25:02',1,null);
--2,查询借书超过3本且状态为未归还的学生学号和姓名
select bw.sno,sname from student st
inner join borrowinfo bw on st.sno=bw.sno
where status=1 group by bw.sno,sname
having COUNT(bw.sno)>3
go
--3,查询书名中含有“祖国”且类型为文学的图书被哪个学生借阅，查询出该学生的学号，姓名和年龄，按照年龄排序
select distinct st.sno,sname,age from student st
inner join borrowinfo bw on bw.sno=st.sno
inner join book bk on bk.bid=bw.bid
where type='1'
and bname like '%祖国%' 
order by age
go
--4,查询借书时间发生在2000年1月1日到2001年1月1日之间的且续借的学生姓名和性别
select sname,sex from student st
inner join borrowinfo bw on bw.sno=st.sno
where bw.borrowtime between '2000-1-1' and '2001-1-1'
and bw.status='3'
go
--5,按性别统计学生借书的数量并且筛选出数量大于3的记录
select sex,COUNT(bw.sno) 借书数量 from student st
inner join borrowinfo bw on st.sno=bw.sno
group by sex
having COUNT(bw.bid)>3
go
--6,按照学号和状态统计年龄在20到30岁之间的学生借书的学号和总量
select st.sno,COUNT(bw.sno) '总量' from student st
inner join borrowinfo bw on st.sno=bw.sno
where age between 20 and 30
group by st.sno,status
go
--7,查询年龄不小于20岁或者性别为女的学生借书信息包括学生姓名和书名
select distinct sname,bk.bname from student st
inner join borrowinfo bw on st.sno=bw.sno
inner join book bk on bw.bid=bk.bid
where age>=20
or sex=0
go
--8,查询没有借阅图书的学生学号和姓名
select st.sno,sname from student st
left outer join borrowinfo bw on bw.sno=st.sno
where bw.sno is null
go
--9,查询没有被借阅过的图书的图书名和作者
select author,bname from book bk
left outer join borrowinfo bw on bw.bid=bk.bid
where bw.bid is null
go
/*
------------------------------------------ END
*/