MySQL数据库的补充知识点

最新推荐文章于 2024-10-30 11:02:01 发布

浪里小白狼（鹤）

最新推荐文章于 2024-10-30 11:02:01 发布

阅读量93

点赞数

文章标签：数据库 mysql database

本文链接：https://blog.csdn.net/m0_51491702/article/details/121111104

版权

title:MySQL数据库的补充知识点

LIMIT基本语法：
SELECT * FROM table LIMIT [offset] rows | rows OFFSET offset;
- 如果只给定一个参数，表示记录数
  SELECT * FROM orange LIMIT 5; //检索前5条记录(1-5)
  相当于：SELECT * from orange LIMIT 0,5;
- 两个参数，第一个参数表示offset, 第二个参数为记录数。
  mysql> SELECT * FROM orange LIMIT 10,15; // 检索记录11-25
  再如，另一种用法：
  mysql> SELECT * FROM orange LIMIT 2 OFFSET 3;//查询4-5两条记录
  等价于：mysql> SELECT * FROM orange LIMIT 3,2;
MySQL中取别名之后，该sql语句中就只能使用该别名。
解决MySQL中文乱码问题
- 修改配置文件
  [client]节点：default-character-set=utf8 (增加)
  [mysql]节点：default-character-set=utf8 (修改)
  [mysqld]节点：character_set_server=utf8 (修改)
  注：修改完配置文件之后要重启服务。如果无法重启，则不修改client下的配置。
- 修改已经存在库的字符集
  
  使用命令：alter database 库名 character set ‘utf8’;
- 修改已经存在表的字符集
  
  查看表的字符集：show table status from 数据库名 LIKE ‘表名称’;
  
  设置表的字符集：alt table 表名 CONVERT TO CHARACTER SET ‘utf8’
创建新用户并添加权限

使用命令：create user 用户名 identified by 密码
注：创建一个新用户的语法格式。默认创建的新用户是所有主机都可以连接的。但是这个用户访问数据库的权限受限。

授权命令：
- grant 权限1，权限2，…权限n ON 数据库名称.表名称 TO 用户名@用户地址 identified by ’连接命令‘；
  注：该权限如果发现没有该用户，则会直接创建一个用户。连接命令就是密码
- grant select ,insert,delete,drop ON wang.* TO he@localhost;
  注：该命令给he这个本地用户，授予wang这个库下所有表的增删改查权限。
- grant all privileges ON *.* TO root@’%’ identified by ’1230‘
  注：授予通过网络方式登录的root用户，对所有库的所有表的全部访问权限，密码设置为1230.
- 修改某个用户密码：update mysql.user set password=新密码 where user=’用户‘
  注：修改完成之后，使用flush privileges 刷新一下内存。

当一个文件的扩展名是.sql，并且该文件中编写了大量的sql语句，我们称这样的文件是sql脚本文件。可以直接使用source命令可以执行sql脚本。
查看表完整结构：show creat table admin;
存储引擎
- 存储引擎描述的是表的存储组织方式。mysql默认使用的存储引擎是innoDB，默认使用的字符集是utf8.
- 存储引擎只有在mysql中存在（oracle中叫存储方式）
- 查看当前MySQL支持的存储引擎：show engines \G;
- MySQL8.0支持的存储引擎有9个。常见的存储引擎有3个
  - MyISAM ：这种存储引擎不支持事务。是最常用的存储引擎。但不是默认的。
    使用三个文件表示每个表：
    - 格式文件——存储表结构的定义（mytable.frm）
    - 数据文件——存储表行的内容（mytable.myd）
    - 索引文件——存储表上索引（mytable.myi）
  - innoDB是mysql的缺省引擎
    特征：- 每个innerDB表再数据库目录中以.ibd .frm格式文件显示
    -innerDB表空间tablespace被用于存储表的内容
    -提供一组用来记录事务性活动的日志文件
    -用commit（提交）、savepoint及rollback（回滚）支持事务处理
    -提供全acid兼容
    -在mysql服务器崩溃后提供自动恢复
    -多版本（mvcc）和行级锁定
    -支持外键及引用的完整性，包括联级删除和更新。
  - MEMORY存储引擎
    特征：不支持事务。数据容易丢失。因为所有数和索引都存储在内存当中。检索速度非常快。
索引
1. 什么是索引？有什么用？
  索引就相当于一本书的目录，通过目录可以快速的找到对应的资源。在数据库方面，查询一张表有两种检索方式：第一种：全表扫描，第二种：根据索引检索（效率很高。）。
  索引虽然可以提高检索效率，但是不能随意的添加索引，因为索引也是数据库当中的对象，也需要数据库不断的维护。是有维护成本的。比如，表中的数据经常被修改，这样就不适合添加索引，因为数据一旦修改，索引需要重新排序，进行维护。
  添加索引是给某个字段或者说某些字段添加索引。
2. 怎么创建索引对象？怎么删除索引对象？
  创建：create index 索引名称 on 表名(字段名)；
  删除：drop index 索引名称 on 表名；
3. 什么时候考虑给字段添加索引？
  - 数据量庞大。（根据客户的需求，根据线上的环境）
  - 该字段很少的DML操作。（因为字段进行修改操作，索引也需要维护）
  - 该字段经常出现在where子句中。（经常根据哪个字段查询）
4. 注意：主键和具有unique约束的字段自动会添加索引。根据主键查询效率较高。尽量根据主键检索。
5. 使用“explain + sql语句”查看执行计划。
6. 索引底层采用的数据结构：B+Tree
7. 索引的实现原理
  
  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-KJeSrAoa-1635863734843)(.\img\索引底层.png)]
8. 索引的分类：单一索引、复合索引、主键索引、唯一索引…
9. 索引什么时候失效：模糊查询
数据库数据的导入导出

导出数据库：
在windows的dos命令窗口中执行：mysqldump 数据库名>导出目标文件(.sql)的路径 -u root -p
导出指定数据库中的表：在windows的dos命令窗口中执行：mysqldump 数据库名表名>导出目标文件(.sql)的路径 -u root -p

导入：
use 数据库名；
source sql文件路径；

数据库设计3范式

什么是设计范式
设计表的依据。按照这三个范式设计的表不会出现数据冗余（数据冗余是指数据之间的重复，也可以说是同一数据存储在不同数据文件中的现象）
三范式都是哪些：
（1）第一范式：
任何一张表都应该有主键，并且每一个字段原子性不可再分。简而言之，第一范式就是无重复的列。
（2）第二范式：
建立在第一范式的基础之上，所有非主键字段完全依赖主键，不能产生部分依赖。
要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性，如果存在。那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体，新实体与原实体之间是一对多的关系。为实现区分通常需要为表加上一个列，以存储各个实例的唯一标识。简而言之，第二范式就是非主属性依赖于主关键字。
（3）第三范式：
首先满足一二范式。然后，所有非主键字段，直接依赖主键。不能产生传递依赖。
设计表：多对多，三个表，关系表两个外键。一对多，两个表，多的表加外键。
提醒：在实际开发中，以满足客户的需求为主，有的时候，会拿冗余换取执行速度。
```
首先满足一二范式。然后，所有非主键字段，直接依赖主键。不能产生传递依赖。
```
设计表：多对多，三个表，关系表两个外键。一对多，两个表，多的表加外键。
提醒：在实际开发中，以满足客户的需求为主，有的时候，会拿冗余换取执行速度。