- 1 为什么要一定要设置主键?
- 2.自增主键用完了怎么办?
- 3.为什么不直接存储图片、音频、视频等大容量内容?
- 4.有几种不同的join?
- 5.Sql和my sql的区别是什么?
- 6.Delete和truncate的区别是什么?
- 7.Drop跟truncate的区别是什么?
- 8.什么是关系?有几种关系?
- 9.什么是主键?
- 10. 什么是外键?
- 11.如何随机的从表中取行?
- 12.Sql server的TCP/ip端口是什么?
- 13.什么是SQL?
- 14.SQL中的Constraints(约束)是什么?
- 15.什么是Index(索引)?
- 16.Clustered(群集)和Non-Clustered Index(非群集)索引之间有什么区别?
- 17.什么是Aggregate Functions(聚合函数)?
- 18.主键是用自增还是UUID?
- 19.CHAR 和 VARCHAR 的区别?
- 20.主键为什么不推荐有业务含义?
- 21.货币字段用什么类型?
- 22.索引应该建在那些列上?
- 23.什么是存储过程?有哪些优缺点?
- 24.字段为什么要定义为NOT NULL?
- 25 索引的分类有哪些?
- 26 时间字段用什么类型?
- 27.什么是最左前缀原则?
- 38.什么是事务?
- 39.mysql有关权限的表都有哪几个?
- 40.什么情况下应不建或少建索引?
- 41.数据表损坏的修复方式有哪些?
- 42.MySQL数据库cpu飙升到100%的话他怎么处理?
- 43.维护数据库的完整性和一致性,你喜欢用触发器还是自写业务逻辑?为什么?
- 44.游标是什么?
- 45数据库NULL是什么意思?
- 46.关系型数据库和非关系型数据库区别?
- 47.查询语句的执行先后顺序?
- 48.什么情况下会造成死锁?
- 49死锁如何解决?
- 50.触发器的作用?
- 51在数据库中查询语句速度很慢,如何优化?
- 52 什么是PL/SQL?
- 53使用SQL命令将教师表teacher中工资salary字段的值增加500,应该使用的命令?
- 54.mdf、.ldf、.tif 、.ndf哪个不是sql 数据库文件的后缀?
- 56.drop、delete与truncate分别在什么场景之下使用?
- 57.MyISAM索引与InnoDB索引的区别?
- 58.有哪几种类型的SQL命令?
1 为什么要一定要设置主键?
其实这个不是一定的,有些场景下,小系统或者没什么用的表,不设置主键也没关系,mysql最好是用自增主键,主要是以下两个原因:如果定义了主键,那么InnoDB会选择主键作为聚集索引、如果没有显式定义主键,则innodb 会选择第一个不包含有NULL值的唯一索引作为主键索引、如果也没有这样的唯一索引,则innodb 会选择内置6字节长的ROWID作为隐含的聚集索引。所以,反正都要生成一个主键,那你还不如自己指定一个主键,提高查询效率!
2.自增主键用完了怎么办?
在mysql中,Int整型的范围(-2147483648~2147483648),约20亿!因此不用考虑自增ID达到最大值这个问题。而且数据达到千万级的时候就应该考虑分库分表了。
3.为什么不直接存储图片、音频、视频等大容量内容?
我们在实际应用中,都是文件形式存储的。mysql中,只存文件的存放路径。虽然mysql中blob类型可以用来存放大容量文件,但是,我们在生产中,基本不用!主要有如下几个原因:
- Mysql内存临时表不支持TEXT、BLOB这样的大数据类型,如果查询中包含这样的数据,查询效率会非常慢。
- 数据库特别大,内存占用高,维护也比较麻烦。
- binlog太大,如果是主从同步的架构,会导致主从同步效率问题!因此,不推荐使用blob等类型!
4.有几种不同的join?
Inner join, left join, right join, full join.
Inner join, 就是只要有一个列能够匹配, 就简单的返回两个table中所有的对应行。
select columnnames from tablename1 inner join tablename2 on columnname1=columnname2
Left join也就是left outer join。当有一个列能够匹配时就返回左边表中所有的行。
select columnnames from tablename1 left join tablename2 on columnname1=columnname2
Right join也就是right outer join,当有列匹配时,返回右边表格中所有的行。
select columnnames from tablename1 right join tablename2 on columnname1=columnname2
Full join也就是full outer join, 当有匹配时, 会返回左边表格和右边表格任意情况下的行组合。
5.Sql和my sql的区别是什么?
Sql是结构化查询语言,my sql是一个关系型数据库。
6.Delete和truncate的区别是什么?
Delete是dml,truncate是ddl。
Delete是用来删除一行或者多行。Truncate是用来删除一个表中的所有行。
我们可以用where跟delete结合使用。Truncate不可以。
7.Drop跟truncate的区别是什么?
truncate是删除表中所有的行,drop是删除整张表。这两个操作都不可以回撤。
8.什么是关系?有几种关系?
关系是指多表在数据库中的关联。
有4种关系。
1对1,多对一,多对多,一对多。
9.什么是主键?
唯一标识一条记录,不能有重复的,不允许为空。
10. 什么是外键?
表的外键是另一表的主键, 外键可以有重复的, 可以是空值。
11.如何随机的从表中取行?
select * from tablename sample 10
12.Sql server的TCP/ip端口是什么?
1433
13.什么是SQL?
SQL(结构化查询语言)是一种设计用于检索和操作数据的数据库。它属于美国国家标准协会(ANSI)的一种标准,可用于执行Select(选择)、Update(更新)、Delete(删除)和Insert(插入)等数据任务。
14.SQL中的Constraints(约束)是什么?
它可用于设置表中数据类型的限制。在创建或更新表语句时,可以使用约束。一些限制是:
NOT NULL、PRIMARY KEY、FOREIGN KEY、UNIQUE、CHECK、DEFAULT
15.什么是Index(索引)?
索引用于加速查询的性能。它可以更快地从表中检索数据。可以在一组列上创建索引。
16.Clustered(群集)和Non-Clustered Index(非群集)索引之间有什么区别?
聚集索引——有助于轻松检索数据,并且只有一个聚集索引与一个表一起分配。它会更改记录在数据库中的保存方式。
非聚集索引——与聚集索引相比,非聚集索引很慢。并且在非集群索引的情况下,该表可以具有多个索引,为表创建一个对象,该表是搜索后指向表的一个点。
17.什么是Aggregate Functions(聚合函数)?
它是一个返回单个值的数学函数。SQL中的聚合函数是:
AVG()——返回平均值
COUNT()——返回行数
MAX()——返回最大值
MIN()——返回最小值
ROUND()——基于十进制规范,此函数对数字字段进行舍入
SUM()——返回总和
18.主键是用自增还是UUID?
最好是用自增主键,主要是以下两个原因:
1.如果表使用自增主键,那么每次插入新的记录,记录就会顺序添加到当前索引节点的后续位置,当一页写满,就会自动开辟一个新的页。
2.如果使用非自增主键(如uuid),由于每次插入主键的值近似于随机,因此每次新纪录都要被插到索引页的随机某个位置,此时MySQL为了将新记录插到合适位置而移动数据,甚至目标页面可能已经被回写到磁盘上而从缓存中清掉,此时又要从磁盘上读回来,这增加了很多开销,同时频繁的移动、分页操作造成索引碎片,得到了不够紧凑的索引结构,后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面。
不过,也不是所有的场景下都得使用自增主键,可能场景下,主键必须自己生成,不在乎那些性能的开销。那也没有问题。
19.CHAR 和 VARCHAR 的区别?
char和varchar类型声明长度表示用户想保存的最大字符数,其中char(M)定义的列的长度为固定的,M的取值可以0-255之间,当保存char值时,在它们的右边填充空格以达到指定的长度。
当检索到char值时,尾部的空格被删除掉。在存储或检索过程中不进行大小写转换。char存储定长数据很方便,char字段上的索引效率很高。
varchar(M)定义的列的长度是可变长度字符串,在MySQL5.0以上的版本中,varchar的数据类型长度支持到了65535,因为起始位和结束位占去了3个字节,所以其整体最大长度为65532字节(varchar的最大有效长度由最大行大小和使用的字符集确定)。
同样在char和varchar尾部加空格,检索时char类型后的被删掉,而varchar类型的保存。
20.主键为什么不推荐有业务含义?
最好是主键是无意义的自增ID,然后另外创建一个业务主键ID, 因为任何有业务含义的列都有改变的可能性,主键一旦带上了业务含义,那么主键就有可能发生变更。主键一旦发生变更,该数据在磁盘上的存储位置就会发生变更,有可能会引发页分裂,产生空间碎片。
还有就是,带有业务含义的主键,不一定是顺序自增的。那么就会导致数据的插入顺序,并不能保证后面插入数据的主键一定比前面的数据大。如果出现了,后面插入数据的主键比前面的小,就有可能引发页分裂,产生空间碎片。
21.货币字段用什么类型?
货币字段一般都用 Decimal类型, float和double是以二进制存储的,数据大的时候,可能存在误差。
22.索引应该建在那些列上?
在经常需要搜索的列上,可以加快搜索的速度;
在作为主键的列上,强制该列的唯一性和组织表中数据的排列结构;
在经常用在连接的列上,这些列主要是一些外键,可以加快连接的速度;
在经常需要根据范围进行搜索的列上创建索引,因为索引已经排序,其指定的范围是连续的;
在经常需要排序的列上创建索引,因为索引已经排序,这样查询可以利用索引的排序,加快排序查询时间;
在经常使用在WHERE子句中的列上面创建索引,加快条件的判断速度。
23.什么是存储过程?有哪些优缺点?
存储过程就像我们编程语言中的函数一样,封装了我们的代码(PLSQL、T-SQL)
存储过程的优点
- 能够将代码封装起来
- 保存在数据库之中
- 让编程语言进行调用
- 存储过程是一个预编译的代码块,执行效率比较高
- 一个存储过程替代大量T_SQL语句 ,可以降低网络通信量,提高通信速率
存储过程的缺点:
- 每个数据库的存储过程语法几乎都不一样,十分难以维护(不通用)
- 业务逻辑放在数据库上,难以迭代
24.字段为什么要定义为NOT NULL?
一般情况,都会设置一个默认值,不会出现字段里面有null,又有空的情况。主要有以下几个原因:
索引性能不好,Mysql难以优化引用可空列查询,它会使索引、索引统计和值更加复杂。可空列需要更多的存储空间,还需要mysql内部进行特殊处理。可空列被索引后,每条记录都需要一个额外的字节,还能导致MYisam 中固定大小的索引变成可变大小的索引。
如果某列存在null的情况,可能导致count() 等函数执行不对的情况。看一下2个图就明白了:
sql 语句写着也麻烦,既要判断是否为空,又要判断是否为null等。
25 索引的分类有哪些?
唯一索引:唯一索引不允许两行具有相同的索引值
主键索引:为表定义一个主键将自动创建主键索引,主键索引是唯一索引的特殊类型。主键索引要求主键中的每个值是唯一的,并且不能为空
聚集索引(Clustered):表中各行的物理顺序与键值的逻辑(索引)顺序相同,每个表只能有一个
非聚集索引(Non-clustered):非聚集索引指定表的逻辑顺序。数据存储在一个位置,索引存储在另一个位置,索引中包含指向数据存储位置的指针。可以有多个,小于249个
26 时间字段用什么类型?
这个看具体情况和实际场景,timestamp ,datatime ,bigint 都行!把理由讲清楚就行!
timestamp,该类型是四个字节的整数,它能表示的时间范围为1970-01-01 08:00:01到2038-01-19 11:14:07。2038年以后的时间,是无法用timestamp类型存储的。
但是它有一个优势,timestamp类型是带有时区信息的。一旦你系统中的时区发生改变,例如你修改了时区,该字段的值会自动变更。这个特性用来做一些国际化大项目,跨时区的应用时,特别注意!
datetime,占用8个字节,它存储的时间范围为1000-01-01 00:00:00 ~ 9999-12-31 23:59:59。显然,存储时间范围更大。但是它坑的地方在于,它存储的是时间绝对值,不带有时区信息。如果你改变数据库的时区,该项的值不会自己发生变更!
bigint,也是8个字节,自己维护一个时间戳,查询效率高,不过数据写入,显示都需要做转换。
27.什么是最左前缀原则?
最左前缀原则指的是,如果查询的时候查询条件精确匹配索引的左边连续一列或几列,则此列就可以被用到。如下:
select * from user where name=xx and city=xx ; //可以命中索引
select * from user where name=xx ; // 可以命中索引
select * from user where city=xx ; // 无法命中索引
这里需要注意的是,查询的时候如果两个条件都用上了,但是顺序不同,如 city= xx and name =xx,那么现在的查询引擎会自动优化为匹配联合索引的顺序,这样是能够命中索引的。
由于最左前缀原则,在创建联合索引时,索引字段的顺序需要考虑字段值去重之后的个数,较多的放前面。ORDER BY子句也遵循此规则。
38.什么是事务?
事务简单来说:一个Session中所进行所有的操作,要么同时成功,要么同时失败
ACID — 数据库事务正确执行的四个基本要素包含:
原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)、持久性(Durability)。
一个支持事务(Transaction)中的数据库系统,必需要具有这四种特性,否则在事务过程(Transaction processing)当中无法保证数据的正确性,交易过程极可能达不到交易。
举个例子:A向B转账,转账这个流程中如果出现问题,事务可以让数据恢复成原来一样【A账户的钱没变,B账户的钱也没变】。
39.mysql有关权限的表都有哪几个?
MySQL服务器通过权限表来控制用户对数据库的访问,权限表存放在mysql数据库里,由mysql_install_db脚本初始化。这些权限表分别user,db,table_priv,columns_priv和host。下面分别介绍一下这些表的结构和内容:
user权限表:记录允许连接到服务器的用户帐号信息,里面的权限是全局级的。
db权限表:记录各个帐号在各个数据库上的操作权限。
table_priv权限表:记录数据表级的操作权限。
columns_priv权限表:记录数据列级的操作权限。
host权限表:配合db权限表对给定主机上数据库级操作权限作更细致的控制。这个权限表不受GRANT和REVOKE语句的影响。
40.什么情况下应不建或少建索引?
表记录太少
经常插入、删除、修改的表
数据重复且分布平均的表字段,假如一个表有10万行记录,有一个字段A只有T和F两种值,且每个值的分布概率大约为50%,那么对这种表A字段建索引一般不会提高数据库的查询速度。
经常和主字段一块查询但主字段索引值比较多的表字段
41.数据表损坏的修复方式有哪些?
使用 myisamchk 来修复,具体步骤:
1.修复前将mysql服务停止。
2.打开命令行方式,然后进入到mysql的/bin目录。
3.执行myisamchk –recover 数据库所在路径/*.MYI
使用repair table 或者 OPTIMIZE table命令来修复,REPAIR TABLE table_name 修复表 OPTIMIZE TABLE table_name 优化表 REPAIR TABLE 用于修复被破坏的表。
OPTIMIZE TABLE 用于回收闲置的数据库空间,当表上的数据行被删除时,所占据的磁盘空间并没有立即被回收,使用了OPTIMIZE TABLE命令后这些空间将被回收,并且对磁盘上的数据行进行重排(注意:是磁盘上,而非数据库)
42.MySQL数据库cpu飙升到100%的话他怎么处理?
列出所有进程 show processlist 观察所有进程 多秒没有状态变化的(干掉)
查看慢查询,找出执行时间长的sql;
explain分析sql是否走索引,sql优化;
检查其他子系统是否正常,是否缓存失效引起,需要查看buffer命中率;
43.维护数据库的完整性和一致性,你喜欢用触发器还是自写业务逻辑?为什么?
尽可能使用约束,如check,主键,外键,非空字段等来约束,这样做效率最高,也最方便。其次是使用触发器,这种方法可以保证,无论什么业务系统访问数据库都可以保证数据的完整新和一致性。
最后考虑的是自写业务逻辑,但这样做麻烦,编程复杂,效率低下。
44.游标是什么?
是对查询出来的结果集作为一个单元来有效的处理。游标可以定在该单元中的特定行,从结果集的当前行检索一行或多行。可以对结果集当前行做修改。一般不使用游标,但是需要逐条处理数据的时候,游标显得十分重要。
45数据库NULL是什么意思?
NULL(空)这个值是数据库世界里一个非常难缠的东西。
NULL这个值表示UNKNOWN(未知):它不表示“”(空字符串)。假设您的SQL Server数据库里有ANSI_NULLS,当然在默认情况下会有,对NULL这个值的任何比较都会生产一个NULL值。您不能把任何值与一个 UNKNOWN值进行比较,并在逻辑上希望获得一个答案。您必须使用IS NULL操作符。
46.关系型数据库和非关系型数据库区别?
关系型数据库的优点:
1、容易理解:二维表结构是非常贴近逻辑世界一个概念,关系模型相对网状、层次等其他模型来说更容易理解;
2、使用方便:通用的SQL语言使得操作关系型数据库非常方便;
3、易于维护:丰富的完整性(实体完整性、参照完整性和用户定义的完整性)大大减低了数据冗余和数据不一致的概率;
4、支持SQL,可用于复杂的查询。
5.支持事务
关系型数据库的缺点:
1、为了维护一致性所付出的巨大代价就是其读写性能比较差;
2、固定的表结构;
3、不支持高并发读写需求;
4、不支持海量数据的高效率读写
非关系型数据库优点:
1.无需经过sql层的解析,读写性能很高
2.基于键值对,数据没有耦合性,容易扩展
3.存储数据的格式:nosql的存储格式是key,value形式
非关系型数据库缺点:
不提供sql
47.查询语句的执行先后顺序?
查询中用到的关键词主要包含六个,并且他们的顺序依次为 select—from—where—group by—having—order by
其中select和from是必须的,其他关键词是可选的,这六个关键词的执行顺序 与sql语句的书写顺序并不是一样的,而是按照下面的顺序来执行
from:需要从哪个数据表检索数据
where:过滤表中数据的条件
group by:如何将上面过滤出的数据分组
having:对上面已经分组的数据进行过滤的条件
select:查看结果集中的哪个列,或列的计算结果
order by :按照什么样的顺序来查看返回的数据
48.什么情况下会造成死锁?
所谓死锁: 是指两个或两个以上的进程在执行过程中。因争夺资源而造成的一种互相等待的现象,若无外力作用,它们都将无法推进下去。此时称系统处于死锁状态或系统产生了死锁,这些永远在互相等竺的进程称为死锁进程。表级锁不会产生死锁.所以解决死锁主要还是针对于最常用的InnoDB。
死锁的关键在于:两个(或以上)的Session加锁的顺序不一致。
那么对应的解决死锁问题的关键就是:让不同的session加锁有次序。
49死锁如何解决?
查出的线程杀死 kill
SELECT trx_MySQL_thread_id FROM information_schema.INNODB_TRX;
设置锁的超时时间
Innodb 行锁的等待时间,单位秒。可在会话级别设置,RDS 实例该参数的默认值为 50(秒)。
生产环境不推荐使用过大的 innodb_lock_wait_timeout参数值
该参数支持在会话级别修改,方便应用在会话级别单独设置某些特殊操作的行锁等待超时时间,如下:
set innodb_lock_wait_timeout=1000; —设置当前会话 Innodb 行锁等待超时时间,单位秒。
50.触发器的作用?
触发器是一中特殊的存储过程,主要是通过事件来触发而被执行的。它可以强化约束,来维护数据的完整性和一致性,可以跟踪数据库内的操作从而不允许未经许可的更新和变化。可以联级运算。如,某表上的触发器上包含对另一个表的数据操作,而该操作又会导致该表触发器被触发。
51在数据库中查询语句速度很慢,如何优化?
1.建索引 ,2.减少表之间的关联 3.优化sql,尽量让sql很快定位数据,不要让sql做全表查询,应该走索引,把数据 量大的表排在前面 4.简化查询字段,没用的字段不要,已经对返回结果的控制,尽量返回少量数据 5.尽量用PreparedStatement来查询,不要用Statement
52 什么是PL/SQL?
PL/SQL是一种程序语言,叫做过程化SQL语言(Procedural Language/SQL)。PL/SQL是Oracle数据库对SQL语句的扩展。在普通SQL语句的使用上增加了编程语言的特点,所以PL/SQL把数据操作和查询语句组织在PL/SQL代码的过程性单元中,通过逻辑判断、循环等操作实现复杂的功能或者计算。PL/SQL 只有 Oracle 数据库有。 MySQL 目前不支持 PL/SQL 的。
53使用SQL命令将教师表teacher中工资salary字段的值增加500,应该使用的命令?
Update teacher set salary=salary+500
54.mdf、.ldf、.tif 、.ndf哪个不是sql 数据库文件的后缀?
.tif
55.SQL语言中,条件“年龄BETWEEN 40 AND 50”表示年龄在40至50之间更准确的描述是?
包括40岁和50岁
56.drop、delete与truncate分别在什么场景之下使用?
SQL中的drop、delete、truncate都表示删除,但是三者有一些差别
delete和truncate只删除表的数据不删除表的结构
速度,一般来说: drop> truncate >delete
delete语句是dml,这个操作会放到rollback segement中,事务提交之后才生效;
如果有相应的trigger,执行的时候将被触发. truncate,drop是ddl, 操作立即生效,原数据不放到rollback segment中,不能回滚. 操作不触发trigger.
不再需要一张表的时候,用drop
想删除部分数据行时候,用delete,并且带上where子句
保留表而删除所有数据的时候用truncate
57.MyISAM索引与InnoDB索引的区别?
InnoDB索引是聚簇索引,MyISAM索引是非聚簇索引。
InnoDB的主键索引的叶子节点存储着行数据,因此主键索引非常高效。
MyISAM索引的叶子节点存储的是行数据地址,需要再寻址一次才能得到数据。
InnoDB非主键索引的叶子节点存储的是主键和其他带索引的列数据,因此查询时做到覆盖索引会非常高效
58.有哪几种类型的SQL命令?
SQL命令分为以下类型:
DDL(数据定义语言) - 用于定义数据库的结构。
DCL(数据控制语言) - 用于为用户提供权限。
DML(数据操作语言) - 用于管理数据。
DQL(数据查询语言) - 所有命令都在SQL中,用于检索DQL中的数据。
TCL(事务控制语言) - 用于管理DML所做的更改。