[大数据]1024数据库探索

创建三个表格，然后添加相关的约束，修改、删除、查询…

drop table Books
create table Books
(BookID char(20) primary key,--图书编号 主键约束
Bname char(50) unique,--图书名 唯一约束
Author varchar(30),--作者
Press varchar(30),--出版社
Price money--定价
)
select *from Books
drop table Readers
create table Readers
(ReaderID char(10) primary key,--读者编号 主键约束
Rname char(20) not null,--读者姓名 非空约束
Rsex char(2) check(rsex = '男' or rsex = '女'),--读者性别
Department varchar(30),--读者所在部门
Phone char(20)--读者电话
)
select *from Readers
drop table Lending
create table Lending
(ReaderID char(10) not null,--读者编号 读者信息表外键
BookID char(20) not null,--图书编号 图书信息表外键
Borrowdate date default getdate() not null,--借出日期 默认当前日期
Returndate date check(Returndate>Borrowdate)--归还日期 晚于借出日期
)
select *from Lending
/*给books表增加一个ISBN字段*/
alter table Books add ISBN char(10)
select *from Books
/*给ISBN字段修改数据类型*/
alter table Books alter column ISBN char(10)
--update Books set ISBN ='df_ISBN' where ISBN = '7111085949';
/*给ISBN设置约束*/
alter table Books add constraint df_ISBN  default '7111085949'for ISBN
/*删除字段上的default约束*/
alter table Books drop constraint df_ISBN 
select *from Books
/*删除表上增加的ISBN字段*/
alter table Books drop column ISBN 



--alter table Books add foreign key(ISBN) references Books (ISBN = 'df_ISBN'default'7111085949')

/*使用insert语句向表中插入若干语句*/
insert into Books values('TP311.138DFG','网络数据库实用教程','石大鑫','机械工业出版社',23)
insert into Books values('TP311.SQ','SQL Server2008实用教程','董建斌','机械工业出版社',25)
insert into Books values('TP311.138QZA','SQL Server实训','郑启芬','清华大学出版社',18)
insert into Books values('TP311.138WJ','数据库程序设计','刘韵华','电子工业出版社',38)
insert into Books values('TP311.138XZQ','SQL Server2008数据库系统管理','赵志清','人民邮电出版社',45)
insert into Books values('TP393.41ZX','ASP.NET案例开发','陈正熙','机械工业出版社',45)
select *from Books

insert into Readers values('R20101001','郭xx','男','计算机系',1008611),('R20101002','姚期智','男','计算机系',6688993),
('R20101003','张大彪','男','计算机系',1234566),('R20101004','毕淑敏','女','信息系',9898765),
('R20101005','董明珠','女','信息系',4008365315),('R20101006','撒贝宁','男','信息系',3816666)
SELECT *FROM Readers
delete Readers

insert into Lending values('R20101001','TP311.138SQ','2014-9-6','2014-10-20'),('R20101001','TP311.138SQZA','2014-9-15','2014-11-9'),
('R20101001','TP393.41ZX','2014-9-6','2014-12-3'),('R20101002','TP311.138WJ','2014-7-5','2014-9-20'),('R20101005','TP311.138SQ','2014-7-8','2014-9-8'),
('R20101005','TP311.138XZQ','2015-3-18',NULL)
select *from Lending
/*查询Books表中书名的第三个字中包含“数”的书号和书名*/
--select *from Books where and BookID？？？--两个下划线代表两个空格，所以查询的是第三个字
/*查询Books表中书名的第三个字中包含“数”的书号和书名*/
select  BookID,Bname from Books where Bname like '%__数%'
/*查询比“数据库程序设计”价格高的“书号”和“书名”*/	
select BookID,Bname from Books where price > '38'
/*查询有未还书记录的读者姓名  ？？？？？？？*/
select ReaderID from Lending where Returndate =  '  '
select *from Books,Readers,Lending where Bname = '%数据库程序设计%'


select distinct ReaderID,Rname,Rsex,Department,Phone
from Readers,Lending
where ReaderID in (select BookID from Lending where BookID =  'TP311.138WJ')

select Rname 
from Readers,Lending
group by Readers.ReaderID
having COUNT(Lending.ReaderID)>2
select *from Readers

select Rname
from Readers
where ReaderID in(
select ReaderID from Lending
group by Lending.ReaderID
having count(Lending.ReaderID)>2
)

未完，待续…