Sqlite3 common Commands and SQL basic statements

Sqlite3

Creation and deletion of the database:

$ sqlite3 stu.db

Sqlite3 is a small database, so directly with a single file as a database, the command line is added to the parameter is to open and manipulate the database, "Stu.db" is the file name of the database. If this file is not available, this file is created to hold the data that will be manipulated. If there are no parameters, no database is opened. Because Sqlite3 stores the database as a single file, the database does not exist if you delete the file directly.

Sqlite3 Common commands:

sqlite> .helpsqlite> .quitsqlite> .exitsqlite> .savesqlite>. Open

When the database is open, the command prompt becomes "sqlite>". Sqlite3 commands start with a point, such as the most basic commands, database open, save, exit, help. where ". Open" ". Save" can take parameters, which is the file name of the database that will be opened or saved. ". Help" can list all the commands. ". Quit" ". Save" exits the database.

Sqlite> .databasesqlite> .tablesqlite>. Schema student

". Database" To view the current database name, which is the full path name of the file. ". Table" to view all the relational tables for the current database.  ". Schema" is the structure of the view relationship table, which consists of which fields, where "student" is the table name.

Creation and deletion of relational tables:

Sqlite> CREATE TABLE student (id int, name text, score int);

"CREATE TABLE" is the keyword, "student" is the table name, "id" "Name" "Score" is the segment name, "int" "text" "int" is the data type of the corresponding segment. "CREATE TABLE" is a SQL standard statement, not a built-in command for SQLite so don't add a bit, but each SQL statement must end with a semicolon, each field is separated by commas, and all fields are enclosed in parentheses.

SQLite common data types, int text date time, type can also be followed by the number of digits, but basically useless.

int integer smallint tinyint	Integer
Decimal numeric	Real
char varchar text	String
Date Time	Date Time

sqlite> drop table student;

The "drop table" keyword is used to delete the relationship table, and "student" is the table name.

Insertion and deletion of records:

sqlite> INSERT into student values (, ' James ', 99);

"INSERT INTO" is the keyword, "student" is the table name, and "values" is followed by the values of each field.

sqlite> INSERT into student (ID, name) VALUES (' James ');

If not all fields have data, add field names after the table name, and then fill in the values of the corresponding fields after "values."

sqlite> Delete from student where id=1;

"Delete from" is the keyword, "student" is the table name, "where" is the keyword, followed by the condition, this statement is to delete the "id" value is "1" record.

Commonly used judgment operators, such as the following table

"="	Equals
"<>"	Not equal to
"<"	Less than
">"	Greater than
">="	Greater than or equal to
"<="	Less than or equal to

"Where" followed by a judgment statement can also have multiple, separated by commas, is the relationship with the condition that satisfies.

Find and modify a record:

Sqlite> Select ID, name from student;

"Select" is the keyword, "id" "Name" is the field name, "from" is the keyword, "student" is the table name, this statement is the table "student" in the "id" "Name" two columns display all.

Sqlite> select * from student;

"*" represents all columns, and this statement displays all columns in the "Student" table.

Sqlite> SELECT * FROM student where name= "James";

The "select" keyword can be paired with the "where" keyword to filter out the records for the specified criteria from the table. This statement prints out all the fields of the record that the value of the field "name" is "James".

Sqlite> SELECT * FROM student the order by ID;

The "ORDER BY" keyword is to sort the filtered records, followed by the sort, which finds all the records from the "Student" table, displays all the columns, and sorts them in the "id" field.

Sqlite> Update student set name= "Luck" where id=1;

"Update" is the keyword, "student" is the table name, the "set" keyword followed by the field to be modified, and if there is no filter criteria, the "Name" field value of all records is "luck".

Sqlite3 common Commands and SQL basic statements