Sql_Server基础知识总结

--------------------------------------第一章  Sql Server数据库基础-----------------------------------------------
1,sql server的历史:
     第一代数据库:网状与层次模型
     第二代数据库：关系模型 (sql server属于关系模型数据库)
     第三代数据库：对象模型
2，实体:客观存在的可以被描述的事物叫实体
3，冗余:存在重复的数据
     消除冗余的办法：分类存储
4，完整性:指的是数据的准确性
5，系统的内置数据库:
     (1)master:存储着系统信息。
     (2)model:所有数据库的模板
     (3)msdb:sql server代理服务存储系统任务以及自动化作业使用的数据库。
     (4)tempdb：存放临时数据的数据库，包括临时表，临时存储过程等。
6，数据库文件后缀
     mdf:主数据库文件，一个数据库只可以有一个主数据库文件，但可以有多个次数据库文件
     ldf:事物日志文件，用于存储对数据库的操作
     ndf:次要数据库文件
7，创建数据库用户并设置权限
     (1)权限:用户对服务器的操作权限
          sysadmin:可以在sql server中执行任何操作
          serveradmin:可以设置服务器范围的配置选项，关闭服务器。
          setupadmin:可以管理链接服务器和启动过程
          securityadmin:可以管理登录和create database 权限，还可以读取错误日志和更改密码
          processadmin:可以管理在sql server中运行的进程
          dbcreator:可以创建、更改和除去数据库
          diskadmin:可以管理磁盘文件
          bulkadmin:可以执行 bulk insert 语句
     (2)用户映射:指定用户对指定数据库的操作权限
     db_owner:拥有数据库的所有许可
     db_securityadmin:能建立架构、更改角色
     db_accessadmin:能添加删除用户、组或成员
     db_backupoperator:能备份数据库
     db_datareader:能从数据库中读取数据
     db_datawriter:能修改数据库表中的数据
     db_ddladmin:能添加、修改或删除数据库对象
     db_denydatareader:不能从数据库表中读数据
     db_denydatawriter:不能修改数据库表中的数据
     public 维护默认的权限
-------------------------------------第二章   数据库表的创建与管理-------------------------------------
1,数据表的完整性
     (1)实体完整性：保证表中每行数据都是唯一的，不能重复存在
     (2)域完整性：某一列数据的完整性
     (3)引用完整性:保证数据先在主表中存在，然后才能出现在子表。
     (4)自定义完整性:自己定义的完整性
2,sql server中常用的数据类型
     (1)二进制数据类型:
          binary    固定长度的二进制数据
          varbinary  可变长度的二进制数据
          image     可用来存储图像
     (2)文本数据类型
          char  固定长度的字符串，最大长度为8000个字符(一个字符占一个字节)
          varchar   可变长度的字符串
          nchar     固定长度的字符串(一个字符占两个字节)
          nvarchar  可变长度的字符串(一个字符占两个字节)
          text     长文本信息
          ntext     可变长度的文本信息
     (3)日期类型
          datetime   日期和时间
     (4)整数
          int ,smalint,tinyint,bigint
     (5)浮点数
          decimal,float,real
     (6),货币数据类型
          money
     (7),bit数据类型
          表示是/否  1代表true 0代表false
3，约束
     主键约束，默认值约束，标识列，检查约束，外键约束
--------------------------------------第三章  T-SQL编程--------------------------------------------
1，T-SQL的组成:
     (1)DML(数据操作语言):增删改查数据操作
     (2)DDL(数据定义语言):用来建立数据库、数据库对象和定义
     (3)DCL(数据控制语言):用来控制存取许可、存取权限等
2，T-SQL中的条件表达式和逻辑运算符
     (1),比较运算符
          =   等于
          >   大于
          <   小于
          >=  大于等于
          <=  小于等于
          <>  不等于
          !   取反
     (2)通配符
          _   任意一个字符
          %   任意长度的字符
          []  括号内指定范围的一个字符(只包括数字和字母)
          [^] 不在括号内指定的范围内的一个字符
     (3)逻辑表达式
          and   并且
          or    或者
          not   非
     (4)模糊查询(like)
          select * form 表名
          where 列名 like '%许超%'
     (5)len()函数
          获取长度
3，数据基本操作
     增:
          单行:insert into 表名(列名) values(值)
          多行:insert into 表名(列名)
          select 值  union
          select 值  union
          select 值  
     删：
          delete from 表名  where  条件
     改:
          update 表名  set  列名=新值 where 条件
     查：
          select * from 表名  where 条件
4，截断表
     trun cate table 表名
     速度快，不可恢复
5，数据备份
     (1),
          select 列名  into  新表名   from 源表名   (只会备份表结构不会备份表约束)
     (2),把现有数据复制到另一个现有的表，必须先建好一章新的表
          insert into 新表名 (新列名)  select 源列名   from  源表名
----------------------------------第四章，T-SQL查询基础--------------------------------------
1，和并列、起别名、查空行、常量列
     合并列和起别名:
          select 列名+列名 as 别名和别名 from 表名
     查空行和查无空行:
          select * from 表名  where 列名 is null or 列名=''
          select * form 表名  where is not null and 列名<>''
     常量列:
          select * form 列名1 as 别名,列名2 as 别名,新值 as 新列名   from 表名
2，限制返回行数(top)
     select top 3 * from 表名
3，排序(order by)
     asc 升序（默认）
     desc 倒叙
     select * from 表名  where 条件   
     order by  列名,可加多列  顺序(ase或desc)
4，消除重复列(distinct)
     select distinct  列名  from  表名
5，函数
     (1)字符串函数
          CharIndex()  用来寻找指定的字符串在另一个字符串中的起始位置。      例：select charIndex('china','inchina',1)，返回3
          len()        返回指定字符串的长度                                例：select len('ab')，返回2
          Upper()      将指定的字符串全部大写                               例:select upper('ab'),输出AB
          lower()      将指定的字符串全部大写                               例:select lower('AB'),输出ab
          Ltrim()      清除字符串左边空格                                   例:select Ltrim(' ab'),输出ab
          Rtrim()      清除字符串右边空格                                   例:select Rtrim('ab '),输出ab
          right()      从字符串右边返回指定数目的字符串                      例：select right('abacd',2)，返回cd
          left()       从字符串左边返回指定数目的字符串                      例：select left('abacd',2)，返回ab
          replace()    替换字符串中的字符                                    例:select replace('china','a','ese'),输出chinese
          stuff()      在一个字符串中删除指定长度的字符，并在该位置插入一个新的字符   例:select stuff('china',2,3,'-'),返回c-a
          substring()  提取字符串                                                例:select substring('abcd',2,1),返回b
     (2)日期函数
          getdate()    获取当前系统日期   例:select dateadd(dd,10,getdate())  输出:2015-01-21 20:22:21.783
          dateadd()    在日期的指定部分加上或减去指定的值       例:select dateadd(dd,10,'2015-01-21')   输出:2015-01-31
          datediff()   获取两个日期之间指定部分之差             例:select datediff(yy,'2014-8-15','2015-01-21')  输出:1
          datename()   获取日期中指定的部分，返回字符串类型     例:select datename(mm,'2015-01-21')    输出:01
          datepart()   获取日期中指定的部分，返回int类型        例：select datepart(yy,'2015-01-21')    输出:2015
          year()       获取日期中年的部分                       例：select year('2015-01-21')            输出:2015
          month()      获取日期中月的部分                       例：select month'2015-01-21')        输出:01
          day()        获取日期中日的部分                       例：select day('2015-01-21')          输出:21
注：
     日期的指定部分:
          yy 年
          mm 月
          dd 日
          hh 小时
          mi 分钟
          ss 秒
          ms 毫秒
          dw 星期
          sql server中日期的格式必须为 2015-01-21 或 2015/01/21
     (3)数学函数
          abs() 获取数值表达式的绝对值                  例：select abs(-2)  输出:2
          ceiling()  返回大于或等于所给数字的最小整数    例：select ceiling(2) 输出:2    select ceiling(2.1) 输出:3
          floor()    返回小于或等于指定表达式的最大整数   例：select floor(3)  输出3     select floor(3.1)   输出:3
          power()    取数值表达式的幂值                   例:select power(5,2)  输出:25   注:power(底数，幂数)
          round()    将数值表达式四舍五入为指定精度       例:select  round(56.765,1) 输出:56.8
          sign()     正数返回1，负数返回-1,0返回0        例：select sign(-55)  输出:-1
          sqrt()     去浮点表达式的平方根                例：select sqrt(9)    输出:3
     (4)系统函数
          convert()  用来转变数据类型    例:select '他的年龄是'+convert(varchar(20),userAge) from users   注:convert(目标类型,'需要转换的值')
          current_user   返回登录的用户名和类型
          host_name      返回当前登录用户的计算机名
          user_name     返回当前登录的用户名类型
          system_user    返回当前登录sql server的用户名
          datalength  返回指定的字节数     例:select datelength('中国A盟')
      
-------------------------------------第五章 T-SQL 高级查询------------------------------------------------------
1,模糊查询(like)
     select *
     from 表名  where name like  '许%'            //查询所有以许开头的数据
2,查询指定范围内的数据(between and)
     select * from 表名  where id  between 5  and   10     //查询从id从5到10的所有数据，包括5和10
3，使用in在指定集合内查询
     select * from 表名 where name in('许超','李四')      //查询name为许超，或者李四的所有信息
4，聚合函数
     (1)sum()     求和，只能用于数值类型的列
     (2)avg()     求平均数，只能用于数值类型的列
     (3)max()     求最大值，能用于数值类型和字符类型的列
     (4)min()     求最小值，能用于数值类型和字符类型的列
     (5)count()   统计行数，能用于数值类型和字符类型的列
5，分组查询(group by)
     规则:
          (1)如果select后除了聚合函数还有其他列，就必须使用group by
          (2)group by后放除了聚合函数的其他列，都要添加到group by 后面
          (2)where 条件必须写在group by之前，在group by 之后添加条件用 having
          (3)order by 必须在group by后面使用
          select stateid as '状态',sum(userpoint) as '积分' from users
          where 条件
          group by stateid
6，使用having分组筛选
     //使用having分组筛选,总积分大于200的用户和人数,并倒叙排序
     select username as '用户',count(*) as '人数' from users
     group by username
     having sum(userpoint)>200
     order by count(*) desc
7,多表连接
     (1)内连接
          1>第一种:在where子句中指定连接条件，实现多表连接
          select a.Statename,b.Rolename,c.username  from state as a,Roleinfo as b,users as c
          where a.stateid=c.stateid and b.reoleid=c.roleid
          2>第二种:在from子句使用 join...no,实现两表连接
          select a.StateName,b.username from
          state as a inner join users as b on a.stateid=b.stateid
     (2)外连接
          1>左外连接  left join
          左表中的数据全部显示
          右表中有关系的连成一行
          没有关系的添null
     例：
     select a.StateName,b.username from
     state as a left join users as b on a.stateid=b.stateid
     2>右外连接  right join
          右表中的数据全部显示
          左表中有对应的拼成一行
          没有对应的补null
     例：
     select a.StateName,b.username from
     state as a right join users as b on a.stateid=b.stateid
     3>完全外连接  full join
          左表中的数据全部显示
          右表中的数据全部显示
          有对应的拼成一行
          没有对应的补null
     例：
     select a.StateName,b.username from
     state as a full join users as b on a.stateid=b.stateid
     (3)交叉连接  cross join
          左表中的数据和右表中的数据每次都拼接
     例：
     select a.StateName,b.username from
     state as a cross join users as b