sqlServer数据库学习笔记
作者: 骆巍
说明: 我用的数据库是sqlServer2008, sqlServer2005和sqlServer2008差不多,所以我就没有分是在哪一个数据库下面学习的, 这是我学习一期数据库的笔记总结, 我拿出来给大家分享, 希望对大家有帮助. 每一个知识点都分得很详细, 如果有相应的数据库可以直接放到数据库命令行里面去执行
------------------------------创建数据库------------------------- create database ckgl
on primary --设置文件组第一个为默认文件组即主要文件组
(--创建第一个数据文件，第一个数据文件是主文件
name = xkgldata1, --设置名字
filename = 'D:\java\Databank\DATA\xkgldata1.mdf', --设置路径要加上文件名字不要忘了最后要打逗号
size = 10, --设置初始文件的大小不写默认单位是MB
maxsize = 500, --设置文件的最大值
filegrowth = 5 --设置文件的增长率
),
(--创建第二个数据文件，后缀名要改成?ndf?
name = xkgldata2,
filename = 'D:\java\Databank\DATA\xkgldata2.ndf', size = 10,
maxsize = 500,
filegrowth = 5
)
log on --创建日志文件
(name = xkg1_log,
filename = 'D:\java\Databank\DATA\xkgllog.ldf',
size = 3,
filegrowth = 10%
)
--修改一般用?alter database + 数据库名字?
--修改数据库名字
alter database ckgl modify name = xkgl
--修改数据库里面的主要数据文件
alter database xkgl modify file
(name = xkgldata1,
newname = xkglmdata
)
--增加一个数据文件
alter database xkgl add file
(name = xkgldata3,
filename = 'D:\java\Databank\DATA\xkgldata3.ndf', size = 10,
maxsize = 500,
filegrowth = 5
)
--添加文件组
alter database xkgl add filegroup myfg
--删除文件组
alter database xkgl remove filegroup myfg
--增加一个数据文件到某个文件组
alter database xkgl add file
(name = xkgldata4,
filename = 'D:\java\Databank\DATA\xkgldata4.ndf', size = 10,
maxsize = 500,
filegrowth = 5
)
to filegroup myfg
--删除一个数据文件
alter database xkgl remove file xkgldata4
--添加日志文件
alter database xkgl add log file
(name = xkgld_lg2,
filename = 'D:\java\Databank\DATA\xkglla2.ldf', size = 3,
filegrowth = 10%
)
--删除日志文件
alter database xkgl remove file xkgld_lg2
--更改数据文件的大小或日志文件的大小
alter database xkgl modify file
(name = xkgldata3,
size = 11MB
)
/*移动数据文件或日志文件，需要先将数据文件设置为脱机状态，然后移动文件到想要的路径，
然后用SQL命令设置数据文件到目标路径，然后把数据库设置为联机状态*/ alter database xkgl set offline
alter database xkgl modify file
(name = xkglmdata,
filename = 'D:\java\Database\DATA\xkgldata1.mdf'
)
alter database xkgl modify file
(name = xkgldata2,
filename = 'D:\java\Database\DATA\xkgldata2.ndf'
)
alter database xkgl modify file
(name = xkgldata3,
filename = 'D:\java\Database\DATA\xkgldata3.ndf'
)
alter database xkgl modify file
(name = xkgldata4,
filename = 'D:\java\Database\DATA\xkgldata4.ndf'
)
alter database xkgl modify file
(name = xkg1_log,
filename = 'D:\java\Database\DATA\xkgllog.ldf'
)
alter database xkgl set online
alter database xkgl modify file
(name = xkg1_log,
newname = xkgl_log
)
alter database xkgl modify file
(name = xkglmdata,
newname = xkgldata1
)
--删除数据库
drop database xkgl ----------------------------数据库表的创建--------------------------
--创建数据表
use class --选择数据库
go
create table Department --创建表
(
DepartmentID char(4) primary key, --primary key 是设置主键
DepartmentName varchar(20) not null unique, --unique是设置唯一 DepartmentHeader varchar(8) not null, --not null 是非空
TeacherNum int
)
use class
go
create table Class
(
ClassID char(8) primary key,
ClassName varchar(20) not null,
Monitor char(8),
StudentNum int check(StudentNum >= 0), --check约束
DepartmentID char(4) foreign key references
Department(DepartmentID), --foreign key 定义外键references用于指定外键所引用的表及表的主键
)
use class
go
create table Student
(
StudentID char(12) primary key,
StudentName char(8) not null,
Sex char(2) not null check(Sex = '男' or Sex = '女'),
Birth date not null,
HomeAddr varchar(80),
EntranceTime date default getdate(), -- getdate（）是得到系统时间的方法
ClassID char(8) references Class(ClassID) --行级约束时可以省略foreign key
)
create table Course
(
CourseID char(8) primary key,
CourseNmae varchar(60) not null,
BookName varchar(80) not null,
Credit int not null
)
create table Grade
(
CourseID char(8) references Course(CourseID),
StudentID char(12) references Student(StudentID),
Semester int not null,
SchoolYear int,
Grade numeric(5,1) check(Grade>= 0),
primary key(CourseID, StudentID) --在定义完CourseID和StudentID列后定义主键约束，这种约束为表级约束
)
use class
go
create table Schedule(
TeacherID char(8) references Teacher(TeacherID),
CourseID char(8) references Course(CourseID),
ClassID char(8) references Class(ClassID),
Semester int not null,
SchoolYear int not null,
ScheduleIdent varchar(40) not null,
Classroom varchar(20) not null
primary key(TeacherID, CourseID, ClassID)
)
/*
非空not null
唯一unique
默认default 默认值
检查check （约束表达式）
外键reference 表名或列名
标示identity （起始值，增量）
*/
==================================================================== ---------------------------------表的修改--------------------------- use test
go
create table goods(
goodsID varchar(10) primary key,
goodsName varchar(15) not null,
price decimal(6,2) check(price > 0),
total int check(total >= 0),
supplier varchar(20)
)
-----------------------用T—SQL语句修改表---------------------------- --格式
alter table 表名
add 列名 数据类型 约束 --添加列
alter column 列名 数据类型 --修改列
drop column 列名 --删除列
--列在userinfo表中添加一列code（邮编）char（）
use xkgl
go
alter table userinfo
add code char(6)
-----------------------查看表属性信息------------------------- sp_help 对象名
sp_help userinfo -----------------------------数据查询----------------------------- --格式
select 列名
from 表名
-- * 代表所有列
--列查询学生表的所有信息
select * from Student
--指定列
--列查询所有学生的姓名与性别,先写哪个列就先显示哪个列
select StudentName, Sex from Student
select Sex, StudentName from Student
--练习查询所有课程的名称与学分
select CourseName, Credit from Course
--运算列
--列把所有成绩+10
select CourseID, StudentID, Grade, Grade+10
from Grade
--用+连接字符串
--列
select StudentName+'您好' +Sex
from Student
--使用系统函数
year() getdate()
--别名
--列名as '列标题' （as关键字可省）
--‘列标题’== 列名
--列
select CourseID '课程号', StudentID as 学号, 成绩=Grade+10
from Grade
--增加说明列
--例
select StudentName, '的性别是：',Sex
from Student
--行的选择
select StudentName, '的性别是：',Sex
from Student
where Sex = '男'
--------------------行的检索----------------------------------------- --比较运算符= > < <>
--查询学生张三的姓名与性别
select StudentName, Sex 性别
from Student
where StudentName = '张三'
--逻辑运算符and or not
--查询年以后出生的男生的信息
select *
from Student
where year(Birth) >= 1992 and Sex = '男'
--查询范围
--列名between 最小值and 最大值
--等价于：列名>= 最小值and 列名<= 最大值
--查询成绩在-80之间的成绩信息
select *
from Grade
where grade between 70 and 80
--查询列表
--列名in(值，值。。。)
--等价于列名= 值or 列名= 值。。。
--查询所有教授与副教授的信息
select *
from Teacher
where Profession in('教授', '副教授')
--字符串模糊匹配% _ [] [^]
--查询所有姓张与姓李的学生信息
select *
from Student
where StudentName like '[张李]%'
--控制判定
--查询所有成绩为null的成绩记录
select *
from grade
where Grade is null
-- distinct 关键字
--从返回的结果数据中删除重复的行(实际不物理删除) --在学生表中查询所有学生所在班级编号，并消除重复记录 select distinct ClassID, StudentName
from Student
--top关键字
--top 你返回前n个数据
--top n percent 返回前百分之n行数据
-- 排序
--order by 列名asc/desc
--asc 是升序可以省略desc是降序
select *
from Student
order by Sex, Birth desc
--------------------数据输入---------------------------------- --格式
insert into 表名(列名....)
values(值....)
--注：列名与值一一对应
--列在Department表中输入dp01计科系。。。
use xkgl
go
select * from Department --查询
insert into
Department(DepartmentID,DepartmentName,DepartmentHeader,TeacherNum) values('DP01','计科系','张三',120)
--等价于
insert Department
values('DP01','计科系','张三',120)
-----在userinfo注册一个用户
insert into userinfo (email,pwd,tel,regdate,uname,question,answer) values('soft678@ccniit.com','987654',null,default,default,default) --如果某列由系统指定数据那么输入时就把那一行当做没有
--练习：输入信管系、外语系、艺术系的信息
insert into
Department(DepartmentID,DepartmentName,DepartmentHeader,TeacherNum) values('DP02','外语系','李四',20)
insert into
Department(DepartmentID,DepartmentName,DepartmentHeader,TeacherNum) values('DP03','艺术系','王五',20)
insert into
Department(DepartmentID,DepartmentName,DepartmentHeader,TeacherNum) values('DP05','信管系','赵六',20)
---------------------------数据删除-------------------------------- --格式
delete 表名
where 条件
--注：列名= < > != like 值
--列删除计科系的信息
delete Department
where DepartmentName = '计科系'
select * from Department
--练习删除教师人数在人以下部门信息
delete Department
where TeacherNum < 50
--练习删除所有系主任性王的
delete Department
where DepartmentHeader like '王%'
select * from Department
-------------------------------修改数据------------------------------ --格式
update 表名
set 列名=值
where 条件
--列将信管系的人数改为人
update Department
set TeacherNum = 103
where DepartmentName = '信管系'
--练将人数大于的部门名改为信管系
update Department
set DepartmentName = '信管系'
where TeacherNum > 100 -------------------------------多表连接--------------------------- --找出公共字段
--格式
select 列名
from 表一
[连接类型] join 表二 on 表一.列名 = 表二.列名
[连接类型] join 表 on 表/2.列名 = 表.列名
......
--[连接类型]
--内连接inner (可省略) 两个表都有的才输出
--左链接left 左边的表有的字段都要输出
--右链接right 右边的表的字段都要输出
--全连接full 只要有的字段都要输出
--查询‘软件技术班’的所有学生信息
select StudentName, Sex, HomeAddr, ClassName, student.ClassID from Student
join class on Student.ClassID = class.ClassID
where ClassName = '09软件技术班'
select * from class
select *
from Student
join class on Student.ClassID = class.ClassID
--查询所有班级的班级号，班级名，学生人数，系别名称
select classid, classname, studentnum, Departmentname
from class c join department d on c.departmentid = d.DepartmentID
--查询所有学生的学号，姓名，课程名称，成绩
select student.StudentID, StudentName, CourseName, Grade
from Student left join Grade on Student.StudentID = Grade.StudentID left join course on Course.CourseID = Grade.CourseID order by StudentID --排序
----查询所有没有选课的学生的学号，姓名
select student.StudentID, StudentName
from Student left join Grade on Student.StudentID = Grade.StudentID left join course on Course.CourseID = Grade.CourseID where Grade.StudentID is null
--查询所有还没有班级的系部名称
select *
from class right join Department on class.DepartmentID =
Department.DepartmentID
where ClassID is null -- 查询什么为空一般查询该表的主键
--查询数据库原理与应用的任课老师是谁
select distinct teachername
from Teacher join Schedule on Teacher.TeacherID = Schedule.TeacherID join Course on Course.CourseID = Schedule.CourseID where CourseName = '数据库原理与应用'
select * from Schedule
select * from Course
select * from Department
--查询所有教师的教师号,教师名,系部名
select TeacherID, TeacherName, DepartmentName
from Teacher left join Department on Teacher.DepartmentID =
Department.DepartmentID
--查询所有老师的授课情况,输出教师名,课程名
select distinct TeacherName, CourseName
from Teacher left join Schedule on Teacher.TeacherID = Schedule.TeacherID left join Course on Course.CourseID = Schedule.CourseID
--可以右击视图选择新建视图,再选择要关联的表 --------------------------------备份和恢复-------------------------- --备份数据库格式
backup database 要备份的数据库名 to disk ='要备份到的路径(要写备份的文件名)'
--列把xkgldata备份到D:/java/Database/backup文件名是xkgldata.bak --备份的文件名可以随便取名,恢复数据库时会重新命名的
backup database xkgldata to disk =
'D:/java/Database/backup/xkgldata.bak'
--恢复数据库格式
restore database 要恢复的数据库名 from disk = '备份数据库的路径'
--有时可能不需要备份整个数据库所以也可以备份表 -------------------------------子查询---------------------------- --格式
select 列名
from 表名
where 列名 in(select 列名 from 表名...)
--把一个查询的结果作为另一个查询的条件
--查询与赵宏同班同学的信息
select *
from Student
where ClassID in(select ClassID
from Student
where StudentName = '张宏')
--查询'09软件技术班'的所有学生的学号,姓名
select StudentID, StudentName
from Student
where ClassID = (select ClassID
from class
where ClassName = '09软件技术班')
--一个可以用"="多个可以用"in",一般都用"in"
--将计算机系的男老师的宿舍改为"东软宿舍栋"
update Teacher
set HomeAddr = '东软宿舍栋'
where Sex = '男' and DepartmentID in(select DepartmentID
from Department
where DepartmentName = '计算机系')
select * from Teacher
--删除信管系的所有学生信息
delete Student
where ClassID in(select ClassID
from class
where DepartmentID in(select DepartmentID
from Department
where DepartmentName = '信管系'))
--查询张宏的所有成绩记录
select *
from Grade
where StudentID in(select StudentID
from Student
where StudentName = '张宏')
-----------------------------在insert中使用子查询---------- --格式
insert into 表名(列名) select语句
--创建stubak 表
create table stubak(
studentID char(12) primary key,
studentName char(8),
sex char(2)
)
--将所有学生的学号,姓名,性别插入到stubak表中
insert into stubak(studentID, studentName, sex)
select studentid, Studentname, sex from Student
select * from stubak
--清空stubak
delete stubak
--例将计算机系的所有学生记录插入到stubak表中(利用的是多表连接)
insert into stubak(studentID, studentName, sex)
select StudentID, StudentName, Sex
from Student join class on student.ClassID = class.ClassID
join Department on Department.DepartmentID =
class.DepartmentID
where DepartmentName = '计算机系'
select * from class
--例将计算机系的所有学生记录插入到stubak表中(利用的是子查询)
insert into stubak(studentID, studentName, sex)
select StudentID, StudentName, Sex
from Student
where ClassID in(select ClassID
from class
where DepartmentID in(select DepartmentID
from Department
where DepartmentName = '计算机系'))
--------------------------select into 语句----------------------- --格式
select 列名 into 新表名
from 表名
--将所有学生的学号,姓名,性别插入到stubak1表中(不用手动建表,功能强大) select StudentID, StudentName, Sex into stubak1
from Student
select * from stubak1
--将所有男生的记录插入到stubak2表中
select * into stubak2
from Student
where Sex = '男'
select * from stubak2
--------------------------联合查询-------------------------
--功能:把两个或多个select语句查询的结果组合成一个结果集
select 语句
union [all]
select 语句
--联合查询student与stubak表
select StudentID, StudentName, sex from Student
union all--不写all则代表去掉重复的行
select * from stubak
-------------------------------在子查询中使用all或any---------------- --如果子查询中返回的是单列多值,则必须在子查询前使用关键字all或any --查询比张宏/姜明凡年龄都大的学生信息
select *
from Student
where Birth <all (select Birth
from Student
where StudentName in('张宏', '姜明凡'))
------------------------复习查询--------------------------------- select [distinct] [top n] 列名 [into 新表名]
from 表 [连接类型] join 表 on 表.列 = 表.列
where 条件
order by 列名 asc/desc -----------------------------------------数据汇总
--------------------------------------
--聚合函数
avg() --平均值
max() --最大值
min() --最小值
sum() --求和
count() --记数
datediff(类型, 日期, 日期) --返回两个日期的差
--查询所有女生的人数,平均年龄
select COUNT(*) 记数, avg(DATEDIFF(YY, Birth, GETDATE()))平均年龄 from Student
where Sex = '女'
select * from Student
--查询张宏的平均成绩,最高成绩,最低成绩,总成绩,修课门数
select AVG(Grade) 平均成绩,
MAX(Grade) 最高成绩,
MIN(Grade) 最低成绩,
SUM(Grade) 总成绩,
COUNT(*) 修课门数
from Grade join Student on Grade.StudentID = Student.StudentID where studentname = '张宏'
--用子查询
select AVG(Grade) 平均成绩,
MAX(Grade) 最高成绩,
MIN(Grade) 最低成绩,
SUM(Grade) 总成绩,
COUNT(*) 修课门数
from Grade
where StudentID in(select StudentID
from Student
where StudentName = '张宏')
-----------------------------------分类汇总
-----------------------------------------------
--功能:将表中的数据分成多个组,每一个组都会返回一个统计值
--注意如果分类汇总有where语句,则选进行where语句再分类汇总
--格式
group by 分组列
having 条件 --对统计结果进行筛选
--查询男女生的人数
select sex, --分组列可以输出
count(*) 人数,
avg(DATEDIFF(YY, Birth, GETDATE()))平均年龄
from Student
group by Sex
--查询每个学生的平均成绩,最高成绩,最低成绩,总成绩,修课门数(组合分组)
select grade.StudentID,
studentName,
sex,
AVG(Grade) 平均成绩,
MAX(Grade) 最高成绩,
MIN(Grade) 最低成绩,
SUM(Grade) 总成绩,
COUNT(*) 修课门数
from Grade join Student on Grade.StudentID = Student.StudentID group by grade.StudentID, StudentName, sex
having AVG(Grade) >= 80 --对统计完成的结果再进行筛选
---------------------------明细汇总
-----------------------------------------
--格式
[order by 分组列] --不是必须的
compute 聚合函数 by 分组列
--注:必须先按分组列排序
--例
select *
from Student
compute count(studentid)
--例:查询男女生人数,输出详细信息与统计结果
select *
from Student
order by sex
compute count(studentid) by sex
--查询每个学生的平均成绩,最高成绩,最低成绩,总成绩,修课门数(组合分组) select grade.StudentID,
studentName,
sex,
AVG(Grade) 平均成绩,
MAX(Grade) 最高成绩,
MIN(Grade) 最低成绩,
SUM(Grade) 总成绩,
COUNT(*) 修课门数
from Grade join Student on Grade.StudentID = Student.StudentID group by grade.StudentID, StudentName, sex
having AVG(Grade) >= 80 --对统计完成的结果再进行筛选
select * from V_jsj
where avggrade < 60
------------------------------创建视图
-------------------------------------------------
create view 视图
as
select 语句
-------------------修改视图---------------------------------- alter view 视图名
as
select 语句
alter view v_student
as
select * from Student
select * from v_student
-------------------------删除视图-------------
drop view 视图名
--使用系统存储过程查看视图信息
sp_help----显示数据库对象的特征
sp_helptext----查看数据库对象在系统表中的定义
sp_depends----显示数据库对象所依赖的关系
sp_help student
sp_helptext v_stu_cur_grad
sp_depends v_stu_cur_grad
--修改v_stu_cur_grad视图,添加性别,出生日期,课程学分
drop view v_stu_cur_grad
SELECT dbo.Student.StudentID AS 学号,
dbo.Student.StudentName AS 姓名,
dbo.Course.CourseName AS 课程名,
dbo.Grade.Grade AS 成绩,
Sex,
Birth,
Credit
FROM dbo.Course INNER JOIN
dbo.Grade ON dbo.Course.CourseID = dbo.Grade.CourseID INNER JOIN
dbo.Student ON dbo.Grade.StudentID =
dbo.Student.StudentID
----------------------------对视图的文本加密------------------ alter view 视图名
with encryption --这里加上这个语句就可以加密
as
select 语句
------------------视图改名----------------------
sp_rename 旧名, 新名 ----------------数据库分页-----------------
select top 每一页的大小 *
from 表名
where id not in(select top (每一页的大小*(页数-1)) id
from 表名
order by id)
order by id
select top 4 * from article
where pid = 0 and id not in(select top (4*1) id
from article
where pid = 0
order by pdata desc)
order by pdata desc
select * from article
where pid = 0
order by pdata desc ------------------数据库对象命名规则-------
服务器名.数据库名.拥有者名.对象名
select * from xkgldata.dbo.student
------------------------变量类型---------------
--全局变量
select @@ERROR--返回最后执行的Transact-SQL 语句的错误代码
select @@CONNECTIONS --连接的次数
select @@IDENTITY --返回最后插入的列值
select @@ROWCOUNT --返回受上一语句影响的行数
--局部变量
--创建局部变量
declare 局部变量名 数据类型[...n]
--局部变量赋值
set 变量名=值
select 变量名=值[,...n]
--查询与张宏同班的学生信息
--声明变量
declare @cid char(10)
--赋值
select @cid = classid from Student where StudentName = '张宏' select * from Student where ClassID = @cid
---------------------函数---------------------------
--数学函数
CEILING (数值) --朝上取整
FLOOR (数值) --朝下取整
ROUND (数值, 保留位数) --将给定的数据四舍五入到给定的位数
PI() --常量3.14159265358979
RAND([seed]) --返回到之间的随机float值(给定了seed就是得到不变的-1的数值)
select PI(), CEILING(PI()),FLOOR(PI()), ROUND(PI(), 2),
ROUND(PI(), 3), ROUND(PI(), -1), ROUND(123456, -2)
select floor(RAND(1)*10000000)
--字符串函数
LTRIM(字符串) --删除字符串前面的空格
RTRIM(字符串) --删除字符串后面的空格
left(字符串, 长度) --返回字符串从左边开始指定个数的字符串
right(字符串, 长度) --返回字符串从右边开始指定个数的字符串
substring(字符串,起始位置, 长度) --返回字符串从指定位置到指定的长度的字符串
select 'a'+RTRIM(LTRIM(' b '))+'c'
---------------------转换函数---------------------------
cast --将一个数据类型强制转换成另一个数据类型
cast(表达式 as 新的数据类型)
--CONVERT 函数允许用户把表达式从一种数据类型转换为另一种数据类型， --并且还在日期的不同显示格式之间进行转换。(一般用在日期转换) --CONVERT（新的数据类型,表达式[,日期显示格式]）
--style参数：提供了各种日期显示格式。
select '123'+2 --一个字符串加一个整型,系统会先把字符串转换成整型 select '123'+CAST(2 as CHAR(1))
select getdate(), LEFT(GETDATE(), 10), CONVERT(char(10), GETDATE(), 20)
--例
select * from Student
where ClassID = 'Cs010902'
select left(StudentID, 11) + cast(RIGHT(studentID, 1)+1 as CHAR(1)), StudentName
from Student
where ClassID = 'Cs010902'
--------------日期函数----------------------
datediff(日期部分, 开始日期, 结束日期)
getdate() --当前系统日期
year(日期)
month(日期)
day(日期)
select DATEDIFF(DD, GETDATE(), '2012-7-18')
-----------------------系统函数-------------------
--COALESCE(参数...)：返回其参数中第一个非空表达式。
--ISNULL(表达式,值)：用指定值替换表达式中的指定空值
select coalesce(null, 456, null, 123, null)
select Studentid, studentname, isnull(homeaddr, '暂无地址')
from student
------------------------用户自定义函数------------------------------ /*1. 创建用户自定义函数的语法形式如下：
CREATE FUNCTION 函数名（形式参数名称AS 数据类型）
RETURNS 返回数据类型
BEGIN
函数内容
RETURN 表达式
END
2. 调用用户自定义函数的基本语法：
变量=用户名.函数名（实际参数列表）
*/
--例创建一个函数IDToName(sid)
create function IDToName(@sid as char(12))
returns char(8)
begin
declare @sname char(8)
select @sname = StudentName from Student where StudentID = @sid return @sname
end
select dbo.IDToName('St0109020001')
select studentid, dbo.IDToName(StudentID), Grade from grade
------------------流程控制--------------------
1.begin ... end
2.if ... else
if (条件)
sql代码
else 代码
sql代码
if (@@ERROR <> 0)
print'出错了'
else
print '成功了'
3.case 语句
case
when 条件 then 结果
...
else 结果
end
select studentId, grade,
case
when grade >= 90 then '优秀'
when grade >= 75 then '良好'
when grade >= 60 then '及格'
else '不及格'
end 等级
from grade
/*
4.WHILE…CONTINUE…BREAK语句
SQL语言中的循环语句，用来重复执行SQL 语句或语句块的 */
--例求*2*3...*10的值
declare @i int, @j int
select @i = 1, @j = 1
while (@i <= 10)
begin
set @j = @j*@i
set @i = @i+1
end
select @j ---------------------------------
--格式
create proc 存储过程名
@参数
as
程序代码
--调用
exec 存储过程名 参数
--删除
drop proc 存储过程名
--例创建一个存储过程返回教师表的所有信息up_select_teacher create proc up_select_teacher
as
select * from Teacher
--调用
exec up_select_teacher
--例创建一个通过教师号查询教师信息的存储过程
create proc up_select_teacher_id
@tid char(8)
as
select * from Teacher where TeacherID = @tid
--调用
exec up_select_teacher_id 'dep01005'
--练习通过学号查询学生详细信息的存储过程
create proc up_select_student_id
@sid char(12)
as
select * from student where StudentID = @sid
exec up_select_student_id '11311110805'
--练习写一个分页的存储过程
create proc up_paging
@
--创建一个输入教师信息的存储过程
create proc up_insert_teacher
@teacherID char(8),
@teacherName char(12),
@sex char(2),
@birth datetime,
@profession char(8),
@telephone varchar(20),
@homeAddr varchar(50),
@departmentID char(4)
as
insert into Teacher
values(@teacherID, @teacherName, @sex, @birth, @profession,@homeAddr, @departmentID)
select * from Teacher
--创建一个修改教师信息的存储过程
create proc up_update_teacher
@teacherID char(8),
@teacherName char(12),
@sex char(2),
@birth date,
@profession char(8),
@telephone varchar(20),
@homeAddr varchar(50),
@departmentID char(4)
as
update Teacher
set TeacherName = @teacherName, @telephone,
Sex = @sex,
Birth = @birth,
Profession = @profession,
Telephone = @telephone,
HomeAddr = @homeAddr,
DepartmentID = @departmentID
where TeacherID = @teacherID
--创建一个修改教师信息的存储过程
create proc up_delete_teacher
@tid char(8)
as
delete teacher where teacherID = @tid ---------------------------------
--格式
create trigger 触发器名
on 表或视图名
触发时机 触发事件
as
程序代码
-------------------------------注意---------------------------------- 触发时机: after/for(后) 在操作完成后触发
instead of(前) 在操作完成前出发,不执行操作本身
触发事件: insert, update, delete
两个特殊表: inserted, deleted
-------------------------------------------------------------------------
--例让student表只读
create trigger tr_student_readonly
on student
instead of insert, update, delete
as
print'student is readonly'
delete student where StudentName = '张宏'
update student set Sex = '女' where StudentName = '张宏'
select * from student
--删除
drop trigger tr_student_readonly
--例创建一个stu表,并让它与student同步
--创建stu表
select * into stu from student
select * from stu
--创建insert触发器
create trigger tr_student_insert
on student
after insert
as
insert into stu select * from inserted
insert into student values('1011', 'jack', '男', '2011-09-09', null, null, null)
--创建delete触发器
create trigger tr_student_delete
on student
after delete
as
delete stu where studentid in(select studentid from deleted )
delete student where StudentID = '101'
--创建update触发器
create trigger tr_student_update
on student
after update
as
delete stu where studentid in(select studentid from deleted)
insert into stu select * from inserted
update student set Sex = '女' where StudentName = '张宏'
update student set ClassID = 'Cs010901' where ClassID is null grant --授予权限
revoke --收回权限
deny --拒绝权限