数据库单表查询

--对xs表增加身份证号码属性列，要求是18位的字符类型
use xsgl
alter table xs
add ID char(18);

以下题目基于xsgl数据库实现，该数据库包括：
kc(课程表）：课程号，课程名，学分，学时数，先行课号
xs(学生表）：学号，姓名，性别，出生时间，专业，总学分
cj(选课表）：学号，课程表，成绩

--1．	查询有直接先行课的课程的课号，课名和先行课号。
select 课程号,课程名,先行课号 
from kc
where 先行课号 is not null;

--2．	查询先行课号是“J001”号课程的课号和课名
select 课程号,课程名
from kc
where 先行课号='J001';

--3．	查询所有的网络工程系姓李，张，王的同学的学号和姓名
select 学号,姓名
from xs
where 姓名 like '[李,张,王]%'and 专业='网络工程';

--4．	查询不在网络工程和信息管理专业学习的学生的学号和姓名，
--系别，并对查询结果按照专业的升序和学号的降序排序
select 学号,姓名,专业
from xs
where 专业 not in('网络工程','信息管理')
order by 专业 asc,学号 desc;

--5．	查询每门课不及格的学生的人数，显示课号和人数
select 课程号,COUNT(*) 人数
from cj
where 成绩>=60
group by 课程号;

datediff(datepart，startdate，enddate）
该函数返回startdate与enddate之间datepart格式的时间）
eg: select DATEDIFF(day,‘1997-12-30 23:59:59’,‘1997-12-31’)
datepart可以填的值： 缩写：
（年）：year yy, yyyy
(季） ：quarter qq, q
(月） :month mm, m
(天） ：dayofyear dy, y
(天） ：day dd, d
(周） ：week wk, ww
(小时） ：hour hh
(分） ：minute mi,n
(秒） ：second ss, s
(毫秒） ：millisecond ms
(微秒,一百万分之一秒） ：microsecond mcs
(毫微秒，十亿分之一秒） ：nanosecond ns

getdate():获取当前时间（得到的数据为时间格式）

convert(type,x) : 把x转化为type类型。

--6．	查询年龄不在30-35之间的网络工程系的学生的学号，姓名和年龄
select 学号,姓名,datediff(yy,出生时间,getdate()) 年龄
from xs
where datediff(yy,出生时间,getdate()) not between 30 and 35 and 专业='网络工程';

select语句中，其后默认为all,即输出所有，包括重复的行。要使去掉重复的行，可以在select后加上distinct(即不默认为all).

--7．	查询没有选修‘J001’号课程的学生的学号（注意去掉重复的元组）
select distinct 学号
from cj
where 学号 not in (select 学号 from cj where 课程号='J001');

SQL 标量函数-----日期函数 day() 、month()、year()
select day(createtime) --取时间字段的天值

select month(createtime) --取时间字段的月值

select year(createtime) --取时间字段的年值

--8．	查询每个学生的学号，姓名，出生年份，并给出生年份起别名为chusheng
select 学号,姓名,year(出生时间) chusheng
from xs

DATENAME ( datepart , date )
参数 datepart 是返回的 date 的一部分。下表列出了所有有效的 datepart 参数。用户定义的变量等效项是无效的。
SELECT DATENAME (day ,getdate())–当月几号
SELECT DATENAME (month ,getdate())–月份
SELECT DATENAME (quarter ,getdate())–季度
SELECT DATENAME (dayofyear ,getdate())–一年中的第几天
SELECT DATENAME(week,GETDATE())–一年中第几周
SELECT DATENAME(weekday,GETDATE())–星期几
SELECT DATENAME(hour,GETDATE())–当前几点，24小时制的
SELECT DATENAME(minute,GETDATE())–取当前分钟数
SELECT DATENAME(second,GETDATE())–取当前秒数
SELECT DATENAME(millisecond,GETDATE())–毫秒

--9.  查询每个学生的学号，姓名和出生日期（出生日期根据身份证号码查询）
select 学号,姓名,datename(yy,出生时间)+'年'+DATENAME(mm,出生时间)+'月'+DATENAME(DD,出生时间)+'日' 出生日期
from xs

--10.  查询选修J001课程成绩排名第一的同学的学号和成绩
select top 1 学号,成绩
from cj
where  课程号='J001'
order by 成绩 desc;

--11.  查询所有名字中含有’明’或者’丽’的同学的学号，姓名
select 学号,姓名
from xs
where 姓名	like '%[明，丽]%';

--12.  查询信息管理专业年龄超过20岁的学生的人数
select COUNT(*) 人数
from xs
where (year(getdate())-year(出生时间))>20 and 专业='信息管理';

--13.  查询平均成绩超过80分的课程的课程号和平均成绩
select 课程号,AVG(成绩) 平均成绩
from cj
group by 课程号
having  AVG(成绩)>80;

--14.  查询每个专业所有姓张的人数
select 专业,COUNT(姓名) 人数
from xs
where 姓名 like '张%'
group by 专业;

left()
LEFT (<character_expression>， <integer_expression>)
返回character_expression 左起 integer_expression 个字符。
right()
right (<character_expression>， <integer_expression>)
返回character_expression 右起 integer_expression 个字符。

eg: select left(‘avsgdh’,4) --返回：avsg

--15.  查询各种姓氏的人数（假设没有复姓）
select LEFT(姓名,1) 姓氏,COUNT(姓名) 人数
from xs
group by LEFT(姓名,1);

--16.  查询选修课程超过5门的学生的学号和选课门数，以及平均成绩
select 学号,COUNT(课程号) 选课数,AVG(成绩) 平均成绩
from cj
group by 学号
having COUNT(课程号)>5;

--17.  查询选修‘J001’课程的成绩排名前五的学生的学号和成绩
select top 5 学号,成绩
from cj
where  课程号='J001'
order by 成绩 desc;

--18.  查询每个学生的最低分和选课门数
select 学号,COUNT(学号) 选课数,MIN(成绩) 最低分
from cj
group by 学号

--19.  查询各个专业各种性别的人数
select 专业,性别,COUNT(*) 人数
from xs
group by 专业,性别;

--20.  查询各个专业男生的人数
select 专业,性别,COUNT(*) 人数
from xs
group by 专业,性别
having 性别='男';

--21.  列出有二门以上课程（含两门）不及格的学生的学号及该学生的平均成绩
select cj.学号,AVG(cj.成绩) 平均成绩
from cj,(
select 学号
from cj
where 成绩<60
group by 学号
having COUNT(distinct 课程号)>=2)q
where q.学号=cj.学号 
group by cj.学号;

substring(

--22.  显示学号第五位或者第六位是1、2、3、4或者9的学生的学号、姓名、性别、年龄及专业
select 学号,姓名,性别,(year(getdate())-year(出生时间)) 年龄,专业
from xs
where SUBSTRING(学号,5,1)in ('1','2','3','4','9') or SUBSTRING(学号,6,1) in ('1','2','3','4','9');

--23.  显示选修课程数最多的学号及选修课程数最少的学号
--(1).显示选修课程数最多的学号
select 学号
from (select 学号,COUNT(课程号) 选课数 
from cj 
group by 学号)J,(
select MAX(p.选课数) 选修课程数最多的,MIN(p.选课数) 选修课程数最少的
from(
select 学号,COUNT(课程号) 选课数 
from cj 
group by 学号)P)Q
where Q.选修课程数最多的=J.选课数;
--(2).显示选修课程数最少的学号
select 学号
from (select 学号,COUNT(课程号) 选课数 
from cj 
group by 学号)J,(
select MAX(p.选课数) 选修课程数最多的,MIN(p.选课数) 选修课程数最少的
from(
select 学号,COUNT(课程号) 选课数 
from cj 
group by 学号)P)Q
where Q.选修课程数最少的=J.选课数;


 --24.  查询选修了A001或者A002或者J001或者J002课程的学生的学号和课程号
 select 学号,课程号
 from cj
 where 课程号 in ('A001','A002','J001','J002');

字符匹配：
（下横线）代表任意单个字符
ps: 用下划线时字符串的长度需完全匹配。
eg: name char(4)
查询 姓张的名字为两位的： ‘张 ’ (_后面接两个空格）

 --25.  查询姓名为两个字的不同姓氏的人数，输出姓氏，人数。
 --第一种解法：
select LEFT(姓名,1) 姓氏,COUNT(姓名) 人数
from xs
where 姓名 like '__        '
group by LEFT(姓名,1);
--第二种解法：
select LEFT(姓名,1) 姓氏,COUNT(姓名) 人数
from xs
where LEN(姓名)=2
group by LEFT(姓名,1);

以下基于spj数据库写的

--1．	求供应工程J1零件的供应商号码SNO
select distinct sno
from spj
where jno='J1';

--2．	求查询每个工程使用不同供应商的零件的个数
select jno,sno,sum(qty) sumqty
from spj
group by jno,sno;

--3．	求供应工程使用零件P3数量超过200的工程号JNO
select jno,sum(qty) sumqtyP3
from spj
where pno = 'P3'
group by jno
having sum(qty)>200;

--4．	求颜色为红色和蓝色的零件的零件号和名称
select pno,pname
from P
where color = '红' or color = '蓝';

--5．	求使用零件数量在200-400之间的工程号
select jno,sum(qty)sumqty
from spj
group by jno
having sum(qty) between 200 and 400;

--6．	查询每种零件的零件号，以及使用该零件的工程数。
select pno,COUNT(jno) sumjno
from spj
group by pno;