案例：创建一个学生管理系统（PXSCJ1）的数据库（SQL）-Toy模板网

这篇具有很好参考价值的文章主要介绍了案例：创建一个学生管理系统（PXSCJ1）的数据库（SQL）。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

1、新建数据库：PXSCJ1

--创建数据库

CREATE DATABASE PXSCJ1

案例：创建一个学生管理系统（PXSCJ1）的数据库（SQL）

--创建并确认属性：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

代码见上！

案例：创建一个学生管理系统（PXSCJ1）的数据库（SQL）

3、设计每个表的实体完整性：键、索引

代码见上！

案例：创建一个学生管理系统（PXSCJ1）的数据库（SQL）

4、设计每个表的域完整性：CHECK语句

代码见上！

案例：创建一个学生管理系统（PXSCJ1）的数据库（SQL）

5、建立表与表之间的参照完整性：XSB与CJB,KCB与CJB

代码见上！

案例：创建一个学生管理系统（PXSCJ1）的数据库（SQL）

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)

案例：创建一个学生管理系统（PXSCJ1）的数据库（SQL）

删除：

--删除学生表中总学分为40的学生
use PXSCJ1
delete from XSB
where (总学分=40)

案例：创建一个学生管理系统（PXSCJ1）的数据库（SQL）

7、进行查询操作

7.1查询计算机专业的学生信息：

--查询计算机专业的学生信息
SELECT * FROM XSB WHERE 专业='计算机'

案例：创建一个学生管理系统（PXSCJ1）的数据库（SQL）

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

案例：创建一个学生管理系统（PXSCJ1）的数据库（SQL）

7.3对XSB表选择姓名，专业和总学分，返回结果集的前6行：

--对XSB表选择姓名，专业和总学分，返回结果集的前6行：


select top 6 姓名,专业,总学分
from XSB

案例：创建一个学生管理系统（PXSCJ1）的数据库（SQL）

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

案例：创建一个学生管理系统（PXSCJ1）的数据库（SQL）

7.5查询XSB表中姓“王”且双名的学生情况：

--查询XSB表中姓“王”且双名的学生情况：

SELECT * FROM XSB 
WHERE 姓名 LIKE '王__'

案例：创建一个学生管理系统（PXSCJ1）的数据库（SQL）

7.6查询未选修离散数学的学生情况

--查询未选修离散数学的学生情况

SELECT * FROM XSB 
 WHERE 学号 IN 
 (
SELECT 学号 FROM CJB 
 WHERE 课程号 IN        
    (	
	SELECT 课程号 FROM KCB
      WHERE 课程名 != '离散数学'))

案例：创建一个学生管理系统（PXSCJ1）的数据库（SQL）

7.7查询206号课程成绩不低于101号课程最低成绩的学生学号

--查找206号课程成绩不低于101号课程最低成绩的学生学号

SELECT 学号,成绩 
  FROM CJB
  WHERE 课程号='206' AND 成绩!<
  (SELECT MIN(成绩)
          FROM CJB
       WHERE 课程号='101')

案例：创建一个学生管理系统（PXSCJ1）的数据库（SQL）

7.8查找选修了206号课程且成绩在80分以上的学生姓名及成绩

--查找选修了206号课程且成绩在80分以上的学生姓名及成绩 

 SELECT XSB.姓名,成绩
  FROM XSB,CJB
  WHERE XSB.学号=CJB.学号 AND  课程号='206'  AND 成绩>80

案例：创建一个学生管理系统（PXSCJ1）的数据库（SQL）

7.9查询每个同学选了几门课，并输出选修大于2的记录

--每个同学选了几门课 
SELECT 学号,COUNT(课程号) AS '选课门数' FROM CJB GROUP BY 学号


--并输出选修大于2的记录
SELECT 学号,COUNT(课程号) AS '选课门数' FROM CJB GROUP BY 学号  HAVING COUNT(学号)>=2

案例：创建一个学生管理系统（PXSCJ1）的数据库（SQL）

7.10 将计算机专业的学生的“计算机基础”课程成绩按照降序排列，成绩相同的的再按姓名升序排列：

--将计算机专业的学生的“计算机基础”课程成绩按照降序排列，成绩相同的的再按姓名升序排列：

SELECT XSB.学号,姓名,KCB.课程名,CJB.成绩
FROM XSB,KCB,CJB
WHERE XSB.学号=CJB.学号 AND CJB.课程号 = KCB.课程号 AND  课程名='计算机基础'
ORDER BY 成绩 DESC

案例：创建一个学生管理系统（PXSCJ1）的数据库（SQL）

8、表上建立视图

8.1在XSB上创建计算机专业的视图

--在XSB上创建计算机专业的视图

CREATE  VIEW  CS_XS
	AS 
			SELECT  *
			FROM  XSB
WHERE 专业= '计算机'

案例：创建一个学生管理系统（PXSCJ1）的数据库（SQL）

8.2使用XSB和CJB创建计算机专业的成绩视图

--使用XSB和CJB创建计算机专业的成绩视图


CREATE  VIEW  CS_KC
	AS 
			SELECT  XSB.学号,课程号,成绩
			FROM  XSB,CJB
			WHERE 专业= '计算机' AND XSB.学号=CJB.学号
WITH CHECK OPTION

案例：创建一个学生管理系统（PXSCJ1）的数据库（SQL）

9、游标

9.1建立游标指向选修计算机基础课程的课程号、课程名、成绩信息：

--建立游标指向选修计算机基础课程的课程号、课程名、成绩信息：

DECLARE KC_CUR3 CURSOR
 LOCAL SCROLL SCROLL_LOCKS
 FOR
SELECT KCB.课程号,课程名,成绩
  FROM KCB,CJB
  WHERE KCB.课程号=CJB.课程号 AND 课程名 = '计算机基础'
 FOR UPDATE OF 成绩

案例：创建一个学生管理系统（PXSCJ1）的数据库（SQL）

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

案例：创建一个学生管理系统（PXSCJ1）的数据库（SQL）

方式二：

方式二：
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

案例：创建一个学生管理系统（PXSCJ1）的数据库（SQL）

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

案例：创建一个学生管理系统（PXSCJ1）的数据库（SQL）

方式二：

方式二：

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

案例：创建一个学生管理系统（PXSCJ1）的数据库（SQL）

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

案例：创建一个学生管理系统（PXSCJ1）的数据库（SQL）

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 循环次数

案例：创建一个学生管理系统（PXSCJ1）的数据库（SQL）