目录
一、子查询详解
准备表
#建表
create table dep(
id int,
name varchar(20)
);
create table emp(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') not null default 'male',
age int,
dep_id int
);
#插入数据
insert into dep values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营');
insert into emp(name,sex,age,dep_id) values
('egon','male',18,200),
('alex','female',48,201),
('wupeiqi','male',38,201),
('yuanhao','female',28,202),
('liwenzhou','male',18,200),
('jingliyang','female',18,204)
;
#查看表结构和数据
mysql> desc dep;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
mysql> desc emp;
+--------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-----------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
| sex | enum('male','female') | NO | | male | |
| age | int(11) | YES | | NULL | |
| dep_id | int(11) | YES | | NULL | |
+--------+-----------------------+------+-----+---------+----------------+
mysql> select * from dep;
+------+--------------+
| id | name |
+------+--------------+
| 200 | 技术 |
| 201 | 人力资源 |
| 202 | 销售 |
| 203 | 运营 |
+------+--------------+
mysql> select * from emp;
+----+------------+--------+------+--------+
| id | name | sex | age | dep_id |
+----+------------+--------+------+--------+
| 1 | egon | male | 18 | 200 |
| 2 | alex | female | 48 | 201 |
| 3 | wupeiqi | male | 38 | 201 |
| 4 | yuanhao | female | 28 | 202 |
| 5 | liwenzhou | male | 18 | 200 |
| 6 | jingliyang | female | 18 | 204 |
+----+------------+--------+------+--------+
表dep与emp
1.1、子查询in
select * from emp where age=18 or age=38 or age=28;
select * from emp where age in (18,38,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
mysql> select * from dep;
+------+--------------+
| id | name |
+------+--------------+
| 200 | 技术 |
| 201 | 人力资源 |
| 202 | 销售 |
| 203 | 运营 |
+------+--------------+
4 rows in set (0.00 sec)
mysql> insert into emp values(7,'lili','female',48,null);
Query OK, 1 row affected (0.03 sec)
mysql> select * from emp
-> ;
+----+------------+--------+------+--------+
| id | name | sex | age | dep_id |
+----+------------+--------+------+--------+
| 1 | egon | male | 18 | 200 |
| 2 | alex | female | 48 | 201 |
| 3 | wupeiqi | male | 38 | 201 |
| 4 | yuanhao | female | 28 | 202 |
| 5 | liwenzhou | male | 18 | 200 |
| 6 | jingliyang | female | 18 | 204 |
| 7 | lili | female | 48 | NULL |
+----+------------+--------+------+--------+
7 rows in set (0.00 sec)
*例
查询出有员工的部门,
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);
select * from dep where id not in
(select distinct dep_id from emp where dep_id is not null);
1.2、子查询any和all
# any后也跟子查询语句,与in不一样的地方在哪里
# in (子查询语句)
# in (值1,值2,值3)
# 而any只能跟子查询语句
# any必须跟比较运算符配合使用
select * from emp where dep_id in
(select id from dep where name in ("技术","人力资源"));
select * from emp where dep_id = any
(select id from dep where name in ("技术","人力资源"));
select * from emp where dep_id not in
(select id from dep where name in ("技术","人力资源"));
select * from emp where ! (dep_id = any(select id from dep where name in ("技术","人力资源")));
查询出那些薪资比所有部门的平均薪资都高的员工=》薪资在所有部门平均线以上的狗币资本家
select * from employee where salary > all
(select avg(salary) from employee where depart_id is not null group by depart_id);
查询出那些薪资比所有部门的平均薪资都低的员工=》薪资在所有部门平均线以下的无产阶级劳苦大众
select * from employee where salary < all
(select avg(salary) from employee where depart_id is not null group by depart_id);
查询出那些薪资比任意一个部门的平均薪资高的员工=》薪资在任一部门平均线以上的员工
select * from employee where salary > any
(select avg(salary) from employee where depart_id is not null group by depart_id);
select * from employee where salary < any
(select avg(salary) from employee where depart_id is not null group by depart_id);
1.3、子查询exists
# exists vs in
# in的效果 高于 exists
# 见博客:https://www.cnblogs.com/linhaifeng/articles/7267596.html#_label4
select * from 表1 where exists (select * from 表2);
# 例如:查询有员工的部门=》
select * from dep where exists (select * from emp where dep.id=emp.dep_id);
# not exists的效果 高于 not in
select * from dep where not exists (select * from emp where 203=emp.dep_id);
# 例:查询选修了所有课程的学生id、name:
# 实现方式一:选修了三门课程的学生就是选修了所有课程的学生
select s.id,s.name from student as s inner join student2course as sc
on s.id = sc.sid
group by sc.sid
having count(sc.cid) = (select count(id) from course);
# 实现方式二:找到这样的学生,该学生不存在没有选修过的课程
select * from student as s where not exists (
select * from course as c not exists (
select * from student2course as sc where sc.sid = s.id and sc.cid = c.id
)
);
select * from student as s where not exists (
select * from course as c where not exists (
select * from student2course as sc where sc.sid = s.id and sc.cid = c.id
)
);
学生记录可以过滤出来,一定是子查询内没有记录
for 学生: # s.id=2
for 课程: # c.id=1
for 学生2课程: # sc.sid = 2 and sc.cid = 1
pass
==================================
for sid in [1,2,3,4]:
for cid in [1,2,3]:
(sid,cid)
最外层循环一次
# (1,1)
# (1,2)
# (1,3)
最外层循环二次
# (2,1)
# (2,2)
# (2,3)
最外层循环三次
# (3,1)
# (3,2)
# (3,3)
最外层循环四次
# (4,1)
# (4,2)
# (4,3)
===================================
# 例2、查询没有选择所有课程的学生,即没有全选的学生。=》找出这样的学生,存在没有选修过的课程
select * from student as s where exists (
select * from course as c where not exists (
select * from student2course as sc where sc.sid = s.id and sc.cid = c.id
)
);
# 例3、查询一门课也没有选的学生=》找出这样的学生,不存在选修过的课程
select * from student as s where not exists (
select * from course as c where exists (
select * from student2course as sc where sc.sid = s.id and sc.cid = c.id
)
);
# 例4、查询至少选修了一门课程的学生=》找出这样的学生,存在选修过课程
select * from student as s where exists (
select * from course as c where exists (
select * from student2course as sc where sc.sid = s.id and sc.cid = c.id
)
);
二、pymysql模块
#安装
pip3 install pymysql
2.1、 链接、执行sql、关闭(游标)
import pymysql
user=input('用户名: ').strip()
pwd=input('密码: ').strip()
#链接
conn=pymysql.connect(host='localhost',user='root',password='123',database='egon',charset='utf8')
#游标
cursor=conn.cursor() #执行完毕返回的结果集默认以元组显示
#cursor=conn.cursor(cursor=pymysql.cursors.DictCursor)
#执行sql语句
sql='select * from userinfo where name="%s" and password="%s"' %(user,pwd) #注意%s需要加引号
print(sql)
res=cursor.execute(sql) #执行sql语句,返回sql查询成功的记录数目
print(res)
cursor.close()
conn.close()
if res:
print('登录成功')
else:
print('登录失败')
2.2、增、删、改:conn.commit()
import pymysql # pip3 install pymysql
conn = pymysql.connect(host="127.0.0.1", port=3306, user="root", password="123", db="db13", charset="utf8mb4")
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 记录=》增删改
# cursor.execute("insert into student values(%s,%s)",[5,'kkk'])
# cursor.executemany("insert into student values(%s,%s)",[(6,'xxx'),(7,'yyy'),(8,'zzzz')])
cursor.execute("insert into student(name) values(%s)",['zzz'])
print(cursor.lastrowid)
# cursor.execute("delete from student where id > 6;")
# 记录=》查
# rows = cursor.execute('select * from student;')
# print(rows)
# line = cursor.fetchone()
# print(line)
# line = cursor.fetchone()
# print(line)
#
# lines = cursor.fetchmany(2)
# print(lines)
# lines = cursor.fetchall()
# print(lines)
# for line in cursor:
# print(line)
# cursor.scroll(3,mode='absolute') # 相对绝对位置移动
# print(cursor.fetchone())
# print(cursor.fetchall())
# cursor.scroll(0,mode='absolute') # 相对绝对位置移动
# print(cursor.fetchall())
# cursor.fetchone()
# cursor.scroll(1,mode='relative') # 相对当前位置移动
# cursor.scroll(0,mode='absolute') # 相对当前位置移动
# print(cursor.fetchone())
conn.commit()
cursor.close()
conn.close()