查询

[a75386666]

create table stuInfo
(
stuId int primary key,--主键
stuName varchar(20),--学生姓名列
stuSex bit foreign key references pSex(sexId),--学生ID列
stuAge tinyint check (stuAge>=0 and stuAge<=200),--学生年龄
stuBirthday datetime not null,--学生生日
stuClass varchar(20) foreign key references pClass(classId),--学生班级
stuAddress varchar(20) default'北京大学',--学生地址
stuCity varchar(20) foreign key references pCity(cityId)--城市
)

题目要求 统计 1 班 出生日期在1980年以后的 男生 的人数
select * from stuInfo where convert(varchar(20),stuBirthday)='1980%' 这段代码写出来什么都查不到 为什么？

[a75386666]

应该是

select * from stuInfo where convert(varchar(20),stuBirthday) like '1980%'

我刚刚复制错了 用 like 什么都查不到

[a75386666]

create database studenta--创建数据库
on primary--主目录下创建
(
name=studenta_data,--数据库在SQL中的名称
filename='e:\studenta_data.mdf',--保存路径
size=1,--初始化数据库大小
maxsize=10,--最大值
filegrowth=1--数据库容量增长率
)
log on--日志文件参数设置
(
name=studenta_log,--日志文件在SQL中的名称
filename='e:\studenta_log.ldf',--保存路径
size=1,--初始化日志文件大小
maxsize=10,--最大值
filegrowth=1--日志文件容量增长率
)
use studenta--使用切换到数据库studenta

--创建pSex表
create table pSex
(
sexId bit primary key,--设置为主键
sexValue varchar(2) check(sexValue='男'or sexValue='女')--设定值并约束
)


--创建pClass表
create table pClass
(
classId varchar(20) primary key,--设置为主键
classValue varchar(20)not null--设定值并约束
)


--创建pCity表
create table pCity
(
cityId varchar(20) primary key,--设置为主键
cityValue Varchar(20) not null--设定值并约束
)

--创建stuInfo表
create table stuInfo
(
stuId int primary key,--设置为主键
stuName varchar(20),--设定值并约束
stuSex bit foreign key references pSex(sexId),--设定值并约束
stuAge tinyint check (stuAge>=0 and stuAge<=200),--设定值并约束
stuBirthday datetime not null,--设定值并约束
stuClass varchar(20) foreign key references pClass(classId),--设定值并约束
stuAddress varchar(20) default'北大青鸟首图校区',--设定值并约束
stuCity varchar(20) foreign key references pCity(cityId)--设定值并约束
)
drop table stuInfo--删除表
drop table stuMark
drop table pCity
drop table pClass
drop table pSex
--创建pMark表
create table stuMark--创建表
(
stuId int foreign key references stuInfo(stuId) primary key,--设置列 并设置外键 连接主键ID 并设置本表主键
stuEn float check(stuEn>=0 and stuEn<=100),--设置列并约束1到100
stuMa float check(stuMa>=0 and stuMa<=100),--设置列并约束1到100
stuCh float check(stuCh>=0 and stuCh<=100)--设置列并约束1到100
)

insert into pSex(sexId,sexValue)
values (0,'男')--添加值
insert into pSex(sexId,sexValue)
values (1,'女')--添加值

select * from pSex--查看表

insert into pCity(cityId ,cityValue)--批量添加
select 'a001','北京' union
select 'a002','天津' union
select 'a003','上海' union
select 'a004','广州' union
select 'a005','上海' union
select 'a006','南京' union
select 'a007','西安' union
select 'a008','吉林' union
select 'a009','南宁' union
select 'a010','铁岭' union
select 'a011','盘锦' union
select 'a012','哈尔滨' union
select 'a013','武汉' union
select 'a014','南京' union
select 'a015','深圳' union
select 'a016','长春'

select * from pCity

insert into  pClass(classId ,classValue)--批量添加
select 'A001','一年一班' union
select 'A002','一年二班' union
select 'A003','一年三班' union
select 'A004','一年四班' union
select 'A005','一年五班' union
select 'A006','一年六班' union
select 'A007','一年七班' union
select 'A008','一年八班' union
select 'A009','一年九班' union
select 'A010','一年十班' union
select 'A012','一年十一班'

SELECT * FROM stuInfo

select * from pClass
insert into stuInfo values(1,'小地方',0,55,1777-8-9,'A005',default,'a001')--以下是逐条添加
insert into stuInfo values(2,'藕节恢',1,44,1982-06-19,'A008',default,'a009')
insert into stuInfo values(3,'斯蒂芬',0,55,1999-04-24,'A008',default,'a012')
insert into stuInfo values(4,'内部',1,55,1928-8-7,'A001',default,'a001')
insert into stuInfo values(5,'对方',1,55,1988-8-5,'A005',default,'a014')
insert into stuInfo values(6,'科技',0,55,1922-3-8,'A007',default,'a001')
insert into stuInfo values(7,'用途',1,55,1966-5-28,'A006',default,'a015')
insert into stuInfo values(8,'杀得',0,55,1988-2-28,'A001',default,'a001')
insert into stuInfo values(9,'他人发',1,55,1988-8-9,'A001',default,'a014')
insert into stuInfo values(10,'上帝八',1,14,1998-06-22,'A009',default,'a009')
insert into stuInfo values(11,'纷纷大',0,55,1993-09-25,'A001',default,'a001')
insert into stuInfo values(12,'而日他',1,44,1997-12-31,'A008',default,'a009')
insert into stuInfo values(13,'斯蒂芬',0,55,1999-04-24,'A008',default,'a012')
insert into stuInfo values(14,'大法官',1,55,1989-04-13,'A001',default,'a011')
insert into stuInfo values(15,'地方如',1,55,1987-03-21,'A001',default,'a001')
insert into stuInfo values(16,'张对方',0,55,1980-08-31,'A001',default,'a011')
insert into stuInfo values(17,'张我',1,55,1970-07-30,'A001',default,'a001')
insert into stuInfo values(18,'我三',1,55,1970-04-11,'A001',default,'a015')
insert into stuInfo values(19,'个三',0,55,1990-03-14,'A001',default,'a013')
insert into stuInfo values(20,'张三',1,55,1989-07-15,'A001',default,'a011')

select * from stuInfo--查看表
truncate table stuInfo--删除表内所有内容

insert into stuMark values(1,57,48,96)--添加
insert into stuMark values(2,98,44,84)--添加
insert into stuMark values(3,98,74,65)--添加
insert into stuMark values(4,32,46,57)--添加
insert into stuMark values(5,12,34,78)--添加
insert into stuMark values(6,47,98,45)--添加
insert into stuMark values(7,98,74,56)--添加
insert into stuMark values(8,45,67,89)--添加
insert into stuMark values(9,13,24,89)--添加
insert into stuMark values(10,48,94,54)--添加
insert into stuMark values(11,57,78,94)--添加
insert into stuMark values(12,91,56,68)--添加
insert into stuMark values(13,29,49,78)--添加
insert into stuMark values(14,18,97,49)--添加
insert into stuMark values(15,49,87,98)--添加
insert into stuMark values(16,45,67,98)--添加
insert into stuMark values(17,23,74,56)--添加
insert into stuMark values(18,34,56,78)--添加
insert into stuMark values(19,45,67,89)--添加
insert into stuMark values(20,12,34,56)--添加

select * from stuMark--查看表内所有内容
truncate table stuMark--删除表内所有内容

这是所有的代码

[a75386666]

--1、	查询 张三 这个人的全部信息 
select stuInfo.stuId as 学号,stuInfo.stuName as 姓名,stuInfo.stuAge as 年龄,
stuInfo.stuBirthday as 生日,stuInfo.stuAddress as 地址,
stuMark.stuEn as 英语成绩,stuMark.stuMa as 数学成绩,
stuMark.stuCh as 语文成绩,pSex.sexValue as 性别,pClass.classValue as 班级,
pCity.cityValue as 城市 from stuInfo
inner join stuMark on stuInfo.stuId=stuMark.stuId
inner join pSex on stuInfo.stuSex=pSex.sexId
inner join pClass on stuInfo.stuClass=pClass.classId
inner join pCity on stuInfo.stuCity=pCity.cityId
where stuName='张三'

--2、	查询 所有姓张的人员的全部信息 并且按照 年龄降序排列
select stuInfo.stuId as 学号,stuInfo.stuName as 姓名,stuInfo.stuAge as 年龄,
stuInfo.stuBirthday as 生日,stuInfo.stuAddress as 地址,
stuMark.stuEn as 英语成绩,stuMark.stuMa as 数学成绩,
stuMark.stuCh as 语文成绩,pSex.sexValue as 性别,pClass.classValue as 班级,
pCity.cityValue as 城市 from stuInfo
inner join stuMark on stuInfo.stuId=stuMark.stuId
inner join pSex on stuInfo.stuSex=pSex.sexId
inner join pClass on stuInfo.stuClass=pClass.classId
inner join pCity on stuInfo.stuCity=pCity.cityId
where stuName like '张%' order by stuAge desc

--3、	查询 所有1班的学员的全部信息

select stuInfo.stuId as 学号,stuInfo.stuName as 姓名,stuInfo.stuAge as 年龄,
stuInfo.stuBirthday as 生日,stuInfo.stuAddress as 地址,
stuMark.stuEn as 英语成绩,stuMark.stuMa as 数学成绩,
stuMark.stuCh as 语文成绩,(stuMark.stuMa+stuMark.stuEn+stuMark.stuCh) as 总分,pSex.sexValue as 性别,pClass.classValue as 班级,
pCity.cityValue as 城市 from stuInfo
inner join stuMark on stuInfo.stuId=stuMark.stuId
inner join pSex on stuInfo.stuSex=pSex.sexId
inner join pClass on stuInfo.stuClass=pClass.classId
inner join pCity on stuInfo.stuCity=pCity.cityId
where stuClass='A001' 

--4、查询 所有 1 班 总分最高的前三名学员的信息 并降序排列 并显示总分

select top 3 stuInfo.stuId as 学号,stuInfo.stuName as 姓名,stuInfo.stuAge as 年龄,
stuInfo.stuBirthday as 生日,stuInfo.stuAddress as 地址,
stuMark.stuEn as 英语成绩,stuMark.stuMa as 数学成绩,
stuMark.stuCh as 语文成绩,(stuMark.stuMa+stuMark.stuEn+stuMark.stuCh) as 总分,pSex.sexValue as 性别,pClass.classValue as 班级,
pCity.cityValue as 城市 from stuInfo
inner join stuMark on stuInfo.stuId=stuMark.stuId
inner join pSex on stuInfo.stuSex=pSex.sexId
inner join pClass on stuInfo.stuClass=pClass.classId
inner join pCity on stuInfo.stuCity=pCity.cityId
where stuClass='A001' order by 总分 desc



--5查询 2 班 男生的数学平均分和女生的平均分

select pSex.sexValue,ceiling(avg(stuMark.stuMa)) as 数学平均分 from stuInfo 
left join stuMark on stuInfo.stuId=stuMark.stuId
inner join pSex on stuInfo.stuSex=pSex.sexId
where stuClass='A002'
group by pSex.sexValue


--6、	统计 1 班 的语文及格人数
select count(stuCh) from stuInfo
inner join stuMark on stuInfo.stuId=stuMark.stuId
where stuClass='A001'and stuCh>=60

这是几条查询语句 很崩溃

[冷箫轻笛]

select * from stuInfo where convert(varchar(20),stuBirthday)='1980%' 这段代码写出来什么都查不到 为什么？

-----------------------------------------

你这个查询的是80年出生的学生
查询80年以后出生的学生这样写不就行了吗？
select * from stuInfo where stuBirthday >= '1980-01-01'

[Lee_jsmwj]

你可以查询

select convert(varchar(20),stuBirthday) from stuInfo 

得到的结果是
10 27 1904 12:00AM
05 12 1905 12:00AM
05 26 1905 12:00AM
03 29 1905 12:00AM
05 30 1905 12:00AM
03 27 1905 12:00AM
04 18 1905 12:00AM
05 13 1905 12:00AM
05 26 1905 12:00AM
05 25 1905 12:00AM
05 14 1905 12:00AM
05 9 1905 12:00AM
05 26 1905 12:00AM
05 27 1905 12:00AM
05 18 1905 12:00AM
04 26 1905 12:00AM
04 18 1905 12:00AM
05 10 1905 12:00AM
05 28 1905 12:00AM
05 22 1905 12:00AM
当然like '1980%' 是查不到东西的

或者可以改用like '%1980%'吧，虽然不是很严谨来着

[冷箫轻笛]

我知道了，你的插入语句有问题
insert into stuInfo values(13,'斯蒂芬',0,55,1999-04-24,'A008',default,'a012')

日期这么插入是不行的
要加引号
insert into stuInfo values(13,'斯蒂芬',0,55,'1999-04-24','A008',default,'a012')

[冷箫轻笛]

insert into stuInfo values(1,'小地方',0,55,'1777-8-9','A005',default,'a001')--以下是逐条添加
insert into stuInfo values(2,'藕节恢',1,44,'1982-06-19','A008',default,'a009')
insert into stuInfo values(3,'斯蒂芬',0,55,'1999-04-24','A008',default,'a012')
insert into stuInfo values(4,'内部',1,55,'1928-8-7','A001',default,'a001')
insert into stuInfo values(5,'对方',1,55,'1988-8-5','A005',default,'a014')
insert into stuInfo values(6,'科技',0,55,'1922-3-8','A007',default,'a001')
insert into stuInfo values(7,'用途',1,55,'1966-5-28','A006',default,'a015')
insert into stuInfo values(8,'杀得',0,55,'1988-2-28','A001',default,'a001')
insert into stuInfo values(9,'他人发',1,55,'1988-8-9','A001',default,'a014')
insert into stuInfo values(10,'上帝八',1,14,'1998-06-22','A009',default,'a009')
insert into stuInfo values(11,'纷纷大',0,55,'1993-09-25','A001',default,'a001')
insert into stuInfo values(12,'而日他',1,44,'1997-12-31','A008',default,'a009')
insert into stuInfo values(13,'斯蒂芬',0,55,'1999-04-24','A008',default,'a012')
insert into stuInfo values(14,'大法官',1,55,'1989-04-13','A001',default,'a011')
insert into stuInfo values(15,'地方如',1,55,'1987-03-21','A001',default,'a001')
insert into stuInfo values(16,'张对方',0,55,'1980-08-31','A001',default,'a011')
insert into stuInfo values(17,'张我',1,55,'1970-07-30','A001',default,'a001')
insert into stuInfo values(18,'我三',1,55,'1970-04-11','A001',default,'a015')
insert into stuInfo values(19,'个三',0,55,'1990-03-14','A001',default,'a013')
insert into stuInfo values(20,'张三',1,55,'1989-07-15','A001',default,'a011')

--查询
select count(1)
from stuInfo
where stuClass = 'A001' and stusex = '0' and stuBirthday >= '1980-01-01'

--结果
4

[冷箫轻笛]

还有，个人认为你的stuInfo和stuMark表虽然有主外键关系，
但是物理的表关系要慎用，呵呵，并不是说建了关系表结构就好。

[ouyangtianke]

晕倒！～ 哪有那么复杂～！～ 就象四楼写的就行

[a75386666]

其实大家都没注意到！

insert into stuInfo values(1,'小地方',0,55,1777-8-9,'A005',default,'a001')--以下是逐条添加
insert into stuInfo values(2,'藕节恢',1,44,1982-06-19,'A008',default,'a009')--生日要用''引起来
insert into stuInfo values(3,'斯蒂芬',0,55,1999-04-24,'A008',default,'a012')--生日要用''引起来
insert into stuInfo values(4,'内部',1,55,1928-8-7,'A001',default,'a001')--生日要用''引起来
insert into stuInfo values(5,'对方',1,55,1988-8-5,'A005',default,'a014')--生日要用''引起来
insert into stuInfo values(6,'科技',0,55,1922-3-8,'A007',default,'a001')--生日要用''引起来
insert into stuInfo values(7,'用途',1,55,1966-5-28,'A006',default,'a015')--生日要用''引起来
insert into stuInfo values(8,'杀得',0,55,1988-2-28,'A001',default,'a001')--生日要用''引起来
insert into stuInfo values(9,'他人发',1,55,1988-8-9,'A001',default,'a014')--生日要用''引起来
insert into stuInfo values(10,'上帝八',1,14,1998-06-22,'A009',default,'a009')--生日要用''引起来
insert into stuInfo values(11,'纷纷大',0,55,1993-09-25,'A001',default,'a001')--生日要用''引起来
insert into stuInfo values(12,'而日他',1,44,1997-12-31,'A008',default,'a009')--生日要用''引起来
insert into stuInfo values(13,'斯蒂芬',0,55,1999-04-24,'A008',default,'a012')--生日要用''引起来
insert into stuInfo values(14,'大法官',1,55,1989-04-13,'A001',default,'a011')--生日要用''引起来
insert into stuInfo values(15,'地方如',1,55,1987-03-21,'A001',default,'a001')--生日要用''引起来
insert into stuInfo values(16,'张对方',0,55,1980-08-31,'A001',default,'a011')--生日要用''引起来
insert into stuInfo values(17,'张我',1,55,1970-07-30,'A001',default,'a001')--生日要用''引起来
insert into stuInfo values(18,'我三',1,55,1970-04-11,'A001',default,'a015')--生日要用''引起来
insert into stuInfo values(19,'个三',0,55,1990-03-14,'A001',default,'a013')--生日要用''引起来
insert into stuInfo values(20,'张三',1,55,1989-07-15,'A001',default,'a011') --生日要用''引起来



select * from stuInfo where stuBirthday>'1980-01-01'--这代码就可以查询80年以后出生的了
主要就是加数据的时候没用''引起来 所以加进去的数据都是垃圾的！！貌似都是 1905……的！！