子查询、事务、python操作mysql、索引

复习

"""
1、单表查询
	增删改查的完整语法
select distinct 字段 from 表 where   group by   having   order by   limit  

比较：> < =
区间：between and  |  in  |  not in
逻辑: and  or  not
相似：like  _%  “转义字符是 \ , 也可以在后面加上escape “你自己定的转义字符”  ”
正则：regexp

聚合函数：group_concat()、max()

having：可以对 聚合函数 结果进行筛选，不能使用 聚合函数 别名
order by：分组后对 聚合函数 进行排序，能使用 聚合函数 别名
limit：条数 | 偏移,条数


2、多表查询
内连接：from emp inner join dep on emp.dep_id = dep.id  只保存两表有对应关系的记录
左连接：from emp left join dep on emp.dep_id = dep.id   左表记录全部保存，右边没有对应记录空填充
右连接：from emp right join dep on emp.dep_id = dep.id  右表记录全部保存，左边没有对应记录空填充
全连接：
from emp left join dep on emp.dep_id = dep.id
union
from emp right join dep on emp.dep_id = dep.id
"""

案例

# 按指定编码创建数据库
create database 数据库名 charset="编码格式";
# 修改数据库编码
alter database 数据库名 charset="编码格式";

# 修改字段
alter table 库.表 modify 字段 类型(长度) 约束;
alter table 库.表 change 旧字段 新字段 类型(长度) 约束;

# 创建一个学生student表，有主键id字段，name唯一字段、age字段、height字段、mobile字段
create table student(
	id int not null auto_increment,
    name varchar(64) unique not null,
    age int unsigned default 0,
    height decimal(5,2) unsigned not null,
    mobile char(11),
    primary key(id),
    unique(name, mobile)
);
truncate student;

# 字段的增加
alter table 表名 add 字段 类型(长度) 约束 
alter table 表名 add 字段 类型(长度) 约束 first
alter table 表名 add 字段 类型(长度) 约束 after 已有字段

# 移动字段
alter table 表名 modify 字段 类型(长度) 约束 first

# 将 学生表、国家表、课程表、学生简介表 四个表放在一起考虑表关系，并完成数据的增删测试
create table country(
	id int,
    name varchar(16)
);
create table student(
	id int,
    name varchar(16),
    country_id int,
    foreign key(country_id) references country(id)
    on update cascade
    on delete cascade
);
create table student_info(
	id int,
    info varchar(256),
    student_id int unique,
    foreign key(student_id) references student(id)
    on update cascade
    on delete cascade
);

create table course(
    id int,
    name varchar(16)
);
create table student_course(
	id int,
    student_id int,
    course_id int,
    foreign key(student_id) references student(id)
    on update cascade
    on delete cascade,
    foreign key(course_id) references course(id)
    on update cascade
    on delete cascade
)

CREATE TABLE `emp`  (
  `id` int(0) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) NOT NULL,
  `gender` enum('男','女','未知') NULL DEFAULT '未知',
  `age` int(0) NULL DEFAULT 0,
  `salary` decimal(5,2) NULL DEFAULT 0,
  `area` varchar(20) NULL DEFAULT '中国',
  `port` varchar(20) DEFAULT '未知',
  `dep` varchar(20),
  PRIMARY KEY (`id`)
);

INSERT INTO `emp` VALUES 
	(1, 'yangsir', '男', 42, 10.50, '上海', '浦东', '教职部'),
	(2, 'engo', '男', 38, 9.4, '山东', '济南', '教学部'),
	(3, 'jerry', '女', 30, 3.0, '江苏', '张家港', '教学部'),
	(4, 'tank', '女', 28, 2.4, '广州', '广东', '教学部'),
	(5, 'jiboy', '男', 28, 2.4, '江苏', '苏州', '教学部'),
	(6, 'zero', '男', 18, 8.8, '中国', '黄浦', '咨询部'),
	(7, 'owen', '男', 18, 8.8, '安徽', '宣城', '教学部'),
	(8, 'jason', '男', 28, 9.8, '安徽', '巢湖', '教学部'),
	(9, 'ying', '女', 36, 1.2, '安徽', '芜湖', '咨询部'),
	(10, 'kevin', '男', 36, 5.8, '山东', '济南', '教学部'),
	(11, 'monkey', '女', 28, 1.2, '山东', '青岛', '教职部'),
	(12, 'san', '男', 30, 9.0, '上海', '浦东', '咨询部'),
	(13, 'san1', '男', 30, 6.0, '上海', '浦东', '咨询部'),
	(14, 'san2', '男', 30, 6.0, '上海', '浦西', '教学部');

# 查询教学部山东人的平均薪资
# 1、查谁 2、从哪查 3、条件是啥
select avg(salary) from emp where dep='教学部' and area='山东';
select avg(salary) from emp where dep='教学部' group by area having area='山东';
select avg(salary) from emp where area='山东' group by dep having dep='教学部';

# 查询姓名中包含英文字母n并且居住在上海的人的所有信息
select * from emp where name like '%n%' and area='上海';

# 查询姓名中包含英文字母n但不包含数字的人的所有信息
select * from emp where name like '%n%' and name not regexp '.*[0-9].*';

# 查看各部门的平均年龄并降序排序
select dep, avg(age) from emp group by dep order by avg(age) desc;

# 查询各部门中年纪最大的人的姓名与居住地（户籍+区域）
select max(age), dep from emp group by dep;

select name, concat_ws('-', area, port) from emp 
where (age, dep) in (('36', '咨询部'),('38', '教学部'),('42', '教职部'));

select name, concat_ws('-', area, port) from emp 
where (age, dep) in (select max(age), dep from emp group by dep);

# 查询不同年龄层次平均薪资大于5w组中工资最高者的姓名与薪资
select age, max(salary) from emp group by age having avg(salary) > 5;

select name, salary from emp where (age, salary) in (select age, max(salary) from emp group by age having avg(salary) > 5);

create table dep(
	id int primary key auto_increment,
	name varchar(16),
	work varchar(16)
);
create table emp(
	id int primary key auto_increment,
	name varchar(16),
	salary float,
	dep_id int
);
insert into dep values(1, '市场部', '销售'), (2, '教学部', '授课'), (3, '管理部', '开车');
insert into emp(name, salary, dep_id) values('egon', 3.0, 2),('yanghuhu', 2.0, 2),('sanjiang', 10.0, 1),('owen', 88888.0, 2),('liujie', 8.0, 1),('yingjie', 1.2, 0);


# 查询每一个部门下的员工们及员工职责
# 1、查谁 2、从哪查，信息量不够连表 3、条件是啥，如果存在分组（不能直接查询的字段聚合处理 | 将要查询的字段添加进分组）
select group_concat(emp.name), work, dep.name from emp right join dep on emp.dep_id=dep.id group by emp.dep_id, work, dep.name;

select group_concat(emp.name) 员工们, max(work) 工作职责, max(dep.name) 部门 from emp right join dep on emp.dep_id=dep.id group by emp.dep_id;

select group_concat(emp.name) 员工们, max(work) 工作职责, dep.name 部门 from emp right join dep on emp.dep_id=dep.id group by dep.name;

联合分组

# 数据来源：在单表emp下

# 联合分组：按多个字段综合结果进行分组

# 按 area与port组合后的结果进行分组，只有组合后的结果还一致，才认为是一组
select group_concat(name),area,port from emp group by area,port;

子查询

# 增：insert into 表 select子查询
# 删：delete from 表 条件是select子查询(表不能与delete表相同)
# 查：select 字段 from 表 条件是select子查询
# 改：update 表 set 字段=值 条件是select子查询(表不能与update表相同)

# 数据来源：在单表emp下

# 子查询：将一条查询sql的结果作为另一条sql的条件

# 思考：每个部门最高薪资的那个人所有信息

# 子查询的sql
select dep, max(salary) from emp group by dep;
# 子查询 - 查
select * from emp where (dep, salary) in (select dep, max(salary) from emp group by dep);

# 将子查询转换为一张表
# 创建一个存子查询数据的一张表
create table t1(dep_name varchar(64), max_salary decimal(5,2));
# 子查询 - 增
insert into t1 select dep, max(salary) from emp group by dep;
# 需求
select name, dep_name, salary 
from emp join t1 
on emp.dep=t1.dep_name and emp.salary=t1.max_salary;

# 子查询 - 改（update更新的表不能 与 子查询select的表同表）
# 每个部门最大薪资+1
update t1 set max_salary=max_salary+1;
# 给t1额外增加一个新部门
insert into t1 values ('打杂部', 100);
# 子查询 - 改
update t1 set max_salary=max_salary+1 where dep_name in (select distinct dep from emp);
# 错误：update更新的表 与 子查询select的表 相同
update t1 set max_salary=max_salary+1 where dep_name in (select distinct dep_name from t1);

# 子查询 - 删
delete from t1 where dep_name in (select distinct dep from emp);
# 错误: delete删除的表 与 子查询select的表 相同
delete from t1 where dep_name in (select distinct dep_name from t1);

all与any：区间修饰条件

# 语法规则
# where id in (1, 2, 3) => id是1或2或3
# where id not in (1, 2, 3) => id不是1，2，3
# where salary < all(3, 6, 9) => salary必须小于所有情况（小于最小）
# where salary > all(3, 6, 9) => salary必须大于所有情况（大于最大）
# where salary < any(3, 6, 9) => salary只要小于一种情况（小于最大）
# where salary > any(3, 6, 9) => salary只要大于一种情况（大于最小）
in < > ()
# 案例
select * from emp where salary < all(select salary from emp where id>11);

视图：view

# 数据依赖：单表emp
"""
1）视图是存在内存中的临时表
2）视图的创建依赖select语句，所有就是select语句操作的结果形参的表
3）视图支持对数据的增删查改 ?
4）视图不允许对视图表的字段做修改
5）视图不仅支持创建，也支持更新与删除
"""
# 语法
# 创建视图
mysql>: create view 视图名[(别名们)] as select 语句;
eg>: create view v1 as select dep, max(salary) from emp group by dep;

# 创建或替换视图
mysql>: create or replace 视图名[(别名们)] as select 语句;
mysql>: alter 视图名[(别名们)] as select 语句;
eg>: create or replace view v1(dep_name, max_salary) as select dep, max(salary) from emp group by dep;
eg>: alter view v1(name, salary) as select dep, max(salary) from emp group by dep;

# 删除视图
mysql>: drop view 视图名
eg>: drop view v1;

# 视图可以作为正常表完成连表查询
select name, dep_name, salary 
from emp join v1 
on emp.dep=v1.dep_name and emp.salary=v1.max_salary;

视图的增删改

# 前提：视图的增删改操作可以直接映射给真实表（本质就是对真实表进行操作）

# 视图可以完成增删改，增删改本质是直接对创建视图的真实表进行操作
create or replace view v2 as select id,name,age,salary from emp;
update v2 set salary=salary+1 where id=1;
delete from v2 where id=1;

create or replace view v3 as select * from emp;
insert into v3 values(1, 'yangsir', '男', 66, 1.11, '上海', '那噶的', '教职部');

# 总结：操作视图，会影响真实表，反之也会影响
update emp set salary=salary+1 where id=1;

事务

# 事务：通常一些业务需要多条sql参与，参与的sql会形参一个执行整体，该整体我们就称之为 事务
# 简而言之：事务 - 就是保护多条执行的sql语句
# 比如：转账就是一个事务：从一个用户将资金转出，再将资金转入到另一个用户

""" 事务的四大特性
1.原子性：事务是一组不可分割的单位,要么同时成功,要么同时不成功
2.一致性：事物前后的数据完整性应该保持一致(数据库的完整性：如果数据库在某一时间点下，所有的数据都符合所有的约束，则称数据库为完整性的状态)
3.隔离性：事物的隔离性是指多个用户并发访问数据时，一个用户的事物不能被其它用户的事务所干扰，多个并发事务之间数据要相互隔离
4.持久性：持久性是指一个事物一旦被提交，它对数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响
"""

# mysql中事务的执行
create table bank(
	id int,
    name varchar(16),
    money decimal(65, 2)
);
insert into bank values(1, 'Tom', 10), (2, "Bob", 10);

# 假设出现以下执行情况

# 没有事务支持情况下，Tom的钱就丢了
update bank set money=money-1 where name='Tom';
update bank set money=money+1 where name='ruakei';

# 将两条sql看做事务处理
# 开启事务
begin;
update bank set money=money-1 where name='Tom';
update bank set money=money+1 where name='ruakei';
# 确认无误，提交事务
commit;
# 确认有误，回滚
rollback;

pymysql：python操作mysql

安装

>: pip3 install pymysql

增删改查

# 选取操作的模块 pymysql

# pymysql连接数据库的必要参数：主机、端口、用户名、密码、数据库
# 注：pymysql不能提供创建数据库的服务，数据库要提前创建
import pymysql

# 1）建立数据库连接对象 conn
# 2）通过 conn 创建操作sql的 游标对象
# 3）编写sql交给 cursor 执行
# 4）如果是查询，通过 cursor对象 获取结果
# 5）操作完毕，端口操作与连接


# 1）建立数据库连接对象 conn
conn = pymysql.connect(user='root', passwd='root', database='oldboy')
# conn = pymysql.connect(user='root', passwd='root', database='oldboy', autocommit=True)

# 2）通过 conn 创建操作sql的 游标对象
# 注：游标不设置参数，查询的结果就是数据元组，数据没有标识性
# 设置pymysql.cursors.DictCursor，查询的结果是字典，key是表的字段
cursor = conn.cursor(pymysql.cursors.DictCursor)

# 3）编写sql交给 cursor 执行

创建表

# 创建表
sql1 = 'create table t1(id int, x int, y int)'
cursor.execute(sql1)

增

sql2 = 'insert into t1 values(%s, %s, %s)'

# 增1
cursor.execute(sql2, (1, 10, 100))
cursor.execute(sql2, (2, 20, 200))
# 重点：在创建conn对象时，不设置autocommit，默认开启事务，增删改操作不会直接映射到数据库中，
# 需要执行 conn.commit() 动作
conn.commit()

# 增多
cursor.executemany(sql2, [(3, 30, 300), (4, 40, 400)])
conn.commit()

删

sql3 = 'delete from t1 where id=%s'
cursor.execute(sql3, 4)
conn.commit()

改

sql4 = 'update t1 set y=666 where id=2'
cursor.execute(sql4)
conn.commit()

查

sql5 = 'select * from t1'
row = cursor.execute(sql5)  # 返回值是受影响的行
print(row)

# 4）如果是查询，通过 cursor对象 获取结果
# fetchone() 偏移一条取出，fetchmany(n) 偏移n条取出，fetchall() 偏移剩余全部
r1 = cursor.fetchone()
print(r1)
r2 = cursor.fetchone()
print(r2)
r3 = cursor.fetchmany(1)
print(r3)
r4 = cursor.fetchall()
print(r4)

# 5）操作完毕，端口操作与连接
cursor.close()
conn.close()

游标操作

import pymysql
from pymysql.cursors import DictCursor

# 1）建立数据库连接对象 conn
conn = pymysql.connect(user='root', passwd='root', db='oldboy')
# 2）通过 conn 创建操作sql的 游标对象
cursor = conn.cursor(DictCursor)
# 3）编写sql交给 cursor 执行
sql = 'select * from t1'
# 4）如果是查询，通过 cursor对象 获取结果
row = cursor.execute(sql)
if row:
    r1 = cursor.fetchmany(2)
    print(r1)

    # 操作游标
    # cursor.scroll(0, 'absolute')  # absolute绝对偏移，游标重置，从头开始偏移
    cursor.scroll(-2, 'relative')  # relative相对偏移，游标在当前位置进行左右偏移

    r2 = cursor.fetchone()
    print(r2)

# 5）操作完毕，端口操作与连接
cursor.close()
conn.close()

pymysql事务

import pymysql
from pymysql.cursors import DictCursor
conn = pymysql.connect(user='root', passwd='root', db='oldboy')
cursor = conn.cursor(DictCursor)

try:
    sql = 'create table t2(id int, name char(4), money int)'
    row = cursor.execute(sql)
    print(row)
except:
    print('表已创建')
    pass

# 空表才插入
row = cursor.execute('select * from t2')
if not row:
    sql = 'insert into t2 values(%s,%s,%s)'
    row = cursor.executemany(sql, [(1, 'tom', 10), (2, 'Bob', 10)])
    conn.commit()


# 可能会出现异常的sql
"""
try:
    sql1 = 'update t2 set money=money-1 where name="tom"'
    cursor.execute(sql1)
    sql2 = 'update t2 set moneys=money+1 where name="Bob"'
    cursor.execute(sql2)
except:
    print('转账执行异常')
    conn.rollback()
else:
    print('转账成功')
    conn.commit()
"""

try:
    sql1 = 'update t2 set money=money-1 where name="tom"'
    r1 = cursor.execute(sql1)
    sql2 = 'update t2 set money=money+1 where name="ruakei"'  # 转入的人不存在
    r2 = cursor.execute(sql2)
except:
    print('转账执行异常')
    conn.rollback()
else:
    print('转账没有异常')
    if r1 == 1 and r2 == 1:
        print('转账成功')
        conn.commit()
    else:
        conn.rollback()

sql注入

import pymysql
from pymysql.cursors import DictCursor
conn = pymysql.connect(user='root', passwd='root', db='oldboy')
cursor = conn.cursor(DictCursor)

try:
    sql = 'create table user(id int, name char(4), password char(6))'
    row = cursor.execute(sql)
    print(row)
except:
    print('表已创建')
    pass

# 空表才插入
row = cursor.execute('select * from user')
if not row:
    sql = 'insert into user values(%s,%s,%s)'
    row = cursor.executemany(sql, [(1, 'tom', '123'), (2, 'bob', 'abc')])
    conn.commit()



# 用户登录
usr = input('usr: ')
pwd = input('pwd: ')

# 自己拼接参数一定有sql注入，将数据的占位填充交给pymysql

"""
sql = 'select * from user where name="%s" and password="%s"' % (usr, pwd)
row = cursor.execute(sql)
if row:
    print('登录成功')
else:
    print('登录失败')
"""
sql = 'select * from user where name=%s and password=%s'
row = cursor.execute(sql, (usr, pwd))
if row:
    print('登录成功')
else:
    print('登录失败')


# 知道用户名时
# 输入用户时：
#   tom => select * from user where name="tom" and password="%s"
#   tom" # => select * from user where name="tom" #" and password="%s"

# 不自定义用户名时
#   " or 1=1 # => select * from user where name="" or 1=1 #" and password="%s"

索引

# 索引就是 键 - key

"""
1）键 是添加给数据库表的 字段 的
2）给表创建 键 后，该表不仅会形参 表结构、表数据，还有 键的B+结构图
3）键的结构图是需要维护的，在数据完成增、删、改操作时，只要影响到有键的字段，结构图都要维护一次
    所以创建键后一定会降低 增、删、改 的效率
4）键可以极大的加快查询速度(开发需求中，几乎业务都和查有关系)
5）建立键的方式：主键、外键、唯一键、index
"""

import pymysql
from pymysql.cursors import DictCursor
conn = pymysql.connect(user='root', passwd='root', db='oldboy')
cursor = conn.cursor(DictCursor)

# 创建两张表
# sql1 = """create table a1(
#     id int primary key auto_increment,
#     x int,
#     y int
# )"""
# cursor.execute(sql1)
# sql2 = """create table a2(
#     id int primary key auto_increment,
#     x int,
#     y int,
#     index(x)
# )"""
# cursor.execute(sql2)

# 每个表插入5000条数据
# import random
# for i in range(1, 5001):
#     x = i
#     y = random.randint(1, 5000)
#     cursor.execute('insert into a1(x, y) values(%s, %s)', (x, y))
#     cursor.execute('insert into a2(x, y) values(%s, %s)', (x, y))
#
# conn.commit()

import time
# a1的x、a1的id、a2的x
b_time = time.time()
sql = 'select * from a1 where id=4975'
cursor.execute(sql)
e_time = time.time()
print(e_time - b_time)

b_time = time.time()
sql = 'select * from a1 where x=4975'
cursor.execute(sql)
e_time = time.time()
print(e_time - b_time)

b_time = time.time()
sql = 'select * from a2 where x=4975'
cursor.execute(sql)
e_time = time.time()
print(e_time - b_time)