Sql Server数据库设计和高级查询

-------------------------------------第一章  数据库的设计-------------------------------------

软件开发周期：
     (1)需求分析
     (2)概要设计
     (3)详细设计
     (4)编码
     (5)集成测试
     (6)上线运行
     (7)后期维护

1,数据库设计步骤：
     (1)收集信息
     (2)标识对象(确定数据库中有哪些表)
     (3)标识对象的属性(确定每个表中的例)
     (4)标识对象的关系(两个表的关系)
    4种关系:
     1对1
     1对多
     多对1
     多对多


2，E-R 实体关系图
     矩形   表示，实体集（即表）
     椭圆   表示，属性（即列）
     菱形   表示，关系集（即关系）

3，数据库的三大范式：(在实际工作中不一定遵循)
     衡量数据库设计是否优秀的标准

     第一范式：确保每一列的数据不可再分
     第二范式：要求每个表只描述一件事
     第三范式：要求每一列与主键有直接关系，即确保表中每一列都直接依赖于主键






--------------------------------------第二章  数据库的实现------------------------------------------
 

1，数据库操作
     (1)使用语句创建数据库
          create database  数据库名
          on primary{     --设置数据库文件
           //逻辑名称
           name='逻辑名称',      --不用带后缀.mdf
          //设置初始大小
           size=3mb,             --sql server2014最小不可小于5mb
          //增量
          filegrowth=1mb,
          //路径包括文件，即物理名称
          filename='路径',
          //增长的最大值
          maxsize='100mb'
             }
             log on{     --设置事物日志文件
          //与上相同
          }

          例：
          create database bbsDB
          on primary
          (
          name='bbsDB',size=3mb,filegrowth=1mb,
          filename='E:\SQL Sever\SQL Server 高级编程\数据库\bbsDB.mdf'
          )
          log on
          (
          name='bbsDB_log',size=1mb,filegrowth=1mb,
          filename='E:\SQL Sever\SQL Server 高级编程\数据库\bbsDB_log.ldf'
          )






     (2)删除数据库
           drop database 数据库名


     (3)判断数据库是否存在
          1>sysdatabases表:保存在master数据库中，这张表保存了所有数据库的信息
            sysobjects表:数据库的系统表

          例:  判断数据库是否存在，如果存在输出a,否则输出b
          if(exists(select * from sysdatabases  where name='students'))
          begin
          select 'a'
          end
          else
          begin
          select 'b'
          end


2,表操作
     (1)创建表
          identity       标识列
          primary  key   主键
          not null       不为空

          create table 表名
          (
            id  int not null primary key  identity,
            name varchar(20)  not null,
           ..................................
          )

     (2)删除表
          drop table 表名






3,约束
     (1)主键约束   primary key
     (2)检查约束   check
     (3)默认值约束  default
     (4)主外键约束   foreign key

     1>添加约束：

          添加主键约束：
               alter table 表名
               add constraint 主键约束名(规范"pk_"开头)    
               primary key(列名)

          添加检查约束：
               alter table 表名
               add constraint 检查约束名(规范"ck_"开头)  check(表达式);

          添加默认值约束
               alter table 表名
               add constraint 默认值约束名(规范"df_"开头)
               default('默认值')   for 列名


          添加主外键约束：
               alter table 表名(外键表)
               add constraint 外键约束名(规范"fk_"开头)
               foreign key(外键名)
               references   主键表名 (主键列名)

     注：
       也可同时给一个表添加多个约束


     2>删除约束
          alter table 表名
          drip constraint 约束名





4，使用命令创建用户和删除用户的6步
     (1)创建：
          第一步:创建sqlserver登录用户
               exec addlogin '登录名','密码'
          第二部：创建对应数据库的用户
               exec grantdbaccess '登录名','用户名'
          第三步：添加用户对指定表的操作权限
               grant insert,upadat,select  on  表名  to  用户名
     (2)删除
          第一步：撤销权限
               revoke  权限  on  表名  from  用户名
          第二步:删除数据库用户
               exec sp_dropuser  '用户名'
          第三步：删除登录账号
               exec sp_droplogin  '登录名'


                


---------------------------------------------第三章 T-SQL编程-------------------------------------------------

1，局部变量(自定义变量)
     declare @变量名  数据类型     。。。。后面可定义多个

2，变量的赋值
     (1)set赋值
          set @变量=值
     (2)select赋值
          select @变量=列名 from 表名
     注：这种赋值方式只适合用户查到的数据只有一条时，如果查询到的结果为多条语句，则给变量赋的值是最后一条语句的值

3，输出变量
     (1)print  变量：以文本的方式输出
     (2)select 变量：以表元素的方式输出


4,全局变量
     常用：
          @@error           获取上一条语句的错误号
          @@identity       获取最后一次产生的标识值
          @@rowcount        受上一条语句所受影响的行数
          不常用：
          @@language       当前使用的语言名称
          @@max_connections   可以创建的同时连接的最大数目
          @@servername         本地服务器的名称
          @@servicname         本地计算机上的SQL服务的名称
          @@timeticks           当前计算机上每刻度的微秒数
          @@version            当前sql server的版本号



5,逻辑控制语句
     if(条件)
     begin

     end
     else
     begin
     end



     例：
     declare @username varchar(10)
     set @username='许超'
     if(@username='超')
     begin
     print '呵呵'
     end
     else
     begin
     print '呜呜。。。'
     end





6,while循环语句
     while
     begin

     end

     例：
     declare @i int
     set @i=0;
     while(@i<10)
     begin
     print @i
     set @i=@i+1
     end


7，case ...end 多分支结构
     case
      when  条件1   then   结果1
      when  条件2   then   结果2

     else
       其他情况
     end
 
     注：case...end  不能单独使用必须嵌套在sel语句中使用



8，批出理   
     关键字:go


---------------------------------------第四章 高级查询-------------------------------------------

1,子查询的特点：
     (1)当子查询返回的值只有一个时可以使用比较运算符,当子查询的值不只一个时要用in 或not in
     (2)子查询中查询的列只能是一列
     (3)子查询中的类型要和条件的类型一致
     (4)子查询可以应用在 insert,update,select,delete  语句中
     (5)如果子查询中使用了order by 就必须包含top关键字

2,简单子查询
     例:select * from stuinfo where scoreid=(select id from stuscore where score=60)

3,in和not in 子查询
     例：select * from stuinfo where scoreid in(select id from stuscore where score>60)
     例：select * from stuinfo where scoreid not in(select id from stuscore where score>60)

4，exists和not exists
     检查查询的数据是否存在，存在返回true,不存在返回false
     用法:
          exists(语句)
          not exists(语句)



                        

-------------------------------------------第五章 事物、索引和视图-------------------------------------


1,事物
     (1)事物作为一个整体，要么都执行，要么都不执行。
          1>开始事物
               begin tran   (或begin transaction)

          2>提交事物
               commit tran
          3>回滚事物
               rollback tran

     (2)事物的属性:
               原子性:是一个整体，不可再分，要么都执行，要么都不执行
               一致性:事物开始之前和事物结束之后，数据的状态保持一致
               隔离性:对同一个数据的操作，事物之间是彼此隔离的，要么A先执行，要么B先执行
               永久性:事物对数据的影响是永久的


2，索引
     (1)分类:
          1>聚集索引:(主键索引属于聚集索引)
               特点：
               *每个表只能有一个聚集索引
               *索引页的顺序和数据页的数据是一致的
               *创建主键会自动创建聚集索引
               *当查询范围值时特别有效

          2>非聚集索引
               每个表最多可健249个，因为没个表最多可以有249个列
               选择索引列的标准：
               *频繁作为查询条件的列
               *唯一值较多的列
               *不经常被更新的列
     (2)使用SQL语句创建索引
          非聚集索引：
               create index  索引名称  on  表名(列名1，列名2，.....)
          聚集索引:
               create clustered index  索引名称   on    表名(列名1，列名2，.....)



3，视图
     创建视图：
          create view 名称(一般以vi_开头)
          as
          内容

     使用视图:
          select * from  视图名



--------------------------------------------第6章 存储过程------------------------------------------------


(一)，存储过程
1,存储过程的优点：
     (1)执行速度更快
     (2)允许模块化程序设计
     (3)提高系统安全性
     (4)减少网络流通量

2,常见的系统存储过程

     sp_database            显示当前系统中的所有数据库信息
     sp_helpdb              显示所有或指定数据库的详细信息
     sp_renamedb            修改数据库名称             例：sp_renamedb  '旧名','新名'   
     sp_tables                显示当前数据库下所有表的信息         例：sp_tables 或 sp_tables '表名'
     sp_columns              显示某个表列的信息        例:sp_columes '表名'
     sp_help                查看某个表的所有信息        例：sp_help  或 sp_help '表名'
     sp_helpconstraint      查看某个表的约束         例：sp_helpconstraint '表名'
     sp_helpindex             查看某个表的索引            例:sp_helpindex '表名'
     sp_stored_procedures      显示当前数据库下的所有存储过程    例:sp_stored_procedures
     sp_password            修改当前登录用户的密码        例:sp_password '旧密码','新密码'
     sp_helptext            显示默认值、未加密的存储过程、用户定义的存储过程、触发器或试图的实际文本        例:sp_helptext


3,自定义存储过程(一般以proc_开头)
     语法:
          create procedure  名称
          参数1,参数2,......输出参数 output
          as
          内容


(二),触发器
     在执行增删改查时会自动触发
1，优点：
     (1)触发器会自动执行
     (2)可以对表进行级联修改
     (3)可以实现强制约束

2，两个重要的表
     inserted表:存在内存中的临时表，存放最新插入的数据，修改后的数据在inserted表中
     deleted表:临时保存了删除的记录，存放修改前的数据

3，触发器的使用
     插入触发器:insert
     修改触发器:update

     例:
     插入的触发器：
          create trigger  触发器名  on  表名
          for insert
          as
          declare @id int,@count int
          select @id=scoreid from inserted
          update stuscore set score=20 where id=@id

     修改的触发器：
          create trigger 触发器名 on 表名
          for update
          as
          declare @id int
          select @id=scoreid from deleted
          update stuscore set score=50 where id=@id



     删除存在的触发器:
          drop trigger 触发器名