精华内容
下载资源
问答
  • 面试必备之数据库知识点总结

    万次阅读 多人点赞 2019-04-12 21:03:06
    日志记录了事务对数据库所做的更新,如果某个事务在执行过程中发生错误,就可以根据日志,撤销事务对数据库已做的更新,使数据库退回到执行事务前的初始状态。 数据库管理系统采用锁机制来实现事务的隔离性 。当多...

    原文地址:https://github.com/DmrfCoder/interview/blob/master/DataBase/DataBase.md

    事物

    事务的概念

    数据库事务通常包含了一个序列的对数据库的读/写操作(一个单元的一系列SQL语句的集合)。包含有以下两个目的:

    1. 为数据库操作序列提供了一个从失败中恢复到正常状态的方法,同时提供了数据库即使在异常状态下仍能保持一致性的方法。(系统错误恢复)
    2. 当多个应用程序在并发访问数据库时,可以在这些应用程序之间提供一个隔离方法,以防止彼此的操作互相干扰。(并发数据库访问)

    当事务被提交给了数据库管理系统(DataBaseManagerService,DBMS),则DBMS需要确保该事务中的所有操作都成功完成且其结果被永久保存在数据库中,如果事务中有的操作没有成功完成,则事务中的所有操作都需要回滚,回到事务执行前的状态;同时,该事务对数据库或者其他事务的执行无影响,所有的事务都好像在独立的运行。

    例子

    某人要在商店使用电子货币购买100元的东西,当中至少包括两个操作:

    1. 该人账户减少100元
    2. 商店账户增加100元

    支持事务的数据库管理系统(transactional DBMS)就是要确保以上两个操作(整个“事务”)都能完成,或一起取消;否则就会出现100元平白消失或出现的情况

    事务的特性(ACID)

    并非任意的对数据库的操作序列都是数据库事务。数据库事务拥有以下四个特性,习惯上被称之为ACID特性

    • A, atomacity 原子性 事务必须是原子工作单元;对于其数据修改,要么全都执行,要么全都不执行。通常,与某个事务关联的操作具有共同的目标,并且是相互依赖的。如果系统只执行这些操作的一个子集,则可能会破坏事务的总体目标。原子性消除了系统处理操作子集的可能性。

    • C, consistency 一致性

      事务将数据库从一种一致状态转变为下一种一致状态。也就是说,事务在完成时,必须使所有的数据都保持一致状态(各种 constraint 不被破坏)。

    • I, isolation 隔离性 由并发事务所作的修改必须与任何其它并发事务所作的修改隔离。事务查看数据时数据所处的状态,要么是另一并发事务修改它之前的状态,要么是另一事务修改它之后的状态,事务不会查看中间状态的数据。换句话说,一个事务的影响在该事务提交前对其他事务都不可见。

    • D, durability 持久性

      事务完成之后,它对于系统的影响是永久性的。该修改即使出现致命的系统故障也将一直保持。

    原子性和一致性的区别:

    转账:张三给李四转账100元。那数据库假设需要 张三扣100,李四加100,记录一条流水。
    如果流水没记录成功,那整体回滚,张三也没转账成功,李四也没多钱。这就是原子性的体现。

    而张三必须扣100,李四必须加100,这个就是一致性了,如果因为某些逻辑原因,导致张三扣了100,流水记录100转账,而李四只加了60。然后这3条操作都成功了,那原子性就符合了,但是一致性就不符合了

    并发控制

    并发控制的主要方法是封锁(Locking)。

    读写异常

    数据库是要被广大客户所共享访问的,那么在数据库操作过程中很可能出现以下几种不确定情况:

    • 丢失修改:两个事务T1,T2读入同一数据并修改,T2提交的结果被T1破坏了,导致T1的修改丢失。(订票系统)
    • 不可重复读:事务T1读取数据后,事务T2执行更新操作,使T1无法再次读取结果。
    • 读脏数据:事务T1修改某个数据并写回磁盘,事务T2读取同一数据,但T1由于某种原因撤销了,这时T1修改过的数据恢复原来的值,T2读取的数据就与数据库中的数据不一致。
    • 幻读:事务在操作过程中进行两次查询,第二次查询结果包含了第一次查询中未出现的数据(这里并不要求两次查询SQL语句相同)这是因为在两次查询过程中有另外一个事务插入数据造成的

    封锁

    封锁是实现并发控制的一个非常重要的技术,所谓 封锁就是事务T在对某个数据对象例如表、记录等操作之前,先向系统发出请求,对其加锁。数据库系统提供两种锁:

    • 排他锁(写锁):若事务T对数据对象A加写锁,则只允许T读取和修改A,其他事务都不能再对A加任何类型的锁,直到T释放A上的锁为止
    • 共享锁(读锁):若事务T对数据对象A加读锁,则只允许T可以读取但不能修改A,其他事务只能再对A加读锁,而不能加写锁,直到T释放A上的读锁为止。

    事务的隔离级别

    为了避免上面出现几种情况在标准SQL规范中定义了4个事务隔离级别,不同隔离级别对事务处理不同 。

    未提交读(Read Uncommitted)

    未提交读(READ UNCOMMITTED)是最低的隔离级别。允许脏读(dirty reads),但不允许更新丢失,事务可以看到其他事务“尚未提交”的修改

    提交读(Read Committed)

    允许不可重复读取,但不允许脏读取。这可以通过“瞬间共享读锁”和“排他写锁”实现。读取数据的事务允许其他事务继续访问该行数据,但是未提交的写事务将会禁止其他事务访问该行

    可重复读(Repeatable Read)

    禁止不可重复读取和脏读取,但是有时可能出现幻读数据。这可以通过“共享读锁”和“排他写锁”实现。读取数据的事务将会禁止写事务(但允许读事务),写事务则禁止任何其他事务。

    可序列化(Serializable)

    最高的隔离级别,它要求事务序列化执行,事务只能一个接着一个地执行,不能并发执行。仅仅通过“行级锁”是无法实现事务序列化的,必须通过其他机制保证新插入的数据不会被刚执行查询操作的事务访问到。

    隔离级别越高,越能保证数据的完整性和一致性,但是对并发性能的影响也越大。

    封锁协议

    使用读锁和写锁时,需要约定一定的规则。比如:何时申请、持续时间、何时释放等。这些规则被称为 封锁协议。针对不同的事务隔离级别,有不同的封锁协议。

    • 一级封锁协议:事务T在修改数据R之前必须先对其加写锁,直到事务结束才释放。一级封锁协议防止了丢失修改,但不能保证可重复读和不读脏数据。
    • 二级封锁协议:在一级封锁协议的基础上增加事务T在读数据R前必须加读锁,读完就可以释放。二级封锁协议进一步防止读脏数据,但不能保证可重复读。
    • 三级封锁协议:一级封锁协议的基础上增加事务T在读数据R前必须加读锁,直到事务结束才释放。三阶封锁协议除了防止丢失修改和读脏数据外,进一步防止了不可重复读。
    • 四级封锁协议:四级封锁协议是对三级封锁协议的增强,其实现机制也最为简单,直接对事务中所读取或者更改的数据所在的表加表锁,也就是说,其他事务不能 读写 该表中的任何数据。

    并行调度

    调度是一个或多个事务的重要操作按时间排序的一个序列。如果一个调度的动作首先是一个事务的所有动作,然后是另一个事务的所有动作,以此类推,而没有动作的混合,那么我们说这一调度是串行的。

    事务的正确性原则告诉我们,每个串行调度都将保持数据库状态的一致性。 通常,不管数据库初态怎样,一个调度对数据库状态的影响都和某个串行调度相同,我们就说这个调度是可串行化的。

    可串行性是并行调度正确性的唯一准则,两段锁(简称2PL)协议是为保证并行调度可串行性而提供的封锁协议。两段锁协议规定:在对任何数据进行读、写操作之前,事务道首先要获得对该数据的封锁,而且在释放一个封锁之生,事务不再获得任何其他封锁。

    所谓“两段”锁的含义是:事务分为两个阶段,第一阶段是获得封锁,也称为扩展阶段,第二阶段是释放封锁,也称为收缩阶段

    使用事务

    在MySQL中使用START TRANSACTIONBEGIN开启事务,提交事务使用COMMITROLLBACK用来放弃事务。MySQL默认设置了事务的自动提交,即一条SQL语句就是一个事务。

    总结

    事务的(ACID)特性是由关系数据库管理系统(RDBMS,数据库系统)来实现的。数据库管理系统采用日志来保证事务的原子性、一致性和持久性。日志记录了事务对数据库所做的更新,如果某个事务在执行过程中发生错误,就可以根据日志,撤销事务对数据库已做的更新,使数据库退回到执行事务前的初始状态。

    数据库管理系统采用锁机制来实现事务的隔离性。当多个事务同时更新数据库中相同的数据时,只允许持有锁的事务能更新该数据,其他事务必须等待,直到前一个事务释放了锁,其他事务才有机会更新该数据。

    索引

    基本概念

    在数据库中,索引的含义与日常意义上的“索引”一词并无多大区别(想想小时候查字典),它是用于提高数据库表数据访问速度的数据库对象

    • 索引可以避免全表扫描。多数查询可以仅扫描少量索引页及数据页,而不是遍历所有数据页。
    • 对于非聚集索引,有些查询甚至可以不访问数据页。
    • 聚集索引可以避免数据插入操作集中于表的最后一个数据页。
    • 一些情况下,索引还可用于避免排序操作。

    索引的存储

    一条索引记录中包含的基本信息包括:键值(即你定义索引时指定的所有字段的值) 逻辑指针(指向数据页或者另一索引页)。

    index_1

    当你为一张空表创建索引时,数据库系统将为你分配一个索引页,该索引页在你插入数据前一直是空的。此页此时既是根结点,也是叶结点。每当你往表中插入一行数据,数据库系统即向此根结点中插入一行索引记录。当根结点满时,数据库系统大抵按以下步骤进行分裂:

    • 创建两个儿子结点
    • 将原根结点中的数据近似地拆成两半,分别写入新的两个儿子结点
    • 根结点中加上指向两个儿子结点的指针

    通常状况下,由于索引记录仅包含索引字段值(以及4-9字节的指针),索引实体比真实的数据行要小许多,索引页相较数据页来说要密集许多。一个索引页可以存储数量更多的索引记录,这意味着在索引中查找时在I/O上占很大的优势,理解这一点有助于从本质上了解使用索引的优势。

    索引的分类

    汉语字典的正文本身就是一个聚集索引。比如,我们要查“安”字,就会很自然地翻开字典的前几页,因为“安”的拼音是“an”,而按照拼音排序汉字的字典是以英文字母“a”开头并以“z”结尾的,那么“安”字就自然地排在字典的前部。如果您翻完了所有以“a”开头的部分仍然找不到这个字,那么就说明您的字典中没有这个字;同样的,如果查“张”字,那您也会将您的字典翻到最后部分,因为“张”的拼音是“zhang”。也就是说,字典的正文部分本身就是一个目录,您不需要再去查其他目录来找到您需要找的内容。正文内容本身就是一种按照一定规则排列的目录称为“聚集索引”。

    如果您认识某个字,您可以快速地从自动中查到这个字。但您也可能会遇到您不认识的字,不知道它的发音,这时候,您就不能按照刚才的方法找到您要查的字,而需要去根据“偏旁部首”查到您要找的字,然后根据这个字后的页码直接翻到某页来找到您要找的字。但您结合“部首目录”和“检字表”而查到的字的排序并不是真正的正文的排序方法,比如您查“张”字,我们可以看到在查部首之后的检字表中“张”的页码是672页,检字表中“张”的上面是“驰”字,但页码却是63页,“张”的下面是“弩”字,页面是390页。很显然,这些字并不是真正的分别位于“张”字的上下方,现在您看到的连续的“驰、张、弩”三字实际上就是他们在非聚集索引中的排序,是字典正文中的字在非聚集索引中的映射。我们可以通过这种方式来找到您所需要的字,但它需要两个过程,先找到目录中的结果,然后再翻到您所需要的页码。

    聚集索引

    表数据按照索引的顺序来存储的。对于聚集索引,叶子结点即存储了真实的数据行,不再有另外单独的数据页。在聚集索引中,叶结点也即数据结点,所有数据行的存储顺序与索引的存储顺序一致。

    在一张表上只能创建一个聚集索引,因为真实数据的物理顺序只可能是一种。如果一张表没有聚集索引,那么它被称为“堆集”(Heap)。这样的表中的数据行没有特定的顺序,所有的新行将被添加的表的末尾位置。

    index_2

    非聚集索引

    表数据存储顺序与索引顺序无关。对于非聚集索引,叶结点包含索引字段值及指向数据页数据行的逻辑指针,该层紧邻数据页,其行数量与数据表行数据量一致。

    非聚集索引与聚集索引相比:

    • 叶子结点并非数据结点
    • 叶子结点为每一真正的数据行存储一个“键-指针”对
    • 叶子结点中还存储了一个指针偏移量,根据页指针及指针偏移量可以定位到具体的数据行。
    • 类似的,在除叶结点外的其它索引结点,存储的也是类似的内容,只不过它是指向下一级的索引页的。

    index_3

    索引失效

    索引并不是时时都会生效的,比如以下几种情况,将导致索引失效:

    1. 如果条件中有or,即使其中有条件带索引也不会使用。 >要想使用or,又想让索引生效,只能将or条件中的每个列都加上索引
    2. 对于多列索引,不是使用的第一部分,则不会使用索引。
    3. like查询是以%开头。
    4. 如果列类型是字符串,那一定要在条件中将数据使用引号引用起来,否则不使用索引。
    5. 如果 mysql 估计使用全表扫描要比使用索引快,则不使用索引。例如,使用<>not innot exist,对于这三种情况大多数情况下认为结果集很大,MySQL就有可能不使用索引。

    索引设计的原则

    • 表的某个字段值得离散度越高,该字段越适合选作索引的关键字。主键字段以及唯一性约束字段适合选作索引的关键字,原因就是这些字段的值非常离散。
    • 占用存储空间少的字段更适合选作索引的关键字。例如,与字符串相比,整数字段占用的存储空间较少,因此,较为适合选作索引关键字。
    • 存储空间固定的字段更适合选作索引的关键字。与 text 类型的字段相比, char 类型的字段较为适合选作索引关键字。
    • Where 子句中经常使用的字段应该创建索引,分组字段或者排序字段应该创建索引,两个表的连接字段应该创建索引
    • 更新频繁的字段不适合创建索引,不会出现在 where 子句中的字段不应该创建索引
    • 最左前缀原则。
    • 尽量使用前缀索引。

    总结

    聚集索引是一种稀疏索引,数据页上一级的索引页存储的是页指针,而不是行指针。而对于非聚集索引,则是密集索引,在数据页的上一级索引页它为每一个数据行存储一条索引记录。

    与非聚集索引相比,聚集索引有着更快检索速度、更快的字段排序。

    在MySQL中InnoDB按照主键进行聚集,如果没有定义主键,InnoDB会试着使用唯一的非空索引来代替。如果没有这种索引,InnoDB就会定义隐藏的主键然后在上面进行聚集,但是主键和聚集索引是不等价的。在InnoDBNormal索引即非聚集索引。

    SQL语句

    SQL语言由一些简单句子构成基本的语法,所有的SQL语句均有自己的格式,典型的SQL语句(查询语句)结构如图所示:

    23

    QL语法的基础是子句(clause),子句中会包括一些关键词(keyword)。每条SQL语句均由一个关键词开始,该关键词描述这条语句要产生的动作。SQL中常用的关键词及其功能如表所示:

    image-20190318202147011

    image-20190318202203656

    image-20190318202221064

    CRUD

    CREATE TABLE

    CREATE TABLE `user` (
      `id` INT AUTO_INCREMENT,
      `name` VARCHAR (20),
      PRIMARY KEY (`id`)
    );
    

    VARCHAR记得指定长度。

    UPDATE

    UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值
    

    INSERT

    INSERT INTO 表名称 VALUES (1,2,....)
    
    INSERT INTO table_name (1,2,...) VALUES (1,2,....)
    

    DELETE

    DELETE FROM 表名称 WHERE 列名称 =

    修改表结构

    ALTER TABLE table_name add column_name datatype
    
    ALTER TABLE table_name drop COLUMN column_name
    
    ALTER TABLE table_name modify COLUMN column_name datatype
    

    权限分配

    grant select,insert on userdb.userinfo to'zhangsan'@'localhost'
    

    模糊查询

    %:表示任意0个或多个字符。可匹配任意类型和长度的字符,有些情况下若是中文,请使用两个百分号(%%)表示。

    select * from test where text like '%1%';
    

    _ : 表示任意单个字符。匹配单个任意字符,它常用来限制表达式的字符长度语句。

    --倒数第三个字符为 1 ,且最小长度为 5
    select * from test where text like '__%1__';
    

    连接

    在数据库原理中,关系运算包含 选择投影连接 这三种运算。相应的在SQL语句中也有表现,其中Where子句作为选择运算,Select子句作为投影运算,From子句作为连接运算。

    连接运算是从两个关系的笛卡尔积中选择属性间满足一定条件的元组,在连接中最常用的是等值连接和自然连接。

    • 等值连接:关系R、S,取两者笛卡尔积中属性值相等的元组,不要求属性相同。比如 R.A=S.B
    • 自然连接(内连接):是一种特殊的等值连接,它要求比较的属性列必须是相同的属性组,并且把结果中重复属性去掉

    关系R:

    A B C
    a1 b1 5
    a1 b2 6
    a2 b3 8
    a2 b4 12

    关系S:

    B E
    b1 3
    b2 7
    b3 10
    b3 2
    b5 2

    自然连接 R & S的结果为:

    A B C E
    a1 b1 5 3
    a1 b2 6 7
    a2 b3 8 10
    a2 b3 8 2

    两个关系在做自然连接时,选择两个关系在公共属性上值相等的元组构成新的关系。此时关系R中某些元组有可能在S中不存在公共属性上相等的元组,从而造成R中这些元组在操作时被舍弃,同样,S中某些元组也可能被舍弃。这些舍弃的元组被称为 悬浮元组

    如果把悬浮元组也保存在结果中,而在其他属性上置为NULL,那么这种连接就成为 外连接,如果只保留左边关系R中的悬浮元组就叫做 左外连接(左连接),如果只保留右边关系S中的悬浮元组就叫做 右外连接(右连接)

    Join

    Join 用于多表中字段之间的联系,语法如下:

    ... FROM table1 INNER|LEFT|RIGHT JOIN table2 ON conditiona
    
    -- table1:左表;table2:右表。
    
    

    JOIN 按照功能大致分为如下三类:

    • INNER JOIN(内连接,或等值连接):取得两个表中存在连接匹配关系的记录。
    • LEFT JOIN(左连接):取得左表(table1)完全记录,即是右表(table2)并无对应匹配记录。
    • RIGHT JOIN(右连接):与 LEFT JOIN 相反,取得右表(table2)完全记录,即是左表(table1)并无匹配对应记录。

    在下面的示例中使用以下数据:

    mysql> select A.id,A.name,B.name from A,B where A.id=B.id;
    
    
    id name name
    1 Pirate Rutabaga
    2 Monkey Pirate
    3 Ninja Darth Vader
    4 Spaghetti Ninja

    4 rows in set (0.00 sec)

    Inner Join

    内连接,也叫等值连接,inner join产生同时符合A和B的一组数据。

    mysql> select * from A inner join B on A.name = B.name;
    
    
    id name id name
    1 Pirate 2 Pirate
    3 Ninja 4 Ninja

    join_1

    Left Join

    mysql> select * from A left join B on A.name = B.name;
    -- 或者:select * from A left outer join B on A.name = B.name;
    
    
    id name id name
    1 Pirate 2 Pirate
    2 Monkey NULL NULL
    3 Ninja 4 Ninja
    4 Spaghetti NULL NULL

    4 rows in set (0.00 sec)

    join_2

    left join,(或left outer join:在Mysql中两者等价,推荐使用left join)左连接从左表(A)产生一套完整的记录,与匹配的记录(右表(B))。如果没有匹配,右侧将包含null。

    如果想只从左表(A)中产生一套记录,但不包含右表(B)的记录,可以通过设置where语句来执行,如下:

    mysql> select * from A left join B on A.name=B.name where A.id is null or B.id is null;
    
    
    id name id name
    2 Monkey NULL NULL
    4 Spaghetti NULL NULL

    2 rows in set (0.00 sec)

    join_3

    根据上面的例子可以求差集,如下:

    SELECT * FROM A LEFT JOIN B ON A.name = B.name
    WHERE B.id IS NULL
    union
    SELECT * FROM A right JOIN B ON A.name = B.name
    WHERE A.id IS NULL;
    
    
    id name id name
    2 Monkey NULL NULL
    4 Spaghetti NULL NULL
    NULL NULL 1 Rutabaga
    NULL NULL 3 Darth Vader

    union :用于合并多个 select 语句的结果集,并去掉重复的值。 union all :作用和 union 类似,但不会去掉重复的值。

    join_4

    Cross join

    交叉连接,得到的结果是两个表的乘积,即笛卡尔积。

    实际上,在 MySQL 中(仅限于 MySQLCROSS JOININNER JOIN 的表现是一样的,在不指定 ON 条件得到的结果都是笛卡尔积,反之取得两个表完全匹配的结果。

    INNER JOINCROSS JOIN 可以省略 INNERCROSS 关键字,因此下面的 SQL 效果是一样的:

    ... FROM table1 INNER JOIN table2
    ... FROM table1 CROSS JOIN table2
    ... FROM table1 JOIN table2
    
    

    主键与外键

    SQL 的主键和外键的作用:

    外键取值规则:空值或参照的主键值
    (1)插入非空值时,如果主键值中没有这个值,则不能插入。
    (2)更新时,不能改为主键表中没有的值。
    (3)删除主键表记录时,可以在建外键时选定外键记录一起联删除还是拒绝删除。
    (4)更新主键记录时,同样有级联更新和拒绝执行的选择。
    
    

    简而言之,SQL的主键和外键就是起约束作用。
    关系型数据库中一条记录中有若干个属性,若其中某一个属性组(注意是组)能唯一标识一条记录,该属性就可以成为一个主键。例如:

    学生表(学号,姓名,性别,班级)
    
    

    其中每个学生的学号是唯一的,学号就是一个主键;

    课程表(课程编号,课程名,学分)
    
    

    其中课程编号是唯一的,课程编号就是一个主键;

    成绩表(学号,课程号,成绩)
    
    

    成绩表中单一一个属性无法唯一标识一条记录,学号和课程编号的组合才可以唯一标识一条记录,所以学号和课程编号的属性组是一个主键。
    成绩表中的学号不是成绩表中的主键,但它和学生表中的学号相对应,并且学生表中的学号是学生表的主键,则称成绩表中的学号是学生表的外键;同理,成绩表中的课程号是课程表的外键。

    定义主键和外键主要是为了维护关系数据库的完整性,总结一下:

    1.主键是能确定一条记录的唯一标识。比如,一条记录包括身份证号码,姓名,年龄。身份证号码是唯一确认你这个人的,其他的都可能有重复,所以身份证号码是主键。
    外键用于与另一张表相关联。是能确认另一张表记录的字段,用于保持数据的一致性。比如,A表中的一个字段,是B表的主键,那它就可以是A表的外键。

    2.主键、外键和索引的区别

    定义:
    主键:唯一标识一条记录,不能有重复,不允许为空。
    外键:表的外键是另一表的主键,外键是可以有重复的,可以是空值。
    索引:该字段没有重复值,但可以有一个空值。
    作用:
    主键:用来保证数据完整性
    外键:用来和其他表建立联系用
    索引:用来提高查询排序的速度
    个数:
    主键:主键只能有一个。
    外键:一个表可以有多个外键。
    索引:一个表可以有多个唯一索引。
    
    

    创建SQL的主键和外键约束的方法:

    --如果在表创建好了以后再加约束,则格式分别为
    
    --主键
    ALTER TABLE 表名
    --"PK"为主键的缩写,字段名为要在其上创建主键的字段名,"PK_字段名"就为约束名
    ADD CONSTRAINT PK_字段名   
    --同上
    PRIMARY KEY(字段名)
    
    --唯一约束
    ALTER TABLE 表名
    ADD CONSTRAINT UQ_字段名
    UNIQUE(字段名)
    
    --外键约束
    ALTER TABLE 表名
    --"FK"为外键的缩写
    ADD CONSTRAINT FK_字段名
    FOREIGN KEY(字段名) REFERENCES 关联的表名(关联的字段名)
    
    --举个例子
    ALTER TABLE 表A
    ADD CONSTRAINT FK_B 
    FOREIGN KEY(TicketNo) REFERENCES 表B(TicketNo)
    
    --级联更新,级联删除,这样在删除主表Student时,成绩表中该学生的所有成绩都会删除
    ALTER TABLE 成绩表 
    ADD CONSTRAINT FK_StudentNo 
    FOREIGN KEY(StudentNo) REFERENCES Student(StudentNo)
    ON UPDATE CASCADE ON DELETE CASCADE
    
    --检查约束
    ALTER TABLE 表名
    ADD CONSTRAINT CK_字段名
    --括号中的"CK_字段名>0"为条件表达式,用关系运算符连接
    CHECK(字段名>0)
    
    --默认值约束
    ALTER TABLE 表名
    ADD CONSTRAINT DF_字段名
    --其中的'默认值'为想要设置的默认值,注意'FOR'
    DEFAULT '默认值' FOR 字段名
    
    --删除创建的约束
    ALTER TABLE 表名
    --约束名为前面创建的如:FK_字段名这样的约束名
    DROP CONSTRAINT 约束名
    --注意:如果约束是在创建表的时候创建的,则不能用命令删除
    --只能在'企业管理器'里面删除
    
    
    --获取SqlServer中的表结构
    SELECT syscolumns.name,systypes.name,syscolumns.isnullable,syscolumns.length
    FROM syscolumns,systypes
    WHERE syscolumns.xusertype = systypes.xusertype
    AND syscolumns.id = OBJECT_ID('Student')
    
    --单独查询表递增字段
    SELECT [NAME] FROM syscolumns 
    WHERE 
    id = OBJECT_ID(N'Student') AND COLUMNPROPERTY(id,name,'IsIdentity')=1
    
    --获取表主外键约束
    EXEC sp_helpconstraint 'Student'
    
    --查询表主键外键信息
    SELECT 
    sysobjects.id AS objectId,
    OBJECT_NAME(sysobjects.parent_obj) AS TableName,
    sysobjects.name AS constraintName,
    sysobjects.xtype AS constraintType,
    syscolumns.name AS columnName
    FROM sysobjects INNER JOIN sysconstraints
    ON sysobjects.xtype IN('C','F','PK','UQ','D')
    AND sysobjects.id = sysconstraints.constid
    LEFT OUTER JOIN syscolumns ON sysconstraints.id = syscolumns.id
    WHERE OBJECT_NAME(sysobjects.parent_obj) = 'Student' 
    
    
    展开全文
  • 目录 博客链接 数据库系统原理 https://blog.csdn.net/daodaipsrensheng/article/details/90750694
    目录 博客链接
    数据库系统原理 https://blog.csdn.net/daodaipsrensheng/article/details/90750694
       

     

    展开全文
  • 数据库基本知识点总结

    万次阅读 多人点赞 2017-11-21 13:43:48
    这里根据整理的资料,对数据库的相关知识也做个总结吧。希望学过数据库但长时间不用的同学根据这些知识能够回忆和重拾,没学过的同学能掌握一些数据库的基础知识。 第一节  一、相关概念  1. Data:数据...

    数据库也是计算机类笔试面试中不可避免会遇到的考点,尤其是银行和部分传统软件类公司。这里根据整理的资料,对数据库的相关知识也做个总结吧。希望学过数据库但长时间不用的同学根据这些知识能够回忆和重拾,没学过的同学能掌握一些数据库的基础知识。

    第一节

          一、相关概念

           1. Data:数据,是数据库中存储的基本对象,是描述事物的符号记录。
           2. Database:数据库,是长期储存在计算机内、有组织的、可共享的大量数据的集合。
           3. DBMS:数据库管理系统,是位于用户与操作系统之间的一层数据管理软件,用于科学地组织、存储和管理数据、高效地获取和维护数据。
           4. DBS:数据库系统,指在计算机系统中引入数据库后的系统,一般由数据库、数据库管理系统、应用系统、数据库管理员(DBA)构成。
           5. 数据模型:是用来抽象、表示和处理现实世界中的数据和信息的工具,是对现实世界的模拟,是数据库系统的核心和基础;其组成元素有数据结构、数据操作和完整性约束。
           6. 概念模型:也称信息模型,是按用户的观点来对数据和信息建模,主要用于数据库设计。
           7. 逻辑模型:是按计算机系统的观点对数据建模,用于DBMS实现。
           8. 物理模型:是对数据最底层的抽象,描述数据在系统内部的表示方式和存取方法,在磁盘或磁带上的存储方式和存取方法,是面向计算机系统的。
           9. 实体和属性:客观存在并可相互区别的事物称为实体。实体所具有的某一特性称为属性。
           10.E-R图:即实体-关系图,用于描述现实世界的事物及其相互关系,是数据库概念模型设计的主要工具。
           11.关系模式:从用户观点看,关系模式是由一组关系组成,每个关系的数据结构是一张规范化的二维表。
           12.型/值:型是对某一类数据的结构和属性的说明;值是型的一个具体赋值,是型的实例。
           13.数据库模式:是对数据库中全体数据的逻辑结构(数据项的名字、类型、取值范围等)和特征(数据之间的联系以及数据有关的安全性、完整性要求)的描述。
           14.数据库的三级系统结构:外模式、模式和内模式。
           15.数据库内模式:又称为存储模式,是对数据库物理结构和存储方式的描述,是数据在数据库内部的表示方式。一个数据库只有一个内模式。
           16.数据库外模式:又称为子模式或用户模式,它是数据库用户能够看见和使用的局部数据的逻辑结构和特征的描述,是数据库用户的数据视图。通常是模式的子集。一个数据库可有多个外模式。
           17.数据库的二级映像:外模式/模式映像、模式/内模式映像。

         

     二、重点知识点

           1. 数据库系统由数据库、数据库管理系统、应用系统数据库管理员构成。

           2. 数据模型的组成要素是:数据结构、数据操作、完整性约束条件

           3. 实体型之间的联系分为一对一、一对多多对多三种类型。

           4. 常见的数据模型包括:关系、层次、网状、面向对象、对象关系映射等几种。

           5. 关系模型的完整性约束包括:实体完整性、参照完整性和用户定义完整性

          6. 阐述数据库三级模式、二级映象的含义及作用。
            数据库三级模式反映的是数据的三个抽象层次: 模式是对数据库中全体数据的逻辑结构和特征的描述。内模式又称为存储模式,是对数据库物理结构和存储方式的描述。外模式又称为子模式或用户模式,是对特定数据库用户相关的局部数据的逻辑结构和特征的描述

            数据库三级模式通过二级映象在 DBMS 内部实现这三个抽象层次的联系和转换。外模式面向应用程序, 通过外模式/模式映象与逻辑模式建立联系, 实现数据的逻辑独立性。 模式/内模式映象建立模式与内模式之间的一对一映射, 实现数据的物理独立性。

    第二节

    一、相关概念

           1. 主键: 能够唯一地标识一个元组的属性或属性组称为关系的键或候选键。 若一个关系有多个候选则可选其一作为主(Primary key)。

           2. :如果一个关系的一个或一组属性引用(参照)了另一个关系的,则称这个或这组属性为外码或外键(Foreign key)。

           3. 关系数据库: 依照关系模型建立的数据库称为关系数据库。 它是在某个应用领域的所有关系的集合。

           4. 关系模式: 简单地说,关系模式就是对关系的型的定义, 包括关系的属性构成、各属性的数据类型、 属性间的依赖、 元组语义及完整性约束等。 关系是关系模式在某一时刻的状态或内容, 关系模型是型, 关系是值, 关系模型是静态的、 稳定的, 而关系是动态的、随时间不断变化的,因为关系操作在不断地更新着数据库中的数据。

           5. . 实体完整性:用于标识实体的唯一性。它要求基本关系必须要有一个能够标识元组唯一性的主键,主键不能为空,也不可取重复值

           6. 参照完整性: 用于维护实体之间的引用关系。 它要求一个关系的外键要么为空, 要么取与被参照关系对应的主键值,即外键值必须是主键中已存在的值

           7. 用户定义的完整性:就是针对某一具体应用的数据必须满足的语义约束。包括非空、 唯一和布尔条件约束三种情况。

          二、重要知识点

          1. 关系数据库语言分为关系代数、关系演算和结构化查询语言三大类。

          2. 关系的 5 种基本操作是选择、投影、并、差、笛卡尔积。

          3.关系模式是对关系的描述,五元组形式化表示为:R(U,D,DOM,F),其中

                R —— 关系名

                U —— 组成该关系的属性名集合

                D —— 属性组 U 中属性所来自的域

                DOM —— 属性向域的映象集合

                F —— 属性间的数据依赖关系集合

           4.笛卡尔乘积,选择和投影运算如下


    第三节

    一、相关概念

           1. SQL:结构化查询语言的简称, 是关系数据库的标准语言。SQL 是一种通用的、 功能极强的关系数据库语言, 是对关系数据存取的标准接口, 也是不同数据库系统之间互操作的基础。集数据查询、数据操作、数据定义、和数据控制功能于一体。

           2. 数据定义:数据定义功能包括模式定义、表定义、视图和索引的定义。

           3. 嵌套查询:指将一个查询块嵌套在另一个查询块的 WHERE 子句或 HAVING 短语的条件中的查询。

          二、重要知识点

           1. SQL 数据定义语句的操作对象有:模式、表、视图和索引。
           2. SQL 数据定义语句的命令动词是:CREATE、DROP 和 ALTER。
           3. RDBMS 中索引一般采用 B+树或 HASH 来实现。
           4. 索引可以分为唯一索引、非唯一索引和聚簇索引三种类型。


           6.SQL 创建表语句的一般格式为

                  CREATE TABLE <表名>

                  ( <列名> <数据类型>[ <列级完整性约束> ]

                  [<列名> <数据类型>[ <列级完整性约束>] ] 

                  [<表级完整性约束> ] ) 

    其中<数据类型>可以是数据库系统支持的各种数据类型,包括长度和精度。 

        列级完整性约束为针对单个列(本列)的完整性约束, 包括 PRIMARY KEY、 REFERENCES表名(列)UNIQUENOT NULL 等。 

        表级完整性约束可以是基于表中多列的约束,包括 PRIMARY KEY ( 列名列表FOREIGN KEY REFERENCES 表名(列名等。


           7. SQL 创建索引语句的一般格式为

                  CREATE [UNIQUE] [CLUSTER] INDEX <索引名>

                  ON <表名> (<列名列表> ) 

    其中UNIQUE:表示创建唯一索引,缺省为非唯一索引;

          CLUSTER:表示创建聚簇索引,缺省为非聚簇索引;

          <列名列表>:一个或逗号分隔的多个列名,每个列名后可跟 ASC 或 DESC,表示升/降序,缺省为升序。多列时则按为多级排序。


            8. SQL 查询语句的一般格式为

                  SELECT [ALLDISTINCT] <算术表达式列表> FROM <表名或视图名列表>

                  [ WHERE <条件表达式 1> ]

                  [ GROUP BY <属性列表 1> [ HAVING <条件表达式 2 > ] ]

                  [ ORDER BY <属性列表 2> [ ASCDESC ] ] 

    其中

          ALLDISTINCT: 缺省为 ALL, 即列出所有查询结果记录, 包括重复记录。 DISTINCT则对重复记录只列出一条。

           算术表达式列表:一个或多个逗号分隔的算术表达式,表达式由常量(包括数字和字符串)、列名、函数和算术运算符构成。每个表达式后还可跟别名。也可用 *代表查询表中的所有列。

          <表名或视图名列表>: 一个或多个逗号分隔的表或视图名。 表或视图名后可跟别名。

          条件表达式 1:包含关系或逻辑运算符的表达式,代表查询条件。

          条件表达式 2:包含关系或逻辑运算符的表达式,代表分组条件。

          <属性列表 1>:一个或逗号分隔的多个列名。

          <属性列表 2>: 一个或逗号分隔的多个列名, 每个列名后可跟 ASC 或 DESC, 表示升/降序,缺省为升序。


          关于SQL语句的知识这里先作如上简略介绍,具体写法下次将专门拿出一篇来叙述。


    第四节

    一、相关概念和知识

           1.触发器是用户定义在基本表上的一类由事件驱动的特殊过程。由服务器自动激活, 能执行更为复杂的检查和操作,具有更精细和更强大的数据控制能力。使用 CREATE TRIGGER 命令建立触发器。

           2.计算机系统存在技术安全、管理安全和政策法律三类安全性问题。

           3. TCSEC/TDI 标准由安全策略、责任、保证和文档四个方面内容构成。

           4. 常用存取控制方法包括自主存取控制(DAC)和强制存取控制(MAC)两种。

           5. 自主存取控制(DAC)的 SQL 语句包括 GRANT 和 REVOKE 两个。 用户权限由数据对象和操作类型两部分构成。


           6. 常见SQL 自主权限控制命令和例子。
             1) 把对 Student 和 Course 表的全部权限授予所有用户。
                  GRANT ALL PRIVILIGES ON TABLE Student,Course TO PUBLIC ;
             2) 把对 Student 表的查询权和姓名修改权授予用户 U4。
                  GRANT SELECT,UPDATE(Sname) ON TABLE Student TO U4 ;
             3) 把对 SC 表的插入权限授予 U5 用户,并允许他传播该权限。
                  GRANT INSERT ON TABLE SC TO U5 WITH GRANT OPTION ;
             4) 把用户 U5 对 SC 表的 INSERT 权限收回,同时收回被他传播出去的授权。
                  REVOKE INSERT ON TABLE SC FROM U5 CASCADE ;
             5) 创建一个角色 R1,并使其对 Student 表具有数据查询和更新权限。
                  CREATE ROLE R1;
                  GRANT SELECT,UPDATE ON TABLE Student TO R1;
             6) 对修改 Student 表结构的操作进行审计。
                  AUDIT ALTER ON Student ;

    数据库知识总结(2)范式

     一、相关概念和知识点

           1.数据依赖:反映一个关系内部属性与属性之间的约束关系,是现实世界属性间相互联系的抽象,属于数据内在的性质和语义的体现。
           2. 规范化理论:是用来设计良好的关系模式的基本理论。它通过分解关系模式来消除其中不合适的数据依赖,以解决插入异常、删除异常、更新异常和数据冗余问题。
           3. 函数依赖:简单地说,对于关系模式的两个属性子集X和Y,若X的任一取值能唯一确定Y的值,则称Y函数依赖于X,记作X→Y。
           4. 非平凡函数依赖:对于关系模式的两个属性子集X和Y,如果X→Y,但Y!⊆X,则称X→Y为非平凡函数依赖;如果X→Y,但Y⊆X,则称X→Y为非平凡函数依赖。
           5. 完全函数依赖:对于关系模式的两个属性子集X和Y,如果X→Y,并且对于X的任何一个真子集X',都没有X'→Y,则称Y对X完全函数依赖。
           6. 范式:指符合某一种级别的关系模式的集合。在设计关系数据库时,根据满足依赖关系要求的不同定义为不同的范式。
           7. 规范化:指将一个低一级范式的关系模式,通过模式分解转换为若干个高一级范式的关系模式的集合的过程。
           8. 1NF:若关系模式的所有属性都是不可分的基本数据项,则该关系模式属于1NF。
           9. 2NF:1NF关系模式如果同时满足每一个非主属性完全函数依赖于码,则该关系模式属于2NF。
           10. 3NF:若关系模式的每一个非主属性既不部分依赖于码也不传递依赖于码,则该关系模式属于3NF。
           11. BCNF:若一个关系模式的每一个决定因素都包含码,则该关系模式属于BCNF。
           12. 数据库设计:是指对于一个给定的应用环境,构造优化的数据库逻辑模式和物理结构,并据此建立数据库及其应用系统,使之能够有效地存储和管理数据,满足各种用户的应用需求,包括信息管理要求和数据操作要求。
           13. 数据库设计的6个基本步骤:需求分析,概念结构设计,逻辑结构设计,物理结构设计,数据库实施,数据库运行和维护
           14. 概念结构设计:指将需求分析得到的用户需求抽象为信息结构即概念模型的过程。也就是通过对用户需求进行综合、归纳与抽象,形成一个独立于具体DBMS的概念模型。
           15. 逻辑结构设计:将概念结构模型(基本E-R图)转换为某个DBMS产品所支持的数据模型相符合的逻辑结构,并对其进行优化。
           16. 物理结构设计:指为一个给定的逻辑数据模型选取一个最适合应用环境的物理结构的过程。包括设计数据库的存储结构与存取方法。
           17. 抽象:指对实际的人、物、事和概念进行人为处理,抽取所关心的共同特性,忽略非本质的细节,并把这些特性用各种概念精确地加以描述,这些概念组成了某种模型。

           18. 数据库设计必须遵循结构设计和行为设计相结合的原则。

           19. 数据字典主要包括数据项、数据结构、数据流、数据存储和处理过程五个部分。
           20. 三种常用抽象方法是分类、聚集和概括
           21. 局部 E-R 图之间的冲突主要表现在属性冲突、命名冲突和结构冲突三个方面。

           22. 数据库常用的存取方法包括索引方法、聚簇方法和 HASH方法三种。
           23. 确定数据存放位置和存储结构需要考虑的因素主要有: 存取时间、 存储空间利用率和维护代价等。

    二、细说数据库三范式

    2.1 第一范式(1NF)无重复的列

           第一范式(1NF)中数据库表的每一列都是不可分割的基本数据项
           同一列中不能有多个值
           即实体中的某个属性不能有多个值或者不能有重复的属性。
           简而言之,第一范式就是无重复的列。

           在任何一个关系数据库中,第一范式(1NF)是对关系模式的基本要求,不满足第一范式(1NF)的数据库就不是关系数据库。

    2.2 第二范式(2NF)属性完全依赖于主键[消除部分子函数依赖]

          满足第二范式(2NF)必须先满足第一范式(1NF)。

          第二范式(2NF)要求数据库表中的每个实例或行必须可以被惟一地区分。

          为实现区分通常需要为表加上一个列,以存储各个实例的惟一标识。 
          第二范式(2NF)要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性,如果存在,那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体,新实体与原实体之间是一对多的关系。为实现区分通常需要为表加上一个列,以存储各个实例的惟一标识。简而言之,第二范式就是属性完全依赖于主键。
    2.3 第三范式(3NF)属性不依赖于其它非主属性[消除传递依赖]

          满足第三范式(3NF)必须先满足第二范式(2NF)。

          简而言之,第三范式(3NF)要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。

          例如,存在一个部门信息表,其中每个部门有部门编号(dept_id)、部门名称、部门简介等信息。那么在的员工信息表中列出部门编号后就不能再将部门名称、部门简介等与部门有关的信息再加入员工信息表中。如果不存在部门信息表,则根据第三范式(3NF)也应该构建它,否则就会有大量的数据冗余。简而言之,第三范式就是属性不依赖于其它非主属性。

    2.4 具体实例剖析

          下面列举一个学校的学生系统的实例,以示几个范式的应用。

           在设计数据库表结构之前,我们先确定一下要设计的内容包括那些。学号、学生姓名、年龄、性别、课程、课程学分、系别、学科成绩,系办地址、系办电话等信息。为了简单我们暂时只考虑这些字段信息。我们对于这些信息,说关心的问题有如下几个方面。

           1)学生有那些基本信息 
           2)学生选了那些课,成绩是什么 
           3)每个课的学分是多少 
           4)学生属于那个系,系的基本信息是什么。

           首先第一范式(1NF):数据库表中的字段都是单一属性的,不可再分。这个单一属性由基本类型构成,包括整型、实数、字符型、逻辑型、日期型等。在当前的任何关系数据库管理系统(DBMS)中,不允许你把数据库表的一列再分成二列或多列,因此做出的都是符合第一范式的数据库。 

           我们再考虑第二范式,把所有这些信息放到一个表中(学号,学生姓名、年龄、性别、课程、课程学分、系别、学科成绩,系办地址、系办电话)下面存在如下的依赖关系。 
           1)(学号)→ (姓名, 年龄,性别,系别,系办地址、系办电话) 
           2) (课程名称) → (学分) 
           3)(学号,课程)→ (学科成绩)

    根据依赖关系我们可以把选课关系表SelectCourse改为如下三个表: 

           学生:Student(学号,姓名, 年龄,性别,系别,系办地址、系办电话); 
           课程:Course(课程名称, 学分); 
           选课关系:SelectCourse(学号, 课程名称, 成绩)。

           事实上,对照第二范式的要求,这就是满足第二范式的数据库表,若不满足第二范式,会产生如下问题 
    数据冗余: 同一门课程由n个学生选修,"学分"就重复n-1次;同一个学生选修了m门课程,姓名和年龄就重复了m-1次。

    更新异常: 1)若调整了某门课程的学分,数据表中所有行的"学分"值都要更新,否则会出现同一门课程学分不同的情况。 
                     2)假设要开设一门新的课程,暂时还没有人选修。这样,由于还没有"学号"关键字,课程名称和学分也无法记录入数据库。

    删除异常 : 假设一批学生已经完成课程的选修,这些选修记录就应该从数据库表中删除。但是,与此同时,课程名称和学分信息也被删除了。很显然,这也会导致插入异常。

           我们再考虑如何将其改成满足第三范式的数据库表,接着看上面的学生表Student(学号,姓名, 年龄,性别,系别,系办地址、系办电话),关键字为单一关键字"学号",因为存在如下决定关系:

          (学号)→ (姓名, 年龄,性别,系别,系办地址、系办电话) 
    但是还存在下面的决定关系 
           (学号) → (所在学院)→(学院地点, 学院电话) 
            即存在非关键字段"学院地点"、"学院电话"对关键字段"学号"的传递函数依赖。 
           它也会存在数据冗余、更新异常、插入异常和删除异常的情况(这里就不具体分析了,参照第二范式中的分析)。根据第三范式把学生关系表分为如下两个表就可以满足第三范式了:

           学生:(学号, 姓名, 年龄, 性别,系别); 
           系别:(系别, 系办地址、系办电话)。



    SQL语句总结

    SQL语句中常用关键词及其解释如下:

    1)SELECT

    将资料从数据库中的表格内选出,两个关键字:从 (FROM) 数据库中的表格内选出 (SELECT)。语法为
    SELECT "栏位名" FROM "表格名"。

    2)DISTINCT

    在上述 SELECT 关键词后加上一个 DISTINCT 就可以去除选择出来的栏位中的重复,从而完成求得这个表格/栏位内有哪些不同的值的功能。语法为
    SELECT DISTINCT "栏位名" FROM "表格名"。

    3)WHERE

    这个关键词可以帮助我们选择性地抓资料,而不是全取出来。语法为
    SELECT "栏位名" FROM "表格名" WHERE "条件" 

    4)AND OR

    上例中的 WHERE 指令可以被用来由表格中有条件地选取资料。这个条件可能是简单的 (像上一页的例子),也可能是复杂的。复杂条件是由二或多个简单条件透过 AND 或是 OR 的连接而成。语法为:
    SELECT "栏位名"  FROM "表格名"  WHERE "简单条件"  {[AND|OR] "简单条件"}+

    5)IN

    在 SQL 中,在两个情况下会用到 IN  这个指令;这一页将介绍其中之一:与 WHERE 有关的那一个情况。在这个用法下,我们事先已知道至少一个我们需要的值,而我们将这些知道的值都放入 IN  这个子句。语法为:
    SELECT "栏位名"  FROM "表格名"  WHERE "栏位名" IN ('值一', '值二', ...)  

    6)BETWEEN

    IN 这个指令可以让我们依照一或数个不连续 (discrete)的值的限制之内抓出资料库中的值,而 BETWEEN 则是让我们可以运用一个范围 (range)  内抓出资料库中的值,语法为:
    SELECT "栏位名"  FROM "表格名" WHERE "栏位名" BETWEEN '值一' AND '值二' 

    7)LIKE

    LIKE 是另一个在 WHERE  子句中会用到的指令。基本上, LIKE  能让我们依据一个模式(pattern) 来找出我们要的资料。语法为:
    SELECT "栏位名"  FROM "表格名"  WHERE "栏位名" LIKE {模式} 

    8)ORDER BY

    我们经常需要能够将抓出的资料做一个有系统的显示。这可能是由小往大 (ascending)  或是由大往小(descending)。在这种情况下,我们就可以运用 ORDER BY 这个指令来达到我们的目的。语法为:
    SELECT "栏位名"  FROM "表格名 [WHERE "条件"] ORDER BY "栏位名" [ASC, DESC] 

    9)函数

    函数允许我们能够对这些数字的型态存在的行或者列做运算,包括 AVG (平均)、COUNT (计数)、MAX (最大值)、MIN (最小值)、SUM (总合)。语法为:
    SELECT "函数名"("栏位名") FROM "表格名"  

    10)COUNT

    这个关键词能够帮我我们统计有多少笔资料被选出来,语法为:
    SELECT COUNT("栏位名") FROM "表格名"

    11)GROUP BY

    GROUP BY 语句用于结合合计函数,根据一个或多个列对结果集进行分组。语法为:
    SELECT "栏位1", SUM("栏位2")  FROM "表格名"  GROUP BY "栏位1" 

    12)HAVING

    该关键词可以帮助我们对函数产生的值来设定条件。语法为:
    SELECT "栏位1", SUM("栏位2")  FROM "表格名"  GROUP BY "栏位1"  HAVING (函数条件)  

    13)ALIAS

    我们可以通过ALIAS为列名称和表名称指定别名,语法为:
    SELECT "表格别名"."栏位1" "栏位别名"  FROM "表格名" "表格别名"  

    下面为一个例子,通过它我们应该能很好地掌握以上关键词的使用方法。


    Student(S#,Sname,Sage,Ssex) 学生表
    Course(C#,Cname,T#) 课程表
    SC(S#,C#,score) 成绩表
    Teacher(T#,Tname) 教师表


    问题:
    1、查询“001”课程比“002”课程成绩高的所有学生的学号;
    select a.S#
    from (select s#,score from SC where C#=’001′) a,
    (select s#,score from SC where C#=’002′) b
    where a.score>b.score and a.s#=b.s#;

    2、查询平均成绩大于60分的同学的学号和平均成绩;
    select S#,avg(score)
    from sc
    group by S# having avg(score) >60;

    3、查询所有同学的学号、姓名、选课数、总成绩;
    select Student.S#,Student.Sname,count(SC.C#),sum(score)
    from Student left Outer join SC on Student.S#=SC.S#
    group by Student.S#,Sname

    4、查询姓“李”的老师的个数;
    select count(distinct(Tname))
    from Teacher
    where Tname like ‘李%’;

    5、查询没学过“叶平”老师课的同学的学号、姓名;
    select Student.S#,Student.Sname
    from Student
    where S# not in (select distinct( SC.S#) from SC,Course,Teacher where SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname=’叶平’);

    6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名;
    select Student.S#,Student.Sname
    from Student,SC

    where Student.S#=SC.S# and SC.C#=’001′and exists( Select * from SC as SC_2 where SC_2.S#=SC.S# and SC_2.C#=’002′);


    7、查询学过“叶平”老师所教的所有课的同学的学号、姓名;
    select S#,Sname
    from Student
    where S# in
    (select S#
    from SC ,Course ,Teacher
    where SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname=’叶平’ group by S# having count(SC.C#)=(select count(C#) from Course,Teacher where Teacher.T#=Course.T# and Tname=’叶平’));

    8、查询所有课程成绩小于60分的同学的学号、姓名;
    select S#,Sname
    from Student
    where S# not in (select Student.S# from Student,SC where S.S#=SC.S# and score>60);

    9、查询没有学全所有课的同学的学号、姓名;
    select Student.S#,Student.Sname
    from Student,SC
    where Student.S#=SC.S#
    group by Student.S#,Student.Sname having count(C#) <(select count(C#) from Course);

    10、查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名;
    select S#,Sname
    from Student,SC
    where Student.S#=SC.S# and C# in (select C# from SC where S#='1001');

    11、删除学习“叶平”老师课的SC表记录;
    Delect SC
    from course ,Teacher
    where Course.C#=SC.C# and Course.T#= Teacher.T# and Tname='叶平';

    12、查询各科成绩最高和最低的分:以如下形式显示:课程ID,最高分,最低分
    SELECT L.C# 课程ID,L.score 最高分,R.score 最低分
    FROM SC L ,SC R
    WHERE L.C# = R.C#
    and
    L.score = (SELECT MAX(IL.score)
    FROM SC IL,Student IM
    WHERE IL.C# = L.C# and IM.S#=IL.S#
    GROUP BY IL.C#)
    and
    R.Score = (SELECT MIN(IR.score)
    FROM SC IR
    WHERE IR.C# = R.C#
    GROUP BY IR.C# );

    13、查询学生平均成绩及其名次
    SELECT 1+(SELECT COUNT( distinct 平均成绩)
    FROM (SELECT S#,AVG(score) 平均成绩
    FROM SC
    GROUP BY S# ) T1
    WHERE 平均成绩 > T2.平均成绩) 名次, S# 学生学号,平均成绩
    FROM (SELECT S#,AVG(score) 平均成绩 FROM SC GROUP BY S# ) T2
    ORDER BY 平均成绩 desc;

    14、查询各科成绩前三名的记录:(不考虑成绩并列情况)
    SELECT t1.S# as 学生ID,t1.C# as 课程ID,Score as 分数
    FROM SC t1
    WHERE score IN (SELECT TOP 3 score
    FROM SC
    WHERE t1.C#= C#
    ORDER BY score DESC)
    ORDER BY t1.C#;

    15、查询每门功成绩最好的前两名
    SELECT t1.S# as 学生ID,t1.C# as 课程ID,Score as 分数
    FROM SC t1
    WHERE score IN (SELECT TOP 2 score
    FROM SC
    WHERE t1.C#= C#
    ORDER BY score DESC )

    ORDER BY t1.C#;

    展开全文
  • 数据库必须具备的四个特性 数据库范式 索引 存储过程 死锁 为何选择B+树 数据库必须具备的四个特性 1:原子性:全部成功或失败,失败不会有影响 2:一致性:事务执行前后状态一致。 3:隔离性:多个并发...

    目录

     

    数据库必须具备的四个特性

    数据库范式

    索引

    存储过程

    死锁

    为何选择B+树


    • 数据库必须具备的四个特性

    1:原子性:全部成功或失败,失败不会有影响

    2:一致性:事务执行前后状态一致。

    3:隔离性:多个并发事务之间要相互隔离;

    4:持久性:数据提交后的改变是永久的,故障不丢失

    隔离性:不考虑事务的隔离性,会产生的几种问题:

    01:脏读

    数据库访问中,事务T1将某一值修改,然后事务T2读取该值,此后T1因为某种原因撤销对该值的修改,这就导致了T2所读取到的数据是无效的。

    02:不可重复读
    不能一个数据同一时间被两个人同时读取。

    03::幻读
    当事务不是独立执行时发生的一种现象,T1修改全部数据,T2在另一时间插入了一行数据,那T1再读取就发现,诶,有一个没改,是幻觉么,就是幻读,可设置只读来解决。

    • 数据库范式

    第一范式(1NF)数据表每一列不可分割,不能是集合数组,不可拆分,例如应该把电话分为座机和移动电话

    第二范式(2NF)实体属性应该完全依赖于主关键字,不能部分依赖,例如学分依赖于课程,但是不依赖另一个关键因素学号。

    第三范式(3NF)非主属性不依赖其他表的非主属性,例如单位表有字段(楼层,区号),那员工表就不用出现这些属性了。

    BCNF                  不能存在关键字段决定关键字段,例如管理员号与仓库号互相决定。

    第四范式(4NF)一项不能是多值依赖的,比如孩子,有可能有很多孩子,选修课也有很多,这就是多值问题

    索引

    聚集索引,按顺序的。非聚集索引,物理上连续的。

    存储过程

    先编译,再执行。大型数据库系统中,先编译好后,用存储过程的名字来调用,速度就会很快

    死锁

    原因:

            系统资源不足,运行顺序不合适,资源分配不当

    必要条件:

            循环等待,不可强占,互斥每个资源只能被一个占有,一个进程申请新资源的时候还占有老资源

    解决方案:

            银行家算法,全部加锁否则不执行,顺序封锁

    为何选择B+树

    若选择二叉查找树:一个二叉查找树是由n个节点随机构成,所以,对于某些情况,二叉查找树会退化成一个有n个节点的线性链。

    AVL:则是带有平衡条件的二叉查找树,如不满足平衡,则要翻转,因此适合查找多插入少的情况

    红黑树:用标注颜色的方式限制一条路径比其他路径大两倍的情况。属于弱平衡,对于搜索、插入、删除操作多的情况下,我们就用红黑树

    红黑树的性质

    1、每个节点非红即黑; 
    2、根节点是黑的; 
    3、每个叶节点(叶节点即树尾端NULL指针或NULL节点)都是黑的; 
    4、如果一个节点是红的,那么它的两儿子都是黑的; 
    5、对于任意节点而言,其到叶子点树NULL指针的每条路径都包含相同数目的黑节点; 
    6、每条路径都包含相同的黑节点;

    B+树

    1. 多叉树,降低树高度,把数据分成多个区间
    2. 每个节点可存储多个key值
    3. 非叶子节点存储key,叶子节点存储key和value
    4. 叶子节点相连,方便顺序查找
    5. 主要是优化了磁盘读取效率,IO更快,顺序存储,不用过多的磁道存储,支持范围查询,无需整个树遍历。

    展开全文
  • 数据库知识点总结

    千次阅读 2014-04-18 20:27:33
    这里根据整理的资料,对数据库的相关知识也做个总结吧。希望学过数据库但长时间不用的同学根据这些知识能够回忆和重拾,没学过的同学能掌握一些数据库的基础知识。 第一节 一、相关概念 1. Data:数据,是...
  • 数据库面试知识点总结

    千次阅读 2018-07-18 18:35:07
    数据库连接 数据库隔离级别 1)Read uncommitted(未授权读取、读未提交) 引发脏读,脏读是指读取了其他事务没有提交的数据 2)Read committed(读提交) Oracle等大部分数据库,默认使用这种隔离级别 。 3)...
  • 数据库事务正确执行的四个基本要素: 原子性(Atomicity): 事务开始后,要么全部完成,要么全部不完成,不能停留在某个中间环节。如果事务出错,那么就回滚(Rollback)到事务开始前的状态。 一致性(Consistency): ...
  • 数据库知识总结 MySQL: 事务:事务是并发控制的基本单元,事务是一个操作序列,要么都执行,要么都不执行,他是一个不可分割的工作单位,事务是维护数据库一致性的单位。 四个ACID基本性质: 原子性:要么都执行,...
  • 关系型和非关系型数据库 关系型数据库 指采用了关系模型来组织数据的数据库。关系模型指的是二维表格模型,而一个关系型数据库就是由二维表及其之间的联系所组成的一个数据组织。数据库事务必须具备ACID特性。 关系...
  • group by 分组 与聚合函数一同使用 按多个字段分组 与having一起使用,having相当于where,where不能与聚合函数联合...重复读 时间从事务开始时,即读取结果都是数据开始前的 MySql默认 4。序列化

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 923
精华内容 369
关键字:

数据库知识点总结面试