Linux-CentOs-MySQL数据库[5.7]

一、部署MySQL

1.清理环境

yum -y remove $(rpm -qa |  grep mysql) && 
rm -f /etc/my* && 
rm -rf /var/lib/mysql/* && 
echo "MySQL环境已清理完成" || echo "MySQL数据库环境清理异常"

2.配置MySQL官方yum源

yum -y install https://dev.mysql.com/get/mysql80-community-release-el7-1.noarch.rpm

修改yum源

vim /etc/yum.repos.d/mysql-community.repo
--------------------------------------
将5.7的源打开，将5.7的gpgcheck关闭将8.0的源关闭
--------------------------------------------
[mysql57-community]
name=MySQL 5.7 Community Server
baseurl=http://repo.mysql.com/yum/mysql-5.7-community/el/7/$basearch/
enabled=1		##把enabled从0改为1
gpgcheck=0		##把gpgcheck从1改为0
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

[mysql80-community]
name=MySQL 8.0 Community Server
baseurl=http://repo.mysql.com/yum/mysql-8.0-community/el/7/$basearch/
enabled=0		##把enabled从1改为0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

3.安装MySQL/启动MySQL

yum -y install mysql-community-server &&
systemctl start mysqld

4.查看

ss -nplt

5.获取临时密码

grep 'password' /var/log/mysqld.log
或
awk '/A temporary password/{p=$NF}END{print p}' /var/log/mysqld.log 

6.更改密码（设置自己的密码）

mysqladmin -uroot -p"$(获取到的密码)" password "新设置的密码"  
mysqladmin -uroot -p"$(awk '/A temporary password/{p=$NF}END{print p}' /var/log/mysqld.log)" password  "新设置的密码"

7.登录数据库

mysql -p新设置的密码

8.退出

exit
quit
\q
ctrl +d

二、基本SQL语句

1.查

查看数据库

show databases;

查看表

show tables;

查看当前所在库

select database();

查看当前登录用户

select user();

切换数据库

use Test;

查看表中数据

select * from test;

查看表结构

desc test;

2.增

创建数据库

create databases Test default charset 'UTF8';

创建表

create tables test (id int ,name varchar(20));

插入数据

insert into test (id,name) values(1,"shiyan");

3.删（谨慎使用）

删除表中数据

delete from test where id=1 ;

删除表

drop table test;

删库

drop database Test;

4.改

改数据

update test set name=txt where id=1;

三、忘记root用户密码

修改配置文件

vim /etc/my.cnf

在[mysqld]下加入
skip-grant-tables

systemctl restart mysqld

免密登录

mysql
mysql>update user set authentication_string=password('Qq111111.') where User='root' and Host='localhost';
mysql>flush privileges;

删除配置文件的修改

MySQL表名大小写不敏感

vim /etc/my.cnf

[mysqld]
lower_case_table_names=1

systemctl restart mysqld