DIAVIS-wiki

• バックアップ一覧
• ソース を表示
• hsqldbデータベース は削除されています。
  • 1 (2006-11-26 (日) 17:07:29)
  • 2 (2006-11-26 (日) 17:45:58)
  • 3 (2007-01-11 (木) 20:27:25)
  • 4 (2007-01-12 (金) 15:38:18)
  • 5 (2007-01-13 (土) 15:36:06)
  • 6 (2007-01-20 (土) 21:13:51)
  • 7 (2007-01-24 (水) 15:55:02)
  • 8 (2007-01-25 (木) 11:08:28)
  • 9 (2007-01-25 (木) 16:09:52)
  • 10 (2007-02-01 (木) 04:13:43)
  • 11 (2007-02-05 (月) 15:13:10)
  • 12 (2007-05-04 (金) 11:30:51)
  • 13 (2007-05-09 (水) 04:20:16)
  • 14 (2007-05-10 (木) 03:58:57)
  • 15 (2007-05-10 (木) 07:09:00)
  • 16 (2007-08-31 (金) 08:47:25)
  • 17 (2007-08-31 (金) 19:42:21)

---

---

---

SELECT LIMIT n m * FROM tablename;
n 開始位置 
m 長さ 

CREATE TABLE TAGS ( ID IDENTITY, KEYWORDS VARCHAR, EVENT_ID INTEGER, DATE TIMESTAMP );

INSERT INTO image (URI) values ('JanTschichold.jpg');

ResultSet rs;
rs = mSQL.executeSQL("select COUNT(*) from keyword where keyword='" + keyword + "';");
int keyword_id = -1;
try {
	rs.next();
	int nResult = rs.getInt(1);

select * from person  where database=0 and name like '%Hide%'  order by name

select * from person  where database=0 and UPPER(name) like '%HIDE%'  order by name

select * from person  where database=0 and UPPER(name) like '%Hide%'  order by name

SELECT * FROM Orders WHERE CustomerID = 'QUICK' AND OrderDate BETWEEN '1996/1/1' AND '1997/12/31' 

select * from keyword order by latest_appearance desc

select * from keyword order by num desc, latest_appearance 
select * from keyword order by  latest_appearance desc, num desc
select * from items where date BETWEEN '2007-05-01 12:00:00.0' AND '2007-05-04 15:00:00.0' order by date desc

SELECT COUNT(*) FROM IN_USE WHERE DATE BETWEEN '2007-05-01 12:00:00.0'  AND '2007-05-04 15:00:00.0' AND KEYWORD_ID = 61517;

SELECT COUNT(*) FROM IN_USE WHERE KEYWORD_ID = 61530;

データベーステーブルに対する何らかの操作（追加、更新、削除）をきっかけに起動する処理を登録できる。HSQLDBでは以下のようにJavaのクラスを直接指定する。
-- レコードの削除後に起動するトリガを登録

登録するクラスはorg.hsqldb.Triggerインタフェースを実装していなければならない。

public class TriggerSample implements org.hsqldb.Trigger {
 public void fire(int type, String trigName, String tabName,
                  Object oldRow, Object newRow) {
   // 起動したい処理を記述
 }

注意！

select * from keyword order by latest_appearance desc

select * from keyword order by num desc, latest_appearance 
select * from keyword order by  latest_appearance desc, num desc
select * from items where date BETWEEN '2007-05-01 12:00:00.0' AND '2007-05-04 15:00:00.0' order by date desc

SELECT COUNT(*) FROM IN_USE WHERE DATE BETWEEN '2007-05-01 12:00:00.0' AND '2007-05-04 15:00:00.0'  AND KEYWORD_ID = 61517;
SELECT * FROM KEYWORD WHERE LATEST_APPEARANCE BETWEEN '2007-04-30 12:38:45.734' AND '2007-05-04 12:38:45.734' ORDER BY LATEST_APPEARANCE DESC
SELECT COUNT(*) FROM IN_USE WHERE KEYWORD_ID = 61530;

SELECT KEYWORD_ID,count(*) AS NUM FROM IN_USE where date BETWEEN '2007-05-01 00:00:00.0' AND '2007-05-09 00:00:00.0' GROUP BY KEYWORD_ID ORDER BY NUM DESC

SELECT * FROM (SELECT KEYWORD_ID,count(*) AS NUM FROM IN_USE WHERE  DATE BETWEEN '2007-05-01 00:00:00.0' AND '2007-05-09 00:00:00.0'  GROUP BY KEYWORD_ID ORDER BY NUM DESC) WHERE NUM >= 2 ORDER BY NUM DESC

SELECT KEYWORD_ID, KEYWORD FROM IN_USE inner join KEYWORD on IN_USE.KEYWORD_ID = KEYWORD.ID

SELECT KEYWORD_ID,count(*),KEYWORD AS NUM FROM (SELECT KEYWORD_ID, KEYWORD FROM IN_USE inner join KEYWORD on IN_USE.KEYWORD_ID = KEYWORD.ID WHERE  DATE BETWEEN '2007-05-01 00:00:00.0' AND '2007-05-09 00:00:00.0') GROUP BY KEYWORD_ID

SELECT KEYWORD_ID,KEYWORD,count(*) AS NUM FROM (SELECT KEYWORD_ID, KEYWORD FROM IN_USE inner join KEYWORD on IN_USE.KEYWORD_ID = KEYWORD.ID WHERE  DATE BETWEEN '2007-05-01 00:00:00.0' AND '2007-05-09 00:00:00.0') GROUP BY KEYWORD_ID,KEYWORD

SELECT * FROM (SELECT KEYWORD_ID,KEYWORD,count(*) AS NUM FROM (SELECT KEYWORD_ID, KEYWORD FROM IN_USE inner join KEYWORD on IN_USE.KEYWORD_ID = KEYWORD.ID WHERE  DATE BETWEEN '2007-05-01 00:00:00.0' AND '2007-05-09 00:00:00.0') GROUP BY KEYWORD_ID,KEYWORD) WHERE NUM >= 2 ORDER BY NUM DESC