精华内容
下载资源
问答
  • 元组(tuple)是关系数据库中的基本概念,关系是一张表,表中的每行(即数据库中的每条记录)就是一个元组,每列就是一个属性二维表里,元组也称为行。 性质:元组是关系数据库中的基本概念 释义:数据库中的每...

    什么是元组?

    元组(tuple)是关系数据库中的基本概念,关系是一张表,表中的每行(即数据库中的每条记录)就是一个元组,每列就是一个属性。 在二维表里,元组也称为行。

    性质:元组是关系数据库中的基本概念
    释义:数据库中的每条记录
    定义:笛卡尔积中的每一个元素,叫做一个n元组或简称元组

    什么是笛卡尔积?

    笛卡尔积是指在数学中,两个集合X和Y的笛卡尔积,表示为 X × Y,第一个对象是X的成员 而第二个对象是Y的所有可能有序对的其中一个成员。
    在这里插入图片描述

    元组不支持修改或删除其所包含的元素。

    创建元组的方法

    1、使用括弧创建

    a = (1,2)
    a
    (1, 2)
    

    2、使用逗号创建

    b = 2,3
    b
    (2, 3)
    

    3、使用tuple()函数把其他种类的序列转化为元组

    c = tuple("good")
    c
    ('g', 'o', 'o', 'd')
    

    元组还可以进行切片

    c[:2]+c[2:4]
    ('g', 'o', 'o', 'd')
    

    元组与列表区别

    元组与列表相同,也是容器对象,可以存储不同类型的内容。元组与列表有两个不同点。
    第一个不同点是元组的声明使用小括号,而列表使用方括号,当声明只有一个元素的元组时,需要在这个元素的后面添加英文逗号;
    第二个不同点是元组声明和赋值后,不能像列表一样添加、删除和修改元素,也就是说元组在程序运行过程中不能被修改。

    展开全文
  • 此方案中,首先将数据库中的所有元组安全地分配到组中。 然后,将水印分别嵌入并逐组验证。 通过使用基于里德-所罗门编码技术的嵌入水印,该方法不仅可以检测和定位组级别的修改,而且还可以从数据库修改后的位置...
  • 超键:关系中能唯一标识元组的属性集称为关系模式的超键。一个属性可以为作为一个超键,多个属性组合一起也可以作为一个超键。超键包含候选键和主键。 候选键:是最小超键,即没有冗余元素的超键。 主键:...

    目录

    一、基本概念

    1.主键、外键、超键、候选键

    2.为什么用自增列作为主键

    3.触发器的作用?

    4.什么是存储过程?用什么来调用?

    5.存储过程的优缺点?

    6.存储过程与函数的区别

    7.什么叫视图?游标是什么?

    8.视图的优缺点

    9.drop、truncate、 delete区别

    10.什么是临时表,临时表什么时候删除?

    11.非关系型数据库和关系型数据库区别,优势比较?

    12.数据库范式,根据某个场景设计数据表?

    13.什么是 内连接、外连接、交叉连接、笛卡尔积等?

    14.varchar和char的使用场景?

    15.SQL语言分类

    16.like %和-的区别

    17.count(*)、count(1)、count(column)的区别

    18.最左前缀原则

    19.数据库的连接池

    第一、连接池的建立。

    第二、连接池的管理。

    第三、连接池的关闭。

    总结

    二、索引

    1.什么是索引

    2.索引的作用?它的优点缺点是什么?

    4.哪些列适合建立索引、哪些不适合建索引?

    5.什么样的字段适合建索引

    7.B树和B+树的区别

    8.为什么说B+比B树更适合实际应用中操作系统的文件索引和数据库索引?

    9.聚集索引和非聚集索引区别?

    三、事务

    1.什么是事务?

    2.事务四大特性(ACID)原子性、一致性、隔离性、持久性?

    3.事务的并发?事务隔离级别,每个级别会引发什么问题,MySQL默认是哪个级别?

    4.事务传播行为

    5.嵌套事务

    四、存储引擎

    1.MySQL常见的三种存储引擎(InnoDB、MyISAM、MEMORY)的区别?

    2.MySQL存储引擎MyISAM与InnoDB如何选择

    3.MySQL的MyISAM与InnoDB两种存储引擎在,事务、锁级别,各自的适用场景?

    五、优化

    1.查询语句不同元素(where、jion、limit、group by、having等等)执行先后顺序?

    2.使用explain优化sql和索引?

    3.MySQL慢查询怎么解决?

    4.联合索引、强制使用索引和什么情况下不会索引不生效?

    5.性能优化方法总结

    1.explain可知,尽量避免全表扫描,合理建立索引,要让索引发挥作用:

    2.索引字段字段长度较短的方法:

    3.尽量让索引的记录都是唯一的,这样用索引查询加锁时加的是行锁而不是表锁;

    4.尽量使用自增主键;

    5.一次性事件尽量避免使用临时表,重复多次事件建议使用临时表。避免频繁创建和删除临时表;

    6.用not exists代替not in,子表大的话用exists,子表小的话可以用in。

    7.联合索引等调优方法,尽量select字段而不是select*;

    8.注意可以把where条件前移到from的临时表里;

    9.尽量避免大事务操作,提升系统并发能力。

    六、数据库锁

    1.mysql都有什么锁,死锁判定原理和具体场景,死锁怎么解决?

    2.有哪些锁(乐观锁悲观锁),select 时怎么加排它锁?

    七、其他

    1.数据库的主从复制

    4.数据库崩溃时事务的恢复机制(REDO日志和UNDO日志)?

    八、整理时参考的资料


    一、基本概念

    1.主键、外键、超键、候选键

    超键:在关系中能唯一标识元组的属性集称为关系模式的超键。一个属性可以为作为一个超键,多个属性组合在一起也可以作为一个超键。超键包含候选键和主键。

    候选键:是最小超键,即没有冗余元素的超键。

    主键:数据库表中对储存数据对象予以唯一和完整标识的数据列或属性的组合。一个数据列只能有一个主键,且主键的取值不能缺失,即不能为空值(Null)。

    外键在一个表中存在的另一个表的主键称此表的外键

    2.为什么用自增列作为主键

    如果我们定义了主键(PRIMARY KEY),那么InnoDB会选择主键作为聚集索引、

    如果没有显式定义主键,则InnoDB会选择第一个不包含有NULL值的唯一索引作为主键索引、

    如果也没有这样的唯一索引,则InnoDB会选择内置6字节长的ROWID作为隐含的聚集索引(ROWID随着行记录的写入而主键递增,这个ROWID不像ORACLE的ROWID那样可引用,是隐含的)。

    数据记录本身被存于主索引(一颗B+Tree)的叶子节点上。这就要求同一个叶子节点内(大小为一个内存页或磁盘页)的各条数据记录按主键顺序存放,因此每当有一条新的记录插入时,MySQL会根据其主键将其插入适当的节点和位置,如果页面达到装载因子(InnoDB默认为15/16),则开辟一个新的页(节点)

    如果表使用自增主键,那么每次插入新的记录,记录就会顺序添加到当前索引节点的后续位置,当一页写满,就会自动开辟一个新的页

    如果使用非自增主键(如果身份证号或学号等),由于每次插入主键的值近似于随机,因此每次新纪录都要被插到现有索引页得中间某个位置,此时MySQL不得不为了将新记录插到合适位置而移动数据,甚至目标页面可能已经被回写到磁盘上而从缓存中清掉,此时又要从磁盘上读回来,这增加了很多开销,同时频繁的移动、分页操作造成了大量的碎片,得到了不够紧凑的索引结构,后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面。

    3.触发器的作用?

    触发器是一种特殊的存储过程,主要是通过事件来触发而被执行的。

    (1)它可以强化约束,来维护数据的完整性和一致性,可以跟踪数据库内的操作从而不允许未经许可的更新和变化

    (2)可以级联运算。如,某表上的触发器上包含对另一个表的数据操作,而该操作又会导致该表触发器被触发。

    4.什么是存储过程?用什么来调用?

    存储过程是一个预编译的SQL语句,优点是允许模块化的设计,就是说只需创建一次,以后在该程序中就可以调用多次。如果某次操作需要执行多次SQL,使用存储过程比单纯SQL语句执行要快。

    调用:

    1)可以用一个命令对象来调用存储过程。

    2)可以供外部程序调用,比如:java程序。

    5.存储过程的优缺点?

    优点:

    1)存储过程是预编译过的,执行效率高。

    2)存储过程的代码直接存放于数据库中,通过存储过程名直接调用,减少网络通讯。

    3)安全性高,执行存储过程需要有一定权限的用户。

    4)存储过程可以重复使用,可减少数据库开发人员的工作量。

    缺点:移植性差

    6.存储过程与函数的区别

    1、存储过程用户在数据库中完成特定操作或者任务(如插入,删除等),函数用于返回特定的数据

    2、存储过程声明用procedure,函数用function。

    3、存储过程不需要返回类型,函数必须要返回类型

    4、存储过程可作为独立的pl-sql执行,函数不能作为独立的plsql执行,必须作为表达式的一部分。

    5、存储过程只能通过out和in/out来返回值,函数除了可以使用out,in/out以外,还可以使用return返回值。

    6、sql语句(DML或SELECT)中不可用调用存储过程,而函数可以。

    https://www.cnblogs.com/karrya/p/11600130.html

    7.什么叫视图?游标是什么?

    视图:

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

    游标:

    是对查询出来的结果集作为一个单元来有效的处理。游标可以定在该单元中的特定行,从结果集的当前行检索一行或多行。可以对结果集当前行做修改。一般不使用游标,但是需要逐条处理数据的时候,游标显得十分重要。

    8.视图的优缺点

    优点:

    1对数据库的访问,因为视图可以有选择性的选取数据库里的一部分。

    2)用户通过简单的查询可以从复杂查询中得到结果。

    3)维护数据的独立性,试图可从多个表检索数据。

    4)对于相同的数据可产生不同的视图。

    缺点:

    性能:查询视图时,必须把视图的查询转化成对基本表的查询,如果这个视图是由一个复杂的多表查询所定义,那么,那么就无法更改数据

    9.drop、truncate、 delete区别

    最基本:

    • drop直接删掉表。
    • truncate删除表中数据,再插入时自增长id又从1开始。
    • delete删除表中数据,可以加where字句。

    (1) DELETE语句执行删除的过程是每次从表中删除一行,并且同时将该行的删除操作作为事务记录在日志中保存以便进行进行回滚操作。TRUNCATE TABLE 则一次性地从表中删除所有的数据并不把单独的删除操作记录记入日志保存,删除行是不能恢复的。并且在删除的过程中不会激活与表有关的删除触发器。执行速度快。

    (2) 表和索引所占空间。当表被TRUNCATE 后,这个表和索引所占用的空间会恢复到初始大小,而DELETE操作不会减少表或索引所占用的空间。drop语句将表所占用的空间全释放掉。

    (3) 一般而言,drop > truncate > delete

    (4) 应用范围。TRUNCATE 只能对TABLE;DELETE可以是table和view

    (5) TRUNCATE 和DELETE只删除数据,而DROP则删除整个表(结构和数据)。

    (6) truncate与不带where的delete :只删除数据,而不删除表的结构(定义)drop语句将删除表的结构被依赖的约束(constrain),触发器(trigger)索引(index);依赖于该表的存储过程/函数将被保留,但其状态会变为:invalid。

    (7) delete语句为DML(data maintain Language),这个操作会被放到 rollback segment中,事务提交后才生效。如果有相应的 tigger,执行的时候将被触发。

    (8) truncate、drop是DLL(data define language),操作立即生效,原数据不放到 rollback segment中,不能回滚。

    (9) 在没有备份情况下,谨慎使用 drop 与 truncate。要删除部分数据行采用delete且注意结合where来约束影响范围。回滚段要足够大。要删除表用drop;若想保留表而将表中数据删除,如果于事务无关,用truncate即可实现。如果和事务有关,或老师想触发trigger,还是用delete。

    (10) Truncate table 表名 速度快,而且效率高,因为:?truncate table 在功能上与不带 WHERE 子句的 DELETE 语句相同:二者均删除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快,且使用的系统和事务日志资源少。DELETE 语句每次删除一行,并在事务日志中为所删除的每行记录一项。TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据,并且只在事务日志中记录页的释放。

    (11) TRUNCATE TABLE 删除表中的所有行,但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值,请改用 DELETE。如果要删除表定义及其数据,请使用 DROP TABLE 语句。

    (12) 对于由 FOREIGN KEY 约束引用的表,不能使用 TRUNCATE TABLE,而应使用不带 WHERE 子句的 DELETE 语句。由于 TRUNCATE TABLE 不记录在日志中,所以它不能激活触发器。

    10.什么是临时表,临时表什么时候删除?

    临时表可以手动删除:
    DROP TEMPORARY TABLE IF EXISTS temp_tb;

    临时表只在当前连接可见,当关闭连接时,MySQL会自动删除表并释放所有空间。因此在不同的连接中可以创建同名的临时表,并且操作属于本连接的临时表。
    创建临时表的语法与创建表语法类似,不同之处是增加关键字TEMPORARY,

    如:

    CREATE TEMPORARY TABLE tmp_table (

    NAME VARCHAR (10) NOT NULL,

    time date NOT NULL
    );

    select * from tmp_table;

    11.非关系型数据库和关系型数据库区别,优势比较?

    非关系型数据库的优势:

    • 性能:NOSQL是基于键值对的,可以想象成表中的主键和值的对应关系,而且不需要经过SQL层的解析,所以性能非常高。
    • 可扩展性:同样也是因为基于键值对,数据之间没有耦合性,所以非常容易水平扩展。

    关系型数据库的优势:

    • 复杂查询:可以用SQL语句方便的在一个表以及多个表之间做非常复杂的数据查询。
    • 事务支持:使得对于安全性能很高的数据访问要求得以实现。

    其他:

    1.对于这两类数据库,对方的优势就是自己的弱势,反之亦然。

    2.NOSQL数据库慢慢开始具备SQL数据库的一些复杂查询功能,比如MongoDB。

    3.对于事务的支持也可以用一些系统级的原子操作来实现例如乐观锁之类的方法来曲线救国,比如Redis set nx。

    12.数据库范式,根据某个场景设计数据表?

    明确概念不包含在任何候选码中的属性叫做非主属性,包含在任何一个候选码中的属性叫做主属性。

    第一范式:(确保每列保持原子性)所有字段值都是不可分解的原子值。

    第一范式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值,就说明该数据库表满足了第一范式。
    第一范式的合理遵循需要根据系统的实际需求来定。比如某些数据库系统中需要用到“地址”这个属性,本来直接将“地址”属性设计成一个数据库表的字段就行。但是如果系统经常会访问“地址”属性中的“城市”部分,那么就非要将“地址”这个属性重新拆分为省份、城市、详细地址等多个部分进行存储,这样在对地址中某一部分操作的时候将非常方便。这样设计才算满足了数据库的第一范式,如下表所示。
    上表所示的用户信息遵循了第一范式的要求,这样在对用户使用城市进行分类的时候就非常方便,也提高了数据库的性能。

    第二范式:(在第一范式的基础上,确保每一个非主属性完全函数依赖于码)在一个数据库表中,一个表中只能保存一种数据,不可以把多种数据保存在同一张数据库表中。

    第二范式在第一范式的基础之上更进一层。第二范式需要确保数据库表中的每一列都和主键相关,而不能只与主键的某一部分相关(主要针对联合主键而言)。也就是说在一个数据库表中,一个表中只能保存一种数据,不可以把多种数据保存在同一张数据库表中。
    比如要设计一个订单信息表,因为订单中可能会有多种商品,所以要将订单编号和商品编号作为数据库表的联合主键。

    第三范式:(确保每个非主属性都直接依赖于码) 数据表中的每一列数据都和主键直接相关,而不能间接相关。

    第三范式需要确保数据表中的每一列数据都和主键直接相关,而不能间接相关。
    比如在设计一个订单数据表的时候,可以将客户编号作为一个外键和订单表建立相应的关系。而不可以在订单表中添加关于客户其它信息(比如姓名、所属公司等)的字段。

    BCNF:符合3NF,并且,主属性对每一个不包含它的码也是完全函数依赖,且没有任何属性完全函数依赖于非码的任何一组属性。

    若关系模式属于第二范式,且每个属性都不传递依赖于键码,则R属于BC范式。
    通常BC范式的条件有多种等价的表述:每个非平凡依赖的左边必须包含键码;每个决定因素必须包含键码。
    BC范式既检查非主属性,又检查主属性。当只检查非主属性时,就成了第三范式。满足BC范式的关系都必然满足第三范式。
    还可以这么说:若一个关系达到了第三范式,并且它只有一个候选码,或者它的每个候选码都是单属性,则该关系自然达到BC范式。
    一般,一个数据库设计符合3NF或BCNF就可以了。

    第四范式:要求把同一表内的多对多关系删除。

    第五范式:从最终结构重新建立原始结构。

    13.什么是 内连接、外连接、交叉连接、笛卡尔积等?

    内连接: 只连接匹配的行

    左外连接: 包含左边表的全部行(不管右边的表中是否存在与它们匹配的行),以及右边表中全部匹配的行

    右外连接: 包含右边表的全部行(不管左边的表中是否存在与它们匹配的行),以及左边表中全部匹配的行

    例如1:
    SELECT a.,b. FROM luntan LEFT JOIN usertable as b ON a.username=b.username

    例如2:
    SELECT a.,b. FROM city as a FULL OUTER JOIN user as b ON a.username=b.username

    全外连接: 包含左、右两个表的全部行,不管另外一边的表中是否存在与它们匹配的行。

    交叉连接: 生成笛卡尔积-它不使用任何匹配或者选取条件,而是直接将一个数据源中的每个行与另一个数据源的每个行都一一匹配

    例如:
    SELECT type,pub_name FROM titles CROSS JOIN publishers ORDER BY type

    注意:

    很多公司都只是考察是否知道其概念,但是也有很多公司需要不仅仅知道概念,还需要动手写sql,一般都是简单的连接查询,具体关于连接查询的sql练习,参见以下链接:

    牛客网数据库SQL实战

    leetcode中文网站数据库练习

    我的另一篇文章,常用sql练习50题

    14.varchar和char的使用场景?

    1.char的长度是不可变的,而varchar的长度是可变的。

    定义一个char[10]和varchar[10]。
    如果存进去的是‘csdn’,那么char所占的长度依然为10,除了字符‘csdn’外,后面跟六个空格,varchar就立马把长度变为4了,取数据的时候,char类型的要用trim()去掉多余的空格,而varchar是不需要的。

    2.char的存取速度还是要比varchar要快得多,因为其长度固定,方便程序的存储与查找。
    char也为此付出的是空间的代价,因为其长度固定,所以难免会有多余的空格占位符占据空间,可谓是以空间换取时间效率。
    varchar是以空间效率为首位。

    3.char的存储方式是:对英文字符(ASCII)占用1个字节,对一个汉字占用两个字节。
    varchar的存储方式是:对每个英文字符占用2个字节,汉字也占用2个字节。

    4.两者的存储数据都非unicode的字符数据。

    15.SQL语言分类

    SQL语言共分为四大类:

    • 数据查询语言DQL
    • 数据操纵语言DML
    • 数据定义语言DDL
    • 数据控制语言DCL。

    1. 数据查询语言DQL

    数据查询语言DQL基本结构是由SELECT子句,FROM子句,WHERE子句组成的查询块:

    SELECT
    FROM
    WHERE

    2 .数据操纵语言DML

    数据操纵语言DML主要有三种形式:

    1) 插入:INSERT

    2) 更新:UPDATE

    3) 删除:DELETE

    3. 数据定义语言DDL

    数据定义语言DDL用来创建数据库中的各种对象-----表、视图、索引、同义词、聚簇等如:
    CREATE TABLE/VIEW/INDEX/SYN/CLUSTER

    表 视图 索引 同义词 簇

    DDL操作是隐性提交的!不能rollback

    4. 数据控制语言DCL

    数据控制语言DCL用来授予或回收访问数据库的某种特权,并控制数据库操纵事务发生的时间及效果,对数据库实行监视等。如:

    1) GRANT:授权。

    2) ROLLBACK [WORK] TO [SAVEPOINT]:回退到某一点。回滚---ROLLBACK;回滚命令使数据库状态回到上次最后提交的状态。其格式为:
    SQL>ROLLBACK;

    3) COMMIT [WORK]:提交。

    在数据库的插入、删除和修改操作时,只有当事务提交到数据库时才算完成。在事务提交前,只有操作数据库的这个人才能有权看
    到所做的事情,别人只有在最后提交完成后才可以看到。

    提交数据有三种类型:显式提交、隐式提交及自动提交。

    下面分别说明这三种类型。

    (1) 显式提交
    用COMMIT命令直接完成的提交为显式提交。其格式为:
    SQL>COMMIT;

    (2) 隐式提交
    用SQL命令间接完成的提交为隐式提交。这些命令是:
    ALTER,AUDIT,COMMENT,CONNECT,CREATE,DISCONNECT,DROP,
    EXIT,GRANT,NOAUDIT,QUIT,REVOKE,RENAME。

    (3) 自动提交
    若把AUTOCOMMIT设置为ON,则在插入、修改、删除语句执行后,
    系统将自动进行提交,这就是自动提交。其格式为:
    SQL>SET AUTOCOMMIT ON;

    参考文章:
    https://www.cnblogs.com/study-s/p/5287529.html

    16.like %和-的区别

    通配符的分类:

    %百分号通配符:表示任何字符出现任意次数(可以是0次).

    _下划线通配符:表示只能匹配单个字符,不能多也不能少,就是一个字符.

    like操作符: LIKE作用是指示mysql后面的搜索模式是利用通配符而不是直接相等匹配进行比较.

    注意: 如果在使用like操作符时,后面的没有使用通用匹配符效果是和=一致的,SELECT * FROM products WHERE products.prod_name like '1000';
    只能匹配的结果为1000,而不能匹配像JetPack 1000这样的结果.

    • %通配符使用: 匹配以"yves"开头的记录:(包括记录"yves") SELECT FROM products WHERE products.prod_name like 'yves%';
      匹配包含"yves"的记录(包括记录"yves") SELECT
      FROM products WHERE products.prod_name like '%yves%';
      匹配以"yves"结尾的记录(包括记录"yves",不包括记录"yves ",也就是yves后面有空格的记录,这里需要注意) SELECT * FROM products WHERE products.prod_name like '%yves';
    • 通配符使用: SELECT FROM products WHERE products.prod_name like '_yves'; 匹配结果为: 像"yyves"这样记录.
      SELECT
      FROM products WHERE products.prod*name like 'yves
      *'; 匹配结果为: 像"yvesHe"这样的记录.(一个下划线只能匹配一个字符,不能多也不能少)

    注意事项:

    • 注意大小写,在使用模糊匹配时,也就是匹配文本时,mysql是可能区分大小的,也可能是不区分大小写的,这个结果是取决于用户对MySQL的配置方式.如果是区分大小写,那么像YvesHe这样记录是不能被"yves__"这样的匹配条件匹配的.
    • 注意尾部空格,"%yves"是不能匹配"heyves "这样的记录的.
    • 注意NULL,%通配符可以匹配任意字符,但是不能匹配NULL,也就是说SELECT * FROM products WHERE products.prod_name like '%;是匹配不到products.prod_name为NULL的的记录.

    技巧与建议:

    正如所见, MySQL的通配符很有用。但这种功能是有代价的:通配符搜索的处理一般要比前面讨论的其他搜索所花时间更长。这里给出一些使用通配符要记住的技巧。

    • 不要过度使用通配符。如果其他操作符能达到相同的目的,应该 使用其他操作符。
    • 在确实需要使用通配符时,除非绝对有必要,否则不要把它们用 在搜索模式的开始处。把通配符置于搜索模式的开始处,搜索起 来是最慢的。
    • 仔细注意通配符的位置。如果放错地方,可能不会返回想要的数.

    参考博文:https://blog.csdn.net/u011479200/article/details/78513632

    17.count(*)、count(1)、count(column)的区别

    • count(*)对行的数目进行计算,包含NULL
    • count(column)对特定的列的值具有的行数进行计算,不包含NULL值。
    • count()还有一种使用方式,count(1)这个用法和count(*)的结果是一样的。

    性能问题:

    1.任何情况下SELECT COUNT(*) FROM tablename是最优选择;

    2.尽量减少SELECT COUNT(*) FROM tablename WHERE COL = ‘value’ 这种查询;

    3.杜绝SELECT COUNT(COL) FROM tablename WHERE COL2 = ‘value’ 的出现。

    • 如果表没有主键,那么count(1)比count(*)快。
    • 如果有主键,那么count(主键,联合主键)比count(*)快。
    • 如果表只有一个字段,count(*)最快。//这三点有问题吧。

    count(1)跟count(主键)一样,只扫描主键。count(*)跟count(非主键)一样,扫描整个表。明显前者更快一些。

    18.最左前缀原则

    多列索引(联合索引):

    ALTER TABLE people ADD INDEX lname_fname_age (lame,fname,age);

    为了提高搜索效率,我们需要考虑运用多列索引,由于索引文件以B-Tree格式保存,所以我们不用扫描任何记录,即可得到最终结果。

    注:在mysql中执行查询时,只能使用一个索引,如果我们在lname,fname,age上分别建索引,执行查询时,只能使用一个索引,mysql会选择一个最严格(获得结果集记录数最少)的索引。

    最左前缀原则:顾名思义,就是最左优先,上例中我们创建了lname_fname_age多列索引,相当于创建了(lname)单列索引,(lname,fname)组合索引以及(lname,fname,age)组合索引(覆盖索引)。

    19.数据库的连接池

    连接池的工作原理主要由三部分组成,分别为连接池的建立、连接池中连接的使用管理、 连接池的关闭。

    第一、连接池的建立。

    一般在系统初始化时,连接池会根据系统配置建立,并在池中创建了几个连接对象,以便使用时能从连接池中获取。连接池中的连接不能随意创建和关闭, 这样避免了连接随意建立和关闭造成的系统开销。Java 中提供了很多容器类可以方便的构 建连接池,例如 VectorStack 等。

    第二、连接池的管理。

    连接池管理策略是连接池机制的核心,连接池内连接的分配和释放对 系统的性能有很大的影响。其管理策略是:

    • 当客户请求数据库连接时,首先查看连接池中是否有空闲连接,如果存在空闲连接,则将连接分配给客户使用;
    • 如果没有空闲连接,则查看当前所开的连接数是否已经达到最大连接数, 如果没达到就重新创建一个连接给请求的客户;如果达到就按设定的最大等待时间进行等待;如果超出最大等待时间,则抛出异常给客户。
    • 当客户释放数据库连接时,先判断该连接的引用次数是否超过了规定值,如果超过就从连接池中删除该连接,否则保留为其他客户服务。

    该策略保证了数据库连接的有效复用,避免频繁的建立、释放连接所带来的系统资源开销。

    第三、连接池的关闭。

    当应用程序退出时,关闭连接池中所有的连接,释放连接池相关的资源,该过程正好与创建相反。

    总结:

    像打开关闭数据库连接这种和数据库的交互可能是很费时的,尤其是当客户端数量增加的时候,会消耗大量的资源,成本是非常高的。可以在应用服务器启动的时候建立很多个数据库 连接并维护在一个池中。连接请求由池中的连接提供。在连接使用完毕以后,把连接归还到池中,以用于满足将来更多的请求。

    二、索引

    1.什么是索引?

    何为索引:

    数据库索引,是数据库管理系统中一个排序的数据结构,索引的实现通常使用B树及其变种B+树。

    在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式引用(指向)数据,这样就可以在这些数据结构上实现高级查找算法。这种数据结构,就是索引。

    B+树,经过优化的 B+树

    主要是在所有的叶子结点中增加了指向下一个叶子节点的指针,因此 InnoDB 建议为大部分表使用默认自增的主键作为主索引。

    2.索引的作用?它的优点缺点是什么?

    索引作用:

    协助快速查询、更新数据库表中数据。

    为表设置索引要付出代价的:

    • 一是增加了数据库的存储空间
    • 二是在插入和修改数据时要花费较多的时间(因为索引也要随之变动)。

    3.索引的优缺点?

    创建索引可以大大提高系统的性能(优点):

    1.通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性。

    2.可以大大加快数据的检索速度,这也是创建索引的最主要的原因。

    3.可以加速表和表之间的连接,特别是在实现数据的参考完整性方面特别有意义。

    4.在使用分组和排序子句进行数据检索时,同样可以显著减少查询中分组和排序的时间。

    5.通过使用索引,可以在查询的过程中,使用优化隐藏器提高系统的性能

    增加索引也有许多不利的方面(缺点):

    1.创建索引和维护索引要耗费时间,这种时间随着数据量的增加而增加

    2.索引需要占物理空间,除了数据表占数据空间之外,每一个索引还要占一定的物理空间,如果要建立聚簇索引,那么需要的空间就会更大。

    3.当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护,这样就降低了数据的维护速度。

    4.哪些列适合建立索引、哪些不适合建索引?

    索引是建立在数据库表中的某些列的上面。在创建索引的时候,应该考虑在哪些列上可以创建索引,在哪些列上不能创建索引。

    一般来说,应该在这些列上创建索引:

    (1)在经常需要搜索的列上,可以加快搜索的速度;

    (2)在作为主键的列上,强制该列的唯一性和组织表中数据的排列结构;

    (3)在经常用在连接的列上,这些列主要是一些外键,可以加快连接的速度;

    (4)在经常需要根据范围进行搜索的列上创建索引,因为索引已经排序,其指定的范围是连续的;

    (5)在经常需要排序的列上创建索引,因为索引已经排序,这样查询可以利用索引的排序,加快排序查询时间;

    (6)在经常使用在WHERE子句中的列上面创建索引,加快条件的判断速度。

    对于有些列不应该创建索引:

    (1)对于那些在查询中很少使用或者参考的列不应该创建索引。

    这是因为,既然这些列很少使用到,因此有索引或者无索引,并不能提高查询速度。相反,由于增加了索引,反而降低了系统的维护速度和增大了空间需求。

    (2)对于那些只有很少数据值的列也不应该增加索引。

    这是因为,由于这些列的取值很少,例如人事表的性别列,在查询的结果中,结果集的数据行占了表中数据行的很大比例,即需要在表中搜索的数据行的比例很大。增加索引,并不能明显加快检索速度。

    (3)对于那些定义为text, image和bit数据类型的列不应该增加索引。

    这是因为,这些列的数据量要么相当大,要么取值很少。

    (4)当修改性能远远大于检索性能时,不应该创建索引。

    这是因为,修改性能和检索性能是互相矛盾的。当增加索引时,会提高检索性能,但是会降低修改性能。当减少索引时,会提高修改性能,降低检索性能。因此,当修改性能远远大于检索性能时,不应该创建索引。

    5.什么样的字段适合建索引

    唯一、不为空、经常被查询的字段

    6.MySQL B+Tree索引和Hash索引的区别?

    Hash索引和B+树索引的特点:

    • Hash索引结构的特殊性,其检索效率非常高,索引的检索可以一次定位;
    • B+树索引需要从根节点到枝节点,最后才能访问到页节点这样多次的IO访问;

    为什么不都用Hash索引而使用B+树索引?

    1. Hash索引仅仅能满足"=","IN"和""查询,不能使用范围查询,因为经过相应的Hash算法处理之后的Hash值的大小关系,并不能保证和Hash运算前完全一样;
    1. Hash索引无法被用来避免数据的排序操作,因为Hash值的大小关系并不一定和Hash运算前的键值完全一样;
    1. Hash索引不能利用部分索引键查询,对于组合索引,Hash索引在计算Hash值的时候是组合索引键合并后再一起计算Hash值,而不是单独计算Hash值,所以通过组合索引的前面一个或几个索引键进行查询的时候,Hash索引也无法被利用;
    1. Hash索引在任何时候都不能避免表扫描,由于不同索引键存在相同Hash值,所以即使取满足某个Hash键值的数据的记录条数,也无法从Hash索引中直接完成查询,还是要回表查询数据;
    1. Hash索引遇到大量Hash值相等的情况后性能并不一定就会比B+树索引高。

    补充:

    1.MySQL中,只有HEAP/MEMORY引擎才显示支持Hash索引。

    2.常用的InnoDB引擎中默认使用的是B+树索引,它会实时监控表上索引的使用情况,如果认为建立哈希索引可以提高查询效率,则自动在内存中的“自适应哈希索引缓冲区”建立哈希索引(在InnoDB中默认开启自适应哈希索引),通过观察搜索模式,MySQL会利用index key的前缀建立哈希索引,如果一个表几乎大部分都在缓冲池中,那么建立一个哈希索引能够加快等值查询。
    B+树索引和哈希索引的明显区别是:

    3.如果是等值查询,那么哈希索引明显有绝对优势,因为只需要经过一次算法即可找到相应的键值;当然了,这个前提是,键值都是唯一的。如果键值不是唯一的,就需要先找到该键所在位置,然后再根据链表往后扫描,直到找到相应的数据;

    4.如果是范围查询检索,这时候哈希索引就毫无用武之地了,因为原先是有序的键值,经过哈希算法后,有可能变成不连续的了,就没办法再利用索引完成范围查询检索;
    同理,哈希索引没办法利用索引完成排序,以及like ‘xxx%’ 这样的部分模糊查询(这种部分模糊查询,其实本质上也是范围查询);

    5.哈希索引也不支持多列联合索引的最左匹配规则;

    6.B+树索引的关键字检索效率比较平均,不像B树那样波动幅度大,在有大量重复键值情况下,哈希索引的效率也是极低的,因为存在所谓的哈希碰撞问题。

    7.在大多数场景下,都会有范围查询、排序、分组等查询特征,用B+树索引就可以了。

    7.B树和B+树的区别

    1. B树,每个节点都存储key和data,所有节点组成这棵树,并且叶子节点指针为nul,叶子结点不包含任何关键字信息。
       
    2. B+树,所有的叶子结点中包含了全部关键字的信息,及指向含有这些关键字记录的指针,且叶子结点本身依关键字的大小自小而大的顺序链接,所有的非终端结点可以看成是索引部分,结点中仅含有其子树根结点中最大(或最小)关键字。 (而B 树的非终节点也包含需要查找的有效信息)
       

    8.为什么说B+比B树更适合实际应用中操作系统的文件索引和数据库索引?

    1.B+的磁盘读写代价更低

    B+的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对B树更小。如果把所有同一内部结点的关键字存放在同一盘块中,那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说IO读写次数也就降低了。

    2.B+tree的查询效率更加稳定

    由于非终结点并不是最终指向文件内容的结点,而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同,导致每一个数据的查询效率相当。

    3.范围查询方便

    叶子结点本身依据关键字的大小自小而大顺序连接,范围查询更加方便;

    9.聚集索引和非聚集索引区别?

    聚合索引(clustered index):

    聚集索引表记录的排列顺序和索引的排列顺序一致,所以查询效率快,只要找到第一个索引值记录,其余就连续性的记录在物理也一样连续存放。聚集索引对应的缺点就是修改慢,因为为了保证表中记录的物理和索引顺序一致,在记录插入的时候,会对数据页重新排序
    聚集索引类似于新华字典中用拼音去查找汉字,拼音检索表于***顺序都是按照a~z排列的,就像相同的逻辑顺序于物理顺序一样,当你需要查找a,ai两个读音的字,或是想一次寻找多个傻(sha)的同音字时,也许向后翻几页,或紧接着下一行就得到结果了。

    非聚合索引(nonclustered index):

    非聚集索引指定了表中记录的逻辑顺序,但是记录的物理顺序和索引指定的逻辑顺序不一定一致,两种索引都采用B+树结构,非聚集索引的叶子层并不和实际数据页相重叠,而采用叶子层包含一个指向表中的记录在数据页中的指针方式。非聚集索引层次多,不会造成数据重排。
    非聚集索引类似在新华字典上通过偏旁部首来查询汉字,检索表也许是按照横、竖、撇来排列的,但是由于正文中是a~z的拼音顺序,所以就类似于逻辑地址于物理地址的不对应。同时适用的情况就在于分组,大数目的不同值,频繁更新的列中,这些情况即不适合聚集索引。

    根本区别:

    聚集索引和非聚集索引的根本区别是表记录的排列顺序和与索引的排列顺序是否一致

    三、事务

    1.什么是事务?

    事务是对数据库中一系列操作进行统一的回滚或者提交的操作,主要用来保证数据的完整性和一致性。

    2.事务四大特性(ACID)原子性、一致性、隔离性、持久性?

    原子性(Atomicity):
    原子性是指事务包含的所有操作要么全部成功,要么全部失败回滚,因此事务的操作如果成功就必须要完全应用到数据库,如果操作失败则不能对数据库有任何影响。

    一致性(Consistency):
    事务开始前和结束后,数据库的完整性约束没有被破坏。比如A向B转账,不可能A扣了钱,B却没收到。

    隔离性(Isolation):
    隔离性是当多个用户并发访问数据库时,比如操作同一张表时,数据库为每一个用户开启的事务,不能被其他事务的操作所干扰,多个并发事务之间要相互隔离。同一时间,只允许一个事务请求同一数据,不同的事务之间彼此没有任何干扰。比如A正在从一张银行卡中取钱,在A取钱的过程结束前,B不能向这张卡转账。

    持久性(Durability):
    持久性是指一个事务一旦被提交了,那么对数据库中的数据的改变就是永久性的,即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。

    3.事务的并发?事务隔离级别,每个级别会引发什么问题,MySQL默认是哪个级别?

    从理论上来说, 事务应该彼此完全隔离, 以避免并发事务所导致的问题,然而, 那样会对性能产生极大的影响, 因为事务必须按顺序运行, 在实际开发中, 为了提升性能, 事务会以较低的隔离级别运行, 事务的隔离级别可以通过隔离事务属性指定。
    事务的并发问题

    1、脏读:事务A读取了事务B更新的数据,然后B回滚操作,那么A读取到的数据是脏数据

    2、不可重复读:事务 A 多次读取同一数据,事务 B 在事务A多次读取的过程中,对数据作了更新并提交,导致事务A多次读取同一数据时,结果因此本事务先后两次读到的数据结果会不一致。

    3、幻读:幻读解决了不重复读,保证了同一个事务里,查询的结果都是事务开始时的状态(一致性)。

    例如:事务T1对一个表中所有的行的某个数据项做了从“1”修改为“2”的操作 这时事务T2又对这个表中插入了一行数据项,而这个数据项的数值还是为“1”并且提交给数据库。 而操作事务T1的用户如果再查看刚刚修改的数据,会发现还有跟没有修改一样,其实这行是从事务T2中添加的,就好像产生幻觉一样,这就是发生了幻读。
    小结:不可重复读的和幻读很容易混淆,不可重复读侧重于修改,幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行,解决幻读需要锁表。

    事务的隔离级别

    读未提交:另一个事务修改了数据,但尚未提交,而本事务中的SELECT会读到这些未被提交的数据脏读

    不可重复读:事务 A 多次读取同一数据,事务 B 在事务A多次读取的过程中,对数据作了更新并提交,导致事务A多次读取同一数据时,结果因此本事务先后两次读到的数据结果会不一致。

    可重复读:在同一个事务里,SELECT的结果是事务开始时时间点的状态,因此,同样的SELECT操作读到的结果会是一致的。但是,会有幻读现象

    串行化:最高的隔离级别,在这个隔离级别下,不会产生任何异常。并发的事务,就像事务是在一个个按照顺序执行一样

    特别注意:

    MySQL默认的事务隔离级别为repeatable-read

    MySQL 支持 4 中事务隔离级别.

    事务的隔离级别要得到底层数据库引擎的支持, 而不是应用程序或者框架的支持.

    Oracle 支持的 2 种事务隔离级别:READ_COMMITED , SERIALIZABLE

    SQL规范所规定的标准,不同的数据库具体的实现可能会有些差异

    MySQL中默认事务隔离级别是“可重复读”时并不会锁住读取到的行

    事务隔离级别:未提交读时,写数据只会锁住相应的行。

    事务隔离级别为:可重复读时,写数据会锁住整张表。

    事务隔离级别为:串行化时,读写数据都会锁住整张表。

    隔离级别越高,越能保证数据的完整性和一致性,但是对并发性能的影响也越大,鱼和熊掌不可兼得啊。对于多数应用程序,可以优先考虑把数据库系统的隔离级别设为Read Committed,它能够避免脏读取,而且具有较好的并发性能。尽管它会导致不可重复读、幻读这些并发问题,在可能出现这类问题的个别场合,可以由应用程序采用悲观锁或乐观锁来控制。

    4.事务传播行为

    1.PROPAGATION_REQUIRED:如果当前没有事务,就创建一个新事务,如果当前存在事务,就加入该事务,该设置是最常用的设置。

    2.PROPAGATION_SUPPORTS:支持当前事务,如果当前存在事务,就加入该事务,如果当前不存在事务,就以非事务执行。

    3.PROPAGATION_MANDATORY:支持当前事务,如果当前存在事务,就加入该事务,如果当前不存在事务,就抛出异常。

    4.PROPAGATION_REQUIRES_NEW:创建新事务,无论当前存不存在事务,都创建新事务。

    5.PROPAGATION_NOT_SUPPORTED:以非事务方式执行操作,如果当前存在事务,就把当前事务挂起。

    6.PROPAGATION_NEVER:以非事务方式执行,如果当前存在事务,则抛出异常。

    7.PROPAGATION_NESTED:如果当前存在事务,则在嵌套事务内执行。如果当前没有事务,则执行与PROPAGATION_REQUIRED类似的操作。

    5.嵌套事务

    什么是嵌套事务?

    嵌套是子事务套在父事务中执行,子事务是父事务的一部分,在进入子事务之前,父事务建立一个回滚点,叫save point,然后执行子事务,这个子事务的执行也算是父事务的一部分,然后子事务执行结束,父事务继续执行。重点就在于那个save point。看几个问题就明了了:

    如果子事务回滚,会发生什么?

    父事务会回滚到进入子事务前建立的save point,然后尝试其他的事务或者其他的业务逻辑,父事务之前的操作不会受到影响,更不会自动回滚。

    如果父事务回滚,会发生什么?

    父事务回滚,子事务也会跟着回滚!为什么呢,因为父事务结束之前,子事务是不会提交的,我们说子事务是父事务的一部分,正是这个道理。那么:

    事务的提交,是什么情况?

    是父事务先提交,然后子事务提交,还是子事务先提交,父事务再提交?答案是第二种情况,还是那句话,子事务是父事务的一部分,由父事务统一提交。

    参考文章:https://blog.csdn.net/liangxw1/article/details/51197560

    四、存储引擎

    1.MySQL常见的三种存储引擎(InnoDB、MyISAM、MEMORY)的区别?

    两种存储引擎的大致区别表现在:

    1.InnoDB支持事务,MyISAM不支持, 这一点是非常之重要。事务是一种高级的处理方式,如在一些列增删改中只要哪个出错还可以回滚还原,而MyISAM就不可以了。

    2.MyISAM适合查询以及插入为主的应用。

    3.InnoDB适合频繁修改以及涉及到安全性较高的应用。

    4.InnoDB支持外键,MyISAM不支持。

    5.从MySQL5.5.5以后,InnoDB是默认引擎。

    6.InnoDB不支持FULLTEXT类型的索引。

    7.InnoDB中不保存表的行数,如select count() from table时,InnoDB需要扫描一遍整个表来计算有多少行,但是MyISAM只要简单的读出保存好的行数即可。注意的是,当count()语句包含where条件时MyISAM也需要扫描整个表。

    8.对于自增长的字段,InnoDB中必须包含只有该字段的索引,但是在MyISAM表中可以和其他字段一起建立联合索引。

    9.DELETE FROM table时,InnoDB不会重新建立表,而是一行一行的 删除,效率非常慢。MyISAM则会重建表

    10.InnoDB支持行锁(where条件为主键)(某些情况下还是锁整表,如 update table set a=1 where user like '%lee%')。

    2.MySQL存储引擎MyISAM与InnoDB如何选择

    MySQL有多种存储引擎,每种存储引擎有各自的优缺点,可以择优选择使用:MyISAM、InnoDB、MERGE、MEMORY(HEAP)、BDB(BerkeleyDB)、EXAMPLE、FEDERATED、ARCHIVE、CSV、BLACKHOLE。

    虽然MySQL里的存储引擎不只是MyISAM与InnoDB这两个,但常用的就是两个。
    关于MySQL数据库提供的两种存储引擎,MyISAM与InnoDB选择使用:如果你的应用程序一定要使用事务,毫无疑问你要选择INNODB引擎。但要注意,INNODB的行级锁是有条件的。在where条件没有使用主键时,照样会锁全表。比如DELETE FROM mytable这样的删除语句。

    • 1.INNODB会支持一些关系数据库的高级功能,如事务功能和行级锁,MyISAM不支持。
    • 2.MyISAM的性能更优,占用的存储空间少,所以,选择何种存储引擎,视具体应用而定。
    • 如果你的应用程序对查询性能要求较高,就要使用MyISAM了。MyISAM索引和数据是分开的,而且其索引是压缩的,可以更好地利用内存。所以它的查询性能明显优于INNODB。压缩后的索引也能节约一些磁盘空间。MyISAM拥有全文索引的功能,这可以极大地优化LIKE查询的效率。

      有人说MyISAM只能用于小型应用,其实这只是一种偏见。如果数据量比较大,这是需要通过升级架构来解决,比如分表分库,而不是单纯地依赖存储引擎。

      现在一般都是选用innodb了,主要是MyISAM的全表锁,读写串行问题,并发效率锁表,效率低,MyISAM对于读写密集型应用一般是不会去选用的

    MEMORY存储引擎

    MEMORY是MySQL中一类特殊的存储引擎。它使用存储在内存中的内容来创建表,而且数据全部放在内存中。这些特性与前面的两个很不同。
    每个基于MEMORY存储引擎的表实际对应一个磁盘文件。该文件的文件名与表名相同,类型为frm类型。该文件中只存储表的结构。而其数据文件,都是存储在内存中,这样有利于数据的快速处理,提高整个表的效率。值得注意的是,服务器需要有足够的内存来维持MEMORY存储引擎的表的使用。如果不需要了,可以释放内存,甚至删除不需要的表。

    MEMORY默认使用哈希索引。速度比使用B型树索引快。当然如果你想用B型树索引,可以在创建索引时指定。

    注意,MEMORY用到的很少,因为它是把数据存到内存中,如果内存出现异常就会影响数据。如果重启或者关机,所有数据都会消失。因此,基于MEMORY的表的生命周期很短,一般是一次性的。

    3.MySQL的MyISAM与InnoDB两种存储引擎在,事务、锁级别,各自的适用场景?

    事务处理上方面

    • MyISAM:强调的是性能,每次查询具有原子性,其执行速度比InnoDB类型更快,但是不提供事务支持。

    • InnoDB:提供事务支持事务,外部键等高级数据库功能。 具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。

    锁级别

    • MyISAM:只支持表级锁,用户在操作MyISAM表时,select,update,delete,insert语句都会给表自动加锁,如果加锁以后的表满足insert并发的情况下,可以在表的尾部插入新的数据。

    • InnoDB:支持事务和行级锁,是innodb的最大特色。行锁大幅度提高了多用户并发操作的新能。但是InnoDB的行锁,只是在WHERE的主键是有效的,非主键的WHERE都会锁全表的。

    关于存储引擎MyISAM和InnoDB的其他参考资料如下:

    MySQL存储引擎中的MyISAM和InnoDB区别详解

    MySQL存储引擎之MyISAM和Innodb总结性梳理

    五、优化

    1.查询语句不同元素(where、jion、limit、group by、having等等)执行先后顺序?

    1.查询中用到的关键词主要包含六个,并且他们的顺序依次为 select--from--where--group by--having--order by

    其中select和from是必须的,其他关键词是可选的,这六个关键词的执行顺序 与sql语句的书写顺序并不是一样的,而是按照下面的顺序来执行

    from:需要从哪个数据表检索数据

    where:过滤表中数据的条件

    group by:如何将上面过滤出的数据分组

    having:对上面已经分组的数据进行过滤的条件

    select:查看结果集中的哪个列,或列的计算结果

    order by :按照什么样的顺序来查看返回的数据

    2.from后面的表关联,是自右向左解析而where条件的解析顺序是自下而上的。

    也就是说,在写SQL语句的时候,尽量把数据量小的表放在最右边来进行关联(用小表去匹配大表),而把能筛选出小量数据的条件放在where语句的最左边 (用小表去匹配大表)

    其他参考资源:
    http://www.cnblogs.com/huminxxl/p/3149097.html

    2.使用explain优化sql和索引?

    对于复杂、效率低的sql语句,我们通常是使用explain sql 来分析sql语句,这个语句可以打印出,语句的执行。这样方便我们分析,进行优化

    table:显示这一行的数据是关于哪张表的

    type:这是重要的列,显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、index和ALL

    all:full table scan ;MySQL将遍历全表以找到匹配的行;

    index: index scan; index 和 all的区别在于index类型只遍历索引;

    range:索引范围扫描,对索引的扫描开始于某一点,返回匹配值的行,常见于between ,等查询;

    ref:非唯一性索引扫描,返回匹配某个单独值的所有行,常见于使用非唯一索引即唯一索引的非唯一前缀进行查找;

    eq_ref:唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配,常用于主键或者唯一索引扫描;

    const,system:当MySQL对某查询某部分进行优化,并转为一个常量时,使用这些访问类型。如果将主键置于where列表中,MySQL就能将该查询转化为一个常量。

    possible_keys:显示可能应用在这张表中的索引。如果为空,没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句

    key: 实际使用的索引。如果为NULL,则没有使用索引。很少的情况下,MySQL会选择优化不足的索引。这种情况下,可以在SELECT语句中使用USE INDEX(indexname)来强制使用一个索引或者用IGNORE INDEX(indexname)来强制MySQL忽略索引

    key_len:使用的索引的长度。在不损失精确性的情况下,长度越短越好

    ref:显示索引的哪一列被使用了,如果可能的话,是一个常数

    rows:MySQL认为必须检查的用来返回请求数据的行数

    Extra:关于MySQL如何解析查询的额外信息。将在表4.3中讨论,但这里可以看到的坏的例子是Using temporary和Using filesort,意思MySQL根本不能使用索引,结果是检索会很慢。

    3.MySQL慢查询怎么解决?

    • slow_query_log 慢查询开启状态,0
    • slow_query_log_file 慢查询日志存放的位置(这个目录需要MySQL的运行帐号的可写权限,一般设置为MySQL的数据存放目录)。
    • long_query_time 查询超过多少秒才记录。
    • MySQL的慢查询,全名是慢查询日志,是MySQL提供的一种日志记录,用来记录在MySQL中响应时间超过阀值的语句。

      具体环境中,运行时间超过long_query_time值的SQL语句,则会被记录到慢查询日志中。

      long_query_time的默认值为10,意思是记录运行10秒以上的语句。

      默认情况下,MySQL数据库并不启动慢查询日志,需要手动来设置这个参数。

      当然,如果不是调优需要的话,一般不建议启动该参数,因为开启慢查询日志会或多或少带来一定的性能影响。

      慢查询日志支持将日志记录写入文件和数据库表。

      官方文档,关于慢查询的日志介绍如下(部分资料,具体参考官方相关链接):

    • https://blog.csdn.net/qq_40884473/article/details/89455740

    4.联合索引、强制使用索引和什么情况下不会索引不生效?

    (1)联合索引、覆盖索引等概念:

    1、覆盖索引:如果查询条件使用的是普通索引(或是联合索引的最左原则字段),查询结果是联合索引的字段或是主键,不用回表操作,直接返回结果,减少IO磁盘读写读取正行数据

    2、最左前缀:联合索引的最左 N 个字段,也可以是字符串索引的最左 M 个字符

    3、联合索引:根据创建联合索引的顺序,以最左原则进行where检索,比如(age,name)以age=1 或 age= 1 and name=‘张三’可以使用索引,单以name=‘张三’ 不会使用索引,考虑到存储空间的问题,还请根据业务需求,将查找频繁的数据进行靠左创建索引。

    4、索引下推:like 'hello%’and age >10 检索,MySQL5.6版本之前,会对匹配的数据进行回表查询。5.6版本后,会先过滤掉age<10的数据,再进行回表查询,减少回表率,提升检索速度。

    (2)如何强制使用索引:

    https://www.cnblogs.com/lizhaoyao/p/7691407.html

    SELECT  * FROM XXX_log force index(ctime) WHERE (`ctime` BETWEEN '2017-09-11 09:34:13'  AND '2017-10-11 09:34:13')
    and     id > 27851
    AND column1 = 'xxx'
    AND (column2 = 'null' OR LENGTH(column2) > 91)
    ORDER BY  id DESC LIMIT 0, 30

    (3)什么情况下索引不会生效?

    • 以“%”开头的 LIKE 语句,模糊匹配;
    • OR 语句前后没有同时使用索引;
    • 数据类型出现隐式转化(如 varchar 不加单引号的话可能会自动转换为 int 型);

    5.性能优化方法总结

    1.explain可知,尽量避免全表扫描,合理建立索引,要让索引发挥作用:

    • 在频繁检索,或用来连接,或有group by/order by的,经常范围查询的,较少增删改,重复数据少,字段长度较短的列上建立索引;
    • 建立索引的字段不要有null值;
    • where子句中尽量不要用“!=”、"<>"、“%”以及表达式函数操作,否则会导致全表扫描;

    2.索引字段字段长度较短的方法:

    • 能不用double就不用double;
    • 能有数字型字段就不要用字符型字段,数字型字段匹配时匹配次数少;
    • 字段修改次数少时,尽量用varchar代替char,因为变长字段一般比较短,查询快,存储也小;字段频繁被修改时,用varchar容易造成“行迁移”现象,可能会造成多余IO,还是用char吧;
    • 字符型字段尽量不要用TEXT;
    • 日期类型尽量用timestamp,不要用datetime,因为它短;如果精确到天就可以,那么尽量用date;

    3.尽量让索引的记录都是唯一的,这样用索引查询加锁时加的是行锁而不是表锁;

    4.尽量使用自增主键;

    5.一次性事件尽量避免使用临时表,重复多次事件建议使用临时表。避免频繁创建和删除临时表;

    6.用not exists代替not in,子表大的话用exists,子表小的话可以用in。

    7.联合索引等调优方法,尽量select字段而不是select*;

    8.注意可以把where条件前移到from的临时表里;

    9.尽量避免大事务操作,提升系统并发能力。

    10.https://youzhixueyuan.com/MySQL-slow-query-optimization-index-optimization.html

    六、数据库锁

    1.mysql都有什么锁,死锁判定原理和具体场景,死锁怎么解决?

    MySQL有三种锁的级别:页级、表级、行级。

    • 表级锁:开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低。
    • 行级锁:开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高。
    • 页面锁(间隙锁):开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间,并发度一般

    什么情况下会造成死锁?

    独占、不可抢占、请求与等待、循环等待;

    什么是死锁?

    死锁: 是指两个或两个以上的进程在执行过程中。因争夺资源而造成的一种互相等待的现象,若无外力作用,它们都将无法推进下去。此时称系统处于死锁状态或系统产生了死锁,这些永远在互相等竺的进程称为死锁进程。

    表级锁不会产生死锁.所以解决死锁主要还是针对于最常用的InnoDB。

    死锁的关键在于:两个(或以上)的Session加锁的顺序不一致。

    那么对应的解决死锁问题的关键就是:让不同的session加锁有次序

    死锁的解决办法?

    1.查出的线程杀死 kill
    SELECT trx_MySQL_thread_id FROM information_schema.INNODB_TRX;

    2.设置锁的超时时间
    Innodb 行锁的等待时间,单位秒。可在会话级别设置,RDS 实例该参数的默认值为 50(秒)。

    生产环境不推荐使用过大的 innodb_lock_wait_timeout参数值
    该参数支持在会话级别修改,方便应用在会话级别单独设置某些特殊操作的行锁等待超时时间,如下:
    set innodb_lock_wait_timeout=1000; —设置当前会话 Innodb 行锁等待超时时间,单位秒。

    3.指定获取锁的顺序(银行家算法)

    2.有哪些锁(乐观锁悲观锁),select 时怎么加排它锁?

    悲观锁(Pessimistic Lock):

    悲观锁特点:先获取锁,再进行业务操作。

    即“悲观”的认为获取锁是非常有可能失败的,因此要先确保获取锁成功再进行业务操作。通常所说的“一锁二查三更新”即指的是使用悲观锁。通常来讲在数据库上的悲观锁需要数据库本身提供支持,即通过常用的select … for update操作来实现悲观锁。当数据库执行select for update时会获取被select中的数据行的行锁,因此其他并发执行的select for update如果试图选中同一行则会发生排斥(需要等待行锁被释放),因此达到锁的效果。select for update获取的行锁会在当前事务结束时自动释放,因此必须在事务中使用。

    悲观锁分为共享锁和排它锁。https://www.cnblogs.com/mr-wuxiansheng/p/7044733.html

    共享锁又称为读锁,简称S锁,顾名思义,共享锁就是多个事务对于同一数据可以共享一把锁,都能访问到数据,但是只能读不能修改。select … for update.

    排他锁又称为写锁,简称X锁,顾名思义,排他锁就是不能与其他所并存,如一个事务获取了一个数据行的排他锁,其他事务就不能再获取该行的其他锁,包括共享锁和排他锁,但是获取排他锁的事务是可以对数据就行读取和修改。select...lock in share mode.

    如果不加锁貌似不会起到限制作用?

    补充:
    不同的数据库对select for update的实现和支持都是有所区别的,

    • oracle支持select for update no wait,表示如果拿不到锁立刻报错,而不是等待,MySQL就没有no wait这个选项。
    • MySQL还有个问题是select for update语句执行中所有扫描过的行都会被锁上,这一点很容易造成问题。因此如果在MySQL中用悲观锁务必要确定走了索引,而不是全表扫描。

    乐观锁(Optimistic Lock):

    1.乐观锁,也叫乐观并发控制,它假设多用户并发的事务在处理时不会彼此互相影响,各事务能够在不产生锁的情况下处理各自影响的那部分数据。在提交数据更新之前,每个事务会先检查在该事务读取数据后,有没有其他事务又修改了该数据。如果其他事务有更新的话,那么当前正在提交的事务会进行回滚。

    2.**乐观锁的特点先进行业务操作,不到万不得已不去拿锁。**即“乐观”的认为拿锁多半是会成功的,因此在进行完业务操作需要实际更新数据的最后一步再去拿一下锁就好。
    乐观锁在数据库上的实现完全是逻辑的,不需要数据库提供特殊的支持。

    3.一般的做法是在需要锁的数据上增加一个版本号,或者时间戳

    实现方式举例如下:

    乐观锁(给表加一个版本号字段) 这个并不是乐观锁的定义,给表加版本号,是数据库实现乐观锁的一种方式

    1. SELECT data AS old_data, version AS old_version FROM …;
    2. 根据获取的数据进行业务操作,得到new_data和new_version
    3. UPDATE SET data = new_data, version = new_version WHERE version = old_version

    if (updated row > 0) {

    // 乐观锁获取成功,操作完成

    } else {

    // 乐观锁获取失败,回滚并重试

    }

    注意:

    • 乐观锁在不发生取锁失败的情况下开销比悲观锁小,但是一旦发生失败回滚开销则比较大,因此适合用在取锁失败概率比较小的场景,可以提升系统并发性能
    • 乐观锁还适用于一些比较特殊的场景,例如在业务操作过程中无法和数据库保持连接等悲观锁无法适用的地方。

    总结:
    悲观锁和乐观锁是数据库用来保证数据并发安全防止更新丢失的两种方法,例子在select ... for update前加个事务就可以防止更新丢失。悲观锁和乐观锁大部分场景下差异不大,一些独特场景下有一些差别,一般我们可以从如下几个方面来判断。

    • 响应速度: 如果需要非常高的响应速度,建议采用乐观锁方案,成功就执行,不成功就失败,不需要等待其他并发去释放锁。'
    • 冲突频率: 如果冲突频率非常高,建议采用悲观锁,保证成功率,如果冲突频率大,乐观锁会需要多次重试才能成功,代价比较大。
    • 重试代价: 如果重试代价大,建议采用悲观锁。

    七、其他

    1.数据库的主从复制

    主从复制的几种方式:

    同步复制:

    所谓的同步复制,意思是master的变化,必须等待slave-1,slave-2,...,slave-n完成后才能返回。 这样,显然不可取,也不是MySQL复制的默认设置。比如,在WEB前端页面上,用户增加了条记录,需要等待很长时间。

    异步复制:

    如同AJAX请求一样。master只需要完成自己的数据库操作即可。至于slaves是否收到二进制日志,是否完成操作,不用关心。MySQL的默认设置。

    半同步复制:

    master只保证slaves中的一个操作成功,就返回,其他slave不管。 这个功能,是由google为MySQL引入的。

    2.数据库主从复制分析的 7 个问题?

    问题1:master的写操作,slaves被动的进行一样的操作,保持数据一致性,那么slave是否可以主动的进行写操作?

    假设slave可以主动的进行写操作,slave又无法通知master,这样就导致了master和slave数据不一致了。因此slave不应该进行写操作,至少是slave上涉及到复制的数据库不可以写。实际上,这里已经揭示了读写分离的概念。

    问题2:主从复制中,可以有N个slave,可是这些slave又不能进行写操作,要他们干嘛?

    实现数据备份:
    类似于高可用的功能,一旦master挂了,可以让slave顶上去,同时slave提升为master。

    异地容灾:比如master在北京,地震挂了,那么在上海的slave还可以继续。
    主要用于实现scale out,分担负载,可以将读的任务分散到slaves上。
    【很可能的情况是,一个系统的读操作远远多于写操作,因此写操作发向master,读操作发向slaves进行操作】

    问题3:主从复制中有master,slave1,slave2,...等等这么多MySQL数据库,那比如一个JAVA WEB应用到底应该连接哪个数据库?

    我们在应用程序中可以这样,insert/delete/update这些更新数据库的操作,用connection(for master)进行操作,

    select用connection(for slaves)进行操作。那我们的应用程序还要完成怎么从slaves选择一个来执行select,例如使用简单的轮循算法

    这样的话,相当于应用程序完成了SQL语句的路由,而且与MySQL的主从复制架构非常关联,一旦master挂了,某些slave挂了,那么应用程序就要修改了。能不能让应用程序与MySQL的主从复制架构没有什么太多关系呢?
    找一个组件,application program只需要与它打交道,用它来完成MySQL的***,实现SQL语句的路由。
    MySQL proxy并不负责,怎么从众多的slaves挑一个?可以交给另一个组件(比如haproxy)来完成。

    这就是所谓的MySQL READ WRITE SPLITE,MySQL的读写分离。

    问题4:如果MySQL proxy , direct , master他们中的某些挂了怎么办?

    ***一般都会弄个副***,以防不测。同样的,可以给这些关键的节点来个备份。

    问题5:当master的二进制日志每产生一个事件,都需要发往slave,如果我们有N个slave,那是发N次,还是只发一次?如果只发一次,发给了slave-1,那slave-2,slave-3,...它们怎么办?

    显 然,应该发N次。实际上,在MySQL master内部,维护N个线程,每一个线程负责将二进制日志文件发往对应的slave。master既要负责写操作,还的维护N个线程,负担会很重。可以这样,slave-1是master的从,slave-1又是slave-2,slave-3,...的主,同时slave-1不再负责select。 slave-1将master的复制线程的负担,转移到自己的身上。这就是所谓的多级复制的概念。

    问题6:当一个select发往MySQL proxy,可能这次由slave-2响应,下次由slave-3响应,这样的话,就无法利用查询缓存了。

    应该找一个共享式的缓存,比如mem***来解决。将slave-2,slave-3,...这些查询的结果都缓存至mam***中。

    问题7:随着应用的日益增长,读操作很多,我们可以扩展slave,但是如果master满足不了写操作了,怎么办呢?

    scale on ?更好的服务器? 没有最好的,只有更好的,太贵了。。。
    scale out ? 主从复制架构已经满足不了。
    可以分库【垂直拆分】,分表【水平拆分】。

    3.mysql 高并发环境解决方案?

    MySQL 高并发环境解决方案: 分库 分表 分布式 增加二级缓存。。。。。

    需求分析:互联网单位 每天大量数据读取,写入,并发性高。

    现有解决方式:水平分库分表,由单点分布到多点数据库中,从而降低单点数据库压力。

    集群方案:解决DB宕机带来的单点DB不能访问问题。

    读写分离策略:极大限度提高了应用中Read数据的速度和并发量。无法解决高写入压力。

    4.数据库崩溃时事务的恢复机制(REDO日志和UNDO日志)?

    转载:MySQL REDO日志和UNDO日志

    Undo Log:可以参考一下https://www.jianshu.com/p/8845ddca3b23

    Undo Log是为了实现事务的原子性,在MySQL数据库InnoDB存储引擎中,还用了Undo Log来实现多版本并发控制(简称:MVCC)。

    事务的原子性(Atomicity)事务中的所有操作,要么全部完成,要么不做任何操作,不能只做部分操作。如果在执行的过程中发生了错误,要回滚(Rollback)到事务开始前的状态,就像这个事务从来没有执行过。
    原理Undo Log的原理很简单,为了满足事务的原子性,在操作任何数据之前,首先将数据备份到一个地方(这个存储数据备份的地方称为UndoLog)。然后进行数据的修改。如果出现了错误或者用户执行了ROLLBACK语句,系统可以利用Undo Log中的备份将数据恢复到事务开始之前的状态。

    之所以能同时保证原子性和持久化,是因为以下特点:

    更新数据前记录Undo log。
    为了保证持久性,必须将数据在事务提交前写到磁盘。只要事务成功提交,数据必然已经持久化。
    Undo log必须先于数据持久化到磁盘。如果在G,H之间系统崩溃,undo log是完整的, 可以用来回滚事务。
    如果在A-F之间系统崩溃,因为数据没有持久化到磁盘。所以磁盘上的数据还是保持在事务开始前的状态。

    缺陷:每个事务提交前将数据和Undo Log写入磁盘,这样会导致大量的磁盘IO,因此性能很低。
    如果能够将数据缓存一段时间,就能减少IO提高性能。但是这样就会丧失事务的持久性。因此引入了另外一种机制来实现持久化,即Redo Log。

    Redo Log:

    原理和Undo Log相反,Redo Log记录的是新数据的备份。在事务提交前,只要将Redo Log持久化即可,不需要将数据持久化。当系统崩溃时,虽然数据没有持久化,但是Redo Log已经持久化。系统可以根据Redo Log的内容,将所有数据恢复到最新的状态。

    八、整理时参考的资料

    java团长 数据库整理

    20个数据库常见面试题讲解 - 鹏磊 - 开源中国"

    34个数据库常见面试题讲解

    漫谈数据库索引知识库博客园

    Mysql| 使用通配符进行模糊查询(like,%,_)

    MySQL存储引擎中的MyISAM和InnoDB区别详解

    MySQL存储引擎之MyISAM和Innodb总结性梳理

    https://blog.csdn.net/liangxw1/article/details/51197560

    个表中存在的另一个表的主键称此表的外键。

    展开全文
  • d2,…,dn)叫作一个n元组(n-tuple)元组是关系数据库中的基本概念,关系是一张表,表中的每行(即数据库中的每条记录)就是一个元组,每列就是一个属性二维表里,元组也称为记录。 public class TwoTuple{//...
    Tuple 元组
    笛卡尔积(两个集合X和Y的乘积)中每一个元素(d1,d2,…,dn)叫作一个n元组(n-tuple)
    元组是关系数据库中的基本概念,关系是一张表,表中的每行(即数据库中的每条记录)就是一个元组,每列就是一个属性。 在二维表里,元组也称为记录。
     
    public class TwoTuple< A, B>{//利用泛型传入不同类型参数
    public final A first;//final 防止修改 可以代替 private getter方法
    public final B second;
     
    public TwoTuple(A a, B b ){
    first =a;
    seconde = b;
    }
     
    public class ThreeTuple<A, B,C> extends TwoTuple< A, B>{//利用继承可以实现多层次Tuple
    public final C three;
     
    public ThreeTuple(A a,B b,C c){
    super.(a b);
    three = c;
    }
    展开全文
  • 首先,关系数据库管理系统执行插入、修改、删除语句时会检查是否破坏已定义完整性规则, 包括:实体完整性、参照完整性、用户定义完整性(not null约束,unique约束,值域约束)。 1、插入数据: insert into ...
  • a)没有WHERE条件,删除关系中的所有元组。 b)指定WHERE条件,删除满足条件的元组。 (2)实现修改数据库记录的功能。 a)没有WHERE条件,修改所有元组的指定属性的值。 b)指定WHERE条件,修改满足条件的元组的...
  • INTO 子句没有出现的属性列, 新元组在这些属性列上将置空值。但必须注意的是,表定义时说明了 NOT NULL 的属性列不能置空值,否则会出错。 出错原因: 主码具有唯一性和不可重复性,当主码为空时,唯一性约束被...

    增删改语句的介绍:

    一、单个数据插入语句

    INSERT
    INTO 表名 [(属性列 1,属性列 2 … )]
    VALUES (常量 1 ,常量 2 … )

    说明:
    插入一个新元组,新元组属性列 1 的值为常量 1,属性列 2 的值为常量 2…
    INTO 子句中没有出现的属性列, 新元组在这些属性列上将置空值。但必须注意的是,在表定义时说明了 NOT NULL 的属性列不能置空值,否则会出错。
    出错原因: 主码具有唯一性和不可重复性,当主码为空时,唯一性约束被破坏所以主码列不能重复和为空值。如果该列不是主码,则说明设置了unique属性。

    unique约束:
    1.UNIQUE 约束唯一标识数据库表中的每条记录。(可以理解为增加UNIQUE约束的列为只有该表中才有的属性)
    2.UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证。
    3.PRIMARY KEY 拥有自动定义的 UNIQUE 约束。
    参考网址:https://www.runoob.com/sql/sql-unique.html
    请注意,每个表可以有多个 UNIQUE 约束,但是每个表只能有一个 PRIMARY KEY 约束。

    如果 INTO 子句中没有指明任何属性列名,则新插入的记录必须在每个属性列上均有值,而且常量的顺序与属性列的顺序相对应;
    如果基本表后面有属性列,属性列的排列顺序不一定要和表定义时顺序一致, 但VALUES 后的常量值对应于属性列名中的分量的值,个数相等。

    二、成批数据插入语句

    INSERT
    INTO 表名 [(属性列 1,属性列 2 … )]
    子查询

    上述命令的功能是一次将子查询的结果全部插入指定表中。

    三、删除数据语句

    DELETE
    FROM 表名
    WHERE 删除限定条件

    功能:从指定表中删除满足 WHERE 子句条件的所有记录。该语句中没有 WHERE 子句时,表示删除此表中的全部记录,但此表的结构不会被删除,此表的定义仍在数据字典中。
    需要说明的是:
    数据删除只能对整个元组或一条记录操作,不能只删除某些属性上的值;
    数据删除只能对一个表起作用,若要从多个表中删除元组,则必须对每个表分别执行删除命令。

    四、更新数据语句

    UPDATE 表名
    SET 属性列 1=常量 1,属性列 2=常量 2...
    WHERE 更新限定条件

    功能:修改基本表中满足条件表达式的那些元组的属性值,用 SET 子句中的值表达式修改原来的属性值。。如果省略 WHERE 子句,则表示要修改表中的所有元组。

    代码实现:

    1.在 SQL Server Management Studio 中向数据库 XSGL 的 student 表加入如表所示的数据(这里为了方便我重新建立了数据库XSGL2 )

    student 表记录
    在这里插入图片描述

    2.建立一个新表‘成教表’ chengjiao, 结构与 student 表相同。

    T-SQL语句:
    CREATE TABLE chengjiao (
    SNO char (8) not null unique, SNAME char(10),
    SEX char(2), DNO char(8),
    AGE smallint, BIRTHDAY datetime )

    3.将一个新学生(学号:‘ 20067027’,姓名:‘ 张三’,年龄:20,所在系编号:‘ 0002’ )插入到学生表中。

    T-SQL语句:
    INSERT INTO student(SNO,SNAME,AGE,DNO)
    VALUES (‘20067027’,‘张三’,20,‘0002’)

    结果如图:在这里插入图片描述

    4.按如下语句插入另外两个同学的信息到成教表中

    T-SQL语句:
    INSERT INTO chengjiao(SNO,SNAME,AGE,DNO)
    VALUES (‘20067011’,‘王二’,23,‘0003’)
    INSERT INTO chengjiao(SNO,SNAME,AGE,DNO)
    VALUES (‘20067021’,‘张三’,19,‘0003’)

    说明:插入的数据必须符合数据库的完整性约束
    结果如图:在这里插入图片描述

    5.将成教表 chengjiao 中的所有学生一次性添加到学生表 student 中。

    T-SQL语句:
    INSERT INTO student (SNO,SNAME,SEX,DNO)
    (SELECT SNO,SNAME,SEX,DNO FROM chengjiao)

    结果如图:在这里插入图片描述

    6.依据学生的生日, 计算出该学生的年龄

    T-SQL语句:
    UPDATE student
    SET AGE=(YEAR(GETDATE())-YEAR(BIRTHDAY))

    未计算之前从上图可以看出李一和李二的生日并未显示

    运行后结果如图:在这里插入图片描述

    7.将所有安排在 B101 的课程调整到 A302(这里以及下面的T-SQL语句的实现,我用到了老师提供的数据库包括课程表,选课表等等)

    T-SQL语句:
    UPDATE course
    SET ROOM=‘A302’
    WHERE ROOM='B101’

    前后对比如下图:
    在这里插入图片描述
    在这里插入图片描述

    8.将选课表中的‘概率论与数理统计’课程的成绩减去 4 分

    T-SQL语句:
    UPDATE sc SET GRADE=GRADE-4
    WHERE CNO IN
    (SELECT CNO FROM course
    WHERE CNAME=‘概率论于数理统计’)

    组课程表这里我们可以看到:概率论与数理统计的课程号CNO为000401
    在这里插入图片描述
    然后在sc表即选课表中发现其中一个学生的概率论与数理统计的成绩如下图:

    在这里插入图片描述
    运行上述T-SQL语句后,结果如下图:

    在这里插入图片描述

    9.从排课表中删除‘ 张聪’ 老师的所有排课纪录

    T-SQL语句:
    DELETE FROM course
    WHERE TNAME='张聪’

    这里我直接执行该语句发现有错误:
    DELETE 语句与 REFERENCE 约束"FK__sc__CNO__24927208"冲突

    仔细看sc选课表跟course课程表发现两表都有课程名CNO这一项,即有外键对两表进行约束,不能直接删除相关内容,所以我们要解除外键的约束(参考CSDN文章:SQL主键和外键约束
    在这里插入图片描述

    在这里插入图片描述
    在查询中执行下面语句:
    alter table sc drop CONSTRAINT FK__sc__CNO__24927208

    在这里插入图片描述
    这时我们再运行删除相关语句,前后结果对比如下:

    在这里插入图片描述
    在这里插入图片描述
    删掉了🙊🙊👏🏾👏🏾👏🏾👏🏾

    10. 删除学院编号为空的学生记录及选课记录(这个语句由于表中数据太多 不好举例子 所以就跳过)

    T-SQL语句:
    DELETE FROM sc WHERE SNO IN
    (SELECT SNO FROM student WHERE DNO is NULL)
    DELETE FROM student WHERE DNO is NULL

    说明:为了满足数据的完整性约束要求,必须先在选课表中删除该生的选课纪录,再在学生表中删除该名学生

    11. 删除表 ’ excelxuanke’

    T-SQL语句:
    DROP table excelxuanke

    这里我直接建了一个表excelxuanke来举例
    运行语句前后:在这里插入图片描述
    在这里插入图片描述
    也是删掉了👏🏻👏🏻👏🏻👏🏻

    展开全文
  • 数据库——知识梳理

    2019-10-02 02:01:46
    2.元组(tuple)是关系数据库中的基本概念,关系是一张表,表中的每行(即数据库中的每条记录)就是一个元组,每列就是一个属性。 二维表里,元组也称为行。 3.超键(super key):关系中能唯一标识元组的属性集.....
  • 10.1.1 给予关系中的属性以清晰的语义 221 10.1.2 元组中的冗余信息和更新异常 223 10.1.3 元组中的NULL值 225 10.1.4 生成寄生元组 226 10.1.5 总结和讨论设计准则 227 10.2 函数依赖 228 ...
  • 数据库练练习题..pdf

    2020-12-15 06:36:34
    一个关系中 某一个属性属性值能唯一地标识该关系的元组 而 装 其真子集不行则称该属性属性组为候选码 3. 每个 Oracle数据库中只有一个重做日志文件 4. DBMS 提供数据定义语言 DDL 实现对数据库检索...
  • 数据库系统与文件系统的主要区别是:文件系统不能解决数据冗余和数据独立性问题,而数据库系统可以解决 一个关系模型中,任意两个元组的值不能完全相同。 当关系是一张表,二维表中的行表...而元组中的元素无法修改
  • 《数据库系统概论》实验...(4)浏览数据库中基本表中的数据。 实验要求: 1)图书-读者库中向图书、读者和借阅3个表中各输入10条记录。要求记录不仅满足数据约束要求,要有表间关联的记录。 2)对图书借阅数据库的
  • 数据库完整性

    2017-07-11 10:51:19
    第五章 数据库完整性 1.数据库的完整性是指:数据实体完整性,参照完整性,用户定义完整性。 2.实体完整性是指基本表,主属性不能为空。 3.实体完整性规则检查:检查主...5.当网表插入元组修改属性的值时
  • 8.2.3 使用属性版本化面向对象数据库中加入时间 159 8.2.4 时态查询构造与TSQL2语言 160 8.2.5 时间序列数据 161 8.3 空间和多媒体数据库 162 8.3.1 空间数据库概念介绍 162 8.3.2 多媒体...
  • 数据库 第五章例题

    2021-04-25 12:09:12
    文章目录数据库完整性实体完整性参照完整性用户定义的完整性属性上的约束条件元组上的约束条件完整性约束命名子句修改中的完整性限制断言触发器定义触发器 数据库完整性 数据库完整性是指数据的正确性和相容性 ...
  • 数据库系统用户可以对这些表进行查询,可以插入新元组、删除元组以及更新(修改元组。 关系模式是指它逻辑设计,而关系实例是指它特定时刻内容。数据库的模式和实例定义是类似。关系模式包括它...
  • 数据库系统实现

    2013-05-12 13:09:11
    3.2.3 定长记录中的放置 习题 3.3 块和记录地址的表示 3.3.1 客户机-服务器系统 3.3.2 逻辑地址和结构地址 3.3.3 指针混写 3.3.4 块返回磁盘 3.3.5 被固定的记录和块 习题 3.4 变长数据和...
  • 如果省略WHERE子句,则表示要修改中的所有元组。  删除语句的一般格式为 DELETE FROM <表名> [WHERE <条件>]; 其功能是从指定表中删除满足WHERE子句条件的所有元组。如果省略WHERE子句,则表示要删除表中的所有...
  • 文章目录数据库完整性5.1 实体完整性5.2 参照完整性5.3 用户定义的完整性5.3.1 属性上的约束条件5.3.2 元组上的约束条件5.4 完整性约束命名子句1.完整性约束命名子句2. 修改中的完整性限制5.6 断言 数据库完整性 ...
  • 修改操作对象也可以是元组的集合,相对于面向记录的数据库语言一次只能操作一条记录来 语言使用简化了用户处理,提高了应用程序运行效率 语言简洁,方便易学 语言功能强大,格式规范,表达简洁,接近英语语法...
  • 在数据库中,关系(relation)指的是表。元组(tuple)指的是每一条记录,其是数据库的最小公民。属性(attribute)指的是表中的每一列。 关系数据库系统使得程序员不需要关心存储结构,查询用高级语言表示,提高了程序员...
  • 数据库的完整性是指数据正确性和相容性。如学号必须唯一,性别必须是男或女,年龄范围18-25等。 一、实体完整性 实体性规则如下: ...1、SC表增加一个元组,该元组的Sno属性的Stude...
  • 通俗一点就是对数据库中的属性的约束条件。 第一范式 1NF 第一范式的条件:元组中的每一个分量都必须是不可分割的数据项。 反例: 应该修改为: 第二范式 2NF 第二范式的条件:第一范式的基础上,所有的非主...
  • 这一章主要内容就是说SQL的基本操作:定义关系模式,查询指定元组集合,删除添加修改关系模式信息和元组信息 一、SQL数据定义 数据库中的关系集合需要由数据定义语言(DDL)指定,特殊的对于SQL的DDL,除了定义...
  • Hibernate_3.2.0_符合Java习惯关系数据库持久化

    千次下载 热门讨论 2009-01-14 08:06:55
    3.4.5. 查询语言中的替换 3.4.6. Hibernate的统计(statistics)机制 3.5. 日志 3.6. 实现NamingStrategy 3.7. XML配置文件 3.8. J2EE应用程序服务器的集成 3.8.1. 事务策略配置 3.8.2. JNDI绑定的...
  • 超键:关系中能唯一标识元组的属性集称为关系模式的超键。 比如一张学生信息表,学生表含有学号或者身份证号的任意组合都为此表的超键。如:(学号)、(学号,姓名)、(身份证号,性别)等。 候选键:不含有
  • (2)修改表结构,包括修改属性列的数据类型,增加新的属性列,删除已有的属性列。 (3)使用单个元组和多元组插入。 (4)简单查询操作,包括投影、选择、数据排序、模糊匹配查询等。如果结果不正确,要进行修改...
  • 数据库系统概论练习3

    2021-01-06 19:41:21
    一、填空题 能够唯一标识实体的属性或属性组称为____实体的码______。 如果两个关系没有公共属性,则其自然联接操作与____笛卡尔积_____操作等价。... SQL的授权语句中的关键字PUBLIC表示__全.
  • MySql数据库数据更新操作其高级应用

    千次阅读 2013-10-10 19:16:09
    数据更新操作有3种:向表中添加数据、修改中的数据和删除表中的数据。 用来演示的数据表用student、course、sc三个数据表,数据表具体内容:PHP和MySql数据库,如何获取每个分类的记录的总数 插入数据 插入...

空空如也

空空如也

1 2 3 4 5
收藏数 87
精华内容 34
关键字:

在数据库中修改元组中的属性