DQL语句的深入及数据库的备份与恢复

最新推荐文章于 2024-10-09 12:58:26 发布

Wxr不是外星人

最新推荐文章于 2024-10-09 12:58:26 发布

阅读量228

点赞数 9

文章标签：数据库 sql

本文链接：https://blog.csdn.net/2401_86272265/article/details/141529166

版权

DQL语句的深入及数据库的备份与恢复

一.连表查询和联合查询

1.介绍

通过多个表的数据查询结果

2.联合查询

将两条查询语句结合在一起,显示到同一个查询结果中

select 列 from 表 union select 列 from 表;
#准备模拟数据
CREATE DATABASE test;
USE test;
CREATE TABLE t1 (`id` INT,`name` CHAR(3));
CREATE TABLE t2 (`id` INT,`age` TINYINT(32));

INSERT INTO t1 VALUES(1,'dma'),(2,'dms'),(3,'dmb');
INSERT INTO t2 VALUES(1,18),(2,6),(3,20);

#使用联合查询
select SELECT * FROM t1 union SELECT * FROM t2;
##注意
1.使用联合查询确保,前后两个查询语句彼此之间的列数必须一致
2.联合查询select不支持过于复杂的子句,可以使用where
##结合order by的使用
select id,name from t1 union select id,age from t2 order by 1;

3.堆叠查询

select SELECT * FROM t1;SELECT * FROM t2;

二.连表查询的应用

1.笛卡尔乘积

集合:A=(a,b) B=(f,g) C=(af,ag,bf,bg)

2.连表查询

将多个表中的数据行通过笛卡尔乘积的形式,两两组合构建成一个新的大表

内连接查询:将两个表取交集
外连接查询:
	左外连接:取左边表的全部数据行
	右外连接:取右边表的全部数据行

内连接查询	
#模拟数据
insert into t2 values(6,10);
insert into t1 values(4,'dmm');
#内连接查询
select * from t1 join t2 on t1.id=t2.id;
#左外连接查询 
select * from t1 left join t2 on t1.id=t2.id;
#右外连接 
select * from t1 right join t2 on t1.id=t2.id;

3.连表查询的使用方法

A:A.1 A.2 A.3
B:B.2 B.6 B.7
C:C.6 C.10 C.4

两个表查询
A B两个表
select * from A join B on A.2=B.2

多个表查询
A C 两个表
select * from A join B on A.2=B.2 join C on B.6=C.6

三.连表查询练习

course: 课程表 		cid 课程ID  cname 课程名称   tid 老师ID
sc: 成绩表     		sid 学生ID  cid   课程ID     score 分数
student:学生信息表  	sid 学生ID  sname 学生名     sage 学生年龄  ssex 学生性别
teacher:老师信息表      tid 老师ID  tname 老师名

-- 练习1：统计每个学员，学习课程的门数？
# 所需表：course student
# 关联列/表： sc  

select student.`sname`,COUNT(course.`cname`) 
from course join sc on course.`cid`=sc.`cid`
join student on sc.sid=student.`sid` 
group by student.`sname`;

-- 练习2：每位老师所教课程名称
# 所需表：course tearcher
# 关联列/表： course.tid teacher.tid
select teacher.tname,group_concat(course.cname) from course join teacher on course.tid=teacher.tid group by teacher.tname;

-- 练习3：统计每位老师教授的学生中女生的数量
# 所需表：tearcher student
# 关联列/表： course.tid teacher.tid
select teacher.`tname`,GROUP_CONCAT(student.`sname`),COUNT(student.`sname`)  from student join sc on student.`sid`=sc.`sid`
join course on course.`cid`=sc.`cid`
join teacher on course.`tid`=teacher.`tid`
where student.`ssex`='女'
group by teacher.`tname`;

四、一些特殊的查询方法

1.表别名和列别名

# 列别名的应用
select 表.列 as 列别名 from 表
select teacher.`tname` as '老师姓名',group_concat(student.`sname`) as '女学生名',count(student.`sname`) as '女生数量' from student join sc on student.`sid`=sc.`sid`
join course on course.`cid`=sc.`cid`
join teacher on course.`tid`=teacher.`tid`
where student.`ssex`='女'
group by teacher.`tname`;

# 表别名
select 表.列 from 表 as 表别名
select t.`tname` as '老师姓名',group_concat(s.`sname`) as '女学生名',count(s.`sname`) as '女生数量' 
from student as s join sc on s.`sid`=sc.`sid`
join course  as c on c.`cid`=sc.`cid`
join teacher as t on c.`tid`=t.`tid`
where s.`ssex`='女'
group by t.`tname`;

2.查询结合判断表达式的应用

作用：只查询符合某条件的结果，不符合做其他处理

 准备环境
create table test.t4(`id` int,`name` char(5),`height` int(64));
insert into test.t4 values(1,'张三',165),(2,'李四',175),(3,'二狗子',65),(4,'麻子',1);


两种判断
if(条件,a,b)  
	成立 则a
	不成立 则 b 

-- 查询t4,身高超过160，正常，低于160，偏低
select name,if(height >160,'正常','偏低' ) from test.t4;



(case when 条件1 then a when 条件2 then b else c end)

-- 查询t4,身高超过等于175，过高，低于175 高于160 正常，低于160 偏低 
select name,height,(case when height >= 175 then '过高' when height > 160 then '正常' else '偏低' end)from test.t4;

五、数据库的备份与恢复

1.备份

全备：完整备份
增备：增量备份，只针对上一次差异的数据进行备份

一般要求一周一全备，每日一增备

2.数据库备份

mysqldump：底层通过逻辑实现的备份，备份的是执行的sql语句

# 参数 
-u 指定用户 
-p 指定密码 
-h 指定数据库的IP 
-P 指定数据库的端口 默认3306

-A 全备

# 进行全备的过程
mysqldump -u -p -A >指定备份文件
mysqldump -uroot -p123456 -A  >/opt/full.sql

# 进行单库备份
mysqldump -u -p -A 指定库1 指定库2>指定备份文件
-B 单库备份
mysqldump -uroot -p -B school
mysqldump -uroot -p -B school test

# 单表备份
mysqldump -u -p 指定库 表1 表2>/opt/full.sql
mysqldump -uroot -p school student teacher >/opt/full.sql
school是库  student，teacher是表

3. 数据的恢复

# 方式一：登录数据库后恢复
  source /opt/full.sql;
  
# 方式二：通过命令行下恢复
  mysql -uroot -p </opt/full.sql

六、MySQL—特殊库—information_schema

1.介绍

存放系统一些元数据的视图信息

show tables from information_schema;

2.核心表

tables：存放系统所有表的属性信息
	 TABLE_SCHEMA：		#每个表所属的库 
	 TABLE_NAME：		#表名
	 TABLE_ROWS：		#当前表的总数据行数
	 AVG_ROW_LENGTH： 	#平均每行数据的长度
	
# 案例：查看当前school各种表的信息
select * from information_schema.tables where table_schema='school' \G;
	
COLUMNS: 存放系统中各表各列的信息

3. 备份字符集位utf8mb4的库

1）mysqldump -uroot -p123 -B 库名 >>back.sql
2）select concat('mysqldump -uroot -p123456 -B ',schema_name,' >>/opt/back.sql') from information_schema.schemata where  DEFAULT_CHARACTER_SET_NAME='utf8mb4' into outfile '/opt/test.sh';

# 注意info outfile
1- 开启安全目录参数
vim /etc/my.cnf
secure-file-priv=/opt
2- 目录需要具备权限
chown -R mysql.mysql /opt