# !/usr/bin/env python
# -*- coding: utf-8 -*-
import MySQLdb
conn=MySQLdb.connect(host="localhost", user="root",db="test", passwd="6329017abc", charset="utf8")
cursor = conn.cursor()
cursor.execute('CREATE DATABASE test')
cursor.execute('DROP DATABASE test')
cursor.execute('DROP TABLE Teacher')
cursor.execute('CREATE TABLE Teacher( Tid int NOT NULL PRIMARY KEY, Tname nvarchar(20) NOT NULL)')
cursor.execute("INSERT INTO Teacher(Tid,Tname) VALUE (1,'张老师')")
cursor.execute("INSERT INTO Teacher(Tid,Tname) VALUE (2,'王老师')")
cursor.execute("INSERT INTO Teacher(Tid,Tname) VALUE (3,'李老师')")
cursor.execute("INSERT INTO Teacher(Tid,Tname) VALUE (4,'赵老师')")
cursor.execute("INSERT INTO Teacher(Tid,Tname) VALUE (5,'刘老师')")
cursor.execute("INSERT INTO Teacher(Tid,Tname) VALUE (6,'向老师')")
cursor.execute("INSERT INTO Teacher(Tid,Tname) VALUE (7,'李文静')")
cursor.execute("INSERT INTO Teacher(Tid,Tname) VALUE (8,'叶平')")
cursor.execute('DROP TABLE Student')
cursor.execute('CREATE TABLE Student( Sid int NOT NULL PRIMARY KEY, Sname nvarchar(20) NOT NULL, Sage DATETIME NOT NULL ,Ssex NVARCHAR(2) NOT NULL )')
cursor.execute("INSERT INTO Student(Sid,Sname,Sage, Ssex) VALUE (1,'张三','1980-1-23','男')")
cursor.execute("INSERT INTO Student(Sid,Sname,Sage, Ssex) VALUE (2,'李四','1982-12-12','男')")
cursor.execute("INSERT INTO Student(Sid,Sname,Sage, Ssex) VALUE (3,'张飒','1981-9-9','男')")
cursor.execute("INSERT INTO Student(Sid,Sname,Sage, Ssex) VALUE (4,'莉莉','1983-3-23','女')")
cursor.execute("INSERT INTO Student(Sid,Sname,Sage, Ssex) VALUE (5,'王弼','1982-6-21','男')")
cursor.execute("INSERT INTO Student(Sid,Sname,Sage, Ssex) VALUE (6,'王丽','1984-10-10','女')")
cursor.execute("INSERT INTO Student(Sid,Sname,Sage, Ssex) VALUE (7,'刘香','1980-12-22','女')")
cursor.execute('DROP TABLE Course')
cursor.execute('CREATE TABLE Course( Cid int NOT NULL PRIMARY KEY, Cname nvarchar(20) NOT NULL, Tid INT NOT NULL )')
cursor.execute("INSERT INTO Course(Cid,Cname,Tid) VALUE (1,'企业管理',3)")
cursor.execute("INSERT INTO Course(Cid,Cname,Tid) VALUE (2,'马克思',1)")
cursor.execute("INSERT INTO Course(Cid,Cname,Tid) VALUE (3,'UML',2)")
cursor.execute("INSERT INTO Course(Cid,Cname,Tid) VALUE (4,'数据库',5)")
cursor.execute("INSERT INTO Course(Cid,Cname,Tid) VALUE (5,'物理',8)")
cursor.execute('DROP TABLE SC')
cursor.execute('CREATE TABLE SC( Sid int NOT NULL, Cid INT NOT NULL, Score INT NOT NULL )')
cursor.execute("INSERT INTO SC(Sid,Cid,Score)VALUES(1,1,80)")
cursor.execute("INSERT INTO SC(Sid,Cid,Score)VALUES(1,2,86)")
cursor.execute("INSERT INTO SC(Sid,Cid,Score)VALUES(1,3,83)")
cursor.execute("INSERT INTO SC(Sid,Cid,Score)VALUES(1,4,89)")
cursor.execute("INSERT INTO SC(Sid,Cid,Score)VALUES(2,1,50)")
cursor.execute("INSERT INTO SC(Sid,Cid,Score)VALUES(2,2,36)")
cursor.execute("INSERT INTO SC(Sid,Cid,Score)VALUES(2,3,43)")
cursor.execute("INSERT INTO SC(Sid,Cid,Score)VALUES(2,4,59)")
cursor.execute("INSERT INTO SC(Sid,Cid,Score)VALUES(3,1,50)")
cursor.execute("INSERT INTO SC(Sid,Cid,Score)VALUES(3,2,96)")
cursor.execute("INSERT INTO SC(Sid,Cid,Score)VALUES(3,3,73)")
cursor.execute("INSERT INTO SC(Sid,Cid,Score)VALUES(3,4,69)")
cursor.execute("INSERT INTO SC(Sid,Cid,Score)VALUES(4,1,90)")
cursor.execute("INSERT INTO SC(Sid,Cid,Score)VALUES(4,2,36)")
cursor.execute("INSERT INTO SC(Sid,Cid,Score)VALUES(4,3,88)")
cursor.execute("INSERT INTO SC(Sid,Cid,Score)VALUES(4,4,99)")
cursor.execute("INSERT INTO SC(Sid,Cid,Score)VALUES(5,1,90)")
cursor.execute("INSERT INTO SC(Sid,Cid,Score)VALUES(5,2,96)")
cursor.execute("INSERT INTO SC(Sid,Cid,Score)VALUES(5,3,98)")
cursor.execute("INSERT INTO SC(Sid,Cid,Score)VALUES(5,4,99)")
cursor.execute("INSERT INTO SC(Sid,Cid,Score)VALUES(6,1,70)")
cursor.execute("INSERT INTO SC(Sid,Cid,Score)VALUES(6,2,66)")
cursor.execute("INSERT INTO SC(Sid,Cid,Score)VALUES(6,3,58)")
cursor.execute("INSERT INTO SC(Sid,Cid,Score)VALUES(6,4,79)")
cursor.execute("INSERT INTO SC(Sid,Cid,Score)VALUES(7,1,80)")
cursor.execute("INSERT INTO SC(Sid,Cid,Score)VALUES(7,2,76)")
cursor.execute("INSERT INTO SC(Sid,Cid,Score)VALUES(7,3,68)")
cursor.execute("INSERT INTO SC(Sid,Cid,Score)VALUES(7,4,59)")
cursor.execute("INSERT INTO SC(Sid,Cid,Score)VALUES(7,5,89)")
cursor.execute("ALTER TABLE SC ADD CONSTRAINT FOREIGN KEY (Sid) REFERENCES Student (Sid)")
cursor.execute("ALTER TABLE SC ADD CONSTRAINT FOREIGN KEY (Cid) REFERENCES Course (Cid)")
cursor.execute("ALTER TABLE Course ADD CONSTRAINT FOREIGN KEY (Tid) REFERENCES Teacher (Tid)")
cursor.execute("SELECT * FROM Teacher")
for each in cursor.fetchall():
print('%s\t%s'% each)
cursor.execute("SELECT * FROM Student")
for each in cursor.fetchall():
print('%s\t%s\t%s\t%s'% each)
cursor.execute("SELECT * FROM Course")
for each in cursor.fetchall():
print('%s\t%s\t%s'% each)
cursor.execute("SELECT * FROM SC")
for each in cursor.fetchall():
print('%s\t%s\t%s'% each)
5.查询没学过"叶平"老师课的同学的学号、姓名
cursor.execute("SELECT Sid, Sname FROM Student WHERE Sid NOT IN"
"(SELECT Sid FROM Course, SC, Teacher WHERE Course.Tid=Teacher.Tid AND Course.Cid=SC.Cid AND Tname='叶平')")
#cursor.execute("SELECT Student.Sid, Sname FROM Student, Course, SC, Teacher WHERE Student.Sid=SC.Sid AND Course.Tid=Teacher.Tid AND Course.Cid=SC.Cid AND Teacher.Tname<>'叶平'")
for each in cursor.fetchall():
print('%s\t%s'% each)
7.查询学过"叶平"老师所教的所有课的同学的学号、姓名
cursor.execute("INSERT INTO Course(Cid, Cname, Tid) VALUE (6,'政治',8)")#额外增加的实验
cursor.execute("INSERT INTO SC(Sid, Cid, score) VALUE (7,6,70)")#额外增加的实验
cursor.execute("SELECT sid, Sname FROM Student WHERE sid in (SELECT DISTINCT sid FROM Course, SC, Teacher WHERE Course.Tid=Teacher.Tid AND Course.Cid=SC.Cid AND Tname='叶平')")
for each in cursor.fetchall():
print('%s\t%s'% each)
8 查询课程编号1比课程编号2成绩高的同学的学号,姓名(FROM后面也可以另起一个表)
as作为一张
cursor.execute('select Student.Sid, Sname from (select Score, sid from SC where Cid=1) as a, (SELECT Score, Sid FROM SC WHERE Cid=2) as b,Student where Student.Sid=a.Sid and Student.Sid=b.Sid AND a.Score>b.Score')
for each in cursor.fetchall():
print ('%s\t%s'% each)
10 查询所有课程成绩大于60分的同学的学号、姓名
怎么选这张表
所有 in 或not in
DISTINCT 用于消除重复
cursor.execute('select DISTINCT Student.Sid, Sname from SC, Student where SC.Sid NOT IN (SELECT Sid FROM SC WHERE SC.score<60) AND SC.Sid=Student.Sid')
for each in cursor.fetchall():
print ("%s\t%s"% each)
11 没有学全所有课的同学的姓名,学号
GROUP BY分组
HAVING 用于分组后的筛选
分组之后不能进行筛选
cursor.execute('select Sname, Student.Sid from SC, Student WHERE SC.Sid=Student.Sid GROUP BY Sid HAVING COUNT(Cid)<(select COUNT(Cid) from Course) ')
for each in cursor.fetchall():
print("%s\t%s"% each)
12 查询至少有一门课程 与 学号为1的同学所学课程 相同的同学的学号和姓名
没什么特别知识点（记得正不行则取反）
cursor.execute('select DISTINCT Student.Sid,Sname from SC, Student where SC.Sid not in (SC.Sid not in (select Cid from SC where SC.Sid=1)) AND SC.Sid=Student.Sid AND SC.Sid<>1')
for each in cursor.fetchall():
print ('%s\t%s'% each)
13 把“sc”表中“刘老师”所教课的成绩都更改为此课程的平均成绩
14 查询和2号同学学习的课程完全相同的其他同学学号和姓名
Count的用法
cursor.execute('select a.Sid, Student.Sname from Student, (select SC.Sid from SC WHERE SC.Cid in (select SC.Cid from SC where SC.Sid=2) AND SC.Sid<>2) as a,(SELECT SC.Cid from SC where SC.Sid=2)as b WHERE a.Sid=Student.Sid GROUP BY a.Sid HAVING COUNT(a.Sid)=COUNT(b.Cid)')
for each in cursor.fetchall():
print ('%s\t%s'% each)
15 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
cursor.execute('select a.Cid from Student, (select Cid, Sid from SC WHERE score<60) as a WHERE a.Sid=Student.Sid GROUP BY a.Cid HAVING COUNT(a.Cid)>=2')
for each in cursor.fetchall():
print('%s'% each)
16 向sc表中插入一些记录，这些记录要求符合以下条件：--将没有课程3成绩同学的该成绩补齐, 其成绩取所有学生的课程2的平均成绩
cursor.execute("insert into SC select sid, 3, (select AVG(score) from SC GROUP by cid HAVING cid=2) FROM Student WHERE sid not in (select Sid from SC where cid=3)")
for each in cursor.fetchall():
print('%s'% each)
17 按平平均分从高到低显示所有学生的如下统计报表-- 学号,企业管理,马克思,UML,数据库,物理,课程数,平均分
cursor.execute("select sid,avg(score) from SC,(SELECT Cname='企业管理' FROM SC, Course WHERE SC.Cid=Course.Cid) AS a group by sid ")
for each in cursor.fetchall():
print('%s\t%s'% each)
24 查询学生平均分及其名次(暂时不知道)
# GROUP BY 分组之后使用cursor.execute('select Sname, avg(SC.score) from SC, Student WHERE SC.Sid=Student.Sid GROUP BY SC.Sid ORDER BY avg(SC.score) DESC')#名次
for each in cursor.fetchall():
print('%s\t\t%s'% each)
26 查询每门课程被选修的学生数
DISTINCT:
IN:筛选出in里面的内容,作为一列,count影响整一列
GROUP BY:count影响一组
为什么分组之后不能使用SC.Sid（不管这个为什么，就是不能用）
cursor.execute('select Course.Cname, count(SC.Cid) from SC, Course WHERE Course.Cid=SC.Cid group by SC.Cid')
for each in cursor.fetchall():
print('%s\t%s'% each)
27 查询出只选修了一门课程的全部学生的学号和姓名
WHERE AND IN OR 其他数学符号
分组之后,group by 之后Cid消失 不能使用where SC.Sid=Student.Sidcursor.execute('select Student.Sid, Student.Sname from SC, Student WHERE SC.Sid=Student.Sid GROUP BY SC.Sid HAVING COUNT(SC.Sid)=1')
#AND SC.Sid=Student.Sid
for each in cursor.fetchall():
print('%s\t%s'% each)
28 查询男生、女生人数cursor.execute('select Ssex, count(Ssex) from Student group by Ssex')
for each in cursor.fetchall():
print('%s\t%s'% each)
29 查询姓“张”的学生名单
like的用法
count 但没分组的情况下,此处的count指得是一列的行数;如果是分组,指的是每一组的行数cursor.execute("select Student.Sname from Student where Sname like '张%' ")
for each in cursor.fetchall():
print('%s'% each)
30 查询同名同性学生名单，并统计同名人数
cursor.execute("select s1.Sid from Student as s1, Student as s2 where s1.Sname=s2.Sname and s1.Ssex=s2.Ssex")
for each in cursor.fetchall():
print('%s'% each)
31 1981年出生的学生名单(注：student表中sage列的类型是datetime)
convert的作用
cursor.execute("select Sname,YEAR(Sage) as age from Student where YEAR(Sage)=1981")
for each in cursor.fetchall():
print('%s\t%s'% each)
32 查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列
DESC用于ODER BY后面
cursor.execute("select avg(score), cid from SC group by cid ORDER BY avg(score),cid DESC ")
for each in cursor.fetchall():
print('%s\t%s'% each)
33 查询平均成绩大于80的所有学生的学号、姓名和平均成绩
为什么不能在分组之后使用WHERE
cursor.execute("select SC.Sid, Student.Sname, avg(score) from SC,Student WHERE SC.sid=Student.Sid GROUP BY sid HAVING avg(score)>80")
for each in cursor.fetchall():
print('%s\t%s\t%s'% each)
34 查询 数据库 分数 低于60的学生姓名和分数
先从查询数据库分数开始,在考虑查询小于60分的
cursor.execute("select Sname, score from SC, Student, Course where Course.Cid=SC.Cid and SC.sid=Student.Sid and score<60 and Cname='数据库'")
for each in cursor.fetchall():
print('%s\t%s'% each)
35 查询所有学生的选课情况
换成一张表的数据cursor.execute('select Student.Sname, Course.Cname from SC, Student, Course WHERE Student.Sid=SC.Sid AND Course.Cid=SC.Cid')
for each in cursor.fetchall():
print('%s\t%s'% each)
36 查询成绩在70分以上的学生姓名、课程名称和分数(这个不对)
自然联接:消除重复的行
内联接:只显示有关系的部分cursor.execute('select Student.Sname, Course.Cname, score from SC, Student, Course where score>70 AND Student.Sid=SC.Sid AND SC.Cid=Course.Cid')
for each in cursor.fetchall():
print ('%s\t%s\t%s'% each)
37 查询不及格的课程，并按课程号从大到小排列
cursor.execute("select cid,sid from SC WHERE score<60 ORDER BY cid")
for each in cursor.fetchall():
print('%s\t%s'% each)
38 查询课程编号为3且课程成绩在80分以上的学生的学号和姓名cursor.execute('select SC.Sid, Student.Sname from SC, Student where SC.Cid=3 and score>80 AND Student.Sid=SC.Sid')
for each in cursor.fetchall():
print ('%s\t%s' % each)
39 求选了课程的学生人数
select子句要带括号Count 和sum的区别
cursor.execute('select COUNT(a.Sid) FROM (select Sid from SC group by SC.Sid HAVING COUNT(SC.Cid) <>0) as a')
for each in cursor.fetchall():
print('%s'% each)
40 查询选修“叶平”老师所授课程的学生中，成绩最高的学生姓名及其成绩(未完成)
MAX:
子查询和WHERE复合使用,为什么
cursor.execute('select max(score) from SC, Student, Course,Teacher where SC.Sid=Student.Sid and Course.Cid=SC.Cid and Teacher.Tid=Course.Tid and Tname="叶平" ')
for each in cursor.fetchall():
print('%s'% each)
41 查询各个课程及相应的选修人数(如果包括那些没有人选的课)
要统计同一组列的不同组的数目需要用到GROUP BY, COUNT
count(*):不忽略NULL值和NULL值.意思是当有一行为NULL,计数为1
count(column):忽略NUll值,意思是当有一行为NULL,计数为0
group by 后面接什么东西取决于 (RIGHT(LEFT)) OUTER JOIN
cursor.execute("INSERT INTO Course(Cid, Cname, Tid) VALUE (6,'历史',8) ")#实验
#cursor.execute("select Cname from Course")cursor.execute('select Course.Cname, COUNT(SC.Cid) from SC RIGHT OUTER JOIN Course ON SC.Cid=Course.Cid group by Cname')#为什么这个SC.Cid不行呢？
for each in cursor.fetchall():
print('%s\t%s'% each)
42 查询不同课程成绩相同的学生的学号、课程号、学生成绩(待定)
迪卡尔的机当连接多个标的时候,如果不使用where
cursor.execute("select DISTINCT a.cid, b.cid, a.score from (select cid,score from SC ) as a,(select cid,score from SC )as b,SC "
"WHERE a.cid<>b.cid and a.score=b.score")#迪卡尔的积
for each in cursor.fetchall():
print('%s\t%s\t%s'% each)
45 检索至少选修两门课程的学生学号cursor.execute('select SC.Sid from SC group by SC.Sid having count(SC.Sid)>=2')
for each in cursor.fetchall():
print('%s'% each)
46 查询全部学生都选修的课程的课程号和课程名
#cursor.execute('select COUNT(Ssex) from Student ')两个count不能同时在括号的左边和右边
cursor.execute('select SC.Cid, Course.Cname from SC,Student, Course where SC.Sid=Student.Sid and SC.Cid=Course.Cid group by SC.Cid HAVING COUNT(SC.Cid)=(SELECT COUNT(Ssex) FROM Student) ')#fromstudent之后需要加上Sid才能发挥作用
for each in cursor.fetchall():
print('%s\t%s'% each)
47 查询没学过"叶平"老师讲授的任一门课程的学生姓名(s)
cursor.execute('select DISTINCT Student.Sname from SC, Course, Student, Teacher where Course.Tid=Teacher.Tid and Student.Sid=SC.Sid AND SC.Cid=Course.Cid AND SC.Sid not in (select SC.Sid from SC, Course, Teacher WHERE SC.Cid=Course.Cid AND Course.Tid=Teacher.Tid AND Tname="叶平")')
for each in cursor.fetchall():
print('%s'% each)
48 查询两门以上不及格课程的同学的学号及其平均成绩(s)
cursor.execute('select a.Sid, AVG(a.score) from (select SC.Sid, SC.score from SC WHERE score<60) AS a GROUP BY a.Sid HAVING COUNT(a.Sid)>2')
for each in cursor.fetchall():
print('%s\t%s'% each)
49 检索"004"课程分数小于60，按分数降序排列的同学学号(s)
ORDER BY 跟在 where 后面
cursor.execute('select sid, score from SC where SC.score<60 AND SC.Cid=4 ORDER BY SC.score DESC ')
for each in cursor.fetchall():
print('%s\t%s'% each)
