数据库、MySQL的简介和使用_mysql安装 open windows firewall取消

---

一、数据库

数据库是一个以某种有组织的方式存储的数据集合。
数据库软件：DBMS(数据库管理系统)

1.1 数据库的分类

数据库模型有：

• 关系型数据库：Oracle、MySQL
• NOSQL数据库(非关系型数据库)：Redis

1.2 常见的关系型数据库

关系型数据库国内常用的有：

1. Oracle
2. MySQL
3. MSSQLServer
4. PostgreSQL
5. Db2

1.3 SQL

SQL （发音为字母S-Q-L或sequel） 是结构化查询语言 （Structured Query Language）的缩写。SQL是一种专门用来与数据库通信的语言。

1.4 关系型数据库

1.4.1 库

数据库是存储数据的仓库。

1.4.2 表

在数据库中这种文件称为表，用于存储各种数据。

1.4.3 行与列

横着的是行，在一行中所有竖着的就是列。
水平为行；垂直为列
列： 存储数据不同的属性
行： 根据列对对应列书写的一行数据

注意：

• 每一行都是由数量不等的列组成的。
• 一列称为一个「字段」
• 一行称为一条「数据」

1.4.4 数据类型

数据类型就是在列中允许写的数据种类。

作用：

1. 限制输入的内容，防止数据格式输入错误
2. 还帮助正确地排序数据，并在优化磁盘使用方面起重要的作用。

1.4.5 主键

• 表中每一行都应该有可以唯一标识自己的一列（或一组列） 。
• 将主键作为一张表中所有行数据的唯一标识符。

二、MySQL简介

MySQL 是数据库软件（DBMS）的一种，属于关系型数据库。

为什么选择MySQL？

• 从公司运营角度来说，选择MySQL有多种原因，主要就是Oracle太贵，MySQL一开始并不是功能强大的数据库，但随着阿里巴巴将数据从Oracle数据库迁移到MySQL后，国内的社区和互联网公司就迅速开始对MySQL的应用和技术升级。到现在为止，国内公司使用MySQL主要从数据安全，稳定，技术服务，以及非常重要的价格上综合考虑成本问题，最终大多选择了MySQL。

• 从技术角度来看，MySQL 由于开源，可以带来两大优势：

  • 可以更加了解软件运作的原理，更好的设置MySQL。一旦出了故障也可以准确定位。
  • 可以更容易开发周边产品。

• 所以MySQL对于初创公司、互联网公司和很多传统行业不管从功能还是性能，又或者从成本角度考虑都是一个很好的选择。

2.1 数据库引擎

数据库引擎是用于存储、处理和保护数据的核心服务 。MySQL的数据库引擎非常多，使用 SHOW ENGINES 就可以查看当前的数据库引擎信息。

SHOW ENGINES; --查看MySQL的数据库引擎
1

InnoDB与MyISAM数据库引擎的区别

• 存储结构
  • MyISAM：每个MyISAM在磁盘上存储成三个文件。第一个文件的名字以表的名字开始，扩展名指出文件类型。.frm文件存储表定义。数据文件的扩展名为.MYD (MYData)。索引文件的扩展名是.MYI (MYIndex)。
  • InnoDB：所有的表都保存在同一个数据文件中（也可能是多个文件，或者是独立的表空间文件），InnoDB表的大小只受限于操作系统文件的大小，一般为2GB。
• 存储空间
  • MyISAM：可被压缩，存储空间较小。支持三种不同的存储格式：静态表(默认，但是注意数据末尾不能有空格，会被去掉)、动态表、压缩表。
  • InnoDB：需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。
• 可移植性、备份及恢复
  • MyISAM：数据是以文件的形式存储，所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作。
  • InnoDB：免费的方案可以是拷贝数据文件、备份 binlog，或者用 mysqldump，在数据量达到几十G的时候就相对痛苦了。
• 事务支持
  • MyISAM：强调的是性能，每次查询具有原子性,其执行数度比InnoDB类型更快，但是不提供事务支持。
  • InnoDB：提供事务支持事务，外部键等高级数据库功能。 具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。
• AUTO_INCREMENT
  • MyISAM：可以和其他字段一起建立联合索引。引擎的自动增长列必须是索引，如果是组合索引，自动增长可以不是第一列，他可以根据前面几列进行排序后递增。
  • InnoDB：InnoDB中必须包含只有该字段的索引。引擎的自动增长列必须是索引，如果是组合索引也必须是组合索引的第一列。
• 表锁差异
  • MyISAM：只支持表级锁，用户在操作myisam表时，select，update，delete，insert语句都会给表自动加锁，如果加锁以后的表满足insert并发的情况下，可以在表的尾部插入新的数据。
  • InnoDB：支持事务和行级锁，是innodb的最大特色。行锁大幅度提高了多用户并发操作的新能。但是InnoDB的行锁，只是在WHERE的主键是有效的，非主键的WHERE都会锁全表的。
• 全文索引
  • MyISAM：支持 FULLTEXT类型的全文索引
  • InnoDB：不支持FULLTEXT类型的全文索引，但是innodb可以使用sphinx插件支持全文索引，并且效果更好。
• 表主键
  • MyISAM：允许没有任何索引和主键的表存在，索引都是保存行的地址。
  • InnoDB：如果没有设定主键或者非空唯一索引，就会自动生成一个6字节的主键(用户不可见)，数据是主索引的一部分，附加索引保存的是主索引的值。
• 表的具体行数
  • MyISAM：保存有表的总行数，如果select count(*) from table;会直接取出出该值。
  • InnoDB：没有保存表的总行数，如果使用select count(*) from table；就会遍历整个表，消耗相当大，但是在加了wehre条件后，myisam和innodb处理的方式都一样。
• CURD操作
  • MyISAM：如果执行大量的SELECT，MyISAM是更好的选择。
  • InnoDB：如果你的数据执行大量的INSERT或UPDATE，出于性能方面的考虑，应该使用InnoDB表。DELETE 从性能上InnoDB更优，但DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除，在innodb上如果要清空保存有大量数据的表，最好使用truncate table这个命令。
• 外键
  • MyISAM：不支持
  • InnoDB：支持

2.2 MySQL数据库的安装

2.2.1 MySQL版本说明

MySQL 版本分为四种：Alpha版、Beta版、RC版(Release Candidate)、GA版(Generally Available)。

• 1. Alpha版
  Alpha版软件，这是软件工程对软件开发过程软件版本定义使用的版本说明。Alpha是内部测试版,一般不向外部发布,会有很多Bug.除非你也是测试人员,否则不建议使用.是希腊字母的第一位,表示最初级的版本，alpha 就是α。
• 2. Beta版
  Beta版软件，这也是软件工程中对软件开发测试版本控制的版本说明。Beta一般是Alpha后面的版本。该版本相对于α版已有了很大的改进，消除了严重的错误，但还是存在着一缺陷，需要经过多次测试来进一步消除。这个阶段的版本会一直加入新的功能。beta 就是β。
• 3. RC版
  RC版，RC即Release Candidate的简写。这是Beta后面的版本，一般RC版并没有新增功能，而是修复了一些反馈的Beta中存在的BUG。所以RC版更接近最终发行版即稳定版(GA版)
• 4. GA版
  GA版，GA即Generally Available的简写。这就是软件最终的发行版。这个版本一般BUG相对较少。这个发行版也可以叫稳定版。
• Release版
  Release版，在有些软件存在，在MySQL中一般没有这个版本。该版本意味“最终版本”，在前面版本的一系列测试版之后，终归会有一个正式版本，是最终交付用户使用的一个版本。该版本有时也称为标准版。一般情况下，Release不会以单词形式出现在软件封面上，取而代之的是符号®。

2.2.2 根据应用场景划分版本

1. MySQL Community Server

• MySQL Community Server是社区版本，开源免费，但不提供官方技术支持。
• 遵循GPL协议。MySQL Community Server也是我们通常用的MySQL的版本。根据不同的操作系统平台细分为多个版本。

2. MySQL Enterprise Edition

• MySQL Enterprise Edition企业版本，需付费，可以试用30天。

3. MySQL Cluster

• MySQL Cluster集群版，开源免费。
• 可将几个MySQL Server封装成一个Server。MySQL Cluster CGE 高级集群版，需付费。

4. MySQL Workbench（GUI TOOL）

• MySQL Workbench（GUI TOOL）一款专为MySQL设计的ER/数据库建模工具。它是著名的数据库设计工具DBDesigner4的继任者。
• MySQL Workbench又分为两个版本：
  ①、社区版（MySQL Workbench OSS）
  ②、商用版（MySQL Workbench SE）

2.2.3 下载MySQL

• 网盘：MySQL 5.7 提取码：1248
• 在 官网 下载界面中，遭到MySQL Community Server的下载选项

选择操作系统，然后选择最下面的下载版本。这里直接选择 Windows (x86, 64-bit), ZIP Archive 版本。

• 注意：下载版本尽量选择MySQL 5.5/5.7版本。无需注册，直接下载。

2.2.4 安装MySQL服务器

• 1、同意许可

同意许可，点击NEXT

• 2、选择安装内容

只安装MySQL服务器，然后点击NEXT

• 3、选择独立MySQL服务器

选择第一个独立的MySQL服务器，点击NEXT

• 4、配置

配置默认即可。

• 注意：Port端口号为3306，在以后访问 MySQL 服务器的时候是需要这个端口号，因此端口号绝对不要记错。
• Open Windows Firewall ports for network access默认是将网络访问许可添加到防火墙规则的，请不要取消。

• 5、输入管理员账户root的密码

这里的密码，在之后的数据库连接和JDBC的连接时都会用到。

• 6、安装 MySQL 服务并配置启动方式

默认的服务名是 MySQL57 ()， 随着系统开机自动启动。在这里可修改这2个选项。但是需要注意的是，服务名在启动服务的时候是需要的。

• 7、应用配置并启动服务

如果全部都是绿色对勾，就说明配置正常应用并且正常启动 MySQL 服务器了。(当绿色对勾到Starting the server时，即使后边报红,MySQL也算安装完成可以使用)

2.3 使用自带客户端连接服务器

MySQL 服务器自带了 CLI 客户端，其中一个使用 Unicode 编码进行连接，可以防止出现乱码

启动任意一个客户端，在提示下输入密码(该密码为管理员账户root的密码)

密码验证通过后，即可使用 MySQL

查看 MySQL 服务器的版本
在提示符下，输入 SQL:

select version(); --这个SQL是用于查看安装的MySQL服务器版本
1

2.3 配置环境变量

环境变量的设置是为了让我们在任意位置，通过 cmd 可以直接访问 mysql.exe程序。如果没有这个需求就不用配置了。

• 1、打开环境变量面板

此电脑 —> 属性 —> 高级系统配置 —> 环境变量

• 2、设置环境变量

选择下面系统变量中的 Path，双击或者点击编辑按钮
添加 MySQL 服务器的安装路径，然后在最后加上 bin 文件夹

• 3、安装测试

配置完成后，点击确定按钮。然后重新打开一个 cmd 程序，输入 mysql ，如果有提示就证明配置正确。

2.4 MySQL图形客户端

分类：

1. Navicat for MySQL（看名字只能连接MySQL）
2. Navicat premium（可以连接各种数据库）
3. DataGrip （功能强大，但是安装包体积也大，有300多M，需要JRE才能运行）
4. SQLyog
5. phpMyAdmin ( 网页版，需要有PHP环境 )
6. workbench
   …

这里选择的是 Navicat premium ，这是一款收费软件。默认可以全功能试用 30 天。

安装过程略… ，一路下一步即可

打开 Navicat，建立一个连接

从图上可以看到，这款程序支持很多数据库，这里我们选择 MySQL

1. 连接名：这个名字是让使用者知道是连接哪个数据库的
2. 主机：localhost 和 IP: 127.0.0.1 是等价的，这2个写法都是默认连接本机的服务器。如果要连接其他服务器，输入目标的 IP 地址即可
3. 端口：在安装的时候，默认是 3306 ，这里就不用改。如果配置的时候改变了端口号，这里就需要输入修改后的值
4. 用户名和密码：可以使用管理员账号root，或者是安装时创建的其他账号。

输入完成后，可以点击左侧连接测试按钮，查看连接是否成功。没有问题的话，点击确定，设置就会进行保存。

2.5 数据库编码问题

MySQL支持多种不同的编码，我们常用的是UTF8和UTF8MB4，如果是已经存在的数据库，根据数据库编码存储格式即可，如果是新创建的数据库，还是建议使用UTF8MB4。

UTF8支持长度3的汉字，但是不支持长度4的汉字，是不完全的UTF8解决方案。MySQL团队并没有对其进行修改，而是使用UTF8MB4支持了完整的UTF8。

2.6 MySQL常用的连接参数

常用的参数：
useUnicode=true
characterEncoding=utf8 / characterEncoding=utf8mb4 如果是新创建的数据库，请用 utf8mb4 编码
serverTimezone=Asia/Shanghai 开发的时候指定上海时区，连接MySQL8必填参数

2.7 MySQL配置文件

2.7.1 my.ini是什么

my.ini是MySQL数据库中使用的配置文件，修改这个文件可以达到更新配置的目的

2.7.2 my.ini存放路径

windows （win7 以后）

如果找不到，就打开隐藏的项目，可能ProgramData文件夹被隐藏了

Date文件夹中存放的是数据库存储的数据，一般由my.ini进行配置
或者在C:\ProgramData\MySQL\MySQL Server 5.7

内部会保存默认的表文件以及文件夹（每新建表都会创建新的对应文件夹）

每创建一个表都会创建对应的3个文件

• .frm文件存储表定义。
• 数据文件的扩展名为.MYD (MYData)。
• 索引文件的扩展名是.MYI (MYIndex)。

2.7.3 my.ini 文件配置说明

[mysqld]
; 设置3306端口
port=3306
; 设置mysql的安装目录
basedir="C:/Program Files/MySQL/MySQL Server 5.7/"
; 设置mysql数据库的数据的存放目录
datadir=C:/ProgramData/MySQL/MySQL Server 5.7/Data
; 允许最大连接数
max_connections=200
; 允许连接失败的次数。这是为了防止有人从该主机试图攻击数据库系统
max_connect_errors=10
; 服务端使用的字符集默认为UTF8
character-set-server=utf8
; 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
; 默认使用“mysql_native_password”插件认证
default_authentication_plugin=mysql_native_password
[mysql]
; 设置mysql客户端默认字符集
default-character-set=utf8
[client]
; 设置mysql客户端连接服务端时默认使用的端口
port=3306
default-character-set=utf8
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

三、MySQL的使用

3.1 连接MySQL

• MySQL服务启动

1.手动。
2.cmd–> services.msc 打开服务的窗口
3.使用管理员打开cmd

• net start mysql : 启动mysql的服务
• net stop mysql:关闭mysql服务

• MySQL登录

mysql -h连接Ip -u账号 -p密码
1

1. -uroot : 其中 root 是账号名
2. -p0000：后面的 4个0是登陆密码
3. -h 连接数据库的Ip（默认为localhost）

• MySQL退出

1.exit
2.quit
3.直接退出

• 注意

如果连接非本机ip数据库连接失败，可能是没有开启远程访问的关系

在本机连接成功后执行如下sql 开启远程连接

• 1.把root的host字段设置成 %，表示所有ip都可以连接
  update user set host='%' where user='root';
• 2.刷新权限
  flush privileges;

3.2 默认MySQL数据库与其使用

3.2.1 默认MySQL数据库说明

打开连接后，在图形客户端可以直接看到4个数据库，这是 MySQL 服务器安装后的默认数据库：

3.2.2 查看所有MySQL数据库

• 语法：

show databases；
1

3.2.3 使用MySQL数据库

由于拥有多个库，所以在使用时不知道对那个库进行使用，所以在执行使用的语句之前，先执行选择对应库的语句

use `数据库名`
1

3.2.3 查看所有表

# 查询某个数据库中所有的表名称
show tables;

# 查询表结构
desc 表名;
1
2
3
4
5

四、SQL语句的使用

关系型数据库操作数据需要使用 SQL 语言（结构化查询语言 Structured Query Language）。

4.1 SQL语句的分类

通过以上 4 类 SQL 语言进行数据库和数据的操作

4.2 SQL通用语法

1. SQL 语句可以单行或多行书写，以分号结尾。
2. 可使用空格和缩进来增强语句的可读性。
3. MySQL 数据库的 SQL 语句不区分大小写，关键字建议使用大写。
4. 3 种注释
   * 单行注释: – 注释内容 或 # 注释内容(mysql 特有)
   * 多行注释: /* 注释 */

4.3 DDL

• （Data Definition Language，DDL）数据库定义语言
• 用于对数据库、表创建、修改、删除操作

4.3.1 创建数据库

• 语法：

# 创建数据库：
create database 数据库名称;
# 创建数据库，判断不存在，再创建：
create database if not exists 数据库名称;
# 创建数据库，并指定字符集
create database 数据库名称 character set '编码';
1
2
3
4
5
6

CHARACTER SET ‘编码’;可以省略，会使用默认编码

4.3.2 删除数据库

• 语法：

# 删除数据库
drop database 数据库名;
# 判断数据库存在，存在再删除
drop database if exists 数据库名称;
1
2
3
4

4.3.3 修改数据库

• 语法

# 修改数据库的字符集
alter database 数据库名称 character set 字符集名称;
1
2

4.3.3 创建表

• 语法：

# 创建表
create table 表名(
	第1列名 数据类型(长度)  其他,
	第2列名 数据类型(长度)  其他,
	第n列名 数据类型(长度)  其他
);


# 复制表：
create table 表名 like 被复制的表名;
1
2
3
4
5
6
7
8
9
10

注意：最后一列，不需要加逗号（,）

4.3.4 删除表

• 语法：

drop table 表名;
drop table  if exists 表名 ;
1
2

4.3.5 修改表

• 语法

# 修改表名
alter table 表名 rename to 新的表名;
# 修改表的字符集
alter table 表名 character set 字符集名称;
# 添加一列
alter table 表名 add 列名 数据类型;
# 修改列名称 类型
alter table 表名 change 列名 新列别 新数据类型;
alter table 表名 modify 列名 新数据类型;
# 删除列
alter table 表名 drop 列名;
1
2
3
4
5
6
7
8
9
10
11

4.4 DML

• （Data Manipulation Language，DML)数据库操作语言
• 用于数据库中表数据的添加、修改、删除操作

4.4.1 添加数据

• 语法1：

为所有列进行数据赋值，也可以只为某些列进行赋值，其他没有设置的列设置为null

insert into 表名(列名1，列名2，列名n) values(列名1的值，列名2的值，列名n的值)
1

• 语法2：

如果在进行赋值时就是为所有的列赋值，那么乐意省略列名直接赋值

insert into 表名 values(列名1的值，列名2的值，列名n的值)
1

• 语法3：

如果一次想进行多条语句的赋值，那么可以在values后面继续书写

insert into 表名(列名1，列名2，列名n) values(列名1的值，列名2的值，列名n的值)，(列名1的值，列名2的值，列名n的值)，(列名1的值，列名2的值，列名n的值)；
1

4.4.2 修改数据

如果没有书写where 会将所有数据对应列都改为指定数据

• 语法：

update 表名 set 更新数据的列名 = 新值,更新数据的列名 = 新值... where 条件;
1

4.4.3 删除数据

• 语法:

delete from 表名 where 条件;
1

4.5 DCL

• （Data Control Language）数据库控制语言
• 用于数据库账号的创建以及相应权限的添加
  （在实际开发中一般不会书写这类语言）

• 管理用户

    1. 添加用户：
    	* 语法：CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
    2. 删除用户：
    	* 语法：DROP USER '用户名'@'主机名';
    3. 修改用户密码：
    	
    	UPDATE USER SET PASSWORD = PASSWORD('新密码') WHERE USER = '用户名';
    	UPDATE USER SET PASSWORD = PASSWORD('abc') WHERE USER = 'lisi';
    	
    	SET PASSWORD FOR '用户名'@'主机名' = PASSWORD('新密码');
    	SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123');
  
    	* mysql中忘记了root用户的密码？
    		1. cmd -- > net stop mysql 停止mysql服务
    			* 需要管理员运行该cmd
  
    		2. 使用无验证方式启动mysql服务： mysqld --skip-grant-tables
    		3. 打开新的cmd窗口,直接输入mysql命令，敲回车。就可以登录成功
    		4. use mysql;
    		5. update user set password = password('你的新密码') where user = 'root';
    		6. 关闭两个窗口
    		7. 打开任务管理器，手动结束mysqld.exe 的进程
    		8. 启动mysql服务
    		9. 使用新密码登录。
    4. 查询用户：
    	-- 1. 切换到mysql数据库
    	USE mysql;
    	-- 2. 查询user表
    	SELECT * FROM USER;
    	
    	* 通配符： % 表示可以在任意主机使用用户登录数据库
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15
  16
  17
  18
  19
  20
  21
  22
  23
  24
  25
  26
  27
  28
  29
  30
  31

• 权限管理

   1. 查询权限：
   	-- 查询权限
   	SHOW GRANTS FOR '用户名'@'主机名';
   	SHOW GRANTS FOR 'lisi'@'%';
   2. 授予权限：
   	-- 授予权限
   	grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';
   	-- 给张三用户授予所有权限，在任意数据库任意表上
   	
   	GRANT ALL ON *.* TO 'zhangsan'@'localhost';
   3. 撤销权限：
   	-- 撤销权限：
   	revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';
   	REVOKE UPDATE ON db3.`account` FROM 'lisi'@'%';
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14

4.6 TCL

• （Transactional Control Language）事务控制语言

• TCL 用于维护数据的一致性，共有3条语句：

  • 1.COMMIT（提交事务）
  • 2.ROLLBACK（回滚事务）
  • 3.SAVEPOINT（设置保存点）

• 在开发中一般会通过书写java代码的形式进行事务的操作，一般不会通过sql语句进行事务的操作

		CREATE TABLE account (
			id INT PRIMARY KEY AUTO_INCREMENT,
			NAME VARCHAR(10),
			balance DOUBLE
		);
		-- 添加数据
		INSERT INTO account (NAME, balance) VALUES ('zhangsan', 1000), ('lisi', 1000);
		SELECT * FROM account;
		UPDATE account SET balance = 1000;
		-- 张三给李四转账 500 元
		-- 0. 开启事务
		START TRANSACTION;
		-- 1. 张三账户 -500
		
		UPDATE account SET balance = balance - 500 WHERE NAME = 'zhangsan';
		-- 2. 李四账户 +500
		-- 出错了...
		UPDATE account SET balance = balance + 500 WHERE NAME = 'lisi';
		
		-- 发现执行没有问题，提交事务
		COMMIT;
		
		-- 发现出问题了，回滚事务
		ROLLBACK;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

4.7 DQL

• （Data query Language）数据查询语言
• 是sql中最常用的语法，用于数据库的数据查询，可以根据查询语法的不同返回相应的不同数据

• 基本查询语法

select 字段列表 from 表名列表 where 条件列表 group by 分组字段 having 分组之后的条件 order by 排序 limit 分页限定
1

---

每日一点点进步
不进则退