多少库面试题汇总,NET学习笔记011SqlServer基础知识

创建规则并将其应用到指定的字段

--创建规则

create rule age_rule as @eAge>0

--把自定义的规则绑定到字段

exec sp_bindrule 'age_rule' ,'employee.eAge'

--‘规则名’,’表名.字段名’

常用SQL代码整理(MS-SQLServer)

str()

  1. 数据库三范式是什么?
    第一范式:表中每个字段都不能再分。
    第二范式:满足第一范式并且表中的非主键字段都依赖于主键字段。
    第三范式:满足第二范式并且表中的非主键字段必须不传递依赖于主键字段。
  2. 什么是数据库事务?
    事务具有四大特性:一致性、原子性、隔离性、持久性。
    数据库事务是指:几个SQL语句,要么全部执行成功,要么全部执行失败。比如银行转账就是事务的典型场景。
    数据库事务的三个常用命令:Begin Transaction、Commit
    Transaction、RollBack Transaction。
  3. 什么是视图?
    视图实际上是在数据库中通过Select查询语句从多张表中提取的多个表字段所组成的虚拟表。
    l
    视图并不占据物理空间,所以通过视图查询出的记录并非保存在视图中,而是保存在原表中。
    l 通过视图可以对指定用户隐藏相应的表字段,起到保护数据的作用。
    l 在满足一定条件时,可以通过视图对原表中的记录进行增删改操作。
    l 创建视图时,只能使用单条select查询语句。
  4. 什么是索引?
    索引是对数据库表中一列或多列的值进行排序的一种结构,使用索引可快速访问数据库表中的特定信息。
    l 索引分为:聚集索引、非聚集索引、唯一索引等。
    l 一张表可以有多个唯一索引和非聚集索引,但最多只能有一个聚集索引。
    l 索引可以包含多列。
    l
    合理的创建索引能够提升查询语句的执行效率,但降低了新增、删除操作的速度,同时也会消耗一定的数据库物理空间。
  5. 什么是存储过程?
    存储过程是一个预编译的SQL语句,优点是允许模块化的设计,就是说只需创建一次,以后在该程序中就可以调用多次。如果某次操作需要执行多次SQL,使用存储过程比单纯SQL语句执行要快。
  6. 什么是触发器?
    触发器是一中特殊的存储过程,主要是通过事件来触发而被执行的。它可以强化约束,来维护数据的完整性和一致性,可以跟踪数据库内的操作从而不允许未经许可的更新和变化。可以联级运算。如,某表上的触发器上包含对另一个表的数据操作,而该操作又会导致该表触发器被触发。
  7. 写出一条Sql语句:取出表A中第31到第40记录 (MS-SQLServer)
    解1:select top 10 * from A where id not in (select top 30 id from
    A)
    解2:select top 10 * from A where id > (select max(id) from
    (select top 30 id from A )as A)
    解3:select * from (select *, Row_Number() OVER (ORDER BY id asc)
    rowid FROM A) as A where rowid between 31 and 40
  8. 写出一条Sql语句:取出表A中第31到第40记录 (Mysql)
    select * from A limit 30, 10
  9. 写出一条Sql语句:取出表A中第31到第40记录 (Oracle)
    select *
    from (select A.*,
    row_number() over (order by id asc) rank
    FROM A)
    where rank >=31 AND rank<=40;
  10. 在关系型数据库中如何描述多对多的关系?
    在关系型数据库中描述多对多的关系,需要建立第三张数据表。比如学生选课,需要在学生信息表和课程信息表的基础上,再建立选课信息表,该表中存放学生Id和课程Id。
  11. 什么是数据库约束,常见的约束有哪几种?
    数据库约束用于保证数据库表数据的完整性(正确性和一致性)。可以通过定义约束索引触发器来保证数据的完整性。
    总体来讲,约束可以分为:
    主键约束:primary key;
    外键约束:foreign key;
    唯一约束:unique;
    检查约束:check;
    空值约束:not null;
    默认值约束:default;
  12. 列举几种常用的聚合函数?
    Sum:求和 Avg:求平均数 Max:求最大值 Min:求最小值
    Count:求记录数
  13. 什么是内联接、左外联接、右外联接?
    l 内联接(Inner Join):匹配2张表中相关联的记录。
    l 左外联接(Left Outer
    Join):除了匹配2张表中相关联的记录外,还会匹配左表中剩余的记录,右表中未匹配到的字段用NULL表示。
    l 右外联接(Right Outer
    Join):除了匹配2张表中相关联的记录外,还会匹配右表中剩余的记录,左表中未匹配到的字段用NULL表示。
    在判定左表和右表时,要根据表名出现在Outer Join的左右位置关系。
  14. 如何在删除主表记录时,一并删除从表相关联的记录?
    如果两张表存在主外键关系,那么在删除主键表的记录时,如果从表有相关联的记录,那么将导致删除失败。
    在定义外键约束时,可以同时指定3种删除策略:一是将从表记录一并删除(级联删除);二是将从表记录外键字段设置为NULL;三是将从表记录外键字段设置为默认值。
    级联删除示例:
    alter table 从表名
    add constraint 外键名
    foreign key(字段名) references 主表名(字段名)
    on delete cascade
  15. 什么是游标?
    游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录进行处理的机制。
    游标的使用步骤:
  16. 定义游标:declare cursor 游标名称 for select查询语句 [for
    {readonly|update}]
  17. 打开游标:open cursor
  18. 从游标中操作数据:fetch… … current of cursor
  19. 关闭游标:close cursor

多列索引

是指一个索引依据多个字段进行排序。其排序方式:第一个索引排序之后,对其中的值相同重复的数据,再按照第二个字段来排序。

  1. 创建数据库
    /创建数据库libraryDB/
    CREATE DATABASE libraryDB
    ON
    (
    /数据库文件的详细描述/
    NAME = ‘libraryDB_mdf’, –主数据库文件的逻辑名
    FILENAME = ‘E:librarylibraryDB_mdf.mdf’, –主数据文件的物理名
    SIZE = 3MB, –初始大小
    FILEGROWTH = 20% –增长率
    )
    LOG ON
    (
    /日志文件的详细描述/
    NAME = ‘libraryDB_ldf’, –日志文件的逻辑名
    FILENAME = ‘E:librarylibraryDB_ldf.ldf’, –日志文件的物理名
    SIZE = 1MB, –初始大小
    MAXSIZE = 15MB, –最大值
    FILEGROWTH = 10% –增长率
    )
    Go

  2. 数据表(创建|修改|删除)
    –判断BookType表是否存在,存在则删除
    if exists (select 1 from sysobjects where [name]=’BookType’)
    begin
    drop table BookType
    end
    –创建图书类型表:BookType
    create table BookType
    (
    TypeId int not null identity(1,1) primary key,
    –图书类型编号(主键、标识列、从1开始、每次增加1)
    TypeName varchar(50) not null, –图书类型名称
    Remark varchar(100) –备注信息
    )
    –修改BookType表,增加备注字段
    alter table BookType
    alter column Remark varchar(100)
    –删除表BookType
    drop table BookType

  3. 创建主键
    –为表添加主键
    alter table productinfo
    add constraint PK_ProductInfo_ProductId primary key(ProductId)
    –删除主键只需要将add替换为drop

  4. 创建外键
    –为表添加外键
    alter table productpromotion
    add constraint FK_Promotion_Product foreign key (ProductId)
    references ProductInfo(ProductId)
    –删除外键只需要将add替换为drop

  5. 检查约束
    –创建检查约束(商品编号的长度大于2)
    alter table productinfo
    add constraint CK_Product_Number
    check(len(ProductNumber)>2)–删除外键只需要将add替换为drop
    –创建检查约束(性别为男或者女)
    –check(Gender in (‘男’, ‘女’))

  6. 惟一约束
    –创建唯一约束(商品编号唯一)
    alter table productinfo
    add constraint UQ_Product_Number unique (ProductNumber)

  7. T-SQL编程(定义变量、为变量赋值)
    –T-SQL中定义变量
    declare @sum int;
    declare @i int;
    –set赋值(一次只能为一个变量赋值)
    set @sum = 0;
    –select赋值(一次可以为多个变量赋值)
    select @sum=0,@i=1;

  8. T-SQL编程(if-else循环)
    –if实例
    declare @i int;
    set @i=7;
    if(@i%2 = 0)
    print ‘偶数’
    else
    print ‘奇数’
    go

  9. T-SQL编程(while)
    –请输出1-10之间的数字
    declare @i int;
    set @i = 1;
    while (@i<=10)
    begin
    print @i;
    set @i = @i + 1;
    end

  10. T-SQL编程(case)
    –case的第一种语法格式(使用case将0显示为:空闲 1显示为:使用中)
    select intComputerId,
    ‘State’=case
    when intInUse=0 then ‘空闲’
    when intInUse=1 then ‘使用中’
    end,
    chvComputerName,chvDescription
    from tblcomputer;
    –case的第二种语法格式
    select intComputerId,
    ‘State’=case intInUse
    when 0 then ‘空闲’
    when 1 then ‘使用中’
    end,
    chvComputerName,chvDescription
    from tblcomputer

  11. 视图
    –创建视图的语法示例
    create view view_RecordDetail
    as
    select cp.chvComputerName, ci.chvUserName, ri.dtmStart, ri.dtmEnd,
    ri.mnyFee
    from TblRecordInfo as ri–as为表取别名
    inner join TblCardInfo as ci on
    ri.intCardId=ci.intCardId–内连接用inner
    join,同时应该为两张表指定连接字段
    inner join TblComputer as cp on ri.intComputerId=cp.intComputerId
    –使用视图(和表很相似)
    Select * from view_RecodDetail

  12. 存储过程(无参数)
    –创建不带参数的存储过程
    create procedure pro_ComputerUseState
    as
    begin
    select * from tblcomputer;
    end
    –调用不带参数的存储过程
    exec pro_ComputerUseState;

  13. 存储过程(含输入参数)
    –如何创建有输入参数的存储过程
    create procedure pro_getComputerState
    @state int=0–参数默认为输入参数
    as
    begin
    select intComputerId,
    ‘intInUse’=case intInuse
    when 0 then ‘未使用’
    when 1 then ‘以使用’
    end,
    chvComputerName,
    chvDescription
    from tblcomputer
    where intInuse=@state
    end
    go
    –调用带有输入参数的存储过程
    declare @state int;
    set @state = 1;
    exec pro_getComputerState @state;

  14. 存储过程(含输入参数、输出参数)
    –创建带有输入参数和输出参数的存储过程,多个参数之间用逗号,隔开,最后一个参数后无需逗号
    create proc pro_getComputerStateById
    @intComputerId int,
    @state int output
    as
    begin
    select @state = intInUse
    from tblcomputer
    where intcomputerid=@intComputerId
    end
    –调用带输出参数的存储过程,调用时一定要在输出参数后加关键字output
    declare @state int, @computerId int;
    set @computerId = 7;
    exec pro_getComputerStateById @computerId,@state output
    select @state;

  15. 触发器
    –判断触发器是否存在,存在则删除触发器
    if exists (select * from sys.sysobjects where name =
    ‘tr_insertRecord’)
    drop trigger tr_insertRecord
    go
    –如何定义|创建一个触发器
    create trigger tr_insertRecord
    on TblRecordInfo
    for insert–for等价于after,表示当新增完记录之后才会执行触发器
    as
    begin
    declare @cardid int, @startTime datetime;
    select @cardid = intcardid, @startTime = dtmStart from inserted;
    select ‘卡号:’+convert(nvarchar(5), @cardid);
    select ‘上机开始时间:’+convert(nvarchar(20), @startTime);
    end

  16. 事务处理
    –定义变量@sumError用于记录事务过程中发生错误的次数
    declare @sumError int;
    set @sumError = 0;
    begin transaction
    update tblaccount set mnycurrentmoney = mnycurrentmoney + 200000
    where chvAccountName=’宝钢集团’
    –通过系统变量@@error可以获取上次被执行的sql是否执行成功,如果执行成功@@error的值为0,否则为1
    set @sumError = @sumError + @@error
    update tblaccount set mnycurrentmoney = mnycurrentmoney – 200000
    where chvAccountName=’安钢集团’
    set @sumError = @sumError + @@error
    –判断是否在执行过程中出现错误
    if(@sumError<>0)
    begin
    print ‘事务执行失败,即将回滚’
    rollback transaction
    end
    else
    begin
    print ‘事务执行成功,即将提交’
    commit transaction
    end

示例:

Create view View_22 as select id,name,age from student where id>3

取消规则绑定

exec sp_unbindrule 'employee.eAge' --‘表名.字段名’

实体约束

它用来针对行进行约束。

比如:要求每个学生的姓名、电话、地址都不能出现重复。

同样的值不能在其行出现。

FORWARD_ONLY和SCROLL二选一

FORWARD_ONLY只能一行一行的前进,而不能后退或跳过中间的行。

SCROLL定义的游标可以在数据集的任何方向的任何位置移动。

示例:

declare Cursou_test cursor for --未定义移动方式

select id,name from student

declare Cursou_test2 cursor FORWARD_ONLY for --只进游标

select id,name from student

declare Cursou_test3 cursor SCROLL for --滚动游标

select id,name from student

open Cursou_test

open Cursou_test2

open Cursou_test3

FETCH NEXT FROM Cursou_test --只能一行行前进

FETCH NEXT FROM Cursou_test2 --只能一行行前进

FETCH ABSOLUTE 1 FROM Cursou_test3 --表中的绝对位置的正序第条

FETCH RELATIVE 2 FROM Cursou_test3 --相对于当前针对位置前进条

FETCH RELATIVE -2 FROM Cursou_test3 --相对于当前针对位置后退条

FETCH FIRST FROM Cursou_test3 --第一条

FETCH LAST FROM Cursou_test3 --最后一条

练习

2、传入一个id参数,根据这个id来查询相应的学生掌握记录,并返回这个学生的姓名、年龄、电话。

3、传入一个age参数,根据这个age参数来返回所有年龄大于该年龄的学生记录。

将游标得到的数据传入局部变量

我们可以INTO关键字,将游标中的select语句查询后的值存入局部变量。

游标每次执行只返回一条数据。

约束的命名

主键约束的命名:PK_student,PK代表主键Primary Key 。

CHECK约束:CK_ students_4j432j,CK_ students_ageNotSmall0

根据约束的方法

主键约束

外键约束

唯一约束

CHECK约束

DEFAULT约束

规则

默认值

按唯一性分类

T-SQL语法相关

删除示例:

–当学生毕业以后,假设学生表中的学生记录就需要被删除掉。借书记录也需要同时被删除掉。

create trigger delStudentWithBR on student for delete

as

delete borrowRecord 

from borrowRecord as br,deleted as d

where br.sNum=d.sNum

--从deleted临时表中找到刚才被删除的那个学生的学号

--然后删除所有这个学号名下借书记录。

练习:

1、 在查询学生数据的时候,根据学生年龄,来显示学生表中的每个学生是否已成年。通过自定函数实现。

2、 在查询学生数据的时候,根据学生的地址,来判断其中是否包含“重庆”两个字,包含显示”本地学生”,不包含则显示“外地学生”。通过自定函数实现。

3、 针对student表,用用户自定义函数,实现每页为10条的分页。传入参数是页数pageIndex,传出参数是存放了每页数据的结果集。

create function getPage(@pindex int,@psize int)

returns table

as

return(select top(@psize) * from student 

where id not in

(

select top(@psize*(@pindex-1)) id from student order by id

) order by id

)

select * from dbo.getPage(3,3)

sqlServer_游标

语法:

SELECT <字段列表> FROM <左表><LEFT | RIGHT > [OUTER] JOIN <右表> ON <连接条件>

如果使用LEFT就是显示左表中的所有数据,如果使用Right就是显示右表中的所有数据

三角函数

sin()、cos()、tan()、cot()

select SIN(0.5*PI()),TAN(0.25*PI())

--sin(90度),tan(45度)

事后触发器

当用户执行某种操作完成以后,才会被触发的触发器。

CREATE DATABASE 的完整语法

CREATE DATABASE
[
[ON | PRIMARY ]
(
[NAME = ‘实例名’ ,]
[FILENAME = ‘文件名’ ,]
[SIZE = 文件大小 ,]
[MAXSIZE = 文件最大容量]
)
]
[
[ON | PRIMARY ]
(
[NAME = ‘实例名’ ,]
[FILENAME = ‘文件名’ ,]
[SIZE = 文件大小 ,]
[MAXSIZE = 文件最大容量]
)
]
 [COLLATE <核对名称>]
[FOR ATTACH [ WITH <server broker> ||FROM ATTACH_REBUILD_LOG ||WITH DB_CHAINING ON|OFF|TRUSTWORTHY ON|OFF  ]]
[AS SNAPSHOT OF<源数据库名>]
ON

 

用在两个地方:一是定义数据库文件的位置。二是定义数据日志库文件的位置。

PRIMARY 关键字用于指定多个数据库文件中的主文件。

NAME 指定文件的实例名称。也就是在数据库的逻辑名(非物理文件名)

FILENAME 就是指数据文件的物理位置和文件名,mdf(数据库)  ldf(日志文件)

SIZE 数据库大小,可以在数字后面用KB或GB表示数据库的大小。

MAXSIZE 最大小容量。

 

3、使用游标

SQL Management Studio的界面操作

左侧目录中,我们可以创建数据库数据表。

左上角的新建查询按钮,可以打开一个输入与执行SQL语句的窗口。在此窗口中我们可以通过按F5键或点击“执行”来运行SQL语句。 在输入多条SQL语句的情况下,可以选中需要执行的代码,然后按F5来只执行被选中的部分。

DELETE语句

delete dbo.student where id=14

waitfor等待

当T-SQL执行到waitfor语句时,程序会进入等待状态,等侍指定时间之后,程序再继续执行后面的语句。

临时表

用于短期存储数据据的表,它使用的是系统的tempdb数据库。

创建触发器

示例:

create Unique nonclustered index

IX_ageMore on student(name desc)

tempdb

用来存放一些临时信息,重启数据库服务端时,它存储的信息会被清空。

DROP语句可以同时删除多张数据表

DROP TABLE 表1,表2,….

示例:

drop table table1,table2,table3

一次插入多行数据

insert into student (name,age,sex,address,phone) values
('大宝',28,1,'城革大本营',12345678),
('小宝',13,1,'城革大本营',12345678),
('老宝',82,1,'城革大本营',12345678);

外键约束

就是为了确保数据的准确性,比如:确保每一条论坛贴子的发贴人都是真正存在于用户表的。

示例2:

ALTER FUNCTION backSex(@sex bit)

returns varchar(10)

AS

BEGIN

declare @sexChar varchar(10)

if @sex=1

set @sexChar = '男'

else

set @sexChar = '女'

RETURN @sexchar

END

select name,dbo.backSex(sex) from student

字符串函数

操作对象仅限:char、varchar、binary、nvarchar、varbinary类型

Ltrim() 去掉字符串左边的空格。select  LTRIM( ‘      abc     ‘)

Rtrim() 去掉字符串右边的空格。select  RTRIM( ‘      abc     ‘)

ascii() 将字符转换为其中ascii码表中的位置。select ascii(‘A’)

char() 将ascii码转换为字符。如:select char(65)

lower() 转换字母为小写。如:select LOWER(‘Hello Kitty’)

upper() 转换字母为大写。如:select upper(‘Hello Kitty’)

str() 将数字转换为字符串。语法:str(数值,字符串长度,小数位数)比如:select ‘圆周率是’+str(3.1415926,5,3)

charIndex 返回子字符串在另一个字符串中第一次出现的位置。语法:

charIndex(子串,母串),就是判断前者是否为后者的子集,如果没有在母串中找到子串则返回0。比如:select
charindex(‘day’,’today is a good day’)

substring(字符串,开始位置,截取长度) 截取字符串,示例:

select substring(‘today is a good day’,12,4)

通常外键在外键引用表上添加

首先要区别哪张表是主键表(是指用已经存在的值作为约束范围),哪张表是外键表(是指添加数据时被约束必须符合范围的那张表)

 

创建外键的时候,通常是在外键表上创建的。

语法:

ROLLBACK TRAN [SACION ] [<事务名称>|<保存点名称>| <@事务参数>|<@保存点参数> ]

group by子句

将指定字段中的相同的值进行分组。值相同的只显示一行。

示例1:

SELECT age,COUNT(name) from student group by age

示例2:

在sql server 中所显示的字段列表中,不能使用group by后面没有出现过的字段名,除非使用聚合函数。

SELECT age,address,COUNT(name) from student group by age,address

创建UDF

CREATE FUNCTION [模式] 函数名

([@参数名 [AS] 参数类型 [=默认值] [READONLY] [,………]])

RETURNS 返回值的类型

AS

BEGIN

[若干语句]

RETURN (返回单一值)

END

创建临时表

系统函数

msdb

提供了SQL Server的代表服务中要执行的任务和调试计划

@@IDENTITY

上一次执行insert语句后插入的数据记录的id

示例:

insert into teacher values('小李',22,'19119111011')

select @@IDENTITY

简写语法

CREATE INDEX 索引名 ON 数据表 ( 字段名 desc )

外部连接

内部连接有一定的排他性,第二张表是对第一张表的补充,如果第一张表不需要第二张表中的某些数据,那么第二张表中不被需要的数据就不会被显示出来。

缺点

1、性能不高,查询耗时耗费资源。

2、对于由复杂的select语句生成的视图而言,修改视图中的数据时有可能会错误。

因此,视图通常只能应用于小型或对性能要求不高的项目上。

开启或关闭触发器

事务的实际操作

当我们开启事务之后,我们操作的实际上都是缓存中的数据。只有当提交事务的时候,操作才会写入日志。

isDate()

判断日期数据是否合法,是返回1,否返回0。

select ISDATE('20160229')

删除与更新

当我们在数据库中执行增删改操作的时候,系统会在sql
server服务器的内存中修改两张临时表Deleted和Inserted临时表。

假如我们现在需要开发一个校园图书管理系统,每个同学前去借书都会生成一个借书记录。

1、首先学生数据表中需要一个sNum学号字段

2、创建借书记录表,其中借书记录需要与学号相关联(不是id)。

CREATE TABLE borrowRecord(

bid int identity primary key not null,

sNum int, --关联学生表学号

borrowDate datetime, --借书日期

returnDate datetime --还书日期

)

ROWGUIDCOL

是指将一个表中的数据复制到另一个表中时,如果产生ID重复情况下,应用如何处理。

语法:

BEGIN TRAN [SACTION] [<事务名称>|<@事务参数>] [WITH MARE[<’描述’>]]

创建带输出参数的存储过程

语法

CREATE PROC 存储过程名

[@参数名1 数据类型 ][, [@参数名2 数据类型 ]]…

[@输出参数名 数据类型 ] OUTPUT

AS   SQL语句序列……

按照执行的操作分类

1、数据操纵语言DML触发器,是指触发器所在数据表中发生了insert、update、delete操作时触发。

2、数据定义语言DDL触发器,这类触发器是指当服务器或数据中执行了create、alter、drop语句时被触发。

3、登录触发器:是指当用户登录sql server时触发。

按激活顺序分类

修改存储过程

语法:

Alter proc[edure] 存储过程名 [@参数1 数据类型],[@参数2 数据类型],[@输出参数名 数据类型] OUTPUT

示例:

ALTER PROC porcTest AS select * from student

单列索引

是指一个索引只针对一个字段进行排序。

创建数据库示例:

CREATE DATABASE TESE22BB
ON
(
NAME =TEST22BB,
FILENAME = 'e:test22bb.mdf',
SIZE =30MB,
MAXSIZE = 50MB
)
LOG ON
(
NAME = 'TEST22BBLOG',
FILENAME='e:test22bb.ldf',
SIZE = 10MB,
MAXSIZE = 20MB
)
GO

 

用这种方式,我们可以在指定的硬盘或U盘路径之下创建数据库。

 

注意:如果需要对数据库文件进行复制、剪切或删除操作。

 

按聚集性分类

ISNUMERIC()

判断是否为合理的数值,即使这个数值以字符串的形式存在。

select ISNUMERIC(‘123f457’)

是返回1,否返回0

赋值:

在T-SQL中,可以用select 或 set 来对变量进行赋值操作

select

它用于显示变量的值,或者对变量进行赋值。

可以一次对多个变量进行赋值。

示例1:

declare @num1 int,@num2 int

select @num1=14,@num2=18

select @num1,@num2

如果select 后面是赋值语句的话,则不会显示变量的值。

如果select 后面是变量的话,则显示变量的值。

示例2:

declare @num1 int

select @num1 = (select COUNT(1) from student where age>19)

print @num1

示例3:

declare @num1 int

select @num1 =  COUNT(1) from student

 where age>19

print @num1

备份

相对于分离数据库,备份的时候我们不需要停止数据库的运行。备份可以在用户正在使用数据库的状态下进行。在指定数据库的右键菜单中à任务à备份à在“目录-备份到”区域中指定数据库备份的路径(默认路径是在sql server的安装目录下,如果需要改变备份路径,需要先删除默认路径,再点击添加)

当SQL Server身份验证无法登陆时

1、 用windows身份验证(也就是用本机管理员来登陆,不需要输入用户名密码的)。

2、 展开左侧目录中的 SQL
Server à 安全性 à 登陆名 à 双击sa à 打开sa 用户的属性窗口。

3、 修改密码

4、 取消”强制实施密码策略”

5、 在“状态”选项卡中,对“是否允许连接到数据引擎”和“登陆”分别选择“授予”和“启动”。

6、 点击确定关闭sa 用户的属性窗口

7、 右键点击服务器根节点,选择属性打开“服务器属性”弹窗。

8、 选择“安全性”选项卡,设置“服务器身份验证”为“SQL Server和Windows身份验证”。

9、 确定并关闭“服务器属性”弹窗,然后在SQL server配置管理器中重启数据服务端,再用sql server 帐户密码来登陆即可。

CAST()

示例:

select '我们班上有'+CAST(10 as  varchar(2))+'个同学'

sqlcmd命令行管理工具

通过纯指令的方式来管理SQL
server数据库服务端。

开始菜单à 运行à输入cmd打开命令行窗口à输入sqlcmd /? 查看sqlcmd命令的帮助信息。

在该命令行下我们可以通过sql语句来操作数据库。

比如:

sqlcmd

use test

select * from student

go

最后,必须输入go才会开始执行SQL语句。exit退出sqlcmd命令行状态。

通过循环(配合游标)来遍历数据表

declare CursorX cursor SCROLL for

select id,name from student

--定义游标

open CursorX

declare @id int,@name nvarchar(50)

FETCH NEXT FROM CursorX INTO @id,@name

while @@FETCH_STATUS=0 --游标执行后的状态

begin

print CAST(@id as nvarchar(10))+@name

FETCH NEXT FROM CursorX INTO @id,@name

end

close CursorX

deallocate CursorX

--其中,@@FETCH_STATUS代表上一条游标语句执行后的状态,它的返回值有三个:

 

返回值

说明

0

FETCH 语句执行成功

-1

FETCH 语句失败或行不在结果集中

-2

提取的行不存在

 

示例:

declare @id int,@name nvarchar(50)

--临时变量

declare myCursor cursor LOCAL for

select id,name from student

--定义游标

open myCursor --打开游标

fetch next from myCursor into @id,@name

print @name

语法:

局部变量必须以@开头

Declare @变量名 类型 [,@变量名2 类型]…..

声明一个或多个变量,示例:

declare @num1 int ,@num2 int

1、定义一个游标

可以理解成在数据集上的指针,我们可以控制这个指针来一条条的将数据集遍历出来,或者也可以仅仅用于得到特定的行,所以游标必须定义在select语句查询的数据集之上。

set

一次只能对一个变量进行赋值

示例1:

declare @num1 int ,@num2 int,@num3 int

set @num1=10

set @num2=25

set @num3= @num1+@num2

select @num3

示例2:

declare @num1 int

set @num1 =(select top 1 age from student)

select @num1

( select语句中的top关键字代表查询到的数据集的最上面的几条数据记录。比如:查询最上面的3条学生数据select top 3 * from
student )

示例3:

declare @num1 int

set @num1 = (select COUNT(1) from

student where age>19)

print @num1

insert into插入数据

在创建数据表的时候添加唯一约束

CREATE TABLE USERINFO(

uid int identity primary key NOT NULL,

uName nvarchar(50),

uPhone char(20) UNIQUE

)

注意:唯一约束与唯一索引达到的效果是一样的。

语句块

if 9-5=5

 begin --相当于{

print '你说对了'

 end --相当于}

else

begin

print '你说错了'

end

表变量

表变量与我们之前在其他语言之中见到的二维数组,是有很多相似之外的,但是我们可以像操作数据表一样来操作它,只需要记住一点,那就是表变量是存在于内存中的,它的执行效率高,但是它像变量一样有作用域和生存周期。

练习

创建一张学生数据表,包含字段id、name、age、sex、address、phone、classNum

1、 一次性插入5条学生数据,并且不写字段名。

2、 用select语句查询ID为2到ID为4之间的记录,(用BETWEEN关键字)。

3、 查询出所有姓王的同学(用LIKE模糊查询)。

4、 查询出班上年龄为(16、17、23、24)的同学

5、 统计各班分别有多少名学生

6、 分别统计男生与女生的年龄总合。

7、 找到年龄最大的女生。

8、 修改id为3的学生姓名为”李小虫”

9、 删除id为3的学生。

连接查询

同时查询多张数据表并将这些数据表以一定的逻辑关系进行连接,让它们显示的结果类似于一张数据表。

与连接有关的关键字:

INNER JOIN 、OUTER JOIN ( LEFT和RIGHT)、FULL JOIN、CROSS JOIN

 

示例1,根据数据表中记录的性别来显示男女。

select id,name,

CASE sex

WHEN 1 THEN '男'

WHEN 0 THEN '女'

END

AS 性别

FROM student

在创建数据库的同时添加外键与级联动作

比如:现创建一张工资表与员工表并建立级联关系。就是说当员工信息被删除的时候,其工资记录一起被删除。

CREATE TABLE EMoney(

mid int identity primary key not null,

mtime datetime,

howMuch float not null,

eid int not null,

CONSTRAINT FK_money_give_to_employee

FOREIGN KEY(eid)

REFERENCES employee(eid)

ON UPDATE NO ACTION

ON DELETE CASCADE

--当主键列的相关数据被删除后,外键列的相关数据也一起被删除

)

其中,CONSTRAINT 与FOREIGN
KEY、REFERENCES语句就是创建外键并声明数据的依赖关系。

基础概念

当数据库在查询的那一瞬间,它实际上是将数据记录有序的一行行取出来,那么游标在这个过程中的作用,就是表示当前正在读取的是第几行。

在sql server中游标的生命周期,由个5部分组成。

数据表的自引用

就是约束一个张表中的某个字段的值必须符合另一个字段的已存在的值的范围。

比如说现有一张员工表,员工表中字段如下(员工id、员工姓名、上级领导id),在此我们可以约束“上级领导id)”必须属于“员工id”的范围内。

create table employee(

eid int identity primary key not null,

eName nvarchar(10),

lindaoID int

FOREIGN KEY REFERENCES

employee(eid)

)

注意:创建自引用的方法与创建外键的方法一样,区别是表名与字段都是当前表中的。

同样用ALTER语句也可以添加自引用

ALTER TABLE employee

ADD CONSTRAINT

FK_linDao_Must_Be_employee

--自引用的名字

FOREIGN KEY (lindaoID)

--指定当前表的字段

REFERENCES employee(eid)

--指定与哪个字段建立自引用关系

键约束

主键、外键、替换键、倒置键

修改字段名

EXEC sp_rename ‘表名.原字段名’ , ’新字段名’ , ’COLUMN’

示例:

EXEC sp_rename 'student.createTime','regTime','COLUMN'

SQL server profiler

当我们的数据服务端出现问题和故障的时候,它可以给我们提供实时的跟踪工具,和性能监控的作用。

if语句

在一个或多个条件的判断下控制流程的走向。可以配合and、or等逻辑运算符来。

getDate()

得到当前时间,比如:

select GETDATE()

很多时候在需要为日期类型的字段添加当前时间为默认值的时候需要使用到该函数。

SAVA TRAN

创建保存点,以便我们在事务回滚的时候引用它。

完全连接

完全连接( FULL JOIN 或 FULL OUTER JOIN )

用于显示所连接的所有表的所有数据,即使这条数据没有任何关联关系。

select *from student  s FULL JOIN classInfo  c  ON s.cid = c.cid

禁用触发器

disable trigger [触发器名] on 表名

示例:

disable trigger mytrgger3 on student

非聚集索引

比如:对于新华字典来说,它有两种补充性的排列方式,按偏旁部首、按笔画。

非聚集索引是指,非物理上的实际排列方式的逻辑目录顺序的索引。

对于数据表而言,创建了主键之后,其他的索引都是非聚集索引。

一张表中最多可以添加249个非聚集索引。

聚合函数

AVG() 求平均值

SUM() 求合

MIN()/MAX() 求最大最小值

COUNT() 统计行数

示例:

alter view View_1 as select id,name,age from student where age>20

修改触发器

运算符

+、-、*、/

运算方法:

比如:求圆面积

select 3.1415926 *4*4

得到一张表中的所有字段的信息

select * from syscolumns where id=OBJECT_ID('Customer')

二进制数据类型

数据类型

值范围

存储空间

bit

null , 0 和 1

1比特

binary

固定长度的二进制数据

8000字节

varbinary

可变长度的二进制数据

最大8000字节

image

可变长度的二进制数据

最大2G

 

另外还有xml、table类型。

UPDATE语句

update dbo.student set name='小白龙' where id = 14

分区视图

这种视图可以在一台或多台数据库服务器上连接一组相关的数据表,以达到像是在操作一个数据表的效果。这是实现分布式数据库的一种方式。

ON

如果数据库由多个部分组成,我们可以指定数据表存储在哪个部分。

随堂练习

1、创建一张学生数据表,包含字段id(int)、name(nvarchar)、age(int)、sex(bit)、address(nvarchar)、phone(char)、classNum(int)。

2、 创建一张班级信息班,cid(int)、className(nvarchar)、teacher(int)。并添加两以上数据。

再创建一张老师表,id(int)、name(nvarchar)、age(int)、phone(char)。并添加两以上数据。

1、 用一条insert语句一次性插入十条以上学生信息,并且省略字段名。

2、 用一条select语句查询学生表ID为2到ID为4之间的记录,(用BETWEEN关键字)。

3、 用一条select语句查询出所有姓王的同学(用LIKE模糊查询)。

4、 用一条select语句查询出班上年龄为(16、17、23、24)的同学

5、 对学生姓名添加唯一索引

6、 创建视图,将学生表、班级表、教师表连接为一个视图。以学生表为主表。

7、 查询这个视图,显示所有男生的姓名、年龄、班号、教师姓名。

sqlServer_存储过程

存储过程是一系列SQL代码集,相当于是将我们输入的多条SQL语句保存为一个函数。

示例1:

BEGIN TRAN --开始事务

DECLARE @errorSum int --定义错误计数器

SET @errorSum=0

update student set age = 11 where id=1

--在事务中操作SQL语句

SET @errorSum=@errorSum+@@ERROR

--@@ERROR是上一次t-sql发生的错误的编号

--此处用来判断是否有出错,没错时@@ERROR的值为零

--有错时就将错误编号进行累加

exec jfdsa --执行一个不存在的存储过程来人为制造错误

SET @errorSum=@errorSum+@@ERROR

if @errorSum<>0

begin

print '有错误,事务开始回滚'

ROLLBACK TRAN --事务回滚

end

else

begin

print '成功,事务已提交'

COMMIT TRAN --事条提交

end

5、释放游标

DEALLOCATE 游标名称

修改表名

EXEC sp_rename ‘原表名’,’新表名’

示例:

EXEC sp_rename 'student','studentInfo'

DROP语句

删除数据库对象,比如:删除数据表、视图、存储过程、触发器

语法:

DROP <数据对象> <数据对象名>

删除索引

DROP INDEX 表名.索引名

示例

drop index student.IX_AM

用途:

1、在循环语句中记录循环的次数或者用于控制循环的条件。

2、控制流程语句的走向。

3、存储函数或存储过程的返回值。

convert()

convert( 目标数据类型(长度) , 需要被转换的数据或字段名 ),示例:

select '我们班上有'+CONVERT( varchar(2),10)+'个同学'

where子句

where运算符

=,>,<,>=,<=,<>,!=,!>,!<

<>表示不等于,!>不大于。

AND 、OR、NOT

 

BETWEEN

select * from student 

where age BETWEEN 13 AND 19

 

查询指定的数据值是否在第一个值和第二个值的范围内。

LIKE

select * from student 

where name LIKE ‘%小%’

 

 

模糊查询,可以使用通配符,

%用来表示任意个任意字符,

_ 下划线用来表示一个字符。

 

select * from student 

where name LIKE ‘_白’

 

 

 

IN

是指从一个集合中去逐一匹配,只要数据值在集合中能找到相同的项,where条件就成立了。

 

select * from student 

where name IN (‘小张’,’小黑’,’小平’,’小李’)

 

———————————-

select * from student 

where name IN (select name from student where age <20)

 

 

 

 

 

EXISTS

用来判断一个子查询是否有结果,当子查询返回了至少一个结果时,where条件成立。

 

select * from student 

where exists(select * from student where age =99)

 

 

 

CREATE TABLE创建数据表

CREATE TABLE 数据表名

创建表之前确定是否已经选择当前数据库

 

col_length(表名,字段名)

返回表中的字段的长度,示例:

select col_length('Product','ProductName')

NOT FOR REPLICATION

就是指对这个表进行复制的时候,ID主键的值是重新排列,还是延用之前的ID

索引的用途和作用

是提高数据查询的性能和效率。

比如:我们按照用户年龄创建了索引。

执行用户年龄的查询操作时,性能会有很大的提升。

select age from student order by age where age>20

替换触发器

当用户执行某种操作开始之前,被触发的触发器,这种触发器可以阻止或用指定的操作来替换原来的操作。

视图结构的修改

实际上就是修改生成视图的select语句

Alter view 视图名 as 新查询语句

练习:

用上例中的表,假设毕业年龄22,判断年龄字段发生更新,其值更新为大于等于22的时候,就自动判断这个学生已经毕业了,因此删除这个学生的学生表和借书表中的相关数据记录。

1、 如何判断你更新的是年龄字段?

if update(age)

2、 如何明确刚刚被修改的是哪一条记录。

select age from deleted

select id, age from inserted

我们可以通过上述两条代码得到更新前后的年龄,和所更新记录的id。

create trigger studentAgeChange on student for update

as

if update(age) --判断是否是指定的字段的值发生了改变

begin

--需要变量:修改后的年龄、学号、id

declare @age int,@sNum int,@id int

set @age = (select age from inserted)

set @sNum = (select sNum from inserted)

set @id = (select id from inserted)

if @age>= 22

begin

delete from student where id=@id

delete from borrowRecord where sNum=@sNum

end

end

临时表与表变量

交叉连接

交叉连接在本质上,也可以看做是一种内连接。只显示有一关联的数据。

注释

多行注释 /* 被注释的内容
*/

单行注释 — 被注释的内容

对使用游标时性能上的一些建议

1、 游标对性能的耗费非常严重的。

2、 用完游标之后一定要关闭和释放。

3、 尽量不要在数据量较大的时候使用游标。

4、 尽量使用FAST_FORWARD快速只进方式来操作游标。

5、 使用游标经常会比直接select 查询慢2-3倍,如果数据量较大这个比例还会增加。如果可以用其他方法解决的问题尽量避免使用游标,它只是最后的一种选择。

print

一次只能输出一个变量: print @num1

按单列或多列分类

通过SQL语句来创建外键

练习:

1、 现有学生表如下(age、name、phoneNum、sex(nvarchar))

限制age 必须0到50。

限制phoneNum必须是11位数字

限制性别只能输入“男”或“女”

示例:

declare @age int;

set @age=61

if @age<12

print '儿童'

else if @age<20

print '少年'

else if @age<30

print '青年'

else if @age<50

print '中年'

else

print '中老年'

sql server中的索引的分类

语法:

BEGIN TRY

执行存储过程

END TRY

BEGIN CATCH

……

END CATCH

只有当try中的语句发生错误的情况下,才会去执行CATCH中的语句。

创建存储过程

SQL Server中的数据类型

二表连接,示例:

select * from student INNER JOIN class ON student.cid = class.id

SQL Server Management Studio

它就是SQL server的图形化的管理界面,也就是客户端。

语法:

cteate table #临时表名(字段列表)

多表连接,示例:

select student.name,classInfo.className,teacher.name from student
INNER JOIN 
classInfo  ON student.cid = classInfo.cid
INNER JOIN 
teacher ON classInfo.teacher= teacher.tid

输出变量

while循环

当条件为true时执行循环代码,当条件为false时退出循环

declare @num1 int

set @num1=0

while @num1<10

begin

print @num1

set @num1=@num1+1

end

ON UPDATE NO ACTION

NO ACTION就是指不执行任何执行,默认值。

select语句

语法:

SELECT 字段列表 FROM 表名

非唯一索引

在一个字段中,可以存在相同的数据。

一次插入一行数据

insert into student (name,age,sex,address,phone) values('小宝',13,1,'城革大本营',12345678)

临时禁用约束

ALTER TABLE employee

NOCHECK

CONSTRAINT CK_ageMoreZero

--这里要指定约束的名字

字符数据

数据类型

存储空间

char(n)

每字符1字节,最大可以存储8000字节

varchar(n)

每字符1字节,最大可以存储8000字节

text

每字符1字节,最大可以存储2GB

nchar(n)

每字符2字节,最大可以存储4000字节

nvarchar(n)

每字符2字节,最大可以存储4000字节

ntext

每字符2字节,最大可以存储2GB

说明:

1、 其中支持Unicode字符集的以n开头。

2、 我们可以用varchar(max),表示可变长度。

练习:

创建一个表变量用于存放彩票号码,有两字段(id,num char(8))。

用于随机函数生成50个彩票号码,存入这个表变量中。然后显示出所有的彩票号码。

用户自定义函数UDF

user define function

它非常类似于存储过程或者js中的function,通常而言它都是配合select语句来进行使用的,它的用法很像针对某个字段进行操作的聚合函数。

语法:

waitfor delay ‘hh:mm:ss’ --时分秒

手动在SQL management中添加引用

右击指定数据表弹出右键菜单
à ‘设计’ à 在编辑表结构界面空白处没点击右键
à “索引/键” à点击“添加”来创建新的索引 à 在“列”选项中选择对哪一个字段进行排序,以什么方式排序。

修改数据库大小

ALTER DATABASE test MODIFY FILE (SIZE = 500MB)

注意:不能变小,只能增大它的容量。

标准视图

就是由一个或多个物理表通过条件查询语句组成的视图,理论上所有用select语句查询出的结果集都可以用来生成视图。

并且,我们对视图中的数据进行修改时会直接影响到其原来的物理数据表。

如何给一张没有主键的表添加主键

ALTER TABLE Table_1

ADD CONSTRAINT PK_table111

PRIMARY KEY (id)

2、打开游标

open 游标名称

游标需要打开才能够使用

RollBack tran

回滚事务,在没有保存点的情况下,回滚到事务最初没有执行操作时的状态,在有保存点的情况下,可以回滚到保存点。

GO指令

代表开始运行,GO之后的语句属于另一个批次的代码。

declare @num1 int

set @num1=10

go

select @num1+1

执行报错,这是因为用户定义的局部变量只能够在同一个批次中有效,而go指令将代码分隔成了两个批次。

练习:

创建一个班级表,并与学生表建立级联关系。要求删除班级的时候,这个表中的学生信息也同时被删除。

唯一约束

就是约定一个字段中的值不能重复,每一个值都是唯一的。

创建数据表的示例:

use testStudent2;

CREATE TABLE student(

sid int IDENTITY PRIMARY KEY NOT NULL,

sName nvarchar(50) NOT NULL,

sAge int,

sSex bit  DEFAULT 0 NOT NULL,

sYW float DEFAULT 0 NOT NULL,

sSX float DEFAULT 0 NOT NULL,

sCount AS sYW+sSX

)

 

更新示例:

通常发生在相关联字段的值发生了改变。

--当学生的学号发生发生改变的时候,借书记录表中的学号也要同时改变,以保证数据仍然与这学生相关联。

create trigger studentNumChange on student for update

as

if update(sNum) --判断是否是指定的字段的值发生了改变

begin

--同时更新借书记录表中的学号

update borrowRecord set sNum = i.sNum

--将借书记录表中的学号改为修改之后的学号

From borrowRecord as br,deleted as d, inserted as i

where br.sNum = d.sNum

--找到借书记录表与更新前的学号相同的记录

--更新操作会同时影响Deleted和inserted这两张表

end

其中:

在执行触发器的update操作的时候,Deleted和inserted这两张表会以更新的一瞬间,先将更新之前记录放入Deleted表中,然后将更新后的记录放入inserted表中。因此我们就可以通过读取这两张表的内容,得到所更新记录的原值和新值,以及这张记录的id

查看当前数据库有多少个触发器

select * from sysobjects where xtype=’TR’

--实际上就是读取了系统数据表,因为触发器的内容就是存放在系统数据库中的。

示例1:

create table #tt(

id int,

name nvarchar(50),

age int

)

insert #tt select 1,'小强',17

union all select 2,'小明',19

select * from #tt

BEGIN TRAN

表示一个事务单元开始,在此之后没有提交的所有语句都属于事务的一部分。

ALTER TABLE 修改数据表

最常见的操作就是修改数据表名和表中的字段。

 

数据库相关的内容

示例:

CREATE PROC  addNum 

@num1 int,@num2 int

AS

select @num1+@num2

execute addNum 25,13

语法:

Alter trigger 触发器名 on 表名 for 操作类型

AS

若干T-SQL语句

GO

示例:

ALTER trigger mytrgger3 on student for update

as

print '这是修改之后的update触发器'

字段约束

对字段中输入的数据进行规则的限制。

主键约束

确保主键的值是唯一的。

示例3,按照学生的年龄来判断是否成年

SELECT ID,NAME,

CASE

WHEN AGE>18 THEN '成年人'

WHEN AGE<=18 THEN '未成年人'

END AS 成年否

FROM student

从上面例子中我们可以看出,CASE语句能够在select查询数据表的时候,通过条件来判断相应字段的值,并按条件自定义返回结果。

分离数据库

数据库默认的存储位置

C:Program FilesMicrosoft SQL
ServerMSSQL10.MSSQLSERVERMSSQLDATA

如果我们需要将它移动位置的话,就需要首先分离数据库:

右击数据库图标弹出菜单à任务à分离à弹出分离数据库窗口à选中”删除连接”à确定

这样我们就可以复制和剪切数据库了。

Try……Catch语句

当我们执行程序出现错误的时候,一般都会报错,并且停止执行。但是如果在try语句的范围内出错的话,程序会继续运行,并且将错误信息在catch语句范围内进行处理。

游标变量

我们可以在定义时先对游标变量赋值,或者定义完之后再赋值。

--在定义时直接赋值

declare myCursou cursor for

select id,name from student



--先定义后赋值

declare @myCursou cursor

set @myCursou = cursor for select id,name from student

创建带默认值的输入参数的存储过程

带有默认值参数,可以不输入具体的参数值,在不输入值使用默认值。

语法

CREATE PROC[EDURE] 存储过程名

[@参数名1 数据类型=默认值 ]…

AS   SQL语句序列……

修改字段类型

ALTER TABLE 表名 ALTER COLUMN 字段名 类型

示例:

ALTER TABLE dbo.student

ALTER COLUMN sAge nvarchar(30)

查看数据库信息

EXEC sp_helpdb ‘test’

以类似查询语句的结果集的方式返回数据库的大小、拥有者、创建日期、文件路径等信息。

 

常用全局变量

根据约束范围

实体约束

域约束

参照完整性约束

示例:

declare @mytable table(

id int,

name nvarchar(50),

age int

)

insert @mytable select 1,'小强',17

insert @mytable select 2,'小明',13

union all select 3,'小红',18

union all select 4,'小丽',19

select * from @mytable

规则

规则与check约束是非常类似的,它们的区别是规则只能限制一个字段,但是规则定义一次,可以多次使用。

就比如:年龄不能为负数,这个规则可以应用于客户表、员工表、学生表。

数据类型转换函数

修改数据表结构

很多时候我们需要修改数据表字段结构,比如添加字段、修改字段类型和字段名,但是SQL
server默认情况下会阻止我们对数据表结构的修改。所以我们需要更改SQL Server的设置参数。

工具菜单à 选项à 打开”选项”弹窗中的”Designers”选项卡à取消”阻止保存要求重新创建表的更改”前面的选中状态。

语法:

SAVA TRAN[SACTION] [<保存点名称>] [<@保存点参数>]

注意:当我们回滚事务的时候,保存点会自动被清除,即使用户保存了多个保存点,执行ROLLBACK时也会被全部清空。

如果此时还需要保存点的话,只能重新创建
SAVE TRAN了。

发表评论

电子邮件地址不会被公开。 必填项已用*标注