MySQL 高级（持续更新....）

尼尔弗格森
209 阅读8分钟

第1 章MySQL 简介

1、什么是Mysql

MySQL 是一个关系型数据库管理系统，由瑞典MySQL AB公司开发，目前属于Oracle公司。

Mysql 是开源的，可以定制的，采用了GPL协议，你可以修改源码来开发自己的Mysql系统。

MySQL 使用标准的SQL 数据语言形式。

Mysql 可以允许于多个系统上，并且支持多种语言。这些编程语言包括C、C++、Python、Java、Perl、PHP、 Eiffel、Ruby和Tcl 等。

MySQL 支持大型数据库，支持5000 万条记录的数据仓库，32位系统表文件最大可支持4GB，64位系统支 持最大的表文件为8TB。

2、在Linux上安装Mysql

2.1 准备工作

2.1.1 检查当前系统是否安装过Mysql

（1）CentOS6 环境下

命令：rpm -qa|grep mysql

默认Linux 在安装的时候，自带了mysql 相关的组件。

先卸载系统自带的mysql，执行卸载命令rpm -e --nodeps mysql-libs

（2）CentOS7 环境下

命令：rpm -qa|grep mariadb

默认Linux（CentOS7）在安装的时候，自带了mariadb(mysql 完全开源版本)相关的组件。

先卸载系统自带的mariadb，执行卸载命令rpm -e --nodeps mariadb-libs

2.1.2 检查/tmp文件夹权限

查看/tmp文件夹权限:

赋予其最大权限: chmod -R 777 /tmp

2.2 Mysql 的安装

安装的版本是mysql 5.5，官网下载地址：dev.mysql.com/downloads/m…

2.2.1 将rpm安装包拷贝到opt目录下

2.2.2 在安装目录下执行rpm安装

① rpm -ivh MySQL-client-5.5.54-1.linux2.6.x86_64.rpm

② rpm -ivh MySQL-server-5.5.54-1.linux2.6.x86_64.rpm

安装完成后，出现如下警告，需要为软件设置root用户的密码。 image.png image.png

③ 查看是否安装成功：mysqladmin --version image.png 或者也可以通过rpm 命令来查看： image.png

④ 设置用户和密码： mysqladmin –u root password xxxxxx image.png 2.3 Mysql 服务

2.3.1 Mysql 服务的启动和停止

查看状态: service mysql status image.png 启动服务: service mysql start image.png 停止服务: service mysql stop image.png 重启服务: service mysql restart image.png 启动之后，查看进程: image.png 2.3.2 Mysql 的安装位置

参数 路径 解释 备注

--datadir /var/lib/mysql/ mysql数据库文件的存放路径

--basedir /usr/bin 相关命令目录mysqladmin mysqldump等命令

--plugin-dir /usr/lib64/mysql/plugin mysql插件存放路径

--log-error /var/lib/mysql/jack.atguigu.err mysql错误日志路径

--pid-file /var/lib/mysql/jack.atguigu.pid 进程pid文件

--socket /var/lib/mysql/mysql.sock 本地连接时用的unix套接字文件 /usr/share/mysql 配置文件目录 mysql脚本及配置文件 /etc/init.d/mysql 服务启停相关脚本

2.3.3 Mysql 服务的自启动

Mysql 服务是开机自动启动的！ image.png 如果要取消开机自启动，则输入命令ntsysv，出现以下界面： image.png 使用空格取消选中，然后按TAB 确定！

2.3.4 Mysql 的重复启动问题 image.png 此时查看，多了很多进程： image.png 尝试去登录或者操作：报错！ image.png 查看服务状态： image.png 解决：杀死所有和mysql 进程相关的操作，然后重启服务！ image.png 注意是mysqld，d代表demon，守护进程。然后再重启： image.png

2.4 修改字符集

2.4.1 常用命令

SQL 语句描述备注

show databases 列出所有数据库

create database 库名创建一个数据库

create database 库名 character set utf8 创建数据库，顺便执行字符集为utf-8

show create database 库名查看数据库的字符集

show variables like '%char%' 查询所有跟字符集相关的信息

set [字符集属性]=utf8 设置相应的属性为utf8 只是临时修改，当前有 效。服务重启后，失效。

alter database 库名 character set 'utf8' 修改数据库的字符集

alter table 表名convert to character set 'utf8' 修改表的字符集

2.4.2 字符集乱码原因

如果在建库建表的时候，没有明确指定字符集，则采用默认的字符集latin1,其中是不包含中文字符的。查看默 认的编码字符集： image.png

2.4.3 永久修改

（1）修改配置文件

在/usr/share/mysql/ 中找到名称my.cnf的配置文件，拷贝其中的my-huge.cnf到/etc/ 并命名为my.cnf。添加以下内容后再重启服务。注意：必须将文件拷贝到指定路径，且名称为my.cnf

[client]

default-character-set=utf8

[mysqld]

character_set_server=utf8

character_set_client=utf8

collation-server=utf8_general_ci

[mysql]

default-character-set=utf8

再次查看：

image.png

注意：已经创建的数据库的设定不会发生变化，参数修改只对新建的数据库有效！

（2）修改已创建库、表字符集

修改数据库的字符集

mysql> alter database mydb character set 'utf8';

修改数据表的字符集

mysql> alter table mytbl convert to character set 'utf8';

（3）修改已经乱码数据

无论是修改mysql 配置文件或是修改库、表字符集，都无法改变已经变成乱码的数据。只能删除数据重新插入或更新数据才可以完全解决。

2.5 设置大小写不敏感

2.5.1 查看大小写是否敏感

show variables like '%lower_case_table_names%'

windows 系统默认大小写不敏感，但是linux 系统是大小写敏感的 image.png 2.5.2 设置大小写不敏感

在my.cnf 这个配置文件[mysqld] 中加入lower_case_table_names = 1 ，然后重启服务器

属性设置 描 述

0 大小写敏感

1 大小写不敏感。创建的表，数据库都是以小写形式存 放在磁盘上，对于sql 语句都是转换为小写对表和DB 进行查找

2 创建的表和DB依据语句上格式存放，凡是查找都是转 换为小写进行

注意：如果要设置属性为大小写不敏感，要在重启数据库实例之前就需要将原来的数据库和表转换为小写，否则将找不到数据库名。在进行数据库参数设置之前，需要掌握这个参数带来的影响，切不可盲目设置。

2.6 sql_mode

sql_mode 定义了对Mysql 中sql 语句语法的校验规则！

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

2.6.1 sql_mode 常用的值

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

2.6.2 查看和修改

① 查看当前的sql_mode: select @@sql_mode;

② sql_mode 的影响案例：group by 查询语法错误！

CREATE TABLE tbl (id INT,NAME VARCHAR(200),age INT,dept INT);
INSERT INTO tbl VALUES(1,'zhang3',33,101);
INSERT INTO tbl VALUES(2,'li4',34,101);
INSERT INTO tbl VALUES(3,'wang5',34,102);
INSERT INTO tbl VALUES(4,'zhao6',34,102);
INSERT INTO tbl VALUES(5,'tian7',36,102);

查询每个dept中年龄最大的人: SELECT NAME,dept,MAX(age) FROM tbl GROUP BY dept;

正确写法:

SELECT id,name,ab.dept,ab.maxage

FROM tbl m

INNER JOIN

(SELECT dept,MAX(age)maxage FROM tbl GROUP BY dept) ab

ON ab.dept = m.dept AND m.age=ab.maxage;

③ 临时修改sql_mode: set @@sql_mode=’’;

④ 永久修改，需要在配置文件my.cnf 中修改

[mysqld] 下添加sql_mode='' 然后重启mysql 即可

第2章 MySql 的用户和权限管理

1、 Mysql 的用户管理

1.1 相关命令

命令 描述 备注 create user zhang3 identified by '123123'; 创建名称为zhang3 的用户，密码设为123123； select host,user,password,select_priv,insert_priv,drop_priv from mysql.user; 查看用户和权限的相关信 息 set password =password('123456') 修改当前用户的密码 update mysql.user set password=password('123456') where user='li4'; 修改其他用户的密码所有通过user 表的修改，必须 用flush privileges; 命令才能生 效 update mysql.user set user='li4' where user='wang5'; 修改用户名所有通过user 表的修改，必须 用flush privileges; 命令才能生 效 drop user li4 删除用户不要通过delete from user u where user='li4' 进行删除，系 统会有残留信息保留。

avatar
文章
阅读
粉丝