Python之SQLAlchemy使用详细说明

见贤思齐

目录1、SQLAlchemy1.1、ORM概述1.2、SQLAlchemy概述1.3、SQLAlchemy的组成部分1.4、SQLAlchemy的使用1.4.1、安装1.4.2、创建数据库连接1.4.3、执行原生SQL语句1.4.4、映射已存在的表1.4.5、创建表1.4.5.1、创建表的两种方式1、使用Table类直接创建表2、使用声明式方式创建模型类1.4.5.2、约束1、创建约束示列2、外键约束3、删除/更新行为1.4.5.3、多表关系2、一对一关系3、一对多/多对一关系4、多对多关系1.4.5.4、scoped_session实现线程安全1.4.5.5、新增数据1.4.5.6、修改数据1.4.5.7、删除数据1.4.5.8、查询数据1、测试数据准备2、基础查询2.1、查询多个字段（查询指定字段）2.2、去除重复记录2.3、调试小技巧3、条件查询3.1、常用的比较运算符3.2、常见的逻辑运算符3.3、综合示例4、聚合函数4.1、常见的聚合函数4.2、综合示列5、分组查询5.1、综合示例6、排序查询6.1、排序方式7、分页查询7.1、综合示例1.4.5.9、多表查询1、多表查询概述1.1、测试数据准备1.2、概述1.3、多表查询的分类2、内连接3、外连接4、自连接查询5、联合查询6、子查询6.1、概述6.2、标量子查询6.3、列子查询6.4、行子查询6.5、表子查询1、SQLAlchemy1.1、ORM概述定义：ORM（Object-RelationalMapping）模型，即对象关系映射，是一种程序设计技术，用于实现面向对象编程语言里不同类型系统的数据之间的转换。在面向对象的编程语言中，如Java、Python等，数据通常被组织成复杂的对象关系。简单来说就是ORM模型把面向对象编程与操作数据库之间建立了映射。设置开发者操作数据库无需维护和编写SQL语句，而是基于面对对象的方式操作数据库。映射关系：数据库中的表>编程语言中的类，表中的字段>类中的属性，表之间的关系>类之间的关系。使用ORM模型的优势在于：提高开发效率：开发者可以使用面向对象的方式来操作数据库，无需编写大量的SQL语句。易于维护：由于ORM模型提供了清晰的映射规则，使得代码更加清晰、易于理解和维护。跨数据库平台：ORM框架通常支持多种数据库平台，使得应用程序可以轻松地在不同的数据库之间迁移。当然ORM也存在一下劣势：性能开销：ORM框架在将对象的操作转换为SQL语句并执行这些语句时，可能会产生一定的性能开销。复杂查询的复杂性：在处理复杂的查询时，ORM的语法可能会变得复杂且难以维护。1.2、SQLAlchemy概述在Python语言中实现ORM系统的就是SQLAlchemy，它具备以下特点：对象关系映射（ORM）：SQLAlchemy允许开发者将数据库表映射为Python类，将表的行映射为Python对象，从而简化了数据库操作。开发者可以像操作Python对象一样来操作数据库记录，无需编写大量的SQL语句。动态SQL生成：SQLAlchemy提供了表达式语言，允许开发者在运行时动态地构建SQL语句。这使得开发者能够灵活地根据应用程序的需求来生成和执行SQL语句。支持多种数据库：SQLAlchemy支持多种关系型数据库，如MySQL、PostgreSQL、SQLite、Oracle等。开发者可以轻松地切换数据库后端，而无需修改太多代码。连接池和事务管理：SQLAlchemy提供了连接池和事务管理的功能，以确保数据库连接的稳定性和事务的原子性。这有助于开发者编写高效、可靠的数据库应用程序。关系映射：SQLAlchemy支持各种关系数据库中的关系类型，如一对一、一对多、多对多等，并提供了相应的API来处理这些关系。然而，ORM模型也存在一些缺点：性能开销：ORM框架在将对象的操作转换为SQL语句并执行这些语句时，可能会产生一定的性能开销。复杂查询的复杂性：在处理复杂的查询时，ORM的语法可能会变得复杂且难以维护。注意：在处理复杂的SQL查询时，由于ORM框架效率低下，所以这个时候可以编写SQL语句执行原生SQL语句。1.3、SQLAlchemy的组成部分1、核心架构（Core）：引擎（Engine）：负责与数据库的通信，管理连接池和事务。它是SQLAlchemy与数据库交互的入口点。连接（Connection）：代表与数据库的单个连接会话。它是执行SQL语句的直接通道。会话（Session）：在ORM中使用，代表与数据库的持久化会话。它用于管理对象的持久化，包括添加、修改、删除和查询对象。元数据（Metadata）：用于定义和存储关于数据库结构的信息，如表和列的定义。表（Table）：表示数据库中的一个表。列（Column）：表示表中的一个列。类型（Types）：用于定义列的数据类型。表达式构造器（ExpressionLanguage）：用于构建SQL表达式，如select(),insert(),update(),delete()等。2、ORM架构：声明基类（DeclarativeBase）：用于定义ORM模型。模型（Model）：表示数据库中的一个表，由Python类定义。属性（Attributes）：表示模型的属性，与数据库表的列相对应。关系（Relationships）：表示模型之间的关系，如一对多、多对多等。4、数据库连接池（ConnectionPooling）：管理数据库连接的池化，确保高效的数据库连接复用。5、Dialect：选择连接数据库的DBAPI种类，根据配置文件的不同调用不同的数据库API，从而实现对数据库的操作。6、架构和类型（Schema/Types）：定义数据库的架构和数据类型。7、SQL表达式语言（SQLExpressionLanguage）：选择（SELECT）：使用select()构造器来构建查询语句。该语言允许用户以非常灵活和直观的方式构造SQL语句，而无需直接编写SQL字符串。1.4、SQLAlchemy的使用SQLAlchemy官方文档：Dialects—SQLAlchemy2.0Documentation1.4.1、安装pipinstallsqlalchemy1.4.2、创建数据库连接注意：sqlalchemy没有提供直接连接数据库的操作，所以需要借助第三方库来连接数据库，操作数据库。以MySQL为例，sqlalchemy就是借助pymsql库来实现对数据的连接和操作。连接不同/相同的数据库借助不同的第三方库如下：MySQL-Python  mysql+mysqldb://: @[: ]/pymysql  mysql+pymysql://: @/[?]MySQL-Connector  mysql+mysqlconnector://: @[: ]/cx_Oracle  oracle+cx_oracle://user:pass@host:port/dbname[?key=value&key=value...]创建连接：fromsqlalchemyimportcreate_enginefromurllibimportparseuser="root"#用户名password=""#密码pwd=parse.quote_plus(password)#解决密码中含@符导致报错host="172.22.70.174"#数据库主机地址#第一步:创建engineengine=create_engine(url=f"mysql+pymysql://{user}:{pwd}@{host}:3306/test?charset=utf8",max_overflow=10,#超过连接池大小外最多创建的连接pool_size=10,#连接池大小pool_timeout=30,#池中没有线程最多等待的时间，否则报错pool_recycle=-1#对线程池中的线程进行一次连接的回收的时间，如果是3600，表示1个小时后对连接进行回收)1.4.3、执行原生SQL语句需求：查询表t_student的全部数据，执行的SQL语句是：select*fromt_studentfromsqlalchemyimportcreate_enginefromurllibimportparseimportthreadinguser="root"#用户名password=""#密码pwd=parse.quote_plus(password)#解决密码中含@符导致报错host="172.22.70.174"#数据库主机地址#第一步:创建engineengine=create_engine(url=f"mysql+pymysql://{user}:{pwd}@{host}:3306/test?charset=utf8",max_overflow=2,#超过连接池大小外最多创建的连接pool_size=3,#连接池大小pool_timeout=30,#池中没有线程最多等待的时间，否则报错pool_recycle=-1#对线程池中的线程进行一次连接的回收的时间，如果是3600，表示1个小时后对连接进行回收)#第二步：使用deftest_execute():#conn=engine.connect()#创建一个新的连接conn=engine.raw_connection()#从连接池中取一个连接cursor=conn.cursor()#创建游标sql="select*fromt_student"#定义执行的SQL语句cursor.execute(sql)#执行SQL语句print(cursor.fetchall())#获取执行的结果并打印置控制台#测试配置是否生效if__name__=='__main__':foriinrange(20):t=threading.Thread(target=test_execute)t.start()1.4.4、映射已存在的表说明：使用ORM映射已存在的表时，只能映射其对应的字段，对于每个字段的约束最好和原表保持一致，映射已存在的表时不能新增字段，新增外键约束，新增索引操作。如果需要进行这些操作可以数据库中执行相关sql语句或者使用SQLAlchemy的迁移工具（如Alembic）注意：在某些情况下，即使SQLAlchemy模型中的约束与原始数据库表不完全一致，应用程序可能仍然能够正常运行。但是，这可能会导致数据完整性问题或难以预测的行为。因此，最佳实践是尽可能保持SQLAlchemy模型中的约束与原始数据库表一致。示例：需求：创建Student表模型映射数据库中的t_student表importdatetimefromsqlalchemy.ormimportdeclarative_base,sessionmakerfromsqlalchemyBaseUseimportengine#这里的engine就是上面创建连接中创建的enginefromsqlalchemyimportColumn,Integer,String,Text,DateTime,Index#声明ORM基类Base=declarative_base()classStudent(Base):__tablename__='t_student'id=Column(Integer,primary_key=True)stuno=Column(String(10),comment="学号")name=Column(String(10),comment="姓名")gender=Column(String(1),comment="性别")age=Column(Integer,comment="年龄")idcard=Column(String(18),comment="身份证")entrydate=Column(DateTime,default=datetime.datetime.now,comment="入学时间")addr=Column(String(50),comment="家庭地址")definit_db():#创建继承base类的表的映射关系Base.metadata.create_all(engine)defdrop_db():#删除继承base类的表，注意：除了删除表的映射关系，数据库中的表和数据都会被删减，生产中谨慎操作Base.metadata.drop_all(engine)if__name__=='__main__':init_db()1.4.5、创建表1.4.5.1、创建表的两种方式1、使用Table类直接创建表编程风格：这是一种更接近于SQL风格的方法，因为它直接定义了表的列和约束，没有额外的类定义。用法：你需要显式地创建Table对象，并指定列和约束。这种方法在不需要ORM功能，只需要直接操作数据库表的场景中特别有用。灵活性：由于这种方法没有与ORM类绑定，因此它更加灵活，可以更容易地用于更复杂的数据库操作或与其他数据库工具集成。示例：fromsqlalchemyimportcreate_engine,Column,Integer,String,MetaData,Table,CheckConstraint#假设已经有了一个引擎和元数据engine=create_engine('sqlite:///:memory:')metadata=MetaData()#创建一个表，并添加一个检查约束my_table=Table('my_table',metadata,Column('id',Integer,primary_key=True),Column('name',String(50)),Column('age',Integer),CheckConstraint("age>=0ANDage=0ANDage0ANDage6的stuno,每个stuno前面都加123注意：如果stuno的长度是10，需要增加一下长度，不然新增超过长度，会出现添加失败的错误。修改字段数据类型的SQL语句如下（需要执行SQL语句）：ALTERTABLEt_studentMODIFYstunovarchar(30);fromsqlalchemy.ormimportdeclarative_base,sessionmaker,scoped_sessionfromsqlalchemyBaseUseimportenginefrommany_to_many_relationshipimportStudent#创建ORM基类Base=declarative_base()#创建会话类Session=sessionmaker(bind=engine)#使用scoped_session创建线程安全会话session=scoped_session(Session)#1、修改id=5的数据，修改为name="Nicky",stuno="2000100109"##查询出id=5的数据#stu=session.get(Student,5)##修改数据#ifstu:#stu.name="Nicky"#stu.stuno="2000100109"##提交修改的数据#session.commit()##2、修改name="大胖"的数据，修改为name="小胖"#session.query(Student).filter_by(name="大胖").update({"name":"小胖"})##提交修改的数据#session.commit()#3、修改id>6的stuno,每个stuno前面都加123session.query(Student).filter(Student.id>6).update({"stuno":"123"+Student.stuno})session.commit()1.4.5.7、删除数据删除数据流程：先查询出数据，然后再删除数据需求：删除表t_student的数据1、删除id=5的数据2、删除name="小胖"的数据fromsqlalchemy.ormimportdeclarative_base,sessionmaker,scoped_sessionfromsqlalchemyBaseUseimportenginefrommany_to_many_relationshipimportStudent#创建ORM基类Base=declarative_base()#创建会话类Session=sessionmaker(bind=engine)#使用scoped_session创建线程安全会话session=scoped_session(Session)#1、删除id=5的数据#stu_info=session.get(Student,5)#ifstu_info:#session.delete(stu_info)#session.commit()#2、删除name="小胖"的数据session.query(Student).filter_by(name="小胖").delete()session.commit()1.4.5.8、查询数据1、测试数据准备创建一张t_student2表，表的数据模型如下：注意：如果数据库的编码类型不是utf8mb4，表生成后，需要执行以下SQL语句修改表的编码为utf8mb4，不然插入中文会出现报错。ALTERTABLEt_student2CONVERTTOCHARACTERSETutf8mb4COLLATEutf8mb4_unicode_ci;fromsqlalchemy.ormimportdeclarative_base,sessionmaker,scoped_sessionfromsqlalchemyBaseUseimportenginefromsqlalchemyimportColumn,Integer,String,DateTime#声明ORM基类Base=declarative_base()Session=sessionmaker(bind=engine)session=scoped_session(Session)classStudent(Base):__tablename__='t_student2'id=Column(Integer,primary_key=True)stuno=Column(String(10),comment="学号")name=Column(String(10),comment="姓名")gender=Column(String(1),comment="性别")age=Column(Integer,comment="年龄")idcard=Column(String(18),comment="身份证")entrydate=Column(DateTime,comment="入学时间")addr=Column(String(50),comment="家庭地址")definit_db():#创建继承base类的表的映射关系Base.metadata.create_all(engine)defbasic_query():passif__name__=='__main__':init_db()往表中插入测试数据，如下：#向表中插入数据insertintot_student2values('1','1','小洋洋','女',18,'12345678901234957','2023-02-03',"北京"),              ('2','2','小芳','女',18,'123456789012345789','2023-02-03',"北京"),              ('3','3','小枫','男',22,'123456789012345123','2023-01-03',"上海"),              ('4','4','小敏','女',20,'123456789012345345','2022-01-03',"北京"),              ('5','5','小李','男',20,'12345678901234534X','2022-01-03',"上海"),              ('6','6','王小敏','女',16,'123456789012345345','2022-01-03',"成都"),              ('7','7','大刘','男',25,'123456789012345102','2022-01-03',"深圳"),              ('8','8','林逸','男',17,'12345678901234534X','2022-01-03',"北京"),              ('9','9','莫小迪','女',21,'123456789012345302','2022-01-03',"成都"),              ('10','10','林仙仙','女',16,'123456789012345330','2022-01-03',"深圳"),              ('11','11','叶小辰','男',18,'123456789012345352','2022-01-03',"成都"),              ('12','12','韩跑跑','男',24,'12345678901234554X','2022-01-03',"北京");2、基础查询2.1、查询多个字段（查询指定字段）#指定字段查询stus=session.query(Student.name,Student.age).all()forname,ageinstus:print(name,age)#全表查询stus=session.query(Student).all()forstuinstus:print(stu.name)2.2、去除重复记录#查询单个字段的不重复值#查询User表中所有不重复的name字段值unique_names=session.query(distinct(User.name)).all()#遍历结果fornameinunique_names:print(name)#查询多个字段的不重复组合#查询User表中所有不重复的name和age组合unique_name_age_combinations=session.query(distinct(User.name,User.age)).all()#遍历结果forname,ageinunique_name_age_combinations:print(name,age)2.3、调试小技巧在编写完查询代码后，可以先打印出代码对应转化的SQL语句，检查SQL语句是否正确，然后再执行。示例：#指定字段查询#此时语句结尾不加.all(),输出的就是SQL语句stus=session.query(Student.name,Student.age)print(stus)3、条件查询3.1、常用的比较运算符比较运算符作用>大于>=大于等于小于小于等于=等于或!=不等于IN(...)在in之后的列表中的值，多选一LIKE占位符模糊匹配(_匹配单个字符,%匹配任意个字符)ISNULL是NULL3.2、常见的逻辑运算符逻辑运算符作用AND并且(多个条件同时成立)OR或者(多个条件任意一个成立)NOT非,不是3.3、综合示例defconditional_query():#1、查询年龄大于20的学生stus=session.query(Student).filter(Student.age>20).all()#2、查询年龄不等于20的学生stus2=session.query(Student).filter(Student.age!=20).all()#或者使用,注意python3.X版本不支持使用，官方推荐使用!=作为不等于运算符。#stus2_1=session.query(Student).filter(Student.age20).all()#3、查询年龄为18或20或25的学生信息stus3=session.query(Student).filter(Student.age.in_([18,20,25])).all()#4、查询年龄不为18或20或25的学生信息stus4=session.query(Student).filter(~Student.age.in_([18,20,25])).all()#5、查询家庭住址为空的学生信息stus5=session.query(Student).filter(Student.addr==None).all()#或者使用is_()方法stus5_1=session.query(Student).filter(Student.addr.is_(None)).all()#6、查询家庭住址不为空的学生信息stus6=session.query(Student).filter(Student.addr!=None).all()#或者使用isnot_()方法stus6_1=session.query(Student).filter(Student.addr.isnot(None)).all()#7、查询姓林，名字是两个字的学生信息stus7=session.query(Student).filter(Student.name.like("林_")).all()#8、查询身份证号最后一位是X的学生信息stus8=session.query(Student).filter(Student.idcard.like("%X")).all()#9、查询年龄在18岁（包含）到25岁（包含）之间的学生信息stus9=session.query(Student).filter(Student.age>=18,Student.age=18,Student.age聚合函数>having。支持多字段分组,具体语法为:groupbycolumnA,columnB在SQLAlchemy中，使用group_by()方法来执行分组查询。分组查询通常与聚合函数（如COUNT(),SUM(),AVG(),MAX(),MIN()等）一起使用，以便对每个分组进行计算。5.1、综合示例defgroup_by_query():#1、根据性别分组，统计男学生和女学生的数量#selectsex,count(*)fromt_student2groupbysex;results=session.query(Student.gender,func.count(Student.id).label("stu_nums")).group_by(Student.gender).all()#forgender,stu_numsinresults:#print(gender,stu_nums)#2、根据性别分组,统计男学生和女学生的平均年龄#selectsex,avg(age)fromt_student2groupbysex;results2=session.query(Student.gender,func.avg(Student.age).label("avg_age")).group_by(Student.gender).all()#forgender,avg_ageinresults2:#print(gender,avg_age)#3、查询年龄小于25的学生,并根据家庭地址分组,获取学生数量大于等于3的家庭地址#selectaddr,count(*)addr_numfromt_student2whereage=3;#方式一:通过having实现results3=session.query(Student.addr,func.count(Student.id)).filter(Student.age=3).all()#方式二:通过子查询实现，在子句中使用filter()和比较运算符来实现它。#3.1、先查询年龄小于25的学生，并根据家庭地址分组，获取学生数量大于等于3的家庭地址subquery=session.query(Student.addr,func.count(Student.id).label("stu_nums")).filter(Student.age=3).all()#foraddr,stu_numsinresults3:#print(addr,stu_nums)#统计不同家庭地址男女生的数量#selectaddr,gender,count(*)fromt_student2groupbyaddr,gender;results4=session.query(Student.addr,Student.gender,func.count(Student.id).label("stu_nums")).group_by(Student.addr,Student.gender).all()print(results4)6、排序查询6.1、排序方式ASC:升序(默认值)DESC:降序注意：如果是升序,可以不指定排序方式ASC;如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序;deforder_by_query():#1、根据年龄对学生进行升序排序#select*fromt_studentorderbyageasc;res=session.query(Student).order_by(Student.age.asc()).all()#或者#select*fromt_studentorderbyage;res_1=session.query(Student).order_by(Student.age).all()#forresinres:#print(res.age)#2、根据年龄对学生进行升序排序,年龄相同,再按照入学时间进行降序排序#select*fromt_studentorderbyage,entrydatedesc;res2=session.query(Student).order_by(Student.age,Student.entrydate.desc()).all()#forresinres2:#print(res.age,res.entrydate)7、分页查询注意：起始索引从0开始，起始索引=（查询页码-1）*每页显示记录数。分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT。如果查询的是第一页数据，起始索引可以省略，直接简写为limit10。说明：在SQLAlchemy中，分页查询通常通过使用offset()和limit()方法来实现。这两个方法分别用于设置索引偏移量和限制查询结果的条数。7.1、综合示例deflimit_query():#1、查询第1页学生数据,每页展示10条记录#select*fromt_studentlimit0,10;res=session.query(Student).offset(0).limit(10).all()#或者#select*fromt_studentlimit10;res_1=session.query(Student).limit(10).all()#forstuinres:#print(stu.id)#2、查询第2页学生数据,每页展示10条记录#说明:起始索引=(页码-1)*页展示记录数#select*fromt_studentlimit10,10;res2=session.query(Student).offset(10).limit(10).all()#forstuinres2:#print(stu.id)1.4.5.9、多表查询1、多表查询概述1.1、测试数据准备说明：先建立t_emp员工表和t_dept部门表两张表，并插入对应数据1、创建对应的ORM模型如下：fromsqlalchemy.ormimportdeclarative_base,sessionmaker,scoped_sessionfromsqlalchemyBaseUseimportenginefromsqlalchemyimportColumn,Integer,String,DateTime,distinct,or_,and_,func,ForeignKey#声明ORM基类Base=declarative_base()Session=sessionmaker(bind=engine)session=scoped_session(Session)#部门表classDepartments(Base):__tablename__='t_dept'id=Column(Integer,primary_key=True,autoincrement=True)name=Column(String(50),nullable=False,comment="部门名称")#员工表classEmployes(Base):__tablename__="t_emp"id=Column(Integer,primary_key=True,autoincrement=True)name=Column(String(50),nullable=False,comment="姓名")age=Column(Integer,comment="年龄")job=Column(String(20),comment="职位")salary=Column(Integer,comment="薪资")entrydate=Column(DateTime,comment="入职时间")managerid=Column(Integer,comment="直属领导ID")dept_id=Column(Integer,ForeignKey("t_dept.id"))definit_db():#创建继承base类的表的映射关系Base.metadata.create_all(engine)if__name__=='__main__':init_db()注意：如果创建的表的编码不是utf8mb4，需要修改为utf8mb4，不然插入中文的数据会出现编码错误，执行以下SQL语句：ALTERTABLEt_deptCONVERTTOCHARACTERSETutf8mb4COLLATEutf8mb4_unicode_ci;ALTERTABLEt_empCONVERTTOCHARACTERSETutf8mb4COLLATEutf8mb4_unicode_ci;2、插入测试数据INSERTINTOt_dept(name)VALUES('研发部'),('市场部'),('财务部'),('销售部'),('总经办'),('人事部');INSERTINTOt_emp(id,name,age,job,salary,entrydate,managerid,dept_id)VALUES(1,'大刘',28,'总裁',40000,'2000-01-01',null,5),(2,'夏析',20,'项目经理',20000,'2005-12-05',1,1),(3,'李兴',33,'开发',8000,'2000-11-03',2,1),(4,'张敏',30,'开发',11000,'2002-02-05',2,1),(5,'林夕',43,'开发',10500,'2004-09-07',3,1),(6,'小美',19,'程序员鼓励师',6600,'2004-10-12',2,1),(7,'林逸',60,'财务总监',8500,'2002-09-12',1,3),(8,'李媛',19,'会计',48000,'2006-06-02',7,3),(9,'林妙妙',23,'出纳',5250,'2009-05-13',7,3),(10,'赵芳',20,'市场部总监',12500,'2004-10-12',1,2),(11,'张三',56,'职员',3750,'2006-10-03',10,2),(12,'李四',19,'职员',3750,'2007-05-09',10,2),(13,'王二',19,'职员',5500,'2009-02-12',10,2),(14,'周鑫',88,'销售总监',14000,'2004-10-12',1,4),(15,'刘达',38,'销售',4600,'2004-10-12',14,4),(16,'老钱',40,'销售',4600,'2004-10-12',14,4),(17,'小六',42,null,2000,'2011-10-12',1,null);1.2、概述说明：多表查询就是指从多张表中查询数据。操作：要执行多表查询，就只需要使用逗号分隔多张表，如：select*fromt_emp,t_dept;具体的执行结果如下:解释：可见查询结果中包含了大量的结果集，总共102条记录，而这其实就是员工表emp所有的记录(17)与部门表dept所有记录(6)的所有组合情况，这种现象称之为笛卡尔积。笛卡尔积:笛卡尔乘积是指在数学中，两个集合A集合和B集合的所有组合情况。而在多表查询中，需要消除无效的笛卡尔积，只保留两张表关联部分的数据；使用SQL语句，消除多表查询的笛卡尔积：select*fromt_emp,t_deptwheret_emp.dept_id=t_dept.id;1.3、多表查询的分类连接查询内连接：相当于查询A、B交集部分数据外连接：右外连接：查询右表所有数据，以及两张表交集部分数据左外连接：查询左表所有数据，以及两张表交集部分数据自连接：当前表与自身的连接查询，自连接必须使用表别名子查询2、内连接说明：内连接查询的是两张表交集部分的数据内连接的语法分为两种:隐式内连接显式内连接语法：1、隐式内连接SELECT字段列表FROM表1,表2WHERE条件...;2、显示内连接（innerjoin）SELECT字段列表FROM表1[INNER]JOIN表2ON连接条件...;注意：SQL语句中的内连接的inner关键可以省略说明：在SQLAlchemy中，内连接（INNERJOIN）是默认的连接类型，当你使用join()方法而不指定isouter=True时，你就是在执行内连接。内连接只返回满足连接条件的行，即两个表中都存在匹配项的行。示例：1、查询每一个员工的姓名,及关联的部门的名称(使用隐式内连接实现)使用filter()函数实现，在filter()函数中声明关联关系res=session.query(Employes,Departments).filter(Employes.dept_id==Departments.id).all()2、查询每一个员工的姓名,及关联的部门的名称(使用显式内连接实现)使用join()函数实现，当你想要连接两个模型时，应该首先指定主模型（在session.query()中列出的第一个模型），然后使用join()方法连接第二个模型，并指定连接条件。res2=session.query(Employes,Departments).join(Departments,Employes.dept_id==Departments.id).all()3、查询每一个员工的姓名,及关联的部门的名称（使用relationship实现内连接查询）首先在Departments类中新建relationship反向关联关系employes=relationship("Employes",back_populates="departments")在Employes类中新建relationship反向关联关系departments=relationship("Departments",back_populates="employes")重新执行ORM映射definit_db():#创建继承base类的表的映射关系Base.metadata.create_all(engine)if__name__=='__main__':init_db()代码实现如下：res3=session.query(Employes).join(Employes.departments)3、外连接外连接分为两种，分别是：左外连接右外连接1、左外连接说明：左外连接相当于查询表1(左表)的所有数据，当然也包含表1和表2交集部分的数据。语法：SELECT字段列表FROM表1LEFT[OUTER]JOIN表2ON条件...;2、右外连接说明：右外连接相当于查询表2(右表)的所有数据，当然也包含表1和表2交集部分的数据。语法：SELECT字段列表FROM表1RIGHT[OUTER]JOIN表2ON条件...;说明：SQLAlchemy支持左外连接（LEFTOUTERJOIN）和右外连接（RIGHTOUTERJOIN）。不过，在SQLAlchemy的ORM层，通常更常见的是使用左外连接，因为SQLAlchemy更多地是按照关系型数据库的标准来设计的，而标准SQL中右外连接并不如左外连接那样常见。但是，你可以使用SQLAlchemy的Core表达式语言来执行右外连接。以下是如何在SQLAlchemy中使用左外连接和右外连接的示例：注意事项：左外连接和右外连接是可以相互替换，只需要调整在连接查询时SQL中，表结构的先后顺序就可以。在实际开发使用时，左外连接常用。示例：1、查询t_emp表的所有数据,和对应的部门信息(左外连接)#1、查询t_emp表的所有数据,和对应的部门信息(左外连接)#selecte.*,d.namefromt_empeleftouterjoint_deptdone.dept_id=d.id;#注意：outerjoin()函数中的连接条件可以省略，Employes.dept_id==Departments.idres=session.query(Employes,Departments.name).outerjoin(Departments).all()#print(res)2、查询t_dept表的所有数据,和对应的员工信息（右外连接）#查询t_dept表的所有数据,和对应的员工信息（右外连接）#selectd.*,e.*fromt_emperightouterjoint_deptdond.id=e.dept_id;#使用join()函数实现，isouter=True使用左外连接，左外连接和右外连接可以相互转换，也就是表的位置不同#join()函数中的第一个参数Employes表示左外连接关联的表是t_emp，主表是t_deptres2=session.query(Employes,Departments).join(Employes,isouter=True).all()#print(res2)4、自连接查询说明：自连接查询，顾名思义，就是自己连接自己，也就是把一张表连接查询多次。连接方式：对于自连接查询，可以是内连接查询，也可以是外连接查询。注意事项：在自连接查询中，必须要为表起别名，不然不清楚所指定的条件、返回的字段，到底是哪一张表的字段。语法：SELECT字段列表FROM表A别名AJOIN表A别名BON条件...;说明：同样的在SQlAlchemy中实现自连接查询，也需要为表起别名的方式，起别名使用aliased()函数实现。示列：1、查询员工及其所属领导的名字分析：可将t_emp表看作两个表，要求查询每个员工及其对应的领导姓名，可见这是一个交集关系，需要使用内连接。#selecta.name'员工姓名',b.name'领导姓名'fromt_empajoint_empbona.managerid=b.id;#或#selecta.name'员工姓名',b.name'领导姓名'fromt_empa,t_empbwherea.managerid=b.id;#为表Employes创建别名user=aliased(Employes)manager=aliased(Employes)#使用Innerjoin的连接方式res=session.query(user.name.label("employee_name"),manager.name.label("manager_name")).join(manager,user.managerid==manager.id).all()print(res)2、查询所有员工t_empa及其领导的名字t_empb,如果员工没有领导,也需要查询出来。分析：如果员工没有领导,也需要查询出来，由此可见需要使用外连接，一般使用左外连接#为表Employes创建别名user=aliased(Employes)manager=aliased(Employes)#selecta.name'员工姓名',b.name'领导姓名'fromt_empaleftjoint_empbona.managerid=b.id;res2=session.query(user.name.label("employee_name"),manager.name.label("manager_name")).join(manager,user.managerid==manager.id,isouter=True).all()print(res2)5、联合查询关键字：union说明：对于union查询，就是把多次查询的结果合并起来，形成一个新的查询结果集。对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。unionall会将全部的数据直接合并在一起。union会对合并之后的数据去重。语法：SELECT字段列表FROM表A...UNION[ALL]SELECT字段列表FROM表B....;说明：在SQLAlchemy中，可以使用union()或者union_all()方法来执行联合查询。示例：1、将薪资低于5000的员工,和年龄大于45岁的员工全部查询出来.分析：当前对于上述需求，可以直接使用多条件查询，使用逻辑运算符and连接即可。也可以通过union/unionall来联合查询.SQL实现如下：select*fromt_empwheresalary45;ORM实现如下：query1=session.query(Employes).filter(Employes.salary45)union_query=query1.union(query2).all()foruninunion_query:print(un.name)6、子查询6.1、概述1、概念SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询子查询外部的语句可以是INSERT/UPDATE/DELETE/SELECT的任何一个。如：SELECT*FROMt1WHEREcolumn1=(SELECTcolumn1FROMt2);2、分类分类依据：根据子查询结果分为：标量子查询（子查询结果为单个值）列子查询(子查询结果为一列)行子查询(子查询结果为一行)表子查询(子查询结果为多行多列)分类依据2：根据子查询位置分为：WHERE之后FROM之后SELECT之后6.2、标量子查询说明：子查询返回的结果是单个值（数字、字符串、日期等）常用操作符：=>>=(selectentrydatefromt_empwherename='林逸');ORM实现如下：subquery2=session.query(Employes.entrydate).filter_by(name="林逸").scalar_subquery()res2=session.query(Employes).filter(Employes.entrydate>subquery2)print(res2)6.3、列子查询说明：子查询返回的结果是一列（可以是多行）常用操作符：IN、NOTIN、ANY、SOME、ALL操作符描述IN在指定的集合范围之内，多选一NOTIN不在指定的集合范围之内ANY子查询返回列表中，有任意一个满足即可SOME与ANY等同，使用SOME的地方都可以使用ANYALL子查询返回列表的所有值都必须满足示列：1、查询"销售部"和"市场部"的所有员工信息SQL实现如下：分析：可先将查询分为两步1、先在部门表中查询出销售部和市场部的部门idselectidfromt_deptwherename='销售部'orname='市场部';2、然后根据1查出的结果，使用关键字in查询出对应销售部和市场部的员工信息1select*fromt_empwheredept_idin(selectidfromt_deptwherename='销售部'orname='市场部');ORM实现如下：subquery=session.query(Departments.id).filter(or_(Departments.name=="销售部",Departments.name=="市场部")).subquery()res=session.query(Employes).filter(Employes.id.in_(subquery))2、查询比“财务部”所有人工资都高的员工信息SQL实现如下：分析：可先将查询分为两步1、先查询出财务部所有人员的工资信息selectsalaryfromt_empejoint_deptdone.dept_id=d.idwhered.name='财务部'; 2、然后根据1查出的结果，使用关键字all查询出比财务部所有人工资都高的员工信息select*fromt_emp wheresalary>all(selectsalaryfromt_empejoint_deptdone.dept_id=d.idwhered.name='财务部');或select*fromt_empwheresalary>all(selectsalaryfromt_empwheredept_id=(selectidfromt_deptwherename='财务部'));ORM实现如下：使用filter()方法结合all_()函数来实现subquery2=session.query(Employes.salary).join(Departments).filter(Departments.name=="财务部").scalar_subquery()res2=session.query(Employes).filter(Employes.salary>all_(subquery2)).all()3、查询比“研发部”其中任意一人工资都高的员工信息SQL实现如下：分析：可先将查询分为两步1、先查询出研发部所有人员的工资信息selectsalaryfromt_empwheredept_id=(selectidfromt_deptwherename='研发部');2、然后根据1查出的结果，使用关键字any查询出比研发部任意一人工资都高的员工信息select*fromt_empwheresalary>any(selectsalaryfromt_empwheredept_id=(selectidfromt_deptwherename='研发部'));ORM实现如下：使用filter()方法结合any_()函数来实现subquery3=session.query(Employes.salary).join(Departments).filter(Departments.name=="研发部").scalar_subquery()res3=session.query(Employes).filter(Employes.salary>any_(subquery3)).all()6.4、行子查询说明：子查询返回的结果是一行（可以是多列）常用的操作符：=、、IN、NOTIN示列：1、查询与"张敏"的薪资及直属领导相同的员工信息SQL实现如下：分析：可先将查询分为两步1、先查询出张敏的薪资和直属领导的idselectsalary,manageridfromt_empwherename='张敏';2、然后根据1查出的结果，使用=查询与"张敏"的薪资及直属领导相同的员工信息select*fromt_empwhere(salary,managerid)=(selectsalary,manageridfromt_empwherename='张敏');ORM实现如下：使用filter()方法结合tuple_()函数来实现subquery=session.query(Employes.salary,Employes.managerid).filter_by(name="张敏").subquery()res=session.query(Employes).filter(tuple_(Employes.salary,Employes.managerid).in_(subquery)).all()6.5、表子查询说明：子查询返回的结果是多行多列常用的操作符：IN示列：1、查询与"林夕","林妙妙"的职位和薪资相同的员工信息SQL实现如下：分析：可先将查询分为两步1、先查询林夕和林妙妙的职位与薪资信息selectjob,salaryfromt_empwherenamein('林夕','林妙妙');2、然后根据1查出的结果，使用in查询与"林夕","林妙妙"的职位和薪资相同的员工信息select*fromt_empwhere(job,salary)in(selectjob,salaryfromt_empwherenamein('林夕','林妙妙'));ORM实现如下：使用filter()方法结合tuple_()函数来实现subquery=session.query(Employes.job,Employes.salary).filter(Employes.name.in_(["林夕","林妙妙"])).subquery()res=session.query(Employes).filter(tuple_(Employes.job,Employes.salary).in_(subquery)).all()2、查询入职日期是"2002-09-12"之后的员工信息,及其部门信息SQL实现如下：分析：可先将查询分为两步1、先查询出入职日期是"2002-09-12"之后的员工信息 select*fromt_empwhereentrydate>"2002-09-12";2、根据1查询出的表信息，在查询对应的部门信息selecte.*,d.*from(select*fromt_empwhereentrydate>"2002-09-12")ejoint_deptdone.dept_id=d.id;ORM实现如下：使用join()实现subquery2=session.query(Employes).filter(Employes.entrydate>"2002-09-12").subquery()res2=session.query(subquery2,Departments).join(Departments,subquery2.c.dept_id==Departments.id).all()