您的位置:永利集团登录网址 > 计算机入门 > 我的ASP.NET学习笔记011SqlServer基础知识

我的ASP.NET学习笔记011SqlServer基础知识

2019-10-05 09:39

sqlServer_基本功概念

  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:求平平均数量 马克斯:求最大值 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

在互连网笔试中,常碰到数据库的问题,遂来差不离总括,注意,以 Sql Server 数据库为例。

SQL server的处理工科具

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

数据库

数据库系统,Database System,由数据库和数据库管理类别结合。
数据库,DataBase ,是Computer应用系统中的一种特地管理数据财富的系统,依据数据结构来组织、存款和储蓄和治本数据的客栈。数据表是最中央的数据库对象,是储存数据的逻辑单元。

数据库管理类别,DataBase Management System,DBMS,管理数据库,担负数据的存储、安全、一致性、并发、恢复生机和访谈。

数据模型,经常由数据结构、数据操作和完整性约束三部分构成。

SQL server联机丛书

最初菜单à Microsoft SQL Server 二〇〇九 à 文书档案和学科 à SQL Server联机丛书

SQL Server 配置管理器

用来运转和治本SQL server数据库的服务端,以及别的连锁效能。

当大家运营SQL Server 配置管理器之后,能够在右臂目录中见到“SQL Server服务”,在“SQL Server服务”里,我们就能够对SQL Server的服务端,也正是骨干数据引擎进行保管。

里头“SQL Server (MSSQLSE昂CoraVE帕Ramela)” 和 “SQL Server (SQLEXPRESS)”正是代表我们所设置的求实的服务端,后面贰个是规范版,前面一个是体验版。

开辟Server配置管理器的另一种艺术:

“小编的Computer”à右键菜单à处理à”服务和行使”àSQL Server配置管理器

  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 + 两千00 where chvAccountName='宝山钢铁集团公司'
    --通过系统变量@@error可以收获上次被施行的sql是还是不是奉行成功,倘诺实践成功@@error的值为0,不然为1
    set @sumError = @sumError + @@error
    update tblaccount set mnycurrentmoney = mnycurrentmoney - 300000 where chvAccountName='安阳钢铁公司公司'
    set @sumError = @sumError + @@error
    --推断是或不是在施行进程中冒出错误
    if(@sumError<>0)
    begin
    print '事务实践倒闭,就要回滚'
    rollback transaction
    end
    else
    begin
    print '事务实施成功,将要交付'
    commit transaction
    end

SQL语言

结构化查询语言,Structured Query Language,SQL是一种数据库查询和次序设计语言,用于存款和储蓄数据以及询问、更新、管理关全面据库系统,高等的非进度化编制程序语言。Transact-SQL是微软对SQL的扩张,具备SQL的严重性特点,同期扩大了变量、运算符、函数、流程调控和注释等语言因素。
SQL语言分四类:数据定义语言[DDL]、数据查询语言[DQL]、数据操纵语言[DML]、数据控制语言[DCL]。

– [1].DDL(Data Defination Language)
      style="color: blue;">创设和处理数据库中的对象,定义SQL形式以及数据库、表、视图和目录的创设和收回。不需COMMIT。
     创建CREAT,  修改ALTER,  删除DROP,  删除TRUNCATE
     TRUNCATE,  RENAME

– [2].DQL(Data Query Language)
     基本结构: SELECT子句、FROM 子句、WHERE子句组成查询块。
     SELECT<字段名表>,  FROM<表或视图名>,   WHERE<查询条件>

– [3].DML(Data Manipulation Language)
      style="color: blue;">直接操作数据表中的数量,根据需求寻觅、插入、删除数据以及更新数据库.
     操作的单位是记录。DML必要COMMIT显式提交。
     插入INSERT,   删除DELETE,  更新UPDATE

– [4].DCL(Data Control Language)
     用于授予或收回对客户对数据库对象的拜见权限,保证数据安全性。
     授权GRANT,  裁撤授权REVOKE,  显式限制权力集结DENY

政工调控语言 - TCL (Transaction Control Language)
交付COMMIT,回滚ROLLBACK,设置保存点SAVEPOINT
SQL>COMMIT:显式提交
SQL>ROLLBACK:回滚命令使数据库状态回到上次最后交给的情形
SQL>SET AUTOCOMMIT ON:自动提交
动用SQL命令直接完结:隐式提交。

1. 数据类型

 1. 字符数据类型
  a. 字符串:char、varchar、text;
  b. Unicode字符串:nchar、nvarchar、ntext,用N标志,unicode是统一字符编码规范, 双字节对字符(斯洛伐克(Slovak)语,汉字)编码;
   使用Unicode数据类型,能够最大限度地解决字符调换的标题。
 2. 数字数据类型
  a. 整数型:tinyint(1)、smallint(2)、int(4)、bigint(8);
  b. Decimal和numeric:固定精度和小数位数,decimal(p,s)或numeric(p,s),0≤s≤p;
  c. 货币类型:smallmoney(4)、money(8);
  d. 近似数字:float、real(4);
  e. bit类型:0/1序列;
 3. 日期和岁月数据类型
  time、date、smalldatetime、datetime、datetime2、datetimeoffset;
 4. 二进制数据类型
  binary、varbinary;
 5. 别的数据类型
  uniqueidentifier:16字节的十六进制数字组合,全局独一,
  sql_variant:支持各样数据类型;
  还会有xml、table等,其它还可以自定义数据类型。

2.1 函数

置于函数详细介绍参照他事他说加以考察:行集函数、聚合函数、排行函数、标量函数 可能数据库书籍。
函数消息查询
   a. 工具栏“支持”- -> “动态接济”;
   b. 初叶“文书档案教程”- -> “SQL server 教程”
系统函数 ~ 自定义函数
a. 系统函数
   允许客户在不直接访谈系统表的图景下得到SQL系统表的新闻。
b. 自定义函数:User Defined Function
优点

  • 模块化设计;
  • 实行进度快,缓存布置下跌编写翻译开销、没有须求再度深入分析和优化;
  • 缩减互联网流量;

分类

  • 标量型函数:Scalar Function,只好回到标量值;
  • 内联表值型函数:Inline table-valued Function,参数化的视图,只能回去 TABLE 类型;
  • 多证明表值型函数:Multi-Statement Table-Valued Function,标量型函数和内联表值型函数的构成;

创建

  create function 函数名(@参数名 参数类型, [..])
      returns 返回值类型
   as
   begin
      SQL语句;
      return 返回的对象;
   end

注:begin…end 块中的语句不可能有其余副功能。
查询
函数的定义、架构等。
修改/删除
alter/drop function 函数名

2.2 关键字

  a. set ~ select
  select援救在三个操作内同时为三个变量赋值,可是为变量赋值和数据检索不能够同一时间扩充,仿效 二者的分歧;
  b. cast() ~ convert(): 类型调换函数
  · cast(源值 as 目标项目);
  · convert(指标数据类型,源数据[, 格式化代号]),能够格式化日期和数值;
  c. delete ~ drop ~ truncate
  · delete:DML,删除数据表中的行(一行或全部行)/记录,自动隐式commit,不可能回滚;
        delete from 表名 where 条件
  · drop:DDL,显式手动commit,可以回滚,删除数据库、数据表或删除数据表的字段;
        drop table 表名
  · Truncate:飞快、无日志记录,删除数据表中的数据、不删除表,不可复苏;
        truncate table 表名
  从删除速度来讲,drop> truncate > delete,其余差异详细参考 delete ~ drop ~ Truncate。
  d. insert
  注意区分上边2个insert语句的区分,第一种Product格式,values中必得付出相应的值,当中国和东瀛期系统暗中同意一九零零-01-01;第三种格式,values中采用default约束。

   insert into Product(productName,productPrice,productStorage,productDate,productClass) 
                 values('电冰箱', null, 0, '', 1)
   insert into Product(productName,productClass) values('电冰箱',1)

  批量插入数据
  [1]. insert into 指标表表名或列视图 select 检索语句 from 源表名
  [2]. select 列列表 into 指标表表名 from 源表表名     
  e. waitfor
   定时、延时或堵住执行批管理、存款和储蓄进程或作业。  

3. 数额库表设计难题

  常用表操作格式 
  [a]. 创建数据库与表 
   create database/table 数据库名/表名 
  [b]. 查看表信息 
   exec sp_help 表名   
  [c]. 添加新列、修改列名与类型 
   alter table 表名 
    add 列名 列类型 
   exec sp_rename ‘表名.列名’, ‘新列名’ (注意必须加引号) 
   alter table 表名 
    alter column 列名 新的列数据类型     

  a. E-R模型图
 实体-联系(Entities-Relationships)模型,描述概念数据模型的办法之一,软件生命周期的设计阶段,提供实体、属性、联系的面向客商的表明方法,实体之间存在卓越、一对多、多对多的交换。
  b. 涉及标准化 - 数据库完整性
  三大范式:
  · 第一范式 1NF:全体属性(值)是不可分割的原子值;
  · 第二范式 2NF:全体属性数据必得注重主键;
  · 第三范式 3NF:数据库表中无法包涵已在任何表中包涵的非主键音讯;
 关系型数据库三大完整性:
  · 实体完整性:主键约束 primary key,独一且非空;
  · 参照完整性:征引完整性,外键约束 foreign key 等涉嫌约束;
  · 客户自定义完整性:域完整性,字段类型等;
  c. 分区表
 依照数据水平格局分区,将数据布满于数据库的多个例外的文书组中:
  - 改良大型表以及有着各个访谈情势的表的可伸缩性和可管理性;
  - 对于多CPU系统,帮忙相互方式对表操作;
 分区函数~分区方案:

  create partition function 函数名(分区条件) 
   as range left/right for values() 
  create partition scheme 方案名 
   as partition 函数名

 贰个分区方案不得不用贰个分区函数,贰个分区函数能够被八个分区方案共用。
  d. 文件组 
 在数据库中对文件进行分组的一种管理机制,贰个文书不能够是三个公文组的积极分子。文件组只好分包数据文件,事务日志文件无法是文本组的一片段。使用文件组可以凝集客商对文本的依靠,通过文件组直接管理文件,能够使得同一文件组内的文本布满在差异的硬盘中,能抓实IO质量。
 具体地可参照他事他说加以考察 文本和文件组。
  e. 标识符
 每一行数据必得皆有二个唯一的可分其余习性作为标记符。
  · identity:本地(表内)独一,使用情势identity(最早种子值,增量);
     select @@identity:查看新插入行数据的标志符(的序号)  
     select $identity from 表名:援引(呈现)表的头一无二标记符列  
  · uniqueidentifier:全局独一,应用rowguidcol属性作为标志符提醒新列为guid列,默肯定义使用newid或newsequentialid()函数生成全局独一值;同理,使用$rowguid引用独一标志符列。
     ID uniqueidentifier default newsequentialid() rowguidcol   

SQL server profiler

当大家的数据服务端出现难题和故障的时候,它能够给大家提供实时的追踪工具,和总体性监察和控制的效应。

 f. 主键 PK ~ 外键 FK 

 主键:保证全局独一性;
 外键:创立和巩固四个表数据里面链接的一列或多列,强制援引完整性,能够有效防御误删;
  主键约束 ~ 外键约束 ~ 独一约束
  - 主键约束
  主键约束用于落实实体完整性,各种表的主键有且只可以有三个,主键列不能够包括null值。声明联合主键选取第2、3种艺术。创造PK约束,具体参见大话数据库或 两种艺术创立主键约束;
  系统暗中同意生成的主键约束名叫:PK_表名_队列串号
  - 外键约束
  外键约束用于落到实处参照完整性,四个表A:foreign key指向另多个表B:primary key,表B是主表,表A是从表。外键约束创制二种艺术,参见大话数据库或许 三种办法创设外键约束;
  系统默许生成的外键约束名字为:FK_表名_字段名_队列串号
 示例主/外键的两种创制方法:
  1. 创制table时,直接在字段前边注解为 primary key 只怕 foreign key

1  create table orders(
2         orderID varchar(10) not null primary key,
3         orderProduct varchar(30) not null,
4         personID varchar(20) foreign key references persons(personID)
5   );

  2. 创制table时,全体字段注明之后,增加主键和外键的牢笼语句

1  create table orders(
2         orderID varchar(10) not null,
3         orderProduct varchar(30) not null,
4         personID varchar(20) not null,
5         constraint PK_orders primary key(orderID),
6         constraint FK_orders_personID foreign key(personID) references persons(personID)
7  );

  3. 在table已开立后,为表增多主外键约束

1  alter table orders
2         add constraint PK_orders primary key(orderID),
3              constraint FK_orders_personID foreign key(personID) references persons(personID) 

  - not null 约束
  强制列不接受null值,具体使用参考上述代码。
  - default 约束
  用于向列中插入暗中同意值,default只好用来insert语句且不能够与identity同分外候用,具体使用仿效如下示例代码:
  1. 创制table时,直接在字段后边注脚为 default

1  create table Certifications(
2      certID int not null primary key identity(1001,1),
3      certName varchar(20) not null,
4      certPassword varchar(20) default('12345678'),
5      certTime varchar(30) default(getdate())
6  );

  2. 注意,default约束不设有此种方法;
  3. 在table已开立后,为表加多暗许约束

1  alter table Certifications
2      add constraint DF_Certifications_certPassword default '123456' for certPassword,
3           constraint DF_Certifications_certTime default getdate() for certTime

  - check 约束
  用于限制列中的数据的范围,为八个列定义check约束选拔第2、3种方法,具体方法如下:
  1. 创办table时,直接在字段前面加多:check(条件表明式)
  2. 创建table时,全部字段评释之后增多:constraint CHK_表名_字段名 check(条件表明式)
  3. 在table已开立后,为表增加check约束

1   alter table 表名
2       add constraint CHK_表名_字段名 check(条件表达式)

  - unique 独一约束
 用于独一标志表中的每条记下,通过独一性性索引强制实体完整性,unique算是对primary key的互补,不过每种表可有七个unique约束且允许null值,创造unique约束的3种方法可参照上述办法:

1  [1].unique
2  [2].constraint UNQ_表名_字段名 unique(字段名)
3  [3].alter table 表名
4        add constraint UNQ_表名_字段名 unique(字段名)

  总结
  · 获取table的封锁消息:exec sp_helpconstraint 表名
  · 裁撤上述各类束缚:alter table 表名 drop constraint 主/外键约束名 
  · 关闭/开启约束检查测量试验:nocheck/check constraint 约束名/all
  · 若表中已存在多少,在丰盛封锁在此以前先接纳with nocheck能够禁止对已有数量的检查实验。
  · 级联更新/删除:on update/delete cascade

4. 或多或少高等搜索本事

where ... union ... group by ... having ... order by ... (limit) ... 

  a. 分组工夫
  SQL Server 之三种分组本事介绍
  · group by
  在select中作为分组条件的列名必定借使在group by子句中选拔的列列表中。

       select 作为分组条件的列名 聚合总计函数(被总括字段列) from 表名 group by 用于分组的列列表(A,B,C) 

  优先级:C > B > A
  · having
  having 与 where 语句看似,where 是在分拣此前过滤,having 是在分拣之后过滤,且having条件中平日包括聚合函数。
   group by … having … order by …  
  · rollup ~ cube
  rollup突显所选列的值得某一档期的顺序结构的集聚,cube展现所选列的值得全体组成的集中,且更为细化;两个均要求和group by一齐用。
  具体不同详解见:rollup ~ cube,rollup ~ cube - 2
  b. 联机查询
  · union
  并集,用于整合2个以上的结果集,私下认可去重,union all不去重。不过有列类型和列数量是还是不是相应一致的范围。 
  c. 连续查询
   连接是关系型数据库模型的严重性特征,通过连接运算符来实现多少个表的联表查询,灵活,语句格式:

   select 表名.列名[列列表...]
      from table_A 连接运算符 table_B [on 联表查询的匹配条件]

  注意,在连接表查询中学会使用外号。以下可参照他事他说加以考察 接连查询简例,三番五次关系暗意图。
  · 内连接
  inner join,也即经常连接,满含等值连接、自然连接、不等连接。重返的询问结果会集仅仅是select的列列表以及切合查询条件和连接条件的行。当中,自然连接会去掉重复的属性列。  
  · 外连接
  outer join,包蕴左外连接、右外连接和完全连接。再次来到的询问结果群集不独有富含select的列列表以及相符查询条件和三番五次条件的行,还富含左表(左连接)、右表(右连接)或多少个连接表(完全连接)中的全体数据行。

      A left join B == B right join A;   
  · 交叉连接
  cross join,连接表中具备数据的笛Carl积,结果集的数额行数 = 第贰个表中适合查询条件的多寡行数 * 首个表中适合查询条件的数码行数。cross join后加条件只好用where,无法用on。  
  · 自连接
  连接主要字的两边都是同三个表,将本身表的二个镜像充任另一个表来看待。自连接可以将索要四回查询的说话综合成一条语句三回施行成功。仿照效法示例:自连接查询,也可参见大话数据库中有关自连接的例证。
  d. 子查询
 即内部查询(inner query),子查询正是坐落select、update或delete语句中内部的查询。子查询在主查询试行此前实践三遍,主查询使用子查询的结果。参谋示例:子查询,各个查询总括. 

  select select_list from table1
      where expression operator(select select_list from table2);

  · 单行子查询
  再次回到零行或一行。单行相比较运算符:= ,>, >= ,< , <= ,<>。
  · 多行子查询 
  再次回到一行或多行。多行相比运算符:IN/NOT IN,ANY/ALL,EXISTS。
  ANY:相称子查询获得的结果集中的大肆一条数据;
  ALL:相配子查询拿到的结果聚集的整套数据;
  EXISTS:重临bool值,只检查行的存在性,而IN检查实际值的存在性(平常情形EXISTS质量高于IN)。
  f. 索引
  此处将引得拿出去作为单身的一章实行总括学习,如下。

5. 索引

目录是对数据库表中一列或多列的值进行排序的一种结构,快捷有效查找与键值关联的行,加快对表中记录的查找过滤或排序。索引选择 B树 结构。
优点:
 (1)快速搜索读取数据;
 (2)保险数据记录的独一性;
 (3)达成表与表之间的参谋完整性,加快表和表之间的连接;
 (4)在选择order by、group by子句实行数据检索时,利用索引能够减去排序分组时间;
 (5)通过运用索引,能够在查询的进度中,使用优化遮蔽器,提升系统的习性;
  缺点:
 (1)扩充了数据库的蕴藏空间;
 (2)创立索引和保卫安全索引要消耗费时间间;
 (3)插入和修改数据时要开销比较多日子更新索引;
 (4)修改品质和寻觅品质是互相顶牛的;
分拣:依据目录的一一与数据表的大要顺序是或不是一律
 · 聚焦索引
  索引的逐个与数据表的大要顺序同样,升高多行追寻速度。二个表只好包涵叁个集中索引。聚焦索引的叶级是数据页,数据值的各种总是遵照升序排列。在创制任何非集中索引在此以前先创建聚焦索引。集中索引的平分大小约为数据表的5%。
 · 非集中索引
  索引的依次与数据表的概况顺序差异,单行检索快。二个表最多2五十个非聚焦索引。非集中索引的叶级是索引页。索引页中的行标志符(或聚焦键)指向数据页中的笔录(或表的聚焦索引,再通过聚集索引检索数据),行标记符由文件ID、页号和行ID组成,况且是独一的。数据堆通过动用索引分配图(IAM)页来维护。
特征:
 · 唯一性索引
 保证索引列中的全体数额是有一无二的。只好在能够保障实体完整性的列上创设独一性索引。
 · 复合索引
  八个目录成立在2个或多个列上。不能够跨表建构复合列。注意列的排列顺序。复合索引能够拉长查询质量,减少在八个表中所制造的目录数量。复合索引键中最多可以结合16列。
创制索引:
 · 直接开立:索引创造向导或create index
 基本方法,灵活易扩充、优化索引。语法:

 create [unique][clustered|nonclustered] index 索引名  
   on {表|视图}(列 [asc|desc][,...n])

 · 直接创设:利用约束直接创设
 主键约束 - -> 独一性集中索引,独一性约束 - ->独一性非集中索引。
 利用约束成立索引的事先级高于create index语句创设的目录。
保养索引:
 · 查看索引
  [1]. exec sp_helpindex 表名
  [2]. select * from sysindexes [where name = "索引名"]
 · 修改索引
  [1]. 修改索引名:exec sp_rename ‘表名.索引名’, ‘新索引名’
  [2]. 重新生成索引:alter index 索引名/all on 表名
           rebuild;
     重新生成索引会先删除再重新创立索引。可以不要rebuild,直接用set设置索引选项。
 · 删除索引
   drop index 索引名 on 表名
   最佳在剔除在此以前,利用exists判断索引名的存在性;
 · 总计消息
 总括新闻是积累在Sql Server中列数据的样书,Sql Server维护某一索引关键值的布满总计音讯。
  [1]. exec sp_updatestats
  [2]. update statistics 表名 [索引名]
 ·dbcc showcontig:展现表的数据和目录的零散音讯。
 ·dbcc dbreindex(表名, 索引名):重新建立表的叁个或五个目录。
 ·showplan_all 和 statistics io:深入分析索引,查询品质,越来越好的调动查询和目录。
   set showplan_all on/off
   set statistics io on/off 
参考:
[1]. 数据库索引的贯彻原理,目录奉公守法
[2]. 表和目录数据结构连串布局,SQL索引学习-索引结构

6. 视图

视图是一种逻辑对象,是由大旨表导出的虚构表,不占用别的数据空间、不存款和储蓄数据,仅封装预订义的查询语句,其剧情由询问定义。视图是翻开数据库表数据的一种艺术,提供了仓库储存预订义的询问语句作为数据库中的对象以备后用的效应,但视图不可能引得。被询问的表称为基表,对视图的数据操作(增、删、改),系统依照视图的概念去操作与视图相关联的基本表。
优点:
 (1)保障数据的逻辑独立性,数据保密;
 (2)遮蔽复杂的SQL,SQL语句复用,数据简化操作逻辑,易于检索数据;
 (3)重新格式化检索出来的数据;
开创视图: 
  create view 视图名 [with schemabinding/encryption] as 查询语句  
 (1)对于视图和基表必需紧凑结合的状态,利用with schemabinding将视图定义为索引视图;
 (2)对创制视图的SQL语句加密,利用with encryption;
保证视图:
 · 查看视图
  [1]. exec sp_helptext 视图名
  [2]. select definition from sys.sql_modules
      where object_id=object_id(‘视图名’)
 · 修改视图
    alter view 视图名 as 查询语句  
   重命名视图: exec sp_rename 旧视图名, 新视图名  
 · 删除视图
    drop view 视图名1 [, 视图名2, …]   

7. 游标

游标是一种只和一组数据中某四个笔录举行交互的格局,是对(select)结果集的一种扩展。将面向集结的数据库管理种类和面向行的先后设计结合,首要用来交互式应用。
Transact-SQL 游标
仓库储存进度、触发器和 T-SQL脚本,服务器端(后台)游标,仅扶助单行数据提取,分为;

  • 静态游标:快速照相游标,在 tempdb 中开创游标;供给不常表保存结果集;
  • 动态游标:展开速度快、不需更动不经常内部工作表,但总是速度慢,不帮忙相对提取;
  • 只进游标:默许值,顺序提取、不支持滚动,最节省财富;
  • 键集驱动游标:键集独一标记行,键集是开荒游标时在 tempdb 中生成并内置在表 keyset 中;需求临时表保存键集;

注:顾客端(前台)游标,仅帮忙静态游标,私下认可在客商机上缓存整个结果集、需保证游标地点消息。服务器(后台)游标品质更佳、考订确的原则性更新,允许三个依靠游标的运动语句。
选用游标的标准进度,分为:

  • 宣示游标:declare 游标名称 + SQL检索语句

    declare 游标名称 cursor

     [local|global] [forward_only|scroll] [static|dynamic] ..
    

    for SQL(select)检索语句

  • 展开游标: open [golbal] 游标名称 | 游标变量  ,游标张开的还要招来数据并累积。

  • 领取数额

    fetch [next|prior|first|last | absolute|relative]

        from [global] 游标名称 | 游标变量
        into 结果变量[..]
    

    定位修改和删除数据:前提是用  for update of 列列表; 设置可编写制定的列。

  update 表名 set 列名=新值[..] where current of 游标名
  delete from 表名 where current of 游标名
  • 闭馆游标: close [golbal] 游标名称 | 游标变量  
  • 除去游标: deallocate [golbal] 游标名称 | 游标变量  

注:游标变量指点用了游标的变量。其余操作:

  select @@CURSOR_ROWS;    // 游标中的数据行数
  select @@FETCH_STATUS;   // fetch执行状态(-2,-1,0)  

8. 存款和储蓄进程

存款和储蓄进度(Stored Procedure),数据库架构功能域内的主要性对象,是积攒在巨型数据库系统中一段为了成功一定作用的可复用的代码块,是SQL语句和可选调控流语句的 预编译集结,经过第贰遍编写翻译后再一次调用不必再一次编写翻译。存款和储蓄进度首要用以再次来到数据。
.vs 函数

  • 函数不能够改改数据库表数据,存款和储蓄进度可以;
  • 储存进程必得 execute 实施,函数调用更加灵活;

优点:简单、安全、高性能

  • 同意标准组件式编制程序,可移植性、可复用;
  • 粗略易用,预编写翻译、执行进程快、效用高;
  • 革新安全机制、有限支持数据的安全;
  • 节约网络流量、减弱互联网负载;

分类

  • 系统存储进程:存款和储蓄在 master 数据库中,以 "sp_"为前缀,用于从系统表中获取音讯。
  • 客户自定义存储进度:T-SQL存款和储蓄进程、CL卡宴存款和储蓄进度、有的时候存款和储蓄进程。不能够将CLEnclave存款和储蓄进度创造为一时存储进度。

创建

1  create proc|procedure 存储过程名
2        (@parameter 参数数据类型 [,...])
3  as
4  begin
5    < SQL语句代码块 
6    return >
7  end

返回值

  • 行使 return 重返三个值;
  • 使用 output 定义再次回到参数来回到多少个值; 

维护
· 查看:
  [1]. exec sp_helptext 存款和储蓄进度名;
  [2]. sys.sql_modules目录视图;
  [3]. object_definition元数据函数; 
· 加密:with encryption
· 修改:直接将 create 替换为 alter;
· 删除:drop proc 存款和储蓄进度名;
执行

  • 语法解析阶段
  • 深入分析阶段
  • 编写翻译阶段:剖析存款和储蓄进度、生成存款和储蓄进程举办布置。实施安插存款和储蓄在经过便捷缓存区(特意用来存款和储蓄已经编写翻译过的查询规划的缓冲区)。
    • 双重编写翻译:[1].sp_recompile;[2]. 实行时在 exec 语句中选取with recompile;
  • 实践阶段

9. 触发器

Trigger,触发器是独特的囤积进程,由 事件 自动触发,无法显式调用,首要用来珍重和进步数据的(一致/引用)完整性约束和事务法则([1]. 约束;[2]. 触发器)。触发器能够级联嵌套。常用的 inserted 和 deleted 表是指向当下触发器的一些表,在高速缓存中积累新插入或删除的行数据的别本。能够明白为委托事件。平时触发器只与单个表关联。 
自律 vs 触发器 vs 存款和储蓄进度
自律首要被用来强制数据的完整性,能提供比触发器更加好的性质;触发器常用来注脚职业准则或是复杂的多少注解。触发器能够达成约束的万事功用,但先行通过自律完结。

  • 错误音讯处理:约束只可以选用口径的系统错误新闻,触发器可以自定义错误消息;
  • 品质差距;
  • 管住保险的专门的职业量; 

参考:封锁与数据库对象法则、暗中认可值+数据库设计中约束、触发器和积累过程;
事件 - -> 触发器 - -> 存款和储蓄过程
·  DML 触发器:响应数据操作语言事件,将触发器和接触它的口舌作为可在触发器内回滚的单个事务;常用、质量成本小,能够兑现相关表数据的级联改变、评估数据修改前后表的境况。
ζ  AFTE宝马Z2 触发器:在 IUD 操作、INSTEAD OF 触发器和封才鱼理以往被激起;推荐且不得不在表上内定; 
ζ  INSTEAD OF 触发器:在封蛇头鱼理在此之前被激发(推行预管理补充约束操作),钦定推行DML触发器以代表经常的触发动作,优先级高于触发语句的操作;
注:每一种表或企图针对种种 DML 触发操作 IUD,有且不得不有一个相应的 INSTEAD OF 触发器,能够有多少个照料的 AFTECR-V 触发器。
ζ  CL汉兰达触发器:试行在托管代码中的方法;
·  DDL 触发器:响应数据定义语言事件,用于在数据库中实行处理任务;
·  登入触发器:响应 logon 事件,用于核查和垄断(monopoly)服务器会话;
优点

  • 预编写翻译、已优化,试行功效高;
  • 已封装,安全、易维护;
  • 可重复使用;

缺点

  • 占用服务器能源多;
  • 前置触发(事后诸葛武侯);

创设与保险
·  DDL

  create/alter trigger 触发器名称
        on 作用域(DDL:数据库名database/服务器名all server)
        FOR create|alter|drop|grant 等DDL触发器
   as SQL处理语句

  删除: drop trigger 触发器名;  修改: create - -> alter  
·  DML

  create trigger 触发器名称
       on 作用域(DML:表名/视图名)
       [FOR|AFTER|INSTEAD OF] {[insert [,] update [,] delete]}
   as SQL处理语句

  嵌套:级联触发,递归触发
   ·  间接递归:更新T,触发Trig,Trig更新T,再一次触发Trig;
   ·  直接递归:更新T1,触发Trig1,Trig1革新T2,T2触发Trig2,Trig2更新T1;
  参考:如何调整触发器递归;

10. 事务 - 锁

 具体参谋 政工和锁 - sqh;

11. 全文索引

全文索引是一种新鲜类其余根据标志的成效性索引,用于提升在大数据文本中搜索内定关键字的进程,由 全文索引引擎服务 (SQL Server FullText Search)成立和保卫安全。全文索引创设和保证的进度称为填充:完全填充、基于时间戳的增量式填充、基于更动追踪的填写。全文索引只好在数据表上开创。
全文索引 .vs. 普通索引

  • 普普通通索引选用B-Tree结构,全文索引基于标志生成倒排、聚成堆且压缩的目录;
  • 常常索引适于字符/字段/短文本查询,全文索引是基于关键字查询的目录,针对语言词语/长文本找寻;
  • 各类表允许有好三个普通索引,全文索引只好有叁个;
  • 普普通通索引自动更新、实时性强,全文索引须要定时维护;

全文目录 - 全文索引
仓库储存全文索引,是成立全文索引的前提。全文目录是杜撰对象,是意味全文索引的逻辑概念。全文目录和全文索引都感到全文字笔迹查验索查询服务。

  • rebuild:重新生成全文目录;
  • reorganize:优化全文目录;

    create fulltext catalog 全文目录名 create fulltext index on 全文索引基于的表名[索引满含的列列表]

原理:两步走
对文本举行分词,并为每三个产出的单词记录八个目录项以保存出现过该单词的富有记录的音信。全文索引引擎对步入到全文索引的列中的内容按字/词创设目录条约,即先定义一个词库,然后在小说中寻觅各个词条(term)出现的效用和地点,把那一个频率地点消息按词库顺序归结,完结对文本创设二个以词库为目录的目录。
· 创制基于关键字查询的目录
    - 怎么着对文件实行分词:二元分词法、最大相配法和总计划办公室法
    - 创设目录的数据结构:采取倒排索引的组织
· 在目录中搜索一定
   全文谓词:在 select 的 where/having 子句中钦定
    - contains:精确。简单词、派生词、加权词、前缀词、邻近词;
    - freetext:模糊。文本拆分,分别搜索;
   行集函数:在 from 子句中内定
    - containstable:
    - freetexttable:

参考:全文索引原理介绍;全文索引原理及表率;

SQL Server Management Studio

它就是SQL server的图形化的治本分界面,也正是顾客端。

SQL-Server Helper

1. 上边给出 SQL-Server 数据库命令推行的两种艺术样例

图片 1图片 2

 1 public static bool ExecuteSqlNoResult(string sql)
 2 {
 3     using(SqlConnection conn = new SqlConnection())
 4     {
 5         try
 6         {
 7             conn.ConnectionString = RVCConnectingString;
 8             conn.Open();
 9             SqlCommand command = new SqlCommand(sql, conn);
10             command.ExecuteNonQuery();
11             return true;
12         }
13         catch(Exception ex)
14         {
15             // 
16             return false;
17         }
18     }
19 }

[1]. 试行SQL,无重回值

其中,SqlCommand表示要对SQL Server数据库试行的叁个Transact-SQL语句或存款和储蓄进度。不能够持续此类。

图片 3图片 4

 1 public static bool ExecuteSqlWithResult(string sql, out DataTable dtResult)
 2 {
 3     using(SqlConnection conn = new SqlConnection())
 4     {    
 5         dtResult = new DataTable(); 
 6         try
 7         {
 8             conn.ConnectionString = DatabaseConnectingString;
 9             conn.Open();
10             SqlDataAdapter sda = new SqlDataAdapter(sql, conn);
11             sda.Fill(dtResult);
12             return true;
13         }
14         catch(Exception ex)
15         {
16             // 
17             return false;
18         }
19     }
20 }

[2]. 试行SQL,再次来到结果

个中,SqlDataAdapter表示用于填充System.Data.DataSet和换代SQL Server数据库的一组数据命令和二个数据库连接。无法继续此类。

图片 5图片 6

 1 public static bool ExecuteSqlTrans(List<string> sqlList)
 2 {
 3     using(SqlConnection conn = new SqlConnection())
 4     {
 5         SqlTransaction sqlTrans = null;
 6         try
 7         {
 8             conn.ConnectionString = DatabaseConnectingString;
 9             conn.Open();
10             
11             sqlTrans = conn.BeginTransaction();
12             SqlCommand command = new SqlCommand();    
13             command.Transaction = sqlTrans;
14             command.Connection = conn;
15             
16             string sql = null;
17             foreach(string sqlTmp in sqlList)
18             {
19                 sql = sqlTmp;
20                 command.CommandText = sql;
21                 command.ExecuteNonQuery();
22             }
23             
24             // 提交事务(前面执行无误的情况下)
25             sqlTrans.Commit();
26             return true;
27         }
28         catch(Exception ex)
29         {
30             if(sqlTrans != null)
31             {
32                 // 执行出错,事务回滚
33                 sqlTrans.RollBack();
34             }
35             retrun false;
36         }
37     }
38 }

[3]. 批量实施SQL,以作业格局

个中,SqlTransaction表示要在 SQL Server 数据库中拍卖的 Transact-SQL 事务。不能持续此类。

2. 判定表、存款和储蓄进度等的存在性

// 判断普通表
IF NOT EXISTS( SELECT  * FROM dbo.SysObjects WHERE ID = object_id(N'TableName') AND OBJECTPROPERTY(ID, 'IsTable')=1 )
BEGIN
CREATE TABLE TableName(
    ... ...
)
END

// 判断存储过程
IF exists(select 1 from sysobjects where id=object_id('ProcName') and xtype='P')
    DROP PROC ProcName
GO

// 判断临时表
IF object_id('tempdb..#TAB_Tmp_Name') is not null 
BEGIN
    DROP table #TAB_Tmp_Name
END;
CREATE table #TAB_Tmp_Name(
  ... ...  
);

3

 

启动Management Studio

在登入分界面输入相关的消息:

服务器类型:数据库引擎

服务器名称:我们得以输入IP地址, Computer名称。假使是访谈本机的SQL server服务何况未有改动暗许端口号的话,只必要输入贰个点 ” . ”,它就象征本机的SQL Server正式版的服务端。(体验版是.SQLEXPRESS)

身份验证:SQL Server身份验证

用户名:sa

密码:sa

当SQL Server身份验证不可能登入时

1、 用windows身份验证(也正是用本机助理馆员来登入,不须求输入顾客名密码的)。

2、 张开侧边目录中的 SQL Server à 安全性 à 登录名 à 双击sa à 张开sa 顾客的习性窗口。

3、 修改密码

4、 撤废”强制推行密码攻略”

5、 在“状态”选项卡中,对“是或不是允许连接到数量引擎”和“登录”分别选择“授予”和“运维”。

6、 点击明确关闭sa 客户的性质窗口

7、 右键点击服务器根节点,采取属性展开“服务器质量”弹窗。

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

9、 分明并关闭“服务器品质”弹窗,然后在SQL server配置管理器中重启数据服务端,再用sql server 帐户密码来登录就可以。

SQL Management Studio的分界面操作

左侧目录中,大家得以制造数据库数据表。

左上角的新建查询按键,能够展开三个输入与实践SQL语句的窗口。在此窗口中大家能够因此按F5键或点击“实行”来运营SQL语句。 在输入多条SQL语句的事态下,可以选中须求实行的代码,然后按F5来只举行被入选的一对。

sqlcmd命令行管理工科具

透过纯指令的方法来保管SQL server数据库服务端。

开头菜单à 运转à输入cmd展开命令行窗口à输入sqlcmd /? 查看sqlcmd命令的支持新闻。

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

比如:

sqlcmd

use test

select * from student

go

末尾,必需输入go才会初阶实践SQL语句。exit退出sqlcmd命令行状态。

修改数据表结构

不知凡哪一天候我们要求修改数据表字段结构,举个例子增加字段、修改字段类型和字段名,可是SQL server暗中同意处境下会阻止大家对数据表结构的修改。所以我们须求更动SQL Server的安装参数。

工具菜单à 选项à 张开”选项”弹窗中的”Designers”选项卡à裁撤”阻止保存供给再一次创设表的转移”后边的当选状态。

T-SQL基本语法

select语句

语法:

SELECT 字段列表 FROM 表名

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)

 

 

 

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

order by子句

举例说倒序排序

SELECT * from studentorder by id DESC

top子句

Having子句

用来给分组织设立置规范

示例:

SELECT age,name from student group by age,name having name = '小李'

DISTINCT子句

消除并回到结果中再度的值。

SELECT DISTINCT age from student

insert into插入数据

叁次插入一行数据

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

三次插入多行数据

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

省略字段名按表的字段顺序来插入数据

insert into student values('小白楼',60,1,'沙坪坝',12345678)

在乎:这种措施必得遵照表的字段顺序(除了主键ID)来排列语句中的字段值,况兼存有字段都必需填写值

聚合函数

AVG() 求平均值

SUM() 求合

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

COUNT() 总括行数

UPDATE语句

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

DELETE语句

delete dbo.student where id=14

练习

创办一张学生数据表,包蕴字段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

 

当中连接

它依照多少个或多少个同样的字段将记录相称在一块,将这两张表中的多寡一同查询出来。

中间连接的性子是,只展示成涉嫌的数码,可是未有提到的数额是不会被出示出来的。

语法:

SELECT <字段列表> FROM <第一张表> <连接类型> <第二张表> <ON 连接条件>

二表连接,示例:

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

多表连接,示例:

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

多表连接的选择外号,省略as

select s.name,c.className,t.name from student as s
INNER JOIN 
classInfo as c  ON s.cid = c.cid
INNER JOIN 
teacher as t ON c.teacher= t.tid

作者们能够透过as关键字来给多少表定义二个外号,况兼经过这一个外号调用表中的字段。

只顾:只要定义了别称,就亟须使用别名,原表的名字就无法再用了。

同期as关键字是足以大致的:

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

增加补充:内部连接的INNE科雷傲JOIN能够简化为JOIN ,效果是一样的。

外部连接

里头连接有肯定的排他性,第二张表是对第一张表的补给,要是第一张表没有供给第二张表中的有个别数据,那么第二张表中不被亟需的数量就不会被展现出来。

语法:

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

万一运用LEFT正是体现左表中的全部数据,如若采用Right便是显示右表中的全体数据

示例:

select *from student as s RIGHT JOIN Class Info as c  ON s.cid = c.cid

多部外界连接示例:

select * from student  s RIGHT JOIN classInfo  c  ON s.cid = c.cid
LEFT JOINteacher t ON c.teacher=t.tid

完全连接

一起连接( FULL JOIN 或 FULL OUTE凯雷德 JOIN )

用于体现所连接的全体表的装有数据,就算那条数据未有其余关联关系。

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

练习:

1、 先重做上课时讲的事例。

2a、 假使现在制作二个百货集团购物系统,产品消息表(product)(id、name、price)、客商表(customer)(id、name)、购物清单表(saleList)(id、产品编号pid、客户号码cid)

2b、 用一条select语句询问某些顾客的购清单上的具备成品。

2c、 用一条select语句询问得到有些客户的购清单上的兼具成品的总价。

 

3a、假诺以往塑造三个影院的数据查询系统,坐位表(site)(id、row、col)、顾客表(customer)(id、name、phoneNum)、电影票(ticket)(id、cid、sid、mid)、电影表(movie)(id、name、mtime)

3b、查询某一场电影的兼具坐位上的客户的新闻。

3c、查询某一场电影的具有坐位上的顾客的信息,何况展现空座位。

(怎么样剖断三个字段的值为NULL值:

select * from movie where name is null)
select s.id,c.name from dbo.ticket t
join dbo.customer c on t.cid=c.id
join dbo.movie m on t.mid=m.id
right join dbo.site s on t.sid = s.id
where m.id=1
union
select id,'无座' as name  from site where id not in
(select site.id from ticket
join dbo.customer on ticket.cid=customer.id
join dbo.movie on ticket.mid=movie.id
right join dbo.site on ticket.sid = site.id
where movie.id=1)

3d、查询某三个用户看过的持有电影的名号。

子查询

它是指八个select查询语句,并不是向来从数据表中来取得数码,而是从别的二个查询语句的结果聚焦来进展询问。

示例:

select s.name,s.age,s.sex from (

select * from student where sex = 0

) as s

where age >20

中间,在from关键字的背后,并不是数据表而是select语句。

穿插连接

接力连接在本质上,也足以看成是一种内接连。只体现成一关联的数目。

示例

--内连接写法

select * from classInfo

inner join teacher

on classInfo.teacher=teacher.tid

 

--交叉连接写法

select * from classInfo,teacher

where classInfo.teacher=teacher.tid

 

 

双方的结果是同样的

 

联合UNION

行使多少个或三个以上查询合并后只回去三个结出集

比如:

取得班下季度龄超越20和具有男士的合集

select * from student where age>20

union

select * from student where sex = 1

 

 

前提每条select语句重返的字段列表的个数和各样必得是一模一样的。

 

一块后归来重新的数码

union联合后的结果自动去除掉四个select结果中的重复数据,假若要求重新显示那一个重新数据,我们能够运用union all关键字:

select * from student where age>20

union all

select * from student where sex = 1

 

创办与修改数据库、表

 

SQL Server中的对象名

好些个场合下大家采取的是数据表或数据库的简写形式,实际上SQL server中的数据表有4层命名约定。

[多少服务器名.[数据库名.[模式名.]]] 对象名

.test.dbo.student

数据库服务器名:默许是指当前已登入的那么些数额服务器。

数据库名:私下认可是指在顾客端左上角的下拉列表中已接纳的数码库名,或用use 指令内定数据库。

use test select * from student where sex = 1

模式名

SQL server对象能够具备两种方式名。

率先种方式:该对象具有的权位的客户。

第三种情势:暗许dbo,允许八个登录客商分享的一种访谈形式。

格局所表示的正是访谈权限,平日大家使用暗中认可的dbo形式。

CREATE语句

它用来制造数据库对象

语法:

CREATE <对象类型> <对象名称>

CREATE DATABASE news

CREATE TABLE newContext( id int )

CREATE DATABASE创立数据库

新创制的数据库,除了创立者、系统管理员、数据库全部者以外,其余人都不可能访问。

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

 

用在八个地点:一是概念数据库文件的职责。二是定义数据日志库文件的职位。

PEscortIMA奇骏Y 关键字用于钦命多个数据库文件中的主文件。

NAME 钦命文件的实例名称。也正是在数据库的逻辑名(非物理文件名)

FILENAME 就是指数据文件的大意地点和文书名,mdf(数据库)  ldf(日志文件)

SIZE 数据库大小,能够在数字后边用KB或GB表示数据库的轻重。

MAXSIZE 最大小体量。

 

COLLATE

用来拍卖排序和字母大小写等难点

 

FOR ATTACH

将已存在的局地数据库文件附加到前段时间服务器上。当前,那么些文件必得是数据库的一片段。

 

WITH DB_CHAINING

赶过数据库全体权

 

TRUSTWORTHY

为sql server数据库文件增加安全层

创设数据库示例:

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盘路线之下成立数据库。

 

细心:假若必要对数据库文件进行复制、剪切或删除操作。

 

翻看数据库音信

EXEC sp_helpdb ‘test’

以看似查询语句的结果集的艺术赶回数据库的轻重、具有者、创制日期、文件路径等音讯。

 

CREATE TABLE创设数据表

CREATE TABLE 数据表名

创建表从前分明是或不是曾经选用当前数据库

 

总身体语言法

CREATE TABLE [数据库.[数据库所有者]] 数据表名
(
<字段名><字段的数据类型>
[DEFAULT <默认值表达式>]
|
[IDENTITY [seed,increment][NOT FOR REPLICATION] ]
[ROWGUIDCOL]
[COLLATE<COLLATION NAME>]
[PRIMARY KEY]
[NULL | NOT NULL]
[<column constraint 字段约束>]
|
[table_constraint 表约束]
|
[字段名 as 计算列表达式]
)
[ON (<文件组>)|DEFAULT]
[TEXTIMAGE_ON(<文件组>)|DEFAULT]

 

DEFAULT 默认值

指该字段在未有输入值的意况下暗中同意使用的值。

IDENTITY标识、自增量

暗中同意意况下,每条记下自动扩展1

NOT FOR REPLICATION

正是指对那些表举行理并答复制的时候,ID主键的值是重新排列,照旧延用从前的ID

ROWGUIDCOL

是指将贰个表中的数额复制到另三个表中时,若是产生ID重复意况下,应用如哪个地方理。

COLLATE

用来拍卖排序和字母大小写等难题。

PRIMARY KEY

设置该字段为主键

NULL/NOT NULL

是不是同意为空

字段约束

对字段中输入的数码开展平整的界定。

计算列

能够成立三个自个儿并未有别的数据的列,这么些列的值由别的列来动态的改造。

比如:

PCount AS price*num

那边大家就定义了三个总计列,总价=单价*数量

 

注意:

1、无法计算主键、外键、唯一键

2、只可以援引当前多少表中的字段

 

表约束

对插入表的多少开展界定

ON

假若数据库由八个部分构成,大家可以钦赐数据表存款和储蓄在哪些部分。

TEXTIMAGE_ON

与ON的效应类似,不过它只有在表中有Text或Image类型的字段时才有效。

创设数据表的演示:

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

)

 

练习:

创办多个产品销售表,字段如下:pid、pname(产品名称)、pPrice(产品价格)、pNum(产品销售数量)、pCount(产品发售总价= p普赖斯* pNum),用CREATE语句创立这几个数据表。

 

ALTEMurano修改语句

ALTER <数据对象类型><数据对象名称>

ALTE酷威 DATABASE 修改数据库

修改数据库名

ALTER DATABASE test MODIFY NAME = test22

将数据库test改名称叫test22

修改数据库大小

ALTER DATABASE test MODIFY FILE (SIZE = 500MB)

注意:不可能变小,只好叠合它的容积。

ALTEENCORE TABLE 修改数据表

最广大的操作正是修改数据表名和表中的字段。

 

增加字段

ALTER TABLE dbo.student

ADD --这个关键字代表添加

phoneNum char(20) DEFAULT '00000000',

sAddress nvarchar(100) ,

createTime DateTime DEFAULT GETDATE()

--GETDATE()代表获取系统当前时间

修改字段名

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

示例:

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

修改字段类型

ALTER TABLE 表名 ALTER COLUMN 字段名 类型

示例:

ALTER TABLE dbo.student

ALTER COLUMN sAge nvarchar(30)

除去字段

ALTER TABLE 表名 DROP COLUMN 字段名

示例:

ALTER TABLE dbo.student

DROP COLUMN sAddress

字段的值会被一块删除

修改表名

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

示例:

EXEC sp_rename 'student','studentInfo'

DROP语句

删去数据库对象,比方:删除数据表、视图、存款和储蓄进度、触发器

语法:

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

DROP语句可以并且删除多张数据表

DROP TABLE 表1,表2,….

示例:

drop table table1,table2,table3

DROP删除数据库

DROP DATABASE 数据库名

练习:

客商CREATE 语句创造一个电影院相关的数据库,个中满含数据表(site)(id、row int、col int)、客户表(customer)(id int,name nvarchar(50)、phoneNum char(20))、电影表(movie)(id int 、name nvarchar(50)、mtime date提姆e)

 

里头,顾客电话的默许值是12345678

影片的暗中认可时间是最近系统时间

各种表的id都必需是自增的主键

修改site数据表名称叫userSite

修改customer中的字段phoneNum的类别为char(50)

 

数据库相关的内容

系统数据库

master

积存了数据库的为主指标音讯,未有这些数据库Sql Server就不可能符合规律运营。

msdb

提供了SQL Server的表示服务中要推行的职分和调养陈设

model

被SQL server用于数据库模板音信的存款和储蓄

tempdb

用来贮存在一些一时新闻,重启数据库服务端时,它存款和储蓄的新闻会被清空。

分离数据库

数据库暗许的蕴藏地方

C:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLDATA

一旦大家需求将它移动地点的话,就要求首先分离数据库:

右击数据库Logo弹出菜单à职务à分离à弹出分手数据库窗口à选中”删除连接”à明确

如此大家就足以复制和撤并数据库了。

叠合数据库

用以将早已分其余数据库文件mdf、ndf(数据库协理文件)、ldf 加多到数据库服务端中进行运维。

右击“数据库”à在弹出菜单中选用”附加”à在“附加数据库”窗口中式茶食击加多à 选拔mdf文件à分明à显明

备份与回复数据库

备份

抵触于分别数据库,备份的时候大家无需甘休数据库的周转。备份能够在客户正在使用数据库的动静下实行。在钦命数据库的右键菜单中à职分à备份à在“目录-备份到”区域中内定数据库备份的门路(暗许路线是在sql server的安装目录下,假若须要退换备份路线,必要先删除默许路线,再点击加多)

还原

右击“数据库”à在弹出菜单中选用”还原数据库”à在“还原数据库”窗口中钦定”设备源” à点击”设备源”后的 ”…” 按键à增添à选拔备份文件à明确à选中数据库前方的对勾à采取对象数据库下拉列表à明显

数据库备份文件的扩充名是bak

sqlServer_束

封锁便是增加一种范围,为字段或表增多限制,以保险数量符合客户制订的平整。

封锁的归类

基于约束范围

实体约束

域约束

参谋完整性约束

听他们说约束的不二等秘书诀

主键约束

外键约束

独一约束

CHECK约束

DEFAULT约束

规则

默认值

封锁的定义

域约束

域约束用来拍卖二个或多个字段。

比如:商品价位无法为负数。

当顾客插入一行数据时,只要有一字段不切合约束原则,那么整条记录都没办法儿插入。

实业约束

它用来针对行举办约束。

比方:供给每一种学员的人名、电话、地址都不可能现身重复。

同一的值不可能在其行现身。

参照他事他说加以考察完整性约束

某一字段的值,必需带有于(当前表或另外表的)别的字段值的界定内。

自律的命名

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

CHECK约束:CK_ students_4j432j,CK_ students_ageNotSmall0

键约束

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

主键约束

确认保证主键的值是天下无敌的。

何以给一张并没有主键的表增添主键

ALTER TABLE Table_1

ADD CONSTRAINT PK_table111

PRIMARY KEY (id)

外键约束

不怕为了保障数量的准头,比方:确定保障每一条论坛贴子的发贴人都是实在存在于客户表的。

透过sql manageMent studio 来增加外键

1、分明须求被界定的数据表。

2、踏向被界定的数据表的“设计”视图,在空白处点击右键菜单中的“关系”项。

3、点击加多开关新建三个约束。

4、选中新扩大长的封锁,在右臂的“表和列标准”前面有二个开关”…”,点它开拓外键关系编辑窗口。

5、选中相应的表的应和字段就能够。

 

外键约束的双向性

当两张表之间加多了外键之后,它所确立的牢笼对这两张表的一举一动都以拥有约束遵守的:

1、 外键援用表,无法增多主键表中不设有的值。

2、 主键表中不能够去除已经被外键表援引的主键。

 

平日来讲外键在外键引用表上加多

第一要有别于哪张表是主键表(是指用已经存在的值作为约束范围),哪张表是外键表(是指加多数码时被封锁必需相符范围的那张表) 。

 

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

练习:

1、 用create创立学生表(sid、sname、sage、cid)和班级表(cid、cname、cteacher)

2、 对这两张表增多外键约束,班级表是主键表、学生表是外键援引表。

3、 在学员表中加多多少个不设有的班级试一下。

4、 在班级表中删除叁个一度被引述的班级试一下。

 

由此SQL语句来创建国门外键

在创建数据表的同期对有些字段加多外键

CREATE TABLE ticketVIP
(
tid int identity primary key not null,
cid int not null
FOREIGN KEY REFERENCES customer(id)
)

其中,FOREIGN KEY REFERENCES之后的表名(字段名)正是象征字段与哪张表的哪些字段塑造外键关系。

询问一张表中的外键新闻

语法:

EXEC sp_helpconstraint 表名

示例:

EXEC sp_helpconstraint ticketVIP

在已存在的数据表中加多外键

ALTER TABLE dbo.ticketVIP

ADD CONSTRAINT

FK_dbocustomer_ticketVIP

--外键的名字

FOREIGN KEY (cid)

--指定当前表的字段

REFERENCES dbo.customer(id)

--指定与哪张表的哪个字段建立外键关系

练习:

1、 用create创立商品表product(pid、pname、pPrice),增添最少5条数据。

2、 用create创造客商表customer(cid、cname)增加最少5条数据。

3、 用create成立购物清单saleList (sid、pid、countNum、saleTime、cid),並且拉长对pid外键。

4、 用ALTE中华V TABLE指令来给saleList表的cid增添外键。

数据表的自引用

不怕约束三个张表中的有些字段的值必需切合另二个字段的已存在的值的范围。

譬喻现有一张职员和工人表,职员和工人表中字段如下(职员和工人id、职员和工人姓名、上级领导id),在此我们得以约束“上级领导id)”必需属于“职员和工人id”的限量内。

create table employee(

eid int identity primary key not null,

eName nvarchar(10),

lindaoID int

FOREIGN KEY REFERENCES

employee(eid)

)

稳重:创造自援引的艺术与创建外键的不二秘技一致,不一样是表名与字段都以时下表中的。

一样用ALTE索罗德语句也能够加多自援引

ALTER TABLE employee

ADD CONSTRAINT

FK_linDao_Must_Be_employee

--自引用的名字

FOREIGN KEY (lindaoID)

--指定当前表的字段

REFERENCES employee(eid)

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

级联合浮动作

当我们转移数据记录的时候,能够同不经常间操作两张表中的有关联的数量。

诚如来说增加数据不供给级联操作,唯有删除和修改的时候有望因为破坏了外键约束而招致三个表之间数据的失实,因而就要求一块的修改或删除八个表之间的数码。

在制造数据库的还要加上海外国语高校键与级联合浮动作

举个例子:现创建一张薪金表与职工表并创设级联关系。就是说当职员和工人消息被剔除的时候,其工资记录一同被去除。

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语句就是创办外键并扬言数据的信任性关系。

ON UPDATE NO ACTION

NO ACTION就是指不实施其他实行,暗中认可值。

ON DELETE CASCADE

CASCADE建构级联删除关系,在这里正是去除员工的还要,删除另一张表中该职工的有关记录。

练习:

始建多个班级表,并与学生表创建级联关系。须要删减班级的时候,那个表中的学习者信息也还要被删除。

独一约束

纵然约定贰个字段中的值无法再一次,每一个值都以独步天下的。

在开创数据表的时候拉长独一约束

CREATE TABLE USERINFO(

uid int identity primary key NOT NULL,

uName nvarchar(50),

uPhone char(20) UNIQUE

)

专一:独一约束与独一索引达到的机能是一律的。

在已存在的表中加多独一约束

ALTER TABLE dbo.employee

ADD CONSTRAINT UQ_name_no_repeat

UNIQUE(eName)

CHECK约束

经过客商自已定义的法则来对二个如故四个字段进展封锁。

对已存在的数码表增加check约束

ALTER TABLE dbo.employee

ADD CONSTRAINT CN_AGE_MORE_ZERO

--约束的名称

CHECK

--说明这是一个CHECK约束

(eAge>=0 AND eAge<250)

小心:增添CHECK约束的时候,数据表中以后的多寡必供给满意约束原则。

CHECK约束原则示例

限制字段age的数据范围为0到250

age BETWEEN 0 AND 250

限制字段PhoneNum 值必须为电话座机号

PhoneNum LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'

限制字段的值为多个可选值之一,比如:学历(初中、高中、大专、本科、研究生、博士)

xueLi IN('初中','高中','大专','本科','研究生','博士')

限制一个字段的值必须小于另外一个字段,比如年龄必须大于工龄。

(age>workYears)

 

练习:

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

限制age 必须0到50。

限制phoneNum必须是11位数字

限定性别只好输入“男”或“女”

剥夺约束

突发性大家要求一时半刻结束或剥夺约束。

不常禁止使用约束

ALTER TABLE employee

NOCHECK

CONSTRAINT CK_ageMoreZero

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

过来已禁止使用的封锁

ALTER TABLE employee

CHECK

CONSTRAINT CK_ageMoreZero

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

规则

平整与check约束是相当类似的,它们的分别是平整只好限量叁个字段,可是准则定义一遍,能够频仍采用。

就比方:年龄不能够为负数,这么些法规能够运用于顾客表、职员和工人表、学生表。

始建准绳并将其选取到钦定的字段

--创建规则

create rule age_rule as @eAge>0

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

exec sp_bindrule 'age_rule' ,'employee.eAge'

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

收回准则绑定

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

剔除法则

drop rule 规则名

示例:

drop rule age_rule

索引 index

目录是一个排列、排序的艺术,索引之后的结果便是目录。

举个例子说:新华字典,它就有种两种索引排序情势:拼音、扩偏旁部首,按笔画。

sql server中的索引的分类

按集中性分类

聚焦索引

比方:对于新华字典来讲其最重大的、物理上的实际上排列方式便是拼音顺序。

聚集索引就是数额的最主最的排列方式,对于数据表来说,自增主键id正是集中索引。

一张数据表只好有四个聚焦索引。

非集中索引

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

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

对此数据表来说,创制了主键之后,其余的目录都以非聚集索引。

一张表中最多能够增加2四十七个非集中索引。

手动在SQL management中加上引用

右击钦赐数量表弹出右键菜单 à ‘设计’ à 在编辑表结构界面空白处没点击右键 à “索引/键” à点击“增加”来成立新的索引 à 在“列”选项中甄选对哪五个字段实行排序,以什么样点子排序。

目录的用途和效果与利益

是狠抓数据查询的属性和频率。

举例说:大家遵照客商年龄创设了目录。

推行客商年龄的查询操作时,质量会有一点都不小的升迁。

select age from student order by age where age>20

按唯一性分类

独一索引

在叁个字段中, 不可能存在重新的千篇一律的多寡。强制约束二个字段中的值无法重复。

非独一索引

在三个字段中,可以存在同样的多少。

怎么样增多独一索引

透过右击钦点数量表弹出右键菜单à “设计” à在编辑表结构分界面空白处点击右键 à “索引/键” à“增添”或选中钦定的索引à选择列à 侧面“是独一的”这一项上选择“是”à分明。

这么就足以在多个点名字段之上加多唯一索引了。

按单列或多列分类

单列索引

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

多列索引

是指叁个目录依靠多少个字段张开排序。其排序格局:第一个目录排序之后,对在那之中的值同样重复的数码,再依据第一个字段来排序。

怎么样添增加列索引

由此右击钦点数量表弹出右键菜单à “设计” à在编辑表结构分界面空白处点击右键 à “索引/键” à“增加”或选中钦定的索引à点击“列”之后的小开关à在弹出窗口中增添四个“列名”。

目录的优点

当我们在询问时行使order by或 group by的时候,sql的实践作用会大大进步。

目录的相干sql指令

翻开一张数据表中的兼具索引的连锁新闻

exec sp_helpindex 数据表名

示例:

exec sp_helpindex student

成立目录

简写语法

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

一体化语法

CREATE [UNIQUE] [CLUSTERED] [NonCLUSTERED] index 索引名 on <表/视图名>(字段 asc/desc)

其中:

UNIQUE 创建独一索引

CLUSTERED /NonCLUSTERED 聚焦索引或非聚焦索引

示例:

create Unique nonclustered index

IX_ageMore on student(name desc)

重命名索引

Exec sp_rename ‘表名.原索引名’ , ’新索引名’ ,’index’

示例:

Exec sp_rename 'student.IX_ageMore','IX_AM','index'

剔除索引

DROP INDEX 表名.索引名

示例

drop index student.IX_AM

视图

在我们数据库中其实存在大多的物理表。而视图正是依附物理表的查询结果,来变化的一张设想的数据表。

在sql management中成立视图

比如:

幸存七个实在存在多少表student

下一场根据student 中保有年龄大于20岁的学童来生成一张设想表,也便是视图。

在数据库下的“视图”节点上点右键菜单 à “新建视图” à 在加多表中选中要求的数码表 à 在视图的统一计划界面写入SQL语句,比方:

select id,name,age from student where age>=20

视图分类

标准视图

正是由一个或四个物理表通过标准查询语句组成的视图,理论上具有用select语句询问出的结果集都得以用来变化视图。

还要,大家对视图中的数据开展退换时会直接影响到其本来的情理数据表。

索引视图

固然给视图增加索引

CREATE [UNIQUE][CLUSTERED][NonClustered] index 索引名 on <表 / 视图名>(字段 asc/desc)

固然大家为叁个视图创建了聚集索引,那么我们就将那几个视图叫做索引视图。

一定于给视图增多了一个主键,然后系统会为索引视图创造缓存,由此索引视图的性质要超越标准视图。

分区视图

这种视图能够在一台或多台数据库服务器上接连一组有关的数据表,以高达疑似在操作二个数据表的效果与利益。那是促成分布式数据库的一种办法。

视图的利弊

优点

1、方便重新排列物理表的数目,和操作源数据表同样。

2、对于复杂的sql查询语句来说,只须求写壹回,就足以将结果生成一个永远性的视图。

3、安全性高,只让特定的客商访问片段字段列,或部分数据。

缺点

1、质量不高,查询耗费时间开支财富。

2、对于由复杂的select语句生成的视图来讲,修改视图中的数据时有十分大希望会错误。

就此,视图平时只好使用于Mini或对质量供给不高的类别上。

视图的连锁SQL指令

视图的寻访

select 字段,…. from 视图名 [where 条件]

视图的操作和表的操作特别临近

视图结构的更换

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

Alter view 视图名 as 新查询语句

示例:

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

视图的创办

Create View 视图名 as 查询语句

示例:

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

除去视图

Drop view 视图名

随堂演习

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语句保存为贰个函数。

创造存款和储蓄过程

创制不带参数的囤积进度

语法:

CREATE PROC[EDURE] 存储过程名 AS   SQL语句序列……

推行存款和储蓄进度

EXEC[UTE] 存储过程名

示例:

CREATE PROC  showNum2 AS
select 1+1;
select 10*21;
select 100/3;
execute showNum

始建带输入参数的积累进程

语法

CREATE PROC[EDURE] 存储过程名

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

AS   SQL语句序列……

实践存款和储蓄进度

EXEC[UTE] 存储过程名 参数值1, 参数值2,….

示例:

CREATE PROC  addNum 

@num1 int,@num2 int

AS

select @num1+@num2

execute addNum 25,13

创办带私下认可值的输入参数的仓库储存进程

包罗暗中认可值参数,能够不输入具体的参数值,在不输入值使用默许值。

语法

CREATE PROC[EDURE] 存储过程名

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

AS   SQL语句序列……

示例:

CREATE PROC  addNum 

@num1 int=1,@num2 int=1

AS

select @num1+@num2

execute addNum 8

创办带输出参数的仓库储存进度

语法

CREATE PROC 存储过程名

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

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

AS   SQL语句序列……

示例:

CREATE PROC  addNum 
@num1 int,@num2 int,
@result int OUTPUT
--定义输出变量@result,它的值会被自动输出
AS
select @result=@num1+@num2
-------执行存储过程-----
DECLARE @result2 int;
--定义变量@result2
execute addNum 8,1,@result2 OUTPUT
--执行存储过程addNum将其输出结果存放在@result2中
select @result2
--显示@result2中的内容

练习:

始建一个积累进程,传入三个职工的日薪资、要扣除的罚款、这个月做事天数,用OUTPUT再次来到前段时间实际报酬。

查阅存款和储蓄进程的新闻

EXEC sp_help 存储进度名

示例:EXEC sp_help porcTest

修改存储进度

语法:

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

示例:

ALTER PROC porcTest AS select * from student

剔除存款和储蓄进度

语法:

DROP PROC[EDURE] 存储过程名

示例:

drop proc porcTest

练习

2、传入贰个id参数,依据那几个id来询问相应的上学的小孩子精晓记录,并回到这几个学生的人名、年龄、电话。

3、传入四个age参数,依照这一个age参数来回到全体年龄大于该年龄的学生记录。

SQL Server中的数据类型

数值类型

数据类型

取值范围

存储空间

tinyint

0~255

1字节

smallInt

-2768到32767

2字节

int

-231到231-1

4字节

bigint

-263到263-1

8字节

decimal(p,s)

-1038+1到1038-1

5到17字节

numeric(p,s)

-214748.3648到214748.3647

4字节

smallmoney

-922337203685477.5808到

922337203685477.5807

9字节

money

-3.438到-1.1838,0, 3.438到1.1838

4字节

real

-1.79308到-2.23308, 0, 1.79308到2.23308

4字节或8字节

表明:decimal(8,3) 表示存储了三个8位数字,小数位数是3位。

字符数据

数据类型

存储空间

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),表示可变长度。

日子与时光项目

数据类型

值范围

精度

存储空间

smalldatetime

01/01/1900 到06/06/2079

1分钟

4字节

datetime

01/01/1753到

12/31/9999

0.0033秒

8字节

datetime2

01/01/0001到12/31/9999

100纳秒

3字节

date

01/01/0001到12/31/9999

1天

3字节

time

00:00:00.0000000

23:59:59.9999999

100纳秒

3到5字节

 

二进制数据类型

数据类型

值范围

存储空间

bit

null , 0 和 1

1比特

binary

固定长度的二进制数据

8000字节

varbinary

可变长度的二进制数据

最大8000字节

image

可变长度的二进制数据

最大2G

 

除此以外还大概有xml、table类型。

T-SQL变量

T-SQL变量依据使用限制大家得以划分为:全局变量(系统变量)和一部分变量(顾客变量)

全局变量

在一切SQL Server中都能访谈到的变量,平日用来代表SQL server的连串参数。

写法:

@@变量名

例子:

SELECT @@SERVERNAME,@@CONNECTIONS

全局变量只可以访谈,不可能赋值。

 

常用全局变量

@@IDENTITY

上三遍举办insert语句后插入的数码记录的id

示例:

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

select @@IDENTITY

@@ROWCOUNT

受影响的行数

示例:

delete from teacher

select @@ROWCOUNT

一些变量

效用域:只限于在三个批管理(指同一群次实行的代码)内卓有功效。

用途:

1、在循环语句中记录循环的次数或然用于调节循环的准则。

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

3、存款和储蓄函数或存款和储蓄进程的重返值。

语法:

一些变量必需以@伊始

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

宣称贰个或几个变量,示例:

declare @num1 int ,@num2 int

赋值:

在T-SQL中,能够用select 或 set 来对变量举办赋值操作

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

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

出口变量

print

一次只可以输出三个变量: print @num1

select

二回输出四个变量

示例:

select  @num1 as 总数,@num2

T-SQL运算符

注释

多行注释 /* 被讲授的剧情 */

单行注释 -- 被疏解的剧情

运算符

+、-、*、/

运算方法:

譬喻说:求圆面积

select 3.1415926 *4*4

比较运算符

>、<、>=、!=或<>

如:

if 2>3

print '2比较大'

else

print '3比较大'

赋值运算符

= 等号 ,与别的编写制定语言同样,将左边的值赋值到左边。

逻辑运算符

AND 逻辑与

OR 逻辑或

NOT 逻辑非

字符串连接 +

select '阿姨'+'你好'

位运算符

&按位逻辑与、|按位逻辑或、^按拉逻辑异或、~按拉逻辑非

T-SQL语法相关

语句块

if 9-5=5

 begin --相当于{

print '你说对了'

 end --相当于}

else

begin

print '你说错了'

end

GO指令

表示最初运营,GO之后的话语属于另一个批次的代码。

declare @num1 int

set @num1=10

go

select @num1+1

实践报错,那是因为客商定义的一部分变量只好够在同一个批次中有效,而go指令将代码分隔成了多个批次。

T-SQL中的流程序调控制

if语句

在一个或多少个条件的判定下决定流程的走向。能够协作and、or等逻辑运算符来。

if..else语句

如果…或者

两段代码中只会进行一段

if…else if…else语句

多规格判定

示例:

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 '中老年'

while循环

当法规为true时进行循环代码,当条件为false时退出循环

declare @num1 int

set @num1=0

while @num1<10

begin

print @num1

set @num1=@num1+1

end

GOTO语句

让眼下程序试行的依次爆发更换,跳转到钦赐的标识处。

示例:

print '今天是星期天'

goto theDay

print '今天是星期一'

print '今天是星期二'

theDay:

print '今天是星期三'

case语句

一定于swith,正是以贰个变量的值来决定实行顺序的哪三个部分。

亲自去做1,根据数据表中记录的性别来显示孩子。

select id,name,

CASE sex

WHEN 1 THEN '男'

WHEN 0 THEN '女'

END

AS 性别

FROM student

示例2,单选判别题

DECLARE @N char(2)

SET @N='C'

SELECT

CASE @N

WHEN 'A' THEN '正确'

WHEN 'B' THEN '错误'

WHEN 'C' THEN '错误'

WHEN 'D' THEN '错误'

END

示例3,依照学生的年纪来决断是还是不是成年

SELECT ID,NAME,

CASE

WHEN AGE>18 THEN '成年人'

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

END AS 成年否

FROM student

从下面例子中大家得以见到,CASE语句能够在select查询数据表的时候,通过标准来剖断相应字段的值,并按原则自定义再次回到结果。

随堂演习:

1、 定义多个int型的变量,求最大值。662

2、用create创造叁个上学的小孩子表(sid、姓名sname、年龄sage、性别ssex、战表score ),须求在select查询时,展现对成绩的评说,40以下差,60分以下非常差、80之下合格、100或以下能够。

SELECT sname,sage,

case

when score<40 then '差'

when score<60 then '较差'

when score<80 then '合格'

when score<100 then '优秀'

end as '评价'

from student

3、创设一个囤积进度,该存款和储蓄进度达成输入1或0,查询上题数据表中的全数哥们或女人的平均分。

4、使用循环向上题的数据表中添增添少记录,姓名使用张1、张2…..,年龄,战绩是随机数生成的。

提示:

类型调换

DECLARE @i int

SET @i =1

SELECT '张'+CAST(@i AS varchar)

成就随机变化

select ROUND( RAND()*100,0 )

5、写一个仓库储存进度,必要回到如下数值。

1、1、2、3、5、8、13、21…………………数列的个数能够轻巧钦定。

6、写三个积攒进程,几个传入参数,一个出口参数,数据表的字段如下:(学生姓名 varchar(10)、性别 bit、出生年月 datetime 、战表群集 varchar(一千)  、重返值:新增学生记录的id int output)。将盛传的值作为一条记下插入到数量表中。

前四个传入参数分别为学生姓名、性别、出生年月,第八个参数varchar代表学生的成绩群集拼接成的字符串,如:“2:85,3:90,5:66”表示学科ID为2的学科成绩是85,学科ID为3的科目成绩是90,就那样推算。第多少个参数为新增加成功后的这条学生记录的ID,实际上便是回到的出口参数。

create proc proc_InsertStudent

@sname nvarchar(50),

@ssex bit,

@birthdate datetime,

@scoreSum varchar(200),

@rid int output

as

insert into student(sname,ssex,birthdate,scoreSum) 

values(@sname,@ssex,@birthdate,@scoreSum)

set @rid = @@identity

declare @rrid int

exec proc_InsertStudent '江小白',1,

'1995-12-22','2:85,3:90,5:66',@rrid output

select @rrid

7、实现单表分页的蕴藏进程,输入表名、pageSize、pageIndex

升迁:拼接并试行一条字符串方式的sql语句:

declare @tname varchar(50),@sql varchar(100)

set @tname=’student’

set @sql=’select * from’ + @tname

exec(@sql)

提示:分页语句

select top 10 * from student 

where sid not in

(

select top(10*(3-1)) sid from student order by sid

) order by sid

答案:

create proc getPage

@tname varchar(50),

@pIndex int,

@pSize int,

@keyName varchar(50)

as

declare @sql varchar(200);

set @sql='select top('+CAST(@pSize as varchar(10) )+') * from '+

@tname+

' where '+@keyName+' not in

(

select top('+CAST(@pSize as varchar(10))+

'*('+CAST(@pIndex as varchar(10))+'-1)) '+@keyName+' from

'+@tname+' order by '+@keyName+'

) order by '+@keyName

exec( @sql)

exec getPage 'student',2,8,'sid'

T-SQL中的流程序调整制语句2

Try……Catch语句

当大家实行顺序出现错误的时候,日常都会报错,何况甘休实践。但是借使在try语句的限定内失误的话,程序会连续运转,何况将错误消息在catch语句范围内举办管理。

语法:

BEGIN TRY

执行存储过程

END TRY

BEGIN CATCH

……

END CATCH

除非当try中的语句产生错误的景况下,才会去施行CATCH中的语句。

示例:

BEGIN TRY

exec getPage 'student',2,8,'sid'

END TRY

BEGIN CATCH

print '错错错错错错'

END CATCH

return语句

从存款和储蓄进度、批管理中无条件退出

if 3>2

begin

print '东'

print '南'

return

print '西'

print '北'

end

print '中'

waitfor等待

当T-SQL试行到waitfor语句时,程序会进去等待状态,等侍钦赐时期现在,程序再继续施行后边的言语。

语法:

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

示例:

declare @i int

set @i=0

while @i<3

begin

waitfor delay '00:00:02'

set @i=@i+1

print @i

end

算术函数

操作对象只限于:int、float、money、smallmoney、decamal

三角形函数

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

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

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

反三角函数

asin()、acos()、atan()

幂函数

power() 次方,比如:select POWER(2,10) --2的10次方

sqrt() 开(平) 方 , select sqrt(81)

square 平方, select SQUARE(9)

Log() 对数, select Log(9)

取近似值

round(浮点数,位数) 保留钦赐位数的小数,最后壹个人四舍五入

select ROUND(3.1415926,4)

FLOO中华V(浮点数) 向下取整,吐弃小数部分保留整数。

select floor(3.999)

标记函数

abs() 取相对值,如:select abs(-30)

Sign() 用于判别一个数值的正负,重返值独有四个(1、0、-1),如

select sign(-10)

正数重临1、负数再次来到-1、零再次来到0

其他

PI() 圆周率 select PI()

RAND() 随机数( 0到1以内的小数 ) ,如:

select ROUND( RAND()*100,0) 得到0到100中间的子弹头

字符串函数

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

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

奥迪Q5trim() 去掉字符串左边的空格。select  RT福特ExplorerIM( '      abc     ')

ascii() 将字符转变为个中ascii码表中的地点。select ascii('A')

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

lower() 转变字母为小写。如:select LOWE凯雷德('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)

数据类型调换函数

convert()

convert( 目的数据类型(长度) , 须求被退换的数量或字段名 ),示例:

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

CAST()

示例:

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

str()

系统函数

col_length(表名,字段名)

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

select col_length('Product','ProductName')

col_name()

回去钦点字段的列名 , 这几个表是以id的款型传播的。

select COL_NAME(OBJECT_ID('Product'),2)

翻开第3个字段的名字

收获一张表中的富有字段的音讯

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

得到一张表的字段的总和

select COUNT(1) from syscolumns where id=OBJECT_ID('Customer')

DateLength()

获取数码的骨子里尺寸,示例:

select

CompanyName,

DATALENGTH(CompanyName)/2 as '名称长度'

from dbo.Customer

查阅函数的补助音讯

将光标移动到函数之上按下F1键,就能够张开联机丛书并出示该函数的相干文书档案。

isDate()

判定日期数据是不是合法,是回来1,否再次回到0。

select ISDATE('20160229')

getDate()

收获当前岁月,比方:

select GETDATE()

有的是时候在须要为日期类型的字段增加当前光阴为暗中认可值的时候供给动用到该函数。

isNull(表达式1,表达式2)

当表明式1的值不为空时,重返表明式1的值。

假如表明式1的值为null空时,再次回到表达式2的值。

示例:

select CompanyName,

ISNULL(cast(regTime as varchar),'未添加注册时间') 

from dbo.Customer

ISNUMERIC()

判别是不是为客体的数值,即便这些数值以字符串的花样存在。

select ISNUMERIC('123f457')

是返回1,否返回0

练习:

1、现存字符串如下:”2:80,3:91,4:75”,在那之中,逗号用于分隔区别的学科和大成。在那之中1:语文,2:数学,3:加泰罗尼亚语,4:物理。将看似那样的数码存放在学生数量表中。然后,创立一个存款和储蓄进度,输入学生id,重返这些学生的各科战表(每列的称谓必需是科目名。)、各科总分,全部科指标平分分。

(倘若那4门都以选修课,相当于说有些人想必有4个成绩,有些人唯恐唯有三个大成)

declare @id int,@scoreChar char(30),

@isCharOver bit

select

@id=1, --要查询的学生记录的id

@isCharOver =0 --用来判断成绩字符串是否结束。

set @scoreChar=(select score from dbo.student

where id=@id

) --得到存放成绩的字符串

declare @scroeWithNum1 char(5), --语文成绩

@scroeWithNum2 char(5), --数学成绩

@scroeWithNum3 char(5), --英语成绩

@scroeWithNum4 char(5) , --物理成绩 @scroeWithNum char(5) --临时存放成绩的变量

while @isCharOver=0

begin

declare @douIndex int

set @douIndex= charIndex(',',@scoreChar) --获取逗号出现的位置。

if @douIndex = 0 --如果没有找到逗号的话

set @isCharOver=1 --就设置字符串结束的标记为

set @scroeWithNum = LTRIM( RTRIM(substring(@scoreChar,0,@douIndex))) --打印截取出的成绩

set @scoreChar =  LTRIM( RTRIM( substring(@scoreChar,@douIndex+1,30)))

------如果是最后一个成绩的话-----------------

if @isCharOver = 1

begin

set @scroeWithNum= @scoreChar

end

------End___如果是最后一个成绩的话-----------------

---第二次分隔成绩-----------

declare @sNum char(1),@RealScore char(3)

set @sNum = substring(@scroeWithNum,0,2)

set @RealScore =substring(@scroeWithNum,3,3)

if @sNum=1

set @scroeWithNum1 =@RealScore;

else if @sNum=2

set @scroeWithNum2 =@RealScore;

else if @sNum=3

set @scroeWithNum3 =@RealScore;

else if @sNum=4

set @scroeWithNum4 =@RealScore;

---End__第二次分隔成绩-----

end

select @scroeWithNum1 as '语文',

@scroeWithNum2 as '数学',

@scroeWithNum3 as '英语',

@scroeWithNum4 as '物理'

触发器

当客户试行某种操作之后,会被电动激动的仓库储存进程,就叫做触发器。触发器的推行决定于sqlserver实行的某种操作,并非由客商直接调用的。

按激活顺序分类

此后触发器

当客商试行某种操作完毕之后,才会被触发的触发器。

轮换触发器

当顾客施行某种操作起来此前,被触发的触发器,这种触发器能够阻止或用钦赐的操作来替换原本的操作。

规行矩步奉行的操作分类

1、数据垄断(monopoly)语言DML触发器,是指触发器所在数据表中发生了insert、update、delete操作时接触。

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

3、登陆触发器:是指当顾客登陆sql server时触发。

DML触发器描述

1、 在sql server 二〇一〇中,DML触发器通过行使两张逻辑表DELETED和INSERTED。这两张是起家在数码服务器的内部存款和储蓄器中的,大家唯有只读取权限。DELETED和INSERTED表的字段结商谈触发器所在的表的构造是同样的。触发器推行增加和删除改操作后,这两张中的记录也会被同一时间立异。

2、 触发器能够通过数量表中的相关表完结级联操作,能够应用比约束更目不暇接的级联操作,也得以完结比约束更目不暇接的自律。

3、 触发器的功力很强大,能够兑现无数错落有致的操作,可是过多选取触发器会招致数据库品质的回降和顺序维护的劳顿。

触发器的施用

创立触发器

语法:

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

AS

若干T-SQL语句

GO

注意:DML触发器是对准某张表的某项目操作而接触的。

示例:

譬喻说创立一个触发器mytrigger用来监视student那张表的update操作,只要实践update语句,就能激活触发器mytrigger

create trigger mytrgger3 on student for update
as
print '这是第三个触发器'
update student set name = '小小白' where id=1

注:当大家本着同一张表的一模二样操作定义了多少个触发器的时候,那四个触发器会被相同的时候触发。

修改触发器

语法:

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

AS

若干T-SQL语句

GO

示例:

ALTER trigger mytrgger3 on student for update

as

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

翻看触发器的内容

exec sp_helptext 触发器名称

示例:

exec sp_helptext mytrgger

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

select * from sysobjects where xtype=’TR’

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

张开或关闭触发器

剥夺触发器

disable trigger [触发器名] on 表名

示例:

disable trigger mytrgger3 on student

开启触发器

enable trigger [触发器名] on表名

示例:

enable trigger mytrgger3 on student

删除触发器

Drop trigger 触发器

示例:

drop trigger mytrgger3

练习

在学员表中定义贰个insert触发器,当插入一条数据的时候,检查实验插入的那个学生的数码是不是高于27虚岁,若是超出删除那条数据并提示,该学生抢先了征集年龄。

答案在662笔记

剔除与立异

当我们在数据库中实行增加和删除改操作的时候,系统会在sql server服务器的内部存款和储蓄器中期维修改两张一时表Deleted和Inserted有时表。

一经咱们今日需求支付叁个学校图书处理系列,各样同学前去借书都会扭转二个借书记录。

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

2、创设借书记录表,在那之中借书记录供给与学号相关联(不是id)。

CREATE TABLE borrowRecord(

bid int identity primary key not null,

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

borrowDate datetime, --借书日期

returnDate datetime --还书日期

)

删除示例:

--当学生结束学业之后,要是学生表中的上学的小孩子记录就须求被删去掉。借书记录也急需同期被删除掉。

create trigger delStudentWithBR on student for delete

as

delete borrowRecord 

from borrowRecord as br,deleted as d

where br.sNum=d.sNum

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

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

履新示例:

常见产生在相关联字段的值发生了改观。

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

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 。

练习:

用上例中的表,固然结业年龄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

有的时候表与表变量

基本概念

表变量

表变量与大家以前在其他语言之中见到的二维数组,是有不胜枚举日常之外的,可是大家得以像操作数据表同样来操作它,只需求记住一点,那正是表变量是存在于内部存款和储蓄器中的,它的实施成效高,然则它像变量同样有成效域和生存周期。

临时表

用于长期存款和储蓄数据据的表,它选用的是系统的tempdb数据库。

主干规范

在能用表变量的时候,就尽可能的行使表变量。实用不容许的状态下,再采纳有的时候表,因为有时表对系统的开支比相当的大。

不过当临时数据量非常的大时,才提出采纳临时表。

始建表变量

语法

declare @变量名 table(字段列表….)

示例:

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

示例2:

declare @mytable table(

id int identity primary key,

name nvarchar(50),

age int

)

insert into @mytable values

('小强',17),

('小明',13),

('小红',18),

('小丽',1)

select * from @mytable

示例3,从数额表中读取数据存入表变量:

declare @ttt table(

id int, name nvarchar(50), age int

)

insert @ttt select id,name,age from student

select * from @ttt

创办有时表

语法:

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

示例1:

create table #tt(

id int,

name nvarchar(50),

age int

)

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

union all select 2,'小明',19

select * from #tt

示例2:

create table #tt2(

id int identity primary key,

name nvarchar(50),

age int

)

insert into #tt2 values('小强',17),

('小明',19),('小红',20)

select * from #tt2

练习:

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

用来随机函数生成肆十六个彩票号码,存入这几个表变量中。然后展现出具备的彩票号码。

客商自定义函数UDF

user define function

它可怜周边于累积进度照旧js中的function,常常而言它都以格外select语句来打开利用的,它的用法很像针对某些字段实行操作的聚合函数。

骨干语法:

创建UDF

CREATE FUNCTION [模式] 函数名

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

RETURNS 返回值的类型

AS

BEGIN

[若干语句]

RETURN (返回单一值)

END

示范1,定义一个函数将bit类型值转化为男或女:

CREATE FUNCTION backSex(@sex bit)

returns varchar(10)

AS

BEGIN

RETURN(

select case @sex

when 1 then '男'

when 0 then '女'

end

)

END

select name,dbo.backSex(sex) from student

示例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

用途:

它的可以起到简化查询语句的意义,制止编写制定重复的话语。

而且要在乎,它还足以再次来到表

练习:

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_游标

基础概念

当数据库在询问的那弹指间,它实际是将数据记录有序的一行行抽取来,那么游标在那个进度中的效率,就是表示最近正在读取的是第几行。

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

1、定义一个游标

能够清楚成在数码集上的指针,大家得以决定那一个指针来一条条的将数据集遍历出来,可能也得以只是用于获取一定的行,所以游标必得定义在select语句询问的数据集之上。

语法:

DECLARE 游标名称 cursor

[ LOCAL | GLOBAL ]

[ FORWARD_ONLY | SCROLL ]

[ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]

[ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]

[ TYPE_WARNING ]

FOR

select 查询语句

[ FORUPDATE [OF 字段名列表…] ]

概念游标的时候,我们要游标类型和游标变量,对于游标变量来讲,正是坚守t-sql的变量的准绳来定义的。

游标变量

笔者们能够在概念时先对游标变量赋值,也许定义完未来再赋值。

--在定义时直接赋值

declare myCursou cursor for

select id,name from student



--先定义后赋值

declare @myCursou cursor

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

LOCAL和GLOBAL只好二选一

用以定义游标的作用域,LOCAL是指游标能够在此时此刻批管理、函数或存款和储蓄进程中采用,GLOBAL是指游标对于日前数码连接来讲在全局有效。

示例:

declare myCursou1 cursor GLOBAL for

select id,name from student



declare myCursou2 cursor LOCAL for

select id,name from student

go

open myCursou1 --全局游标在批处理之外也可以访问

open myCursou2 --局部游标当批处理执行完之后就不存在了

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、张开游标

open 游标名称

游标须求开拓才干够利用

3、使用游标

将游标获得的数目传入局地变量

大家能够INTO关键字,将游标中的select语句询问后的值存入局地变量。

游标每便执行只回去一条数据。

示例:

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 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

提取的行不存在

 

4、关闭游标

close 游标名称

游标使用完后,须要关闭。

5、释放游标

DEALLOCATE 游标名称

对运用游标时质量上的部分建议

1、 游标对质量的成本特别严重的。

2、 用完游标之后自然要关闭和假释。

3、 尽量不要在数据量不小的时候利用游标。

4、 尽量使用FAST_FO凯雷德WA途达D火速只进格局来操作游标。

5、 使用游标常常会比直接select 查询慢2-3倍,尽管数据量异常的大这一个比重还或许会加多。借使能够用别样办法化解的主题材料尽量制止使用游标,它只是最后的一种选取。

练习

1、通过游标和循环,查询学生表中的奇数行的多少。

2、通过游标和存款和储蓄进度来达成对学生表的分页功效。

sqlServer_事务

举个例子说您去银行转账,你从帐户A转四千元到帐户B,实际上那就要分两步来操作,第一步,帐户A上扣除5000元。第二步,在帐户B上存服兵役仟元。假使第一步已做到,可是出于一些原因促成第二步未有正确实行的话。就能够招致很要紧的损失。

就此,我们就需求一种体制,来保险率先条语句实施之后,第二条语句也会执行。不过实际上情况下,我们很小概百分百确认保证这或多或少。因而退而求其次,用一种体制来保管,固然第二条语句未有科学施行的话,那么我们就撤除第一条语句所实施的操作,这种机制就称为事务。

能够形象的将其知晓为操作软件时的历史记录。

基本概念

事情供给显著的起来和截至点,就比方钦命在哪一步起头记录“历史记录”,然后在哪一步甘休历史记录。

SQL server中的select、insert、update和delete语句都得以产生工作的一部份。

作业的标志点

BEGIN TRAN 设置职业的初阶点。

COMMIT TRAN 提交业务,保存你所试行的操作,让其不可防止。

ROLLBACK TRAN 回滚事务,撤废你曾经进行的操作。

SAVE TRAN 保存标记符,保存点,正是将您的操作在此存档,允许将事情回滚到你日前保留的操作地方。

BEGIN TRAN

意味着二个政工单元早先,在此之后未有付诸的具有语句都属于职业的一某些。

语法:

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

COMMIT TRAN

交由事条,也正是事情的巅峰,当实践了commit tran之后,大家所举行的操作就完毕保存。

语法:

Commite tran[SACTION] [<事务名称>|<@事务参数>]

RollBack tran

回滚事务,在未有保存点的景况下,回滚到业务最先并未有试行操作时的境况,在有保存点的意况下,能够回滚到保存点。

语法:

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

SAVA TRAN

创制保存点,以便大家在作业回滚的时候引用它。

语法:

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

瞩目:当我们回滚事务的时候,保存点会自行被化解,就算用户保存了七个保存点,推行ROLLBACK时也会被整个清空。

假设此时还须要保存点的话,只可以重复成立SAVE TRAN了。

事务的实操

当我们展开事务之后,大家操作的实在都是缓存中的数据。唯有当提交业务的时候,操作才会写入日志。

示例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

示例2,使用保存点

BEGIN TRAN mytran

insert into student values('小小白',15,0,'中国','12234678')

SAVE TRAN mysave

delete student where id=1

ROLLBACK TRAN mysave

COMMIT TRAN

练习

1、模拟提款机,达成一个银行转账的事例,成立帐户表account表(id、accName、accNum、money),达成从一个帐户转5000块到别的八个帐户,固然出现错误,则事务回滚,没有不当则成功转帐。

 

本文由永利集团登录网址发布于计算机入门,转载请注明出处:我的ASP.NET学习笔记011SqlServer基础知识

关键词: