《数据库》小结02

一、SQL题

1.学生成绩表,把每科最高分前三名统计出来
成绩表Score(student_no,subject_no,score)

-分组排序函数:
函数名() over(partition by 分组列 order by 排序列 desc) 重命名列

  • rank() :并列时下一位空出所占的名次1,2,2,4。
  • dense_rank():并列时下一位不空出所占的名次1,2,2,3。
  • row_number():不需要考虑是否并列,哪怕根据条件查询出来的数值相同也会进行连续排名。
解法1:相关子查询
SELECT *
FROM Score AS s1
WHERE score in(
              Select score
              From Score As s2
              WHERE s2.subject_no = s1.subject_no and rownum<=3
              )
 order by s1.subject_no,s1.score desc;

解法2:分组排序函数
SELECT *
FROM ( SELECT *,row_number() over(partition by subject_no order by score desc) rank
       FROM Score 
       )
 WHERE rank<=3;

2.选出每个人成绩的最高的前两条纪录
成绩表Score(student_no,subject_no,score)

SELECT *
FROM ( SELECT *,rank() over(partition by student_no order by score desc) rank
       FROM Score
       )
WHERE rank<=2;

3.删除订单表中重复的记录

DELECT FROM Score
WHERE name IN(SELECT name
              FROM Score
              GROUP BY name
              HAVING count(name)>1)
AND ID NOT IN(SELECT MIN(ID)
              FROM Score
              GROUP BY name
              HAVING count(name)>1)

4.查询A表有但是B表没有

解法1:相关子查询
SELECT DISTINCT A.ID
FROM A
WHERE A.ID NOT IN ( SELECT ID
                    FROM B
                    )
解法2:左连接
SELECT A.ID
FROM A LEFR JOIN B ON A.ID=B.ID
WHERE B.ID IS NULL

5.找出单科成绩高于该科平均成绩的同学名单(无论该学生有多少科,只要有一科满足即可)

SELECT * 
FROM (SELECT *,AVG(score) over(partition by subject_no) flag
      From Score
      ) AS t
 WHERE score>t.flag

6.找出单科成绩高于该科平均成绩的同学名单(该学生所有科都必须满足)

SELECT DISTINCT a.name
FROM Score AS a 
LEFT JOIN( SELECT *
           FROM(SELECT *,AVG(score) over(partition by subject_no) flag
                FROM Score
                )AS t
            WHERE t.flag<=score
            ) AS b 
 ON a.name=b.name and a.subject_no = b.subject_no and a.score=b.score and b.score is not NULL; 

二、数据库索引

1.索引的作用和优点

  • 1.提高数据的查询检索速度
  • 2.通过创建唯一性索引可以保证数据库中每一行的唯一性
  • 3.加速表和表之间的连接,特别是在实现数据的参考完整性方面特别有意义。
  • 4.在使用分组和排序子句进行数据检索时,同样可以显著减少查询中分组和排序的时间。
  • 5.通过使用索引,可以在查询的过程中,使用查询优化器,提高系统的性能。

2.索引的缺点

  • 1.创建索引和维护索引要耗费时间,这种时间随着数据量的增加而增加。
  • 2.索引需要占物理空间,除了数据表占数据空间之外,每一个索引还要占一定的物理空间,如果要建立聚簇索引,那么需要的空间就会更大。
  • 3.当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护,这样就降低了数据的维护速度。

3.索引的类型
索引有两个特征,即唯一性索引和复合索引

  • 唯一索引:每一列都唯一
  • 主键索引:主键唯一,其余列可以相同
  • 聚簇索引和非聚簇索引
    ----表中行的物理顺序与键值的逻辑(索引)顺序相同,升序排列。每个表只有一个聚簇索引。
    ----与非聚簇索引相比,聚簇索引查询速度更快。在创建任何非聚簇索引之前创建聚簇索引,这是因为聚簇索引改变了表中行的物理顺序,数据行按照一定的顺序排列,并且自动维护这个顺序。
    ----保证有足够的空间来创建聚簇索引。
  • 复合索引

4.索引实现方式

  • B+树:一般比较矮胖,ORACLE默认索引是B+树。范围查询只能用这个。

问题:B树和B+树有什么区别?为什么索引不用B树?
答:B+树在非叶子结点不保存数据,只在叶子结点保存。而B树在叶子结点和非叶子结点都会保存。这种结构导致你如果用B树来进行查询,会增加磁盘IO的次数,导致性能不如B+树。

  • 散列索引:通过散列函数来定位,根据对应键的散列码来查找的一种索引。二级辅助索引。
  • 位图索引:针对多个字段的同时简单查询,只适用于字段值固定并且值种类少的情况。
  • HASH索引

a.根据索引的键值计算出响应的hash值,然后根据hash表中的地址来定位数据。
b.其检索效率非常高,索引的检索可以一次定位,不像B-Tree 索引需要从根节点到枝节点,最后才能访问到页节点这样多次的IO访问,所以 Hash索引的查询效率要远高于 B-Tree 索引
c.只有Memory存储引擎显示支持hash索引

1.优点:检索效率非常高,索引的检索可以一次定位
2.缺点:

  • (1)Hash 索引仅仅能满足"=",“IN"和”<=>"查询,不能使用范围查询。
    由于 Hash 索引比较的是进行 Hash 运算之后的 Hash 值,所以它只能用于等值的过滤,不能用于基于范围的过滤,因为经过相应的 Hash 算法处理之后的 Hash
    值的大小关系,并不能保证和Hash运算前完全一样。
  • (2)Hash 索引无法被用来避免数据的排序操作。
    由于 Hash 索引中存放的是经过 Hash 计算之后的 Hash 值,而且Hash值的大小关系并不一定和 Hash 运算前的键值完全一样,所以数据库无法利用索引的数据来避免任何排序运算。
  • (3)Hash 索引不能利用部分索引键查询。
    对于组合索引,Hash 索引在计算 Hash 值的时候是组合索引键合并后再一起计算 Hash 值,而不是单独计算 Hash 值,所以通过组合索引的前面一个或几个索引键进行查询的时候,Hash 索引也无法被利用。
  • (4)Hash 索引在任何时候都不能避免表扫描。
    Hash 索引是将索引键通过 Hash 运算之后,将 Hash运算结果的 Hash 值和所对应的行指针信息存放于一个 Hash 表中,由于不同索引键存在相同 Hash 值,所以即使取满足某个 Hash 键值的数据的记录条数,也无法从 Hash
    索引中直接完成查询,还是要通过访问表中的实际数据进行相应的比较,并得到相应的结果。
  • (5)Hash 索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高。
    对于选择性比较低的索引键,如果创建 Hash 索引,那么将会存在大量记录指针信息存于同一个 Hash 值相关联。这样要定位某一条记录时就会非常麻烦,会浪费多次表数据的访问,而造成整体性能低下。

5.索引设置的规则
1)应该创建索引的列

  • 经常搜索的列、主键的列、连接的列、根据范围进行搜索的列、需要排序的列上、使用在WHERE子句中的列上。

2)不应该创建索引的列

  • 1.在查询中很少使用或者参考的列不应该创建索引。
  • 2.只有很少数据值的列也不应该增加索引。
  • 3.对于那些定义为text, image和bit数据类型的列不应该增加索引。
  • 4.当修改性能远远大于检索性能时,不应该创建索引。

6.索引失效

  • 如果条件中有or。注意:要想使用or,又想让索引生效,只能将or条件中的每个列都加上索引。
  • 对于多列索引,不是使用的第一部分,则不会使用索引(即不符合最左前缀原则)
  • like查询是以%开头
  • 如果列类型是字符串,那一定要在条件中将数据使用引号引用起来,否则不使用索引
  • 如果mysql估计使用全表扫描要比使用索引快,则不使用索引

7.最左前缀原则
多列索引(联合索引)有最左前缀的原则,即最左优先。

已标记关键词 清除标记
©️2020 CSDN 皮肤主题: 编程工作室 设计师:CSDN官方博客 返回首页