01.MYSQL概述

1.数据库概念

数据库:存储数据的仓库,数据有组织的进行存储(DataBase:DB)。

数据库管理系统:操纵和管理数据库的大型软件(DataBase Management System:DBMS)。

SQL:操作关系型数据库的编程语言,定义了一套关系型数据库的统一标准(Structured Query Language:SQL)。

主流的关系型数据库管理系统

  • ORACLE
  • MYSQL
  • Microsoft SQL Server
  • PostgreSQL
  • IBM DB2
  • ...

2.MYSQL数据库

2.1版本

  • 社区版:免费,MYSQL不提供任何技术支持
  • 商业版:收费,可以试用30天,官方提供技术支持

2.2下载

统一使用版本为8.0.X

官方链接:点击跳转

国内加速链接:点击跳转

2.3安装

2.3.1Windows安装

2.3.1.1MSI安装

image-20230311003518320

双击运行(如果提示没有安装依赖,则需要安装c++环境)

  1. c++合集点击这里
  2. 官网下载

Microsoft Visual C++ Redistributable 2019
x86: https://aka.ms/vs/16/release/VC_redist.x86.exe
x64: https://aka.ms/vs/16/release/VC_redist.x64.exe

Microsoft Visual C++ Redistributable 2017
x86: https://go.microsoft.com/fwlink/?LinkId=746571
x64: https://go.microsoft.com/fwlink/?LinkId=746572

Microsoft Visual C++ Redistributable 2015
x86+x64: https://www.microsoft.com/en-pk/download/details.aspx?id=48145
x86+x64 update3: https://www.microsoft.com/en-us/download/details.aspx?id=53587

Microsoft Visual C++ Redistributable 2013
x86+x64 in all Language: https://support.microsoft.com/en-us/help/4032938/update-for-visual-c-2013-redistributable-package

Microsoft Visual C++ Redistributable 2012
x86+x64 in all Language: https://my.visualstudio.com/Downloads?pid=1452

Microsoft Visual C++ Redistributable 2010
x86-sp1: https://www.microsoft.com/en-pk/download/details.aspx?id=8328
x86-sp1-MFC-sec-update: https://www.microsoft.com/en-us/download/details.aspx?id=26999

Microsoft Visual C++ Redistributable 2008
x86: https://www.microsoft.com/en-us/download/details.aspx?id=29
x64: https://www.microsoft.com/en-us/download/details.aspx?id=15336
x86+x64-MFC-update: https://www.microsoft.com/en-us/download/details.aspx?id=26368

Microsoft Visual C++ Redistributable 2005
x86: https://www.microsoft.com/en-pk/download/details.aspx?id=3387
x64: https://www.microsoft.com/en-US/Download/confirmation.aspx?id=21254

image-20230311003620006

image-20230311012049843

image-20230311012115213

image-20230311161325556

image-20230311161420244

这里需要更改安装路径

image-20230311161502524

image-20230311161521661

image-20230311161538628

image-20230311161601942

这个版本好像不会自动添加环境变量,所以这里手动添加一下

新建一个系统变量,命名为MYSQL_HOME,值为MYSQL安装路径

image-20230311161938357

然后添加到path中,记得添加;

image-20230311162031499

使用mysql -V验证是否正确安装

image-20230311162205024

新建配置文件my.ini

[mysqld]
# 设置3306端口
port=3306
# 设置mysql的安装目录
basedir=D:\Server\MySQL8
# 设置mysql数据库的数据的存放目录
datadir=D:\Server\MySQL8\data
# 允许最大连接数
max_connections=200
# 允许连接失败的次数。
max_connect_errors=10
# 服务端使用的字符集默认为utf8mb4
character-set-server=utf8mb4
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 默认使用“mysql_native_password”插件认证
#mysql_native_password
default_authentication_plugin=mysql_native_password

[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8mb4

[client]
# 设置mysql客户端连接服务端时默认使用的端口
port=3306
default-character-set=utf8mb4

接下来初始化MySQL数据库(注意,初始化之前删除data下的所有文件以及文件夹)

mysqld --initialize --console

image-20230311171939976

输出日志中root@localhost后边的就是默认密码,须牢记

接下来安装服务

mysqld --install mysql

image-20230311172153975

启动mysql服务

net start mysql

image-20230311172234951

使用默认密码登录上,之后修改密码即可使用

alter user 'root'@'localhost' identified by '新密码'

image-20230311172528094

然后重启MySQL服务,使得MySQL更改生效

image-20230311172655165

2.3.1.2绿色版免安装

image-20230311015012360

解压文件之后,新建data文件夹和my.ini配置文件

image-20230311015058919

编辑my.ini配置文件

basedirdatadirServer,这三个路径根据自己的路径修改

[mysqld]
# 设置3306端口
port=3306
# 设置mysql的安装目录
basedir=D:\\server\\mysql-8.0.29-winx64
# 设置mysql数据库的数据的存放目录
datadir=D:\\server\\mysql-8.0.29-winx64\\data
# 允许最大连接数
max_connections=200
# 允许连接失败的次数。
max_connect_errors=10
# 服务端使用的字符集默认为utf8mb4
character-set-server=utf8mb4
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 默认使用“mysql_native_password”插件认证
#mysql_native_password
default_authentication_plugin=mysql_native_password

[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8mb4

[client]
# 设置mysql客户端连接服务端时默认使用的端口
port=3306
default-character-set=utf8mb4

image-20230311193434268

新建系统环境变量

image-20230311193143509

关联到PATH

image-20230311193231532

初始化

初始化之前必须把cmd切换到安装MySQL的根目录下,不然后边的操作会报错找不到关联文件

mysqld --initialize --console

image-20230311194217440

必须牢记root@localhost后边的代码,这是MySQL的默认密码

安装MySQL服务

mysqld --install mysql

image-20230311194243603

启动服务

net start mysql

image-20230311194300470

使用默认密码连接数据库

mysql -uroot -p

修改数据库密码

ALTER USER 'root'@'localhost' IDENTIFIED BY '123456.';

image-20230311194558930

退出数据库重新启动,使得更改生效

image-20230311194808249

2.3.2CentOS安装

2.3.2.1YUM安装

  1. 在国内镜像站中查找对应的tar包点击跳转

    image-20230311201059263

  2. 下载RPM包

    wget https://repo.huaweicloud.com/mysql/Downloads/MySQL-8.0/mysql-8.0.29-1.el7.x86_64.rpm-bundle.tar

    image-20230311202037579

  3. 解压

    tar -xvf mysql-8.0.29-1.el7.x86_64.rpm-bundle.tar

    image-20230311202225573

  4. YUM安装

    yum install mysql-community* -y

  5. 启动MySQL

    systemctl start mysqld

  6. 查看默认密码

    grep 'password' /var/log/mysqld.log

    image-20230311202937651

  7. 使用默认密码登录

    mysql -u root -p

    image-20230311203036842

  8. 修改登录密码

    ALTER USER 'root'@'localhost' IDENTIFED BY 'Test123456.';

    linux上不知道什么鬼,有密码检测插件,简单的密码无法设置,后边直接卸载插件

    image-20230311203815490

  9. 退出MySQL,重启使得更改生效

    systemctl restart mysqld

    image-20230311204030405

卸载密码检测插件:

  • MySQL5.7, 用的是 validate_password 插件
  • MySQL8.0, 用的是 validate_password 组件

Ubuntu20.04下用apt安装的MySQL8.0默认没有使用validate_password 插件或组件
CentOS7下用官方yum仓库安装的MySQL8.0默认启用validate_password 组件
MySQL5.7的 validate_password 插件 在MySQL8.0中任然可以使用,但默认不使用,已过时,未来准备移除

  1. 登录MySQL

  2. 卸载组件

    UNINSTALL COMPONENT 'file://component_validate_password';

    image-20230311204609035

  3. 修改简易密码

    ALTER USER 'root'@'localhost' IDENTIFIED BY '0';

    image-20230311204804286

2.3.2.2编译安装

  1. 下载源码包

    wget https://repo.huaweicloud.com/mysql/Downloads/MySQL-8.0/mysql-8.0.29-linux-glibc2.12-x86_64.tar.xz

  2. 解压文件

    tar -xvf mysql-8.0.29-linux-glibc2.12-x86_64.tar.xz

  3. 新建一个mysql文件夹

    mkdir /usr/local/mysql

  4. 将解压出来的文件全部移动到/usr/local/mysql/

    mv mysql-8.0.29-linux-glibc2.12-x86_64/* /usr/local/mysql/

  5. 创建mysql用户组和用户,并添加密码

    # 创建MySQL用户组
    groupadd mysql
    
    # 创建MySQL用户
    useradd mysql
    
    # 为创建的用户添加密码
    passwd mysql
    
  6. 修改配置文件/etc/my.cnf

    # For advice on how to change settings please see
    # http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
    # *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
    # *** default location during install, and will be replaced if you
    # *** upgrade to a newer version of MySQL.
    
    [mysqld]
    
    # Remove leading # and set to the amount of RAM for the most important data
    # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
    # innodb_buffer_pool_size = 128M
    
    # Remove leading # to turn on a very important data integrity option: logging
    # changes to the binary log between backups.
    # log_bin
    
    # These are commonly set, remove the # and set as required.
    basedir = /usr/local/mysql
    datadir = /usr/local/mysql/data
    port = 3306
    # server_id = .....
    socket = /tmp/mysql.sock
    character-set-server = utf8mb4
    collation_server = utf8mb4_general_ci
    skip-name-resolve
    log-error = /usr/local/mysql/data/error.log
    pid-file = /usr/local/mysql/data/mysql.pid
    lower_case_table_names=1
    
    # Remove leading # to set options mainly useful for reporting servers.
    # The server defaults are faster for transactions and fast SELECTs.
    # Adjust sizes as needed, experiment to find the optimal values.
    # join_buffer_size = 128M
    # sort_buffer_size = 2M
    # read_rnd_buffer_size = 2M 
    
    sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
    

    直接备份覆盖即可。

    配置文件中注意几点:

    character-set-server = utf8mb4:表示mysql服务器级别默认的字符集编码,最好设置为utf8mb4;
    lower_case_table_names=1:表示mysql中将表名一律转为小写;(其实取值有0、1或2,其中2是最理想的状态,但是这个配置在window、linux、macos下表现的不一致,所以还是取1吧,只不过取1后,所有的表名都强制小写了。)

  7. 新建数据库文件夹

    mkdir /usr/local/mysql/data

  8. 设定访问权限

    这里需要手动设定,MySQL包自己会用mysql用户启动进程,所以需要提前新增用户和用户组

    # 设置文件所属用户
    chown -R mysql /usr/local/mysql/
    
    # 设置文件所属组
    chgrp -R mysql /usr/local/mysql/
    
  9. 初始化数据库

    /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --lower_case_table_names=1
    
  10. 查看随机密码

    grep 'password' /usr/local/mysql/data/error.log

    image-20230311211506531

  11. 启动MySQL服务

    # 查询MySQL是否启动
    ps -ef | grep mysql
    ps -ef | grep mysqld
    
    # 启动服务
    /usr/local/mysql/support-files/mysql.server start
    
  12. 使用随机密码登录

    mysql -u root -p

  13. 修改密码

    ALTER USER 'root'@'localhost' IDENTIFIED BY '0';

  14. 重新启动服务

    /usr/local/mysql/support-files/mysql.server restart

  15. 服务启动制作

    # 添加MySQL软连接重新启动
    ln -s /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
    ln -s /usr/local/mysql/bin/mysql /usr/bin/mysql
    
    # 重新启动,此时只能用`service`启动服务,暂时不能用`systemctl`
    service mysqld restart
    
    # 制作systemctl启动
    vim /usr/lib/systemd/system/mysqld.service
    
    # 填入如下内容
    [Unit]
    Description=mysql
    After=network.target
    
    [Service]
    Type=forking
    PIDFile=/usr/local/mysql/data/mysql.pid
    ExecStart=/usr/local/mysql/support-files/mysql.server start
    ExecReload=/usr/local/mysql/support-files/mysql.server restart
    ExecStop=/usr/local/mysql/support-files/mysql.server stop
    PrivateTmp=true
    
    [Install]
    WantedBy=multi-user.target
    
    # 刷新系统的服务
    systemctl daemon-reload
    
    # 然后就可以用systemctl来启动服务了
    

    image-20230311212601625

2.3.3Ubuntu安装

2.3.2.1APT安装

  1. 更新索引

    sudo apt update

  2. 下载存储包

    wget https://repo.mysql.com//mysql-apt-config_0.8.17-1_all.deb

  3. 添加资源包

    sudo dpkg -i mysql-apt-config_0.8.17-1_all.deb

  4. 更新索引

    sudo apt update

  5. 安装MySQL

    sudo apt-get install mysql-server

  6. 跳过密码认证

    编辑/etc/my.cnf配置文件

    [mysqld]
    skip-grant-tables
    
  7. 使用空密码登录MySQL

  8. 刷新权限

    FLUSH PRIVILEGES;

  9. 设置密码

    ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '0';

  10. 退出,注释掉上边的添加的字段

    image-20230311232525183

  11. 重启服务即可

2.3.2.2编译安装

  1. 下载源码包

    wget https://repo.huaweicloud.com/mysql/Downloads/MySQL-8.0/mysql-8.0.29-linux-glibc2.12-x86_64.tar.xz

  2. 解压文件

    tar xvf mysql-8.0.29-linux-glibc2.12-x86_64.tar.xz

  3. 创建MySQL目录

    sudo mkdir /usr/local/mysql

  4. 移动文件至创建的目录

    sudo mv mysql-8.0.29-linux-glibc2.12-x86_64/* /usr/local/mysql/

  5. 安装依赖环境

    sudo apt-get install libaio1 libnuma1 libncurses5

  6. 编辑配置文件

    # vim /etc/my.cnf
    
    [mysqld]
    basedir = /usr/local/mysql
    datadir = /usr/local/mysql/data
    port = 3306
    socket = /tmp/mysql.sock
    character-set-server = utf8mb4
    collation_server = utf8mb4_general_ci
    skip-name-resolve
    log-error = /usr/local/mysql/data/error.log
    pid-file = /usr/local/mysql/data/mysql.pid
    # lower_case_table_names=1
    sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
    
    [client]
    default-character-set=utf8mb4
    socket=/tmp/mysql.sock
    
    [mysql]
    default-character-set=utf8mb4
    socket=/tmp/mysql.sock
    
  7. 创建MySQL的用户和用户组并更改文件权限

    # 创建用户组
    sudo groupadd mysql
    
    # 创建用户
    sudo useradd -r -g mysql -s /bin/false mysql
    
    # 创建数据目录
    sudo mkdir /usr/local/mysql/data
    
    # 创建数据导出目录
    sudo mkdir /usr/local/mysql/mysql-files
    
    # 更改文件权限
    sudo chmod 750 /usr/local/mysql/mysql-files
    
    # 更改文件的所属用户
    sudo chown -R mysql /usr/local/mysql
    
    # 更改文件的所属用户组
    sudo chgrp -R mysql /usr/local/mysql
    
  8. 初始化

    # 执行初始化:过程中会为root用户生成一个随机密码并打印至控制台
    sudo /usr/local/mysql/bin/mysqld --initialize --user=mysql
    
    # 创建证书和密钥文件
    sudo /usr/local/mysql/bin/mysql_ssl_rsa_setup
    
    # 启动服务
    sudo /usr/local/mysql/support-files/mysql.server start
    
    # 查看服务
    sudo /usr/local/mysql/support-files/mysql.server status
    
    # 停止服务
    sudo /usr/local/mysql/support-files/mysql.server stop
    
  9. 制作系统服务

    # 创建/etc/systemd/system/mysql.service
    sudo vim /etc/systemd/system/mysql.service
    
    # 编辑如下内容
    [Unit]
    Description=mysql
    After=network.target
    
    [Service]
    Type=forking
    PIDFile=/usr/local/mysql/data/pythonServer.pid
    ExecStart=/usr/local/mysql/support-files/mysql.server start
    ExecReload=/usr/local/mysql/support-files/mysql.server restart
    ExecStop=/usr/local/mysql/support-files/mysql.server stop
    PrivateTmp=true
    
    [Install]
    WantedBy=multi-user.target
    
    # 重载系统服务列表
    sudo systemctl daemon-reload
    
    # 然后就可以用systemctl来启动服务了
    

3.数据模型

3.1关系型数据库

概念:建立在关系模型基础之上,由多张表互相连接的二维表组成的数据库。

特点:

  • 使用表存储数据,格式统一,便于维护
  • 使用SQL语言操作,标准统一,使用方便

3.2MYSQL数据模型

image-20230312004019693

posted @ 2023-03-12 01:51  ChanceySolo  阅读(74)  评论(0编辑  收藏  举报