数据库开发（一）SQLServer基础

作者 Zhendong Ho 日期 2019-03-25
SQL Server
数据库开发（一）SQLServer基础

数据库介绍

数据库特点

  1. 海量存储
  2. 查询速度快
  3. 控制并发访问
  4. 安全性
  5. 数据完整性（保存在数据库中的数据是正确的、真实的）。

各种DBMS的特点

  • MySQL：适合网站系统，速度很快，但是数据要不不严谨，去掉了很多中小型企业中不常用的功能。（facebook、symantec、wikipedia、百度博客、新浪博客、twitter、youtube、wordpress）

  • MSSQLServer：与.NET结合很好，功能比MySQL多，但是处理大量数据、大交易量表现表现并不十分好。（雅虎广告业务、携程网、速8酒店、阿里巴巴呼叫中心、凡客诚品）

  • 其他数据库：DB2（大型）、Oracle（大型）、Access（文件型数据库）、SQLite（极其轻量级数据库）。

主键

主键概述

主键就是数据行的唯一标识。

主键的作用：唯一标识表中的一条记录。

主键的特点：

  1. 不能重复。
  2. 主键不能为空。不允许为null。

主键的选择

主键有两种选用途径：业务主键和逻辑主键。推荐使用逻辑主键。

选择什么样的列作为主键：

  1. 不允许为空的列。
  2. 没有重复的列。
  3. 与实际业务没有关系的列。（逻辑主键）
  4. 稳定的列。（列种保存的数据不经常改变）
  5. 选择单列作为主键。（当通过多列共同唯一标识表中的一条记录的时候，此时可以选择多列来共同组成一个主键，叫做组合主键或复合主键）一般不推荐使用组合主键。
  6. 尽量选择数字类型作为主键，不要选择字符串，或者数据类型比较大的列作为主键。（选择比较小的列作为主键）

注意：一个表中只能有一个主键（也可以没有主键，不推荐）。当建立好主键后，默认会建立索引，而实际数据在磁盘上存储的顺序和主键的顺序是一致的。因为实际的物理顺序只能有一种，所以一个表中只能有一个主键。

外键

数据冗余的问题：

  1. 造成了存储空间的浪费。
  2. 更新异常，删除异常。

解决方法：

把原来表中的数据拆分成多个表来存储。使用主键和外键将两张表中的数据关联起来。因为外键列中引用的是另外一张表中的主键列的数据，所以外键列中的数据是不能随便写的，必须是在另外一张表中的主键列中已经存在的数据。

SQL Server版本

以SQL Server 2008版本为例

SQL Server 2008 Datacenter（x86，x64，ia64）：数据中心版，功能最全。

SQL Server 2008 Enterprise（x86，x64，ia64）：企业版，功能和数据中心一样，但是对服务器内存有限制。

SQL Server 2008 Standard（x86，x64，ia64）：标准版，功能比数据中心版少。

SQL Server 2008 Web（x86，x64，ia64）：有功能限制。

SQL Server 2008 Workgroup（x86，x64，ia64）：有功能限制。

SQL Server 2008 Developer（x86，x64，ia64）：开发者版，功能与数据中心版一样，只用于学习和测试，不能商用。

SQL Server 2008 Datacenter（x86，x64，ia64）：快速版，功能极其有限。

查看SQL Server版本信息

print @@version

数据库身份验证方式

两种验证方式

用户名验证和Windows验证。

启用sa账号

  1. 先用Windows账号登陆数据库。

  2. 启用Windows身份验证方式和sql server身份验证方式。

    选择“实例”→右键→属性→安全性→服务器身份验证→SQL Server和Windows身份验证（选中）→确定→重启SQL Server服务。

  3. 启用sa账号。

    安全性→登录名→sa→右键→属性→状态→登录→启用→确定。

  4. 修改sa密码。

    安全性→登录名→sa→右键→属性→常规→改密码→确定。

注意：

  • 一般要把sa账号禁用，防止其他人通过账号连接数据库。
  • 密码的“强制实施密码策略”，要求用户输入一个保密性强的密码，一般不建议开启选项。

卸载数据库

  1. 先卸载。在控制面板卸载所有带“SQL Server”的程序。
  2. 删除安装目录。
  3. 删除注册表。（所有带有“SQL Server”的注册表）

通过设计器创建数据库和表

SQL Server实例介绍

  • 不同实例对应的不同文件夹：C:\Program Files\Microsoft SQL Server。
  • 可以在同一台计算机（服务器）上安装多个数据库实例。
  • 多个实例之间互相完全不知道，没有关系，安全。

创建数据库

数据库→右键→新建数据库→数据库名称、存储路径、初始大小和自动增长。

创建好一个数据库后，每个数据库必须包含：

  1. 主数据文件（*.mdf）
  2. 日志文件（*.ldf）

还可以包含：

  • 任意多个次要数据文件（*.ndf）
  • 多个其他日志文件（*.ldf）

创建表

数据库→具体数据库→表→右键→新建表→添加列名、数据类型、设置主键→保存。

注意：bit数据类型在写程序的时候只能用1或0来表示，但是在设计器中只能用true或false来表示。

分离、附加

当要拷贝数据库给别人的时候，直接复制粘贴会报错。这时需要用到分离和附加：

数据库→具体数据库→右键→任务→分离（勾上删除连接）→确定→复制粘贴→附加。

另外还可以使用脱机、联机的方法：

数据库→具体数据库→右键→任务→脱机→复制粘贴→联机。

SQL Server常用数据类型

image类型

image类型，用来存储byte[]。可以用来存储任何类型的数据。相当于varbinary(MAX)。

字符串类型

char、nchar、varchar、nvarchar、text、ntext、varchar(max)、nvarchar(max)，都表示字符串类型。

带n和不带n的区别

char(2)，表示可以存储2个字节。如“ab”、“12”、“啊”。存储中文等双字节字符，占用2个字节，存储英文，数字等每个字符占用1个字节。

nchar(2)，表示可以存储2个字符，每个字符占用2个字节。无论存储中文还是英文或数字等，每个字符都是占用两个字节。

注意：不带n的这些数据类型，长度最长可以设置为8000。而带n的这些数据类型，长度最长可以设置为4000。如char(8000)，varchar(8000)、nchar(4000)、nvarchar(4000)。

带var和不带var的区别

带var：表示可变长度。char(10)，存储1个字节也是要占用10个字节，会自动补9个空格。

不带var：表示固定长度。varchar(10)，会根据实际存储的数据的大小动态重新分配存储空间，相对来说节省存储空间。

注意：固定长度的字符串相对于可变长度的字符串来说效率要高一些。在数据长度固定的情况下优先选用固定长度，可以省去计算长度的过程，提高效率。

text和ntext

text相当于varchar(max)、ntext相当于nvarchar(max)。max表示有4个G大小，一般情况下足够存储任何字符串。

什么时候必须使用nvarchar或nchar

当数据库的排序规则不是中文的时候，如果想要存储中文字符，必须使用nvarchar或nchar，否则数据会乱码。nvarchar和nchar是针对所有双字节字符使用的。所以，要存储双字节的字符数据时，一般使用nvarchar或者nchar。

系统数据库介绍

master：记录SQL Server系统的所有系统级信息。例如，登录账户信息、链接服务器和系统配置设置、记录其他所有数据库的存在、数据文件的位置、SQL Server的初始化信息等。如果master数据库不可用，则无法启动SQL Server。

msdb：用于SQL Server代理计划警报和作业。数据库定时执行某些操作、数据库邮件等（定时任务）。

model：用于SQL Server实例上创建的所有数据库的模板。对model数据库进行修改（如数据库大小、排序规则、恢复模式和其他数据库选项）将应用于以后创建的所有数据库。在model数据库中创建一张表，则以后每次创建数据库的时候都会有默认的一张同样的表。

tempdb：临时数据库，一个工作区间，用于保存临时对象或中间结果集。一个全局资源，可供连接到SQL Server实例的所有用户使用。每次启动SQL Server时都会重新创建tempdb（清空tempdb的数据）。

Resource：一个只读数据库，包含SQL Server包括的系统对象。系统对象在物理上保留在Resource数据库中，但在逻辑上显示在每个数据库的sys架构中。Resource数据库的物理文件名为 mssqlsystemresource.mdf和mssqlsystemresource.ldf。每个SQL Server实例都具有一个（也是唯一的一个）关联的mssqlsystemresource.mdf文件，并且实例间不共享此文件。

通过代码创建数据库和表

创建数据库

直接创建数据库，没有设置任何特殊选项，都是使用默认的。

create database MyDatabaseOne;

删除数据库

drop database MyDatabaseOne;

创建数据库（带参数选项）

create database MyDatabaseOne
on primary
(
    --配置主数据文件的选项
    name='MyDatabaseOne',--主数据文件的逻辑名称
    filename='c:\MyDatabaseOne.mdf',--主数据文件的实际保存路径
    size=5MB,
    maxsize=150MB,
    filegrowth=20%
)
log on
(
    --配置日志文件的选项
    name='MyDatabaseOne_log',--日志文件的逻辑名称
    filename='c:\MyDatabaseOne_log.ldf',--日志文件的实际保存路径
    size=5mb,--日志文件的初始大小
    filegrowth=5mb
)

在数据库中创建一个表

--将代码环境切换到MyDatabaseOne下
use MyDatabaseOne

create table Departments
(
    AutoID int identity(1,1) primary key,
    DepartmentName nvarchar(50) not null
)

删除表

通过代码，删除master数据库下的某些表。

use master
drop table Departments;
drop table TblTeacher;
drop table users;

创建一个员工表

--<员工表>：员工Id，身份证号，姓名，性别，入职日期，年龄，地址，电话，所属部门，Email
create table Employees
(
    EmpID int identity(1,1) primary key,
    EmpIDCard varchar(18) not null,
    EmpName nvarchar(50) not null,
    EmpGender bit not null,
    EmpJoinDate datetime,
    EmpAge int,
    EmpAddress nvarchar(300),
    EmpPhone varchar(100),
    DeptID int not null,
    EmpEmail varchar(100)
)
FEATURED TAGS
SQL Server
FRIENDS