SQL期末考试复习资料 题解.docx

SQL期末考试复习资料 题解一、 填空题 Select 的各子句的作用 Select 表示仅仅读取信息而不修改数据信息 From 指定要读取数据所在的一个表或几个表达名称 Where 用来设定返回记录的限制条件 Order by 用来定义返回数据从排列顺序 Group by 用于聚合信息 Having 用于带有group by 子句的查询中，应用于分组的聚合值 For xml 输出xml Option 优化运行sql 查询的方法 Distinct 和 all 用于返回无重复的行或所有行 Create table语句中各类约束条件的实现关键字 CONSTRAINT 约束的开始 PRIMARY KEY 主键约束 UNIQUE 唯一约束 FOREIGN KEY REFERENCES 外键约束 CHECK 完整性约束 在DML触发器中，DML命令操作的数据保存在哪里 在updated和deleted表中 怎么判断查询结果是否为空 使用exist函数 怎么判断某值X是否为空 使用isnull函数 如isnull ( value tested, 0) 怎么实现表连接 内部连接 Inner join 存储过程形参的定义形式 名称 数据类型 默认值 方向 parameter datatype = default output 默认值和方向可以省略，但对于声明输出参数是必须使用output 数据完整性有几种 实体完整性 域完整性 引用完整性 用户定义完整性 怎么实现级联修改 在外键中使用 on update no action 在函数定义中怎么定义返回值。 使用return子句 return 标量 返回标量值 return table 返回表 二、名词解释 数据库 为特定目的而组织和表示的信息、表和其他对象的集合 外键 其值与同一个表或另一个表中的主键 (PK) 或唯一键相匹配的列或列的组合，也称为引用键。 死锁 两个用户各拥有一个数据块的锁，并尝试获取对方的数据块锁时出现on delete cascade 使用DML触发器 外部连接 outer join 全连接 full join 交叉连接 cross join 的情况。 事务 组成一个逻辑工作单元的一组数据库操作，这些操作要么全部提交，要么全部回滚 主键 三、简答题 索引的设计原则 1.了解数据库本身的特征。 2.了解最常用的查询的特征。 3.了解查询中使用的列的特征 4.确定哪些索引选项可在创建或维护索引时提高性能. 5.确定索引的最佳存储位置。 事务的属性 原子性、一致性、隔离性和持久性 简述各类数据完整性及其对应的约束实现 实体完整性：规定表的每一行在表中是惟一的实体。用主键约束实现 域完整性：是指表中的列必须满足某种特定的数据类型约束。用check约束实现 参照完整性：是指两个表的主关键字和外关键字的数据应一致。用外键约束实现 用户定义的完整性：针对某个特定关系数据库的约束条件，它反映某一具体应用必须满足的语义要求。用非空约束，check约束和触发器来实现 索引的作用 索引能大大加快数据的检索速度，加速表和表之间的连接。在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间。创建唯一性索引，保证数据库表中每一行数据的唯一性; DML触发器的用途 唯一地标识表中所有行的一列或一组列。 实体完整性 一种状态，在该状态下，每个表的每一行都能唯一标识。 可通过数据库中的相关表实现级联更改。 防止恶意或错误的 INSERT、UPDATE 以及 DELETE 操作。 引用其他表中的列。 评估数据修改前后表的状态，并根据该差异采取措施 SQL分为几类，每类常用的命令有哪些？ 数据定义语言(DDL)，例如：CREATE、DROP、ALTER等语句。 数据操作语言(DML)，例如：SELECT、INSERT、UPDATE、DELETE语句。 数据控制语言(DCL)，例如：GRANT、REVOKE、COMMIT、ROLLBACK等 存储过程的优点 提高数据库执行速度。 可以重复使用。 安全性高。 当对数据库进行复杂操作时，可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。 四、计算题 一个数据表使用单一扩展盘区或混合扩展盘区存储时占用磁盘空间的估算。 五、操作题 所有操作假设由sa账号用SQL语句完成。 1、 创建一个数据库 create database 教学 on (name='教学_data', filename='e:sqltestdata教学_data.mdf', size=10MB, maxsize=200MB, filegrowth=10MB ) log on (name='教学_log', filename='e:sqltestdata教学_log.ldf', size=1MB, maxsize=20MB, filegrowth=1MB ) 2、在SQL Serve中添加一个登录名 Windows 域帐户创建登录名 create login sasa from windows; 创建带密码的登录名 create login dafei with password='111111' 3、在“教学”数据库中添加数据库用户 use 教学 create user fei without login 或 use 教学 create user fei for login dafei 4、在“教学”数据库中创建三个数据表： create table 学生 (学号 char(10) primary key constraint xs_xh_ck check(学号 like '0808_ _ _1-2_ _ '), check语句是按软件班上面的要求来写的 姓名 nvarchar(20) not null, 生日 datetime ) create table 课程 (课程号 char(4) primary key, 课程名 nvarchar(20) not null ) create table 成绩 ( 学号 char(10) constraint xs_cj_xh foreign key references 学生(学号), 课程号 char(4) constraint xs_cj_kch foreign key references 课程(课程号), 分数 decimal(4,1) default(0) check(分数 between 0 and 100), primary key (学号,课程号) ) 考核内容重点：数据类型、主键、外键、空/非空、数据校验、默认等约束。 5、修改表：添加一列“性别”，并在“生日”上添加不对已有数据进行验证的数据校验约束“bircheck”，要求输入的数据需满足的条件是学生的年龄必须不小于14岁。 考核内容重点：添加列、添加约束条件、开关约束条件 alter table 学生 add 性别 char(1) check (性别 like '男女'); alter table 学生 add constraint bircheck check(year(getdate)-left(生日,4)>=14); 6、权限管理 use 教学 grant select on 学生 to fei deny select on 学生 to fei revoke select on 学生 from fei grant create table to fei 必须加use 教学 因为前面的是为教学添加的fei用户 7、创建一个视图，用以显示各班的SQL成绩统计。 use 教学 go create view SQL成绩_view as select left(学生.学号,8) as 班级,max(分数) as 最高分,min(分数) as 最低分,avg(分数) as 平均分 from 学生 left join 成绩 on 学生.学号=成绩.学号 inner join 课程 on 成绩.课程号=课程.课程号 where 学生.学号 like '_ _08_ _ _ _ _ _ _' and 课程.课程名='SQL' group by 班级 创建一个显示所有XX级计算机系学生的YY课程的成绩的视图，其中包括学号，姓名，课程名，分数四列。 use 教学 go create view 某课程的成绩_view as select 学生.学号,学生.姓名,课程.课程号,成绩.分数 from 学生 left join 成绩 on 学生.学号=成绩.学号 inner join 课程 on 成绩.课程号=课程.课程号 where subtring(学生.学号,2,2)='08' and 课程.课程名='某课程' 按班级、性别统计XX级计算机系各班的YY课程的平均成绩，结果按照班级的升序，性别的降序显示。 use 教学 select left(学生.学号,8) as 班级,学生.性别, avg(成绩.分数 ) as 分数 from 学生 left join 成绩 on 学生.学号=成绩.学号 inner join 课程 on 成绩.课程号=课程.课程号 where subtring(学生.学号,2,2)='08' and 课程.课程名='YY课程' group by 班级,学生.性别 order by 班级 asc, 性别 desc 8创建简单/复合索引。 use 教学 create index xh_index on 学生(学号 asc) go use 教学 create index xh_fs_index on 成绩(学号 desc,分数 desc) 9、定义触发器 use 教学 go create trigger xst on 学生 after delete as delete from 成绩 where 成绩.学号 in (select 学号 from deleted ) 在“教学”数据库中添加一个角色。 use 教学 create role xs_role authorization fei 5、将安全账户添加到角色中。 use 教学 exec sp_addrolemember 'xs_role','fei' 六、编程题 在“教学”数据库中创建一个存储过程cj_proc，实现按照输入的课程名称打印此门课程的成绩报表，输出结果按照分数降序排列。 use 教学 go if exists ( select * from sysobjects where name ='cj_proc'and xtype ='p') drop proc cj_proc go create proc cj_proc KCM nvarchar(40)='SQL' as SET NOCOUNT ON DECLARE XH char(10), -学号 XM nvarchar(10), -姓名 kch nvarchar(6), -课程号 CJ numeric(4,1), -成绩 message nvarchar(80), -信息 mc int -名次 if not exists (select 课程.课程号 from 课程 inner join 成绩 on 课程.课程号=成绩.课程号 and 课程.课程名=kcm) print '无此课程成绩' else begin select kch=课程.课程号 from 课程 where 课程.课程名=kcm print ' '+kcm+'成绩表' print REPLICATE('*',48)-重复个*制成一种表格形式 print '名次 学号 姓名 成绩' declare xhcj_cursor cursor scroll for select 学生.学号,姓名,分数 from 学生,成绩 where 成绩.学号=学生.学号 and 课程号=kch order by 分数 desc open xhcj_cursor fetch next from xhcj_cursor into xh,xm,cj set mc=1 while fetch_status=0 begin set message=ltrim(str(mc)+space(10)+xh+space(7)+xm+space(12)+ltrim(str(CJ) PRINT message fetch next from xhcj_cursor into xh,xm,CJ set mc=mc+1 end print REPLICATE('*',48) CLOSE xhcj_cursor DEALLOCATE xhcj_cursor end go