习题实战：
sqlite3 authors.db
SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
sqlite> .databases
main: /home/kowlooncen/authors.db
sqlite> SELECt tbl_name FROM sqlite_master WHERe type = 'table';
authors
Authors2
author
sqlite> SELECt sql FROM sqlite_master WHERe type = 'table' AND tbl_name = 'authors';
CREATE TABLE authors(
"first_name" TEXT,
"last_name" TEXT,
"title" TEXT,
"publisher" TEXT
)
sqlite> SELECt sql FROM sqlite_master WHERe type = 'table' AND tbl_name = 'author';
CREATE TABLE author
(
author_id INTEGER NOT NULL PRIMARY KEY,
first_name VARCHAR,
last_name VARCHAR
)
sqlite> SELECt sql FROM sqlite_master WHERe type = 'table' AND tbl_name = 'Authors2';
CREATE TABLE Authors2(
Authors_ID int not null primary key,
A_Name varchar(30),
A_Family_Name varchar(30),
Book varchar(100),
Publisher varchar(50)
)
sqlite> SELECt * FROM Authors2;
sqlite> SELECt * FROM authors;
Isaac|Asimov|Foundation|Random House
Pearl|Buck|The Good Earth|Random House
Pearl|Buck|The Good Earth|Simon & Schuster
Tom|Clancy|The Hunt For Red October|Berkley
Tom|Clancy|Patriot Games|Simon & Schuster
Stephen|King|It|Random House
Stephen|King|It|Penguin Random House
Stephen|King|Dead Zone|Random House
Stephen|King|The Shining|Penguin Random House
John|Le Carre|Tinker, Tailor, Solider, Spy: A George Smiley Novel|Berkley
Alex|Michaelides|The Silent Patient|Simon & Schuster
Carol|Shaben|Into The Abyss|Simon & Schuster
|||
Alexsandr|Pushkin||
sqlite> .header on
sqlite> .mode column
sqlite> SELECt * FROM authors;
first_name last_name title publisher
---------- ---------- ---------- ------------
Isaac Asimov Foundation Random House
Pearl Buck The Good E Random House
Pearl Buck The Good E Simon & Schu
Tom Clancy The Hunt F Berkley
Tom Clancy Patriot Ga Simon & Schu
Stephen King It Random House
Stephen King It Penguin Rand
Stephen King Dead Zone Random House
Stephen King The Shinin Penguin Rand
John Le Carre Tinker, Ta Berkley
Alex Michaelide The Silent Simon & Schu
Carol Shaben Into The A Simon & Schu
Alexsandr Pushkin
sqlite> SELECt * FROM author;
author_id first_name last_name
---------- ---------- ----------
1 Isaac Asimov
2 Pearl Buck
3 Pearl Buck
4 Tom Clancy
5 Tom Clancy
6 Stephen King
7 Stephen King
8 Stephen King
9 Stephen King
10 John Le Carre
11 Alex Michaelide
12 Carol Shaben
13
14 Alexsandr Pushkin
sqlite> .quite
Error: unknown command or invalid arguments: "quite". Enter ".help" for help
sqlite> .quit
$ sqlite3 testDB.db
SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
sqlite> CREATE TABLE COMPANY(
...> ID INT PRIMARY KEY NOT NULL,
...> NAME TEXT NOT NULL,
...> AGE INT NOT NULL,
...> ADDRESS CHAr(50),
...> SALARY REAL
...> );
sqlite> INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
...> VALUES (1, 'Paul', 32, 'California', 20000.00 );
sqlite> INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
...> VALUES (2, 'Allen', 25, 'Texas', 15000.00 );
sqlite> INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
...> VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );
sqlite> SELECt * FROM "COMPANY"
...> ;
1|Paul|32|California|20000.0
2|Allen|25|Texas|15000.0
3|Teddy|23|Norway|20000.0
sqlite> .show
echo: off
eqp: off
explain: auto
headers: off
mode: list
nullvalue: ""
output: stdout
colseparator: "|"
rowseparator: "n"
stats: off
width:
filename: testDB.db
sqlite> .header on
sqlite> .mode column
sqlite> .show
echo: off
eqp: off
explain: auto
headers: on
mode: column
nullvalue: ""
output: stdout
colseparator: "|"
rowseparator: "n"
stats: off
width:
filename: testDB.db
sqlite> SELECt * FROM "COMPANY";
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
sqlite> INSERT INTO COMPANY VALUES (3, 'James', 24, 'Houston', 10000.00 );
Error: UNIQUE constraint failed: COMPANY.ID
sqlite> INSERT INTO COMPANY VALUES (4, 'James', 24, 'Houston', 10000.00 );
sqlite> .show
echo: off
eqp: off
explain: auto
headers: on
mode: column
nullvalue: ""
output: stdout
colseparator: "|"
rowseparator: "n"
stats: off
width:
filename: testDB.db
sqlite> SELECt * FROM "COMPANY";
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 James 24 Houston 10000.0
sqlite> SELECt * FROM author.db;
Error: no such table: author.db
sqlite> CREATE TABLE author (
...> CREATE TABLE author (^Z
[2]+ Stopped sqlite3 testDB.db
sqlite3 authors.db
SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
sqlite> SELECt * FROM author.db;
Error: no such table: author.db
sqlite> CREATE TABLE author (
...> author_id INTEGER NOT NULL PRIMARY KEY,
...> first_name VARCHAR,
...> last_name VARCHAR
...> );
Error: table author already exists
sqlite> CREATE TABLE book (
...> book_id INTEGER NOT NULL PRIMARY KEY,
...> author_id INTEGER REFERENCES author,
...> title VARCHAR
...> );
sqlite> CREATE TABLE publisher (
...> publisher_id INTEGER NOT NULL PRIMARY KEY,
...> name VARCHAR
...> );
sqlite> .tables
Authors2 author authors book publisher
sqlite> INSERT INTO first_table_name [(column1, column2, ... columnN)]
...> SELECt column1, column2, ...columnN
...> FROM second_table_name
...> [WHERe condition];
Error: near "[(column1, column2, ... columnN)]": syntax error
sqlite> INSERT INTO author (first_name, last_name)
...> SELECt DISTINCT first_name, last_name
...> FROM authors;
sqlite> INSERT INTO author
...> (first_name, last_name)
...> VALUES ('Alexsandr', 'Pushkin');
sqlite> UPDATe author
...> SET first_name = 'Aleksandr'
...> WHERe first_name = 'Alexsandr' AND last_name = 'Pushkin';
sqlite> SELECT * FROM author;
1|Isaac|Asimov
2|Pearl|Buck
3|Pearl|Buck
4|Tom|Clancy
5|Tom|Clancy
6|Stephen|King
7|Stephen|King
8|Stephen|King
9|Stephen|King
10|John|Le Carre
11|Alex|Michaelides
12|Carol|Shaben
13||
14|Aleksandr|Pushkin
15|Isaac|Asimov
16|Pearl|Buck
17|Tom|Clancy
18|Stephen|King
19|John|Le Carre
20|Alex|Michaelides
21|Carol|Shaben
22||
23|Aleksandr|Pushkin
24|Aleksandr|Pushkin
sqlite> DELETe FROM author WHERe (author_id < 13);
sqlite> SELECt * FROM author;
13||
14|Aleksandr|Pushkin
15|Isaac|Asimov
16|Pearl|Buck
17|Tom|Clancy
18|Stephen|King
19|John|Le Carre
20|Alex|Michaelides
21|Carol|Shaben
22||
23|Aleksandr|Pushkin
24|Aleksandr|Pushkin
sqlite> INSERT INTO publisher (name)
...> SELECt DISTINCT publisher
...> FROM authors;
sqlite> SELECt * FROM publisher;
1|Random House
2|Simon & Schuster
3|Berkley
4|Penguin Random House
5|
sqlite> .show
echo: off
eqp: off
explain: auto
headers: off
mode: list
nullvalue: ""
output: stdout
colseparator: "|"
rowseparator: "n"
stats: off
width:
filename: authors.db
sqlite> .head on
sqlite> .mode column
sqlite> SELECt * FROM publisher;
publisher_id name
------------ ------------
1 Random House
2 Simon & Schu
3 Berkley
4 Penguin Rand
5
sqlite> .width(20,20)
Error: unknown command or invalid arguments: "width(20,20)". Enter ".help" for help
sqlite> .width 20,20
sqlite> SELECt * FROM publisher;
publisher_id name
-------------------- ------------
1 Random House
2 Simon & Schu
3 Berkley
4 Penguin Rand
5
sqlite> .width 10,20
sqlite> SELECt * FROM publisher;
publisher_ name
---------- ------------
1 Random House
2 Simon & Schu
3 Berkley
4 Penguin Rand
5
sqlite> .width 15,20
sqlite> SELECt * FROM publisher;
publisher_id name
--------------- ------------
1 Random House
2 Simon & Schu
3 Berkley
4 Penguin Rand
5
sqlite> INSERT INTO book (title)
...> SELECt title
...> FROM authors;
sqlite> SELECt * FROM book;
book_id author_id title
--------------- ---------- ----------
1 Foundation
2 The Good E
3 The Good E
4 The Hunt F
5 Patriot Ga
6 It
7 It
8 Dead Zone
9 The Shinin
10 Tinker, Ta
11 The Silent
12 Into The A
13
14
sqlite> SELECt * FROM authors;
first_name last_name title publisher
--------------- ---------- ---------- ------------
Isaac Asimov Foundation Random House
Pearl Buck The Good E Random House
Pearl Buck The Good E Simon & Schu
Tom Clancy The Hunt F Berkley
Tom Clancy Patriot Ga Simon & Schu
Stephen King It Random House
Stephen King It Penguin Rand
Stephen King Dead Zone Random House
Stephen King The Shinin Penguin Rand
John Le Carre Tinker, Ta Berkley
Alex Michaelide The Silent Simon & Schu
Carol Shaben Into The A Simon & Schu
Alexsandr Pushkin
sqlite> SELECt * FROM author;
author_id first_name last_name
--------------- ---------- ----------
13
14 Aleksandr Pushkin
15 Isaac Asimov
16 Pearl Buck
17 Tom Clancy
18 Stephen King
19 John Le Carre
20 Alex Michaelide
21 Carol Shaben
22
23 Aleksandr Pushkin
24 Aleksandr Pushkin
sqlite> SELECt * FROM publisher;
publisher_id name
--------------- ------------
1 Random House
2 Simon & Schu
3 Berkley
4 Penguin Rand
5
sqlite> INSERT INTO book (title)
...> SELECt DISTINCT title
...> FROM authors;
sqlite> SELECt * FROM book;
book_id author_id title
--------------- ---------- ----------
1 Foundation
2 The Good E
3 The Good E
4 The Hunt F
5 Patriot Ga
6 It
7 It
8 Dead Zone
9 The Shinin
10 Tinker, Ta
11 The Silent
12 Into The A
13
14
15 Foundation
16 The Good E
17 The Hunt F
18 Patriot Ga
19 It
20 Dead Zone
21 The Shinin
22 Tinker, Ta
23 The Silent
24 Into The A
25
sqlite> DELETe FROM bool WHERe (book_id < 15);
Error: no such table: bool
sqlite> DELETe FROM book WHERe (book_id < 15);
sqlite> SELECt * FROM book;
book_id author_id title
--------------- ---------- ----------
15 Foundation
16 The Good E
17 The Hunt F
18 Patriot Ga
19 It
20 Dead Zone
21 The Shinin
22 Tinker, Ta
23 The Silent
24 Into The A
25
sqlite> DROp TABLE book;
sqlite> .tables
Authors2 author authors publisher
sqlite> CREATE TABLE book (
...> book_id INTEGER NOT NULL PRIMARY KEY,
...> title VARCHAR
...> );
sqlite> INSERT INTO book (title)
...> SELECT DISTINCT title
...> from athors;
Error: no such table: athors
sqlite> from authors;
Error: near "from": syntax error
sqlite> INSERT INTO book (title)
...> SELECt DISTINCT title
...> from authors;
sqlite> SELECt * FROM book;
book_id title
--------------- ----------
1 Foundation
2 The Good E
3 The Hunt F
4 Patriot Ga
5 It
6 Dead Zone
7 The Shinin
8 Tinker, Ta
9 The Silent
10 Into The A
11
sqlite> .quit
$ sqlite3 testDB.db
SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
sqlite> .show
echo: off
eqp: off
explain: auto
headers: off
mode: list
nullvalue: ""
output: stdout
colseparator: "|"
rowseparator: "n"
stats: off
width:
filename: testDB.db
sqlite> .head on
sqlite> .mode column
sqlite> .tables
COMPANY a1
sqlite> DROp TABLE COMPANY;
sqlite> CREATE TABLE COMPANY(
...> ID INT PRIMARY KEY NOT NULL,
...> NAME TEXT NOT NULL,
...> AGE INT NOT NULL,
...> ADDRESS CHAr(50),
...> SALARY REAL
...> );
sqlite> INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
...> VALUES (1, 'Paul', 32, 'California', 20000.00 );
sqlite>
sqlite> INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
...> VALUES (2, 'Allen', 25, 'Texas', 15000.00 );
sqlite>
sqlite> INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
...> VALUES (3, 'Teddy', 23, 'Norway', 20000.00 );
sqlite>
sqlite> INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
...> VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 );
sqlite>
sqlite> INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
...> VALUES (5, 'David', 27, 'Texas', 85000.00 );
sqlite>
sqlite> INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
...> VALUES (6, 'Kim', 22, 'South-Hall', 45000.00 );
sqlite> SELECT * FROM COMPANY
...> ;
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- ---------- ----------
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
sqlite> .width 10 10 10 20 10
sqlite> INSERT INTO COMPANY VALUES (7, 'James', 24, 'Houston', 10000.00 );
sqlite> SELECt * FROM COMPANY
...> ;
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- -------------------- ----------
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0
sqlite> SELECt tbl_name FROM sqlite_master WHERe type = 'table';
tbl_name
----------
a1
COMPANY
sqlite> SELECt sql FROM sqlite_master WHERe type = 'table' AND tbl_name = 'COMPANY';
sql
----------
CREATE TAB
sqlite> SELECt * FROM COMPANY WHERe AGE >= 25 AND SALARY >= 65000;
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- -------------------- ----------
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
sqlite> SELECt * FROM COMPANY WHERe AGE IS NOT NULL;
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- -------------------- ----------
1 Paul 32 California 20000.0
2 Allen 25 Texas 15000.0
3 Teddy 23 Norway 20000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
6 Kim 22 South-Hall 45000.0
7 James 24 Houston 10000.0
sqlite> SELECt * FROM COMPANY WHERe NAME LIKE 'Ki%';
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- -------------------- ----------
6 Kim 22 South-Hall 45000.0
sqlite> SELECt * FROM COMPANY WHERe AGE IN ( 25, 27 );
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- -------------------- ----------
2 Allen 25 Texas 15000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
sqlite> SELECt * FROM COMPANY WHERe AGE BETWEEN 25 AND 27;
ID NAME AGE ADDRESS SALARY
---------- ---------- ---------- -------------------- ----------
2 Allen 25 Texas 15000.0
4 Mark 25 Rich-Mond 65000.0
5 David 27 Texas 85000.0
sqlite> CREATE TABLE DEPARTMENT(
...> ID INT PRIMARY KEY NOT NULL,
...> DEPT CHAr(50) NOT NULL,
...> EMP_ID INT NOT NULL
...> );
sqlite> INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID)
...> VALUES (1, 'IT Billing', 1 );
sqlite> INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID)
...> VALUES (2, 'Engineering', 2 );
sqlite> INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID)
...> VALUES (3, 'Finance', 7 );
sqlite> SELECt EMP_ID, NAME, DEPT FROM COMPANY CROSS JOIN
...> DEPARTMENT;
EMP_ID NAME DEPT
---------- ---------- ----------
1 Paul IT Billing
2 Paul Engineerin
7 Paul Finance
1 Allen IT Billing
2 Allen Engineerin
7 Allen Finance
1 Teddy IT Billing
2 Teddy Engineerin
7 Teddy Finance
1 Mark IT Billing
2 Mark Engineerin
7 Mark Finance
1 David IT Billing
2 David Engineerin
7 David Finance
1 Kim IT Billing
2 Kim Engineerin
7 Kim Finance
1 James IT Billing
2 James Engineerin
7 James Finance
sqlite> SELECt EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN
...> DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID;
EMP_ID NAME DEPT
---------- ---------- ----------
1 Paul IT Billing
2 Allen Engineerin
7 James Finance
sqlite> CREATE TABLE TAB2(
...> EMP_ID INT NOT NULL,
...> DEPT CHAr(50) NOT NULL,
...> NAME CHAr(50) NOT NULL
...> );
sqlite> INSERT INTO tab2
...> SELECt EMP_ID, NAME, DEPT FROM COMPANY CROSS JOIN DEPARTMENT;
sqlite> SELECt * FROM tab2
...> ;
EMP_ID DEPT NAME
---------- ---------- ----------
1 Paul IT Billing
2 Paul Engineerin
7 Paul Finance
1 Allen IT Billing
2 Allen Engineerin
7 Allen Finance
1 Teddy IT Billing
2 Teddy Engineerin
7 Teddy Finance
1 Mark IT Billing
2 Mark Engineerin
7 Mark Finance
1 David IT Billing
2 David Engineerin
7 David Finance
1 Kim IT Billing
2 Kim Engineerin
7 Kim Finance
1 James IT Billing
2 James Engineerin
7 James Finance
sqlite> CREATE TABLE TAB1(
...> EMP_ID INT NOT NULL,
...> DEPT CHAr(50) NOT NULL,
...> NAME CHAr(50) NOT NULL
...> );
sqlite> INSERT INTO tab1
...> SELECt EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT ON
...> COMPANY.ID = DEPARTMENT.EMP_ID;
sqlite> SELECt * FROM tab1
...> ;
EMP_ID DEPT NAME
---------- ---------- ----------
1 Paul IT Billing
2 Allen Engineerin
7 James Finance
sqlite> .quit
