MySQL学习CentOS7rpm方式安装5.7、用户管理、杂项配置

一、CentOS7rpm方式安装MySQL5.7

链接：https://pan.baidu.com/s/1pi5L2_uobD1GMCdbRBudlw
提取码：pvnk

1. 检查当前系统是否安装过mysql，执行安装命令前，先执行查询命令

   # CentOS6:
   rpm -qa|grep mysql      
   # 如果存在卸载命令：rpm -e --nodeps  mysql-libs
   
   # CentOS7
   rpm -qa|grep mariadb
   # 如果存在卸载命令：rpm -e --nodeps  mariadb-libs
   

   如果存在：
   

2. 检查当前mysql依赖环境

   rpm -qa|grep libaio  # 检查libaio包
   rpm -qa|grep net-tools # 检查net-tools包
   

   没有的话执行命令下载：yum install libaio-devel.x86_64 -y

3. 检查/tmp文件夹权限

   由于mysql安装过程中，会通过mysql用户在/tmp目录下新建tmp_db文件，所以请给/tmp较大的权限

   执行 ：

   chmod -R 777 /tmp
   

4. 安装：在mysql的安装文件目录下执行：（必须按照顺序执行）

   rpm -ivh mysql-community-common-5.7.16-1.el7.x86_64.rpm 
   rpm -ivh mysql-community-libs-5.7.16-1.el7.x86_64.rpm
   rpm -ivh mysql-community-client-5.7.16-1.el7.x86_64.rpm 
   rpm -ivh mysql-community-server-5.7.16-1.el7.x86_64.rpm
   如在检查工作时，没有检查mysql依赖环境在安装mysql-community-server会报错
   

   

5. 查看MySQL安装版本

   mysqladmin --version
   

6. mysql服务的初始化

   为了保证数据库目录为与文件的所有者为 mysql 登陆用户，如果你是以 root 身份运行 mysql 服务，需要执行下面的命令初始化

   mysqld --initialize --user=mysql
   

   另外 --initialize 选项默认以“安全”模式来初始化，则会为 root 用户生成一个密码并将该密码标记为过期，登陆后你需要设置一个新的密码
   查看密码：cat /var/log/mysqld.log
   
   7. mysql服务的启+停

   启动：systemctl start mysqld.service
   关闭：systemctl stop mysqld.service
   重启：systemctl restart mysqld
   查看状态：systemctl status mysqld
   

   
   8. 首次登录

   首次登陆通过 mysql -uroot -p进行登录，在Enter password：录入初始化密码.

   因为初始化密码默认是过期的，所以查看数据库会报错

   修改密码：

   ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password'; 
   

   9. 查看MySQL的安装位置

   ps -ef|grep mysql
   

   10. 自启动mysql服务

   查看mysql是否自启动（默认自启动）

   systemctl list-unit-files|grep mysqld.service 
   

   
   如不是enabled可以运行如下命令设置自启动

   systemctl enable mysqld.sercice
   

   11. 修改字符集问题
       直接插入中文数据报错。
       修改配置文件： vim /etc/my.cnf
       在最后加上中文字符集配置character_set_server=utf8
       

二、用户管理

1. 创建用户

create user zhang3 identified by '123123';
   表示创建名称为zhang3的用户，密码设为123123；

2. 了解user表

 select host,user,authentication_string,select_priv,insert_priv,drop_priv from mysql.user;

• host：表示连接类型

  ​ % ：表示所有远程通过 TCP方式的连接

  ​ IP 地址： 如 (192.168.1.2,127.0.0.1) 通过制定ip地址进行的TCP方式的连接

  ​ 机器名 ：通过制定i网络中的机器名进行的TCP方式的连接

  ​ ::1 ：IPv6的本地ip地址 等同于IPv4的 127.0.0.1

  ​ localhost ：本地方式通过命令行方式的连接 ，比如mysql -u xxx -p 123xxx 方式的连接。

• User：表示用户名

  ​ 同一用户通过不同方式链接的权限是不一样的。

• password ： 密码

  ​ 所有密码串通过 password(明文字符串) 生成的密文字符串。加密算法为MYSQLSHA1 ，不可逆 。

  ​ mysql 5.7 的密码保存到 authentication_string 字段中不再使用password 字段。

• select_priv , insert_priv等 ：为该用户所拥有的权限。

3. 设置密码

   修改当前用户的密码:

   set password =password('123456')
   

   修改某个用户的密码:

   update mysql.user set password=password('123456') where user='li4';
   flush privileges;  # 所有通过user表的修改，必须用该命令才能生效。
   

4. 修改用户名

   update mysql.user set user='li4' where user='wang5';
   flush privileges;  #所有通过user表的修改，必须用该命令才能生效。
   

5. 删除用户

   drop user li4 ;
   不要通过delete from  user u where user='li4' 进行删除，系统会有残留信息保留。 
   

6. 给用户授权

   授权命令：

   grant 权限1,权限2,…权限n on 数据库名称.表名称 to 用户名@用户地址 identified by ‘连接口令’;

   该权限如果发现没有该用户，则会直接新建一个用户。

   比如：

   grant select,insert,delete,drop on mysql.* to li4@localhost ;
    #给li4用户用本地命令行方式下，授予mysql这个库下的所有表的插删改查的权限。
    
   grant all privileges on *.* to joe@'%' identified by '123'; 
   #授予通过网络方式登录的的joe用户 ，对所有库所有表的全部权限，密码设为123.
   

7. 收回权限
   查看当前用户权限

   show grants;
   

   收回权限命令：

   revoke 权限1,权限2,…权限n on 数据库名称.表名称 from 用户名@用户地址 ;
   
   REVOKE ALL PRIVILEGES ON mysql.* FROM joe@localhost;
   #收回全库全表的所有权限
   
   REVOKE select,insert,update,delete ON mysql.* FROM joe@localhost;
   #收回mysql库下的所有表的插删改查权限
   

   必须用户重新登录后才能生效

三、杂项配置

引入：

CREATE TABLE mytbl2 (id INT,NAME VARCHAR(200),age INT,dept INT);
INSERT INTO mytbl2 VALUES(1,'zhang3',33,101);
INSERT INTO mytbl2 VALUES(2,'li4',34,101);
INSERT INTO mytbl2 VALUES(3,'wang5',34,102);
INSERT INTO mytbl2 VALUES(4,'zhao6',34,102);
INSERT INTO mytbl2 VALUES(5,'tian7',36,102);
#每个机构年龄最大的人
SELECT NAME,dept,MAX(age) FROM mytbl2 GROUP BY dept;

上面语句是错的，group by使用原则：select 后面只能放 函数 和group by后的字段

正确的写法：

SELECT * FROM mytbl2 m INNER JOIN(
SELECT dept,MAX(age)maxage FROM mytbl2 GROUP BY dept
)ab ON ab.dept=m.dept AND m.age=ab.maxage

配置：

MySQL的sql_mode合理设置

sql_mode是个很容易被忽视的变量，默认值是空值，在这种设置下是可以允许一些非法操作的，比如允许一些非法数据的插入。在生产环境必须将这个值设置为严格模式，所以开发、测试环境的数据库也必须要设置，这样在开发测试阶段就可以发现问题。

show  variables like 'sql_mode';
sql_mode常用值如下： 
set sql_mode='ONLY_FULL_GROUP_BY';

• ONLY_FULL_GROUP_BY：对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么这个SQL是不合法的，因为列不在GROUP BY从句中
• NO_AUTO_VALUE_ON_ZERO：该值影响自增长列的插入。默认设置下，插入0或NULL代表生成下一个自增长值。如果用户 希望插入的值为0，而该列又是自增长的，那么这个选项就有用了。
• STRICT_TRANS_TABLES：在该模式下，如果一个值不能插入到一个事务表中，则中断当前的操作，对非事务表不做限制
• NO_ZERO_IN_DATE：在严格模式下，不允许日期和月份为零
• NO_ZERO_DATE：设置该值，mysql数据库不允许插入零日期，插入零日期会抛出错误而不是警告。
• ERROR_FOR_DIVISION_BY_ZERO：在INSERT或UPDATE过程中，如果数据被零除，则产生错误而非警告。如 果未给出该模式，那么数据被零除时MySQL返回NULL
• NO_AUTO_CREATE_USER：禁止GRANT创建密码为空的用户
• NO_ENGINE_SUBSTITUTION：如果需要的存储引擎被禁用或未编译，那么抛出错误。不设置此值时，用默认的存储引擎替代，并抛出一个异常
• PIPES_AS_CONCAT：将"||"视为字符串的连接操作符而非或运算符，这和Oracle数据库是一样的，也和字符串的拼接函数Concat相类似
• ANSI_QUOTES：启用ANSI_QUOTES后，不能用双引号来引用字符串，因为它被解释为识别符
• ORACLE：设置等同：PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, NO_AUTO_CREATE_USER.