----------第一阶段 创建数据库,表和分配权限
----判断是否存在数据库
IF EXISTS(SELECT * FROM sysdatabases where name='student')
DROP DATABASE student
GO
CREATE DATABASE student
ON PRIMARY
(
NAME='STUDENT_data', ---主数据逻辑文件
FILENAME='E:\STUDENT_data.mdf', ----主数据物理文件
SIZE=9MB, ---初始化值
MAXSIZE=50MB, ---最大值
FILEGROWTH=20% ---增长率
)
LOG ON
(
NAME ='STUDENT_log', ----日志文件逻辑名字、
FILENAME='E:\STUDENT_log.ldf', ----日志文件物理名称
SIZE=6MB, ---初始化值
MAXSIZE=15MB, ---最大化值
FILEGROWTH=30% ---增长率
)
GO
---判断数据中是否存在表
IF EXISTS(SELECT * FROM sysobjects where name='studentInfo')
DROP TABLE studentInfo
GO
---创建数据表studentInfo
CREATE TABLE studentInfo
(
stuName VARCHAR(20) not null,
stuNo CHAR(8) NOT NULL,
stuAge INT NOT NULL,
stuId VARCHAR(8) NOT NULL,
stuSeat SMALLINT IDENTITY(1,1) NOT NULL,
stuAddress TEXT
)
GO
---在studentInfo中插入数据
INSERT INTO studentInfo VALUES('梁章荣','S2IT007',23,'S2IT001','广东省广州')
INSERT INTO studentInfo VALUES('周 翔','S2IT008',22,'S2IT002','湖南省常德')
INSERT INTO studentInfo VALUES('柳絮','S2IT009',25,'S2IT003','广东省云浮')
INSERT INTO studentInfo VALUES('邓小平','S2IT010',24,'S2IT004','四川省')
INSERT INTO studentInfo VALUES('毛主席','S2IT011',20,'S2IT005','湖南省')
---查询表studentInfo中的数据
SELECT * FROM studentInfo
---判断数据库中是否存在表stuMarks
IF EXISTS(SELECT * FROM sysobjects where name='stuMarks')
DROP TABLE stuMarks
GO
---创建数据表stuMarks
CREATE TABLE stuMarks
(
ExamNo CHAR(8) NOT NULL,
stuNo CHAR(8) NOT NULL,
WrittenExam INT NOT NULL,
LabExam INT NOT NULL,
)
GO
--删除表
DROP TABLE stuMarks
---插入数据stuMarks
INSERT INTO stuMarks VALUES('S2ID10','S2IT007',59,96)
INSERT INTO stuMarks VALUES('S2ID11','S2IT008',86,50)
INSERT INTO stuMarks VALUES('S2ID12','S2IT009',96,96)
INSERT INTO stuMarks VALUES('S2ID13','S2IT010',45,80)
INSERT INTO stuMarks VALUES('S2ID14','S2IT011',34,68)
----查询表中数据
SELECT * FROM stuMarks
--删除表
DROP TABLE stuMarks
---添加约束
--1.主键
ALTER TABLE studentInfo ADD CONSTRAINT PK_stuNo PRIMARY KEY(stuNo)
--2.唯一约束
ALTER TABLE studentInfo ADD CONSTRAINT UQ_stuId UNIQUE(stuId)
--3.默认约束
ALTER TABLE studentInfo ADD CONSTRAINT DF_stuAddress DEFAULT('地址不祥')FOR stuAddress
--4.检查约束
ALTER TABLE studentInfo ADD CONSTRAINT CK_stuAge CHECK(stuAge BETWEEN 15 AND 50)
--5.外键约束
ALTER TABLE stuMarks ADD CONSTRAINT FK_stuNo FOREIGN KEY(stuNo) REFERENCES studentInfo(stuNo)
GO
--删除约束
---1.删除外键约束
ALTER TABLE stuMarks DROP CONSTRAINT FK_stuNo
---2.删除主键约束
ALTER TABLE studentInfo DROP CONSTRAINT PK_stuNo
---3.删除唯一约束
ALTER TABLE studentInfo DROP CONSTRAINT UQ_stuId
---4.删除默认约束
ALTER TABLE studentInfo DROP CONSTRAINT DF_stuAddress
---5.删除检查约束
ALTER TABLE studentInfo DROP CONSTRAINT CK_stuAge
---创建登陆
EXEC sp_addlogin '梁章荣','123456'
---创建数据库用户
EXEC sp_grantdbaccess '梁章荣','studentInfoMessage'
---添加权限
GRANT select,update,delete ON studentInfo TO studentInfoMessage
---权限操作 创建数据表
GRANT CREATE TABLE TO studentInfoMessage
--------第二阶段 数据编程
----1.局部变量
DECLARE @name varchar(8) ----学生姓名
SET @name='周 翔' ---使用SET 赋值
----查找左右同桌
SELECT * FROM studentInfo WHERE stuName=@name
---座位号
DECLARE @seat int
----使用SET 赋值
SELECT @seat=stuSeat FROM studentInfo WHERE stuName=@name
----查询出左右同桌的名字
SELECT * FROM studentInfo WHERE (stuSeat=@seat+1)OR(stuSeat=@seat-1)
GO
---2.全局变量
print 'SQL SERVER 的版本'+@@VERSION
print '服务器的名称'+@@SERVERNAME
---插入数据
INSERT INTO studentInfo VALUES('张三','S2IT013',22,'S2IT009','湖南省')
print '当前错误号:'+CONVERT(VARCHAR(8),@@ERROR)
print '刚报名的学员座位号:'+CONVERT(VARCHAR(8),@@IDENTITY)
UPDATE studentInfo SET stuAge=90 WHERE stuName='周 翔'
PRINT '当前的错误号:'+CONVERT(VARCHAR(8),@@ERROR)
GO
----3.求平均分
DECLARE @jackAvg FLOAT
SELECT @jackAvg=avg(WrittenExam) FROM stuMarks
PRINT '本班的平均成绩:'+CONVERT(VARCHAR(5),@jackAvg)
IF(@jackAvg>70)
BEGIN
PRINT '本班成绩比较优秀的同学,前三名的成绩为:'
SELECT TOP 3 * FROM stuMarks ORDER BY WrittenExam DESC
END
ELSE
BEGIN
PRINT '本班成绩比较差的同学,后三的成绩:'
SELECT TOP 3 * FROM stuMarks ORDER BY WrittenExam
END
GO
---4.求不及格的同学加两分
DECLARE @n int
WHILE(1=1)
BEGIN
SELECT @n=COUNT(*) FROM stuMarks WHERE WrittenExam<60 ---统计不及格少于60分的同学
IF(@n>0)
BEGIN
UPDATE stuMarks SET WrittenExam=WrittenExam+2
END
ELSE
BEGIN
BREAK -------退出循环
END
END
print '加分后的成绩为:'
SELECT * FROM stuMarks
GO
----5.用ABCDEF用表示成绩的
PRINT '用ABCDEF用表示成绩的如下:'
SELECT stuNo,
成绩=CASE
WHEN WrittenExam<60 THEN 'F'
WHEN WrittenExam BETWEEN 60 AND 69 THEN 'E'
WHEN WrittenExam BETWEEN 69 AND 79 THEN 'D'
WHEN WrittenExam BETWEEN 79 AND 89 THEN 'C'
WHEN WrittenExam BETWEEN 79 AND 90 THEN 'B'
ELSE 'A'
END
FROM stuMarks
GO
----6.0 为同学们评分等级
USE student
GO
SELECT 考号=ExamNo,学号=stuNo,笔试=WrittenExam,机试=LabExam,平均分=(WrittenExam+LabExam)/2,
等级=CASE
WHEN (WrittenExam+LabExam)/2<60 THEN '不及格'
WHEN (WrittenExam+LabExam)/2 BETWEEN 60 AND 69 THEN '差'
WHEN (WrittenExam+LabExam)/2 BETWEEN 69 AND 79 THEN '中'
WHEN (WrittenExam+LabExam)/2 BETWEEN 79 AND 89 THEN '良'
WHEN (WrittenExam+LabExam)/2 BETWEEN 79 AND 90 THEN '优'
ELSE '很好'
END
FROM stuMarks
----6.0加分制度
SELECT * FROM stuMarks --原始成绩
DECLARE @labAvg INT
WHILE(1=1)
BEGIN
UPDATE stuMarks
SET labExam=
CASE
WHEN labExam<60 THEN labExam+5
WHEN labExam between 60 AND 69 THEN labExam+3
WHEN labExam between 70 AND 79 THEN labExam+2
WHEN labExam between 80 AND 89 THEN labExam+1
ELSE labExam
END
SELECT @labAvg=AVG(labExam) FROM stuMarks
IF @labAvg>=85
BREAK
END
SELECT * FROM stuMarks --加分后的成绩
------第三阶段 高级查询
CREATE TABLE bank
(
cardNo varchar(8) not null, ---卡号
userName varchar(8) not null, ----用户名
balance money not null -- 金额
)
GO
INSERT INTO bank(cardNo,userName,balance) VALUES('ID100','梁章荣',1000)
INSERT INTO bank(cardNo,userName,balance) VALUES('ID200','声动天下',1000)
DECLARE @MyMoney INT
SET @MyMoney=0
SELECT @MyMoney=balance FROM bank WHERE userName='梁章荣'
IF(@MyMoney<100)
BEGIN
print '你的卡上的余额不足100,请你充值'
print '卡上的余额:'+CONVERT(VARCHAR(8),@MyMoney)
END
print '你的年利息为:'
SELECT 利息=CASE
WHEN balance<100 THEN balance*0.01
WHEN balance>1000 THEN balance*0.20
ELSE balance*0.10
END
FROM bank WHERE userName='梁章荣'
GO