[PostgreSQL]データの挿入・参照

テストデータ

テーブル名: students

id	name
1	Albert
3	Emily
14	John

テーブル名: dates

id	date
1	2010-02-25
2	2010-07-20
3	2012-12-15

テーブル名: test_scores

id	student_id	date_id	math	physics	chemistry
1	1	1	69	42	74
2	3	1	80	93	85
3	14	1	35	42	38
4	1	2	72	53	68
5	3	2	75	85	87
6	14	2	46	54	46
7	1	3	63	54	65
8	3	3	85	88	92
9	14	3	43	53	41

テーブル作成

testdb=# CREATE TABLE test_scores (id SERIAL PRIMARY KEY, student_id INTEGER NOT NULL, date_id INTEGER NOT NULL, math INTEGER, physics INTEGER, chemistry INTEGER);
testdb=# CREATE TABLE students (id SERIAL PRIMARY KEY, name TEXT);
testdb=# CREATE TABLE dates (id SERIAL PRIMARY KEY, date DATE);

データ挿入

studentsテーブルに登録。

testdb=# INSERT INTO students (id, name) VALUES (1, 'Albert');
INSERT 0 1
testdb=# INSERT INTO students (id, name) VALUES (3, 'Emily');
INSERT 0 1
testdb=# INSERT INTO students (id, name) VALUES (14, 'John');

datesテーブルに登録。

testdb=# INSERT INTO dates (date) VALUES (date('2010-02-25'));
INSERT 0 1
testdb=# INSERT INTO dates (date) VALUES (date('2010-07-20'));
INSERT 0 1
testdb=# INSERT INTO dates (date) VALUES (date('2012-12-15'));
INSERT 0 1

test_scoreテーブルに登録。

testdb=# INSERT INTO test_scores (student_id, date_id, math, physics, chemistry) VALUES (1, 1, 69, 42, 74);
INSERT 0 1
testdb=# INSERT INTO test_scores (student_id, date_id, math, physics, chemistry) VALUES (3, 1, 80, 93, 85);
INSERT 0 1
testdb=# INSERT INTO test_scores (student_id, date_id, math, physics, chemistry) VALUES (14, 1, 35, 42, 38);
INSERT 0 1
testdb=# INSERT INTO test_scores (student_id, date_id, math, physics, chemistry) VALUES (1, 2, 72, 53, 68);
INSERT 0 1
testdb=# INSERT INTO test_scores (student_id, date_id, math, physics, chemistry) VALUES (3, 2, 75, 85, 87);
INSERT 0 1
testdb=# INSERT INTO test_scores (student_id, date_id, math, physics, chemistry) VALUES (14, 2, 46, 54, 46);
INSERT 0 1
testdb=# INSERT INTO test_scores (student_id, date_id, math, physics, chemistry) VALUES (1, 3, 63, 54, 65);
INSERT 0 1
testdb=# INSERT INTO test_scores (student_id, date_id, math, physics, chemistry) VALUES (3, 3, 85, 88, 92);
INSERT 0 1
testdb=# INSERT INTO test_scores (student_id, date_id, math, physics, chemistry) VALUES (14, 3, 43, 53, 41);

参照

結合したテーブルを表示する。

testdb=# SELECT * FROM test_scores JOIN students ON test_scores.student_id = students.id JOIN dates ON test_scores.date_id = dates.id;
 id | student_id | date_id | math | physics | chemistry | id | name | id | date
----+------------+---------+------+---------+-----------+----+--------+----+---- --------
  1 | 1 | 1 | 69 | 42 | 74 | 1 | Albert | 1 | 2010-02-25
  2 | 3 | 1 | 80 | 93 | 85 | 3 | Emily | 1 | 2010-02-25
  3 | 14 | 1 | 35 | 42 | 38 | 14 | John | 1 | 2010-02-25
  4 | 1 | 2 | 72 | 53 | 68 | 1 | Albert | 2 | 2010-07-20
  5 | 3 | 2 | 75 | 85 | 87 | 3 | Emily | 2 | 2010-07-20
  6 | 14 | 2 | 46 | 54 | 46 | 14 | John | 2 | 2010-07-20
  7 | 1 | 3 | 63 | 54 | 65 | 1 | Albert | 3 | 2012-12-15
  8 | 3 | 3 | 85 | 88 | 92 | 3 | Emily | 3 | 2012-12-15
  9 | 14 | 3 | 43 | 53 | 41 | 14 | John | 3 | 2012-12-15
(9 行)

testdb=# SELECT test_scores.id, students.name, dates.date, test_scores.math, test_scores.physics, test_scores.chemistry FROM test_scores JOIN students ON test_scores.student_id = students.id JOIN dates ON test_scores.date_id = dates.id;
 id | name | date | math | physics | chemistry
----+--------+------------+------+---------+-----------
  1 | Albert | 2010-02-25 | 69 | 42 | 74
  2 | Emily | 2010-02-25 | 80 | 93 | 85
  3 | John | 2010-02-25 | 35 | 42 | 38
  4 | Albert | 2010-07-20 | 72 | 53 | 68
  5 | Emily | 2010-07-20 | 75 | 85 | 87
  6 | John | 2010-07-20 | 46 | 54 | 46
  7 | Albert | 2012-12-15 | 63 | 54 | 65
  8 | Emily | 2012-12-15 | 85 | 88 | 92
  9 | John | 2012-12-15 | 43 | 53 | 41
(9 行)

[PostgreSQL]基本操作

Database 作成

createuserコマンドで作成したユーザーで実行

$ createdb testdb (作成するデータベース名)
CREATE DATABASE

Databaseを使う

$ psql testdb
testdb=>

Database一覧表示

postgresユーザーで実行

$ psql
postgres=# \l
  List of databases
  Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
  | | | | | postgres=CTc/postgres
 template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
  | | | | | postgres=CTc/postgres
(3 rows)

Database削除

postgres=# drop database db_name;
DROP DATABASE

テーブルの作成

testdb=> CREATE TABLE tbl (id SERIAL PRIMARY KEY, date DATE NOT NULL, name TEXT NOT NULL, math INTEGER, physics INTEGER, chemistry INTEGER);

レコードの登録

testdb=> INSERT INTO tbl (date, name, math, physics, chemistry) VALUES (NULL, date('2010-02-25'), 'Albert', 69, 42, 74);
testdb=> INSERT INTO tbl (date, name, math, physics, chemistry) VALUES (NULL, date('2010-02-25'), 'Emily', 80, 93, 85);
testdb=> INSERT INTO tbl (date, name, math, physics, chemistry) VALUES (NULL, date('2010-02-25'), 'John', 35, 42, 38);

レコード一覧を表示

testdb=> SELECT * FROM tbl;
 id | date | name | math | physics | chemistry
----+------------+--------+------+---------+-----------
  1 | 2010-02-25 | Albert | 69 | 42 | 74
  2 | 2010-02-25 | Emily | 80 | 93 | 85
  3 | 2010-02-25 | John | 35 | 42 | 38
(3 rows)

テーブル定義の確認

testdb=> \d tbl
  Table "public.tbl"
  Column | Type | Modifiers
-----------+---------+--------------------------------------------------
 id | integer | not null default nextval('tbl_id_seq'::regclass)
 date | date | not null
 name | text | not null
 math | integer |
 physics | integer |
 chemistry | integer |
Indexes:
  "tbl_pkey" PRIMARY KEY, btree (id)

psqlコマンド終了

testdb=> \q

postgresqlを再起動

# service postgresql restart

[CentOS][PostgreSQL]インストール

インストール

# yum install postgresql
# yum install postgresql-server

サービスの起動

# service postgresql start
postgresql サービスを開始中:            [  OK  ]

データベースクラスタの作成

# su - postgres
$ echo $PGDATA
/var/lib/pgsql/data
$ initdb --encoding=UTF8 --no-locale

ユーザー作成

1. postgresユーザーに移行

   $ su -  postgres
   

2. ユーザー作成

$ createuser
Enter name of role to add: xxxx
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) y
Shall the new role be allowed to create more new roles? (y/n) n
CREATE ROLE

一般ユーザーの作成

1. postgresユーザーに移行

   $ su -  postgres
   

2. ユーザー作成

$ createuser username

3. ユーザーが作成されていることを確認する

   $ psql
   postgres=# SELECT usename,usesuper from pg_user;
     usename | usesuper
    ----------+----------
     postgres | t
     xxxx | f
   (2 rows)
   
   postgres=# \q
   

phpPgAdminのインストール

1. phpPgAdminのインストール

   $ sudo yum install phpPgAdmin
   

2. 設定
   1. postgresユーザーでログインできるように、/etc/phpPgAdmin/config.inc.phpを編集する。

      # vi /etc/phpPgAdmin/config.inc.php
      $conf['extra_login_security'] = false; ← trueをfalseに変更する
      

   2. PostgreSQL, httpdを再起動する。

      # service postgresql restart
      # service httpd restart
      

3. 動作確認
   http://localhost/phpPgAdmin/にブラウザでアクセスする。

[Python][gnuplot]SQL データからグラフを作る

次のような SQL データからグラフを作る
2010 年 9 月平均気温

area	temperature
Tokyo	25.1
Abashiri	17.8
Naha	28.0

# -*- coding:utf-8 -*-
from numpy import *
import Gnuplot, Gnuplot.funcutils
import os
import time
import MySQLdb

########################################################################
# @brief グラフを描く
########################################################################
def drawGraph(filename, pngfile, xmin, xmax, ymin, ymax):
 g = Gnuplot.Gnuplot(debug=1) # debug=1 を引数に付けるとコンソールに gnuplot に渡したコマンドが表示される

 # Gnuplot に指示を与える
 g('set xrange [%d:%d]' % (xmin, xmax))
 g('set yrange [%d:%d]' % (ymin, ymax))
 g('set boxwidth 0.5 relative')
 g('set style fill solid 0.2')
 g('set grid')
 g('unset key') # 凡例なし

 # データ読み込み
 str = "'%s' using 2:xticlabels(1) with boxes" % (filename)
 g.plot(str)

 # こちらでも png 出力可能, 一瞬 window が表示される
 g.hardcopy(pngfile, terminal = 'png')

 # すぐに終了してしまうとデータ出力できないので 0.1 sec sleep
 time.sleep(0.1)

########################################################################
# @brief Main function
########################################################################
# temporary ディレクトリを current directory にする
os.environ['TEMP']="."

# DB に接続
db = MySQLdb.connect(user="username", passwd="password", db="test", charset="utf8")

# SELECT
db.query("SELECT `area`, `temperature` FROM `temperature`;")
result = db.store_result()

# データ格納
filename = 'data03.dat'
pngfile = 'date03.png'
f = open(filename, 'w')
num = result.num_rows()
for row in result.fetch_row(num):
str = "%s\t%.2f\n" % (row[0], row[1])

# print str
f.write(str)
f.close()

# グラフ作成
drawGraph(filename, pngfile, -1, 3, 0, 30)

[MySQL]自動バックアップ

データベースの自動バックアップ用バッチファイル (Windows)
以下のバッチファイルを起動時に走らせることでデータベースの自動バックアップができるようになる。

set DBNAME=cake_test
set BACKDATE=%date:~0,4%%date:~5,2%%date:~8,2%
if "%time:~0,1%"==" " (set BACKTIME=0%time:~1,1%%time:~3,2%%time:~6,2%) else (set BACKTIME=%time:~0,2%%time:~3,2%%time:~6,2%)
set BACKUP_FILENAME=%DBNAME%_%BACKDATE%_%BACKTIME%.sql
c:\usr\xampp\mysql\bin\mysqldump.exe -h localhost -u your_account --pass="your_password" %DBNAME% > sql_backup\%BACKUP_FILENAME%

バックアップファイル名は 2009/03/26 8:27:45 に作成すると cake_test_20090326_082745.sql となる。
%date% により 2009/03/26 という文字列を得ることができる。%date:~0,4% で %date% の 0 文字目から 4 文字だけ切り出したものを得られる。同様にして以下の部分で 20090326 という文字列が BACKDATE に代入される。

set BACKDATE=%date:~0,4%%date:~5,2%%date:~8,2%

%time% により 8:27:45.30 という文字列を得ることができる 時間が 1 桁台の時は 0 文字目が空白になっているので if 分により 0 埋めを行うか判断している。以下の部分で 082745 という文字列が BACKTIME に代入される。

if "%time:~0,1%"==" " (set BACKTIME=0%time:~1,1%%time:~3,2%%time:~6,2%) else (set BACKTIME=%time:~0,2%%time:~3,2%%time:~6,2%)

[MySQL]使い方

SQL参考ページ
SQL Tutorial

SQLサーバーに接続する

$ mysql -uユーザー名 -p データベース名
Enter password:

カラムの情報を表示する

mysql> DESCRIBE テーブル名;

SQLサーバーから切断

mysql> quit;

条件に該当するものを検索

SELECT * FROM table WHERE column_name operator value;

例

SELECT no,name FROM test WHERE name = 'dog';

検索結果を昇順・降順に表示する

SELECT * FROM table WHERE column_name operator value ORDER BY column_name ACS|DESC;

例（昇順: 若い番号が先）

SELECT no,name FROM test WHERE name = 'dog' ORDER BY no ACS;

例（降順: 若い番号が後）

SELECT no,name FROM test WHERE name = 'dog' ORDER BY no DESC;

あいまい検索

SELECT * FROM table WHERE column_name LIKE pattern;

例

SELECT no,name FROM test WHERE name LIKE "%john%";

条件に該当しないものを検索

SELECT * FROM table WHERE NOT column_name = 'word';

例

SELECT no,name FROM test WHERE NOT name = 'dog';

条件に該当するレコードの件数を取得する

SELECT COUNT (column_name) FROM table WHERE column_name operator value;

例

SELECT COUNT(no) FROM test WHERE name = 'dog';

日毎の件数を取得する

SELECT date, COUNT(date) as cnt FROM table WHERE column_name operator value GROUP BY date;

例

SELECT date, COUNT(date) as cnt FROM test WHERE name = 'dog' GROUP BY date;

[MySQL]文字化け対策

取得したデータが文字化けする場合は /usr/xampp/mysql/bin/my.cnf の[mysqld]セクションに以下を追加する

default-character-set=utf8
skip-character-set-client-handshake

1行目: デフォルトの文字コードを指定
2行目: サーバー・クライアントの文字コードが違っていても内部変換することを抑制する

[MySQL]phpMyAdmin インストール (Fedora13)

1. インストール

   # yum install phpMyAdmin
   

2. /usr/share/phpMyAdmin にインストールされるので /var/www/html からスタティックリンクを張る。

   # cd /var/www/html
   # ln -s /usr/share/phpMyAdmin phpMyAdmin
   

3. httpd.conf を編集して index.php が読み込まれるようにする。
4. DirectoryIndex に indx.php を追加。

   DirectoryIndex index.html index.html.var index.php
   

5. アクセス
   localhost/phpMyAdmin にアクセスするとパスワードを聞かれるので MySQL にアクセスできるユーザー/パスワードを入力する。

[MySQL]インストール (Fedora13)

1. インストール

   # yum install mysql-server
   

2. 設定
   /etc/my.cnf に以下を追加する。

   [mysqld]
   default-character-set = utf8
   [client]
   default-character-set = utf8
   

3. 自動起動の設定

   # chkconfig --list mysqld
   mysqld 0:off 1:off 2:off 3:off 4:off 5:off 6:off
   # chkconfig mysqld on
   # chkconfig --list mysqld
   mysqld 0:off 1:off 2:on 3:on 4:on 5:on 6:off
   

4. 今回は手動で起動

   # /etc/init.d/mysqld start
   MySQL データベースを初期化中: Installing MySQL system tables...
   OK
   Filling help tables...
   OK
   To start mysqld at boot time you have to copy
   support-files/mysql.server to the right place for your system
   PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
   To do so, start the server, then issue the following commands:
   /usr/bin/mysqladmin -u root password 'new-password'
   /usr/bin/mysqladmin -u root -h fedora.local password 'new-password'
   Alternatively you can run:
   /usr/bin/mysql_secure_installation
   which will also give you the option of removing the test
   databases and anonymous user created by default. This is
   strongly recommended for production servers.
   See the manual for more instructions.
   You can start the MySQL daemon with:
   cd /usr ; /usr/bin/mysqld_safe &
   You can test the MySQL daemon with mysql-test-run.pl
   cd /usr/mysql-test ; perl mysql-test-run.pl
   Please report any problems with the /usr/bin/mysqlbug script!
   [ OK ]
   mysqld を起動中: [ OK ]
   

5. 初期設定

   # mysql_secure_installation
   NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL
   SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!
   In order to log into MySQL to secure it, we'll need the current
   password for the root user. If you've just installed MySQL, and
   you haven't set the root password yet, the password will be blank,
   so you should just press enter here.
   Enter current password for root (enter for none): <----- (今は空欄のはず)
   OK, successfully used password, moving on...
   Setting the root password ensures that nobody can log into the MySQL
   root user without the proper authorisation.
   Set root password? [Y/n] Y
   New password: <-------- (MySQL root パスワードを入力)
   Re-enter new password: Password updated successfully!
   Reloading privilege tables..
   ... Success!
   By default, a MySQL installation has an anonymous user, allowing anyone
   to log into MySQL without having to have a user account created for
   them. This is intended only for testing, and to make the installation
   go a bit smoother. You should remove them before moving into a
   production environment.
   Remove anonymous users? [Y/n] Y
   ... Success!
   Normally, root should only be allowed to connect from 'localhost'. This
   ensures that someone cannot guess at the root password from the network.
   Disallow root login remotely? [Y/n] Y
   ... Success!
   By default, MySQL comes with a database named 'test' that anyone can
   access. This is also intended only for testing, and should be removed
   before moving into a production environment.
   Remove test database and access to it? [Y/n] Y
   ... skipping.
   Reloading the privilege tables will ensure that all changes made so far
   will take effect immediately.
   Reload privilege tables now? [Y/n] Y
   ... Success!
   Cleaning up...
   All done! If you've completed all of the above steps, your MySQL
   installation should now be secure.
   Thanks for using MySQL!
   

[SQLite][SQL]データの表示方法を変更する

Default 表示

sqlite> SELECT * FROM tbl1;
hello|10
goodbye|20

column モード

sqlite> .mode column
sqlite> SELECT * FROM tbl1;
hello       10
goodbye     20

Header 追加

sqlite> .header on
sqlite> SELECT * FROM tbl1;
one         two
----------  ----------
hello       10
goodbye     20

[SQLite][SQL]コマンドライン操作

sqlite3.exe の引数はデータベースファイル名

> sqlite3.exe ex0.sqlite
SQLite version 3.7.5
Enter ".help" for instructions
Enter SQL statements terminated with a ";"

テーブルを作成してデータを追加、表示まで

sqlite> CREATE TABLE tbl1(one varchar(10), two smallint);
sqlite> INSERT INTO tbl1 VALUES ('hello', 10);
sqlite> INSERT INTO tbl1 VALUES ('goodbye', 20);
sqlite> SELECT * FROM tbl1;
hello|10
goodbye|20

データを絞って表示

sqlite> SELECT * FROM tbl1 WHERE two >= 15;
goodbye|20

終了

sqlite> .exit

DB ファイル内にあるテーブル一覧表示

sqlite> .tables
tbl1  tbl2

参照: Command Line Shell For SQLite

[SQLite][SQL]Date データの扱い

Date データを扱う時は date() 関数を通す。

sqlite> CREATE TABLE tokyoTemperature (id INTEGER NOT NULL PRIMARY KEY, date DATE NOT NULL, ave FLOAT NOT NULL);
sqlite> INSERT INTO tokyoTemperature VALUES (NULL, date('2011-01-01'), 6.6);
sqlite> INSERT INTO tokyoTemperature VALUES (NULL, date('2011-01-02'), 7.0);
sqlite> INSERT INTO tokyoTemperature VALUES (NULL, date('2011-01-03'), 5.9);
sqlite> INSERT INTO tokyoTemperature VALUES (NULL, date('2011-01-04'), 6.3);
sqlite> INSERT INTO tokyoTemperature VALUES (NULL, date('2011-01-05'), 7.3);
sqlite> INSERT INTO tokyoTemperature VALUES (NULL, date('2011-01-06'), 6.5);
sqlite> SELECT * from tokyoTemperature;
id          date        ave
----------  ----------  ----------
1           2011-01-01  6.6
2           2011-01-02  7.0
3           2011-01-03  5.9
4           2011-01-04  6.3
5           2011-01-05  7.3
6           2011-01-06  6.5
sqlite> SELECT * from tokyoTemperature WHERE date >= date('2011-01-05');
id          date        ave
----------  ----------  ----------
5           2011-01-05  7.3
6           2011-01-06  6.5

date() を通さずにデータを登録すると異なる値が入ってしまう。

sqlite> INSERT INTO tokyoTemperature VALUES (NULL, 2011-01-07, 4.0);
sqlite> SELECT * FROM tokyoTemperature WHERE id = 7;
id          date        ave
----------  ----------  ----------
7           2003        4.0

参考: SQLite Query Language: Date And Time Functions

[SQLite][SQL]AUTO INCREMENT

SQLite には AUTO_INCREMENT (AUTOINCREMENT) キーワードが準備されていない。
AUTO INCREMENT を使う場合は "INTEGER NOT NULL PRIMARY KEY" でフィールドを定義して、データ登録の時は NULL を指定する。

実行結果

> a.exe test.db "CREATE TABLE test_tbl(ID INTEGER PRIMARY KEY, NAME TEXT NOT NULL);"
> a.exe test.db "INSERT INTO test_tbl(ID, NAME) VALUES (NULL, '1st');"
> a.exe test.db "INSERT INTO test_tbl(ID, NAME) VALUES (NULL, '2nd');"
> a.exe test.db "INSERT INTO test_tbl(ID, NAME) VALUES (NULL, '3rd');"
> a.exe test.db "SELECT * from test_tbl;"
ID = 1
NAME = 1st

ID = 2
NAME = 2nd

ID = 3
NAME = 3rd

sqlite3.exe の場合

sqlite> CREATE TABLE tbl3 (id INTEGER NOT NULL PRIMARY KEY, name TEXT NOT NULL);
sqlite> INSERT INTO tbl3 VALUES (NULL, 'Tokyo');
sqlite> INSERT INTO tbl3 VALUES (NULL, 'Osaka');
sqlite> INSERT INTO tbl3 VALUES (NULL, 'Nagoya');
sqlite> SELECT * FROM tbl3;
1|Tokyo
2|Osaka
3|Nagoya

[SQLite][SQL]sqlite3_get_table

sqlite3_exec() に callback 関数を登録する以外にテーブルのデータ一覧を取得する方法として sqlite3_get_table() がある

char **azResult;
int row, column;
int i, j;

rc = sqlite3_get_table(db, argv[2], &azResult, &row, &column, &zErrMsg);
if (rc != SQLITE_OK) {
    fprintf(stderr, "SQL error: %s\n", zErrMsg);
    sqlite3_free(zErrMsg);
}
printf("row=%d column=%d\n", row, column);
for (i = 0; i <= row; i++) {
    for (j = 0; j < column; j++) {
        printf("[%d,%d] %s ", i, j, azResult[(i * column + j)]);
    }
    printf("\n");
    if (i == 0) {
        printf("-----------------------------\n");
    }
}
sqlite3_free_table(azResult);

実行結果

row=3 column=2
[0,0] ID [0,1] NAME
-----------------------------
[1,0] 1 [1,1] 1st
[2,0] 2 [2,1] 2nd
[3,0] 3 [3,1] 3rd

[SQLite][SQL]MinGW, MSYS 環境での sqlite のコンパイル・使用方法

1. sqlite3 の関数を使用するプログラムを準備する。
   main.c

   #include 
   #include "sqlite3.h"
   
   static int callback(void *NotUsed, int argc, char **argv, char **azColName)
   {
       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;
       int rc;
       char *zErrMsg = 0;
   
       if (argc != 3) {
           fprintf(stderr, "Usage: %s DATABASE SQL-STATEMENT\n", argv[0]);
           return -1;
       }
   
       rc = sqlite3_open(argv[1], &db);
       if (rc) {
           fprintf(stderr, "Cannot open database: %s\n", sqlite3_errmsg(db));
           sqlite3_close(db);
           return -1;
       }
   
       rc = sqlite3_exec(db, argv[2], callback, 0, &zErrMsg);
       if (rc != SQLITE_OK) {
           fprintf(stderr, "SQL error: %s\n", zErrMsg);
           sqlite3_free(zErrMsg);
       }
       sqlite3_close(db);
   
       return 0;
   }
   

2. Makafile を準備する。
   Makefile

   CC=gcc
   CFLAGS = -Wall
   LDFLAGS = -lsqlite3
   TARGET = \
        main.exe
   
   .SUFFIXES: .exe .c .o
   
   .c.o:
        $(CC) $(CFLAGS) -c -o $@ $<
   
   .o.exe:
    $(CC) -o $@ $< $(LDFLAGS)
   
   default: $(TARGET)
   
   clean:
    rm -rf *.o *.exe
   

3. コンパイルをする
4. 実行して動作確認する。第 1 引数はデータベースファイル名、第 2 引数に SQL 文を渡す。

   > a.exe test.db "CREATE TABLE test_tbl(ID INT PRIMARY KEY, NAME TEXT NOT NULL);"
   > a.exe test.db "INSERT INTO test_tbl(ID, NAME) values (0, 'hoge');"
   > a.exe test.db "SELECT * from test_tbl;"
   ID = 0
   NAME = hoge
   
   > a.exe test.db "INSERT INTO test_tbl(ID, NAME) values (1, 'aaa');"
   > a.exe test.db "SELECT * from test_tbl;"
   ID = 0
   NAME = hoge
   
   ID = 1
   NAME = aaa
   

[SQLite][SQL]インストール

Windows 用コンパイル済みバイナリ
SQLite Download Page から Windows 用コンパイル済みバイナリ (sqlite-shell-win32-x86-3070500.zip, sqlite-dll-win32-x86-3070500.zip) をダウンロードする。

MinGW

1. SQLite Home Page の DOWNLOAD ページにあるソースコード (sqlite-amalgamation-3_6_18.zip, 2009/10/13 現在) をダウンロードする。
2. ダウンロードしたファイルを展開する。
3. configure

   > sh ./configure --prefix=/c/usr/MinGW
   

4. make

   > make
   

5. ここで下記のような Warning が頻出するが、コンパイルは完了する。

   Temp/ccfFx7ME.s:184210: Warning: .stabn: description field '10d6b' too big, try a different debug format
   

6. make install

> make install

[SQL]データ集計

サンプルテーブル作成

sqlite> CREATE TABLE tbl2 (id INTEGER NOT NULL PRIMARY KEY, date DATE NOT NULL, name TEXT NOT NULL, math INTEGER, physics INTEGER, chemistry INTEGER);
sqlite> INSERT INTO tbl2 VALUES (NULL, date('2010-02-25'), 'Albert', 69, 42, 74);
sqlite> INSERT INTO tbl2 VALUES (NULL, date('2010-02-25'), 'Emily', 80, 93, 85); sqlite> INSERT INTO tbl2 VALUES (NULL, date('2010-02-25'), 'John', 35, 42, 38);
sqlite> INSERT INTO tbl2 VALUES (NULL, date('2010-07-20'), 'Albert', 72, 53, 68);
sqlite> INSERT INTO tbl2 VALUES (NULL, date('2010-07-20'), 'Emily', 75, 85, 87);
sqlite> INSERT INTO tbl2 VALUES (NULL, date('2010-07-20'), 'John', 46, 54, 46);
sqlite> INSERT INTO tbl2 VALUES (NULL, date('2010-12-15'), 'Albert', 63, 54, 65);
sqlite> INSERT INTO tbl2 VALUES (NULL, date('2010-12-15'), 'Emily', 85, 88, 92);
sqlite> INSERT INTO tbl2 VALUES (NULL, date('2010-12-15'), 'John', 43, 53, 41);
sqlite> SELECT * FROM tbl2;
id date name math physics chemistry
---------- ---------- ---------- ---------- ---------- ----------
1 2010-02-25 Albert 69 42 74
2 2010-02-25 Emily 80 93 85
3 2010-02-25 John 35 42 38
4 2010-07-20 Albert 72 53 68
5 2010-07-20 Emily 75 85 87
6 2010-07-20 John 46 54 46
7 2010-12-15 Albert 63 54 65
8 2010-12-15 Emily 85 88 92
9 2010-12-15 John 43 53 41

合計
各テストの合計点を出す

sqlite> SELECT *,math+physics+chemistry AS total FROM tbl2;
id date name math physics chemistry total
---------- ---------- ---------- ---------- ---------- ---------- ----------
1 2010-02-25 Albert 69 42 74 185
2 2010-02-25 Emily 80 93 85 258
3 2010-02-25 John 35 42 38 115
4 2010-07-20 Albert 72 53 68 193
5 2010-07-20 Emily 75 85 87 247
6 2010-07-20 John 46 54 46 146
7 2010-12-15 Albert 63 54 65 182
8 2010-12-15 Emily 85 88 92 265
9 2010-12-15 John 43 53 41 137

平均
math, physics を足して 2 で割った結果を出す

sqlite> SELECT *, (math+physics)/2.0 FROM tbl2;
id date name math physics chemistry (math+physics)/2.0
---------- ---------- ---------- ---------- ---------- ---------- ------------------
1 2010-02-25 Albert 69 42 74 55.5
2 2010-02-25 Emily 80 93 85 86.5
3 2010-02-25 John 35 42 38 38.5
4 2010-07-20 Albert 72 53 68 62.5
5 2010-07-20 Emily 75 85 87 80.0
6 2010-07-20 John 46 54 46 50.0
7 2010-12-15 Albert 63 54 65 58.5
8 2010-12-15 Emily 85 88 92 86.5
9 2010-12-15 John 43 53 41 48.0

GROUP BY
name が同じものを集めて math の平均値を出す

sqlite> SELECT name,AVG(math) FROM tbl2 GROUP BY name;
name AVG(math)
---------- ----------
Albert 68.0
Emily 80.0
John 41.3333333

ORDER BY
2010-12-15 のテスト結果のみ表示してみる

sqlite> SELECT date, name, math FROM tbl2 WHERE date = date('2010-12-15');
date name math
---------- ---------- ----------
2010-12-15 Albert 63
2010-12-15 Emily 85
2010-12-15 John 43

ORDER BY を使って 2010-12-15 のテストで math の点数順に並べる (昇順 (ASCending-order))

sqlite> SELECT date, name, math FROM tbl2 WHERE date = date('2010-12-15') ORDER BY math;
date name math
---------- ---------- ----------
2010-12-15 John 43
2010-12-15 Albert 63
2010-12-15 Emily 85

sqlite> SELECT date, name, math FROM tbl2 WHERE date = date('2010-12-15') ORDER BY math ASC;
date name math
---------- ---------- ----------
2010-12-15 John 43
2010-12-15 Albert 63
2010-12-15 Emily 85

降順に並べる (DESCending-order)

sqlite> SELECT date, name, math FROM tbl2 WHERE date = date('2010-12-15') ORDER BY math DESC;
date name math
---------- ---------- ----------
2010-12-15 Emily 85
2010-12-15 Albert 63
2010-12-15 John 43

[SQL]レコードを全削除

DELETE from テーブル名;

または

TRUNCATE [TABLE] テーブル名;

[SQL]テーブル名の変更

ALTER TABLE 変更前テーブル名 RENAME TO 変更後テーブル名;

[PHP]SQL で COUNT() を使って取得した件数を表示する

<?php
 /*
  * 条件に該当するレコードの件数を取得する
  */
function getRecordCount($connId, $query)
{
    $result = mysql_query($query, $connId);
    if ($result == false) {
        print "Error: " . $query . "<br>(" . mysql_error() . ")";
        exit;
    }

    list($num) = mysql_fetch_row($result);
    switch ($num) {
    case "":
        print "Not found<br>\n";
        $cnt = 0;
        break;
    default:
        $cnt = $num;
        break;
    }

    /* メモリの開放 */
    mysql_free_result($result);

    return $cnt;
}
?>

<?php
    $cnt = getRecordCount($connection_id, $query);
    print "結果: " . $cnt . "件<br>\n";
?>