📄 readme_sqlite_tutorial.html
字号:
e1|2|Bob|Anderson|2|82|2004-10-02 15:33:16
e2|1|Sue|Carter|1|89|2004-10-02 16:04:12
e2|2|Sue|Carter|2|100|2004-10-02 16:04:12
</pre>
<p>
To summarize a query was performed on two tables that resided in separate databases.
This union created the virtual table. The select syntax is as
follows: SELECT <expression> FROM <TABLE>. For the table option we have
used the complete string "(select 'e1' as db,* from e1.exam union select 'e2'
as db,* from e2.exam)", which is our virtual table.
<p></p>
<p>
Here is a query example performed on this virtual table. Suppose you wanted
the maximum score by exam across databases.
</p>
<pre>
sqlite> select exam,max(score) from
(select 'e1' as db,* from e1.exam union select 'e2' as db,* from e2.exam)
group by exam;
1|89
2|100
</pre>
<p>
No problem. Above you got the maximum score for each exam, but who does it below to?
Find the ln and fn. But be careful, if you add "ln" and "fn" on the first part of
the select, you will get the WRONG answer.
</p>
<pre>
sqlite> select exam,max(score),ln,fn from
(select 'e1' as db,* from e1.exam union select 'e2' as db,* from e2.exam)
group by exam;
** THIS IS INCORRECT it should be Carter|Sue **
1|89|Anderson|Bob
2|100|Anderson|Bob
</pre>
<p>
The above answer is incorrect. "Anderson", "Bob" happens to be the name
that dropped down in this select statement. Again, it is not the correct
answer. If by chance you got the correct answer doing this query, then, it is
because you entered the names in a different order. If that is the case, perform
the query below which takes the min(score) - will will get an error on one of
these examples.
</p>
<p>
Here the min(score) is queried. By chance, because of the order data was entered
into this table, the correct answer is displayed.
</p>
<pre>
sqlite> select exam,min(score),ln,fn from
(select 'e1' as db,* from e1.exam union select 'e2' as db,* from e2.exam)
group by exam;
** correct answer -- just chance **
1|75|Anderson|Bob
2|82|Anderson|Bob
</pre>
<p>
Clearly there needs to be a better way of finding out who got the maximum and minimum
scores for each exam. By the way, here is the CORRECT sql statement that will
always give you the correct answer.
</p>
<pre>
sqlite> select db,ln,fn,exam,score from
(select 'e1' as db,* from e1.exam union select 'e2' as db,* from e2.exam)
where
(
score=(
select max(score) from
(select 'e1' as db,* from e1.exam union select 'e2' as db,* from e2.exam)
where exam=1
)
and exam = 1
)
OR
(
score=(
select max(score) from
(select 'e1' as db,* from e1.exam union select 'e2' as db,* from e2.exam)
where exam=2
)
and exam = 2
) ;
e2|Carter|Sue|1|89
e2|Carter|Sue|2|100
</pre>
<p>
Or it can be done as two independent select
statements as follows:
</p>
<pre>
sqlite> select db,ln,fn,exam,score from
(select 'e1' as db,* from e1.exam union select 'e2' as db,* from e2.exam)
where exam=1 order by score desc limit 1;
e2|Carter|Sue|1|89
</pre>
<p>
Here is the second select.
</p>
<pre>
sqlite> select db,ln,fn,exam,score from
(select 'e1' as db,* from e1.exam union select 'e2' as db,* from e2.exam)
where exam=2 order by score desc limit 1;
e2|Carter|Sue|2|100
</pre>
<p>
<h3>A Pivot Table </h3>
<p></p>
<p>
What if you wanted a pivot table where the scores are
listed across the top: exam1,exam2,..examN for each person.
For example the results will look like the following:
</p>
<pre>
fn ln exam1 exam2
--- -------- ----- -----
Bob Anderson 75 82
Sue Carter 89 100
</pre>
<p>
Also, is there a way to display the deltas between exams,
to have a 5th column that would show 7 points (82-75) or the delta
between exam1 and exam2 and similar data for Sue Carter?
</p>
<p>
Such power select statements can be done with the sign
function. And unlike the case statement, the sign function
can be place in the group by and having clause.
The sign function does not exist in sqlite; however,
that is not a problem, since we can easily create it.
<p>
<p>
As a side note, you wonder why you should create the sign
function. Instead, why not create an IF or IIF function?
The main reason is the IF statement is not standard on all
databases. And, on some databases where it is standard, MySQL,
it needs to be wrapped in an sum() function. Take a look at the
following Reference (
<a href=http://prdownloads.sourceforge.net/souptonuts/README_mysql.txt?download>LONGWINDED TIP 1</a>)
for an example of MySQL's IF statement that must be inside a sum statement, and how the sign function solves this problem.
(This was pointed out to me by Paul DuBois [paul at snake.net] )
<h2>
Power of the Sign Function - A Mathematical Explanation
</h2>
<p></p>
<p>
It may come as a shock, but the problems in the last
section, and much more, can be solved using the sign function.
This is just the simple sign function where sign(-200)=-1,.. sign(-1)=-1, sign(0)=0,
sign(1)=1,.. sign(300)=1. So if the number is > 0 a 1
is returned. Zero is the only number that returns zero.
All negative numbers return -1. Again, this simple
function does not exist in sqlite. But, you can
easily create it, permanently. The next section will
focus on the creation of this function; however, here the mathematical
properties are explained.
</p>
<p>
The sign function can define the absolute value function abs() as the value
of a number times it's sign. Or sign(x)*(x), abbreviated
sign(x)(x). Here is a more detailed look at this function.
</p>
<pre>
sign(x)(x) = abs(x)
Example, assume x=3
sign(3)(3) = abs(3)
1*3 = 3
Example, assume x=-3
sign(-3)(-3) = abs(-3)
-1*-3 = 3
Example, assume x=0
sign(0)(0) = abs(0)
0*0 = 0
</pre>
<p>
Comparsions can be made with the sign function betwen two
variables x and y. For instance, if sign(x-y)
is 1, then, x is greater than y.
</p>
<pre>
sign(x-y) is equal to 1 if x > y
sign(x-y) is equal to 0 if x = y
sign(x-y) is equl to -1 if x < y
</pre>
<p>
Now look closely at the 3 statements below.
The sign function starts to resemble an
IF statement, where a 1 is returned if and only if
x = y. Thoroughly understanding the statements below is
important, as rest of the discussion quickly builds
from these examples.
</p>
<pre>
IF ( X==Y ) return 1; ELSE return 0;
can be expressed as follows:
1 - abs(sign(x-y)) is equal to 0 if x > y
1 - abs(sign(x-y)) is equal to 1 if x = y
1 - abs(sign(x-y)) is equl to 0 if x < y
</pre>
<p>
It is possible to return a 1 if and only if x < y,
otherwise return a zero.
</p>
<pre>
IF ( X < Y ) return 1; ELSE return 0;
can be expressed as follows:
1-sign(1+sign(x-y)) is equal to 0 if x > y
1-sign(1+sign(x-y)) is equal to 0 if x = y
1-sign(1+sign(x-y)) is equal to 1 if x < y
</pre>
<p>
The last example is known as the delta for x<y, or
Delta[x<y]. This Delta notation will be used instead
of writing it out in long form or using the IF statement.
Therefore, the following is a summarized table of all
the Delta functions or comparison operators.
</p>
<pre>
Delta[x=y] = 1 - abs(sign(x-y))
Delta[x!=y] = abs(sign(x-y))
Delta[x<y] = 1-sign(1+sign(x-y))
Delta[x<=y] = sign(1-sign(x-y))
Delta[x>y] = 1-sign(1-sign(x-y))
Delta[x>=y] = sign(1+sign(x-y))
Delta[z=x AND z=y] = sign( Delta[z=x]*Delta[z=y] )
Delta[z=x OR z=y] = sign( Delta[z=x]+Delta[z=y] )
Delta[z>x AND z<y] = sign( Delta[z>x]*Delta[z<y] )
... more can be defined ... but you get the idea
</pre>
<p>
To summarize the following if statement, note
the introduction of a third variable z, below
</p>
<pre> if( x==y )
return z;
else
return 0;
</pre>
<p>
The above expression, in Delta notation, is the following:
</p>
<pre>
z*Delta[x=y]
</pre>
<p>
Here is an interesting example.
</p>
<pre>
create table logic (value int);
insert into logic (value) values (1);
insert into logic (value) values (0);
insert into logic (value) values (-1);
</pre>
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -