MySQL必知必会知识点总结一二

不念不忘少年蓝@ 2022-06-13 02:38 440阅读 0赞

## 一、MySQL官方文档地址 ##

[https://dev.mysql.com/doc/refman/5.7/en/][https_dev.mysql.com_doc_refman_5.7_en]

## 二、MySQL常用命令 ##

1、如何登陆mysql数据库

mysql -u username -p

1.1、指定IP、端口

mysql -uroot -h 192.168.1.213 -P3306 -p123456

\-P（大写）：指定端口；  
\-p（小写）：指定密码；

2、如何开启/关闭mysql服务

service mysql start/stop

3、查看mysql的状态

service mysql status

4、如何显示数所有数据库

show databases

5、如何获取表内所有字段对象的名称和类型

describe table_name;

## 三、SQL分类 ##

SQL全程为Structure Query Language(结构化查询语言)。

SQL 语句主要可以划分为以下 3 个类别：

1、DDL(Data Definition Languages)语句:数据定义语言，这些语句定义了不同的数据段、 数据库、表、列、索引等数据库对象的定义。常用的语句关键字主要包括 create、drop、alter 等。

2、DML(Data Manipulation Language)语句:数据操纵语句，用于添加、删除、更新和查询数据库记录，并检查数据完整性，常用的语句关键字主要包括 insert、delete、update 和 select 等。

3、DCL(Data Control Language)语句:数据控制语句，用于控制不同数据段直接的许可和 访问级别的语句。这些语句定义了数据库、表、字段、用户的访问权限和安全级别。主要的语句关键字包括 grant、revoke 等。

## 四、MySQL使用帮助命令 ##

在 MySQL 使用过程中，快速查找某一个命令的使用方法和实例。使用命令：

mysql> ? contents;

![这里写图片描述][c0cb4b2b2a2b347333dccabc718f7eff.png]

示例：

1、mysql> ? Data Types;

![这里写图片描述][35ce580adb5846e4e1cab3f61cf9678a.png]

2、mysql> ? show create table;

![这里写图片描述][c899801cfeb97a8fec08fb2e84af776d.png]

3、mysql> ? show

4、mysql> ? int  
。。。。。。

因此，只要是对于某一个命令不是很熟的话，使用`？ 命令名称` 即可显示其用法。

## 五、MySQL支持的数据类型 ##

**1、数值类型**

![这里写图片描述][e0f1857e4210b67fa06cfd4ac6dc53e5.png]

常见的数值类型如下：

![这里写图片描述][9715e5c057ca8f361b9c7dbc5afb9f89.png]

注：这里的最小值和最大值代表的是宽度。

对于这几种常见的数值类型，我们应该知道存储的所占的字节，我们还要知道INT最大的长度为10位数字，那么我们设置11为，如：int(11)也是无用的，最多只能为10位数字。TINYINT最大支持3位，那么我们设置tinyint(4)也是无用的。

> int(M) 在 integer 数据类型中，M 表示最大显示宽度。 在 int(M) 中，M 的值跟 int(M)所占多少存储空间并无任何关系。 int(3)、int(4)、int(8) 在磁盘上都是占用 4 btyes的存储空间。说白了，除了显示给用户的方式有点不同外，int(M) 跟 int 数据类型是相同的。
> 
> 1、如果int的值为10 int（10）显示结果为0000000010  
> 2、int（3）显示结果为010 就是显示的长度不一样而已都是占用四个字节的空间

**2、日期时间类型**

![这里写图片描述][8714dfa7aec9ba8aac5f9d6118c3e53d.png]

上图可以看出，每种日期时间类型都有一个有效值范围，如果超出这个范围，在默认的SQLMode下，系统会进行错误示，并将以零值来进行存储。不同日期类型零值的表示如下：

![这里写图片描述][d4becf34a1713b5f1d2b2e3ec9bc1c72.png]

提示：

（1）日期格式中只有`TIMESTAMP` 可以使用MySQL提供的函数，实现自动设置时间和自动更新时间，脚本如下：

`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',

（2）Navicat for MqlSQL设置如下：

![这里写图片描述][4428b314c2de6b5cabfd0a3dd79f8665.png]

（3）、使用`TIMESTAMP` 要认识到，他会停止在2038年的一个时刻，但MySQL官方应该后期会提供相应的支持。

（4）DATETIME、TIMESTAMP都可以精确到秒，但是`TIMESTAMP`只占4个字节，因此可以有效考虑使用；

**3、字符串类型**

![这里写图片描述][bc2e5026810795e6c97a30edf437b003.png]

1、常见的char和varchar区别：

（1）char(N)用于存放固定长度的字符串，长度最大为255，比指定长度大的值将被截断，而比指定长度小的值将会用空格进行填补；

（2）varchar(N)用于保存可变长度的字符串，长度最大为65535，只存储字符串实际需要的长度，它会增加一个额外字节来保存字符串本身的长度，varchar使用额外的12字节来存储值得长度，如果列的最大长度小于或等于255，则使用1字节，否则就是使用2字节；（1个字节占8位，2的8次方是256（-128127）；2个字节占16位，2的16次方为65536）

（3）char和varchar跟字符编码也有密切联系，lantin1占用1个字节，gbk占用2个字节，utf8占用3个字节。示例如下：

*  lantin1、1个字符1个字节：

![这里写图片描述][1f9a9f2bc4bca8ad60bb88c989fd449d.png]

*  gbk、1个字符2个字节：

![这里写图片描述][f48a41ae23835cca5e58b2cef7d11c06.png]

*  utf8、1个字符3个字节：

![这里写图片描述][c2002b646873440af31635378f6ae773.png]

2、如何选择char和varchar

## 六、MySQL数据类型的选择和优化案例 ##

**1、手机号存储**

使用BIGINT代替CHAR或者VARCHAR存放手机号码。

这是因为CHAR或者VARCHAR，占用空间大，影响查询性能。  
例如：11位手机号CHAR存储，utf8编码，则占用33个字节；  
使用如果使用INT的话，INT最大只能保存10为数据，而手机号为11位，会出现溢出，所以使用BIGINT占用8个字节，支持11为数据存储。

**2、IP地址可以使用INT存储**

MySQL里提供了一个很好的函数：`INET_ATON()`,他负责把IP地址转化为数字，而另一个函数`INET_NTOA（）`负责将数字转化为IP地址，示例如下：

注意：INT使用无符号，这是因为INT有符号最大为2147483647而无符号 最大为4294967295，如果使用有符号的话，会出现溢出，使用无符号则不会溢出。

*  插入操作：

![这里写图片描述][5c6a90fbba8837c6eba715d93059ed7c.png]

mysql> insert into test values(1,INET_ATON('192.168.1.213'));

*  选择操作：

![这里写图片描述][b70b015907246b99dfd23956ff62a452.png]

mysql> select id,INET_NTOA(ip) from test;

可以看出INT无符号最大值为4294967295，而存放IP最大为255.255.255.255刚好等于4294967295。

\*\*3、建议使用TINYINT来代替ENUM类型。 \*\*

\*\*4、使用VARBINARY存储大小写敏感的变长字符串或二进制内容。 \*\*

## 七、MySQL中的常用运算符 ##

1、算术运算符

![这里写图片描述][6b85c1c954c529d317c451d30a390b6a.png]

2、比较运算符

![这里写图片描述][3e6c4b21536b418d8ecaa5b2d15a4336.png]

## 八、MySQL中的常用函数 ##

1、字符串函数

![这里写图片描述][25a0e94ff8f0f551c5ef930d9e8a1cf5.png]

2、数值函数

![这里写图片描述][4c59222122459e4fa182d7db8b14304e.png]

3、日期和时间函数

![这里写图片描述][6eb4ee5b4ff2d04b6a29a38e35503411.png]

## 九、常见查看MySQL状态命令 ##

1、查看状态

mysql> show status;

![这里写图片描述][a426bc8c70a18cce9d01d358544fc682.png]

2、刷选状态显示

mysql> show global status like 'Max_used_connections';

![这里写图片描述][4c9ef6993114c25bc0beed12f218a803.png]

3、查看可设置的变量

mysql> show variables\G;

![这里写图片描述][8d1f5c52ede561483b045d4ac757078e.png]

4、查看最大的连接

mysql> show variables like '%max_con%';

5、查看进程的连接

mysql> show full processlist;

![这里写图片描述][f83708959f573a936bfe5fa89b15cc84.png]

此命令在数据库连接出现Too many connections解决的时候，很有帮助。

6、sql执行性能分析命令`explain`

![这里写图片描述][f3b117013056e255f148db93ec7285dd.png]

在索引创建及分析很有帮助。

[https_dev.mysql.com_doc_refman_5.7_en]: https://dev.mysql.com/doc/refman/5.7/en/
[c0cb4b2b2a2b347333dccabc718f7eff.png]: /images/20220613/e218c0369a114bf6ab5696d84b77f3c6.png
[35ce580adb5846e4e1cab3f61cf9678a.png]: /images/20220613/aa9dac7bd7674dadb4ae027e8d4daff7.png
[c899801cfeb97a8fec08fb2e84af776d.png]: /images/20220613/26b2b29226d5411eba9caf308ef0a941.png
[e0f1857e4210b67fa06cfd4ac6dc53e5.png]: /images/20220613/01552d20b82246ffb8494c792113c620.png
[9715e5c057ca8f361b9c7dbc5afb9f89.png]: /images/20220613/6d686ee4ecaa47948a4a77390f659259.png
[8714dfa7aec9ba8aac5f9d6118c3e53d.png]: /images/20220613/9748831982d046349c2c6cff9a6dd957.png
[d4becf34a1713b5f1d2b2e3ec9bc1c72.png]: /images/20220613/4fb13d08ea3b42ce959f383407194764.png
[4428b314c2de6b5cabfd0a3dd79f8665.png]: /images/20220613/609760c0e7f84e2ba5896b4d7e5d7653.png
[bc2e5026810795e6c97a30edf437b003.png]: /images/20220613/7f265be7e2f14e4d9f6800ec3a82f287.png
[1f9a9f2bc4bca8ad60bb88c989fd449d.png]: /images/20220613/9d0b76269c484f2ca4af284fac893875.png
[f48a41ae23835cca5e58b2cef7d11c06.png]: /images/20220613/0a9a66239fb94e3db67df2d1b56bf629.png
[c2002b646873440af31635378f6ae773.png]: /images/20220613/dfc2fc7cbcc6497791f21a0dbd0772db.png
[5c6a90fbba8837c6eba715d93059ed7c.png]: /images/20220613/83d313cc2365489eb9cbea6c9ae73b05.png
[b70b015907246b99dfd23956ff62a452.png]: /images/20220613/80fa5b110add45aba972a337ec6b27f1.png
[6b85c1c954c529d317c451d30a390b6a.png]: /images/20220613/890caf97168e435e8531b3328a91f297.png
[3e6c4b21536b418d8ecaa5b2d15a4336.png]: /images/20220613/6bbb88ca19d4497892fddc60ef152bc2.png
[25a0e94ff8f0f551c5ef930d9e8a1cf5.png]: /images/20220613/7546503291c844bfbded1f5d7a647561.png
[4c59222122459e4fa182d7db8b14304e.png]: /images/20220613/2bc8195d704a464b8fd8826414253a63.png
[6eb4ee5b4ff2d04b6a29a38e35503411.png]: /images/20220613/4d93ad5a1ac345838558a05141d0e8b7.png
[a426bc8c70a18cce9d01d358544fc682.png]: /images/20220613/a444b3b7d643431594d9264ad2104d0a.png
[4c9ef6993114c25bc0beed12f218a803.png]: /images/20220613/155e15f5c53447d4802bdb3203cc28d2.png
[8d1f5c52ede561483b045d4ac757078e.png]: /images/20220613/886fca775e5a42bfa53c36b5b74c9827.png
[f83708959f573a936bfe5fa89b15cc84.png]: /images/20220613/f2e17994bf3a4140ba6051e63f653961.png
[f3b117013056e255f148db93ec7285dd.png]: /images/20220613/47b580c0ed18422c9c60b2bef3bded3e.png