SQL statements in iOS programming

First, SQL statements

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

1. What is SQL

Structured Query Language (SQL) : Structured query language

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

SQL language concise, simple syntax, easy to learn and easy to use

2. What are SQL statements

The use of SQL language prepared by the sentence \ code, is the SQL statement

In the process of running the program, to operate (add, delete, change, search, CRUD) in the database data, must use SQL statements

3. Characteristics of SQL statements

Case insensitive (for example, the database thinks user and user are the same)

Each statement must be preceded by a semicolon; At the end

4. Common keywords in SQL are

Select, INSERT, update, delete, FROM, CREATE, WHERE, desc, ORDER, BY, group, table, ALTER, view, index, etc

You cannot use keywords to name tables and fields in a database

Second, types of SQL statements

1. DDL: Data Definition Language

The operations include create and drop

Create a new table or drop a table from a database

2. Data Manipulation Language (DML)

The operations include INSERT, UPDATE, and delete

The above three operations are used to add, modify, and delete data in a table respectively

3. DQL: Data Query Language

Can be used for queries to get data from tables

The keyword SELECT is the most commonly used operation in DQL (and all SQL)

Other commonly used DQL keywords are WHERE, Order BY, Group BY, and HAVING

Three, basic operation

1. Create the tables

Create table name (field name 1, field type 1, field name 2, field type 2…) ;

Create table if not exists Table name (field name 1 field type 1, field name 2 Field type 2,…) ;

The sample

create table t_student (id integer, name text, age integer, score real) ;

2. Field type

SQLite divides data into the following storage types:

Integer: indicates an integer value

Real: floating point value

Text: indicates a text string

Blob: binary data (such as files)

Note: SQLite is actually untyped, and can store string text even if declared as integer (except for primary keys).

When creating a table, you can either declare a type or not declare a type. This means that you can create a statement like this:

create table t_student(name, age);

Tip: In order to maintain good programming practices and facilitate communication between programmers, it is best to include the specific type of each field when writing table building sentences

3. Delete table

format

Drop table table name;

Drop table if exists Specifies the name of the table.

The sample

drop table t_student ;

4. Insert data

format

Insert into table name (select * from table 1, select * from table 2) Values (field 1 value, field 2 value,…) ;

The sample

Insert into t_student (name, age) values (‘ mj ‘, 10);

Pay attention to

The contents of strings in the database should be enclosed in single quotes

5. Update data

format

Select * from table_name where table_name = 1 and table_name = 2; ;

The sample

Update t_student set name = ‘jack’, age = 20;

Pay attention to

The above example would change the name of all records in the T_student table to Jack and the age to 20

6. delete data (delete)

format

Delete from table name;

The sample

delete from t_student ;

Pay attention to

The above example deletes all records from the T_student table

7. Conditional statements

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

A common format for conditional statements

Where field = some value; // Do not use two =

The WHERE field is a value; // is equivalent to =

Where the field! = a value;

Where field is not a value; // Is not equal to! =

Where field > some value;

Where field 1 = 2 > a value and field a value; // And is the equivalent of && in C

Where 1 = a value or field 2 = a value; / / or equivalent to the C language in the | |

The sample

Select * from T_student where age > 10 and name not equal to Jack where age = 5

update t_student set age = 5 where age > 10 and name != ‘jack’ ;

Delete from T_student where age < 10 or age > 30

delete from t_student where age <= 10 or age > 30 ;

Guess what the following statement does

Update t_student set score = age where name = ‘jack’;

Alter table t_student alter table score = AGE alter table score = AGE

8. DQL statement

format

Select 1, 2… From the name of the table;

Select * from table_name; // Query all fields

The sample

select name, age from t_student ;

select * from t_student ;

select * from t_student where age > 10 ; // Conditional query

9. Names

Format (fields and tables can be aliased)

Select field 1 alias, field 2 alias,… From table name alias;

Select 1 as alias, 2 as alias,… From 表名 as alias;

Select the alias. Field 1, alias. Field 2,… From table name alias;

The sample

select name myname, age myage from t_student ;

Give name an alias called myName and age an alias called myage

select s.name, s.age from t_student s ;

Give table T_student a unique name s and use s to refer to fields in the table

10. Count records

format

Select count (*) from table_name;

Select count (*) from table_name;

The sample

select count (age) from t_student ;

select count ( * ) from t_student where score >= 60;

11. The sorting

The results of the query can be sorted by order by

Select * from t_student order by;

select * from t_student order by age ;

The default is to sort in ascending order (from smallest to largest) or descending order (from largest to smallest).

select * from t_student order by age desc ; / / descending

select * from t_student order by age asc ; // Ascending (default)

You can also sort with multiple fields

select * from t_student order by age asc, height desc ;

Sorted by age (ascending), sorted by height (descending)

12.limit

Using limit allows you to precisely control the number of query results, such as 10 at a time

format

Select * from table_name limit 1, 2;

The sample

select * from t_student limit 4, 8 ;

Skip the first four statements and fetch eight records

Limit is often used for paging queries, such as a fixed display of 5 entries per page, so the data should be fetched this way

Page 1: Limit 0, 5

Page 2: Limit 5, 5

Page 3: Limit 10, 5

…

Page n: limit 5*(n-1), 5

select * from t_student limit 7 ; Select * from t_student limit 0, 7; Takes the first seven records

Four, constraints,

1. Simple constraints

When building a table, you can set constraints on specific fields. Common constraints are

Not NULL: Specifies that the value of a field cannot be NULL

Unique: Specifies that the value of a field must be unique

Default: specifies the default value of a field

(Suggestion: try to set strict constraints on fields to ensure the standardization of data)

The sample

create table t_student (id integer, name text not null unique, age integer not null default 1) ;

The name field cannot be null and unique

The age field cannot be null and defaults to 1

2. Primary key constraints

(1) Brief explanation

Select * from t_student; select * from t_student; select * from t_student; select * from t_student; select * from t_student

Good database programming practices should ensure that each record is unique, and to do so, add a primary key constraint

That is, each table must have a primary key that identifies the uniqueness of the record

(2) What is a primary key?

A Primary Key (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 card

The primary key can be a field or multiple fields

(3) Design principles of primary keys

Primary keys should be meaningless to the user

Never update the primary key

Primary keys should not contain dynamically changing data

The primary key should be generated automatically by the computer

(4) Primary key declaration

Declare a primary key when creating a table

create table t_student (id integer primary key, name text, age integer) ;

An ID of type INTEGER is used as the primary key of the T_student table

Primary key field

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

The primary key field contains both not NULL and unique constraints by default

Note: If you want the primary key to grow automatically (it must be of type INTEGER), you should add autoINCREMENT

create table t_student (id integer primary key autoincrement, name text, age integer) ;

3. Foreign key constraints

Foreign key constraints can be used to establish relationships between tables

A foreign key is a field in a table that references a primary key field in 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_student_class foreign key (class_id) references t_class (id));

The T_student table has a foreign key called fk_t_student_class_id_t_class_id

This foreign key is used to reference the ID field of t_class with the class_id field in T_student

4. Table join query

Table join query: you need to join multiple tables to find the desired data

The type of the table join

Inner join: inner join or JOIN (displays records with full field values for both the left and right tables)

Left outer join: left outer join left outer join

The sample

Query all students in iOS class

Select s.name,s.age from t_student s, t_class c where s.class_id = c.id and c.name = ‘iOS’; select s.name,s.age from t_student s, t_class c where s.class_id = c.id and c.name = ‘iOS’;