MySQL数据库入门（三）--- 查询、权限、join语法、外键、备份

MySQL数据库入门（三）

MySQL回顾

查询：

where
_ 单一一个任意字符
% 零到多个字符 # 类似 * 通配符

select name from server where name like "web%";

and:

where id = 2 and name = "db_server2";

统计: count()

select count(id) from server where id=2;

GRANT 授权，为用户赋予访问权限是
REVOKE 取消授权，撤回授权权限

创建用户

create user  用户名@'客户端来源IP' identified by '密码';

删除用户

drop user  用户名@'客户端来源IP'

修改用户

rename user 用户名@'客户端来源IP' to '新用户名'@'客户端来源IP' ;

登录用户

mysql -u 用户名 -p  密码 -h IP地址

修改密码

// 第一种方法：

set password for '用户名'@'IP地址'=Password('新密码')

​ // 第二种方法：

alter user '用户名'@'客户端来源IP地址' identified by '新密码';

​ // 第三种方法(忘记密码时，必须使用此方法修改密码)：

UPDATE mysql.user SET authentication_string=password('QFedu123!') WHERE user='root' and host='localhost';

查看字段

desc user\G

权限管理

grant 权限 on 数据库.表 to ‘用户’@‘客户端来源IP地址’ identified by ‘密码’; – 授权并设置密码
revoke 权限 on 数据库.表 from ‘用户’@‘客户端来源IP地址’ – 取消权限

查询权限

select * from mysql.user where user='root' and host='localhost' \G

授权查询

针对所有库和表的权限，比如 *.* 去 mysql.user 中查看

select * from mysql.user where user='shark'\G

针对具体到库的权限，比如db_name.* 去 mysql.db 中查看

select * from mysql.db  where user='shark'\G

针对具体表的授权，在 mysql.tables_priv 中查看

select * from mysql.tables_priv where user='shark'\G

允许用户u1 从任何来源访问我的任何逻辑库和表

grant all privileges on *.* to u1@'%' identified by 'QianFeng@123';
select * from mysql.user where user="u1"\G
select * from mysql.db where user="u1"\G
select * from mysql.tables_priv where user="u1"\G

允许用户u2 从192.168.1.100来使用select 权限查询db1 逻辑库的所有的表信息

grant select on db1.* to u2@'192.168.1.100' identified by 'QianFeng@123';
select * from mysql.user where user="u2"\G
select * from mysql.db where user="u2"\G
select * from mysql.tables_priv where user="u2"\G

允许用户u3 从192.168.1.x来访问(select)和删除(delete)db1 逻辑库的server表信息

create database db1;
use db1
create db1.server(name varchar(10));
grant select,delete on db1.server to u3@'192.168.1.%' identified by 'QianFeng@123';
select * from mysql.user where user="u3"\G
select * from mysql.db where user="u3"\G
select * from mysql.tables_priv where user="u3"\G

root 默认只能在本地登录

select * from mysql.tables_priv where user="u1"\G
mysql -uu1 -p'QianFeng@123' -h 192.168.116.136
只有查询和删除权限
grant all on *.* to u2@'%' identified by 'QianFeng@123';
拥有全部权限

多表查询

起别名 [as 或者 空格]

select class.name as 班级, student.name from class, student where student.class_id=class.id;
select class.name as 班级, student.name 学员 from class, student where student.class_id=class.id; 

查具体哪个班级 [ and class.name=‘xxx’]

select class.name as 班级, student.name 学员 from class, student where student.class_id=class.id and class.name='云计算1901';

查具体哪个学生在哪个班级[and student.name=‘马邦德’ ]

select class.name as 班级, student.name 学员 from class, student where student.class_id=class.id and student.name='马邦德';

链接查询 [与起别名一样 as 或者 空格]

select c.name as 班级, s.name 学员  
from class as c,student  s
where s.class_id = c.id and s.name='马邦德';

join语法

/* 内连接   无对应关系则不显示 */
select A.class_name, B.name
from class as A inner join teacher as B
on A.id = B.class_id
/* 左连接   左边的表(A)所有显示，如果右边的表(B)中无对应关系，则值为null*/
select A.class_name, B.name
from class as A left join teacher as B
on A.id = B.class_id
/* 右连接 右边的(B)表所有显示，如果左边的表(A)中无对应关系，则值为 NULL*/
select A.name, B.name
from class as A right join teacher as B
on A.id = B.class_id

select t2. name, tl. name from tl,t2 where t2. other id=tl. id and t2. name="A";
select t2. name, tl. name from tl,t2 where t2. other id=tl. id and t2. name="B";

insert into student values（9，“眼镜”，18，5）；

外键

Eg：

CREATE TABLE cj (
  id int  NOT NULL AUTO_INCREMENT,
  name varchar(10) ,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

create table  car( 
id int auto_increment primary key, 
name varchar(10), 
cj_id int, 
foreign key(cj_id) references cj(id) 
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

备份：

指定时间段备份：

• mysqldump

• xtrabackup

实时备份：

二进制日志

---

• 热备份
  不停 MySQL 服务情况下，对数据进行备份

• 冷备份
  需要停止 MySQL 服务情况下，对数据进行备份
  systemctl stop mysqld

---

• 逻辑备份
  把目前数据库中存在的数据转换为SQL 语句，把这些语句保存
  在一个明文的文件中。

  工具：

• mysqldump # 热备份

物理备份

直接对数据库中产生的数据文件，进行备份。
备份的是文件或者文件夹
对文件系统的备份

• cp # 冷备份
• tar # 冷备份
• scp # 冷备份
• ghost # 冷备份
• 硬盘 镜像 # 冷备份
• LVM 的 LV 磁盘快照
• xtrabackup(掌握) # 热备份

---

mysqldump
场景：
数据量小 100G 以下

恢复数据速度慢

xtrabackup
场景：
数据量大的

恢复数据速度快

---

练习：

服务器表：
id
主机名

硬盘表：
容量

内存表：
容量

1. 创建 3 张表

2. 授权 用户 shark 从 10.9.99.x 网段访问 服务器表

3. 公司来了一个漂亮的小姐姐，是一位开发人员，请根据目前的情况
   对其进行授权。他负责整个项目的开发。

4. 写 查询 语句

• 查询服务器 dbserver 的总硬盘容量

• 查询内存容器大于 32G 的服务器名称

create table t1 (ID int NOT NULL AUTO_INCREMENT, NAME varchar(20) ,PRIMARY KEY (ID))ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
create table t2 (ID int auto_increment primary key, MEM varchar(10),LOCAL_ID int  ,foreign key(LOCAL_ID) references t1(ID))ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
create table t3 (ID int auto_increment primary key, MEM varchar(10),LOCAL_ID int ,foreign key(LOCAL_ID) references t1(ID))ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
insert into t1 values(1,"DELL"),(2,"LENOVO"),(3,"H3C");
insert into t2 values(1,2,1),(2,2,2),(3,1,3);
insert into t3 values(1,10,1),(2,2,2),(3,1,3);

grant select on Neko.t1  to  'shark'@'192.168.116.%' identified by 'QianFeng@123';
mysql -ushark -p'QianFeng@123' -h 192.168.116.136

grant select on Neko.*  to  'xjj'@'192.168.116.136' identified by 'QianFeng@123';

select t1.NAME 服务器,sum(t2.MEM) 容量 from t1,t2 where t1.ID=t2.LOCAL_ID and t1.NAME='DELL';

select t1.NAME 服务器,t3.size from (select sum(t3.MEM) size,LOCAL_ID from t3 group by LOCAL_ID) as t3,t1 where t1.ID=t3.LOCAL_ID and t3.size>= 2;

---