实验之前最好重新建一个数据库,记得改路径
这个可以查SQL的基本语句/sql/sql-tutorial.html
USE [master]GO/****** Object: Database [xscj] Script Date: /2/21 16:42:38 ******/CREATE DATABASE [xscj]CONTAINMENT = NONEON PRIMARY ( NAME = N'XSCJ_Data', FILENAME = N'home\learn\xscj.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)LOG ON ( NAME = N'XSCJ_Log', FILENAME = N'home\learn\xscj_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)GOUSE [xscj]GO/****** Object: Table [dbo].[COU] Script Date: /2/21 16:42:38 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGODROP TABLE SCGODROP TABLE COUGODROP TABLE STUGODROP TABLE MAJORGOCREATE TABLE dbo.COU(CNO char(4) NOT NULL,CNAME varchar(30) NOT NULL,CREDIT smallint NULL,PTIME char(5) NULL,TEACHER nchar(10) NULL,CONSTRAINT PK_COU PRIMARY KEY CLUSTERED (CNO ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON PRIMARYGO/****** Object: Table [dbo].[MAJOR] Script Date: /2/21 16:42:38 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE major(mno char(2) NOT NULL,mname varchar(20) NOT NULL,CONSTRAINT PK_MAJOR PRIMARY KEY CLUSTERED (mno ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON PRIMARYGO/****** Object: Table [dbo].[SC] Script Date: /2/21 16:42:38 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE sc(sno char(4) NOT NULL,cno char(4) NOT NULL,grade numeric(6, 1) NULL,CONSTRAINT [PK_SC] PRIMARY KEY CLUSTERED ([SNO] ASC,[CNO] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GO/****** Object: Table [dbo].[STU] Script Date: /2/21 16:42:38 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE stu(sno char(4) NOT NULL,sname char(8) NOT NULL,sex int NULL,mno char(2) NULL,birdate smalldatetime NULL,memo text NULL,CONSTRAINT [PK_STU] PRIMARY KEY CLUSTERED ([SNO] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GOALTER TABLE stu ALTER COLUMN sex SET DEFAULT 1;GOALTER TABLE sc WITH NOCHECK ADD CONSTRAINT FK_CNO FOREIGN KEY(cno)REFERENCES cou (cno)GOALTER TABLE [dbo].[SC] CHECK CONSTRAINT [FK_CNO]GOALTER TABLE cou ADD CHECK ((credit>=0 AND credit<=20))GOALTER TABLE [dbo].[COU] CHECK CONSTRAINT [ck_credit]GOUSE [master]GOALTER DATABASE [xscj] SET READ_WRITE GOUSE xscjGOinsert cou values ( 'C001','高等数学',8,'1','孙老师');insert cou values ( 'C002','C语言',5,'2','张老师');insert cou values ( 'C003','数据结构',4,'3','张老师');insert cou values ( 'C004','操作系统',3,'4','罗老师');insert cou values ( 'C005','组成原理',4,'3','陈老师');insert cou values ( 'C006','数据库原理',3,'5','吴老师');insert cou values ( 'C007','JAVA程序设计',3,'3','李老师');insert major values ( '01','计算机工程');insert major values ( '02','软件工程');insert major values ( '03','网络工程');insert major values ( '04','智能技术');insert stu values ( 'S001','张三',1,'01','2001-10-01 00:00:00.000','二年级，荣获学院三好学生');insert stu values ( 'S002','李四 ',0,'02','2001-05-13 00:00:00.000',NULL);insert stu values ( 'S003','李芳 ',0,'01','2000-05-11 00:00:00.000',NULL);insert stu values ( 'S004','张明 ',1,'02','2002-05-31 00:00:00.000','一年级，荣获优秀学生干部');insert stu values ( 'S005','张强 ',1,'02','2001-06-01 00:00:00.000',NULL);insert stu values ( 'S006','吴玲 ',0,'02','2000-06-11 00:00:00.000',NULL);insert stu values ( 'S007','郑奇 ',1,'01','2000-06-11 00:00:00.000',NULL);insert stu values ( 'S008','吴丽丽',0,'03','2001-06-17 00:00:00.000',NULL);insert stu values ( 'S009','林森 ',1,'03','2002-01-10 00:00:00.000',NULL);insert stu values ( 'S010','徐小宜 ',1,'04','-05-11 00:00:00.000',NULL);insert sc values ( 'S001','C001',68.6);insert sc values ( 'S001','C002',95.5);insert sc values ( 'S001','C003',74.5);insert sc values ( 'S002','C001',70.0);insert sc values ( 'S002','C002',86.1);insert sc values ( 'S003','C001',89.3);insert sc values ( 'S003','C005',78.8);insert sc values ( 'S003','C006',67.6);insert sc values ( 'S004','C002',67.6);insert sc values ( 'S004','C004',50.2);insert sc values ( 'S005','C002',80.9);insert sc values ( 'S010','C001',NULL);select * from scselect * from couselect * from stuselect * from major
1.视图的创建与应用
基本概念
为表 sc创建一个视图，包括有各个学生学号、选修课程的门数及平均分,如：
create view s_grade(sno,c_num,avg_grade)
as
select sno,count(cno),avg(grade) from sc group by sno
实践下列查询和更新操作的功能，是否出错，若可运行，写出转换至基本表SC上的相应操作。 若不可运行，说明原因
(1)select * fr
(2)om s_grade;
(3)select sno,c_num from s_grade where avg_grade>80
(4)select sno,avg_grade from s_grade where c_num>(select c_num from s_grade where sno=’S004’)
(5)update s_grade set c_num=c_num+2
(5)delete from s_grade where c_num>3
2．对SC表创建视图COMPUTER_SC，该视图包括‘01’专业的学生的学号、姓名与性别 设计实验完成该视图数据的查询、添加、修改与删除。进一步理解行列子集视图与非行列子集视图应用上的区别
create view COMPUTER_SC as select sno, sname, sex from stu where mno = '01'select * from COMPUTER_SC;insert into COMPUTER_SC values('S012', '叶华', '1')update COMPUTER_SC set sname = '花生酱' where sno = 'S001'delete from COMPUTER_SC where sno = 'S001'
3．T-SQL 1）使用CASE WHEN
查询学生的所有信息，若性别为1，则性别显示‘男’，否则显示‘女’
select * , '性别' = case when sex = 1 then '男' else '女' end from stu
2）使用WHILE及游标等
游标概念用法请戳
对SC表添加一个等级列，若学生成绩80及以上等级A，70-79分为B，其余为C，null仍为null
declare @sno char(4),@cno char(4),@grade numeric(6, 1),@rank char(1)declare sc_cur1 cursorforselect *from scopen sc_cur1while(@@fetch_status=0)beginfetch next from sc_cur into @sno,@cno,@gradeif(@grade>=80) set @rank='A'else if(@grade>=70 and @grade<70) set @rank='B'else if(@grade is null) set @rank=nullelse set @rank='C'select sno=@sno,cno=@cno,grade=@grade,rank=@rankendclose sc_cur1deallocate sc_cur1
3）使用WHILE及游标
在XSCJ数据库运行如下脚本，
Create table sumcredit(sno char(4) primary key,sum_credit numeric(4,1))
Insert into sumcredit select sno,0 from stu
使用游标完成每个学生的总学分，并更新入sumcredit表中。只有60分及以上才可获得课程学分。
deallocate sum_curdeclare @sno char(4), @sum numeric(4,1)declare sum_cur cursor for select sno, sum(credit) from sc left outer join cou on o = o and sc.grade >=60 group by sc.snoopen sum_curfetch next from sum_cur into @sno, @sumwhile(@@FETCH_STATUS = 0)beginfetch next from sum_cur into @sno, @sum update sumcredit set sum_credit = @sum where sumcredit.sno = @snoendclose sum_curselect * from sumcredit
4．据SQL SERVER提供的完整性完成完整性实践
SQL SERVER提供的数据库完整性包括域完整性(CHECK,DEFAULT,UNQUE,RULE，用户自定义类型、基本数据类型)、表间完整性（复习实体完整性、参照完整性的定义与验证）、复杂完整性表达（触发器）
1）创建stu1,major1表，表的结构与xscj数据库的的stu及major一致。在创建与修改表结构的过程中应用好以上完整性概念
2）插入数据，说明实体完整性与参照完整性的检查机制。删除数据说明参照完整性的检查机制。截图说明
3）对STU1表任意插入一条数据，该数据违背了你所定义的所有完整性规则。分别说明 违背了什么，并截图
完整性概念
2）创建一个用户自定义类型，用于定义MAJOR1表与STU1表的mno(提示：查看帮助CREATE TYPE 、CREATE RULE，SP_BINDRULE,SP_UNBINDRULE等) 专业号由M开头，其余字符为数字字符，长度为2.
sno like ‘S[0-9][0-9]’
5、存储过程创建与使用
1）创建存储过程P1，查询学生表所有信息；运行之。
create procedure p1 as select * from stup1
2）创建存储过程P2，查询某学号学生的所有信息，运行
create procedure p2 @sno char(4) as select * from stu where sno = @snop2 'S001';
3）创建存储过程P3，查询某学号学生的姓名及所就读专业，运行之
create procedure p3 @sno char(4) as select sname, mname from stu, major where sno = @sno and stu.mno = major.mnop3 'S001';
4）创建存储过程P4，查询某课程名的平均分，选课人数并输出之。运行之
create procedure p4 @cname varchar(30) as select cname, avg(grade) from sc, cou where O = o and cname = @cname group by cnamep4 '高等数学'
5）创建存储过程P5，完成该生的退学业务处理（即删除某学号学生的所有信息），运行之。
create procedure p5 @sno char(4) asdelete sc from sc where sno = @snodelete stu from stu where sno = @snop5 'S013'
6）创建存储过程P6，完成为某学号学生插入两条选课记录（注意不能插入重复的选课记录），并返回统计当前选修了几门课。运行之
create procedure p6 @sno char(4) asinsert into sc values(@sno, 'C004', 99)insert into sc values(@sno, 'C005', 66)select count(cno) as sumcno from sc where sno = @sno group by snop6 'S009'--drop procedure p6
7)创建存储过程P7，返回每个学生的平均分。运行之
create procedure p7 asselect sno, avg(grade) from sc group by snop7
8)创建存储过程P8，完成学生状态处理，若学生75分以下课程数超过其75分以上的课程数，则留级，否则标注为升学,运行之
提示：存储过程创建可参考CREATE PROCEDURE的帮助。
create procedure p8 asselect distinct sno, sname, '状态' = case when(select count(grade) from sc where grade >= 60 and sno = stu.sno) >=(select count(grade) from sc where grade < 60 and sno = stu.sno)then '正常' else '留级'endfrom stup8
6．触发器应用 触发器（create trigger） 1）创建触发器
触发器是一种特殊的存储过程，它不能被显式地调用，而是在往表中插入记录﹑更新记录或者删除记录时被自动地激活。 所以触发器可以用来实现对表实施复杂的完整性约束。
触发器基础知识
触发器用法
create trigger cr_sc on sc after insert as begin tran tran_scdeclare @sno varchar(4)declare @cno varchar(4)declare @cnt intset @sno = (select sno from inserted)set @cno = (select cno from inserted)set @cnt = (select count(cno) from sc where sno = @sno and cno = @cno group by sno)if(@cnt >= 2)begin print '已存在该选课'rollback tranendelse beginprint '选课成功'commit tranend
2）创建触发器，实现每门课最多只能8个学生选修。
create trigger tri_sc on scafter insert as begin tran p_numdeclare @cno varchar(4)declare @cnt intset @cnt = (select count(sno) from sc where cno = @cno)if(@cnt >= 8)beginprint '选课人数已达上限，请选择其他课程'rollback tranendelse begin print '选课成功'commit tran end
3）创建触发器，实现每个学生最多选修5门课.
create trigger t_sc on sc after insert asbegin tran p_numdeclare @sno varchar(4) declare @cnt int set @sno = (select sno from inserted)set @cnt = (select count(cno) from sc where sno = @sno)if(@cnt >= 5)begin print '你的选课已达上限'rollback tranendelse beginprint '选课成功'commit tranend
4）创建触发器，实现删除STU表某学生的记录，注意完整性概念保证，使用用instead of
instead of 用法
create trigger del_stu on stuinstead of deleteas begin delete stu where sno = (select sno from deleted)delete sc where sno =(select sno from deleted)end
5）创建触发器，实现“男子汉的培养”课程限男生选修。（注：在课程表中添加一条记录“男子汉的培养”）
create trigger man_sc on scafter insert asbegin tran p_numdeclare @sno varchar(4)declare @sex bitdeclare @cno varchar(4)set @sno = (select sno from inserted)set @sex = (select sex from stu where sno = @sno)set @cno = (select cno from inserted)if @sex = 1 and @cno = (select cno from cou where cname = '男子汉培养' )begin print '选课成功'commit tran endelse beginprint '男生才能选'rollback tranend
7．综合应用：
设系统增加一个需求，为stu表添加一个sum_credit字段，填写该字段的数值
（提示：原已选修的课程所获得的学分必须计算统一，以后每选修一门课，若成绩满足>=60分条件则获得自动获取该课程学分）
Alter table stu add sum_credict;insert into stuselect sum(credit) from (select stu.sno,(case when grade>=60 then credit else 0 end) creditfrom stuleft join sc on stu.sno=sc.sno left join cou on o=o) agroup by sno
思考题：
为以下录入记录要求设计界面，可以使用java、net或javascript实现后截图
为stu设计录入界面，并编程实现之
为COU表设计录入界面，并编程实现之
为SC表设计录入界面,并编程实现之
要求：附界面及运行结果。
如有错误,请指出
