2023年SQL语言实验报告.pdf

实 验 五 SQ L语 言*、目 的 与 规 定 1.掌 握 SQ L语 言 的 查 询 功 能；2.掌 握 SQL语 言 的 数 据 操 作 功 能；3.掌 握 对 象 资 源 管 理 器 建 立 查 询、索 引 和 视 图 的 方 法;二、实 验 准 备 1.了 解 SQL语 言 的 查 改 增 删 四 大 操 作 的 语 法；2.了 解 查 询、索 引 和 视 图 的 概 念；3.了 解 各 类 常 用 函 数 的 含 义。三、实 验 内 容(-)S Q L查 询 功 能 使 用 提 供 的 stud entdb数 据 库 文 献，先 附 加 到 目 录 树 中，再 完 毕 下 列 题 目,SQL命 令 请 保 存 到 脚 本 文 献 中。1.基 本 查 询(1)查 询 所 有 姓 王 的 学 生 的 姓 名、学 号 和 性 别 S el e c t St_N a me,St_S ex,S t _IDF rom st_ i nfoW h er e St_Name I i k e 王%SQLQueryl.sql-127.0.0.1.studentdb(PC-20160924WFDLAdministrator(59)*1 臼 Select St_Name,St_Sex,St_ID2 From st_info3 Where St_Name like*王 100%口 结 果 二|消 息 St_N am e St_Sex St_ID1 j 主，j 雍|里 2602060106图 5-1(2)查 询 全 体 学 生 的 情 况，查 询 结 构 按 班 级 降 序 排 列，同 一 班 级 再 按 学 号 升 序,并 将 结 果 存 入 新 表 new中 s elect*into newf rom s t _ infoo r d er by Cl_ Name d e sc,st_ I D as cSQLQuery2.sql-127.0.0.1.studentdb(PC-20160924WFDLAdministrator(53)*1 B se le c t*into new2 from st_ in fo3 order by ClLName desc,st_ID asc100%一 由 消 息 _(1 6行 受 影 响)图 5-2(3)对 S _ C _ in f。表 中 选 修 了“体 育”课 的 学 生 的 平 均 成 绩 生 成 汇 总 行 和 明 细 行。(提 醒：用 compute汇 总 计 算)因 2023版 本 已 不 支 持 c o mpute关 键 字，所 以 选 择 用 其 他 方 式。Sei e ct c_n o,scoreFr o m s_ c _infoW h ere c _no=2 9 0 0 0011g ro u p by c_ n o,s c orePC-20160924WFDL.db-dbo.stjnfo 对 象 资 源 笞 理 器 SQLQuery3.sql-l.min1234E Select c_no_,scoreFrom s_c_infoWhere c_no=29000011group by c_no_,score)100%二 结 果 j 消 息 c_no score1 i 29000011 j 772 29000011 833 29000011 924 29000011 97图 5-32.嵌 套 查 询 查 询 其 他 班 级 中 比“材 料 科 学 06 0 1班”的 学 生 年 龄 都 大 的 学 生 姓 名 和 年 龄 sei e c t st_name,b orn_d a t efrom s t_ infow h e r e cl_ n a m e!=材 料 科 学 06 0 1 班 a n d bo r n_date(selectmin(bo r n _date)f rom s t _info wher e c l_ n a m e=7 材 料 科 学 0601 班)SQLQuery4.sql-l.ministrator(59)*X PC-20160924WFDL.db-dbo.stjnfo 对 象 资 源 S 3 器 1 臼 s e le c t st_name.,b o rn-d ateJ2 from s t_ in fo3 where cl_name 二 材 料 科 学 0601班 and born_date(s e le c t m in(born_date)4 from s t_ in fo where cl_name二 材 料 科 学 0601班)100%一 口 结 果 消 息 st_name born_date:邓 红 艳|1986-07-03 00:00:00.000金 萍 1984-11-06 00:00:00.000吴 中 华 1985-04-10 00:00:00.000郑 远 月 1986-06-18 00:00:00,000图 5-4 用 e x i s t s查 询 选 修 了“9 7 10041课 程 的 学 生 姓 名s el e ct s t namef rom st_ i nfowhere e x ist s(s e 1 ect*from s _ c_ info where c_n o=971004 1a n d st_ i d=st_ in fo.st_id)PC-20160924WFDL.db-dbo.stjnfo 对 象 资 源 霞 器 1 B se le c t st_name2 from st_ in fo3 where e x is ts(s e le c t*from s_c_info where c_no=9710041 and st_ id=st_ in fo.st_ id)结 果,J 消 息 st_name1 福 受 受 2 黄 正 刚 3 张 红 飞 4 曾 莉 娟 图 5-5 用 i n 查 询 找 出 没 有 选 修“9710041”课 程 的 学 生 的 姓 名 和 所 在 班 级。sele c t s t_ name,c 1 _ n am ef r o m s t _ in f ow h e re s t_id no t in(s e le ct st_ i d f r o m s_c_inf o wher e cno 97 1 00417)I SQLQueryG.sql-l.ministrator(52)*X1-s e le c t st_name.,cl_name2 from st_ in fo3 where st_ id not in(s e le c t st_ id from s_c_info where c_no=r 971004T)PC-20160924WFDL.db-dbo.stjnfo 对 象 资 源 告 理 器 结 果 消 息 100%4st_name cl_name1 邓 红 艳 法 学 05012 金 萍 法 学 05023 吴 中 华 法 学 05034 杨 柳 法 学 05035 郑 远 月 法 学 06016 张 力 明 法 学 06027 张 好 然 法 学 06038 李 娜 法 学 06049 杨 平 娟 口 腔（七）0601班 10 王 小 维 口 腔（七）0601班 11 划 小 玲 口 腔（七）0601班 12 何 邵 阳 j 口 腔（七）06。1班 图 5-6 查 询 选 修 了 学 号 为 的 学 生 所 选 所 有 课 程 的 学 生 姓 名。select s t _ n a m efr o m s t_i n f o wh e r e st_id i n(select disti n ct st_id from s_c_info whe r e n o t e x ists(se 1 e ct*from s_ c_ i nfowher e s t_ id=and not exi s t s(s e lec t*fr o m s_ c _info where st_info.s t _id=s_c_ i nfo.st_idandc_ n o=a n y(sele c t c_ n o)f rom s c i n fo where st i dSQLQuery7.sql-l.ministrator(52)*x11 S select st_najne2 from st_info where st_ id in43(sseelleecctt*dfirsotmin cs_t c_stin_fiod from s_c_info where not e x ists5 where st-id=2001050105*and not e x ists6(select*from s_c_info where st_info.st_id=s_c_info.st_ id and7 c_no=any(select c_no from s_c_inf o where st_id=*2001050105,)|100%国 结 果 口 消 息 st_nam e1 福 箱 12 荃 舞 3 吴 中 华 图 5 7PC-20160924WFDL.db-dbo.st.info 对 源 频 器 3.连 接 综 合 查 询 及 其 他(1)查 询 每 个 学 生 所 选 课 程 的 最 高 成 绩，规 定 列 出 学 号，姓 名，课 程 编 号 和 分 数。s elect s t_ in f o.s t_ id,st_name,cin f o.c _ n o,sc o r ef r o m s t_ info i nner join s_ c_in f o o n st_in f o.s t_ id=s_ c_inf o.s t _ id i nner jo i nc _ i n f o on s _c_ i nfo.c_no=c_i n fo.c _now h ere sco r e:(s e lect m ax(s _c_ i n f o.s c o re)from s_c_ in f ow h e re s t _in f o.st_id=s_c_ i n f o,s t i d)SQLQuery8.sql-l.ministrator(52)*X PC-20160924WFDL.db-dbo.stjnfo 对 象 资 源 压 器 SQLQuery3.sql-12345100%，B select st_info.st_id,st.name,c_info.c_no,scorefrom st_ info inner join s_c_info on st_info.st_id=s_c_info.st_id inner joinc_info on s_c_info.c_no=c_info.c_nowhere score-select m a x(s_c_info.score)from s_c_infowhere st_info.st_id=s_c_info.st_id)口 结 果 J 消 息 st_id st_name c_no see1j 0603060108;徐 文 文 9710041 672 0603060109 黄 正 刚 9710041 783 0603060110 张 红 飞 9710041 524 0603060111 曾 莉 娟 9710041 995 2001050105 邓 红 艳 9720013 906 2001050106 金 萍 9720013 937 2001050107 吴 中 华 9720013 778 2602060105 杨 平 娟 29000011 779 2602060106 王 小 维 29000011 9710 2602060107 刘 小 玲 29000011 9211 2602060108 何 邵 阳 29000011 83图 5-8 查 询 所 有 学 生 的 总 成 绩，规 定 列 出 学 号、姓 名、总 成 绩，没 有 选 修 课 程 的 学 生 总 成 绩 为 空。s e 1 ect s t _info.s t_i d,s t_ n a m e,总 成 绩 from s t_ i nfo1 e f t outer j oin(s e 1 ec t st_ i d,s um(sc o r e)a s 总 成 绩 from s _c_i n f o g roup b yst i d)s c info o n s t i nf o.s t i d s c i nfo.s t i dr2LQuery9.sql-l.m inistrator(52)*X1 E s e le c t st_ in fo.st_ id,st_nam e,总 成 绩 2 from s t _info4 3 s le t_ fi td)o s u_ te cr _ in jo f i o n on(ss et l_ ei c n t fo s.t _ si td _i,d s=um s_(c sc _o in re f)o.a s s t_ 总 id 成 绩 from s_c_inf o group byPC-20160924WFDL.db-dbo.stjnfo 对 象 资 源 管 理 器 结 果 二 I 消 息 100%st-id st_name 总 成 绩 10603060108 I 徐 文 文 672 0603060109 黄 正 刚 783 0603060110 张 红 飞 524 0603060111 曾 莉 娟 995 2001050105 邓 红 艳 1786 2001050106 金 萍 1827 2001050107 吴 中 华 1538 2001050109 杨 柳 NULL9 2001060103 郑 远 月 NULL10 2001060104 张 力 明 NULL11 2001060105 张 好 然 NULL12 2001060106 李 娜 HULL13 2602060105 杨 平 娟 7714 2602060106 王 小 维 9715 2602060107 刘 小 玲 9216 2602060108 何 邵 阳 83图 5 9(3)查 询“大 学 计 算 机 基 础”课 程 考 试 成 绩 前 三 名 的 学 生 姓 名 和 成 绩。s e l e c t s t_info.st_id,s t _n a me,s c orefrom st_ i n foirir)(?r Joi n s_c_info on st_ i n f o.s t _id=s_ c _ i n f o.st_ idinner jo i n c_ i n f o o n s_c_info.c_no c_i n fo.c_ n oan d c_ n ame=7大 学 计 算 机 基 础 SQLQuerylO.sql-.dministrator(52)*X 骐，O T 1 立 庭 睦 迎”4 将 s_ c _ i n f o中 的 sco r e 列 的 值 转 为 等 级 制 输 出，即 6 0 分 以 下 显 示 为“不 及 格 及 6 0 6 9分 显 示“及 格”，70 7 9分 显 示“中 档”，80 8 1显 示“良 好”，9 0 100显 示“优 秀 二 规 定 输 出 学 号、姓 名、课 程 名、成 绩 等 级。(提 醒:在 sei e c t 字 句 中 使 用 case.when.end语 句)s e l e c t s t _i n fo.s t _ id,s t_ name,c_nam e,成 绩 等 级 二 c asewhe n score 二 9 0 th e n 优 秀 when s c o re=80 th e n 良 好，w h e n sc o r e=7 0 th e n 中 档 w h en sco r e 6 0 th e n 及 格 when s c o r e 6 0 th e n，不 及 格 e n dfrom s _c_in f o,st_info,c_in f owhe r e st_in f o.st_id s _c_ i nfo.s t_ i d and c _ i n f o.c_ n o s _c in f o.c n oI S Q L Q u e ryll.sq l-.d m in istra to r(52)*X|310s e le c t s t_ in fo.s t_ id,st_name.,c_name.成 绩 等 级 二 casewhen score=90 t h e n 优 秀 when score=80 th e n 良 好 when score=70 th e n 中 等 when score=60 t h e n 及 格 when score 60 t h e n 不 及 格 endfro m s _ c _ in fo,s t _ in fo,c _ in fowhere s t in fo.s t i d 二 s c in fo.s t i d and c in fo,c no二 s c in fo,c nol100%一 结 果 消 息 st_ d st_name c_name 成 绩 等 级 1 2 603项 108.徐 文 文 C语 言 程 序 设 计 基 甜 及 格 2 0 6 0 3 0 6 0 1 0 9黄 正 刚 C语 言 程 序 设 计 基 酬 中 等 3 0 6 0 3 0 6 0 1 1 0张 红 飞 C语 言 程 序 设 计 基 础 不 及 格 4 0 6 0 3 0 6 0 1 1 1曾 莉 娟 C语 言 程 序 设 计 基 础 优 秀 5 2 0 0 1 0 5 0 1 0 5邓 红 艳 大 学 计 算 机 基 础 良 好 6 2 0 0 1 0 5 0 1 0 5邓 红 艳 大 学 计 算 机 基 础 实 蛉 优 秀 7 2 0 0 1 0 5 0 1 0 6金 第 大 学 计 算 机 基 础 良 好 8 2 0 0 1 0 5 0 1 0 6金 萍 大 学 计 算 机 基 础 实 验 优 秀 9 2 0 0 1 0 5 0 1 0 7吴 中 华 大 学 计 算 机 基 础 中 等 10 2 0 0 1 0 5 0 1 0 7吴 中 华 大 学 计 算 机 基 础 实 蛉 中 等 11 2 6 0 2 0 6 0 1 0 5杨 平 娟 体 育 中 等 12 2 6 0 2 0 6 0 1 0 6王 小 维 体 育 优 秀 13 2 6 0 2 0 6 0 1 0 7刘 小 玲 体 育 优 秀 14 2 6 0 2 0 6 0 1 0 8何 邵 阳 体 育 良 好 图 5-11（二）SQL的 增 删 改 功 能 在 实 验 四 建 立 的 studb数 据 库 中，写 SQL语 句 实 现 增 删 改 功 能。1.在 S 表 中 增 长 如 下 记 录：_|_sno sname|ssex|bomdate|dname|enscore|address1|j S3 j 张 明 华 男 1995）8-21 00:00:00,000 MA重 学 530.0 浙 江 杭 州 图 5 1 2inser t Sv a lu e s(s 3?张 明 华，男,1995/0 8/2 1 00:00：00.0 0 O,MA_ 数 学,530.0 浙 江 杭 州:NULL)SQLQuery5.sql-127.0.0.l.studb(PC-20160924WFDLAdministrator(57)*1 H in s e rt S2 value s(s 3 张 明 华 里 1995/08/21 00:00:00.000F/M A一 数 学，530.O,浙 江 杭 州，NULL)图 5 13PC-20160924WFDLstudb-dbo.S X PC-20160924WFDLstudb-dbo.Ssno sname ssex borndate clname enscore address sfzh SI 恒 男 1995-03-20 0.CS_计 算 机 460.0 湖 南 长 沙 NULLS10 刘 京 女 1996-07-10 0.EN五 语 472.0 NULLS2 欧 阳 蒙 女 1994-10-09 0.M A R 519.5 NULL NULLs3 张 明 华 男 1995-08-21 0.MA 530.0 浙 江 杭 州 NULL图 5-142.在 C 表 中 将 课 程 名 为“数 据 库”的 学 分 更 改 为 3。update C set cere d it=7 3 where cnam e=数 据 库，SQLQueryG.sql-l.ministrator(52)*X PC-20160924WFDLstudb-dbo.S1 Eupdate C set ccredit=,3 2 where cname二 数 据 库 100%一 由 肖 息(1 行 受 影 响)图 5-1 5PC-20160924WFDL.studb-dbo.C SQLQuery6.sql-L.ministrator(52)*1eno cname ccredit epno Cl 高 等 数 学 7.2 NULLC2 数 据 库 3.0 C5C5 4.0 Cl NULL NULL NULL图 5-16NULL3.删 除 S 表 中 S 2的 学 生 记 录，请 问 是 否 能 删 除，为 什 么,要 如 何 操 作。能 删 除 de 1 e t e from Sw he r e sno=S 2SQLQuery7.sql-127.0.0.l.studb(PC-20160924WFDLAdministrator(52)*1 t-d e le te from S2 where sno=S210 0%t d 消 息 消 息 5 4 7,级 别 1 6,状 态。，第 1 行 DELETE 语 句 与 REFERENCE 约 束 FK_SC S冲 突。该 冲 突 发 生 于 数 据 库“s t u d b,表 dbo.SC,colum n n o。语 句 已 终 止。图 5-17PC-20160924WFDL.studb-Diagram)SC8 sno8 enoscore8 enocname8 snosnamessexborndateclnameenscorec o n occredit图 5-18SQLQuery7.sql-127.0.0.1.studb(PC-20160924WFDLAdministrator(52)*1 E delete from S|2 where sno=，S2100%石 肖 息（1 行 受 影 响）图 5-1 9C-20160924WFDLstudb-dbo.Ssno sname ssex borndate clname enscore address sfzh SI 恒 aa1995-03-20 0.CSJ+算 机 460.0 湖 南 长 沙 NULLS10 踞 女 1996-07-10 0.EN至 语 472.0 NULL张 明 华 E B1995-08-21 0.MA费 学 530.0 浙 江 杭 州 NULLNULL NULL NULL NULL NULL NULL NULL NULL图 5-2 0（三）索 引 1.在 stu d b 数 据 库 中，分 别 用 对 象 资 源 管 理 器 和 SQL语 言 定 义 索 引 在 对 象 资 源 管 理 器 中，在 T表 的 tname列 上 中 建 立 聚 集 索 引 ix _ tn a m e,降 序。查 看 聚 集 的 效 果。-；新 建 案 引 i 就 绪 区 鹿 本,的 帮 助 衰 名 CD：、案 引 名 称：x_tname索 引 姆 凶:雌 睚 一 素 弓 i 键 列|上 移 凶 酬 除(R)|W(D)瑕 取 消 帮 助 图 5-2 1(&13-J图 5-221.使 用 S Q L 语 言 定 义 T C 表 的(tn o,eno)列 上 的 复 合 索 引 ix_tc,t n o列 设 为 升 序，e n o 列 设 为 降 序 先 增 长 cn o 列，再 删 除 聚 集 索 引 ix_tnameoc r eate c 1 ustered i ndex ixt con T(tn o,cn o)SQLQuery9.sql-127.0.0.1.studb(PC-20160924WFDLAdministrator(52)*1 E c re a te c lu ste re d index ix_tc2 on T(tno.,cng)|100%一 信 消 息 I _命 令 已 成 功 完 成。图 5-23图 5-24（四）视 图 在 stud b数 据 库 中 操 作。1.在 对 象 资 源 管 理 中 建 立 视 图 v _ s _ c,列 出 所 有 学 生 所 选 课 程 的 成 绩:学 号,姓 名，班 级 名，课 程 号，课 程 名，成 绩。8 127.0.0.1(SQL Server 12.0.5000.0-PC-20160924WFD2 数 据 库 a 系 统 卦 据 库 州 库 快 照 国 J ReportServer田|J ReportServerTempDBB U studb田 数 据 库 关 系 图 国 立 表 一 寇 r 新 建 视 图（N）“.（+）图 5-25添 加 表 图 5-2 6PC-20160924WFDLstudb-dbo.View_l*为 育 e脩 二 cor*slasIE怖 glsexorn0*0 5口 n b或.列 别 名 表 城 出 排 序 类 型 排 序,顿 序 sno Expr2 S团 sname Expr3 scname c回 eno Exprl ci.clname Expr4 sscore sc色 F5EccrCPI回 回 口 口 SELECT dbo.S.sno AS Expr2,dbo.S.sname AS Expr3,dbo.C.cname,dbo.C.cno AS Exprl,dbo.S.dname AS Expr4,dbo.SC.scoreFROM dbo.C INNER JOIN图 5-2 72.使 用 S Q L语 言 建 立 视 图 v _ c j tj,列 出 每 位 同 学 的 学 号，最 高 成 绩，最 低 成 绩,平 均 成 绩 和 总 成 绩,按 总 成 绩 降 序 排 列。creat e view v_ cjt j(x h,z g f,z df,pj f,z f)(s el e ct t op 10 0 s n o,m ax(s c o re),m i n(s c ore),av g(s c ore),s um(score)fro m SC group by snoo r d er b y s um(s c o r e)d esc)SQLQueryll,sql-127.0.0.1.studb(PC-20160924WFDLAdministrator(60)*1-cre a te view v _ cjt j(xh,zgf,zd f,p j f.zf J八 一 2 as3(se le ct top 100 sno.,max(score),min(score),avg(score)sum(score)4 from S C5 group by sno6 order by sum(score)desc|7 1)办 消 息 L命 令 已 成 功。成。图 5-28PC-20160924WFDL.studb-dbo.v_cjtjxh zqf zdf pjf zfSI 85.0 76.0 79.333333 238.0S2 93.0 91.0 92,000000 184.0S10 88.0 50.0 69.000QQ0 138.0.NULL NULL NULL图 5-2 9NULL NULL四、思 考 与 练 习 1.视 图 和 表 有 何 区 别？(1)视 图 是 已 经 编 译 好 的 sql语 句。而 表 不 是(2)视 图 没 有 实 际 的 物 理 记 录。而 表 有。(3)表 是 内 容，视 图 是 窗 口(4)表 只 用 物 理 空 间 而 视 图 不 占 用 物 理 空 间，视 图 只 是 逻 辑 概 念 的 存 在，表 可 以 及时 对 它 进 行 修 改，但 视 图 只 能 有 创 建 的 语 句 来 修 改 表 是 内 模 式，视 图 是 外 模 式(6)视 图 是 查 看 数 据 表 的 一 种 方 法，可 以 查 询 数 据 表 中 某 些 字 段 构 成 的 数 据，只 是 一 些 SQL语 句 的 集 合。从 安 全 的 角 度 说，视 图 可 以 不 给 用 户 接 触 数 据 表,从 而 不 知 道 表 结 构。(7)表 属 于 全 局 模 式 中 的 表，是 实 表;视 图 属 于 局 部 模 式 的 表，是 虚 表。(8)视 图 的 建 立 和 删 除 只 影 响 视 图 自 身，不 影 响 相 应 的 基 本 表。2.视 图 中 的 列 都 能 更 新 吗？不 一 定 3.查 询 年 龄 最 大 的 教 师 号 和 年 龄,SQL命 令 如 下:请 问 为 什 么 报 错?如 何 修 改？Sei e c t tno,max(y e a r(getd a t e()-y e a r(tbirday)F rom T选 择 列 表 中 的 列 T.t n o 无 效，由 于 该 列 没 有 包 含 在 聚 合 函 数 或 GROUP BY 子 句 中。在 f rom后 面 加 gr o up b y t no