MySQL数据库

目录

一、数据库的基本概念

1、数据 (Data)

2、表

3、数据库

4、数据库管理系统(DBMS）

5、数据库系统(DBS)

二、数据库系统发展史

1、第一代数据库

2、第二代数据库

三、当今主流数据库介绍

1、SQL Server(微软公司产品）

2、Oracle (甲骨文公司产品)

3、DB2(IBM公司产品)

4、MySQL (甲骨文公司收购)

四、关系型数据库和非关系型数据库

五、MySQL数据库编译安装

六、MySQL数据库管理

1、常用的数据类型：

2、查看数据库结构

3、SQL语句 DDL  DML  DQL  DCL

DDL：用于管理数据库对象（库、表、索引等）

DML：用于管理表数据

DQL：用于根据条件查询表数据

DCL：用于管理用户和权限

---

一、数据库的基本概念

1、数据 (Data)

。描述事物的符号记录

。包括数字，文字、图形、图像、声音、档案记录等

。以“记录”形式按统一的格式进行存储

2、表

。将不同的记录组织在一起

。用来存储具体数据

3、数据库

。表的集合，是存储数据的仓库

。以一定的组织方式存储的相互有关的数据集合

。是按照数据结构来组织、存储和管理数据的仓库

4、数据库管理系统(DBMS）

。是管理数据库的系统软件，其包括数据库及用于访问、管理数据库的接系统

。主要功能是维护数据库，并方便、有效地访问数据库中各个部分的数据

5、数据库系统(DBS)

。是一个人机系统，由硬件、OS、数据库、DBMS、应用软件和数据库用户组成

。用户可以通过DBMS或应用程序操作数据库

二、数据库系统发展史

1、第一代数据库

。自20世纪60年代起，第一代数据库系统问世

。是层次模型与网状模型的数据库系统

。为统一管理和共享数据提供了有力的支撑

2、第二代数据库

。20世纪70年代初，第二代数据库-关系数据库开始出现

。20世纪80年代初，IBM公司的关系数据库系统DB2问世，开始逐取代层次与网状模型的数据库，成为行业主流

。到目前为止，关系数据库系统仍占领数据库应用的主要地位

三、当今主流数据库介绍

1、SQL Server(微软公司产品）

。面向Windows操作系统

。简单、易用

2、Oracle (甲骨文公司产品)

。面向所有主流平台

。安全、完善，操作复杂

3、DB2(IBM公司产品)

。面向所有主流平台

。大型、安全、完善

4、MySQL (甲骨文公司收购)

。免费、开源、体积小

四、关系型数据库和非关系型数据库

1、描述主流的数据库系统 关系型数据库： Mysql（Oracle公司） 、 SQL server（微软) 、access(微软公司office产品） Oracle 、DB2（IBM公司），sybase（sybase） 等等

2、关系型数据库和非关系型数据库的区别 关系数据库： 关系数据库结构是二维数据库表，二维表当中每个字段（列）用来描述对象的一个属性， 每个记录（行）用来描述一个对象的信息（完整信息），关系数据库写到哪里也就是存储在硬盘当中 读写系统就会受到的IO限制或者瓶颈

其他 关系型数据库最典型的数据结构是表，由二维表及其之间的联系所组成的一个数据组织

优点：

1、易于维护：都是使用表结构，格式一致；

2、使用方便：SQL语言通用，可用于复杂查询；

3、复杂操作：支持SQL，可用于一个表以及多个表之间非常复杂的查询。

缺点：

1、读写性能比较差，尤其是海量数据的高效率读写；

2、固定的表结构，灵活度稍欠；

3、高并发读写需求，传统关系型数据库来说，硬盘I/O是一个很大的瓶颈。

缓存加速软件 非关系型数据库（NoSQL）： MongoDB 、 Redis（内存数据库/缓存数据库）K-V键值对、与之类似的Memcache,K-V键值对 redis-memecache对比：

相同点：存储高热数据(在内存中高速运行）

不同点：redis可以做持久化保存，可以存储对象

非关系型数据库严格上不是一种数据库，应该是一种数据结构化存储方法的集合，可以是文档或者键值对等。

优点：

1、格式灵活：存储数据的格式可以是key,value形式、文档形式、图片形式等等，文档形式、 图片形式等等，使用灵活，应用场景广泛，而关系型数据库则只支持基础类型。

2、速度快：nosql可以使用硬盘或者随机存储器作为载体，而关系型数据库只能使用硬盘；

3、高扩展性与高可用性；

4、成本低：nosql数据库部署简单，基本都是开源软件。

缺点：

1、不提供sql支持，学习和使用成本较高；

2、无事务处理；

3、数据结构相对复杂，复杂查询方面稍欠。
 

五、MySQL数据库编译安装

1.将安装mysql 所需软件包传到/opt目录下
mysql-5.7.20.tar.gz
boost_1_59_0.tar.gz        #支持c++的运行库


2.安装环境依赖包
yum -y install \
gcc \
gcc-c++ \
ncurses \                #字符终端下图形互动功能的动态库
ncurses-devel \            #ncurses开发包
bison \                    #语法分析器
cmake                    #mysql需要用cmake编译安装
---------------------------------------------------------
yum -y install gcc gcc-c++ ncurses ncurses-devel bison cmake openssl-devel 
-----------------------------------------------------------


3.配置软件模块
cd /opt

tar xf mysql-boost-5.7.20.tar.gz #解压
tar xf boost_1_59_0.tar.gz #解压

mv boost_1_59_0 /usr/local/boost #重命名


cd /opt/mysql-5.7.20/

cmake \
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql \        #指定mysql的安装路径
-DMYSQL_UNIX_ADDR=/usr/local/mysql/mysql.sock \ #指定mysql进程监听套接字文件（数据库连接文件）的存储路径
-DSYSCONFDIR=/etc \                             #指定配置文件的存储路径
-DSYSTEMD_PID_DIR=/usr/local/mysql \            #指定进程文件的存储路径
-DDEFAULT_CHARSET=utf8  \                       #指定默认使用的字符集编码，如 utf8
-DDEFAULT_COLLATION=utf8_general_ci \            #指定默认使用的字符集校对规则
-DWITH_EXTRA_CHARSETS=all \                        #指定支持其他字符集编码
-DWITH_INNOBASE_STORAGE_ENGINE=1 \              #安装INNOBASE存储引擎
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \               #安装ARCHIVE存储引擎 
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \             #安装BLACKHOLE存储引擎 
-DWITH_PERFSCHEMA_STORAGE_ENGINE=1 \            #安装FEDERATED存储引擎 
-DMYSQL_DATADIR=/usr/local/mysql/data \         #指定数据库文件的存储路径
-DWITH_BOOST=/usr/local/boost \            #指定boost的路径，若使用mysql-boost集成包安装则-DWITH_BOOST=boost
-DWITH_SYSTEMD=1                                #生成便于systemctl管理的文件

--------------------------------------------------------
cmake \
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
-DMYSQL_UNIX_ADDR=/usr/local/mysql/mysql.sock \
-DSYSCONFDIR=/etc \
-DSYSTEMD_PID_DIR=/usr/local/mysql \
-DDEFAULT_CHARSET=utf8  \
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_EXTRA_CHARSETS=all \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DMYSQL_DATADIR=/usr/local/mysql/data \
-DWITH_BOOST=boost \
-DWITH_SYSTEMD=1

注意：如果在CMAKE的过程中有报错，当报错解决后，需要把源码目录中的CMakeCache.txt文件删除，然后再重新CMAKE，否则错误依旧
-----------------------------------------------------------

4.编译及安装（需要很久时间，建议多核编译 make -j2 #两核编译）
make -j2 && make install

5.创建mysql用户
useradd -M -s /sbin/nologin  mysql

6.修改mysql 配置文件
vim /etc/my.cnf              #删除原配置项，再重新添加下面内容

[client]                #客户端设置
port = 3306
socket = /usr/local/mysql/mysql.sock            

[mysql]                    #服务端设置
port = 3306
socket = /usr/local/mysql/mysql.sock
auto-rehash                                  #开启自动补全功能

[mysqld]                                #服务全局设置
user = mysql                               #设置管理用户
basedir=/usr/local/mysql                #指定数据库的安装目录
datadir=/usr/local/mysql/data            #指定数据库文件的存储路径
port = 3306                                #指定端口
character-set-server=utf8                #设置服务器字符集编码格式为utf8
pid-file = /usr/local/mysql/mysqld.pid    #指定pid 进程文件路径
socket=/usr/local/mysql/mysql.sock        #指定数据库连接文件
bind-address = 0.0.0.0                    #设置监听地址，0.0.0.0代表允许所有，如允许多个IP需空格隔开
skip-name-resolve                        #禁止域名解析，包括主机名，所以授权的时候要使用 IP 地址
max_connections=4096                     #设置mysql的最大连接数
default-storage-engine=INNODB            #指定默认存储引擎
max_allowed_packet=32M                    #设置在网络传输中一次消息传输量的最大值。系统默认值为 1MB，最大值是 1GB，必须设置 1024 的倍数。
server-id = 1                            #指定服务ID号

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,PIPES_AS_CONCAT,ANSI_QUOTES
--------------------------------------------------------
[client]
port = 3306
socket=/usr/local/mysql/mysql.sock

[mysql]
port = 3306
socket = /usr/local/mysql/mysql.sock
auto-rehash

[mysqld]
user = mysql
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
port = 3306
character-set-server=utf8
pid-file = /usr/local/mysql/mysqld.pid
socket=/usr/local/mysql/mysql.sock
bind-address = 0.0.0.0
skip-name-resolve
max_connections=4096
default-storage-engine=INNODB
max_allowed_packet=32M
server-id = 1

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,PIPES_AS_CONCAT,ANSI_QUOTES
-----------------------------------------------------------


7.更改mysql安装目录和配置文件的属主属组
chown -R mysql:mysql /usr/local/mysql/
chown mysql:mysql /etc/my.cnf


8.设置路径环境变量
echo 'export PATH=/usr/local/mysql/bin:/usr/local/mysql/lib:$PATH' >> /etc/profile    

source /etc/profile

9.初始化数据库
cd /usr/local/mysql/bin/

./mysqld \
--initialize-insecure \                #生成初始化密码为空
--user=mysql \                      #指定管理用户
--basedir=/usr/local/mysql \        #指定数据库的安装目录
--datadir=/usr/local/mysql/data        #指定数据库文件的存储路径
--------------------------------------------------------
./mysqld \
--initialize-insecure \
--user=mysql \
--basedir=/usr/local/mysql \
--datadir=/usr/local/mysql/data
---------------------------------------------------------


10.添加mysqld系统服务
cp /usr/local/mysql/usr/lib/systemd/system/mysqld.service /usr/lib/systemd/system/ #用于systemctl服务管理
systemctl daemon-reload         #刷新识别     
systemctl start mysqld.service  #开启服务
systemctl enable mysqld         #开机自启动
netstat -anpt | grep 3306       #查看端口

11.修改mysql 的登录密码
mysqladmin -u root password "abc123"     #给root账号设置密码为abc123，原始密码为空

12.授权远程登录
mysql -u root -p
grant all privileges on *.* to 'root'@'%' identified by 'abc123';
#授予root用户可以在所有终端远程登录，使用的密码是abc123，并对所有数据库和所有表有操作权限

（mysql -u root -pabc123  #可直接免交互登录）

show databases;            #查看当前已有的数据库

quit  #退出

六、MySQL数据库管理

1、常用的数据类型：

。int ：整型   无符号[0,2^32-1]，有符号[-2^31,2^31-1]

。float ：单精度浮点    4字节32位

。double ：双精度浮点    8字节64位

。char ：固定长度的字符类型

。varchar ：可变长度的字符类型

。text ：文本

。image ：图片

。decimal(5,2) ：5个有效长度数字，小数点后面有2位

2、查看数据库结构

1．查看当前服务器中的数据库
show databases;        #大小写不区分，分号“;”表示结束

2．查看数据库中包含的表
use 数据库名;
show tables;

3．查看表的结构（字段）
use 数据库名;
describe [数据库名.]表名;
可缩写成：desc 表名;

3、SQL语句 DDL  DML  DQL  DCL

DDL：用于管理数据库对象（库、表、索引等）

create database 库名;

use 库名;   #选择/使用数据库

create table 表名 (字段1 数据类型, 字段2 数据类型, ....[, primary key(字段)]);   #创建数据表

create table user(id int(4),username varcher(20),password varchar(10));  #创建数据表user

drop table [库名.]表名;   #删除数据表

drop database 库名;    #删除数据库

MySQL的六大约束特性：

primary key     主键约束     字段的值不能重复，不能为Null，一个表只能有一个主键

unique key      唯一性约束   字段的值不能重复，但能为Null，一个表可以有多个唯一键

not null        非空约束     字段的值不能为Null

default         默认值约束   字段的值如果没有设置则使用默认值自动填充

auto_increment  自增约束     字段的如果没有设置默认会从 1 开始每次自动递增 1，要求自增字段必须为主键

foreign key     外键约束     保证外键表与主键表相关联的表数据的完整性和一致性

int(N) zerofill   零填充

修改表结构

1、改表名： alter table 旧表名 rename 新表名

2、增加字段 ：alter table 表名 add 新字段 数据类型 [约束属性];

3、修改字段：alter table 表名 change 旧字段 新字段 数据类型 [约束属性];

4、删除字段：alter table 表名 drop 字段名;

克隆表

create table 新表 like 旧表;                 #克隆表结构

insert into 新表 (select * from 旧表);       #克隆表数据、可以实现表结构和数据与旧表都一致

create table 新表 (select * from 旧表);      #表数据和旧表一致，表结构和旧表不一定一致

清空表

delete from 表名;       #一条一条的删除记录，效率较慢；自增长字段仍然会按照清空表前的最大记录继续自增

truncate table 表名;    #直接重建表，清空表效率更快；自增长字段会重新从 1 开始自增 

临时表

create temporary table (....);      

临时表跟普通表一样可以进行增删改查，但是show tables是查不到的；
                                                   

临时表只能在当前会话中有效，退出当前会话或在其它会话中，临时表都会失效

DML：用于管理表数据

insert into 表名 (字段1, 字段2, ....) values (字段1的值, 字段2的值, ....);  #选择性给字段赋予值

insert into 表名 values (所有字段的值);   #给所有字段全部赋值

update 表名 set 字段1=值[, 字段2=值, ....]  where 条件表达式;

delete from 表名 where 条件表达式;

DQL：用于根据条件查询表数据

select * from 表名 [where 条件表达式];   #查看数据表

select  字段1,字段2,....  from 表名 [where 条件表达式];

select * from 表名\G      #纵向查看每行记录的字段的值

select * from 表名 limit N;      #查看表的前N行的记录

select * from 表名 limit N,M;    #查看表的前N行之后的连续M行的记录（不包含第N行）

DCL：用于管理用户和权限

用户管理:

1、增删改查
create user '用户名' @'源地址' identified by '密码';   
#创建用户，源地址可以是localhost/IP/网段/主机名/%

rename user '用户名' @'源地址'  to '新用户名' @'源地址'; 
#重命名用户

drop user '用户名'@'源地址';  
#删除用户

select user,host,authentication_string from mysql.user;  
#查看有哪些用户信息

2、密码修改方式

update mysql.user set authentication_string=password('123456') where user='liqi';
#初始状态，只有本地的root用户有权限

set password for'用户名' @'源地址' = password('密码');
#修改用户密码

alter user '用户名'@'源地址' identified by '密码';
#修改用户密码

3、权限管理
grant 权限1,权限2 on 库名.表名 to '用户名'@'源地址';  
#授予权限，5.7或之前版本支持创建新用户和授予权限，8.0版本只能用于授予权限

show grants for '用户名'@'源地址';
#查询权限

revoke 权限1,权限2 on 库名.表名 from '用户名'@'源地址';
#删除权限

如何找回 root 密码？（未验证）
1）修改MySQL配置文件，在 [mysqld] 配置项下面添加 skip-grant-tables 配置参数
2）重启mysqld服务，使用 mysql 密码直接登录数据库
3）执行命令 update mysql.user set authentication_string=password('密码') where user='root'; 修改 root 用户密码
4）还原MySQL配置，重启mysqld服务，使用 mysql -u用户名 -p密码 [-h mysql地址 -P mysql端口] 命令登录数据库验证