简单查询1.查询课程表中的课程号和Cname。select Cid,Cname from C;2.查询学生表中的全体学生记录。select * from S;3.查询学生表的全体学生的姓名和入校年份。select sname,year(entrydate) from S;4.查询学生表中全体学生的姓名及入校年份，为无标题列加标题。select sname,year(entrydate) '入校年份'from S;或者：select sname, DATEPART(yy,entrydate) as '入校年份'from S;5.查询学生表中全体学生的姓名、性别及出生年份，添加标题为birthselect S.sname,S.sex,year(S.birthday) 'birth'from S6.查询选修了课程的学生学号。select distinct Sid from SC;7.查询教师表中的前条记录。select top 3 * from T;8.查询课程表中的前30%的记录。select top 30 percent * from C;9.查询工龄小或等于35岁的教师信息。select * from Twhere year(getdate())- year(workingtime)<=35;或者：select * from Twhere datediff(year,workingtime,getdate())<=35;10.查询工作时间在1990-的教师编号、姓名和工作时间。select Tid,name,workingtimefrom Twhere year(workingtime) between 1990 and ;或者：select Tid,name,workingtime from Twhere T.workingtime between '1990.1.1' and '.12.31'或者select Tid,name,workingtime from T where DATEPART(yy, workingtime) between 1990 and ;select Tid,name,workingtime from T where DATEPART(yy, workingtime)>=1990 and DATEPART(yy, workingtime)<=;11.查询计算机系、软件系的教师。select * from T where department='计算机系' unionselect * from T where department='软件'或者：select * from T where department='计算机系' or department='软件'或者：select * from T where department in ('计算机系','软件');12.查询学生表中姓张的学生的信息。select * from Swhere sname like '张%';或者：select * from S where left( sname,1) ='张'13.查询课程表的“C语言”的课程号。select Cid from C where Cname='C语言';14.查询选课表中没有得分的考生号。select Sid from SC where score is null;15.查询男教师的基本信息，按职称降序排列select * from Twhere sex='男'order by title Desc;16.查询教师表中信息，按职称降序排序，工作时间升序排序。select * from Torder by title Desc ,workingtime asc;17.查询学生表中学生总数。select '学生总数',count(*) from S;18.查询学生的平均年龄。select avg(age) as '平均年龄'from S;19.查询选课表中课程选课人数3人以上的各个课程号和相应的选课人数。select Cid,count(Sid)from SCgroup by Cidhaving count(*)>3;20.创建选课表的一个副本。select * into copyCfrom SC;21.查询姓张或姓刘的学生信息，按姓名降序排序。select * from Swhere sname like '刘%' or sname like '张%'order by sname desc;或者：select * from Swhere sname like '[张刘]%'order by sname desc;或者select * from S where left(sname,1) in ('张','刘') order by sname desc22.查询选课表中每个学生的总分数，并按分数降序排序。select Sid,sum(score) as '总分'from SCgroup by Sidorder by sum(score) desc;23.查询学生的学号、姓名、性别、入学年份、年级(学号前4位)、专业(学号第5-6))SELECT Sid,sname,sex, DATEPART(yy,entrydate) AS '入学年份,left(Sid,4) AS '年级' ,SUBSTRING(Sid,5,2) as '专业' FROM S24.查询含有db_课程信息，(-默认为通配符，这里取消转移用escape)select * from C where Cname like 'db\_%' escape '\'
数据表create database jxglgouse jxglgocreate table T(Tid nchar(5) primary key,name nchar(10) not null,sex nchar(2) default '男',workingtimedatetime,plandscape nchar(20),degree nchar(10),title nvarchar(6),department nvarchar(20),telnchar(11))create table S(Sidnchar(10) primary key,sname nchar(10),sexnchar(2) default '男',birthday Date,ageas datediff(yy,birthday,getdate()),entrydate date,plandscape nchar(10) default '团员',nativeplacenvarchar(20),class as substring(Sid,7,2))create table C(Cidnchar(3) primary key,cname nvarchar(20) Not null,category nchar(4) Not null check(category in('考试','考查')) default '考试',creditint,Tidnchar(5) foreign key references T(Tid) on update cascade)create table SC(Sidnchar(10) foreign key references S(Sid) on update cascade on delete cascade,Cidnchar(3),score int check(score between 0 and 100),primary key(Sid,Cid),foreign key(Cid) references C(Cid) on update cascade on delete cascade)INSERT [dbo].[T] ([Tid], [name], [sex], [workingtime], [plandscape], [degree], [title], [department], [tel]) VALUES (N'95011', N'赵西明 ', N'男 ', CAST(0x0000768300000000 AS DateTime), N'群众 ', N'硕士 ', N'副教授', N'软件', N'13733152369')INSERT [dbo].[T] ([Tid], [name], [sex], [workingtime], [plandscape], [degree], [title], [department], [tel]) VALUES (N'95012', N'李小平 ', N'男 ', CAST(0x0000775E00000000 AS DateTime), N'党员 ', N'硕士 ', N'教授', N'计算机系', N'13733152370')INSERT [dbo].[T] ([Tid], [name], [sex], [workingtime], [plandscape], [degree], [title], [department], [tel]) VALUES (N'95013', N'张爽 ', N'男 ', CAST(0x00007BC700000000 AS DateTime), N'群众 ', N'本科 ', N'副教授', N'计算机系', N'13733152371')INSERT [dbo].[T] ([Tid], [name], [sex], [workingtime], [plandscape], [degree], [title], [department], [tel]) VALUES (N'95014', N'李丽宁 ', N'女 ', CAST(0x00007BC700000000 AS DateTime), N'党员 ', N'硕士 ', N'副教授', N'计算机系', N'13733152372')INSERT [dbo].[T] ([Tid], [name], [sex], [workingtime], [plandscape], [degree], [title], [department], [tel]) VALUES (N'95015', N'张金明 ', N'男 ', CAST(0x0000812C00000000 AS DateTime), N'群众 ', N'硕士 ', N'讲师', N'计算机系', N'13733152373')INSERT [dbo].[T] ([Tid], [name], [sex], [workingtime], [plandscape], [degree], [title], [department], [tel]) VALUES (N'95016', N'李燕 ', N'女 ', CAST(0x000082C800000000 AS DateTime), N'党员 ', N'硕士 ', N'讲师', N'计算机系', N'13733152374')INSERT [dbo].[T] ([Tid], [name], [sex], [workingtime], [plandscape], [degree], [title], [department], [tel]) VALUES (N'95017', N'宛平 ', N'女 ', CAST(0x0000840B00000000 AS DateTime), N'群众 ', N'博士 ', N'副教授', N'软件', N'13733152375')INSERT [dbo].[T] ([Tid], [name], [sex], [workingtime], [plandscape], [degree], [title], [department], [tel]) VALUES (N'95018', N'陈江川 ', N'男 ', CAST(0x0000883900000000 AS DateTime), N'群众 ', N'博士 ', N'讲师', N'软件', N'13733152376')INSERT [dbo].[T] ([Tid], [name], [sex], [workingtime], [plandscape], [degree], [title], [department], [tel]) VALUES (N'95019', N'郭新 ', N'男 ', CAST(0x00008CAA00000000 AS DateTime), N'党员 ', N'博士 ', N'讲师', N'软件', N'13733152377')INSERT [dbo].[S] ([Sid], [sname], [sex], [birthday], [entrydate], [plandscape], [nativeplace]) VALUES (N'071101', N'张佳 ', N'女 ', CAST(0x3F1D0B00 AS Date), CAST(0x3C3D0B00 AS Date), N'团员 ', N'江西南昌')INSERT [dbo].[S] ([Sid], [sname], [sex], [birthday], [entrydate], [plandscape], [nativeplace]) VALUES (N'071102', N'好生 ', N'男 ', CAST(0xCD1E0B00 AS Date), CAST(0x3C3D0B00 AS Date), N'团员 ', N'广州顺德')INSERT [dbo].[S] ([Sid], [sname], [sex], [birthday], [entrydate], [plandscape], [nativeplace]) VALUES (N'071203', N'徐克 ', N'男 ', CAST(0xBF210B00 AS Date), CAST(0x3C3D0B00 AS Date), N'团员 ', N'江西南昌')INSERT [dbo].[S] ([Sid], [sname], [sex], [birthday], [entrydate], [plandscape], [nativeplace]) VALUES (N'071204', N'叶飞 ', N'女 ', CAST(0xD1210B00 AS Date), CAST(0x3C3D0B00 AS Date), N'党员 ', N'上海')INSERT [dbo].[S] ([Sid], [sname], [sex], [birthday], [entrydate], [plandscape], [nativeplace]) VALUES (N'071205', N'任伟 ', N'男 ', CAST(0x7B220B00 AS Date), CAST(0x3C3D0B00 AS Date), N'团员 ', N'北京顺义')INSERT [dbo].[S] ([Sid], [sname], [sex], [birthday], [entrydate], [plandscape], [nativeplace]) VALUES (N'071206', N'江贺 ', N'男 ', CAST(0x02200B00 AS Date), CAST(0x3D3D0B00 AS Date), N'党员 ', N'福建厦门')INSERT [dbo].[C] ([Cid], [cname], [category], [credit], [Tid]) VALUES (N'101', N'计算机基础', N'考试 ', 2, N'95011')INSERT [dbo].[C] ([Cid], [cname], [category], [credit], [Tid]) VALUES (N'102', N'C语言', N'考试 ', 3, N'95012')INSERT [dbo].[C] ([Cid], [cname], [category], [credit], [Tid]) VALUES (N'103', N'计算机组成原理', N'考试 ', 3, N'95012')INSERT [dbo].[C] ([Cid], [cname], [category], [credit], [Tid]) VALUES (N'104', N'汇编语言', N'考试 ', 3, N'95014')INSERT [dbo].[C] ([Cid], [cname], [category], [credit], [Tid]) VALUES (N'105', N'Java语言', N'考查 ', 2, N'95015')INSERT [dbo].[C] ([Cid], [cname], [category], [credit], [Tid]) VALUES (N'106', N'操作系统', N'考试 ', 3, N'95016')INSERT [dbo].[C] ([Cid], [cname], [category], [credit], [Tid]) VALUES (N'107', N'数据结构', N'考试 ', 3, N'95017')INSERT [dbo].[C] ([Cid], [cname], [category], [credit], [Tid]) VALUES (N'108', N'编译原理', N'考试 ', 3, N'95017')INSERT [dbo].[C] ([Cid], [cname], [category], [credit], [Tid]) VALUES (N'109', N'网络基础', N'考试 ', 3, N'95017')INSERT [dbo].[C] ([Cid], [cname], [category], [credit], [Tid]) VALUES (N'110', N'数据库原理', N'考试 ', 3, N'95017')INSERT [dbo].[C] ([Cid], [cname], [category], [credit], [Tid]) VALUES (N'120', N'SQLServer', N'考查 ', 2, N'95018')INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'071101', N'101', 90)INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'071101', N'102', 70)INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'071101', N'103', 48)INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'071101', N'105', 80)INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'071102', N'102', 90)INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'071102', N'104', 77)INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'071102', N'106', 68)INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'071102', N'107', 85)INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'071102', N'109', 77)INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'071102', N'110', 65)INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'071102', N'120', 48)INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'071203', N'102', 65)INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'071203', N'104', 82)INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'071203', N'105', 79)INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'071203', N'107', 55)INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'071203', N'110', 77)INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'071203', N'120', 67)INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'071204', N'101', 86)INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'071204', N'102', 86)INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'071204', N'104', 77)INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'071204', N'105', 84)INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'071204', N'106', 95)INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'071204', N'108', 91)INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'071204', N'110', 82)INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'071205', N'101', 63)INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'071205', N'102', 84)INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'071205', N'103', 77)INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'071206', N'107', 58)INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'071206', N'108', 74)INSERT [dbo].[SC] ([Sid], [Cid], [score]) VALUES (N'071206', N'109', 74)
