day45
一.多表查询
建表
#建表
create table department(
id int,
name varchar(20)
);
create table employee(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') not null default 'male',
age int,
dep_id int
);
#插入数据
insert into department values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营');
insert into employee(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 department;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
mysql> desc employee;
+--------+-----------------------+------+-----+---------+----------------+
| 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 department;
+------+--------------+
| id | name |
+------+--------------+
| 200 | 技术 |
| 201 | 人力资源 |
| 202 | 销售 |
| 203 | 运营 |
+------+--------------+
mysql> select * from employee;
+----+------------+--------+------+--------+
| 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 |
+----+------------+--------+------+--------+
表department与employee
外链接语法:
select 字段列表
from 表1 inner|left|right join 表2
on 表1.字段 = 表2.字段
1.多表连接查询
把多张物理表合并成一张虚拟表,再进行后续查询
1.1交叉连接:不适用任何匹配条件,生成笛卡尔积
# 查询2张表
select * from employee,department;
# 查询结果:
+----+------------+--------+------+--------+------+--------------+
| id | name | sex | age | dep_id | id | name |
+----+------------+--------+------+--------+------+--------------+
| 1 | egon | male | 18 | 200 | 200 | 技术 |
| 1 | egon | male | 18 | 200 | 201 | 人力资源 |
| 1 | egon | male | 18 | 200 | 202 | 销售 |
| 1 | egon | male | 18 | 200 | 203 | 运营 |
| 2 | alex | female | 48 | 201 | 200 | 技术 |
| 2 | alex | female | 48 | 201 | 201 | 人力资源 |
| 2 | alex | female | 48 | 201 | 202 | 销售 |
| 2 | alex | female | 48 | 201 | 203 | 运营 |
| 3 | wupeiqi | male | 38 | 201 | 200 | 技术 |
| 3 | wupeiqi | male | 38 | 201 | 201 | 人力资源 |
| 3 | wupeiqi | male | 38 | 201 | 202 | 销售 |
| 3 | wupeiqi | male | 38 | 201 | 203 | 运营 |
| 4 | yuanhao | female | 28 | 202 | 200 | 技术 |
| 4 | yuanhao | female | 28 | 202 | 201 | 人力资源 |
| 4 | yuanhao | female | 28 | 202 | 202 | 销售 |
| 4 | yuanhao | female | 28 | 202 | 203 | 运营 |
| 5 | liwenzhou | male | 18 | 200 | 200 | 技术 |
| 5 | liwenzhou | male | 18 | 200 | 201 | 人力资源 |
| 5 | liwenzhou | male | 18 | 200 | 202 | 销售 |
| 5 | liwenzhou | male | 18 | 200 | 203 | 运营 |
| 6 | jingliyang | female | 18 | 204 | 200 | 技术 |
| 6 | jingliyang | female | 18 | 204 | 201 | 人力资源 |
| 6 | jingliyang | female | 18 | 204 | 202 | 销售 |
| 6 | jingliyang | female | 18 | 204 | 203 | 运营 |
+----+------------+--------+------+--------+------+--------------+
1.2内连接:保留两张表有对应关系的记录
# 找两张表公有的部分,相当于利用条件从笛卡尔积结果中筛选出了正确的结果
# department没有204这个部门,因而employee表中关于204这条员工信息没有匹配出来
# 改名
alter table employee rename emp;
alter table department rename dep;
# 查询方式1:(推荐)
select emp.id,emp.name,emp.age,emp.sex,dep.name from emp inner join dep on emp.dep_id = dep.id;
# 输出结果
+----+-----------+------+--------+--------------+
| id | name | age | sex | name |
+----+-----------+------+--------+--------------+
| 1 | egon | 18 | male | 技术 |
| 2 | alex | 48 | female | 人力资源 |
| 3 | wupeiqi | 38 | male | 人力资源 |
| 4 | yuanhao | 28 | female | 销售 |
| 5 | liwenzhou | 18 | male | 技术 |
+----+-----------+------+--------+--------------+
select dep.name,emp.name from emp inner join dep on emp.dep_id=dep.id where dep.name = '技术';
# 查询方式2:(where更多用于过滤条件)
select emp.id,emp.name,emp.age,emp.sex,dep.name from emp,dep where emp.dep_id=dep.id;
1.3外连接之左连接:在内连接的基础上保留左表的记录
# 以左表为准,即找出所有员工信息,当然包括没有部门的员工
# 本质就是:在内连接的基础上增加左边有右边没有的结果
select * from emp left join dep on emp.dep_id = dep.id;
select emp.id,emp.name,dep.name as depart_name from emp left join dep on emp.dep_id=dep.id;
# 显示效果
+----+------------+--------------+
| id | name | depart_name |
+----+------------+--------------+
| 1 | egon | 技术 |
| 5 | liwenzhou | 技术 |
| 2 | alex | 人力资源 |
| 3 | wupeiqi | 人力资源 |
| 4 | yuanhao | 销售 |
| 6 | jingliyang | NULL |
+----+------------+--------------+
1.4外连接之右链接:在内连接的基础上保留右表的记录
# 以右表为准,即找出所有部门信息,包括没有员工的部门
# 本质就是:在内连接的基础上增加右边有左边没有的结果
select * from emp right join dep on emp.dep_id=dep.id;
select emp.id,emp.name,dep.name as depart_name from emp right join dep on emp.dep_id=dep.id;
# 查询结果
+------+-----------+--------------+
| id | name | depart_name |
+------+-----------+--------------+
| 1 | egon | 技术 |
| 2 | alex | 人力资源 |
| 3 | wupeiqi | 人力资源 |
| 4 | yuanhao | 销售 |
| 5 | liwenzhou | 技术 |
| NULL | NULL | 运营 |
+------+-----------+--------------+
1.5全外连接:在内连接的基础上保留左右表的记录
全外连接:在内连接的基础上增加左边有右边没有的和右边有左边没有的结果
# 注意:mysql不支持全外连接 full join
# 强调:mysql可以使用此种方式间接实现全外连接
select * from emp left join dep on emp.dep_id=dep.id
union
select * from emp right join dep on emp.dep_id=dep.id
;
# 查询结果
+------+------------+--------+------+--------+------+--------------+
| id | name | sex | age | dep_id | id | name |
+------+------------+--------+------+--------+------+--------------+
| 1 | egon | male | 18 | 200 | 200 | 技术 |
| 5 | liwenzhou | male | 18 | 200 | 200 | 技术 |
| 2 | alex | female | 48 | 201 | 201 | 人力资源 |
| 3 | wupeiqi | male | 38 | 201 | 201 | 人力资源 |
| 4 | yuanhao | female | 28 | 202 | 202 | 销售 |
| 6 | jingliyang | female | 18 | 204 | NULL | NULL |
| NULL | NULL | NULL | NULL | NULL | 203 | 运营 |
+------+------------+--------+------+--------+------+--------------+
# 注意:union与union all 的区别:union会去掉相同的记录
2.符合条件连接查询
# 2.1 查询所有部门名及对应的员工个数
select dep.name,count(emp.id) from emp right join dep on emp.dep_id=dep.id group by dep.name;
select dep.name,count(emp.id) from emp right join dep
on emp.dep_id=dep.id
group by dep.name
having count(emp.id) < 2;
# 2.2以内连接的方式查询emp和dep表,并且emp表中的age字段值必须大于25,即找出年龄大于25岁的员工以及员工所在的部门
select emp.name,dep.name from emp inner join dep on emp.dep_id=dep.id where age > 25;
# 2.3以内连接的方式查询emp和dep表,并且以age字段的升序方式显示
select emp.id,emp.name,emp.age,dep.name from emp,dep where emp.dep_id=dep.id and age > 25 order by age asc;
# 把多张表连接到一起:
select * from (
select emp.*,dep.name as dep_name from emp inner join dep on emp.dep_id=dep.id)as t1
inner join
dep on t1.dep_id=dep.id;
select * from emp inner join dep on emp.dep_id=dep.id
inner join dep as t1 on t1.id=dep.id;
3.子查询
子查询是将一个查询语句嵌套在另一个查询语句中
内层查询语句的查询结果,可以为外层查询语句提供查询条件
子查询中可以包含:in,not in,any, all, exists和not exists等关键字
还可以包含比较运算符:= , != , > , < 等
3.1带in关键字的查询
# 查询平均年龄在25岁以上的部门名
select id,name from dep where id in(select dep_id from emp group by dep_id having avg(age) > 25);
# 查看技术部员工姓名
select name from emp where dep_id in (select id from dep where name ='技术');
# 查看不足1人的部门名(子查询得到的是有人的部门id)
select name from dep where id not in(select distinct dep_id from emp);
3.2 带exists关键字的子查询
exists关键字表示存在,在使用exists关键字时,内层查询语句不返回查询的记录,而是返回一个真假值,True或False
当返回True时,外层查询语句将进行查询,当返回值为False时,外层查询语句不进行查询
# department表中存在dep_id=203,True
select * from emp where exists (select id from dep where id=200);
# 查询结果
+----+------------+--------+------+--------+
| 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 |
+----+------------+--------+------+--------+
# department表中存在dep_id=205,False
select * from emp where exists(select id from dep where id=204);
# 查询结果:
Empty set (0.00 sec)
二.pymysql
安装:pip3 install pymysql
1.链接,执行sql,关闭(游标)
import pymysql
conn=pymysql.connect(host='127.0.0.1',port=3306,user='root',password='123',database='db4',charset='utf8mb4')
#游标
cursor=conn.cursor() #执行完毕返回的结果集默认以元组显示
# cursor.execute("insert into user(name,pwd) values('egon','123'),('tom','456'),('jack','111');")
# sql="insert into user(name,pwd) values('%s','%s');" %('lili','123')
# cursor.execute(sql)
# %s不要加引号
# cursor.execute("insert into user(name,pwd) values(%s,%s);",('kkk','123'))
username = input("username>>>: ").strip()
password = input("password>>>: ").strip()
# sql = "select * from user where name='%s' and pwd='%s'" %(username,password)
# select * from user where name='egon' -- hello' and pwd='%s'
# select * from user where name='xxx' or 1=1 -- hello' and pwd='%s';
# rows=cursor.execute(sql)
rows=cursor.execute("select * from user where name=%s and pwd=%s",(username,password))
if rows:
print('ok')
else:
print('no')
conn.commit()
cursor.close()
conn.close()
791
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
