SQL Server 经验总结（二）

恒晖瑶

数据类型
----系统数据类型
系统数据类型是SQL SERVER支持的内置数据类型。

数据类型	描述	所需空间
binary	固定长度的二进制数据，最大长度为8，000字节	0到8000字节，具体取决于定义
Bit	整型数据，值为1或0（真或假）	1字节
Char	固定长度的非Unicode的字符数据，最大长度为0到8000字节，8，000个字符	0到8000字节，8，000个字符。具体取决于定义
Datetime	日期和时间数据，从1753年1月1日到9999年12月30日，精确到三百分之一秒，即3.33秒	8个字节。
Decimal	带定点精度和小数位数的十进制数字类型	2-17字节
float	浮点精度数据类型，从-1.79E+308到1.73+308.	8字节
image	可变长度的二进制数据，最大长度为2^31-1字节	16字节的指针，每个 初始化列至少外加2000字节
Int	整数数据，从-2^31-1到2^31-1	4个字节
Money	货币数据类型，从-2^63到2^63-1，精确到每个货币单位的万分之一	8个字节
Numeric	decimal的同义词	2-17字节
Real	浮点精度数字数据，从-3.40E+38到3.40E+38	4个字节
Smalldatetime	Smalldatetime  日期和时间数据，从1900年1月1日到2079年6月6日，精确到1分钟	4个字节
Smallint	整数数据，从2^15到达^15-1	2个字节
Text	（文本）可变长度的非Unicode数据，最大长度为2^31-1个字符	16字节的指针，每个 初始化列至少外加2000字节
Timestamp	整个数据库范围内的唯一数字	8个字节
Tinyint	整数数据，从0 到255	1个字节
Uniqueidentifier	存储作为全局唯一标识符（GUID）的16字节的二进制值。GUID是确保唯一性的二进制数字	16字节
Nvarchar	可变长度的Unicode数据，最大长度为4000个字符	存储大小是输入数据

 
----用户数据类型
添加用户数据类型:
sp_addType 自定义数据类型名，基本数据类型,’not null’
    删除用户数据类型：
        sp_dropType自定义数据类型名
   
规则：被绑定在一列或一个用户定义数据类型上的一个数据库对象，它指定了该列可以输入的数据
       创建规则：create rule 规则名 as @rID like ‘指定输入样式’
       删除规则：drop rule 规则名
 
综合实例：现在我要创建一个类型，这种类型用来输入特殊的ID，比如 ‘TT-AC01’,只能以TT-开头，后接两个字母和两个个数字,不输入的话，则默认添入’TT-AA11’
       --创建规则,规则名为r_id,@rr为变量，代表以后输入的值
              create rule r_id as @rr like ‘TT-[A-Z][A-Z][0-9][0-9]’
       --创建默认,默认名为 dData
              create default dData as ‘TT-AA11’
       --添加用户自定义类型,类型名为inID
              sp_addType inID,varchar(7),not null
       --将规则和默认绑定到数据类型上
              sp_bindrule r_id,inID
              sp_bindefault dData,inID
       现在就完成了，我们可以直接用inID这种数据类型，只能输入’TT-VC24’这种格式，默认输入为’TT-AA11’
 
-----------------------------------------------------------------------------
 
数据插入、更新、删除
        ----插入数据
(1)  插入所有字段
insert into 表名 values(值1，值2…)
(2)  插入个别字段(插入的值要和列的类型相对应)
insert into 表名(列1，列2…) values(值1，值2…)
(3)  表对表的插入
insert 表名(列1，列2…)
select 列1，列2…
from 源表名
 
        ----更新数据
    update 表名 set 列1=值，列2=值..
    where 条件
 
        ----删除数据
    delete from 表名
    where 条件
    (truncate table 表名，删除整个表的数据，速度更快)
 
-----------------------------------------------------------------------------
数据查询
    (返回的结果是一个临时表)
    完整的查询语句:
        select 列|表达式
        from 表名
        where 条件(针对select和from返回的结果进行筛选)
        group by 列(针对where子句的输出结果分组)
        having 条件(针对分组汇总后的结果进行筛选)
        order by 列,…(进行排序)
   
    聚合函数：sum(求和)
           avg(求平均值)
           count(记数)
           max(求最大值)
           min(求最小值)
    (聚合函数忽略null值。如果加入distinct的话，只进行一次重复值的运算)
    ---------------------------------
    distinct(独特的)只返回不重复的值

         Date between  '2009-01-26'  and  '2009-02-06'  范围的查询
 
    如：select distinct name
        from student
        查询结果只返回不重复的值
    ---------------------------------
    order by 用来排序，Asc代表升序，Desc代表降序
    如：有个成绩表，我先按学生的ID升序排序，再按Score(分数)降序排序
        select *
        from tbExam
        order by u_ID asc,Score desc
    ---------------------------------
    使用常量:
    如：有学生表字段name,sex,age,我要连接在一起显示出来
    select ‘姓名：’+name+’ 性别：’+sex+’ 年龄：’+cast(age as varchar)
    from tbStudent
    注：”+”号左右两边的数据类型必须相等
cast是把age数字型转换成了varchar文本型
注：类型转换 1)convert(类型,内容,格式)  2)cast(内容 as 类型)
---------------------------------
    给列命别名：
(1)  select 别名=列名 from 表
(2)  select 列名 as 别名 from 表
    查询，列是没有名字的，我们可以给列命别名
    select ‘姓名：’+name+’ 性别：’+sex+’ 年龄：’+cast(age as varchar)
               as ‘学生信息’
    from tbStudent
    ---------------------------------
    用Top关键字对返回的数据的数量进行控制
    如：学生表有10条数据，我要返回5条，可以采用下面两种方法
(1)  select top 5 *
from tbStudent
(2)  select top 50 percent
from tbStudent
注：percent是百分比的意思，代表我只返回百分之50的数据
    ---------------------------------
    联接：
                    联接
                     |
        ----------------------------------------------------
        |                     |                            |
         内联接             外联接                      内联接
                     |
                 -------------------------------
                 |            |                |
              左外部联接    右外部联接     完全外部联接
 
1． 内联接，只返回左右两表相匹配的记录
select *
from A inner join B
on 条件
   
2.  外联接
左外部联接，返回左表中所有的记录，但仅返回右表中相匹配的记录
select *
from A left join B
on 条件
 
右外部联接，返回右表中所有的记录，但仅返回左表中相匹配的记录
select *
from A right join B
on 条件
 
完全外部联接，返回左右表中所有的记录
select *
from A full join B
on 条件
 
3.  内联接,表自身与自己进行查询
 
-----------------------------------------------------------------------------
数据库安全模型
　　
         数据库3层安全模型

第一层：登录到SQLServer系统，即需要登录帐户

SQL Server服务器

数据库A

数据库B

数据库C

表1

表2

表1

表2

表1

表2

第二层：访问某个数据库，需要成为该数据库的用户

第三层：访问数据库中的表，需要数据库管理员DBA给自己授权，如添、修、删、查等权限

        
登录验证两种方式：
--SQL身份验证：适合非Windows平台的用户或Internet用户，需要帐户和密码
--Windows身份验证：适合于Windows平台用户，不需要提供密码，和Windows集成验证
 
1.  创建登录
         Windows登录：
            授权：sp_grantlogin ‘windows域名\域帐户’
            撤销：sp_revokelogin ‘windows域名\域帐户’
         SQL登录帐户：
            添加：sp_addlogin ‘登录名’,’密码’
            删除：sp_droplogin ‘登录名’
2.  创建数据库用户
         sp_grantdbaccess ‘登录帐户’,’数据库用户’
3.  向数据库用户授权
         授权：grant 权限 [on 表] to 数据库用户
         撤销：revoke 权限 [on 表] to 数据库用户