MySQL数据库

最新推荐文章于 2022-05-18 20:38:38 发布

Samuel-zl

最新推荐文章于 2022-05-18 20:38:38 发布

阅读量242

点赞数 1

分类专栏：面经文章标签： mysql

本文链接：https://blog.csdn.net/weixin_44383135/article/details/118096070

版权

面经专栏收录该内容

3 篇文章 0 订阅

订阅专栏

1、数据库的基本操作

- 相关命令：

进入数据库：mysql -u 用户名 -p 密码

查看数据库：show databases

使用数据库：use 数据库名

查看支持的存储引擎：show engines \G

创建数据库：create database 数据库名

删除数据库：drop database 数据库名

显示表结构：desc 表名；

2、MyISAM与InnoDB 的区别

1. InnoDB支持事务，MyISAM不支持

对于InnoDB每一条SQL语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条SQL语言放在begin和commit之间，组成一个事务；

2. InnoDB支持外键，而MyISAM不支持

对一个包含外键的InnoDB表转为MYISAM会失败

3. InnoDB是聚集索引, MyISAM是非聚集索引

InnoDB使用B+Tree作为索引结构，数据文件是和（主键）索引绑在一起的（表数据文件本身就是按B+Tree组织的一个索引结构），必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。

MyISAM是非聚集索引，也是使用B+Tree作为索引结构，索引和数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。

也就是说：InnoDB的B+树主键索引的叶子节点就是数据文件，辅助索引的叶子节点是主键的值；而MyISAM的B+树主键索引和辅助索引的叶子节点都是数据文件的地址指针。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Y1x6DoGs-1624285496525)(C:\Users\Samuel\AppData\Roaming\Typora\typora-user-images\image-20210621151438317.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-nmnI3eAF-1624285496526)(C:\Users\Samuel\AppData\Roaming\Typora\typora-user-images\image-20210621151449881.png)]

4. InnoDB不保存表的具体行数，MyISAM用一个变量保存了整个表的行数

InnoDB执行select count(*) from table时需要全表扫描，MyISAM只需要读出该变量即可，速度很快;

– 那么为什么InnoDB没有了这个变量呢？

因为InnoDB的事务特性，在同一时刻表中的行数对于不同的事务而言是不一样的，因此count统计会计算对于当前事务而言可以统计到的行数，而不是将总行数储存起来方便快速查询。

5. Innodb不支持全文索引，而MyISAM支持全文索引

在涉及全文索引领域的查询效率上MyISAM速度更快高；PS：5.7以后的InnoDB支持全文索引了

6. MyISAM表格可以被压缩后进行查询操作

7. InnoDB支持表、行(默认)级锁，而MyISAM支持表级锁

8. InnoDB表必须有唯一索引; 而Myisam可以没有

InnoDB必须有唯一索引（如主键）（用户没有指定的话会自己找/生产一个隐藏列Row_id来充当默认主键）

9. Innodb存储文件有frm、ibd，而Myisam是frm、MYD、MYI

Innodb：frm是表定义文件，ibd是数据文件

   Myisam：frm是表定义文件，myd是数据文件，myi是索引文件

3、如何选择：

是否要支持事务，如果要请选择innodb，如果不需要可以考虑MyISAM；
如果表中绝大多数都只是读查询，可以考虑MyISAM，如果既有读也有写，请使用InnoDB。
系统奔溃后，MyISAM恢复起来更困难，能否接受；
MySQL5.5版本开始Innodb已经成为Mysql的默认引擎(之前是MyISAM)，说明其优势是有目共睹的，如果你不知道用什么，那就用InnoDB，至少不会差。

4、InnoDB为什么推荐使用自增ID作为主键？

自增ID可以保证每次插入时B+索引是从右边扩展的，可以避免B+树和频繁合并和分裂（对比使用UUID）。如果使用字符串主键和随机主键，会使得数据随机插入，效率比较差。

5、InnoDB引擎的4大特性

插入缓冲（insert buffer),

二次写(double write),

自适应哈希索引(ahi),

预读(read ahead)

6、数据完整性

（1）实体完整性：

--实体完整性中的实体指的是表中的行,因为一行记录对应一个实体

--实体完整性规定表的一行在表中是唯一的实体,不能出现重复。

--实体完整性通过表的主键来实现

创建主键的两种方式：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-OuEm4Kqj-1624285496527)(C:\Users\Samuel\AppData\Roaming\Typora\typora-user-images\image-20210621162015423.png)]

（2）域完整性

域完整性指数据库表的列必须符合某种特定的数据类型或约束，比如说数据类型，长度，非空约束，唯一约束等等

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-o6xPxMTE-1624285496528)(C:\Users\Samuel\AppData\Roaming\Typora\typora-user-images\image-20210621162135317.png)]

（3）参照完整性

MySQL参照完整性一般是通过MySQL外键(foreign key)实现的。

外键（仅innoDB支持）所引用表的列必须是主键。

外键声明包括三个部分：

A、哪个列或列组合是外键

B、指定外键参照的表和列

C、参照动作[cascade（级联操作），restrict（拒绝操作），set null（设为空），no action，set default]。

如果外键约束指定了参照动作，主表记录做修改，删除，从表引用的列会做相应修改，或不修改，拒绝修改或设置为默认值。
引用表的列名必须是主键，且在删除引用表时必须删除引用关系或者删除当前表。

7、数据库操作

添加主键和外键

主键：

create table bmdmb(
bmh varchar(10) not null primary key comment'部门编号',
bjzwmc varchar(50) comment'班级名称',
)engine=innodb;

外键：

create table bjdmb(
bjbh varchar(20) not null primary key comment'班级编号',
bmh varchar(10) not null comment'部门编号',
bjzwmc varchar(50) comment'班级名称',
foreign key(bmh) references bmdmb(bmh)
)engine=innodb;

删除修改字段

删除字段：

alter table 表名 drop 字段名

修改字段名：

alter table 表名 change 旧字段 新字段 新数据类型

修改表明：

alter table 就表明 rename 新表名

删除表：

drop table if exists 表名

插入：

insert into 表名（字段一，字段二） values （values1，values2）

8、约束

主键约束

有一个或多个字段组成，可以唯一的标记表中的一条记录；

不能为null ，取之唯一，不能重复（保证保证实体完整性）

一个表最多一个主键；

单一主键：

create table stu(
	sid varchar(10) primary key,
    name varchar(20) not null
)engine=innodb;

复合主键：

create table ss(
	sid varchar(10) not null,
    tid varchar(10) not null,
    score tinyint
    primary key(sid,tid)
)engine=innodb;

为已经存在的表添加主键约束：

单一：

alter table 表名 modify 字段名 数据类型 primary key

复合：

alter table 表名 add primary key(字段1，字段2)

删除主键约束：

alter table 表名 drop primary key

外键约束：

引用另外一个表的一列或者是多列，被引用的列具有主键约束或者是唯一性约束

唯一性约束：

保证表中字段取值的唯一性。和主键一样，唯一性字段可以是表中的一列或者多列，他们的值在任何时候都是唯一的。关键字：unique；

sname varchar(10) unique

或者是：

sname varchar(10) 
unique(sname)

非空约束：

字段值不能为null，通过not null来定义。

9、数据查询：

关键字顺序：

select (distinct) ->from -> where -> group by(having) -> order by(asc/desc) -> limit -> into

where特殊：

where后面可接等值查询、范围查询、模糊查询（=，大于小于，in，between and ，like，is null，and，or，）

聚合函数

（max，min，count，sum，AVG）放在select之后！！！

like：

like ‘AB%’ 表示以AB开头的任意字符串

like ‘%AB’ 表示以AB结尾的任意的字符串

like ‘%AB%’ 表示包含AB的任意字符串

like ‘_AB’ 表示以AB结尾的三个字符串

where和having的区别：

1、where是对数据表中的数据操作；having是对已分组之后的数据进行操作；

2、where在order by之前；having在order by 之后；

3、当同时出现where ，order by，having时，先执行where 再order by 再执行聚合函数，之后再having；

limit：

limit offset N；

offset表示偏移量 N表示最大记录数：

limit 3；表示获取排名前3 的数据；

limit 3,4：表示获取排名4-7 的数据；

多表查询：

inner join：只返回两表满足连接条件的数据行；

left join：以左表为基准，右表不能满足的列值为null

right join：以右表为基准，左表不能满足的列值为null

cross join：结果大小为两表的行数之积

正则表达式：

用来检索或者是替换某个模式的文本内容，

^用来匹配以特定字符开头的记录：

查询以 “张” 开头的

select xh,xm,xb
from stu
where xm regexp '^张'

等同于：

select xh,xm,xb
from stu
where xm regexp '张%'

$ 查询以特定字符穿结尾的

select xh
from stu
where xm regexp '技术$'

等同于：

select xh
from stu
where xm regexp '%技术'

匹配字符串：

匹配记录中包含2001或2002 的字符串

select xh,xm,xb
from stu
where xm regexp '2001|2002'

合并结果集：

union

10、索引

分类：

普通索引（index）：允许重复和空值

创建course表，在cname上添加普通索引：

create table course(
	cid varchar(10) not null,
    cname varchar(20) not null,
    credit float(3,1),
    semester int(1),
    index(cname)
);

修改已创建好的表的索引：

//普通索引
alter table stu 
add index index_xx(cname)

//唯一索引
//对cname的钱8个字段加索引
alter table stu 
add unique index index_xx(cname（8）)

唯一索引（unique）：字段值必须唯一，允许空值，一个表可以存在多个唯一索引

创建course表，在cname上添加唯一索引,并按降序排序：

create table course(
	cid varchar(10) not null,
    cname varchar(20) not null,
    credit float(3,1),
    semester int(1),
    unique index index_name(cname )
);