MySQL学习

一、Ubuntu环境下安装MySQL

关于在Ubuntu22下安装MySQL5.7，我主要参考的是下面这几篇博客：

Ubuntu22部署MySQL5.7详细教程_ubuntu22安装mysql5.7-CSDN博客

Ubuntu 安装和使用MySQL_奉君逍遥-CSDN开发云

在配置文件中设置默认编码格式与存储引擎。

打开配置文件

sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

添加下面这两条：

character-set-server=utf8
default-storage-engine=innodb

配置文件修改后重启MySQL服务即可生效。

二、数据库基础

1、登陆mysql

mysql是可以跨网络访问的，所以需要ip和端口。

mysql -h 127.0.0.1 -P 3306 -u root -p

去掉-h选项就代表登录本机搭建的mysql服务。

去掉-P选项就默认为配置的端口号3306。

2、mysql与mysqld

我们在安装数据库时分别安装了mysql和mysqld：

1. mysql其实是数据库服务的客户端。
2. mysqld是数据库服务的服务器端。
3. mysql的本质：基于CS模式的一种网络服务。

1. mysql是一套给我们提供数据存取服务的网络程序。
2. 我们在口语中说的数据库，一般指的是在磁盘或内存中存储的特定结构组织的数据。
3. 数据库服务指的是mysqld。

3、数据库存在的意义

一般文件确实提供了数据的存储功能，但文件并没有提供很好的数据管理能力（站在用户的角度来看）。

数据库的本质：对数据内容存储的一套解决方案，用户给出字段或者要求，数据库给用户结果。

4、见一见数据库

查看数据库：

show databases;

在配置文件中记录了数据库存放路径：

创建数据库：

create database helloworld;

创建了对应的文件夹

由此可以得出一个结论：建立数据库，本质就是在linux下创建一个目录。

使用数据库：

要先使用数据库，之后才能对数据库内的内容进行操作，比如创建表的操作。

use helloworld;

创建表：

后面会细讲。

create table student(
    name varchar(32),
    age int,
    gender varchar(2)
    );

在helloworld文件夹中创建了对应的文件

由此可以得出第二个结论：在数据库内创建表，本质就是在linux下创建对应的文件。

第三个结论：数据库本质也是文件！！只不过这些文件并不由程序员直接操作，而是由mysqld帮我们操作。

5、SQL语句分类

• DDL【data definition language】 数据定义语言，用来维护存储数据的结构，代表指令: create, drop, alter
• DML【data manipulation language】 数据操纵语言，用来对数据进行操作，代表指令： insert，delete，update，DML中又单独分了一个DQL，数据查询语言，代表指令： select
• DCL【Data Control Language】 数据控制语言，主要负责权限管理和事务，代表指令： grant，revoke，commi

6、存储引擎

存储引擎是：数据库管理系统如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。

查看存储引擎：

show engines;

最常用的存储引擎是InnoDB和MyISAM。

三、库的操作

1、创建数据库

create database database1;

create database if not exists database1;

加上if not exists的意思是如果数据库不存在就创建。

2、删除数据库

drop database database1;

3、数据库编码集

创建数据库时，有两个编码集：

1. 数据库字符集。（数据库未来存储数据）
2. 数据库校验规则。（支持数据库，进行字段比较使用的编码，本质是一种读取数据库数据库中数据采用的编码格式）

数据库无论对数据做任何操作，都必须保证操作和编码是编码一致的。

查看系统默认字符集：

show variables like 'character_set_database';

查看系统默认校验规则：

show variables like 'collation_database';

查看数据库支持的字符集：

show charset;

查看数据库支持的校验规则：

show collation;

4、创建指定编码的数据库

创建一个使用utf8字符集的数据库：

create database d1 charset=utf8;

create database d2 character set utf8;

创建一个使用utf8字符集以及对应校验规则的数据库：

create database d3 charset=utf8 collate utf8_general_ci;

5、校验规则对数据库的影响

创建一个数据库，校验规则使用utf8_ general_ ci（不区分大小写）：

使用数据库：

创建表：

向表中插入数据：

查找表中name=‘a’的数据：

 创建一个数据库，校验规则使用utf8_ bin（区分大小写）：

使用数据库：

创建表：

向表中插入数据：

查找表中name=‘a’的数据：

6、操纵数据库

查看有哪些数据库：

show databases;

使用数据库（进入数据库）：

use test2;

查看自己目前在哪个数据库：

select database();

修改test2数据库的字符集与校准规则：

alter database test2 charset=gbk collate gbk_chinese_ci;

显示创建数据库时的语句：

show create database test2;

/*!40100 default.... */ 这个不是注释，表示当前mysql版本大于4.01版本，就执行这句话。

7、数据库的备份与恢复

不建议直接删除数据库，最好在删除前进行备份。

备份test1数据库：

mysqldump -P 3306 -u root -p -B test1 >test1.sql

删除test1数据库：

恢复test1数据库：

source ~/MySQL/backups/test1.sql;

也可以备份数据库中的表：

mysqldump -P 3306 -u root -p test1 person > ./test1.sql

还可备份多个数据库：

mysqldump -P 3306 -u root -p -B test1 test2 > ./test1.sql

如果备份一个数据库时，没有带上-B参数， 在恢复数据库时，需要先创建空数据库，然后使用数据库，再使用source来还原。

8、查看连接情况

show processlist;

可以告诉我们当前有哪些用户连接到我们的MySQL，如果查出某个用户不是你正常登陆的，很有可能你的数据库被人入侵了。以后大家发现自己数据库比较慢时，可以用这个指令来查看数据库连接情况。

四、表的操作

1、创建表

语法如下：

CREATE TABLE table_name (
	field1 datatype,
	field2 datatype,
	field3 datatype
) character set 字符集 collate 校验规则 engine 存储引擎;

• field 表示列名。
• datatype 表示列的类型。
• character set 字符集，若没有指定字符集，以所在数据库的字符集为准。
• collate 校验规则，若没有指定校验规则，以所在数据库的校验规则为准。

create table if not exists user(
     id int,
     name varchar(20),
     password varchar(32),
     birthday date
 );

上面没有指定存储引擎默认用的是InnoDB。

create table if not exists user2(
     id int,
     name varchar(20),
     password varchar(32),
     birthday date
)engine=MyIsam;

上面选择的是MyIsam引擎。

不同的存储引擎创建的文件不一样：

2、查看表

查看当前数据库下有哪些表：

show tables;

查看表的详细信息：

desc user;

查看创建表时的详细信息：

show create table user \G

3、修改表

修改表名：

alter table user2 rename to user;

在表中添加字段：

alter table user add image_path varchar(128) after birthday;

after表示把新字段添加到birthday字段后面，可省略。

在表中修改字段：

alter table user modify name varchar(10);

在表中删除字段：

alter table user drop password;

当我们删除一个字段时，这一列曾经所有的数据都没有了，所以轻易不要删除。

在表中修改字段名：

alter table user change name xingming varchar(10);

在修改字段名时，新的字段名必须带上完整的定义。

4、删除表

删除user表：

drop table user;

最后说一句：尽量不要修改表结构！尽量在一开始就确定好表结构。

五、数据类型

1、数据类型分类

2、数值类型

（1）tinyint类型

创建一张表：

tinyint是有符号的，最小值为-128，最大值为127，插入几个值试试：

都没出问题。

插入几个超过tinyint的值试试：

报错了，没让我们插入。

再创建一张表：

tinyint unsigned是无符号的，最小值为0，最大值为225。

插入几个值试试：

当我们插入范围外数据时，会报错，不让我们插入。

如果我们向MySQL特定的类型中插入不合法的数据，MySQL一般都会直接拦截我们，不让我们做出对应的操作。

反过来，如果我们已经有数据成功插入了MySQL中，那么插入的数据一定是合法的。所以MySQL中，数据类型本身也是一种约束。

这样可以倒逼程序员尽可能正确插入。

另外，如果你不是一个很好的使用者，MySQL也能保证数据插入的合法性。

这样就能保证数据库中的数据是可预期的、完整的。

（2）bit类型

基本语法：

bit[(M)] : 位字段类型。M表示指定的位数，范围从1到64。如果M被省略，默认为1。

[]里的内可省略。

创建一张表：

其中的online大小只有一个比特位。

插入数据：

当插入的online超过一个比特位大小时就报错了。

查看表中内容：

发现online为空。

bit类型在显示时会按照ASSIC码代表的符号来显示，ASSIC码值0和1代表的符号是不可显示的。

修改online大小为8比特位，插入69：

3、小数类型

（1）float类型

基本语法：

float[(m, d)] [unsigned] : M指定显示长度，d指定小数位数，占用空间4个字节。

创建一张表：

salary的范围为-99.99~99.99。

插入数据：

继续插入数据：

当我们插入超过指定精度的值时，会进行四舍五入。

再插入数据：

当我们插入的值四舍五入后，超过了指定的长度，就不会四舍五入，无法插入数据。

新建一张表：

salary为无符号时，直接去掉负数部分，salary的范围为0~99.99。

插入数据：

float是由精度损失的。

修改salary的类型：

再插入数据，就出现了精度损失：

（2）decimal类型

基本语法：

decimal[(m, d)] [unsigned] : 定点数m指定长度，d表示小数点的位数。m最大为65，默认为10。d最大为30，默认为0。

新建表：

尝试插入数据：

修改表：

向f1和f2插入相同的高精度浮点数：

f1出现了精度损失，但是f2没有。

4、字符串类型

（1）char类型

基本语法：

char(L): 固定长度字符串，L是可以存储的长度，单位为字符，最大长度值可以为255。

创建表：

插入数据：

插入中文字符：

在utf-8中，一个汉字是要占三个字节，在gbk中，一个汉字占两个字节。

MySQL中的字符和C/C++中的字符概念不一样。C/C++中一个字符就是一个字节。MySQL中一个字符就真的是一个符号，一个汉字或者一个英文符号就是一个字符。

（2）varchar类型

基本语法：

varchar(L): 可变长度字符串，L表示字符长度，最大长度65535个字节。

建表：

插入数据：

尝试修改name的类型：

失败了，提示说name列的最大长度为21845。

我们上面说的，varchar的最大长度为65535个字节，注意，是字节，不是字符。65545字节/3=21845字符。

char和varchar的区别：

char是固定长度字符串，varchar是变长字符串，用多少开辟多少空间，L是上限。

varchar中有1~3个字节用于记录数据大小。

所以实际上varchar的上限为65532字节，即21844字符。L的上限也就是21844。

再次修改：

还是错了。为什么呢？这次提示的是行大小太大，这一行里除了name，还有id也占了空间。

改小一点就成功了：

新创建一个表，只有name一个字段：

如何选择定长或变长字符串？

• 如果数据确定长度都一样，就使用定长（char），比如：身份证，手机号，md5
• 如果数据长度有变化,就使用变长(varchar), 比如：名字，地址，但是你要保证最长的能存的进去。
• 定长的磁盘空间比较浪费，但是效率高。
• 变长的磁盘空间比较节省，但是效率低。
• 定长的意义是，直接开辟好对应的空间
• 变长的意义是，在不超过自定义范围的情况下，用多少，开辟多少。

5、日期与时间类型

常见的有三种：

• date：日期，“yyyy-mm-dd”，占三个字节。
• datetime：日期时间，“yyyy-mm--dd HH:ii:ss”，占八个字节。
• timestamp：时间戳，从1970年开始，格式和datetime一致，占四个字节，会自动变化（当修改表数据时）。

创建表：

尝试插入数据：

进行修改：

t3也随之改变了。

再次插入数据：

6、enum与set类型

enum：枚举，单选类型。

enum('选项1','选项2','选项3',...);

set：集合，多选类型。

set('选项值1','选项值2','选项值3', ...);

建表：

插入数据：

不允许插入不存在的选项。

插入数据，gender字段插入数字：

枚举类型可以直接写选项对应的下标，下标从1开始。

再次插入数据：

允许数据为空。

插入数据，hobby字段插入数字：

插入1时，是代码没错，插入2时，是羽毛球也没错，插入3时，不应该是乒乓球吗，怎么变成羽毛球了？

其实可以以二进制的视角来理解：

有为1，没有为0。

插入hobby为7：

所以7代表的就是代码、羽毛球、乒乓球。

在表中查找所有性别为男的：

在表中查找爱好为羽毛球的人：

如此查找有一点小问题，有些人的爱好十分广泛，除了羽毛球还有别的爱好，这次查找，为什么找到的是只有羽毛球爱好的人呢？

此时就需要用到find_ in_ set函数：

find_in_set(sub,str_list) ：如果 sub 在 str_list 中，则返回下标；如果不在，返回0；str_list 用逗号分隔的字符串。

find_in_set只能查一个元素是否在对应的字符串中：

在表中查找爱好有羽毛球的人：

在表中查找爱好有羽毛球和代码的人：

六、表的约束

真正约束字段的是数据类型，但是数据类型约束很单一，所以在表中一定要有各种约束，通过约束，让我们未来插入数据库中的数据是符合预期的。约束本质是通过技术手段倒逼程序员插入正确的数据。反过来，站在MySQL的视角看，凡是插入进来的数据，都是符合约束的。

约束的最终目标：保证数据的完整性和可预期性。

表的约束很多，这里主要介绍如下几个： null/not null,default, comment, zerofill，primary key，auto_increment，unique key 。

1、空属性约束

• 两个值：null（允许为空，默认的）和not null(不允许为空)
• 数据库默认字段基本都是字段为空，但是实际开发时，尽可能保证字段不为空，因为数据为空没办法参与运算。

新建表：

插入数据：

当插入一行时，不指明class_room，就会提示没有默认值（这是接下来要讲的）。

当插入一行时，class_room设置为NULL，就会提示不能为空。

2、默认值约束

默认值：某一种数据会经常性的出现某个具体的值，可以在一开始就指定好，在需要真实数据的时候，用户可以选择性的使用默认值。在建表时，如果没有指定默认值，MySQL会自动设置默认值为NULL。

建表：

插入数据：

如果某个字段设置了default，将来插入数据时，该字段指定了值，就用指定的，如果没有，就用默认值。

再建一张表：

空属性约束和默认值约束可以一起使用。

插入数据：

由此可以得出一个结论：空属性约束和默认值约束并不冲突，而是相互补充的。

空属性约束作用于用户插入数据指定字段的值时，指定的值不能为NULL。

默认值约束作用于用户插入数据忽略字段的值时，使用默认值（如果设置了），如果没有设置，直接报错。

3、列描述约束