SQLServlet数据库学习笔记重难点.docx

1、SQLServlet数据库学习笔记重难点创建登录用户(1) 添加windows身份验证登录账户的语法为：EXEC sp_grantlogin windows 域名域账户 实例如下： 创建用户的步骤如下：在我的电脑上 单击右键单击管理出现如下界面：创建用户后就可以执行语句了由于我的电脑上的域名改为了覃成成(2) 添加SQL身份验证登录账户的语法EXEC sq_ADDlogin 账户名，密码实例如下：创建SQL身份验证登录账户用户名为haha,密码为1309014752 (3) 删除SQL身份验证登录账户的语法为：EXEC sq_droplogin账户名删除SQL身份验证登录账户hahaEXEC

2、sp_droplogin haha使用扩展存储过程：xp_cmdshell中启用xp_cmdshell的方法如果为启用xp_cmdshell则会出现如下错误：启用xp_cmdshell的方法如下：-使用事务的好处，实例如下： 如果不使用事务则会出现以下错误： 由于转账过程中，出现了张三余额为0，违反了余额约束在=1,正确的应该是转账不成功，张三和李四的余额仍为以前的余额，张三：1000元，李四1元使用事务： begin transaction -开始事务 declare errorsum int set errorsum=0 -转账：张三的账户少元，李四的账户多元 update bank se

3、t currentMoeny=currentMoeny-1000 where customerName=张三 set errorsum=errorsum+error -错误进行累计,如果errorsu=0就表示没有错误 update bank set currentMoeny=currentMoeny+1000 where customerName=李四 set errorsum=errorsum+error if errorsum0 begin print 交易失败，回滚事务 rollback transaction -当出现错误时,就回滚到开始事务时,数据都恢复到开始事务时 end els

4、e begin print 交易成功，提交事务 commit transaction -当没有错误时,就提交事务,执行SQL语句 end go print 查看转账事务后的余额 select * from bank go执行的结果如下： 如果出现以下错误表示当执行多条语句时，未加上go进行批处理1.使用语句创建数据库，创建主数据文件和日志文件的属性为5个 -主数据（日志）名（逻辑名） -主数据文件存储的位置（物理名） -主数据文件的初始大小 -主数据文件的最大大小 -主数据文件的增长率2.在数据库中打印文字if exists(select * from sysdatabases where n

5、ame=xp9) -该语句的含义为如果在数据库中已经存在xp9的话，则删除数据库xp9 begin drop database xp9 print 删除数据库xp9成功 endgo-其中begin的含义相当于if语句中的，而其中的end的含义相当于if语句中的-要想在数据库中打印出文字则只需要使用print关键字即可3.数据库文件的组成 主数据文件：*.mdf 次要数据文件：*.ndf 日志文件:*.ldf4.数据库其他的属性： 文件存放位置，分配的初始空间，属于哪个文件组 文件增长：可以按百分比或实际大小指定增长速度 文件容量设置：可以指定文件增长的最大值或不受限制5.使用语句创建数据库代码

6、如下： create database xp9 on primary ( name=stuDB_data,-主数据文件名（逻辑名） filename=D:projectstuDB_data.mdf, -主数据文件存储的位置（物理名） size=5mb,-主数据文件的初始大小 maxsize=100, -主数据文件的最大的大小 filegrowth=15% -主数据文件的增长率 ) log on ( name=stuDB_log, -主日志文件名(逻辑名) filename=D:projectstuDB_log.ldf,-主日志文件存储的位置(物理名) size=1mb, -主日志文件的初始大小

7、 maxsize=10mb, -主日志文件的最大的大小 filegrowth=10% -主日志文件的增长率 ) go6.删除数据库 -drop database 数据库名 -实例如下 use master -设置当前数据库为master,以便访问sysdatabases表 go if exists(select * from sysdatabases where name=xp9) -该语句的含义为如果在数据库中已经存在xp9的话，则删除数据库xp9 begin drop database xp9 print 删除数据库xp9成功 -要想在数据库中打印文本，则只需要使用print关键字即可 e

8、ndgo7.创建多日志和多主文件的数据库create database xp9on primary( name=stuDB_data, filename=D:projectstuDB_data.mdf, size=3mb, maxsize=100mb, filegrowth=15%),( name=stuDB_data1, filename=D:projectstuDB_data1.mdf, size=3mb, maxsize=100mb, filegrowth=15%)log on( name=stuDB_log, filename=D:projectstuDB_log.ldf, size=

9、1mb, maxsize=10mb, filegrowth=10%),( name=stuDB_log1, filename=D:projectstuDB_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

10、个字符，如一个汉字则占2个字符，并且数据长度不能超过10字符长度 nchar(10)8.创建表的基本步骤： 确定表中有哪些列 确定每列的数据类型 给表添加各种约束 创建各表之间的关系 具体代码如下： 格式如下： create table 表名 （ 列名1 列的数据类型 是否允许为空， 列名2 列的数据类型 是否允许为空， 列名3 列的数据类型 是否允许为空， ） go create table stuInfo ( stuid int identity(1000,1) not null, -种子标识，从1000开始，增长量为1 stuname varchar(20) not null, stus

11、ex 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 con

12、straint 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 stusex alter table stuinfo -唯一约束 add constraint UQ_stuinfo_stuname unique(stuname

13、) alter table stuinfo add constraint CH_stuinfo_stuemail check(stuemail like %) alter table stymarks add 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

14、）删除约束的语法为： alter table 表名 drop constraint 约束名 （2）实例如下： alter table stuinfo -删除学生的唯一约束 drop constraint UQ_stuinfo_stuname -使用创建唯一约束时的约束名来删除约束11.删除表 （1）语法为：drop table 表名 创建表时首先要判断表是否存在，如果不存在则如果执行创建表语句， 使用实例如下： use stuDB go if exists(select * from sysobjects where name=stuinfo) drop table stuinfo -判断表是

15、否已经存在，如果存在则删除，不存在则创建 create table stuinfo ( )go12.新建一个SQL账户（三道权限） -1.新建登录用户，可以打开SQL数据库，但是不能打开数据库 exec sp_addlogin hxh,12323 -2.新建数据库用户，可以打开指定的数据库 -在数据库xp9中新建数据库xp9用户，xunpo_db_aaa，可以打开xp9数据库，但是不能打开xp9数据库中的表第三章1.使用语句创建数据库，创建主数据文件和日志文件的属性为5个 -主数据（日志）名（逻辑名） -主数据文件存储的位置（物理名） -主数据文件的初始大小 -主数据文件的最大大小 -主数据文

16、件的增长率2.在数据库中打印文字if exists(select * from sysdatabases where name=xp9) -该语句的含义为如果在数据库中已经存在xp9的话，则删除数据库xp9 begin drop database xp9 print 删除数据库xp9成功 endgo-其中begin的含义相当于if语句中的，而其中的end的含义相当于if语句中的-要想在数据库中打印出文字则只需要使用print关键字即可3.局部变量 -局部变量的名称必须以标记作为前缀 -声明局部变量的语句为：declare 变量名 变量类型 如： declare num1 int -局部变量赋值

17、有两种方法： 使用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 -使用s

18、elect赋值 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 当前计算机上每刻度

19、的微秒数 transcount 当前连接打开的事务数 version SQL Server 的版本信息5.输出语句有两种： print 局部变量或字符串 select 局部变量as自定义列名 实例： print 服务器的名称+ servername select servername as 服务器名称 注意print和select的区别 用print方法输出结果将在消息窗口以文本的方式显示，用select方法结果将在网格窗口以表格方式显示6.if-else 条件语句 语法： if(条件) 语句或语句块 else 语句或语句块 语句块使用begin.end表示 if(条件) begin 语句1

20、语句2 end else 使用实例如下： declare avg float select avg=avg(writexam) from stymarks -为声明的变量赋值 if(avg70) begin print 成绩优秀 select top 3 * from stymarks order by writexam desc end else begin print 本班成绩较差 select top 3 * from stymarks order by writexam asc end7.while 循环语句 语法： while(条件) 语句或语句块 break 或 while(条件)

21、 begin 语句1 语句2 break end break表示退出循环，如果有多条语句，才需要begin-end语句块 使用实例如下：-本次考试成绩较差，如果有一个人的笔试没有通过，则所有人的笔试都加2分-方法一：declare minWritExam intselect minWritExam=min(writexam) from stymarkswhile(minWritExam60) begin update stymarks set writexam=writexam+2 -这里必须使用更新 set minWritExam=minWritExam+2 -注意要想对局部变量赋值，只能有

22、两种方法set或select endselect * from stymarks-方法二： -1.找出没有通过的人数 declare sum int select sum=count(writexam) from stumarks where writexam60 -2.如果存在没有通过的人，那么就要提分 declare sum int while(1=1) begin select sum=count(*) from stymarks where writexam0) update stymarks set writexam=writexam+2 else break end8.case多分

23、支语句 语法： case when 条件1 then 结果1 when 条件2 then 结果2 else 其他结果 end实例如下：-case.end 分支语句-使用ABCDE来给机试成绩分等级select *,平均成绩=(writexam+labexam)/2,等级评定= case when (writexam+labexam)/290 then A when (writexam+labexam)/280 then B when (writexam+labexam)/270 then C when (writexam+labexam)/260 then D else E endfrom s

24、tymarks9.批处理语句 -go批处理语句 SQLServer 规定：如果是建库，建表语句，以及存储过程和视图等，必须在语句末尾添加GO批处理标志 -goto跳转 if(21) begin print 第一条语句 lab: -标记 print 第二条语句 goto lab print 第三条语句 end10.retrun :可以在任意位置使用return从语句块或过程中退出，系统将不会执行return语句之后的语句 语法： return 值为整数的表达式11.GOTO语句 GOTO语句可以是程序直接跳到指定的标有标识符的位置处继续执行，而位于GOTO语句和标识符之间的程序将不会被执行。 注

25、意点：GOTO语句和标识符可以用在语句块，批处理和存储过程中，标识符可以为数字与字符的组合，但必须以:结尾，如：al: 在GOTO语句行，标识符后面不用跟：12.waitfor语句 -指定触发语句块，存储过程或事务执行时间，时间间隔或事件 -用来暂时停止程序执行，直到所设定的等待时间过后才继续往下去执行 语法： waitfor delay time|time time 其中，delay 用于指定时间间隔，time用于指定某一时刻，其数据类型为datetime,格式为：hh:mm:ss 具体实例： 例3-58使用WAITFOR TIME语句，以便在晚上10:20执行存储过程 update_all

26、_stats。 程序清单如下： begin waitfor time 22:20 execute update_all_stats end 第四章一、子查询-找出比李四大的学员信息 -1用变量得到结果 use xp9 -找出李四的年龄用变量存起来 declare age int select age=stuAge from student where stuName=李斯文 -使用变量值来查找信息 select * from student where stuAgeage -2使用子查询 select * from student where stuAge(select stuAge from

27、 student where stuName=李斯文)-查询笔试成绩刚好通过60分的学员select * from student-1.使用表连接select student.*,stumarks.* from student inner join stumarks on student.stuNo=stumarks.stuNo where stumarks.LabExam60-2.子查询 -在成绩表中根据成绩查询出学员的学号 -子查询也是内部查询 select stuNo from stumarks where LabExam60 -在学员表中根据查出来的学号查询学员信息 -父查询也是外部查

28、询 select * from student where stuNo in (select stuNo from stumarks where LabExam60) -in 在定值的范围中 - in (定值1,定值2,.)值要完全匹配-查询没有参加考试的学员信息 -1表连接 select student.* from student left outer join stumarks on student.stuNo=stumarks.stuNo where ExamNo is null -2子查询 select student.* from student where student.stuNo not in(select stuNo from stumarks)-exists子查询 -例如数据库的存在检测 if exists (select * from sysDatbases where name=stuDB) drop database stuDB -检测数据库是否存在如果存在则删除，不存在则创建 if exists(子查询) 语句 -如果子查询的结果非空，即记录条数1条以上，则exists（子查询）将返回真（true），否则返回假(false) -exists也可以作为where语句的子查询，但一般都能用in子查询替换-如果笔试有80分以上的，就每人