SQLServlet数据库学习笔记重难点.docx
《SQLServlet数据库学习笔记重难点.docx》由会员分享,可在线阅读,更多相关《SQLServlet数据库学习笔记重难点.docx(55页珍藏版)》请在冰豆网上搜索。
SQLServlet数据库学习笔记重难点
创建登录用户
(1)添加windows身份验证登录账户的语法为:
EXECsp_grantlogin‘windows域名\域账户’
实例如下:
创建用户的步骤如下:
在我的电脑上单击右键—>单击管理—>出现如下界面:
创建用户后就可以执行语句了
由于我的电脑上的域名改为了‘覃成成’
(2)添加SQL身份验证登录账户的语法
EXECsq_ADDlogin‘账户名’,‘密码’
实例如下:
创建SQL身份验证登录账户用户名为‘haha’,密码为’1309014752’
(3)删除SQL身份验证登录账户的语法为:
EXECsq_droplogin’账户名’
删除SQL身份验证登录账户‘haha’
EXECsp_droplogin'haha'
使用扩展存储过程:
xp_cmdshell中启用xp_cmdshell的方法
如果为启用xp_cmdshell则会出现如下错误:
启用xp_cmdshell的方法如下:
--使用事务的好处,实例如下:
如果不使用事务则会出现以下错误:
由于转账过程中,出现了张三余额为0,违反了余额约束在>=1,正确的应该是转账不成功,张三和李四的余额仍为以前的余额,张三:
1000元,李四1元
使用事务:
begintransaction--开始事务
declare@errorsumint
set@errorsum=0
--转账:
张三的账户少元,李四的账户多元
updatebanksetcurrentMoeny=currentMoeny-1000
wherecustomerName='张三'
set@errorsum=@errorsum+@@error--错误进行累计,如果@errorsu=0就表示没有错误
updatebanksetcurrentMoeny=currentMoeny+1000
wherecustomerName='李四'
set@errorsum=@errorsum+@@error
if@errorsum<>0
begin
print'交易失败,回滚事务'
rollbacktransaction--当出现错误时,就回滚到开始事务时,数据都恢复到开始事务时
end
else
begin
print'交易成功,提交事务'
committransaction--当没有错误时,就提交事务,执行SQL语句
end
go
print'查看转账事务后的余额'
select*frombank
go
执行的结果如下:
如果出现以下错误表示当执行多条语句时,未加上go进行批处理
1.使用语句创建数据库,创建主数据文件和日志文件的属性为5个
--主数据(日志)名(逻辑名)
--主数据文件存储的位置(物理名)
--主数据文件的初始大小
--主数据文件的最大大小
--主数据文件的增长率
2.在数据库中打印文字
ifexists(select*fromsysdatabaseswherename='xp9')--该语句的含义为如果在数据库中已经存在xp9的话,则删除数据库xp9
begin
dropdatabasexp9
print'删除数据库xp9成功'
end
go
--其中begin的含义相当于if语句中的‘{’,而其中的end的含义相当于if语句中的‘}’
--要想在数据库中打印出文字则只需要使用print关键字即可
3.数据库文件的组成
主数据文件:
*.mdf
次要数据文件:
*.ndf
日志文件:
*.ldf
4.数据库其他的属性:
文件存放位置,分配的初始空间,属于哪个文件组
文件增长:
可以按百分比或实际大小指定增长速度
文件容量设置:
可以指定文件增长的最大值或不受限制
5.使用语句创建数据库代码如下:
createdatabasexp9
onprimary
(
name='stuDB_data',--主数据文件名(逻辑名)
filename='D:
\project\stuDB_data.mdf',--主数据文件存储的位置(物理名)
size=5mb,--主数据文件的初始大小
maxsize=100,--主数据文件的最大的大小
filegrowth=15%--主数据文件的增长率
)
logon
(
name='stuDB_log',--主日志文件名(逻辑名)
filename='D:
\project\stuDB_log.ldf',--主日志文件存储的位置(物理名)
size=1mb,--主日志文件的初始大小
maxsize=10mb,--主日志文件的最大的大小
filegrowth=10%--主日志文件的增长率
)
go
6.删除数据库
--dropdatabase数据库名
--实例如下
usemaster--设置当前数据库为master,以便访问sysdatabases表
go
ifexists(select*fromsysdatabaseswherename='xp9')--该语句的含义为如果在数据库中已经存在xp9的话,则删除数据库xp9
begin
dropdatabasexp9
print'删除数据库xp9成功'--要想在数据库中打印文本,则只需要使用print关键字即可
end
go
7.创建多日志和多主文件的数据库
createdatabasexp9
onprimary
(
name='stuDB_data',
filename='D:
\project\stuDB_data.mdf',
size=3mb,
maxsize=100mb,
filegrowth=15%
),
(
name='stuDB_data1',
filename='D:
\project\stuDB_data1.mdf',
size=3mb,
maxsize=100mb,
filegrowth=15%
)
logon
(
name='stuDB_log',
filename='D:
\project\stuDB_log.ldf',
size=1mb,
maxsize=10mb,
filegrowth=10%
),
(
name='stuDB_log1',
filename='D:
\project\stuDB_log1.ldf',
size=1mb,
maxsize=10mb,
filegrowth=10%
)
--注意点:
创建多主文件的方法就是在主文件的后面加上一个逗号
8.char(6),varchar(10),nchar(10)三则之间的区别
char(6)固定长度即长度为6个字符,不管该数据是否有6个字符,都是占6个字符,如一个汉字实际占2个字符,但是使用char(6)类型修饰后,则该汉字占6个字符
varchar(10)可变长度,即最大长度为10个字符,如一个汉字则占2个字符,并且数据长度不能超过10字符长度
nchar(10)
8.创建表的基本步骤:
确定表中有哪些列
确定每列的数据类型
给表添加各种约束
创建各表之间的关系
具体代码如下:
格式如下:
createtable表名
(
列名1列的数据类型是否允许为空,
列名2列的数据类型是否允许为空,
列名3列的数据类型是否允许为空,
)
go
createtablestuInfo
(
stuidintidentity(1000,1)notnull,--种子标识,从1000开始,增长量为1
stunamevarchar(20)notnull,
stusexnchar
(1)notnull,
stuageintnotnull,
stuemailvarchar(50),
stutelvarchar(20),
stuaddressvarchar(100)
)
go
9.为表添加约束
(1)添加约束的语法为:
altertable表名
addconstraint约束名约束类型具体的约束说明
(2)具体的实例如下:
altertablestuinfo--主键约束
addconstraintPK_stuinfo_stuidprimarykey(stuid)
altertablestuinfo--检查约束
addconstraintCH_stuinfo_stusexcheck(stusex='男'orstusex='女')
altertablestuinfo--检查约束
addconstraintCH_stuinfo_stuagecheck(stuagebetween18and60)
altertablestuinfo--默认约束
addconstraintDF_stuinfo_stusexdefault('男')forstusex
altertablestuinfo--唯一约束
addconstraintUQ_stuinfo_stunameunique(stuname)
altertablestuinfo
addconstraintCH_stuinfo_stuemailcheck(stuemaillike'%@%')
altertablestymarks
addconstraintCH_stymarks_labexamcheck(labexambetween0and100)
添加外键约束:
`altertablestymarks--外键约束
addconstraintFK_stuinfo_stymarks_stuidforeignkey(stuid)referencesstuinfo(stuid)
10.删除约束:
(1)删除约束的语法为:
altertable表名
dropconstraint约束名
(2)实例如下:
altertablestuinfo--删除学生的唯一约束
dropconstraintUQ_stuinfo_stuname--使用创建唯一约束时的约束名来删除约束
11.删除表
(1)语法为:
droptable表名
创建表时首先要判断表是否存在,如果不存在则如果执行创建表语句,
使用实例如下:
usestuDB
go
ifexists(select*fromsysobjectswherename='stuinfo')
droptablestuinfo--判断表是否已经存在,如果存在则删除,不存在则创建
createtablestuinfo
(
)go
12.新建一个SQL账户(三道权限)
--1.新建登录用户,可以打开SQL数据库,但是不能打开数据库
execsp_addlogin'hxh','12323'
--2.新建数据库用户,可以打开指定的数据库
--在数据库xp9中新建数据库xp9用户,xunpo_db_aaa,可以打开xp9数据库,但是不能打开xp9数据库中的表
第三章
1.使用语句创建数据库,创建主数据文件和日志文件的属性为5个
--主数据(日志)名(逻辑名)
--主数据文件存储的位置(物理名)
--主数据文件的初始大小
--主数据文件的最大大小
--主数据文件的增长率
2.在数据库中打印文字
ifexists(select*fromsysdatabaseswherename='xp9')--该语句的含义为如果在数据库中已经存在xp9的话,则删除数据库xp9
begin
dropdatabasexp9
print'删除数据库xp9成功'
end
go
--其中begin的含义相当于if语句中的‘{’,而其中的end的含义相当于if语句中的‘}’
--要想在数据库中打印出文字则只需要使用print关键字即可
3.局部变量
--局部变量的名称必须以标记@作为前缀
--声明局部变量的语句为:
declare@变量名变量类型
如:
declare@num1int
--局部变量赋值有两种方法:
使用set语句或select语句
如:
set@varible_name=value或select@variable_name=value
综合实例:
根据座位号查找李文才的左右同桌
/*--查找李文才的信息--*/
declare@namevarchar(8)--学员姓名
set@name='李文才'--使用set赋值
set*fromstuinfowherestuname=@name
/*--查找李文才的左右同桌--*/
declare@seatint--座位号
select@seat=stuseatfromstuinfowherestuname=@name--使用select赋值
select*fromstuinfowhere(stuseat=@seat+1)or(stuseat=@seat-1)
go
4.全局变量
SQLServer中的所有全局变量都使用两个@标志作为前缀
常用的全局变量
@@error最后一个T-SQL错误的错误号
@@identity最后一次插入的标识值
@@language当前使用的语言的名称
@@max_connections可以创建的同时连接的最大数目
@@rowcount受上一个SQL语句影响的行数
@@servername本地服务器的名称
@@servicename该计算机上的SQL服务的名称
@@timeticks当前计算机上每刻度的微秒数
@@transcount当前连接打开的事务数
@@versionSQLServer的版本信息
5.输出语句有两种:
print局部变量或字符串
select局部变量as自定义列名
实例:
print'服务器的名称'+@@servername
select@@servernameas'服务器名称'
注意print和select的区别
用print方法输出结果将在消息窗口以文本的方式显示,用select方法结果将在网格窗口以表格方式显示
6.if-else条件语句
语法:
if(条件)
语句或语句块
else
语句或语句块
语句块使用begin...end表示
if(条件)
begin
语句1
语句2
end
else
使用实例如下:
declare@avgfloat
select@avg=avg(writexam)fromstymarks--为声明的变量赋值
if(@avg>70)
begin
print'成绩优秀'
selecttop3*fromstymarksorderbywritexamdesc
end
else
begin
print'本班成绩较差'
selecttop3*fromstymarksorderbywritexamasc
end
7.while循环语句
语法:
while(条件)
语句或语句块
break
或
while(条件)
begin
语句1
语句2
break
end
break表示退出循环,如果有多条语句,才需要begin-end语句块
使用实例如下:
--本次考试成绩较差,如果有一个人的笔试没有通过,则所有人的笔试都加2分
--方法一:
declare@minWritExamint
select@minWritExam=min(writexam)fromstymarks
while(@minWritExam<60)
begin
updatestymarkssetwritexam=writexam+2--这里必须使用更新
set@minWritExam=@minWritExam+2--注意要想对局部变量赋值,只能有两种方法set或select
end
select*fromstymarks
--方法二:
--1.找出没有通过的人数
declare@sumint
select@sum=count(writexam)fromstumarkswherewritexam<60
--2.如果存在没有通过的人,那么就要提分
declare@sumint
while(1=1)
begin
select@sum=count(*)fromstymarkswherewritexam<60
if(@sum>0)
updatestymarkssetwritexam=writexam+2
else
break
end
8.case多分支语句
语法:
case
when条件1then结果1
when条件2then结果2
else其他结果
end
实例如下:
--case...end分支语句
--使用ABCDE来给机试成绩分等级
select*,平均成绩=(writexam+labexam)/2,等级评定=
case
when(writexam+labexam)/2>90then'A'
when(writexam+labexam)/2>80then'B'
when(writexam+labexam)/2>70then'C'
when(writexam+labexam)/2>60then'D'
else'E'
end
fromstymarks
9.批处理语句
--go批处理语句
SQLServer规定:
如果是建库,建表语句,以及存储过程和视图等,必须在语句末尾添加‘GO'
批处理标志
--goto跳转
if(2>1)
begin
print'第一条语句'
lab:
--标记
print'第二条语句'
gotolab
print'第三条语句'
end
10.retrun:
可以在任意位置使用return从语句块或过程中退出,系统将不会执行return语句之后的语句
语法:
return[值为整数的表达式]
11.GOTO语句
GOTO语句可以是程序直接跳到指定的标有标识符的位置处继续执行,而位于GOTO语句和标识符之间的程序将不会被执行。
注意点:
GOTO语句和标识符可以用在语句块,批处理和存储过程中,标识符可以为数字与字符的组合,但必须以':
'结尾,如:
‘al:
’在GOTO语句行,标识符后面不用跟‘:
’
12.waitfor语句
--指定触发语句块,存储过程或事务执行时间,时间间隔或事件
--用来暂时停止程序执行,直到所设定的等待时间过后才继续往下去执行
语法:
waitfor{delay'time'|time'time'}
其中,delay用于指定时间间隔,time用于指定某一时刻,其数据类型为datetime,格式为:
‘hh:
mm:
ss’
具体实例:
例3-58 使用WAITFORTIME语句,以便在晚上10:
20执行存储过程update_all_stats。
程序清单如下:
begin
waitfortime'22:
20'
executeupdate_all_stats
end
第四章
一、子查询
--找出比李四大的学员信息
--1>用变量得到结果
usexp9
--找出李四的年龄用变量存起来
declare@ageint
select@age=stuAgefromstudentwherestuName='李斯文'
--使用变量值来查找信息
select*fromstudentwherestuAge>@age
--2>使用子查询
select*fromstudentwherestuAge>(selectstuAgefromstudentwherestuName='李斯文')
--查询笔试成绩刚好通过60分的学员
select*fromstudent
--1.使用表连接
selectstudent.*,stumarks.*fromstudentinnerjoinstumarks
onstudent.stuNo=stumarks.stuNo
wherestumarks.LabExam>60
--2.子查询
--在成绩表中根据成绩查询出学员的学号--子查询也是内部查询
selectstuNofromstumarkswhereLabExam>60
--在学员表中根据查出来的学号查询学员信息--父查询也是外部查询
select*fromstudentwherestuNo
in(selectstuNofromstumarkswhereLabExam>60)
--in在定值的范围中
--in(定值1,定值2,...)值要完全匹配
--查询没有参加考试的学员信息
--1>表连接
selectstudent.*fromstudentleftouterjoinstumarks
onstudent.stuNo=stumarks.stuNo
whereExamNoisnull
--2>子查询
selectstudent.*fromstudentwherestudent.stuNo
notin(selectstuNofromstumarks)
--exists子查询
--例如数据库的存在检测
ifexists(select*fromsysDatbaseswherename='stuDB')
dropdatabasestuDB
--检测数据库是否存在如果存在则删除,不存在则创建
ifexists(子查询)
语句
--如果子查询的结果非空,即记录条数1条以上,则exists(子查询)将返回真(true),否则返回假(false)
--exists也可以作为where语句的子查询,但一般都能用in子查询替换
--如果笔试有80分以上的,就每人