MySQL（六）多表查询

一、多表连接查询

1、笛卡尔积（左边表里的一条记录对应右边表的多条记录，相当于俩表的记录互乘）
select * from employee,department;

#建表
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> select * from department;
+------+----------+
| id   | name     |
+------+----------+
|  200 | 技术     |
|  201 | 人力资源 |
|  202 | 销售     |
|  203 | 运营     |
+------+----------+
4 rows in set (0.00 sec)

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 |
+----+------------+--------+------+--------+

ps：先生成笛卡尔积，然后按照约束条件进行筛选

mysql> select * from employee,department where employee.dep_id=department.id;
+----+-----------+--------+------+--------+------+--------------+
| id | name      | sex    | age  | dep_id | id   | name         |
+----+-----------+--------+------+--------+------+--------------+
|  1 | egon      | male   |   18 |    200 |  200 | 技术         |
|  2 | alex      | female |   48 |    201 |  201 | 人力资源     |
|  3 | wupeiqi   | male   |   38 |    201 |  201 | 人力资源     |
|  4 | yuanhao   | female |   28 |    202 |  202 | 销售         |
|  5 | liwenzhou | male   |   18 |    200 |  200 | 技术         |
+----+-----------+--------+------+--------+------+--------------+

2、内连接inner join:取两张表交集

mysql> select * from employee inner join department on employee.dep_id = department.id;
+----+-----------+--------+------+--------+------+--------------+
| id | name      | sex    | age  | dep_id | id   | name         |
+----+-----------+--------+------+--------+------+--------------+
|  1 | egon      | male   |   18 |    200 |  200 | 技术         |
|  2 | alex      | female |   48 |    201 |  201 | 人力资源     |
|  3 | wupeiqi   | male   |   38 |    201 |  201 | 人力资源     |
|  4 | yuanhao   | female |   28 |    202 |  202 | 销售         |
|  5 | liwenzhou | male   |   18 |    200 |  200 | 技术         |
+----+-----------+--------+------+--------+------+--------------+

3、左连接left join：在内链接的基础上，保留左表的记录

mysql> select * from employee left join department on employee.dep_id = department.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         |
+----+------------+--------+------+--------+------+--------------+

4、左连接right join：在内链接的基础上，保留右表的记录

mysql> select * from employee right join department on employee.dep_id = department.id;
+------+-----------+--------+------+--------+------+--------------+
| id   | name      | sex    | age  | dep_id | id   | name         |
+------+-----------+--------+------+--------+------+--------------+
|    1 | egon      | male   |   18 |    200 |  200 | 技术         |
|    2 | alex      | female |   48 |    201 |  201 | 人力资源     |
|    3 | wupeiqi   | male   |   38 |    201 |  201 | 人力资源     |
|    4 | yuanhao   | female |   28 |    202 |  202 | 销售         |
|    5 | liwenzhou | male   |   18 |    200 |  200 | 技术         |
| NULL | NULL      | NULL   | NULL |   NULL |  203 | 运营         |
+------+-----------+--------+------+--------+------+--------------+

5、全外连接full join：mysql中没有full join，想实现该效果需要借助与union

select * from employee left join department on employee.dep_id = department.id
union
select * from employee right join department on employee.dep_id = department.id;

#关键字的优先级

select distinct 字段1，字段2，。。。 from 左表 inner/left/right join 右表
    on 连接条件
    where 约束条件
    group by 分组字段
    having 过滤条件
    order by 排序字段
    limit 限制条件


ps：Vn代表虚拟表
1、先找到两张表，生成笛卡尔积，得到V1
2、按照on后的条件得到两张的共同部分，得到V2
3、如果是left join，则在V2的基础上保留左表的记录，得到V3
4、where
5、group by
6、having
7、select（distinct）
8、order by
9、limit

二、子查询

1：子查询是将一个查询语句嵌套在另一个查询语句中。
2：内层查询语句的查询结果，可以为外层查询语句提供查询条件。
3：子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字
4：还可以包含比较运算符：= 、 !=、> 、<等

mysql> select id from department where name in ('技术','销售');
+------+
| id   |
+------+
|  200 |
|  202 |
+------+
2 rows in set (0.00 sec)

mysql> select id,name from employee where dep_id in (200,202);
+----+-----------+
| id | name      |
+----+-----------+
|  1 | egon      |
|  4 | yuanhao   |
|  5 | liwenzhou |
+----+-----------+
3 rows in set (0.00 sec)


#查出技术部，销售部人员的姓名和id
select id,name from employee where dep_id in (select id from department where name in ('技术','销售'));


#查询平均年龄在25岁以上的部门名
select name from department where id in (
    select dep_id from employee group by dep_id having avg(age) > 25
);
#查看技术部员工姓名
select name from employee where dep_id = (select id from department where name = '技术');

#查看不足1人的部门名
select name from department where id in (
select dep.id from department dep left join employee emp on dep.id = emp.dep_id
    group by dep.id
    having count(emp.id) < 1
);

select * from deparment where id in (
select dep_id from employee group by dep_id having count(id) >= 1
);

ps：带EXISTS关键字的子查询

exists：存在

mysql> select * from employee where exists (select id from department 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 |
+----+------------+--------+------+--------+
6 rows in set (0.00 sec)

mysql> select * from employee where exists (select id from department where id=110);
Empty set (0.00 sec)

 三、pymysql

import pymysql

#发起连接
conn=pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='',
    database='day45',
    charset='utf8'
)

cur=conn.cursor() #拿到游标，游标负责执行

在mysql上创建库与表
create table userinfo(id int primary key auto_increment,user char(16),password varchar(30));

insert into userinfo(user,password)varlues('erha','123'),('abc',''456);

在pycharm

import pymysql

#发起连接
conn=pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='',
    database='day45',
    charset='utf8'
)

cur=conn.cursor() #拿到游标

# 增，删，改

cur.execute('insert into userinfo(user,password) values ("bbb",("789");) # 插入一条记录，也可插入多条
# insert_sql='insert into userinfo(user,password) values("%s","%s");' %('yh','123')
# print(insert_sql)
# cur.execute(insert_sql)
####以上有点麻烦

# insert_sql='insert into userinfo(user,password) values(%s,%s);'
# cur.execute(insert_sql,('evia1','1234'))  #一条记录
# cur.executemany(insert_sql,[('evia2','1234'),('evia3','1234'),('evia4','1234')]) ##多条记录



#查
# rows=cur.execute('select * from userinfo where 1 = 2;') # 结果为0，不成立
rows=cur.execute('select * from userinfo;') #有多少条记录
print(rows)

print(cur.fetchone())  # 拿出第一条记录
print(cur.fetchone())
print(cur.fetchmany(2)) #取哪几条
print(cur.fetchall()) 拿出所有的记录，以元组形式显示出来
# print(cur.fetchall())

#提交，不然无法保存新建或者修改的数据
conn.commit()

# 关闭游标
cur.close()

# 关闭连接
conn.close()