查询表达式¶
查询表达式query_expression是PLOSCAR的核心表达式，PLOSCAR支持所有类型的神通数据库查询表达式。查询表达式生成一个查询结果集，也就是一个所谓的基本表。查询表达式的类型是集合类型，可以将查询表达式中的一条记录赋给一个记录变量或者行变量。
查询表达式可以是两个查询表达式的交、并、差，可以是一个表的连接，也可以是SELECT加上字段名列表和一个表表达式，还可以是一个集合类型的函数调用或过程调用。
查询表达式可以包含一个FROM子句、一个WHERE子句、一个GROUP BY子句和HAVING子句。
详细用法请参考《神通数据库SQL语言参考手册》“函数和操作符”章节
示例环境准备：
-- 清理环境
drop table student CASCADE;
--创建查询环境
create table student
(
id int,
name varchar(50),
subject varchar(50),
score int
);
insert into student values(1001,'xiaoming','Physics',90);
insert into student values(1001,'xiaoming','Maths',97);
insert into student values(1001,'xiaoming','Chemistry',94);
insert into student values(1002,'ganggang','Physics',80);
insert into student values(1002,'ganggang','Maths',70);
insert into student values(1002,'ganggang','Chemistry',90);
insert into student values(1003,'honghong','Physics',93);
insert into student values(1003,'honghong','Maths',95);
insert into student values(1003,'honghong','Chemistry',91);
insert into student values(1004,'zhangsan','Physics',95);
insert into student values(1004,'zhangsan','Maths',96);
insert into student values(1004,'zhangsan','Chemistry',95);
- SELECT ... WHERE 列或运算式比较运算运算[any|all](子查询）
只要主查询中列或运算式与子查询所得结果中任一（any）或全部（all）数据符合比较条件的话则主查询的结果为我们要的数据
示例：选出成绩最高的学生信息¶
SELECT * FROM student
WHERE score = all(SELECT MAX(score) FROM student);
ID(int) |NAME(varchar) |SUBJECT(varchar) |SCORE(int) |
----------------------------------------------------------------------
1001 |xiaoming |Maths |97 |
总数目：1
示例：选出所有成绩大于xiaoming的学生信息¶
SELECT * FROM student WHERE score > ANY(
SELECT score FROM student WHERE name = 'xiaoming') order by ID,SCORE;
ID(int) |NAME(varchar) |SUBJECT(varchar) |SCORE(int) |
----------------------------------------------------------------------
1001 |xiaoming |Chemistry |94 |
----------------------------------------------------------------------
1001 |xiaoming |Maths |97 |
----------------------------------------------------------------------
1003 |honghong |Chemistry |91 |
----------------------------------------------------------------------
1003 |honghong |Physics |93 |
----------------------------------------------------------------------
1003 |honghong |Maths |95 |
----------------------------------------------------------------------
1004 |zhangsan |Chemistry |95 |
----------------------------------------------------------------------
1004 |zhangsan |Physics |95 |
----------------------------------------------------------------------
1004 |zhangsan |Maths |96 |
总数目：8
- SELECT ...WHERE 列或运算式[NOT] IN (子查询)
只要主查询中列或运算式是在（不在）子查询所得结果列表中的话，则主查询的结果为我们要的数据
示例：选出所有成绩等于90的学生信息¶
SELECT * FROM student WHERE score IN (
SELECT score FROM student WHERE score = 90);
ID(int) |NAME(varchar) |SUBJECT(varchar) |SCORE(int) |
----------------------------------------------------------------------
1001 |xiaoming |Physics |90 |
----------------------------------------------------------------------
1002 |ganggang |Chemistry |90 |
总数目：2
- SELECT ...WHERE [NOT] EXISTS ( 子查询）
子查询的结果至少存在一条数据时，则主查询的结果为我们要的数据。（EXISTS）或自查询的结果找不到数据时，则主查询的结果为我们要的数据(NOT EXISTS)
示例：选出分数为九十分的学生信息¶
SELECT * FROM student WHERE EXISTS (
SELECT score FROM student WHERE score = 90) order by ID,SCORE;
ID(int) |NAME(varchar) |SUBJECT(varchar) |SCORE(int) |
----------------------------------------------------------------------
1001 |xiaoming |Physics |90 |
----------------------------------------------------------------------
1001 |xiaoming |Chemistry |94 |
----------------------------------------------------------------------
1001 |xiaoming |Maths |97 |
----------------------------------------------------------------------
1002 |ganggang |Maths |70 |
----------------------------------------------------------------------
1002 |ganggang |Physics |80 |
----------------------------------------------------------------------
1002 |ganggang |Chemistry |90 |
----------------------------------------------------------------------
1003 |honghong |Chemistry |91 |
----------------------------------------------------------------------
1003 |honghong |Physics |93 |
----------------------------------------------------------------------
1003 |honghong |Maths |95 |
----------------------------------------------------------------------
1004 |zhangsan |Physics |95 |
----------------------------------------------------------------------
1004 |zhangsan |Chemistry |95 |
----------------------------------------------------------------------
1004 |zhangsan |Maths |96 |
总数目：12
- SELECT ...WHERE ...GROUP BY
对所选的记录进行分组
示例：查询每个学生的最高成绩¶
SELECT MAX(score) FROM student GROUP BY id;
MAX(int) |
--------------
90 |
--------------
96 |
--------------
95 |
--------------
97 |
总数目：4
- SELECT ...WHERE ...ORDER BY
对所选的记录进行排序
示例：查询所有学生成绩从低到高排列¶
SELECT * FROM student ORDER BY score,ID;
ID(int) |NAME(varchar) |SUBJECT(varchar) |SCORE(int) |
----------------------------------------------------------------------
1002 |ganggang |Maths |70 |
----------------------------------------------------------------------
1002 |ganggang |Physics |80 |
----------------------------------------------------------------------
1001 |xiaoming |Physics |90 |
----------------------------------------------------------------------
1002 |ganggang |Chemistry |90 |
----------------------------------------------------------------------
1003 |honghong |Chemistry |91 |
----------------------------------------------------------------------
1003 |honghong |Physics |93 |
----------------------------------------------------------------------
1001 |xiaoming |Chemistry |94 |
----------------------------------------------------------------------
1003 |honghong |Maths |95 |
----------------------------------------------------------------------
1004 |zhangsan |Chemistry |95 |
----------------------------------------------------------------------
1004 |zhangsan |Physics |95 |
----------------------------------------------------------------------
1004 |zhangsan |Maths |96 |
----------------------------------------------------------------------
1001 |xiaoming |Maths |97 |
总数目：12
- SELECT ...WHERE ...GROUP BY ...HAVING...
对所选的分组记录添加额外的约束条件
示例：查询平均分¶
查询平均分数大于id为1001，科目为Physics 学生的其他学生的平均分
SELECT id, name, AVG(score) FROM student
GROUP BY id, name HAVING AVG(score) > (
SELECT score FROM student WHERE id=1001 AND subject = 'Physics');
ID(int) |NAME(varchar) |AVG(numeric) |
--------------------------------------------------
1001 |xiaoming |93.666666666 |
--------------------------------------------------
1004 |zhangsan |95.333333333 |
--------------------------------------------------
1003 |honghong |93 |
总数目：3
-- 删除表
DROP TABLE student CASCADE;