CONTENT

CHANGES

Szukaj

#top Zapytania SQL¶

[<<]|[<]|[^]|[v]|[>]|[>>]|[Zwiń]|[#top]|[X]

#top users¶

Since SQLite reads and writes an ordinary disk file, the only access permissions that can be applied
are the normal file access permissions of the underlying operating system.
The GRANT and REVOKE commands commonly found on client/server RDBMSes are not implemented
because they would be meaningless for an embedded database engine.

#top USER CREATE¶

#top USER ALTER¶

#top USER SET PASSWORD¶

#top USER DROP¶

#top permissions¶

#top GRANT¶

#top REVOKE¶

#top SHOW GRANTS¶

#top databases¶

Single-file Cross-platform Database
A database in SQLite is a single disk file. Furthermore, the file format is cross-platform. A database that is created on one machine can be copied and used on a different machine with a different architecture. SQLite databases are portable across X86(32-bit) and X86-64(64-bit) machines and between Endianness(big-endian) and Endianness(little-endian) architectures.

Próba otworzenia bazy danych z nieistniejącego pliku powoduje utworzenie bazy danych (pustego pliku).
Należy wowołać polecenie sqlite3 z argumentem będącym ścieżką do nieistiejącego pliku bazy danych /home/user/tmp/temp.sqlite
sqlite3 /home/user/tmp/temp.sqlite
a następnie instrukcją .databases (działa tylko z wiersza poleceń sqlite3) wyświetlić listę baz danych:

sqlite> .databases

W rezultacie sqlite3 wyświetli analogiczne do poniższych informacje:

seq  name             file
---  ---------------  ----------------------------------------------------------
0    main             /home/user/tmp/temp.sqlite

Następnie należy wpisać instrukcję .quit aby poinformować interpreter o zakończeniu pracy (naciśnięcie kombinacji klawiszy CTRL+D również spowoduje zakończenie działania interpretera sqlite3 jednakże baz zapisu / utworzenia nowej bazy danych):

sqlite> .quit

Na koniec można sprawdzić, czy plik bazy danych sqlite został utworzony ls -l /home/user/tmp/temp.sqlite:
W rezultacie na ekranie powinna zostać wyświetlona analogiczna informacja:

-rw-r--r-- 1 user users 0 2013-07-29 14:50 /home/user/tmp/temp.sqlite

#top DATABASE CREATE¶

#top DATABASE ALTER¶

#top DATABASE DROP¶

#top schemas¶

#top SCHEMA CREATE¶

#top SCHEMA ALTER¶

#top SCHEMA DROP¶

#top tablespaces¶

#top TABLESPACE CREATE¶

#top TABLESPACE ALTER¶

#top TABLESPACE DROP¶

#top tables¶

#top TABLE CREATE¶

comment

#top TABLE CREATE LIKE¶

-- comment

#top TABLE CREATE AS¶

-- comment

#top TABLE CREATE with PRIMARY KEY¶

CREATE TABLE temp_prikey (
idtemp int(11) NOT NULL,
name varchar(255) NOT NULL,
value varchar(255) NOT NULL,
PRIMARY KEY (idtemp)
);

Następnie dla testu należy dodać kilka wierszy do tabeli:

INSERT INTO temp_prikey(idtemp,name,value) VALUES (1,'name1','value1');
INSERT INTO temp_prikey(idtemp,name,value) VALUES (2,'name2','value2');
INSERT INTO temp_prikey(idtemp,name,value) VALUES (3,'name3','value3');
INSERT INTO temp_prikey(idtemp,name,value) VALUES (3,'name4','value4');
SQL error: column idtemp is not unique

oraz wyświetlić zawartość tabeli SELECT * FROM temp_prikey LIMIT 10;(zgodnie z poniższym rezultatem ostatni INSERT nie powiódł):

1|name1|value1
2|name2|value2
3|name3|value3

#top TABLE CREATE with auto_increment¶

The AUTOINCREMENT Keyword
If a column has the type INTEGER PRIMARY KEY AUTOINCREMENT then a slightly different ROWID selection algorithm is used. The ROWID chosen for the new row is at least one larger than the largest ROWID that has ever before existed in that same table. If the table has never before contained any data, then a ROWID of 1 is used. If the table has previously held a row with the largest possible ROWID, then new INSERTs are not allowed and any attempt to insert a new row will fail with an SQLITE_FULL error.

create table:

CREATE TABLE temp_autoinc (idtemp integer NOT NULL, name varchar(255) NOT NULL, value varchar(255) NOT NULL, PRIMARY KEY (idtemp));

insert data:

INSERT INTO temp_autoinc(name,value) VALUES('name1','value1');
INSERT INTO temp_autoinc(name,value) VALUES('name2','value2');
INSERT INTO temp_autoinc(name,value) VALUES('name3','value3');
INSERT INTO temp_autoinc(name,value) VALUES('name4','value4');
INSERT INTO temp_autoinc(name,value) VALUES('name5','value5');
INSERT INTO temp_autoinc(name,value) VALUES('name6','value6');

check result:

SELECT * FROM temp_autoinc;

result:

1|name1|value1
2|name2|value2
3|name3|value3
4|name4|value4
5|name5|value5
6|name6|value6

#top TABLE CREATE with UNIQUE KEY (singlecolumn)¶

CREATE TABLE temp_unikey (
idtemp int(11) NOT NULL,
name varchar(255) UNIQUE NOT NULL,
value varchar(255) NOT NULL
);
<pre>

<pre>
INSERT INTO temp_unikey(idtemp,name,value) VALUES (1,'name1','value1');
INSERT INTO temp_unikey(idtemp,name,value) VALUES (2,'name2','value2');
INSERT INTO temp_unikey(idtemp,name,value) VALUES (3,'name3','value3');
INSERT INTO temp_unikey(idtemp,name,value) VALUES (4,'name3','value4');
SQL error: column name is not unique

SELECT * FROM temp_unikey LIMIT 10;

1|name1|value1
2|name2|value2
3|name3|value3

#top TABLE CREATE with UNIQUE KEY (multicolumn)¶

CREATE TABLE temp_unikey (
idtemp int(11) NOT NULL,
name varchar(255) NOT NULL,
value varchar(255) NOT NULL
);

CREATE UNIQUE INDEX temp_unikey_namevalue ON temp_unikey(name,value);

INSERT INTO temp_unikey(idtemp,name,value) VALUES (1,'name1','value1');
INSERT INTO temp_unikey(idtemp,name,value) VALUES (2,'name2','value2');
INSERT INTO temp_unikey(idtemp,name,value) VALUES (3,'name3','value3');
INSERT INTO temp_unikey(idtemp,name,value) VALUES (4,'name3','value3');
SQL error: columns name, value are not unique

SELECT * FROM temp_unikey LIMIT 10;

1|name1|value1
2|name2|value2
3|name3|value3

#top TABLE CREATE with FULLTEXT KEY¶

-- comment

#top TABLE CREATE with FOREIGN KEY¶

-- comment

#top TABLE CREATE with INDEX¶

-- comment

#top TABLE CREATE with partitioned¶

-- comment

#top TABLE CREATE with COLLATE without UNIQUE¶

-- comment

#top TABLE CREATE with COLLATE with UNIQUE¶

-- comment

#top TABLE ALTER¶

comment

#top TABLE ALTER with PRIMARY KEY¶

CREATE TABLE temp_prikey (
idtemp int(11) NOT NULL,
name varchar(255) NOT NULL,
value varchar(255) NOT NULL
);

ALTER TABLE temp_prikey ADD PRIMARY KEY (idtemp);
SQL error: near "PRIMARY": syntax error

Dokumentacja SQLite:
http://sqlite.org/faq.html#q11
(11) How do I add or delete columns from an existing table in SQLite.

SQLite has limited ALTER TABLE support that you can use
to add a column to the end of a table or to change the name of a table.
If you want to make more complex changes in the structure of a table,
you will have to recreate the table. You can save existing data to a temporary table,
drop the old table, create the new table, then copy the data back in from the temporary table.

For example, suppose you have a table named "t1" with columns names "a", "b", and "c"
and that you want to delete column "c" from this table. The following steps illustrate
how this could be done:

BEGIN TRANSACTION;
CREATE TEMPORARY TABLE t1_backup(a,b);
INSERT INTO t1_backup SELECT a,b FROM t1;
DROP TABLE t1;
CREATE TABLE t1(a,b);
INSERT INTO t1 SELECT a,b FROM t1_backup;
DROP TABLE t1_backup;
COMMIT;

SQLite supports a limited subset of ALTER TABLE. The ALTER TABLE command in SQLite
allows the user to rename a table or to add a new column to an existing table.
It is not possible to rename a column, remove a column,
or add or remove constraints from a table.

CREATE TABLE temp_prikey (
idtemp int(11) NOT NULL,
name varchar(255) NOT NULL,
value varchar(255) NOT NULL
);

INSERT INTO temp_prikey(idtemp,name,value) VALUES (1,'name1','value1');
INSERT INTO temp_prikey(idtemp,name,value) VALUES (2,'name2','value2');
INSERT INTO temp_prikey(idtemp,name,value) VALUES (3,'name3','value3');

SELECT * FROM temp_prikey LIMIT 10;

1|name1|value1
2|name2|value2
3|name3|value3

CREATE TABLE temp_pri2key (
idtemp int(11) NOT NULL,
name varchar(255) NOT NULL,
value varchar(255) NOT NULL
);

INSERT INTO temp_pri2key SELECT * FROM temp_prikey;

DROP TABLE temp_prikey;

CREATE TABLE temp_prikey (
idtemp int(11) NOT NULL,
name varchar(255) NOT NULL,
value varchar(255) NOT NULL,
PRIMARY KEY (idtemp)
);

INSERT INTO temp_prikey SELECT * FROM temp_pri2key;

DROP TABLE temp_pri2key;

INSERT INTO temp_prikey(idtemp,name,value) VALUES (3,'name4','value4');
SQL error: column idtemp is not unique

SELECT * FROM temp_prikey LIMIT 10;

1|name1|value1
2|name2|value2
3|name3|value3

#top TABLE ALTER with auto increment¶

#top TABLE ALTER with UNIQUE KEY (singlecolumn)¶

CREATE TABLE temp_unikey (
idtemp int(11) NOT NULL,
name varchar(255) NOT NULL,
value varchar(255) NOT NULL
);

ALTER TABLE temp_unikey ADD UNIQUE KEY name;
SQL error: near "UNIQUE": syntax error

CREATE UNIQUE INDEX temp_unikey_name ON temp_unikey(name);

INSERT INTO temp_unikey(idtemp,name,value) VALUES (1,'name1','value1');
INSERT INTO temp_unikey(idtemp,name,value) VALUES (2,'name2','value2');
INSERT INTO temp_unikey(idtemp,name,value) VALUES (3,'name3','value3');
INSERT INTO temp_unikey(idtemp,name,value) VALUES (4,'name3','value4');
SQL error: column name is not unique

------------------------------------

SQLite supports a limited subset of ALTER TABLE. The ALTER TABLE command in SQLite
allows the user to rename a table or to add a new column to an existing table.
It is not possible to rename a column, remove a column,
or add or remove constraints from a table.

CREATE TABLE temp_unikey (
idtemp int(11) NOT NULL,
name varchar(255) NOT NULL,
value varchar(255) NOT NULL
);

INSERT INTO temp_unikey(idtemp,name,value) VALUES (1,'name1','value1');
INSERT INTO temp_unikey(idtemp,name,value) VALUES (2,'name2','value2');
INSERT INTO temp_unikey(idtemp,name,value) VALUES (3,'name3','value3');

SELECT * FROM temp_unikey LIMIT 10;

1|name1|value1
2|name2|value2
3|name3|value3

CREATE TABLE temp_uni2key (
idtemp int(11) NOT NULL,
name varchar(255) NOT NULL,
value varchar(255) NOT NULL
);

INSERT INTO temp_uni2key SELECT * FROM temp_unikey;

DROP TABLE temp_unikey;

CREATE TABLE temp_unikey (
idtemp int(11) NOT NULL,
name varchar(255) UNIQUE NOT NULL,
value varchar(255) NOT NULL
);

INSERT INTO temp_unikey SELECT * FROM temp_uni2key;

DROP TABLE temp_uni2key;

INSERT INTO temp_unikey(idtemp,name,value) VALUES (4,'name3','value4');
SQL error: column name is not unique

SELECT * FROM temp_unikey LIMIT 10;

1|name1|value1
2|name2|value2
3|name3|value3

#top TABLE ALTER with UNIQUE KEY (multicolumn)¶

-- comment

#top TABLE ALTER with FULLTEXT KEY¶

-- comment

#top TABLE ALTER with FOREIGN KEY¶

-- comment

#top TABLE ALTER with INDEX¶

-- comment

#top TABLE ALTER with partitioned¶

-- comment

#top TABLE ALTER with COLLATE without UNIQUE¶

-- comment

#top TABLE ALTER with COLLATE with UNIQUE¶

-- comment

#top TABLE RENAME¶

comment

Example: rename table

-- comment

#top TABLE TRUNCATE¶

Zobacz także TABLE TRUNCATE dla: PostgreSQL | MySQL | Firebird | SQLite | MsSQL | Oracle | DB2 | Informix | Sybase

SQLite nie posiada obsługi zapytania w postaci TRUNCATE TABLE. Jednakże można użyć alternatywnego zapytania DELETE do usunięcia wszystkich danych z tabeli.
W przypadku użycia zapytania DELETE w celu usunięcia wszystkich danych z tabeli zalecane jest wykonanie zapytania VACUUM w celu przebudowania bazy danych (The VACUUM command rebuilds the entire database).

DELETE FROM temp_delete_table;
VACUUM;

Alternatywną możliwością jest również usunięcie tabeli poprzez wykonanie zapytania DROP TABLE i utworzenie jej ponownie. W przypadku kasowania tabeli również zalecane jest wykonanie zapytania VACUUM w celu przebudowania bazy danych (The VACUUM command rebuilds the entire database).

DROP TABLE temp_delete_table;
VACUUM;
CREATE TABLE temp_delete_table ([...]);

#top TABLE DROP¶

comment

Example:

-- comment

#top sequences¶

#top SEQUENCE CREATE¶

#top SEQUENCE ALTER¶

#top SEQUENCE DROP¶

#top indexes¶

#top INDEX CREATE¶

comment

Example: alter table add INDEX

-- comment

#top INDEX ALTER¶

#top REINDEX¶

Dokumentacja SQLite:
http://www.sqlite.org/lang_reindex.html

Składnia:

comment

Example:

-- comment

#top INDEX DROP¶

comment

Example: alter table drop INDEX

-- comment

#top functions¶

#top Functions and Operators¶

-- comment

#top DATA Manipulations¶

#top SELECT with LIMIT and OFFSET¶

comment

Example:

CREATE TABLE temp_limit (
idtemp integer NOT NULL,
name varchar(255) NOT NULL,
value varchar(255) NOT NULL,
PRIMARY KEY (idtemp)
);

INSERT INTO temp_limit(idtemp,name,value) VALUES(1,'name-01','val-01');
INSERT INTO temp_limit(idtemp,name,value) VALUES(2,'name-02','val-02');
INSERT INTO temp_limit(idtemp,name,value) VALUES(3,'name-03','val-03');
INSERT INTO temp_limit(idtemp,name,value) VALUES(4,'name-04','val-04');
INSERT INTO temp_limit(idtemp,name,value) VALUES(5,'name-05','val-05');
INSERT INTO temp_limit(idtemp,name,value) VALUES(6,'name-06','val-06');
INSERT INTO temp_limit(idtemp,name,value) VALUES(7,'name-07','val-07');
INSERT INTO temp_limit(idtemp,name,value) VALUES(8,'name-08','val-08');
INSERT INTO temp_limit(idtemp,name,value) VALUES(9,'name-09','val-09');

Po wykonaniu powyższych zapytań mających na celu utworzenie tabeli oraz dodaniu przykładowych danych można wykonać poniższe zapytania w celu sprawdzenia działania klauzul LIMIT przy wyświetlaniu danych z tabeli:

SELECT * FROM temp_limit LIMIT 3;
SELECT * FROM temp_limit LIMIT 5;
SELECT * FROM temp_limit LIMIT 7;

Uzyskany rezultat powinien być analogiczny do poniższego:

1|name-01|val-01
2|name-02|val-02
3|name-03|val-03

1|name-01|val-01
2|name-02|val-02
3|name-03|val-03
4|name-04|val-04
5|name-05|val-05

1|name-01|val-01
2|name-02|val-02
3|name-03|val-03
4|name-04|val-04
5|name-05|val-05
6|name-06|val-06
7|name-07|val-07

SELECT * FROM temp_limit LIMIT 3 OFFSET 0;
SELECT * FROM temp_limit LIMIT 3 OFFSET 3;
SELECT * FROM temp_limit LIMIT 3 OFFSET 6;

Uzyskany rezultat powinien być analogiczny do poniższego:

1|name-01|val-01
2|name-02|val-02
3|name-03|val-03

4|name-04|val-04
5|name-05|val-05
6|name-06|val-06

7|name-07|val-07
8|name-08|val-08
9|name-09|val-09

#top WHERE IN subselect with multiple columns¶

#top Deleting duplicates rows¶

#top REPLACE INTO¶

Składnia:
INSERT-statement

INSERT-expression

SELECT-statement

with-clause

For example:

INSERT OR REPLACE INTO Employee (id, name, role) VALUES (1, 'John Foo', 'CEO');

Or shorten to

REPLACE INTO Employee (id, name, role) VALUES (1, 'John Foo', 'CEO');

#top GROUP with CONCAT¶

-- comment

Zmodyfikowany ostatnio: 2017/11/02 00:00:43 (6 lat temu), textsize: 50,5 kB, htmlsize: 90,4 kB

Zapraszam do komentowania, zgłaszania sugestii, propozycji, własnych przykładów, ...

Dodaj komentarzKomentarze użytkowników