Ubuntu下Sqlite3的基本命令使用

每个存储在 SQLite 数据库中的值都具有以下存储类之一：
存储类描述NULL值是一个 NULL 值。INTEGER值是一个带符号的整数，根据值的大小存储在 1、2、3、4、6 或 8 字节中。REAL值是一个浮点值，存储为 8 字节的 IEEE 浮点数字。TEXT值是一个文本字符串，使用数据库编码（UTF-8、UTF-16BE 或 UTF-16LE）存储。BLOB值是一个 blob 数据，完全根据它的输入存储。亲和类型

如下操作：创建–查看test.db

zion6135@zion6135-VirtualBox:~/Desktop/sqlite3$ sqlite3 test.db  #创建
SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.

sqlite> .open test.db  #打开

sqlite> .quit  #退出

zion6135@zion6135-VirtualBox:~/Desktop/sqlite3$ ls
test.db

zion6135@zion6135-VirtualBox:~/Desktop/sqlite3$

db转sql文件
zion6135@zion6135-VirtualBox:~/Desktop/sqlite3$ sqlite3 testDB.db .dump > testDB.sql
#sql文件转db文件
zion6135@zion6135-VirtualBox:~/Desktop/sqlite3$ sqlite3 testDB.db < testDB.sql

语法：ATTACH DATABASE file_name AS database_name;

zion6135@zion6135-VirtualBox:~/Desktop/sqlite3$ sqlite3
SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.

Connected to a transient in-memory database.

Use ".open FILENAME" to reopen on a persistent database.

#ATTACH
sqlite> ATTACH DATABASE 'testDB.db' as 'TEST';
sqlite> .databases
main:
TEST: /home/zion6135/Desktop/sqlite3/testDB.db

#main不可用于连接，注意错误
sqlite> ATTACH DATABASE 'testDB.db' as 'main';
Error: database main is already in use

语法：DETACH DATABASE ‘Alias-Name’;

attach到tttt
sqlite> ATTACH DATABASE 'testDB.db' as 'tttt';
sqlite> .databases
main:
TEST: /home/zion6135/Desktop/sqlite3/testDB.db
tttt: /home/zion6135/Desktop/sqlite3/testDB.db

#detachtttt
sqlite> DETACH DATABASE 'tttt';
sqlite> .databases
main:
TEST: /home/zion6135/Desktop/sqlite3/testDB.db

语法 CREATE TABLE 语句的基本语法如下：

CREATE TABLE database_name.table_name(
   column1 datatype  PRIMARY KEY(one or more columns),
   column2 datatype,
   column3 datatype,
   .....

   columnN datatype,
);

#创建表COMPANY
sqlite> CREATE TABLE COMPANY(
   ...>    ID INT PRIMARY KEY     NOT NULL,
   ...>    NAME           TEXT    NOT NULL,
   ...>    AGE            INT     NOT NULL,
   ...>    ADDRESS        CHAR(50),
   ...>    SALARY         REAL
   ...> );

#创建表DEPARTMENT
sqlite> CREATE TABLE DEPARTMENT(
   ...>    ID INT PRIMARY KEY      NOT NULL,
   ...>    DEPT           CHAR(50) NOT NULL,
   ...>    EMP_ID         INT      NOT NULL
   ...> );

#查看有的表
sqlite> .tables
COMPANY     DEPARTMENT

#查看表COMPANY的具体内容
sqlite> .schema COMPANY
CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);

语法：DROP TABLE database_name.table_name;

#查看有的表
sqlite> .tables
COMPANY     DEPARTMENT
#删除表
sqlite> DROP TABLE COMPANY;
#查看：表COMPANY被删除
sqlite> .tables
DEPARTMENT
sqlite>

用于向数据库的某个表中添加新的数据行

语法：INSERT INTO TABLE_NAME [(column1, column2, column3,...columnN)]
VALUES (value1, value2, value3,...valueN);

#向COMPANY表插入信息
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Paul', 32, 'California', 20000.00 );

语法：INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);

#向COMPANY表插入信息
INSERT INTO COMPANY VALUES (7, 'James', 24, 'Houston', 10000.00 );

从 SQLite 数据库表中获取数据，以结果表的形式返回数据。这些结果表也被称为结果集。

获取所有可用的字段语法：SELECT * FROM table_name;

sqlite> SELECT * FROM COMPANY;
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

获取特定的列的内容语法：SELECT column1, column2, columnN FROM table_name;

sqlite> SELECT ID, NAME, SALARY FROM COMPANY;
1|Paul|20000.0
2|Allen|15000.0
3|Teddy|20000.0
4|Mark|65000.0
5|David|85000.0
6|Kim|45000.0

用SELECT来查询数据库中相关内容

#查找table
sqlite> SELECT tbl_name FROM sqlite_master WHERE type = 'table';
DEPARTMENT
COMPANY
sqlite>

#查找COMPANY table
sqlite> SELECT tbl_name FROM sqlite_master WHERE type = 'table' AND tbl_name = 'COMPANY';
COMPANY
sqlite>

*运算符

算术运算符（+ – * /）

sqlite> select 1+2
   ...> ;
3
sqlite>

比较运算符

WHERE 子句是用来设置 SELECT 语句的条件语句

sqlite> SELECT * FROM COMPANY WHERE SALARY > 50000;
4|Mark|25|Rich-Mond |65000.0
5|David|27|Texas|85000.0
sqlite>

逻辑运算符

#相当于 if (AGE > 25 && SALARY >= 65000)
sqlite> SELECT * FROM COMPANY WHERE AGE >= 25 AND SALARY >= 65000;
4|Mark|25|Rich-Mond |65000.0
5|David|27|Texas|85000.0
sqlite>

位运算符

sqlite> select (1 << 2);
4
sqlite>


sqlite> SELECT * FROM COMPANY;
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|Texas|45000.0

#修改ID为6的内容ADDRESS改为Texas5
sqlite> UPDATE COMPANY SET ADDRESS = 'Texas5' WHERE ID = 6;
sqlite> SELECT * FROM COMPANY;
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|Texas5|45000.0

DELETE FROM table_name WHERE [condition];

sqlite> SELECT * FROM COMPANY;
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|Texas5|45000.0
#删除ID为6的内容
sqlite> DELETE FROM COMPANY WHERE ID = 6;

sqlite> SELECT * FROM COMPANY;
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

百分号（%）代表零个、一个或多个数字或字符。下划线（_）代表一个单一的数字或字符。

sqlite> SELECT * FROM COMPANY;
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

#中间有字符-的内容
sqlite> SELECT * FROM COMPANY WHERE ADDRESS  LIKE '%-%';
4|Mark|25|Rich-Mond |65000.0

GLOB 运算符是用来匹配通配符指定模式的文本值。和LIKE很像

sqlite> SELECT * FROM COMPANY;
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

#&#x627E;&#x6709;2*&#x7684;&#x5B57;&#x7B26;
sqlite> SELECT * FROM COMPANY WHERE AGE  GLOB '2*';
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
sqlite>

LIMIT子句限制SELECT返回的数据数量
SELECT column1, column2, columnN
FROM table_name
LIMIT [no of rows] OFFSET [row num]

sqlite> SELECT * FROM COMPANY LIMIT 6;
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

#限制返回最多一条
sqlite> SELECT * FROM COMPANY LIMIT 1;
1|Paul|32|California|20000.0

基于一个或多个列按升序或降序排列数据

SELECT column-list
FROM table_name
[WHERE condition]
[ORDER BY column1, column2, .. columnN] [ASC | DESC];

#SALARY 升序排列
sqlite> SELECT * FROM COMPANY ORDER BY SALARY ASC;
2|Allen|25|Texas|15000.0
1|Paul|32|California|20000.0
3|Teddy|23|Norway|20000.0
4|Mark|25|Rich-Mond |65000.0
5|David|27|Texas|85000.0

#SALARY 降序排序
sqlite> SELECT * FROM COMPANY ORDER BY SALARY DESC;
5|David|27|Texas|85000.0
4|Mark|25|Rich-Mond |65000.0
1|Paul|32|California|20000.0
3|Teddy|23|Norway|20000.0
2|Allen|25|Texas|15000.0
sqlite>

Original: https://blog.csdn.net/m0_37844072/article/details/122920309
Author: L7256
Title: Ubuntu下Sqlite3的基本命令使用

原创文章受到原创版权保护。转载请注明出处：https://www.johngo689.com/815573/

转载文章受原作者版权保护。转载请注明原作者出处！

2024 年 5 月
一	二	三	四	五	六	日
		1	2	3	4	5
6	7	8	9	10	11	12
13	14	15	16	17	18	19
20	21	22	23	24	25	26
27	28	29	30	31

Ubuntu下Sqlite3的基本命令使用

语法 CREATE TABLE 语句的基本语法如下：

大家都在看