SQL复习



SQL分类：
 DDL—数据定义语言(Create，Alter，Drop，DECLARE) Definition language
 DML—数据操纵语言(Select，Delete，Update，Insert) Manipulation language
 DCL—数据控制语言(GRANT，REVOKE，COMMIT，ROLLBACK) Control language
1、实体完整性(Entity Integrity)
实体完整性是指主关系键的值不能为空或部分为空。
2、参照完整性(Referential integrity)
如果关系R2的外部关系键X与关系R1的主关系键相符,则X的每个值或者等于R1中主关系键的某一个值，或者取空值。
3、域完整性
域完整性是针对某一具体关系数据库的约束条件。它反映某一具体应用所涉及的数据必须满足的语义要求。

第一范式（1NF）：元组中每一个分量都必须是不可分割的数据项 or 确保每列的原子性<每列都是不可再分的最小单元,有主键列不可分>
第二范式（2NF）：在第一范式基础上，所有非主属性完全依赖于其主码 每个表只描述一件事件 联合主键时 其他非主键的列不能部分依赖 主键
第三范式（3NF）：在第二范式基础上，而且它的任可一个非主属性都不传递于任何主关键字
1、E-R图
 矩形-->实体集、椭圆-->属性、菱形-->关系集、直线-->连属性和实体集也可连实体集和关系集
2、库设计
 设计数据库步骤-->概要设计阶段-->详细设计阶段
 进行数据的系统分析时,基本步骤为:
  收集信息-->标识对象-->标识每个对象需要存储的详细信息
 规范化和性能的关系:
  考虑三大范式,避免数据冗余和各种数据的操作异常,又要考虑数据访问的性能,有时为减少表连接,提高
  数据库的访问性能,允许适当的数据冗余
系统数据库 包括
Master数据库是SQL Server系统最重要的数据库，它记录了SQL Server系统的所有系统信息。这些系统信息包括所有的登录信息、系统设置信息、SQL Server的初始化信息和其他系统数据库及用户数据库的相关信息

model 数据库用作在 SQL Server 实例上创建的所有数据库的模板

Msdb数据库是代理服务数据库，为其报警、任务调度和记录操作员的操作提供存储空间

Tempdb是一个临时数据库，它为所有的临时表、临时存储过程及其他临时操作提供存储空间

逻辑运算符
 先not 后 and 再  or
Q1:创建/删除 数据库  语法
- 使用 master数据库
use master;
--判断数据库是否存在
if exists(select * from sysdatabases where name='DB_name')
drop datebase DB_name
create database DB_name
on(--主数据库文件
 name=networksManger,-- 逻辑名称
 filename='E:\T30\project\DB_name.mdf',-- 保存路径\物理名称
 size = num    --文件才初始大小
 filegrowth= 15% -- 文件增长方式及增长大小 
)
log on ( -- 日志文件
 name = networksManger_log ,-- 逻辑名称
 filename='E:\T30\project\itemActualCombat_log.ldf'-- 保存路径 \物理名称
 size = num    --文件才初始大小
);
--使用当前数据库
use DB_name;
--创建表
if exists (select * from sysobjects where name='table_name')
drop table table_name
create table table_name
(
 列名 类型+长度 是否为空 [check(约束表达式)] [default(默认值)] [int identity(标识种子,标识增量)]
 stuNo char(6) not null check(stuNo like 'S253%') primary key,
 stuSex varchar(2) not null default('男') check(stuSex in ('男','女')),--学员性别  默认男
 stuAge int  not null check(stuAge>=15 and stuAge <= 50),--学员年龄 15～50
 stuSeat int identity(1,1) not null check(stuSeat >=1 and stuSeat <=30),--学员座位号  1-30
);
-- 创建标识列
数据类型(int) identity(标识种子，标识增量)  --一表只可有一标识列
主外键用来强制引用完整性
 --创建主键  只能有一个(设置主键考虑 最少性 和稳定性 复合主键-->多列做主键)理想情况下不更新
 primary key
 --创建外键
 references 表名 -- 是那个表中列的外键
 
--通配符
 % 任意长度字符 _ 任意单个字符 (建check约束时like stuNo char(6) not null check(stuNo like 'S253%') 中%可用__替代)
--说明：增加一个列
 Alter table tabname add column col type
 --注：列增加后将不能删除。DB2中列加上后数据类型也不能改变，唯一能改变的是增加varchar类型的长度
 BACKUP DATABASE pubs TO testBack
--插入数据
(1)、一次插入单行
insert into 表名 [列名()] values ();//当列的值全部要插入时,可省略[]
(2)、一次插入多行
insert into 目标表名(目标列名) --列名可不同
select 原表列名 from  原表   --目标表，列存在 可用
(2)、一次插入多行
select  原表 列名 into 新表名 from 原表名 --  目标表，列不存在
  --插入标识列
 identity (int, 标识种子，标识增量)
--更新数据
update 表名 set 列名 = 值 [where 条件表达式]
 
Q2:三种删除表的 语法与区别
 drop table table_name --删除表结构同时清空表数据
 delete talbe table_name--不删除表结构只清空表数据 删标识列时,标识列继续按原来的增加
 truncate table_name --不删除表结构只清空表数据 删除标识列时 ,从标识种子开始)
Q3:为列添加/删除约束(单独添加,各种约束都需写出 包括单词)
 约束建立在sysobjects 表中
 --删除约束
 alter table table_name drop constraint 约束名
 --检查唯一约束是否存在
 if exists(select * from sysobjects where name='UQ_字段')
 --删除约束
 alter table table_name drop constraint UQ_字段
 --创建唯一 约束
 alter table table_name add constraint UQ_字段 unique(字段)
 -- 添加主键
 alter table  table_name add constraint PK_字段 primary key(字段)
 --添加外键 约束
 alter table table_name add constraint FK_字段 foreign key(字段) references P_table_name(字段)
 -- 添加 check 约束
 alter table table_name add constraint CK_字段 check()
 --添加默认 值
 alter table table_name add constraint DF_字段 default(getdate()) for 字段
Q4:创建数据库登陆用户 和创建数据库用户   说出SQL三层验证结构
  --windows登录帐户了解
  /*exec sp_grantlogin 'windows域名\域帐户';*/
  exec sp_grantlogin 'PC2011071813wtr\s2001'; 
  --使用登陆账户要管理的数据库
  use studentInfo;
  -- 法1、创建SQL登陆用户    exec  实行
  exec sp_addlogin 'dragon','dragon'
  -- 法2、创建SQL登陆用户
  -- create login 用户名 with password=''  --密码='';
  --删除SQL登陆用户
  drop login dragon
  --使用当前访问的数据库
  use studentInfo;
  --创建能访问数据库用户
  exec sp_grantdbaccess '登录帐户','数据库用户'--在创建之前需使用当前访问的数据库
  --eg:exec sp_grantdbaccess 'dragon','accesdb';
  --删除数据库用户
  exec sp_dropuser 'accesdb';  
  
Q5:分配相应的权限  (是否可为登陆用户分配权限 , 数据库用户权限)
  给数据库用户授权作用:
   已经创建了访问数据库的用户，但还不能访问数据库中表的权限，
   此时需要DBA数据库管理员进行授权，常见的是DML操作(insert\delete\update\select)
  --授权语法:
   grant 权限 [ON 表名] to 数据库用户
   use studentInfo;
   grant select on studentInfo to accesdb;
  --撤销数据库用户所分配的权限
   revoke delete on studentInfo to accesdb;
   
Q6:全局/局部语句变量 说出 两种变量的区别  声明变量  变量的生命周期 常见全局变量
 全局                         局部
 系统提供并赋值,用户不可定义和通过set语句赋值     declare @v_name 声明,
 @@v_name多数都是报告本次SQL启动后           set语句赋值或select @v_name=from table_name
 发生的系统活动 @@ERROR  
Q7:T-SQL中的流程控制语句 说出其作用
 简单case表达式
  select 字段,字段=case
   when 条件1 then 结果1
   when 条件2 then 结果2
   else 其他结果
  end
  from table_name
 GO
 搜索型case表达式
 case关系函数
Q8:什么是批处理命令 批处理的优点 使用批处理应该注意什么?
 批处理看做执行单元、要么全部执行、要么全部不被执行
 GO指令并不是T-SQL语句,只有查询编辑器才能识别并进行处理,如编写其他应用程序就不能使用GO指令
 批与批之间是独立的,一个批发生错误不会影响其他批的执行
Q9:T-SQL代码的 解析、编译、执行 是怎样一个过程 on book Page 80
 每个批处理语句单元都经过以下三步
  解析:检查批处理语句过程
  编译:将书序树生成一个执行规划
  执行:根据执行规划在高速缓存中运行并停止
Q10:基本查询
  select * from 表名 where 条件 表达式
   --模糊查询
   like  ，not like
   between 小值 and 大值 , not between 小值 and 大值 
   in ,not in
  --having 条件 只可用于分组后 条件筛选
  select 列名 from 表名 where 表达式
  group by 列名
  having 条件
  order by -- 其位置灵活
  --列重命名
  a、列可用[as]重命名 as 可省 但必须有空格
  b、列+ 列 as 别名
  c、'='  --别名 = 原名
  --排序
  order by 列名--可为 as别名
   (如有多个列排序，则先满足第一个列如后面的列还可排则继续排)
  --分组
  group by 列名(规则同排序) 没有被分组或被聚合的列不可显示在查询结果中
  --Exists 函数
  返回一个集的一组成员，该组成员与一个或多个其他集的一个或多个元组共存
  
Q11:计算统计查询使用 注意事项   ps(这里有很多知识 注意 + by 时 与分组查询有什么区别)
  单独使用  compute 统计处理的为合计值 聚合函数不可取别名
  compute by 字段1 对数据进行分组统计 子句必须与order by 字段2 子句联合使用 字段1必须和字段2相同
Q12:子查询分为嵌套子查询和相关子查询 它们的区别及各自的概念
  子查询一般操作单表
  连接查询一般操作多表数据
  将子查询和比较运算符结合使用时,必须保证子查询返回的值只能是唯一的一个不允许返回多个值 
  一般来说表连接都可用子查询替换
  
Q13:事务 概念 语法 属性(简称) 创建、创建注意事项 事务执行  语法及注意事项
 概念：用户定义的一个数据库操作序列  一个不可分割的工作单位
 定义事务：语法
   begin transaction  -- 表示事务开始
   commit  transaction-- 提交事务
   rollback transaction-- 回滚事务
   必具属性：
    原子性  (atomicity)  事务的所有操作都要做
    一致性  (consistency)   数据不会因事务的执行而遭受破坏
    隔离性  (isolation)   保证事务不受其他并发执行的事务的影响
    持久性  (durability)  事务成功完成对数据库的改变时永久的
  事务属性 acid 可能遭破坏的因素
   多个事务并发执行，不同事务的操作交叉执行
    --DBMS须确保多个事务的交叉运行不影响这些事务的原子性
   事务在运行过程中被强行停止
    --DBMS须确保强行终止的事务对数据库和其他事务没有任何影响
 创建事务的技巧：
  事务要尽可能简短
  事务中访问的数据量要尽可能的短
  浏览数据时尽可能不要打开事务
  事务处理期间尽可能不要请求用户输入
 事务执行注意事项：
  每个操作之后都要检查  @@error 和@@rowcount的值 以确定操作是否成功-- 返回受上一条语句影响的行数
  事务结束后紧跟在事务之后的语句还可继续执行 但出错后不能回滚事务了
  已提交完毕的事务会将数据正式写入 不能再回滚事务了
  当以事务正在执行 发生意外事件 下次重启 事务回滚
  事务发生错误使得事务无法 执行下去时 事务自动回滚
  无法回滚的语句不能在事务中使用
  
 事务三种执行模式
  显示事务  begin  commit  rollback
  自动提交事务  T-SQL语句都可看做是一自动提交事务
  隐式事务 执行 set implicit_transactions on 进入隐式 事务
  --系统将在提交或回滚当前事务后自动启动新的事务 不需再次定义事务的开始 只需提交或回滚每个事务
    结束隐式事务   执行  set implicit_transactions off
    
Q14:索引 概念 优点(即为什么要使用索引) 语法(创建 删除)
  索引应该由数据库自动调用
   注：索引是不可更改的，想更改必须删除重新建。
   为加速对表中数据行的检索而创建的一种关键字与其他相应地址对应表  针对表建立 只能由表创建者创建
  一般考虑建立所引导列有表的主关键字列 外部关键字列和子啊某一范围内频繁搜索的列或按排序频繁检索的列
  优点:
   加快访问速度
   加强行的唯一性
  缺点:
   带索引的表在数据库中需要更多的存储空间
   操纵数据的命令需要更长的处理时间，因为它们需要对索引进行更新
   
  创建索引指导原则:
     请按照下列标准选择建立索引的列
    该列用于频繁搜索
    该列用于对数据进行排序
    请不要使用下面的列创建索引：
    列中仅包含几个不同的值
    表中仅包含几行。为小型表创建索引可能不太划算，因为SQL Server在索引中搜索数据所花的时间比在表中逐行搜索所花的时间更长
 
  聚集索引、
    按索引的属性列排序和存储  并照排好顺序 存储表中--不适用于需经常更新的列 物理顺序与键值的逻辑顺序相同
  非聚集索引、
    按索引的属性列排序和存储  不存储 于表中 --物理顺序与键值的逻辑顺序不一定相同
  唯一索引、不允许两行具有相同的索引值(主键索引是唯一索引的一种特殊类型)
  复合索引、视图索引、全文索引
  创建非聚集索引语法
  if exists(select * from sysindexes where name='索引名')
  drop index 表名.索引名
  create nonclustered index 索引名 on 表名(列名) with fillfactor =30--fillfactor为填充因子
  --指定按索引名查询
  select * from table_name with (index=索引名) [where]
Q15:视图 概念 优点(即为什么要使用视图) 语法(创建 删除)  创建注意事项
  --说明：创建视图：create view viewname as select statement
   删除视图：drop view viewname
   关系数据库中一‘可见得表’  虚拟表
 建立视图优点 ：
  能对机密数据提供安全保护
  能够简化用户的查询操作
  能保证数据一定程度的逻辑独立性
  使用户以多种角度看待同一数据 获取数据更容易 结果更容易理解
  限制数据检索更容易 维护应用程序更方便
 语法
  create view  name
   as select_statement--可为任意复杂的 select 语句 
        注意事项
          不能包括  compute compute by
          不能包括  order by 子句 如需排序可建立视图后对视图查询时再排序
          不能包括 distinct 短句
          不能包括option 子 句
          不能包括  into 关键字
          不能引用临时表或表变量
Q16:存储过程  分类  使用存储过程的优点  <是否带参数  区别>
 存储过程优点
  执行速度更快
  允许模块化程序设计  --模块化 
  提高系统安全性
  减少网络流通量
系统存储过程
 由系统定义，存放在master数据库中
 类似Java语言类库中的方法
 系统存储过程的名称都以“sp_”开头或“xp_”开头
用户自定义存储过程
 由用户在自己的数据库中创建的存储过程
 类似Java语言中用户自定义的方法
 不能在存储过程中使用 create view 命令

-- 无参数
if exists(select * from sysobjects where name="proc_name")
drop proc proc_name
GO
create procedure proc_name
as
 --计算平均分
GO
 输入参数  --Java 形参   输出参数  --Java返回值
   创建存储过程 带输入参数时 输入参数声明不要用declare 关键字
 输出参数
  声明一变量保存存储过程的 output 值
 declare @out int
exec proe_showinfo 90,90,@out output-- 不用output 关键字则默认变量@out 为输入参数
Exec Sp_Databases 参数 --如果是输出参数必须加output
创建 视图,存储过程create 语句必须位于执行语句的第一行
 
Q17:自定义错误  使用 规则
  存储过程中的错误  
  --自定义错误--相当于Java中的自定义异常  人性化的错误提示
  --语法错误 系统变量@@error
  raiserror 指定严重级别 设置系统变量@@error  记录所发生的错误
  raiserror('错误信息',错误级别,错误类型)  -- 必须要有三个参数以上
 
Q18:触发器  优点  概念(产生表) 语法  分类
  --什么是触发器
  触发器是一种实施复杂的完整性约束的  特殊存储   过程，它是基于
  一个表创建的，并和一个或多个数据修改操作相关联的。触发器不需要
  直接调用，当满足一定条件时则会自动触发.
   触发器是在对表进行插入、更新或删除操作时自动执行的存储过程
   触发器通常用于强制业务规则
   触发器是一种高级约束，可以定义比用CHECK 约束更为复杂的约束
   可执行复杂的SQL语句（if/while/case）
   可引用其它表中的列
   触发器定义在特定的表上，与表相关
   自动触发执行
   不能直接调用
   是一个事务（可回滚）
 
  触发器建立在表一级,它与指定的数据修改操作相对应，每个表可以建立多个触发器.
 
 sqlserver为每个触发器都创建了两个专用表:inserted与deleted表，
 这是两个逻辑表，由系统来维护的，不允许用户直接对此两表进行修改,
 用户只能   读取，它存放在数据库服务的内存中，不存放在数据库中，
 这两个表的结构总是与触发器所在的表的结构是一模一样的。
 
 --(1)inserted表:存放由于insert或update语句的执行而导致要加到
 --触发表中去的所有新行(只是insert或update所影响的新行数据，并非触发表所有数据)
 
 --(2)deleted表:存入由于delete或update语句执行前的行，也就是旧行.
 
 --对于insert操作,只在inserted表中保存所插入的新行，而deleted表表中无一行数据
 --对于delete操作,只在deleted表中保存被删除的旧行,而inserted表无一行数据
 --对于update操作,可以将它考虑为delete操作与insert操作的结果,所以
 --在inserted表中存放着更新后的新行值,而deleted表则存放更新前的旧行.
 -- 如何创建触发器   语法:
 if exists(select * from sysobjects where name='trig_name')
 drop trigger trig_name
 GO
 create trigger trig_name
 on table_name
 instead of insert  --default  after 触发器 要改变触发器类型 (after instead of) 则不用for关键字
 as
  --把inserted 表中的临时数据取出来 用于自动触发更新账户信息
 GO
 
 --tigger_name:触发器名, table_name:触发器所在的表, for | after | instead of
    如指定for,默认为after:此类触发器是在记录发生改变之后才会触发执行.
    如指定为instead of:是在记录变更之前发生,但它并不执行原来的SQL语句.
 
 deleted表 备份删除之前的数据
 
Q19:常用函数
 (1)、字符串函数 
 --寻找一指定字符串在另一字符串的起始位置
 select charindex ('指定字符串','字符串',起始位置1)--如有多个指定字符串则找到第一个就不继续了
 --返回字符串长度
 select len ('字符串')  -> (汉字做1 个字节计算)
 --把传递的字符串转换成大/小写
 select upper/lower('字符串')
 --清楚字符左/右边空格
 select ltrim/rtrim ('字符串')
 --从字符串左/右返回指定数目的字符
 select left/right('字符串',指定数目)
 -- 替换一字符串中得字符
 select replace('字符串','待替换字符','替换成的字符')
 --在一字符串中删除指定长度的字符,并在起始位置插入新字符
 select stuff('字符串',起始位置num,指定长度num,'新字符')
 (2)、日期函数
 --获取系统当前日期
 select getdate()
 --计算日期差值
 select datediff(yy/mm/dd,'较早日期','较晚日期')
 --取出(整数)日期值
 select datepart(yy/mm/dd,'日期')
 --指定日期部分的字符串形式
 select datename(yy/..,'日期')
 --添加指定数值到指定日期中
 select dateadd(日期缩写,num,'日期')
 --部分日期缩写
 年份  yy、yyyy 季度  qq、q 月份  mm、m 每年的某一日  dy、y 日期  dd、d
 星期  wk、ww 小时  hh 分钟  mi、n 秒 ss、s 毫秒 ms
 --数学函数
 abs ->绝对值  ceiling ->   >=指定数值、表达式的最小整数
 floor->   <=指定数值、表达式的最大整数 power ->去表达式的幂值
 round ->数值四舍五入指定精度 select round(43.543,1) 1 表示精度 返回 43.5
 sign ->正数返回+1 负数返回-1 0 返回0
 sqrt 取表达式的平方根
 --系统函数
 convert -> 转换数据类型 (2 参数)
 datalength ->返回指定表达式的长度 (汉字做2个字节计算)
 --聚合函数
 max() min() sum() count() avg()
 --连接查询
 (1)、内连接 inner join on ()  ---> 可用 select 列名 from 表名1,表名2，... where 条件 = on()
 (2)、交叉连接查询cross join !!! 不可以用on 关键字
 (3)、外连接
  a、左外连接 left join 表名 on () ---->左配右 null 填右表 from后的为左表
  b、右外连接 right join 表名 on ()---->右配左 null 填左表 from后的为右表
  c 、完整外连接 full join on ()