MySQL 数据库

MySQL数据库

1.数据库前戏

1)存储数据的演变史

阶段一：文本文件

文件路径不固定、数据格式不统一

阶段二：软件开发目录规范

规定了数据应该保存在db目录下

但是数据格式仍未统一

阶段三：数据库服务

统一了存取位置，统一了操作方式

降低学习成本，提高开发效率

2)数据库软件应用使

1.单机游戏：数据存储于各个计算机本地，信息无法共享

2.网络游戏：数据存储于网络中，信息可以共享（数据库服务）但是必须联网

"""
远程数据库服务有两个问题：
1.数据安全性问题
2.服务器负载问题
解决方法：
[数据库集群]（让多台服务器运行相同的数据库服务）
即备份了数据又让服务器负载均衡
"""

3)数据库的本质

1.站在底层原理的角度
操作数据的进程（运行在内存中的代码）
2.站在实际应用的角度
可视化操作界面（用于操作进程的界面）


不做特殊说明时提数据库其实都是在指数据库软件
数据库软件本质就是cs架构程序

4)数据库的分类（重要）

(1)关系型数据库

一般用在数据的永久存储

1.数据的存储方式，有固定的表结构（字段名、字段类型）
eg：id name pwd
# 关系型数据库存取方式可以看成是表格

2.表与表之间可以建立数据库层面的关系
eg：用户层  豪华层
# 只要获取用户表中的某条数据，就可以获取豪宅表中对应的信息

# 常用关系型数据库
1.MySQL :开源免费 使用最广 性价比
2.MariaDB: 开源免费 MySQL是同一个作者 用法相似
3.PostgreSQL : 开源免费 支持二次开发 兼容性高
4.Oracle ：收费 安全性高但是维护成本也高，银行等重要机关用
5.sqlite： 小型数据库  主要用于本地测试（django框架自的数据库）

(2)非关系型数据库

一般用在数据的查询

1.数据的存储方式，没固定的表结构
eg：{'name':''}
# 主要采用k：v键值对形式

2.数据之间无法直接建立数据库层面的关系  
# 自己编写代码建立逻辑层面的关系

# 常用非关系型数据库
1. redis : 目前最火、使用频率最高的缓存数据库
    虽然缓存数据库是基于内存做数据存取，但是也拥有持久化的功能
2.mongoDB:文档型数据库，最像关系型数据库的非关系型数据库
    主要用在爬虫、大数据等
3.memcache： 已经被redis淘汰

2.MySQL数据库简介

1)MySQL版本问题

5.6版本：学习阶段使用
5.7版本：使用频率最高
8.0版本：最新版

# mac建议下载8.0版本
# windows哪个版本都可以

2)下载与安装

1.访问官网  https://www.mysql.com/
2.点击下载  DOWNLOADS
3.最下方选择  MySQL Community (GPL) Downloads »
4.点击  MySQL Community Server
5.点击  Archives
6.选择版本 与 本机系统 (学习建议5.6版本 如：5.6.44)
7.选择64位然后download下载
8.解压安装
	上述方式下载的压缩包里含有服务端和客户端 支持本地操作

3)主要目录介绍

bin文件夹
    mysqld.exe服务端    mysql.exe客户端
data文件夹
    存取数据
my-default.ini
    默认配置文件

3.MySQL基本使用

# 建议以管理员身份启动
1.用cmd命令启动服务端
  cmd>>D:>>cd RuanJian\mysql-5.6.44-winx64\bin>>mysqld
    (Tab自动补全)
	'可能会报错:拷贝关键信息去百度'
    此时该cmd窗口已经启动了mysqld服务端(如果关闭窗口服务端也会停止)
    
2.再打开一个新的cmd窗口启动客户端
	cmd>>D:>>cd RuanJian\mysql-5.6.44-winx64\bin>>mysql
    直接回车会以'游客模式'进入 权限和功能很少
    
3.用管理员登录
    mysql -u用户名 -p密码(eg：mysql -uroot -p密码  直接回车)
    # mysql默认管理员账号 用户名是root 密码是空
    如：
    cmd>>D:>>cd RuanJian\mysql-5.6.44-winx64\bin>>mysql -uroot -p
    
4.退出
    exit  或  quit

4.系统服务制作

1.先把bin目录添加到环境变量中
2.将mysql服务端制作成系统服务（随着计算机的开启而启动，关闭而关闭，不需要每次都打开一个服务端在开一个客户端）
	1.以管理员身份打开cmd窗口（搜索cmd右键管理员模式启动）
    2.管理员cmd中执行系统服务命令mysqld--insta11
    （把MySQL添加到系统服务中）
    3.启动服务端
    方式1：右键直接点击启动
    方式2：管理员cmd中执行命令启动net start mysql
    
    
   """
   1.查看系统服务的命令
   services.me
   2.如何卸载重新安装：
   1）关闭mysql服务端
   net stop mysql
   2）移除系统服务
   			1.先确保服务已经关闭
   			2.执行移除命令mysqld--remove
   """

5.密码相关操作

1.修改密码：
	mysqladmin命令
    方式1：# 直接在cmd中写 不要进入mysql
    mysqladmin-u 用户名-p原密码psaaword新密码
    			第一次修改
    mysqladmin-uroot-p123 password 321
    
    方式2：直接修改存储用户数据的表
    
    方式3： 偏门方式（有些版本无法使用）：# 需要先登录
    set password = password('新密码')；

2.忘记密码
    方式1：卸载重装
    方式2：把data目录删除 拷贝别人的目录
    方式3：命令方式修改
        1.关闭正常的服务端：
            net stop mysql
        2.以跳过授权表的方式重启服务端(不校验密码)：
            mysqld --skip-grant-tables
        3.再开一个cmd以管理员身份进入：
            mysql -uroot -p
        4.修改mysql.user表数据
            update mysql.user set password=password('123') where Host='localhost' and User='root';
        5.net stop mysql
        6.net start mysql

6.SQL与NoSQL数据库语句

数据库服务端是可以服务读懂中类型的客户端
客户端可以是自己开发的 也可以是python代码编写的 也可以是java代码编写的

SQL： 操作关系型数据库的语言
有时候也表示关系型数据库

NOSQL： 操作非关系型数据库的语言
有时候也代表非关系型数据库

7.数据库重要概念

# 以下表示并不精准  仅限于小白了解
库 类似于  文件夹
表 类似于   文件夹里的文件
记录  类似于  文件夹里的文件里的每行数据

1.查看所有的数据库
show  databases;
2.查看所有的表
show tables;
3.查看user表里的所有记录
select from mysql.user;

8.基本SQL语句

# 预备知识
1.SQL 语句结束符是分号
    ；
2.取消SQL语句的执行（可以直接让他报错重新输入，反正不影响）
    \C

1)针对库的基本SQL语句

1.增
	create database  库名； # 新建库
2.查
	show databases; # 查看所有库名字
    show create database 库名；#查看指定库的信息
3.改
	alter database 库名 charset = 'gbk';# 修改库的编码
4.删
	drop database 库名；# 删除库

2)针对表的基本SQL语句

'操作表前需先确定库'

select database();  # 查看当前所在的库
use 库名;  # 切换库

1.增
	create table 表名（字段名  字段类型）；# 创建表
    create table t1(id int,name varchar(32));
2.查
	show tables; # 查看库下所有的表名
    show create table 表名； # 查看指定表中的字段
    desc 表名; # 查看指定表中的字段
    """
    跨库查询表：
    desc 库名.表名；
    desc mysql.user;
    """
3.改
	alter table 旧表名rename 新表名；# 改表名
4.删
	drop table 表名； # 删除表

3)针对记录的基本SQL语句

'操作记录前需要先确定库和表'

1.增 
	insert into 表名 values(数据值1，数据值2..)
    insert info t1 values (1,'jason');
    insert info t1 values(2,'torry'),(3,'jack');
2.查
	select from 表名；# 查看表中所有的数据
    '表示查看所有字段'
    select from mysql。user\G；
    '如果表中字段较多出现错乱 结尾可以加\G'
    select name,pwd from t1; # 查看表中name 和 pwd 字段的所有数据
3.改
	update 表名set 字段名=新数据值where筛选条件；# 修改表中某个条件下的字段名改为新数据值
    update t1 set name='torry' where id=1;
4.删
	delete from 表名；# 删除表中所有的数据
    '就会变成一个空表'
    delete from 表名 where 筛选条件 # 按照条件删除数据
  

9.字符编码与配置文件

\s #查看数据库基本信息(用户、版本号、字符编码、端口号等)
5.6版本字符编码【不统一】 需统一改为utf8
5.6以后的版本不需要 默认就是统一的utf8

1.MySQL文件中 my-default.ini #windows下MySQL默认配置文件，Mac后缀不一样
	再复制一份该文件,重命名为my.ini
2.清空里面内容 添加字符编码相关配置(百度查看)
  # 注：此处清空内容后会埋下一个坑 下面整型严格模式时会发现
    [mysqld]
      character-set-server=utf8mb4
      collation-server=utf8_general_ci
    [client]
      default-character-set=utf8mb4
    [mysql]
      default-character-set=utf8mb4 
   # utf8mb4能存储表情 功能更强大
   # utf8与utf-8不一样,MySQL只有utf8
   # 如果配置文件涉及到mysqld相关的配置修改,那必须重启服务端才能生效
   # 搜索【服务】,找到MySQL重新启动即可
   # 需注意以上修改编码前的之前的数据编码并未修改

"""
偷懒操作：每次登录还要输root 密码 太麻烦,所以可以在配置文件中的[mysql]下写好用户名和密码,这样以后登录的时候直接打mysql就可以登录
    [mysql]
        user='root'
        password='123'
"""

10.数据库存储引擎(重要)

存储引擎：数据库针对数据采取的多种存取方式
show engines; 查看所有的存储引擎

1)需了解的4个存储常见引擎

1.MyISAM
      1)MySQL 5.5及以前版本默认的存储引擎
      2)存取数据的速度快 功能较少 安全性较低

2.InnoDB  ※
      1)MySQL 5.6开始之后版本默认的存储引擎
      2)存取数据的速度没MyISAM快 支持事物、行锁、外键等功能 安全性较高
        
3.Memory
      1)基于内存的存储引擎
      2)存取数据极快 但是重启数据库,数据立刻丢失(仅用于临时表数据存取)
        
4.BlackHole
      1)黑洞 类似垃圾桶
      2)任何写进去的数据都会立刻丢掉

2)不同存储引擎之间底层文件的区别

MySQL中默认忽略大小写 对大小写不敏感

# 1.创建4个表 分别对应4个不同的存储引擎
create table t1(id int) engine = myisam;
create table t2(id int) engine = innodb;
create table t3(id int) engine = memory;
create table t4(id int) engine = blackhole;

# 2.检查MySQL>>data目录>>对应库中的文件发现：各个表中新建的文件不同
1)t1表(MyISAM 存储引擎);
      t1.frm  '表结构'
      t1.MYD  '表数据'
      t1.MYI  '表索引(加快了数据查询)'
2)t2表(InnoDB 存储引擎);
      t2.frm  '表结构'
      t2.ibd  '表数据、表索引(加快了数据查询)'
3)t3表(Memory 存储引擎);
      t3.frm  '表结构'
4)t4表(BlackHole 存储引擎);
      t4.frm  '表结构'
    
# 3.往4个表中插入记录发现：
insert into t1 values(1);
insert into t2 values(1);
insert into t3 values(1);
insert into t4 values(1);

select * from t1; # 正常插入记录
select * from t2; # 正常插入记录
select * from t3; # 重启数据库Memory中的数据丢失
select * from t4; # 无法把数据存入BlackHole中

11.创建表的完整语法

create table 表名(
    字段名1 字段类型(数字) 约束条件，
    字段名2 字段类型(数字) 约束条件,
    字段名3 字段类型(数字) 约束条件
);


 1.字段名和字段类型必须要有(可以不用写多对,但是'至少写一对')
 2.字段类型后的数字 和 约束条件 可以写'也可以不写'
 3.约束条件可以写多个 不过需要'空格隔开'
*4.当多个字段名和字段类型时,最后一对'结尾不能加逗号'！！！

12.MySQL字段基本数据类型

1)整型

类型	所占字节bytes	带负号	不带负号
tinyint(微整型)	1	-128~127	0~255
smallint(小整型)	2	-32768~32767	0~65535
int(整型)	4	-2147483648~2147483647	0~4294967295
bigint(大整型)	8	-9223372036854775808~9223372036854775807	0~18446744073709551615

(1)验证整型默认是否携带正负号

当默认携带正负号时 范围则是负数到正数的值

create table t5(id tinyint);  # 创建t5表 类型为微整型
insert into t5 values(-129),(128);  # 插入两条数据 分别超出带正负号的最大、最小范围
select * from t5;  # 查看t5表中所有记录数据

1)'发现结果为-128 和 127' # 由此可得正负号也默认占一个字节
2)发现哪怕超出范围 MySQL会自动填写为两个边界值，不合理(应该直接报错)

(2)移除负号：解决正负号问题

当移除负号后 范围则是正数的值

unsigned约束条件之一：不带负号

create table t6(id tinyint unsigned);  # 创建t6表 类型为微整型 不带负号
insert into t6(-1),(256);  # 插入两条数据 分别超出不带负号的最大、最小范围
select * from t6;  # 查看t6表中所有记录数据

1)'发现结果为0 和 255' # 由此可得移除负号后 则不能插入负数 只能插入正数,且范围变成了255
2)发现哪怕超出范围 MySQL会自动填写为两个边界值，不合理(应该直接报错)

(3)严格模式：解决自动填写边界值问题

插入的数据当超过数据类型的范围时 应该直接报错,不该自动修改数据！这样会导致我插入的数据失去了本身的意义。

PS: 其实MySQL按理说是会报错的,只是在前面清空配置文件时清掉了

# 方式一：命令临时修改
set session sql_mode = 'strict_trans_tables'; # 在当前客户端有效
set global sql_mode = 'strict_trans_tables'; # 在当前服务端有效

# 方式二：在配置文件中永久修改(建议)
    [mysqld]
        sql_mode='STRICT_TRANS_TABLES'

2)浮点型

float
double
decimal
以上三种'都可以存储浮点型数据' 只是各自的'精确度不一样'

float(20,10)  # 共存储20位数 小数点后面占10位
double(20,10)  # 共存储20位数 小数点后面占10位
decimal(20,10)  # 共存储20位数 小数点后面占10位

(1)验证精确度问题

create table t7(id float(30,20));  # 创建t7表 类型为float 共存储30位数, 小数点后占20位
create table t8(id double(30,20));  # 创建t8表 类型为double 共存储30位数, 小数点后占20位
create table t9(id decimal(30,20));  # 创建t9表 类型为decimal 共存储30位数, 小数点后占20位

insert into t7 values(1.11111111111111111111);
insert into t8 values(1.11111111111111111111);
insert into t9 values(1.11111111111111111111);

select * from t7;  # 发现小数点后到第7位后精确度就不准确了 1.11111116409301760000
select * from t8;  # 发现小数点后到第15位后精确度就不准确了 1.11111111111111120000
select * from t9;  # 发现小数点后到第20位后精确度还很准确 1.11111111111111111111

"""
结论：
1.精确度排名
    float < double < decimal

2.一般float就够平时使用了，如果想追求完美的精确度可以用字符型来代替(如：手机号等)
"""

3)字符型

1)char 定长
     char(4)  
         # 最多存储4个字符 超出范围报错 
         # 不够4个用空格填充至4个
2)varchar 变长
     varchar(4)  
          # 最多存储4个字符 超出范围报错 
          # 不够4个有几个存几个

(1)验证char与varchar长度问题

char_length(字段名) 获取字段存储的数据长度

create table t10(name char(4)); # 创建t10表 类型为char 最大长度为4
create table t11(name varchar(4)); # 创建t11表 类型为varchar 最大长度为4

insert into t10 values('a');
insert into t11 values('b');

select char_length(name) from t10;  # 获取t10表中name字段数据的长度
select char_length(name) from t11;  # 获取t11表中name字段数据的长度
"""
以上结果：
    t10表中长度为1
    t11表中长度为1
    
1.提问：为什么char(4)不够4个用空格填充至4个 获取长度后还是1？
1.回答：因为默认情况下char在存储的时候针对没有满足固定位数的字符确实会自动在后面填充空格,但是【在读取的时候又会自动将填充的空格移除掉】

2.提问：如何取消该移除空格机制？
2.回答：命令临时修改
  set global sql_mode='strict_trans_tables,pad_char_to_full_length';
"""
# 需注意：上述临时配置命令是替换不是新增，所以要把之前配置的超出范围报错的命令也加上！

该取消移除空格机制命令不用添加到配置文件中 临时用一下就好,因为移除的机制挺好用的

(2)char与varchar哪个好

类型	优势	劣势
char	整存整取 速度快	浪费存储空间
varchar	节省存储空间	存取数据的速度慢于char

# char与varchar在内存中怎么存数据的：

1)char(4)
    a  bb ccdddd
'占4个位置存放'

2)varchar(4)
1bytes+a1bytes+son1bytes+jack1bytes+tom1bytes+lili
'存储数据都是需要操作报头才能让varchar识别每个字符串多少长度分开处理。这就是varchar比char耗时多的原因'

# 结论：两者使用频率都很高根据实际应用决定 现在默认很多时候是varchar

4)数字的含义

数字在大部分情况下都是用来表示限制存储数据的长度

但是！在整型中是控制最少展示的长度！！

zerofill约束条件，不足的用0填充

# zerofill常用在快递单号中
create table t12(id int(3) zerofill);
insert into t12 values(1),(12345);

select * from t12;
"""
结果为：
001     因为最少展示3位 且不足的用0填充
12345   因为够3位了 所以不需要填充
"""
# 总结：以后涉及到整型字段的定义 类型后面不需要加括号写数字 除非有业务需求必须固定位数(快递单号等)

5)枚举与集合

(1)enum 枚举

多选一

# eg: 性别(男 女 其他)

create table t14(gender enum('male','female','others'));
# 只能选三个中的一个
insert into t14 values('male');

(2)set 集合

多选一 或 多选多

# eg：爱好(唱 跳 rap)
create table t15(hobby set('sing','jump','rap'));
# 只能输三个中的一个或多个
insert into t15 values('sing'); # 多选一
insert into t15 values('sing,jump'); # 多选多

6)日期

date		 年月日
datetime	 年月日时分秒
time		 时分秒
year		 年份

create table t16(date date, datetime datetime, time time,year year);
insert into t16 values('2022-11-23','2022-11-23 12:00:00','12:00:00','2022');
select * from t16;

# 结果：
+------------+---------------------+----------+------+
| date       | datetime            | time     | year |
+------------+---------------------+----------+------+
| 2022-11-23 | 2022-11-23 12:00:00 | 12:00:00 | 2022 |
+------------+---------------------+----------+------+