数据库明BookManager,有两个表student和lendbook,sql语句如下use BookManager
go
if exists(select * from sysobjects where name='student')
drop table student
go
create table student
(
sid varchar(10) primary key,--学号
sname varchar(20) not null,--姓名
age int not null,--年龄
sex char(2) not null,--性别
)
go
if exists (select * from sysobjects where name = 'lendbook')
drop table lendbook
go
create table lendbook
(
bookid varchar(20) primary key,--书的编号
bookname varchar(20) not null,
price float not null,
sid varchar(10) ,--外键
sname varchar(20) not null,
lenddate datetime not null--借出时间
)
go
alter table Student
add
constraint CH_sex check(sex in('男','女')),--检查约束,性别必须是男或女
constraint CH_age check(age between 10 and 100)insert into student values('20050001','刘德华',25,'男');
insert into student values('20050002','李开复',40,'男');
insert into student values('20050003','赵云',27,'男');
insert into student values('20050004','李晓庆',23,'女');
insert into lendbook values('10001','java开发详解',31,'200050001','刘德华','2009-3-10')
insert into lendbook values('10002','STL源码剖析',53,'200500003','赵云','2009-2-22')
insert into lendbook values('10003','C++编程思想',53,'200500003','赵云','2009-2-22')
insert into lendbook values('10004','深入浅出MFC',53,'200500003','赵云','2009-2-22')我想查刘德华借了哪本书select bookname from lendbook where sid in(select sid from student where sname='刘德华')怎么结果为空?
go
if exists(select * from sysobjects where name='student')
drop table student
go
create table student
(
sid varchar(10) primary key,--学号
sname varchar(20) not null,--姓名
age int not null,--年龄
sex char(2) not null,--性别
)
go
if exists (select * from sysobjects where name = 'lendbook')
drop table lendbook
go
create table lendbook
(
bookid varchar(20) primary key,--书的编号
bookname varchar(20) not null,
price float not null,
sid varchar(10) ,--外键
sname varchar(20) not null,
lenddate datetime not null--借出时间
)
go
alter table Student
add
constraint CH_sex check(sex in('男','女')),--检查约束,性别必须是男或女
constraint CH_age check(age between 10 and 100)insert into student values('20050001','刘德华',25,'男');
insert into student values('20050002','李开复',40,'男');
insert into student values('20050003','赵云',27,'男');
insert into student values('20050004','李晓庆',23,'女');
insert into lendbook values('10001','java开发详解',31,'200050001','刘德华','2009-3-10')
insert into lendbook values('10002','STL源码剖析',53,'200500003','赵云','2009-2-22')
insert into lendbook values('10003','C++编程思想',53,'200500003','赵云','2009-2-22')
insert into lendbook values('10004','深入浅出MFC',53,'200500003','赵云','2009-2-22')我想查刘德华借了哪本书select bookname from lendbook where sid in(select sid from student where sname='刘德华')怎么结果为空?
select bookname from lendbook where sid in(select sid from student where rtrim(ltrim(sname))='刘德华')
(
bookid varchar(20) primary key,--书的编号
bookname varchar(20) not null,
price float not null,
sid varchar(10) ,--外键
sname varchar(20) not null,
lenddate datetime not null,--借出时间
foreign key (sid) references student(sid) )