案例：创建一个学生管理系统（PXSCJ1）的数据库（SQL）
案例：创建一个学生管理系统（PXSCJ1）的数据库（SQL）纯代码创建
·
1、新建数据库：PXSCJ1
--创建数据库
CREATE DATABASE PXSCJ1 
--创建并确认属性：XSB、KCB、CJB（以下代码用于2、3、4、5题）
use PXSCJ1
create table XSB
(学号 char(6) primary key check(学号 like '[0][8][1][12][0-9][0-9]') not null,
姓名 char(8) not null,
性别 bit default 1 not null ,
出生日期 date check(出生日期 >'1980-01-01') ,
专业 char(12) check(专业 in('计算机','通信工程')),
总学分 int default 0 check(总学分<=160 and 总学分>=0),
备注 varchar(500)
)
create table KCB
(课程号 char(3) primary key check(课程号>='100'and 课程号<='299') not null,
课程名 char(16) check(课程名 in('计算机基础','程序设计与语言','离散数学')) not null,
开课学期 tinyint default '1' check(开课学期>=1 and 开课学期<=8),
学时 tinyint check(学时>=1 and 学时<=80),
学分 tinyint check(学分>=1 and 学分 <=10) not null,
)
create table CJB
(学号 char(6) check(学号>=0 and 学号<='100') not null,
课程号 char(3) not null,
primary key (学号,课程号),
foreign key(学号) references XSB (学号),
foreign key(课程号) references KCB (课程号),
成绩 int default 0 ,
)2、创建并确认属性：XSB、KCB、CJB
代码见上！
3、设计每个表的实体完整性：键、索引
代码见上！
4、设计每个表的域完整性：CHECK语句
代码见上！
5、建立表与表之间的参照完整性：XSB与CJB,KCB与CJB
代码见上！
6、输入表数据：增加、删除、修改记录
增加和修改记录：
--增加和修改记录信息
use PXSCJ1
insert into XSB(学号,姓名,性别,出生日期,专业,总学分,备注)
values('081101','王林',1,'1990-02-10','计算机',50,null),
('081103','王燕',0,'1989-10-06','计算机',50,null),
('081108','林一帆',1,'1989-08-05','计算机',52,'已提前修完一门课'),
('081202','王林',1,'1989-01-29','通信工程',40,'有一门课不及格，待补考'),
('081204','马琳琳',0,'1989-02-10','通信工程',42,null)
insert into KCB(课程号,课程名,开课学期,学时,学分)
values('101','计算机基础',1,80,5),
('102','程序设计与语言',2,68,4),
('206','离散数学',4,68,4)
insert into CJB(学号,课程号,成绩)
values('081101',101,80),
('081101','102',78),
('081101','206',76),
('081103','101',62),
('081103','102',70),
('081108','101',85),
('081108','102',64),
('081108','206',87),
('081202','101',65),
('081204','101',91)
删除：
--删除学生表中总学分为40的学生
use PXSCJ1
delete from XSB
where (总学分=40)
7、进行查询操作
7.1查询计算机专业的学生信息：
--查询计算机专业的学生信息
SELECT * FROM XSB WHERE 专业='计算机'
7.2查询CJB表中的学号，课程号，成绩，其中成绩空显示“尚未考试”，<60不及格， =<60---<70及格，=<70---<80中，=<80---<90良，=<90---<100优秀。列标题别名为“成绩等级”：
--查询CJB表中的学号，课程号，成绩，其中成绩空显示“尚未考试”，<60不及格， =<60---<70及格，=<70---<80中，=<80---<90良，=<90---<100优秀。列标题别名为“成绩等级”：
SELECT 学号,课程号,成绩等级=
CASE
WHEN 成绩 IS NULL THEN '尚未考试'
WHEN 成绩<60 THEN '不及格'
WHEN 成绩>=60 and 成绩<70 THEN '及格'
WHEN 成绩>=70 and 成绩<80 THEN '中'
WHEN 成绩>=80 and 成绩<90 THEN '良'
WHEN 成绩>=90 and 成绩<100 THEN '优秀'
ELSE '输入错误'
END
FROM CJB
7.3对XSB表选择姓名，专业和总学分，返回结果集的前6行：
--对XSB表选择姓名，专业和总学分，返回结果集的前6行：
select top 6 姓名,专业,总学分
from XSB
7.4输出成绩的max，min，sum，avg，count五个函数，写出合适的列名：
--输出成绩的max，min，sum，avg，count五个函数，写出合适的列名：
SELECT AVG(成绩) AS '平均分', SUM(成绩) AS '总和', MAX(成绩) AS '最高分',MIN(成绩) AS '最底分',COUNT(成绩) AS '成绩个数'
FROM CJB
7.5查询XSB表中姓“王”且双名的学生情况：
--查询XSB表中姓“王”且双名的学生情况：
SELECT * FROM XSB
WHERE 姓名 LIKE '王__'
7.6查询未选修离散数学的学生情况
--查询未选修离散数学的学生情况
SELECT * FROM XSB
WHERE 学号 IN
(
SELECT 学号 FROM CJB
WHERE 课程号 IN
(
SELECT 课程号 FROM KCB
WHERE 课程名 != '离散数学'))
7.7查询206号课程成绩不低于101号课程最低成绩的学生学号
--查找206号课程成绩不低于101号课程最低成绩的学生学号
SELECT 学号,成绩
FROM CJB
WHERE 课程号='206' AND 成绩!<
(SELECT MIN(成绩)
FROM CJB
WHERE 课程号='101')
7.8查找选修了206号课程且成绩在80分以上的学生姓名及成绩
--查找选修了206号课程且成绩在80分以上的学生姓名及成绩
SELECT XSB.姓名,成绩
FROM XSB,CJB
WHERE XSB.学号=CJB.学号 AND 课程号='206' AND 成绩>80
7.9查询每个同学选了几门课，并输出选修大于2的记录
--每个同学选了几门课
SELECT 学号,COUNT(课程号) AS '选课门数' FROM CJB GROUP BY 学号
--并输出选修大于2的记录
SELECT 学号,COUNT(课程号) AS '选课门数' FROM CJB GROUP BY 学号 HAVING COUNT(学号)>=2
7.10 将计算机专业的学生的“计算机基础”课程成绩按照降序排列，成绩相同的的再按姓名升序排列：
--将计算机专业的学生的“计算机基础”课程成绩按照降序排列，成绩相同的的再按姓名升序排列：
SELECT XSB.学号,姓名,KCB.课程名,CJB.成绩
FROM XSB,KCB,CJB
WHERE XSB.学号=CJB.学号 AND CJB.课程号 = KCB.课程号 AND 课程名='计算机基础'
ORDER BY 成绩 DESC
8、表上建立视图
8.1在XSB上创建计算机专业的视图
--在XSB上创建计算机专业的视图
CREATE VIEW CS_XS
AS
SELECT *
FROM XSB
WHERE 专业= '计算机' 
8.2使用XSB和CJB创建计算机专业的成绩视图
--使用XSB和CJB创建计算机专业的成绩视图
CREATE VIEW CS_KC
AS
SELECT XSB.学号,课程号,成绩
FROM XSB,CJB
WHERE 专业= '计算机' AND XSB.学号=CJB.学号
WITH CHECK OPTION
9、游标
9.1建立游标指向选修计算机基础课程的课程号、课程名、成绩信息：
--建立游标指向选修计算机基础课程的课程号、课程名、成绩信息：
DECLARE KC_CUR3 CURSOR
LOCAL SCROLL SCROLL_LOCKS
FOR
SELECT KCB.课程号,课程名,成绩
FROM KCB,CJB
WHERE KCB.课程号=CJB.课程号 AND 课程名 = '计算机基础'
FOR UPDATE OF 成绩 
9.2使用游标输出所行
方式一：
--使用游标输出所行
DECLARE KC_CUR3 CURSOR
LOCAL SCROLL SCROLL_LOCKS
FOR
SELECT KCB.课程号,课程名,成绩
FROM KCB,CJB
WHERE KCB.课程号=CJB.课程号 AND 课程名 = '计算机基础'
FOR UPDATE OF 成绩
OPEN KC_CUR3
DECLARE @ckh CHAR(6)
DECLARE @kcm CHAR(6)
DECLARE @cj INT
FETCH FIRST FROM KC_CUR3 INTO @ckh,@kcm,@cj
WHILE(@@FETCH_STATUS=0)
BEGIN
SELECT @ckh,@kcm,CAST(@cj AS CHAR(2)) ----CONVER(CHAR(2),@zxf)
FETCH NEXT FROM KC_CUR3 INTO @ckh,@kcm,@cj
END 
方式二：
方式二：
DECLARE KC_CUR3 CURSOR
LOCAL SCROLL SCROLL_LOCKS
FOR
SELECT KCB.课程号,课程名,成绩
FROM KCB,CJB
WHERE KCB.课程号=CJB.课程号 AND 课程名 = '计算机基础'
FOR UPDATE OF 成绩
OPEN KC_CUR3
DECLARE @ckh CHAR(6)
DECLARE @kcm CHAR(6)
DECLARE @cj INT
FETCH FIRST FROM KC_CUR3 INTO @ckh,@kcm,@cj
WHILE(@@FETCH_STATUS=0)
BEGIN
PRINT '课程号:'+@ckh
PRINT '课程名:'+@kcm
PRINT '成绩:'+CAST(@cj AS CHAR(2)) ----CONVER(CHAR(2),@zxf)
PRINT '--------------'
FETCH NEXT FROM KC_CUR3 INTO @ckh,@kcm,@cj
END 
9.3使用游标把数据集的成绩加10：
方式一：
--使用游标把数据集的成绩加10
DECLARE KC_CUR3 CURSOR
LOCAL SCROLL SCROLL_LOCKS
FOR
SELECT KCB.课程号,课程名,成绩
FROM KCB,CJB
WHERE KCB.课程号=CJB.课程号 AND 课程名 = '计算机基础'
FOR UPDATE OF 成绩
OPEN KC_CUR3
FETCH FIRST FROM KC_CUR3
WHILE(@@FETCH_STATUS=0)
BEGIN
UPDATE CJB SET 成绩=成绩+10 WHERE CURRENT OF KC_CUR3
FETCH NEXT FROM KC_CUR3
END 
方式二：
方式二：
DECLARE KC_CUR3 CURSOR
LOCAL SCROLL SCROLL_LOCKS
FOR
SELECT KCB.课程号,课程名,成绩
FROM KCB,CJB
WHERE KCB.课程号=CJB.课程号 AND 课程名 = '计算机基础'
FOR UPDATE OF 成绩
OPEN KC_CUR3
DECLARE @ckh CHAR(6)
DECLARE @kcm CHAR(6)
DECLARE @cj INT
FETCH FIRST FROM KC_CUR3 INTO @ckh,@kcm,@cj
WHILE(@@FETCH_STATUS=0)
BEGIN
PRINT '课程号:'+@ckh
PRINT '课程名:'+@kcm
PRINT '成绩:'+CAST(@cj+10 AS CHAR(2)) ----CONVER(CHAR(2),@zxf)
PRINT '--------------'
FETCH NEXT FROM KC_CUR3 INTO @ckh,@kcm,@cj
END 
9.4使用游标删除数据集的所有行
--使用游标删除数据集的所有行
DECLARE KC_CUR3 CURSOR
LOCAL SCROLL SCROLL_LOCKS
FOR
SELECT KCB.课程号,课程名,成绩
FROM KCB,CJB
WHERE KCB.课程号=CJB.课程号 AND 课程名 = '计算机基础'
FOR UPDATE OF 成绩
OPEN KC_CUR3
FETCH FIRST FROM KC_CUR3
WHILE(@@FETCH_STATUS=0)
BEGIN
DELETE FROM CJB WHERE CURRENT OF KC_CUR3
FETCH NEXT FROM KC_CUR3
END 
10、将课程号为101、学号为081101的成绩循环修改到大于等于94，每次加5，并输出循环次数。
-- 将课程号为101、学号为081101的成绩循环修改到大于等于94，每次加5，并输出循环次数。
DECLARE @num INT
SET @num=0
WHILE (SELECT 成绩 FROM CJB WHERE 学号='081101' and 课程号 = '101')<=94
BEGIN
UPDATE CJB SET 成绩=成绩+5 WHERE 学号='081101' and 课程号 = '101'
SET @num=@num+1
END
SELECT @num AS 循环次数 
本社区面向用户介绍CSDN开发云部门内部产品使用和产品迭代功能，产品功能迭代和产品建议更透明和便捷
更多推荐
20
0- 0
已为社区贡献1条内容
所有评论(0)