ㅤ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
) ;
-
使用 T-SQL命令，为stsc数据库创建教材P388所示的student，course，score，teacher数据表结构。
use stsc;
-- 创建表 student
create table student(
/* 学号 */
sno char(6) not null primary key,
/* 姓名 */
sname char(8) not null,
/* 性别 */
stsex char(2) not null,
/* 出生日期 */
stbirthday date not null,
/* 专业 */
speciality char(12),
/* 总学分 */
tc int
);
-- 创建表 course
create table course(
-- 课程号
cno char(3) not null primary key,
-- 课程名
cname char(16) not null,
-- 学分
credit int ,
-- 教师号
tno char(6)
);
-- 创建表 score
create table score(
-- 学号
stno char(6) not null,
-- 课程号
cno char(3) ,
-- 成绩
grade int ,
-- 添加联合主键
primary key(stno,cno)
);
-- 创建表 teacher
create table teacher(
-- 教师号
tno char(6) not null,
-- 姓名
tname char(8) not null,
-- 性别
tsex char(2) not null,
-- 出生日期
tbirthday date not null,
-- 职称
title char(12) ,
-- 学院名
school char(12),
);
-
使用 T-SQL命令，为stsc数据库的student表添加Email字段，类型为char(20)
use stsc
go
ALTER TABLE student
ADD Email char(20) NOT NULL;
-
使用 T-SQL命令，在stsc数据库中修改course表的credit字段类型，改为float
USE stsc
GO
ALTER TABLE course
ALTER COLUMN credit float;
-
使用 T-SQL命令，在stsc数据库中删除student表的email字段
USE stsc
GO
ALTER TABLE student
DROP COLUMN email;
-
用 SSMS图形界面向stsc数据库中的student，course，score表插入数据，（表中数据如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', '计算机原理', 4, NULL);
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);
-
用 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. 新增练习
-
将李贤友同学的出生日期改为‘1995-4-5’
/* 1. 将李贤友同学的出生日期改为‘1995-4-5’。*/
UPDATE student
SET stbirthday = '1995-4-5'
WHERE sname = '李贤友';
-
输出: 
-
在teacher表添加一名老师的信息: 通信学院，张丽，女，教授，教师号：102103，出生日期：1977-8-7。
INSERT INTO teacher ( school, tname, tsex, title, tno, tbirthday)
VALUES ( '通信学院', '张丽', '女', '教授', '102103', '1977-8-7');
-
输出: 
-
删除张丽老师的信息。
-- 3. 删除张丽老师的信息。
DELETE
FROM teacher
WHERE tname = '张丽';
-
输出: 
-
查询student表中总分大于或等于50分的学生情况。
-- 4. 查询student表中总分大于或等于50分的学生情况。
SELECT *
FROM student
WHERE tc >= 50;
-
输出: 
-
查询谢暄的“高等数学”成绩
-- 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 = '高等数学';
-
输出: 
-
查找选修了“数字电路”的学生的姓名及成绩，并按成绩降序排列。
-- 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;
-
输出: 
-
查询学号为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;
-
输出: 
-
查找“数据库系统”和“微机原理”的平均成绩。
-- 8. 查找“数据库系统”和“微机原理”的平均成绩。
SELECT cname, avg(grade) AS "Avg"
FROM score
INNER JOIN course ON score.cno = course.cno
WHERE cname in ('数据库系统','微机原理')
GROUP BY cname;
-
输出: 
-
查询每个专业最高分的课程名和分数。
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;
-
输出: 
-
查询通信专业最高分的学生的学号、姓名、课程号和分数。 -- 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;
-
输出: 
-
查询有两门以上（含两门）课程超过 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;
-
输出: 
-
查询选修了所有已安排任课教师的课程的学生的姓名
-- 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