MYSQL面经-命令相关和索引

BineHello

已于 2022-04-08 08:56:04 修改

阅读量298

点赞数

分类专栏：春招面试文章标签： mysql 春招

于 2022-04-08 08:55:39 首次发布

原文链接：https://blog.csdn.net/Mind_programmonkey/article/details/114693532

版权

春招面试专栏收录该内容

6 篇文章 1 订阅

订阅专栏

一、命令相关

1、常见命令

mysql -uroot -p #如果刚安装好MySQL，root是没有密码的*
mysql> mysql -h192.168.206.100 -uroot -p12345678; #u与root可以不加空格

mysql> drop database db_name;    # -- 删除数据库
mysql> use db_name;              #-- 选择数据库
mysql> create table tb_name (字段名 varchar(20), 字段名 char(1));   #-- 创建数据表模板
mysql> show tables;              #-- 显示数据表
mysql> desc tb_name；            # -- 显示表结构
mysql> drop table tb_name；      #-- 删除表



#创建学生表
create table Student(
     Sno char(10) primary key,
     Sname char(20) unique,
     Ssex char(2),
     Sage smallint,
     Sdept char(20)
)；

#第一种形式无需指定要插入数据的列名，只需提供被插入的值即可：
mysql> insert into tb_name values (value1,value2,value3,...);
#第二种形式需要指定列名及被插入的值：
mysql> insert into tb_name (column1,column2,column3,...) values (value1,value2,value3,...);


#插入数据
mysql> insert into Student values ( 20180001,张三,男,20,CS）;
mysql> insert into Student (Sno,Sname,Ssex,Sage,Sdept) values ( 20180003,王五,男,18,MA）;
mysql> insert into Student (Sno,Sname,Ssex,Sage,Sdept) values ( 20180004,赵六,男,20,IS）;

2.指定查询结果中的列标题

通过指定列标题（也叫列别名）可使输出结果更容易被人理解。指定列标题时，可在列名之后使用AS子句；也可使用：列别名=<表达式>的形式指定列标题。AS子句的格式为：列名或计算表达式 [AS] 列标题
模板：

select <字符型字段> as 列标题1,<字符型字段> as 列标题2, <字符型字段> as 列标题3 from bt_name；

3.查询经过计算的列（即表达式的值）

选择行：选择表中的部分行或全部行作为查询的结果。格式: select [all|distinct] [top n[percent]]<目标列表达式列表> from 表名

消除查询结果中的重复行

在select语句中使用distinct关键字可以消除结果集中的重复行,
模板：

select distinct <字符型字段>[,<字符型字段>,...] from tb_name；

限制查询结果中的返回行数

使用top选项可限制查询结果的返回行数，即返回指定个数的记录数。其中：n是一个正整数，表示返回查询结果集的前n行；若带percent关键字，则表示返回结果集的前n%行。

  模板：select top n from tb_name order by ..; /*查询前 n 的数据*/
  模板：select top n percent from tb_name order by ..; /*查询前 n% tb_name的数据*/

查询满足条件的行: 用where子句实现条件查询

通过where子句实现,该子句必须紧跟在From子句之后。格式为：select [all|distinct] [top n[percent]]<目标列表达式列表> from 表名 where <条件>；说明：在查询条件中可使用以下运算符或表达式：

 运算符                 运算符标识
 比较运算符         <=，<，=，>，>=，!=，<>，!>，!<
 范围运算符         between... and，not between... and
 列举运算符         in，not in
 模糊匹配运算符      like，not like
 空值运算符         is null，is not null
 逻辑运算符         and，or，not

使用比较运算符：

模板：select * from tb_name where <字符型字段> >= n ；

指定范围：

用于指定范围的关键字有两个：between…and和 not between…and。
格式为：

select * from tb_name where [not] between <表达式1> and <表达式2>;

其中：between关键字之后的是范围的下限（即低值）,and关键字之后的是范围的上限（即高值）。用于查找字段值在（或不在）指定范围的行。

使用列举:

使用in关键字可以指定一个值的集合，集合中列出所有可能的值，当表达式的值与集合中的任一元素个匹配时，即返回true，否则返回false。

模板：select * from tb_name where <字符型字段> [not] in(值1,值2,...,值n);

使用通配符进行模糊查询:

可用like 子句进行字符串的模糊匹配查询，like子句将返回逻辑值（true或False）。
like子句的格式： select * from tb_name where <字符型字段> [not] like <匹配串>；
其含义是：查找指定字段值与匹配串相匹配的记录。匹配串中通常含有通配符%和_（下划线）。
其中: %：代表任意长度（包括0）的字符串

多重条件查询：使用逻辑运算符

逻辑运算符and（与：两个条件都要满足）和or（或：满足其中一个条件即可）可用来联接多个查询条件。and的优先级高于or,但若使用括号可以改变优先级。
模板：select * from tb_name where <字符型字段> = ‘volues’ and <字符型字段> > n;

5.对查询结果排序

order by子句可用于对查询结果按照一个或多个字段的值（或表达式的值）进行升序（ASC）或降序（DESC）排列，默认为升序。
格式：order by {排序表达式[ASC|DESC]}[,…n]；
其中：排序表达式既可以是单个的一个字段，也可以是由字段、函数、常量等组成的表达式，或一个正整数。
模板：

select * from tb_name order by <排序表达式> <排序方法>；

使用统计函数:又称集函数，聚合函数
在对表进行检索时，经常需要对结果进行计算或统计，T-SQL提供了一些统计函数（也称集函数或聚合函数），用来增强检索功能。统计函数用于计算表中的数据，即利用这些函数对一组数据进行计算，并返回单一的值。
常用统计函数表

        函数名      功能
        AVG         求平均值
        count        求记录个数，返回int类型整数
        max          求最大值
        min           求最小值
        sum          求和

SUM和AVG
功能：求指定的数值型表达式的和或平均值。
模板：

select avg(<字符型字段>) as 平均数,sum(<字符型字段>) as 总数 from tb_name where <字符型字段> ='字符串';

Max和Min
功能：求指定表达式的最大值或最小值。
模板：

select max(<字符型字段>) as 最大值,min(<字符型字段>) as 最小值 from tb_name;

count
该函数有两种格式：count()和count([all]|[distinct] 字段名），为避免出错，查询记录个数一般使用count()，而查询某字段有几种取值用count(distinct 字段名）。
(1).count():
功能：统计记录总数。
模板：select count(<字符型字段>) as 总数 from tb_name;
(2).count([all]|[distinct] 字段名）
功能：统计指定字段值不为空的记录个数，字段的数据类型可以是text、image、ntext、uniqueidentifier之外的任何类型。
```
 模板：select count(<字符型字段>) as 总数 from tb_name;
```

6.对查询结果分组

group by子句用于将查询结果表按某一列或多列值进行分组，列值相等的为一组，每组统计出一个结果。该子句常与统计函数一起使用进行分组统计。
格式为：group by 分组字段[…n] [having <条件表达式>]

在使用group by子句后
select列表中只能包含：group by子句中所指定的分组字段及统计函数。
having子句的用法
having子句必须与group by 子句配合使用，用于对分组后的结果进行筛选（筛选条件中常含有统计函数）。

分组查询时不含统计函数的条件
通常使用where子句；含有统计函数的条件,则只能用having子句。
模板：

  select <字符型字段>,count(<字符型字段>) as 列标题 from tb_name where <字符型字段>='字符串' group by <字符型字段>；

修改数据(Update）
Update 语句用于修改表中的数据。

格式：update tb_name set 列名称 = 新值 where 列名称 = 某值；

删除数据(Delete)

 删除单行
      格式：delete from tb_name where 列名称 = 某值；
  删除所有行
      可以在不删除表的情况下删除所有的行。这意味着表的结构、属性和索引都是完整的：
      格式：

   delete * from tb_name   或  delete from tb_name；

7、MySQL – 应用

学生-课程数据库
学生表：Student（Sno，Sname，Ssex，Sage，Sdept）
课程表：Course（Cno，Cname，Cpno，Ccredit）
学生选课表：SC（Sno，Cno，Grade）
关系的主码加下划线表示。各个表中的数据示例如图所示：

 建立一个“学生”表Student：
create table Student(
  Sno char(9) peimary key, /*列级完整性约束条件，Sno是主码*/
  Sname char(20) unique, /* Sname取唯一值*/
  Ssex char(2),
  Sage smallint,
  Sdept char(20)
);
 建立一个“课程”表Course：
create table Course(
  Sno char(4) primary key, /*列级完整性约束条件，Cname不能取空值*/
  Sname char(40) not null, /*Cpno的含义是先修课*/
  Cpno char(4)
  Ccredit smallint,
  foreign key (Cpnoo) references Course(Cno) /*表级完整性约束条件，Cpno是外码，被参照表是Course，被参照列是Cno*/
);
 建立学生选课表SC：
create table SC(
  Sno char(9),
  Cno char(4),
  Grade smallint,
  frimary key (Sno,Cno), /*主码由两个属性构成，必须作为表级完整性进行定义*/
  foreign key (Sno) references Student(Sno), /*表级完整性约束条件，Sno是外码，被参照表是Student*/
  foreign key (Cno) references Course(Cno)   /*表级完整性约束条件，Cno是外码，被参照表是Course */
);

增插入数据
语法：insert into [table_name] ([column],…) values(“name”,…);
例子：

insert into student(name,age,NCEE_grade,register_date) values("刘备",20,400,"2018-9-1")；
insert into student(name,age,NCEE_grade,register_date) values("关羽",19,380,"2018-8-31");
insert into student(name,age,NCEE_grade,register_date) values("张飞",18,360,"2018-8-30");

删删除行数据
语法：delete from [table_name] where [column_name] = " "
例子：

1.删除叫刘备的小朋友 delete from student where name = "刘备"；
2.删除age大于等于19的小朋友 delete from student where age>=19;
3.删除登记日期在9月之后的 delete from student where date like "2018-09%";

改更改数据值
格式：

update [table_name] set [columns] = " ",[columns] = " " where /

例子：将刘备小朋友的高考成绩改成390

update student set NCEE_grade = 390 where name = "刘备";

原文链接：https://blog.csdn.net/caipengbenren/article/details/98388046

二、MySQL索引

关于MySQL索引的好处，如果正确合理设计并且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车。对于没有索引的表，单表查询可能几十万数据就是瓶颈，而通常大型网站单日就可能会产生几十万甚至几百万的数据，没有索引查询会变的非常缓慢。还是以WordPress来说，其多个数据表都会对经常被查询的字段添加索引，比如wp_comments表中针对5个字段设计了BTREE（二叉树）索引。

合理的设计自己的数据库表和索引可以大大提高数据的检索速度，如果在大表中滥用索引反而会影响你的数据库性能，下边数据库优化有详细提到。

Alter table employees add index first_name (first_name);

2.1 索引类型

2.1.1 B树

大多数存储引擎都支持B树索引。B树通常意味着所有的值都是按顺序存储的，并且每一个叶子也到根的距离相同。B树索引能够加快访问数据的速度，因为存储引擎不再需要进行全表扫描来获取数据。下图就是一颗简单的B🌲。
请添加图片描述

2.1.2 B+树

下图为B+树的结构，B+树是B树的升级版，我们可以观察一下，B树和B+树的区别是什么？

请添加图片描述

2.1.3 B+树和B树的区别是：

B树的节点中没有重复元素，B+树有(因为B树的节点是储存信息的)。
B树的中间节点会存储数据指针信息，而B+树只有叶子节点才存储。
B+树的每个叶子节点有一个指针指向下一个节点，把所有的叶子节点串在了一起。
B+树最大的区别就是所有的数据都是存储在叶子节点上的，而非叶子节点中存储的都是数据索引。并且所有的叶子结点再连接成一个链表！

从下图我们可以直观的看到B树和B+树的区别：紫红色的箭头是指向被索引的数据的指针，大红色的箭头即指向下一个叶子节点的指针。

在这里插入图片描述

我们假设被索引的列是主键，现在查找主键为5的记录，模拟一下查找的过程：

B树，在倒数第二层的节点中找到5后，可以立刻拿到指针获取行数据，查找停止。B+树，在倒数第二层的节点中找到5后，由于中间节点不存有指针信息，则继续往下查找，在叶子节点中找到5，拿到指针获取行数据，查找停止。

B+树每个父节点的元素都会出现在子节点中，是子节点的最大（或最小）元素。叶子节点存储了被索引列的所有的数据。

2.1.4 B+树比起B树有什么优点呢？

由于中间节点不存指针，同样大小的磁盘页可以容纳更多的节点元素，树的高度就小。（数据量相同的情况下，B+树比B树更加“矮胖”），查找起来就更快。
B+树每次查找都必须到叶子节点才能获取数据，而B树不一定，B树可以在非叶子节点上获取数据。因此B+树查找的时间更稳定。
B+树的每一个叶子节点都有指向下一个叶子节点的指针，方便范围查询和全表查询：只需要从第一个叶子节点开始顺着指针一直扫描下去即可，而B树则要对树做中序遍历。

了解了B+树的结构之后，我们对一张具体的表做分析：

create table Student(
 last_name varchar(50) not null, 
 first_name varchar(50) not null, 
 birthday date not null, 
 gender int(2) not null, 
 key(last_name, first_name, birthday)
);

对于表中的每一行数据，索引中包含了name，birthday列的值。下图显示了该索引的结构：

在这里插入图片描述
索引对多个值进行排序的依据是create table语句中定义索引时列的顺序，即如果名字相同，则根据生日来排序。

B+树的结构决定了这种索引对以下类型的查询有效：

全值匹配 和索引中所有的列进行匹配，例如查找姓名为Cuba Allen，生日为1960-01-01的人。
匹配最左前缀查找姓为Allen的人，即只用索引的第一列。
匹配列前缀匹配某一列的值的开头部分，例如查找所有以J开头的姓的人。
匹配范围值查找姓在Allen和Barrymore之间的人。
精确匹配某一列并范围匹配另外一列查找姓为Allen，名字是字母K开头的人。即第一列last_name全匹配，第二列first_name范围匹配。
只访问索引的查询查询只需要访问索引，无需访问数据行。这种索引叫做覆盖索引。

一些限制:

如果不是按照索引的最左列开始查找，无法使用索引。例如上面例子中的索引无法用于查找某个特定生日的人，因为生日不是最左数据列。也不能查找last_name以某个字母结尾的人。
不能跳过索引的列。上述索引无法用于查找last_name为Smith并且某个特定生日的人。如果不指定first_name，则mysql只能使用索引的第一列。
如果查询中有某个列的范围查询，则右边所有的列都无法使用索引优化查找。例如查询WHERE last_name=’Smith’ AND first_name LIKE ‘J%’ AND birthday=‘1996-05-19’，这个查询只能使用索引的前两列。

2.2 MySQL中InnoDB的一级索引、二级索引

每个InnoDB表具有一个特殊的索引称为聚簇索引（也叫聚集索引，聚类索引，簇集索引）。如果表上定义有主键，该主键索引就是聚簇索引。如果未定义主键，MySQL取第一个唯一索引（unique）而且只含非空列（NOT NULL）作为主键，InnoDB使用它作为聚簇索引。如果没有这样的列，InnoDB就自己产生一个这样的ID值，它有六个字节，而且是隐藏的，使其作为聚簇索引。

表中的聚簇索引（clustered index ）就是一级索引，除此之外，表上的其他非聚簇索引都是二级索引，又叫辅助索引（secondary indexes）。

原文链接：https://blog.csdn.net/RoxLiu/article/details/70160664

2.3 数据库Mysql-索引的最左前缀匹配原则

最左前缀匹配原则：

最左优先，以最左边的为起点任何连续的索引都能匹配上。同时如果范围查询(>、<、between、like)就会停止匹配。

2.3.1 例子来理解最左前缀匹配原则

前一篇文中，我们已经了解到Mysql数据库的索引的底层存储是一棵B+树，那么联合索引的底层也还是一棵B+树。只不过联合索引的键值对数量不是一个，而是多个。

假如：构建一个(a,b)的联合索引，那么它在数据库底层的索引树是下列这样的：

可以看到a的值是有顺序的，1，1，2，2，3，3，而b的值是没有顺序的1，2，1，4，1，2。所以b = 2这种查询条件没有办法利用索引，因为联合索引首先是按a排序的，b是无序的。

**同时我们还可以发现在a值相等的情况下，b值又是按顺序排列的，**但是这种顺序是相对的。所以最左匹配原则遇上范围查询就会停止，剩下的字段都无法使用索引。例如a = 1 and b = 2 a,b字段都可以使用索引，因为在a值确定的情况下b是相对有序的，而a>1and b=2，a字段可以匹配上索引，但b值不可以，因为a的值是一个范围，在这个范围中b是无序的。

2.3.2 最左前缀匹配原则适用场景

假如建立联合索引(a,b,c)

1 全值匹配查询时

select * from table_name where a = '1' and b = '2' and c = '3' 
select * from table_name where b = '2' and a = '1' and c = '3' 
select * from table_name where c = '3' and b = '2' and a = '1'

用到了索引

where子句几个搜索条件顺序调换不影响查询结果，因为Mysql中有查询优化器，会自动优化查询顺序

2 匹配左边的列时

select * from table_name where a = '1' 
select * from table_name where a = '1' and b = '2'  
select * from table_name where a = '1' and b = '2' and c = '3'

都从最左边开始连续匹配，用到了索引

select * from table_name where  b = '2' 
select * from table_name where  c = '3'
select * from table_name where  b = '1' and c = '3'

这些没有从最左边开始，最后查询没有用到索引，用的是全表扫描

select * from table_name where a = '1' and c = '3'

如果不连续时，只用到了a列的索引，b列和c列都没有用到

3 匹配列前缀
如果列是字符型的话它的比较规则是先比较字符串的第一个字符，第一个字符小的哪个字符串就比较小，如果两个字符串第一个字符相通，那就再比较第二个字符，第二个字符比较小的那个字符串就比较小，依次类推，比较字符串。

如果a是字符类型，那么前缀匹配用的是索引，后缀和中缀只能全表扫描了

select * from table_name where a like 'As%'; //前缀都是排好序的，走索引查询
select * from table_name where  a like '%As'//全表查询
select * from table_name where  a like '%As%'//全表查询

4 匹配范围值

select * from table_name where  a > 1 and a < 3

可以对最左边的列进行范围查询

select * from table_name where  a > 1 and a < 3 and b > 1;

多个列同时进行范围查找时，只有对索引最左边的那个列进行范围查找才用到B+树索引，也就是只有a用到索引，在1<a<3的范围内b是无序的，不能用索引，找到1<a<3的记录后，只能根据条件 b > 1继续逐条过滤

5 精准匹配某一列并范围匹配另外一列
如果左边的列是精确查找的，右边的列可以进行范围查找

select * from table_name where  a = 1 and b > 3;

a=1的情况下b是有序的，进行范围查找走的是联合索引

6 排序
一般情况下，我们只能把记录加载到内存中，再用一些排序算法，比如快速排序，归并排序等在内存中对这些记录进行排序，有时候查询的结果集太大不能在内存中进行排序的话，还可能暂时借助磁盘空间存放中间结果，排序操作完成后再把排好序的结果返回客户端。Mysql中把这种在内存中或磁盘上进行排序的方式统称为文件排序。文件排序非常慢，但如果order子句用到了索引列，就有可能省去文件排序的步骤

select * from table_name order by a,b,c limit 10;

因为b+树索引本身就是按照上述规则排序的，所以可以直接从索引中提取数据，然后进行回表操作取出该索引中不包含的列就好了

order by的子句后面的顺序也必须按照索引列的顺序给出，比如

select * from table_name order by b,c,a limit 10;

这种颠倒顺序的没有用到索引

select * from table_name order by a limit 10;
select * from table_name order by a,b limit 10;

这种用到部分索引

select * from table_name where a =1 order by b,c limit 10;

联合索引左边列为常量，后边的列排序可以用到索引

https://blog.csdn.net/Mind_programmonkey/article/details/114693532

2.4 哈希索引

哈希索引，只有精确匹配索引所有列的查询才有效。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码。哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。如果多个列的哈希值相同，索引会以链表的方式存放多个指针记录到同一个哈希条目中。

hash索引的特点
1、hash索引是基于hash表实现的，只有查询条件精确匹配hash索引中的所有列的时候，才能用到hash索引。

2、对于hash索引中的所有列，存储引擎都会为每一行计算一个hash码，hash索引中存储的就是hash码。

3、hash索引包括键值、hash码和指针。

因为hash索引本身只需要存储对应的hash值，所以索引的结构十分紧凑，这也让hash索引查找的速度非常快。然而，hash索引也是存在其限制的：

hash索引的限制
1、Hash索引必须进行二次查找
　　使用哈市索引两次查找，第一次找到相应的行，第二次读取数据，但是被频繁访问到的行一般会缓存在内存中，这点对数据库性能的影响不大。

原文链接：https://blog.csdn.net/z_ryan/article/details/82322418

因为索引自身只存储对应的哈希值，所以索引的结构十分紧凑，哈希索引查找的速度非常快。但是哈希索引也有它的限制：

哈希索引不是按照索引顺序存储的，无法用于排序。
不支持部分索引列匹配查找，必须全值匹配。
不支持范围查找。

BineHello

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
MYSQL面经-命令相关和索引

一、命令相关1、常见命令mysql -uroot -p #如果刚安装好MySQL，root是没有密码的*mysql> mysql -h192.168.206.100 -uroot -p12345678; #u与root可以不加空格mysql> drop database db_name; # -- 删除数据库mysql> use db_name; #-- 选择数据库mysql> create table tb_name (字段名 varc
复制链接

扫一扫