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 数据库用户