一、SQL 的执行逻辑
这一节的原文地址是lifepoem
首先我们来看一条完整的SQL语句
(5) SELECT (5-2) DISTINCT (5-3) TOP(<top_specification>) (5-1) <select_list>
(1) FROM (1-J) <left_table> <join_type> JOIN <right_table> ON <on_predicate>
| (1-A) <left_table> <apply_type> APPLY <right_table_expression> AS <alias>
| (1-P) <left_table> PIVOT(<pivot_specification>) AS <alias>
| (1-U) <left_table> UNPIVOT(<unpivot_specification>) AS <alias>
(2) WHERE <where_predicate>
(3) GROUP BY <group_by_specification>
(4) HAVING <having_predicate>
(6) ORDER BY <order_by_list>;
可以看到，SQL语言和其他编程语言的第一个区别就是代码的处理顺序。在多数编程语言中，代码按照其书写的顺序依次处理。而对于SQL，第一个被处理的子句是FROM，尽管SELECT子句最先出现，但它几乎在最后处理。
查询处理的每一步都会生成一个虚表，它会成为下一步骤的输入。这些虚表对调用者是不可见的，只有最后一步生成的表才会返回给调用者。如果查询中没有指定某个子句，其对应的步骤则被略过。下面简单的描述了不同的逻辑步骤，如果你现在对这些描述不甚了解也不用担心，稍后的例子会让你得到更好的理解和更详细的说明。
(1) FROM
FROM处理阶段会确定查询的数据源表并处理表操作符。每个表操作符都会有一系列的子步骤。比如，Join包含的子步骤有：
- (1-J1) 笛卡儿积（Cross Join）,
- (1-J2) 应用ON条件,
- (1-J3) 添加外部数据行（Add Outer Rows）.
FROM阶段生成虚表VT1.
- (1-J1) Cross Join 这个阶段针对两个数据表执行cross join，生成VT1-J1.
- (1-J2) ON Filter 这个阶段会基于ON子句中的条件来对VT1-J1中的记录进行过滤，只有条件返回TRUE的数据行会被插入到VT1-J2.
- (1-J3) Add Outer Rows 如果指定了外连接（OUTER JOIN），保留表中没有匹配成功的行会加入到VT1-J2的数据行中，生成VT1-J3.
(2) WHERE
这个阶段会基于WHERE子句的条件对VT1表进行过滤，只有条件返回TRUE的数据行会被插入到VT2.
(3) GROUP BY
对VT2中的数据行进行分组，生成VT3. 这样对于每一个组将只有一条记录。
(4) HAVING
基于HAVING子句中的条件对VT3中的分组数据进行过滤，只有条件返回TRUE的分组数据行会被插入到VT4.
(5) SELECT
处理SELECT子句中的元素，生成VT5. 它包含以下几个子步骤：
- (5-1) Evaluate Expressions 对SELECT列表进行求值，生成VT5-1.
- (5-2) DISTINCT 去除VT5-1中的重复行，生成VT5-2.
- (5-3) TOP 基于ORDER BY子句定义的逻辑顺序，过滤出顶部的相应行数（或百分比行数），生成VT5-3.
(6) ORDER BY
对VT5-3中的数据行进行排序，生成游标(cursor) VC6
如果用图表来表示即
二、练习题目
节选于文章：SQL面试，让你的面试官无fu,ck可说，第17题难倒一片人
SQL的各种查询技巧参见：sql各种查询技巧
(1) 数据表
Orders(OID,month,datetime,account,price) --OID 订单编号,month 学订单月份,datetime 订单时间,account 订单账户，price 订单价格
Student(SID,Sname,Sage,Ssex) --SID 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别
Course(CID,Cname,TID) --CID --课程编号,Cname 课程名称,TID 教师编号
Teacher(TID,Tname) --TID 教师编号,Tname 教师姓名
SC(SID,CID,score) --SID 学生编号,CID 课程编号,score 分数
(2) 练习题目
0、信息展示
select a.*, b.score as score_01,c.score as score_02,d.score as score_03,
ifnull(b.score,0)+ifnull(c.score,0)+ifnull(d.score,0) as score_total
from Student a
left join SC b
on a.SID=b.SID and b.CID='01'
left join SC c
on a.SID = c.SID and c.CID='02'
left join SC d
on a.SID =d.SID and d.CID='03'
order by a.SID;
1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
# 查询同时存在"01"课程和"02"课程的情况
select a.* , b.score as score_01,c.score as score_02
from Student a , SC b , SC c
where a.SID = b.SID
and a.SID = c.SID
and b.CID = '01'
and c.CID = '02'
and b.score > c.score;
# 查询存在"01"课程但可能不存在"02"课程的情况
select a.* , b.score,c.score
from Student a
left join SC b
on a.SID = b.SID and b.CID = '01'
left join SC c
on a.SID = c.SID and c.CID = '02'
where b.score > ifnull(c.score,0);
2、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
# 查询在sc表存在成绩的学生信息的SQL语句
select a.* ,avg(b.score) as score_avg
from Student a,SC b
where a.SID=b.SID
group by a.SID
having avg(b.score) < 60
order by a.SID;
#查询在sc表中不存在成绩的学生信息的SQL语句
select a.SID , a.Sname , ifnull(avg(b.score),0) as count_score
from Student a
left join sc b on a.SID = b.SID
group by a.SID
having ifnull(avg(b.score),0) < 60
order by a.SID;
3、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
select a.SID, a.Sname, count(b.CID) as CSum, ifnull(sum(b.score),0) as Score_Sum
from Student a
left join SC b
on a.SID = b.SID
group by a.SID
order by a.SID;
4、查询没学过"张三"老师授课的同学的信息
select m.* from Student m
where m.SID not in
(select distinct SC.SID
from SC , Course , Teacher
where SC.CID = Course.CID and Course.TID = Teacher.TID and Teacher.Tname = '张三')
order by m.SID;
5、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
# 方法一：在大的信息表中查询
select a.*, b.score as score_01,c.score as score_02
from Student a
left join SC b
on a.SID=b.SID and b.CID='01'
left join SC c
on a.SID = c.SID and c.CID='02'
where b.score>0 and isnull(c.score)
order by a.SID;
#方法二：直接来
select a.*
from Student a,SC b
where a.SID =b.SID and b.CID='01'
and a.SID not in
(select a.SID from Student a,SC b where a.SID=b.SID and b.CID='02');
6、查询和"01"号的同学学习的课程完全相同的其他同学的信息
select Student.* from Student
where SID in
(select distinct SC.SID
from SC
where SID <> '01' and SC.CID in
(select distinct CID from SC where SID = '01')
group by SC.SID
having count(1) =
(select count(1) from SC where SID='01')
);
7、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select a.SID as 学生编号 , a.Sname as 学生姓名 ,
max(case c.Cname when '语文' then b.score else null end) as 语文,
max(case c.Cname when '数学' then b.score else null end) as 数学,
max(case c.Cname when '英语' then b.score else null end) as 英语,
cast(avg(b.score) as decimal(18,2)) as 平均分,
sum(b.score) as 总分
from Student a
left join SC b
on a.SID = b.SID
left join Course c
on b.CID = c.CID
group by a.SID , a.Sname
order by 平均分 desc
8、随机选取1条学生信息
# 简单但效率可能比较低的方法
select * from Student order by rand() limit 1;
# 一种高效的方式，手动找一个随机的ID号
select * from Student
where SID >= (
(select min(SID) from Student)+
((select max(SID) from Student)-(select min(SID) from Student))*rand()
) limit 1;
9、选择年龄大于等于26岁的学生信息
select *,date_format(from_days(to_days(now())-
to_days(Sage)),'%Y')+0 as age from Student
where date_format(from_days(to_days(now())-
to_days(Sage)),'%Y')+0>=26
order by age;
10、选取总分排名前三的学生及其总成绩
因为MySQL没有 rank() 函数，所以我们用limit来实现
# 分数相同的随机选择其一
select a.SID,a.Sname,sum(b.score) as total
from Student as a,SC as b
where a.SID=b.SID
group by a.SID
order by sum(b.score) desc
limit 5;
此时排名是连续的，分数相同的时候只能选择其一。如果需要都被选上，我们可以这样解读排名。排名第一的同学代表比他分数高的同学人数为0，排名第二的同学代表比他分数高的同学人数为1，以此类推。于是便有了下面的方法
#创建临时表tmp
create table tmp (
select a.SID,a.Sname,sum(b.score) as total
from Student as a,SC as b
where a.SID=b.SID
group by a.SID);
#选择不连续排名前5的同学
select a.*
from tmp as a
left join tmp as b
on a.total < b.total
group by a.SID
having count(b.total)<5;
11、5月份有订单6月份没订单的用户[京东的面试题]
Order(userid,orderid,month,price)
# 方法一
select userid ,month,count(orderid) as count
from order
group by userid,month
having (month=5 and count(orderid)>0) or
(month=6 and count(orderid)=0)；
三、MySQL 技巧分析
1、联结技巧
SQL join技巧
注1：MySQL没有
full join，图中左下角可以用 left join union all right join 实现注2：
left outer join 等价于 left join注3：
select a.* ,b.* from tablea as a inner join tableb as b on a.key=b.key 等价于 select a.*,b.* from tablea as a,tableb as b where a.key=b.key注4：
select a.* ,b.* from tablea as a cross join tableb as b 等价于 select a.*,b.* from tablea as a,tableb as b
2、UNION
union：上下连接，返回不重复数据
union all：上下连接，返回重复数据
差集Except:
SELECT ID FROM (
SELECT DISTINCT A.AID AS ID FROM TABLE_A A
UNION ALL
SELECT DISTINCT B.BID AS ID FROM TABLE_B B
)TEMP GROUP BY ID HAVING COUNT(ID) = 1
交集Intersect:
SELECT ID FROM (
SELECT DISTINCT A.AID AS ID FROM TABLE_A A
UNION ALL
SELECT DISTINCT B.BID AS ID FROM TABLE_B B
)TEMP GROUP BY ID HAVING COUNT(ID) = 2
四、MySQL的安装和使用
1、安装
下载 msi文件 MySQL ，一路默认就好
2、添加到环境变量
3、CMD启动方式
# cmd下运行
mysql -uroot -p
mysql>show 数据库名;
mysql> use 具体数据库名;
mysql> 执行语句
4、python3.x启动方式（参考文章）
# 如果电脑安装了MySQL
import pymysql
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='******',db='*****');
cursor = conn.cursor()
cursor.execute('select * from table')
cursor.fetchone()
# 如果不像安装任何数据库(SQL lite)
from pandasql import sqldf
pysqldf = lambda q: sqldf(q, globals())
df=pd.DataFrame()
d=pysqldf('select * from df')
5、MySQL的导入导出
5-0： 准备工作
我使用的是MySQL5.7，平台是win10。在导入导出数据前得先开权限。
- 找到MySQL的数据目录：
C:\ProgramData\MySQL\MySQL Server 5.7 - 找到
my.ini文件，注释掉其中的secure-file-priv = "*************" - 保存重启MySQL即可
- 注意导入导出时，数据都只能在工作目录中进行，其他地方不被允许.
5-1： 导入数据（.sql、.txt、.csv)
# =========导入 sql文件===========
# 方法一
mysql -u 用户名 -p 数据库名 < 文件名.sql
#方法二
mysql>use 数据库名;
mysql>source 文件名.sql;
# =========导入 文本 文件===========
mysql> LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl
-> CHARACTER SET utf8
-> FIELDS TERMINATED BY ':'
-> LINES TERMINATED BY '\r\n';
5-1 ： 导出数据（.sql、.txt、.csv)
# ====直接导出数据库=======
mysqldump -u用户名 -p 数据库名 > 文件名.sql
mysqldump -u用户名 -p 数据库名 表名> 文件名.sql
mysqldump -u用户名 -p 数据库名 表名> 文件名.txt
# ====SQL语句导出数据到文件=======
mysql -uroot -p123456 --default-character-set=utf8
mysql> use 数据库名
mysql> SELECT * FROM EMPLOYEE INTO OUTFILE '/tutorials.txt'
-> FIELDS TERMINATED BY ','
-> OPTIONALLY ENCLOSED BY '"'
-> LINES TERMINATED BY '\r\n';
参考文章：
SQL教程
SQL语法大全
MySQL学习笔记（1）—— 入门与初步
MySQL学习笔记（2）—— 数据类型和列类型
MySQL学习笔记（3）—— MySQL的安装与配置
MySQL学习笔记（4）—— MySQL 的服务器管理
MySQL学习笔记（5）—— Python 操作 MySQL
MySQL学习笔记（6）—— MySQL 的数据库操作
MySQL学习笔记（7）—— MySQL 查询/更新数据记录
网友评论