sqlite-sql-the data record in the table and check the operation

1 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);

Note: INSERT into company (id,name,age,address,salary)

VALUES (1, ' Paul ', +, ' California ', 20000.00);

Text Format data, where the ' xxxx ' single quote is used----add by Cuiz

2 modifying data records--

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;

3 Deleting data records---Data deletion by specified criteria

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;

4 Query data record----field display condition Lookup sort show------------have return result set

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.

5 Indexing-no understanding of what the index is indexed.

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.

6 Deleting a data table or index

DROP TABLE table_name;

Drop index index_name;

sqlite-sql-the data record in the table and check the operation