服务中心网站建设方案,什么是网络营销方案,wordpress标签管理系统,个人微信公众平台怎么用第四章 SQL 基本内容 系统结构、DDL、DML、视图、数据控制、嵌入式SQL SQL介绍 特点 一体化#xff1b;面向集合操作#xff1b;非过程化语言#xff1b;可以单独写#xff0c;也可以作为嵌入式语言#xff08;JDBC#xff09; 体系结构 数据库存储结构 逻辑存储结构 面…第四章 SQL 基本内容 系统结构、DDL、DML、视图、数据控制、嵌入式SQL SQL介绍 特点 一体化面向集合操作非过程化语言可以单独写也可以作为嵌入式语言JDBC 体系结构 数据库存储结构 逻辑存储结构 面向用户可视化部分 物理存储结构 存储在磁盘上的文件包括数据文件和事务日志文件 主数据文件.mdf 数据库起点指向数据库中的其他文件每个数据库 有且仅有一个主数据文件 次要数据文件.ndf 除了主数据文件之外的文件都是次要数据文件一个数据库科可以包含零个或多个次要数据文件 事务日志文件.ldg 存储所有事物及每个事务对数据库的修改用于对数据库的恢复每个数据库至少有一个日志文件 数据库主要对象 用户创建数据库时仅仅创建了物理文件逻辑数据库在逻辑角度对数据库进行描述提供给用户逻辑视图主要对象有表table视图view关系存储过程procedure触发器trigger索引index约束constrain规则rule角色role用户user用户定义数据类型用户自定义函数function 数据库组成 系统数据库用于记录系统信息的数据库 master 记录SQL Server的所有系统级信息还记录其他数据库是否存在以及这些数据库文件的位置需要经常备份master数据库修改后备份一次 model 为新建的数据库创建一个模板在用户执行CREATE DATABASE 自动生成 不可删除 msdb 提供程序调度警报和作业及记录操作员使用 resource 只读数据库包含所有系统对象 tempdb 保存所有临时表和临时存储过程每次启动时都重新创建 用户数据库示例数据库 数据库数据类型 整数类型 bigint8字节int4字节small2字节tinyint1字节 浮点数类型 real4字节可以存储正或负的十进制数float8字节decimal小数numeric 字符类型 charn固定长度字符串,1字节n取值为1~8000varcharn | max可变长度字符串text可变长度字符串 日期和时间类型 date3字节 数据格式为“YYYY-MM-DD”time5字节 据格式为“hh:mm:ss[.nnnnnnn]” 数据库操作 面试 创建数据库 创建模式创建模式就是创建数据库 create scheme scheme_name authorization user_name 也可以在创建表的时候显示指明模式从而同时创建二者 create table STT.student (sno.....) 创建表 create table student(sno char(6) not null unique, sage int, ssex char(2), cno char(6) not null, constrain c1 check ssex in (男,女), constarin pk primary key(sno), constrain fk foreign key (cno));constrain规定了列级作用域一列或者表级作用于整个表的约束其中可以使用创建域来约束域 create domain domain_name data_type 修改表 alter table add 列名 类型alter table drop 列名 [cascade | restrict]alter table modify 列名 类型创建约束 添加主键约束 Alter table 表名 add Constraint 主键名 primary key(字段) 添加唯一约束 Alter table 表名 add Constraint 约束名 unique(字段) 添加默认约束 Alter table 表名 add Constraint 约束名 default默认内容 for 字段名 添加检查约束 Alter table 表名 add Constraint 约束名 check (字段表达) 添加外键约束 Alter table 表名 add Constraint 约束名 foreign key(字段) references 表名字段名 撤销表 drop table table_name 删除数据删除一个或多个元组 delete from table [where P] 插入数据 列名和值一一对应 面试 没有写到的属性为null 可以省略列名默认为全部属性且values的值必须按照顺序进行排布。成批插入数据是把一次子查询的所有结果插入指定表中DBMS在执行插入语句后会自动检查是否破坏了完整性约束insert into table_name(属性列) values(值|子查询) 更新数据 主码数据不允许修改 面试 update table_name set 列名 表达式|子查询 截断表 truncate table table_name 删除表的所有行数据 比delete更快且使用更少的事务和系统资源操作不可以回滚delete可以回滚 索引 一种数据结构可以提高查询速度可以在一个表上建立一个或者多个索引 索引的数据结构 B树/B树Hash索引 建立索引 有的DBMS自动创建索引建立在PK上也可以由DBA自己建立索引 create [unique] index index_name on table_name (列表名) 非聚簇索引 索引存储在一个地方数据存储在另一个地方通过指针进行指明 聚簇索引 面试 数据进行重新排序存储索引和数据混为一体 一个表只能有一个聚簇索引但是聚簇索引可以包含多个列适用于很少增删很少对变长列修改找到一个值后寻找后续索引可以直接找相邻物理位置有助于提高查询性能 删除索引 drop index index_name 索引选择 面试 在进行索引选择上应该考虑到空间和时间效率 对某一列进行索引可以提高其查询效率还可以加快连接带索引的表会占用更多空间同时对数据进行插入、更新等操作会有更大的代价更适合建立索引情况 查询操作多于更新操作经常出现在where中的属性 索引优化 面试 在进行查询操作时不要使用非索引列可以使用多列索引提高效率优先使用选择性强的索引即在选择性强的列设置索引 DBMS自动维护和使用索引不需要用户管理索引失效的情况 对数据进行增删没有在索引列进行查找使用like模糊查询索引列的判断条件是is NULL或is not NULL 元组操作 针对表中元组的操作 查询select select选中的属性名默认是all也可以使用distinct来消除重复值select中可以使用四则元素对属性的域进行修改但是这种修改不会执行到数据库中执行和编写顺序join-where-group by-havinggroup by中的属性必须在select中写出查询语句与关系代数对应关系 select——投影where——选择join——连接 聚集函数 avg、max、min、sum对数值类型进行操作、count对任意类型操作 出了count函数其他都不对null进行操作即直接忽视null 常常用在having操作中不能出现在where子句中 like模糊查询 面试 % 包含 零个或更多 字符的任意字符串。_下划线 任何 单个 字符如a_b可以是abbaabahb 指定 范围 例如 [a-f] 或集合 例如 [abcdef]内的任何单个字符。[^] 不在指定范围例如 [^a - f]或集合例如 [^abcdef]内的任何单个字符。实例 LIKE ‘赵%’ 将搜索姓赵的人名或者说以汉字‘赵’ 开头的字符串如 赵刚、赵小刚等。LIKE ‘%刚’ 将搜索以汉字‘刚’结尾的所有字符串如 刘刚、李小刚等。LIKE ‘%小%’ 将搜索在任何位置包含汉字‘小’的所有字符串如赵小刚、李小刚、山本小郎等。LIKE ‘_小刚’ 将搜索以汉字“小刚”结尾的所有三个汉字的名称如 李小刚、赵小刚。 集合操作 针对整个集合的操作 union并集 如果需要保留重复的元组需要使用union all intersect交集 except差集 面试 以上都是自动去重若不需要自动去重在后面加上all order语句只能出现在最后 嵌套子查询 where 嵌套 集合成员比较where expression (not) in 子查询 非相关子查询 子查询与外层无关只执行一次效率高 实例 集合之间比较where expression comparison_operator some|any子查询 集合基数where (not) exist 子查询 相关子查询 子查询与外层查询有关依赖于外层查询结果或引用了外层查询 from 嵌套having 嵌套可以用嵌套子循环替换and操作和一些自然连接 优化效率 空值操作 面试 SQL中有三种逻辑值truefalseunknown任何值包括null与null进行 逻辑运算 都会返回unknownnull与 数值 进行运算时会返回null在where 和having条件中unknown会被视作false处理在check约束中unknown会被视作true处理空值判断只可以使用 name (not) is null 可以使用 但是最好使用 is 判断条件未必是个单值如果返回值多个则会出错聚集函数中只有count不会忽视null进行操作 视图 面试 根据基本表导出也叫虚表不存储与内存中 特点 对不同用户提供 个性化服务提高 数据库安全性 限制了用户对数据的访问范围提供了 逻辑独立性不会出现数据冗余 基表中数据发生变化视图中数据也会变化不占用存储空间 以定义的方式存储在数据库中使用时只是执行了查询语句视图上还可以定义视图 创建视图 create view view_name as 子查询 【with check option】 撤销视图 drop view view_name 删除基表时视图也会删除 插入视图 insert into view_name values(Lee,1000) 修改视图 最好不使用视图更新有些视图是无法被更新的更新视图是基于更新基本表的情况更新的视图更新约束 只有视图包含基本表得主键时才可以更新目标列不包含聚集函数不使用unique和distinct不包括group by 视图查询 检查视图是否存在将视图临时实体化生成临时表查询视图转化为查询临时表查询完毕删除临时表也可以使用视图消解法即将对视图得查询语句进行修改然后转化为对基表得查询语句 关系连接join 连接 第三章 关系数据库 内连接 inner join 默认是笛卡尔乘积 select * from table1 inner join table2 on condition等值连接 保留重复列的自然连接也就是关系数据库运算中的theta连接 select * from T_student s inner join T_class c on s.classId 运算符 c.classId 外连接 outer join 默认相当于取并集分成左外连接和右外连接left outer joinright outer join 全连接对于并集中没有的用null填充 自连接self join 面试 select * from table as t1,table as t2 当一个表需要多次使用时使用 交叉连接cross join 就是笛卡尔连接实际上很少使用 compute by group by返回的数据集中只保留合计数据并没有保留原始数据使用compute by函数可以使得合计数据作为附加项附加到原始数据集的最后compute by前面必须使用order by 嵌入式SQL 面试 SQL使用方式 在终端使用得交互式SQL 在高级语言中使用的嵌入式SQL高级语言称作宿主语言 实现方式 k扩种宿主语言的编译器使之可以编译SQL语言 进行预处理方式 嵌入式SQL在嵌入高级语言时已经确定无法更改 动态SQL 面试 传统在高级语言中嵌入SQL例如JDBC需要预先编译好语句虽然可以用占位符但是还是不方便根据用户输入或者外部条件动态组合的SQL语句根据不同的需求完成不同的任务经过预处理和执行阶段 预处理只需要执行一次多次调用需要多次执行编译 T-SQLtransact SQL 面试 提供了变量定义赋值操作流程控制函数等语句供用户使用。标准SQL的非过程性的缺陷 数据类型 系统定义数据类型 用户自定义数据类型 用户自定义数据类型是建立在 SQL Server 系统数据类型基础上的 一般情况下当多个表的列中要存储同样类型的数据且想确保这些列具有完全相同的数据类型、长度、取值范围和是否等特点时可使用用户定义数据类型。SQL Server 为 用 户 提 供 了 两 种 方 法 来 创 建 自 定 义 数 据 类 型 即 使 用 SQL ServerManagement Studio 创建用户自定义数据类型调用 系统存储过程 sp_addtype 创建用户自定义数据类型。 sp_addtype type_name 系统数据类型 常量 整型常量实型常量日期型常量货币常量 变量 局部变量必须先定义后使用 命名 标识符定义 declare 标识符 类型赋值 set 标识符 值select 标识符 值set 和select区别 set一次只能赋值一个变量select一次可以赋值多个变量 set 变量1 value1set 变量2 value2select 变量1 value1,变量2 value2 当表达式返回多个值时select会返回最后一个而set会报错 set 变量 (select 属性 from 表)报错select 变量 属性 from 表 当表达式返回空值时 set会赋空值select则保持不变 set 变量 (select 属性 from 表)报错select 变量 属性 from 表 使用标量子查询时如果无返回值SET和SELECT一样都将置为NULL 用途函数和存储过程参数局部使用供用户使用强制转化操作 cast 变量 as type 初始化为null 全局变量 命名标识符常见的由系统提供存储系统的信息 流程控制 流程控制和程序设计语言中的流程控制类似是SQL提供给用户用于改变语句执行顺序的控制语句 begin and相当于程序设计语言中的一个语句块{ } 批处理中的语句一旦出现错误和事务一样一句也不执行 函数 T-SQL提供了很多内置函数 字符串函数数学函数时间和日期函数 DATENAME(YYYY,GETDATE()) 返回日期中某部分字符串DATEPART(MM,GETDATE()) 返回日期中某部分数据DATEADD(datepart,number,date) 在一个日期格式上增加间隔返回值还是日期值DATEDIFF(datepart,startdate,enddate) 计算两个日期的间隔GETDATE() 获取当前的日期 用户自定义函数 create function 【database_name】 function_name【参数名 参数类型】returns **参数数据类型**asbegin function bodyreturn **返回参数** end 使用函数 select 变量 **database.函数名(参数)** 游标 从包含多条数据记录的结果集中返回一条记录每次处理一行或一部分行 可以被视作一个指针可以指向结果集中的任意一个位置并对其进行处理,使用过程声明游标-》打开游标-》检索游标-》关闭游标-》释放游标声明游标 declare 游标名 cursor for select语句 打开游标 open 游标名 检索游标 fetch【next | prior | first | last | 】from 游标名 into 变量名称 检查游标是否有更多行可以提取使用系统变量 FTECH_STATUS WHILE FTECH_STATUS 0BEGIN FETCH [NEXT | PRIOR | FIRST | LAST] FROM 游标名 into 变量名称 END 关闭游标删除游标 clsoe 游标名deallocate 游标名 关闭游标并释放资源关闭了的游标与原来的查询结果集不再关联可以被再次打开与新的结果集进行关联 游标系统变量 CURSOR_ROWS 表示当前打开的游标中存在的记录数量。FETCH_STATUS 返回被 FETCH 语句执行的最后游标的状态 存储过程和触发器 存储过程 是SQL中的一种对象封装了可重用的模块和子程序可以接受、输出参数返回单个或多个结果集以及返回值 存储过程经数据库编译后存储在数据库服务端应用程序只需要调用一次代码便可以执行存储过程的所有代码类似于程序设计语言中的函数 优点 执行前已经存储在数据库中比起函数有更好的性能用户不用访问数据库的数据和对象提供了安全性机制减少了网络通信量用户只需要执行存储过程即可存储过程内大量的代码已经在服务器中存储 分类 系统存储过程用于完成某些特殊的功能以sp_开头用户自定义存储过程 创建存储过程 执行存储过程 EXEC procedure_name 参数 触发器 面试 是一种特殊的存储过程 和数据库架构和表紧密关联当数据库架构发生变化或表的结构发生变化增、删、修改时触发器自动运行一个表可以有多个触发器 创建触发器 实现设置好触发器名称、触发器关联的表、触发器触发的时间、触发器触发执行的操作的类型uupdate、delete、insert、触发器触发后执行的语句 分类 update触发器检查修改操作是否符合要求insert触发器检查插入操作是否符合要求delete触发器用作级联删除和记录外键的删除操作 delete触发器不会被截断表truncate触发 原理 触发器创建时会创建两个表 deleted和inserted表 他们都是 临时表 DBMS会自动管理这些临时表 触发器完成后自动删除 面试deleted表中存储的是被delete操作和update操作影响的数据在从原表中操作后会自动记录一份副本到deleted表中inserted表中存储的是inserted操作和update操作中受影响的数据插入后会自动存一份副本到inserted表中 创建触发器 触发器性能考虑 触发器执行的语句尽量简单触发器执行速度快因为deleted和inserted表都在缓存中尽量减少触发器中ROLLBACK语句使用系统开销特别大在使用触发器前最好还是考虑使用check约束
