精华内容
下载资源
问答
  • 复杂查询实验报告.doc

    2021-10-12 13:26:05
    复杂查询实验报告.doc
  • Oracle复杂查询实验报告, 用SQL语句实现下列查询。要求:能用嵌套查询的尽量使用嵌套查询。
  • 实用标准文案 宁夏师范学院数学与计算机科学学院 数据库 实验报告 实验序号 07 实验项目名称数据查询 学 号 2014210758 姓 名 邹业安 专业班级 14 信科 实验地点 222 指导教师 褚万军 时间 2017.6.0 7 一 实验目的及...
  • 实验内容1、 多表查询(1)等值与非等值连接(2)自身连接(3)复合条件连接(4)外连接2、 集合查询交 并 差3、 嵌套查询(1)带有IN谓词的子查询 sno in() in 里面对应sno(2)带有比较运算符的子查询(3)带有any或all...
      
    

    实验内容

    1、 多表查询
    1)等值与非等值连接

    2)自身连接

    3)复合条件连接

    4)外连接

    2、 集合查询

    3、 嵌套查询
    (1)带有IN谓词的子查询 sno in() in 里面对应sno
    (2)
    带有比较运算符的子查询

    (3)带有anyall的子查询   ???

    (4)带有existsnot exists的子查询(连接查询 实现全称量词)

     

     

    实验过程

    1:工程项目j2使用的各种零件的名称和数量(数量要求和)

    select Pname,sum(qty)as 数量 from spj,p

    where p.pno=spj.pno

    and jno='j2'

    group by pname

    /*红螺丝刀和蓝螺丝刀属于同一种零件*/

    2:上海厂商提供的所有零件号码(用in实现)

    select distinct pno

    from spj

    where sno in(

    select sno from s

    where city='上海'

    )

    3:没有使用天津生产的零件的工程号(用in not exists实现)

     

    select jno

    from j                ---不能在spj中查询

    where not exists(   

    select *

    from spj,s where s.sno=spj.sno and j.jno=spj.jno and city='天津' --注意要和j表连接

    )

    select distinct jno

    from j               ---不能在spj中查询

    where jno not in(           --不相关子查询

    select distinct jno

    from spj,s where s.sno=spj.sno and city='天津'

    )4:查询课程的间接先行课及学分

    ----查询课程的间接先行课及课程的学分

    select c1.cno 课程,c2.cno 先行课的先行课,c1.Ccredit 课程的学分

           from Course c1,Course c2

           where c1.cpno=c2.cno

    ----查询课程的间接先行课及间接先行课的学分

    select c1.cno 课程,c2.cno 先行课的先行课,c3.Ccredit 先行课的先行课的学分

           from Course c1,Course c2,Course c3

           where c1.cpno=c2.cno and c2.cpno=c3.cno

    5:查询选修了“数据库”课程的学生姓名和成绩

     

    select Sname,grade

           from student,course,sc

                  where student.sno=sc.sno

                         and course.cno=sc.cno

                                and course.Cname='数据库'

     

     

    6:查询选修了数据库的同学的课程平均分和学号

    select sno,avg(grade) 选修了数据库的同学的课程平均分

    from sc

    where sno in(

           select sno

           from sc,course

           where sc.cno=course.cno and cname='数据库'

           )

    group by sno

     

    7:查询选修数据库同学中数据库的最高分及学生姓名

    --7:查询选修数据库同学中数据库的最高分及学生姓名

    /*

    select sname,max(grade)

    from student,sc

    where student.sno=sc.sno and student.sno in(

           select sno                    --子查询找出所有选修数据库的同学的学号

                  from sc,course

                  where sc.cno=course.cno and cname='数据库')

    group by sname

    */错误错误错误错误错误错误

    select sname,grade

    from student,sc

    where student.sno=sc.sno and grade>=all(

           select grade                  --子查询找出所有选修数据库的同学的数据库的成绩

           from sc,course

                  where sc.cno=course.cno and cname='数据库')

    8:查询选修全部课程的学生学号

     

    Select sno                  --------------------------假的 查询哪个学生至少有一门课没有选修

    From student

    Where not exists

    (select *           ------------查询某学生对应一门课程他没有选修

    From course

    Where not exists

    (select*      -----某个学生选修的所有课程

       From sc

       Where sno=student.sno

             And cno=course.cno))

    附:

    课后习题

    4.针对上题中建立的四个表试用SQL语言完成第二章习题5中的查询:
    (1) 求供应工程J1零件的供应商号码SNO:
    SELECT DIST SNO FROM SPJ WHERE JNO=’J1’
    (2) 求供应工程J1零件P1的供应商号码SNO:
    SELECT DIST SNO FROM SPJ WHERE JNO='J1' AND PNO='P1'
    (3) 求供应工程J1零件为红色的供应商号码SNO:
    SELECT SNO FROM SPJ,P WHERE JNO='J1' AND SPJ.PNO=P.PNO AND COLOR='红'
    (4) 求没有使用天津供应商生产的红色零件的工程号JNO:
    SELECT DIST JNO FROM SPJ WHERE JNO NOT IN (SELE JNO FROM SPJ,P,S WHERE S.CITY='天津' AND COLOR='红' AND S.SNO=SPJ.SNO AND P.PNO=SPJ.PNO)。
    (5) 求至少用了供应商S1所供应的全部零件的工程号JNO。??????
       由于VFP不允许子查询嵌套太深,将查询分为两步
    A、查询S1供应商供应的零件号
    SELECT DIST PNO FROM SPJ WHERE SNO='S1'结果是(P1,P2)
    B、查询哪一个工程既使用P1零件又使用P2零件。
    SELECT JNO FROM SPJ WHERE PNO='P1'
    AND JNO IN (SELECT JNO FROM SPJ WHERE PNO='P2')
    5.针对习题3中的四个表试用SQL语言完成以下各项操作:
        (1)找出所有供应商的姓名和所在城市。
           SELECT SNAME,CITY FROM S
    (2)找出所有零件的名称、颜色、重量。
    SELECT PNAME,COLOR,WEIGHT FROM P
        (3)找出使用供应商S1所供应零件的工程号码。
            SELECT DIST JNO FROM SPJ WHERE SNO='S1'
    (4)找出工程项目J2使用的各种零件的名称及其数量。
    SELECT PNAME,QTY FROM SPJ,P
    WHERE P.PNO=SPJ.PNO AND SPJ.JNO='J2'
        (5)找出上海厂商供应的所有零件号码。
    SELECT PNO FROM SPJ,S WHERE S.SNO=SPJ.SNO AND CITY='上海'
    (6)出使用上海产的零件的工程名称。
    SELECT JNAME FROM SPJ,S,J
    WHERE S.SNO=SPJ.SNO AND S.CITY='上海' AND J.JNO=SPJ.JNO
        (7)找出没有使用天津产的零件的工程号码。
    注意: SELECT DISP JNO FROM SPJ WHERE JNO NOT IN (SELECT DIST JNO FROM SPJ,S WHERE S.SNO=SPJ.SNO AND S.CITY='天津') 适用于JNO是唯一或不唯一的情况.
        注意: SELECT DIST JNO FROM SPJ,S WHERE S.SNO=SPJ.SNO AND S.CITY<>'天津'适用于JNO是唯一的情况
    (8)把全部红色零件的颜色改成蓝色。
    UPDATE P SET COLOR='蓝' WHERE COLOR='红'
    (9)由S5供给J4的零件P6改为由S3供应。
        UPDATE SPJ SET SNO='S3' WHERE SNO='S5' AND JNO='J4' AND PNO='P6'
    (10)从供应商关系中删除供应商号是S2的记录,并从供应情况关系中删除相应的记录。
        A、DELETE FROM S WHERE SNO=’S2’
        B、DELETE FROM SPJ WHERE SNO=‘S2’
    (11)请将(S2,J6,P4,200)插入供应情况关系。
         INSERT INTO SPJ VALUES(‘S2’,‘J6’,‘P4’,200)

     

     

     

     

     

     

    注 :

    注 查询选修了所有课程的学生姓名的貌似比较容易理解的 算法

    select sname from student
    where sno in (
           select sno from sc
           group by sno
           having count(*) =
          ( select count(*) from course)
    )

    展开全文
  • 熟练掌握SQL的SELECT简单查询语句的使用。 熟练使用SQL语句进行多表连接查询、嵌套查询、集合查询等复杂查询。 熟练使用GROUP BY、HAVING、ORDER BY 等SQL子语句进行查询数据分组和排序
  • 数据库原理及运用实验五-复杂查询.doc
  • 数据库系统原理与设计实验报告,内容正确,全面,欢迎各位下载。
  • 大二数据库实验报告

    万次阅读 多人点赞 2018-05-23 18:22:22
    实验四 使用SQL语言进行复杂查询 实验五 SQL常用数据更新操作 实验六 综合应用\ 实验一:熟悉数据库管理系统环境 实验过程及分析: 首先创建一个数据库和需要的表: create database XSGL GO use XSGL ...

    实验要求:

    实验一 熟悉数据库管理系统环境
    实验二SQL定义语言
    实验三 使用SQL语言进行简单查询
    实验四 使用SQL语言进行复杂查询
    实验五 SQL常用数据更新操作
    实验六 综合应用\

    实验一:熟悉数据库管理系统环境

    实验过程及分析:
    首先创建一个数据库和需要的表:

    create database XSGL
    GO
    use XSGL
    GO
    Create table student      --创建学生表
    (sno char(8) primary key,        --(实体完整性)学生姓名
     sname char(8) not null unique, --学生姓名
     ssex char(2) default '男' check(ssex='男' or ssex='女'),  --性别给定默认值为'男',取值只能取‘男’或‘女’
     sage tinyint check(sage>13 and sage<50),
     sdept char(20))
    
    
    create table course       --创建课程表 
    (cno char(2) PRimary key,        --课程编号
     cname varchar(50),  --课程名称
     cpno char(2),       --先修课号
     ccredit tinyint)  --课程名
    
    create table sc        --创建成绩表
    (sno char(8),           --学生学号
     cno char(2),         --课程编号
     grade tinyint,           --成绩
     constraint pk_grade primary key(sno,cno),
     constraint fk_stuid foreign key(sno) references student(sno),
     constraint fk_course foreign key(cno) references course(cno),
     constraint ck_grade check(grade>=0 and grade<=100) )
    go
    insert into student(sno,sname, ssex,sage,sdept) values('95001', '李勇', '男', 20, 'CS')
    insert into student(sno,sname, ssex,sage,sdept) values('95002', '刘晨', '女', 19, 'IS')
    insert into student(sno,sname, ssex,sage,sdept) values('95003', '王敏', '女', 18, 'MA')
    insert into student(sno,sname, ssex,sage,sdept) values('95004', '张立', '男', 19, 'IS')
    insert into student(sno,sname, ssex,sage,sdept) values('95005', '刘云', '女', 18, 'CS ')
    insert into course(cno, cname,ccredit,cpno) values('1', '数据库', 4, '5')
    insert into course(cno, cname,ccredit,cpno) values('2', '数学', 6, null)
    insert into course(cno, cname,ccredit,cpno) values('3', '信息系统', 3, '1')
    insert into course(cno, cname,ccredit,cpno) values('4', '操作系统', 4, '6')
    insert into course(cno, cname,ccredit,cpno) values('5', '数据结构', 4, '7')
    insert into course(cno, cname,ccredit,cpno) values('6', '数据处理', 3, null)
    insert into course(cno, cname,ccredit,cpno) values('7', 'PASCAL语言', 4, '6')
    insert into sc(sno,cno,grade) values('95001', '1' ,92)
    insert into sc(sno,cno,grade) values('95001', '2' ,85)
    insert into sc(sno,cno,grade) values('95001', '3' ,88)
    insert into sc(sno,cno,grade) values('95002', '2' ,90)
    insert into sc(sno,cno,grade) values('95002', '3' ,80)
    insert into sc(sno,cno,grade) values('95003', '2' ,85)
    insert into sc(sno,cno,grade) values('95004', '1' ,58)
    insert into sc(sno,cno,grade) values('95004', '2' ,85)
    

    1)STUDENT表中增加一个字段入学时间scome,

    alter table student 
    add scome date

    2)删除STUDENT表中sdept字段;

    alter table student
    drop column sdept

    3)删除创建的SC表中cno字段和COURSE表cno字段之间的外键约束;

    alter table sc
    DROP fk_course

    4)重建3)中删除的约束

    alter table sc
    add constraint fk_course foreign key(cno) references course(cno)

    5、重新定义一个简单表,然后用SQL语言DROP语句删除该表结构;

    drop table sc

    6、用SQL语言CREATE INDEX语句定义表STUDENT的SNAME字段的降序唯一索引;

    create index index_sname
    on student(sname desc)

    7、用SQL语言DROP语句删除索引;

    drop index index_sname on student

    8.我觉得没有错误……

    实验总结:
    1. 创建表的时候可以添加约束
    2. 可以添加主键唯一标识 用primary key
    3. 可以使用默认值 是 default
    4. 可以使用外键来限制取值范围、
    5. 使用alter添加,修改列;还可以删除表中约束如索引 index
    6. 使用DROP 可以直接删除表 删除的时候先要删除外键表后才可以删除主键表

    实验二: SQL定义语言

    实验过程及分析:
    1.首先建立服务器连接 如果连接无法连接到本地local可以参考
    sqlserver2008无法连接到local解决方案
    2.然后打开新建查询选择数据库
    3.创建表用SQL语言,具体的过程实验一已经有了

    用SQL语言ALTER语句修改表结构;
    (1)STUDENT表中增加一个字段入学时间scome,
    (2)删除STUDENT表中sdept字段;
    (3)删除创建的SC表中cno字段和COURSE表cno字段之间的外键约束;
    (4)重建(3)中删除的约束
    5、重新定义一个简单表,然后用SQL语言DROP语句删除该表结构;
    6、用SQL语言CREATE INDEX语句定义表STUDENT的SNAME字段的降序唯一索引;
    7、用SQL语言DROP语句删除索引;

    有没有发现 和实验一重复了…………
    没错 所以为了凑字数 我再抄了一遍……

    实验总结:
    1. 删除外键只能用alter 指定表 而不能用on来选择表……原因不明
    2. 删除索引不能用alter 直接用DROP INDEX 索引 ON 表 原因不明

    实验三:使用SQL语言进行简单查询

    实验过程及分析:
    还是使用实验一的表
    (1)查询全体学生的学号和姓名

    select sno,sname 
    from student;

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

    select * 
    from student;

    (3)查询软件学院的学生姓名、年龄、系别

    select sname,sage,sdept
    from student where sdept='MA';

    (4)查询所有选修过课程的学生学号(不重复)

    select distinct sno 
    from sc;

    (5)查询考试不及格的学生学号(不重复)

    select distinct sno
    from sc
    where grade<60;

    (6)查询不是软件学院、计算机系的学生性别、年龄、系别

    select ssex,sage,sdept
    from student 
    where sdept not in('CS','MA');

    (7)查询年龄18-20岁的学生学号、姓名、系别、年龄;

    select sno,sname,sdept,sage 
    from student
    where sage>=18 and sage<=20;

    (8)查询姓刘的学生情况

    select * 
    from student
    where sname like '刘%';

    (9)查询姓刘或姓李的学生情况

    select * from student where sname like '刘%' or sname like '李%'

    (10)查询姓刘且名字为两个字的学生情况

    select * 
    from student
    where sname like '刘_';

    (11)查询1983年以后出生的学生姓名。

    select sname from student where sage < 2018-1983

    (12)创建表 studentgrad(sno,mathgrade,englishigrade,chinesegrade)
    计算学生各科总成绩并赋予别名

    Create table studentgrad(
        Sno char(8) ,
        mathgradeint,
        englishigradeint,
        chinesegradeint
    )
    Select sum(mathgrade+chinesegrade+englishigrade) '学生总成绩' from studentgrad 

    (13)利用内部函数 year()查找软件学院学生的出生年份

    select (year(getdate())-student.sage+1) 
    from student 
    where sdept='MA';

    (14)利用字符转换函数实现字符联接。

    select sname + '年龄为'+cast(sage as char(2))+'岁'
    from student;
    Select sname + ‘年龄为’+cast(sage as char(2))+’岁’
    From student

    (15)查询全体学生情况,查询结果按所在系升序排列,对同一系中的学生按年龄降序排列。

    select*
    from student order by sdept,sage desc;

    (16)查询学生总人数。

    select count(*) 
    from student;

    (17)查询选修了课程的学生人数。

    select count(distinct sno) 
    from sc;

    (18)查询选修了7号课程的学生总人数和平均成绩

    select count(*),avg(grade)as avggrade 
    from student ,sc 
    where student.sno=sc.sno and sc.cno='1';

    (19)查询选修6号课程学生的最好成绩

    select max(grade) as maxgrade 
    from sc
    where cno='2';

    (20)查询每个系的系名及学生人数。

    select sdept,count(*) 
    from student group by sdept;

    (21)查找每门课的选修人数及平均成绩

    select cno,count(*),avg(grade) as avggrade 
    from sc group by cno;

    (22)查找没有先修课的课程情况

    select * 
    from course 
    where cpno is null;

    实验总结:
    1. 函数year(),count(),max()可以方便查询
    2. 模糊查询法要% 如like ‘刘%’
    3. group by 可以分组查询

    实验四:使用SQL语言进行复杂查询

    实验过程及分析:
    1、实验一中的数据为基础
    2、对各表中的数据进行不同条件的连接查询和嵌套查询;
    (1)查询每个学生及其选课情况;

    select student.sno,sname,ssex,sage,sdept,cno,grade
    from student,sc
    where student.sno=sc.sno

    (2)查询每门课的间接先修课

    select first.cno,second.cpno
    from course first,course second
    where first.cpno=second.cno 

    (3)将STUDENT,SC进行右连接

    select student.sno,sname,ssex,sage,sdept,cno,grade
    from student right outer join sc on student.sno=sc.sno

    (4)查询既选修了2号课程又选修了3号课程的学生姓名、学号;

    select student.sno,sname
    from student inner join sc on student.sno=sc.sno
    where cno='3' and sc.sno in
    (select sno
    from sc
    where cno='2')

    (5)查询和刘晨同一年龄的学生

    select student.sno,sname
    from student
    where sname!='刘晨' and sage=
    (select sage 
    from student
    where sname='刘晨')

    (6)选修了课程名为“数据库”的学生姓名和年龄

    select sname,sage
    from student
    where sno in
    (select sno
    from sc
    where cno in
    (select cno
    from course 
    where cname='数据库'))

    (7)查询其他系比IS系任一学生年龄小的学生名单

    select student.sno,sname
    from student
    where sdept<>'IS' and
    sage<any
    (select sage 
    from student
    where sdept='IS')

    (8)查询其他系中比IS系所有学生年龄都小的学生名单

    select student.sno,sname
    from student
    where sdept<>'IS' and 
    sage<all
    (select sage 
    from student 
    where sdept='IS')

    (9)查询选修了全部课程的学生姓名

    select sname
    from student
    where Sno in
    (select Sno from SC
    group by Sno
    having count(*) = (select count(*) from course ))

    (10)查询计算机系学生及其性别是男的学生

    select student.sno,sname
    from student
    where sdept='IS' and ssex='男'

    (11)查询选修课程1的学生集合和选修2号课程学生集合的差集

    select sno
    from sc 
    where cno='1' except 
    select sno
    from sc
    where cno='2'

    (12)查询李丽同学不学的课程的课程号

    select cno
    from course
    where cno not in
    (select cno
    from sc
    where sno in
    (select sno
    from student
    where sname='李丽'))

    (13)查询选修了3号课程的学生平均年龄

    select AVG(sage) as avgsage
    from student inner join sc on student.sno=sc.sno
    where cno='3'

    (14)求每门课程学生的平均成绩

    select cno,AVG(grade) as avggrade
    from sc
    group by cno

    (15)统计每门课程的学生选修人数(超过3人的才统计)。要求输出课程号和选修人数,结果按人数降序排列,若人数相同,按课程号升序排列

    select course.cno '课程号', count(sc.sno) '人数'
    from course,sc 
    where course.cno=sc.cno 
    group by course.cno having count(sc.sno)>3 order by COUNT(sc.sno) desc,course.cno asc

    (16)查询学号比刘晨大,而年龄比他小的学生姓名。

    select sname
    from student
    where sno>
    (select sno from student where sname='刘晨')and
    sage<(select sage from student where sname='刘晨')

    (17)求年龄大于所有女同学年龄的男同学姓名和年龄

    select sname,sage
    from student
    where ssex='男'and sage>
    (select MAX(sage) from student where ssex='女')

    实验总结:
    1. 求总数可以用COUNT()函数
    2. 分组group by 要用having来限制条件
    3. order by是排序要求 desc是降序 ,asc是升序
    4. any()函数是任意的意思,all()是所有

    实验五:SQL的常用数据更新操作

    实验过程及分析:
    1、应用INSERT,UPDATE,DELETE语句进行更新操作;
    (1)插入如下学生记录(学号:95030,姓名:李莉,年龄:18)

    insert into student(sno,sname,sage)
    values ('95030','李莉',18)

    (2)插入如下选课记录(95030,1)

    insert into sc(sno,cno)
    values('95030',1)

    (3)计算机系学生年龄改成20

    update student
    set sage=20
    where sdept='CS'

    (4)把数学系所有学生成绩改成0

    update sc
    set grade=0
    where 'MA'=
    (select sdept
    from student
    where student.sno=sc.sno)

    (5)把低于总平均成绩的女同学成绩提高5分

    update sc 
    set grade+=5
    where grade<
    (select avg(grade) 
    from sc inner join student
    on student.sno=sc.sno
    where ssex='女')

    (6)删除95030学生信息

    delete
    from student
    where sno='95030'

    (7)删除SC表中无成绩的记录

    delete 
    from sc
    where grade is null;

    (8)删除张娜的选课记录

    delete
    from sc
    where sno=(select sno from student 
    where sname='张娜')

    (9)删除不及格的学生选课记录

    delete
    from sc
    where grade<60

    (10)删除数学系所有学生选课记录

    delete
    from sc
    where sno in (select sno from student where sdept='MA')

    (11)删除所有未被选修的课程

    delete
    from course
    where cno not in (select cno from sc)

    (12)查询每一门课程成绩都大于等于80分的学生学号、姓名和性别,把值送往另一个已经存在的基本表STU(SNO,SNAME,SSEX)中

    Create table STU 
    (sno char(8), 
    sname char(8), 
    ssex char(2) 
    )
    
    insert into STU(sno,sname,ssex)
    select distinct student.sno,sname,ssex
    from student,sc 
    where student.sno not in
    (select sno from sc where grade<80) and student.sno=sc.sno
    

    (13)建立一个sdeptgrade 表,包含(sdept,avggrade)字段,对每一个系,求学生的成绩,并把结果存入sdeptgrade

    Create table sdeptgrade 
    (sdept char(8) primary key, 
    avggrade int; ) 
    
    insert into sdeptgrade 
    select student.sdept, avg(sc.grade) 
    from student inner join SC on 
    (student.sno = SC.sno) group by student.sdept;

    实验总结:
    1. 删除主键表数据如果有外键约束就会报错
    2. 插入数据用insert into 表直接+表
    3. 更新用update
    4. 删除直接用delete 可以直接删除一行数据

    实验六:综合应用**

    实验过程及分析:
    建立一个数据库和五张表的表结构
    这里写图片描述
    这里写图片描述
    首先创建表:

    create database Person
    GO 
    use person
    GO
    Create table employee
    (
    emp_no char(5) primary key,
    emp_name char(10) not null,
    Sex char(1) not null,
    Dept char(4) not null,
    Title char(6) not null,
    data_hired datetime not null,
    birthday datetime null,
    salary int not null,
    Addr char(50) null,
    Mod_date datetime Default(getdate())
    )
    create table customer
    (
    cust_id char(5) primary key,
    cust_name char(20) not null,
    Addr char(40) not null,
    tel_no char(10) not null,
    Zip char(6) null
    )
    create table sales
    (
    order_no int primary key,
    cust_id char(5) not null,
    sale_id char(5) not null,
    tot_amt numeric(9,2) not null,
    order_date datetime not null,
    ship_date datetime not null,
    incoice_no char(10) not null
    )
    create table sale_item
    (
    order_no int not null,
    prod_id char(5) not null,
    Qty int not null,
    unit_price numeric(9,2) not null,
    order_date datetime null
    constraint primary_sale primary key(order_no,prod_id)
    )
    create table product
    (
    prod_id char(5) not null primary key,
    prod_naem char(20) not null
    )

    3、录入数据并实现实现如下查询
    (1)查找定单金额高于20000的客户编号;

    select cust_id from sales where tot_amt>2000

    (2)选取销售数量最多的前5条订单订单号、数量;

    select top 5 order_no,Qty from sale_item order by Qty DESC

    (3)显示sale_item表中每种个别产品的订购金额总和,并且依据销售金额由大到小排
    来显示出每一种产品的排行榜;

    select prod_id, sum(Qty*unit_price) '金额' from sale_item group by prod_id order by '金额' DESC

    (5)计算每一产品每月的销售金额总和,并将结果按销售(月份,产品编号)排序;

    select "s2".月份,SUM("s2".tot_amt) '销售金额总和',"s1".prod_id '产品编号'
    from sale_item "s1"
    join (select MONTH(order_date) '月份',order_no,tot_amt from sales) "s2"
    on "s1".order_no="s2".order_no
    group by "s2".月份,"s1".prod_id
    order by "s2".月份,"s1".prod_id

    (6)检索单价高于2400元的的产品编号、产品名称、数量、单价及所在订单号;

    select s.prod_id '产品编号',product.prod_name '产品名称',s.Qty '数量',s.unit_price '单价',s.order_no 
    from product INNER JOIN 
    (select order_no,prod_id,Qty,unit_price from sale_item where unit_price>2400) s
    on product.prod_id=s.prod_id

    (7)计算每一产品销售数量总和与平均销售单价;

    select "s1".销售总额,"s2".unit_price '平均销售单价'
    from(select SUM(Qty*unit_price) '销售总额',prod_id from sale_item group by prod_id) "s1"
    join sale_item "s2"
    on "s1".prod_id="s2" .prod_id

    (8)创建一个视图,该视图只含上海客户信息,即客户号、客户姓名、住址。

    CREATE VIEW view_name AS
    select cust_id,cust_name,Addr from customer where Addr='上海'

    实验总结:
    1. 设置主键,自动为 not null
    2. unique和主键区别:
    unique:唯一并且 一张表可设置多个unique 可空 但是只能有一行数据空
    主键: 唯一并且 一张表只能有一个主键
    何时用到主键?
    设置外键的时候需要主键 还有唯一标识一列的时候 比如身份证
    3. 主键可通过 constraint 主键名 primary key(列,列)来设置*组合键*
    4. 给表取别名的时候 不能用单引号,要用双引号或者不用引号 而给列取别名的时候可以选择单引号 或者 as 连接词 或者不用引号
    5. where之类的范围时 列=单引号内容时值 双引号为列名
    6. top 5表示 取前5名
    7. 视图是为了保存一张表 下次查找该表可直接 使用 如本实验中:

    select * from view_name

    即可查看 视图

    展开全文
  • 数据库实验报告.doc

    2021-06-22 00:59:09
    实验1-实验10 创建数据库 创建数据表 管理表数据 简单数据查询 复杂数据查询 视图 SQL程序设计 索引 数据库的维护
  • Sql Server 数据库实验报告

    万次阅读 多人点赞 2019-11-28 15:25:37
    数据库原理 实验报告 目录 实验一 SQL Server的安装和配置 4 一、实验目的 4 二、实验内容 4 三、实验步骤 4 四、实验代码和结果 4 五、实验总结与体会 4 实验二 创建数据库 5 一、实验目的 5 二、实验内容 5 三、...

    数据库原理 实验报告

    目录
    实验一 SQL Server的安装和配置 4
    一、实验目的 4
    二、实验内容 4
    三、实验步骤 4
    四、实验代码和结果 4
    五、实验总结与体会 4

    实验二 创建数据库 5
    一、实验目的 5
    二、实验内容 5
    三、实验步骤 5
    四、实验代码和结果 6
    五、实验总结与体会 6

    实验三 创建数据表 7
    一、实验目的 7
    二、实验内容 7
    三、实验步骤 7
    四、实验代码和结果 8
    五、实验总结与体会 9

    实验四 管理表数据 10
    一、实验目的 10
    二、实验内容 10
    三、实验步骤 10
    四、实验代码和结果 10
    五、实验总结与体会 13

    实验五 简单数据查询 14
    一、实验目的 14
    二、实验内容 14
    三、实验步骤 14
    四、实验代码和结果 14
    五、实验总结与体会 18

    实验六 高级数据查询 19
    一、实验目的 19
    二、实验内容 19
    三、实验步骤 19
    四、实验代码和结果 19
    五、实验总结与体会 20

    实验七 视图 21
    一、实验目的 21
    二、实验内容 21
    三、实验步骤 21
    四、实验代码和结果 21
    五、实验总结与体会 24

    实验八 索引 25
    一、实验目的 25
    二、实验内容 25
    三、实验步骤 25
    四、实验代码和结果 25
    五、实验总结与体会 26

    实验九 数据库维护 27
    一、实验目的 27
    二、实验内容 27
    三、实验步骤 27
    四、实验代码和结果 27
    五、实验总结与体会 27

    实验十 SQL程序设计 …28
    一,实验目的………………………………………………………………………………28
    二,实验内容………………………………………………………………………………28
    三,实验步骤………………………………………………………………………………28
    四,实验代码和结果………………………………………………………………………28
    五,实验总结和体会………………………………………………………………………29

    实验一 SQL Server的安装和配置

    一, 实验目的
    (1) 了解Microsoft SQL Server 2008系统。
    (2) 掌握SQL Server 2008的安装过程。
    (3)熟悉SQL Server Management Studio的工作环境。
    (4)掌握sQL Server 2008服务器注册与配置。

    二,实验内容
    (1) SQL Server 2008数据库管理系统安装。
    (2) SQL Server 2008 Management Studio 使用。
    (3) SQL Server 2008服务器配置。

    三,实验步骤
    (1)安装SQL Server 2008数据库管理系统。
    安装SQL Server 2008数据库管理系统,熟悉其安装过程。
    从微软官方下载SQL Server 2008 Enterprise Evaluation,将其安装到本地计算机,使本地计算机成为服务器和客户端工具,并采用Windows身份验证模式。
    SQL Server 2008的安装与其他Microsoft Windows系列产品类似。用户可根据向导提示,选择需要的选项一步一步地完成。安装过程中涉及的实例名、用户账户、身份验证模式、排序规则等关键内容需要我们根据安装界面的提示和实际的需要来进行设置。
    (2)启动SQL Server Management Studio.
    启动SQL Server Management Studio,熟悉其界面环境。
    在SQL Server以前的版本中主要有两个工具:图形化的管理工具(EnterpriseManager)和T-SQL编辑器(Query Analyzer)。 在SQL Server 2008 中,SQL ServerManagement Studio将Enterprise Manager和Query Analyzer两个工具结合在一一起,可以在对服务器进行图形化管理的同时编写T-SQL. SQL Server Management Sudio中的对象浏览器结合了Query Analyzer的对象浏览器和Enerpie Manager的服务器树形视图,可以浏览所有已注册的服务器。另外,对象浏览器还提供了类似与Query Analyzer的工作区,工作区中有类似语言解析器和显示统计图的功能。现在可以在编写查询和脚本的同时,在同一个工具下使用Wizards和属性页面处理对象。
    (3)注册服务器。
    服务器只有在注册后才能被纳人SQL Server Management Studio的管理范围。为了18 管理、配置和使用Microsoft SQL Server 2008 系统,必须使用Microsoft SQL Server
    Management Studio工具注册服务器。注册服务器就是为Microsoft SQL Server 客户机/服务器系统确定一台数据库所在的机器,该机器作为服务器,可以为客户端的各种请求提供服务。在SQL Server Management Studio中有一个单独可以同时处理多台服务器的注册服务器窗口,同时其不仅可以对服务器进行注册,还可以注册分析服务、报告服务、SQLServer综合服务以及移动SQL等。
    (4)配置SQL Server 2008。
    利用SQL Server 2008配置管理器,对数据库管理系统进行配置。
    启动SQL Server配置管理器,查看与SQL Server相关联的服务,并尝试启动和停止服务、配置SQL Server使用的网络协议以及从sQL Server客户端计算机管理网络连接配置。

    四,实验代码和结果

    五,实验总结和体会
    成功安装了SQL Server 2008,步骤不算复杂,但需要自己动手配置的地方很多,安装总体来说还算顺利。

    实验二 创建数据库

    一,实验目的
    (1)了解sQL Server 数据库的物理结构和逻辑结构。
    (2)掌握使用对象资源管理器创建和管理数据库。
    (3)掌握使用T-SQL语句创建和管理数据库。

    二,实验内容
    (1)利用对象资源管理器创建、修改和删除数据库。
    (2)利用T-SQL语句创建修改和删除数据库。

    三,实验步骤
    (1)启动sQL Server Management Studio,在对象资源管理器中,利用图形化的方法创
    建数据库student.
    (2)启动SQL Server Management Studio,在对象资源管理器中,利用图形化的方法修改数据库student,增加数据文件。
    其中:数据文件逻辑名student_ data2,操作系统文件的名称为C:\Program Files\Microsoft SQL Server\MSSQL10. MSSQLSERVER\ MSSQL\DATA\student_ data2. ndt,
    初始大小为50MB,最大为100MB,以30%的速度增长。
    (3)启动SQL Server Management Studio,在对象资源管理器中,利用图形化的方法删除数据库student。
    (4)启动SQL Server Management Studio,在SQL编辑器中,利用T-SQL语何
    CREATE DATABASE命令创建数据库student.
    (5) 启动SQL Server Management Studio,在 SQL编辑器中,利用T-SQL语句ALTER DATABASE命令修改数据库student,增加日志文件。
    其中:日志文件逻辑名student_ log2,操作系统文件的名称为 C:\Program Files\Microsoft SQL Server\MSSQL10. MSSQLSERVER\MSSQL\DATA\student_ data2. ldf,初始大小为3MB,最大为50MB,以1M的速度增长。
    (6)启动SQL Server Management Studio,在SQL编辑器中,利用T-SQL语句DROPDATABASE命令删除数据库student。

    四,实验代码和结果
    –创建数据库student
    CREATE DATABASE student
    ON PRIMARY
    (
    NAME=student_data,
    FILENAME=‘E:\数据库\student_data.mdf’,
    SIZE=3,
    MAXSIZE= UNLIMITED,
    FILEGROWTH=1
    )
    LOG ON
    (
    NAME=student_log,
    FILENAME=‘E:\数据库\student_log.ldf’,
    SIZE= 1,
    MAXSIZE= 20,
    FILEGROWTH=10%
    )
    –修改数据库student,增加日志文件
    alter database student
    add log file
    (
    NAME=student_log2,
    FILENAME=‘E:\数据库\student_log2.ldf’,
    SIZE= 3,
    MAXSIZE= 50,
    FILEGROWTH=1
    )
    –删除数据库student
    drop database student

    五,实验总结和体会
    除了可以通过对象资源管理器的图形化界面创建数据库外,还可以使用T-SQL语言所提供的CREATE DATABASE语句来创建数据库。通过实验个人认为,后一种方法更加简单有效。

    实验三 创建数据表

    一,实验目的
    (1)了解SQL Server表的结构特点。
    (2)了解SQL Server 2008的基本数据类型。
    (3)掌握对象资源管理器创建和管理数据表。
    (4)掌握T-SQL语句创建和管理数据表。
    (5)理解约束的概念。

    二,实验内容
    (1)在对象资源管理器创建修改和删除数据表。
    (2)利用T-SQL语句创建、修改和删除数据表。
    (3)创建主键约束、缺省约束、check约束、唯一约束和外键约束。

    三,实验步骤
    (1)启动SQL Server Management Studio,在对象资源管理器中,利用图形化的方法创建学生信息表stu_info、课程信息表course_info和学生成绩表stu_grade,其结构参考附录A。
    (2)启动SQL Server ManagementStudio,在对象资源管理器中,利用图形化的方法对数据表进行修改:
    *在数据表stu_info中,增加备注字段,字段名memo,字段类型nvarchar,字段长度
    200,允许为空。
    *在数据表stu_info中,删除备注字段memo。
    *在数据表course_info中,对于字段course_name设置UNIQUE约束。
    *在数据表stu_grade中,对于字段grade设置CHECK约束,其取值在0~100之间。
    *在数据表stu_grade中,对于字段stu_id设置FOREIGN KEY约束,其取值参考数据 表stu_info中stu_id 字段取值。
    (3)启动SQL Server Management Studio,在对象资源管理器中,利用图形化的方法删除数据表stu_grade.
    (4)启动SQL Server Management Studio,在 SQL编辑器中,利用T-SQL语句CREATE TABLE命令创建学生信息表stu_info、课程信息表course_info 和学生成绩表
    stu_grade,其结构参考附录A。
    (5)启动SQL Server Management Studio,在SQL编辑器中,利用T-SQL语句ALTER TABLE命令修改数据表:
    *在数据表stu_info中,增加身份证号码字段,字段名code,字段类型char,字段长度
    18,允许为空。
    *在数据表stu_info中,对于字段code设置UNIQUE约束。
    *在数据表stu_info中,删除身份证号码字段code.
    *在数据表stu_grade中,对于字段grade设置CHECK约束,其取值在0~100之间,
    *在数据表stu_grade中,对于字段course_id 设置FOREIGN KEY约束,其取值参 考数据表course_info中course_id字段取值。
    (6)启动SQL Server Management Studio,在SQL编辑器中,利用T-SQL语句DROP TABLE 命令删除数据表stu_info。

    四,实验代码和结果
    –创建数据表
    create table stu_info(
    stu_id char(10) not null constraint pk_stu_id primary key, --主键
    name nvarchar(20) not null,
    birthday date null,
    sex nchar(1) null default ‘男’,
    address nvarchar(20) null,
    mark int null,
    major nvarchar(20) null,
    sdept nvarchar(20) null,
    );
    GO
    CREATE TABLE course_info(
    course_id char(3) NOT NULL constraint pk_course_id primary key, --主键
    course_name nvarchar(20) NOT NULL,
    course_type nvarchar(20) NULL default ‘考试’,
    course_mark tinyint NULL,
    course_time tinyint NULL,
    pre_course_id char(3) NULL constraint fk_pre_course_id foreign key references course_info(course_id), --外键
    );
    GO
    CREATE TABLE stu_grade(
    stu_id char(10) not null CONSTRAINT fk_stu_id FOREIGN KEY REFERENCES stu_info(stu_id), --外键
    course_id char(3) not null CONSTRAINT fk_course_id FOREIGN KEY REFERENCES course_info(course_id), --外键
    grade tinyint null,
    );
    –stu_grade:stu_id与course_id合在一起作为主键
    GO
    ALTER TABLE stu_grade
    ADD CONSTRAINT pk_stu_course PRIMARY KEY(stu_id,course_id)
    –修改数据表
    GO
    ALTER TABLE stu_info
    ADD code char(18) NULL
    GO
    ALTER TABLE stu_info
    ADD CONSTRAINT un_code UNIQUE --UNIQUE约束:确保某个或某些列(非主键列)没有相同的列值
    GO
    ALTER TABLE stu_info
    DROP COLUMN code
    GO
    ALTER TABLE stu_grade
    ADD CONSTRAINT ck_grade CHECK(grade between 0 and 100) --CHECK约束:限制输入到一列或多 列的值的范围
    ----删除数据表
    –DROP TABLE stu_info

    五,实验总结和体会
    创建数据表的一般步骤为:首先定义表结构,即给表的每一列取列名,并确定每一列的数据类型、数据长度、列数据是否可以为空等;然后,为了限制某列数据的取值范围,以保证输入数据的正确性和一致性而设置约束;当表结构和约束建立完成之后,最后就可以向表中输入数据了。

    实验四 管理表数据

    一,实验目的
    (1)掌握在对象资源管理器中对数据表进行插人、修改和删除数据的操作。
    (2)掌握T-SQL语句对数据表进行插人、修改和删除数据的操作。

    二,实验内容
    (1)利用对象资源管理器向数据表中添加、修改和删除数据。
    (2)使用T-SQL语句向数据表中添加、修改和删除数据。

    三,实验步骤
    (1)启动SQL Sever Mangement Sudi,在对象资源管理器中,利用图形化的方法向学生信息表stu_info、课程信息表course. info和学生成绩表stu grade中添加数据,其数据
    内容参考附录B。
    (2)启动SQL Server Management Studio,在对象资源管理器中,利用图形化的方法对
    表数据进行修改:
    *在数据表stu_info中,将学号(stu_ id)为2007070101同学的籍贯address)改为“河
    南洛阳”。
    *在数据表stu_ grade中,将成绩(grade)小于60分的所有同学成绩增加10%。
    (3)启动SQL Server Management Studio,在对象资源管理器中,利用图形化的方法删除数据表stu_ grade中成绩(grade)小于60分的记录。
    (4) 启动SQL Server Management Studio,在SQL编辑器中,利用T-SQL语句INSERT INTO命令向学生信息表stu_ info、 课程信息表course_ info 和学生成绩表stu_grade中添加数据,其数据内容参考附录B。
    (5)启动SQL Server Management Studio,在SQL编辑器中,利用T-SQL语句UPDATE命令修改表数据:
    *在数据表stu info 中,将学号(stu_ id) 为2007070102同学的院系(sdept)改为“会计 学院”。
    *在数据表stu grade中,将成绩(grade)小于60分的所有同学成绩置空。
    (6)启动sQL Server Management Studio,在SQL 编辑器中,利用T-SQL语句DELETE命令删除数据表stu info中所有性别(sex)为空的记录。

    四,实验代码和结果
    –添加表数据
    INSERT INTO stu_info(Stu_id,name,sex,birthday,address,mark,major,sdept)
    VALUES(‘2007070101’,‘张元’,‘男’,‘1985-10-09’,‘河南许昌’,576,‘计算机科学与技术’,‘信息学院’)
    INSERT INTO stu_info(Stu_id,name,sex,birthday,address,mark,major,sdept)
    VALUES(‘2007070102’,‘张红’,‘女’,‘1985-01-14’,‘河南开封’,565,‘计算机科学与技术’,‘信息学院’)
    INSERT INTO stu_info(Stu_id,name,sex,birthday,address,mark,major,sdept )
    VALUES(‘2007070103’,‘王明’,‘男’,‘1986-07-08’,‘河南洛阳’,570,‘计算机科学与技术’,‘信息学院’)
    INSERT INTO stu_info(Stu_id,name,sex,birthday,address,mark,major,sdept )
    VALUES(‘2007070104’,‘李伟’,‘男’,‘1986-03-11’,‘河南郑州’,564,‘计算机科学与技术’,‘信息学院’)
    INSERT INTO stu_info(Stu_id,name,sex,birthday,address,mark,major,sdept )
    VALUES(‘2007070201’,‘郑澜’,‘女’,‘1985-12-01’,‘河南平顶山’,567,‘电子商务’,‘信息学院’)
    INSERT INTO stu_info(Stu_id,name,sex,birthday,address,mark,major,sdept )
    VALUES(‘2007070202’,‘赵恒’,‘男’,‘1986-03-11’,‘河南周口’,566,‘电子商务’,‘信息学院’)
    INSERT INTO stu_info(Stu_id,name,sex,birthday,address,mark,major,sdept )
    VALUES(‘2007070203’,‘张兰’,‘女’,‘1986-03-11’,‘河南许昌’,571,‘电子商务’,‘信息学院’)
    INSERT INTO stu_info(Stu_id,name,sex,birthday,address,mark,major,sdept )
    VALUES(‘2007080101’,‘李伟’,‘男’,‘1986-03-11’,‘河南郑州’,578,‘会计学’,‘会计学院’)
    INSERT INTO stu_info(Stu_id,name,sex,birthday,address,mark,major,sdept )
    VALUES(‘2007080102’,‘钱丽’,‘女’,‘1986-03-11’,‘河南安阳’,573,‘会计学’,‘会计学院’)
    INSERT INTO stu_info(Stu_id,name,sex,birthday,address,mark,major,sdept )
    VALUES(‘2007080201’,‘孙楠’,‘男’,‘1986-1-19’,‘河南南阳’,578,‘财务管理’,‘会计学院’)
    INSERT INTO course_info(course_id,course_name,course_type,course_mark,course_time,pre_course_id)
    VALUES(‘701’,‘计算机基础’,‘考试’,3,50,NULL)
    INSERT INTO course_info(course_id,course_name,course_type,course_mark,course_time,pre_course_id)
    VALUES(‘702’,‘操作系统’,‘考试’,4,50,701)
    INSERT INTO course_info(course_id,course_name,course_type,course_mark,course_time,pre_course_id)
    VALUES(‘703’,‘计算机网络’,‘考试’,4,50,701)
    INSERT INTO course_info(course_id,course_name,course_type,course_mark,course_time,pre_course_id)
    VALUES(‘704’,‘数据库原理’,‘考察’,3,50,701)
    INSERT INTO course_info(course_id,course_name,course_type,course_mark,course_time,pre_course_id)
    VALUES(‘706’,‘Java’,‘考察’,3,40,704)
    INSERT INTO course_info(course_id,course_name,course_type,course_mark,course_time,pre_course_id)
    VALUES(‘801’,‘宏观经济学’,‘考试’,4,50,NULL)
    INSERT INTO course_info(course_id,course_name,course_type,course_mark,course_time,pre_course_id)
    VALUES(‘802’,‘初级会计’,‘考试’,4,50,NULL)
    INSERT INTO course_info(course_id,course_name,course_type,course_mark,course_time,pre_course_id)
    VALUES(‘803’,‘财政学’,‘考试’,3,50,NULL)
    INSERT INTO course_info(course_id,course_name,course_type,course_mark,course_time,pre_course_id)
    VALUES(‘804’,‘会计电算化’,‘考查’,3,NULL,NULL)
    INSERT INTO stu_grade(stu_id,course_id,grade)
    VALUES(‘2007070101’,‘701’,89)
    INSERT INTO stu_grade(stu_id,course_id,grade)
    VALUES(‘2007070101’,‘702’,81)
    INSERT INTO stu_grade(stu_id,course_id,grade)
    VALUES(‘2007070101’,‘703’,96)
    INSERT INTO stu_grade(stu_id,course_id,grade)
    VALUES(‘2007070102’,‘701’,85)
    INSERT INTO stu_grade(stu_id,course_id,grade)
    VALUES(‘2007070102’,‘702’,74)
    INSERT INTO stu_grade(stu_id,course_id,grade)
    VALUES(‘2007070102’,‘703’,77)
    INSERT INTO stu_grade(stu_id,course_id,grade)
    VALUES(‘2007070104’,‘701’,91)
    INSERT INTO stu_grade(stu_id,course_id,grade)
    VALUES(‘2007070104’,‘702’,88)
    INSERT INTO stu_grade(stu_id,course_id,grade)
    VALUES(‘2007080101’,‘801’,79)
    INSERT INTO stu_grade(stu_id,course_id,grade)
    VALUES(‘2007080101’,‘802’,91)
    INSERT INTO stu_grade(stu_id,course_id,grade)
    VALUES(‘2007080102’,‘801’,87)
    INSERT INTO stu_grade(stu_id,course_id,grade)
    VALUES(‘2007080102’,‘802’,83)
    INSERT INTO stu_grade(stu_id,course_id,grade)
    VALUES(‘2007080201’,‘803’,75)
    INSERT INTO stu_grade(stu_id,course_id,grade)
    VALUES(‘2007080201’,‘804’,82)
    –修改表数据
    GO
    UPDATE stu_info
    SET sdept='会计学院’WHERE Stu_id =‘2007070102’
    GO
    DELETE FROM stu_grade WHERE grade<‘60’
    –删除stu_info中所有性别(sex)为空的记录
    GO
    DELETE FROM stu_info WHERE sex=‘男’ and sex=‘女’

    五,实验总结和体会
    如果向表中所有列都要插入数据时,字段名表可以省略,但必须保证VALUES后的各数据项位置和类型与表结构的定义完全一致,使得输入的数据真实有效。
    在插人数据时,对于允许空值的列,可以使用用NUL插入空值;对于具有默认值的列,
    可以使用 DEFAULT插人默认值。
    INSERT INTO除了能够实现一次插入一条记录以外,也可以通过子查询实现一次插入多条记录。

    实验五 简单数据查询

    一,实验目的
    (1) 掌握SELECT语句的基本语法。
    (2)掌握数据汇总的方法。
    (3) 掌握SELECT语句的GROUP BY子句的作用和使用方法。
    (4)掌握SELECT语句的ORDER BY子句的作用和使用方法。

    二,实验内容
    (1) SELECT语句的基本使用。
    (2)数据汇总。
    (3) GROUP BY子句和ORDER BY、COMPUTEBY子句的使用。

    三,实验步骤
    针对student数据库中,stu_info数据表,course_info数据表和stu_grade数据表,完成下列查询。
    (1) SELECT基本使用
    查询每个同学的所有数据。
    查询每个同学的学号、姓名和院系。
    查询学号为2070同学的姓名、性别、籍贯和院系。
    查询每个女同学的学号,姓名和院系,并将结果中各列的标题指定为学号、姓名和
    院系。
    查询计算每个同学的年龄。
    查询所有其籍贯含有“阳”的同学的姓名、性别、籍贯。
    查询课程编号702,且成绩 在70~80之间的同学的学号。
    (2)数据汇总
    查询信息学院同学入学平均分。
    查询全体同学的入学最高分和最低分。
    查询会计学院同学总人数。
    查询学号为207070101同学的各门课程总分。
    (3) GROUP BY
    查询每个院系的总人数。
    查询每位同学的平均分。
    (4) ORDER BY
    将各位同学的信息按入学成绩由高到低排列输出。
    查询每个同学的学号、姓名、课程名和成绩信息,并按成绩由低到高排列输出。

    四,实验代码和结果
    –select 基本使用
    GO
    SELECT*
    FROM stu_info
    GO
    SELECT*
    FROM stu_grade
    GO
    SELECT*
    FROM course_info
    GO
    SELECT stu_id,name,sdept
    FROM stu_info
    GO
    SELECT name,sex,address,sdept
    FROM stu_info
    WHERE stu_id=‘2007070103’
    GO
    SELECT stu_id AS’学号’,name AS’姓名’,sdept AS’院系’
    FROM stu_info
    WHERE sex=‘女’
    GO
    SELECT birthday
    FROM stu_info
    GO
    SELECT name,sex,address
    FROM stu_info
    WHERE address like ‘%阳%’
    GO
    SELECT grade
    FROM stu_grade
    WHERE course_id=702 and grade between 70 and 80
    –数据汇总
    GO
    SELECT AVG(mark)as’平均分’
    FROM stu_info
    WHERE sdept=‘信息学院’
    GO
    SELECT MAX(mark)as’最高分’,MIN(mark)as’最低分’
    FROM stu_info
    GO
    SELECT COUNT()as’会计学院总人数’
    FROM stu_info
    WHERE sdept=‘会计学院’
    GO
    SELECT sum(grade)
    FROM stu_grade
    WHERE Stu_id=2007070101
    –GROUP BY
    GO
    SELECT sdept,COUNT(
    )as’总人数’
    from stu_info
    group by sdept
    GO
    SELECT stu_id,AVG(grade)as’平均分’,COUNT()as’课程数’
    from stu_grade
    group by stu_id
    –ORDER BY
    GO
    SELECT

    FROM stu_info
    order by mark desc --desc:降序排列
    GO
    SELECT stu_info.stu_id,name,course_name,grade
    FROM stu_info,course_info,stu_grade
    WHERE stu_info.Stu_id=stu_grade.Stu_id and stu_grade.course_id =course_info.course_id
    order by grade asc --asc:升序(默认)

    五,实验总结和体会
    实验时要注意查询要求的详细描述,先确定要查询的表然后确定要输出的列和行,如果没有指定输出列,默认为输出所有列。
    在数据查询时,经常需要对表中的列进行计算,才能获得所需要的结果。在SELECT
    子句中可以使用各种运算符和函数对指定列进行运算
    在SQL语言中, ORDER BY子句用于排序。 ORDER BY子句总是在 WHERE子句(如果有的话)后面说明的,可以包含一个或多个列,每个列之间以逗号分隔,可以选择使用ASC/ DESC关键字指定按照升序降序排序。如果没有特别说明値長以升序序列进行排序的。如用于多列进行排序,各列在 ORDER BY子句中的顺序决定了排序过程中的优先级。
    使用 GROUP BY子包为每一个组产生一个汇总结果,每个组只返回一行,不返回详细信息。SELECT子句句中指定的列必须是 GROUP BY子句中指定的列,或是和聚合函数起使用。如果包含 WHERE子句,则只对满足 WHERE条件的行进行分组汇总。如果 GROUP BY子句使用关键字ALL,则 WHERE子句将不起作用。

    实验六 高级数据查询

    一,实验目的
    (1)掌握嵌套查询、连接查询的表示。

    二,实验内容
    (1) 嵌套查询的使用。
    (2) 连接查询的使用。

    三,实验步骤
    针对student数据库中,stu_info数据表,course_info数据表和stu_grade数据表,完成下列查询。
    (1)嵌套查询
    查询选修702课程的同学的学号、姓名和院系信息。
    查询没有选修702课程的同学的学号、姓名和院系信息。
    查询比会计学院的同学人学分数都高的同学的学号、姓名、专业和院系信息。
    (2)连接查询
    查询每个同学的学号、姓名、课程名和成绩信息。
    查询人学成绩大于575分的同学姓名和其选课情况(包括课程名称、课程学时和成绩)。

    四,实验代码和结果
    –嵌套查询
    GO
    SELECT stu_id,name,sdept
    FROM stu_info
    WHERE stu_id IN (
    SELECT stu_id
    FROM stu_grade
    WHERE course_id=702
    )
    GO
    SELECT stu_id,name,sdept
    FROM stu_info
    WHERE stu_id not IN (
    SELECT stu_id
    FROM stu_grade
    WHERE course_id=702
    )
    GO
    SELECT stu_id,name,major,sdept
    FROM stu_info
    WHERE mark>ALL
    (SELECT mark
    FROM stu_info
    WHERE sdept=‘会计学院’)
    AND sdept<>‘会计学院’
    –连接查询
    GO
    SELECT stu_info.Stu_id,name,course_name,grade
    FROM stu_info
    left outer join stu_grade on stu_info.Stu_id=stu_grade.Stu_id
    left outer join course_info on stu_grade.course_id=course_info.course_id
    GO
    SELECT name,course_name,course_time,grade
    FROM stu_info,course_info,stu_grade
    WHERE stu_info.Stu_id=stu_grade.Stu_id and stu_grade.course_id =course_info.course_id and mark>575

    五,实验总结和体会
    表的连接的实现可以通过两种方法:利用 SELECT语句的 WHERE子句。在FROM子句中使用JOIN关键字。由于连接是涉及多个表及其之间的引用,所以列的引用必须明确指出,对于重复的列名必须用表名限定,即 Table_name. Column_name的完整表达方式。
    所谓嵌套查询指的是在一个SELECT査询语句中包含另一个(或多个)SELECT查询语句。其中,外层的SELECT查询语句叫外部查询,内层的的SELECT査询语句叫子查询。使用子查询时需注意以下几个问题:子查询可以嵌套多层。子查询需用圆括号括起来。子查询中不能使用COMPUTE[BY]和INTO子句。子查询的SELECT语句中不能使用 Image、text或 ntext数据类型。

    实验七 视图

    一,实验目的
    (1)理解视图的重要性。
    (2)掌握在对象资源管理器中创建和管理视图。
    (3)掌握T-SQL语句创建和管理视图。

    二,实验内容
    (1)在对象资源管理器创建、修改和删除视图。
    (2)在对象资源管理器使用视图。
    (3)利用T-SQL语句创建、修改和删除视图。
    (4)利用T-SQL语句使用视图。

    三,实验步骤
    (1)启动SQL Server Management Studio,在对象资源管理器中,利用图形化的方法创建下列视图:
    视图view_male,包含学生信息表中所有男生信息。
    视图view_ stu_grade,包含每个同学的学号、姓名、课程名和成绩信息。
    视图view_ avg, 包含每个同学的学号、姓名、平均成绩信息。
    (2)启动SQL Server Management Studio,在对象资源管理器中,利用图形化的方法对视图v_ male进行修改,只显示信息学院所有男生的信息。
    (3)启动SQL Server Management Studio,在对象资源管理器中,利用图形化的方法删除视图view_ male.
    (4)启动SQL Server Management Studio,在对象资源管理器中,利用图形化的方法查询视图view_ male 中的记录信息。
    (5)启动SQL Server Management Studio,在 SQL编辑器中,利用T-SQL语句CREATE VIEW命令创建下列视图:
    视图view. female, 包含学生信息表中所有女生信息。
    视图view. count,包含每个院系的名称和学生人数信息。
    视图view sum,包含每个同学的学号、姓名、课程总成绩信息。
    (6)启动SQL Server Management Studio在SQL编辑器中,利用TSQL语句ALTER VIEW命令修改视图view _female,增加加密性。
    (7)启动SQL Server Management Studio,在SQL编辑器中,利用T-SQL语句DROP
    VIEW命令删除视图view_female.
    (8)启动SQL Server Management Studio,在SQL编辑器中通过视图,利用T-SQL语句查询下列信息:
    查询“信息学院”的学生人数。
    查询学号2007070101同学的所选课程和课程成绩信息。

    四,实验代码和结果
    –创建视图
    create view view_female
    with encryption --加密
    as
    select*
    from stu_info
    where sex=‘女’
    go
    create view view_count(sdept,count)
    as
    select sdept,count() as’学生人数’
    from stu_info
    group by sdept
    go
    create view view_sum(学号,姓名,总成绩)
    as
    select stu_info.stu_id,stu_info.name,sum(grade)
    from stu_info left outer join stu_grade
    on stu_info.stu_id=stu_grade.stu_id
    group by stu_info.stu_id,name
    go
    create view view_stu_grade
    as
    select stu_info.stu_id,name,course_name,grade
    from stu_info
    left outer join stu_grade on stu_info.Stu_id=stu_grade.Stu_id
    left outer join course_info on stu_grade.course_id=course_info.course_id
    ----删除视图
    –go
    –drop view view_female
    –查询信息
    go
    select count(
    ) as’学生人数’
    from view_female
    where sdept=‘信息学院’
    go
    select*
    from view_stu_grade
    where stu_id=‘2007070101’

    五,实验总结和体会
    视图的定义和属性信息都保存在系统数据库和系统数据表中,可以通过系统提供的存
    储过程来获取有关视图的定义信息。sp help用于返回视图的特征信息。sp_ helptext查看视图的定义文本。sp_ depends查看视图对表的依赖关系和引用的字段。
    通过本次实验,我练习了在对象资源管理器中创建、修改、删除和使用视图,以及利用T-SQL语句创建、修改、删除和使用视图,理解了视图的重要性,掌握了在对象资源管理器中和利用T-SQL语句创建和管理视图。

    实验八 索引

    一,实验目的
    (1)理解索引的概念和分类。
    (2)掌握在对象资源管理器中创建和管理索引。
    (3)掌握T-SQL语句创建和管理索引。

    二,实验内容
    (1)在对象资源管理器创建、修改和删除索引。
    (2)在对象资源管理器使用索引。
    (3)利用T-SQL语句创建、修改和删除索引。(4)利用T-SQL语句使用索引。

    三,实验步骤
    (1)启动SQL Server Management Studio,在对象资源管理器中,利用图形化的方法创建下列索引:
    *对学生信息表stu_info的name列创建非聚集索引idx_name。
    *对学生成绩表stu_ info 的stu_ id .course_ id列创建复合索引idx stu course_ id
    (2)启动SQL Server Management Studio,在对象资源管理器中,利用图形化的方法对索引idx_ name进行修改,使其成为唯一索引。
    (3)启动SQL Server Management Studio,在对象资源管理器中,利用图形化的方法删除索引idx_ stu_ course_ id。
    (4)启动SQL Server Management Studio,在 SQL编辑器中,利用T-SQL语句CREATE INDEX命令创建下列索引:
    *对课程信息表course_info的course_name列创建非聚集索引idx_course _name。
    *对学生成绩表stu_grade的stu_id、course_id列创建复合索引idx_stu course id。
    (5)启动SQL Server Management Studio,在sQL编辑器中,利用T-SQL语句对索引
    idx
    course_name进行修改,使其成为唯索引。
    (6)启动SQL Server Management Studio,在SQL编辑器中,利用系统存储过程sp_helpindex查看索引idx course _name信息。
    (7)启动SQL Server Management Sudio,在sQL编辑器中,利用T-SQL语句DROPINDEX命令删除索引idx course _name.

    四,实验代码和结果
    –创建索引
    CREATE UNIQUE NONCLUSTERED INDEX idx_course_name --唯一非聚集
    ON course_info(course_name)
    GO
    CREATE INDEX idx_stu_course_id
    ON stu_grade(stu_id,course_id)
    –查看
    GO
    Exec sp_helpindex course_info
    –删除
    GO
    DROP INDEX course_info.idx_course_name

    五,实验总结和体会
    在 SQL Server2008中,提供了多种索引类型。如果以存储结构来区分,则有“聚集索
    引”( Clustered Index,也称聚类索引、簇集索引)和“非非聚集索引”( Nonclustered Index,也称非聚类索引、非簇集索引)的的区别;如果以数据的唯一性来区别,则有“唯一索引”( Unique Ihdex)和“非唯一索引”( Nonunique Index)的不同;若以键列的个数来区分,则有“单列索引”与“多列索引”的分别。
    利用系统提供的存储过程sp_ rename可以对索引进行重命名。
    利用系统提供的存储过程sp_ helpindex可以查看索引信息,其语法格式如下:
    sp_helpindex [@objname =]’object_name’,其中,[@objname =]’object_name’表示所要查看的当前数据库中表的名称。

    实验九 数据库维护

    一,实验目的
    (1)理解备份与恢复的意义。
    (2)掌握在对象资源管理器中备份和恢复数据库。
    (3)掌握T-SQL语句备份和恢复数据库。

    二,实验内容
    (1)在对象资源管理器中备份和恢复数据库。
    (2)利用T-SQL语句备份和恢复数据库。

    三,实验步骤
    (1)启动SQL Server Management Studio, 在对象资源管理器中,利用图形化的方法对数据库student进行完整备份。
    (2)启动SQL Server Management Studio,在对象资源管理器中,利用图形化的方法,针对(1)中的完整备份,对数据库student进行恢复。
    (3)启动SQL Server Management Studio,在SQL编辑器中,利用T-SQL语句BACKUP命令对数据库student进行完整备份。
    (4)启动SQL Server Management Studio,在 sQL编辑器中,利用T-SQL语句
    RESTORE命令,针对()中的完整备份对数据库sudent 进行恢复。

    四,实验代码和结果
    –完整备份
    Backup Database student
    To Disk=‘E:\数据库\备份\student.bak’

    –恢复
    Restore Database student
    From Disk=‘E:\数据库\备份\student.bak’

    五,实验总结和体会
    数据库的备份和恢复是数据库管理员维护数据库安全性和完整性必不可少的操作,合
    理地进行备份和恢复可以将可预见的和不可预见的问题对数据库造成的伤害降到最低。当
    运行 SQL Server的服务器出现故障,或数据库遭到某种程度的破坏时,可以利用以前对数
    据库所做的备份重建或恢复数据库。
    通过这次实验,掌握了数据库备份的概念和种类,各种数据库备份的实现方法,数据库恢复模型以及从各种数据库备份中恢复数据库的方法。

    实验十 SQL程序设计

    一、实验目的
    1.熟练掌握变量的定义和赋值。
    2.掌握各种运算符。
    3.掌握流程控制语句,尤其是条件语句和循环预语句。

    二、实验内容
    1.全局变量和局部变量的定义与使用。
    2.流程控制语句的使用。

    三、实验步骤
    1.创建一名为Student_name的局部变量,并在select语句中使用该变量查找“张元”同学所选课程的成绩情况,给出相应的语句段和运行结果。
    2.将选修课程号为701的同学的成绩增加3分,同时使用全局变量@@rowcount检查获得更新的数据行数目。
    3.利用学生数据库student中的stu_grade表查找学生的成绩信息并判断是否有某学生的某门课程的成绩在90分以上的信息,若有,则显示学号、姓名、课程名和成绩,否则输出信息,说明“不存在成绩大于90分的学生!”
    4.从学生数据库student中查询所有学生选课成绩情况:姓名、课程名、成绩。要求:将学生的百分制转换为5级评分制,成绩大于等于90显示为“优秀”,成绩在80-89分显示为“良好”,“70-79”分显示为“中等”,成绩在60-69显示为“及格”,60以下显示为“不及格”,没成绩的显示为“未考”。

    四,实验代码和结果
    Declare @Student_name varchar(20)
    Set @Student_name=‘张元’
    SELECT grade
    From stu_grade,stu_info
    WHERE stu_grade.stu_id=stu_info.stu_id AND name=‘张元’
    GO
    UPDATE stu_grade
    Set grade=grade+3
    WHERE course_id=‘701’
    if @@rowcount=0
    print’警告:没有发生更新数据行!’
    else
    print @@rowcount
    GO
    if Exists(SELECT * From stu_grade where grade>90)
    SELECT stu_info.stu_id,name,course_name,grade
    From stu_grade,stu_info,course_info
    WHERE stu_grade.stu_id=stu_info.stu_id AND stu_grade.course_id=course_info.course_id AND grade>90
    else
    print’不存在成绩大于90分的学生!’
    GO
    SELECT name,course_name,grade=
    case
    When grade>=90 then’优秀’
    When grade>=80 then’良好’
    When grade>=70 then’中等’
    When grade>=60 then’及格’
    When grade>0 then’不及格’
    else ‘未考’
    END
    FROM stu_info,stu_grade,course_info
    WHERE stu_info.stu_id=stu_grade.stu_id AND stu_grade.course_id=course_info.course_id

    五,实验总结和体会
    通过这次实验的学习,掌握以下内容:常量的使用,变量的定义和和赋值,各种运算符的使用与表达式的用法以及流程控制语句的使用。

    展开全文
  • 1.学会使用对象资源管理器创建数据库、创建基本表和查看数据库属性。 2. 学会使用对象资源管理器向数据库输入数据,修改数据,删除数据的操作。...3. 在SQL Server查询编辑器中完成复杂查询及视图定义
  • 数据库原理实验报告

    千次阅读 2019-10-14 16:12:22
    实验四 使用SQL语言进行复杂查询 实验五 SQL常用数据更新操作 实验六 综合应用 实验一:熟悉数据库管理系统环境&&实验二:SQL定义语言 实验过程及分析: 1.创建一个数据库和需要的表: create database ...
    -- 查询总的选课数
    select COUNT(*) as 选课总人数
    from student
    
    -- 查询没门被选课程的人数、平均分
    select cno,COUNT(*) as 人数,AVG(grade) as 平均分
    from sc group by cno
    --from sc
    
    --查询每个选课同学所选的课的课程门数和选课平均分
    select COUNT(*) as 课程门数,AVG(grade) as 选课平均分
    from sc
    group by sno
    --查询每个选课平均分在80以上的同学所选的课程门数和平均分
    select sno,COUNT(*) as 所选课程数 ,AVG(grade) as 成绩
    from sc
    group by sno
    having AVG(grade)>=80
    --查询女生人数小于200的各学院的女生人数
    select sdept as 学院 ,COUNT(*) as 女生人数
    from student
    where ssex='女'
    group by sdept
    having COUNT(*) <=200
    
    --查询选课门数在三门以上学生的学号
    select sno as 学号,COUNT(*) as 选课门数
    from student
    --where COUNT(*)>=3
    group by sno
    having COUNT(*)>=1
    --查询选课门数在三门以上且每门成绩都在80分以上学生的学号
    select sno as 学号,COUNT(*) as 选课门数
    from sc
    where grade >=80
    group by sno
    having COUNT(*)>=3
    
    --DAY YEAR
    select DAY('2019-10-23')
    select YEAR(getdate())
    
    --LEFT
    select sname,LEFT(sno,2)--查询sno前两位
    from student
    
    --
    selcet *,YEAR(getdate())-sage
    from student
    where YEAR(getdate())-sage>2000
    
    

    实验要求:

    实验一 熟悉数据库管理系统环境
    实验二 SQL定义语言
    实验三 使用SQL语言进行简单查询
    实验四 使用SQL语言进行复杂查询
    实验五 SQL常用数据更新操作
    实验六 综合应用

    实验一:熟悉数据库管理系统环境&&实验二:SQL定义语言

    在这里插入图片描述
    在这里插入图片描述

    实验过程及分析:

    1.创建一个数据库和需要的表:

    create database XSGL
    go
    use XSGL
    go
    create table student			--创建学生表
    (sno char(8) primary key,       --(主键)学生姓名
     sname char(8) not null unique, --学生姓名
     ssex char(2) default '男' check(ssex='男' or ssex='女'),  --性别给定默认值为'男',取值只能取‘男’或‘女’
     sage tinyint check(sage>13 and sage<50),
     sdept char(20))
    
    
    create table course				 --创建课程表 
    (cno char(2) PRimary key,        --课程编号
     cname varchar(50),  --课程名称
     cpno char(2),       --先修课号
     ccredit tinyint)	 --课程名
    
    create table sc         --创建成绩表
    (sno char(8),           --学生学号
     cno char(2),           --课程编号
     grade tinyint,         --成绩
     constraint pk_grade primary key(sno,cno),
     constraint fk_stuid foreign key(sno) references student(sno),
     constraint fk_course foreign key(cno) references course(cno),
     constraint ck_grade check(grade>=0 and grade<=100) )
    go
    insert into student(sno,sname, ssex,sage,sdept) values('95001', '李勇', '男', 20, 'CS')
    insert into student(sno,sname, ssex,sage,sdept) values('95002', '刘晨', '女', 19, 'IS')
    insert into student(sno,sname, ssex,sage,sdept) values('95003', '王敏', '女', 18, 'MA')
    insert into student(sno,sname, ssex,sage,sdept) values('95004', '张立', '男', 19, 'IS')
    insert into student(sno,sname, ssex,sage,sdept) values('95005', '刘云', '女', 18, 'CS ')
    insert into course(cno, cname,ccredit,cpno) values('1', '数据库', 4, '5')
    insert into course(cno, cname,ccredit,cpno) values('2', '数学', 6, null)
    insert into course(cno, cname,ccredit,cpno) values('3', '信息系统', 3, '1')
    insert into course(cno, cname,ccredit,cpno) values('4', '操作系统', 4, '6')
    insert into course(cno, cname,ccredit,cpno) values('5', '数据结构', 4, '7')
    insert into course(cno, cname,ccredit,cpno) values('6', '数据处理', 3, null)
    insert into course(cno, cname,ccredit,cpno) values('7', 'PASCAL语言', 4, '6')
    insert into sc(sno,cno,grade) values('95001', '1' ,92)
    insert into sc(sno,cno,grade) values('95001', '2' ,85)
    insert into sc(sno,cno,grade) values('95001', '3' ,88)
    insert into sc(sno,cno,grade) values('95002', '2' ,90)
    insert into sc(sno,cno,grade) values('95002', '3' ,80)
    insert into sc(sno,cno,grade) values('95003', '2' ,85)
    insert into sc(sno,cno,grade) values('95004', '1' ,58)
    insert into sc(sno,cno,grade) values('95004', '2' ,85)
    
    (1)STUDENT表中增加一个字段入学时间scome:
    alter table student 
    add scome date
    
    (2)删除STUDENT表中sdept字段:
    alter table student
    drop column sdept
    
    (3)删除创建的SC表中cno字段和COURSE表cno字段之间的外键约束:
    alter table sc
    DROP fk_course
    
    (4)重建(3)中删除的约束:
    alter table sc
    add constraint fk_course foreign key(cno) references course(cno)
    

    (5).重新定义一个简单表,然后用SQL语言DROP语句删除该表结构:

    drop table sc
    

    (6).用SQL语言CREATE INDEX语句定义表STUDENT的SNAME字段的降序唯一索引:

    create index index_sname
    on student(sname desc)
    

    (7).用SQL语言DROP语句删除索引:

    drop index index_sname on student
    

    实验总结:

    1. 创建表的时候可以添加约束
    2. 可以添加主键唯一标识 用primary key
      在这里插入图片描述
    3. 使用alter添加,修改列,还可以删除表中约束如索引 index
    4. 使用DROP 可以直接删除表 删除的时候先要删除外键表后才可以删除主键表
    5. 删除外键只能用alter 指定表 而不能用on来选择表, 删除索引不能用alter 直接用DROP INDEX 索引 ON 表(语法限定)

    实验目的

    掌握简单数据查询操作。

    实验内容

    使用各种查询条件完成指定的查询操作

    实验步骤

    1、创建学生表student、课程表course和选课表SC,并输入数据(注意数据的完整性);

    2、对各表中的数据进行不同条件的查询;

    实验过程(还是使用实验一的表)

    1、查询全体学生的学号和姓名

    select sno,sname 
    from student;
    

    在这里插入图片描述
    2、查询全体学生的详细记录

    select * 
    from student;
    

    在这里插入图片描述
    3、查询软件学院的学生姓名、年龄、系别

    select sname,sage,sdept
    from student
    where sdept='MA';
    

    在这里插入图片描述
    4、查询所有选修过课程的学生学号(不重复)

    select distinct sno 
    from sc;
    

    在这里插入图片描述
    5、查询考试不及格的学生学号(不重复)

    select distinct sno
    from sc
    where grade<60;
    

    在这里插入图片描述
    6、查询不是软件学院、计算机系的学生性别、年龄、系别

    select ssex,sage,sdept
    from student 
    where sdept not in('CS','MA');
    

    在这里插入图片描述
    7、查询年龄18-20岁的学生学号、姓名、系别、年龄

    select sno,sname,sdept,sage 
    from student
    where sage>=18 and sage<=20;
    

    在这里插入图片描述
    8、查询姓李的学生情况
    select *
    from student
    where sname like ‘李%’;
    在这里插入图片描述
    9、查询姓刘或姓李的学生情况
    select *
    from student
    where sname like ‘刘%’ or sname like ‘李%’;
    在这里插入图片描述
    10、查询1983年以后出生的学生姓名

    select sname 
    from student 
    where sage < 2019-1983
    

    在这里插入图片描述
    11、创建表 studentgrad(sno,mathgrade,englishigrade,chinesegrade)计算学生各科总成绩并赋予别名

    create table studentgrade(
        Sno char(8) ,
        mathgrade int,
        englishigrade int,
        chinesegrade int
    )
    

    在这里插入图片描述

    select sum(mathgrade+chinesegrade+englishigrade) '学生总成绩' 
    from studentgrade;
    

    在这里插入图片描述
    12、查询全体学生情况,查询结果按所在系升序排列,对同一系中的学生按年龄降序排列

    select *
    from student 
    order by sdept,sage desc;
    

    在这里插入图片描述

    实验总结

    模糊查询用% 如like ‘李%’
    或者确定仅两个字的用_ 如like ‘李_’

    数据库原理实验四:使用SQL语言进行复杂查询

    实验目的

    掌握复杂数据查询操作。

    实验内容

    掌握各种连接查询、嵌套查询的使用。

    实验过程(还是使用实验一的表)

    1. 查询每个学生及其选课情况
    select student.sno,sname,ssex,sage,sdept,cno,grade
    from student,sc
    where student.sno=sc.sno
    

    在这里插入图片描述
    2. 查询每门课的间接先修课

    select first.cno,second.cpno
    from course first,course second
    where first.cpno=second.cno 
    

    在这里插入图片描述
    3. 将STUDENT,SC进行右连接

    select student.sno,sname,ssex,sage,sdept,cno,grade
    from student right outer join sc on student.sno=sc.sno
    

    在这里插入图片描述
    4. 查询既选修了2号课程又选修了3号课程的学生姓名、学号

    select student.sno,sname
    from student inner join sc on student.sno=sc.sno
    where cno='3' and sc.sno in
    (select sno
    from sc
    where cno='2')
    

    在这里插入图片描述
    5. 查询和刘晨同一年龄的学生

    
    select student.sno,sname
    from student
    where sname!='刘晨' and sage=
    (select sage 
    from student
    where sname='刘晨')
    

    在这里插入图片描述
    6. 选修了课程名为“数据库”的学生姓名和年龄

    select sname,sage
    from student
    where sno in
    (select sno
    from sc
    where cno in
    (select cno
    from course 
    where cname='数据库'))
    
    1. 查询其他系比IS系任一学生年龄小的学生名单
    select student.sno,sname
    from student
    where sdept<>'IS' and
    sage<any
    (select sage 
    from student
    where sdept='IS')
    

    在这里插入图片描述
    8. 查询其他系中比IS系所有学生年龄都小的学生名单

    select student.sno,sname
    from student
    where sdept<>'IS' and 
    sage<all
    (select sage 
    from student 
    where sdept='IS')
    

    在这里插入图片描述
    9. 查询选修了全部课程的学生姓名

    select sname
    from student
    where Sno in
    (select Sno from SC
    group by Sno
    having count(*) = (select count(*) from course ))
    

    在这里插入图片描述
    10. 查询计算机系学生及其性别是男的学生

    select student.sno,sname
    from student
    where sdept='IS' and ssex='男'
    

    在这里插入图片描述
    11. 查询选修课程1的学生集合和选修2号课程学生集合的差集

    select sno
    from sc 
    where cno='1' except 
    select sno
    from sc
    where cno='2'
    

    在这里插入图片描述
    12. 查询李丽同学不学的课程的课程号

    select cno
    from course
    where cno not in
    (select cno
    from sc
    where sno in
    (select sno
    from student
    where sname='李丽'))
    

    在这里插入图片描述
    13. 查询选修了3号课程的学生平均年龄

    select avg(sage) as avgsage
    from student inner join sc on student.sno=sc.sno
    where cno='3'
    

    在这里插入图片描述
    14. 求每门课程学生的平均成绩

    select cno,avg(grade) as avggrade
    from sc
    group by cno
    

    在这里插入图片描述
    15. 统计每门课程的学生选修人数(超过3人的才统计)。要求输出课程号和选修人数,结果按人数降序排列,若人数相同,按课程号升序排列

    select course.cno '课程号', count(sc.sno) '人数'
    from course,sc 
    where course.cno=sc.cno 
    group by course.cno 
    having count(sc.sno)>3 
    order by count(sc.sno) desc,course.cno asc
    

    在这里插入图片描述
    16. 查询学号比刘晨大,而年龄比他小的学生姓名

    select sname
    from student
    where sno>(select sno from student where sname='刘晨') 
    and
    sage<(select sage from student where sname='刘晨')
    

    在这里插入图片描述
    17. 求年龄大于所有女同学年龄的男同学姓名和年龄

    select sname,sage
    from student
    where ssex='男' and 
    sage>(select max(sage) from student where ssex='女')
    

    在这里插入图片描述

    实验总结

    1. 分组group by 要用having来限制条件
    2. desc是降序,asc是升序
    3. any()是任意,all()是所有

    数据库原理实验五:SQL的常用数据更新操作

    实验目的

    掌握SQL的常用数据更新操作,熟练应用INSERT,UPDATE,DELETE语句。

    实验内容

    1. 插入如下学生记录(学号:95030,姓名:李莉,年龄:18)
    insert into student(sno,sname,sage)
    values ('95030','李莉',18)
    

    在这里插入图片描述
    2. 插入如下选课记录(95030,1)

    insert into sc(sno,cno)
    values('95030',1)
    

    在这里插入图片描述
    3. 计算机系学生年龄改成20

    update student
    set sage=20
    where sdept='CS'
    

    在这里插入图片描述
    4. 把数学系所有学生成绩改成0

    update sc
    set grade=0
    where 'MA'=
    (select sdept
    from student
    where student.sno=sc.sno)
    

    在这里插入图片描述
    5. 把低于总平均成绩的女同学成绩提高5分

    update sc 
    set grade+=5
    where grade<
    (select avg(grade) 
    from sc inner join student
    on student.sno=sc.sno
    where ssex='女')
    

    在这里插入图片描述
    6. 删除95030学生信息

    delete
    from student
    where sno='95030'
    
    1. 删除SC表中无成绩的记录
    delete 
    from sc
    where grade is null;
    
    1. 删除张娜的选课记录
    delete
    from sc
    where sno=
    (select sno from student where sname='张娜')
    
    1. 删除不及格的学生选课记录
    delete
    from sc
    where grade<60
    

    10.删除数学系所有学生选课记录

    delete
    from sc
    where sno in 
    (select sno from student where sdept='MA')
    
    1. 删除所有未被选修的课程
    delete
    from course
    where cno not in (select cno from sc)
    
    1. 查询每一门课程成绩都大于等于80分的学生学号、姓名和性别,把值送往另一个已经存在的基本表STU(SNO,SNAME,SSEX)中
    create table STU 
    (sno char(8), 
    sname char(8), 
    ssex char(2) 
    )
    
    insert into STU(sno,sname,ssex);
    
    select distinct student.sno,sname,ssex
    from student,sc 
    where student.sno not in
    (select sno from sc where grade<80) and student.sno=sc.sno
    
    1. 建立一个sdeptgrade 表,包含(sdept,avggrade)字段,对每一个系,求学生的成绩,并把结果存入sdeptgrade
    create table sdeptgrade 
    (sdept char(8) primary key, 
    avggrade int
    ) 
    
    insert into sdeptgrade;
    
    select student.sdept, avg(sc.grade) 
    from student inner join sc
    on (student.sno = sc.sno) 
    group by student.sdept;
    

    实验总结

    删除主键表数据如果有外键约束就会报错

    数据库原理实验六:综合应用

    实验目的

    根据数据库设计步骤完成简单应用的设计和创建,了解数据在前后台的交互。

    实验内容

    1、建立一个数据库和五张表的表结构。

    2、根据表结构使用SQL语句添加相应约束。

    (1)员工人事表employee
    在这里插入图片描述

    create table employee
    (
    emp_no char(5) primary key,
    emp_name char(10) not null,
    Sex char(1) not null,
    Dept char(4) not null,
    Title char(6) not null,
    data_hired datetime not null,
    birthday datetime null,
    salary int not null,
    Addr char(50) null,
    Mod_date datetime default(getdate())
    )
    

    (2)客户表customer
    在这里插入图片描述

    create table customer
    (
    cust_id char(5) primary key,
    cust_name char(20) not null,
    Addr char(40) not null,
    tel_no char(10) not null,
    Zip char(6) null
    )
    

    (3)销售主表sales
    在这里插入图片描述

    create table sales
    (
    order_no int primary key,
    cust_id char(5) not null,
    sale_id char(5) not null,
    tot_amt numeric(9,2) not null,
    order_date datetime not null,
    ship_date datetime not null,
    incoice_no char(10) not null
    )
    

    (4)销货明细表sale_item
    在这里插入图片描述

    create table sale_item
    (
    order_no int not null,
    prod_id char(5) not null,
    Qty int not null,
    unit_price numeric(9,2) not null,
    order_date datetime null
    constraint primary_sale primary key(order_no,prod_id)
    )
    

    (5)产品名称表product

    在这里插入图片描述

    create table product
    (
    prod_id char(5) not null primary key,
    prod_naem char(20) not null
    )
    

    3、录入数据并实现实现如下查询

    (1)查找定单金额高于20000的客户编号;

    select cust_id 
    from sales 
    where tot_amt>20000
    

    (2)选取销售数量最多的前5条订单订单号、数量;

    select top 5 order_no,Qty 
    from sale_item 
    order by Qty desc
    

    (3)显示sale_item表中每种个别产品的订购金额总和,并且依据销售金额由大到小排

         来显示出每一种产品的排行榜;
    
    select prod_id, sum(Qty*unit_price) '金额' 
    from sale_item 
    group by prod_id 
    order by '金额' desc
    

    (5)计算每一产品每月的销售金额总和,并将结果按销售(月份,产品编号)排序;

    select "s2".月份,sum("s2".tot_amt) '销售金额总和',"s1".prod_id '产品编号'
    from sale_item "s1"
    join (select month(order_date) '月份',order_no,tot_amt from sales) "s2"
    on "s1".order_no="s2".order_no
    group by "s2".月份,"s1".prod_id
    order by "s2".月份,"s1".prod_id
    

    (6)检索单价高于2400元的的产品编号、产品名称、数量、单价及所在订单号;

    select s.prod_id, product.prod_name, s.Qty, s.unit_price, s.order_no 
    from product,sale_item s
    where s.unit_price> 2400 and product.prod_id=s.prod_id
    

    (7)计算每一产品销售数量总和与平均销售单价;

    select sum(Qty)'销售数量', avg(unit_price)'平均销售单价'
    from sale_item
    

    (8)创建一个视图,该视图只含上海客户信息,即客户号、客户姓名、住址。

    create view view_name AS
    select cust_id,cust_name,Addr from customer where Addr='上海'
    

    实验总结

    1. 设置主键,自动为 not null

    2. unique和主键区别:
      unique:唯一并且 一张表可设置多个unique 可空 但是只能有一行数据空
      主键: 唯一并且 一张表只能有一个主键

    3. 主键可通过 constraint 主键名 primary key(列,列)来设置组合键

    4. 给表取别名的时候 不能用单引号,要用双引号或者不用引号
      而给列取别名的时候可以选择单引号 或者 as 连接词 或者不用引号

    5. 视图是为了保存一张表 下次查找该表 可直接 使用 如本实验中:

    select * from view_name 
    

    就可以查看视图。

    展开全文
  • SQL Server数据库原理实验报告(完)

    万次阅读 多人点赞 2019-04-18 11:34:04
    实验四 使用SQL语言进行复杂查询 实验五 SQL常用数据更新操作 实验六 综合应用 实验一 熟悉数据库管理系统环境 实验目的 1、 熟悉并掌握SQL Server的基本使用环境; 2、 掌握SQL Server环境下SQL定义语言的用法,能...
  • sql 实验报告

    2013-01-06 17:15:50
    用T-SQL,5个较复杂的库表操作(包括多表查询、应用控制流语句、应用游标)
  • 实用标准文档 实验六 数据查询分析实验...高编写复杂查询的 SQL 程序的能力 实验内容 1. 索引对查询的影响 1对结果集只有一个元组的查询分三种情况进行执行 必如查询一个具体学生的信息 不建立索引学号上建立非聚集索引
  • 数据库实验报告

    千次阅读 2018-11-11 19:29:17
    学生要对课程设计的各个实验环节进行理论学习、实验分析与设计、编码实现、最后进行分析与总结,通过提交总结报告的形式进行考核,字数不少于5000字。报告占课程总成绩的10%。 本课程设计的实验环节要求每个学生...
  • 此为吉林大学数据库的实验报告,实验内容为: 实验一 熟悉MySQL环境及SQL定义语言 一、实验目的: 1. 了解MySQL程序构成、安装、管理方法。 2. 了解MySQL数据库及表结构。 3. 熟练掌握SQL语言进行基本表结构的创建...
  • 应用集成实验报告

    千次阅读 2020-01-20 09:49:56
    应用集成原理与工具集成实验报告 实验环境:普通PC机,Windows 2003或Windows XP 系统、网络交换机、VS.Net 开发环境、数据库运行环境。 实验目的: 通过这次实验掌握并了解基于中间件集成的实验原理与实验操作; ...
  • 此次设计所涉及的数据不是非常复杂,选用最方便的Access就足够完成所有规划的功能。 建立student.mdb,并在此数据库下建立三个表:studentid、course和score三个表。在studentid表中,以studentno为主键,在course表...
  • PE文件病毒实验(二)——实验报告

    千次阅读 2020-03-01 15:06:11
    实验目的:掌握PE文件格式;理解病毒感染PE文件的原理。 实验内容: (1) 了解PE文件格式。 (2) 根据实验步骤,编程实现在PE文件中插入病毒代码。运行插入病毒代码后的PE文件。 (3) 编程实现在磁盘中搜索.exe...
  • 网络扫描实验报告

    千次阅读 2020-04-18 01:09:29
    实验目的 掌握网络扫描之端口状态探测的基本原理 实验环境 python + scapy2.4 实验先修知识 kali中基本端口开启/关闭命令 nc -l -p 8888 -u//指定端口开启关闭(加-u是UDP监听,不加u是TCP监听进行监听) ...
  • 大二数据库实验报告答案

    千次阅读 2019-12-05 20:30:01
    实验四 使用SQL语言进行复杂查询 实验五 SQL常用数据更新操作 实验六 综合应用 实验一,二:熟悉数据库管理系统环境,SQL定义语言 实验一是让我们利用图形界面(非sql语句)进行数据库和表的创建,太简单且...
  • 大数据导论实验报告

    千次阅读 2019-01-14 21:44:44
    实验名称 Python的安装与应用 专 业 软件工程 姓 名   学 号   班 级     实验目的:    ...
  • 《程序设计基础课程设计》实验报告

    千次阅读 多人点赞 2020-06-23 08:24:12
    C语言程序设计实验报告
  • 软件工程实验报告

    千次阅读 2013-12-12 13:40:20
    课程 班级 学 号 姓 名 实验时间 ...软件工程实验报告 三 一、实验名称 系统详细设计 二、实验目的 根据需求分析、概要设计,完成系统的详细设计 三、实验主
  • 软件工程实验报告

    千次阅读 2013-12-09 20:18:39
    课程 班级 学 号 姓 名 实验时间 ...软件工程实验报告 二  一、实验名称 酒店管理系统概要设计 二、实验目的 完成电子商务环境酒店管理系统概要设计,建立概要
  • 只是在校学生看看 此系统需要满足来自两方面的需求,这两个方面分别是图书借阅者和图书馆管理人员。图书借阅者的需求是图书查询和...图书馆管理人员的功能则较为复杂,包括图书查询,图书录入,图书更新和图书删除。

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 14,263
精华内容 5,705
关键字:

复杂查询实验报告