一、多表连接查询
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()