本文围绕SQL展开，介绍了on后接where and的运算顺序，明确on执行顺序在where前；阐述了SQL语句的运算顺序；对比了视图和表的区别与联系；说明了python3对接mysql数据库的问题及解决办法；讲解了Oracle中排名函数的运用，还介绍了数据库中OVER语句的使用。
一、on后面接where and 的运算顺序
（10分）说明以下两个sql语句的区别：
（1）select a.* from a left join b on a.id = b.id where b.id = 3;
（2）select a.* from a left join b on a.id = b.id and b.id = 3;
上面是个面试题，好像现在好多面试SQL的都会问这个问题吧。
首先，明确一点，on的执行顺序在where之前。
对于（1）SQL语句：
先进行a表和b表的左连接操作生成table1表
--首先执行左连接生成table1
(select a.* from a left join b on a.id = b.id) as table1在对生成的table1表进行where操作，筛选出b.id=3的表a所有字段和条数
对于（2）SQL语句：
由于，on的执行顺序在前。即先从b表中筛选出b.id=3的所有行，再与表a进行左连接操作。此时，筛选出来的条数与a表的条数一致
所以，SQL（1）筛选出来的条数小于等于SQL（2）的条数。
二、SQL语句的运算顺序
from-->[join on]-->where-->group by-->having-->select-->order by
使用窗口函数时的运算顺序： Note that window functions are performed on the result set after all JOIN, WHERE, GROUP BY, and HAVING clauses and before the ORDER BY, LIMIT and SELECT DISTINCT.
三、视图和表的区别
数据库中的数据都是存储在表中的，而视图只是一个或多个表依照某个条件组合而成的结果集，一般来说你可以用update，insert，delete等sql语句修改表中的数据，而对视图只能进行select操作。但是也存在可更新的视图，对于这类视图的update，insert和delete等操作最终会作用于与其相关的表中数据。因此，表是数据库中数据存储的基础，而视图只是为了满足某种查询要求而建立的一个对象。
1.视图是数据库数据的特定子集。可以禁止所有用户访问数据库表，而要求用户只能通过视图操作数据，这种方法可以保护用户和应用程序不受某些数据库修改的影响。2.视图是抽象的，他在使用时，从表里提取出数据，形成虚的表。 不过对他的操作有很多的限制 。
3. 而且视图是永远不会自己消失的除非你删除它。
视图有时会对提高效率有帮助。临时表几乎是不会对性能有帮助，是资源消耗者。
视图一般随该数据库存放在一起，临时表永远都是在tempdb里的。
4.视图适合于多表连接浏览时使用!不适合增、删、改.，存储过程适合于使用较频繁的SQL语句，这样可以提高 执行效率!
视图和表的区别和联系
区别：
1、视图是已经编译好的sql语句。而表不是
2、视图没有实际的物理记录。而表有。
3、表是内容，视图是窗口
4、表只用物理空间而视图不占用物理空间，视图只是逻辑概念的存在，表可以及时对它进行修改，但视图只能有创建的语句来修改
5、表是内模式，视图是外模式
6、视图是查看数据表的一种方法，可以查询数据表中某些字段构成的数据，只是一些SQL语句的集合。从安全的角度说，视图可以不给用户接触数据表，从而不知道表结构。
7、表属于全局模式中的表，是实表；视图属于局部模式的表，是虚表。
8、视图的建立和删除只影响视图本身，不影响对应的基本表。
联系：视图（view）是在基本表之上建立的表，它的结构（即所定义的列）和内容（即所有数据行）都来自基本表，它依据基本表存在而存在。一个视图可以对应一个基本表，也可以对应多个基本表。视图是基本表的抽象和在逻辑意义上建立的新关系
四、python3对接mysql数据库
select * from orderinfo where date_format(o_date, '%Y-%m') = date_format(date_sub(curdate(), interval 1 month),'%Y-%m')用python3进行上述语句查询时，报错 TypeError: not enough arguments for format string 产生原因是语句中有% 此时要使用%%。 总之，要插入%, 应该写成%%, 否则系统会认为你是字符串的格式化。
下面是用python3本地连接mysql数据库的一般方法
import pymysql
class MysqlHelper(object):
def __init__(self, host, port, db, user, passwd, charset='utf8'):
self.host = host
self.port = port
self.db = db
self.user = user
self.passwd = passwd
self.charset = charset
def connect(self, dict=False):
self.conn = pymysql.connect(host=self.host,
port=self.port,
db=self.db,
user=self.user,
passwd=self.passwd,
charset=self.charset)
#返回查询结果为字典
if dict == True:
self.cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor)
else:
self.cursor = self.conn.cursor()
def close(self):
self.cursor.close()
self.conn.close()
def get_one(self, sql, params=()):
result = None
try:
self.connect()
self.cursor.execute(sql, params)
result = self.cursor.fetchone()
self.close()
except Exception as e:
print(e)
return result
def get_all(self, sql, params=()):
#不知道fetchall返回什么形式的结果
result = None
try:
self.connect()
self.cursor.execute(sql, params)
result = self.cursor.fetchall()
self.close()
except Exception as e:
print(e)
return result
def insert(self, sql, params=()):
self.__edit(sql=sql, params=params)
def update(self, sql, params=()):
self.__edit(sql=sql, params=params)
def delete(self, sql, params=()):
self.__edit(sql=sql, params=params)
def __edit(self, sql, params=()):
count = 0
try:
self.connect()
count = self.cursor.execute(sql, params)
self.conn.commit()
self.close()
except Exception as e:
self.conn.rollback()
print(e)
return count
if __name__ == '__main__':
params = {
'host':'127.0.1.1',
'port':3306,
'db':'test',
'user':'root',
'passwd':'123456'
}
sql = MysqlHelper(**params)
tmp = "select * from orderinfo " \
"where date_format(o_date, '%%Y-%%m') = date_format(date_sub(curdate(), interval 1 month),'%%Y-%%m')"
result = sql.get_all(tmp)
print(result)五、Oracle中row_number(), rank(), dense_rank()用于排名
MySQL中写排名确实有点麻烦~~~
下面是三个函数的运用结构
- ROW_NUMBER() OVER([PARTITION BY col1] ORDER BY col2)
- DENSE_RANK() OVER([PARTITION BY col1] ORDER BY col2)
- RANK() OVER([PARTITION BY col1] ORDER BY col2)
三个函数都是按照col1分组内从1开始排序
ROW_NUMBER() 是没有重复值的排序(即使两条记录相同，序号也不重复的)，不会有同名次。例如：1,2,3,4（没有重复）
DENSE_RANK() 是连续的排序，两个第二名仍然跟着第三名。例如：1,2,2,3
RANK() 是跳跃排序，两个第二名下来就是第四名；例如：1,2,2,4
下面是一张score表，s_id为学生编号；c_id为课程编号；s_score为课程得分
select * from score;
+------+------+---------+
| s_id | c_id | s_score |
+------+------+---------+
| 01 | 01 | 80 |
| 01 | 02 | 50 |
| 01 | 03 | 99 |
| 02 | 01 | 70 |
| 02 | 02 | 71 |
| 02 | 03 | 80 |
| 03 | 01 | 80 |
| 03 | 02 | 65 |
| 03 | 03 | 80 |
| 04 | 01 | 50 |
| 04 | 02 | 35 |
| 04 | 03 | 61 |
| 05 | 01 | 76 |
| 05 | 02 | 73 |
| 06 | 01 | 31 |
| 06 | 03 | 34 |
| 07 | 02 | 78 |
| 07 | 03 | 98 |
+------+------+---------+
18 rows in set (0.00 sec)
求：每科同学的排名
--row_number() 按顺序排，每一行都只有唯一的排名
select s_id, c_id, row_number() over(partition by c_id order by s_score desc) as 名次 from score;--rank()跳跃排名，两个第二名后是第四名；第1 第2 第2 第4
select s_id, c_id, rank() over(partition by c_id order by s_score desc) as 名次 from s_score;--dense_rank() 连续排序，如果有两个第一时，接下来是第二; 第1 第1 第2
select s_id, c_id, dense_rank() over(partition by c_id order by s_score desc) as 名次 from score;下面是截取：https://www.cnblogs.com/qiuting/p/7880500.html 的一些比较重要的东西。
关于Parttion by：
Parttion by关键字是Oracle中分析性函数的一部分，用于给结果集进行分区。它和聚合函数Group by不同的地方在于它只是将原始数据进行名次排列，能够返回一个分组中的多条记录（记录数不变），而Group by是对原始数据进行聚合统计，一般只有一条反映统计值的结果（每组返回一条）。
TIPS：
使用rank over()的时候，空值是最大的，如果排序字段为null, 可能造成null字段排在最前面，影响排序结果。
可以这样： rank over(partition by course order by score desc nulls last)
总结：
在使用排名函数的时候需要注意以下三点：
1、排名函数必须有 OVER 子句。
2、排名函数必须有包含 ORDER BY 的 OVER 子句。
3、分组内从1开始排序
MySQL中实现row_number()的方式：http://www.360doc.com/content/19/0802/09/12906439_852519519.shtml
MySQL 8.0支持窗口函数：https://www.cnblogs.com/DataArt/p/9961676.html
六、数据库中的OVER
在五中，使用row_numver() rank()， dense_rank()时，有用到over语句。记录
OVER用于为行定义一个窗口，它对一组值进行操作，不需要使用GROUP BY子句对数据进行分组，能够在同一行中同时返回基础行的列和聚合列。
OVER 使用结构： OVER( [PARTITION BY col1] [ORDER BY col2] )
PARTITION BY进行分组，ORDER BY 进行排序。窗口函数OVER指定一组行，开窗函数计算从窗口函数输出的结果集中各行的值。开窗函数不需要GROUP BY就可以对数据进行分组，还可以同时返回基础行的列和聚合列。
OVER开窗函数必须与聚合函数（MAX(),MIN(),SUM()）或排序函数（RANK(),ROW_NUMVER(),DENSE_RANK()）一起使用。
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
