数据库整理

发表于 | 分类于

什么是存储过程？有什么优缺点？

存储过程是一些预编译的SQL语句。也就是说存储过程是一个记录集，它是由一些T-SQL语句组成的代码块，这些T-SQL语句代码像一个方法一样实现一些功能（比如对单表的CRUD），然后再给这个代码块取一个名字，在用到这个功能的时候调用就可以。

  • 存储过程是一个预编译的代码块，执行效率比较高
  • 一个存储过程替代大量T-SQL语句，可以降低网络通信量，提高通信速率。
  • 可以一定程度上确保数据安全。

索引是什么？有什么作用及优缺点？

  • 索引是对数据库表中一列或多列的值进行排序的结构，是帮助MySQL高效获取数据的数据结构。

  • 索引就是加快检索表中数据的方法。数据库的索引类似于书籍的索引。在书籍中，索引允许用户不必翻阅整个书就能迅速地找到所需要的信息。在数据库中，索引也允许数据库程序迅速地找到表中的数据，而不必扫描整个数据库。

  • MySQL数据库几个基本的索引类型：

    • 普通索引：没有任何限制，最基本的索引，创建方式有以下几种

      • 直接创建索引：CREATE INDEX index_name ON table(column(length))

      • 修改表结构的方式添加索引：ALTER TABLE table_name ADD INDEX index_name ON (column(length))

      • 创建表的时候创建索引：

        1
        2
        3
        4
        5
        6
        7
        8
        CREATE TABLE `table` (
          `id` int(11) NOT NULL AUTO_INCREMENT ,
          `title` char(255) CHARACTER NOT NULL ,
          `content` text CHARACTER NULL ,
          `time` int(10) NULL DEFAULT NULL ,
          PRIMARY KEY (`id`),
          INDEX index_name (title(length))
          )

      • 删除索引：DROP INDEX index_name ON table

    • 主键索引：一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般在建表的同时创建主键索引：

      1
      2
      3
      4
      5
      CREATE TABLE `table` (
          `id` int(11) NOT NULL AUTO_INCREMENT ,
          `title` char(255) NOT NULL ,
          PRIMARY KEY (`id`)
      );

    • 唯一索引：索引列的值必须是唯一的，允许有控制。如果是组合索引，则列的组合必须唯一，创建方式：

      • 创建唯一索引：CREATE UNIQUE INDEX indexName ON table(column(length))

      • 修改表结构的时候指定唯一索引：ALTER TABLE table_name ADD UNIQUE indexName ON (column(length))

      • 创建表的时候直接指定：

        1
        2
        3
        4
        5
        6
        7
        CREATE TABLE `table` (
            `id` int(11) NOT NULL AUTO_INCREMENT ,
            `title` char(255) CHARACTER NOT NULL ,
            `content` text CHARACTER NULL ,
            `time` int(10) NULL DEFAULT NULL ,
            UNIQUE indexName (title(length))
        );

    • 组合索引：多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用，使用组合索引时遵循最左前缀集合：ALTER TABLEtableADD INDEX name_city_age (name,city,age);

    • 全文索引:主要用来查找文本中的关键字，而不是直接参与索引中的值的比较。fulltext索引和其他索引不太一样，更像一个搜索引擎，而不是一个简单的where语句的参数匹配。fulltext索引配合match against操作使用，而不是一般的where语句加like。全文索引可以在建表，修改表，创建索引中使用，不过目前只有cahr,varchar,text列上可以创建全文索引。而当数据量较大时，将现有数据放入一个没有全局索引的表中，然后再create index创建全文索引，要比先为一张表创建全文索引然后再将数据写入速度快很多。

      • 创建表时添加全文索引:

        1
        2
        3
        4
        5
        6
        7
        8
        CREATE TABLE `table` (
            `id` int(11) NOT NULL AUTO_INCREMENT ,
            `title` char(255) CHARACTER NOT NULL ,
            `content` text CHARACTER NULL ,
            `time` int(10) NULL DEFAULT NULL ,
            PRIMARY KEY (`id`),
            FULLTEXT (content)
        );

      • 修改表结构添加全文索引：ALTER TABLE article ADD FULLTEXT index_content(content)

      • 直接创建索引：CREATE FULLTEXT INDEX index_content ON article(content)

  • 优缺点：

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

什么是事务？

  • 事务是并发控制的基本单位。所谓的事务，它指的是一个操作序列，这些操作要么都执行，要么都不执行。它是一个不可分割的工作单位。事务是数据库维护数据一致性的单位，在每个事务结束时，都能保持数据的一致性。

数据库的乐观锁AND悲观锁是什么？

  • 数据库管理系统DBMS中并发控制的任务是确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性和统一性以及数据库的统一性。
  • 乐观锁和悲观锁是并发控制主要采用的技术手段：
    • 乐观锁：假定不会发生并发冲突，只在提交操作时检查是否违反数据完整性。
    • 悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作。

使用索引查询一定能提高查询的性能吗？为什么？

  • 通常来说，通过索引查询数据比全表扫描要快。
  • 但是索引需要空间来存储，也需要定期维护。每当有记录在表中增减或者索引列被修改时，索引本身也会被修改。意味着每条记录的insert，delete，update方法将会为此多付出4,5次的磁盘IO。
  • 因为索引需要额外的存储空间和处理，那些不必要的索引反而会使查询反应时间变慢。使用索引不一定能提高查询性能。

  • 索引范围性查找适用于两种情况：

    • 基于一个范围的检索，一般返回结果集小于表中记录的30%。
    • 基于唯一性索引的检索。

MySQL数据库索引失效的原因：

  • 对单字段建立了索引，where条件多字段
  • 建立了联合索引，where条件单字段
  • 对索引列运算，运算包括（+、-、*、/、！、<>、%、like’%_‘、or、in、exist）导致索引失效
  • 类型错误，如字段类型为varchar，where条件用number
  • 对应索引内部函数，这种情况下应该建立基于函数的索引
  • 查询表的效率要比查询索引快的情况
  • is null索引失效，is not null betree索引生效。

说一下drop、delete、truncate的区别：

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

drop、delete、truncate的使用场景？

  • drop，不再需要一张表时
  • delete，向删除部分数据行时，并带上where子句
  • truncate，保留表结构而删除所有数据的时候

超键，候选键，主键，外键分别是什么

GET
超键 在关系中能唯一标识元组的属性集成为关系模式的超键。一个属性可以作为一个超键，多个属性组合在一起也可以作为一个超键。超键包含候选键和主键
候选键 是最小的超键，即没有冗余元素的超键
主键 数据库表中对存储数据对象给予唯一和完整标识的数据列或者属性的集合。一个数据列只能有一个主键，且主键的取值不能缺少，即不能为空值（null）
外键 在一个表中存在另一个表的主键称为此表的外键

什么是视图？以及视图的使用场景？

  • 视图是一张虚拟的表，具有和物理表相同的功能。可以对视图进行CRUD，视图通常是由一个表或者多个表的行或列的子集。对视图的修改不影响基本表。它使得我们获取数据更容易，相比多表查询。

  • 只暴露部分字段给访问者，所以就建一个虚表，也就是一个视图。

  • 查询的数据来源于不同的表，而查询者希望以统一的方式查询，这样可以建立一个视图，把多个表查询结果联合起来，查询者只需要直接从视图中获取数据，不必考虑数据来源于不同表中的差异。

数据库三范式

  • 1NF：数据库表中的字段都是单一属性，不可再分。这个单一属性由基本类型构成，包括整形，实数，字符型，逻辑型，日期型等。

  • 2NF：数据库表中不存在非关键字段对任意候选关键字段的部分依赖（部分依赖指的是存在组合关键字中的某些字段决定非关键字段的情况），也即所有非关键字段都完全依赖于任意一组候选关键字。

  • 3NF：数据表中不存在非关键字段对任一候选关键字段的传递依赖。传递依赖指的数据库中不存在如下依赖关系：关键字段->非关键字段X->非关键字段Y

SQL语句总结

  1. 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中，与 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：排序指令。语法为：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" "；列1别名" FROM "表格名" "表格别名"

  • 实战，数据库结构如下

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

1. 查询“001”课程比“002”课程成绩高的所有学生的学号：


1
2
3
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分的同学的学号和平均成绩：


1
2
3
SELECT S#, AVG(score)
FROM SC
GROUP by S# HAVING AVG(score) > 60;


3. 查询所有同学的学号、姓名、选课数、总成绩:


1
2
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. 查询姓“李”的老师的个数:


1
2
3
select COUNT(distinct(Tname))
from Teacher
WHERE Tname LIKE '李%'；


5. 查询没学过“叶平”老师课的同学的学号、姓名；


1
2
3
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”课程的同学的学号、姓名:


1
2
3
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. 查询学过“叶平”老师所教的所有课的同学的学号、姓名:


1
2
3
4
5
6
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分的同学的学号、姓名:


1
2
3
select S#,Sname
from Student
where S# not in (select Student.S# from Student,SC where S.S#=SC.S# and score>60);


9. 查询没有学全所有课的同学的学号、姓名:


1
2
3
4
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”的同学所学相同的同学的学号和姓名:


1
2
3
select S#,Sname
from Student,SC
where Student.S#=SC.S# and C# in （select C# from SC where S#='1001'）;


11. 删除学习“叶平”老师课的SC表记录:


1
2
3
Delect SC
from course ,Teacher
where Course.C#=SC.C# and Course.T#= Teacher.T# and Tname='叶平';


12. 查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分

  
1
2
3
4
5
6
7
8
9
10
11
12
13
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. 查询学生平均成绩及其名次:
  
1
2
3
4
5
6
7
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. 查询各科成绩前三名的记录:(不考虑成绩并列情况)

  
1
2
3
4
5
6
7
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. 查询每门功成绩最好的前两名
  
1
2
3
4
5
6
7
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#;
Donate comment here