Sql Server学习笔记

一、数据库的组成

由文件和文件组 组成

数据库文件

• 主要数据文件，存放数据和数据库的初始化信息。每个数据库有且只能是一个主要数据文件。.mdf 扩展名
• 次要数据文件，存放除了主要数据文件外的所有数据的文件。次要数据文件并不是必须的，可以没有，如果有的话，可以是一个，也可以是多个。** .ndf** 扩展名
• 事务日志文件，存放用于恢复数据库所有日志信息。每个数据库至少有一个日志文件也可以有多个。默认**.ldf** 扩展名

文件组

是数据库文件的一种逻辑管理单位，它将数据库文件分成不同的文件组，方便对文件的分配和管理。分为俩种类型：

• 主文件组：primary 主要数据文件和没有明确指派给其他文件组的文件
• 用户自定义的文件组。Create DataBase 或 Alter Database语句，filegroup关键字指定的文件组。
• 设计原则
  • 文件只能是一个文件组的成员
  • 文件或文件组不能由一个以上的数据库使用
  • 数据和日志信息不能属于同一个文件或文件组
  • 日志不能作为文件组的一部分

二、数据库常用对象

• 表：包含数据库中所有数据的对象，由行和列组成，用于组织和存储数据
• 字段：表中的列，一个表可以有多个列，自己的属性：数据类型（决定了该字段存储了什么类型的数据），大小（长度）
• 视图：表（虚拟表）一张或多张表中导出的表，用户查看数据的一种方式，结构和数据是建立在对表的查询基础上
• 索引 为了给用户提供一种快速访问数据的途径，索引是依赖于表而建立的，检索数据时，不用对整个表进行扫描，可以快速找到所需的数据
• 存储过程：是一组为了完成特定功能的SQL语句的集合（可以有查询、插入、修改、删除），编译后，以名称的方式存储在数据库中，以名称来调用，调用执行时，这些操作就会被执行
• 触发器：在数据库中，属于用户定义的SQL事务命令集合，针对于表来说，当对表执行增删改操作时，命令就会自动触发而执行
• 约束：对数据表的列，进行一种限制，可以更好规范表中的列。
• 缺省值：默认值，null

数据类型分类

• 整数类型

数据类型	范围	存储
bigint	-2^63(-9223372036854775808)~ 2^63-1(9223372036854775807)	8字节
int	-2^31(-2147483648)~ 2^31-1(2147483647)	4字节
smallint	-215(-32768)~215-1(32767)	2字节
tinyint	0~255	1字节

• 浮点型

  • float近似数值，存在精度损失，real近似数值。
  • decimal 精确数值，不存在精度损失

• 货币类型

  • smallmoney 8字节
  • money 4字节

• 字符类型

  • char 固定长度8000
  • varchar（n） 可变长度字符串，最多8000
  • varchar（max）可变长度字符串，最多1073741824
  • text 可变长度字符串，最多2GB

• Unicode 字符串(不会乱码）

  • nchar 固定长度4000
  • nvarchar（n） 可变长度字符串，最多4000
  • nvarchar（max）可变长度字符串，最多1073741824 / 2
  • ntext 可变长度字符串，最多2GB

• 日期类型

  • datetime 精确度高，3.33毫秒
  • datetime2 精确度更高，100纳秒
  • smalldatetime 精确到时分秒
  • date 仅存储日期
  • time 仅存储时间 精度100纳秒

• 其他类型

  • uniqueidentifier 存储全局标识符
  • bit 允许0、1、null

约束

定义：规定表中的数据规则。如果存在违反规则的行为，会被阻止。
可以在创建过程中，创建之后都可以创建约束。

• 主键约束： Primary Key约束
• 外键：Foreign Key约束，加强两个表的一列或多列数据之间的连接的。先建立主表，然后定义从表中的外键。只有主表中的主键才能被从表用来作为外键使用。主表限制了从表的更新和插入的操作。当删除主表中的某种数据。应该是先删除从表中相关的数据，再删除主表。
• Unique约束：唯一性约束，确保表中的一列数据没有相同的值。与主键约束相似，但又不同。主键只能有一个，但一个表中可以定义多个唯一约束。
• check约束：通过逻辑表达式来判断数据的有效性，用来限制一列或多列的值的范围。
• Default约束：默认值约束

三、数据库脚本

数据库脚本：用于创建数据库对象的语句集合

T-SQL

Transact-Sql 基于SQL（structured query language）结构化查询语言，用于应用程序和数据库之间沟通的编程语言。sql server 支持的脚本语言。
sql语言 ：一种有目的的编程语言，用于存取数据、查询、更新和管理关系数据库，高级的非过程化的编程语言。

SQL语言分类

• DQL数据查询语言：
  • select 查询；select 列（*）from 表名 where 条件 ；
• DML数据操纵语言 ：
  • insert 插入；insert into 表名（列，列） values （对应的值，对应的值）；
  • update 更新；update 表名 set 列名 = 值 where 条件；
  • delete 删除；delete from 表名 where 条件
• DDL数据定义语言：创建数据库及其对象 Create/Alter/DRop Database/Table/View/Proc/index
• DCL数据控制语言：用于授予或回收访问数据库某种特权，对数据进行监视等commit 提交，rollback回滚grant授权。

脚本创建数据库

master：系统数据库，他记录了SQl Server系统的所有系统级信息，还记录了所有其他数据库的问价位置，SQL Server的初始化信息

使用脚本创建数据库

use master;--选择要操作的数据库
go--批处理命令
--创建数据库
create database TestNewBase--数据库名称
on primary--文件组
(
name='TestNewBase',--数据库主要数据文件的逻辑名
filename='E:\新建文件夹\TestNewBase.mdf',--主要数据文件的路径
size=5MB,--数据库主要文件的初始大小
filegrowth=1MB--文件的增量
)

log on --创建日志文件
(
name='TestNewBase_log',--数据库主要数据文件的逻辑名
filename='E:\新建文件夹\TestNewBase_log.ldf',--主要数据文件的路径
size=5MB,--数据库日志的初始大小
filegrowth=10%--日志文件的增量
)
go

创建表

--数据定义语言DDL
use TestNewBase
go
--创建表
create table ProductInfos
(
Id int identity(1001,1) primary key not null,
ProNo varchar(50) not null,
proName nvarchar(50) not null,
TypeId int not null,
Price decimal(18,2) default (0.00) not null,
ProCount int default (0) null
)
go

--数据定义语言DDL
use 数据库名
go
create table 表名
(
--列名 数据类型 长度 是否为空 标识列 主键 默认值约束 列与列之前以逗号隔开
)
go

修改表

• 直接在原来的脚本基础上进行修改，如何删表重新执行脚本，这种方式，一般不采取，除非表是空表或者数据不重要。
• alter table 进行修改
  • 添加一列 ：alter table 表名 add 列名 数据类型 是否为空
  • 删除一列 ：alter table 表名 drop column 列名
  • 修改列 ：alter table 表名 alter column 列名 类型 是否为空

T-SQL创建约束

在创建表的过程中创建约束

• 主键约束：primary key
• 外键约束：foreign key references 主表名 （主键列名）
• check约束：check(sex =‘男’ or sex=‘女’)
• unique约束（唯一约束）：unique
• default约束 default（‘’）

在创建表完成后再创建约束
alter table 表名 add constraint 约束名 +

• 主键约束：primary key (列名)
• 外键约束：foreign key(列名) references 主表名 （主键列名）
• check约束：unique(列名逻辑表达式）
• unique约束（唯一约束）：unique(列名）
• default约束：default (缺省值）for 列名

T-SQl 插入数据（克隆表）

单表插入

• inster into 表名 （列名,列名）value (值,值）
• inster into 表名 （列名，列名）select 值，值

多条插入

• inster into 表名（列名，列名）values （值，值），（值，值）
• inster Test（列名，列名）select 值，值 select 值，值
• 关键字
  • union 去重
  • union 允许重复
  • union all比union效率高

克隆表

目标表在数据库中已存在

• inster into Test（MName）–目标表 select TypeName from ProductType --源表
  目标表之前数据库中不存在，执行操作时自动创建
• select TypeName into Test2 --目标表 from ProductType --源表

删除数据

• delete from 表名 where 不加where条件 ，会删除表中所有数据
• truncate table 表名 表数据清空，恢复到初始化，标识列也恢复
• truncate比delete效率高，但truncate不会记录日志，不激活触发器，是即时操作。delete会记录在日志中，可以在事务中恢复。

T-SQL查询数据

查询所有数据

select * from 表

查询部分信息

select 列，列 from 表 where

别名

select 列 as 别名 from 表 where

排序

• 主键默认排序，从小到大
• order by 关键字排序 升序asc 降序desc
• 不管是否有条件，order by永远放后面
• select 列，列 from 表名 order by 列 desc

模糊查询

select 列，列 from 表名 where 列 like 。。
完整查询和模糊查询 like 4中匹配方式

• % 前后0个或者多个
• _ 匹配单个字符
• 范围匹配 ，括号中所有字符串中的一个
• [^] 不在括号里所有字符之内的单个字符

聚合函数

• count记录个数
• sum 求和
• avg 求平均
• max 最大值
• min 最小值

分组查询

group up列，列 结合聚合函数，根据一个列或多个列结果集进行分组。

连表查询

分类：内连接，外连接，全连接，交叉连接

• 内连接：inner join 使用比较运算符= > < <= >== <> 进行表间的比较 查询与条件相匹配的数据。

  • 等值连接：相匹配的数据查询出来，没有匹配的就不显示。
  • 显示连接：表 inner join 表 on 条件 where
  • 隐式连接：select * from 表，表 where 关联条件

• 外连接 ：左连接，右连接，全连接

  • left join on返回左表所有行，右表没有匹配上的显示null
  • right join on 返回右表所有行，左表没有匹配上的返回null

• 全连接

  • full join全外连接 ，返回左表和右表中所有行，当，某一行在另一张表中没有匹配时，则返回null

• 交叉连接 cross join 笛卡尔积

  • 如果不带where子句时，返回被连接的俩个表的笛卡尔积，返回的行数是俩个表行数的乘积。
  • 带where 子句，等价于inner join 返回的匹配数据

类型转换函数

• convert函数：convert（类型（长度），表达式）
• cast函数：cast （表达式 as 类型）

字符串操作

• charindex：charindex（‘b’,‘abc’）,返回字符串指定子串出现开始位置，索引从1开始

• patindex：patindex（‘%bc%’,‘abcd’）,返回字符串指定子串出现开始位置，子串前后必须带%

• 大小写转换

  • upper（大写转小写） ：upper（‘abvd’）
  • lower（小写转大写） ：lower（“JDIJNE”）

• len（取长度）：len（‘abc’）

• 去空格

  • ltrim（去左边空格）：ltrim（“”）
  • rtrim（去右边空格）：rtrim（“”）

• 取子串

  • left（‘abcdefg’,4）;返回字符串从左边起4个
  • right（‘abcdefg’,4）;返回字符串从右边起4个
  • substring（‘abcdefg’,3,4）;返回字符串从第个起，取4个字符串

• replicate（‘abc’,4）;返回重复4次的字符串

• reverse（‘abcdefg’）;返回翻转过来的字符串

• replace（‘abcdefg’,‘cd’,‘ss’）;字符串替换

• stuff（‘abcdefg’,2,3,‘tt’）；返回attefg，用指定的子串替换指定位置长度的子串后，生成字符串

索引

索引的作用：数据的查询，处理速度–应用程序成败的标准，最普遍的优化方式
索引是什么？一本书，目录 查找
索引 --目录，快速定位我们要查找的数据，而不是去扫描整个表。从而可以加快我们查询的速度，提高系统的性能。
索引的目的 可以更快速高效地查询数据，减少系统的响应时间。
索引分类：

• 聚集索引：Clustered 逻辑顺序和物理顺序是一致的，最多只能一个，或者没有
• 非聚集索引：NonClustered 逻辑顺序和物理顺序是并不一致的。可以有多个，也可以没有
• 非聚集索引比聚集索引效率低

视图

视图：虚拟表 有一个或者多个表通过查询而定义的，将查询定义保存起来，实际不包括数据。
与表的区别：表存储数据的地方，视图存储的是查询语句
作用：简化查询，增加数据的保密性，安全性上得到的保证
缺点：只是简化查询，并不能提高查询速度，增加了维护成本。
分类：

• 标准视图：存储查询定义，没有存储数据
• 索引视图：被具体化了的，创建了索引，显著提高查询性能，聚合了很多行的查询，不太适合经常更新的基本数据集
• 分区视图：一台或者多台服务器水平连接一组成员表的分区数据

存储过程

存储过程：一种为了完成特定功能的一个或者一组sql语句集合。经编译后存储在服务器端的数据库中，可以利用存储过程来加速sql语句的执行，调用名称，传入参数，执行来完成特定功能。
分类：

• 系统存储过程：master数据库中，其他数据库中是可以直接调用，并且不必在前面加上数据库名，创建数据库时，这些系统存储过程在新的数据库中自动创建。
• 自定义存储过程：用户自己创建。可以传入参数，也可以传入返回值，表明存储过程执行是否成功。里面可以只是一个操作，也可以包含多个。
  • 执行：execute/exec 存储过程名 参数列表（多个参数，以逗号隔开）

优点：

• 提高应用程序的通用性和可移植性。多次调用，而不必重新再去编写，维护人员可以随时修改。
• 可以更有效的管理数据库权限
• 提高执行sql的速度
• 减轻服务器的负担

缺点

• 专门维护他，占用数据库空间

语法

• create proc 存储过程名
• @userName int；
• @userName varchar（50）
  • as
  • begin
    • –T-SQL 语句
  • end
  • go