SQLite: Tutorial - Revision history

Onnowpurbo at 23:04, 28 January 2021

2021-01-28T23:04:37Z

Onnowpurbo at 09:55, 28 March 2014

2014-03-28T09:55:34Z

Show changes

Onnowpurbo at 09:51, 28 March 2014

2014-03-28T09:51:32Z

Onnowpurbo at 07:36, 28 March 2014

2014-03-28T07:36:50Z

Onnowpurbo: /* Referensi */

2013-12-29T13:36:36Z

Referensi

Onnowpurbo at 22:11, 12 February 2012

2012-02-12T22:11:09Z

Onnowpurbo: New page: Sumber: http://souptonuts.sourceforge.net/readme_sqlite_tutorial.html SQLite Tutorial Copyright (c) 2004 by Mike Chirico mchirico@users.sourceforge.net This material may be distributed ...

2012-02-12T12:24:51Z

Show changes

@@ Line 44: / Line 44: @@
 You do not have to enter the sqlite3 interactive environment. Instead, you could perform all commands at the shell prompt, which is ideal when running bash scripts and commands in an ssh string. Here is an example of how you would create a simple table from the command prompt:
-     $ sqlite3 test.db  "create table t1 (t1key INTEGER
+ $ sqlite3 test.db  "create table t1 (t1key INTEGER PRIMARY KEY,data TEXT,num double,timeEnter DATE);"
 After table t1 has been created, data can be inserted as follows:
-     $ sqlite3 test.db  "insert into t1 (data,num) values ('This is sample data',3);"
+ $ sqlite3 test.db  "insert into t1 (data,num) values ('This is sample data',3);"
-     $ sqlite3 test.db  "insert into t1 (data,num) values ('More sample data',6);"
+ $ sqlite3 test.db  "insert into t1 (data,num) values ('More sample data',6);"
-     $ sqlite3 test.db  "insert into t1 (data,num) values ('And a little more',9);"
+ $ sqlite3 test.db  "insert into t1 (data,num) values ('And a little more',9);"
 As expected, doing a select returns the data in the table. Note that the primary key "t1key" auto increments; however, there are no default values for timeEnter. To populate the timeEnter field with the time, an update trigger is needed. Note that you should not use the abbreviation "INT" when working with the PRIMARY KEY. You must use "INTEGER" for the primary key to update.
-     $ sqlite3 test.db  "select * from t1 limit 2";
+ $ sqlite3 test.db  "select * from t1 limit 2";
-|This is sample data|3|
-|More sample data|6|
+|This is sample data|3|
 In the statement above, the limit clause is used, and only two rows are displayed. For a quick reference of SQL syntax statements available with SQLite, see the syntax page. There is an offset option for the limit clause. For instance, the third row is equal to the following: "limit 1 offset 2".
-     $ sqlite3 test.db "select * from t1 order by t1key limit 1 offset 2";
+ $ sqlite3 test.db "select * from t1 order by t1key limit 1 offset 2";
 |And a little more|9|
 The ".table" command shows the table names. For a more comprehensive list of tables, triggers, and indexes created in the database, query the master table "sqlite_master", as shown below.
-     $ sqlite3 test.db ".table"
+ $ sqlite3 test.db ".table"
-     t1
+ t1
-     $ sqlite3 test.db "select * from sqlite_master"
+ $ sqlite3 test.db "select * from sqlite_master"
-     table|t1|t1|2|CREATE TABLE t1 (t1key INTEGER
+ table|t1|t1|2|CREATE TABLE t1 (t1key INTEGER PRIMARY KEY,data TEXT,num double,timeEnter DATE)
 All SQL information and data inserted into a database can be extracted with the ".dump" command. Also, you might want to look for the "~/.sqlite_history" file.
-     $ sqlite3 test.db ".dump"
+ $ sqlite3 test.db ".dump"
-     BEGIN TRANSACTION;
-     CREATE TABLE t1 (t1key INTEGER
+ PRAGMA foreign_keys=OFF;
-                  PRIMARY KEY,data TEXT,num double,timeEnter DATE);
+ BEGIN TRANSACTION;
-     INSERT INTO "t1" VALUES(1, 'This is sample data', 3, NULL);
+ CREATE TABLE t1 (t1key INTEGER PRIMARY KEY,data TEXT,num double,timeEnter DATE);
-     INSERT INTO "t1" VALUES(2, 'More sample data', 6, NULL);
+ INSERT INTO t1 VALUES(1,'This is sample data',3.0,NULL);
-     INSERT INTO "t1" VALUES(3, 'And a little more', 9, NULL);
+ INSERT INTO t1 VALUES(2,'More sample data',6.0,NULL);
-     COMMIT;
+ INSERT INTO t1 VALUES(3,'And a little more',9.0,NULL);
 The contents of the ".dump" can be filtered and piped to another database. Below, table t1 is changed to t2 with the sed command, and it is piped into the test2.db database.

@@ Line 17: / Line 17: @@
 bagaimana fungsi sign mendefinisikan nilai absolut dan kondisi IF.
-Although the sign function currently does not exist in sqlite3, it is very easy to create in the "/src/func.c" file so that this function will be permanently available to all sqlite applications. Normally, user functions are created in C, Perl, or C++, which is also documented in this article. sqlite3 has the ability to store "blob", binary data. The sample program in the download, "eatblob.c", reads a binary file of any size into memory and stores the data in a user-specified field.
+Semua contoh dapat di temukan di sqlite_examples.tar.gz, dan sangat di sarankan untuk men-download contoh ini sambil kita membaca tulisan ini.
 This tutorial was made with sqlite3 version 3.0.8.

@@ Line 2: / Line 2: @@
-SQLite Tutorial
+ Copyright (c) 2004 by Mike Chirico mchirico@users.sourceforge.net
-Copyright (c) 2004 by Mike Chirico mchirico@users.sourceforge.net
+ Updated: Thu Jan 11 17:35:19 EST 2007
-This article explores the power and simplicity of sqlite3, starting with common commands and triggers. It then covers the attach statement with the union operation, introduced in a way that allows multiple tables, in separate databases, to be combined as one virtual table, without the overhead of copying or moving data. Next, I demonstrate the simple sign function and the amazingly powerful trick of using this function in SQL select statements to solve complex queries with a single pass through the data, after making a brief mathematical case for how the sign function defines the absolute value and IF conditions.
+Tulisan ini mengeksplorasi kekuatan dan kesederhanaan sqlite3, mulai dengan commands biasa dan triggers.
 Although the sign function currently does not exist in sqlite3, it is very easy to create in the "/src/func.c" file so that this function will be permanently available to all sqlite applications. Normally, user functions are created in C, Perl, or C++, which is also documented in this article. sqlite3 has the ability to store "blob", binary data. The sample program in the download, "eatblob.c", reads a binary file of any size into memory and stores the data in a user-specified field.

← Older revision		Revision as of 13:36, 29 December 2013
Line 1,627:		Line 1,627:

	* http://souptonuts.sourceforge.net/readme_sqlite_tutorial.html		* http://souptonuts.sourceforge.net/readme_sqlite_tutorial.html
		+	* http://www.tutorialspoint.com/sqlite/
		+	* http://www.tutorialspoint.com/sqlite/sqlite_quick_guide.htm
		+	* http://www.tutorialspoint.com/sqlite/sqlite_php.htm

	==Pranala Menarik==		==Pranala Menarik==

@@ Line 17: / Line 17: @@
 This tutorial was made with sqlite3 version 3.0.8.
 Getting Started
-Common Commands
+==Getting Started==
 To create a database file, run the command "sqlite3", followed by the database name. For example, to create the database "test.db", run the sqlite3 command as follows:
@@ Line 77: / Line 79: @@
        $ sqlite3 test.db ".dump"|sed -e s/t1/t2/|sqlite3 test2.db
-Triggers
+==Triggers==
 An insert trigger is created below in the file "trigger1". The Coordinated Universal Time (UTC) will be entered into the field "timeEnter", and this trigger will fire after a row has been inserted into the table t1.
@@ Line 257: / Line 259: @@
-Other Date and Time Commands
+==Other Date and Time Commands==
 If you look in the sqlite3 source file "./src/date.c", you will see that datetime takes other options. For example, to get the localtime, plus 3.5 seconds, plus 10 minutes, you would execute the following command:
@@ Line 451: / Line 453: @@
 As a side note, you may wonder why you should create the sign function. Instead, why not create an IF or IIF function? The main reason is that the IF statement is not standard on all databases, and, on some databases where it is standard (MySQL), it was created incorrectly. Yes, if you are a MySQL user, take a look at the following LONGWINDED TIP 1 for an example of MySQL's incorrect IF statement and how the sign function solves this problem.
 The Power of the Sign Function -- A Mathematical Explanation
 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 function in which 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, but here, the mathematical properties are explained.
@@ Line 625: / Line 628: @@
-Modifying the Source: Creating a Permanent Sign Function
+==Modifying the Source: Creating a Permanent Sign Function==
 Sqlite functions are defined in "./src/func.c". In this file, the name of this function will be "signFunc". The user will call this function in sqlite as sign(n). It will hold only a single variable.
@@ Line 707: / Line 710: @@
-Using the New Sign Function
+==Using the New Sign Function==
 Now, back to the problem of creating a pivot table for displaying exam scores in a spreadsheet-like format. First, more data is is needed. By the way, if have not added any data, the following script, enterExamdata, will create the necessary tables and insert the data.
@@ Line 815: / Line 818: @@
 Try finding the deltas, or the differences between each exam score. For hints on this, see the end of this article in the LONGWINDED TIPS section.
 Pivot Table "Spreadsheet Format" to Normalized Data
 Consider the reverse: Suppose you had a pivot table, or the data in a 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:
@@ Line 880: / Line 884: @@
 For more examples, see this article.
 Max Min Problems
 Assume you have the following table of names, ages, and salaries. Find the age, name, and salary of the youngest person making the overall highest salary, or first find the highest salary, then, from this group, select the youngest person.
@@ Line 959: / Line 964: @@
 C and C++ API
 Simple C Program
 The following is a simple C program, simplesqlite3.c, which will open a database and execute a SQL string.
@@ Line 1,034: / Line 1,040: @@
 There are really only three important statements, sqlite3_open(), which takes the name of the database and a database pointer, sqlite3_exec(), which executes the SQL commands in argv[2] and lists the callback function used to display the results, and sqlite3_close(), which closes the database connection.
 A C++ Program -- Building a Class to Do the Work
 It is possible to build a class, SQLITE3 (defined below), which reads the returned data into a vector. Note that instead of using the sqlite3_exec() function, sqlite3_get_table() is used instead. It copies the result of the SQL statement into the variable array of string result. Note this variable must be freed with sqlite3_free_table() after it has been used to copy the returned SQL headings and data into the vectors vcol_head and vdata. Note that the first row is the heading.
@@ Line 1,124: / Line 1,131: @@
+     }
-Defining SQLite User Functions
+==Defining SQLite User Functions==
 There are two types of functions, aggregate functions and simple functions. Simple functions like sign(), which was created above, can be used in any expression. Aggregate functions like "avg()" can only be used in the select statement. Some functions like "min" and "max" can be defined as both. min() with 1 argument is an aggregate function, whereas min() with an unlimited number of arguments is a simple function.
@@ Line 1,160: / Line 1,167: @@
-Creating a User-defined Sign Function: msign
+==Creating a User-defined Sign Function: msign==
 Below is an example of the sign function. It is called msignFunc so as not to interfere with the permanent sign function that was created earlier.
@@ Line 1,391: / Line 1,398: @@
 , which takes the index in the list. The first index starts at zero.
 Reading Images (Blob data)
 First, a demonstration of how the program eatblob.c works. This program is a C API which inserts binary (blob) data into a table.
@@ Line 1,487: / Line 1,495: @@
 Note that in the above program, buf is a pointer to a pointer, *buf is a pointer, and **buf is the first byte of data.
 Perl and sqlite3
 To use Perl with sqlite3, DBI and DBD::SQLite must be installed. To install the packages from CPAN, use the following commands.
@@ Line 1,559: / Line 1,568: @@
        -d delete last entry
-Dirty Tricks
+==Dirty Tricks==
 This trick works on sqlite version 3.2.7 and all historical versions. However, it is not guaranteed to work on all future versions. Still, this is an interesting trick.