SQLite basic Syntax

Command line Operation SQLite

Create a table

CREATE TABLE table name (Field Name field type, field Name field type);

CREATE TABLE info (ID int,name varchar (20));

Increase:

Insert into table name values (value to add ...);

When the data type of the field being added is of type int, the direct write value

If the field type you are adding is a varchar type and a date type, use single quotation marks to wrap the value together

INSERT into info VALUES (1, ' Zhangsan ', ' 110 ');

INSERT into test (name) VALUES (' Lisi ');

When inserting records, you can add (column names) after the name of the table to insert data into the specified column

Change:

Update table name SET field Name 1 = value 1, field Name 2 = value 2 where condition;

Update info set phone= ' 12345 ' where name= ' Zhangsan ';

By deleting:

Delete from table name where condition removes all data from a table without a Where condition

Delete from info where ID > 1;

Delete from info; Delete all data in a table

Check:

Select Field Name (*) as alias from table name where condition

SELECT * from info; Querying the contents of all fields

Select Name,phone from info where id = 1;

Remove duplicate records in a table (affects only the results of the query and does not modify the records in the table)

SELECT DISTINCT * from table name;

Select distinct name from info; Show only names to remove duplicate records

Partial query: Desc Descending limit query number, offset coordinate

Select Number,mode from Info ORDER by _id desc limit? Offset?

Read contacts contacts, two table nesting

Select location from Data1 where id= (select Outkey from Data2 where Num=1)

Db.execsql ("CREATE table info (_id integer primary key autoincreament,name varchar[20], phone varchar [20])")

Db.execsql ("ALTER TABLE info add age integer") inserts a column

5 using SQL to manipulate a database

Create a database

Create database name;

Show all databases

show databases;

Use a database

The name of the use database;

See which database is currently in use

Select Database ();

Deleting a database

drop database name;

6 using SQL to manipulate a table

Create a table

CREATE TABLE table name (Field Name field type, field Name field type);

CREATE TABLE info (ID int,name varchar (20));

View table Structure

DESC table name;

DESC info;

See which tables are in the current database

Show tables;

Delete a table

drop table name;

drop table info;

Modify table structure Add a column

ALTER TABLE name Add field Name field type;

ALTER TABLE info add phone varchar (20);

Data types supported by SQL

String type

varchar variable-length string varchar (20) 13888888888; How much is allocated within the scope of the application space?

Char char (20);

Big Data types

Blob

Text is larger than

Numeric type

TINYINT byte

SMALLINT Short

int int

BIGINT Long

float float

Double Double

Logic

BIT Boolean

Date type

Date Dates

Time

DateTime Date and Time

TIMESTAMP time Stamp

7 using SQL to manipulate records in a table crud☆☆☆☆☆☆☆☆☆☆

1. Inserting records

Insert into table name values (value to add ...);

When the data type of the field being added is of type int, the direct write value

If the field type you are adding is a varchar type and a date type, use single quotation marks to wrap the value together

INSERT into info VALUES (1, ' Zhangsan ', ' 110 ');

INSERT into test (name) VALUES (' Lisi ');

When inserting records, you can add (column names) after the name of the table to insert data into the specified column

2. Modify the Record

Update table name SET field Name 1 = value 1, field Name 2 = value 2 where condition;

Update info set phone= ' 12345 ' where name= ' Zhangsan ';

3. Deleting records

Delete from table name where condition removes all data from a table without a Where condition

Delete from info where id = 1;

Delete from info; Delete all data in a table

4. Query records

Select Field Name (*) as alias from table name where condition

SELECT * from info; Querying the contents of all fields

Select Name,phone from info where id = 1;

5. Remove duplicate records in the table (affects only the results of the query and does not modify the records in the table)

SELECT DISTINCT * from table name;

Select distinct name from info; Show only names to remove duplicate records

Constraints of 6.MySQL

A. A non-null constraint NOT NULL

* Indicates that the data cannot be empty

B. Uniqueness constraint unique

* Records in a table cannot be duplicated

C. PRIMARY KEY constraint primary key

* Indicates non-empty, uniqueness

D Automatic Growth auto_increment

CREATE TABLE test (ID int primary KEY auto_increment,name varchar (20));

Use of 8where conditions

The. WHERE clause uses the

A. operator <, >=, <=

B. In within range

SELECT * FROM student where Android in (70,90); Note In does not specify the start and end range but rather the selection in the data in ()

If you want to specify the starting and ending ranges to use the and condition

C. and conditions satisfy both

SELECT * FROM student where android>=70 and android<=90;

D. Like fuzzy query

% = placeholder (wildcard)% replaces several words

SELECT * from student where name is like '%zha% ';

Query the name of the table containing Zha records

Sort

Order BY field name ASC Ascending

Order BY field name desc Descending

Default ascending if you want to add desc to descending order

SELECT * FROM student order by 中文版;

9 aggregation function, group query &limit keyword

1.count () function

Select COUNT (*) from ... where ....

2.sum () function

Select sum (to be summed field) from ... where ....

3. AVG () function

Select AVG (field name to calculate average) from ...

4. Max () function

Select Max (field) from ...

5. Min () function

Select min (field) from ...

Grouping Query GROUP BY

Select Name,sum (Price) from the orders group by name;

Group by name to find the total price of each item

Group query If you add a condition that cannot use where and you want to use the having

Select Name,sum (price) from orders group by name have sum (price) >5000;

Limit

SELECT * from Orders limit 2; Show 2 from the beginning of the first article

SELECT * FROM Orders limit 2, 3; Show 3 records starting from 2nd + 1;

The Limit keyword enables paged query of data (only part of the data is queried)

SQLite basic Syntax