mysql高度进阶

1.子查询

# 1.子查询
语句中嵌套其他查询语句, 其中的一个查询语句的查询结果作为另一个语句的一部分使用

# 2.子查询分类: 了解

# 子查询简单案例(引入)
	select * from stu where max(age);  # 查询年两最大的学生的所有信息, 发生错误
    # 思路:
    1).先查询出学生中年龄最大的那个的年龄值
    2).将该年龄作为筛选条件嵌入到另一条查询语句中
    # 实现:
    select * from stu where age=(select max(age) from stu);

# 子查询数据准备:
class表: id, name
student表: id, name, age, height, cls_id
insert into class values (1, '2005A'), (2, '2004A'), (3, '2003A'), ('4', '2002A');
insert into student values
(1, 'lcj', 20, 170, 1),
(2, 'lcc', 20, 175, 2),
(3, 'lqq', 18, 165, 3),
(4, 'hzp', 20, 190, 4),
(5, 'fz', 18, 175, 1);

# 子查询详解:
1.简单: 
    (1).语句示例: select * from stu where age=(select max(age) from stu);
    (2).说明: 先查询出stu表中最大的年龄, 在查询出表中最大年龄的学生信息
2.in关键字: 
    (1).语句示例: select name from class where id in (select cls_id from student);
    (2).说明: 先查询出学生表中所有学生的班级id, 再查询出班级表中所有的班级名称
3.any, some关键字:
    (1).语句示例: select * from student where age < any (select age from student where height > 175);
    (2).说明:查询子句是筛选出身高大于175的所有学生的年龄, 然后再次从学生表中筛选出年龄大于查询子句结果中的任何年龄的数据.
        select age from student where height > 175: 175身高以上学生的年龄-->20
            
4.all关键字:
    (1).语句示例: select * from student where height > all (select height from student where age <=18);
    (2).说明: 先查询出student表中年龄小于等于18的学生的身高, 再查询student表中身高大于任何以上身高结果的所有数据.
5.exists关键字:
    (1).语句示例: select * from student where exists (select age from student where height > 185);
    (2).说明: 先查询student表中身高大于185的学生的年龄, 如果查询有结果, 则select主句继续执行, 如果查询为空, 则主句不再执行.

---

2.事务

# 1.事务的引入:
银行转账

# 2.事务:
所谓事务,它是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位。

# 3.事务的特性:
1.原子性（atomicity）:
    一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作，这就是事务的原子性
2.一致性（consistency）:
    数据库总是从一个一致性的状态转换到另一个一致性的状态。（在前面的例子中，一致性确保了，即使在执行第三、四条语句之间时系统崩溃，支票账户中也不会损失200美元，因为事务最终没有提交，所以事务中所做的修改也不会保存到数据库中。）
3.隔离性（isolation）:
    通常来说，一个事务所做的修改在最终提交以前，对其他事务是不可见的。（在前面的例子中，当执行完第三条语句、第四条语句还未开始时，此时有另外的一个账户汇总程序开始运行，则其看到支票帐户的余额并没有被减去200美元。）
4.持久性（durability）:
    一旦事务提交，则其所做的修改会永久保存到数据库。（此时即使系统崩溃，修改的数据也不会丢失。）

# 4.事务的提交与回滚: python代码中实现
提交: commit
回滚: rollback

---

---

---

day15 — 多表查询, pymysql

1.连接查询

# 1.1 多表查询
当查询的字段来自多张表时即为多表查询, 我们使用连接查询

# 1.2 连接查询分类:
SQL92: ....
SQL99:
    1.内连接: *****
    2.外连接: 
        左外连接*****
        右外连接*****
        全外连接(*)
    3.交叉连接: 存在的意义不大, 笛卡尔积.*

# 1.3 内连接
1.内连接语法:
    select 字段 from 表1
    inner join 表2
    on 连接条件;
2.作用: 取出表1与表2的匹配部分, 不相匹配的数据丢弃掉
3.语句示例:
# SQL1:查询出所有有部门的员工的姓名, 年龄和部门名称.
SELECT e.name, e.age, d.name FROM employ e
INNER JOIN department d
ON e.dp_id=d.id;

# SQL2:
SELECT e.name, e.age, d.name FROM department d
INNER JOIN employ e
ON e.dp_id=d.id;
# 说明:
查询员工姓名, 年龄和对应的部门名称, 如果员工不属于department的任何一个部分或者department中的部门没有人存在则丢弃掉.

# 内连接 --- 自关联
1.啥是自关联: 只可意会不可言传
	自关联: 在表设计中有这样一种情况, 比如一张表是员工表, 员工表中多出一个字段, 代表该员工的上级领导是谁, 那这个员工的领导也是员工, 自然会关联员工的id, 所以一张表中的数据关联这张表的其他数据就是自关联.
2.自关联: 表设计
employ1:
    id: int primary key,
    name: varchar(32), 
    age: int,
    pid: int 上级的id
# 创建表
CREATE TABLE employ1 (
id INT PRIMARY KEY,
NAME VARCHAR(32),
age INT,
pid INT
);
# 插入数据
INSERT INTO employ1 VALUES 
(1, 'laowang', 84, NULL),
(2, 'laoyu', 18, 1),
(3, 'benben', 38, 1),
(4, 'lcj', 20, 2),
(5, 'hzp', 84, 3);

# 查询: 查询所有有领导的员工的姓名和其上级领导的姓名
SELECT e.name AS '员工', m.name AS '领导' FROM employ1 AS e
INNER JOIN employ1 AS m
ON e.pid = m.id;

# 1.4 左外连接:
1.左外连接语法:
	select 字段 from 表1 left join 表2 on 连接条件; 
2.作用: 左表作为主表(表1), 根据主表的数据进行查询, 当主表在从表中找不到匹配项则使用null填充
3.语句示例:
	SELECT e.name, e.age, d.name FROM employ e LEFT JOIN department d ON e.`dp_id`=d.id;
# 思考: 哪张表是主表呢?
# 说明:
从左表employ中查询出所有员工的姓名, 年龄, 再查询出各个员工对应的部分名称, 如果department中没有该员工的部分则使用null填充, 但需要保证员工都在.

# 1.5 右外连接
1.左外连接语法:
	select 字段 from 表1
	right join 表2
	on 连接条件; 
2.作用: 右表作为主表(表2), 根据右表的数据进行查询, 当右表在左表中找不到匹配项则使用null填充
3.语句示例:
    SELECT e.name, e.age, d.name FROM employ e RIGHT JOIN department d ON e.`dp_id`=d.id;
4.说明: 以右表为主表

# 1.6 全外连接
1.内连接结果
2.取出左外连接
3.取出右外连接
select 字段
from 表1
full join 表2
on 连接条件;

---

2.pymysql介绍

# 1.pymysql
pymysql是python提供给用户的一个操作mysql数据库的接口模块, 该模块提供了各种方法来操作mysql数据库

# 2.pymysql的安装
pip install pymysql -i 清华源网址

# 3.pymysql的大致使用
# 1.导包
import pymysql

# 2.打开数据库连接
conn = pymysql.connect(host='localhost', user='root', password="root",
        database='db', port=3306, charset='utf-8') 
# 3.获取游标
cursor = conn.cursor()
# 4.执行SQL语句
cursor.execute("SELECT VERSION()") 

conn.close()

---

3.pymysql详细使用

cursor.execute("DROP TABLE IF EXISTS 表名")
 
# 使用预处理语句创建表
sql = """CREATE TABLE 表名 (
         字段名 字段类型 字段完整性约束,
         ...
        )"""
 # 执行SQL语句
cursor.execute(sql)

# 2.1数据操作---增加
1.事务的提交与回滚
conn.commit(): 提交事务
conn.rollback(): 事务回滚
2.插入数据
sql = """
	insert into 表名  字段名 values 字段值
"""
try:
    # 执行SQL语句
	cursor.execute(sql)
    # 提交事务
	conn.commit
except:
    # 回滚
    conn.rollback()

# 2.2数据操作---查询
sql = """
	select * from 表名
"""
cursor.execute(sql)
# 该方法获取下一个查询结果集。结果集是一个对象
cursor.fetchone()
# 接收全部的返回结果行
cursor.fetchall()  # 可以遍历

# 2.4数据操作---更新
sql = """UPDATE 表名 SET 字段名 = 字段值 WHERE 条件"""
try:
   cursor.execute(sql)  # 执行SQL语句
   db.commit()          # 提交到数据库执行
except
   db.rollback() 

# 2.5数据操作---删除
sql = """DELETE FROM 表名 WHERE 条件"""
try
   cursor.execute(sql)  # 执行SQL语句
   db.commit()          # 提交修改
except
   db.rollback()        # 发生错误时回滚

---

3.数据库备份与恢复

#语法：
# mysqldump -h 服务器 -u用户名 -p密码 数据库名 > 备份文件.sql

#示例：
#单库备份
mysqldump -uroot -p123 db1 > db1.sql
mysqldump -uroot -p123 db1 table1 table2 > db1-table1-table2.sql

#多库备份
mysqldump -uroot -p123 --databases db1 db2 mysql db3 > db1_db2_mysql_db3.sql

#备份所有库
mysqldump -uroot -p123 --all-databases > all.sql

#方法一：
[root@egon backup]# mysql -uroot -p123 < /backup/all.sql

#方法二：
mysql> use db1;
mysql> SET SQL_LOG_BIN=0;   #关闭二进制日志，只对当前session生效
mysql> source /root/db1.sql