[原创]MySQL数据库查询和LVM备份还原学习笔记记录
一、查询语句类型:
1)简单查询
2)多表查询
3)子查询
4)联合查询
1)简单查询:
SELECT * FROM tb_name;
SELECT field1,field2 FROM tb_name;投影
SELECT [DISTINCT] * FROM tb_name WHERE qualification;选择
FROM子句:要查询的关系
表、多个表、其他select语句
WHERE子句:布尔关系表达式
AND,OR,NOT,BETWEEN...AND...,LIKE ''[%:任意长度任意字符;_:任意单个字符],RLIKE,IN,IS NULL,IS NOT NULL
ORDER BY field_name {ASC|DESC}
AS 字段别名
LIMIT [offset,]Count offset偏移量,Count取几个
聚合:
SUM(),MIN(),MAX(),AVG(),COUNT()
GROUP BY分组:
HAVING:过滤[qualification]
SELECT AVG(Age) FROM students GROUP BY Gender;
ELECT COUNT(CID1) AS Persons,CID1 FROM students GROUP BY CID1;
SELECT COUNT(CID1) AS Persons,CID1 FROM students GROUP BY CID1 HAVING Persons>=2;
SQL语句执行的顺序:
start->from->where->group by->having->order by->select fieldN->LIMIT->end
2)多表查询:
连接:
交叉连接:笛卡尔积
select * from students,courses;
自然连接:内连接,外链接:左外连接:... LEFT JOIN ... ON ...,右外连接:... RIGHT JOIN ... ON ...,自连接
select * from students,courses where students.cid1 = courses.cid;
select Name,Cname from students,courses where students.cid1 = courses.cid;
select s.Name,c.Cname from students AS s,courses AS c where s.cid1 = c.cid;
SELECT s.Name,c.Cname FROM students AS s LEFT JOIN courses AS c ON s.CID1=c.CID1;
SELECT s.Name,c.Cname FROM students AS s RIGHT JOIN courses AS c ON s.CID1=c.CID1;
3)子查询:
比较操作中使用子查询:子查询只能返回单个值
IN():使用子查询
在FROM中使用子查询
select name from students where Age > (select AVG(Age) from students);
select name from students where Age IN (select Age from tutors);
select Name,Age from (SELECT Name,Age FROM students) AS t where t.Age >= 20;
4)联合查询:
UNION:
(select Nmae,Age FROM students) UNION (select Tname,Age from tutors);
二、备份和还原
备份:
select * into OUTFILE '/path/beifen.txt' from tb_name [where clause];
恢复:
LOAD DATA INFILE '/path/beifen.txt' INTO TABLE tb_name;
LVM快照备份和还原:
前提:
1、数据文件要在逻辑卷上;
2、此逻辑卷所在卷组必须有足够空间使用快照卷;
3、数据文件和事务日志要在同一个逻辑卷上;
步骤:
1、打开会话,施加读锁,锁定所有表:
mysql> FLUSH TABLES WITH READ LOCK;
mysql> FLUSH LOGS;
2、通过另一个终端,保存二进制日志文件及相关位置信息:
$mysql -uroot -p -e "SHOW MASTER STATUS\G" > /path/master.info
3、创建快照卷:
#lvcreate -L [size] -s -p r -n LV_NAME /path/source_lv
4、释放锁:
mysql> UNLOCK TABLES;
5、挂载快照卷,备份:
#mount LV_NAME /mnt -o ro
#cp -a ./* /backup/
#rm -f /backup/mysql-bin.*
6、删除快照卷:
#lvremove --force LV_NAME
7、增量备份二进制日志[从master.info文件的位置开始备份,如果还有其他滚动的二进制文件单独备份即可]:
#mysqlbinlog --start-post=107 mysql-bin.000003 mysql-bin.000004> /backup/incremental.sql
8、还原数据:
#cp -a /bakcup/* /mysql/data/
#service mysqld start
mysql> set sql_log_bin=0;
mysql> source /backup/incremental.sql;
mysql> set sql_log_bin=1;
mysql> SHOW MASTER STATUS\G;
扩展:
MySQL创建表的三种方式:
1)直接手动创建:
mysql> create table tb_name(feild1-N);
2)基于其他表创建新表,这种方式创建的表不会把原表的主键,约束,触发器等复制过来,需要手动在追加。
mysql> create table test_new as select * from test where 1=2; //where 1=2只有表结构没有数据,where 1=1包括表结构和表数据一起复制了
3)基于原表创建的新表,这种方式使用like关键字会将原表的结构都复制过来,包括主键,约束,触发器等。
mysql> create table test like tb_name;