SQLite (http://www.sqlite.org) , SQL92.
, MySQL, PostgreSQL , SQLite , , .
SQLite .
SQLite , Python, C, C++, PHP, Ruby, Delphi, Java, Haskell, Smalltalk .
SQLite Windows SQLiteSpy, :
http://www.yunqa.de/delphi/doku.php/products/sqlitespy/index
. World.db3, .
, SQLite , SQLiteSpy SQLite , SQLite SQLiteSpy.
.
1 - SQLiteSpy
World.db3 (File -> Open Database)
.
2
, main 3 (, , ) 3 ( )( , ). (Collations) .
SQLiteSpy .
, , Show Data. . , .
3
, , .
, , .
. , Edit -> Edit Cell, F2. .
4
, .
|
|
|
, , .
SQL-, ( Show CREATE Statement) ( Show SELECT Statement). .
, City :
5
6
( DROP TABLE _).
!!!
SQLiteSpy.
, , (). Columns , , 2 Columns
7
SQL-:
8
. , ( ):
,
( ) NULL-
( )
( )
9
SQLiteSpy, File -> New Database , , .
. 2 : . . ( ).
SQL create table, , , , , . :
NOT NULL ( );
PRIMARY KEY ( NOT NULL). , , , ;
UNIQUE ( );
FOREIGN KEYREFERENCES , ( );
DEFAULT .
create table (
ID INTEGER PRIMARY KEY,
|
|
|
VARCHAR(30));
, 2 : ID INTEGER ( ) VARCHAR(30) ( ). (PRIMARY KEY).
, , (.. ). :
CREATE TABLE T1 (
A INT,
B INT,
C INT,
PRIMARY KEY (A, B));
T1 (A, B, C), (A, B) .
, .. .
F9 Execute -> Execute SQL. , SQLiteSpy . , :
11
, , .. .
(File -> Save SQL).
. :
create table (ID INTEGER PRIMARY KEY, FOREIGN KEY ID INTEGER NOT NULL REFERENCES (ID), VARCHAR(30) NOT NULL, INT);
, ( ) , . , ID ( ), ID .
, , , . SQLite , . , .
, , , Ctrl-F9. , , , :
drop table if exists ;drop table if exists ;
( ) .
SQLite . , ALTER TABLE.
T1. D:
alter table T1 rename to TABLE1;alter table TABLE1 add D INT NOT NULL;
. .
INSERT INTO. values, , , - , . .
:
insert into values (NULL,'Peter Gabriel');
ID NULL 'Peter Gabriel'. , ID ( NULL), , ( 1) ID . , , , 1.
, NULL , , , . , . , . :
|
|
|
insert into ()
values ('Bruce Hornsby');
, , ( ), ('Bruce Hornsby'). ID 2.
.
insert into ()
values ('Lyle Lovett');
insert into ()
values ('Beach Boys');
. ID, .
insert into (ID,,)
values (1,'So',1984);
insert into (ID,,)
values (1,'Us',1992);
insert into (ID,,)
values (2,'The Way It Is',1986);
insert into (ID,,)
values (2,'Scenes from the Southside',1990);
insert into (ID,,)
values (1,'Security',1990);
insert into (ID,,)
values (3,'Joshua Judges Ruth',1992);
insert into (ID,,)
values (4,'Pet Sounds',1966);
, (VARCHAR(30)) . ID ID .
, SQLite FOREIGN KEY. ID, ID , . , , . :
http://www.sqlite.org/cvstrac/wiki?p=ForeignKeyTriggers
SQLite . , . .
.
insert into () values ('Supernatural');
. , . UPDATE. UPDATE , , SET, = , . (, - ), WHERE. .
.
UPDATE SET = 'Santana' WHERE ArtistID=5;
.
insert into (ID,ID,,) values (NULL,5,'Supernatural','1999');
- . , - , Super.
DELETE. WHERE , . , .
|
|
|
:
DELETE FROM WHERE = 'Supernatural';
(view). CREATE VIEW. , . , , ( SELECT), . SELECT, .
, , .
CREATE VIEW AS
SELECT AS FROM ;
.
DROP VIEW ;
