面试篇——mysql
背景:在面试过程中发现很多公司对sql查询有要求,虽然不高,但是简单常见的sql查询必须熟练的掌握,否则一面都无法通过,痛定思痛,必须要深入了解下!!!
注释符号
聚集索引和非聚集索引的区别?
InnoDB 存储引擎支持以下几种常见的索引: B+树索引、全文索引、哈希索引 而 B+树索引最为常见,可以分为聚集索引和非聚集索引。非聚集索引也可以叫做辅助索引,二级索引。
两种索引相同点: 内部都是 B+ 树,高度平衡,叶子节点存放着所有的数据。
不同点:
聚集索引的叶子节点存放是一整行的信息。 聚集索引一个表只能有一个,而非聚集索引一个表可以存在多个。
聚集索引存储记录是物理上连续存在,而非聚集索引是逻辑上的连续,物理存储并不连续。
聚集索引查询数据速度快,插入数据速度慢;非聚集索引反之。
聚集索引范围查询快。
聚集索引:
InnoDB 存储引擎表是索引组织表,表种数据按照主键顺序存放,而聚集索引就是按照每张表的主键构造一颗 B+ 数,同时叶子节点中存放的就是整张表的行记录数据,也将聚集索引的叶子节点称为数据页。 每张表只能拥有一个聚集索引。 查询优化器倾向于采用聚集索引。
非聚集索引:
叶子节点不包含记录的全部数据。 叶子节点中索引行中还包含了一个书签,用来告诉 InnoDB 存储引擎在哪里可以找到与索引相应的行数据。 这个书签就是相应的行数据的聚集索引键。 可以有多个非聚集索引。 使用非聚集索引来寻找数据时,通过叶级别的指针获得指向主键索引的主键,再通过主键索引找到一个完整的行记录。
面试题1 查询没有成绩的学生信息
参考下面的连接建表:
https://cloud.tencent.com/developer/article/1070155
select a.SID , a.Sname from Student a left join sc b on a.SID = b.SID where ISNULL(b.score) =1 group by a.SID , a.Sname order by a.SID # isnull作为函数参数 -- isnull作为函数参数 /* isnull作为函数参数 */ select a.SID , a.Sname, ISNULL(b.score) =1 from Student a left join sc b on a.SID = b.SID group by a.SID , a.Sname order by a.SID
PS:如果ISNULL为1 就表示查询出所有有成绩的学生信息。上面三种注释方式都可以;
MySql 里的IFNULL、NULLIF和ISNULL用法
创建一个表,并设置联合主键
数据库B树和B+树理解
为什么 B+ 树比 B 树更适合应用于数据库索引?
-
B+树更加适应磁盘的特性,相比B树减少了I/O读写的次数。由于索引文件很大因此索引文件存储在磁盘上,B+树的非叶子结点只存关键字不存数据,因而单个页可以存储更多的关键字,即一次性读入内存的需要查找的关键字也就越多,磁盘的随机I/O读取次数相对就减少了。
-
B+树的查询效率相比B树更加稳定,由于数据只存在在叶子结点上,所以查找效率固定为O(log n)。
-
B+树叶子结点之间用链表有序连接,所以扫描全部数据只需扫描一遍叶子结点,利于扫库和范围查询;B树由于非叶子结点也存数据,所以只能通过中序遍历按序来扫。也就是说,对于范围查询和有序遍历而言,B+树的效率更高。
B+tree性质:
1.)n棵子tree的节点包含n个关键字,不用来保存数据而是保存数据的索引。
2.)所有的叶子结点中包含了全部关键字的信息,及指向含这些关键字记录的指针,且叶子结点本身依关键字的大小自小而大顺序链接。
3.)所有的非终端结点可以看成是索引部分,结点中仅含其子树中的最大(或最小)关键字。
4.)B+ 树中,数据对象的插入和删除仅在叶节点上进行。
5.)B+树有2个头指针,一个是树的根节点,一个是最小关键码的叶节点
索引的基本原理
索引用来快速地寻找那些具有特定值的记录。如果没有索引,一般来说执行查询时遍历整张表。
索引的原理很简单,就是把无序的数据变成有序的查询
1、把创建了索引的列的内容进行排序
2、对排序结果生成倒排表
3、在倒排表内容上拼上数据地址链
4、在查询的时候,先拿到倒排表内容,再取出数据地址链,从而拿到具体数据
MySQL数据类型格式及长度
char:定长,效率高,一般用于固定长度的表单提交数据存储 ;例如:身份证号,手机号,电话,密码等
varchar:不定长,效率偏低
CHAR和VARCHAR最大的不同就是一个是固定长度,一个是可变长度。由于是可变长度,因此实际存储的时候是实际字符串再加上一个记录 字符串长度的字节(如果超过255则需要两个字节)。如果分配给CHAR或VARCHAR列的值超过列的最大长度,则对值进行裁剪以使其适合。如果被裁掉 的字符不是空格,则会产生一条警告。如果裁剪非空格字符,则会造成错误(而不是警告)并通过使用严格SQL模式禁用值的插入。
关于int的解释:
CREATE TABLE `test` (
`id` int(1) NOT NULL,
`ids` int(10) default NULL,
`c` char(1) default NULL,
`cs` varchar(2) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- Records
-- ----------------------------
INSERT INTO `test` VALUES ('1', '1', 'a', 'aa');
INSERT INTO `test` VALUES ('10', '10', 'a', 'aa');
INSERT INTO `test` VALUES ('2147483647', '2147483647', 'a', 'aa');
![](https://s4.51cto.com/attachment/200904/200904201240191882984.jpg)