|
?
======================SqlServer大部分知识都在我这脚本里===============
如果你能把数据结构画出来的话，那么你就入门了！！呵呵！！
--查看konwyoumore库是否存在
USE master
IF exists(SELECT * FROM sysdatabases WHERE name='KnowYouMore')
BEGIN
DROP DATABASE knowyoumore;
END
GO
--创建konwyoumore数据库
CREATE DATABASE KnowYouMore
GO
USE KnowYouMore
GO
--创建角色表
CREATE TABLE roles
(
rno INT PRIMARY KEY IDENTITY(1,1),
rname VARCHAR(12) NOT NULL
);
GO
--创建注册表
CREATE TABLE users
(
uno INT PRIMARY KEY IDENTITY(1,
uname VARCHAR(32) NOT NULL UNIQUE,
upwd VARCHAR(32) NOT NULL,
star INT,
trueName VARCHAR(16) NOT NULL,
rno INT FOREIGN KEY REFERENCES roles(rno)
);
GO
--创建班级表
CREATE TABLE classes
(
cno INT PRIMARY KEY IDENTITY(1,
cname VARCHAR(32) NOT NULL UNIQUE,
?? cdescribe VARCHAR(100)
);
GO
--创建学生表
CREATE TABLE students
(
sno INT PRIMARY KEY IDENTITY(1,
sname VARCHAR(16) NOT NULL UNIQUE,
sex CHAR(2) NOT NULL CHECK(sex='男'or sex='女') DEFAULT('男'),
birth DATETIME NOT NULL DEFAULT getdate(),
nativeplace VARCHAR(16) NOT NULL,
address VARCHAR(100) NOT NULL,
QQNo VARCHAR(32) NOT NULL,
phoneNo VARCHAR(12) NOT NULL,
nowTerm VARCHAR(5) NOT NULL,
selfPic VARCHAR(100) DEFAULT '6666.jpg',
selfDescribe VARCHAR(100) NOT NULL,
cno INT FOREIGN KEY REFERENCES classes(cno)
);
GO
--创建教师表
CREATE TABLE teachers
(
tno INT PRIMARY KEY IDENTITY(1,
tname VARCHAR(16) NOT NULL UNIQUE,
taddress VARCHAR(100) NOT NULL,
tQQNo VARCHAR(32) NOT NULL,
tphoneNo VARCHAR(12) NOT NULL,
selfPic VARCHAR(100),
cno INT FOREIGN KEY REFERENCES classes(cno)
);
GO
--创建意见表 每天学习评分
CREATE TABLE stuQuestion
(
sname VARCHAR(16),
cname VARCHAR(32) FOREIGN KEY REFERENCES classes(cname),
todayGoal INT NOT NULL,
konwledge VARCHAR(100) NOT NULL,
diffPoint VARCHAR(100) NOT NULL,
unknow VARCHAR(100) NOT NULL,
stuIdea VARCHAR(100),
mywords VARCHAR(800),
nowterm VARCHAR(100),
emdate DATETIME DEFAULT getdate(),
);
GO
--创建每天学习规划表
CREATE TABLE everyDayLayout
(
sname VARCHAR(16),
myidea VARCHAR(200),
);
GO
--创建老师满意度表
CREATE TABLE oktoTea
(
tname VARCHAR(16) FOREIGN KEY REFERENCES teachers(tname) ON DELETE CASCADE ON UPDATE CASCADE,
choicecount INT DEFAULT 0,
choiceNo INT,-- 1-》很差 2-》一般 3-》较好 4-》不错
);
GO
--当有老师添加时就自动给生成四种状态的记录 -->触发器
CREATE TRIGGER addFourthingstoTeacher
ON teachers
AFTER INSERT
AS
DECLARE @tname VARCHAR(16)
SELECT @tname=tname FROM inserted;
INSERT INTO oktoTea VALUES(@tname,1);
INSERT INTO oktoTea VALUES(@tname,2);
INSERT INTO oktoTea VALUES(@tname,3);
INSERT INTO oktoTea VALUES(@tname,4);
GO
--查看老师满意度的视图
CREATE VIEW query_teaInfor
AS
SELECT tname '姓名',choicecount '票数',
CASE choiceNo
WHEN 1 THEN '很差'
WHEN 2 THEN '一般'
WHEN 3 THEN '很好'
WHEN 4 THEN '不错'
END '评价'
FROM oktoTea
GO
SELECT * FROM query_teaInfor
--创建学校满意度表
CREATE TABLE oktoSchool
(
choicecount INT DEFAULT 0,
choiceNo INT -- 1-》很差 2-》一般 3-》较好 4-》不错
);
GO
--创建学生的豆豆表
CREATE TABLE myBeans
(
sname VARCHAR(16) REFERENCES students(sname) ON DELETE CASCADE ON UPDATE CASCADE,
beancount INT DEFAULT 0
);
GO
--创建投豆豆表
CREATE TABLE sendBean
(
whosend VARCHAR(32),
sendwho VARCHAR(32),
reason VARCHAR(256),
emdate DATETIME DEFAULT getdate()
);
GO
--当有学生注册自动给生成个自己豆豆记录--》触发器
CREATE TRIGGER addMyBeanInfor
ON students
AFTER INSERT
AS
DECLARE @sname VARCHAR(16)
SELECT @sname=sname FROM inserted
INSERT INTO myBeans VALUES(@sname,0);
GO
--当有学生删除自动给消除自己豆豆记录信息--》触发器
CREATE TRIGGER deleteMyBeanInfor
ON students
AFTER DELETE
AS
DECLARE @sname VARCHAR(16)
SELECT @sname=sname FROM deleted
DELETE FROM myBeans WHERE sname=@sname ;
GO
--当有用户投豆豆给自己则豆豆数量加1--》触发器
CREATE TRIGGER addBeanCount
ON sendBean
AFTER INSERT
AS
DECLARE @name VARCHAR(32)
SELECT @name=sendwho FROM inserted
UPDATE myBeans SET beancount=beancount+1 WHERE sname=@name
GO
--当有用户被删除投豆豆信息删除--》触发器
--CREATE TRIGGER deleteBeanCount
--ON students
--AFTER DELETE
--AS
-- DECLARE @name VARCHAR(32)
-- SELECT @name=sendwho FROM deleted
-- DELETE FROM sendBean WHERE sname=@name
--GO
--------数据插入地带-----------------
--角色插入
INSERT INTO roles VALUES('学生');
INSERT INTO roles VALUES('教师');
INSERT INTO roles VALUES('校长');
SELECT * FROM roles
--用户插入
INSERT INTO users VALUES('飞跃无限','nibuzhidao',5000,'何骏飞',1);
INSERT INTO users VALUES('teacher','nihao',100,'杨莹',2);
SELECT * FROM users
--班级插入
INSERT INTO classes VALUES('02IT010808011','以后的精英，加油各位！');
SELECT * FROM classes
--学生插入
INSERT INTO students VALUES('何骏飞','男','1990-01-13','江西','江西省九江市彭泽县上十岭','394201098','15010831383','二期','6666.jpg','加油！明天会更好!',1);
INSERT INTO students VALUES('何飞','012.jpg',1);
SELECT * FROM students
--教师插入
INSERT INTO teachers VALUES('杨莹','女','1986-06-06','沈阳','沈阳某地方','12345678','15901211436','teaYang.jpg','大家加油！',1);
SELECT * FROM teachers
--意见分析
INSERT INTO stuQuestion VALUES('何骏飞','02IT010808011',7,'学到了很多类','布局绝的不怎么好看','同步','让我们看些漂亮的布局','synchronized','2008-11-11');
SELECT * FROM stuQuestion
--规划表
INSERT INTO everyDayLayout VALUES('何骏飞','C#???','软件还不了解','今天很热','2008-11-11');
SELECT * FROM query_teaInfor
--满意度表
INSERT INTO oktoSchool VALUES(0,1);
INSERT INTO oktoSchool VALUES(0,2);
INSERT INTO oktoSchool VALUES(0,3);
INSERT INTO oktoSchool VALUES(0,4);
SELECT * FROM oktoSchool;
SELECT * FROM myBeans;
SELECT * FROM sendBean;
GO
---------------------------存储过程(学生版)--------------------------
--返回角色存储过程
CREATE PROCEDURE return_Roles
AS
BEGIN
SELECT rname FROM roles
END
GO
--返回班级名存储过程
CREATE PROCEDURE return_cname
AS
BEGIN
SELECT cname FROM classes
END
GO
--返回老师姓名存储过程
CREATE PROCEDURE return_tname
AS
BEGIN
SELECT tname FROM teachers
END
GO
--登录存储过程
CREATE PROCEDURE Query_user
@uname VARCHAR(32),
@upwd1 VARCHAR(32),
@rname VARCHAR(12),
@result INT OUTPUT
AS
BEGIN
IF exists(SELECT * FROM users WHERE uname=@uname )
BEGIN
?? DECLARE @upwd2 VARCHAR(32),@rno1 INT,@rno2 INT
?? SELECT @rno1=rno FROM roles WHERE rname=@rname
?? SELECT @upwd2=upwd,@rno2=rno FROM users WHERE uname=@uname
?? IF @upwd2=@upwd1
?? BEGIN
??? IF @rno2=@rno1
??? BEGIN
???? UPDATE users SET star=star+1
???? SET @result=0 RETURN--输入都正确
??? END
??? ELSE SET @result=-3 RETURN
?? END
?? ELSE SET @result=-2 RETURN--密码不对
END
ELSE
BEGIN
?? SET @result=-1--用户名不存在
?? RETURN
END
END
GO
--注册存储过程
CREATE PROCEDURE login
@uname VARCHAR(32),
@upwd VARCHAR(32),
@trueName VARCHAR(16),
@result INT OUTPUT
AS
BEGIN
IF exists(SELECT * FROM users WHERE uname=@uname )
BEGIN
?? SET @result=-1--用户名已存在
?? RETURN
END
ELSE
BEGIN
?? DECLARE @rno INT
?? SELECT @rno=rno FROM roles WHERE rname=@rname
?? INSERT INTO users VALUES(@uname,@upwd,1,@trueName,@rno)
?? SET @result=1 RETURN
END
END
GO
--添加学生存储过程
CREATE PROCEDURE addStudent
@sname VARCHAR(16),
@sex CHAR(2),
@birth VARCHAR(16),
@nativeplace VARCHAR(16),
@address VARCHAR(100),
@QQNo VARCHAR(32),
@phoneNo VARCHAR(12),
@nowTerm VARCHAR(5),
@selfPic VARCHAR(100),
@selfDescribe VARCHAR(100),
@cname VARCHAR(32),
@result INT OUTPUT
AS
BEGIN
DECLARE @cno INT
SELECT @cno=cno FROM classes WHERE cname=@cname
INSERT INTO students(sname,sex,birth,nativeplace,address,QQNo,phoneNo,nowTerm,selfPic,selfDescribe,cno) VALUES(@sname,@sex,@birth,@nativeplace,@address,@QQNo,@phoneNo,@nowTerm,@selfPic,@selfDescribe,@cno)
SET @result=1 RETURN
END
GO
--添加教师存储过程
CREATE PROCEDURE addTeacher
@tname VARCHAR(16),
@taddress VARCHAR(100),
@tQQNo VARCHAR(32),
@tphoneNo VARCHAR(12),
@result INT OUTPUT
AS
BEGIN
DECLARE @cno INT
SELECT @cno=cno FROM classes WHERE cname=@cname
INSERT INTO teachers(tname,taddress,tQQNo,tphoneNo,cno) VALUES(@tname,@taddress,@tQQNo,@tphoneNo,@cno)
SET @result=1 RETURN
END
GO
--查询同学信息存储过程
CREATE PROCEDURE query_classmate
@sname VARCHAR(16),
@result INT OUTPUT
AS
BEGIN
IF exists(SELECT * FROM students WHERE sname=@sname )
BEGIN
?? DECLARE @no INT
?? DECLARE @cname2 VARCHAR(32)
?? SELECT sname,cname,selfDescribe FROM students,classes WHERE sname=@sname AND classes.cno=students.cno
?? SET @result=1 RETURN
END
ELSE
BEGIN
?? SET @result=-1 RETURN--不存在此学生
END
END
GO
--查看老师信息
CREATE PROCEDURE query_AllTeachers
AS
BEGIN
SELECT tname,cname FROM teachers,classes WHERE teachers.cno=classes.cno
END
GO
--查询等级存储过程
CREATE PROCEDURE query_Star
@uname VARCHAR(32)
AS
BEGIN
SELECT star FROM users WHERE uname=@uname
END
GO
--返回当前用户所在的班级的同学存储过程
CREATE PROCEDURE return_NowUserClassmate
@uname VARCHAR(32)
AS
BEGIN
DECLARE @name VARCHAR(32);
DECLARE @cno INT;
SELECT @name=trueName FROM users WHERE uname=@uname ;
SELECT @cno=cno FROM students WHERE sname=@name ;
SELECT sname FROM students WHERE cno=@cno ;
END
GO
--返回当前用户的班级名称存储过程
CREATE PROCEDURE return_NowUserCName
@uname VARCHAR(32)
AS
BEGIN
DECLARE @name VARCHAR(32);
DECLARE @cno INT;
SELECT @name=trueName FROM users WHERE uname=@uname ;
SELECT @cno=cno FROM students WHERE sname=@name ;
SELECT cname FROM classes WHERE cno=@cno ;
END
GO
--返回当前用户的个人图片名称存储过程
CREATE PROCEDURE return_NowUserPic
@uname VARCHAR(32)
AS
BEGIN
DECLARE @name VARCHAR(32);
SELECT @name=trueName FROM users WHERE uname=@uname ;
SELECT selfPic FROM students WHERE sname=@name ;
END
GO
--每天学习评分存储过程
CREATE PROCEDURE addEveryDayGrade
@sname VARCHAR(16),
@todayGoal INT,
@knowledgePoint VARCHAR(100),
@diffPoint VARCHAR(100),
@unknow VARCHAR(100),
@stuIdea VARCHAR(100),
@myWords VARCHAR(800),
@nowTerm VARCHAR(100),
@emDate VARCHAR(100),
@result INT OUTPUT
AS
BEGIN
IF exists(SELECT * FROM stuQuestion WHERE emdate=@emDate )
BEGIN
?? SET @result=-1 RETURN
END
ELSE
BEGIN
?? INSERT INTO stuQuestion VALUES(@sname,@cname,@todayGoal,@knowledgePoint,@diffPoint,@unknow,@stuIdea,@myWords,@emDate);
?? SET @result=1 RETURN
END
END
GO
--每天学习规划存储过程
CREATE PROCEDURE addEveryDayLayout
@sname VARCHAR(16),
@myIdea VARCHAR(100),
@result INT OUTPUT
AS
BEGIN
IF exists(SELECT * FROM everyDayLayout WHERE emdate=@emDate )
BEGIN
?? SET @result=-1 RETURN
END
ELSE
BEGIN
?? INSERT INTO everyDayLayout VALUES(@sname,@myIdea,@emDate);
?? SET @result=1 RETURN
END
END
GO
--老师查看学生当天表现、建议
CREATE PROCEDURE query_StuBehave
AS
BEGIN
SELECT sname,todayGoal,diffPoint,unknow,stuIdea,emdate FROM stuQuestion,teachers WHERE teachers.cno=(SELECT cno FROM classes WHERE cname=stuQuestion.cname)
END
GO
--学生学期总结时给老师的评分存储过程
CREATE PROCEDURE ticketToTea
@tname VARCHAR(16),
@choice VARCHAR(10)
AS
BEGIN
IF @choice='很差' UPDATE oktoTea SET choicecount=choicecount+1 WHERE tname=@tname AND choiceNo=1;
ELSE IF @choice='一般' UPDATE oktoTea SET choicecount=choicecount+1 WHERE tname=@tname AND choiceNo=2;
ELSE IF @choice='很好' UPDATE oktoTea SET choicecount=choicecount+1 WHERE tname=@tname AND choiceNo=3;
ELSE IF @choice='不错' UPDATE oktoTea SET choicecount=choicecount+1 WHERE tname=@tname AND choiceNo=4;
END
GO
--学生学期总结时给学校的评分存储过程
CREATE PROCEDURE ticketToSchool
@choice VARCHAR(10)
AS
BEGIN
IF @choice='很差' UPDATE oktoSchool SET choicecount=choicecount+1 WHERE choiceNo=1;
ELSE IF @choice='一般' UPDATE oktoSchool SET choicecount=choicecount+1 WHERE choiceNo=2;
ELSE IF @choice='很好' UPDATE oktoSchool SET choicecount=choicecount+1 WHERE choiceNo=3;
ELSE IF @choice='不错' UPDATE oktoSchool SET choicecount=choicecount+1 WHERE choiceNo=4;
END
GO
--投豆豆存储过程
CREATE PROCEDURE sendBeanTo
@uname VARCHAR(32),
@sendwho VARCHAR(32),
@reason VARCHAR(256),
@emdate VARCHAR(32),
@result INT OUTPUT
AS
BEGIN
DECLARE @whosend VARCHAR(32)
SELECT @whosend=trueName FROM users WHERE uname=@uname
IF exists(SELECT * FROM sendBean WHERE emdate=@emdate and whosend=@whosend )
BEGIN
?? SET @result=-1 RETURN;
END
ELSE
BEGIN
?? INSERT INTO sendBean VALUES(@whosend,@sendwho,@reason,@emdate);
?? SET @result=1 RETURN;
END
END
GO
--返回当前用户豆豆数
CREATE PROCEDURE return_Beancount
@uname VARCHAR(32)
AS
BEGIN
DECLARE @name VARCHAR(32)
SELECT @name=trueName FROM users WHERE uname=@uname ;
SELECT beancount FROM myBeans WHERE sname=@name ;
END
GO
--查看是谁给我豆豆存储过程
CREATE PROCEDURE whogiveMeBean
@uname VARCHAR(32)
AS
BEGIN
DECLARE @name VARCHAR(32)
SELECT @name=trueName FROM users WHERE uname=@uname ;
SELECT whosend,reason,emdate FROM sendBean WHERE sendwho=@name
END
GO
--返回用户的信息存储过程
CREATE PROCEDURE userInfor
@uname VARCHAR(32)
AS
SELECT upwd,trueName,rname FROM users,roles WHERE users.rno=roles.rno and uname=@uname
GO
--返回用户当前学期存储过程
CREATE PROCEDURE userNowTerm
@uname VARCHAR(32)
AS
DECLARE @trueName VARCHAR(16)
SELECT @trueName=trueName FROM users WHERE uname=@uname
SELECT nowTerm FROM students WHERE sname=@trueName
GO
--用户修改自己信息（用户表的修改）的存储过程
CREATE PROCEDURE updateSelfUserInfor
@unameAgo VARCHAR(32),
@uname VARCHAR(32),
@rname VARCHAR(12)
AS
BEGIN
DECLARE @rno INT
SELECT @rno=rno FROM roles WHERE rname=@rname
UPDATE users SET uname=@uname,rno=@rno WHERE uname=@unameAgo
END
GO
--用户学生修改时修改学生表
CREATE PROCEDURE updateSelfStudentInfor
@sname VARCHAR(16),
@snameAgo VARCHAR(16)
AS
BEGIN
DECLARE @cno INT
SELECT @cno=cno FROM classes WHERE cname=@cname
UPDATE students SET sname=@sname,cno=@cno WHERE sname=@snameAgo END GO
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
