数据库（SQLite）学习笔记（四）

最新推荐文章于 2024-09-10 11:01:32 发布

Kowlooncen

最新推荐文章于 2024-09-10 11:01:32 发布

阅读量2.4k

点赞数

分类专栏：数据库文章标签： sqlite 数据库 database

本文链接：https://blog.csdn.net/Kowlooncen/article/details/121802929

版权

习题实战：

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 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> .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