MySQl(二)——数据库的查询
准备工作
1.在db_1数据库中创建一个Student和Course表
import pymysql
# 连接db_1数据库
db = pymysql.connect('localhost','root','111','db_1')
2.导入表
# 创建一个表 Student Sdept所在系
sql1 = "create table Student( \
Sno char(9) primary key, \
Sname char(20) unique ,\
Ssex char(2) not null, \
Sage smallint not null,\
Sdept char(20) );"
#Cpno 先修课
sql2 = "create table Course( \
Cno char(4) primary key,\
Cname char(40) not null,\
Cpno char(4) not null, \
Ccredit smallint );"
cursor.execute(sql1)
cursor.execute(sql2)
sql4 = "show tables;"
cursor.execute(sql4)
tables = cursor.fetchall()
for table in tables:
print(table)
db.close() #每次命令执行完不要忘记关闭数据库的连接
基本表导入成功
3.向表中插入记录
# 插入
sql4 = "insert into Student(Sno,Sname,Ssex,Sage,Sdept)\
values('201701','lily','m',20,'network project');"
sql5 = "insert into Student(Sno,Sname,Ssex,Sage,Sdept)\
values('201702','lucy','f',19,'network project');"
sql6 = "insert into Student(Sno,Sname,Ssex,Sage,Sdept) \
values('201703','Boy','m',21,'network project');"
sql7 = "insert into Student(Sno,Sname,Ssex,Sage,Sdept) \
values('201704','lilo','m',21,'Big data');"
cursor.execute(sql4)
cursor.execute(sql5)
cursor.execute(sql6)
cursor.execute(sql7)
db.commit()
一、基本查询语句
# 具体问题,具体应用
select [all|distinct] <目标列表达式> [,<目标列表达式>]......
from <表名或视图名> [<,表名或视图名>]......
[where <条件表达式>]
[group by <列名1> [having <条件表达式>]]
[order by <列名2> [asc|desc]]
二、单表查询
1>查询所有字段
try:
cursor.execute("select * from Student")
result = cursor.fetchall()
for row in result:
Sno = row[0]
Sname = row[1]
Ssex = row[2]
Sage = row[3]
Sdept = row[4]
print("Sno=%s,Sname=%s,Ssex=%s,Sage=%d,Sdept=%s"%(Sno,Sname,Ssex,Sage,Sdept))
except:
print("Error: unable to fetch data!")
2>查询指定字段
# 查询指定字段
try:
cursor.execute("select Sno,Sname,Sdept from Student")
result = cursor.fetchall()
for row in result:
Sno = row[0]
Sname = row[1]
Sdept = row[2]
print("Sno=%s,Sname=%s,Sdept=%s"%(Sno,Sname,Sdept))
except:
print("Error: unable to fetch data!")
3>查询指定数据
# 查询指定数据
# 查询系名为Big data系的学生
try:
cursor.execute("select Sno,Sname,Sdept from Student where Sdept='Big data'")
result = cursor.fetchall()
for row in result:
Sno = row[0]
Sname = row[1]
Sdept = row[2]
print("Sno=%s,Sname=%s,Sdept=%s"%(Sno,Sname,Sdept))
except:
print("Error: unable to fetch data!")
4>带IN关键字的查询
# 带in关键字的查询
# 查询年龄在(15,18,21)之内的学生
try:
cursor.execute("select Sno,Sname,Sdept from Student where Sage in (15,18,21)")
result = cursor.fetchall()
for row in result:
Sno = row[0]
Sname = row[1]
Sdept = row[2]
print("Sno=%s,Sname=%s,Sdept=%s"%(Sno,Sname,Sdept))
except:
print("Error: unable to fetch data!")
5>带BETWEEN AND的范围查询
# 查询年龄在18到20之间(包括18和20)的学生的信息
try:
cursor.execute("select Sno,Sname,Sdept from Student where Sage between 18 and 20;")
result = cursor.fetchall()
for row in result:
Sno = row[0]
Sname = row[1]
Sdept = row[2]
print("Sno=%s,Sname=%s,Sdept=%s"%(Sno,Sname,Sdept))
except:
print("Error: unable to fetch data!")
6>带LIKE的字符匹配查询
# '_'表示一个字符,'%'表示一个至多个字符
# like的用法
# 查询姓名是以l开头中间含有l字母的学生信息
try:
cursor.execute("select Sno,Sname,Sdept from Student where Sname like 'l%l%';")
result = cursor.fetchall()
for row in result:
Sno = row[0]
Sname = row[1]
Sdept = row[2]
print("Sno=%s,Sname=%s,Sdept=%s"%(Sno,Sname,Sdept))
except:
print("Error: unable to fetch data!")
7>用IS NULL关键字查询控制
# is not null查询
# 查询看学生的专业是否有为空的
try:
cursor.execute("select Sno,Sname,Sdept from Student where Sdept is not null;")
result = cursor.fetchall()
for row in result:
Sno = row[0]
Sname = row[1]
Sdept = row[2]
print("Sno=%s,Sname=%s,Sdept=%s"%(Sno,Sname,Sdept))
except:
print("Error: unable to fetch data!")
# is null的用法和is not null的用法相似
# 结果显示,Student基本表中的所有学生的专业都不为空
8>带AND的多条件查询
# And查询
# 查看Sdept不为空并别性别是男生的学生
try:
cursor.execute("select Sno,Sname,Sdept from Student where Sdept is not null and Ssex='f';")
result = cursor.fetchall()
for row in result:
Sno = row[0]
Sname = row[1]
Sdept = row[2]
print("Sno=%s,Sname=%s,Sdept=%s"%(Sno,Sname,Sdept))
except:
print("Error: unable to fetch data!")
9>带OR的多条件查询
# or查询
# 查看姓名里有y或者性别为男的学生
try:
cursor.execute("select Sno,Sname from Student where Sname like '%y%' or Ssex='m';")
result = cursor.fetchall()
for row in result:
Sno = row[0]
Sname = row[1]
print("Sno=%s,Sname=%s"%(Sno,Sname))
except:
print("Error: unable to fetch data!")
10>用DISTINCT关键字去除结果中的重复行
# distinct
## 查看Student表中的学生都来自哪个系
try:
cursor.execute("select distinct Sdept from Student;")
result = cursor.fetchall()
for row in result:
# Sno = row[0]
Sdept = row[0]
print("Sdept=%s"%(Sdept))
except:
print("Error: unable to fetch data!")
11>用ORDER BY关键字对查询结果排序
# order by
# 按性别将学生记录排序,默认为升序
try:
cursor.execute("select Sno,Sname from Student order by Ssex;")
result = cursor.fetchall()
for row in result:
Sno = row[0]
Sname = row[1]
print("Sname=%s"%(Sname))
except:
print("Error: unable to fetch data!")
12>用GROUP By关键字分组查询
# 按系分组
try:
cursor.execute("select Sno,Sdept from Student group by Sdept;")
result = cursor.fetchall()
for row in result:
Sno = row[0]
Sdept = row[1]
print("Sno=%s,Sdept=%s"%(Sno,Sdept))
except:
print("Error: unable to fetch data!")
13>用LIMIT限制查询结果的数量
limit
# 限制返回的记录数为2
# 默认为前两条,limit的两个参数,第一个为开始的位置(类似索引从0开始),第二个为返回的记录数目
try:
cursor.execute("select Sno,Sdept from Student limit 0,2;")
result = cursor.fetchall()
for row in result:
Sno = row[0]
Sdept = row[1]
print("Sno=%s,Sdept=%s"%(Sno,Sdept))
except:
print("Error: unable to fetch data!")
三、聚合函数查询
1>count(),当为count(*)的时候则也对空记录进行选择
## 按院系将学生分组
#try:
# cursor.execute("select count(Sno) number,Sdept from Student group by Sdept;")
# result = cursor.fetchall()
# for row in result:
# number = row[0]
# Sdept = row[1]
# print("number=%d,Sdept=%s"%(number,Sdept))
#except:
# print("Error: unable to fetch data!")
2>sum()
# 计算所有学生加起来的年龄有多大
try:
cursor.execute("select sum(Sage) sum_sage from Student;")
result = cursor.fetchall()
for row in result:
sum_sage = row[0]
print("sum_sage=%d"%(sum_sage))
except:
print("Error: unable to fetch data!")
3>avg
# 计算这些学生的平均年龄
try:
cursor.execute("select avg(Sage) avg_sage from Student;")
result = cursor.fetchall()
for row in result:
avg_sage = row[0]
print("avg_sage=%d"%(avg_sage))
except:
print("Error: unable to fetch data!")
4>max
# max
# 求取年龄最大的学生信息
try:
cursor.execute("select max(Sage) max_sage from Student;")
result = cursor.fetchall()
for row in result:
max_sage = row[0]
print("max_sage=%d"%(max_sage))
except:
print("Error: unable to fetch data!")
# 求取年龄最大的学生信息
try:
cursor.execute("select * from Student where Sage =(select max(Sage) from Student);")
result = cursor.fetchall()
for row in result:
Sno = row[0]
Sname=row[1]
Ssex=row[2]
Sage=row[3]
Sdept = row[4]
print("Sno=%s,Sname=%s,Ssex=%s,Sage=%d,Sdept=%s"%(Sno,Sname,Ssex,Sage,Sdept))
except:
print("Error: unable to fetch data!")
5>min
# min
# 求取年龄最小的学生的信息
try:
cursor.execute("select min(Sage) min_sage from Student;")
result = cursor.fetchall()
for row in result:
min_sage = row[0]
print("min_sage=%d"%(min_sage))
except:
print("Error: unable to fetch data!")
四、连接查询
1>首先向表Course插入几行记录
try:
cursor.execute("select * from Course")
result = cursor.fetchall()
for row in result:
Cno = row[0]
Cname = row[1]
Cpno = row[2]
Ccredit = row[3]
print("Cno=%s,Cname=%s,Cpno=%s,Ccredit=%d"%(Cno,Cname,Cpno,Ccredit))
except:
print("Error: unable to fetch data!")
2>由于表Student和Course不能进行连接,需要再导入一张SC表
sql12 = "create table sc( \
Sno char(9) , \
Cno char(4) ,\
grade smallint,\
primary key(Sno,Cno));" #这一点是需要注意的
cursor.execute(sql12)
# 向sc表中插入信息
sql13 = "insert into sc(Sno,Cno,grade)\
values('201701','1',91);"
sql14 = "insert into sc(Sno,Cno,grade)\
values('201702','2',93);"
sql15 = "insert into sc(Sno,Cno,grade) \
values('201703','3',95);"
sql16 = "insert into sc(Sno,Cno,grade) \
values('201704','4',97);"
cursor.execute(sql13)
cursor.execute(sql14)
cursor.execute(sql15)
cursor.execute(sql16)
db.commit()
3> 三张表内连接
try:
cursor.execute("select Student.Sname,Course.Ccredit,sc.grade from Student,Course,sc where Course.Cno=sc.Cno and Student.Sno=sc.Sno")
result = cursor.fetchall()
for row in result:
Sname = row[0]
Ccredit = row[1]
grade = row[2]
print("Sname=%s,Ccredit=%d,grade=%d"%(Sname,Ccredit,grade))
except:
print("Error: unable to fetch data!")
五、子查询
1>带in关键字的子查询
sql = "select Sno,Sname from Student where Sno in (select sc.Sno from sc);"
mysql查询结果,五部分的结果均为mysql的界面下的结果 ![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/d6a0926e1c59a962605fc2458fe4af54.png)
2>带比较运算符的子查询
sql1 = "select grade from sc where grade > (select min(grade) from sc);"
3>带exists关键字的子查询
sql2 = “select * from sc where exists (select * from Course where Cpno=5);”
使用exists关键字时,内层查询语句不返回查询的记录,只返回一个布尔值(True、False),所以子查询所返回的表头属性都用*来代替,指定表头属性没有意义,如果返回值为false时外查询不进行查询或者查询不到任何记录
sql2 = "select * from sc where exists (select * from Course where Cpno=5);"
4>带any关键字的子查询
sql3 = "select grade from sc where grade > ANY(select grade from sc);"
sql3_1 = "select grade from sc where grade < ANY(select grade from sc);"
5>带all关键字的子查询
sql4 = "select grade from sc where grade > ALL(select grade from sc);"
# 因为没有大于sc的最高分的分数,所以返回空
六、合并查询结果
# 由于该表不适合这个查询命令执行,故只贴了代码
1> union试讲所有的查询结果合并起来,去除相同的记录
sql = "select user from tb_book union select user from tb_login"
cursor.extecute(sql)
#然后查询看看表就知道怎么回事了
2>union all 关键字只是简单地将结果合并到一起
sql="select user from tb_book union all select user from tb_login"
cursor.extecute(sql)
#然后查询看看表就知道怎么回事了
七、定义表和字段的别名
1> 为表取别名
# 为表取别名
try:
cursor.execute("select * from Student s where s.Sname='lucy';")
result=cursor.fetchall()
for row in result:
Sno = row[0]
Sname=row[1]
Ssex=row[2]
Sage=row[3]
Sdept = row[4]
print("Sno=%s,Sname=%s,Ssex=%s,Sage=%d,Sdept=%s"%(Sno,Sname,Ssex,Sage,Sdept))
except:
print("Error: unable to fetch data!")
2>为字段取别名
# 为字段取别名
try:
cursor.execute("select Sno,Sage from Student;")
result=cursor.fetchall()
for row in result:
Sno = row[0]
Sage = row[1]
print("Sno=%s,age=%d,"%(Sno,Sage))
except:
print("Error: unable to fetch data!")
# 在python中给字段取别名,只需在输出的时候将名字改一下即可,而在mysql中操作如下
八、使用正则表达式查询
可以手动点击→mysql|菜鸟教程(关于正则表达式的模式字符)
字段名 REGEXP ‘匹配方式’
1>匹配指定字符中的任意一个,只要满足其中的一个就输出
try:
cursor.execute("select * from Student where Sname REGEXP '[luc]';")
result=cursor.fetchall()
for row in result:
Sno = row[0]
Sname=row[1]
Ssex=row[2]
Sage=row[3]
Sdept = row[4]
print("Sno=%s,Sname=%s,Ssex=%s,Sage=%d,Sdept=%s"%(Sno,Sname,Ssex,Sage,Sdept))
except:
print("Error: unable to fetch data!")
2>使用“*”和“+”匹配多个字符
+ 和*是有区别的,“+”号至少标识一个字符 “*”可以表示零个字符
try:
cursor.execute("select * from Student where Sname REGEXP 'l*y';")
result=cursor.fetchall()
for row in result:
Sno = row[0]
Sname=row[1]
Ssex=row[2]
Sage=row[3]
Sdept = row[4]
print("Sno=%s,Sname=%s,Ssex=%s,Sage=%d,Sdept=%s"%(Sno,Sname,Ssex,Sage,Sdept))
except:
print("Error: unable to fetch data!")
try:
cursor.execute(“select * from Student where Sname REGEXP ‘l+y’;”)
result=cursor.fetchall()
for row in result:
Sno = row[0]
Sname=row[1]
Ssex=row[2]
Sage=row[3]
Sdept = row[4]
print(“Sno=%s,Sname=%s,Ssex=%s,Sage=%d,Sdept=%s”%(Sno,Sname,Ssex,Sage,Sdept))
except:
print(“Error: unable to fetch data!”)
总结:用Python实现的mysql其实都只有cursor.execute()中的参数内容在变化,其他的大致都是一样的,有时候特殊的操作不要忘记pymysql.connect对象的commit方法的应用