day46

（1）子查询

　　　　in / not in

　　　　any

　　　　all

　　　　exists

（2）pymysql模块

　　　　记录的增删改查

　　　　增：insert 表名 values（值）；

　　　　删：delete from 表名 where 条件

　　　　改：update 表名 set 字段='...' where 条件

　　　　查：select * from 表名

　　　　　　fetchone()

　　　　　　fetchall()

　　　　　　fetchmany()

　　　　游标的移动：

　　　　　　cursor.scroll()

子查询
# in  /  not in
select * from emp where age in(18,16,28);
select * from emp where dep_id in (select id from dep where name='技术' or name='销售');
select * from emp inner join dep on emp.dep_id=dep.id where dep.name in ('技术','销售');    # 通过链表查询

# not in 不支持 null
insert emp values(7,'Bron','female',16,null);       -- 插入数据
select * from dep where id in (select distinct dep_id from emp);    # 查询出有员工的部门
select * from dep where id not in (select distinct dep_id from emp);    # 查询出没有员工的部门
# 结果本应该是203，因not in不支持null，这里会输出Empty
# 解决：先在子查询中将 null 过滤，再进行not in查询
select * from dep where id not in (select distinct dep_id from emp where dep_id is not null);


# any
# any 与 in 的不同之处在于：in后可直接跟值或子查询语句，而any只能跟子查询语句，并且必须与比较运算符配合使用
select * from emp where dep_id = any (select id from dep where name='技术' or name='销售');
select * from emp where !(dep_id = any (select id from dep where name='技术' or name='销售'));

# all
# 查询出薪资比所有部门的平均薪资都高的员工
select * from employee where salary > all (select avg(salary) from employee group by depart_id);
# 查询出那些薪资比所有部门的平均薪资都低的员工
select * from employee where salary < all (select avg(salary) from employee group by depart_id);
# 查询出那些薪资比任意一个部门的平均薪资高的员工
select * from employee where salary > any (select avg(salary) from employee group by depart_id);


# exists
# select * from 表 where exists(子查询语句);
# in 的效率高于 exists, not exists的效率高于not in

# exists后面一般都是子查询，后面的子查询被称做相关子查询（即与主语句相关），当子查询返回行数时，exists条件返回true，
# 否则返回false，exists是不返回列表的值的，exists只在乎括号里的数据能不能查找出来，是否存在这样的记录。
# exists的执行原理为：
# 1、依次执行外部查询：即select * from class
# 2、然后为外部查询返回的每一行分别执行一次子查询：即(select * from stu where stu.cid=class.cid)
# 3、子查询如果返回行，则exists条件成立，条件成立则输出外部查询取出的那条记录
# in的执行原理为：
# in()的执行次序和exists()不一样,in()的子查询会先产生结果集,然后主查询再去结果集里去找符合要求的字段列表去.符合要求的输出,反之则不输出

# 查询有员工的部门
select * from dep where exists(select * from emp where dep_id = dep.id);


# ex:
create database db13;
use db13;

create table student(
    id int primary key auto_increment,
    name varchar(16)
);

create table course(
    id int primary key auto_increment,
    name varchar(16),
    comment varchar(20)
);

create table student2course(
    id int primary key auto_increment,
    sid int,
    cid int,
    foreign key(sid) references student(id),
    foreign key(cid) references course(id)
);

insert into student(name) values("Welt"),("Joyce"),("Jack"),("Tom");
insert into course(name,comment) values("数据库","MySQL"),("数学","MATH"),("英语","ENGLISH");
insert into student2course(sid,cid) values(1,1),(1,2),(1,3),(2,1),(2,2),(3,2);

# 查询选修了所有课程的学生
# 方法一
select s.id,s.name from student as s inner join student2course as s2c on s.id = s2c.sid
group by s.id
having count(s.id)=(select count(course.id) from course);
# 方法二   （学生不存在没有选修过的课程）
select s.id,s.name from student as s where not exists(
    select * from course as c where not exists(
        select * from student2course as s2c where s2c.sid=s.id and s2c.cid=c.id
        )
    );

# 查询没有选择所有课程的学生，即没有全选的学生    （存在没有选修过的课程）
select * from student as s where exists(
    select * from course as c where not exists(
        select * from student2course as s2c where s2c.sid=s.id and s2c.cid=c.id
        )
    );
# 查询一门课也没有选的学生          （不存在选修过的课程）
select * from student as s where not exists(
    select * from course as c where exists(
        select * from student2course as s2c where s2c.sid=s.id and s2c.cid=c.id
        )
    );
# 查询至少选修了一门课程的学生        （存在选修过的课程）
select * from student as s where exists(
    select * from course as c where exists(
        select * from student2course as s2c where s2c.sid=s.id and s2c.cid=c.id
                                        )
                                     );

pymysql
import pymysql

conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='2499', database='pycharmdata',
                       charset='utf8mb4')

cursor = conn.cursor()

# 记录=>增删改查
# 增
# cursor.execute('insert into user values(%s,%s)', [8, 'ddd'])          # 执行一条
# cursor.executemany('insert user values(%s,%s)', ((9, 'eee'), (10, 'jjj')))    # 执行多条
# cursor.execute('insert user(name) values(%s)', ('ttt'))

# 删
# cursor.execute('delete from user where id>9')         # 删除记录 delete from 表名 where 条件

# 改
# cursor.execute('update user set name="xxx" where id=7')     # 修改记录 update 表名 set 字段=‘...’ where 条件

# 查
rows = cursor.execute('select * from user')
# for line in range(rows):
#     print(cursor.fetchone())        # fetchone一次只查询一条记录
# print(cursor.fetchall())        # fetchall查询全部记录
# print(cursor.fetchmany(3))        # fetchmany查询指定条数的记录


# cursor.scroll(2, mode='absolute')     # 游标相对于绝对位置移动
# print(cursor.fetchone())  # (7, 'xxx')
cursor.fetchone()
cursor.scroll(2, mode='relative')       # 游标相对于当前位置移动
print(cursor.fetchone())    # (8, 'ddd')


conn.commit()
cursor.close()
conn.close()