事先声明:此文章适合已经有基本的增删改查概念的用户。其语法基于Oracle(其实数据库在SQL语法方面都一样),如果对一些关键词有所不理解,比如WHERE、SELECT等等,请自行谷歌。谢谢。
哇,吓一大跳!
select major_name,studentCount,count(b.student_id) as jiuyrenshu ,count(b.student_id)/studentCount
from(select m.major_name, count(s.student_id) as studentCount from student s,class_ c_,major m
where s.class_id in c_.class_id and c_.grade_id = '001' and c_.major_id = '001' and m.major_id=c_.major_id
group by m.major_name) a,
(select distinct student_id from employment_register_society where verify_state=1
union
select distinct student_id from employment_register_school where verify_state=1) b
group by major_name,studentCount;
若能够一眼看出这句SQL的意思,这篇文章对于各位大佬只有巩固的作用了
SELECT语句的执行规范
DB会自觉的对提交的SQL语句进行语法和语义分析,并且优化SQL的执行效率。如果不是DBA的话,在书写SQL时只要考虑会不会写和有没有较优秀的执行效率就好。所以从开发者的视角出发,优先从SELECT开始,剖析下“开发者视角下的SQL执行规范”。
无论如何复杂的SQL语句,每一步都可以看做:(备注:行筛选指的是对SQL查询结果进行每一条数据的筛选。列筛选是对SQL查询结果进行每一个字段的筛选)
1.对结果集(虚拟表)进行筛选
#WHERE
SELECT * FROM [TABLE_A] WHERE [TABLE_A].[COLUMN_A] = "X";
#这句SQL描述了从表TABLE_A中以[TABLE_A].[COLUMN_A] = "X"为条件,筛选出*(所有的)内容。这是两步:
#第一步,WHERE条件对表TABLE_A进行行筛选;
#第二步,SELECT条件对第一步产生的结果集进行列筛选。
#ON
SELECT [TABLE_A].[COLUMN_A],[TABLE_B].[COLUMN_B] FROM [TABLE_A] LEFT JOIN [TABLE_B] ON [TABLE_A].[COLUMN_A] = [TABLE_B].[COLUMN_B];
#这句SQL描述了:
#第一步:从表[TABLE_A]中关联上表[TABLE_B],两表以ON的内容为条件关联
#第二步:以第一步的结果集(虚拟表)用SELECT进行列筛选
#HAVING
SELECT [TABLE_A].[COLUMN_A] FROM [TABLE_A] GROUP BY [TABLE_A].[COLUMN_A] HAVING MAX([TABLE_A].[COLUMN_B]) > 0;
#这句SQL描述了:
#第一步:GROUP BY对表TABLE_A这个结果集进行分组
#第二步:HAVING对第一步分组完的结果集进行行筛选
#第三步:SELECT对第二步筛选完的结果集进行列筛选
2.对结果集(虚拟表)进行分组
#GROUP BY
SELECT MAX([TABLE_A].[COLUMN_B]) FROM [TABLE_A] GROUP BY [TABLE_A].[COLUMN_A]
#这句SQL描述了:
#1.第一步:对TABLE_A,以COLUMN_A字段进行分组
#2.第二步:对于第一步的分组条件,筛选出每组最大的COLUMN_B
3.对结果集(虚拟表)进行排序
#ORDER BY
SELECT [TABLE_A].[COLUMN_A] FROM [TABLE_A] ORDER BY [TABLE_A].[COLUMN_A]
#这句SQL描述了:
#第一步:从TABLE_A以SELECT的条件进行列筛选
#第二步:以第一步的结果,根据COLUMN_A的内容进行排序
这三步我们看完后,继续重新回到文章开头的那句SQL,他是怎么跑通的呢?
1.student s,class_ c_,major m三张表,以WHERE为条件,进行列筛选
2.GROUP BY以m.major_name为条件对第1步进行分组
3.SELECT以count(s.student_id)为条件对第2步进行列筛选
4.将第3步的结果集取别名 a
5.表employment_register_society,以WHERE为条件,进行列筛选
6.DISTINCT对第5步进行进行唯一性筛选(俗称去重)
7.表employment_register_school,以WHERE为条件,进行列筛选
8.DISTINCT对第7步进行进行唯一性筛选(俗称去重)
9.用UNION拼凑第6步和第8步的结果集,取别名 b
10.关联第4步和第9步的结果集
11.GROUP以major_name,studentCount为条件对第10步结果集进行分组
12.SELECT对第11步的结果集进行进一步运算和筛选
复杂吗?其实这一点也不复杂。为什么前面提到大佬一眼就能看出文章开头的那句SQL的意思?其实简单点说:
第二层引用在什么:一部分需要的数据在a,一部分需要的数据在b
第一层引用在干嘛:把第一层引用的数据结合起来,再查需要的内容
SO EASY!
SELECT语句的执行流程
上一段已经提到过了,SQL语句中的每一步都可以看做对结果集的一次操作,每一步都在根据1.表(结果集)之间的关系2.判断筛选3.排序展示,形成不同的结果集,然后下一步在上一步的结果集之下,在对结果集进行操作。这些操作有1.表(结果集)之间的关系2.判断筛选3.排序展示。理解了干什么(语义),下面就得知道怎么干(语法)。
就像所有的程序语言一样,是将等号右边的赋值给等号左边。语法是约定俗称的。SQL语句可以建立在语法的基础上随意发挥。那么基础是?
WITH {BLOCK}
(7)SELECT (8) DISTINCT (10) <TOP_COUNT><SELECT_LIST>
(3) FROM <TABLE(FROM)>
(1) <JOIN_TYPE> JOIN <TABLE(JOIN)>
(2) ON <CONDITION(JOIN)>
(4) WHERE <CONDITION(FROM)>
(5) GROUP BY <GROUP_BY_LIST>
(6) HAVING <CONDITION(GROUP BY)>
(9) ORDER BY <ORDER_BY_LIST>
这里面WITH比较特殊先避开不谈,循序渐进的来(除特殊粗体字说明,每一步以上一步的结果集为准,进行进一步处理):
1.2.[LEFT_TABLE] <JOIN_TYPE> JOIN [RIGHT_TABLE] ON <CONDITION(JOIN)>
以WHERE的一张表为[LEFT_TABLE]以一定规则(左关联、全关联等等)JOIN上一张[RIGHT_TABLE],ON后面是两张表JOIN的条件。
JOIN理解上难了点,后面会并上第四步一起讲。
3.FROM [TABLE_A]
FROM之后的单表形成一个结果集;或者多表强关联形成一个结果集。
4.WHERE <CONDITION(FROM)>
WHERE条件对FROM组成了的结果集进行筛选。
以一个示例规范前四步:
SELECT * FROM [TABLE_LEFT] LEFT JOIN [TABLE_RIGHT] ON <CONDITION(JOIN)>,[TABLE_A] WHERE [TABLE_A].[COLUMN_A] = [TABLE_RIGHT].[COLUMN_RIGHT]
1.2.JOIN以一定规则(这里是LEFT JOIN),连接[TABLE_LEFT],[TABLE_RIGHT],连接条件是ON <CONDITION(JOIN)>。
3.4.FROM以第一步的结果集和[TABLE_A]强关联(一般WHERE条件后的表逗号关联都这么说,或者叫笛卡尔积?),条件为WHERE <CONDITION(FROM)>,这里的WHERE可以以FROM形成的结果集内任何一个COLUMN为条件(尤其是不受SELECT影响,毕竟这会SELECT还未对结果集造成影响,所以SELECT对结果集的筛选这里也是没用的。所以别抱怨说为嘛SELECT的COUNT(1)在WHERE中不好下条件之类的)。
5.6.GROUP BY <GROUP_BY_LIST> HAVING <CONDITION(GROUP BY)>
对之前的结果集进行分群,以方便下面一些特殊函式的使用(例如COUNT、SUM、MAX都是常见的分群函数),HAVING则是只可以对分群进行条件筛选
7.SELECT <SELECT_LIST>
最容易理解的,对结果集进行列筛选。这里面常见的两个注意点就是:
第一点:SELECT是在分群之后的结果集进行操作的,请注意。
第二点:在SELECT中进行子查询时,其实就是遍历结果集的行,把每行的值传入从而得到结果。而SELECT只是对结果集进行筛选,返回多结果就会报错。
8.DISTINCT
DISTINCT去重。其唯一要关注的是,其是对SELECT完的结果集去重,即不是对DISTINCT后面接的第一个COLUMN去重,也不是对WHERE的结果集去重
9.ORDER BY <ORDER_BY_LIST>
ORDER BY排序。排序只可以以SELECT的结果集排序。被SELECT剔除的列不参加排序
10.<TOP_COUNT>
TOP在ORACLE是没有的,所以就没得用,但是记住顺序即可。
11.WITH
WITH虽说留到最后,其实也不是大头,WITH简单点说就是临时表。在书写WITH时会建立一个临时表为SELECT语句所用。这一点上很像可以直接作为结果集使用的VIEW(用的较少,如果要用WITH一般早建VIEW了)。
好了,更具上面的顺序来说几件有趣的事
- 按COLUMN_A排序,取前十条?
由于ORACLE没有TOP,很多人喜欢用SELECT * FROM [TABLE_A] WHERE ROWNUM <=10 ORDER BY [COLUMN_A]
这句话错到不能再错,因为WHERE在ORDER BY之前,所以这是先筛前十再排序。 - 以一条SELECT语句为结果集?
SELECT语句为什么能复杂到无以复加?正是因为,就上面这样的九步齐全的SELECT语句依然可以作为一个结果集参加另一个SELECT语句。上述凡是关键词包含TABLE的,都是可以被另一个结果集取代的。
不多说,下面的语句用来复习上述内容,并不讲解。有问题可以留言。
1.SELECT * FROM [TABLE_A];
2.SELECT * FROM [TABLE_A] WHERE [TABLE_A].[COLUMN_A] = "X";
3.SELECT * FROM [TABLE_A] JOIN [TABLE_B] ON [TABLE_A].[COLUMN_A] = [TABLE_B].[COLUMN_B] WHERE [TABLE_A].[COLUMN_A] = "X";
4.SELECT [TABLE_A].[COLUMN_A],MAX([TABLE_A].[COLUMN_B]) FROM [TABLE_A] JOIN [TABLE_B] ON [TABLE_A].[COLUMN_A] = [TABLE_B].[COLUMN_B] WHERE [TABLE_A].[COLUMN_A] = "X" GROUP BY [TABLE_A].[COLUMN_A];
5.WITH TABLE_NAME AS (SELECT * FROM [TABLE_C])
SELECT [TABLE_C].[COLUMN_C],MAX([TABLE_A].[COLUMN_B]) FROM [TABLE_A] JOIN [TABLE_B] ON [TABLE_A].[COLUMN_A] = [TABLE_B].[COLUMN_B] WHERE [TABLE_A].[COLUMN_A] = [TABLE_C].[COLUMN_C] GROUP BY [TABLE_A].[COLUMN_A];
6.SELECT [TABLE_A].[COLUMN_A],MAX([TABLE_A].[COLUMN_B]) FROM [TABLE_A] JOIN [TABLE_B] ON [TABLE_A].[COLUMN_A] = [TABLE_B].[COLUMN_B] GROUP BY [TABLE_A].[COLUMN_A] HAVING MAX([TABLE_A].[COLUMN_B]) > 0;
7.SELECT DISTINCT [TABLE_A].[COLUMN_A],MAX([TABLE_A].[COLUMN_B]) FROM [TABLE_A] JOIN [TABLE_B] ON [TABLE_A].[COLUMN_A] = [TABLE_B].[COLUMN_B] WHERE [TABLE_A].[COLUMN_A] = "X" GROUP BY [TABLE_A].[COLUMN_A];
8.SELECT DISTINCT [TABLE_A].[COLUMN_A],MAX([TABLE_A].[COLUMN_B]) FROM [TABLE_A] JOIN [TABLE_B] ON [TABLE_A].[COLUMN_A] = [TABLE_B].[COLUMN_B] WHERE [TABLE_A].[COLUMN_A] = "X" GROUP BY [TABLE_A].[COLUMN_A] ORDER BY [TABLE_A].[COLUMN_A];
UPDATE?DELETE?INSERT?
在书写UPDATE、DELETE、INSERT,原则是极简。原因很简单,这三者会动到表内容。
所以:
UPDATE
UPDATE [TABLE] SET [TABLE].[COLUMN_B] = ? WHERE [TABLE].[COLUMN_A] = ?
问号处补充具体的值,或者进一步更方便的写法,用子查询填充:
UPDATE [TABLE_A] SET [TABLE_A].[COLUMN_B] = (SELECT [TABLE_B].[COLUMN_A] FROM [TABLE_B] WHERE [TABLE_B].[COLUMN_B] = [TABLE_A].[COLUMN_A])
#当[TABLE_A]中[COLUMN_A]等于[TABLE_B]的[COLUMN_B],则更新[TABLE_A]中[COLUMN_A]为[TABLE_B]中的[COLUMN_A]
#顶多再多写WHERE条件进行子查询筛选,不推荐多写复杂的
DELETE
DELETE FROM [TABLE_A] WHERE [TABLE_A].[COLUMN_B] = ?
#顶多?处子查询,不能再多。
INSERT
INSERT INTO [TABLE_A](?) VALUES(?)
INSERT INTO [TABLE_A](?) SELECT ? FROM ?
#这个额外多说一句,在团体开发中,特别是表结构可能都没稳定的时候,一定要把INSERT的[TABLE_A](?)写具体了。这个问题听话就好,不多说
MARGE?
MARGE是ORACLE中的得力干将,简单点说,是用来解决下面的情况的:存在则UPDATE,不存在则INSERT。这里有兴趣的就谷歌搜一搜了。实际用处倒也不多。毕竟复杂的情况没法用,简单的情况用不到。
CONNECT BY?
这是一个复杂且常用的写法。不知大家对BOM的概念如何。不说这个数据结构树也是一种。再不说这个,在非关系数据库中,尤其是搜索引擎,特别喜欢层次数据结构,都与这个关键词脱不了干系。
简单点说CONNECT BY是用来父子关系存在一个表中的理清树这种结构关系的,这种关系在产品结构、爬虫抓取的网页资料等十分常见。因为这边介绍基础,所以有兴趣的自己查吧。
编程语言?
CASE THEN WHEN END?
SUBSTR?
等等等等,SQL语句中还有很多操作,但这些操作就是很简单的了。基本明确一个概念,他们就是你给他传参,他给你回传值就好。其实就是简单的逻辑语句和函数应用而已。
网友评论