MySQL数据库详解 一：安装MySQL数据库及基本管理

1. 数据库的基本概念

1.1 数据库的组成

1.1.1 数据

• 描述事物的符号记录
• 包括数字、文字、图形、图像、声音、档案记录等
• 以”记录“形式按统一的格式进行存储

1.1.2 表

• 将不同的记录组织在一起
• 依赖存储具体数据

1.1.3 数据库

• 表的集合，是存储数据的仓库
• 以一定的组织方式存储的相互有关的数据集合
• 是按照数据结构来组织、存储和管理数据的仓库

1.2 当前主流数据库及其特点

• SQL Server (微软公司产品)
  面向Windows操作系统
  简单、易用

• Oracle (甲骨文公司产品)
  面向所有主流平台
  安全、完善、操作复杂

• DB2 （IBM公司产品）
  面向所有主流平台
  大型、安全、完善

• MySQL （甲骨文公司收购）
  免费、开源、体积小

1.3 数据库类型

1.3.1 关系数据库

关系数据库系统是基于关系模型的数据库系统

1.3.1.1 关系数据库的组成

• 关系型数据库：SQL
• 操作语言：SQL语句
• 存储的数据结构：二维表格
• 存储的数据：结构化数据
• 行字段：用来描述对象的一个属性
• 列字段：用来描述一个对象的信息

库 -> 表 -> 二维表格形式的结构化数据

1.3.1.2 非关系数据库

• 非关系型数据库：NoSQL
• 存储数据不以关系模型为依据，不需要固定的表格式
• 存储结构：键值对 文档、索引、时间序列

缓存型 Redis Memcached
文档型 MongoDB
索引型 ElasticSearch
时序型 Prometheus InfluxDB

2. 安装MySQL

2.1 yum安装

#创建mysql源
cd /etc/yum.repos.d/
vim mysql.repo
[mysql]
name=mysql5.7
baseurl=https://mirrors.tuna.tsinghua.edu.cn/mysql/yum/mysql-5.7-community-el7-x86_64/
gpgcheck=0
 
#安装服务mysql-community-server
yum install mysql-community-server -y 
 
#启动服务
systemctl start mysqld
ss -ntap |grep 3306
 
#登入数据库，但是密码是默认的，需要查看
mysql -u root -p

grep password /var/log/mysqld.log
mysql -u root -p'默认的密码'
#特殊符号用单引号引起来
 
#登入数据库后必须修改密码
alter user root@'localhost' identified by 'Zhuo@123';
#alter user root@'主机名' identified by '新密码';
 
#可以使用mysql> \h查看帮助
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

2.2 编译安装MySQL

2.2.1 前置准备

systemctl stop firewalld
setenforce 0
#关闭防火墙和selinux服务

yum -y install gcc gcc-c++ ncurses ncurses-devel bison cmake
#安装Mysql环境依赖包
1
2
3
4
5
6

useradd -M -s /sbin/nologin  mysql
#创建运行用户
1
2

2.2.2 编译安装

cd /opt

tar zxvf mysql-boost-5.7.20.tar.gz
#解压缩源码包
1
2
3
4

cd /opt/mysql-5.7.20/

#搭建编译环境
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 \
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_PERFSCHEMA_STORAGE_ENGINE=1 \
-DMYSQL_DATADIR=/usr/local/mysql/data \
-DWITH_BOOST=boost \
-DWITH_SYSTEMD=1
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

make -j2 && make install
#编译并安装
1
2

2.2.3 修改mysql配置文件

vim /etc/my.cnf

#客户端设置
[client]
port = 3306
default-character-set=utf8
socket = /usr/local/mysql/mysql.sock

#服务端配置
[mysql]
port = 3306
default-character-set=utf8
socket = /usr/local/mysql/mysql.sock

#服务全局配置
[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         #指定数据库连接文件
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
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

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

2.2.4 设置路径环境变量

echo 'PATH=/usr/local/mysql/bin:/usr/local/mysql/lib:$PATH' >> /etc/profile

echo 'export PATH' >> /etc/profile

source /etc/profile  #刷新使其生效
1
2
3
4
5

2.2.5 初始化数据库

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
1
2
3
4
5
6
7
8
9
10
11
12
13
14

2.2.6 添加mysql系统服务

cp /usr/local/mysql/usr/lib/systemd/system/mysqld.service /usr/lib/systemd/system/
#使其能使用systemd服务
1
2

systemctl daemon-reload          刷新识别    
systemctl start mysqld.service   开启服务
systemctl enable mysqld          开机自启动
ss -anpt | grep 3306 |tr -s ' '       查看端口
1
2
3
4

2.2.7 修改MySql登录密码

mysqladmin -u root -p password "abc123" 
#刚开始没密码是空的直接回车
1
2

mysql -u root -p     
#这个命令敲下，提示要输入密码,这个就是刚才设置的密码abc123
1
2

3. MySQL数据库的基本操作

3.1 SQL主要类别

数据库：database

表：table，行：row 列：column

索引：index

视图：view

存储过程：procedure

存储函数：function

触发器：trigger

事件调度器：event scheduler，任务计划

用户：user

权限：privilege

3.2 语言规范

在数据库系统中，SQL 语句不区分大小写，建议用大写

SQL语句可单行或多行书写，默认以 " ; " 结尾

关键词不能跨多行或简写

用空格和TAB 缩进来提高语句的可读性

子句通常位于独立行，便于编辑，提高可读性

数据库对象和命名

3.3 SQL语句分类

SQL语句用于维护管理数据库，包括数据查询、数据更新、访问控制、对象管理等功能。

#help后面跟上具体命令可以查看帮助
例如：mysql> help create
1
2

#查看支持字符集
show charset;
默认拉丁文字，需要修改为
 
utf8     | UTF-8 Unicode   
#阉割版的
 
utf8mb4  | UTF-8 Unicode 
#真实的版本
 
#修改字符集
vim /etc/my.cnf
[mysqld]
character-set-server=utf8mb4
1
2
3
4
5
6
7
8
9
10
11
12
13
14

4. Mysql数据库的管理

4.1 查看数据库

4.1.1 查看当前服务器中已有的数据库

show databases;					#大小写不区分，分号“;”表示结束
1

4.1.2 查看数据库中包含的表

#方式一：在库中查看表
use scj;             #use 数据库名
#前往scj数据库
show tables;           #查看表
 
#方式二 ：在库外查看表  
show tables from scj;    #show tables from 数据库名
#查看scj数据库下所有已创建表
1
2
3
4
5
6
7
8

4.1.3 查看表的结构（字段）

#表内查看
use 数据库名;

describe [数据库名.]表名;
可缩写成：desc 表名;

#表外查看
desc 数据库名.表名;
1
2
3
4
5
6
7
8

4.1.4 查看当前mysql版本

mysql -V
1

5. 数据类型

5.1 常用数据类型

char和varchar的区别

char的长度是不可变。char如果存入数据的实际长度比指定长度要小 会补空格至指定长度 如果存入的数据的实际长度大于指定长度，低版本会被截取高版本会报错。

varchar长度是可变的，默认会加一个隐藏的结束符，因此结束符会多算一个字节。

https://dev.mysql.com/doc/refman/8.0/en/data-types.html
#官方文档
1
2

选择正确的数据类型的三大原则：

• 更小的通常更好，尽量使用可正确存储数据的最小数据类型

• 简单就好，简单数据类型的操作通常需要更少的CPU周期

• 尽量避免NULL，包含为NULL的列，对MySQL更难优化

5.2 字符串类型

char(n) 固定长度，最多255个字符,注意不是字节

varchar(n) 可变长度，最多65535个字符

tinytext 可变长度，最多255个字符

text 可变长度，最多65535个字符

mediumtext 可变长度，最多2的24次方-1个字符

longtext 可变长度，最多2的32次方-1个字符

BINARY(M) 固定长度，可存二进制或字符，长度为0-M字节

VARBINARY(M) 可变长度，可存二进制或字符，允许长度为0-M字节

内建类型：ENUM枚举, SET集合

5.3 整数类型

tinyint(m) 1个字节 范围(-128~127)

smallint(m) 2个字节 范围(-32768~32767)

mediumint(m) 3个字节 范围(-8388608~8388607)

int(m) 4个字节 范围(-2147483648~2147483647)

bigint(m) 8个字节 范围(±9.22*10的18次方)

上述数据类型，如果加修饰符unsigned后，则最大值翻倍

例如：tinyint unsigned的取值范围为(0~255)

6. 数据库和表的添加和删除

6.1 创建数据库

#建立数据库 
create database 数据库名;
1
2

6.2 删除指定数据库

drop database 数据库名;
1

6.3 创建表

use 数据库名;
create table 表名 (字段1 数据类型,字段2 数据类型[,...][,primary key (主键名)]);
#主键一般选择能代表唯一性的字段不允许取空值（NULL），一个表只能有一个主键。

例如：
use scj;
create table students (id int, name char(4), age int, sex char(2), hobby varchar(20), primary key(id));
#创建students表并指定id为主键
1
2
3
4
5
6
7
8

6.4 删除指定的数据表

#方式一：库内删除
use 数据库名；
drop table 数据表名;
 
#方式二：库外删除
drop table 数据库名.数据表名;		
1
2
3
4
5
6

7. 表中数据的添加和修改

7.1 向指定表里添加数据

insert into 表名(字段1,字段2[,...]) values(字段1的值,字段2的值,...);
或
insert into 表名 values (字段1的值, 字段2的值, ...);    #要按照表结构的字段顺序设置值

#示例
insert into students values (2, '张三', 20, '男', 'game')
select * from students;   #查看表内容
1
2
3
4
5
6
7

7.2 查询数据表内数据记录

select 字段名1,字段名2[,...] from 表名 [where 条件表达式];
 
#查询全部数据
select * from 数据表名;
#示例
select * from students;
 
#查询指定数据
#示例
select id,name from students where id=2;
#查看id为2行的id和name列
1
2
3
4
5
6
7
8
9
10
11

#以列表方式竖向显示
select * from 数据表名\G;			
#示例
select * from students\G;
 
#只显示头2行
select * from 数据表名 limit 2;
#示例	
select * from students limit 2;
 
#显示第3行后的2行
select * from 数据表名 limit 3,2;
#示例
select * from students limit 3,2;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

7.3 修改、更新数据表中的数据记录

update 表名 set 字段名1=字段值1[,字段名2=字段值2] [where 条件表达式];
 
#示例
update students set age=19 where id=3;
#修改id为3行的age为19
1
2
3
4
5

7.4 删除表内容

delete from 表名 [where 条件表达式];
 
#示例
delete from students where id=3;
#删除studtens表里id为3的内容
1
2
3
4
5

8. 修改表名和表结构

8.1 修改表名

alter table 旧表名 rename 新表名;
 
#示例
alter table students rename st;
#将表students改名为st
1
2
3
4
5

8.2 扩展表结构，添加字段

alter table 表名 add 字段;

alter table st add address varchar(50) default '不详';
#为st表添加默认值为不详，字段名为address的属性
1
2
3
4

8.3 修改字段（列）名，添加唯一键

alter table 表名 change 旧列名 新列名 数据类型 [unique key];

#示例
alter table st change name username varchar(10) unique key;
#修改st表name字段名为username并指定其为唯一键
1
2
3
4
5

#验证
insert into st (id, username, age) values (6, '张三', 22);

insert into st (id, username, age) values (6, '老王', 22);
#唯一键不可重复
1
2
3
4
5

8.4 删除字段

alter table 表名 drop 字段名;
 
例如：
#删除st表中sex字段
alter table st drop sex;
1
2
3
4
5

#拓展
use scj;

create table if not exists info (
id int(4) zerofill primary key auto_increment,				
name varchar(10) not null default '匿名',
cardid int(18) not null unique key,
hobby varchar(50));
----------------------------------------------------------------------------------------------------------
#if not exists：表示检测要创建的表是否已存在，如果不存在就继续创建
#int(4) zerofill：表示若数值不满4位数，则前面用“0”填充，例0001
#auto_increment：表示此字段为自增长字段，即每条记录自动递增1，默认从1开始递增；自增长字段数据不可以重复；自增长字段必须是主键；如添加的记录数据没有指定此字段的值且添加失败也会自动递增一次
#unique key：表示此字段唯一键约束，此字段数据不可以重复；一张表中只能有一个主键, 但是一张表中可以有多个唯一键
#not null：表示此字段不允许为NULL
----------------------------------------------------------------------------------------------------------
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15