2019.9.26学习内容及小结

复习

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

比较： > < =
区间: between and  |  in  | not in
逻辑: and or not
相似: like  _% (一个 _ 代表 一个任意字符， % 代表不限制数量任意字符)
正则: regexp '.*[0-9]'  表示包含数字[0-9]，用 not regexp '.*[0-9]' 就是不包含数字

聚合函数： group_concat()、max()、min()等；注：group_concat就是将字段合在一起，用于group by的拼接，用法和直接concat是一样的     也可以用于直接加上某条字段：group_concat(name) 名字， 来用于group by 后面没有字段的情况

having: 可以对 聚合函数  结果进行筛选，不能使用 聚合函数 别名
order by: 分组后对 聚合函数 进行排序，能使用 聚合函数 别名
limit: 条数 |  偏移量，条数 如limit(5,3), 过滤掉前五条数据，取出之后三条数据

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 : 右表记录全部保留，左表没有对应的记录空填充

全连接：就是把 左连接 和 右连接 通过关键字 union（联盟） 给合并起来就是全连接
from emp left join dep on emp.dep_id = dep.id
union(联盟)
from emp left join dep on emp.dep_id = dep.id

案列

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

# 修改字段
alter table 库.表 modify 字段 类型(长度) 约束;
alter table db1.t1 modify name char(10);

alter table 库.表 change 旧字段 新字段 类型(长度) 约束;
alter table db1.t1 change name phone int();

# 创建一个学生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;

注： group_concat()用法含义
首先先有group by（x,y) 联合分组，联合分组后得到联合分组的信息，但是现在想要查询联合分组外的信息，就要用到group_concat()，因为某个人除了有联合分组的字段及对应信息外 还有其它字段及所对应的信息，我们就可以通过group_concat()括号内放入其他字段来查询出满足条件的信息

子查询

# 增： 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 ti set max_salary=max_salary + 1;

# 给t1额外添加一个部门
insert into t1 values('打杂部',100);
# 子查询 - 改 (更改与emp表相关的字段数据)
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);

# 子查询 - 删 ((删除与emp表相关的字段数据))
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 语句;
      ：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)