欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

SqlServer2012语句操作学习之用SQL语句对表中数据进行操作及数据的导入和导出

程序员文章站 2022-06-16 13:22:22
--一、用sql语句对表中数据进行操作 --1.根据系部表、班级表、学生表、教师表已有数据(如果没有数据,请使用管理平台或者sql语句先为以上表添加数据),使用sql语句在课程表course、选课表...

--一、用sql语句对表中数据进行操作

--1.根据系部表、班级表、学生表、教师表已有数据(如果没有数据,请使用管理平台或者sql语句先为以上表添加数据),使用sql语句在课程表course、选课表s_c和授课表t_c三个表中添加适当的数据

insert s_c

values('2016010203','002','80')--刘晓信息

insert s_c

values('2016010101','001','80')

insert s_c

values('2016010102','002','80')

insert t_c

values('0101','001','5')

insert t_c

values('0103','001','6')

insert t_c

values('0104','002','3')

--2.将教师编号为“0001”的教师的姓名修改为“刘清华”,并将所有教师的工资提高500元。

update teacher

set t_name='刘清华'

where t_id='0001'

update teacher

set t_salay=t_salay+500

--3.教师“于林“已经离开学校,需要将他的信息删除

delete teacher

where t_name='于林';

--4.使用sql语句将学号为2016010101的学生的姓名修改为“白沧铭”。

update student

set s_name='白沧铭'

where s_id='2016010101'

--5.由于“刘晓”同学已经退学,需要将该同学的选课信息和基本信息删除。

delete s_c

where s_id='2016010203'

delete student

where s_id='2016010203'

--6.将20160101班的学生的成绩如果不足60分,全部提高5分

update s_c

set result=result+5

where s_id in(select c_id from class where c_id ='20160101')

and result<60

--7.将教师刘清华的工资修改为平均工资

update teacher

set t_salay=(select avg(t_salay) from teacher)

where t_name='刘清华'

--8.将学号为“2016010101”的同学的“计算机基础”的成绩改为该课的平均成绩

update s_c

set result=(select avg(result) from s_c where course_id='003' )

where s_id='2016010101'

--9.把成绩低于总平均成绩的女同学的成绩提高5%

update s_c

set result=result*(1+0.05)

where result

--10、学生王丽在0002号课程考试中作弊,该课成绩应作零分计

update s_c

set result=0

where course_id='0002'and s_id=(select s_id from student where s_name='王丽')

--11、把学号为2016010101的学生的性别和系改成与2016010102学号的学生一样

update student

set s_sex=(select s_sex from student where s_id='2016010102')

where s_id ='2016010101'

--11、把学号为2016010101的学生的性别和系改成与2016010102学号的学生一样

update student

set s_sex=(select s_sex from student where s_id='2016010102')

where s_id ='2016010101'

update department

set dep_id=(select dep_id from class,student where class.c_id=student.c_id and s_id='2016010102')

where dep_id=(select dep_id from class,student where class.c_id=student.c_id and s_id='2016010101')

--12、删除没有选课的学生的基本信息

delete from student

where s_id not in(select s_id from s_c)

--13、删除“白沧铭”同学的计算机基础课程的选课记录。

begin transaction;

delete from s_c

where s_id=(select s_id from student where s_name='白沧铭') and course_id=(select course_id from course where course_name='计算机基础')

rollback transaction

--二、数据的导入和导出

--1、检索出studentmanager中表student和teacher表的信息并插入表student2和teacher2中(提示:用select……into命令)

select * into student2 from student

select * into teacher2 from teacher

--2.将学生表student中的数据导出为文本文件,文件名为student.txt

--3、将教师表teacher中的数据导出为excel文件,文件名为teacher.xls

--4、将文件teacher.xls中的数据库导入teacher2表中

--5.将文件student.txt中的数据导入student2表中

--6、检索出20160101班学生的学号、课程号和成绩并插入表s_c2中(提示:用insert命令)

insert into s_c2(s_id,course_id,result)--s_c2需存在

select s_id,course_id,result from s_c

where s_id in(select s_id from student where c_id='20160101')

--三、简单查询及排序显示(在studentmanager数据库中完成以下各题目)

--(1)查询讲授了课程的教师的教师号

select t_id

from t_c

--(2)查询每个教师的工龄

select t_name as 姓名, year(getdate())-year(t_entrydate)as 工龄

from teacher

--(3)查询教师所属的部门

select t_name as 姓名, dep_name as 部门

from teacher,department

where teacher.dep_id=department.dep_id

--(4)查询姓名中第2个字为“沧”的学生的个人信息。

select *

from student

where s_name like '_沧%'

--(5)检索全体学生的情况,结果按做在班级升序排序,同一班级的按出生日期升序排列。

select *

from student

order by c_id asc , s_borndate desc

--(6)查询0003号课程成绩排在前两名的学生的学号和成绩。

select s_id 学号,result 成绩

from s_c

where course_id='0003'and s_id in (select top 2 s_id from s_c where course_id='0003' order by result desc)

--(7)查询年龄在前5位的教师的信息。

select top 5 * from teacher order by year(getdate())-year(t_borndate) desc

--四、集合运算

--(1)查询所有副教授和教授的信息(要求用union,不要用or)

select *

from teacher

where t_professor='副教授'

union

select *

from teacher

where t_professor='教授'

--(2)对student表使用交intersect和差except运算,并分析查询结果。

--查询班级号为20160101与自年龄不大于19的学生信息交集

select *

from student

where c_id='20160101'

intersect

select *

from student

where s_id in(select s_id from student where (year(getdate())-year(s_borndate))<=22)

--查询班级号为20160101与自年龄不大于19的学生信息差集

select *

from student

where c_id='20160101'

except

select *

from student

where s_id in(select s_id from student where (year(getdate())-year(s_borndate))<=22)

--五、使用分组和汇总检索数据

--(1)查询学生总数

select count(*) 学生总数

from student

--(2)检索每个学生的总成绩

select s_id as 学号,sum(result)as 总成绩

from s_c

group by s_id

--(3)查询选修课课程的学生的人数

select count(distinct s_id) as 总人数

from s_c

--(4)查询选修0001号课程的学生的平均成绩

select avg(result)平均成绩

from s_c

where course_id='0001'

--(5)检索每门课程的平均分

select course_id 课程号,avg(result)平均分

from s_c

group by course_id

--(6)检索0002号课程的最高成绩、最低成绩和平均成绩

select max(result)最高成绩,min(result)最低成绩,avg(result)平均分

from s_c

where course_id='0002'

--(7)检索男女生人数

select s_sex 性别,count(*) 总人数

from student

group by s_sex

--(8)检索平均分大于85分的课程的课程号和平均分

select course_id 课程号,avg(result) 平均分

from s_c

group by course_id

having avg(result)>85

--(9)统计各班级人数

select c_id 班级,count(distinct c_id)人数

from student

where c_id is not null

group by c_id

--(10)统计学生表student中20160101班的男女生人数

select s_sex 性别,count(*) 人数

from student

where c_id='20160101'

group by s_sex

--(11)统计各部门的教师总人数

select dep_id 系部,count(*) 人数

from teacher

group by dep_id

--(12)统计各门课程的选课人数

select course_id 课程号,count(*) 人数

from s_c

group by course_id

--(13)统计选修了2门以上课程的学生的学号和课程门数

select s_id 学号,count(*) 课程门数

from s_c

group by s_id

having count(*)>=2

--(14)在teacher表上验证count(*)、count(sal)、count(distinct t_professor)的使用区别

select count(*)

from teacher

select count(t_salary)--提示sal列明无效

from teacher

select count(distinct t_professor)--根据t_professor去掉了重复值

from teacher

--六、多表数据的检索(用连接查询)

--(1)检索学生的学号、姓名和所在班级名称

select s_id,s_name,c_name

from student,class

where student.c_id=class.c_id

--(2)检索选修“数据库原理与应用”课程的学生的姓名和成绩

select s_name,result

from student,s_c

where student.s_id=s_c.s_id and course_id=(select course_id from course where course_name='数据库原理与应用')

select s_name,result

from student,s_c,course

where student.s_id=s_c.s_id and s_c.course_id=course.course_id and course_name='数据库原理与应用'

--(3)查询每门课程的课程号、任课教师姓名及其选课人数

select s_c.course_id 课程号,t_name 教师姓名,count(*)选课人数

from teacher,t_c,s_c

where teacher.t_id=t_c.t_id and t_c.course_id=s_c.course_id

group by s_c.course_id,t_name

--(4)检索与“王丽”在同一班级的其他学生的姓名

select b.s_name

from student a,student b

where a.c_id=b.c_id and a.s_name='王丽'and b.s_name<>'王丽'

--(5)查询“大学英语”,成绩在85分以上的学生的学号和姓名

select student.s_id,s_name,result

from student,s_c,course

where student.s_id=s_c.s_id and s_c.course_id=course.course_id and course_name='大学英语' and result>85

--(6)查询讲授“大学英语”的教师的姓名和所在部门名-----------交叉链接

select t_name,dep_name

from department,teacher,t_c

where department.dep_id=teacher.dep_id and teacher.t_id=t_c.t_id and course_id='大学英语'

--(7)查询与“刘清华”在同一部门的其他教师的姓名

select b.t_name

from teacher a,teacher b

where a.t_id=b.t_id and a.t_name='刘清华'and b.t_name<>'刘清华'

--七、子查询(嵌套查询)

--(1)查询选修0003号课程且成绩低于该门课程平均分的学生的学号

select s_id

from s_c

where course_id='0003'and result<(select avg(result)from s_c where course_id='0003')

--(2)查询年龄高于20160101班所有学生年龄的其他班的学生的学号和姓名

select s_id,s_name

from student

where c_id <>'20160101' and

s_borndate

--(3)查询所有成绩大于90分的学生的姓名

select s_name

from student

where s_id in(select s_id from s_c where result>90)

--(4)查询没有选修大学英语的学生的学号和姓名

select s_id,s_name

from student

where s_id not in (select s_id from s_c where course_id=(select course_id from course where course_name='大学英语'))

--(5)查询成绩高于平均分的学生的学号和课程号

select s_id,course_id

from s_c

where result >all(select avg(result) from s_c)

--(6)查询未选修任何课程的学生的学号和姓名

select s_id,s_name

from student

where not exists (select * from s_c where s_c.s_id=student.s_id)

--(7)查询所有工资高于平均工资的教师的教师名

select t_name

from teacher

where t_salary>(select avg(t_salary)from teacher)

--(8)查询年龄比“白沧铭”小的学生的姓名,结果按学号降序排序udsfdvq c

select s_name

from student

where (select year(getdate())-year(s_borndate) from student where s_name='白沧铭')

--(9)查询出生日期大于所有女同学的出生日期的男同学的姓名