SQL语句大全及实验内容

1 查询全体学生的学号和姓名 select SNO SN from S 2 查询全体学生的所有基本信息 仅针对Student表 select from S 3 对SC表查询全体学生的选课记录 在显示结果中为 Grade 列增 加分 select SNO CNO SCORE 5 as SCORE from sc 4 查询全体学生的学号和姓名 将原来的英文列名设置中文别名 SELECT SNO as 学号 SN as 姓名 SEX as 性别 AGE as 年龄 DEPT as 系别 from S 5 显示所有选课学生的学号 去掉重复结果 select distinct sno from sc 6 查询成绩在分以上的学生选课记录 select from sc where score 80 7 查询计算机系所有学生的学号和姓名 select SNO SN from s where DEPT 计算机 8 查询成绩在 分的学生选课记录 select from sc where score between 80 and 90 9 查询年龄不在 的学生记录 select from S where age not between 20 and 30 10 查询所有姓 李 的学生记录 select from S where sn like 李 11 查询无考试成绩 成绩为空值 的学号 课程号 select distinct sno from sc where score is null 12 查询考试成绩非空值的学号 课程号 select sno cno from sc where score is not null 13 查询自动化系年龄小于岁的学生记录 select from s where dept 自动化 and age2 23 查询所有学生信息 查询结果按年龄降序排列 针对Student 表操作 select from s order by AGE desc 24 查询所有学生信息 查询结果按系名升序 同系学生按年龄降序 排列 select from S order by DEPT asc AGE desc 25 查询选课数超过的学生学号及其选课数 查询结果按选课数降序 排列 select sno COUNT cno from sc group by SNO having COUNT CNO 2 order by COUNT CNO desc 实验 数据库的多表查询 一 实验目的 继续熟练SQL SELECT语句的使用 理解多表查询的概念 掌握多表 连接查询中各个子句的用法 特别要比较内连接和外连接的结果 掌 握非相关子查询 相关子查询的用法 理解它们的执行过程 学会在 SQL Server中用Exists实现交运算 用Not Exists实现差运算 二 实验环境 Windows XP操作系统 SQL Server 2005软件 三 实验课时 2课时 四 实验内容 一 在已建立的数据库和数据表的基础上 用Select语句的连接 查询完成下列操作 1 查询每个学生的基本信息及其选课情况 select from S SC where s sno sc sno 2 查询选修 C8 课程的学生学号 姓名和成绩 select s sno s sn sc score from s sc where s sno sc sno and o C8 3 查询与 谢斌 在同一个系学习的学生记录 select from s where dept select dept from s where sn 谢斌 4 查询与 刘莎莎 年龄相同的学生记录 select from s where age select age from s where sn 刘莎莎 5 查询选修课程名为 数据库 的选课记录 select from sc where cno in select cno from c where cn 数据库 6 查询选修课程名为 数据库 的学生记录 select from s where sno in select sno from sc where cno in select cno from c where cn 数据库 7 查询选修 c8 课程且成绩 的学号 姓名和成绩 select s sno s sn sc score from s sc where s sno sc sno and o C8 and sc score 90 8 查询 谢斌 的所有选课记录 select from sc where sno select sno from s where sn 谢斌 9 查询计算机系学生选修的课程号 要求结果中去掉重复记录 select distinct o from s sc where s sno sc sno and s dept 计算机 select distinct cno from sc where sno in select sno from s where dept 计算机 10 查询计算机系选修课程数 的学号 姓名及平均成绩 查询结果按 平均成绩降序 select xs XH xs XM cj KCH cj CJ from XSB as xs left join CJB as cj on xs XH cj XH select s sno s sn sc score from s left join sc on s sno sc sno where s dept 计算机 select s sno s sn sc score from s sc where s sno sc sno and s dept 计算机 group by select s sno s sn sc score from s sc where s sno sc sno select s sno s sn sc score form 11 查询每个学生的学号 选修课程号 课程名及成绩 12 查询所有学生的选修情况 包括选课和未选课的学生 要求显 示学号 姓名 课程号和成绩 二 在已建立的数据库和数据表的基础上 用Select语句的嵌套 查询完成下列操作 1 查询选修 课程且成绩 的学号 姓名 2 查询 李平 的所有选课记录 3 查询与 李平 在同一个系学习的学生记录 4 查询与 李平 年龄相同的学生记录 5 查询选修课程名为 数据库原理 的选课记录 输出结果包括学号和 成绩 6 查询选修课程名为 数据库原理 的学生记录 输出结果包括学号 姓名和所在系 7 查询学号为 学生的选修课程号和课程名 8 查询没有选修 课程的学生学号和姓名 9 查询选修 课程或 课程的学生姓名 10 查询选修 课程和 课程的学生学号 11 查询选修 课程但没选修 课程的学生学号 12 查询没有选修任何课程的学生记录 输出结果包括学号 姓名和 所在系 13 查询数学系学生选修的课程号 要求结果中去掉重复记录 14 查询选修课程至少包含 选修课程的学生学号 实验 数据库更新 视图定义及使用 一 实验目的 掌握Insert Update Delete语句的使用 对于Insert语句 要 求理解默认值约束 空值约束在插入记录时所起的作用 理解视图的 概念 掌握Create View Drop View语句的使用 掌握基于视图 的查询语句的使用 二 实验环境 Windows XP操作系统 SQL Server 2005软件 三 实验课时 2课时 四 实验内容 一 在已建立的数据库和数据表的基础上 向数据库中添加一个 临时表ST 其结构比Student表结构多一个score字段 用Insert 语句向临时表输入数据 输入有误时用Update语句进行修改 再用三 个更新语句完成下列操作 1 向Student表添加若干新记录 内容自定 观察已定义的表的约 束情况 2 向SC表添加新记录 内容自定 不低于条 注意不能违反参照完整 性 3 为临时表ST添加记录 4 把Student表的所有行一次性地加到临时表ST中 5 在ST表中把所有学生的成绩加分 6 在ST表中把所有学生的年龄增加 7 在ST表中把 李平 的所在系改为 计算机 8 在ST表中将选修课程 数据库原理 的学生成绩加分 9 在SC表中删除所有成绩为空值的选修记录 10 删除计算机系选修成绩不及格的选课记录 二 在已建立的数据库和数据表的基础上 完成下列操作 1 建立数学系学生的视图MAST 2 建立计算机系选修课程名为 数据库原理 的学生视图 视图名 CSTVIEW 该视图中应包括属性列 学号 姓名 成绩 3 创建一个名为STSUMVIEW的视图 包括所有学生的学号和总成绩 4 建立学生选课视图SCVIEW 包括所有学生的学号 姓名 课程号 课程名和成绩 5 通过MAST视图查询学生基本信息 6 通过SCVIEW查询成绩大于分的学生的学号和成绩 7 查询计算机系选修课程名为 数据库原理 并且成绩大于分的学生的 学号和成绩 8 通过MAST视图将学号为 学生的年龄修改为岁 9 通过MAST视图将学号为 学生所在系改为 经济 是否能成功执 行 若不能成功请说明理由 若修改成功 请再次通过MAST视图查询 学生基本信息 是否能查询到该生 若不能查询请说明理由 10 通过SCVIEW视图将学号为 学生的总成绩修改为分 是否能成 功执行 若不能成功请说明理由 实验 数据库的安全与保护 一 实验目的 理解SQL Server的用户与权限管理机制 掌握用对象资源管理器创 建服务器登录帐号 并同时建立用户 掌握如何为给定的用户分配权 限 二 实验环境 Windows XP操作系统 SQL Server 2005软件 三 实验课时 2课时 四 实验内容 用户与权限管理 打开 SQL Server Management Studio 窗口 使用对象资 源管理器创建两个服务器登录帐号Test1和Test2 并在 sample st 数据库中创建两个对应的同名用户 设置Test1用户和 Test2用户的默认架构为guest 使用Test1身份登录 并为Test2分配创建数据表的权限 用 Test2身份登录来验证权限分配成功 使用Test1身份登录 收回刚刚分配给Test2的创建数据表的权 限 用Test2身份登录来验证权限回收成功 五 实验步骤 1 使用对象资源管理器创建两个服务器登录帐号 以系统管理员身份登录SQL Server 打开 SQL Server Management Studio 窗口 在对象资源管理器列表中 打开展开 安全性 文件夹 选择 登录名 图标 单击鼠标右键 在弹出的菜单 中选择 新建登录名 在打开的对话框中依次建立Test1和Test2 选择 SQL Server身份验证 同时还要输入密码 默认数据库为 sample st 在 用户映射 中列出了当前登录帐号可以选择访问的数据库如 sample st 在其左侧的复选框中打勾 表示当前登录帐号可以访 问对应的数据库 默认用户名与登录帐号相同 单击 确定 按钮完成创建 2 创建新的数据库用户 在对象资源管理器中选中要访问操作的数据库 展开 安全性 文件夹 在 用户 文件夹中查找是否已建立与登录名Test1和Test2同名的数 据库用户 若已建立 则在属性中修改其默认架构为guest 否则右 击鼠标在菜单中选择 新建用户 选项 出现 数据库用户 新建 对话 框 建立相应的用户 最后 在sample st数据库的 安全性 架构 guest 属性 权限 中 为test1和test2用户分配相应权限 3 用Grant Revoke命令实现对用户的授权和收权 4 用Create命令创建数据表验证授权和收权是否成功 5 以系统管理员身份登录SQL Server 使用 GRANT SELECT ON SC TO Test1 WITH GRANT OPTION 命令 再分别以Test1和 Test2身份登录 进行授权及查询数据表 验证 WITH GRANT OPTION 能否成功执行 6 以系统管理员身份使用 REVOKE 命令回收上述授权 实验 数据库的完整性 一 实验目的 通过实验掌握数据库完整性概念 掌握利用SQL Server 2005实现 数据库完整性的基本方法和步骤 二 实验环境 Windows XP操作系统 SQL Server 2005软件 三 实验课时 2课时 四 实验内容 1 建立关系模式 完成教材P164第题 用SQL语言定义 职工 和 部门 两个关系模式 职工 职工号 姓名 年龄 职务 工资 部门号 职工号为主码 部门 部门号 名称 经理名 电话 部门号为主码 要求在模式中完成以下完整性约束条件的定义 定义每个模式的主码 定义参照完整性 定义职工年龄不得超过岁 定义部门名称必须取值唯一 2 验证完整性约束 使用Insert Values语句向两个表中各插入 条记录 对下列内容进 行验证 验证主键约束 职工 和 部门 两个表中是否能接受主码值相同 的记录 通过实例验证 验证唯一约束 部门 表中是否能接受名称相同的两个不同记录 为什么 验证检查约束 职工 表中能否接受年龄不满足条件的记录 为 什么 验证参照完整性 若 部门 表中不存在部门号为 的记录 是否 能在 职工 表中插入部门号为 的