SQLite basic Syntax

Source: Internet
Author: User
Tags create index sqlite

SQLite, is a lightweight database, is to comply with acid-related database management system, its design goals are embedded, and has been used in many embedded products, it occupies very low resources, in embedded devices, may only need hundreds of K of memory enough.

It can support Windows/linux/unix and other mainstream operating systems, and can be combined with many programming languages, such as Tcl,C #,PHP,Java, and ODBC interface, the same compared to Mysql,PostgreSQL The two open-source world-famous database management system, it processing speed faster than they.

SQLite Common Instructions

1) Set up a data sheet
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, create a simple student information table that contains student information such as number and name:
CREATE TABLE Student_info (stu_no Interger primary key, name text);

The CREATE table if not EXISTS table name (field name 1, field name 2 ...);

2) adding data records
INSERT INTO table_name (field1, field2, ...) VALUES (Val1, val2, ...);
Valx is the value that needs to be stored in the field.
example, add data to the Student information table:
Insert into Student_info (stu_no, name) VALUES (0001, Alex);

3) Modify the data record
UPDATE table_name set FIELD1=VAL1, field2=val2 where expression;
Where is the command used in the SQL statement for conditional judgment, and expression is the judge-
example, modify the data record for student information table number 0001:
Update Student_info set stu_no=0001, name=hence where stu_no=0001;

4) Delete data records
Delete from table_name [where expression];
Clears all data records of the table without judging the condition.
example, delete the data record of Student information table number 0001:
Delete from Student_info where stu_no=0001;

5) Querying data records
Select instruction Basic Format:
Select columns from table_name [where expression];
A query outputs all data records
SELECT * FROM table_name;
b limit the number of output data records
SELECT * FROM table_name limit Val;
C Output data record in ascending order
SELECT * FROM table_name order BY field ASC;
D Descending Output data record
SELECT * FROM table_name order BY field DESC;
E conditional Query
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 Number of query records
Select COUNT (*) from TABLE_NAME;
G Distinguishing Column data
SELECT DISTINCT field from table_name;
Some of the values of the fields may recur, distinct remove duplicates, and each field value in the column is listed individually.

6) Building the index
When you say that a data table has a large number of records, indexing helps speed up finding data tables.
CREATE INDEX index_name on table_name (field);
example, for the Student Table Stu_no field, create an index:
Create INDEX Student_index on student_table (STU_NO);
When Setup is complete, Sqlite3 automatically uses the index when it queries the field.

7) Delete data table or index
DROP TABLE table_name;
Drop index index_name;

SQLite basic Syntax

Related Article

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.