C:\Users\23232>sqlite3 SQLite version 3.50.4 2025-07-30 19:33:53 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite>
sqlite> .help .archive ... Manage SQL archives .auth ON|OFF Show authorizer callbacks .backup ?DB? FILE Backup DB (default "main") to FILE .bail on|off Stop after hitting an error. Default OFF .cd DIRECTORY Change the working directory to DIRECTORY .changes on|off Show number of rows changed by SQL .check GLOB Fail if output since .testcase does not match .clone NEWDB Clone data into NEWDB from the existing database .connection [close] [#] Open or close an auxiliary database connection .crlf ?on|off? Whether or not to use \r\n line endings .databases List names and files of attached databases .dbconfig ?op? ?val? List or change sqlite3_db_config() options .dbinfo ?DB? Show status information about the database .dbtotxt Hex dump of the database file .dump ?OBJECTS? Render database content as SQL .echo on|off Turn command echo on or off .eqp on|off|full|... Enable or disable automatic EXPLAIN QUERY PLAN .excel Display the output of next command in spreadsheet .exit ?CODE? Exit this program with return-code CODE .expert EXPERIMENTAL. Suggest indexes for queries .explain ?on|off|auto? Change the EXPLAIN formatting mode. Default: auto .filectrl CMD ... Run various sqlite3_file_control() operations .fullschema ?--indent? Show schema and the content of sqlite_stat tables .headers on|off Turn display of headers on or off .help ?-all? ?PATTERN? Show help text for PATTERN .import FILE TABLE Import data from FILE into TABLE .indexes ?TABLE? Show names of indexes .intck ?STEPS_PER_UNLOCK? Run an incremental integrity check on the db .limit ?LIMIT? ?VAL? Display or change the value of an SQLITE_LIMIT .lint OPTIONS Report potential schema issues. .load FILE ?ENTRY? Load an extension library .log FILE|on|off Turn logging on or off. FILE can be stderr/stdout .mode ?MODE? ?OPTIONS? Set output mode .nonce STRING Suspend safe mode for one command if nonce matches .nullvalue STRING Use STRING in place of NULL values .once ?OPTIONS? ?FILE? Output for the next SQL command only to FILE .open ?OPTIONS? ?FILE? Close existing database and reopen FILE .output ?FILE? Send output to FILE or stdout if FILE is omitted .parameter CMD ... Manage SQL parameter bindings .print STRING... Print literal STRING .progress N Invoke progress handler after every N opcodes .prompt MAIN CONTINUE Replace the standard prompts .quit Stop interpreting input stream, exit if primary. .read FILE Read input from FILE or command output .recover Recover as much data as possible from corrupt db. .restore ?DB? FILE Restore content of DB (default "main") from FILE .save ?OPTIONS? FILE Write database to FILE (an alias for .backup ...) .scanstats on|off|est Turn sqlite3_stmt_scanstatus() metrics on or off .schema ?PATTERN? Show the CREATE statements matching PATTERN .separator COL ?ROW? Change the column and row separators .session ?NAME? CMD ... Create or control sessions .sha3sum ... Compute a SHA3 hash of database content .shell CMD ARGS... Run CMD ARGS... in a system shell .show Show the current values for various settings .stats ?ARG? Show stats or turn stats on or off .system CMD ARGS... Run CMD ARGS... in a system shell .tables ?TABLE? List names of tables matching LIKE pattern TABLE .timeout MS Try opening locked tables for MS milliseconds .timer on|off Turn SQL timer on or off .trace ?OPTIONS? Output each SQL statement as it is run .version Show source, library and compiler versions .vfsinfo ?AUX? Information about the top-level VFS .vfslist List all available VFSes .vfsname ?AUX? Print the name of the VFS stack .width NUM1 NUM2 ... Set minimum column widths for columnar output .www Display output of the next command in web browser
E:\SQLite>sqlite3 test.db SQLite version 3.50.4 2025-07-30 19:33:53 Enter ".help" for usage hints. sqlite> .databases; Error: unknown command or invalid arguments: "databases;". Enter ".help" for help sqlite> .databases main: E:\SQLite\test.db r/w sqlite> .quit
数据库名称 main 和 temp 被保留用于主数据库和存储临时表及其他临时数据对象的数据库。这两个数据库名称可用于每个数据库连接，且不应该被用于附加，否则将得到一个警告消息
1 2 3 4
sqlite> attach database 'test.db' as 'main'; Runtime error: database main is already in use sqlite> attach database 'test.db' as 'temp'; Runtime error: database temp is already in use
sqlite> create table helloworld( (x1...> id integer primary key not null, (x1...> username text not null, (x1...> password text not null (x1...> );
.tables 命令
.tables 命令可以用于 显示当前数据库里所有表的列表。
我们看看刚刚创建的数据表是否存在
1 2 3 4 5 6 7
sqlite> create table helloworld( (x1...> id integer primary key not null, (x1...> username text not null, (x1...> password text not null (x1...> ); sqlite> .tables TEST.helloworld helloworld
主数据库表：helloworld 是您在当前数据库中创建的表，属于主数据库（通常是 main 数据库）。
附加数据库表：TEST.helloworld 表示在一个名为 TEST 的附加数据库中的 helloworld 表。
sqlite> .schema CREATE TABLE helloworld( id integer primary key not null, username text not null, password text not null ); CREATE TABLE TEST.helloworld( id integer primary key not null, username text not null, password text not null );
GLOB 运算符用于把某个值与使用通配符运算符的相似值进行比较。GLOB 与 LIKE 不同之处在于，它是大小写敏感的。
NOT
NOT 运算符是所用的逻辑运算符的对立面。比如 NOT EXISTS、NOT BETWEEN、NOT IN，等等。它是否定运算符。
OR
OR 运算符用于结合一个 SQL 语句的 WHERE 子句中的多个条件。
IS NULL
NULL 运算符用于把某个值与 NULL 值进行比较。
IS
IS 运算符与 = 相似。
IS NOT
IS NOT 运算符与 != 相似。
||
连接两个不同的字符串，得到一个新的字符串。
UNIQUE
UNIQUE 运算符搜索指定表中的每一行，确保唯一性（无重复）。
8、WHERE子语句
SQLite的WHERE子句用于指定从一个表或多个表中获取数据的条件。
基础语法
1 2 3
SELECT column1, column2, columnN FROM table_name WHERE [condition]
例如我们希望查询id小于2以及小于3的数据内容
1 2 3 4 5 6 7 8 9
sqlite> select * from helloword where id < 2; id username password -- ---------- -------- 1 wanth3f1ag 123456 sqlite> select * from helloword where id < 3; id username password -- ---------- --------- 1 wanth3f1ag 123456 2 test1 123123123
9、更新数据
UPDATE命令
SQLite的UPDATE语句用于修改表中已有的数据行的数据
基础语法
1 2 3
UPDATE table_name SET column1 = value1, column2 = value2...., columnN = valueN WHERE [condition];
举个例子
1 2 3 4 5 6 7 8 9 10 11 12
sqlite> select * from helloword; id username password -- ---------- --------- 1 wanth3f1ag 123456 2 test1 123123123 sqlite> update helloword ...> set username = 'test2',password = '456456456' where id = 1; sqlite> select * from helloword; id username password -- -------- --------- 1 test2 456456456 2 test1 123123123
更新数据最好结合where子语句去准确定位需要更新的数据行，如果没有的话就是更新所有的数据
举个例子
1 2 3 4 5 6 7 8 9 10 11 12
sqlite> select * from helloword; id username password -- -------- --------- 1 test2 456456456 2 test1 123123123 sqlite> update helloword ...> set username = 'All_user' , password = '123456'; sqlite> select * from helloword; id username password -- -------- -------- 1 All_user 123456 2 All_user 123456
可以看到这里所有的数据行都更新了
10、删除数据
DELETE 命令
SQLite 的DELETE查询用于删除表中已有的记录。可以使用带有 WHERE 子句的 DELETE 查询来删除选定行，否则所有的记录都会被删除。
基础语法
1 2
DELETE FROM table_name WHERE [condition];
我们尝试删除刚刚的id为2的数据
1 2 3 4 5 6 7 8 9 10
sqlite> select * from helloword; id username password -- -------- -------- 1 All_user 123456 2 All_user 123456 sqlite> delete from helloword where id = 2; sqlite> select * from helloword; id username password -- -------- -------- 1 All_user 123456
如果没有指定where子语句的话
1 2 3 4 5 6 7 8 9 10 11 12 13
sqlite> select * from helloword; id username password -- -------- -------- 1 All_user 123456 sqlite> insert into helloword values(2,'111','123123'); sqlite> select * from helloword; id username password -- -------- -------- 1 All_user 123456 2 111 123123 sqlite> delete from helloword; sqlite> select * from helloword; sqlite>
LIKE运算符
LIKE 是一种 模糊匹配 运算符，用来在 WHERE 子句中匹配部分字符串。，如果匹配则返回真(TRUE)，否则为假(FALSE)
sqlite> select * from helloword where password glob 'admin*'; id username password -- -------- -------- 2 admin123 admin123 sqlite> select * from helloword where password glob 'admi[a-z]*'; id username password -- -------- -------- 2 admin123 admin123 sqlite> select * from helloword where password glob 'admi[1-9]*';
Limit子语句
和mysql一样的，用于限制由SELECT语句返回的数据结果集的数量
Order By子语句
SQLite 的 ORDER BY 子句是用来基于一个或多个列按升序或降序顺序排列数据。
基础语法
1 2 3 4
SELECT column-list FROM table_name [WHERE condition] [ORDER BY column1, column2, .. columnN] [ASC | DESC];
ASC 默认值，从小到大，升序排列
DESC 从大到小，降序排列
1 2 3 4 5 6 7
sqlite> select * from helloword order by 3; id username password -- -------- -------- 1 admin 123123 2 admin123 admin123 sqlite> select * from helloword order by 4; Parse error: 1st ORDER BY term out of range - should be between 1 and 3
SELECT column-list FROM table_name WHERE [ conditions ] GROUP BY column1, column2....columnN ORDER BY column1, column2....columnN
需要注意的是GROUP BY 子句必须放在 WHERE 子句中的条件之后，必须放在 ORDER BY 子句之前。
group by 子句也可以用来测试字段数
1 2 3 4 5 6 7
sqlite> select * from helloword group by 3; id username password -- -------- -------- 1 admin 123123 2 admin123 admin123 sqlite> select * from helloword group by 4; Parse error: 1st GROUP BY term out of range - should be between 1 and 3
UNION联合查询
SQLite的 UNION 子句/运算符用于合并两个或多个 SELECT 语句的结果，不返回任何重复的行。
#5.执行查询的SQL语句 c.execute("select * from helloworld") rows1 = c.fetchall() for row in rows1: print(row)
#7.执行更新数据的查询语句 c.execute("update helloworld set password=? where id=?",("123123123",1)) conn.commit()
c.execute("select * from helloworld") rows2 = c.fetchall() for row in rows2: print(row)
#8.执行删除数据的SQL语句 c.execute("delete from helloworld where id=?",(1,)) conn.commit() c.execute("select * from helloworld") rows3 = c.fetchall() for row in rows3: print(row)
```sqlite sqlite> SELECT * FROM sqlite_master; type name tbl_name rootpage sql ----- ---------- ---------- -------- -------------------------------- table helloworld helloworld 2 CREATE TABLE helloworld( id integer primary key not null, username text not null, password text not null )
可以看到这个表记录了当前数据库中的一个表的表名以及他对应的sql语句
所以由此可以得出我们常规的查询表的语句就是
查询表名
1 2
SELECT name FROM sqlite_master WHERE type='table'; SELECT tbl_name FROM sqlite_master WHERE type='table';
group_concat(X)/group_concat(X,Y) 函数能将多行值拼接成字符串，中间用逗号分隔。如果存在参数 Y，则将其用作 X 实例之间的分隔符。如果省略 Y，则使用逗号 （“，”） 作为分隔符。
举个例子
1 2 3 4 5 6 7 8 9 10 11 12 13
sqlite> select tbl_name from sqlite_master where type = 'table'; tbl_name ----------- helloworld helloworld2 sqlite> select group_concat(tbl_name) from sqlite_master where type = 'table'; group_concat(tbl_name) ---------------------- helloworld,helloworld2 sqlite> select group_concat(tbl_name,'111') from sqlite_master where type = 'table'; group_concat(tbl_name,'111') ---------------------------- helloworld111helloworld2
sqlite> select tbl_name from sqlite_master where type = 'table' and substr((select group_concat(sql) from sqlite_master),1,1)>'a'; sqlite> select tbl_name from sqlite_master where type = 'table' and substr((select group_concat(sql) from sqlite_master),1,1)<'a'; tbl_name ----------- helloworld helloworld2
如果结合编码函数就是
1 2 3 4 5 6
sqlite> select tbl_name from sqlite_master where type = 'table' and unicode(substr((select group_concat(sql) from sqlite_master),1,1))>97; sqlite> select tbl_name from sqlite_master where type = 'table' and unicode(substr((select group_concat(sql) from sqlite_master),1,1))<97; tbl_name ----------- helloworld helloworld2
