mysql——练习

一、创建表，并建立约束

#!/usr/bin/env python
# -*- coding:utf-8 -*-
# create all tables

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy import create_engine

engine = create_engine("mysql+pymysql://root@127.0.0.1:3306/chouti?charset=utf8")
Base = declarative_base()

# 成绩表
class Score(Base):
    __tablename__='score'
    sid=Column(Integer,primary_key=True,autoincrement=True)
    student_id=Column(Integer,ForeignKey('student.sid'))
    course_id=Column(Integer,ForeignKey('course.cid'))
    number=Column(Integer)
    student=relationship('Student',backref='score')             # 关联：score—student
    course=relationship('Course',backref='score')               # 关联：score—course

# 班级表
class Class(Base):
    __tablename__='class'
    cid=Column(Integer,primary_key=True,autoincrement=True)
    caption=Column(String(32),unique=True)

# 学生表
class Student(Base):
    __tablename__='student'
    sid=Column(Integer,primary_key=True,autoincrement=True)
    sname=Column(String(32),unique=True)
    gender=Column(String(8))
    class_id=Column(Integer,ForeignKey('class.cid'))
    cls=relationship('Class',backref='student')                 # 关联：student—class

# 老师表
class Teacher(Base):
    __tablename__='teacher'
    tid=Column(Integer,primary_key=True,autoincrement=True)
    tname=Column(String(32),unique=True)

# 课程表
class Course(Base):
    __tablename__='course'
    cid=Column(Integer,primary_key=True,autoincrement=True)
    cname=Column(String(32),unique=True)
    teacher_id=Column(Integer,ForeignKey('teacher.tid'))
    teacher=relationship('Teacher',backref='course')                                # 关联：teacher—course
    student=relationship('Student',secondary=Score.__table__,backref='course')      # 关联：student—course

# Base.metadata.create_all(engine)
# Base.metadata.drop_all(engine)

#!/usr/bin/env python
# -*- coding:utf-8 -*-
# add all items

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy import create_engine
from create_tables import Class,Student,Course,Teacher,Score

engine = create_engine("mysql+pymysql://root@127.0.0.1:3306/chouti?charset=utf8")
Session = sessionmaker(bind=engine)
session = Session()

# 班级表数据
cls1=Class(caption='三年二班')
cls2=Class(caption='一年三班')
cls3=Class(caption='三年一班')
session.add_all([cls1,cls2,cls3])

# 学生表数据
s1=Student(sname='钢蛋',gender='女')
s2=Student(sname='铁锤',gender='女')
s3=Student(sname='山炮',gender='男')
session.add_all([s1,s2,s3])

# 老师表数据
t1=Teacher(tname='波多')
t2=Teacher(tname='苍空')
t3=Teacher(tname='饭岛')
session.add_all([t1,t2,t3])

# 课程表数据
c1=Course(cname='生物')
c2=Course(cname='体育')
c3=Course(cname='物理')
session.add_all([c1,c2,c3])

# 提交上面数据
session.commit()

# 设置关联
cls1.student=[s1,s2]
cls2.student=[s3]
t1.course=[c1,c2]
t2.course=[c3]
s1.course=[c1,c2]
s2.course=[c2]
session.add_all([cls1,cls2,t1,t2,s1,s2])
session.commit()

# 成绩表
session.query(Score).filter(Score.student_id==1 , Score.course_id==1).update({"number":60})
session.query(Score).filter(Score.student_id==1 , Score.course_id==2).update({"number":59})
session.query(Score).filter(Score.student_id==2 , Score.course_id==2).update({"number":100})
session.commit()

# 注意1：多对多中添加数据时，先添加关联，再修改其他字段
# 注意2：filter中同时多个条件时，用逗号隔开，表示and，

二、联表查询所有信息

#!/usr/bin/env python
# -*- coding:utf-8 -*-
# add all items

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy import create_engine
from create_tables import Class,Student,Course,Teacher,Score

engine = create_engine("mysql+pymysql://root@127.0.0.1:3306/chouti?charset=utf8")
Session = sessionmaker(bind=engine)
session = Session()

# 1、联表，查询所有信息
ret = session\
    .query(Score.sid.label('id'),
           Score.student_id,Student.sname.label('student'),
           Student.class_id,Class.caption.label('class'),
           Score.course_id,Course.cname.label('course'),
           Course.teacher_id,Teacher.tname.label('teacher'),
           Score.number.label('score'))\
    .join(Student,Course,Teacher,Class)\
    .all()
# print('id','student_id','student','course_id','course','teacher_id','teacher')
for item in ret:
    print(item.id,item.student_id,item.student,item.course_id,item.course,item.score,item.teacher_id,item.teacher)

三、有关表的一些操作实例：原生SQL

#!/usr/bin/env python
# -*- coding:utf-8 -*-

# 2、查询“生物”课程比“物理”课程成绩高的所有学生的学号
# 思路：将学生学号、生物课成绩、物理课成绩，放到一张虚拟表中，进行比较筛选
# select A.student_id,sw,wl from
# (select score.student_id,score.number as sw
# from score
# left join course on score.course_id=course.cid
# left join student on score.student_id=student.sid
# where course.cname='生物')as A
# left join
# (select score.student_id,score.number as wl
# from score
# left join course on score.course_id=course.cid
# left join student on score.student_id=student.sid
# where course.cname='物理')as B
# on A.student_id=B.student_id

# 3、查询平均成绩大于60分的同学的学号和平均成绩
# 思路：按学生分组，对分组信息进行函数处理
# select student_id,avg(number) from score group by student_id having avg(number)>60

# 4、查询所有同学的学号、姓名、选课数、总成绩
# select student_id,sname,count(*),sum(number),avg(number)from score
# left join course on score.course_id=course.cid
# left join student on score.student_id=student.sid
# group by student_id order by avg(number) desc

# 5、查询姓“李”的老师的个数
# select count(tid) from teacher where tname like '李%'

# 6、查询没学过“叶平”老师课的同学的学号、姓名
# 思路：确定’叶平‘老师的课程编号，找选过’李平‘课的学生,再筛选
# select Distinct student_id,sname from score
# left join student on score.student_id=student.sid
# where student_id not in (
#     select student_id from score where course_id in (
#         select cid from course left join teacher on course.teacher_id=teacher.tid where tname='叶平'
#     )
# )

# 7、查询学过“001”并且学过编号“002”课程的同学的学号、姓名
# 思路：分别找出学过两门课程的学生学号，在从总学生名单中筛选
# select DISTINCT student_id,sname from score
# left join student on score.student_id=student.sid
# where student_id in (select student_id from score where course_id = 1)
# and student_id in (select student_id from score where course_id = 2)

 # 8、查询学过“叶平”老师说教的所有课的同学的学号、姓名
# 思路:先找叶平老师教过的所有课，找出选过叶平老师课程的学生，按学生分组，选修课程数与开课数一致
# select student_id,sname from score
# left join student on student_id=student.sid
# where course_id in (
#     select cid from course left join teacher on course.teacher_id=teacher.tid where tname='叶平'
# )
# group by student_id having count(student_id)=(
#     select count(cid)from course
#     left join teacher on course.teacher_id=teacher.tid
#     group by tname
#     having tname='叶平'
# )

# 9、查询课程“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名
# select A.student_id,score1,score2 from (
#     select student_id,number as score1 from score where course_id=1
# )as A
# left join(
#     select student_id,number as score2 from score where course_id=2
# )as B
# on A.student_id=B.student_id
# where score1 < if(isnull(score2),0,score2)

# 10、查询所有课程成绩小于60分的同学的学号、姓名
# 思路：按学生分组，成绩最高分小于60
# select student_id,sname from score
# left join student on score.student_id=student.sid
# group by student_id
# having max(number)<60

#!/usr/bin/env python
# -*- coding:utf-8 -*-

# # 11、查询没有学全所有课的同学的学号、姓名
# select student_id,sname from score
# left join student on score.student_id=student.sid
# group by student_id having count(1)< (select count(1) from course)

# 12、查询至少一门课与学号为“001”的同学所学相同的同学的学号和姓名
# select DISTINCT student_id,sname from score
# left join student on score.student_id=student.sid
# where course_id in(
#     select course_id from score where student_id = 1
# ) and student_id!=1

# 13、查询至少学过学号为“001”同学所有课的其他同学学号和姓名
# select student_id, count(course_id) from score
# where course_id in(
#     select course_id from score where student_id=1
# )
# group by student_id having count(course_id)= (
#     select count(course_id) from score where student_id=1
# )and student_id!=1

# 14、查询和“002”号的同学学习的课程完全相同的其他同学学号和姓名
# select student_id from score
# where course_id in (
#     select course_id from score where student_id=2
# )
# group by student_id having count(course_id)=(
#     select count(course_id) from score where student_id=2
# ) and student_id != 2

# 15、删除学习“叶平”老师课的SC表记录
# select sid,student_id,course_id,number from score
# where course_id not in (
# select cid from course
# left join teacher on course.teacher_id=teacher.tid
# where tname='叶平'
# )

# 16、向SC表中插入一些记录，这些记录要求符合以下条件：
# c没有上过编号“002”课程的同学学号；c插入“002”号课程的平均成绩，
# -- 思路：insert into tb1(X,X)select S,S from tb2
# insert into score(student_id,course_id,number)
# select sid,2,(select avg(number) from score where course_id =2)
# from student where sid not in (select student_id from score where course_id = 2)

# 17、按平均成绩由低到高显示所有学生的“语文”、“数学”、“英语”三门的课程成绩，
#     按如下形式显示：学生ID，语文，数学，英语，有效课程数，有效平均分，
# select DISTINCT score.student_id as stu_id,wl,sw,ty,average,num from score
# left join (
#     select student_id,number as wl from score left join course on score.course_id=course.cid where cname = '物理'
# )as A on score.student_id = A.student_id
# left join(
#     select student_id,number as sw from score left join course on score.course_id=course.cid where cname = '生物'
# )as B on score.student_id = B.student_id
# left join(
#     select student_id,number as ty from score left join course on score.course_id=course.cid where cname = '体育'
# )as C on score.student_id = C.student_id
# left join(
#     select student_id,avg(number)as average from score group by student_id
# )as D on score.student_id = D.student_id
# left join(
#     select student_id,count(number)as num from score group by student_id
# )as E on score.student_id = E.student_id
#  order by average desc

# 18、查询各科成绩最高和最低的分，以如下形式显示：课程ID，最高分，最低分，
# select course_id,max(number),min(number)from score
# group by course_id

# 19、按各科平均成绩由低到高和及格率的百分数从高到低顺序
# 思路：case when ...then...end
# select course_id as '科目',avg(number)as '平均成绩',sum(case when number>60 then 1 else 0 end)/count(1)*100 as '百分数'
# from score group by course_id order by avg(number)

# 20、课程平均分从高到低显示（显示任课老师）
# select cname,avg(number),tname from score 
# left join course on score.course_id=course.cid
# left join teacher on course.teacher_id=teacher.tid
# group by score.course_id

#!/usr/bin/env python
# -*- coding:utf-8 -*-

# 21、查询各科成绩前三名的记录：（不考虑成绩并列的情况）
# -- 思路：选出每门课的最好成绩和第三名成绩，放入成绩表，然后筛选出第二名成绩
# select DISTINCT score.course_id,score.number,T.first_num,T.third_num from score
# left join(
#     select
#     sid,
#     (select number from score as s2 where s2.course_id=s1.course_id order by number desc limit 1,1)as first_num,
#     (select number from score as s2 where s2.course_id=s1.course_id order by number desc limit 3,1)as third_num
#     from score as s1
# )as T
# on score.sid=T.sid
# where score.number > T.third_num and score.number < T.first_num

# 22、查询每门课程被选修的学生数
# select course_id, count(student_id) from score group by course_id

# 23、查询出只选修了一门课程的全部学生的学号和姓名，
# select student_id,sname from score
# left join student on score.student_id=student.sid
# group by student_id having count(course_id)=1

# 24、查询男生、女生的人数
# select gender,count(gender) from score
# left join student on score.student_id=student.sid
# group by gender

# 25、查询姓“张”的学生名单
# select sid,sname,gender,caption from student
# left join class on student.class_id=class.cid
# where sname like('张%')

# 26、查询同名同姓学生名单，并统计同名人数
# select sname, count(1) from student
# group by sname having count(1)>1

# 27、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列
# select course_id,avg(number) as avg from score
# group by course_id
# order by avg,course_id desc

# 28、查询平均成绩大于85的所有学生的学号、姓名和平均成绩
# select student_id as stu_id,sname,avg(number) as avg from score
# left join student on score.student_id=student.sid
# group by student_id having avg>85

# 29、查询课程名称为“数字”，且分数低于60的学生姓名和分数
# select sname,number from score
# left join student on score.student_id = student.sid
# left join course on score.course_id=course.cid
# where cname='数学' and number<60

# 30、查询课程编号为“003”且成绩在80分以上的学生的学号和姓名
# select student_id,sname from score
# left join student on score.student_id=student.sid
# where course_id=3 and number>80

#!/usr/bin/env python
# -*- coding:utf-8 -*-

# 31、求选了课程的学生人数
# select count(DISTINCT student_id) from score

# 32、查询选修了“杨艳”老师所授课程的学生中，成绩最高的学生姓名及其成绩
# select sname,number from score
# left join student on score.student_id= student.sid
# where course_id in (
#     select cid from course
#     left join teacher on course.teacher_id=teacher.tid
#     where tname='波多'
# )
# order by number desc limit 1

# 33、查询各个课程及相应的选修人数
# select cname,count(1) from score
# left join course on score.course_id=course.cid
# group by course_id

# 34、查询不同课程但成绩相同的学生的学号、课程号、学生成绩
# select DISTINCT s1.course_id,s2.course_id,s1.number,s2.number
# from score as s1,score as s2
# where s1.number=s2.number and s1.course_id!=s2.course_id

# 35、查询每门课程成绩最好的前两名
# select score.sid,student_id,course_id,number,sec,max from score
# left join
# (
# select sid,
#     (select number from score as s2 where s2.course_id=s1.course_id order by number desc limit 2,1)as sec,
#     (select number from score as s2 where s2.course_id=s1.course_id order by number desc limit 1,1)as max
# from score as s1
# )as T
# on score.sid=T.sid where score.number = sec or score.number = max

# 36、检索至少选择两名课程的学生学号
# select student_id from score
# group by student_id having count(1)>1

# 37、查询全部学生都选修的课程的课程号和课程名
# select course_id,cname from (select course_id, count(1) as stu_num from score group by course_id)as s2
# left join course on s2.course_id=course.cid
# where s2.stu_num=(select count(1) from (select DISTINCT student_id as A from score)as s1)

# 38、查询没学过“叶平”老师所授的任一门课程的学生姓名
# select DISTINCT sname from score
# left join student on score.student_id=student.sid
# where student_id not in (
#     select DISTINCT student_id from score
#     where course_id in (
#         select cid from course
#         left join teacher on course.teacher_id=teacher.tid
#         where tname='叶平'
#     )
# )

# 39、查询两门以上不及格课程的同学的学号及其平均成绩
# select student_id,avg(number)as avg from (
#     select student_id,number from score where number <60
# )as s1
# group by student_id having count(1)>2

# 40、检索“004”课程分数小于60，按分数降序排列的同学学号
# select student_id,number from score
# where course_id=2 and number<60
# order by number desc

# 41、删除“002”同学的“001”课程的成绩
# update score set number=0 where student_id=1 and course_id=1
#
# delete from score where student_id=1 and course_id=1

 注意：

　　1、查询结果去重：DISTINCT

　　2、判断是否为空：if（isnull（arg），0,num），空为0，非空为num，参考题目9，

　　3、将查询结果插入表：insert into tb1（xxx，xxx）select xxx，xxx from tb2，参考题目16，

　　4、case条件判断：case when 条件 then xxx else yyy end，可以同时存在多个case语句，参考题目19，

　　5、exist条件判断：select * from tb1 exists（select * from tb2 where tb1.xxx = tb2.xxx），

　　　  其中exists（）中的内容取不出来，只能做判断使用，况且tb1只能出现在第一层，

　　6、搭建逻辑关系时，善用分组查个数，in、not in、join等语句，但in的子查询中不支持limit，参考题目21，

　　7、可以直接组合：select * from A，B，参考题目24，

 说明：

　　内容参考：http://www.cnblogs.com/wupeiqi/articles/5748496.html