数据库与SQL知识汇总/面试真题解答

数据库与SQL

索引

我们常见的数据库系统，其索引使用的数据结构多是B-Tree或者B+Tree。例如，MsSql使用的是B+Tree，Oracle及Sysbase使用的是B-Tree。

什么是索引
在数据库中，索引是用于提高数据库表数据访问速度的数据库对象。
索引的作用

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

索引越多越好吗?
不是，建立索引需要消耗内存，数据量多的时候，索引占用空间也会大，创建索引要消耗空间。
如果内存足够呢，不考虑消耗空间？
索引建多了对插入和删除有影响

创建索引
在执行CREATE TABLE语句时可以创建索引，也可以单独用CREATE INDEX或ALTER TABLE来为表增加索引。

  • ALTER TABLE 用来创建普通索引、唯一索引或主键索引。
ALTER TABLE table_name ADD INDEX index_name (column_list)
ALTER TABLE table_name ADD UNIQUE (column_list)
ALTER TABLE table_name ADD PRIMARY KEY (column_list)

table_name是要增加索引的表名，column_list指出对哪些列进行索引，多列时各列之间用逗号分隔。
索引名index_name可选，缺省时，MySQL将根据第一个索引列赋一个名称。
另外，ALTER TABLE允许在单个语句中更改多个表，因此可以在同时创建多个索引。

  • CREATE INDEX 对表增加普通索引或唯一索引。
CREATE INDEX index_name ON table_name (column_list)
CREATE UNIQUE INDEX index_name ON table_name (column_list)

索引名index_name不可选。

删除索引
可利用ALTER TABLE或DROP INDEX语句来删除索引。类似于CREATE INDEX语句，DROP INDEX可以在ALTER TABLE内部作为一条语句处理，语法如下。

DROP INDEX index_name ON talbe_name
ALTER TABLE table_name DROP INDEX index_name
ALTER TABLE table_name DROP PRIMARY KEY

其中，前两条语句是等价的，删除掉table_name中的索引index_name。
第3条语句只在删除PRIMARY KEY索引时使用，因为一个表只可能有一个PRIMARY KEY索引，因此不需要指定索引名。如果没有创建PRIMARY KEY索引，但表具有一个或多个UNIQUE索引，则MySQL将删除第一个UNIQUE索引。

如果从表中删除了某列，则索引会受到影响。对于多列组合的索引，如果删除其中的某列，则该列也会从索引中删除。如果删除组成索引的所有列，则整个索引将被删除。

** 查看索引**

mysql> show index from talbe_name;
mysql> show keys from talbe_name;

索引的分类

  • 唯一索引
    唯一索引是不允许其中任何两行具有相同索引值的索引。
    当现有数据中存在重复的键值时，大多数数据库不允许将新创建的唯一索引与表一起保存。数据库还可能防止添加将在表中创建重复键值的新数据。例如，如果在employee表中职员的姓(lname)上创建了唯一索引，则任何两个员工都不能同姓。
  • 主键索引
    数据库表经常有一列或列组合，其值唯一标识表中的每一行。该列称为表的主键。
    在数据库关系图中为表定义主键将自动创建主键索引，主键索引是唯一索引的特定类型。该索引要求主键中的每个值都唯一。当在查询中使用主键索引时，它还允许对数据的快速访问。
  • 聚集索引，表数据按照索引的顺序来存储的。对于聚集索引，叶子结点即存储了真实的数据行，不再有另外单独的数据页。
  • 非聚集索引，表数据存储顺序与索引顺序无关。对于非聚集索引，叶结点包含索引字段值及指向数据页数据行的逻辑指针，该层紧邻数据页，其行数量与数据表行数据量一致。

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

增删查改操作

  • 插入
    • 聚集索引
      • 插入操作根据索引找到对应的数据页，然后通过挪动已有的记录为新数据腾出空间，最后插入数据。
      • 如果数据页已满，则需要拆分数据页（页拆分是一种耗费资源的操作，一般数据库系统中会有相应的机制要尽量减少页拆分的次数，通常是通过为每页预留空间来实现）
    • 非聚集索引
      • 如果一张表包含一个非聚集索引但没有聚集索引，则新的数据将被插入到最末一个数据页中，然后非聚集索引将被更新。
      • 如果也包含聚集索引，该聚集索引将被用于查找新行将要处于什么位置，随后，聚集索引、以及非聚集索引将被更新。
  • 删除
    • 聚集索引
      • 删除行将导致其下方的数据行向上移动以填充删除记录造成的空白。
      • 如果删除的行是该数据页中的最后一行，那么该数据页将被回收，相应的索引页中的记录将被删除。如果回收的数据页位于跟该表的其它数据页相同的段上，那么它可能在随后的时间内被利用。如果该数据页是该段的唯一一个数据页，则该段也被回收。
    • 非聚集索引
      • 如果在删除命令的Where子句中包含的列上，建有非聚集索引，那么该非聚集索引将被用于查找数据行的位置，数据删除之后，位于索引叶子上的对应记录也将被删除。如果该表上有其它非聚集索引，则它们叶子结点上的相应数据也要删除。
      • 如果删除的数据是该数所页中的唯一一条，则该页也被回收，同时需要更新各个索引树上的指针。
      • 由于没有自动的合并功能，如果应用程序中有频繁的随机删除操作，最后可能导致表包含多个数据页，但每个页中只有少量数据。

对于数据的删除操作，可能导致索引页中仅有一条记录，这时，该记录可能会被移至邻近的索引页中，原索引页将被回收，即所谓的“索引合并”。

SQL删除语句

  • drop table TNAME // 删除的是整个表，删除表没有确认，也不能撤销
  • delete from table TNAME //删除表中数据
  • truncate from table TNAME //删除数据（不可恢复）
  • alter table TNAME //删除表中约束
  • drop constraint CNAME//删除约束

sql中drop、truncate和delete的区别

DropTruncateDelete
语言类型DDL（数据库定义语言)DDL（数据库定义语言)DML（数据库操作语言)
是否回滚立即生效，不能回滚立即生效，不能回滚提交后才生效，可以回滚
是否可恢复不可以不可以可以
操作速度最快其次最慢
应用范围表表和视图
删除内容整个表（结构和数据）只删除数据只删除数据（不带where的Delete）
删除后的空间全部释放掉恢复到初始大小不变
安全性不好不好好
  • 要删除部分数据行采用delete且注意结合where来约束影响范围。回滚段要足够大。
  • 要删除表用drop
  • 若想保留表而将表中数据删除，如果于事务无关，用truncate即可实现。如果和事务有关，或老师想触发trigger,还是用delete。
  • Truncate 速度快,而且效率高,因为truncate table 在功能上与不带 WHERE 子句的 DELETE 语句相同：二者均删除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少。DELETE 语句每次删除一行，并在事务日志中为所删除的每行记录一项。TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。

SQL查询语句

  • select语句
    在MySQL数据库中查询数据通用的 SELECT 语法：
    SELECT column_name,column_name
    FROM table_name
    [WHERE Clause]
    [LIMIT N][ OFFSET M]
select * from runoob_tbl;

select的执行顺序：from，where，group ,having ,(select)order by,limit
在使用 limit 子句时，如果没有足够的行，则MySQL将只返回它能返回的那么多行

  • where 子句
    如需有条件地从表中选取数据，可将 WHERE 子句添加到 SELECT 语句中。
    SELECT field1, field2,…fieldN FROM table_name1, table_name2…
    [WHERE condition1 [AND [OR]] condition2…
    MySQL 的 WHERE 子句的字符串比较是不区分大小写的。 你可以使用 BINARY 关键字来设定 WHERE 子句的字符串比较是区分大小写的。
SELECT * from runoob_tbl WHERE runoob_author='菜鸟教程';
SELECT * from runoob_tbl WHERE BINARY runoob_author='RUNOOB.COM';
  • order by子句
    使用 MySQL 的 ORDER BY 子句来设定你想按哪个字段哪种方式来进行排序
    SELECT field1, field2,…fieldN FROM table_name1, table_name2…
    ORDER BY field1 [ASC [DESC][默认 ASC]], [field2…] [ASC [DESC][默认 ASC]]
SELECT * from runoob_tbl ORDER BY submission_date ASC; //或DESC

约束

约束和索引
约束和索引， 前者是用来检查数据的正确性，后者用来实现数据查询的优化，目的不同。

  1. 唯一性约束与唯一索引有所不同：
    （1）创建唯一约束会在Oracle中创建一个Constraint，同时也会创建一个该约束对应的唯一索引。
    （2）创建唯一索引只会创建一个唯一索引，不会创建Constraint。
    也就是说其实唯一约束是通过创建唯一索引来实现的。
    （3）在删除时这两者也有一定的区别，删除唯一约束时可以只删除约束而不删除对应的索引，所以对应的列还是必须唯一的，而删除了唯一索引的话就可以插入不唯一的值
  2. 唯一性约束和主键约束的区别：
    （1）唯一性约束允许在该列上存在NULL值，而主键约束的限制更为严格，不但不允许有重复，而且也不允许有空值。
    （2）在创建唯一性约束和主键约束时可以创建聚集索引和非聚集索引，但在 默认情况下主键约束产生聚集索引，而唯一性约束产生非聚集索引
    主键约束和唯一性约束
  3. 主键约束（PRIMARY KEY）
    主键用于唯一地标识表中的每一条记录，可以定义一列或多列为主键。
    是不可能（或很难）更新．
    主键列上没有任何两行具有相同值（即重复值），不允许空（NULL）．
    主健可作外健，唯一索引不可；
  4. 唯一性约束（UNIQUE）
    唯一性约束用来限制不受主键约束的列上的数据的唯一性，用于作为访问某行的可选手段，一个表上可以放置多个唯一性约束．
    只要唯一就可以更新．
    即表中任意两行在 指定列上都不允许有相同的值，允许空（NULL）．
    一个表上可以放置多个唯一性约束
  5. 唯一索引（INDEX）
    创建唯一索引可以确保任何生成重复键值的尝试都会失败。

手写SQL

面试真题

  1. SQL使用having
  2. 数据库外键
  3. 创建索引、索引的分类
  4. 索引的作用，索引何时无效
  5. 索引和存储过程
  6. 数据库增删查改
  7. Drop和Delete的区别
  8. MYSQL关系型和非关系的有什么区别

非关系型数据库：1. 性能是基于键值对的，而且不需要经过SQL层的解析，所以性能非常高。2. 可扩展性好，同样也是因为基于键值对，数据之间没有耦合性，所以非常容易水平扩展。
关系型数据库：1. 复杂查询可以用SQL语句方便的在一个表以及多个表之间做非常复杂的数据查询。2. 事务支持使得对于安全性能很高的数据访问要求得以实现。

  1. MYSQL 查询语句 where 两张表的连接 降序排序 MySQL熟悉哪些函数
  2. MYSQL索引作用是什么，建立索引的原则，索引失效，索引建的越多越好吗，索引的底层结构
  3. 建立一个表，id,name,id自增长，且为主键
    在原来的表上增加一列，创建时间列，并获取当前系统时间
    找出创建时间在某个时间范围以内，并按照创建时间降序排列
    在原来的表上增加一百条数据，名字字段每条后边加个0，比如第一个是name,下边是name0,name00…
  4. 从一张用户信息表中统计出年龄最大的10个人（limit+order by）
  5. 所有课每一门成绩都大于90的学生
    　　 Select sname from s,sc where s.sno=sc.sno
    group by s.sno,sname having min(grade)>=90
  6. 数据库里面 选出语文最高分

更多推荐

数据库与SQL知识汇总/面试真题解答