精华内容
下载资源
问答
  • NULL 博文链接:https://ohgrateboy.iteye.com/blog/477863
  • 本文通过实例代码给大家介绍了BootStrap Table复选框默认选中功能(从数据库获取到对应的状态进行判断是否为选中状态),代码简单易懂,非常不错,具有参考借鉴价值,需要的的朋友参考下吧
  • 数据库面试

    千次阅读 多人点赞 2019-02-13 09:03:42
    一、数据库问答题 1. SQL语言包括哪些类型? 数据定义DDL:Create Table,Alter Table,Drop Table, Create/Drop Index等 数据操纵DML:Select ,insert,update,delete, 数据控制DCL:grant,revoke 2. 内联接,外联接...

    数据库常见问题

    1. SQL语言包括哪些类型?
    数据定义DDL:Create Table,Alter Table,Drop Table, Create/Drop Index等
    数据操纵DML:Select ,insert,update,delete,
    数据控制DCL:grant,revoke

    2. 内联接,外联接区别?
    内连接是保证两个表中所有的行都要满足连接条件,而外连接则不然。
    在外连接中,某些不满条件的列也会显示出来,也就是说,只限制其中一个表的行,而不限制另一个表的行。分左连接、右连接、全连接(mysql不支持)三种。

    3. 什么是存储过程?用什么来调用?
    答:存储过程是一组为了完成特定功能的SQL 语句集,经编译创建并保存在数据库中,一次编译后永久有效。通过Call语句指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。
    由于存储过程是经过预编译处理的,因此如果某次操作需要执行多次SQL,使用存储过程比单纯SQL语句执行要快。存储过程不允许执行return语句,但是可以通过out参数返回多个值,存储过程一般是作为一个独立的部分来执行,存储过程是一个预编译的SQL语句。

    4.触发器的作用?
    答:触发器是一种特殊的存储过程,主要是通过事件来触发而被执行的。
    它可以强化约束,来维护数据的完整性和一致性,可以跟踪数据库内的操作从而不允许未经许可的更新和变化。可以联级运算。如,某表上的触发器上包含对另一个表的数据操作,而该操作又会导致该表触发器被触发。触发器无法由用户直接调用,而是由于对表的【增/删/改】操作被动引发的。

    5. 索引的作用?和它的优点缺点是什么?
    答:索引就一种特殊的查询表,可以利用它加速对数据的检索。它很类似与现实生活中书的目录,不需要查询整本书内容就可以找到想要的数据。索引可以是唯一的,创建索引允许指定单个列或者是多个列。
    缺点:它减慢了数据录入和修改的速度,同时索引也会占用一定的存储资源,因此我们在建索引的时候需要斟酌。

    6. 维护数据库的完整性和一致性,你喜欢用触发器还是自写业务逻辑?为什么?
    答:我是这样做的,尽可能使用约束,如主键,外键,非空字段等来约束(Check约束在mysql中可以使用但是对数据验证没有任何作用),这样做效率最高,也最方便。其次是使用触发器,这种方法可以保证,无论什么业务系统访问数据库都可以保证数据的完整新和一致性。触发器是针对每一行的;对增删改非常频繁的表上切记不要使用触发器,因为它会非常消耗资源。 最后考虑的是自写业务逻辑,但这样做麻烦,编程复杂,效率低下。

    7. 什么是事务?什么是锁?事务有哪些类型?

    1. 事务:就是被绑定在一起作为一个逻辑工作单元的SQL语句分组。如果任何一个语句操作失败那么整个操作就被失败,以后操作就会回滚到操作前状态,或者是上有个节点。为了确保要么执行,要么不执行,就可以使用事务。要将有组语句作为事务考虑,就需要通过ACID测试,即原子性,一致性,隔离性和持久性。
    2. 锁:在所以的DBMS中,锁是实现事务的关键,锁可以保证事务的完整性和并发性。与现实生活中锁一样,它可以使某些数据的拥有者,在某段时间内不能使用某些数据或数据结构。当然锁还分级别的。
    • 扁平化事务:在扁平事务中,所有的操作都在同一层次,这也是平时使用最多的事务,主要限制是不能提交或回滚事务的某一部分,要么都成功要么都回滚。
      带保存点的扁平事务:解决了扁平事务的弊端,它允许事务在执行过程中回滚到较早的状态而不是全部回滚,通过在事务中插入保存点,当操作失败后可以选择回滚到最近的保存点处。
      链事务:可看做第二种事务的变种,它在事务提交时,会将必要的上下文隐式传递给下一个事务,当事务失败时,可以回滚到最近的事务,不过链事务只能回滚到最近的保存点,而带保存点的扁平化事务是可以回滚到任意一个保存点。
      嵌套事务:由顶层事务和子事务构成,类似于树的结构,一般顶层事务负责逻辑处理,子事务负责具体的工作,子事务可以提交,但真正的提交要等到顶层事务的提交,如果顶层事务回滚,那么所有的子事务都将回滚。
      分布式事务:在分布式环境中的扁平化事务。
      常用的分布式事务解决方案:
      (1) XA规范,是保证强一致性的刚性事务,实现方式有两段式提交(2PC)和三段式提交(3PC),2PC需要一个事务协调者来保证事务的参与者都完成了第一阶段的准备工作,如果协调者收到了所有的参与者都准备好的消息,就会通知所有的事务执行第二阶段的提交,一般场景下两段式提交已经能很好的解决分布式事务了。然而两阶段在即使只有一个进程发生故障时,也会导致整个系统存在较长时间的阻塞。三段式提交通过增加pre-commit阶段来减少两段式提交提到的系统阻塞时间,三段式提交很少在实际中使用,简单了解就行了。
      (2) TCC:是满足最终一致性的柔性事务方案。TCC采用补偿机制,核心的思想是对每一个操作都要注册对应的确认和补偿操作,分为三个阶段,try阶段主要对业务系统进行检测及资源预留,confirm阶段对业务系统进行确认提交,cancel阶段是对业务执行错误,执行回滚释放预留的资源。
      (3) 消息一致性方案:基本思路是将本地操作和发送消息封装在一个事务中,保证本地的操作和消息发送要么都成功,要么都失败。下游应用订阅消息,收到消息后执行对应的操作。
      (4)GTS:阿里云的全局事务服务,对应的开源版本是Fescar,Fescar基于两段式提交进行改良,剥离了分布式事务方案对数据库在协议支持上的要求,使用Fescar的前提是分支事务中涉及的资源必须支持ACID事务的关系型数据库,分支的提交和回滚都依赖于本地事务来保障。了解即可。

    8. 事务的基本要素?
    原子性Atomicity,指事务由原子的操作序列组成,所有操作要么全部成功,要么全部失败回滚。
    一致性Consistency,指事务的执行不能破坏数据库数据的完整性和一致性,一个事务在执行之前和执行之后,数据库都必须处以一致性状态。比如在做多表操作时,多个表要么都是事务后新的值,要么都是事务前的旧值。
    隔离性Isolation,指多个用户并发访问数据库时,数据库为每个用户执行的事务,不能被其他事务的操作所干扰,多个并发事务之间要相互隔离。事务的隔离级别在后文中介绍。
    持久性Durability,指一个事务一旦提交并执行成功,那么对数据库中数据的改变就是永久性的,即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。

    9. 事务的并发问题?事务的隔离级别有哪些?

            事务的并发问题

      1、脏读:事务A读取了事务B更新的数据,然后B回滚操作,那么A读取到的数据是脏数据

      2、不可重复读:在一个事务里面读取了两次某个数据,读出来的数据不一致,事务 A 多次读取同一数据,事务 B 在事务A多次读取的过程中,对数据作了更新并提交,导致事务A多次读取同一数据时,结果不一致。

      3、幻读:在一个事务里面的操作中发现了未被操作的数据,系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级,但是系统管理员B就在这个时候插入了一条新的学生成绩具体分数的记录,当系统管理员A改结束后发现还有一条记录没有改过来,就好像发生了幻觉一样,这就叫幻读。

      小结:不可重复读的和幻读很容易混淆,不可重复读侧重于修改,幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行,解决幻读需要锁表

    事务隔离级别脏读不可重复读幻读
    读未提交(read-uncommitted)
    读已提交(read-committed)
    可重复读(repeatable-read)
    串行化(serializable)

    10. 什么叫视图?游标是什么?
    答:视图是对一些原表选择部分列合并成的一个虚拟表格,具有和物理表相同的功能。可以对视图进行插入、更新、删除操作。对视图的修改会同步到具体数据表中。注意以下几种情况不能对视图进行插入、更新、删除的操作:

    1. 视图列中含有统计函数的情况
    2. 视图使用了GROUP BY /HAVING,DISTINCT,UNION语句的情况
    3. 视图定义时使用了子查询的情况
    4. 对视图的修改涉及到了多个基础表的数据

    游标:是对查询出来的结果集作为一个单元来有效的处理。游标可以定在该单元中的特定行,从结果集的当前行检索一行或多行。可以对结果集当前行做修改。一般不使用游标,但是需要逐条处理数据的时候,游标显得十分重要。

    11. 什么是主键?什么是外键?
    主键是表格里的(一个或多个)字段,只用来定义表格里的行;主键里的值总是唯一的。外键是一个用来建立两个表格之间关系的约束。这种关系一般都涉及一个表格里的主键字段与另外一个表格(尽管可能是同一个表格)里的一系列相连的字段。那么这些相连的字段就是外键。

    12.一个表有过多索引需要有什么样的性能考虑?
    时间和空间。对一个表格的索引越多,数据库引擎用来更新、插入或者删除数据所需要的时间就越多,因为在数据操控发生的时候索引也必须要维护。

    13.你可以用什么来确保表格里的字段只接受特定范围里的值?
    这个问题可以用多种方式来回答,但是只有一个答案是"好"答案。您希望听到的回答是Check限制(MYSQL有,但是没有强制约束,使用了也没用),它在数据库表格里被定义,用来限制输入该列的值。
    触发器也可以被用来限制数据库表格里的字段能够接受的值,但是这种办法要求触发器在表格里被定义,这可能会在某些情况下影响到性能。因此,微软建议使用Check限制而不是其他的方式来限制域的完整性。

    15. 使用索引查询一定能提高查询的性能吗?为什么
    通常,通过索引查询数据比全表扫描要快.但是我们也必须注意到它的代价.
    索引需要空间来存储,也需要定期维护, 每当有记录在表中增减或索引列被修改时,索引本身也会被修改. 这意味着每条记录的INSERT,DELETE,UPDATE将为此多付出磁盘I/O. 因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢.使用索引查询不一定能提高查询性能,索引范围查询(INDEX RANGE SCAN)适用于两种情况:
        1.基于一个范围的检索,一般查询返回结果集小于表中记录数的30%宜采用;
        2.基于非唯一性索引的检索
    索引就是为了提高查询性能而存在的,如果在查询中索引没有提高性能,只能说是用错了索引,或者讲是场合不同

    16. 列举几种表连接方式,并写出SQL语句? 什么是相关子查询?关联查询和相关子查询的区别?
    内连接、外连接(左、右、全)、自连接(特殊的内连接)、交叉连接
    Or hash join/merge join/nest loop(cluster join)/index join 
    1)内连接:只连接匹配的行
    select A.c1,B.c2 from A join B on A.c3 = B.c3;
    2)左外连接:包含左边表的全部行(不管右边的表中是否存在与它们匹配的行)以及右边表中全部匹配的行,最终结果数大于等于左表行数
    select A.c1,B.c2 from A left join B on A.c3 = B.c3;
    3)右外连接:包含右边表的全部行(不管左边的表中是否存在与它们匹配的行)以及左边表中全部匹配的行,最终结果数大于等于右表结果数
    select A.c1,B.c2 from A right join B on A.c3 = B.c3;
    4)全外连接:包含左、右两个表的全部行,不管在另一边的表中是否存在与它们匹配的行
    select A.c1,B.c2 from A full join B on A.c3 = B.c3;
    5)交叉连接又称隐式的内联查询:生成笛卡尔积——它不使用任何匹配或者选取条件,而是直接将一个数据源中的每个行与另一个数据源的每个行一一匹配
    select A.c1,B.c2 from A,B;
    6)相关子查询是一种包含子查询的特殊类型的查询。查询里包含的子查询会用到外部查询的值。
        SELECT * FROM A WHERE A.id IN(SELECT B.aId FROM B  WHERE B.name=A.name)
     7)表关联可能有多条记录,子查询只有一条记录,如果需要唯一的列,最好走子查询

    交叉连接与内连接,查询的结果是一样的,但是效率不同,内连接查询的效率更高。

    17 如何优化数据库,如何提高数据库的性能?
    主要从:表结构、索引、sql语句考虑
    (1)表结构如:合适的字段类型、适当增加中间表、适当拆分表、增加冗余字段避免关联查询、尽量不要使用null
    (2)索引:不要盲目使用索引、要在数据差别大的列上使用索引、要在查询常用到的列上增加索引、根据具体查询情况选择合适的索引数据结构
    (3)查询时不要select *,注意查询是否使用了索引、减少关联查询、减少in和not in这些查询、针对不同的情况选择in或者exists查询
    (4)适当的使用存储过程

    18. 谈谈数据库设计的三范式
    第一范式的定义:所有的域都应该是原子性的,即数据库表的每一列都是不可分割的原子数据项,则这个表属于第一范式(常记成1NF)。简而言之:"每一字段只存储一个值"。例如:职工号,姓名,电话号码组成一个表(一个人可能有一个办公室电话 和一个家里电话号码)
    第二范式的定义:如果一个表属于1NF,任何属性只依赖于关键字,则这个表属于第二范式(常记成2NF )。简而言之:必须先符合1NF的条件,且每一行都能被唯一的识别。将1NF转换成2NF的方法是添加主键。例如:学号,姓名,课程名,成绩
    第三范式的定义:如果一个表属于2NF,且不包含传递依赖性,则这个表是第三范式(常记成 3NF)。简而言之,第三范式就是属性不依赖于其它非主属性。例如,存在一个部门信息表,其中每个部门有部门编号(dept_id)、部门名称、部门简介等信息。那么在员工信息表中列出部门编号后就不能再将部门名称、部门简介等与部门有关的信息再加入员工信息表中。如果不存在部门信息表,则根据第三范式(3NF)也应该构建它,否则就会有大量的数据冗余。

    19. 设计数据库应注意那些问题
    首先应尽量满足三范式的要求,在一定程度上打破三范式的要求以提高数据库的性能。例如,我们创建某些表的时候,不仅会插入外键,还会插入相关的属性,这违反了第三范式,但这样做的好处,就是我们在业务查询的时候会减少很多关联查询,从而提高查询效率。

    20. 表与表之间的关联关系
    分为3种:一对一、一对多、多对多。

    21. 主键和外键的区别
    主键在本表中是唯一的、不可为空的,外键可以重复可以唯空;外键和另一张表的主键关联,不能创建对应表中不存在的外键。

    22. 游标的作用?如何知道游标已经到了最后?
    游标用于定位结果集的行,在mysql中用DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; 可以判断是否到了最后,通常此变量不等于0表示出错或到了最后。

    23. 事前触发和事后触发有何区别?语句级触发和行级触发有何区别?
    事前触发器运行于触发事件发生之前,如表的插入、更新、删除之前,而事后触发器运行于触发事件发生之后,如表的插入、更新、删除之后。通常事前触发器可以获取事件之前和新的字段值。语句级触发器可以在语句执行前或后执行,而行级触发在触发器所影响的每一行触发一次。

    24. 数据库设计的必要性及设计步骤
    好的数据库结构有利于:节省数据的存储空间,能够保证数据的完整性,方便进行数据库应用系统的开发
    设计不好的数据库结构将导致:数据冗余、存储空间浪费和内存空间浪费
    不管数据库的大小和复杂程度如何,可以用下列基本步骤来设计数据库:收集信息--标识对象--设计数据模型--标识每个对象--存储的信息类型–标识对象之间的关系

    25. 什么是数据模型?什么是规范化?
    数据模型是一种标识实体类型及其实体间联系的模型。典型的数据模型有网状模型、层次模型和关系模型。
    从关系数据库的表中,除去冗余数据的过程称为规范化。包括:精简数据库的结构,从表中删除冗余的列,标识所有依赖于其它数据的数据

    26. 说出一些数据库优化方面的经验?
    用PreparedStatement 一般来说比Statement性能高:一个sql 发给服务器去执行,涉及步骤:语法检查、语义分析, 编译,缓存
    “inert into user values(1,1,1)”-?二进制
    “inert into user values(2,2,2)”-?二进制
    “inert into user values(?,?,?)”-?二进制

    有外键约束会影响插入和删除性能,如果程序能够保证数据的完整性,那在设计数据库时就去掉外键。(比喻:就好比免检产品,就是为了提高效率,充分相信产品的制造商)
    (对于hibernate来说,就应该有一个变化:empleyee->Deptment对象,现在设计时就成了employee?deptid)

    看mysql帮助文档子查询章节的最后部分,例如,根据扫描的原理,下面的子查询语句要比第二条关联查询的效率高:
    1)select e.name,e.salary where e.managerid=(select id from employee where name='zxx');
    2)select e.name,e.salary,m.name,m.salary from employees e,employees m where
    e.managerid = m.id and m.name='zxx';

    表中允许适当冗余,譬如,主题帖的回复数量和最后回复时间等
    将姓名和密码单独从用户表中独立出来。这可以是非常好的一对一的案例哟!
    sql语句全部大写,特别是列名和表名都大写。特别是sql命令的缓存功能,更加需要统一大小写,sql语句?发给oracle服务器?语法检查和编译成为内部指令?缓存和执行指令。根据缓存的特点,不要拼凑条件,而是用?和PreparedStatment
    还有索引对查询性能的改进也是值得关注的。

    27. union和union all有什么不同?
    Union和Union All的区别之一在于对重复结果的处理。

    UNION在进行表链接后会筛选掉重复的记录,所以在表链接后会对所产生的结果集进行排序运算,删除重复的记录再返回结果。
    而UNION ALL只是简单的将两个结果合并后就返回。这样,如果返回的两个结果集中有重复的数据,那么返回的结果集就会包含重复的数据了。
    从效率上说,UNION ALL 要比UNION快很多,所以,如果可以确认合并的两个结果集中不包含重复的数据的话,那么就使用UNION ALL

    28. 几种索引类型?

    MySQL数据库几个基本的索引类型:普通索引、唯一索引、主键索引、组合索引、全文索引

    • 索引加快数据库的检索速度
    • 索引降低了插入、删除、修改等维护任务的速度
    • 唯一索引可以确保每一行数据的唯一性
    • 通过使用索引,可以在查询的过程中使用优化隐藏器,提高系统的性能
    • 索引需要占物理和数据空间 

    29. 数据库的乐观锁和悲观锁是什么?数据库表锁和行锁又是什么?

    数据库管理系统(DBMS)中的并发控制的任务是确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性和统一性以及数据库的统一性。
    乐观并发控制(乐观锁)和悲观并发控制(悲观锁)是并发控制主要采用的技术手段。

    • 悲观锁:假定会发生并发冲突,屏蔽一切可能违反数据完整性的操作
    • 乐观锁:假设不会发生并发冲突,只在提交操作时检查是否违反数据完整性。

    在MySQL中 引擎为InnoDB的表支持表锁和行锁,而引擎为MyISAM的表只支持表锁

    • 表锁:开销小,加锁快;不会出现死锁;锁定力度大,发生锁冲突概率高,并发度最低
    • 行锁:开销大,加锁慢;会出现死锁;锁定粒度小,发生锁冲突的概率低,并发度高

    30. char和vachar区别?

    char是固定长度,处理速度比vachar快,费内存空间,当存储的值没有达到指定的范围时,会用空格替代。
    vachar是不固定长度,节约存储空间,存储的是真实的值。
    如:存储字符串'abc'
    使用char(10),表示存储的字符将占10个字节(包括7个空字符)
    使用varchar(10),则表示只占3个字节,10是最大值,当存储的字符小于10时,按照实际的长度存储。

    31. 什么是E-R图?

    E-R图也称实体-联系图(Entity Relationship Diagram),提供了表示实体类型、属性和联系的方法,用来描述现实世界的概念模型。

    ER图中有如下四个成分:

    矩形框:表示实体,在框中记入实体名。

    菱形框:表示联系,在框中记入联系名。

    椭圆形框:表示实体或联系的属性,将属性名记入框中。对于主属性名,则在其名称下划一下划线。

    连线:实体与属性之间;实体与联系之间;联系与属性之间用直线相连,并在直线上标注联系的类型。(对于一对一联系,要在两个实体连线方向各写1; 对于一对多联系,要在一的一方写1,多的一方写N;对于多对多关系,则要在两个实体连线方向各写N,M。

    E-R图的3个基本要素是实体、属性和联系

    32.MySQL两种引擎InnoDB和MyISAM的区别?

    1. MyISAM不支持事务,而InnoDB支持事务。
    2. MyISAM锁的是表锁(表锁又分读锁和写锁),而InnoDB锁为行级锁。
    3. MyISAM支持全文类型索引,而InnoDB不支持。
    4. MyISAM相对简单,所以在效率上要优于InnoDB,小型应用可以考虑使用MyISAM。
    5. MyISAM表是保存成文件的形式,在跨平台的数据转移中使用MyISAM存储会省去不少的麻烦。
    6. InnoDB表比MyISAM表更安全,可以在保证数据不会丢失的情况下,切换非事务表到事务表(alter table tablename type=innodb)。

    33. 如何解决MYSQL数据库中文乱码问题?

    建表和建库的时候指定字符集。

    SQL练习

    1.为管理业务培训信息,建立3个表:
    S(S#,SN,SD,SA)S#,SN,SD,SA分别代表学号,学员姓名,所属单位,学员年龄
    C(C#,CN)C#,CN分别代表课程编号,课程名称
    SC(S#,C#,G) S#,C#,G分别代表学号,所选的课程编号,学习成绩
    1)使用标准SQL嵌套语句查询选修课程名称为’税收基础’的学员学号和姓名?
    答案:select s# ,sn from s where S# in(select S# from c,sc where c.c#=sc.c# and cn=’税收基础’)
    2) 使用标准SQL嵌套语句查询选修课程编号为’C2’的学员姓名和所属单位?
    答:select sn,sd from s,sc where s.s#=sc.s# and sc.c#=’c2’
    3) 使用标准SQL嵌套语句查询不选修课程编号为’C5’的学员姓名和所属单位?
    答:select sn,sd from s where s# not in(select s# from sc where c#=’c5’)
    4)查询选修了课程的学员人数
    答:select 学员人数=count(distinct s#) from sc
    5) 查询选修课程超过5门的学员学号和所属单位?
    答:select sn,sd from s where s# in(select s# from sc group by s# having count(distinct c#)>5)

    2. 查询表A(id,name)中存在id重复三次以上的记录,查询语句请写出来?
    SELECT * FROM A GROUP BY id HAVING COUNT(id)>3

    3. 适用于MySql的分页查询语句?
    --limit函数,从数据库表中的m条记录开始,检索n条记录。
    select * from 表名 limit m,n;

    4. 适用于Oracle的分页查询语句?使用rownum关键字,oracle特有的分析函数和树查询非常有用可以看看。
    --从m条开始,检索n条记录。
    select * from (select rownum r,t1.* from 表名称 t1 where rownum < m + n) b where b.r >= m;

    5. 所有部门之间的比赛组合
    一个叫department的表,里面只有一个字段name,一共有4条纪录,分别是a,b,c,d,对应四个球对,现在四个球对进行比赛,用一条sql语句显示所有可能的比赛组合.

    答:SELECT t1.name AS whiteName,t2.name AS blackName FROM department t1 INNER JOIN department t2 ON t1.name>t2.name

    6.简单说一说drop、delete与truncate的区别

    SQL中的drop、delete、truncate都表示删除,但是三者有一些差别

    • delete和truncate只删除表的数据不删除表的结构
    • 速度,一般来说: drop> truncate >delete 
    • delete语句是dml,这个操作会放到rollback segement中,事务提交之后才生效;
      如果有相应的trigger,执行的时候将被触发. truncate,drop是ddl, 操作立即生效,原数据不放到rollback segment中,不能回滚. 操作不触发trigger. 

     7. 每个月份的发生额都比101科目多的科目
    请用SQL语句实现:从TestDB数据表中查询出所有月份的发生额都比101科目相应月份的发生额高的科目。
    请注意:TestDB中有很多科目,都有1-12月份的发生额。
    表名:TestDB
    字段:AccID:科目代码,Occmonth:发生额月份,DebitOccur:发生额。
    答案:
    SELECT DISTINCT t1.AccID FROM TestDB t1 INNER JOIN TestDB t2 ON t2.Occmonth=t1.OccMonth AND t1.DebitOccur>t2.DebitOccur AND t2.AccID=101

    8. 统计每年每月的信息
    year month amount
    1991 1 1.1
    1991 2 1.2
    1991 3 1.3
    1991 4 1.4
    1992 1 2.1
    1992 2 2.2
    1992 3 2.3
    1992 4 2.4
    查成这样一个结果
    year m1 m2 m3 m4
    1991 1.1 1.2 1.3 1.4
    1992 2.1 2.2 2.3 2.4
    答案一:
    select sales.year ,
    (select t.amount from sales t where t.month='1' and t.year= sales.year) '1',
    (select t.amount from sales t where t.month='1' and t.year= sales.year) '2',
    (select t.amount from sales t where t.month='1' and t.year= sales.year) '3',
    (select t.amount from sales t where t.month='1' and t.year= sales.year) as '4' 
    from sales group by year;
    答案二:

    SELECT sales.`year`,GROUP_CONCAT(CONCAT(sales.`year`,':',sales.amount))
     FROM sales GROUP BY sales.`year`

    9. 显示文章标题,发帖人、最后回复时间
    表:id,title,postuser,postdate,parentid
    准备sql语句:
    drop table if exists articles;
    create table articles(id int auto_increment primary key,title varchar(50), postuser varchar(10), postdate datetime,parentid int references articles(id));
    insert into articles values
    (null,'第一条','张三','1998-10-10 12:32:32',null),
    (null,'第二条','张三','1998-10-10 12:34:32',null),
    (null,'第一条回复1','李四','1998-10-10 12:35:32',1),
    (null,'第二条回复1','李四','1998-10-10 12:36:32',2),
    (null,'第一条回复2','王五','1998-10-10 12:37:32',1),
    (null,'第一条回复3','李四','1998-10-10 12:38:32',1),
    (null,'第二条回复2','李四','1998-10-10 12:39:32',2),
    (null,'第一条回复4','王五','1998-10-10 12:39:40',1);

    答案:
    select a.title,a.postuser,
    (select max(postdate) from articles where parentid=a.id) reply 
    from articles a where a.parentid is null;

    10. 查出比经理薪水还高的员工信息:
    Drop table if not exists employees;
    create table employees(id int primary key auto_increment,name varchar(50)
    ,salary int,managerid int references employees(id));
    insert into employees values (null,' lhm',10000,null), (null,' zxx',15000,1
    ),(null,'flx',9000,1),(null,'tg',10000,2),(null,'wzg',10000,3);

    Wzg大于flx,lhm大于zxx

    解题思路:
    根据sql语句的查询特点,是逐行进行运算,不可能两行同时参与运算。
    涉及了员工薪水和经理薪水,所有,一行记录要同时包含两个薪水,所有想到要把这个表自关联组合一下。
    首先要组合出一个包含有各个员工及该员工的经理信息的长记录,譬如,左半部分是员工,右半部分是经理。而迪卡尔积会组合出很多垃圾信息,先去除这些垃圾信息。

    select e.* from employees e,employees m where e.managerid=m.id and e.sala
    ry>m.salary;

    11. 求出小于45岁的各个老师所带的大于12岁的学生人数
    数据库中有3个表 teacher 表,student表,tea_stu关系表。 
    teacher 表 teaID name age 
    student 表 stuID name age 
    teacher_student表 teaID stuID 
    要求用一条sql查询出这样的结果 
    1)显示的字段要有老师name, age 每个老师所带的学生人数 
    SELECT t1.teaID,COUNT(*) FROM teacher_student t1 LEFT JOIN teacher t2 ON t1.teaID=t2.teaID GROUP BY t1.teaID
    2)只列出老师age为40以下,学生age为12以上的记录
    SELECT t1.teaID,t2.name AS teaName,t1.stuID,t3.name AS stuName FROM teacher_student t1 INNER JOIN teacher t2 ON t1.teaID=t2.teaID AND t2.age<40 INNER JOIN student t3 ON t1.stuID=t3.stuID AND t3.age>12


    12. 求出发帖最多的人:
    select authorid,count(*) total from articles group by authorid ORDER BY total DESC LIMIT 1

    13. 一个用户表中有一个积分字段,假如数据库中有100多万个用户,若要在每年第一天凌晨将积分清零,你将考虑什么,你将想什么办法解决?
    alter table drop column score;
    alter table add colunm score int;
    可能会很快,但是需要试验,试验不能拿真实的环境来操刀,并且要注意,
    这样的操作时无法回滚的,在我的印象中,只有inert update delete等DML语句才能回滚,
    对于create table,drop table ,alter table等DDL语句是不能回滚。

    解决方案一,update user set score=0; 
    解决方案二,假设上面的代码要执行好长时间,超出我们的容忍范围,那我就
    alter table user drop column score;
    alter table user add column score int。

    14. xxx公司的sql面试
    Table EMPLOYEES Structure:
    EMPLOYEE_ID NUMBER Primary Key,
    FIRST_NAME VARCHAR2(25),
    LAST_NAME VARCHAR2(25),
    Salary number(8,2),
    HiredDate DATE,
    Departmentid number(2)
    Table Departments Structure:
    Departmentid number(2) Primary Key,
    DepartmentName VARCHAR2(25).

    1)基于上述EMPLOYEES表写出查询:写出雇用日期在今年的,或者工资在[1000,2000]之间的,或者员工姓名(last_name)以’Obama’打头的所有员工,列出这些员工的全部个人信息。
    select * from employees 
    where Year(hiredDate) = Year(date()) 
    or (salary between 1000 and 200)
    or left(last_name,3)='abc';

    2) 基于上述EMPLOYEES表写出查询:查出部门平均工资大于1800元的部门的所有员工,列出这些员工的全部个人信息。
    mysql> select id,name,salary,deptid did from employee1 where (select avg(salary)
    from employee1 where deptid = did) > 1800;

    3) 基于上述EMPLOYEES表写出查询:查出个人工资高于其所在部门平均工资的员工,列出这些员工的全部个人信息及该员工工资高出部门平均工资百分比。
    select employee1.*,(employee1.salary-t.avgSalary)*100/employee1.salary 
    from employee1,
    (select deptid,avg(salary) avgSalary from employee1 group by deptid) as t
    where employee1.deptid = t.deptid and employee1.salary>t.avgSalary;

    二、数据库选择题:
    1. 下面叙述正确的是___C___。 
    A、算法的执行效率与数据的存储结构无关 
    B、算法的空间复杂度是指算法程序中指令(或语句)的条数 
    C、算法的有穷性是指算法必须能在执行有限个步骤之后终止D、以上三种描述都不对

    2. 以下数据结构中不属于线性数据结构的是__C____。
    A、队列 B、线性表 C、二叉树 D、栈

    3. 在一棵二叉树上第5层的结点数最多是__B____。
    A、8 B、16 C、32 D、15

    4. 在结构化方法中,用数据流程图(DFD)作为描述工具的软件开发阶段是__B__。 
    A、可行性分析 B、需求分析 C、详细设计 D、程序编码

    5. 在软件开发中,下面任务不属于设计阶段的是___D___。 
    A、数据结构设计 B、给出系统模块结构 C、定义模块算法 D、定义需求并建立系统模型

    6. 数据库系统的核心是___B___。 
    A、数据模型 B、数据库管理系统 C、软件工具 D、数据库

    7. 下列叙述中正确的是___C___。 
    A、数据库是一个独立的系统,不需要操作系统的支持 
    B、数据库设计是指设计数据库管理系统 
    C、数据库技术的根本目标是要解决数据共享的问题 
    D、数据库系统中,数据的物理结构必须与逻辑结构一致

    8. SQL语句中修改表结构的命令是__C____。 
    A、MODIFY TABLE B、MODIFY STRUCTURE C、ALTER TABLE D、ALTER STRUCTURE

    9. 如果要创建一个数据组分组报表,第一个分组表达式是"部门",第二个分组表达式是"性别",第三个分组表达式是"基本工资",当前索引的索引表达式应当是___B___。 
    A、部门+性别+基本工资 B、部门+性别+STR(基本工资) 
    C、STR(基本工资)+性别+部门 D、性别+部门+STR(基本工资) 

    10. 数据库DB、数据库系统DBS、数据库管理系统DBMS三者之间的关系是___A___。 
    A、DBS包括DB和DBMS B、DBMS包括DB和DBS 
    C、DB包括DBS和DBMS D、DBS就是DB,也就是DBMS

    11. 要控制两个表中数据的完整性和一致性可以设置"参照完整性",要求这两个表__A___。 
    A、是同一个数据库中的两个表 B、不同数据库中的两个表 
    C、两个自由表 D、一个是数据库表另一个是自由表

    12. 在关系模型中,实现"关系中不允许出现相同的元组"的约束是通过___B___。 
    A、候选键 B、主键 C、外键 D、超键

    13. 只有满足联接条件的记录才包含在查询结果中,这种联接为___C___。 
    A、左联接 B、右联接 C、内部联接 D、完全联接

    14. 索引字段值不唯一,应该选择的索引类型为___B___。 
    A、主索引 B、普通索引 C、候选索引 D、唯一索引

    15. 从数据库中删除表的命令是__A____。 
    A、DROP TABLE B、ALTER TABLE C、DELETE TABLE D、USE

    16. DELETE FROM S WHERE 年龄>60语句的功能是__B____。 
    A、从S表中彻底删除年龄大于60岁的记录 B、S表中年龄大于60岁的记录被加上删除标记 
    C、删除S表 D、删除S表的年龄列

    17. SELECT-SQL语句是__B____。 
    A、选择工作区语句 B、数据查询语句 C、选择标准语句 D、数据修改语句

    18. SQL语言是___C___语言。
    A、层次数据库 B、网络数据库 C、关系数据库 D、非数据库

    19. 在SQL中,删除视图用___C___。 
    A、DROP SCHEMA命令 B、CREATE TABLE命令 C、DROP VIEW命令 D、DROP INDEX命令

    20. 在命令窗口执行SQL命令时,若命令要占用多行,续行符是__D____。 
    A、冒号(:) B、分号(;) C、逗号(,) D、连字符(-)

    21. 设有图书管理数据库: 
    图书(总编号C(6),分类号C(8),书名C(16),作者C(6),出版单位C(20),单价N(6,2)) 
    读者(借书证号C(4),单位C(8),姓名C(6),性别C(2),职称C(6),地址C(20)) 
    借阅(借书证号C(4),总编号C(6),借书日期D(8)) 
    对于图书管理数据库,查询0001号借书证的读者姓名和所借图书的书名。 
    SQL语句正确的是___A___。 
    SELECT 姓名,书名 FROM 借阅,图书,读者 WHERE; 
    借阅.借书证号="0001" AND; 
    ______ 
    ______ 
    A、图书.总编号=借阅.总编号 AND; 
    读者.借书证号=借阅.借书证号 
    B、图书.分类号=借阅.分类号 AND; 
    读者.借书证号=借阅.借书证号 
    C、读者.总编号=借阅.总编号 AND; 
    读者.借书证号=借阅.借书证号 
    D、图书.总编号=借阅.总编号 AND; 
    读者.书名=借阅.书名

    22. 设有图书管理数据库: 
    图书(总编号C(6),分类号C(8),书名C(16),作者C(6),出版单位C(20),单价N(6,2)) 
    读者(借书证号C(4),单位C(8),姓名C(6),性别C(2),职称C(6),地址C(20)) 
    借阅(借书证号C(4),总编号C(6),借书日期D(8)) 
    对于图书管理数据库,分别求出各个单位当前借阅图书的读者人次。下面的SQL语句正确的是___A___。 
    SELECT 单位,______ FROM 借阅,读者 WHERE; 
    借阅.借书证号=读者.借书证号 ______ 
    A、COUNT(借阅.借书证号) GROUP BY 单位 B、SUM(借阅.借书证号) GROUP BY 单位 
    C、COUNT(借阅.借书证号) ORDER BY 单位 D、COUNT(借阅.借书证号) HAVING 单位

    23. 设有图书管理数据库: 
    图书(总编号C(6),分类号C(8),书名C(16),作者C(6),出版单位C(20),单价N(6,2)) 
    读者(借书证号C(4),单位C(8),姓名C(6),性别C(2),职称C(6),地址C(20)) 
    借阅(借书证号C(4),总编号C(6),借书日期D(8)) 
    对于图书管理数据库,检索借阅了《现代网络技术基础》一书的借书证号。下面SQL语句正确的是___B___。 
    SELECT 借书证号 FROM 借阅 WHERE 总编号=; 
    ______ 
    A、(SELECT 借书证号 FROM 图书 WHERE 书名="现代网络技术基础") 
    B、(SELECT 总编号 FROM 图书 WHERE 书名="现代网络技术基础") 
    C、(SELECT 借书证号 FROM 借阅 WHERE 书名="现代网络技术基础") 
    D、(SELECT 总编号 FROM 借阅 WHERE 书名="现代网络技术基础")


    三、Oracle面试题
    1. 解释冷备份和热备份的不同点以及各自的优点 
    解答:热备份针对归档模式的数据库,在数据库仍旧处于工作状态时进行备份。而冷备份指在数据库关闭后,进行备份,适用于所有模式的数据库。热备份的优点在于当备份时,数据库仍旧可以被使用并且可以将数据库恢复到任意一个时间点。冷备份的优点在于它的备份和恢复操作相当简单,并且由于冷备份的数据库可以工作在非归档模式下,数据库性能会比归档模式稍好。(因为不必将archive log写入硬盘)
    2. 你必须利用备份恢复数据库,但是你没有控制文件,该如何解决问题呢?
    解答:重建控制文件,用带backup control file 子句的recover 命令恢复数据库。
    3. 如何转换init.ora到spfile?
    解答:使用create spfile from pfile 命令.
    4. 解释data block , extent 和 segment的区别(这里建议用英文术语)
    解答:data block是数据库中最小的逻辑存储单元。当数据库的对象需要更多的物理存储空间时,连续的data block就组成了extent . 一个数据库对象
    拥有的所有extents被称为该对象的segment.
    5. 给出两个检查表结构的方法
    解答:
    1)DESCRIBE命令
    2)DBMS_METADATA.GET_DDL 包
    6. 怎样查看数据库引擎的报错
    解答:alert log.

    7. 比较truncate和delete 命令
    解答:两者都可以用来删除表中所有的记录。区别在于:truncate是DDL操作,它移动HWK,不需要 rollback segment .而Delete是DML操作, 需要rollback segment 且花费较长时间.

    8. 使用索引的理由
    解答:快速访问表中的data block

    9. 给出在STAR SCHEMA中的两种表及它们分别含有的数据
    解答:Fact tables 和dimension tables. fact table 包含大量的主要的信息而 dimension tables 存放对fact table 某些属性描述的信息

    10. FACT Table上需要建立何种索引?
    解答:位图索引 (bitmap index)

    11. 给出两种相关约束?
    解答:主键和外键

    12. 如何在不影响子表的前提下,重建一个母表
    解答:子表的外键强制实效,重建母表,激活外键

    13. 解释归档和非归档模式之间的不同和它们各自的优缺点
    解答:归档模式是指你可以备份所有的数据库 transactions并恢复到任意一个时间点。非归档模式则相反,不能恢复到任意一个时间点。但是非归档模式可以带来数据库性能上的少许提高.

    14. 如何建立一个备份控制文件?
    解答:Alter database backup control file to trace.

    15. 给出数据库正常启动所经历的几种状态 ?
    解答:
      STARTUP NOMOUNT – 数据库实例启动
      STARTUP MOUNT - 数据库装载
      STARTUP OPEN – 数据库打开

    16. 哪个column可以用来区别V$视图和GV$视图?
    解答: INST_ID 指明集群环境中具体的 某个instance 。

    17. 如何生成explain plan?
    解答:运行utlxplan.sql. 建立plan 表
    针对特定SQL语句,使用 explain plan set statement_id = 'tst1' into plan_table
    运行utlxplp.sql 或 utlxpls.sql察看explain plan

    18. 如何增加buffer cache的命中率?
    解答:在数据库较繁忙时,适用buffer cache advisory 工具,查询v$db_cache_advice . 如果有必要更改,可以使用 alter system set db_cache_size 命令

    19. ORA-01555的应对方法?
    解答:具体的出错信息是snapshot too old within rollback seg , 通常可以通过
    增大rollback seg来解决问题。当然也需要察看一下具体造成错误的SQL文本

    20. 解释$ORACLE_HOME和$ORACLE_BASE的区别?
    解答:ORACLE_BASE是oracle的根目录,ORACLE_HOME是oracle产品的目录。

    21. 如何判断数据库的时区?
    解答:SELECT DBTIMEZONE FROM DUAL;

    22. 解释GLOBAL_NAMES设为TRUE的用途
    解答:GLOBAL_NAMES指明联接数据库的方式。如果这个参数设置为TRUE,在建立数据库链接时就必须用相同的名字连结远程数据库

    23。如何加密PL/SQL程序?
    解答:WRAP

    24. 解释FUNCTION,PROCEDURE和PACKAGE区别
    解答:function 和procedure是PL/SQL代码的集合,通常为了完成一个任务。procedure 不需要返回任何值而function将返回一个值在另一
    方面,Package是为了完成一个商业功能的一组function和proceudre的集合

    25. 解释TABLE Function的用途
    解答:TABLE Function是通过PL/SQL逻辑返回一组纪录,用于普通的表/视图。他们也用于pipeline和ETL过程。

    26. 举出3种可以收集three advisory statistics
    解答:Buffer Cache Advice, Segment Level Statistics, Timed Statistics

    27. Audit trace 存放在哪个oracle目录结构中?
    解答:unix $ORACLE_HOME/rdbms/audit Windows the event viewer

    28. 解释materialized views的作用
    解答:Materialized views 用于减少那些汇总,集合和分组的信息的集合数量。它们通常适合于数据仓库和DSS系统。

    29. 当用户进程出错,哪个后台进程负责清理它
    解答: PMON

    30. 哪个后台进程刷新materialized views?
    解答:The Job Queue Processes.

    31. 如何判断哪个session正在连结以及它们等待的资源?
    解答:V$SESSION / V$SESSION_WAIT

    32. 描述什么是 redo logs
    解答:Redo Logs 是用于存放数据库数据改动状况的物理和逻辑结构。可以用来修复数据库.

    33. 如何进行强制LOG SWITCH?
    解答:ALTER SYSTEM SWITCH LOGFILE;

    34. 举出两个判断DDL改动的方法?
    解答:你可以使用 Logminer 或 Streams

    35. Coalescing做了什么?
    解答:Coalescing针对于字典管理的tablespace进行碎片整理,将临近的小extents合并成单个的大extent.

    36. TEMPORARY tablespace和PERMANENT tablespace 的区别是?
    解答:A temporary tablespace 用于临时对象例如排序结构而 permanent tablespaces用来存储那些'真实'的对象(例如表,回滚段等)

    37. 创建数据库时自动建立的tablespace名称?
    解答:SYSTEM tablespace.

    38. 创建用户时,需要赋予新用户什么权限才能使它联上数据库。
    解答:CONNECT

    39. 如何在tablespace里增加数据文件?
    解答:ALTER TABLESPACE ADD DATAFILE SIZE

    40. 如何变动数据文件的大小?
    解答:ALTER DATABASE DATAFILE RESIZE ;

    41. 哪个VIEW用来检查数据文件的大小?
    解答: DBA_DATA_FILES

    42. 哪个VIEW用来判断tablespace的剩余空间
    解答:DBA_FREE_SPACE

    43. 如何判断谁往表里增加了一条纪录?
    解答:auditing

    44. 如何重构索引?
    解答: ALTER INDEX REBUILD;

    45. 解释什么是Partitioning(分区)以及它的优点。
    解答:Partition将大表和索引分割成更小,易于管理的分区。

    46. 你刚刚编译了一个PL/SQL Package但是有错误报道,如何显示出错信息?
    解答:SHOW ERRORS

    47. 如何搜集表的各种状态数据?
    解答: ANALYZE
    The ANALYZE command.

    48. 如何启动SESSION级别的TRACE
    解答: DBMS_SESSION.SET_SQL_TRACE
    ALTER SESSION SET SQL_TRACE = TRUE;

    49. IMPORT和SQL*LOADER 这2个工具的不同点
    解答:这两个ORACLE工具都是用来将数据导入数据库的。
    区别是:IMPORT工具只能处理由另一个ORACLE工具EXPORT生成
    的数据。而SQL*LOADER可以导入不同的ASCII格式的数据源

    50。用于网络连接的2个文件?
    解答: TNSNAMES.ORA and SQLNET.ORA

    展开全文
  • 数据库DROP TABLE IF EXISTS语句

    千次阅读 2020-10-28 09:24:21
    DROP TABLE IF EXISTS database; 创建数据库sql语句

    DROP TABLE IF EXISTS
    创建表之前判断表是否存在,如果存在则删除已有表

    展开全文
  • 数据库学习】数据库总结

    万次阅读 多人点赞 2018-07-26 13:26:41
    1,数据库 1)概念 数据库是长期存储在计算机内、有组织的、可共享的大量数据的集合。 常见数据库管理系统有:Access、mysql、sql server 2)特点 ①数据库数据特点 永久存储、有组织...

    1,概念

    1)数据库

    数据库是长期存储在计算机内、有组织的、可共享的大量数据的集合。
    数据库中存储的是数据及数据之间的关系。

    正常情况读写文件系统比数据库快一到两个数据级;
    数据库的查询,大量并发的时候可能最浪费时间的是connect和close。
    数据库的优势是体现的大量数据的查询、统计以及并发读写,不是在速度上。

    2)数据库数据特点

    永久存储、有组织、可共享。
    (数据的最小存取单位是数据项)

    3)数据库系统的特点

    ①数据结构化

    ②数据的共享性,冗余度,易扩充

    ③数据独立性高

    数据独立性包括:物理独立性和逻辑独立性
    a)物理独立性(外模式\模式映像):
    用户程序不需要了解,应用程序要处理的只是数据的逻辑结构,这样当数据的物理存储改变了,应用程序不用改变。
    b)逻辑独立性(模式\内模式映像):
    逻辑独立性是指用户的应用程序与数据库的逻辑结构是相互独立的,即,当数据的逻辑结构改变时,用户程序也可以不变。
    逻辑数据独立性(logical data independence)是指概念模式改变,外模式和应用程序不变。在逻辑数据独立性里,数据的逻辑结构发生改变或存储关系的选择发生改变时用户不会受到影响。改变概念模式,例如增加和删除实体、增加和删除属性、增加和删除联系,不需要改变现有的外模式或重写应用程序。在DBMS中只需要修改视图的定义和映像来支持逻辑数据独立性。对用户来说,不再关心所做的修改是非常重要的。换句话说,模式经过逻辑重构之后,根据外模式构建的应用程序还是和从前一样工作。

    4)概念模型(E-R模型)

    ①概念

    概念模型的一种表示方法:实体联系方法,用E-R方法(E-R模型)来描述。
    概念模型是用于信息世界的建模,是一种信息模型,与具体的DBMS无关。且能满足用户对数据的处理要求,易于修改。
    概念模型与具体数据模型无关且容易向数据库模型转化。

    实体:举行表示
    属性:椭圆表示,并用直线与实体连接
    联系:菱形表示,用直线与实体连接,同时在边上标上联系的类型(1:1,1:n,m:n)。
    

    一个联系转化为一个关系模式,与该联系相连的各实体的码以及联系的属性转化为关系的属性,该关系的码则有三种情况:
    若联系为1:1,则每个实体的码均是该关系的后选码。
    若联系为1:n,则关系的码为n端实体的码。
    若联系为m:n,则关系的码为诸实体码的组合。

    数据库模式定义语言DDL(Data Definition Language):是用于描述数据库中要存储的现实世界实体的语言。一个数据库模式包含该数据库中所有实体的描述定义。这些定义包括结构定义、操作方法定义等。

    数据库逻辑设计: 将概念设计所得到的概念模型转换为某一具体的数据模型(层次、网状、关系、面向对象).

    5)关系完整性

    在关系模型中,关系完整性主要是指以下三方面:

    实体完整性

    所谓的实体完整性就是指关系(所谓的关系就是表)的主码不能取空值;
    比如学生表的主码通常是取学号为主码

    参照完整性

    是指参照关系中每个元素的外码要么为空(NULL),要么等于被参照关系中某个元素的主码;
    参照关系也称为外键表,被参照关系也称为主键表。

    用户定义的完整性

    指对关系中每个属性的取值作一个限制(或称为约束)的具体定义。比如 性别属性只能取”男“或”女“,再就是年龄的取值范围,可以取值0-130 ,但不能取负数,因为年龄不可能是负数。

    6)关系数据库规范化

    目地:使结构更合理,消除存储异常,使数据冗余尽量小,便于插入、删除和更新。
    原则:遵从概念单一化“一事一地”原则,即一个关系模式描述一个实体或实体间的一种联系。
    规范的实质:概念的单一化。
    规范化的方法:将关系模式投影分解成两个或两个以上的关系模式。

    2,依赖和范式

    1)依赖

    ①部分函数依赖

    设X,Y是关系R的两个属性集合,存在X→Y,若X’是X的真子集,存在X’→Y,则称Y部分函数依赖于X。

        举个例子:通过AB能得出C,通过A也能得出C,通过B也能得出C,那么说C部分依赖于AB。
    

    ②完全函数依赖

    设X,Y是关系R的两个属性集合,X’是X的真子集,存在X→Y,但对每一个X’都有X’!→Y,则称Y完全函数依赖于X。

        举个例子:通过AB能得出C,但是AB单独得不出C,那么说C完全依赖于AB.
    

    ③传递函数依赖

    设X,Y,Z是关系R中互不相同的属性集合,存在X→Y(Y !→X),Y→Z,则称Z传递函数依赖于X。

        举个例子:通过A得到B,通过B得到C,但是C得不到B,B得不到A,那么成C传递依赖于A
    

    ④多值依赖

    设R(U)是属性集U上的一个关系模式。X,Y,Z是U的子集,并且Z=U-X-Y。关系模式R(U)中多值依赖X→→Y成立,当且仅当对R(U)的任一关系r,给定的一对(x,z)值有一组Y的值,这组值仅仅决定于x值而与z值无关。

    举例:
    有这样一个关系 <仓库管理员,仓库号,库存产品号> ,假设一个产品只能放到一个仓库中,但是一个仓库可以有若干管理员,那么对应于一个 <仓库管理员,库存产品号>有一个仓库号,而实际上,这个仓库号只与库存产品号有关,与管理员无关,就说这是多值依赖。

    2)范式

    各个范式联系:
    5NF⊂4NF⊂BCNF⊂3NF⊂2NF⊂1NF

    ①1NF(满足最低要求的范式:字段不可再分,原子性)

    如果一个关系模式R的所有属性都是不可分的基本数据项,则R∈1NF。
    自我理解1NF就是无重复的列。
    如:(X1,X2)→X3,X2→X3 其中x3对x2部分依赖
    如:(X1,X2)→X3,X2→X4 其中有非主属性X4部分依赖于候选键{X1,X2},所以这个关系模式不为第二范式;又因为范式之间的关系满足1NF⊇2NF⊇3NF ⊇ BCNF,所以是第一范式。

    ②2NF(消除部分子函数依赖:一个表只能说明一个事物)

    若R∈1NF,且每一个非主属性完全函数依赖于码,则R∈2NF。
    即要求数据库表中的每个实例或行必须可以被唯一地区分。

    ③3NF(消除传递依赖,即消除非主属性对键的传递依赖:每列都与主键有直接关系,不存在传递依赖。任何非主属性不依赖于其它非主属性。)

    若R∈3NF,则每一个非主属性既不部分依赖于码,也不传递依赖于码。
    自我理解是:表中所有的数据元素不但要能唯一地被主键所标识,而且他们之间还必须相互独立,不存在其他的函数关系。

    ④BCNF(修正第三范式、扩充第三范式 消除主属性对键的传递依赖)

    所有非主属性对每一个码都是完全函数依赖;
    所有主属性对每一个不包含它的码,也是完全函数依赖;
    没有任何属性完全函数依赖于非码的任何一组属性。

    ⑤4NF

    关系模式R<U,F>∈1NF,如果对于R的每个非平凡多值依赖X->->Y(Y∉X),X都含有码,则称R<U,F>∈4NF

    3,数据库平台

    数据库管理系统(DBMS):是系统软件,是数据库系统的核心。
    常见数据库管理系统有:Access、mysql、sql server

    4,数据库语句

    SQL 语言是非过程化的语言,易学习。
    SQL语言具有两种使用方式:一种是在终端交互方式下使用,称为交互式SQL; 另一种是嵌入在高级语言的程序中使用,称为嵌入式SQL,而这些高级语言可以是C、PASCAL、COBOL等,称为宿主语言。

    1)基本对象

    关系数据库系统支持 三级模式结构,其概念模式、外模式和内模式中的基本对象有表、视图和索引。
    三级模式结构有效地组织、管理数据,提高了数据库的逻辑独立性和物理独立性。使数据库达到了数据独立性。

    ①模式(schema,逻辑模式)

    A.概念

    是数据库中全体数据的逻辑结构和特征的描述,是所有用户的公共数据视图。是数据库系统模式结构的中间层,即不涉及数据的物理存储细节和硬件环境,也与具体的应用程序、开发工具及高级设计语言无关。
    模式是数据库数据在逻辑级上的视图,一个数据库只有一个模式。

    也用于区分一个 大项目中的各个小项目,这样若有相同名字的表的话, 不同模式不会发生冲突。相当于编程时的命名空间。
    如:
    一个公司的系统,分2个子系统,分别为财务系统和人力资源系统.
    这2个子系统, 共用一个数据库。
    那么 财务系统的表, 可以放在财务的模式(schema).
    人力资源系统的表,放在人力资源系统的模式里面。
    这2个子系统,能够互相访问对方的表。
    但是又不因为 表重名 的问题,影响对方。

    B.访问

    访问具体的一个表,可以由 4个部分组成
    分别为 服务器名, 数据库名,模式名,表名。

    对于访问本地的数据库:
    不指定模式名的话, 数据库默认使用dbo模式。
    (DBO是每个数据库的默认用户,具有所有者权限,即DbOwner )
    pg不指定模式的话默认使用public模式。

    C.操作

    --创建
    CREATE SCHEMA schema_name;
    

    ②外模式(子模式,用户模式)

    是数据库用户能够看见和使用的局部数据的逻辑结构和特征的描述,是数据库用户的数据视图,是与某一应用有关的数据的逻辑表示。
    外模式通常是模式的子集,一个数据库可以有多个外模式,但一个应用程序只能有一个外模式。
    外模式是保证数据库安全性的一个有力措施:用户只能访问外模式的数据,其余数据不可见。

    ③内模式(存储模式)

    一个数据库只有一个内模式。
    内模式是数据物理结构和存储方式的描述,是数据在数据库内部的表示方式。

    数据库管理系统在三级模式之间提供了两层映像:
    外模式/模式映像(保证数据的逻辑独立性)
    模式/内模式映像(保证了物理独立性)

    ④表

    表分为临时表和永久表。

    临时表

    临时表存储在tempdb中(如下),当不再使用时会自动删除。

    IF OBJECT_ID('tempdb..#ownerAnnouce') IS NOT NULL
    

    根据进程独立,只有进程的拥有者有表的访问权限,其它用户不能访问该表;
    不同的用户进程,创建的临时表虽然“名字”相同,但是这些表之间相互并不存在任何关系;在SQLSERVER中,通过特别的命名机制保证临时表的进程独立性。

    临时表有两种类型:本地和全局。

    A.本地临时表

    名称以单个数字符号 (#) 打头;它们仅对当前的用户连接是可见的;当用户从 SQL Server 实例断开连接时被删除。

    B.全局临时表

    名称以两个数字符号 (##) 打头,创建后对任何用户都是可见的,当所有引用该表的用户从 SQL Server 断开连接时被删除。

    临时表优点

    真正的临时表利用了数据库临时表空间,由数据库系统自动进行维护,因此节省了表空间。并且由于临时表空间一般利用虚拟内存,大大减少了硬盘的I/O次数,因此也提高了系统效率。

    临时表的创建

    A. create table #临时表名
    B.select * into #临时表名 from 表名(永久表或临时表)

    ⑤视图

    A.概念

    视图是一张虚拟表,视图的字段是自定义的,视图只支持查询,查询数据来源于实体表。

    一般视图是只读的,在pg中通过添加规则可以进行视图的更新。从pg9.1开始,用户可以通过INSTEAD OF的触发器来实现视图更新。

    B.优缺点

    • 优点
      视图可以将多个复杂关联表提取信息,方便查询,但不能优化查询速度(调用视图查询时才进行动态检索数据)。
      即,如果你认为一个sql查询非常慢,为了优化它的速度把它建立成视图,这是不可取的,视图是每次调用的时候生成,并不是数据源变化就刷新数据,并不能提高检索效率。
    • 缺点
      视图就是临时表,即调即用,如果数据源没有任何变化,在反复调用中,临时表会缓存到内存中(SHOW STATUS LIKE ‘Qcache%’;),视图中不能创建索引,但视图可以基于索引生成 。

    C.场景

    1. 重用SQL语句;
    2. 简化复杂SQL操作(生成视图),重用查询且不需要知道基本查询细节。
    3. 保护数据。用户有表的部分权限。
    4. 更改数据格式和表示。视图可返回与底层表不同的表示和格式。

    D.操作

    --创建视图
    CREATE OR REPLACE VIEW view_name(studentName, studentAge)  --(studentName, studentAge) 可以去掉,加上是重命名列名
    AS 
    SELECT user_info.name, user_info.age from user_info;
    
    --删除视图
    DROP VIEW view_name;
    

    ⑥实体视图

    相对于普通的视图来说,实体化视图的不同之处在于实体化视图管理存储数据,占据数据库的物理空间。

    实体化视图的结果会保存在一个普通的数据表中,在对实体化视图进行查询的时候不再会对创建实体化视图的基表进行查询,而是直接查询实体化视图对应的结果表,然后通过定期的刷新机制来更新实体化视图表中的数据。

    demo

    -- 创建物化视图
    CREATE MATERIALIZED VIEW MAX_ID_MVIEW 
    AS
      SELECT PART_ID, MAX(ID)  MAX_ID
      FROM PART_DETAIL GROUP BY PART_ID;
      
    -- 如果刷新时不带CONCURRENTLY则无需创建唯一索引
    CREATE UNIQUE INDEX IDX_MAX_ID ON MAX_ID_MVIEW(PART_ID);
    
    -- 利用watch命令每120s刷新一次物化视图
    REFRESH MATERIALIZED VIEW CONCURRENTLY MAX_ID_MVIEW; \watch 120
    

    作用

    1. 减轻网络负担:通过实体化视图将数据从一个数据库分发到多个不同的数据库上,通过对多个数据库访问来减轻对单个数据库的网络负担。
    2. 搭建分发环境:通过从一个中央数据库将数据分发到多个节点数据库,达到分发数据的目的。
    3. 复制数据子集:实体化视图可以进行行级/列级的筛选,这样可以复制需要的那一部分数据。
    4. 实体化视图是用于汇总,预计算,复制或分发数据的对象, 在大型的数据库中使用它可以提高涉及到的SUM,COUNT,AVG,MIN,MAX等的表的查询的速度。
    5. 物化视图的快速刷新采用了增量的机制,在刷新时,只针对基表上发生变化的数据进行刷新。因此快速刷新是物化视图刷新方式的首选。

    ⑦索引

    为了改变数据库的性能和可访问性所增加的一组辅助性数据。
    详细介绍见下文。

    2)数据结构创建及修改

    1>数据库操作

    --查看数据库
    show databases;
    --建库
    create database children;
    --删库
    drop database children;
    --调用数据库
    use children;
    

    2>表操作

    --pg建表
    CREATE TABLE if not exists public.stu_info(   --创建public模式下的表
        FOREIGN KEY (ID) REFERENCES people_info (ID),  --单个外键,一般情况下不建议增加这种强约束
        id int8 PRIMARY KEY,    --系统会自动为主键创建一个隐含的索引  primary key(Sno,Cno)组合主键
        address VARCHAR (255) UNIQUE NOT NULL,
        birthday TIMESTAMP NOT NULL,  
        age int default 15,  --默认值,影响后续插入值。但对旧数据没有影响。
       CONSTRAINT student2_pkey PRIMARY KEY (id),
       CONSTRAINT ck_age CHECK(age<18), --检查约束,约束某些字段需要满足的要求。NULL被认为满足条件。
       CONSTRAINT uk_tbl_unique_a_b unique(id ,address) --唯一约束。唯一键中可以写入任意多个NULL!即可以存在多组 1,null  
    )
    WITH (
      OIDS=FALSE
    );
    ALTER TABLE myschema.tb_test
      OWNER TO postgres;
    
    --重命名表
    alter table tableName RENAME TO newName;--pg
    

    i>指定默认值

    一般用于数据预置或create_time、update_time的自动录入。各个DBMS获得系统日期如下:

    DBMS函数/变量
    AccessNOW()
    DB2CURRENT_DATE
    MySQLCURRENT_DATE()
    OracleSYSDATE
    PostgreSQLCURRENT_DATE
    SQL ServerGETDATE()
    SQLitedate(‘now’)
    --修改默认值
    alter table tableName alter column age set DEFAULT 15;--pg
    --删除默认值
    alter table tableName alter column age drop DEFAULT 15;--pg
    

    ii>表约束

    表约束有:主键、外键、检查约束、唯一约束、非NULL约束。

    --添加主键(有些DBMS不允许在建表之后修改主键)
    ALTER TABLE tableName ADD PRIMARY KEY(fieldName) ; --fieldName在库中不能有重复数据
    --增加约束
    alter table tableName add check (age<16);--pg 增加检查约束,约束名为:tableName_age_check
    alter table tableName add constraint uk_tbl_unique_a_b unique (a,b);--pg 增加唯一约束
    alter table tableName alter column fieldName set NOT NULL;--pg 增加非空约束
    --删除约束
    alter table tableName drop constraint constraintName;--pg 根据约束名删除检查约束、唯一约束
    alter table tableName alter column fieldName drop NOT NULL;--pg 删除非空约束(非空约束没有约束名)
    

    iii>修改表字段

    --增加列\添加一个字段
    alter table tableName add column columnName varchar(30) default 'a' not null; --column 可加可不加
    --删除列(会连同字段上的约束一并删除)
    alter table tableName drop column columnName; --column 可加可不加
    --修改列名:
    alter table tableName rename column fieldName TO fieldNameNew;--pg、oracle中
    exec sp_rename '[表名].[列名]‘,’[表名].[新列名]'--在sqlserver
    ALTER TABLE 表名 CHANGE 列名 新列名 列类型--mysql
    
    --修改字段类型或长度:
    alter table tableName modify column 字段名 类型;
    alter table tableName alter column fieldName TYPE text;--pg修改字段数据类型。仅在当前数据都可以隐式转换为新类型时才可以执行成功
    --将NAME最大列宽增加到10个字符
    ALTER TABLE CARD ALTER COLUMN NAME varchar(10) 
    
    

    3)数据查询

    数据库处理一个查询的步骤:
    客户端连接->查询缓存->解析器->预处理器->查询优化器->查询执行引擎->数据

    1. 客户端发送一条查询给服务器;
    2. 服务器先会检查查询缓存query cache,如果命中了缓存,则立即返回存储在缓存中的结果。否则进入下一阶段;
    3. 服务器端进行SQL解析parsing、预处理transition,再由优化器optimization生成对应的执行计划;
    4. 根据优化器生成的执行计划,调用存储引擎的API来执行分布distribution查询;
    5. 将结果返回给客户端。
    

    1>简单查询

    select * from student;
    select 1+2; #当表达式与表列无关时,在pg和mysql中不适用“from tableName”
    

    拼接查询:
    Access和 SQL Server使用 + 号。DB2、Oracle、PostgreSQL、SQLite和Open Office Base 使用 ||。

    select label || '_' || id from user_info;  --结果:abc_1
    

    2>条件查询

    功能表达举例备注
    等于=
    不等于<>!=
    空值is null,is not nullselect * from student where class is not null;
    确定集合,ininnot inselect * from student where age not in(21,23);在sql标准中仅支持100个以内的占位符作为查询参数。根据数据库不同,对in的参数和长度有不同的限制,否则会直接报错。
    确定范围between and , not between and
    模糊查询like ,not likeselect * from student where name like '%丽%';’ %代表任意长度(可为0)的字符串;_(下划线):代表任意单个字符。(汉字代表2个字符,所以一个汉字用两个下划线);\为转义字符

    select出的别名是否可以作为where查询条件?不能,因为执行计划中where在selectz之前。如:select label a from asset_field where a = '分类'

    3>排序查询

    非排序查询的数据顺序:pg默认返回数据的顺序是插入表的数据顺序。

    # 单个排序:
    select name,age from student order by age desc; # 默认为asc:升序排列。desc:降序排序。
    
    #多重排序:
    order by 字段5,字段6 asc  //先按字段5排序,再按字段6排序
    

    4>case when then查询

    --简单case函数
    case sex
      when '1' then '男'
      when '2' then '女’
      else '其他' end
    --case搜索函数
    case when sex = '1' then ''
         when sex = '2' then ''
         else '其他' end  
    

    应用:

    select (case sex
    		  when '1' then '男'
    		  when '2' then '女’
    		  else '其他' end)sex from student where class = 11;
    

    5>where、group by、having

    大部分的where都可以背having代替,不同的是where过滤行,而having过滤分组,用在group by之后。(where在分组前过滤,having在分组后过滤)

    select class,avg(age) as age from student 
    group by class 
    having avg(age)>23 /*要求平均年龄大于23*/
    

    where肯定在group by 之前
    where后的条件表达式里不允许使用聚合函数,而having可以。

    6> 函数

    聚合函数

    avg平均数,同min(age)、max(age)、sum(age)

    select avg(age)  as age from student group by class order by age desc; 
    

    count

    select count(class)from student;
    /*数量 因为使用了92标准,所以null不计入count*/
    count(*) 跟count(1) 的结果一样,返回记录的总行数,都包括对NULL 的统计,
    count(column) 是不包括NULL 的统计。
    

    distinct

    select distinct(class)from student;/*去重复,出现所有不同的内容*/
    select count(distinct(class)) from student;
    

    其它

    LEFT(“123456789”,LEN(“数据库”))/*分两步运算,第一步是运算LEN函数,结果是3。第二步针对123456789这个字符从左边开始连续取三个数*/
    
    select top 100 * from student where no=11;/*显示前100行*/
    select isnull(name,'无') as name,age,class from student;/*isnull之后就无列名了 用as给列重命名*/
    select name,age,class,'the name is' + name as introduce from student;/*用加号形成一个自定义列*/
    

    7>SQL-92 规则

    是数据库的一个标准。以下代码 写在存储过程前面,表示遵从SQL-92 规则。
    SQL-92 标准要求在对空值进行等于 (=) 或不等于 (<) 比较时取值为 FALSE。

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    

    SET ANSI_NULLS ON
    即使 column_name 中包含空值,使用 WHERE column_name = NULL 的 SELECT 语句仍返回零行。
    即使 column_name 中包含非空值,使用 WHERE column_name < NULL 的 SELECT 语句仍会返回零行。

    SET QUOTED_IDENTIFIER ON
    为ON:标识符可以由双引号分隔,而文字必须由单引号分隔。
    为OFF:标识符不可加引号。

    8>多层查询 EXISTS

    如果内层查询语句查询到符合条件的记录,就返回一个真值(true),否则,将返回
    一个假值(false)。

    SELECT * FROM employee
    WHERE EXISTS
    (SELECT d_name FROM department WHERE d_id=1003);
    

    同理还有:NOT EXISTS。

    9>关联查询、联结(JOIN)表

    关系数据库设计中表的设计是把信息分解成多个表,一类数据一个表,各表通过某些共同的值相互关联。
    一般情况下我们不建议建立外键这种强关联的关联信息。

    可伸缩(scale)
    能够适应不断增加的工作量而不失败。关系数据库的可伸缩性远远优于非关系数据库。

    注意:

    1. 联结的表越多效率越低。
    2. SQL本身不限制联结表的数目,但DBMS有最大数目限制。
    3. 一般情况下,联结查询比子查询快,实际应用中应该尝试两种方法看哪种快。
    JSON类型说明备注
    JOIN如果表中有至少一个匹配,则返回行INNER已省略。外联结比内联结返回的行数多(还包括没有关联的行)
    LEFT JOIN即使右表中没有匹配,也从左表返回所有的行OUTER已省略
    RIGHT JOIN即使左表中没有匹配,也从右表返回所有的行OUTER已省略
    FULL JOIN只要其中一个表中存在匹配,就返回行OUTER已省略

    luo_persons表:

    id_plast_namefirst_nameaddresscity

    luo_orders表:

    id_oorder_noid_p

    要求输出:谁订购了产品,并且他们订购了什么产品?

    ①联表查询(等值联结,equijoin)

    SELECT
    	a.last_name, a.first_name, b.order_no
    FROM
    	luo_persons a,
    	luo_orders b 
    WHERE
    	a.id_p = b.id_p	
    

    ②join查询(内联结,inner join, 推荐)

    /*(推荐)等值联结明确指定联结类型可转换为inner join

    SELECT
    	last_name,
    	first_name,
    	order_no 
    FROM
    	luo_persons
    	INNER JOIN luo_orders ON luo_persons.id_p = luo_orders.id_p
    

    ③union查询(复合查询、并查询)

    UNION 操作符用于合并两个或多个 SELECT 语句的结果集。

    注意:

    1. UNION 内部的 SELECT 语句必须拥有相同数量的列、表达式或聚集函数。列也必须拥有相似的数据类型(可以不完全相同,但是可以互相转换)。同时,每条 SELECT 语句中的列的顺序必须相同。
    2. 默认地,UNION 操作符选取不同的值。如果允许重复的值,请使用 UNION ALL。
    3. UNION能组合的最大语句数目限制需要查询具体的DBMS文档。
    	select id_p from luo_persons 
    	union 
    	SELECT id_p from luo_orders
    

    某些DBMS中还支持其它类型的UNION:

    1. EXCEPT(或MINUS):检索在第一个表中存在而在第二个表中不存在的行;
    2. INTERSECT:检索两个表中都存在的行。

    4)数据更新

    ①数据插入

    i> insert

    insert into tableName(no,name) values'1','kate');
    --按表中列的顺序,但如果表结构发生了变化那么对应 sql也要改。不推荐
    insert into product values('001','001','N','N');
    

    有自增长主键(id)的插入:
    i>可以把id的值设置为null或者0,这样mysql会自己做处理
    ii>手动指定需要插入的列,不插入这一个字段的数据!

    ii> insert select

    将select结果插入表中,一般用于可重复执行的sql。
    注:
    1.insert select语句中,如果select返回多行,那么会insert多行数据。

    INSERT INTO "public"."vendors"("vend_name", "vend_id") select 'vend_name1', 1 
    WHERE NOT EXISTS (select 1  FROM "public"."vendors" WHERE vend_id = 1);
    

    iii> select into

    1. SELECT INTO 语句从一个表中选取数据,然后把数据插入另一个表中。
    2. SELECT INTO 语句常用于创建表的备份复件或者用于对记录进行存档。
    3. select into 可以从多个表中检索数据,但只能插入到一个表中。

    函数里面,把一个查询出来的值存入临时变量:

    SELECT LastName,FirstName
    INTO _lName,_fName  FROM Persons
    

    也可以存入临时表中:

    SELECT *
    INTO Persons_backup
    FROM Persons
    

    ②数据修改

    update tableName set name = 'Tom' where name='kate';
    update tableName set age = age + 1;
    

    5)数据删除

    删除表中几行:

    DELETE FROM Person WHERE LastName = 'Wilson' 
    

    删除表中所有行,保留表、不释放空间。所删除的每行记录都会进日志,可以回滚。

    DELETE FROM table_name
    

    删除表:删除内容和定义,释放空间

    drop table user;    
    DROP TABLE IF EXISTS "public"."role_relation"; 可重复执行sql
    

    删除表中所有数据,保留表、同时释放空间(速度比delete快,但是无法撤回,日志里面只记录页释放):

    truncate table book;
    

    truncate是DDL语句(Data Definition,数据定义语句),相当于用重新定义一个新表的方法把原表的内容直接丢弃了,所以执行起来很快。delete语句是DML语句(Data Manipulation,数据操作语句),把数据一条一条的删除,所以删除多行数据执行较慢。

    6)其他注意

    ①加中括号

    列名、表名、存储过程名、函数名等都可以按需要加中括号。防止某些关键字在应用中引起歧义。

    select [select] from 表名;
    

    7)数据库授权

    ①授权GRANT

        GRANT <权限>
        ON <对象类型>  <对象名>
        TO <用户>
        [WITH GRANT OPTION]  // 如果指定了WITH GRANT OPTION子句,则获得某种权限的用户还可以把这种权限再授予其他用户,允许用户传递权限,但是不允许循环授权。
    

    举例:

    例1:把查询Student表的权限授给用户U1
    GRANT SELECT
    ON TABLE Student
    TO U1;
    
    例2:把全部操作权限授予用户U2和U3
    GRANT ALL PRIVILEGES
    ON TABLE Student,Course
    TO U2,U3;
    
    例3:把查询权限授予所有用户
    GRANT SELECT
    ON TABLE SC
    TO PUBLIC;
    

    ③权限的收回 REVOKE

    REVOKE <权限>
    ON <对象类型>  <对象名>
    FROM <用户>
    

    举例:

    例6:收回所有用户对表sc的查询权限
    REVOKE SELECT
    ON TABLE SC
    FROM PUBLIC;
    

    ③对用户模式的授权

    由DBA(数据库管理员,Database Administrator,简称DBA)在创建用户时实现。

    CREATE USER <username>
    [WITH] [DBA|RESOURCE|CONNECT]
    

    只有系统的超级用户才有权创建一个新的数据库用户
    新创建的用户有三种权限:DB,|RESOURCE,CONNECT

    ④数据库角色创建及授权

    CREATE ROLE <角色名>
    

    给角色授权:

    GRANT <权限>
    ON <对象类型>  对象名
    TO <角色>
    

    将一个角色授予其他的角色或用户

    GRANT <角色1>
    TO <角色3>
    [WITH ADMIN OPTION]//如果指定了WITH ADMIN OPTION 子句,则获得某种权限的角色或用户还可以把这种权限再授予其他角色
    

    角色权限的收回

    REVOKE <权限>
    ON <对象类型>  <对象名>
    FROM <角色>
    

    ⑤DENY 拒绝账户访问

    在安全系统中创建一项,以拒绝给当前数据库内的安全帐户授予权限并防止安全帐户通过其组或角色成员资格继承权限。

    DENY { ALL | statement [ ,...n ] }
    TO security_account [ ,...n ]
    

    和授权区别:
    不授权是没有权限,但是如果这个用户属于某个角色,这个角色有了权限,那么这个用户可以从角色继承这个权限。如果选择了deny,即使这个用户属于某个具有权限的角色,他也没有权限。

    8)数据类型

    ①uniqueidentifier

    可存储16字节的二进制值,其作用与全局唯一标记符(GUID)一样。GUID是唯一的二进制数:世界上的任何两台计算机都不会生成重复的GUID值。GUID主要用于在用于多个节点,多台计算机的网络中,分配必须具有唯一性的标识符。

    9)函数

    ①OBJECT_ID

    A. 返回指定对象的对象 ID

    USE master;
    GO
    SELECT OBJECT_ID(N'AdventureWorks.Production.WorkOrder') AS 'Object ID';
    GO
    

    B. 验证对象是否存在

    USE AdventureWorks;
    GO
    IF OBJECT_ID (N'dbo.AWBuildVersion', N'U') IS NOT NULL
    DROP TABLE dbo.AWBuildVersion;
    GO
    

    N是显式的将非unicode字符转成unicode字符,它来自 SQL-92 标准中的 National(Unicode)数据类型,用于扩展和标准化,在这里可以不用,写作object_id(PerPersonData)。

    10)SQL中的借书经典案例

    ①问题描述

    本题用到下面三个关系表:
    CARD 借书卡。 CNO 卡号,NAME 姓名,CLASS 班级
    BOOKS 图书。 BNO 书号,BNAME 书名, AUTHOR 作者,PRICE 单价,QUANTITY 库存册数
    BORROW 借书记录。 CNO 借书卡号,BNO 书号,RDATE 还书日期

    备注:限定每人每种书只能借一本;库存册数随借书、还书而改变。

    要求1. 写出建立BORROW表的SQL语句,要求定义主码完整性约束和引用完整性约束。

    CREATE TABLE BORROW(
        CNO int FOREIGN KEY REFERENCES CARD(CNO),
        BNO int FOREIGN KEY REFERENCES BOOKS(BNO),
        RDATE datetime,
        PRIMARY KEY(CNO,BNO)) 
    

    要求2. 找出借书超过5本的读者,输出借书卡号及所借图书册数。

    SELECT CNO,借图书册数=COUNT(*)
    FROM BORROW
    GROUP BY CNO
    HAVING COUNT(*)>5
    

    要求3. 查询借阅了"水浒"一书的读者,输出姓名及班级

    CARD 借书卡。 CNO 卡号,NAME 姓名,CLASS 班级
    BOOKS 图书。 BNO 书号,BNAME 书名, AUTHOR 作者,PRICE 单价,QUANTITY 库存册数
    BORROW 借书记录。 CNO 借书卡号,BNO 书号,RDATE 还书日期

    SELECT * FROM CARD c
    WHERE EXISTS(
        SELECT * FROM BORROW a,BOOKS b 
        WHERE a.BNO=b.BNO
            AND b.BNAME=N'水浒'
            AND a.CNO=c.CNO) 
    

    要求4. 查询过期未还图书,输出借阅者(卡号)、书号及还书日期。

    SELECT * FROM BORROW 
    WHERE RDATE<GETDATE() 
    

    要求5. 查询书名包括"网络"关键词的图书,输出书号、书名、作者。

    SELECT BNO,BNAME,AUTHOR FROM BOOKS
    WHERE BNAME LIKE N'%网络%' 
    

    N’string’ 表示string是个Unicode字符串

    要求6. 查询现有图书中价格最高的图书,输出书名及作者。

    SELECT BNO,BNAME,AUTHOR FROM BOOKS
    WHERE PRICE=(
        SELECT MAX(PRICE) FROM BOOKS) 
    

    要求7. 查询当前借了"计算方法"但没有借"计算方法习题集"的读者,输出其借书卡号,并按卡号降序排序输出。

    SELECT a.CNO
    FROM BORROW a,BOOKS b
    WHERE a.BNO=b.BNO AND b.BNAME=N'计算方法'
        AND NOT EXISTS(
            SELECT * FROM BORROW aa,BOOKS bb
            WHERE aa.BNO=bb.BNO
                AND bb.BNAME=N'计算方法习题集'
                AND aa.CNO=a.CNO)
    ORDER BY a.CNO DESC 
    

    要求8. 将"C01"班同学所借图书的还期都延长一周。

    UPDATE b SET RDATE=DATEADD(Day,7,b.RDATE)
    FROM CARD a,BORROW b
    WHERE a.CNO=b.CNO
        AND a.CLASS=N'C01' 
    
    DATEADD(datepart,number,date)  
    date 参数是合法的日期表达式。number 是您希望添加的间隔数;对于未来的时间,此数是正数,对于过去的时间,此数是负数。
    

    要求9. 从BOOKS表中删除当前无人借阅的图书记录。

    DELETE FROM BOOKS a
    WHERE NOT EXISTS(
        SELECT * FROM BORROW
        WHERE BNO=a.BNO) 
    

    要求11.在BORROW表上建立一个触发器,完成如下功能:如果读者借阅的书名是"数据库技术及应用",就将该读者的借阅记录保存在BORROW_SAVE表中(注ORROW_SAVE表结构同BORROW表)。

    CREATE TRIGGER TR_SAVE ON BORROW
    FOR INSERT,UPDATE
    AS
    IF @@ROWCOUNT>0
    INSERT BORROW_SAVE SELECT i.*
    FROM INSERTED i,BOOKS b
    WHERE i.BNO=b.BNO
        AND b.BNAME=N'数据库技术及应用' 
    

    要求13.查询当前同时借有"计算方法"和"组合数学"两本书的读者,输出其借书卡号,并按卡号升序排序输出。

    SELECT a.CNO
    FROM BORROW a,BOOKS b
    WHERE a.BNO=b.BNO
        AND b.BNAME IN(N'计算方法',N'组合数学')
    GROUP BY a.CNO
    HAVING COUNT(*)=2
    ORDER BY a.CNO DESC
    

    5,索引

    6,关系运算

    1)集合运算符

    并(∪)、差(-)、交(∩)、笛卡尔积(×)

    笛卡尔积(直积):表示为X × Y,第一个对象是X的成员而第二个对象是Y的所有可能有序对的其中一个成员。
    例如,A={a,b}, B={0,1,2},则
    A×B={(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)}
    

    2)专门的关系运算符

    ①选择(限制、σ)

    在关系R中选择满足给定条件的诸元组。

    ②投影(π)

    关系R上的投影是从R中选择出若干属性列组成新的关系。
    这里写图片描述
    投影之后可既改变行,又改变元组的数量。

    ③连接(θ连接、⋈)

    从两个关系的笛卡尔积中选取属性间满足一定条件的元组。(连接由乘积(笛卡尔积)、选择、投影组成)
    分为等值连接(=)、自然连接(要求比较的分量是相同的属性组,并在结果中把重复的属性列去掉)。
    这里写图片描述

    ④除运算(➗)

    RS÷S的意义就是:“在R和S的联系RS中,找出与S中所有的元组有关系的R元组”。

    3)算术比较符

    4)逻辑运算符

    非与或

    7,数据库完整性

    1)实体完整性

    主键唯一且不为空。

    2)参照完整性

    不允许修改外码
    级连操作:当删除或修改被参照表时,同时删除或修改参照表中的不一致元祖。

    3)用户定义的完整性

    4)触发器(Trigger)

    是用户定义在关系表上的一类由事件驱动的特殊过程。一旦定义,任何用户对标的增删改操作均由服务器自动激活相应触发器,在DBMS核心层进行集中的完整性控制。

    8,存储过程(Stored Procedure)

    1)概念

    存储过程是一组为了完成特定功能的SQL 语句集,存储在数据库中,经过第一次编译后再次调用不需要再次编译,用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。

    2)优点

    ①执行效率高

    存储过程因为SQL 语句已经预编译过了,因此运行的速度比较快。

    ②降低了客户机和服务器之间的通信

    存储过程在服务器端运行,减少客户端的压力。
    减少网络流量,客户端调用存储过程只需要传存储过程名和相关参数即可,与传输SQL 语句相比自然数据量少了很多。

    ③方便实施企业规则(提高了可维护性、安全性)

    可以把企业规则的运算程序写成存储过程放入数据库服务器中,由RDBMS管理,既有利于集中控制,又能够方便地进行维护。
    当用户规则发生变化时,只要修改存储过程,无须修改其他应用程序。

    允许模块化程序设计,就是说只需要创建一次过程,以后在程序中就可以调用该过程任意次,类似方法的复用。
    增强了使用的安全性,充分利用系统管理员可以对执行的某一个存储过程进行权限限制,从而能够实现对某些数据访问的限制,避免非授权用户对数据的访问,保证数据的安全。程序员直接调用存储过程,根本不知道表结构是什么,有什么字段,没有直接暴露表名以及字段名给程序员。

    ④安全性高

    可设定只有某些用户才具有对指定存储过程的使用权。

    3)缺点

    调试麻烦(至少没有像开发程序那样容易),可移植性不灵活(因为存储过程是依赖于具体的数据库)。

    4)场景

    当一个事务涉及到多个SQL语句时或者涉及到对多个表的操作时就要考虑用存储过程;
    当在一个事务的完成需要很复杂的商业逻辑时(比如,对多个数据的操作,对多个状态的判断更改等)要考虑;还有就是比较复杂的统计和汇总也要考虑,但是过多的使用存储过程会降低系统的移植性。

    sql尽量放在存储过程中。
    面对大量数据,用orcle比sql server稳定。

    5)代码

    ①创建

    use test1
    set ansi_nulls on
    go
    set quoted_identifier on
    go
    create procedure procedure_student
    	-- add the parameters for the stored procedure here
    	@gradeid int,
    	@gradename varchar(10) --传入的参数
    as
    begin
    	--计算内容
    end
    go
    

    ②执行

    exec dbo.procedure_student 1,'g'
    

    9,数据库恢复技术

    1)事务

    10,并发控制

    为了保证事务的隔离性和一致性,DBMS需要对并发操作进行正确调度。

    1)并发操作带来的数据不一致性

    ①更新丢失

    ②读“脏”数据

    事务T1修改数据,T2读取数据,T1由于某种原因被撤销,则数据修改回原值,但T2读取的数据是之前修改的数据,即脏数据、不正确的数据。

    ③不可重复读

    事务T1读数据后,T2修改了数据,T1无法再现上一次读取的结果。

    ④幻读

    事务T1读数据后,T2新增或者删除了数据,T1无法再现上一次读取的结果。

    2)并发控制技术

    悲观锁:封锁
    乐观锁:版本号、时间戳

    3)封锁分类(悲观锁)

    ①共享锁(S锁、读锁)

    (读取)操作创建的锁。其他用户可以并发读取数据,但任何事物都不能获取数据上的排它锁,直到已释放所有共享锁。
    若事务T对数据对象A加上S锁,则事务T只能读A;其他事务只能再对A加S锁,而不能加X锁,直到T释放A上的S锁。这就保证了其他事务可以读A,但在T释放A上的S锁之前不能对A做任何修改。

    ②排它锁(X锁、写锁,eXclusive lock)

    若事物T对数据对象A加上X锁,则只允许T读取和修改A,其它任何事务都不能再对A加任何类型的锁,直到T释放A上的锁。它防止任何其它事务获取资源上的锁,直到在事务的末尾将资源上的原始锁释放为止。

    ③更新锁(U锁)

    用来预定要对此页施加X锁,它允许其他事务读,但不允许再施加U锁或X锁;当被读取的页将要被更新时,则升级为X锁;U锁一直到事务结束时才能被释放。

    4)封锁问题

    ①活锁

    i>饥饿

    考虑一台打印机分配的例子,当有多个进程需要打印文件时,系统按照短文件优先的策略排序,该策略具有平均等待时间短的优点,似乎非常合理,但当短文件打印任务源源不断时,长文件的打印任务将被无限期地推迟,导致饥饿以至饿死。

    ii>活锁概念

    与饥饿相关的另外一个概念称为活锁,在忙式等待条件下发生的饥饿,称为活锁。

    a)忙式等待:不进入等待状态的等待。
    b)阻塞式等待:进程得不到共享资源时将进入阻塞状态,让出CPU 给其他进程使用。
    c)忙等待和阻塞式等待的相同之处:
    在于进程都不具备继续向前推进的条件,不同之处在于处于忙等待的进程不主动放弃CPU,尽管CPU 可能被剥夺,因而是低效的;而处于阻塞状态的进程主动放弃CPU ,因而是高效的。

    iii>举例

    事务T1请求封锁R,T2请求封锁R,T3请求封锁R……
    T1释放R之后,系统批准了T3的请求,然后是T4……请求,T2可能永远等待下去。(在整个过程中,事务T2 在不断的重复尝试获取锁R)。

    iv>与死锁区别

    活锁的时候,进程是不会阻塞的,这会导致耗尽CPU 资源,这是与死锁最明显的区别。
    处于活锁的实体是在不断的改变状态,所谓的“活”, 而处于死锁的实体表现为等待;活锁有一定几率解开,而死锁是无法解开的。

    v>避免方式

    采用先来先服务策略。

    ②死锁

    i>概念

    是指两个或两个以上的进程在执行过程中,因争夺资源而造成的一种互相等待的现象,若无外力作用,它们都将无法推进下去,此时称系统处于死锁状态或系统产生了死锁。

    ii>举例

    T1请求封锁R1,T2请求封锁R2,然后T1又请求封锁R2,T1一直等待T2释放R2,此时,T2请求封锁R1,T2将一直等待T1释放R1。

    iii>死锁原因

    在数据库中,产生死锁的原因主要是:
    两个或多个事务都已封锁了一些数据对象,然后又都请求其他事务已封锁的数据对象,从而出现死等待。

    产生死锁的四个必要条件:
    (1) 互斥条件:一个资源每次只能被一个进程使用。
    (2) 请求与保持条件:一个进程因请求资源而阻塞时,对已获得的资源保持不放。
    (3) 不可剥夺条件: 进程已获得的资源,在末使用完之前,不能强行剥夺。
    (4) 环路等待条件: 若干进程之间形成一种头尾相接的循环等待资源关系。
    只要系统发生了死锁,这些条件必然成立,而只要上述条件之一不满足,就不会发生死
    锁。

    iv>死锁预防

    预防死锁的发生只需破坏死锁产生的四个必要条件之一即可。

    1. 破坏互斥条件
      如果允许系统资源都能共享使用,则系统不会进入死锁状态。但有些资源根本不能同时访问,如打印机等临界资源只能互斥使用。所以,破坏互斥条件而预防死锁的方法不太可行,而且在有的场合应该保护这种互斥性。
    2. 破坏不剥夺条件
      当一个已保持了某些不可剥夺资源的进程,请求新的资源而得不到满足时,它必须释放已经保持的所有资源,待以后需要时再重新申请。这意味着,一个进程已占有的资源会被暂时释放,或者说是被剥夺了,或从而破坏了不可剥夺条件。
      该策略实现起来比较复杂,释放已获得的资源可能造成前一阶段工作的失效,反复地申请和释放资源会增加系统开销,降低系统吞吐量。这种方法常用于状态易于保存和恢复的资源,如CPU 的寄存器及内存资源,一般不能用于打印机之类的资源。
    3. 破坏请求和保持条件
      釆用预先静态分配方法,即进程在运行前一次申请完它所需要的全部资源,在它的资源未满足前,不把它投入运行。一旦投入运行后,这些资源就一直归它所有,也不再提出其他资源请求,这样就可以保证系统不会发生死锁。
      这种方式实现简单,但缺点也显而易见,系统资源被严重浪费,其中有些资源可能仅在运行初期或运行快结束时才使用,甚至根本不使用。而且还会导致“饥饿”现象,当由于个别资源长期被其他进程占用时,将致使等待该资源的进程迟迟不能开始运行。
    4. 破坏环路等待条件
      为了破坏循环等待条件,可釆用顺序资源分配法。首先给系统中的资源编号,规定每个进程,必须按编号递增的顺序请求资源,同类资源一次申请完。也就是说,只要进程提出申请分配资源Ri,则该进程在以后的资源申请中,只能申请编号大于Ri 的资源。
      这种方法存在的问题是,编号必须相对稳定,这就限制了新类型设备的增加;尽管在为资源编号时已考虑到大多数作业实际使用这些资源的顺序,但也经常会发生作业使甩资源的顺序与系统规定顺序不同的情况,造成资源的浪费;此外,这种按规定次序申请资源的方法,也必然会给用户的编程带来麻烦。

    都不好用,一般采用死锁的诊断和解除。

    v>死锁的诊断和解除

    a)超时法
    如果一个事务等待时间超时,则认为发生死锁。(可能误判)
    b)事务等待图法
    事务等待图是一个有向图,反映了事务的等待情况。如果图中出现回路,就表示出现了死锁。

    处理方案是:选择一个处理代价最小的事务,将其撤销并释放所有锁。
    a) 从死锁进程处剥夺资源
    b) 终止部分或全部进程

    5) 两段锁协议(Two-Phase Locking――2PL)

    两段锁协议规定所有的事务应遵守的规则:
    ① 在对任何数据进行读、写操作之前,首先要申请并获得对该数据的封锁。
    ② 在释放一个封锁之后,事务不再申请和获得其它任何封锁。
    即事务的执行分为两个阶段:
    第一阶段是获得封锁的阶段,称为扩展阶段。
    第二阶段是释放封锁的阶段,称为收缩阶段。

    定理:若所有事务均遵守两段锁协议,则这些事务的所有交叉调度都是可串行化的。
    对于遵守两段协议的事务,其交叉并发操作的执行结果一定是正确的。值得注意的是,上述定理是充分条件,不是必要条件。一个可串行化的并发调度的所有事务并不一定都符合两段锁协议,存在不全是2PL的事务的可串行化的并发调度。
    同时我们必须指出,遵循两段锁协议的事务有可能发生死锁。

    此时事务T1 、T2同时处于扩展阶段,两个事务都坚持请求加锁对方已经占有的数据,导致死锁。
    为此,又有了一次封锁法。一次封锁法要求事务必须一次性将所有要使用的数据全部加锁,否则就不能继续执行。因此,一次封锁法遵守两段锁协议,但两段锁并不要求事务必须一次性将所有要使用的数据全部加锁,这一点与一次性封锁不同,这就是遵守两段锁协议仍可能发生死锁的原因所在。

    11,常见图

    DFD 数据流图(Data Flow Diagram):
    这里写图片描述
    ER图 实体-联系图(Entity-Relationship Diagram)
    这里写图片描述

    12,数据库连接:JDBC与JdbcTemplate

    13,数据库安全

    1)SQL注入

    ①概念

    在SQL 语句在拼接的情况下,用户输入为一部分sql语句。

    ②解决方法

    i> 对特殊字符进行过滤、转义或者使用预编译的sql 语句绑定变量

    SQL执行时,2种方式:
    ①字符串处理(拼接),然后执行SQL
    用户输入的时候,可以通过输入sql语句来进行SQL注入。
    ②传参,执行SQL -->交给SQL引擎**(推荐)**
    用prepareStatement,参数用set 方法进行填装。

    String sql= "insert into userlogin values(?,?)";
    PreparedStatement ps=conn.prepareStatement(sql);
    for(int i=1;i<100;i++){
    ps.setInt(1, i);
    ps.setInt(2, 8888);
    ps.executeUpdate();
    ps.close();
    conn.close();
    

    ii> 当sql 语句运行出错时,不要把数据库返回的错误信息全部显示给用户,以防止泄漏服务器和数据库相关信息

    iii>检查变量的数据类型和格式

    只要是有固定格式的变量,在SQL 语句执行前,应该严格按照固定格式去检查,确保变量是我们预想的格式,这样很大程度上可以避免SQL 注入攻击。
    例如:对于where id={$id}这种形式,数据库里所有的id 都是数字,那么就应该在SQL 被执行前,检查确保变量id 是int 类型。

    iv>所有的SQL 语句都封装在存储过程中

    所有的SQL 语句都封装在存储过程中,这样不但可以避免SQL 注入,还能提高一些性能。

    14,分布式数据库

    1)概念

    分布式数据库是一个物理上分散的而逻辑上集中的数据集。
    它有三大特点: 数据分布性 逻辑关联性 站点自治性

    2)五个基本原则

    ①资源的重复性
    指分布式系统中硬件,软件以及数据的冗余配置。
    ②物理上的分布性
    从硬件,软件以及数据上看都是相互独立地分布。
    ③高层操作系统(或者分布式操作系统)
    高层操作系统负责对分布性的资源进行统一的控制,它使一个简单的硬件堆积转变为一个统一协调的工作系统。
    ④系统的透明性
    透明性是分布式系统的灵魂,实现不同层次的透明性是分布式系统必须解决的关键问题之一。
    ⑤协作的自治性
    每一节点都是一个完整的处理系统,同时又是合作的。 简而言之:分布式系统是一个多节点的,处理或数据分布的,在统一下提高综合处理能力的协作体。

    3)待解决问题

    不完整系统状态信息
    时间延迟
    通信的代价
    负载均衡

    4)分类(从控制方式角度)

    ①紧耦合式DDBMS

    全局控制信息放在一个称为中心站点的站点上。所有的全局访问都必须通过中心站点来确定远程数据片的位置。
    优点:容易实现数据的一致性和完整性。
    缺点:易产生访问瓶颈,系统效率不高,可靠性较差。

    ②联邦式DDBMS

    每个站点都包含全局控制信息的一个副本,都可以接受全局访问。任何对远程数据的请求,都可以通过广播方式传播到其他节点。
    优点:具有较好的可靠性和可用性,并行性好,更容易适应旧有的系统集成和异构分布式数据库系统的建立。
    缺点:保持数据的一致性很困难,实现难度大。

    ③组合式DDBMS

    是上述方案的折衷,它把站点分为两类,一类具有全局控制信息,称为主节点,可以接受全局任务,另一类没有全局信息,只能为主节点提供数据服务。
    优点:灵活性较好,易于实现层次控制结构。
    缺点:设计复杂。

    5)分布透明性

    即在分布式数据库系统中用户不必关心数据的分布情况。分为三个层次:

    ①分片透明性

    它是分布式数据库系统的最高透明性层次,它向用户完全屏蔽了DDB的分片信息。这样的透明性保持了高水平的数据独立性。

    ②位置透明性

    用户的应用程序不需要关心数据分片的具体存储站点,当数据库的数据片的存储站点发生改变时,只需改变对应的GRS/NRS映射就可以保持全局表示模式不发生改变

    ③数据模型透明性

    它向用户屏蔽的只是本站点的具体数据库存储及其管理情况。 在异构的情况下,这种透明性避免了用户对不同数据模型的转换的实现。
    本地透明性是3种透明方式中最低的。

    6)数据分割方法

    ①水平分割

    把全局关系的元组分割成一些子集,这些子集被称为数据分片或段(Fragment)。
    水平分割可以通过关系运算“选择”来定义。

    水平分片是对全局关系执行“选择”操作,把具有相同性质的元组进行分组,构成若干个不相交的子集.水平分片的方法可归为初级分片和导出分片两类。

    ②垂直分割

    把全局关系按照属性组(纵向)分隔成一些数据分片或段。
    垂直分割可以通过关系运算“投影”来定义。

    ③混合分割

    可把水平分割和垂直分割这两种方法结合起来使用,产生混合式数据分片。

    ④数据分片应遵循的原则

    若R={R1,R2,…,Rn}满足:
    1)完整性(completeness)条件:
    如果分片 a∈R,则必有a∈Ri,i=l,2,…,n
    2)可重构(reconstructed)条件:
    R=∪ Ri,(水平分片)或R=∞Ri,(垂直分片)
    3)不相交(disjoint)条件:
    Ri∩ Rj=φ,i≠j,I,j:=1,2,…,,n(水平 分片)
    Ri∩Rj=主键属性,I,j=1,2,…,n(垂直分片)

    7)分布式数据库和集中式区别

    分布式(distributed)是指在多台不同的服务器中部署不同的服务模块,通过远程调用协同工作,对外提供服务。
    集群(cluster)是指在多台不同的服务器中部署相同应用或服务模块,构成一个集群,通过负载均衡设备对外提供服务。

    15,数据库优化

    1)优化SQL 语句

    ①explain

    通过explain(查询优化神器)用来查看SQL 语句的执行效果,可以帮助选择更好的索引和优化查询语句,写出更好的优化语句。
    通常我们可以对比较复杂的尤其是涉及到多表的SELECT 语句,把关键字EXPLAIN 加到前面,查看执行计划。例如:explain select * from news;

    explain语法:

    explain select … from … [where ...] 
    

    ② 用具体的字段列表代替“*

    任何地方都不要使用select * from t ,不要返回用不到的任何字段。

    ③ 不在索引列做运算或者使用函数

    ④ 查询尽可能使用limit 减少返回的行数,减少数据传输时间和带宽浪费。

    2)优化表的数据类型

    ① 使用procedure analyse()函数对表进行分析

    该函数可以对表中列的数据类型提出优化建议。能小就用小。表数据类型第一个原则是:使用能正确的表示和存储数据的最短类型。这样可以减少对磁盘空间、内存、cpu 缓存的使用。
    使用方法:select * from 表名procedure analyse();

    ② 对表进行拆分

    通过拆分表可以提高表的访问效率。有2 种拆分方法:
    1.垂直拆分
    把主键和一些列放在一个表中,然后把主键和另外的列放在另一个表中。如果一个表中某些列常用,而另外一些不常用,则可以采用垂直拆分。
    2.水平拆分
    根据一列或者多列数据的值把数据行放到二个独立的表中。

    ③ 使用中间表来提高查询速度

    创建中间表,表结构和源表结构完全相同,转移要统计的数据到中间表,然后在中间表上进行统计,得出想要的结果。

    3)硬件优化

    ①CPU 的优化

    选择多核和主频高的CPU。

    ②内存的优化

    使用更大的内存。将尽量多的内存分配给MYSQL 做缓存。

    ③磁盘I/O 的优化

    i>使用磁盘阵列

    RAID 0 没有数据冗余,没有数据校验的磁盘陈列。实现RAID 0至少需要两块以上的硬盘,它将两块以上的硬盘合并成一块,数据连续地分割在每块盘上。
    RAID1 是将一个两块硬盘所构成RAID 磁盘阵列,其容量仅等于一块硬盘的容量,因为另一块只是当作数据“镜像”。
    使用RAID-0+1 磁盘阵列。RAID 0+1 是RAID 0 和RAID 1 的组合形式。它在提供与RAID 1 一样的数据安全保障的同时,也提供了与RAID 0 近似的存储性能。

    ii>调整磁盘调度算法

    选择合适的磁盘调度算法,可以减少磁盘的寻道时间。

    4)MySQL 自身的优化

    对MySQL 自身的优化主要是对其配置文件my.cnf 中的各项参数进行优化调整。如指定MySQL 查询缓冲区的大小,指定MySQL 允许的最大连接进程数等。

    5)应用优化

    ①使用数据库连接池

    ②使用查询缓存

    它的作用是存储select 查询的文本及其相应结果。如果随后收到一个相同的查询,服务器会从查询缓存中直接得到查询结果。查询缓存适用的对象是更新不频繁的表,当表中数据更改后,查询缓存中的相关条目就会被清空。

    6)大访问量的优化

    ①使用优化查询的方法

    (见上面)

    ②主从复制,读写分离

    i>主从复制(master,slave):

    通过配置两台(或多台)数据库的主从关系,可以将一台数据库服务器的数据更新同步到另一台服务器上。网站可以利用数据库的这一功能,实现数据库的读写分离,从而改善数据库的负载压力。一个系统的读操作远远多于写操作,因此写操作发向master,读操作发向slaves 进行操作(简单的轮循算法来决定使用哪个slave)。
    利用数据库的读写分离,Web 服务器在写数据的时候,访问主数据库(Master),主数据库通过主从复制机制将数据更新同步到从数据库(Slave),这样当Web 服务器读数据的时候,就可以通过从数据库获得数据。这一方案使得在大量读操作的Web 应用可以轻松地读取数据,而主数据库也只会承受少量的写入操作,还可以实现数据热备份,可谓是一举两得的方案。
    这里写图片描述

    负载均衡(Load Balance,简称LB)

    7)数据库分表、分区、分库

    分表见上面描述。
    分区就是把一张表的数据分成多个区块,这些区块可以在一个磁盘上,也可以在不同的磁盘上,分区后,表面上还是一张表,但数据散列在多个位置,这样一来,多块硬盘同时处理不同的请求,从而提高磁盘I/O 读写性能,实现比较简单。包括水平分区和垂直分区。
    分库是根据业务不同把相关的表切分到不同的数据库中,比如web、bbs、blog 等库。

    17,应用

    1)服务器与服务器之间传输文件夹下的文件,一个文件夹下有10 个文件,另一个文件夹下有100 个文件,两个文件夹大小相等,问,哪个传输更快?

    10 个文件更快。
    1)建立连接数更少,建立连接的开销比传输文件的开销大。
    2)文件写入磁盘,要计算文件的起始位置,文件数目少的话,这个开销就小了

    展开全文
  • Qt 判断数据库db中 table是否已经创建

    千次阅读 2016-08-22 17:44:18
    怎么判断数据库表格是否已经创建 Qt SQLITE 数据库

    这两天使用Qt SQLITE 数据库,遇到怎么判断数据库表格是否已经创建的问题,找了一下资料,初步解决 ,
    希望网友能提供更优秀的解决方案。

    QSqlDatabase db = QSqlDatabase::database();
    QSqlQueryq(db);
    
    if(db.tables().contains("Tab_medicineInfoComplemented")){
           return QSqlError();
    }

    if判断中就是判断数据库存不存在的

    另外一个问题就是 Qt数据库判断本地是否已经创建/存在数据库,解决方法如下:
    http://blog.csdn.net/jh1513/article/details/52154887

    这篇文章很不错
    http://blog.csdn.net/cctv_hu/article/details/6216386

    展开全文
  • 判断数据库是否有重复行的问题

    千次阅读 2016-09-06 11:33:29
    现在往数据库插入一条数据,但是要确保这条数据不能重复,那只能先在数据库查询一下是否有这条数据, 可以用 Datatable dt=db.DoQueryEx(str); if(dt.rows.count>0) { } else { 插入到数据库中 } 这里...
  • java判断mysql数据库中存在某一张表

    千次阅读 2018-10-22 14:20:13
    ResultSet DatabaseMetaData.getTables(String catalog, String schemaPattern, String tableNamePattern, String ...catalog - 数据库目录名称,可设为null,(具体JDBC驱动的实现不一样在MySQL中指数据库名)...
  • 查看数据库是否存在(支持模糊查询) SELECT * FROM information_schema.SCHEMATA WHERE SCHEMA_NAME = '...判断数据表是否存在(支持模糊查询) -- 精确查询 SELECT * FROM information_schema.TABLES W.
  • 然后在java中判断查询结果是否大于 0 来判断数据库表中是否有相关的记录,虽然这样做没什么问题,但是符合条件的记录数量较多时会有性能问题,所有可以将查询的sql改成下面的方式: SELECT 1 FROM table WHERE a = 1...
  • mysql 判断数据库某一张表是否存在

    千次阅读 2019-02-26 10:36:07
    select TABLE_NAME from INFORMATION_SCHEMA.TABLES whereTABLE_SCHEMA=‘dbname’ and TABLE_NAME=‘tablename’ ; 如果表不存在就建立这个表,那么可以直接用 create table if not exists tablename...
  • 最近在开发一个同步数据的统计任务的时候遇到一个问题:要在Java代码中判断数据库中某张表是否存在,查资料后,总结了以下两种方法:1、使用JdbcTemplate beanpublic boolean validateTableNameExist(String ...
  • QSqlQuery query(db); QString selectsql; selectsql ="select * from sys.tables where name='car_bayonet_info'" ;//是否已经存在表car_bayonet_info query.exec(selectsql); if(!query.next()) {
  • 问题描述: 一般而言,我们创建创建数据库,可以在mysql或者navicat中自定义,...这个时候,项目和数据库之间的操作就没有办法实现,因为安装好的mysql中并没有数据库,则在建立连接时无法连接。 问题分析: 我...
  • 判断数据库中的表是否存在

    千次阅读 2017-12-22 09:53:55
    1.sql语句判断数据库表是否存在: sql:select * from user_all_tables where table_name='tableName' 如果结果为空则表示不存在,如何结果不为空则表示存在; 2.java如何判断数据库表是否存在 方法一:可以利用...
  • 第一次运行APP时,按钮的点击事件会创建SQLite数据库。此后退出系统,然后再重新运行时,(1)如何判断 数据库已经存在,就不创建数据库。(2)如何判断数据库不存在,就创建数据库
  • thinkphp5.x 判断数据库表是否存在

    千次阅读 2019-01-19 10:29:08
    thinkphp5系统有些地方要修改表,但是修改之前...//这是要查询的表的原始名称【没有加配置前缀】,$data['table_name']。 $tableName=config('database.prefix').$data['table_name']; $isTable=db()-&gt;query...
  • 很多时候在开发的过程中,由于业务的需要,我们可能需要在手机本地建立数据库,进行本地保存一些数据。那么问题来了,很多时候我们并希望每次用户使用的应用的时候都去创立数据库的表... * 判断数据库中某张表是否存在
  • Mysql 判断数据库和表是否存在

    千次阅读 2018-11-09 10:23:51
    1、判断mysql 中表是否存在 SELECT table_name FROM information_schema.TABLES ...2、判断mysql 中,数据库是否存在 SELECT * FROM information_schema.SCHEMATA where SCHEMA_NAME='skt100_200';  ...
  • mysql判断数据库或表是否存在

    千次阅读 2018-09-29 11:04:09
    (1) 判断数据库存在, 则删除: drop database if exists db_name; (2) 判断数据表存在, 则删除: drop table if exists table_name; 注: db_name, table_name可用``(1键旁边那个键)号引起来, 也可不引起来. (1) ...
  • 转载地址... 数据库是否存在 if exists(select top 1 * from sys.databases where name='database_name') begin print('数据库已存在!') end else begin create database database...
  • 达梦数据库查看指定模式下的表是否存在以及达梦数据库的常用语句!
  • SqlServer数据库DROP TABLE IF EXISTS语句

    万次阅读 2018-08-17 11:11:46
    近日碰到SqlServer数据库版本不一致,执行建表脚本时DROP TABLE IF EXISTS语法的小问题,自己做个记录便于后续查看。 在SqlServer2016版本及更高版本,建表前判断表是否存在,可以使用: DROP TABLE IF EXISTS ...
  • 现在有这么个需求: 就是我有一个爬虫脚本,我想把爬取到的数据存入到mysql数据库里去(这个...增量:就是每次插入数据的时候,根据里面的一个字段(时间time)来判断即将插入的数据是不是在数据库里,如果没有就插...
  • elastic-job没有自动创建表JOB_EXECUTION_LOG,JOB_STATUS_TRACE_LOG
  • Shell命令行判断数据库中的表是否存在 Shell命令行判断数据库中的表是否存在,调用的sql语句为: select '1' from table_name limit 1; 如果表存在,数据返回1,不存在则返回错误信息。...
  • tp5 判断数据库表是否存在

    千次阅读 2019-07-30 15:23:36
    tp5 判断数据库表是否存在 use think\Db; //验证表是否存在 //这是要查询的表的原始名称(全名) $tableName = config('database.prefix').$data['table_name']; $isTable = db()->query('SHOW TABLES LIKE '....
  • MySQL数据库命令大全

    千次阅读 2019-11-28 08:54:50
    MySQL数据库命令大全 运维常用三种数据库命令类型: 数据库定义语言DDL: Data Defination Lanuage 数据库操作语言DML: Data Manupulate Language 数据库控制语言DCL: Data Control Language 1.数据库定义语言...
  • Android 判断数据库中是否存在某个表

    千次阅读 2016-03-08 17:50:27
    "select count(*) as c from sqlite_master where type ='table' and name ='"  + tabName.trim() +  "' " ;   cursor = db.rawQuery(sql,  null );    if  (cursor.moveToNext()) {...
  • 这几天使用Qt 数据库,摸索着怎样判断自己的数据库是否已经存在,和大家分享自己的心得,希望各路大神指点不足或者提供更好的解决方案bool isTableExist = query.exec(QString("select count(*) from sqlite_master...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 251,425
精华内容 100,570
关键字:

判断数据库没有table