mysql语句练习50题（sqlalchmey写法）

sqlalchemy import create_engine from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column,Integer,String,ForeignKey,DateTime from sqlalchemy.orm import relationship,sessionmaker engine = create_engine( "mysql+pymysql://root:123456@localhost:3306/school?charset=utf8", max_overflow=20, pool_size=5, pool_timeout=30, pool_recycle=20 ) Base = declarative_base() #class Stu2Course(Base): # __tablename__ = "student_course" # id = Column(Integer,primary_key=True,autoincrement=True) # stu_id = Column(Integer,ForeignKey("student.id")) # course_id = Column(Integer,ForeignKey("course.id")) #class Stu2Teacher(Base): # __tablename__ = "student_teacher" # id = Column(Integer,primary_key=True,autoincrement=True) # stu_id = Column(Integer,ForeignKey("student.id")) # tea_id = Column(Integer,ForeignKey("teacher.id")) class Stu(Base): __tablename__ = "student" id = Column(Integer,primary_key=True) name = Column(String(32),nullable=False) birth = Column(String(32),nullable=False) sex = Column(String(5),nullable=False) grades = relationship("Grade",backref="student") #courses = relationship("Course",secondary="Stu2Course",backref="student") # teachers = relationship("Teacher",secondary="Stu2Teacher",backref="student") class Course(Base): __tablename__ = "course" id = Column(Integer,primary_key=True) name = Column(String(32),nullable=False) grades = relationship("Grade",backref="course") teachers = relationship("Teacher",backref="teacher") class Teacher(Base): __tablename__ = "teacher" id = Column(Integer,primary_key=True) name = Column(String(32),nullable=False) cou_id = Column(Integer,ForeignKey("course.id")) class Grade(Base): __tablename__ = "grade" id = Column(Integer,primary_key=True) score = Column(Integer,nullable=False) stu_id = Column(Integer,ForeignKey("student.id")) cou_id = Column(Integer,ForeignKey("course.id")) Session = sessionmaker(bind=engine) session = Session() if __name__ == "__main__": Base.metadata.create_all(engine) #Base.metadata.drop_all(engine)

 

插入数据

#学生表
insert into student values(‘01‘ , ‘赵雷‘ , ‘1990-01-01‘ , ‘男‘);
insert into student values(‘02‘ , ‘钱电‘ , ‘1990-12-21‘ , ‘男‘);
insert into student values(‘03‘ , ‘孙风‘ , ‘1990-05-20‘ , ‘男‘);
insert into student values(‘04‘ , ‘李云‘ , ‘1990-08-06‘ , ‘男‘);
insert into student values(‘05‘ , ‘周梅‘ , ‘1991-12-01‘ , ‘女‘);
insert into student values(‘06‘ , ‘吴兰‘ , ‘1992-03-01‘ , ‘女‘);
insert into student values(‘07‘ , ‘郑竹‘ , ‘1989-07-01‘ , ‘女‘);
insert into student values(‘08‘ , ‘王菊‘ , ‘1990-01-20‘ , ‘女‘);

#课程表
insert into course values(‘01‘ , ‘语文‘);
insert into course values(‘02‘ , ‘数学‘);
insert into course values(‘03‘ , ‘英语‘);

#教师表
insert into teacher values(‘01‘ , ‘张三‘,‘01‘);
insert into teacher values(‘02‘ , ‘李四‘,‘02‘);
insert into teacher values(‘03‘ , ‘王五‘,‘03‘);

#成绩表
insert into grade values(1,80,‘01‘ , ‘01‘);
insert into grade values(2,90,‘01‘ , ‘02‘);
insert into grade values(3,99,‘01‘ , ‘03‘);
insert into grade values(4,70,‘02‘ , ‘01‘);
insert into grade values(5,60,‘02‘ , ‘02‘);
insert into grade values(6,80,‘02‘ , ‘03‘);
insert into grade values(7,80,‘03‘ , ‘01‘);
insert into grade values(8,80,‘03‘ , ‘02‘);
insert into grade values(9,80,‘03‘ , ‘03‘);
insert into grade values(10,50,‘04‘ , ‘01‘);
insert into grade values(11,30,‘04‘ , ‘02‘);
insert into grade values(12,20,‘04‘ , ‘03‘);
insert into grade values(13,76,‘05‘ , ‘01‘);
insert into grade values(14,87,‘05‘ , ‘02‘);
insert into grade values(15,31,‘06‘ , ‘01‘);
insert into grade values(16,34,‘06‘ , ‘03‘);
insert into grade values(17,89,‘07‘ , ‘02‘);
insert into grade values(18,98,‘07‘ , ‘03‘);

练习题和sql语句

#1 查询01课程比02课程成绩高的学生的信息以及课程分数
# 找出课程为01的学生的学生信息以及成绩
# 找出课程为02的学生的学生信息以及成绩
data_1 = session.query(Grade.stu_id,Grade.cou_id,Grade.score).filter(Grade.cou_id==1).subquery()
data_2 = session.query(Grade.stu_id,Grade.cou_id,Grade.score).filter(Grade.cou_id==2).subquery()

data_3 = session.query(data_1.c.stu_id).filter(data_1.c.stu_id==data_2.c.stu_id,data_1.c.score>data_2.c.score).subquery()


#学生信息
data_4 = session.query(Stu).filter(Stu.id == data_3.c.stu_id).all()
#课程分数
data_5  = session.query(Stu.name,data_1.c.stu_id,data_1.c.score,data_2.c.score).filter(
        data_1.c.stu_id==data_2.c.stu_id,
        data_1.c.score>data_2.c.score,
        Course.id==data_1.c.cou_id,
        Stu.id == data_1.c.stu_id,
        
        ).all()

print([v for v in data_5])

 

 

mysql语句练习50题（sqlalchmey写法）

标签：tail   mys   utf8   练习题   lse   har   lte   orm   ima