Research on iOS learning Note 14-sqlite database

1, CREATE database: Sqlite3 database name. db

2, display database:. databases

3, Creating Tables: Create table table name (Field name 1 field type, Field Name 2 field type, ... ）；

4. Insert data: INSERT into table name (Field 1, Field 2 ...) Values (the Value of field 1, the value of field 2 ...) ）；

5, find:

1) Select *from Table Name--Displays the contents of all table names

2) Select*from table name where query criteria

3) Select Field Name 1, field name 3 from table name

6, update

Update table name set field name = new value WHERE condition

7, delete

1) delect from table name

2) Delete from table name where condition statement

Create a database

Sqlite3 dsn.db

Show all databases

. Database

Create a table

CREATE TABLE Dsntable

Create the contents of a table

CREATE table dsntable (ID integer primary key autoincrement,value name);

Show All Tables

. Table

Show content

. Schema MyTable

Find all fields

Select

One, SQL statements

If you want to manipulate the data in the database while the program is running, you must first learn to use SQL statements

1. What is SQL

SQL (Structured Query Language): Structured Query Language

SQL is a language for defining and manipulating data in a relational database

SQL language concise, simple syntax, studious and useful

2. What is a SQL statement

The sentence \ code written in the SQL language is the SQL statement

In the process of running the program, you must use SQL statements to manipulate (add and remove, CRUD) data in the database

Features of the 3.SQL statement

case insensitive (e.g. the database thinks the user and user are the same)

Each statement must have a semicolon; End

The commonly used keywords in 4.SQL are

Select, insert, UPDATE, Delete, from, create, where, desc, order, by, Group, table, Alter, view, index, and so on

You can not use keywords to name tables, fields in a database

II. Types of SQL statements

1. Data definition statements (ddl:data definition Language)

Includes operations such as Create and drop

Create a new table or delete a table in the database (CREATE table or drop tables)

2. Data manipulation statements (Dml:data manipulation Language)

Include INSERT, update, delete, and more

The above 3 actions are used to add, modify, and delete data in a table, respectively

3. Data query Statement (dql:data query Language)

can be used to query for data in a table

Keyword SELECT is the most used operation for DQL (and all SQL)

Other dql commonly used keywords are where,order by,group by and having

Three, the basic operation

1. Create a table

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

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

Example

CREATE table t_student (ID integer, Name text, age integer, score real);

2. Field type

SQLite divides the data into the following types of storage:

Integer: Integer value

Real: floating-point value

Text: literal string

BLOB: Binary data (such as files)

Note: In fact, SQLite is untyped and can store string literals (except primary keys), even if declared as an integer type

It is possible to declare what type or not to declare a type when building a table, which means that the statement can be written like this:

CREATE table T_student (name, age);

Tip: In order to maintain good programming specifications and facilitate communication between programmers, it is best to add the specific type of each field when writing a table statement

3. By deleting the table

Format

drop table name;

drop table if exists name;

Example

drop table t_student;

4. Inserting data (insert)

Format

Insert into table name (Field 1, Field 2, ...) VALUES (Value of field 1, Value of field 2, ...);

Example

Insert into T_student (name, age) VALUES (' MJ ', 10);

Attention

The string contents in the database should be enclosed in single quotes

5. Updating data (update)

Format

Update table name set field 1 = value of field 1, field 2 = value of field 2, ...;

Example

Update t_student Set name = ' Jack ', age = 20;

Attention

The previous example changes the name of all records in the T_student table to Jack,age to 20

6. Deleting data (delete)

Format

Delete from table name;

Example

Delete from t_student;

Attention

The above example deletes all the records in the T_student table

7. Conditional statements

If you only want to update or delete some fixed records, you must add some conditions after the DML statement

Common formats for conditional statements

where field = a value; cannot be used for two x =

The Where field is a value; is equals =

where field! = a value;

The Where field is not a value; is isn't equivalent to! =

Where field > a value;

where field 1 = a value and field 2 > a value; and equivalent to the && in C language

where field 1 = a value or field 2 = a value; or equivalent to the C language | |

Example

Change the age of the T_student table to more than 10 and the name does not equal Jack's record to 5

Update t_student Set age = 5 where age > Ten and name! = ' Jack ';

Delete records with age less than or equal to 10 or older than 30 in the T_student table

Delete from T_student where is age <= or age > 30;

Guess the effect of the following statement

Update T_student Set score = age WHERE name = ' Jack ';

Change the name in the T_student table to the Jack record, and the value of the score field to the value of the age field

8.DQL statements

Format

Select field 1, Field 2, ... from table name;

SELECT * from table name; Query all fields

Example

Select name, age from T_student;

SELECT * from T_student;

SELECT * from T_student where age > 10; Conditional query

9. Aliases

Format (fields and tables can be aliases)

Select field 1 alias, Field 2 alias, ... from table name alias;

Select field 1 alias, Field 2 as Alias, ... from table name as alias;

Select Alias. Field 1, alias. Field 2, ... from table name alias;

Example

Select name MyName, age myage from T_student;

Give name a nickname called MyName, an alias called Myage for Age.

Select S.name, s.age from T_student s;

Give T_student an individual name called S and use s to refer to the fields in the table

10. Calculate the number of records

Format

Select count (field) from table name;

Select COUNT (*) from table name;

Example

Select count (age) from T_student;

Select COUNT (*) from T_student where score >= 60;

11. Sorting

The results of the query can be sorted with order by

SELECT * from T_student the order by field;

SELECT * from T_student order by age;

The default is to sort in ascending order (from small to large), or to descending (from large to small)

SELECT * from T_student order BY age Desc; Descending

SELECT * from T_student order by age ASC; Ascending (default)

You can also sort by multiple fields

SELECT * from T_student ORDER BY age ASC, height desc;

Sort by age (ascending), and age equal by height (descending)

12.limit

Use limit to precisely control the number of query results, such as querying only 10 data at a time

Format

SELECT * from table name limit value 1, value 2;

Example

SELECT * from T_student limit 4, 8;

Can be understood as: Skip the first 4 statements, then fetch 8 records

The limit is often used for paging queries, such as 5 data per page, so you should take the data

1th page: Limit 0, 5

2nd page: Limit 5, 5

3rd page: Limit 10, 5

...

Page N: Limit 5* (n-1), 5

SELECT * from t_student limit 7; This statement is equivalent to select * from T_student limit 0, 7; to take the first 7 records

Iv. constraints

1. Simple constraints

When you build a table, you can set some constraints on specific fields, and the common constraints are

Not NULL: The value of the specified field cannot be null

Unique: The value of the specified field must be unique

Default: Specify a value for the field

(recommendation: As far as possible to set strict constraints on the field to ensure the normative data)

Example

CREATE table t_student (ID integer, name text NOT null-unique, age integer NOT null default 1);

The name field cannot be null, and the unique

The age field cannot be null, and the default is 1

2. PRIMARY KEY constraints

(1) Brief description

If the T_student table is in the name and age two fields, and some records have the same name and the value of the Date field, then the data cannot be distinguished, resulting in a database that is not unique, which makes it inconvenient to manage the data

A good database programming specification should ensure uniqueness of each record, adding a PRIMARY KEY constraint

In other words, each table must have a primary key to identify the uniqueness of the record

(2) What is a primary key?

Primary KEY (Primary key, referred to as PK) uniquely identifies a record

For example, t_student can add an ID field as the primary key, which is equivalent to a person's ID

The primary key can be a field or multiple fields

(3) Design principle of primary key

The primary key should not be meaningful to the user

Never update the primary key

The primary key should not contain dynamically changing data

The primary key should be automatically generated by the computer

(4) Declaration of the primary key

Declare a primary key with primary key when creating a table

CREATE table t_student (ID integer primary key, Name text, age integer);

The ID of the integer type as the primary key for the T_student table

Primary key field

As long as primary key is declared, it is a primary key field

The primary key field contains not NULL and a unique two constraint by default

Description: If you want the primary key to grow automatically (must be an integer type), you should increase the AutoIncrement

CREATE table t_student (ID integer primary key autoincrement, name text, age integer);

3. FOREIGN KEY constraints

Using FOREIGN KEY constraints can be used to establish a table-to-table connection

The general case of a foreign key is a field in a table that refers to the primary key field of another table

Create a new foreign key

CREATE table t_student (ID integer primary key autoincrement, name text, age integer, class_id integer, constraint Fk_stud Ent_class foreign KEY (class_id) references T_class (ID));

There is a foreign key called fk_t_student_class_id_t_class_id in the T_student table.

The role of this foreign key is to refer to the ID field of the T_class table using the class_id field in the T_student table

4. Table Connection Query

Table Join query: You need to federate multiple tables to find the data you want

Types of table joins

Inner joins: INNER JOIN or join (displays records with a full field value in the left and right tables)

Left outer connection: Left OUTER join (guarantees the integrity of data on the table)

Example

Find all students in class 0316iOS

Select S.name,s.age from T_student s, t_class c where s.class_id = c.id and c.name = ' 0316iOS ';

Research on iOS learning Note 14-sqlite database