SQLServlet数据库学习笔记(重难点)

创建登录用户(1)添加windows身份验证登录账户的语法为：EXEC sp_grantlogin ‘windows 域名\域账户’实例如下：创建用户的步骤如下：在我的电脑上单击右键—>单击管理—>出现如下界面：创建用户后就可以执行语句了由于我的电脑上的域名改为了‘覃成成’(2)添加SQL身份验证登录账户的语法EXEC sq_ADDlogin ‘账户名’，‘密码’实例如下：创建SQL身份验证登录账户用户名为‘haha’,密码为’1309014752’(3)删除SQL身份验证登录账户的语法为：EXEC sq_droplogin’账户名’删除SQL身份验证登录账户‘haha’EXEC sp_droplogin'haha'使用扩展存储过程：xp_cmdshell中启用xp_cmdshell的方法如果为启用xp_cmdshell则会出现如下错误：启用xp_cmdshell的方法如下：如果不使用事务则会出现以下错误：由于转账过程中，出现了张三余额为0，违反了余额约束在>=1,正确的应该是转账不成功，张三和李四的余额仍为以前的余额，张三：1000元，李四1元使用事务：begin transaction --开始事务declare @errorsum intset @errorsum=0--转账：张三的账户少元，李四的账户多元update bank set currentMoeny=currentMoeny-1000where customerName='张三'set @errorsum=@errorsum+@@error--错误进行累计,如果@errorsu=0就表示没有错误update bank set currentMoeny=currentMoeny+1000where customerName='李四'set @errorsum=@errorsum+@@errorif @errorsum<>0beginprint'交易失败，回滚事务'rollback transaction--当出现错误时,就回滚到开始事务时,数据都恢复到开始事务时endelsebeginprint'交易成功，提交事务'commit transaction--当没有错误时,就提交事务,执行SQL语句endgoprint'查看转账事务后的余额'select*from bankgo执行的结果如下：如果出现以下错误表示当执行多条语句时，未加上go进行批处理1.使用语句创建数据库，创建主数据文件和日志文件的属性为5个--主数据（日志）名（逻辑名）--主数据文件存储的位置（物理名）--主数据文件的初始大小--主数据文件的最大大小--主数据文件的增长率2.在数据库中打印文字if exists(select * from sysdatabases where name='xp9') --该语句的含义为如果在数据库中已经存在xp9的话，则删除数据库xp9begindrop database xp9print '删除数据库xp9成功'endgo--其中begin的含义相当于if语句中的‘{’，而其中的end的含义相当于if语句中的‘}’--要想在数据库中打印出文字则只需要使用print关键字即可3.数据库文件的组成主数据文件：*.mdf次要数据文件：*.ndf日志文件:*.ldf4.数据库其他的属性：文件存放位置，分配的初始空间，属于哪个文件组文件增长：可以按百分比或实际大小指定增长速度文件容量设置：可以指定文件增长的最大值或不受限制5.使用语句创建数据库代码如下：create database xp9on primary(name='stuDB_data',--主数据文件名（逻辑名）filename='D:\project\stuDB_data.mdf', --主数据文件存储的位置（物理名）size=5mb,--主数据文件的初始大小maxsize=100, --主数据文件的最大的大小filegrowth=15% --主数据文件的增长率)log on(name='stuDB_log', --主日志文件名(逻辑名)filename='D:\project\stuDB_log.ldf',--主日志文件存储的位置(物理名)size=1mb, --主日志文件的初始大小maxsize=10mb, --主日志文件的最大的大小filegrowth=10% --主日志文件的增长率)go6.删除数据库--drop database 数据库名--实例如下use master --设置当前数据库为master,以便访问sysdatabases表goif exists(select * from sysdatabases where name='xp9') --该语句的含义为如果在数据库中已经存在xp9的话，则删除数据库xp9begindrop database xp9print '删除数据库xp9成功' --要想在数据库中打印文本，则只需要使用print关键字即可endgo7.创建多日志和多主文件的数据库create database xp9on primary(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%)log on(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.创建表的基本步骤：确定表中有哪些列确定每列的数据类型给表添加各种约束创建各表之间的关系具体代码如下：格式如下：create table 表名（列名1 列的数据类型是否允许为空，列名2 列的数据类型是否允许为空，列名3 列的数据类型是否允许为空，）gocreate table stuInfo(stuid int identity(1000,1) not null, --种子标识，从1000开始，增长量为1 stuname varchar(20) not null,stusex nchar(1) not null,stuage int not null,stuemail varchar(50),stutel varchar(20),stuaddress varchar(100))go9.为表添加约束（1）添加约束的语法为：alter table 表名add constraint 约束名约束类型具体的约束说明(2)具体的实例如下：alter table stuinfo --主键约束add constraint PK_stuinfo_stuid primary key(stuid)alter table stuinfo --检查约束add constraint CH_stuinfo_stusex check(stusex='男' or stusex='女')alter table stuinfo --检查约束add constraint CH_stuinfo_stuage check(stuage between 18 and 60)alter table stuinfo --默认约束add constraint DF_stuinfo_stusex default('男') for stusexalter table stuinfo --唯一约束add constraint UQ_stuinfo_stuname unique(stuname)alter table stuinfoadd constraint CH_stuinfo_stuemail check(stuemail like '%@%')alter table stymarksadd constraint CH_stymarks_labexam check (labexam between 0 and 100)添加外键约束：`alter table stymarks --外键约束add constraint FK_stuinfo_stymarks_stuid foreign key(stuid) references stuinfo(stuid)10.删除约束：（1）删除约束的语法为：alter table 表名drop constraint 约束名（2）实例如下：alter table stuinfo --删除学生的唯一约束drop constraint UQ_stuinfo_stuname --使用创建唯一约束时的约束名来删除约束11.删除表（1）语法为：drop table 表名创建表时首先要判断表是否存在，如果不存在则如果执行创建表语句，使用实例如下：use stuDBgoif exists(select * from sysobjects where name='stuinfo')drop table stuinfo --判断表是否已经存在，如果存在则删除，不存在则创建create table stuinfo()go12.新建一个SQL账户（三道权限）--1.新建登录用户，可以打开SQL数据库，但是不能打开数据库exec sp_addlogin 'hxh','12323'--2.新建数据库用户，可以打开指定的数据库--在数据库xp9中新建数据库xp9用户，xunpo_db_aaa，可以打开xp9数据库，但是不能打开xp9数据库中的表第三章1.使用语句创建数据库，创建主数据文件和日志文件的属性为5个--主数据（日志）名（逻辑名）--主数据文件存储的位置（物理名）--主数据文件的初始大小--主数据文件的最大大小--主数据文件的增长率2.在数据库中打印文字if exists(select * from sysdatabases where name='xp9') --该语句的含义为如果在数据库中已经存在xp9的话，则删除数据库xp9begindrop database xp9print '删除数据库xp9成功'endgo--其中begin的含义相当于if语句中的‘{’，而其中的end的含义相当于if语句中的‘}’--要想在数据库中打印出文字则只需要使用print关键字即可3.局部变量--局部变量的名称必须以标记@作为前缀--声明局部变量的语句为：declare @变量名变量类型如：declare @num1 int--局部变量赋值有两种方法：使用set语句或select语句如：set @varible_name=value 或select @variable_name=value综合实例：根据座位号查找李文才的左右同桌/*--查找李文才的信息--*/declare @name varchar(8) --学员姓名set @name='李文才' --使用set赋值set * from stuinfo where stuname=@name/*--查找李文才的左右同桌--*/declare @seat int --座位号select @seat=stuseat from stuinfo where stuname=@name --使用select赋值select * from stuinfo where(stuseat=@seat+1)or（stuseat=@seat-1）go4.全局变量SQL Server中的所有全局变量都使用两个@标志作为前缀常用的全局变量@@error 最后一个T-SQL错误的错误号@@identity 最后一次插入的标识值@@language 当前使用的语言的名称@@max_connections 可以创建的同时连接的最大数目@@rowcount 受上一个SQL语句影响的行数@@servername 本地服务器的名称@@servicename 该计算机上的SQL服务的名称@@timeticks 当前计算机上每刻度的微秒数@@transcount 当前连接打开的事务数@@version SQL Server 的版本信息5.输出语句有两种：print 局部变量或字符串select 局部变量as自定义列名实例：print '服务器的名称'+ @@servernameselect @@servername as '服务器名称'注意print和select的区别用print方法输出结果将在消息窗口以文本的方式显示，用select方法结果将在网格窗口以表格方式显示6.if-else 条件语句语法：if(条件)语句或语句块else语句或语句块语句块使用begin...end表示if(条件)begin语句1语句2endelse使用实例如下：declare @avg floatselect @avg=avg(writexam) from stymarks --为声明的变量赋值if(@avg>70)beginprint '成绩优秀'select top 3 * from stymarks order by writexam descendelsebeginprint '本班成绩较差'select top 3 * from stymarks order by writexam ascend7.while 循环语句语法：while(条件)语句或语句块break或while(条件)begin语句1语句2breakendbreak表示退出循环，如果有多条语句，才需要begin-end语句块使用实例如下：--本次考试成绩较差，如果有一个人的笔试没有通过，则所有人的笔试都加2分--方法一：declare @minWritExam intselect @minWritExam=min(writexam) from stymarkswhile(@minWritExam<60)beginupdate stymarks set writexam=writexam+2 --这里必须使用更新set @minWritExam=@minWritExam+2 --注意要想对局部变量赋值，只能有两种方法set或selectendselect * from stymarks--方法二：--1.找出没有通过的人数declare @sum intselect @sum=count(writexam) from stumarks where writexam<60--2.如果存在没有通过的人，那么就要提分declare @sum intwhile(1=1)beginselect @sum=count(*) from stymarks where writexam<60if(@sum>0)update stymarks set writexam=writexam+2elsebreakend8.case多分支语句语法：casewhen 条件1 then 结果1when 条件2 then 结果2else 其他结果end实例如下：--case...end 分支语句--使用ABCDE来给机试成绩分等级select *,平均成绩=(writexam+labexam)/2,等级评定=casewhen (writexam+labexam)/2>90 then 'A'when (writexam+labexam)/2>80 then 'B'when (writexam+labexam)/2>70 then 'C'when (writexam+labexam)/2>60 then 'D'else 'E'endfrom stymarks9.批处理语句--go批处理语句SQLServer 规定：如果是建库，建表语句，以及存储过程和视图等，必须在语句末尾添加‘GO'批处理标志--goto跳转if(2>1)beginprint '第一条语句'lab: --标记print '第二条语句'goto labprint '第三条语句'end10.retrun :可以在任意位置使用return从语句块或过程中退出，系统将不会执行return语句之后的语句语法：return [值为整数的表达式]11.GOTO语句GOTO语句可以是程序直接跳到指定的标有标识符的位置处继续执行，而位于GOTO语句和标识符之间的程序将不会被执行。