3分钟带你了解SQL高级操作

这是菜鸟学Python的第94篇原创文章

阅读本文大概需要3分钟

比如我们有一个这样的数据库，里面是记录员工的名字，年龄，性别和工资的一些二维数据表：

cursor=conn.execute(<span style="color: #faea29;">"SELECT sex,</span><span style="color: #ffc66d; font-style: italic;">sum</span><span style="color: #faea29;">(salary) "
                    "FROM Employee GROUP BY sex "</span>)
for row in cursor:
    print row

cursor=conn.execute(<span style="color: #faea29;">"SELECT sex,</span><span style="color: #ffc66d; font-style: italic;">sum</span><span style="color: #faea29;">(salary) "
                    "FROM Employee "
                    "WHERE Employee.age>=30 "
                    "GROUP BY sex ORDER BY salary  "</span>)
for row in cursor:
     print row

cursor=conn.execute(<span style="color: #faea29;">"SELECT sex,</span><span style="color: #ffc66d; font-style: italic;">sum</span><span style="color: #faea29;">(salary) "
                    "FROM Employee "
                    "WHERE age>20 and age<=30 "
                    "GROUP BY sex ORDER BY salary  "</span>)
for row in cursor:
     print row

>>

(u'M', 5000.0)

(u'W', 26500.0)

• 上面三种情况都是以sex为类别进行GROUP BY 分组，加上WHERE来做条件判断，并且加上ORDER BY 排序
• 但是有一点要注意GROUP BY 必须要在WHERE 之后，然后ORDER BY 之前

有的时候我们需要在数据库里面模糊查询，Python里面有正则可以做类似的操作，数据库里面有没有类似的呢~~有啊，有的SQL里面有两个关键字可以搞定：LIKE 和GLOB

SQLite 的 LIKE 运算符:

是用来匹配通配符指定模式的文本值

百分号 （%）:代表零个、一个或多个数字或字符。

下划线 （_）:代表一个单一的数字或字符

SQLite 的 GLOB运算符:

星号 （*）:代表零个、一个或多个数字或字符

问号 （?）:代表一个单一的数字或字符

有同学说这两个不是一回事吗，怎么要两个关键字呢，这位同学观察真仔细，其实是有区别的，GLOB 是大小写敏感的，而Like不区分大小写

1).用like过滤出所有字母J打头的员工的薪资:

cursor=conn.execute(<span style="color: #faea29;">"SELECT name,age,salary "
                    "FROM Employee WHERE name LIKE 'j%' "</span>)
for row in cursor:
     print row

>>

(u'Jack', 23, 5000.0)

(u'James', 31, 11000.0)

2).用Glob过滤出所有字母J打头的员工的薪资:

cursor=conn.execute(<span style="color: #faea29;">"SELECT name,age,salary "
                    "FROM Employee WHERE name GLOB 'J*' "</span>)
for row in cursor:
     print row

>>

(u'Jack', 23, 5000.0)

(u'James', 31, 11000.0)

当数据库的数量非常大的时候，我们有的时候希望取头部，或者中间的一部分数据

比如：取排序之后的Top3,怎么办呢，SQL里面有一个关键字Limit

1).取出数据库里的工资最高的3人

cursor=conn.execute(<span style="color: #faea29;">"SELECT </span><span style="color: #ffc66d;">*</span><span style="color: #faea29;"> FROM Employee "
                    "ORDER BY salary DESC LIMIT 3"</span>)
for row in cursor:
     print row

>>

(4, u'Martin', 35, u'M', 14000.0)

(5, u'Suan', 28, u'W', 14000.0)

(3, u'Lily', 30, u'W', 12500.0)

2).我们希望从数据库里面的第3个开始取3个人的薪资

(1, u'Jack', 23, u'M', 5000.0)

(2, u'James', 31, u'M', 11000.0)

(3, u'Lily', 30, u'W', 12500.0)

(6, u'Lisa', 32, u'W', 10000.0)

(4, u'Martin', 35, u'M', 14000.0)

(5, u'Suan', 28, u'W', 14000.0)

怎么操作呢，很简单，看源码:

cursor=conn.execute(<span style="color: #faea29;">"SELECT </span><span style="color: #ffc66d;">*</span><span style="color: #faea29;"> FROM Employee "
                    "ORDER BY name DESC LIMIT 3 OFFSET 2"</span>)
for row in cursor:
     print row

>>

(6, u'Lisa', 32, u'W', 10000.0)

(3, u'Lily', 30, u'W', 12500.0)

(2, u'James', 31, u'M', 11000.0)

LIMIT 3 OFFSET 2: 表示从第三行开始，向下偏移2行，这样一共取3行的数据

上面的关键字都是进行一次过滤得到了一些分组结果，如果我们想在上面过滤出的结果中，再进行二次过滤，怎么办呢，SQL有一个关键字Having

1).先按名字进行过滤分组:

cursor=conn.execute(<span style="color: #faea29;">"SELECT name,age FROM Employee "
                    "GROUP BY name "</span>)
for row in cursor:
    print row

>>

(u'Jack', 23)

(u'James', 31)

(u'Lily', 30)

(u'Lisa', 32)

(u'Martin', 35)

(u'Suan', 28)

2).在上面的分组结果，再进行二次过滤，筛选出薪资>10000

cursor=conn.execute(<span style="color: #faea29;">"SELECT name,age,salary FROM Employee "
                    "GROUP BY name HAVING salary>10000"</span>)
for row in cursor:
    print row

>>

(u'James', 31, 11000)

(u'Lily', 30, 12500)

(u'Martin', 35, 14000)

(u'Suan', 28, 14000)

如果我们的数据库里面的名字有重复的怎么办，我如何统计唯一的名单，我们知道在Python中用set()去掉列表重复元素，那么数据库中如何操作呢，用关键字Distinct

比如：我们的数据库有2个人名是一样的

(1, u'Jack', 23, u'M', 5000.0)

(2, u'James', 31, u'M', 11000.0)

(3, u'Lily', 30, u'W', 12500.0)

(4, u'Lisa', 32, u'W', 10000.0)

(5, u'Martin', 35, u'M', 14000.0)

(6, u'Suan', 28, u'W', 14000.0)

(7, u'Jack', 28, u'W', 6000.0)

(8, u'James', 31, u'M', 12000.0)

现在我们要统计这个数据库里面的人名(唯一性)

cursor=conn.execute(<span style="color: #faea29;">"SELECT DISTINCT </span><span style="color: #ffc66d; font-style: italic;">count</span><span style="color: #faea29;">(name) "
                    "FROM Employee "</span>)
for row in cursor:
    print row

>>

6

结论：

好了，SQLite数据库的高级操作就讲到这里,是不是很简单啊，如果大家对今天的内容有什么疑问，欢迎留言一起讨论，上面这些都是单表的复杂操作。数据库还有很多一对多，多对多的操作，以及最复杂的多表联合查询和精选的实战例子，敬请期待吧.

来源 | 菜鸟学Python

作者 | xinxin

本篇文章来源于: 菜鸟学Python