MySQL基础学习① 进入MySQL基础学习,安装、启动、停止、访问、卸载MySQL

1、什么是MySql?

在这里插入图片描述
在这里插入图片描述
截取自百度百科

互联网时代,我们都是在跟数据打交道,例如:

  • 天猫淘宝上热卖产品,个人历史订单
  • 微信朋友圈聊天记录
  • 手机本地通信录

可以毫不夸张地说,只要涉及到互联网,始终离不开数据。

那么数据就得有地方去存放,并且是持久化存放(总不能全部数据直接加载在内存中,然后断电GG)。

并且为了快速查询到我们需要的数据(一些大系统数据库至少过T,总不能全库扫描),那么总得有一个系统去帮我们做这件事情。

那么就得有一个系统能满足以下两点要求:

  • 持久化存储(硬盘文件系统)
  • 快速查询目标数据(也叫做快速索引,那么就意味着我们的数据肯定处于某种有效的组织中 —— 从算法角度来说,就是数据结构,举个例子:教育局为了管理学生资料,给每一个学生分配一个唯一的学号,通过学号来找到每个学生)

能满足以上两个条件的系统就是我们常说的数据库管理系统(DataBase Manager System,DBMS),在DBMS下管理着数据库(DataBase,DB),最终我们通过SQL
结构化查询语言
Structured query language)来对数据库进行CURD(增删改查)。

注意:我们常说数据库,其实指的是整个数据库管理系统,包括DBMD和DB。

在众多的数据库管理系统中,最受大众喜欢也是目前绝大部分公司都在使用的就是MySQL(开源、免费,意味着接入成本低),这也是我们为什么要学习它的原因。

2、安装MySql

Mysql采用C-S架构,也就是Client-Server,所以我们首先需要安装 MySql Server。

2.1 下载地址

2.2.1 下载地址1 —— 可以下载免安装版本

https://dev.mysql.com/downloads/mysql/

在这里插入图片描述
习惯性我比较喜欢后退两个版本(说白了就是求稳)。
比如说,我这里下载 5.6.49版本(这个是免安装版本):

在这里插入图片描述
点击Download
在这里插入图片描述
静静等待下载成功。

如果你还是下载不了,那么我也上传到了CSDN,免积分下载,请点击

下载地址 免积分下载

2.1.2 下载地址2 —— 下载MSI文件(推荐这种方式。。。)

http://mirrors.sohu.com/mysql/MySQL-5.5/?C=S&O=A

在这里插入图片描述
我这里下载了 mysql-5.5.60-winx64.msi 这个文件、
如果你还是下载不了,那么我也上传到了CSDN,免积分下载,请点击

下载地址 免积分下载

2.2 推荐安装步骤 —— 针对下载方式2

2.2.1 安装 mysql-5.5.60-winx64.msi —— 图片讲解,认真看好每一步

  • 点击 mysql-5.5.60-winx64.msi ,弹出界面,点击Next
    在这里插入图片描述

  • 勾选 I accept the terms in the License Agreement,点击 Next
    在这里插入图片描述

  • 选择需要的构建类型,这里我们选择custom
    在这里插入图片描述
    在这里插入图片描述
    点击 Next
    在这里插入图片描述

  • 点击 install,进行安装
    在这里插入图片描述
    安装成功
    在这里插入图片描述
    到这一步我们仅仅是安装好了环境,但是还没有进行配置

我们来看看一下安装目录 C:\Program Files\MySQL\MySQL Server 5.5
在这里插入图片描述
一般熟悉win应用程序的人来说,我们会更关注bin目录(因为一般执行命令都是在里面):
在这里插入图片描述

先了解到这里,点击 Finish,进行下一步的配置。

2.2.2 配置MySQL

  • 点击 Finish,正常情况会弹出这个页面
    在这里插入图片描述

如果不小心关闭了,那么也可以通过bin命令来调起:
在这里插入图片描述

  • 点击 Next,选择Detailed Configuration
    在这里插入图片描述

  • 选择服务类型
    在这里插入图片描述

  • 选择数据库类型
    在这里插入图片描述
    在这里插入图片描述
    点击Next

  • 设置并发数,默认点击Next
    在这里插入图片描述

  • 设置MySql的端口号,默认即可,点击next
    在这里插入图片描述

  • 设置默认字符集
    在这里插入图片描述

  • 设置windows上的选项
    在这里插入图片描述

  • 配置MySql Server的安全选项(账号密码)
    在这里插入图片描述

  • 配置完毕,点击执行,等待配置成功
    在这里插入图片描述
    在这里插入图片描述

2.2.3 查看配置(可忽略)

在这里插入图片描述
使用记事本打开,可以看到以下内容:

# MySQL Server Instance Configuration File
# ----------------------------------------------------------------------
# Generated by the MySQL Server Instance Configuration Wizard
#
#
# Installation Instructions
# ----------------------------------------------------------------------
#
# On Linux you can copy this file to /etc/my.cnf to set global options,
# mysql-data-dir/my.cnf to set server-specific options
# (@localstatedir@ for this installation) or to
# ~/.my.cnf to set user-specific options.
#
# On Windows you should keep this file in the installation directory 
# of your server (e.g. C:\Program Files\MySQL\MySQL Server X.Y). To
# make sure the server reads the config file use the startup option 
# "--defaults-file". 
#
# To run run the server from the command line, execute this in a 
# command line shell, e.g.
# mysqld --defaults-file="C:\Program Files\MySQL\MySQL Server X.Y\my.ini"
#
# To install the server as a Windows service manually, execute this in a 
# command line shell, e.g.
# mysqld --install MySQLXY --defaults-file="C:\Program Files\MySQL\MySQL Server X.Y\my.ini"
#
# And then execute this in a command line shell to start the server, e.g.
# net start MySQLXY
#
#
# Guildlines for editing this file
# ----------------------------------------------------------------------
#
# In this file, you can use all long options that the program supports.
# If you want to know the options a program supports, start the program
# with the "--help" option.
#
# More detailed information about the individual options can also be
# found in the manual.
#
#
# CLIENT SECTION
# ----------------------------------------------------------------------
#
# The following options will be read by MySQL client applications.
# Note that only client applications shipped by MySQL are guaranteed
# to read this section. If you want your own MySQL client program to
# honor these values, you need to specify it as an option during the
# MySQL client library initialization.
#
[client]

port=3306

[mysql]

default-character-set=utf8


# SERVER SECTION
# ----------------------------------------------------------------------
#
# The following options will be read by the MySQL Server. Make sure that
# you have installed the server correctly (see above) so it reads this 
# file.
#
[mysqld]

# The TCP/IP Port the MySQL Server will listen on
port=3306


#Path to installation directory. All paths are usually resolved relative to this.
basedir="C:/Program Files/MySQL/MySQL Server 5.5/"

#Path to the database root
datadir="C:/ProgramData/MySQL/MySQL Server 5.5/Data/"

# The default character set that will be used when a new schema or table is
# created and no character set is defined
character-set-server=utf8

# The default storage engine that will be used when create new tables when
default-storage-engine=INNODB

# Set the SQL mode to strict
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

# The maximum amount of concurrent sessions the MySQL server will
# allow. One of these connections will be reserved for a user with
# SUPER privileges to allow the administrator to login even if the
# connection limit has been reached.
max_connections=100

# Query cache is used to cache SELECT results and later return them
# without actual executing the same query once again. Having the query
# cache enabled may result in significant speed improvements, if your
# have a lot of identical queries and rarely changing tables. See the
# "Qcache_lowmem_prunes" status variable to check if the current value
# is high enough for your load.
# Note: In case your tables change very often or if your queries are
# textually different every time, the query cache may result in a
# slowdown instead of a performance improvement.
query_cache_size=0

# The number of open tables for all threads. Increasing this value
# increases the number of file descriptors that mysqld requires.
# Therefore you have to make sure to set the amount of open files
# allowed to at least 4096 in the variable "open-files-limit" in
# section [mysqld_safe]
table_cache=256

# Maximum size for internal (in-memory) temporary tables. If a table
# grows larger than this value, it is automatically converted to disk
# based table This limitation is for a single table. There can be many
# of them.
tmp_table_size=35M


# How many threads we should keep in a cache for reuse. When a client
# disconnects, the client's threads are put in the cache if there aren't
# more than thread_cache_size threads from before.  This greatly reduces
# the amount of thread creations needed if you have a lot of new
# connections. (Normally this doesn't give a notable performance
# improvement if you have a good thread implementation.)
thread_cache_size=8

#*** MyISAM Specific options

# The maximum size of the temporary file MySQL is allowed to use while
# recreating the index (during REPAIR, ALTER TABLE or LOAD DATA INFILE.
# If the file-size would be bigger than this, the index will be created
# through the key cache (which is slower).
myisam_max_sort_file_size=100G

# If the temporary file used for fast index creation would be bigger
# than using the key cache by the amount specified here, then prefer the
# key cache method.  This is mainly used to force long character keys in
# large tables to use the slower key cache method to create the index.
myisam_sort_buffer_size=69M

# Size of the Key Buffer, used to cache index blocks for MyISAM tables.
# Do not set it larger than 30% of your available memory, as some memory
# is also required by the OS to cache rows. Even if you're not using
# MyISAM tables, you should still set it to 8-64M as it will also be
# used for internal temporary disk tables.
key_buffer_size=55M

# Size of the buffer used for doing full table scans of MyISAM tables.
# Allocated per thread, if a full scan is needed.
read_buffer_size=64K
read_rnd_buffer_size=256K

# This buffer is allocated when MySQL needs to rebuild the index in
# REPAIR, OPTIMZE, ALTER table statements as well as in LOAD DATA INFILE
# into an empty table. It is allocated per thread so be careful with
# large settings.
sort_buffer_size=256K


#*** INNODB Specific options ***


# Use this option if you have a MySQL server with InnoDB support enabled
# but you do not plan to use it. This will save memory and disk space
# and speed up some things.
#skip-innodb

# Additional memory pool that is used by InnoDB to store metadata
# information.  If InnoDB requires more memory for this purpose it will
# start to allocate it from the OS.  As this is fast enough on most
# recent operating systems, you normally do not need to change this
# value. SHOW INNODB STATUS will display the current amount used.
innodb_additional_mem_pool_size=3M

# If set to 1, InnoDB will flush (fsync) the transaction logs to the
# disk at each commit, which offers full ACID behavior. If you are
# willing to compromise this safety, and you are running small
# transactions, you may set this to 0 or 2 to reduce disk I/O to the
# logs. Value 0 means that the log is only written to the log file and
# the log file flushed to disk approximately once per second. Value 2
# means the log is written to the log file at each commit, but the log
# file is only flushed to disk approximately once per second.
innodb_flush_log_at_trx_commit=1

# The size of the buffer InnoDB uses for buffering log data. As soon as
# it is full, InnoDB will have to flush it to disk. As it is flushed
# once per second anyway, it does not make sense to have it very large
# (even with long transactions).
innodb_log_buffer_size=2M

# InnoDB, unlike MyISAM, uses a buffer pool to cache both indexes and
# row data. The bigger you set this the less disk I/O is needed to
# access data in tables. On a dedicated database server you may set this
# parameter up to 80% of the machine physical memory size. Do not set it
# too large, though, because competition of the physical memory may
# cause paging in the operating system.  Note that on 32bit systems you
# might be limited to 2-3.5G of user level memory per process, so do not
# set it too high.
innodb_buffer_pool_size=107M

# Size of each log file in a log group. You should set the combined size
# of log files to about 25%-100% of your buffer pool size to avoid
# unneeded buffer pool flush activity on log file overwrite. However,
# note that a larger logfile size will increase the time needed for the
# recovery process.
innodb_log_file_size=54M

# Number of threads allowed inside the InnoDB kernel. The optimal value
# depends highly on the application, hardware as well as the OS
# scheduler properties. A too high value may lead to thread thrashing.
innodb_thread_concurrency=10

里面的内容就是我们刚才配置好的。

2.3 免安装步骤 —— 针对下载方式1(可忽略)

ZIP 文件下载下来后,找个路径解压一下,我这里放到D盘:
在这里插入图片描述

2.3.1 配置环境变量 Path

D:\mysql-5.6.49-winx64\bin

2.3.2 新增data目录 + my.ini配置文件

在这里插入图片描述
修改配置文件内容为:

# 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 = "D:\mysql-5.6.49-winx64"

datadir = "D:\mysql-5.6.49-winx64\data"

port = 3307

character-set-server=utf8
# server_id = .....


# 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 

2.3.3 执行初始化命令

mysqld --initialize --user=mysql --console

在这里插入图片描述

2.3.4 进行MySQL服务安装

mysqld –install mysql

在这里插入图片描述

2.3.5 启动服务

net start mysql

在这里插入图片描述
其他操作流程 可以参考下面的。

3、启动、停止MySql

3.1 方法1 —— 任务管理器(服务)

在这里插入图片描述
去到Windows任务管理器看看有没有对应服务名字,比如我配置的时候命名为MySql2020
我当前状态是正在运行,那么停止它可以通过右键 “停止服务”或者 “启动服务

3.2 方法2 —— cmd命令

输入cmd命令,然后以管理员身份运行:
在这里插入图片描述
然后分别测试输入以下两条命令:

  • 启动命令 net start mysql服务名
    比如我这里 是 net start mysql2020

  • 停止命令 net stop mysql服务名
    比如我这里 是 net stop mysql2020

在这里插入图片描述

4、访问MySql

因为Mysql基于c/s架构,我们需要通过client端去访问服务。

4.1 访问方式1 —— MySqL自带的命令行工具

在这里插入图片描述
缺点:

  • 只支持Root用户

在这里插入图片描述
输入:exit或者 ctrl + c
表示退出访问。

4.2 访问方式2 —— Window Cmd命令

在这里插入图片描述
命令:

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

  • mysql 表示命令
  • -h localhost 表示主机,目前我们是本地
  • -P 3306 服务器端口
  • -u root 账号
  • -p 密码

输入:exit或者 ctrl + c
表示退出访问。

4.3 访问方式3 —— 利用第三方客户端(推荐,也是目前开发人员习惯方式)

比如博主这里利用 Navicat Premium 软件:
在这里插入图片描述
创建连接:
在这里插入图片描述
在这里插入图片描述

在这里插入图片描述
到这里也是可以访问成功的。

断开连接
表示退出访问。

5、卸载MySQL(理解一下完全卸载)

如果想卸载MySQL怎么办:

去到控制面板,点击卸载
在这里插入图片描述

然后删除安装目录:
在这里插入图片描述

再删除 C:\ProgramData\MySQL 目录
在这里插入图片描述

6、总结

作为MySql的开篇,主要讲解工具的安装以及简单使用,后面会陆续更新新的知识点,敬请关注。觉得可以,请点赞,谢谢支持。

单片机菜鸟哥 CSDN认证博客专家 Java ESP8266
1、多年Android App开发经验;
2、小程序流行期加入了小程序开发大军;
3、业余时间爱捣鼓自己的业余爱好,对ESP8266物联网开发有自己的见解;
4、喜欢研究代码规范,喜欢了解新知识,致力于不甘做个平凡程序员,知其原理,懂其核心。
©️2020 CSDN 皮肤主题: 猿与汪的秘密 设计师:上身试试 返回首页
实付 9.90元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值