来自 数据库 2019-10-23 03:34 的文章
当前位置: 网上澳门金莎娱乐 > 数据库 > 正文

关于SQL SERVER数据库学习总结

4、打开“sql server profiler ”工具进行跟踪,如下图所示:

use master
GO
if exists(select * from sysdatabases where name='Wages')
DROP database Wages
CREATE DATABASE Wages
 ON
 (
  NAME='Wages_data',
  FILENAME='e:projectWages_data.mdf',
  SIZE=5mb,
  FILEGROWTH=15%
 )
 LOG ON
 (
  NAME= 'Wages_log',
  FILENAME='e:projectWages_log.ldf',
  SIZE=3mb,
  FILEGROWTH=15%
 )
GO

 图片 1

 

图片 2

 

图片 3

--赋值:来自于表内 select @sex=stuSex,@age=stuAge from stuinfo  where stuName=@name

图片 4

As

2、创建视图,更改列的表头

create view V_Tstudent1

as

select StudentID  学号,Sname姓名,sex  性别,cardID  身份证号码,Birthday  生日,Class  班级 from dbo.TStudent

 

select * from V_Tstudent1

 

以后再去查询的时候就非常方便了。

 

实验案例四:存储过程

1、常用的系统存储过程

exec sp_databases      --列出当前系统中的数据库

exec sp_renamedb  'mybank','bank'   --改变数据库名称(单用户访问)

use MySchool

go

exec sp_tables                       --当前数据库中可查询对象的列表

exec sp_columns student            --查看表student中列的信息

exec sp_help student               --查看表student的所有信息

exec sp_helpconstraint student       --查看表student表的约束

exec sp_helptext view_student_result   --查看视图的语句文本

exec sp_stored_procedures      --返回当前数据库中的存储过程列表

 

 

2、常用的扩展存储过程(在C盘下创建一个文件夹bank)

exec xp_cmdshell  'mkdir  c:bank',no_output  --创建文件夹c:bank

exec xp_cmdshell  'dir c:bank'               --查看文件

如果执行不了上面的语句,请开启下面的功能。然后再次执行上面的两条语句。

 

若xp_cmdshell作为服务器安全配置的一部分而被关闭,请使用如下语句启用:

exec sp_configure  'show advanced options', 1   --显示高级配置选项(单引号中的只能一个空格隔开)

go

reconfigure                                    --重新配置

go

exec sp_configure  'xp_cmdshell',1                 --打开xp_cmdshell选项

go

reconfigure                                    --重新配置

go

 

 

3、用户自定义的存储过程(以schoolDB数据库为例,计算网络管理专业的平均分)

use schoolDB

go

if exists  (select *  from  sysobjects where name='usp_getaverageresult')

drop  procedure  usp_getaverageresult

go

create  procedure  usp_getaverageresult

as

declare  @subjectid  nvarchar(4)

select @subjectid=subjectid  from  dbo.TSubject where subJectName='网络管理'

declare  @avg decimal (18,2)

select  @avg=AVG(mark) from  dbo.TScore wheresubJectID=@subjectid

print '网络管理专业平均分是:'+convert(varchar(5),@avg)

go

exec usp_getaverageresult

 

实验案例五:触发器

 

(Myschool数据库为例)

创建触发器(禁止修改admin表中数据):

create trigger  reminder

on  admin

for update

as

print '禁止修改,请联系DBA'

rollback transaction

go

 

执行语句,查看错误信息:

update Admin set  LoginPwd='123'  where LoginId='benet'

select *  from  Admin

 

实验案例六:创建触发器

(Myschool数据库为例)

要求:创建一个触发器,以确保student表中的数据不会被删除。

create  trigger stu_del

on  student

for  delete

as

print   '你不具备删除管理员信息的权限'

rollback  transaction

go

 

执行一条delete语句,测试结果。

delete   from  Student where  StudentName='喜洋洋

 

文章参考微信公众号:L宝宝聊IT

--2-- 计算1-100的累加和,结果格式:1-100的累加和为:xxx declare  @i int=1, @sum int=0 while(@i<=100) begin set @sum+=@i set @i+=1 end print '1-100的累加和为:'+convert(varchar,@sum)

2、创建组合索引

为成绩表创建组合索引,因为一个学生不能为一门学科录入两次成绩,所以将成绩表中的studentID和subjectID创建组合索引

 图片 5

图片 6

图片 7

图片 8

解决办法:

菜单栏----工具----选项

找到设计器(designers),将标记处的勾去掉,单击“确定”

 图片 9

这样组合索引就创建成功了。

 图片 10

 

图片 11

create 索引类型 index 索引名

1、首先创建一个数据量大的表,名称为“学生表”,分别有三列,学号,姓名和班级,如下图所示,学号为自动编号,班级为默认值“一班”。

  1. 数据缓存:执行个查询语句,Sql Server会将相关的数据页(Sql Server操作的数据都是以页为单位的)加载到内存中来,下一次如果再次请求此页的数据的时候,就无需读取磁盘了,大大提高了速度。

发现总计时间为1毫秒,几乎忽略不计

  设计数据库步骤:需求分析阶段,概要设计阶段,详细设计阶段,

1、创建聚集索引

赋值方式二:用select 输出方式二:用select --

 图片 12

2.外键约束   为学生表添加一个clsid列来表示其班级   alter table StuInfos add clsid int   外键约束   

6、按照“数据库引擎优化顾问”的索引建议建立聚集索引,并且选择“唯一”

SQL语句

 图片 13

2.创建带入参数的存储过程

图片 14

  4、 带通配符参数存储过程

 

赋值方式一:用set 输出方式一:用print   declare @temp int --声明   

 

三 用户自定义的存储过程

上面语句是一个死循环,除非强制结束,如果1大于0就会一直向表中插入姓名

   我们首先学习数据库设计的一些知识点和用SQL语句建库。

5、打开“数据库引擎优化顾问”,添加跟踪文件,进行分析,发现索引建议,需要建立索引。

 事物视图索引

7、再次执行Select * from 学生表Where 学号=900000

 

图片 15

然后我们学习了变量,变量分全局变量和局部变量。

3、创建唯一索引

创建唯一性约束的时候就会创建唯一性索引,不能有重复值

为Tstudent表创建唯一非聚集索引

create unique nonclustered index U_cardID on TStudent(cardID)

图片 16

--输出 print '姓名:'+@name print '性别:'+@sex print '年龄:'+cast(@age as varchar)

为了下面分析文件更准确,多执行几次Select* from 学生表 Where 学号=900000

 1. 查询SQL语句,如: select * from 表名  

打开“sql server profiler ”工具查看跟踪的信息,发现查询时间很长,cpu工作了265毫秒,reads:读了8649次,writes:写了10次,duration:总计花费2336毫秒完成查询。

select @变量名=value

4、创建非聚集索引---可以有重复值

为Tstudent表的姓名列创建非聚集索引

图片 17

使用命令查看表上的索引

Select * from sys.sysindexes where id=(select object_id from sys.all_objects where name='Tstudent')

Indid中1代表聚集索引

Indid中2代表唯一非聚集索引

Indidz中3代表非聚集索引

图片 18

使用sp_help  Tstudent也可以查看到相关表的信息

图片 19

实验案例三:创建视图

方法一:在图形界面下创建视图(以Myschool数据库为例)

创建一个视图,分别来自三个的表的三个列,并重命名列,生成的视图名为student_info,如下图所示:

 图片 20

通过查询语句查看视图:select   *    from  student_info

 

方法二:使用语句创建视图(以schoolDB数据库为例)

进行数据库设计的时候,一个表有很多列,我们可以在表上创建视图,只显示指定的列。

Select语句可以作为一个视图

select Sname,sex,Class from dbo.TStudent where Class='网络班'

-- 语法:  case(数据 as 类型)

1、创建视图,筛选行和列

create view netstudent

as

select Sname,sex,Class from dbo.TStudent where Class='网络班'

从视图中查找数据:

select * from netstudent where sex='男'

创建视图,更改列的表头,计算列,产生计算列

selectStudentID,Sname,sex,cardID,Birthday,Email,Class

from dbo.TStudent

图片 21

区别是select可一次对多个变量赋值,set只能一次对一个变量赋值。

如下图所示:

2-- 声明三个变量存储'姓名、性别、年龄',再打印信息,格式如下: 姓名:杰达姆,性别:男,年龄:18 --声明

 图片 22

add constraint 约束名 约束类型 具体的约束说明

8、打开sql server profiler查看跟踪的时间,发现查询时间大幅提升,说明索引可以提高查询速度。

Create proc usp_selectstu

实验案例二:分别练习创建各种索引

2.执行命令缓存:在执行存储过程,自定函数时,Sql Server需要先二进制编译再运行,编译后的结果也会缓存起来, 再次调用时就无需再次编译。

为tstudent表创建聚集索引

As

目前tstudent表中没有任何索引也没有主键

exec sp_helptext 'proc_temp'

 图片 23

insert into WageInfo(CompanyName, Poste)values
('微软','职员'),
('百度','职员'),
('腾讯','职员'),
('爱奇艺','职员'),
('华为','职员')
insert into StudentInfo(Name, CompanyID, PosteDate, Wage)values
('张三',1,'2016-05-05',2500),
('李四',2,'2016-05-06',2600),
('王五',3,'2016-05-07',3000),
('赵二',4,'2016-05-08',4000),
('钱一',5,'2016-05-09',5000)
insert into StudentInfo(Name, CompanyID, PosteDate, Wage)values('钱二',null,'2016-05-09',NULL)

然后把跟踪的结果保存在桌面上:

Create proc usp_stuInfo @gradeid int=2       (默认)

为Tstuden表的studentID创建主键就同时创建了聚集索引

 create table TeaInfos  

2、向表中插入大量数据,数据越多,验证索引的效果越好。

存储过程是在数据库管理系统保存的,预先编译的,能实现某种功能的SQL程序,它是数据库应用中运用比较广泛的一种数据对象。

等待5分钟左右,打开表的属性,查看表的行数,当前为1032363,如下图所示:

exec sp_helptext 'proc_temp'

注意选择benet数据库中的学生表,然后点击“开始分析”

exec sp_helptext 储存过程名      可以查看储存过程代码

实验案例一:验证索引的作用

  1. 数据缓存:执行个查询语句,Sql Server会将相关的数据页(Sql Server操作的数据都是以页为单位的)加载到内存中来,下一次如果再次请求此页的数据的时候,就无需读取磁盘了,大大提高了速度。

使用语句完成:While 1>0  Insert into 学生表(姓名)  values(‘于美丽’)

3.减少网络流量

索引类型为clusterd(聚集索引),索引列为“学号”。

if exists(select name from sysindex where name='索引')

选中studentID,单击左上侧的主键按钮

--但此时,存储过程仍然能被execute、alter和drop。

图片 24

一 存储过程的概念

3、 使用语句查询第900000行的数据,Select * from 学生表 Where 学号=900000

局部变量有两种赋值方法

图片 25

go

图片 26

with encryption

1.存储过程只在创造时进行编译,以后每次执行存储过程都不需再重新编译,而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。 2.当对数据库进行复杂操作时,可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。 3.存储过程可以重复使用,可减少数据库开发人员的工作量。 4.安全性高,可设定只有某些用户才具有对指定存储过程的使用权

--3-- select * from StuInfo

As

as

 alter table Classes

为什么需要存储过程?

Create proc usp_selectstu

2.执行速度块,效率高

为什么需要存储过程?

declare @age int set @age=21

if exists(select * from sysobjects where name='视图名')

 alter table Classes

 insert into Classes select 't10',25 union select 't11',18 union select 't12',23

print @gradeid

USE Wages
GO
if exists(select * from sysobjects where name='WageInfo')
DROP table WageInfo
CREATE TABLE WageInfo 
(
  CompanyID INT primary key IDENTITY(1,1),
  CompanyName varchar(50) NOT NULL,
  Poste varchar(50) NOT NULL,
)
GO
if exists(select * from sysobjects where name='StudentInfo')
DROP table StudentInfo
CREATE TABLE StudentInfo
(
  ID  INT primary key IDENTITY(1,1),
  Name  VARCHAR(50) NOT NULL,
  CompanyID INT ,
  PosteDate  DATETIME ,
  Wage  int,
)
GO

二 系统存储过程

示例:检索出在work表中每一个部门的最高基本工资的职工资料  select * from work a where 基本工资=(select max(基本工资) from work b where a.部门名称=b.部门名称)

--3-- 不停的提高学生笔试成绩2分,让所有学生的笔试成绩都及格

2.执行命令缓存:在执行存储过程,自定函数时,Sql Server需要先二进制编译再运行,编译后的结果也会缓存起来, 再次调用时就无需再次编译。

drop view 视图名

on 表名(列名)

select top 3 * from StuExam order by writtenExam desc end else begin print '本班成绩较差' select top 3 * from StuExam order by writtenExam end

select * from StuInfo select * from StuExam

 

查询

declare @name varchar(10),         @sex char(2),         @age int

create view  视图名

 

add constraint pk_clsid_Classes   primary key(clsid)  

调用存储过程:exec usp_selectstu

2.执行速度块,效率高

'alter table Classes

--应用这个,我们可以对某些关键的存储过程进行加密。

2.创建带入参数的存储过程

As

视图

exec usp_selectGrade '李小龙',@id output 

--1-- 声明变量number,并赋值,然后判断是偶数还是奇数,结果如下: 当前值为11,它是一个奇数 declare @number int set @number=12 if(@number%2=0) print '该数为偶数' else print '该数为奇数' -

   建数据库的SQL语句如下(包含了如果有该数据库先删除在建立数据库)

exec sp_helptext 'proc_temp_encryption'

下面是创建表的SQL语句(包含了如果有该表先删除在建表)

alter table StuInfos drop constraint fk_StuInfos_Classes

三:循环结构 --1-- 输出九九次'我爱你' declare @i int=1  while(@i<=99) begin print '第'+convert(varchar,@i)+'我爱你' set @i+=1 end

drop index 表名。索引名

create proc usp_selectGrade @name nvarchar(10),@gradeid int output

一 存储过程的概念

name varchar(10) not null, --名称

 

语法如下

1.创建不带参数的存储过程

 print @temp       --输出   

print '老年人'

--存储过程的内容不会被轻易看到(虽然解密也是有可能的)。

这些内存一般都是Sql Server运行时候用作缓存的,例如你运行一个select语句, 执行个存储过程,调用函数;

As

    select * from student;

子查询:查询中再查询,通常是以一个查询作为条件来供另一个查询使用
语法:
   select 列表 from 表名 
 where >(子查询)
注意:
 使用比较运算符时,要求子查询只能返回一条或空的记录!
示例:
 要求查出比我小的学生信息
 select * from student
 where studentno in
 (select studentno from student
 where studentname='我')

go

tel char(11) unique, --电话

 3. 输出操作,   如: select 变量名 二:控制流程结构:if,else

--输出 print @name+'是一个'+@result

   5、 不缓存存储过程

  go

  go  

创建视图索引

-2-- 根据输入的年龄显示人的生长阶段 未成年人<18,青年人<30,成年人<60,老年人<100,超人>=100

常用的系统存储过程

系统存储过程

说明

sp_databases

列出服务上的所有数据库

sp_helpdb

报告有关指定数据库或所有数据库的信息

sp_renamedb

更改数据库的名称

sp_tables

返回当前环境下可查询的对象的列表

sp_columns

返回某个表列的信息

sp_help

返回某个表的所有信息

sp_helpconstraint

查看某个表的约束

sp_helpindex

查看某个表的索引

sp_stored_procedures

列出当前环境中的所有存储过程

sp_password

添加或修改登录账户的密码

 

--应用这个,我们可以对某些关键的存储过程进行加密。

性别     (男或女,默认男)   年龄     (12-60)   电话     (唯一)   班级编号 (外键)   

create proc usp_selectGrade @name nvarchar(10),@gradeid int output

exec sp_helptext 'proc_temp_encryption'

alter table 表名

select * from dbo.Student where StudentName like @name

(注意:加密存储过程前应该备份原始存储过程,且加密应该在部署到生产环境前完成。) 

exec proc_temp_encryption;

Sql Server系统内存管理在没有配置内存最大值,很多时候我们会发现运行Sql Server的系统内存往往居高不下。这是由于他对于内存使用的策略是有多少闲置的内存就占用多少,直到内存使用虑达到系统峰值时(预留内存根据系统默认预留使用为准,至少4M),才会清除一些缓存释放少量的内存为新的缓存腾出空间。

else if(@age<100)

 1. set一次只能为一个变量赋值,而select能同时为多个变量赋值  

 2. 赋值操作,   如: select 变量名=值  

3.创建带出参数的存储过程

GO

  4、 带通配符参数存储过程

存储过程是在数据库管理系统保存的,预先编译的,能实现某种功能的SQL程序,它是数据库应用中运用比较广泛的一种数据对象。

--4-- 问题: 根据如下规则对机试成绩进行反复加分,       直到机试平均分超过85分为止       请编写T-SQL语句实现,注:(循环+多分支) 90分以上:  不加分 80-89分:   加1分 70-79分:   加2分 60-69分:   加3分 60分以下:  加5分 declare @a int   while(1=1) begin select @a=AVG(labExam)from dbo.stuExam if(@a<85) begin update dbo.stuExam set labExam =case          when labExam>=90 then  labExam          when labExam>=80 then labExam+1          when labExam>=70 then labExam+2          when labExam>=60 then labExam+3          else          labExam+5         end end else break end select AVG(labExam) AS 机试成绩平均分 from dbo.stuExam

as

存储过程的优点:

常用的系统存储过程

系统存储过程

说明

sp_databases

列出服务上的所有数据库

sp_helpdb

报告有关指定数据库或所有数据库的信息

sp_renamedb

更改数据库的名称

sp_tables

返回当前环境下可查询的对象的列表

sp_columns

返回某个表列的信息

sp_help

返回某个表的所有信息

sp_helpconstraint

查看某个表的约束

sp_helpindex

查看某个表的索引

sp_stored_procedures

列出当前环境中的所有存储过程

sp_password

添加或修改登录账户的密码

 

Select @gradeid=gradeid from student where  studentname=@name

 2. set只能赋一个固定的值,而select能动态的赋值(值来自于表内) select的作用  

declare @avgScore int select @avgScore=AVG(writtenExam) from StuExam if(@avgScore>=70) begin print '本班成绩优秀'

go   

3.创建带出参数的存储过程

本文由网上澳门金莎娱乐发布于数据库,转载请注明出处:关于SQL SERVER数据库学习总结

关键词: