1# SQLite Debugging Tool 2 3SQLite is a lightweight, embedded, serverless relational database management system. Its core advantage is that the entire database is stored in a single file, without the need for an independent server process. It supports cross-platform operation and is widely used in mobile applications, embedded devices, and desktop software. 4 5This debugging tool implements operations on the SQLite database based on the hdc command. It provides an efficient way to manage the SQLite database through the CLI, which is applicable to quick database operations and verification in the development, debugging, and O&M phases. 6 7## Environment Requirements 8 9- Before using this tool, you should enable the developer mode, obtain the hdc tool, and run **hdc shell**. 10- The devices are properly connected. 11 12## Preparations 13 14Before using SQLite, switch to a directory that has read and write permissions. 15 16```bash 17# Open the hdc CLI. 18c:/users/zzz>hdc shell 19# cd data 20# mkdir temp 21# cd temp 22# SQLite3 23SQLiteversion 3.40.1 2022-12-28 14:03:47 24Enter ".help" for usage hints. 25Connected to a transient in-memory database. 26Use ".open FILENAME" to reopen on a persistent database. 27``` 28 29If you attempt to create a database or table in a path that does not have read and write permissions, the system throws a permission error. 30 31Run the **CREATE TABLE** command in the SQL statement to create a table. 32 33```bash 34# Open the hdc CLI. 35c:/users/zzz>hdc shell 36# Open or create a database. 37#SQLite3 a.db 38SQLiteversion 3.40.1 392022-12-28 14:03:47 40Enter ".help" for usage hints. 41# Create a table. 42SQLite>create table t1(a int); 43Error:unable to open 44``` 45 46When the preceding command is run, SQLite attempts to open or create the file. However, due to the lack of permissions, an error is reported: 47 48```bash 49Error:unable to open 50``` 51 52## Command List 53 54The following table lists the commands supported by the SQLite debugging tool. 55 56| Command | Description | 57| ------------------ | --------------------------------------- | 58| .help | Displays all available SQLite commands and brief descriptions. | 59| .open *database name*| Opens or creates a database. | 60| .databases | Lists all connected databases. | 61| .tables | Lists all tables in a database. | 62| .show | Displays the default settings of the SQLite command prompt. | 63| .schema | Obtains complete information about a table. | 64| .quit or .exit| Exits the SQLite CLI. | 65| .schema [TABLE] | Displays the SQL statement for creating a table (or the structure of all tables).| 66 67## Notes 68 69- Semicolons are not required in SQLite commands. Unlike SQL statements, SQLite commands can be run by pressing the **Enter** key. 70- Ensure that there is no space between the **SQLite>** prompt and the command. Otherwise, the command cannot be run. 71 72## Command Usage and Examples 73 74### Displaying All Commands and Descriptions 75 76```SQLite 77SQLite>.help 78``` 79### Creating or Opening an Existing Database 80 81- Open an existing database. 82 83 If the database file already exists, you can run the **.open** command to open it. 84 85 ```SQLite 86 SQLite3 # Enter the SQLite shell. 87 .open mydb.db # Open the existing database in the shell. 88 ``` 89 90 Alternatively, specify the file path in the command line. 91 92 ```SQLite 93 SQLite3 mydb.db # Open the database directly. 94 ``` 95 96 97- Create and open a database. 98 99 If the specified database file does not exist, SQLite automatically creates one. 100 101 ```SQLite 102 SQLite3 # Enter the SQLite shell. 103 .open newdb.db # Create and open a database in the shell. 104 ``` 105 106 Alternatively, create a database directly through a command. 107 108 ```SQLite 109 SQLite3 newdb.db # Create and open a database directly. 110 ``` 111 112### Creating a Table 113 114You can run the SQL statement **create table** to create a **COMPANY** table, set **ID** as the primary key, and set **NOT NULL** to ensure that the fields cannot be empty when records are created in the table. 115 116```SQL 117SQLite>create table COMPANY( 118 ID INT PRIMARY KEY NOT NULL, 119 NAME TEXT NOT NULL, 120 AGE INT NOT NULL, 121 ADDRESS CHAR(50), 122 SALARY REAL 123); 124``` 125 126### Querying a Table 127 128Run the SQLite command **.tables** to check whether the table is created. This command is used to list all tables in a database. 129 130```SQLite 131SQLite>.tables 132COMPANY 133``` 134 135After the **.tables** command is run, the created **COMPANY** table is listed. 136 137Run the SQLite command **.schema** to obtain the complete information about the table. 138 139```SQLite 140SQLite>.schema COMPANY 141CREATE TABLE COMPANY( 142 ID INT PRIMARY KEY NOT NULL, 143 NAME TEXT NOT NULL, 144 AGE INT NOT NULL, 145 ADDRESS CHAR(50), 146 SALARY REAL 147); 148``` 149 150### Deleting a Table 151 152The SQL statement **DROP TABLE** is used to delete a table definition and all associated data, indexes, triggers, constraints, and permission specifications. 153 154For example, if the **COMPANY** table already exists in the database, you can run the following command to delete it: 155 156```SQLite 157SQLite>DROP TABLE COMPANY; 158``` 159 160After the command is run, the **COMPANY** table cannot be queried using the **.tables** command. If the command output is empty, the table is deleted successfully. 161 162```SQLite 163SQLite>.tables 164``` 165 166### Inserting Data 167 168- Enter the following SQL statement under the **SQLite>** prompt to insert a single data record: 169 170 ```sql 171 INSERT INTO COMPANY(name, age,) VALUES ('Zhang San', 25); 172 ``` 173 174 Run **SELECT * FROM *Table name*** to obtain the complete information about the table. 175 176 ```sql 177 SQLite> SELECT * FROM COMPANY; 178 1|Zhang San|28|Chaoyang District, Beijing|20000.5 179 ``` 180 181 182- Run **INSERT INTO COMPANY (field) values ()** to insert multiple data records. 183 184 ```sql 185 SQLite> INSERT INTOCOMPANY(ID, NAME, AGE, ADDRESS, SALARY) 186 ...> VALUES 187 ...> (3, 'Wang Wu', 25, 'Tianhe District, Guangzhou', 18000.75), 188 ...> (4, 'Zhao Liu', 40, 'Nanshan District, Shenzhen', 30000.25); 189 ``` 190 191 Run **SELECT * FROM *Table name*** to obtain the complete information about the table. 192 193 ```sql 194 SQLite> SELECT * FROM COMPANY; 195 1|Zhang San|28|Chaoyang District, Beijing|20000.5 196 3|Wang Wu|25|Tianhe District, Guangzhou|18000.75 197 4|Zhao Liu|40|Nanshan District, Shenzhen|30000.25 198 ``` 199 200### Querying Data 201 202Run the **SELECT** command in either of the following manners to query data: 203 204- Run **SELECT * FROM *Table name*** to query all data. 205 206 ```sql 207 SQLite> SELECT * FROM COMPANY; 208 1|Zhang San|28|Chaoyang District, Beijing|20000.5 209 3|Wang Wu|25|Tianhe District, Guangzhou|18000.75 210 4|Zhao Liu|40|Nanshan District, Shenzhen|30000.25 211 ``` 212 213- Run **SELECT *specified field*, *specified field* FROM *Table name*** to query data of specified fields. 214 215 ```sql 216 SQLite> SELECT name, age FROM COMPANY; 217 Zhang San|28 218 Wang Wu|25 219 Zhao Liu|40 220 ``` 221 222- Run **SELECT * FROM *Table name* WHERE *Specified field* > 30;** to query the table by specified condition. 223 224 ```sql 225 SQLite> SELECT * FROM COMPANY WHERE age > 30; 226 4|Zhao Liu|40|Nanshan District, Shenzhen|30000.25 227 ``` 228 229- Run **SELECT * FROM *Table name* ORDER BY *Specified field* ASC;** to query data by specified field order. 230 231 ```sql 232 SQLite>SELECT * FROM COMPANY ORDER BY age ASC; 233 3|Wang Wu|25|Tianhe District, Guangzhou|18000.75 234 1|Zhang San|28|Chaoyang District, Beijing|20000.5 235 4|Zhao Liu|40|Nanshan District, Shenzhen|30000.25 236 ``` 237 238### Updating Data 239 240Run **UPDATE *Table name* SET age = 31 WHERE name = 'Zhang San';** after the **SQLite>** prompt to update a single data record. 241 242```sql 243SQLite>UPDATE COMPANY SET age = 31 WHERE name = 'Zhang San'; 244SQLite>SELECT * FROM COMPANY; 2451|Zhang San|31|Chaoyang District, Beijing|20000.5 2463|Wang Wu|25|Tianhe District, Guangzhou|18000.75 2474|Zhao Liu|40|Nanshan District, Shenzhen|30000.25 248``` 249 250### Deleting Data 251 252Run **DELETE FROM *Table name* WHERE name = 'Wang Wu';** after the **SQLite>** prompt to delete data. 253 254```sql 255SQLite> DELETE FROM COMPANY WHERE name = 'Wang Wu'; 256SQLite> SELECT * FROM COMPANY; 2571|Zhang San|28|Chaoyang District, Beijing|20000.5 2584|Zhao Liu|40|Nanshan District, Shenzhen|30000.25 259``` 260 261## References 262 263The [SQLite website](https://SQLite.org/docs.html) provides rich sample code, including creating a database, creating a table, inserting data, and querying data. 264 265## FAQs 266 267### What Should I Do If the Query Result Is Empty? 268 269In actual database operations, the query result may be empty because no data exists in the table. 270 271To avoid this, you should ensure that related data is inserted to the table before running the query command. 272