ㅤcoderitl

V1

2022/02/07阅读:45主题:萌绿

SQLServer-增删改查

1. SQL Server 创建数据库、表命令使用

CREATE DATABASE stsc
ON PRIMARY (
    /*
    NAME is filespec: filespec 指定的文件的逻辑文件名
    逻辑文件名--数据库文件在数据库中显示的名字
    */

    name=coderitl_datafile,
    /* 主数据文件存储路径 name.mdf */
    filename='G:\SQLServer\Data\database-20210928\stsc.mdf',
    /* SIZE: 指定 filespec 定义的文件的初始大小 */
    size=10mb,
    /* MAXSIZE:指定 filespec 定义文件的最大大小 */
    maxsize = 50mb, filegrowth = 5mb )

log ON (
   name=coderit_log,
   filename='G:\SQLServer\Data\database-20210928\stsc.ldf',
   SIZE=5mb,
   maxsize=25mb,
   filegrowth=5mb
) ;
  1. 使用T-SQL命令,为stsc数据库创建教材P388所示的studentcoursescoreteacher数据表结构。
  use stsc;

  -- 创建表 student
  create table student(
   /* 学号 */
   sno char(6not null primary key,
   /* 姓名 */
   sname char(8)  not null,
   /* 性别  */
   stsex char(2not null,
   /* 出生日期 */
   stbirthday date not null,
   /* 专业 */
   speciality char(12),
   /* 总学分 */
   tc int
  );

-- 创建表 course
create table course(
 -- 课程号
 cno char(3not null primary key,
 -- 课程名
 cname char(16)  not null,
 -- 学分
 credit int ,
 -- 教师号
 tno char(6)
);


-- 创建表 score
create table score(
 -- 学号
 stno char(6not null,
 -- 课程号
 cno char(3)  ,
 -- 成绩
 grade int ,
 -- 添加联合主键
 primary key(stno,cno)
);

  -- 创建表 teacher

  create table teacher(
   -- 教师号
   tno char(6not null,
   -- 姓名
   tname char(8)  not null,
   -- 性别
   tsex char(2not null,
   -- 出生日期
   tbirthday date not null,
   -- 职称
   title char(12) ,
   -- 学院名
   school char(12),
  );

  1. 使用T-SQL命令,为stsc数据库的student表添加Email字段,类型为char(20)
  use stsc
  go
  ALTER TABLE student
  ADD Email char(20NOT NULL;
  1. 使用T-SQL命令,在stsc数据库中修改course表的credit字段类型,改为float
  USE stsc
  GO
  ALTER TABLE course
  ALTER COLUMN credit float;
  1. 使用T-SQL命令,在stsc数据库中删除student表的email字段
  USE stsc
  GO
  ALTER TABLE student
   DROP COLUMN email;
  1. SSMS图形界面向stsc数据库中的studentcoursescore表插入数据,(表中数据如P389
  student:
      INSERT INTO student VALUES ('121001''李贤友''男''1991-12-30''通信'52);
      INSERT INTO student VALUES ('121002''周映雪''女''1993-01-12''通信'49);
      INSERT INTO student VALUES ('121005''刘刚''男''1992-07-05''通信'50);
      INSERT INTO student VALUES ('122001''郭德纲''男''1991-10-23''计算机'48);
      INSERT INTO student VALUES ('122002''谢暄''女''1992-09-11''计算机'52);
      INSERT INTO student VALUES ('122004''孙婷''女''1991-02-24''计算机'50);
   course:
       INSERT INTO course VALUES ('102''数字电路'3'102101');
       INSERT INTO course VALUES ('203''数据库系统'3'204101');
       INSERT INTO course VALUES ('205''微机原理'4'204107');
       INSERT INTO course VALUES ('208''计算机原理'4NULL);
       INSERT INTO course VALUES ('801''高等数学'4'801102');
  score:
  INSERT INTO score VALUES ('121001''102'92);
  INSERT INTO score VALUES ('121002''102'72);
  INSERT INTO score VALUES ('121005''102'87);
  INSERT INTO score VALUES ('122002''203'94);
  INSERT INTO score VALUES ('122004''203'81);
  INSERT INTO score VALUES ('121001''205'91);
  INSERT INTO score VALUES ('121002''205'65);
   INSERT INTO score VALUES ('121005''205'85);
   INSERT INTO score VALUES ('121001''801'94);
   INSERT INTO score VALUES ('121002''801'73);
   INSERT INTO score VALUES ('121005''801'82);
   INSERT INTO score VALUES ('122001''801'NULL);
   INSERT INTO score VALUES ('122002''801'95);
   INSERT INTO score VALUES ('122004''801'86);
  1. Insert语句向stsc数据库中的teacher数据表插入数据(表中数据如P389
    Teacher:
      INSERT INTO teacher VALUES ('102101''刘林卓''男''1962-03-21''教授''通信学院');
      INSERT INTO teacher VALUES ('102105''周学莉''女''1977-10-05''讲师''通信学院');
      INSERT INTO teacher VALUES ('204101''吴波''男''1978-04-26''教授''计算机学院');
      INSERT INTO teacher VALUES ('204107''王冬琴''女''1968-11-18''副教授''计算机学院');
      INSERT INTO teacher VALUES ('801102''李伟''男''1975-08-19''副教授''数学学院');

2. 新增练习

  1. 将李贤友同学的出生日期改为‘1995-4-5’
    /* 1. 将李贤友同学的出生日期改为‘1995-4-5’。*/
    UPDATE student
    SET stbirthday = '1995-4-5'
    WHERE sname = '李贤友';
  • 输出: 在这里插入图片描述
  1. 在teacher表添加一名老师的信息: 通信学院,张丽,女,教授,教师号:102103,出生日期:1977-8-7。
    INSERT INTO teacher ( school, tname, tsex, title, tno, tbirthday)
    VALUES ( '通信学院''张丽''女''教授''102103''1977-8-7');
  • 输出: 在这里插入图片描述
  1. 删除张丽老师的信息。
    -- 3.   删除张丽老师的信息。
    DELETE
    FROM teacher
    WHERE tname = '张丽';
  • 输出: 在这里插入图片描述
  1. 查询student表中总分大于或等于50分的学生情况。
    -- 4.   查询student表中总分大于或等于50分的学生情况。
    SELECT *
    FROM student
    WHERE tc >= 50;
  • 输出: 在这里插入图片描述
  1. 查询谢暄的“高等数学”成绩
    -- 5. 查询谢暄的“高等数学”成绩。
    SELECT sname, grade
    FROM student stu
            INNER JOIN score ON stu.sno = score.stno
            INNER JOIN course ON score.cno = course.cno
    WHERE stu.sname = '谢暄'
     AND cname = '高等数学';
  • 输出: 在这里插入图片描述
  1. 查找选修了“数字电路”的学生的姓名及成绩,并按成绩降序排列。
  -- 6. 查找选修了“数字电路”的学生的姓名及成绩,并按成绩降序排列。
  SELECT sname, grade
  FROM student stu
          INNER JOIN score ON stu.sno = score.stno
          INNER JOIN course ON score.cno = course.cno
  WHERE cname = '数字电路'
  ORDER BY grade DESC;
  • 输出: 在这里插入图片描述
  1. 查询学号为121001的学生的所有课程的平均成绩。
-- 7.查询学号为121001的学生的所有课程的平均成绩。
    use stsc
    go
    SELECT count(*) AS "leanCourse",avg(grade) AS "allCourseAvgScore", stu.stno
    FROM student stu
           INNER JOIN score ON stu.stno = score.stno
    WHERE stu.stno = '121001'
    GROUP BY stu.stno;
  • 输出: 在这里插入图片描述
  1. 查找“数据库系统”和“微机原理”的平均成绩。
  -- 8.   查找“数据库系统”和“微机原理”的平均成绩。
    SELECT cname, avg(grade) AS "Avg"
    FROM score
            INNER JOIN course ON score.cno = course.cno
    WHERE cname in ('数据库系统','微机原理')
    GROUP BY cname;
  • 输出: 在这里插入图片描述
  1. 查询每个专业最高分的课程名和分数。
  9. 查询每个专业最高分的课程名和分数。
  -- 查询课程名
  SELECT max(grade) AS "grade",cname
  FROM course
  INNER JOIN score
  ON course.cno = score.cno
  INNER JOIN student stu
  ON stu.sno = score.stno
  WHERE speciality IN
  -- 查询专业
  (SELECT DISTINCT student.speciality
  FROM student)
  AND grade IN
  -- 查询最大成绩
  (SELECT max(grade) AS "grade"
  FROM student
  INNER JOIN score
  ON student.sno = score.stno
  GROUP BY speciality)
  GROUP BY cname;
  • 输出: 在这里插入图片描述
  1. 查询通信专业最高分的学生的学号、姓名、课程号和分数。
      -- 10. 查询通信专业最高分的学生的学号、姓名、课程号和分数。

      select sno, sname, c.cno, grade,speciality
      from student
              inner join score sc on sno = stno
              inner join
          course c on c.cno = sc.cno
      where grade = (select max(grade)
                    from student
                             inner join score sc on sno = stno
                             inner join
                         course c on c.cno = sc.cno
                    where speciality = '通信')
        and  speciality = '通信';
      -------------------------------------------------------------------------------
      /* 临时解决方法: 创建一个视图 */
      create view getMaxGradeInfo as
      SELECT sno
          , sname
          , score.cno
          , grade
          , cname
          , speciality
      FROM student stu
              INNER JOIN score ON stu.sno = score.stno
              INNER JOIN course ON score.cno = course.cno
      WHERE speciality = '通信'
      GROUP BY sno, sname, score.cno, cname, grade, speciality;

      /* 临时解决方案 */
      select top 1 max(grade) as 'MaxGrade',sno, sname, cno, grade
      from getMaxGradeInfo
      group by sno, sname, cno, grade;
  • 输出: 在这里插入图片描述
  1. 查询有两门以上(含两门)课程超过 80 分的学生的姓名及其平均成绩。
 -- 11. 查询有两门以上(含两门)课程超过 80 分的学生的姓名及其平均成绩。
 SELECT count(*) as 'TCourse'avg(grade) as 'Avg', sname
 FROM student stu
         inner join score on
    score.stno = stu.sno
         inner join course on score.cno = course.cno
 where grade > 80
 group by sname
 having count(*) > 2;
  • 输出: 在这里插入图片描述
  1. 查询选修了所有已安排任课教师的课程的学生的姓名
 -- 12. 查询选修了所有已安排任课教师的课程的学生的姓名
 select sname, tname, cname,tch.tno
 from student stu
         inner join score on
    score.stno = stu.sno
         inner join course on score.cno = course.cno
         inner join teacher tch on course.tno = tch.tno where tch.tno is not null;


 -- 12.查询选修了所有已安排任课教师的课程的学生的姓名。
 use stsc
 go
 select sname, tname, cname
 from student stu
         inner join score
                    on
                        stu.sno = score.stno
         inner join course c
                    on
                        score.cno = c.cno
         inner join teacher t
                    on c.tno = t.tno
 where exists(select * from course b where c.tno = b.tno);

分类:

后端

标签:

其他

作者介绍

ㅤcoderitl
V1