📄 readme_sqlite_tutorial.html
字号:
sqlite> select 'e1' as db,* from e1.exam union select 'e2' as db,* from e2.exam;
db ekey fn ln exam score timeEnter
---------- ---------- ---------- ---------- ---------- ---------- -------------------
e1 1 Bob Anderson 1 75 2004-10-17 22:01:42
e1 2 Bob Anderson 2 82 2004-10-17 22:02:19
e1 3 Bob Anderson 3 92 2004-10-17 22:05:04
e1 4 Bob Anderson 4 95 2004-10-17 22:05:16
e1 5 Tom Stoppard 1 88 2004-10-17 22:05:24
e1 6 Tom Stoppard 2 90 2004-10-17 22:05:31
e1 7 Tom Stoppard 3 92 2004-10-17 22:05:40
e1 8 Tom Stoppard 4 95 2004-10-17 22:05:50
e2 1 Sue Carter 1 89 2004-10-17 22:03:10
e2 2 Sue Carter 2 100 2004-10-17 22:03:10
e2 3 Sue Carter 3 99 2004-10-17 22:05:57
e2 4 Sue Carter 4 95 2004-10-17 22:06:05
sqlite>
</pre>
<p>
Below, continuing with the same attached setup,
is an example of horizontal averages and horizontal maximum values.
</p>
<pre>
sqlite> .headers on
sqlite> .mode column
sqlite> select db,ln as lastname,fn as first,
sum(score*(1-abs(sign(exam-1)))) as exam1,
sum(score*(1-abs(sign(exam-2)))) as exam2,
sum(score*(1-abs(sign(exam-3)))) as exam3,
sum(score*(1-abs(sign(exam-4)))) as exam4,
avg(score) as avg, max(score) as max
from (select 'e1' as db,* from e1.exam union select 'e2' as db,* from e2.exam)
group by ln,fn,db ;
db lastname first exam1 exam2 exam3 exam4 avg max
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---- ----
e1 Anderson Bob 75 82 92 95 86 95
e2 Carter Sue 89 100 99 95 95.75 100
e1 Stoppard Tom 88 90 92 95 91.25 95
</pre>
<p>
Try finding the delta's, or the differences between each
exam score. For hints on this, reference the end
of the following (
<a href=http://prdownloads.sourceforge.net/souptonuts/README_mysql.txt?download>article </a>)
in the LONGWINDED TIPS section.
</p>
<p>
<h3>Pivot Table "Spreadsheet Format" to Normalized Data</h3>
<p></p>
<p>
Consider the reverse: suppose you had a pivot table, or the data in
spreadsheet like format, and you wanted a normalized table of exams.
For this example the table nonormal is needed. This table is defined
and created as follows:
</p>
<pre>
SQLite version 3.0.8
Enter ".help" for instructions
sqlite> attach database 'examdatabase' as e1;
sqlite> attach database 'examdatabase2' as e2;
sqlite> create table e1.nonormal as
select ln,fn,
sum(score*(1-abs(sign(exam-1)))) as exam1,
sum(score*(1-abs(sign(exam-2)))) as exam2,
sum(score*(1-abs(sign(exam-3)))) as exam3,
sum(score*(1-abs(sign(exam-4)))) as exam4
from (select 'e1' as db,* from e1.exam union select 'e2' as db,* from e2.exam)
group by ln,fn;
sqlite> .header on
sqlite> .mode column
sqlite> select * from e1.nonormal;
ln fn exam1 exam2 exam3 exam4
---------- ---------- ---------- ---------- ---------- ----------
Anderson Bob 75 82 92 95
Carter Sue 89 100 99 95
Stoppard Tom 88 90 92 95
</pre>
<p>
Above the nonormal table was created in the examdatabase, since "e1." was
given before the name. Again, the objective here is to go back-wards and create
a normalized table from the pivot table: a table that will list all exam scores in one field,
and all the exam numbers in another, without having a separate field
for each exam. In addition, the goal is to do all this in one select statement without
looping through the data. First it is necessary to create a number table "enum",
and it must have the field "e" from 1..N where N is the number of exams, which
is 4 in this case.
</p>
<pre>
sqlite> CREATE TABLE enum (e int);
sqlite> INSERT INTO "enum" VALUES(1);
sqlite> INSERT INTO "enum" VALUES(2);
sqlite> INSERT INTO "enum" VALUES(3);
sqlite> INSERT INTO "enum" VALUES(4);
</pre>
<p>
The coalesce function is used in an interesting way for this example.
</p>
<pre>
sqlite> .mode list
sqlite> select n.ln,n.fn,
1*(1-abs(sign(e.e-1)))+
2*(1-abs(sign(e.e-2)))+
3*(1-abs(sign(e.e-3)))+
4*(1-abs(sign(e.e-4))),
coalesce(0/(e.e-1),n.exam1)+
coalesce(0/(e.e-2),n.exam2)+
coalesce(0/(e.e-3),n.exam3)+
coalesce(0/(e.e-4),n.exam4)
from enum as e,e1.nonormal as n;
Anderson|Bob|1|75
Carter|Sue|1|89
Stoppard|Tom|1|88
Anderson|Bob|2|82
Carter|Sue|2|100
Stoppard|Tom|2|90
Anderson|Bob|3|92
Carter|Sue|3|99
Stoppard|Tom|3|92
Anderson|Bob|4|95
Carter|Sue|4|95
</pre>
<p>
For more examples see the following article:
<a href=http://prdownloads.sourceforge.net/souptonuts/Solving_Complex_SQL_Problems.txt?download>
Solving_Complex_SQL_Problems.txt</a>
</p>
<p><h3>
Max Min Problems
</h3>
<p></p>
<p>
Assume you have the following table of names, ages, and salaries,
find the age, name and salary, of youngest person making the
over all highest salary. Or, first find the highest salary, then,
from this group select the youngest person.
</p>
<pre>
create table salary (name varchar(3),age int, salary double);
insert into salary values ('dan',23,67);
insert into salary values ('bob',45,94);
insert into salary values ('tom',24,94);
insert into salary values ('sue',23,45);
insert into salary values ('joe',45,51);
insert into salary values ('sam',22,51);
</pre>
<p>
Or, once you have the data entered you will have the following;
</p>
<pre>
sqlite> .headers on
sqlite> .mode column
sqlite> select * from salary;
name age salary
---------- ---------- ----------
dan 23 67
bob 45 94
tom 24 94
sue 23 45
joe 45 51
sam 22 51
sqlite>
</pre>
<p>
The following select will give you the youngest person making
the top salary in the company.
<pre>
sqlite> select 1000-max(salary*1000-age)%1000 from salary;
1000-max(salary*1000-age)%1000
------------------------------
24
</pre>
<p>
This is the correct answer. The highest salary is
94 for bob and tom. Tom is the youngest at 24.
</p>
<p>
First, why the number 1000. Well, no one lives to be
1000, so we know that age will never be >= 1000.
Therefore max(salary*1000-age)
will clearly choose the highest salary
independent of age, as long as salary is >= 1. In
cases of a tie in salary, the youngest person will subtract the
the least amount from the salary. So this value will
return as the highest. Now, it's easy to remove the
salary part from this number. Since salary is multiplied
by 1000 it will disappear with mod 1000, because it
it's a perfect factor of 1000.
</p>
<p>
To understand how this works it is helpful to break the statement into
separate, smaller parts as follows:
</p>
<pre>
sqlite> select salary*1000-age,salary*1000,-age from salary;
salary*1000-age salary*1000 -age
--------------- ----------- ----------
66977 67000 -23
93955 94000 -45
93976 94000 -24
44977 45000 -23
50955 51000 -45
50978 51000 -22
sqlite>
</pre>
<p>
But what about the negative value for age? With the
non-Knuth method of the mod function, '%' , a negative
number -x will always for -x.
<pre>
x % y is defined as follows:
x % y == x - INT(x/y)*y
and undefined for y == 0. The language
C and Fortran use this method.
In contrast the Knuth method, also found in python,
an accepted in mathematics defines this function
as follows:
x mod y == x - floor(x/y),
and equal to x if y == 0
The difference between the two shows up with negative
values for x.
</pre>
Or, put another way,
as long as -x != y, then, -x % y = -x. For example,
assume x=4 and y=5, then, -4 % 5 will return a -4.
Here are a few other examples. Again this is NOT
the Kuth method for the mod function.
<p></p>
<pre>
-1 % 5 = -1
-2 % 5 = -2
-3 % 5 = -3
</pre>
<p>
So what we are really doing is the following:
</p>
<pre>
1000 + -1*(1000-age) = age
</pre>
<p>
<h2>C and C++ API</h2>
<p></p>
<p>
<h3>Simple C program </h3>
<p></p>
<p>
The following is a simple C program,
<a href=http://souptonuts.sourceforge.net/code/simplesqlite3.c.html>simplesqlite3.c</a> that will open a
database and execute a sql string
</p>
<pre>
#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>
static int callback(void *NotUsed, int argc, char **argv, char **azColName){
NotUsed=0;
int i;
for(i=0; i<argc; i++){
printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");
}
printf("\n");
return 0;
}
int main(int argc, char **argv){
sqlite3 *db;
char *zErrMsg = 0;
int rc;
if( argc!=3 ){
fprintf(stderr, "Usage: %s DATABASE SQL-STATEMENT\n", argv[0]);
exit(1);
}
rc = sqlite3_open(argv[1], &db);
if( rc ){
fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
sqlite3_close(db);
exit(1);
}
rc = sqlite3_exec(db, argv[2], callback, 0, &zErrMsg);
if( rc!=SQLITE_OK ){
fprintf(stderr, "SQL error: %s\n", zErrMsg);
}
sqlite3_close(db);
return 0; ⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -