关系型数据库：

• Oracle

• MS SQL Server

• MySQL

• PostgreSQL

 

MySQL分支：

• MySQL

• MariaDB

• Percona Server

• AliSQL

 

非关系型数据库(NoSQL)：key:value形式存储数据，数据之间没有关联

• redis

• mongodb

 

一、 安装配置mariadb

1、 安装MariaDB

yum install -y mariadb-server

systemctl start mariadb

systemctl enable mariadb

 

2、 配置MariaDB

2.1、 服务端配置文件

vi /etc/my.cnf

#数据存放位置

datadir=/var/lib/mysql

 

#socket文件所在位置

socket=/var/lib/mysql/mysql.sock

 

#指定监听地址，不写或设为0.0.0.0表示监听所有地址来访问，可以不写，默认就是监听所有。

bind-address

 

#服务器监听端口，默认为3306

port

 

#字符集

character-set-server=utf8

 

注意：针对不同用途，给了不同的配置文件模版，在/usr/share/mysql/*.cnf

 

2.2、 客户端配置文件

vi /etc/my.cnf.d/client.cnf

[client]

default-character-set=utf8

 

vi /etc/my.cnf.d/mysql-clients.cnf

[mysql]

default-character-set=utf8

 

2.3、 防火墙

firewall-cmd --permanent --add-service=mysql

firewall-cmd --reload

 

2.4、 修改root密码

安装完成后，数据库的root密码默认为空

 

mysql_secure_installation

• 设置root密码

• 删除可以从本地主机外部访问的root帐户

• 删除anonymous-user

• 删除test数据库

 

注意：必须先启动数据库，才能进行这步操作

 

2.5、 登陆

#本地登陆

mysql -u root -p

 

#远程登陆

mysql -u user1 -h 172.25.0.11 -p

 

注意

• 如果要在命令行输入密码，要紧跟-p后面，不能空格，否则认为密码为空

• 表和数据库的名称要区分大小写，命令不区分大小写

 

3、 SQL语言

• DDL（Data Definition Language）数据定义语言，用于定义或修改数据库中的对象，如：表、索引、视图、序列、用户、角色、表空间等。

• DML（Data Manipulation Language）数据操纵语言，用于对数据库表中的数据进行操作，如插入，更新和删除。

• DCL（Data Control Language）数据控制语言，用来设置或更改数据库事务、权限操作（用户或角色授权，权限回收）、锁表（支持共享锁和排他锁两种锁表模式）、停机等。

• DQL（Data Query Language）数据查询语言，用来查询数据库内的数据，如单表查询、多表查询。

 

4、 查看数据库

#查询数据库

mysql> show databases;

 

#查看databse()这个函数，查询当前数据库名称

mysql> select database();

 

#连接数据库mysql

mysql> use mysql;

 

#列出mysql库中的所有表

mysql> show tables;

 

5、 查看帮助

#查看create table命令的帮助

help create table;

 

6、 字符集

字符集不对，中文会乱码，使用utf-8

 

#character_set_database，当前字符集

mysql> show variables like '%char%';

 

7、 数据类型

字符型

• char：定长数据类型，最大255字节，不管输入的字符多短，占用空间就是指定的大小

• varchar：变长数据类型，最大65535字节，在指定范围内，用多少占多少

• 数值型

• int：整型

• float：浮点型，float(10,5)，表示小数点前面最多10位数，小数点后面最多5位

 

日期时间型

• date：YYYY-MM-DD

• time：HH:MM:SS

• datetime：YYYY-MM-DD HH:MM:SS

• timestamp：YYYYMMDD HHMMSS

• year：YYYY

 

内建类型

• set，集合，指定一个字符集，赋值可以从这个字符集中选择多个字符进行组合。

• enum，枚举，比如，一个字段是性别，只定义男和女，只能从中选一个

 

二、 DDL

在mysql管理软件，可以通过SQL语句中的DDL语言来实现对表结构的操作

• create，创建表

• alter，修改表

• drop，删除表

 

注意：进入数据库后，命令可用tab补齐

 

1、 创建数据库

mysql> create database db1;

mysql> use db1;

 

#查看now()函数，查询当前时间

#在插入数据时，如果要插入当时时间，可直接调用now()函数

select now();

 

2、 创建表

#创建表t1

#同时，在表内创建4个列：id name sex age，并指定类型

#enum，枚举，只能从指定的选项中选一个

#male,男

#female，女

mysql> create table t1(id int,name varchar(50),sex enum('m','f'));

 

#列出t1表结构

mysql> describe t1;

 

#通过查询现有的表创建，新表会被直接插入查询来的数据

create table t1 select语句

 

#通过复制现有的表的表结构创建，不会复制数据，只复制表结构

create table t1 like 旧表;

 

3、 查看表

#查看表名称

show tables;

 

#查看t1表的属性

#engine，使用的引擎

#row_format，行格式，和引擎有关

#rows，总共有多少行数据

#avg_row_length，每行的平均数据大小

#data_length，总共数据大小

#data_free，已经分配置空间，但还没数据的大小

#auto_increment，下次该增长到几了

#collation，排序规则

show table status like 't1'\G

 

#查看详细的表创建过程

#显示会有点乱

show create table t1;

 

#更改显示风格

show create table t1\G

 

#查看表结构

describe t1;

 

4、 重命名表

#修改表名

mysql> alter table t1 rename t2;

 

5、 增加字段

#修改表table1，添加一列age

mysql> alter table t1 add column age int;

 

#在最前面加一个字段

mysql> alter table t1 add y1 int first;

 

#指定在哪个字段下面加一个新字段

mysql> alter table t1 add y2 int after age;

 

6、 修改字段

#modify主要用来修改字段类型

mysql> alter table t1 modify name char(10);

 

#change主要用来修改字段名，也可同时修改数据类型

#注意，必须加上数据类型，不管是旧类型，还是新类型

mysql> alter table t1 change name name1 varchar(50);

 

7、 删除字段

#修改表table1，删除age这个列

mysql> alter table table1 drop column age;

 

8、 删除表

#删除t1这个表

mysql> drop tables t1;

 

9、 删除数据库

#删除db1这个数据库

mysql> drop database db1;

 

三、 DML

在mysql管理软件，可以通过SQL语句中的DML语言来实现对数据的操作

• insert，插入数据

• update，更新数据

• delete，删除数据

 

一次可插入一行或多行数据

 

注意：

• 字符串，必须加引号，双引号或单引号都可

• 整形，不用加引号

 

1、 插入数据

#插入完整数据

insert into 表名 values(值1，值2....);

 

#向t1这个表添加两行数据，中间用逗号隔开

#注意，没指定字段，必须按表结构的列字段的顺序插入数据

mysql> insert into t1 values(1,'tom','m'),(2,'jerry','f');

 

#指定字段插入数据

insert into 表名(字段名2，字段名5.....) values(值2，值5....);

 

#指定字段，只插入指定字段的数据

mysql> insert into t1(id,name) values(3,'sam');

 

#插入多条记录

#每条数据，用（）括起来

#多条数据之间，用逗号隔开

insert into 表名 values

(值1，值2....),

(值1，值2....);

 

#插入查询结果

#从t2表中查询id>3的数据

#然后将这些数据，插入到t1表中

insert into t1 select * from t2 where id>3;

 

注意：两张表中对应的字段，数据类型必须要一致

 

2、 更新数据

update 表名 set

字段1=值1,

字段2=值2,

where 条件；

 

注意：如果不加where条件过滤，字段对应的数据，将全部被修改

 

#更新表t1，把name列的值是sam的行中，把id这一列的值改成 2

mysql> update t1 set id=2 where name='sam';

 

3、 查询数据

#列出t1表的所有行

mysql> select * from t1;

 

#查询表中的数据，但只列出id和name两列

mysql> select id,name from t1;

 

3.1、 where子句

指明过滤条件，过滤条件是一个布尔型表达式

 

LIKE

• 模糊匹配

• %，任意长度的任意字符

• _，任意单个字符

 

逻辑操作符：

• AND，与

• OR，或

• NOT，非

• XOR，异或，所谓异或，二者相同则为假，不同则为真

 

#一个条件就能查找到对应行

mysql> select * from t1 where id=1;

 

#需要两个条件才能找到对应行

mysql> select * from mysql.user where name='root' and host='localhost';

 

#查询所有名字以j开头的

select * from employee where name like 'j%';

 

#查询名字以j开头，并且后面只有一个字符的

#多个字符，就用多个_

select * from employee where name like 'j_';

 

4、 多表查询

#查询两张表中name字段相同的行，并进行合并

select * from t1,t2 where t1.name=t2.name;

 

5、 删除数据

#从表t1中删除id列为1的行

mysql> delete from t1 where id=1;

 

注意：一定要带where，指定限制条件，否则将清空整张表的数据

 

6、 实例1

#创建表2

#4列，id name interest birthday

#interest的值是一个集合，插入的数据只能从这个集合中选择

#birthday的值是一个日期

create table t2(id int,name varchar(50),interest set('chiji','basketball','bar'),birthday date);

 

#插入数据

insert into t2 values(1,'mike','chiji,bar',19910611);

 

四、 DDL进阶：约束条件

• PRIMARY KEY(PK)：标识为主键，可以唯一标识一条数据，不可为NULL

• FOREIGN KEY(FK)：标识为外键，标识表与表之间的关系，父表的主键和子表的外键

• NULL：允许为空

• NOT NULL：不能为空

• UNIQUE KEY(UK)：标识值是唯一的，不可重复，可以为空，一个表中可以有多个UK

• AUTO_INCREMENT：标识值自动增长，整形，且为主键

• DEFAULT：默认值

• UNSIGNED：无符号，也就是正数

• ZEROFILL：使用0填充

 

注意：

• 默认，字段允许为空（NULL），可设置为不允许为空（NOT NULL），就必须赋值

• 如果插入数据时，不给字段赋值，则该字段使用默认值，缺省的默认值是NULL

• key

主键，PK

外键，FK

索引，index,UK

 

例1：

mysql> create table db1.t3(

-> id int not null primary key auto_increment,

-> name varchar(50) not null,

-> sex enum('m','f') default 'm' not null,

-> age int unsigned default 20 not null,

-> interest set('chiji','basketball','bar') default 'chiji,bar'

-> );

 

desc t3;

 

#插入一条数据，其它为默认值

insert into t3(name) values('sam');

 

例2：

#创建表t4

#指定dep_name字段的值，必须唯一，不能重复

mysql> create table t4(

-> id int,

-> dep_name varchar(50),

-> unique(dep_name)

-> );

 

desc t4;

 

insert into t4 values(1,'it')

 

例3：

例2还可以用以下方法：

mysql> create table t4(

-> id int,

-> dep_name varchar(50) unique,

-> );

 

1、 主键

• 主键是能确定一条记录的唯一标识。

• primary key字段的值不允许重复，且不能为NULL（UNIQUE+NOT NULL）

• 主键可以是单列

• 也可以是多列（复合主键）

 

注意：如果没有显式地在表定义时指定主键，InnoDB存储引擎会为每一行生成一个6字节的ROWID，作为主键。

 

1.1、 单例主键

create table t5(

id int primary key not null auto_increment,

name varchar(50)

);

 

或者：

create table t5(

id int not null auto_increment,

name varchar(50)，

primary key(id)

);

 

1.2、 复合主键

复合主键，允许其中某一个字段重复，但复合主键的多个字段连起来，不能重复

 

#mysql.user表就是复合主键

#以host和user为主键

#只有host和user两个条件加起来，才能定位到一行

desc mysql.user;

 

create table t6(

host varchar(20) not null,

user varchar(20) not null,

ip varchar(20),

primary key(host,user)

);

 

注意:复合主键，只能在最后用primary key指定，因为primary key只能在语句中出现一次

 

1.3、 增加主键

#增加主键，不建议用modify

#如果用modify，需要将字段的所有信息都重写一遍

alter table 表名 add primary key(id);

 

注意：

• 如果要修改的字段，已经是主键，modify的时候，就不要再加primary key这个约束条件了，否则报错

• 如果已经存在单列主键，想添加复合主键，需要先将已存在的主键drop掉，再添加

 

#比如：给当前主键ID增加auto_increment约束条件

#不要将primary key再加上

alter table tt modify id int not null auto_increment

 

1.4、 删除主键

注意：

• 如果主键有auto_increment，需要先将auto_increment删除，才能再删除primary key

• 因为auto_increment是依赖主键的

 

alter table tt modify id int not null;

alter table tt drop primary key;

 

2、 外键

外键用于与另一张表的关联。

 

注意：

• 子表的外键是关联父表的主键

• 一个字段不能同时作为父、子表的主键

• 外键和主键的数据类型和长度必须一致，比如：varchar(50)

 

2.1、 父表

create table t6(

name varchar(50) not null,

mail varchar(20),

primary key(name)

);

 

2.2、 子表

子表(t7)的name是外键，关联父表(t6)的主键name，同步更新，同步删除

 

create table t7(

id int not null auto_increment,

name varchar(50) not null,

payroll float(10,2) not null,

primary key(id),

foreign key(name)

 

#参照父表t6的name字段

references t6(name)

 

#同步更新

on update cascade

 

#同步删除，也就是说父表数据删除了，子表对应的行也删除

on delete cascade

);

 

insert into t6 values('sam','sam@example.com'),('jerry','jerry@example.com');

insert into t7(name,payroll) values('sam',8000),('jerry',10000);

 

#同步更新

update t6 set name='same' where name=sam;

select * from t7;

 

注意:如果子表没有设置同步更新，且已经有了数据，父表不允许更新

 

#同步删除

delete from t6 where name='jerrry';

select * from t7;

 

2.3、 添加外键

#给一个已经存在的表，添加一个外键

#constraint，给定义的约束取个名字，不指定，mariadb会自动分配一个

alter table t2 add constraint fk_t2 foreign key(name) references t1(name);

 

注意：子表中不能存在数据

 

2.4、 删除外键

#查看foreign key的名字

show create table t2\G

 

#通过名字，删除foreign key

alter table t2 drop foreign key fk_t2_t1;

 

五、 DQL

select，查询过程：

• from，从哪个表查询

• where，过滤表中的字段

• group by，将过滤后的字段分组

• having，将分组进行过滤

• order by，将数据进行排序

• select，挑选所需字段

• limit，限制输出结果

 

#select的结果，如果符合缓存条件，会被缓存下来

#query_cache_type ON，表示开启了查询缓存功能

show global variables like 'query%';

 

1、 准备环境

#编号、姓名、性别、年龄、入职时间、职位、工资、部门编号

create database test;

use test;

 

#创建员工信息表

#id、姓名、性别、岁数、入职日期、部门id、职位、工资

create table employee(

id int primary key auto_increment not null,

name varchar(30) not null,

sex enum('m','f') default 'm' not null,

age int,

entry_date date not null,

dep_id int(3) zerofill not null

position varchar(50) not null,

salary double(15,2) not null,

);

 

#插入数据

insert into employee(name,sex,age,entry_date,dep_id,position,salary) values

('tom','m',20,'20010101',001,'engineer',10000),

('jerry','f',22,'20010102',002,'hr',3000),

('jack','m',30,'20120103',003,'seller',5000),

('tony','m',28,'20180101',003,'seller',5500),

('harry','m',34,'20180101',002,'hr',8000),

('marry','f',21,'20180501',002,'hr',10000),

('annie','f',NULL,'20180601',005,'seller',20000);

 

#部门信息表

#id、部门id、部门名称

create table department(

id int not null primary key auto_increment,

dep_id int(3) zerofill not null,

dep_name varchar(30) not null

);

 

#技术部，人事部，销售部、账务部

insert into department(dep_id,dep_name) values

(001,'technology'),

(002,'personnel'),

(003,'sales'),

(004,'finance');

 

2、 简单查询

#查询所有数据

select * from employee;

 

#只查询name和salary这两个字段的数据

select name,salary from employee;

 

2.1、 去重

#发现，查询出来的结果，很多重复的

select position from employee;

 

#distinct，去重

select distinct position from employee;

 

注意:distinct不能用于多字段查询，通常仅用于单个字段查询

 

2.2、 排序

根据指定的字段对查询结果进行排序，默认为升序（ASC），可手动指定降序（DESC）

 

#单列排序

#按salary进行排序

#默认按升序排列，从低到高

select * from employee order by salary;

 

#ASC，升序排列，默认的

select name,salary from employee order by salary ASC;

 

#DESC，降序排列，从高到低

select name,salary from employee order by salary DESC;

 

注意：

• 如果是数字，就按数字从小到大，或从大到小排序

• 如果是字符串，按头字母在字母表的顺序排序

 

#多列排序

#先按hire_date，降序排列

#再按salary，升序排列

select * from employee order by entry_date DESC,salary ASC;

 

2.3、 字段别名

#将name这个字段，显示为employee_name

select name as employee_name from employee;

 

2.4、 四则运算查询

四则运算，即加减乘除

 

#本来没有salary*12这个字段

#定义一个公式：slary*12，算出年薪，就是有薪乘以12

#默认，新字段名是以公式命名

select name,salary,salary*12 from employee;

 

#AS,表示给新字段起个别名

select name,salary,salary*12 AS annual_salary from employee;

 

#或者

#AS可以省略

select name,salary,salary*12 annual_salary from employee;

 

2.5、 限制显示的条数

对查询的结果进行输出行数的限制

 

#只显示前2行

select name,age from employee order by age desc limit 2;

 

#显示第3行到第4行

#第一个2，表示偏移量，偏移2行，也就是从第3行开始

#第二人2，表示显示后面2行，也就是第3行到第4行

select name,age from employee order by age desc limit 2,2;

 

3、 where子句

指明过滤条件，过滤条件是一个布尔型表达式

 

• 算术操作符：+,-,*,/,%

• 比较操作符：=,!=,<>,<,<=,>,>=，注意：!=和<>，都表示不等于

• BETWEEN min_num AND max_num;#指定一个范围

• IN (element1,element2);#in后面跟一个列表，在列表内指明取值的范围，要么等于element1，要么等于element2

•  IS NULL;#指明值为空的行

• IS NOT NUL

• LIKE，#模糊匹配；#%，任意长度的任意字符；#_，任意单个字符

• RLIKE，#后面跟正则表达式

 

逻辑操作符：

• NOT#非

• AND#与

• OR#或

• XOR#异或

#所谓异或，二者相同则为假，不同则为真

 

3.1、 单条件查询

select name,position from employee where position='hr';

 

3.2、 多条件查询

#查询HR部门，并且工资高于3000的人

select name,position from employee where position='hr' and salary>3000;

 

#查询HR部门，或工资高于3000的人

select name,position from employee where position='hr' or salary>3000;

 

3.3、 关键字between and

#查询工资在5000-10000之间的人

select name,salary from employee where salary between 5000 and 10000;

 

#排除工资在5000-1000之间的人

select name,salary from employee where salary not between 5000 and 10000;

 

3.4、 关键字IN

#查询工资在3000，5000，8000，10000的人

#太麻烦

select name,salary from employee

where salary=3000 or salary=5000 or salary=8000 or salary=10000;

 

#使用IN，指定一个集合

select name,salary from employee

where salary in (3000,5000,8000,10000);

 

#not in，取反

select name,salary from employee

where salary not in (3000,5000,8000,10000);

 

3.5、 关键字is null

#查询age字段是空的数据

select name,age from employee where age is null;

 

注意:null和空格不是一回事，空格，是一个特殊字符。

 

#查询age字段不是空的数据

select name,age from employee where age is not null;

 

3.6、 like

like,模糊查询

通配符%，匹配任意个任意字符

通配符_，匹配一个任意字符

 

#查询所有名字以j开头的

select * from employee where name like 'j%';

 

#查询名字以j开头，并且后面只有一个字符的

#多个字符，就用多个_

select * from employee where name like 'j_';

 

3.7、 正则表达式查询

regexp，使用正则表达式

 

#^ja，以ja开头

select * from employee where name regexp '^ja';

 

#om$，以om结尾

select * from employee where name regexp 'om$';

 

#n{2}，n出现两次

select * from employee where name regexp 'n{2}';

 

#.o.*，表示第二字母是o，第一个字母任意，o后面有任意个任意字符

select * from employee where name regexp '.o.*';

 

4、 使用函数查询

• avg()，求平均值

• max()，求最大值

• min()，求最小值

• count()，求数量

• sum()，求和

 

#count，统计查询的行数

select count(*) from employee;

 

#max，最大值

#查询谁的工资最高

select max(salary) from employee;

 

#min，最小值

#查询谁的工资最低

select min(salary) from employee;

 

#avg，平均值

#查询平均工资

select avg(salary) from employee;

 

#sum，求和

#查询所有人的工资总和

select sum(salary) from employee;

 

#查询出工资最低的那个人的信息

select name,salary from employee

where salary = (select min(salary) from employee);

 

4.1、 自定义显示格式

#自定义显示格式为：name annual salary:

#并起个别名：annual_salary

#注意，字符串前后加一个空格，否则，两个字段中间没有间隔

#concat()函数用于将多个字段的值，拼接成一个字段

select CONCAT(name,' annual salary: ',salary*12) annual_salary from employee;

 

5、 group子句

根据指定的条件把查询结果分组，然后进行“聚合”运算

 

#按性别进行分组，然后算出每个性别的平均年龄

#注意，结果一个组只会显示一行数据，只是个代表，代表一组数据

#avg()，算平均值函数

select sex,avg(age) from employee group by sex;

 

#按性别进行分组，然后算出每个性别的平均年龄，最后算出平动年龄大于25的分组

select sex,avg(age) as ages from employee group by sex having ages>25;

 

注意：

• where，是对select的结果进行过滤

• having，是对group by的结果进行过滤

 

6、 子查询

• 子查询是将一个查询语句嵌套在另一个查询语句中。

• 内层查询语句的查询结果，可以为外层查询提供查询条件

• 子查询中可以包含：IN、NTO IN、ANY、ALL、EXISTS、NOT EXISTS等关键字

• 还可以包含比较运算符：=、!=、>、<等

 

#查询dep_id在department表中存在的行

select * from employee

where dep_id in (select dep_id from department);

 

#查询年龄大于平均年龄的人

select name,age from employee where age>(select avg(age) from employee);

 

7、 多表查询

7.1、 交叉连接查询

不带任何匹配条件，才叫交叉连接，生成笛卡尔积。但结果可能不是我们想要的，所以还是要带上匹配条件，结果和内连接一样了。

 

#查询所有人在哪个部门

#两张表中不重复的字段，可以省略前面的表名称

#但如果有重复的字段，必须指明表名称，否则报错

#发现，没有匹配的行，并没有列出来，比如，005号部门和annie这个员工

select name,age,employee.dep_id,dep_name from employee,department where employee.dep_id = department.dep_id;

 

7.2、 inner join（常用）

内连接，只连接匹配的行

 

#将两张表交叉的部分打印出来。

select * from employee a inner join department b on a.dep_id=b.dep_id;

 

7.3、 left join

左连接，返回左表所有的行，即使右表没有匹配

 

#不管有没有连接，把左边employee表内的项都显示出来

#发现annie的部门名称是NULL

select name,dep_name from employee left join department on employee.dep_id = department.dep_id;

 

7.4、 right join

右连接，返回右表所有的行

 

#不管有没有连接，把右边department表内的项都显示出来

#发现finance部门没有人

select name,dep_name from employee right join department on employee.dep_id = department.dep_id;

 

7.5、 union

全外连接

• 连接两个以上的SELECT语句的结果组合到一个结果集合中。

• 多个SELECT语句会删除重复的数据。

 

#合并两条语句结果，去重，得到所有

#union，合并去重

select * from employee a left join department b on a.dep_id=b.dep_id

union

select * from employee a right join department b on a.dep_id=b.dep_id;

 

#去掉两个表相同的数据

select * from employee a left join department b on a.dep_id=b.dep_id where b.dep_id is null

union

select * from employee a right join department b on a.dep_id=b.dep_id where a.dep_id is null;

 

注意：union查询的分析结果会产生一张额外的临时表。

 

六、 DCL

默认，有个mysql库，里面包含以下表：

• user，记录用户信息，包含用户名，权限等

• db，数据库级的权限

• tables_priv，表级的权限

• columns_priv，列级的权限

• procs_priv，存储过程的权限

• proxies_priv，代理用户的权限

 

1、 用户管理

1.1、 登陆

mysql -h 192.168.1.11 -P 3306 -uroot -p'123' mysql -e 'select user,host from user'

• -h，指定主机名

• -P，指定mysql服务端口

• -u，指定用户名

• -p，指定密码

• mysql，指定登陆的数据库名称，这里指登陆到mysql这个库

• -e，要执行的SQL语句

 

1.2、 创建用户

用户账号组成部分：

'username'@'host'

• host,指作为客户端的主机，如果是主机名，需要能反解

• 可用通配符：% _

 

方法一：

create user user1@'localhost' identified by '123';

 

方法二：

#grant命令本来是用来赋权的

#如果给一个不存在的用户赋权，则会直接创建这个用户

grant all on *.* to 'user1'@'localhost' identified by '123';

 

#刷新权限

flush privileges;

 

注意：

• 一定要刷新权限，否则当前不生效

• 因为，创建的用户信息是存在磁盘上，而当前生效的信息在内存中

• 刷新就是将磁盘上的信息同步到内存中

 

1.3、 用户重命名

rename user old_name to new_name

 

2、 删除用户

2.1、 方法一

mysql> drop user 'user1'@'localhost';

 

2.2、 方法二

mysql> delete from mysql.user where user='user1' and host='localhost';

 

3、 修改用户密码

3.1、 方法一

#root用户的旧密码是123，改成345.

mysqladmin -uroot -p'123' password '345'

 

3.2、 方法二

#password()函数，对密码进行hash加密

update mysql.user set password=password('345') where user='root' and host='localhost';

 

#刷新

#在数据库启动时，验证数据会被加载到内存，运行过程中，会使用内存的数据来进行验证。

#而我们上面的步骤只是修改了磁盘上的数据，内存中的数据并没有被修改，所以需要刷新一下，重新加载验证数据

flush privileges;

 

3.3、 方法三

#更改当前用户的密码

mysql> set password=password('345');

 

mysql> set password for user1@'localhost'=password('345');

• 注意，只有root用户才能更改其它用户的密码

 

3.4、 方法四

mysql> alter user root@'localhost' identified by "redhat"

 

4、 root密码丢失

#重启的时候，跳过授权表

vi /etc/my.cnf

[mysqld]

skip-grant-talbes

 

systemctl restart mysqld

 

#跳过授权表，就不需要密码认证了

mysql -uroot

 

#跳过授权表，就没办法使用工具改密码了，只能直接改数据库

update mysql.user set password=password('NEW_PASSWORD')

where user='root' and host='localhost';

 

注意：

• 跳过授权表后，不能使用set命令改密码

• 修改完密码，别忘了恢复配置文件，重启服务

 

5、 权限管理

5.1、 语法

grant 权限列表 on 库名.表名 to '用户名'@'客户端主机' [identified by '密码' with option 参数];

 

权限列表：

• all，所有权限(不包括授权权限)

• select,update,delete等

 

数据库名.表名:

• *.*表示所有库下的所有表，全局级别

• db1.*表示db1这个库下的所有表，库级别

• db1.tab1表示db1库下的tab1表，表级别

• select (column1),insert(column2,column3) on db1.tab1 表示column1这个列只有select权限，column2和column3两列有insert权限，列级别

 

客户端主机：

• localhost仅本地

• %表示所有主机

• 192.168.1.%表示192.168.1这个网段内的所有主机

• 192.168.1.1指定一台具体主机

 

#查看拥有全局权限的用户信息

#\G，将内容坚着显示

#mysql.user表记录的是用户信息，及全局权限

#mysql.db表记录的是库级别的权限

select * from mysql.user\G

 

5.2、 设置权限

grant all on *.* to user1@'%' identified by 'Redhat123';

 

#grant option，表示user2可以将自己的权限再次授权给别人

grant all on *.* to user2@'%' identified by 'Redhat123' with grant option;

 

#查看全局级别权限

select * from mysql.user\G

 

#如果db1这个库不存在，也没关系，可以赋权之后，再去创建

grant all on db1.* to user3@'%' identified by 'Redhat123';

 

#查看库级别权限

select * from mysql.db\G

 

#如果库和表都不存在，没关系

grant all on db1.t1 to user4@'%' identified by 'Redhat123';

 

#查看表级别权限

select * from mysql.tables_priv\G

 

#如果表不存在，会报错

#列不存在，没关系

grant select(col1),insert(col2,col3) on db1.t1 to user5@'%' identified by 'Redhat123';

 

#查看列级别权限

select * from mysql.columns_priv\G

 

5.3、 查看权限

#查看当前用户的权限

show grants\G

 

#查看指定用户的权限

#注意，只有管理员才能查看

show grants for user1@'%'\G

 

5.4、 收回权限

#只回收部分权限

revoke delete on *.* from user1@'%';

 

#回收所有权限

revoke all privileges on *.* from user2@'%';

 

#回所所有权限，及赋权权限

revoke all privileges,grant option on *.* from user2@'%';

 

七、 备份数据库

http://172.25.254.254/content/courses/rh254/rhel7.0/materials/mariadb/

 

0.1、 备份

#将数据库t1备份到/tmp/bachup目录下，备份文件名叫t1.dump

mysqldump -u root -p t1 > /tmp/backup/t1.dump

 

#备份所有数据

mysqldump -u root -p --all-databases > /tmp/backup/all.dump

 

0.2、 恢复

systemctl stop mariadb

 

#删除数据文件

rm -rf /var/lib/mysql/*

 

systemctl start mariadb

 

方法一：

mysql -u root -p tt < /tmp/backup/tt.dump

mysql -u root -p --all-databases < /tmp/backup/all.dump

 

#从全备中只恢复tt这个数据库

mysql -u root -p tt < /tmp/bakcup/all.dump

 

方法二：

mysql> source /backup/2018-07-28-11-mysql-all.sql