数据库索引(约束种类)
数据库常见问题总结
数据库常见问题总结1.常见的关系型数据库和⾮关系型数据库？ 关系型数据库(需要表结构)：sqllite、db2、oracle、access、SQLserver、MySQL ⾮关系型数据库(key-value结构存储，没有表结构)：mongodb、redis、memcache2.常见的数据库引擎⽐较？1>.InnoDB⽀持事物，⽽MyISAM不⽀持事物2>.InnoDB⽀持⾏级锁，⽽MyISAM⽀持表级锁3>.InnoDB⽀持MVCC, ⽽MyISAM不⽀持4>.InnoDB⽀持外键，⽽MyISAM不⽀持5>.InnoDB不⽀持全⽂索引，⽽MyISAM⽀持。
3.数据库设计三⼤范式第⼀范式：1NF是对属性的原⼦性约束，要求属性具有原⼦性，不可再分解；第⼆范式：2NF是对记录的惟⼀性约束，要求记录有惟⼀标识，即实体的惟⼀性；第三范式：3NF是对字段冗余性的约束，即任何字段不能由其他字段派⽣出来，它要求字段没有冗余范式化设计优缺点:优点:可以尽量得减少数据冗余，使得更新快，体积⼩缺点:对于查询需要多个表进⾏关联，减少写得效率增加读得效率，更难进⾏索引优化4.事务，以及mysql如何⽀持事务？'什么是事务'事务由⼀个或多个sql语句组成⼀个整体；在事务中的操作，要么都执⾏修改，要么都不执⾏，只有在该事务中所有的语句都执⾏成功才会将修改加⼊到数据库中，否则回滚到上⼀步。
'Mysql实现事务'InnoDB⽀持事务，MyISAM不⽀持# 启动事务：# start transaction；# update from account set money=money-100 where name='a';# update from account set money=money+100 where name='b';# commit；'start transaction ⼿动开启事务，commit ⼿动关闭事务'5.数据库五⼤约束主键约束 primary key默认值约束 default唯⼀约束 unique⾮空约束not null外键约束 foreign key6.mysql索引种类'主键索引(单列)'：primary key加速查找+约束：不能重复、不能为空'普通索引(单列)':加速查找'唯⼀索引(单列)'：unique加速查找+约束：不能重复'联合索引(多列)'：查询时根据多列进⾏查询(最左前缀)'联合唯⼀索引(多列)'：遵循最左前缀规则(命中索引)# 其他词语1、索引合并：利⽤多个单列索引查询2、覆盖索引：在索引表中就能将想要的数据查询到7.索引什么情况下遵循最左前缀原则 联合索引A,B,C,D 只要出现A，不管顺序如何都会使⽤索引；只要没有A，就不会使⽤索引；具体⽤法参考博客：8.创建索引但是⽆法命中索引的8种情况#使⽤'like ‘%xx’'select * from tb1 where name like '%cn';#使⽤'函数'select * from tb1 where reverse(name)='zgc';#使⽤'or'select * from tb1 where nid=1 or email='zgc@';特别的：当or条件中有未建⽴索引的列才失效，⼀下会⾛索引# select * from tb1 where nid=1 or name='zgc';# select * from tb1 where nid=1 or email='zgc@' and name='zgc'; #'类型不⼀致'如果列是字符串类型，传⼊条件是必须⽤引号引起来，不然则可能会⽆法命中 select * from tb1 where name=666；#含有'!= 'select * from tb1 where name != 'zgc';特别的：如果是主键，还是会⾛索引# select * from tb1 where nid != 123;#含有'>'select * from tb1 where name > 'zgc';特别的：如果是主键或者索引是整数类型，则还是会⾛索引# select * from tb1 where nid > 123;# select * from tb1 where name > 123;#含有'order by'select email from tb1 order by name desc;当根据索引排序时，选择的映射如果不是索引，则不⾛索引特别的：如果对主键排序，则还是⾛索引：# select * from tb1 order by nid desc;#组合索引最左前缀如果组合索引为：（name，email）name and email #使⽤索引name #使⽤索引email #不使⽤索引9.开启慢⽇志查询'可以通过修改配置⽂件开启'slow_query_log=ON #是否开启慢⽇志记录long_query_time=2 #时间限制，超过此时间，则记录slow_query_log_file=/usr/slow.log #⽇志⽂件long_queries_not_using_indexes=ON #是否记录使⽤索引的搜索1、创建数据表时把固定长度的放在前⾯2、将固定数据放⼊内存：choice字段(django中⽤到，1,2,3对应相应内容)3、char不可变，varchar可变4、联合索引遵循最左前缀(从最左侧开始检索)5、避免使⽤ select *6、读写分离：#利⽤数据库的主从分离：主，⽤于删除、修改、更新；从，⽤于查#实现：两台服务器同步数据\原⽣SQL:select * from db.tb\ORM：er.object.all().using('default')\路由：d b router7、分库# 当数据库中的表太多，将某些表分到不同数据库，例如：1W张表时# 代价：连表查询跨数据库，代码变多8、分表# ⽔平分表：将某些列拆分到另⼀张表，例如：博客+博客详情# 垂直分表：将某些历史信息，分到另外⼀张表中，例如：⽀付宝账单9、加缓存# 利⽤redis、memcache(常⽤数据放到缓存⾥，提⾼取数据速度)# 缓存不够可能会造成雪崩现象10、如果只想获取⼀条数据select * from tb where name = 'zgc' limit 1;11.1000w条数据，使⽤limit offset 分页时，为什么越往后翻越慢？如何解决？# 例如:#limit 100000,20; 从第⼗万条开始往后取⼆⼗条,#limit 20 offset 100000; limit后⾯是取20条数据,offset后⾯是从第10W条数据开始读因为当⼀个数据库表过于庞⼤，LIMIT offset, length中的offset值过⼤，则SQL查询语句会⾮常缓慢--------------------------------------------------------------------------'优化⼀'先查看主键，再分页：select * from tb where id in (select id from tb where limit 10 offset 30)--------------------------------------------------------------------------'优化⼆'记录当前页，数据、ID、最⼤值和最⼩值(⽤于where查询)在翻页时，根据条件进⾏筛选，筛选完毕后，再根据 limit offset 查询select * from(select * from tb where id > 2222) as B limit 10 offset 0;\如果⽤户⾃⼰修改页码，也可能导致变慢，此时可以对 url 页码进⾏加密，例如rest framework--------------------------------------------------------------------------'优化三'可以按照当前业务需求，看是否可以设置只允许看前200页；⼀般情况下，没⼈会咔咔看个⼏⼗上百页的；12.什么是索引合并？ 索引合并，让⼀条sql可以使⽤多个索引。
12-索引(SQL Server)
203 101 104 104 110 BC CB AC CS IS IS E07 E08 E09 BB AD BD IS CS IS E07 E10 110 111 111 E10 E11 E12 BA CC CA IS CS CS
中间级
101
E01 E04
叶级 数据页
E01 E02 E03
AB AA BB
索引
杨之江
yangzhijiang@
内容
索引基本概念 索引分类 创建索引 查看和删除索引 索引调整向导 Q&A
用户对数据库最频繁的操作是进行数据 查询，一般情况下数据库在进行查询操 作时需要对整个表进行数据搜索 当表中的数据很多时，搜索数据就需要 很长的时间，这就造成了服务器的资源 浪费。 为了提高检索数据的能力数据库引入了 索引机制
唯一索引
可以确保索引列不包含重复的值 在组合唯一索引的情况下，该索引可以确 保索引列中每个值的组合都是唯一的 例如，如果在last_name、first_name 和middle_initial列的组合上创建了唯一 索引full_name，则该表中任何两个人都 不可以具有完全相同的名字 聚簇索引和非聚簇索引都可以是唯一的。
1 索引基本概念
索引是一个单独的、物理的数据库结构 索引是某个表中一列或若干列值的集合和相应的指 向表中物理标识这些值的数据页的逻辑指针清单 索引是依赖于表建立的，它提供了数据库中编排表 中数据的内部方法 索引使对数据的查找不需要对整个表进行扫描，就 可以在其中找到所需数据。 可以为表中的单个列建立索引，也可以为一组列 （索引项）建立索引。 索引一般采用B树结构
CS CS IS
E04 E05 E06
注意
定义聚簇索引时使用的列越少越好 如果定义了一个大型的聚簇索引， 则同一个表上定义的任何非聚簇索 引都将增大许多，因为非聚簇索引 项将包含聚簇索引的全部列
数据库索引的作用及实例
数据库索引的作用及实例首先，数据库索引的主要作用有以下几个方面：1.提高查询速度：数据库索引可以加快数据的检索速度，通过索引时，数据库系统可以直接通过索引定位到所需数据的位置，而不需要遍历整个表，大大减少了查询所需的时间。
2.减少磁盘IO操作：数据库索引可以减少读写磁盘的次数。
当没有索引时，数据库系统需要遍历整个表来查找所需数据，会导致大量的磁盘IO操作。
而有了索引后，数据库系统可以直接通过索引找到所需数据的位置，从而减少了磁盘IO次数。
3.提高排序性能：数据库索引适用于排序操作。
通过索引，数据库系统可以按照特定的排序顺序进行排序。
对于需要频繁进行排序的操作，使用索引可以提高排序的性能。
4.保证数据的唯一性：数据库索引可以用于唯一约束。
通过在列上创建唯一索引，可以保证该列的数值的唯一性，从而避免了数据的冗余和错误。
接下来，我们来看一些数据库索引的实例：1. 聚集索引：在关系数据库中，每个表只能有一个聚集索引。
聚集索引决定了表中数据的物理顺序，并且数据库系统按照该顺序存储数据。
聚集索引通常被创建在主键（primary key）上，可以加速对主键的检索操作。
2.非聚集索引（辅助索引）：非聚集索引是基于表中的列或多列创建的索引，与聚集索引不同，非聚集索引并不决定数据的物理顺序。
非聚集索引可以提高对非主键列的检索效率。
3.唯一索引：唯一索引用于保证列的数值的唯一性。
在创建唯一索引时，数据库系统会自动为该列添加唯一约束，来保证该列的数值的唯一性。
唯一索引可以提高对唯一列的查询和排序操作的性能。
4.全文索引：全文索引用于对文本数据进行全文。
全文索引可以对文本列中的单词或短语进行索引，从而加快全文的速度。
5.复合索引：复合索引是基于多个表列创建的索引。
复合索引可以提高对多列的组合查询的性能。
在创建复合索引时，可以指定多个列的排序顺序，从而根据不同的查询需求来进行优化。
总结起来，数据库索引在提高查询速度、减少磁盘IO操作、提高排序性能和保证数据的唯一性等方面发挥着重要作用。
列举数据表的相关约束
数据表的相关约束有很多种，以下是一些常见的约束类型：主键约束（Primary Key Constraints）：定义：确保某列（或列的组合）中的数据唯一，且不允许NULL 值。
作用：确保数据的唯一性，有助于快速检索记录。
外键约束（Foreign Key Constraints）：定义：在两个表之间建立关系，确保参照完整性。
作用：确保相关表之间的数据一致性。
唯一约束（Unique Constraints）：定义：确保某列（或列的组合）中的数据唯一。
允许NULL 值。
作用：确保数据的唯一性，但不强制非NULL。
检查约束（Check Constraints）：定义：确保某列中的值满足特定的条件。
作用：限制列中的可能值。
非空约束（Not Null Constraints）：定义：确保某列中的值不为NULL。
作用：确保数据完整性，某些列必须有值。
默认值约束（Default Constraints）：定义：为某列设置默认值。
作用：当插入新记录时，如果没有为该列指定值，则使用默认值。
级联更新和删除（Cascade Update and Delete）：当主表中的数据发生变化时，相关的外键数据也自动变化或删除。
触发器约束（Trigger Constraints）：基于特定的事件（如INSERT、UPDATE 或DELETE），当满足某些条件时自动执行的操作。
索引约束：虽然不是直接约束，但索引可以帮助确保数据的快速检索和完整性。
例如，唯一索引可以确保某列中的数据唯一。
自定义约束：某些数据库系统允许用户定义自己的约束逻辑，以适应特定的业务规则和需求。
当设计数据库和数据表时，正确使用这些约束可以大大提高数据的完整性和准确性，同时确保应用程序中数据的可靠性。
数据库的四种类型的完整性约束
数据库的四种类型的完整性约束数据完整性：是指存储在数据库中的所有数据值均正确的状态。
四种类型的完整性约束：1、实体完整性约束：表中的每⼀⾏数据都反映不同的实体,不能存在相同的数据⾏.通过索引.唯⼀约束,主键约束或标识列属性,可以⽤来实现表的实体完整性.索引：将⽂献中具有检索意义的事项（可以是⼈名、地名、词语、概念、或其他事项）按照⼀定⽅式有序编排起来，以供检索的⼯具书。
数据库索引好⽐是⼀本书前⾯的⽬录，能加快数据库的查询速度。
例如这样⼀个查询：select * from table1 where id=44。
如果没有索引，必须遍历整个表，直到ID等于44的这⼀⾏被找到为⽌；有了索引之后(必须是在ID这⼀列上建⽴的索引)，直接在索引⾥⾯找44（也就是在ID这⼀列找），就可以得知这⼀⾏的位置，也就是找到了这⼀⾏。
可见，索引是⽤来定位的。
唯⼀约束：⽐如⼀张表的学⽣姓名设置了唯⼀约束，那么学⽣的姓名就是不能重复的，只能是唯⼀的唯⼀约束保证在⼀个字段或者⼀组字段⾥地数据与表中其它⾏的数据相⽐是唯⼀的附上与主键约束的区别，以更好理解创建唯⼀约束可确保在未参与主键的特定列中不输⼊重复值。
尽管唯⼀约束和主键都强制唯⼀性，但如果情况如下，则应向表附加唯⼀约束⽽不是主键约束：希望在某⼀列或某⼀组列中强制唯⼀性。
您可以向表附加多个唯⼀约束，但只能向表附加⼀个主键约束。
希望在允许空值的列中强制唯⼀性。
您可以向允许空值的列附加唯⼀约束，但只能向不允许空值的列附加主键约束。
当向允许空值的列附加唯⼀约束时，请确保在所约束的列中最多只有⼀⾏包含空值。
2、域完整性约束：指给定列的输⼊有效性通过限制数据类型,检查约束,输⼊格式,外键约束,默认值,⾮空约束等多种⽅法,可以⽤来实现表的域完整性.3、参照完整性约束：在输⼊或删除数据⾏时,引⽤完性约束⽤来保持表之间已定义的关系4：、⽤户定义的完整性约束：⽤来定义特定的规则,例如,向⽤户信息表中插⼊⼀个⽤户记录时,要求通过⾝份证编号来检查另外⼀个数据库中是否存在该⽤户,并且该⽤户的信誉度是否满⾜要求等.如果不满⾜要求则不能够插⼊,这个时候就需要使⽤数据库的规则存储过程或者触发器来进⾏约束。
数据库索引的种类
数据库索引的种类数据库索引是数据库中提高查询效率的一种重要手段，它可以帮助数据库快速查找到需要的数据，提高数据查询的效率和准确性。
在数据库中，索引可以按照不同的属性进行分类。
一、单列索引单列索引是最普及的索引类型之一，也是最简单的索引类型。
它只涉及到一个字段或一个列，基本实现原理就是为查询语句中涉及到的字段或列创建索引，通过索引找到数据记录并返回给用户。
常见的单列索引包括B-Tree索引、Hash索引、全文索引等。
1.B-Tree索引：B-Tree索引使用B-Tree数据结构进行排序以减少磁盘I/O操作次数，通常用于比较简单的查询条件，如等于、大于、小于等操作，对于范围查询和排序操作，B-Tree索引效果较差。
2.Hash索引：Hash索引使用哈希表进行操作，它对于等值查询效果很好，但不适合范围查询、模糊查询等操作。
3.全文索引：全文索引可以识别文本中的单词和单词的位置，将文本按照单词进行索引，通常用于文本搜索操作。
二、联合索引联合索引也称为复合索引，指针是在多列上建立的索引，这种索引比单列索引更适合复杂查询。
常见的联合索引包括B-Tree索引、R-Tree索引、Hash索引等。
1.B-Tree索引：在B-Tree索引中建立的联合索引，必须先按照前面的字段排序，然后再按照后面的字段排序，索引的效率与字段的排序非常相关。
2.R-Tree索引：R-Tree索引是一种多维空间索引结构，它能够有效处理空间数据类型的查询问题，比如地理位置查询。
3.Hash索引：联合Hash索引也是使用哈希表进行操作，只不过会把多个字段进行哈希计算，再将它们合并成一个哈希值，通常用于需要快速访问的数据表。
三、唯一索引唯一索引是一个不允许重复数据的索引，它可以避免数据表中出现重复数据的情况。
常见的唯一索引包括B-Tree唯一索引、Hash唯一索引等。
1.B-Tree唯一索引：当创建B-Tree唯一索引时，所有的值都被唯一索引所占据，并且该索引可以加速唯一值约束的检查。
数据库constraint语句
数据库constraint语句数据库constraint语句是用于定义和实施数据完整性规则的重要工具。
下面是满足要求的10个constraint语句的例子，每个例子都包含了清晰的描述和适当的格式。
1. 唯一约束（Unique Constraint）：确保某个列中的值是唯一的，不允许重复。
例如，可以使用以下语句创建一个唯一约束来确保学生表中的学号是唯一的：```sqlALTER TABLE 学生表ADD CONSTRAINT 唯一学号 UNIQUE (学号);```2. 非空约束（Not Null Constraint）：确保某个列中的值不为空。
例如，可以使用以下语句创建一个非空约束来确保员工表中的姓名不为空：```sqlALTER TABLE 员工表MODIFY 姓名 NOT NULL;```3. 主键约束（Primary Key Constraint）：将一个或多个列定义为表的主键，确保每行数据都有唯一的标识。
例如，可以使用以下语句创建一个主键约束来定义订单表的订单号为主键：```sqlALTER TABLE 订单表ADD CONSTRAINT 主键订单号 PRIMARY KEY (订单号);```4. 外键约束（Foreign Key Constraint）：确保一个表中的数据与另一个表中的数据保持一致性。
例如，可以使用以下语句创建一个外键约束来确保订单表中的客户ID在客户表中存在：```sqlALTER TABLE 订单表ADD CONSTRAINT 外键客户ID FOREIGN KEY (客户ID)REFERENCES 客户表(客户ID);```5. 检查约束（Check Constraint）：定义一个条件，确保插入或更新的数据满足特定的条件。
例如，可以使用以下语句创建一个检查约束来确保员工表中的年龄在18到60之间：```sqlALTER TABLE 员工表ADD CONSTRAINT 检查年龄 CHECK (年龄 BETWEEN 18 AND 60);```6. 默认约束（Default Constraint）：为某个列定义一个默认值，当插入新行时，如果没有提供该列的值，则使用默认值。
《数据库原理与应用》课后习题参考答案
《数据库原理与应用》课后习题参考答案《数据库原理与应用》课后习题参考答案第一章作业参考答案1. 单选题 C C D B C2. 判断题对错错错对3填空题网状模型用户商业智能数据挖掘系统设计4简答题1）数据模型是指描述事物对象的数据组成、数据关系、数据约束的抽象结构及其说明。
数据模型是指描述事物对象的数据组成、数据关系、数据约束的抽象结构及其说明。
数据模型是指描述事物对象的数据组成、数据关系、数据约束的抽象结构及其说明。
3）数据约束：用于描述数据结构中数据之间的语义联系、数据之间的制约和依存关系，以及数据动态变化的规则。
主流数据库采用关系图模型。
数据库典型数据模型：层次数据模型网状数据模型关系数据模型其它数据模型（如对象数据模型、键值对数据模型、列式数据模型。
）2）数据库——是一种依照特定数据模型组织、存储和管理数据的文件，数据库文件一般存放在辅助存储器以便长久保存。
数据库具有如下特点：数据不重复存放；提供给多种应用程序访问；数据结构独立于使用它的应用程序；对数据增、删、改、检索由统一软件进行管理和控制。
3）数据库(Database)是一种依照特定模型组织、存储和管理数据的数据结构。
在数据库中，不仅存放了数据，而且还存放了数据与数据之间的关系。
数据库内部元素：用户表：用户在数据库中创建的数据库表；系统表：数据库中系统自带的数据库表；视图：数据库中用于对数据进行查询的虚拟表；索引：数据库中用于加快数据查询的索引项；约束：数据库中对数据、数据关系施加的规则；存储过程：数据库内部完成特定功能处理的程序；触发器：数据库内部因数据变化自动执行的一类存储过程等等4）数据库系统包括：用户、数据库应用程序、数据库管理系统和数据库四个组成要素。
5）数据库管理系统（Database Manage System，DBMS ）——是一种专门用来创建数据库、管理数据库、维护数据库，并提供对数据库访问的系统软件。
数据库管理系统（DBMS）主要功能：创建数据库和表; 创建支持结构,如索引等; 读取数据库数据 ; 修改数据库数据; 维护数据库结构; 执行规则; 并发控制; 提供安全性; 执行备份和恢复等等第二章作业参考答案1 单选题 C B D A A2. 判断题对对错对错3填空题全外连接数据约束候选键用户定义完整性4简答题外码键1）在关系模型中，使用“关系”来存储“实体”中的数据。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
alter table emp add constraint jfkdsj foreign key (did) references dept面有2列数据，一列是ID一列是DID
id:表示用户号
did：表示购买的产品号
要让emp表中的did列去引用dept表中的id
可以用下面的方法
格式：
alter table表名add constraint约束名称约束类型(列名) references被引用的表名称（列名）
例子：
alter table emp add constraint xxx check(age>20)
______________________________________________________________________
3.unique约束：
这样的约束就是给列的数据追加的不重复的约束类型
格式：
alter table表名add constraint约束名称约束类型（列名）
比方说可以给ename列加个unique，让ename列的数据不重复
例子：
alter table emp add constraint qwe unique(ename)
————————————————————————————————
————————————————————————————————
5.外键约束：
这个有点难理解了，外键其实就是引用
因为主键实现了实体的完整性，
外键实现了引用的完整性，
应用完整性规定，所引用的数据必须存在！
其实就是个引用，
比方说一个表名称叫dept里面有2列数据一列是ID一列是ENAME
id：表示产品的编号
4.默认约束：
意思很简单就是让此列的数据默认为一定的数据
格式：
alter table表名称add constraint约束名称约束类型默认值）for列名
比方说：emp表中的gongzi列默认为10000
alter table emp add constraint jfsd default 10000 for gongzi
例子：
alter table emp add constraint ppp primary key (id)
————————————————————————————————
2.check约束：
就是给一列的数据进行了限制
比方说，年龄列的数据都要大于20的
表名（emp）列名（age）
格式：
alter table表名称add constraint约束名称增加的约束类型（列名）
以下几种约束、并一一列举：
1.主键(primary key)约束：
要对一个列加主键约束的话，这列就必须要满足的条件就是分空
因为主键约束：就是对一个列进行了约束，约束为（非空、不重复）
以下是代码要对一个列加主键，列名为id,表名为emp
格式为：
alter table表格名称add constraint约束名称增加的约束类型（列名）