sql server


文章目录

  • sql server
  • 数据库的语句操作
  • 数据表的语句操作
  • 单表查询（1）
  • 单表查询(2)
  • 连接查询
  • 子查询
  • 视图操作
  • 索引
  • 查询的综合应用
  • 综合练习
  • 数据操纵和视图练习


数据库的语句操作

任务二：用SQL语句完成下列任务：

1、 创建数据库TEST2，它有1个数据文件，名为data, 初始大小为4MB，按10%增长, 增长不受限制；还有1个日志文件，初始大小为3MB，按2增长MB, 最大大小为50 MB。

CREATE DATABASE TEST2
ON  PRIMARY 
( NAME = 'data', FILENAME = 'D:\姓名\data.mdf' ,
 SIZE = 4MB,
 MAXSIZE = UNLIMITED,
 FILEGROWTH = 10%)
 LOG ON 
( NAME = 'TEST2_log', 
FILENAME = 'D:\姓名\TEST2_log.ldf' ,
 SIZE =3MB,
  MAXSIZE = 50MB ,
   FILEGROWTH = 2MB)
 GO

2、 为TEST2增加两个数据test2_data1和test2_data2文件，初始大小分别为3MB,4MB，按1MB和10%增长, 增长均不受限制（即最大大小不限制）

ALTER DATABASE TEST2
ADD FILE (
NAME =test2_data1 ,
FILENAME='D:\姓名\test2_data1.ndf',
SIZE =3MB,
MAXSIZE=UNLIMITED,
FILEGROWTH=1MB
),
(
NAME =test2_data2,
FILENAME='D:\姓名\test2_data2.ndf',
SIZE=4MB,
MAXSIZE=UNLIMITED,
FILEGROWTH=10%
)

3、为TEST2增加两个日志文件，初始大小均为3MB,按1MB和10%增长，增长均不受限制（即最大大小不限制）。

alter database TEST2
add log file(
name=TEST_log1,
filename='D:\xm\TEST_log1.ldf',
size=3mb,
maxsize=unlimited,
filegrowth=1mb
),(
name=TEST_log2,
filename='D:\xm\TEST_log2.ldf',
size=3mb,
maxsize=unlimited,
filegrowth=10%
)
  1. 为TEST2增加一个文件组FGROUP1，并增加一个数据文件test2_data3，初始大小为10mb, 最大大小为100mb， 增长方式按5%增长，将此数据文件加入到FGROUP1中。
alter database TEST2
add filegroup FILEGROUP1
alter database TEST2
add file (
name=test2_data3,
filename='D:\xm\test2_data3.ndf',
size=10mb,
maxsize=100mb,
filegrowth=5%
)to filegroup FILEGROUP1

5、删除文件组FGROUP1。

alter database TEST2
remove file test2_data3
alter database TEST2
remove filegroup FILEGROUP1

6、修改数据文件TEST2_data1，将其初始大小改为4MB，增长方式设为按2MB增长。修改数据文件TEST2_data2的最大大小，将其设置为100MB。

alter database TEST2
modify file
(
name=TEST2_data1,
size=4mb,
filegrowth=2mb
)
alter database TEST2
modify file
(
name=TEST2_data2,
maxsize=100mb
)

7、删除数据库TEST2 。

drop database TEST2

数据表的语句操作

任务一：

1、用T-SQL语言在职员管理数据库ZYGL中创建以下三张数据表：职员表、部门表、工资表。

表1 职员表

列名

数据类型

长度

是否允许空值

说明

员工号

定长字符型（char）

3

×

主键

姓名

定长字符型（char）

8

×

性别

定长字符型（char）

2

×

要检查是否为“男”或“女”

出生日期

smalldatetime

√

手机号码

定长字符型（char）

11

√

工龄

微整型（tinyint）

√

应在0~35的范围

部门号

定长字符型（char）

2

×

要参照部门表的部门号

备注

文本型（text）

√

create table 职员表
(
员工号 char(3) constraint 员工号 not null constraint 员工号 primary key,
姓名 char(8) constraint 姓名 not null,
性别 char(2) constraint 性别 not null default '男' constraint 性别 check(性别 in ('男','女')),
出生日期 smalldatetime constraint 出生日期 null,
手机号码 char(11) constraint 手机号码 null,
工龄 tinyint constraint 工龄 null constraint 工龄 check (工龄>=0 and 工龄<=35),
部门号 char(2)constraint 部门号 not null foreign key references 部门表(部门号),
备注 text constraint 备注 null,
)

表2 部门表

列名

数据类型

长度

是否允许空值

说明

部门号

定长字符型（char）

2

×

主键

部门名

定长字符型（char）

10

×

电话

定长字符型（char）

4

√

create table 部门表
(
部门号 char(2) constraint 部门号 not null constraint 部门号 primary key,
部门名 char(10) constraint 部门名 not null,
电话 char(4) constraint 电话 null
)

表3 工资表

列名

数据类型

长度

是否允许空值

说明

员工号

定长字符型（char）

3

×

要参照职员表的员工号

基本工资

精确数据类型decimal（7，2）

5

√

津贴

精确数据类型decimal（5，2）

5

√

三金扣款

精确数据类型decimal（6，2）

5

√

应发工资

精确数据类型decimal（7，2）

5

√

为基本工资+津贴

实发工资

精确数据类型decimal（7，2）

5

√

为基本工资+津贴-三金扣款

create table 工资表
(
员工号 char(3) constraint 员工号 not null foreign key references 职员表(员工号),
基本工资 decimal(7,2) constraint 基本工资 null ,
津贴  decimal(5,2) constraint 津贴  null,
三金扣款  decimal(6,2) constraint 三金扣款 null, 
应发工资  as 基本工资+津贴,
实发工资  as 基本工资+津贴+三金扣款,
)

2、向职员表中增加“籍贯”这列，数据类型为字符类型，且要求非空。

alter table 职员表
add 籍贯 char(10) constraint 籍贯 not null

3、删除职员表中“手机号码”这列。

alter table 职员表
drop  column 手机号码

4、将部门表的部门名数据类型改为nchar(15)。

alter table 部门表
alter column 部门名 char(15)

5、给职员表增加一个约束，要求出生日在1985-01-01以前。

alter table 职员表
add 
constraint 出生日期 check(出生日期<=1985-01-01)

6、向这三个表中录入前3行数据。

insert into 部门表(部门号,部门名,电话)
values
('01','学习部','1111'),
('02','活动部','2222'),
('03','组织部','3333')

insert into 职员表(员工号,姓名,性别,工龄,部门号,备注,籍贯)
values
('01','一','男','5','01','无','湖北武汉'),
('02','二','女','6','02','无','湖北黄石'),
('03','三','男','7','03','无','湖北黄冈')

insert into 工资表(员工号,基本工资,津贴,三金扣款)
values
('01','2000','600','1000'),
('02','3000','500','1000'),
('03','4000','700','1000')

7、删除部门表

alter table 职员表
drop constraint FK__职员表__部门号__060DEAE8

drop table 部门表

任务二：

附加数据库SCD，用SQL完成下列任务：

1、学校又增加了两个系部：一个物理系，编号006，一个经济系，编号008。

insert into department
values 
('006','物理系'),
('008','经济系')

2、计算机系又增加了两个班：一个班号103，专业名物联网，入学年份2012；一个班号104，专业名网络工程，入学年份2012.

insert into dbo.class (classid,sdeptname,departid, studydate)
values
('103','物联网','002','2012'),
('104','网络工程·','002','2012')

3、新增进了一个同学，学号2012101，姓名王燕。

insert into dbo.student(sno,sname)
values('2012101' ,'Эѕбр')

4、向class表中增加一个约束，以限制studydate的取值范围为2003-2012，并忽略对原有数据的检查。

alter table class
add constraint class_student check(studydate between 2003and 2012 )

5、将入学年份在2005年以前的班级删除。

alter table student
drop constraint FK__student__classid__0519C6AF

delete  
from class
where studydate<2005

6、学生王琳转到101班，请更新相关的表。

update student 
set classid = 101
where sname ='ЭѕСе'

7、为每个学生的年龄增加1岁，请更新相关的表。

update student
set age =age+1

8、删除student表中的age 字段

alter table student
drop constraint CK__student__age__03317E3D

alter table student
drop constraint DF__student__age__0425A276

use scd
alter table student
drop column age

单表查询（1）

附件数据库CJGL，对数据库进行查询。

1、 查询全体学生的学号与专业名。

use CJGL
select 学号,专业名
from XS

2、 查询全体学生的详细记录

select *
from xs

3、 查全体学生的姓名及其出生日期。

select 姓名,出生日期
from xs

4、 查询选修了课程的学生学号。

select 学号
from xs_kc

5、 查询身高在172以上的学生姓名及其出生年份。

select 姓名,出生日期
from xs
where 身高>172

6、 查询2000年出生的学生的姓名、性别。

use cjgl
select 姓名,性别
from xs 
where 出生日期>'1999-12-31' and 出生日期<='2000-12-31'

7、 查询2000年以外出生的学生姓名、性别。

use cjgl
select 姓名,性别
from xs 
where 出生日期<='1999-12-31' or 出生日期>'2000-12-31'

8、 查询通信工程、财务管理和机电一体化这三个专业的学生情况。

select *
from xs
where 专业名 in ('通信工程','财务管理','机电一体化')

9、 查询既不是通信工程、财务管理，也不机电一体化专业的学生情况。

select *
from xs
where 专业名  not in ('通信工程','财务管理','机电一体化')

10、 查询所有的非党员的详细情况。

use CJGL
select *
from xs
where 党员否='0';

11、 查考试成绩大于等于85的学生的学号。

use CJGL
select distinct 学号
from xs_kc
where 成绩>=85;

12、 查询所有姓刘学生的姓名、学号和性别。

use CJGL
select 姓名,学号,性别
from xs
where 姓名 like '刘%';

13、 查询姓"林一"且全名为三个汉字的学生的姓名。

use CJGL
select 姓名
from xs
where 姓名 like '林一_';

14、 查询名字中第2个字为"小"字的学生的姓名和学号。

use CJGL
select 姓名,学号
from xs
where 姓名 like '_小%';

15、 查询所有不姓刘的学生姓名。

use CJGL
select 姓名
from xs
where 姓名 not like '刘%';

16、 查询学号的最后一位为介于数字1－3之间的学生信息。

use CJGL
select *
from xs
where 学号 like '_____[123]%';

17、 某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号。

use CJGL
select 学号,课程号
from xs_kc
where 成绩 is null;

18、 查所有有成绩的学生学号和课程号。

use CJGL
select 学号,课程号
from xs_kc
where 成绩 is not null;

单表查询(2)

\1. 检索所有职工的相关信息。

use ZYGL
select *
from 职员表

\2. 检索在1980年后出生的所有的女职工，且按工龄降序排列。

use ZYGL
select *
from 职员表
where 出生日期>'1980-12-31' and
      性别='女'
order by 工龄 desc

\3. 检索‘01’号部门，且工龄超过10年的员工信息。

use ZYGL
select *
from 职员表
where 部门号='01' and 工龄>10;

\4. 检索基本工资超过1600，且应发工资超过2000的员工的员工号及其实发工资

use ZYGL
select *
from 职员表
where 部门号='01' and 工龄>10;

\5. 检索姓‘张’，名字中带有‘英’字的，且共有三个字的员工姓名

use ZYGL
select 姓名
from 职员表
where 姓名 like '张_英%' or 姓名 like '张英_%';

\6. 检索电话不为空的部门信息。

use ZYGL
select *
from 部门表
where 电话 is not null;

\7. 检索‘01’‘02’‘03’号部门所有员工信息

use ZYGL
select *
from 职员表
where 部门号 in ('01','02','03');

\8. 统计该公司一共有多少个部门

use ZYGL
select COUNT(部门号) as 部门数
from 部门表

\9. 统计工龄在10年以上（含10年）的女职工的人数。

use ZYGL
select COUNT(员工号) as 人数
from 职员表
where 工龄>=10 and 性别='女';

\10. 统计该公司员工的平均基本工资是多少

use ZYGL
select AVG(基本工资) as 平均基本工资
from 工资表

\11. 统计基本工资超过1500的职工人数

use ZYGL
select COUNT(员工号) as 职工人数
from 工资表
where 基本工资>'1500';

\12. 统计男女职工各自的人数

use ZYGL
select 性别,COUNT(*) as 人数
from 职员表
group by 性别

\13. 统计手机号码以‘138’开头的职工人数

use ZYGL
select COUNT(员工号) as 职工人数
from 职员表
where 手机号码 like '138%';

\14. 统计该公司员工每月总共所扣除的三金扣款是多少

use ZYGL
select SUM(三金扣款) as 总共扣除的三金扣款
from 工资表

\15. 统计01号部门且手机号码不为空的职工人数

use ZYGL
select COUNT(*) as 职工人数
from 职员表
where 部门号='01' and
      手机号码 is not null;

\16. 统计各个部门工龄在10年以上（含10年）的职工人数

use ZYGL
select COUNT(*) as 职工人数
from 职员表
where 工龄>=10
group by 部门号

连接查询

\1. 检索李平的选修情况，包括学号、姓名、所选课程的课程名及成绩。

use CJGL
select xs.学号,姓名,课程名,成绩
from xs,xs_kc,kc
where 姓名='李平' and xs.学号=xs_kc.学号
      and xs_kc.课程号=kc.课程号;

\2. 检索‘离散数学’这门课程被哪些学生所选修，显示其姓名及成绩,结果按成绩降序排列。

use CJGL
select 姓名,成绩
from xs,xs_kc,kc
where 课程名='离散数学'
      and xs_kc.课程号=kc.课程号
      and xs.学号=xs_kc.学号
order by 成绩 desc;

\3. 检索选修了‘离散数学’这门课程，且成绩在80分以上的学生姓名及成绩，结果按成绩的降序排列。

use CJGL
select 姓名,成绩
from xs,xs_kc,kc
where 课程名='离散数学'
      and 成绩>80
      and xs_kc.课程号=kc.课程号
      and xs.学号=xs_kc.学号
order by 成绩 desc;

\4. 检索林一番同学的‘ 操作系统’成绩。

use CJGL
select 成绩
from xs,xs_kc,kc
where 姓名='林一番'
      and 课程名='操作系统'
      and xs.学号=xs_kc.学号
      and xs_kc.课程号=kc.课程号;

\5. 查询选修了’102’号课程的学生的姓名及成绩。

use CJGL
select 姓名,成绩
from xs,xs_kc
where 课程号='102'
      and xs.学号=xs_kc.学号;

\6. 查找选修了206课程且成绩在75分以上的学生姓名及成绩。

use CJGL
select 姓名,成绩
from xs,xs_kc
where 课程号='206'
      and 成绩>75
      and xs.学号=xs_kc.学号;

\7. 检索每个已经选修课程的学生的学号，姓名及其总分。

use CJGL
select xs.学号,姓名,SUM(成绩) as 总分
from xs,xs_kc
where xs.学号=xs_kc.学号
      and 课程号 is not null
group by xs.学号,姓名

\8. 检索每个被选修的课程的课程号，课程名及这门课的平均分。

use CJGL
select xs_kc.课程号,课程名,AVG(成绩) as 平均分
from xs_kc,kc
where xs_kc.课程号=kc.课程号
group by xs_kc.课程号,课程名

\9. 检索林一番同学目前已获得了几个学分。

use CJGL
select sum(学分) as 已获得学分
from xs,xs_kc,kc
where 姓名='林一番' and xs.学号=xs_kc.学号
      and xs_kc.课程号=kc.课程号;

\10. 查询王一鸣所选修的课程的课程名及成绩。

use CJGL
select 课程名,成绩
from xs,xs_kc,kc
where 姓名='王一鸣' and xs.学号=xs_kc.学号
      and xs_kc.课程号=kc.课程号

\11. 检索目前每个已经选修课程的学生的学号，姓名及其获得的学分。

use CJGL
select xs.学号,姓名,SUM(学分) as 已获得学分
from xs,xs_kc,kc
where  xs.学号=xs_kc.学号
      and xs_kc.课程号=kc.课程号
group by xs.学号,姓名

\12. 查找与李林同学在同一专业学习的学生姓名

use CJGL
select a.姓名
from xs as a,xs as b
where  a.专业名=b.专业名 and b.姓名='李林' ;

\13. 检索目前学分超过5(含5)的学生的学号，姓名及其总分。

use CJGL
select xs.学号,姓名,sum(成绩) as 总分
from xs
left join xs_kc
on xs.学号=xs_kc.学号
left join kc
on xs_kc.课程号=kc.课程号
   and 学分>=5
group by xs.学号,姓名

\14. 查询所有党员所选修课程的课程号及成绩。

use CJGL 
select 课程号,成绩
from xs
left join xs_kc on xs_kc.学号=xs.学号
     and 党员否='1'
group by 课程号,成绩

\15. 查询所有党员所选课程的课程名。

use CJGL 
select 课程名
from xs
left join xs_kc on xs_kc.学号=xs.学号
left join kc on kc.课程号=xs_kc.课程号
     and 党员否='1'
group by 课程名

\16. 查找所有学生的选课情况。查询结果要包含学号、专业名、课程号。若学生未选修任何课，也要包括其情况。

use CJGL 
select xs.学号,专业名,xs_kc.课程号
from xs
left join xs_kc on xs_kc.学号=xs.学号
left join kc on kc.课程号=xs_kc.课程号
group by xs.学号,专业名,xs_kc.课程号

\17. 查找被选修了的课程的情况。要求包含学号、课程号、课程名。若某门课没有人选，也要显示其课程号和课程名。

use CJGL
select xs.学号,kc.课程号,课程名
from kc
left join xs_kc on kc.课程号=xs_kc.课程号
left join xs on xs.学号=xs_kc.学号

任务二:附加数据库ZYGL,完成下列任务。

\1. 查询每个职员的基本情况及其薪水详细情况。

use ZYGL
select *
from 职员表,工资表
where 职员表.员工号=工资表.员工号

\2. 查询刘裕所属部门的部门名。

use ZYGL
select 部门名
from 部门表,职员表
where 职员表.部门号=部门表.部门号
      and 姓名='刘裕'

\3. 查询销售科实发工资在2000以上(含2000)的职员的姓名及其薪水详细情况。

use ZYGL
select 姓名,工资表.*
from 职员表,工资表,部门表
where  部门名='销售科' and 实发工资>=2000
       and 职员表.员工号=工资表.员工号
       and 职员表.部门号=部门表.部门号;

\4. 按实际收入由低到高排列，显示各职员的姓名、性别、工龄、应发工资和实发工资。

use ZYGL
select 姓名,性别,工龄,应发工资,实发工资
from 职员表,工资表
where 职员表.员工号=工资表.员工号
order by 实发工资 asc

\5. 求销售科职员的总人数。

use ZYGL
select COUNT(员工号) as 总人数
from 职员表,部门表
where 职员表.部门号=部门表.部门号
      and 部门名='销售科'

\6. 求各个部门的部门名及其职工人数。

use ZYGL
select 部门名,COUNT(员工号) as 职工人数
from 职员表,部门表
where 职员表.部门号=部门表.部门号
group by 部门名

\7. 求销售科职员的平均实际收入。

use ZYGL
select AVG(实发工资) as 平均实际收入
from 工资表,部门表,职员表
where 部门名='销售科'
      and 职员表.部门号=部门表.部门号
      and 职员表.员工号=工资表.员工号

子查询

1、 查询选修206号课程且成绩在90分以上的所有学生的学号、姓名。

use CJGL
select 学号,姓名
from xs
where 学号 in (select 学号
              from xs_kc
              where 成绩>90 and 课程号='206')

2、 查找未选修101号这门课的学生的姓名和专业名。

use CJGL
select 姓名,专业名
from xs
where (xs.学号 not in(select 学号
                     from xs_kc
                     where 课程号='101'))

3、 查询与“赵勇”在同一个专业学习的学生信息。

use CJGL
select *
from xs
where 专业名 = (select 专业名
                     from xs
                     where 姓名='赵勇')

4、 查询选修了课程名为“离散数学”的学生学号、姓名和所在专业名。

use CJGL
select 学号,姓名,专业名
from xs
where 学号 in(select 学号
              from xs_kc
              where 课程号 = (select 课程号
                             from kc
                             where 课程名='离散数学'))

5、 查询其他专业中比计算机应用所有学生年龄均大的学生姓名。

use CJGL
select 姓名
from xs
where 出生日期 < (select min(出生日期)
                 from  xs
                 where 专业名='计算机应用')
      and (专业名 <> '计算机应用')

6、 查询既选修了“301”号课程又选修了“104”号课程的学生的姓名。

use CJGL
select 姓名
from xs
where 学号 in (select 学号
              from xs_kc
              where 课程号='104' 
              and 学号 in(select 学号
                         from  xs_kc
                         where 课程号='301'))

7、 检索选修了301号课程，且分数最高的学生姓名。

use CJGL
select 姓名
from xs
where 学号 in (select 学号
              from xs_kc
              where 课程号='301' 
                    and 成绩 =(select MAX(成绩)
                         from  xs_kc
                         where 课程号='301'))

8、 检索选修了“计算机基础”课程的学生姓名。

use CJGL
select 姓名
from xs
where 学号 in (select 学号
              from xs_kc
              where  课程号 =(select 课程号
                             from  kc
                             where 课程名='计算机基础'))

9、 查询101号这门课的成绩在该课程平均分以上的学生姓名。

use CJGL
select 姓名
from xs
where 学号 in (select 学号
              from xs_kc
              where  成绩>(select AVG(成绩)
                           from  xs_kc
                           where 课程号='101'))

10、 检索选修了2门（含2门）以上课程的学生姓名。

use CJGL
select 姓名
from xs
where 学号 in(
	select 学号
	from xs_kc
	group by 学号
	having COUNT(课程号)>=2)

11、 检索平均成绩在75分以上的学生姓名，性别和专业。

use CJGL
select 姓名,性别,专业名
from xs
where 学号 in(
	select 学号
	from xs_kc
	group by 学号
	having AVG(成绩)>75)

12、 检索“林一番”同学不学的课程号。

use CJGL
select 课程号
from kc
where 课程号 not in(
	select 课程号
	from xs_kc
	where 学号 =(
		select 学号
		from xs
		where 姓名='林一番'))

13、 查询只有1门课不及格的学生姓名。

use CJGL
select 姓名
from xs
where 学号 in(
	select 学号
	from xs_kc
	where 成绩<60
	group by 学号
	having COUNT(成绩)=1)

14、 检索选修课程包含“程明”同学所选课程之一的学生学号。

use CJGL
select 学号
from xs_kc
where 课程号 in(
	select 课程号
	from xs_kc
	where 学号=(
		select 学号
		from xs
		where 姓名='程明'))

15、 查询每门课程的课程号，课程名及最高分是多少。

use CJGL
select xs_kc.课程号,课程名,MAX(成绩) as 最高分
from xs_kc,kc
where xs_kc.课程号=kc.课程号 and
   成绩=(
	select MAX(成绩)
	from xs_kc
	where xs_kc.课程号=kc.课程号
	)
group by xs_kc.课程号,课程名

16、 查询各个已选课学生的学号，姓名及获得的最高成绩是多少。

use CJGL
select xs.学号,姓名,MAX(成绩) as 最高分
from xs,xs_kc
where xs.学号=xs_kc.学号 and
   成绩=(
	select MAX(成绩)
	from xs_kc
	where xs_kc.学号=xs.学号
	)
group by xs.学号,姓名

17、 查询没有课程不及格的学生姓名。

use CJGL
select 姓名
from xs
where  学号 not in(
	select 学号
	from xs_kc
	where 成绩<60)

18、 查找课程号206的成绩不低于课程号101的最低成绩的学生学号。

use CJGL
select 学号
from xs_kc
where 课程号 in (
	select 课程号
	from xs_kc
	where 课程号='206' and 成绩>=(
		select MIN(成绩)
		from xs_kc
		where 课程号='101'))

19、 查询所有党员所选修课程的课程号和课程名。

use CJGL
select 课程号,课程名
from kc
where 课程号 in (
	select 课程号
	from xs_kc
	where 学号 in(
		select 学号
		from xs
		where 党员否='1'))

20、查询李林所选修的课程的课程名。

use CJGL
select 课程名
from kc
where 课程号 in (
	select 课程号
	from xs_kc
	where 学号 =(
		select 学号
		from xs
		where 姓名='李林'))

视图操作

1． 创建通信工程专业学生的视图ce_xs

create view ce_xs
as 
	select 学号,姓名,专业名,性别,出生日期,身高,党员否,备注
	from xs
	where 专业名='通信工程'

2． 创建通信工程专业学生的平均成绩视图ce_kc_AVG,包括学号（在视图中列名为num）和平均成绩（在视图中列名为score_avg）

create view ce_kc_AVG(num,score_avg)
as
	select xs.学号,AVG(成绩) 
	from xs,xs_kc
	where xs.学号=xs_kc.学号 and 专业名='通信工程'
group by xs.学号

3． 在视图 ce_kc_AVG上创建通信工程专业平均成绩在70分以上的学生视图cs_kc_avg1

create view cs_kc_avg1
as
	select *
	from ce_kc_AVG
	where score_avg>70

4． 向ce_xs视图中插入一条记录：（'061404’，’刘林’,’通信工程’，’男’，’1988-8-8’,185.5,0,NULL）

insert into ce_xs(学号,姓名,专业名,性别,出生日期,身高,党员否,备注)
values ('061404','刘林','通信工程','男','1988-8-8',185.5,0,NULL)

5． 将ce_xs视图中所有学生的备注列改成“提前获得2学分

update ce_xs
set 备注='提前获得2学分'

6． 创建一个视图ce_kc，包括计通信工程专业各学生的学号、选修课程号、成绩

create view ce_kc(学号,课程号,成绩)
as
	select xs.学号,课程号,成绩
	from xs,xs_kc
	where xs.学号=xs_kc.学号 and 专业名='通信工程'

7． 将ce_kc视图中192101学生的101课程的成绩改为90

update ce_kc
set 成绩=90
where 学号='192101' and 课程号='101'

8． 删除ce_xs视图中的女同学记录。

delete 
from ce_xs
where 性别='女'

索引

1、 为xs表的姓名列创建索引。

create nonclustered index xs_姓名 on xs(姓名)

2、 为xs表按姓名的升序，出生日期的降序建立索引。

create nonclustered index xs_姓名_出生日期 on xs(姓名 asc,出生日期 desc)

3、 根据kc表的课程名列降序创建唯一索引。

create nonclustered index kc_课程名 on kc(课程名 desc)

4、 根据xs表生成xs1表，在xs1表的学号列创建唯一聚集索引。

select * 
into xs1
from xs
create clustered index xs1_学号 on xs1(学号)

5、 删除kc表课程名列上的索引。

drop index kc_课程名 on kc

6、 使用存储过程查看xs表上所有的索引。

sp_helpindex xs

查询的综合应用

任务：附加数据库CJGL, 完成下列习题

\1. 查询选修课了101号课程的学生学号、姓名及成绩，且按分数的降序排列

use CJGL
select xs.学号,姓名,成绩 
from xs,xs_kc
where 课程号='101' and xs.学号=xs_kc.学号
order by 成绩 desc

\2. 查询全体学生，结果按系的专业名升序排列，年龄的降序排列，查询结果返回前5条记录。

use CJGL
select top 5 *
from xs
order by  专业名 asc,出生日期 desc

\3. 查询通信工程专业学生的总人数

use CJGL 
select COUNT(学号) as 总人数
from xs
where 专业名='通信工程'

\4. 查询选修了课程的学生人数

use CJGL
select COUNT(distinct xs.学号) as 学生人数
from xs,xs_kc,kc
where xs_kc.课程号=kc.课程号
      and xs.学号=xs_kc.学号

\5. 查询李林同学所选课程的学分总数

use CJGL
 select sum(学分) as 学分总数
 from xs,xs_kc,kc
 where 姓名='李林' 
	   and xs.学号=xs_kc.学号
	   and xs_kc.课程号=kc.课程号

\6. 求各个已被选修的课程的课程号及相应选课人数，平均分，最高分，最低分

use CJGL
select 课程号,COUNT(学号) as 选课人数,
       AVG(成绩) as 平均分,
       MAX(成绩) as 最高分,
       MIN(成绩) as 最低分
from xs_kc
group by 课程号

\7. 查询选修了2门以上(含2门)课程的学生姓名，且在查询结果集中将姓名字段显示为sname

use CJGL
select 姓名 as sname
from xs
where 学号 in (
	select 学号
	from xs_kc
	where xs.学号=xs_kc.学号
	group by 学号
	having COUNT(课程号)>=2
	)

\8. 查询平均成绩超过80分(含80)的学生的学号、姓名及其总分

use CJGL
select xs.学号,姓名,SUM(成绩) as 总分
from xs,xs_kc
where xs.学号=xs_kc.学号
group by xs.学号,姓名
having AVG(成绩)>=80

\9. 检索课程号以’1’至’3’结尾的且平均成绩在80分以上(含80)的课程的课程号、课程名、及其平均成绩

use CJGL
select xs_kc.课程号,课程名,avg(成绩) as 平均成绩
from xs_kc,kc
where xs_kc.课程号=kc.课程号
      and xs_kc.课程号 like '__[123]%' 
group by xs_kc.课程号,课程名
having AVG(成绩)>=80

\10. 检索年龄在18岁以上(含18)的人数超过2人(含2人)的专业的专业名及相应人数

use CJGL
select 专业名,count(学号) as 相应人数
from xs
where 2021-year(出生日期)>=18
group by 专业名
having COUNT(学号)>=2

\11. 查询计算机基础不及格的男生名单

use CJGL
select 姓名
from xs,xs_kc,kc
where xs.学号=xs_kc.学号
      and 课程名='计算机基础'
      and 成绩<60

\12. 在XS表与 XS_KC表之间通过‘学号’做右外连接，现实已选课的学生的基本信息和他们的选课信息，同时显示没选课的学生的学生信息。

use CJGL
select *
from xs_kc
right outer join xs on xs_kc.学号=xs.学号

综合练习

\1. 创建一个名为“图书管理”的数据库，存储路径为D：\MSSQL\Data 。其中主文件大小为10mb, 最大大小为50mb, 每次增长15%；事务日志文件的大小为20mb, 最大大小为100mb, 文件每次增长10mb.

create database 图书管理
on
(
	name=图书管理_date,
	filename='d:\mssql\data.mdf',
	size=10mb,
	maxsize=50mb,
	filegrowth=15%
)
log on
(
	name=图书管理_log,
	filename='d:\mssql\data.ldf',
	size=20mb,
	maxsize=100mb,
	filegrowth=10mb
)

\2. 修改第1题中所建的数据库，修改主数据文件，将大小改为20mb, 最大大小为100mb。事务日志文件每次增长15mb.

alter database 图书管理
modify file
(
	name=图书管理_date,
	size=20mb,
	maxsize=100mb
)
alter database 图书管理
modify file
(
	name=图书管理_log,
	filegrowth=15mb
)

\3. 图书管理数据库中包含book、reader、borrow 3个表，各表的结构如下,

book

列名

数据类型

长度

是否为空

说明

booknum

char

5

×

书号

bookname

char

30

×

书名

booktype

float

20

√

类别

publisher

int

20

√

出版社

writer

char

8

√

作者

price

int

√

价格

reader(readernum, rname, sex, workplace, phone)

borrow(booknum, readernum, borrowdate, returndate)

1） reader和borrow属性类型及宽度自定（要求为属性选择合适的数据类型，长度，是否为空和缺省值），请用SQL语句创建这3个表。

create table book
(
	booknum char(5) not null,
	bookname char(30) not null,
	booktype float(20) ,
	publisher int,
	writer char(8),
	price int
)


create table borrow
(
	booknum char(5) not null,
	readernum char(20) not null,
	borrowdate char(20)  not null,
	returndate char(20) not null
) 

create table reader
(
	readernum char(20) not null,
	rname char(10) not null,
	sex nchar(2)  not null,
	workplace char(20),
	phone char(20) not null,
)

\2) 向reader中增加“age”这列，要求年龄在18-60岁间，缺省值为18岁； 对workplace 增加一个名为“ck_city”的检查约束，以限制workplace在洪山、武昌、硚口这3个地点中的某一个。

alter table reader
add 
age char(10)  default '18'
	constraint age_年龄 check(age between 18 and 60 ),
constraint ck_city 
	check(workplace in( '洪山','武昌' ,'硚口'))

3）删除reader中的 age 列。

alter table reader
drop constraint DF__reader__age__03317E3D
alter table reader
drop column age

\4. 用SQL语句实现如下查询

1） 查询borrow表中的前3条记录

use 图书管理
select top 3*
from borrow

2） 查询作者姓王的自然科学类图书的信息

use 图书管理
select *
from book
where writer like '王%'
      and booktype='自然科学类'

3） 查询浙江大学出版社的图书的平均定价

select AVG(price) as 平均定价
from book
where publisher='浙江大学出版社'

4） 查找书号为B0002的借阅者的姓名

select rname
from borrow,reader
where booknum='B0002' 
      and borrow.readernum=reader.readernum

5） 查询每种类型的图书的数量

select  booktype,COUNT(booknum) as 图书数量
from book
group by booktype

6） 定义自然科学类图书基本情况视图V_Science

create view V_Sciense
as
	select booknum,bookname,booktype,publisher,writer,price
	from book
	where booktype='自然科学类'

数据操纵和视图练习

学生作业管理数据库给定如表3.6、表3.7和表3.8所示的学生信息。

表3.6 学生表

学号

姓名

性别

专业班级

出生日期

联系电话

0433

张艳

女

生物04

1986-9-13

0496

李越

男

电子04

1984-2-23

1381290××××

0529

赵欣

男

会计05

1984-1-27

1350222××××

0531

张志国

男

生物05

1986-9-10

1331256××××

0538

于兰兰

女

生物05

1984-2-20

1331200××××

0591

王丽丽

女

电子05

1984-3-20

1332080××××

0592

王海强

男

电子05

1986-11-1

表3.7 课程表

课程号

课程名

学分数

学时数

任课教师

K001

计算机图形学

2.5

40

胡晶晶

K002

计算机应用基础

3

48

任泉

K006

数据结构

4

64

马跃先

M001

政治经济学

4

64

孔繁新

S001

高等数学

3

48

赵晓尘

表3.8 学生作业表

课程号

学号

作业1成绩

作业2成绩

作业3成绩

K001

0433

60

75

75

K001

0529

70

70

60

K001

0531

70

80

80

K001

0591

80

90

90

K002

0496

80

80

90

K002

0529

70

70

85

K002

0531

80

80

80

K002

0538

65

75

85

K002

0592

75

85

85

K006

0531

80

80

90

K006

0591

80

80

80

M001

0496

70

70

80

M001

0591

65

75

75

S001

0531

80

80

80

S001

0538

60

80

一、使用数据操纵完成以下任务（每一个任务都要给出SQL语句）

（1）在学生表中添加一条学生记录，其中，学号为0593，姓名为张乐，性别为男，专业班级为电子05。

insert into 学生表(学号,姓名,性别,专业班级,联系电话)
values('0593','张乐','男','电子','05')

（2）将所有课程的学分数变为原来的两倍。

update 课程表
set 学分数=2*学分数

（3）删除张乐的信息。

delete 
from 学生表
where 姓名='张乐'

二、根据学生作业管理数据库以及其中的学生表、课程表和学生作业表，进行以下操作（每一个任务都要给出SQL语句）。

1．创建一个电子05的学生视图（包括学号、姓名、性别、专业班级、出生日期）。

create view 电子05(学号,姓名,性别,专业班级,出生日期)
as
	select 学号,姓名,性别,专业班级,出生日期
	from 学生表
	where 专业班级='电子05'

2．创建一个生物05的学生作业情况视图（包括学号、姓名、课程名、作业1成绩、作业2成绩、作业3成绩）。

create view 生物05(学号,姓名,课程名,作业1成绩,作业2成绩,作业3成绩)
as
	select 学生表.学号,姓名,课程名,作业1成绩,作业2成绩,作业3成绩
	from 学生作业表,学生表,课程表
	where 专业班级='生物05'

3．创建一个学生作业平均成绩视图（包括学号、作业1平均成绩、作业2平均成绩、作业3平均成绩）。

create view avg_学生平时成绩
as
	select 学号,avg(作业成绩) as 作业平均成绩,avg(作业成绩) as 作业平均成绩,avg(作业成绩) as 作业平均成绩
	from 学生作业表
group by 学号

4．修改第2题中生物05的学生作业情况视图，将作业2成绩和作业3成绩去掉。

alter view 生物05
as 
 select 学号,姓名,课程名,作业成绩
 from 生物05

5．向电子05的学生视图中添加一条记录，其中学号为0596，姓名为赵亦，性别为男，专业班级为电子05，出生日期为1986-6-8（除了电子05的学生视图发生变化之外，看看学生表中发生了什么变化？）。

insert into 电子05(学号,姓名,性别,专业班级,出生日期)
values ('0596','赵亦','男','电子','1986-6-8')

6．将电子05的学生视图中赵亦的性别改为“女”（除了电子05的学生视图发生变化之外，看看学生表中发生了什么变化？）。

update 电子05
set 性别='女'
where 姓名='赵亦'

7．删除电子05的学生视图中赵亦的记录。

delete 
from 电子05
where 姓名='赵亦'

8．删除电子05的学生视图。

drop view 电子05

| 80 | 80 | 80 |
| M001 | 0496 | 70 | 70 | 80 |
| M001 | 0591 | 65 | 75 | 75 |
| S001 | 0531 | 80 | 80 | 80 |
| S001 | 0538 | 60 | | 80 |

一、使用数据操纵完成以下任务（每一个任务都要给出SQL语句）

（1）在学生表中添加一条学生记录，其中，学号为0593，姓名为张乐，性别为男，专业班级为电子05。

insert into 学生表(学号,姓名,性别,专业班级,联系电话)
values('0593','张乐','男','电子','05')

（2）将所有课程的学分数变为原来的两倍。

update 课程表
set 学分数=2*学分数

（3）删除张乐的信息。

delete 
from 学生表
where 姓名='张乐'

二、根据学生作业管理数据库以及其中的学生表、课程表和学生作业表，进行以下操作（每一个任务都要给出SQL语句）。

1．创建一个电子05的学生视图（包括学号、姓名、性别、专业班级、出生日期）。

create view 电子05(学号,姓名,性别,专业班级,出生日期)
as
	select 学号,姓名,性别,专业班级,出生日期
	from 学生表
	where 专业班级='电子05'

2．创建一个生物05的学生作业情况视图（包括学号、姓名、课程名、作业1成绩、作业2成绩、作业3成绩）。

create view 生物05(学号,姓名,课程名,作业1成绩,作业2成绩,作业3成绩)
as
	select 学生表.学号,姓名,课程名,作业1成绩,作业2成绩,作业3成绩
	from 学生作业表,学生表,课程表
	where 专业班级='生物05'

3．创建一个学生作业平均成绩视图（包括学号、作业1平均成绩、作业2平均成绩、作业3平均成绩）。

create view avg_学生平时成绩
as
	select 学号,avg(作业成绩) as 作业平均成绩,avg(作业成绩) as 作业平均成绩,avg(作业成绩) as 作业平均成绩
	from 学生作业表
group by 学号

4．修改第2题中生物05的学生作业情况视图，将作业2成绩和作业3成绩去掉。

alter view 生物05
as 
 select 学号,姓名,课程名,作业成绩
 from 生物05

5．向电子05的学生视图中添加一条记录，其中学号为0596，姓名为赵亦，性别为男，专业班级为电子05，出生日期为1986-6-8（除了电子05的学生视图发生变化之外，看看学生表中发生了什么变化？）。

insert into 电子05(学号,姓名,性别,专业班级,出生日期)
values ('0596','赵亦','男','电子','1986-6-8')

6．将电子05的学生视图中赵亦的性别改为“女”（除了电子05的学生视图发生变化之外，看看学生表中发生了什么变化？）。

update 电子05
set 性别='女'
where 姓名='赵亦'

7．删除电子05的学生视图中赵亦的记录。

delete 
from 电子05
where 姓名='赵亦'

8．删除电子05的学生视图。

drop view 电子05