SQL实例--查询、引索、视图

 

 

查询

--①在student表中，查询每个学生的学号、姓名、出生日期信息。

select 学号,姓名,出生日期from student

--②查询学号为的学生的姓名和家庭住址。

select 姓名,家庭住址from student where 学号='0002'

--③找出所有男同学的学号和姓名。

select 学号,姓名from student where 性别='男'

 

 

--①在grade表中查找分数在~90范围内的学生的学号和分数。

select 学号,分数from grade where 分数between 80 and 90

--②在grade表中查询课程编号为的学生的平均分。

select avg(分数) from grade where 课程编号='0003'

--③在grade表中查询学习各门课程的人数。

select 课程编号,count(*) 编号from grade group by  课程编号

--④将学生按出生日期由大到小排序。

select * from student order by 出生日期desc

--⑤查询所有姓“张”的学生的学号和姓名。

select 学号,姓名from student where 姓名like '张%'

 

 

--3．对student表，按性别顺序列出学生的学号、姓名、性别、

--出生日期及家庭住址，性别相同的按学号由小到大排序。

select 学号,姓名,性别,出生日期,家庭住址from student order by 性别,学号;

 

 

--4．使用GROUP BY查询子句列出

--平均成绩在分以上的学生的学号和平均成绩。

select 学号,avg(分数)平均成绩from grade group by 学号having avg(分数)>'80'; 

 

 

--5．使用UNION运算符将student表中姓“张”的学生的学号、姓名与

--course表的课程编号、课程名称返回在一个表中，且列名为u_编号、u_名称

select 学号,姓名from student where 姓名like '张%' UNION

select 课程编号 u_编号,课程名称u_名称from course   

--6．嵌套查询。

--①在student表中查找与“刘卫平”性别相同的所有学生的姓名、出生日期。

select 姓名,出生日期from student where 性别=(select 性别from student where 姓名='刘卫平' )

--②使用IN子查询查找所修课程编号为、的学生学号、姓名、性别。

select 学号,姓名,性别from student where 学号in (select 学号from grade where 课程编号='0002' or 课程编号='0005')

--③列出学号为的学生的分数比号的学生的最低分数高的课程编号和分数。

select 课程编号,分数from grade where  学号='0001' and 分数>(select min(分数) from grade where 学号='0002')

--④列出学号为的学生的分数比学号为的学生的最高成绩还要高的课程编号和分数。

select 课程编号,分数from grade where  学号='0001' and 分数>(select max(分数) from grade where 学号='0002')

 

-- 7．连接查询。

--①查询分数在~90范围内的学生的学号、姓名、分数。

select a.学号,姓名,分数from student a,grade b where a.学号=b.学号and 分数between 80 and 90

--②查询学习“C语言程序设计”课程的学生的学号、姓名、分数。

select a.学号,姓名,分数from student a,grade b,course c where a.学号=b.学号and 课程名称='C语言程序设计' and b.课程编号=c.课程编号

--③查询所有男同学的选课情况，要求列出学号、姓名、课程名称、分数。

select a.学号,姓名,课程名称,分数from student a,grade b,course c where a.学号=b.学号and 性别='男' and b.课程编号=c.课程编号

--④查询每个学生的所选课程的最高成绩，要求列出学号、姓名、课程编号、分数。

select a.学号,姓名,课程编号,分数from student a, grade b where a.学号=b.学号and 分数in (select max(分数) from grade  group by 学号)

--⑤查询所有学生的总成绩，要求列出学号、姓名、总成绩，没有选修课程的学生的总成绩为空。

--提示：使用左外连接。

select a.学号,姓名,总成绩from student a left join(select 学号,sum(分数) 总成绩from grade group by 学号) b 

on a.学号=b.学号

--⑥为grade表添加数据行：学号为、课程编号为、分数为。查询所有课程的选修情况，要求列出课程编号、课程名称、选修人数，course表中没有的课程列值为空。

--提示：使用右外连接。

insert into grade values ('0004','0006',76)

select a.课程编号,课程名称,选修人数from course a right join(select 课程编号,count(学号) 选修人数from grade group by 课程编号) b on a.课程编号=b.课程编号

 

索引

--（1）使用Transact-SQL语句按course表的课程编号列创建唯一性索引。

create unique index cou_in on course(课程编号)

 

--（2）分别使用SQL Server管理平台和Transact-SQL语句为studentsdb数据库的grade表的“分数”字段创建一个非聚集索引，命名为grade_index。

 create index grade_index on grade(分数)

 

--（3）为studentsdb数据库的grade表的“学号”和“课程编号”字段创建一个复合唯一索引，命名为grade_id_c_ind。

 create unique index grade_id_c_ind on grade(学号,课程编号)

 

--（4）使用SQL Server管理平台对grade表创建一个聚集索引和唯一索引。

create unique clustered index gr_inde on grade(学号,课程编号) 

 

--（5）使用系统存储过程sp_rename将索引grade_index更名为grade_ind。

sp_rename 'grade.grade_index','grade_ind','INDEX'

 

--（6）分别使用SQL Server管理平台和Transact-SQL语句DROP INDEX删除索引grade_ind。

drop index grade.grade_ind  

视图

--（7）在studentsdb数据库中，以student表为基础，使用SQL Server管理平台建立

---名为v_stu_i的视图，使视图显示学生姓名、性别、家庭住址。

create view v_stu_i as

select 姓名,性别,家庭住址from student 

 

--（8）在studentsdb数据库中，使用Transact-SQL语句CREATE VIEW建立一个名为v_stu_c的视图，

---显示学生的学号、姓名、所学课程的课程编号，并利用视图查询学号为的学生情况。

 

create view v_stu_c as

select a.学号,姓名,课程编号from student a,grade b where  a.学号=b.学号

select * from v_stu_c where 学号='0003'

--（9）基于student表、course表和grade表，建立一个名为v_stu_g的视图，

---视图中具有所有学生的学号、姓名、课程名称、分数。

---使用视图v_stu_g查询学号为的学生的所有课程与成绩，如图所示。

---图 学号为的学生的视图信息

create view v_stu_g as

select a.学号,姓名,课程名称,分数from student a,course b,grade c where a.学号=c.学号and b.课程编号=c.课程编号

select *from v_stu_g   where 学号='0001'

 

--（10）分别使用SQL Server管理平台和Transact-SQL语句修改视图v_stu_c，

---使之显示学号、姓名、每个学生所学课程数目。

alter view 

select distinct  b.学号,姓名,课程数目

from (select 学号, count(学号) 课程数目from v_stu_c group by 学号) as a(学号,课程数目),v_stu_c  b

where a.学号=b.学号

 

---（11）使用Transact-SQL语句ALTER VIEW修改视图v_stu_i，使其具有列名学号、姓名、性别。

ALTER VIEW v_stu_i

AS SELECT 学号,姓名,性别FROM student

 

---（12）利用视图v_stu_i为student表添加一行数据：学号为、姓名为陈婷、性别为女。

insert into v_stu_i(学号,姓名,性别) values('0015','陆婷','女') 

 

---（13）利用视图v_stu_i删除学号为的学生记录。

delete from v_stu_i where 学号='0015'

---（14）利用视图v_stu_g修改姓名为刘卫平的学生的高等数学的分数为。

update v_stu_g set 分数='84' where 姓名='刘卫平' and 课程名称='高等数学'