MySQL 数据库

yum -y install gcc gcc-c++ ncurses ncurses-devel bison cmake openssl-devel

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

配置软件模块

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管理的文件

存储引擎选项：
MYISAM，MERGE，MEMORY，和CSV引擎是默认编译到服务器中，并不需要明确地安装。
静态编译一个存储引擎到服务器，使用-DWITH_engine_STORAGE_ENGINE= 1
可用的存储引擎值有：ARCHIVE, BLACKHOLE, EXAMPLE, FEDERATED, INNOBASE (InnoDB), PARTITION (partitioning support), 和PERFSCHEMA (Performance Schema)

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，否则错误依旧

编译并安装 make && make install

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

修改MySQL 配置文件 /etc/my.cnf

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号

[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

更改mysql安装目录和配置文件的属主属组

chown -R mysql:mysql /usr/local/mysql/
chown mysql:mysql /etc/my.cnf

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

source /etc/profile

初始化数据库

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

添加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 #查看端口

修改mysql 的登录密码

mysqladmin -u root password "abc123" 给root账号设置密码为abc123，原始密码为空

授权远程登录

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

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

MySQL数据库管理

行（记录）：用来描述一个对象的信息
列（字段）：用来描述对象的一个属性

常用的数据类型

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位

value	CHAR(4)	Storage Required	VARCHAR(4)	Storage Required
' '	' '	4 bytes	' '	1 bytes
'ab'	'ab '	4 bytes	'ab'	3 bytes
'abcd'	'abcd'	4 bytes	'abcd'	5 bytes
'abcdefg'	'abcd'	4 bytes	'abcd'	5 bytes

数据库的管理操作

大小写不区分，分号“;”表示结束

SQL语句

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

SQL语言分类：

DDL：数据定义语言，用于创建数据库对象，如库、表、索引等
DML：数据操纵语言，用于对表中的数据进行管理
DQL：数据查询语言，用于从数据表中查找符合条件的数据记录
DCL：数据控制语言，用于设置或者更改数据库用户或角色权限

登录及退出

查看数据库结构

查看当前的 MySQL 版本信息及连接用户名 SELECT VERSION() :SELECT USER();

查看当前服务器中的数据库 SHOW DATABASES;

查看数据库中包含的表 USE 数据库名; SHOW TABLES; 或SHOW TABLES FROM 库名;

查看表的结构（字段） USE 数据库名; DESCRIBE [数据库名.]表名; 可缩写成：DESC 表名;

也可以用 desc [库名.]表名; 查看表的结构

MySOL会附带4个系统数据库:

information schema: 主要存储系统中的一些数据库对象信息，如用上表信息、字段信息、权限信息、字符集信息和分区信息等
performance schema: 主要存储数据库服务器性能参数信息
mysql: 主要存储系统的用户权限信息
sys: MpSL51之后引入的一个新的 sys 数据库，sys 库里面的表、视图、函数以及存储过程可以让用户快速了解MySOL的一些信息，它的数据来源于performance schema

创建及删除数据库和表

创建新的数据库 CREATE DATABASE 数据库名;

创建新的表
CREATE TABLE 表名 (字段1 数据类型,字段2 数据类型[,...][,PRIMARY KEY (主键名)]);
主键一般选择能代表唯一性的字段不允许取空值（NULL），一个表只能有一个主键。

删除指定的数据表
DROP TABLE [数据库名.]表名; 如不用USE进入库中，则需加上数据库名

删除指定的数据库 DROP DATABASE 数据库名;

管理表中的数据记录

向数据表中插入新的数据记录
INSERT INTO 表名(字段1,字段2[,...]) VALUES(字段1的值,字段2的值,...);

SELECT * FROM 表名; 查询表的数据记录

查询数据记录
SELECT 字段名1,字段名2[,...] FROM 表名 [WHERE 条件表达式];

select * from 表名 [where 条件表达式];

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

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

修改、更新数据表中的数据记录
UPDATE 表名 SET 字段名1=字段值1[,字段名2=字段值2] [WHERE 条件表达式];

在数据表中删除指定的数据记录 DELETE FROM 表名 [WHERE 条件表达式];

修改表名和表结构

修改表名 ALTER TABLE 旧表名 RENAME 新表名;

扩展表结构（增加字段）
ALTER TABLE 表名 ADD address varchar(50) default '地址不详'; default '地址不详'：表示此字段设置默认值地址不详；可与 NOT NULL 配合使用

修改字段（列）名，添加唯一键
ALTER TABLE 表名 CHANGE 旧列名新列名数据类型 [unique key]; CHANGE可修改字段名、数据类型、约束等所有项。

删除字段
ALTER TABLE 表名 DROP 字段名;

use kgc;
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

数据表高级操作

克隆表，将数据表的数据记录生成到新的表中

方法一：
create table test01 like KY08; #通过 LIKE 方法，复制 KY08 表结构生成 test01 表
insert into test01 select * from KY08;

此方法能保证新表的表结构、表数据跟旧表都是一致的

方法二：
CREATE TABLE test02 (SELECT * from KY08);

此方法创建的新表的表数据和旧表是一样的，但可能会出现新表的表结构和旧表的不一致

show create table test02\G #获取数据表的表结构、索引等信息
SELECT * from test02;

清空表，删除表内的所有数据

方法一：
delete from test01;
#DELETE清空表后，返回的结果内有删除的记录条目；DELETE工作时是一行一行的删除记录数据的；如果表中有自增长字段，使用DELETE FROM 删除所有记录后，再次新添加的记录会从原来最大的记录 ID 后面继续自增写入记录。

方法二：
truncate table test01;
#TRUNCATE 清空表后，没有返回被删除的条目；TRUNCATE 工作时是将表结构按原样重新建立，因此在速度上 TRUNCATE 会比 DELETE 清空表快；使用 TRUNCATE TABLE 清空表内数据后，ID 会从 1 开始重新记录。

创建临时表

临时表创建成功之后，使用SHOW TABLES命令是看不到创建的临时表的，临时表会在连接退出后被销毁。如果在退出连接之前，也可以可执行增删改查等操作，比如使用 DROP TABLE 语句手动直接删除临时表。

CREATE TEMPORARY TABLE 表名 (字段1 数据类型,字段2 数据类型[,...][,PRIMARY KEY (主键名)]);

create temporary table test03 (
id int(4) zerofill primary key auto_increment,
name varchar(10) not null,
cardid int(18) not null unique key,
hobby varchar(50));

insert into test03 values(1,'zhangsan',123456,'running');

select * from test03;
show tables;

quit
select * from test03;

外键的定义

如果同一个属性字段X在表一中是主键，而在表二中不是主键，则字段X称为表二的外键。

主键表和外键表的理解：
（1）以公共关键字作主键的表为主键表（父表、主表）
（2）以公共关键字作外键的表为外键表（从表、外表）

注意：与外键关联的主表的字段必须设置为主键。要求从表不能是临时表，主从表的字段具备相同的数据类型、字符长度和约束。

创建外键约束，保证数据的完整性和一致性。

#创建主表 profession
create table profession (pid int(4),proname varchar(50));

#创建从表 student
create table student (id int(4) primary key auto_increment,name varchar(10),age int(3),proid int(4));

#为主表 profession 添加一个主键约束。主键名建议以“PK_”开头。
alter table profession add constraint PK_pid primary key (pid);

#为从表 student 表添加外键，并将 student 表的 proid 字段和 profession 表的 pid 字段建立外键关联。外键名建议以“FK_”开头。
alter table student add constraint FK_pro foreign key (proid) references profession (pid);

desc student;

#插入新的数据记录时，要先主表再从表
insert into profession values(1,'云计算');
insert into profession values(2,'大数据');
insert into student values(1,'zhangsan',18,1);
insert into student values(2,'lisi',19,1);
insert into student values(3,'wangwu',20,2);

#删数数据记录时，要先从表再主表，也就是说删除主键表的记录时必须先删除其他与之关联的表中的记录。
delete from student where proid=1;
delete from profession where pid=1;

#查看和删除外键约束
show create table student;
desc student;
alter table student drop foreign key FK_pro;
alter table student drop key FK_pro;

MySQL中6种常见的约束

主键约束（primary key）
外键约束（foreign key）
非空约束（not null）
唯一性约束（unique [key|index]）
默认值约束（default）
自增约束（auto_increment）

数据库用户管理

新建用户

CREATE USER '用户名'@'来源地址' [IDENTIFIED BY [PASSWORD] '密码'];

'用户名'：指定将创建的用户名
'来源地址'：指定新创建的用户可在哪些主机上登录，可使用IP地址、网段、主机名的形式，本地用户可用localhost，允许任意主机登录可用通配符%
'密码'：

若使用明文密码，直接输入'密码'，插入到数据库时由Mysql自动加密；
若使用加密密码，需要先使用SELECT PASSWORD('密码'); 获取密文，再在语句中添加 PASSWORD '密文';
若省略“IDENTIFIED BY”部分，则用户的密码将为空（不建议使用）

CREATE USER 'user1'@'localhost' IDENTIFIED BY '123456';

SELECT PASSWORD('abc123');
CREATE USER 'user2'@'localhost' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9';

查看用户信息

创建后的用户保存在 mysql 数据库的 user 表里
USE mysql;
SELECT User,authentication_string,Host from user;

查看当前登录用户 select user();

重命名用户

RENAME USER 'zhangsan'@'localhost' TO 'lisi'@'localhost';

删除用户

DROP USER 'lisi'@'localhost';

修改当前登录用户密码

SET PASSWORD = PASSWORD('abc123');

修改其他用户密码

SET PASSWORD FOR 'user1'@'localhost' = PASSWORD('abc123');

忘记 root 密码的解决办法

（1）修改 /etc/my.cnf 配置文件，不使用密码直接登录到 mysql
vim /etc/my.cnf
[mysqld]
skip-grant-tables #添加，使登录mysql不使用授权表

systemctl restart mysqld

mysql #直接登录

（2）使用 update 修改 root 密码，刷新数据库
UPDATE mysql.user SET AUTHENTICATION_STRING = PASSWORD('abc123') where user='root';

FLUSH PRIVILEGES;
quit

mysql -u root -pabc123

注意：最后再把 /etc/my.cnf 配置文件里的 skip-grant-tables 删除，并重启 mysql 服务。

数据库用户授权

授予权限

GRANT语句：专门用来设置数据库用户的访问权限。当指定的用户名不存在时，GRANT语句将会创建新的用户；当指定的用户名存在时， GRANT 语句用于修改用户信息。

GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'来源地址' [IDENTIFIED BY '密码'];

#权限列表：用于列出授权使用的各种数据库操作，以逗号进行分隔，如“select,insert,update”。使用“all”表示所有权限，可授权执行任何操作。
#数据库名.表名：用于指定授权操作的数据库和表的名称，其中可以使用通配符“*”。例如，使用“kgc.*”表示授权操作的对象为 kgc数据库中的所有表。
#'用户名@来源地址'：用于指定用户名称和允许访问的客户机地址，即谁能连接、能从哪里连接。来源地址可以是域名、IP地址，还可以使用“%”通配符，表示某个区域或网段内的所有地址，如“%.kgc.com”、“192.168.80.%”等。
#IDENTIFIED BY：用于设置用户连接数据库时所使用的密码字符串。在新建用户时，若省略“IDENTIFIED BY”部分，则用户的密码将为空。

允许用户 zhangsan 在本地查询 kgc 数据库中所有表的数据记录，但禁止查询其他数据库中的表的记录。

GRANT select ON kgc.* TO 'zhangsan'@'localhost' IDENTIFIED BY '123456';

#允许用户 lisi 在所有终端远程连接 mysql ，并拥有所有权限。
GRANT ALL [PRIVILEGES] ON *.* TO 'lisi'@'%' IDENTIFIED BY '123456';

flush privileges;
quit

mysql -u zhangsan -p123456
use kgc;
show tables;
select * from KY08;