sql server 2008 r2 基础

微软   关系型数据库管理系统


一:数据库模型
1.层次
2.网状
3.关系   -数据表 与数据表


二:关系型完整性规则
1.实体完整性  必须   -主键不为空
2.参照完整性  必须   -外键不为空
3.用户定义完整性     -约束条件


三:范式理论-数据保持数据完整性同时最小化冗余数据的结构
关系型数据库6种范式:1-3NF，Boyce-Codd范式（BANF）4,5
1NF范式 同一列不能有多个值.
2NF      员工部门表.分两个表。主外键
3nf      表中字段数据不存在函数依赖关系.如 工资 奖金


四:E-R 模型  (Entity-Relationship) 实体-联系数据模型 1976提出.
关系.1对多 1对1 n对n




五:sql Server 2008 体系结构
1.数据库引擎 基本操作
2.Analysis Services 分析处理.数据挖掘
3.Reporting Services 创建和发布报表及报表模型的图形工具和向导
4.Integration Services 数据集成平台，数据的提取,转换,加载等


六:IIS服务








七:安装


SQL Server Management Studio 使用
 集成环境.用于访问 配置 管理 开发sqlserver的所有组件
 组合图形工具.脚本编辑器
 
 
 一 
 1.创建数据库
 
  create database stuDB 
  on  primary  -- 默认就属于primary文件组,可省略
  (
  /*--数据文件的具体描述--*/
      name='stuDB_data',  -- 主数据文件的逻辑名称
      filename='C:\luis', -- 主数据文件的物理名称
      size=5mb, --主数据文件的初始大小
      maxsize=100mb, -- 主数据文件增长的最大值
      filegrowth=15%--主数据文件的增长率
 )
 log on
 (
 /*--日志文件的具体描述,各参数含义同上--*/
     name='stuDB_log',
     filename='C:\luis',
     size=2mb,
     filegrowth=1mb
 )  
 
 
查看数据库的状态
 2.函数
  select DATABASEPROPERTYEX('luis','property');
 3.使用系统存储过程
 sp_helpdb
 
 
数据快照限制
1.不能对数据库进行删除.分离.还原 影响性能.不能从源数据库或任何快照中删除文件.源数据必须在线.除非是镜像数据库


create database 快照名字
on(
name 
fILEName 保存的具体路径
)[...n]n个数据文件
as SNAPSHOT of 源数据名称


 错误:Standard Edition 不支持 Database Snapshot。
 解决:标准版不支持数据库快照，换成企业版的就可以了
 
 从快照恢复数据
 RESTORE DATABASE NAME
 FROM
 DATABASE_SHAPSHOT =快照名称
 
  
   二：数据类型
 整数
  bigint长整型  int smallint tinyint 
   
  decimal  mumeric 小数
  
  money smallmoney 货币
  
  float real       浮点 
  
  bit              0 和 1
  
  字符数据类型
  char 给多少就多少
  varchar 50实际只有30 就保存30
  text   不限长度
  nchar  可变.
  nvarchar
  ntext
  
  日期和时间
  datetime    1753-1-1 - 9999-12-31  时间精度3.33
  
  smalldatetime 1900-1-1 -2079-12-31 精度一分钟
  
  二进制类型
  1.binary 固定长度的二进制数据
  2.varbinay 可变    1 2 的长度由n值决定.n取值范围1-8000
  image   图像信息
  
  专用数据类型
  cursor 
  sql_variant
  table 
  timestamp
  uniqueidentifier
  xml
  


表.
系统表
用户自定义表
临时表
分






临时表: 创建的时候用# 或者 ##
 1.本地临时  单个数字符号#开头   当前用户     断开连接删除
 2.全局临时  两个数字符号##打头  任何用户可以看到  断开连接删除




标示列
IDENTITY(1,1)
 
 存储过程
 1.exec 
 exec sp_databases; --查看数据库
exec sp_tables;        --查看表
exec sp_columns student;--查看列
exec sp_helpIndex student;--查看索引
exec sp_helpConstraint student;--约束
exec sp_stored_procedures;
exec sp_helptext 'sp_stored_procedures';--查看存储过程创建、定义语句
exec sp_rename student, stuInfo;--修改表、索引、列的名称
exec sp_renamedb myTempDB, myDB;--更改数据库名称
exec sp_defaultdb 'master', 'myDB';--更改登录名的默认数据库
exec sp_helpdb;--数据库帮助，查询数据库信息
exec sp_helpdb master;


2.alter 
 alter table 表明
  add idd int null --新加的列
  
  


1.用户自定义数据类型
   参数.name 基于的数据类型. 
   
   
   
2.规则.单独的sql server对象.可以关联多表多列多方式完成对数据值的检验.
   可以使用函数返回验证.可以使用关键字between,like in 完成对数据的检查


区别
约束  创建表的时候指定.规则 单独数据库对象实现
一个列上只能用一个规则.可以使用多个CHECK约束
规则可以应用于多个列.还可以用于用户自定义数据库.check只能用于它定义的列


创建规则语句     ？？？
create rule name
as


exec 绑定 规则名称


==
drop 规则name--- 删除规则


解除规则绑定
 sp_unbindrule  规则name  用户自定义类型
 
 
 
 三:创建视图   
 
 create view view_name (列明)
 as 查询表达式
 约束条件
 
 修改视图
 alter view 视图name 列
 as
  select 语句
  约束条件
  
 drop view name 删除
 
通过视图插入内容
insert into 视图名 values(内容)  
 
 
 四：索引
单独的 物理的数据库结构.多列的一个集合
数据唯一性.加快数据检索速度.表与表的连接速度.


create 
 UNIQUE微信索引  CLUSTERED 聚集索引 可以建立1个 NONCLUSTERED 非聚集  可以建立多个


index 索引name


on 


table_name view_name


列名


删除
drop index 索引名






全文索引




1.聚集索引 和非聚集索引
聚集 表中数据的物理顺序 一个表只能包含一个聚集索引但可以包含多个列(组合索引)
非聚集索引存储在另外一个地方.索引存储在另一个位子.索引带有指针指向数据的存储位置.


区别:
相同的B-tree结构 


数据行不按非聚集索引键的顺序排序和存储
非聚集的叶层不包含数据页,相反叶节点包含索引行.
非聚集最多249个.包含PRIMARYKEY  UNIQUE约束创建的任何索引.不包括XML索引




五：架构 
  create schema name
  权限比如 
  table_defintition view_definition grant_statement
  revoke_statement deny_statement
   
    AUTHORIZATION owner_name  -角色 用户关联
    
    
sql server配置管理器
1.sql server2008 服务
2.sql 网络配置
3.sql nativeClient配置
打开:sqlservermanager.msc


  
  
语句:


select *


into table 将查询结果保存到另外一张表里


from table  
  
where 条件


GROUP  by  分组查看


HAVING 查询判断


order by 排序(asc  desc )
  


---------------------------------


distinct  去重
and 且 并且
or  或  
like  ''  模糊查询
order by  排序  asc 升序 desc 降序  
group by 分组      rollup() 求出所有的平均值 一组求出所有平均值 二组求出所有平均值
                   cube     对相同值的平均.后面进行总平均
max 最高
min 最小
avg 平均值 
sum 和
top 3  前三行. 可以放在select 后面
 
查询出每个课程编号的平均值
select 课程编号,avg(分数) from table where 考试编号='001' group by 课程编号




having  子句 group by 一起使用


 
插入语句
insert into table (列) values 值






insert ...select 语句  --将表1插入表2


inset  table (列);
select 列
from table_name
where 条件
--列要一一对应


select ...into 语句  -将查的数据.插入行的表
select 列 
into #table2  临时局部表
from table1
where 条件






------------------------------
Business Intelligence     bids
Development Studio
用于开发分析 集成 报表 服务的主要环境
  








update  语句
update [top]   table与view_name                  --top 前几行
set
列明='值'
where 条件




update from 字句  修改更新语句
  
update table set 列=a.列
from table a join  table3 b on  a.name=b.name
where 




删除
delete from table_name
where 




58:top关键字和top表达式  函数


top 数字或者百分比:expression percent 


子句with ties 有排序时使用  order by 


（1）SELECT TOP 20 * FROM student    --查询前20名学生的信息


（2）SELECT TOP 20 * PERCENT FROM student   --查询学生表中前20%的学生信息


声明一个变量.并赋值
declare @i int
set @i=20
使用 (@i)




六:compute 子句


compute  avg count max min stdev stdevp var varp sum
列
by 依据某个列产生合计


如：
select * from table where age order by 编号
  compute sum(总数),min(最小),max(最大),avg(平均) by 编号(by 必须有一个排序表)




   where 
1.between 90 and 100    
2.(分数 % 5=0) 5的整数
3.分数  in (89,100,97) 查询出这些分数的数据  加not 不显示这些分数的数据








 sql语句备份数据库
--假设你的数据库名为AAA,临时保存备份的地方是d:\data\backup
-- 第一步,先备份到文件:
backup database luis to disk='C:\luis\asdf\luis.bak' with init


-- 第2步,把备份还原成新的数据库,比要还原成新数据库BBB,库BBB的数据文件放在d:\data目录:
restore database BBB from disk='C:\luis\asdf\luis.bak'
with move 'luis' to 'C:\luis\asdf\luis.mdf',
move 'luis_log' to 'C:\luis\asdf\luis.ldf', 
replace














二、如果两个数据库在不同的服务器上


分为导出和导入两种，都需要先连接远程数据库，再进行操作。如何连接远程数据库?


在连接远程数据库之前，需要先打开Ad Hoc服务，依次执行下面四句语句（不要一次执行）：


EXEC sp_configure "show advanced options",1
RECONFIGURE
EXEC sp_configure "Ad Hoc Distributed Queries",1
RECONFIGURE


这样就可以连接远程数据库了：


SELECT * INTO DatabaseB.dbo.TableB FROM opendatasource( 'SQLOLEDB',  'Data Source=IP/ServerName;User ID=SA;Password=***' ).DatabaseA.dbo.TableA WHERE 1=2


记住，为了安全起见，执行完成之后，请关闭Ad Hoc服务：


EXEC sp_configure "Ad Hoc Distributed Queries",0
RECONFIGURE
EXEC sp_configure "show advanced options",0
RECONFIGURE




-------------------------------------------------
   基本连接


  1.内连接
select  列
from table1 [inner] join table2  on 连接条件{id=id}
where 
order by 
  
  外连接   比配主表数据,从表没有数据返回空
  2.左外连接 
  
  select  列
from table1  left [OUTER]  join table2  on 连接条件{id=id}
where 
order by 
  
  3.右连接
    select  列
from table1  right  [OUTER]  join table2  on 连接条件{id=id}
where 
order by
 
  4.完全连接 full
select  列
from table1 full    join table2  on 连接条件{id=id}
where 
order by
  




sql server2005 外围应用配置器
  配置相关的功能
  
  
  5.交叉连接  没有where 返回两个表数据的笛卡尔积
     返回:符合第一个表查询条件的数据 乘以 第二个表 符合查询条件的数据行数
     
     select 列
     from table1 cross join table2
     where 
     order by 
     
  自连接:通过表的别名 相连 表
  
  
  7.联合查询 - 结果集有相同的目标列.数据内容尽量相同


  查询
  union[all]
  查询
  
  
  8.子查询
  where (子查询)
  where in ()返回一个列表
  
  9.嵌套查询
  子查询中加子查询
  
  
  10.exists关键字查询 有结果返回true 反之 false
  
  exists(子查询)  判断查询是否有结果
  
  
  11.差查询   except     去除两个结果集相交的部分
     返回两个结果集的差（即从左查询中返回右查询没有找到的所有非重复值）。
  
  查询
  except
  查询
  
  
  12.intersect 交查询 返回两个结果集交集的数据
     -前一个查询不能使用 order by  可以放在子查询中      
  查询
  intersect
  查询
  
  13.xml查询
     declare 申明关键字 @data xml
     set @data=(select data from table where id=1)
     select @data.query('节点.name') 姓名,
            @data.query('节点.sex')
     
  13，
  for xml 子句   默认auto 模式
     将数据改成xml格式. 显示模式 raw auto explicit path 模式
     
     查询语句
     for xml raw 
     
  
  
  
  14.sqlcmd工具 查询
   sqlcmd -s 服务器名称 连上了为1
   
   
  15.transaction-sql 概述
   结构化查询语音 。
   分类:
     数据定义语音ddl 数据操纵dml 数据控制
    其他常用类型
    事务管理语言 流程控制 附加的语言元素<变量 常量等>
    
    
  
  
  
  常量与变量
  
  
  变量.
   全局
   
   
   局部
     declare @name 数据类型
     赋值
     set @变量名=值
     select @name=expression{...n} 表达式
     
   运算符
   算数
   + - / % 
   位运算
   & 与 两者都为1时为1
   | 或 有一个为1返回1.都为0时返回0
   ^ 异或 有一个值得时候返回一个值.有两个两个值的话返回第一个
   
   比较运算符
   <>：不等于  !< 不小于
   
   逻辑运算符
   
   all   一组比较都为true 返回true
   and   两个布尔都为true 返回true 有一个false 结果为false
   any   有一个为true 结果为true
   between 操作数在某个范围内.那么结果为true
   exists  子查询包含一些行.结果为true
   in       操作数等于表达式的列表的一个.那么结果为true
   like   操作数与某种模式匹配.结果为true
   not    对布尔运算符的结果取反
   or       两个布尔表达式 一个为true 结果为true
   some   一组比较中.有一些比较为true 结果为true
   
   其他运算符
    1.赋值运算符
    2.连接运算符
    3.一元运算符  + 正  -      ~ 返回数字的逻辑非
    
    
    
    
    表达式
      select @s='  '+a.name+' ' from table where id=1
      
      
    
    
    注释
    --
    /* */
    
    
    
    流程控制语句
    
      1.degin ...  end 语句
      形成一个组.俺组执行
      
      begin{
       sql
      }
       end --结束
  
      
      ---
       if @sex is not null 判断是否为空
       
       
      2. if {} else {} 语句  --Boolean 布尔
        
       
       
       
20.  case 语句 对多个条件判断
 case 条件
  when 情况1  then 执行块
  ....n
 else 语句  
 
 end
 
 21 while 语句  控制循环
  while boolean 
  { sql }
  break 跳出全部循环 continue 跳出本次循环
  
   1.表值函数
   
   
   
   
 23.waitfor 延迟语句
 
 waitfor
 
  delay time | 
  |TIME time
 
 24.goto 语句 跳转语句 重复的sql跳转
 
 25.try ... catch 错误处理语句