SQL（Structure Query Language结构化查询语言）
一、使用数据库（DataBase）的必要性
可以结构化的存储大量的数据信息，方便用户有效的检索和访问
有效的保持数据的一致性、完整性、降低数据冗余
满足应用的共享和安全方面
二、Database基本概念
1、数据
描述事物的符号记录称为data。（在database中data是以“记录record”形式按统一的格式进行存储的，相同格式和type的data统一放在一起。）
2、Database和database table
Table用来存储具体data的，database是table的集合。
例：关系型database是由字符或数字组成字段、字段组成记录、记录组成表
3、Database system and database management system
数据库管理系统（DBMS，database management system）实现对database资源有效组织、管理和存取的系统软件。
4、DBMS主要功能
database建立和维护功能：包括建立database的结构（structure）和data的录入与转换，database的转储与恢复，database的重组与性能监视等。
Data定义功能：定义全局data structure、局部逻辑data structure、存储structure、保密模式及信息格式（保证存储在database中的data是正确、有效和相容，以防止不和语义的错误data被输入或输出。）
Data操纵功能：data 查询query和data update两方面。
Datab的运行管理功能：数据库管理部分的核心部分，并发控制、存取控制、数据库内部维护等功能。
通信功能：DBMS与其它软件系统之间的通信。
数据库系统（DBS，database system）是一个人一机的系统，一般有硬件、操作系统、数据库、DBMS、应用软件和数据库用户（包括数据库管理员）组成。
数据库管理员DBA：负责database的update and 备份、database system的维护、user管理等工作，保证了database的正常运行。
注：DB、DBS、DBMS甚至DBT（表）在日常生活中没有严格区分，根据具体情况而定。
三、DBMS发展史
初级阶段：层次mode与网状mode的DBS。1969年IBM研制的层次mode数据管理系统IMS（information management system，信息管理系统）和20世纪70年代美国数据系统语言协商（CODASYL）下属数据库任务组（DBTG）提议的网状mode。
中级阶段：20世纪70年代初关系数据库开始出现，80年代初Oracle的DB2出现。关系型数据库将结构化查询语言SQL作为数据定义语言（DDL，data definition language）和数据操作语言（DML，data manipulation language）
高级阶段：Oracle支持“关系—对象”（E—R）
数据模型三个方面：数据结构（data structure）、数据操作（data manipulation）和数据约束。
网状：数据约束是零散孤立的（降低效率）
关系模型：“实体—关系”（E—R）包含实体、关系和属性三个要素。（实体、实体集、属性、联系）
补充：MySQL主要作用在网站的DB，LAMP会用到。（LAMP是指（Linux+Apache+Mysql/MariaDB+Perl/PHP/Python）一组通常一起使用来运行动态网站或者服务器的自由软件，本身都是各自独立的程序，但是因为常被放在一起使用，拥有了越来越高的兼容度，共同组成了一个强大的Web应用程序平台。随着开源潮流的蓬勃发展，开放源代码的LAMP已经与J2EE和.Net商业软件形成三足鼎立之势，并且该软件开发的项目在软件方面的投资成本较低，因此受到整个IT界的关注。从网站的流量上来说，70%以上的访问流量是LAMP来提供的，LAMP是最强大的网站解决方案。）
四、关系数据库的基本概念
存储结构：多个二维表格
每一行称为一条记录
每一列称为一个字段
数据表与数据库之间存在相应的关联，这些关联将用来查询相关的数据。这也就是一个数据表。
五、主键与外键
主键primary key
每行记录都必须是唯一的，可以保证记录（实体）的唯一性，不允许NULL空值。
如果一个属性集能唯一地标识表的一行而又不含有多余的属性，那么这个属性集称为候选键。表中可以有多个候选键，但是只能有一个候选键可以选作表的primary key，所有其他候选键称为备用键。
外键 foreign key （将表关联起来）
通过外键foreign key 可以使用这些表之间关联起来。
六、数据完整性规则
1、实体完整性规则
要求关系中的元组在primary key 的属性上不能有kull。
2、域完整性规则
也称列完整性规则，指定一个数据集对某一个列是否有效或确定是否允许kull空值。
3、引用完整性规则
不允许引用不存在的元组。
4、用户定义的完整性规则
针对某一具体数据的约束条件，有应用环境而定。
SQL server 2008介绍
数据加密
增强了审查
自动修复页面
扩展事件
资源监控器
ADO.NET实体框架
安装条件
内存最小512MB，硬盘空间2GB。提示安装Microsoft .NET Framework 和 Windows Installer。
七、系统数据库
Master 数据库：记录SQL server 系统所有系统级别的信息，它record所有的登录账户和system configure 设置。Master database record 所有其他的database 信息，其中包括database file的位置，同时还record SQL server的init信息。
Model 数据库：用作SQL server 实例上创建的所有数据库的模板。对model database进行的修改将应用于以后创建的所有database。
Msdb 数据库：用于SQL server 代理计划警报和作业，所有的task 调度、报警、manipulation都存储在该database中，该database还用于存储所有database备份历史。SQL server agent将会使用这个database。
Tempdb 数据库：用于保存临时对象或中间结果集。在SQL server 每次restart时都会重新创建tempdb database，因此，该database在system restart时总是clean。
默认位置在目:Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA
数据库：服务器 实例 数据库 表格 记录 字段
八、SQL server 常用工具
SQL server management studio：用于访问、配置、控制、管理和开发SQL server所有组件。
SQL server profiler：提供用于监视SQL server 数据库引擎实例
SQL server 数据库优化顾问：可以协助创建索引、索引视图和分区的最佳组合。
Business Intelligence Development Studio商业智能开发平台：用于Analyze services 和Integration Services 解决方案的集成开发环境。
命令提示实用工具：从命令提示符管理SQL server
SQL server 配置管理器：管理服务器和客户端网络配置设置（TCP/IP 端口号1433）
导入和导出数据：提供了一套用于移动、复制及转换数据的图形化工具和可编程对象。
SQL server 安装程序：安装、更改或升级SQL server实例中的组件。
SSMS（SQL Server Management Studio，结构化查询语言服务管理工作室）集成了DBS常用的管理工具；BIDS（business intelligence development studio商业智能开发平台）是用于数据提取、数据转换、数据分析及报告的工具；SQL server profiler用于帮助系统管理员监视数据库和服务器的行为。
九、BIDS介绍
1、BIDS中的分析服务（analysis services）
多用于多维数据集、维度、挖掘结构、数据源、数据源视图和角色的模板，并提供用于处理这些对象的工具。
2、BIDS中的整合服务（integration services）
用于开发ETL（Extract，数据抽象 Transform，转换 loading，装载）解决方案的integration services项目。
3、BIDS中的报告服务（reporting services）
用于开发报表解决方案的报表模型项目和报表项目。
SQL server profiler结构化查询语言服务分析器
逐步analysis分析有问题的query查询并找到问题的原因
查找并诊断运行慢的query
捕获导致某一问题的一系列Transact-SQL语句。然后复制此问题在诊断此问题。
监视SQL server 的性能以优化工作负荷
使性能计数器与诊断问题关联
支持对SQL server实例上执行的manipulation操作进行审核。审核记录record与相关的manipulation，供安全管理员以后复查。
十、文件类型
主数据文件：扩展名.mdf包含数据库的启动信息，指向数据库中的其他文件。
次要数据文件：扩展名.ndf除主数据文件以外的所有其他数据文件都是次要的数据文件
事务日志文件：扩展名.ldf包含恢复数据库所有事务日志的信息
文件流（filestream）数据文件：能在文件系统中存储非结构化的数据。
十一、数据文件
有若干个64KB大小的区（Extent）组成，每个区有8个8KB的连续页（Page）组成。最小存储单位称为页，一个页的大小是8KB（磁盘I/O操作manipulation在页级执行）
十二、事务日志
事务是一个或多个Transaction-SQL语句的集合，相当于一个“原子”的任务，要么执行成功，要么完全不执行。
当灾难disaster发生时可以通过事务日志record记录的T-SQL语句，可以恢复database。
注：
新建的数据库会生成两个文件，一个是主数据文件，另一个是事务日志文件。
当数据文件或日志文件满时，它们会根据设定的初始值自动增大文件的容量。
创建数据库的权限默认授予sysadmin和dbcreator服务器角色成员。
十三、数据库管理
1、扩展extend数据库
扩展数据库时，必须使数据库的大小至少增加1MB，还可以指定允许文件增长到的最大值，这样可以防止文件无限制地增大，导致用尽整个磁盘空间。
2、收缩contract数据库
Database在使用一段time后，时常会因为data delete而造成database中空闲空间太多的情况，这时就需要较少分配给database file和事务日志file的disk空间，以免浪费disk空间。
注意：在contract database时，无法将整个database contract的比其初始大小更小。但是，使用contract file时，可以将各个database file contract得比其初始大小更小。
3、分离和附加数据库
分离separate数据库：将数据库从SQL server实例中移除move，但是要保证数据库中的数据文件和日志文件完整无损。之后这些文件可以重新附加到其他数据库实例中。
附加addition数据库：附加数据库中，所有数据文件（主数据文件和辅助数据文件）都必须可用。（注：如果数据库的事务日志文件丢失，数据文件保持完好，也可以附加成功）
4、删除delete数据库
一旦删除数据库，它即被永久删除。
十四、表的基本概念
行 记录（每一行代表由表建模的对象的一个单独的实例）
列 字段（每一列代表由表建模的对象的某个属性）
十五、数据完整性
1、实体完整性
实体完整性通过UNIQUE（唯一）索引，unique restriction唯一约束或Primary key 约束，强制表的标识符或primary key 的完整性。
2、域完整性
指特定列的有效性。可以使用输的类型，限制格式（通过使用Check restriction 检查约束和规则）、Default 定义、not null 定义和规则。
3、引用完整性
引用完整性确保键值在所有表中的一致，这类一致性要求不引用不存在的值。
防止用户执行的manipulation操作
在主表中没有关联行的情况下载相关表中添加或更改行
在主表中更改值（可导致相关表中出现孤立行）
在有匹配的相关行的情况下delete主表中的行
4、用户定义完整性
可以定义特定业务规则。所有完整性类别都支持用户定义完整性。
Primary key unique唯一标识表中的行数据，一个主键值对应一行数据。主键由一个或多个字段组成，其值具有unique唯一性，而且不允许取kull空值，一个表只能有一个primary key。
十六、数据类型
精确数字、近似数字、日期和时间、字符串character、Unicode character统一编码字符串、binary character二进制字符串、其他数据类型。
1、精确数字
Int ： 主要整数数据类型，该数据类型在database里占用4个字节空间。
Decimal： 固定精度和范围的数值型数据。
Money： 占用8个字节空间
Bit： 属于整型数据，其值只能是0、1或NULL。这种类型的数据只有两种可能。
2、日期和时间date and time
Date： 占用3个字节
Time： 占用5个字节
Datetime： 占用8个字节
3、字符串character（使用时要加单引号）
Char 最多包含8000个字节
Varchar 一般用作备注信息
Text 文本
4、Unicode character 统一编码字符串
比字符串多个n，并且增加了一倍。
十七、标识符列
列的数据类型为不带小树的数值类型
在进行插入insert manipulation时，该列的值由system按一定规律生成，不允许kull
列值不重复，具有标识表中每一行的作用，每个表只能有一个标识列。创建一个标识列，通常要指定三个内容。
类型type：在SQL server 2008 中，标识符列type必须是数值type。注意项：decimal和numeric时，小数位数必须为零。另外还需要注意每中data type所表示的数值范围。
种子seed：指派给表中第一行值，默认值1。
递增量increment：相邻两个标识值之间的增量，默认值1。
十八、检查约束
Age >= 0 and age <=200 约束的范围在0到200之间。
十九、T-SQL的组成
DML（Data Manipulation Language，数据操作语言）：用来query、check、delete和修改database中的data。
DDL（Data Definition Language，数据定义语言）：用来建立database，database对象和Definition定义其列，大部分是以Create开头的命令。
DCL（Data Control Language，数据控制语言）：用来控制database组件的存取许可存取权限。
二十、使用T-SQL语句Manipulation Database Table
1、插入语句：
将data插入到表中
Insert [into] <表名> [列名] values <值列表>
Into ：可选可略
例如：
Insert into student （姓名，***，学号）
Values （‘姓名’，‘***号’，‘学号’）如果姓名是primary key 的话则不需要在写。
插入数据的时候注意一下事项。
每次插入一整数据，不可能只插入半行或者几列数据。
数据值的数目必须与列数相同，每个数据值的数据类型，精度和小数位数也必须与相应的列匹配。
Insert 语句不能为标识符列，当插入数据的时候，用单引号将其引起来。
如果在设计表的时候指定某列不允许为空，则该列必须插入数据，否则将报告错误信息。
插入的数据项要求符合检查约束的要求。
尽管可以不指定列名，但是应养成好习惯，明确指定插入的列和对应的值。
2、更新数据
Update <表名> SET <列名=更新值> [ where <更新条件>]
Set：后面可以紧随多个数据列的更新值，不限一个。
Where：可选用来限制条件（指谁）
例如：
Update student set 成绩=’88’ where 姓名=’玉兰’
3、删除数据
只删除整条record，不会只删除单个字段。所以在delete后面不能出现段名。
Delete from <表名> [where <删除条件>]
例如：删除所有记录delete from <表名>
输出record行：Truncate table <表名>
Truncate截断 table与delete的区别如下
Truncate截断 table 不带where子句，只能将整个表数据清空。而delete可以带where子句，允许按条件删除某些record。
Truncate截断 table不记录事务日志，delete每删除一行就记录一条事务日志。
Truncate截断 table删除后会重置，而delete不会重置标识符列。
Truncate截断 table不能用于有foreign key外键约束引用的表，这时就需要delete语句。
Truncate截断 table所删除的数据将无法恢复。
4、查询数据
Select <列名> from <表名> where <查询限定条件>
Select 语句的语法
Select select_list
[into new_table_name]
From table_name
[where search_conditions]
[group by group_by_expression] [having search_conditions]
[order by order_expression [ASC | DESC] ]
select子句：指定查询内容 select_list ：用于指定查询字段列表
Into子句：用于把查询结果存放到一个新表中。New_table_name指定新表的名称
From子句：指定查询源。Table_name指定需要查询的表名称
Where子句：查询条件。Search_conditions 为由字段组成的条件表达式或逻辑表达式
Group by子句：指定查询结果的分组条件。Group_by_expression指明分组条件，通常是一个列名。
Having子句：指定分组搜索条件，通常与group by 子句一句使用
Order by子句：指定查询结果的默认排序方式。Order_expression指定排序的规则，其中ASC表示升序，DESC表示降序。
比较运算符 | |
运算符 | 含义 |
= | 等于 |
> | 大于 |
< | 小于 |
> = | 大于或等于 |
< = | 小于或等于 |
< > | 不等于 |
! = | 不等于，等同于 |
Between | 指定值的包含范围。使用and分隔开始值和结束值 |
Is[not]null | 指定是否搜索空值或非空值 |
like | 模糊查询 |
IN | 是否在数据范围里面 |
‘_’ | 任何单个字符 |
% | 任意长度字符串 |
[] | 括号中所有指定范围内的任意 |
[^] | 不在括号中所指定范围内的任意一个字符 |
Not | 取反操作manipulation |
And | 组合两个条件，在两个条件都为TRUE时取值为TRUE |
Or | 组合两个条件，在两个条件之一为TRUE时，取值为TRUE。 |
查询表中所有列
Select * from table_name
查询表中特定列
Select column_name_1，column_name_2··from table_name
Column_name指定列名
查询表中特定行
Select select_list from table_name where search_conditions
使用top关键字来限制查询返回的行数
Select top n select_list from table_name//查看前N行
改变查询结果集列名称
Select column_name AS column_alias from table_name
Select 姓名 as name，*** as idcard form student//姓名别名为name，***别名idcrad
等同于Select column_name column_alias from table_name
等同于Select column_alias = column_name from table_name
Select SUM（成绩） as 总成绩 from student //student的总成绩
查询结果排序
Select select_list from table_name order by column_name [ASC|DESC]
分组查询
Select select_list from table_name group by column_name [ASC|DESC]
T-SQL语句中的聚合函数
SUM：sum返回表达式中所有数值的总和，sum只能用于数字类型的列
AVG：avg函数返回表达式中所有数值的平均值，AVG函数也只能用于数字类型的列
MAX和MIN：Max返回表达式中的最大值，min返回表达式中的最小值，它们都可以用于数字型。
COUNT：count返回表达式中非空值的计算，count可以用于数字和字符类型的列，另外也可以使用星号作为count的表达式
插入数据
Select select_list into new_table_name from table_name
使用T-SQL实现多表查询
内连接（inner join）“求和”
外连接“并集”
左外连接（Left join 或left outer join）：如果左表的某一行在右表中没有匹配行，则将为右表返回空值，否则返回相应值。
右外连接（right join或right outer join）：如果右表在某一行在左表中没有匹配行，则将为左表返回空值，否则返回相应值。
全链接/完整外连接（full join 或full outer join）：当某一行在另外一个表中没有匹配时，另一个表返回空值，否则返回相应值。
