一般数据检索

阅读(3326) 标签: 一般数据检索,

本章主要展示关于一般数据检索的代码参考,比如SELECT * FROM …、WHERE …、SELECT FROMAS、常数表、ORDER BY/ASC/DESCDISTINCTFISRT/LAST/TOP/BOTTOM UNION/UNION ALL/INTERSECT/MINUS SELECT FROM (SELECT )SELECT (SELECT FROM) FROM CURSOR/FETCH等。

SELECT * FROM …

 

A

 

1

=demo.query("select * from EMPLOYEE")

 

 

WHERE …

 

A

 

1

=demo.query("select * from EMPLOYEE")

 

2

=A1.select(SALARY>5000)

 

 

SELECT … FROM

 

A

 

1

=demo.query("select * from EMPLOYEE")

 

2

=A1.new(EID,NAME)

 

 

AS

 

A

 

1

=demo.query("select * from EMPLOYEE")

 

2

=A1.new(EID:EmployeeNo,NAME+" "+SURNAME: EmployeeName)

 

 

常数表

 

A

 

1

=[[1,"Beverage"]].new(~(1):TypeNumber,~(2):Name)

创建1条记录的序表

2

=create(TypeNumber,TypeName).record([1,"Beverage",2,"Grain"])

先创建空序表后填数

 

ORDER BY/ASC/DESC

 

A

 

1

=demo.query("select * from EMPLOYEE")

 

2

=A1.sort(BIRTHDAY,-SALARY)

 

 

DISTINCT

 

A

 

1

=demo.query("select * from SALES")

 

2

=A1.id(CLIENT)

取唯一值

3

=A1.(CLIENT)

所有出现值

4

=A1.([CLIENT,SELLERID])

组合的所有出现值

 

FISRT/LAST/TOP/BOTTOM

 

A

 

1

=demo.query("select * from EMPLOYEE")

 

2

=A1.m(1).NAME

FIRST

3

=A1.m(-1).NAME

LAST

4

=A1.m(to(3))

TOP 3

5

=A1.to(-3)

BOTTOM 3

 

UNION/UNION ALL/INTERSECT/MINUS

 

A

 

1

=demo.query("select * from EMPLOYEE")

 

2

=A1.select(DEPT=="Sales" || DEPT=="R&D")

 

3

=A1.select(SALARY>5000)

 

4

=A2|A3

UNION ALL

5

=A2&A3

UNION

6

=A2^A3

INTERSECTION

7

=A2\A3

DIFFERENCE

 

SELECT … FROM (SELECT … )

 

A

 

1

=demo.query("select * from EMPLOYEE")

 

2

=A1.select(DEPT=="Sales")

执行查询

3

=A2.count()

对结果集再统计

 

SELECT (SELECT … FROM) FROM

 

A

 

1

=demo.query("select * from EMPLOYEE")

 

2

=demo.query("select * from FAMILY")

 

3

=A1.derive()

 

4

=A1.run(EID=A2.select(EID:A1.EID))

先计算出子表引用

5

=A1.new(NAME,EID.count():NumberOfMembers)

 

6

=A3.new(NAME,A2.select(EID:A3.EID).count():NumberOfMembers)

直接写的方法

 

CURSOR/FETCH

 

A

B

C

 

1

=demo.cursor("select * from SALES")

 

 

 

2

for

 

 

 

3

 

=A1.fetch(100)

 

每次取100

4

 

if B3==null

break

 

5