简述SQL结构化查询语言

q528

　　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  
　　大于
　　<
　　小于
　　> =
　　大于或等于
　　< =
　　小于或等于
　　< >
　　不等于
　　! =
　　不等于，等同于
　　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）：当某一行在另外一个表中没有匹配时，另一个表返回空值，否则返回相应值。