sqlite3 基本命令

官方文档:https://www.sqlite.org/cli.html

大致学了一下，还是去用mysql吧，这玩意咋感觉很不靠谱的样子。

打开一个database

在sqlite中一个database就对应本地的一个数据库文件，所以没有showdatabases这种东西，直接打开本地的数据库文件，或者创建一个新的即可

直接打开或创建一个新的

sqlite3 test.db

先进入sqlite再打开database

sqlite3
.open test.db

查看当前的数据库

.databases

sqlite> .databases
main: D:\code\python\sqllite\test3.db r/w

sqlite> .dbinfo
database page size:  4096
write format:        1
read format:         1
reserved bytes:      0
file change counter: 2
database page count: 3
freelist page count: 0
schema cookie:       2
schema format:       4
default cache size:  0
autovacuum top root: 0
incremental vacuum:  0
text encoding:       1 (utf8)
user version:        0
application id:      0
software version:    3047002
number of tables:    2
number of indexes:   0
number of triggers:  0
number of views:     0
schema size:         111
data version         3

查看当前的设置

.show

sqlite> .show
        echo: off
         eqp: off
     explain: auto
     headers: off
        mode: list
   nullvalue: ""
      output: stdout
colseparator: "|"
rowseparator: "\n"
       stats: off
       width:
    filename: test3.db

创建一个table

create table person(name text, age int);
create table Project(Project_Id int primary_key, Project_Name char(255));

查看table

.tables

sqlite> .tables
Project  person

查看表结构，它只能看创建表的命令，

.schema ?PATTERN?        Show the CREATE statements matching PATTERN

sqlite> .schema Project
CREATE TABLE Project(Project_Id int primary_key, Project_Name char(255));

插入数据

insert into Project (Project_Id, Project_Name) values (10, 'hello');

查询

sqlite> select * from Project;
10|hello

查询当前输出的格式

sqlite> .mode
current output mode: list

改变输出的格式

The sqlite3 program is able to show the results of a query in 14 different output formats:

ascii
box
csv
column
html
insert
json
line
list
markdown
quote
table
ta

sqlite> .mode table
sqlite> select * from Project;
+------------+--------------+
| Project_Id | Project_Name |
+------------+--------------+
| 10         | hello        |
+------------+--------------+

sqlite> insert into Project (Project_name) values ('aaa');
sqlite> select * from Project;
+------------+--------------+
| Project_Id | Project_Name |
+------------+--------------+
| 10         | hello        |
|            | aaa          |
+------------+--------------+

可以看到主键也可以为空。

sqlite> select * from Project where Project_Id is NULL;
+------------+--------------+
| Project_Id | Project_Name |
+------------+--------------+
|            | aaa          |
|            | bbbaaa       |
+------------+--------------+

sqlite> insert into Project (Project_name, Project_id) values ('bbbaaacc', 10);
sqlite> select * from Project;
+------------+--------------+
| Project_Id | Project_Name |
+------------+--------------+
| 10         | hello        |
|            | aaa          |
|            | bbbaaa       |
| 10         | bbbaaacc     |
+------------+--------------+

这是什么沙雕。

sqlite> .schema Project
CREATE TABLE Project(Project_Id int primary_key, Project_Name char(255));

这玩意写primary key中间不能加下划线，加了不生效，他娘的还不报错，woc。

sqlite> .schema Stu
CREATE TABLE Stu(id int primary key, name char(200));
sqlite> insert into Stu values (1, 'alimiing');
Runtime error: UNIQUE constraint failed: Stu.id (19)

sqlite> select * from Stu;
+----+------------+
| id |    name    |
+----+------------+
| 1  | limiing    |
|    | alimiing   |
|    | alimiingaa |
+----+------------+

null可以为主键的值，woc，，，

sqlite> create table apple(id int primary key not null, name char(200));
sqlite> .schema APPLE
CREATE TABLE apple(id int primary key not null, name char(200));
sqlite> insert into apple (name) values ('hongfushiaa');
Runtime error: NOT NULL constraint failed: apple.id (19)

必须给主键再加上not null，。。。。。woc，你猜主键是用来干嘛的，还要我说不能为null？？？

sqlite> create table ball2(id integer primary key autoincrement not null, name char(200));
sqlite> create table ball3(id int primary key autoincrement not null, name char(200));
Parse error: AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY
sqlite> create table ball4(id integer primary key not null autoincrement, name char(200));
Parse error: near "autoincrement": syntax error
  reate table ball4(id integer primary key not null autoincrement, name char(200
                                      error here ---^

自增长必须是integer 并且 not null 必须在autoincrement后面声明。