Basic commands for using SQLite3 under Ubuntu _sqlite

Source: Internet
Author: User
Tags create index sqlite

System platform: ubuntu10.04

Brief introduction
Sqlite3 a lightweight database for embedded purposes, this article aims to provide technical documentation for familiar Sqlite3 basic commands.
Note: All operations in this article are performed under Root.

1. Installation Sqlite3
Ubuntu install sqlite3 directly in the terminal to run the command:

#apt-get Install Sqlite3

To view version information:

#sqlite3-version


2, sqlite3 Common command
Create or open Test.db database files in the current directory, and enter the SQLite command terminal, identified by the sqlite> prefix:

#sqlite3 test.db


View database file Information commands (note the character '. ' Before the command) :

Sqlite>.database

To view the creation statements for all tables:

Sqlite>.schema


To view the CREATE statement for a specified table:

Sqlite>.schema table_name


Lists the contents of the table as an SQL statement:
sqlite>.dump table_name
 
Set the separator for displaying information:
sqlite>.separator symble
Example: Set display information to ': ' Separate
sqlite>.separator:
 
set display mode:
sqlite>.mode mode_name
Example: Default is List, Set to column, other modes are available through. Help view mode related content
sqlite>.mode column
 
output assistance:
sqlite>.help
 
Sets the display width for each column:
sqlite>.width Width_value
Example: Set width to 2
sqlite>.width 2
 
lists the configuration for the current display format:
sqlite>.show
 
exit SQLite terminal command:
sqlite >.quit
or
sqlite>.exit


3, Sqlite3 instructions
SQL instruction format: All SQL instructions are annotated with a semicolon (;) end, two minus signs (-).
Such as:

Sqlite>create studen_table (stu_no interger PRIMARY KEY, Name text not NULL, Id Interger UNIQUE, age Interger CHECK (age& GT;6), School text DEFAULT ' xx primary school);

This statement creates a data table that records student information.

3.1 Sqlite3 The type of data stored
Null: Identifies a null value
Interger: Integer type
Real: Floating point numbers
TEXT: String
BLOB: Binary number

3.2 sqlite3 Storage data constraint conditions
SQLite commonly used constraint conditions are as follows:
PRIMARY Key-PRIMARY key:
1 The value of the primary key must be unique to identify each record, such as the student's school number
2 primary key is also an index, through the primary key to find records faster
3 If the primary key is an integer type, the value of the column can grow automatically
Not null-non-empty:
The constraint column record cannot be empty, otherwise the error
Unique-Unique:
In addition to primary keys, the values of data that constrain other columns are unique
Check-condition Check:
Constraint the value of the column must meet the criteria to deposit
Defaults-Default value:
The values in the column data are basically the same, so that the field columns can be set to the default values

3.3 Sqlite3 Common Instructions

1 CREATE TABLE table_name (field1 type1, Field2 type1, ...);
TABLE_NAME is to create a data table name, FIELDX is the field name in the datasheet, and Typex is the field type.
 
example, the establishment of a simple student information table, which contains student numbers and names and other students information: CREATE TABLE Student_info (stu_no Interger primary key, name text);
2 Add data records INSERT INTO table_name (field1, field2, ...) VALUES (Val1, val2, ...);
Valx is the value you want to deposit into the field.
 
For example, add data to the Student information table: Insert into Student_info (stu_no, name) VALUES (0001, Alex);
3 Modify data records update table_name set FIELD1=VAL1, field2=val2 where expression; Where is the command used in the SQL statement for conditional judgment, expression for the judgment expression example, modify the data record of the Student Information table number 0001: Update student_info set stu_no=0001, name=hence where
 
stu_no=0001;
4 Delete the data record delete from table_name [where expression];
Empty all data records of the table without a judgment condition.
 
For example, delete data records with Student information table number 0001: Delete from student_info where stu_no=0001;
5 Query data Record SELECT command basic format: SELECT columns from table_name [where expression];
A query output all data records SELECT * FROM table_name;
b Limit Output data record number SELECT * FROM table_name limit Val;
C Ascending Output data record SELECT * FROM table_name order BY field ASC;
D Descending Output data record SELECT * FROM table_name order BY field DESC; Article EQuery SELECT * FROM table_name where expression;
SELECT * FROM table_name where field in (' Val1 ', ' val2 ', ' val3 ');
SELECT * FROM table_name where field between Val1 and Val2;
F Query Record number SELECT COUNT (*) from TABLE_NAME;
G-Differentiate Column data select DISTINCT field from table_name;
 
The values of some fields may recur, distinct remove duplicates, and list individual field values in the column.
6 indexing when the data table has a large number of records, the index helps speed up the lookup table speed.
CREATE INDEX index_name on table_name (field);
For the Student Table Stu_no field, an index is established: CREATE INDEX Student_index on student_table (STU_NO);
 
When the build is complete, Sqlite3 automatically uses the index when it queries the field.
7 Delete datasheet or index drop table table_name;
 Drop index index_name;

Resources:
http://www.sqlite.com.cn/MySqlite/4/378.Html

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.