Basic usage guidelines for using SQL statements to manipulate databases in IOS development _ios

Examples of SQL code applications

One, the use of code to bulk Add (import) data into the database

1. Execute SQL statement to add a piece of information to the database

An SQL statement that inserts a single piece of data:

After clicking Run EXECUTE statement, refresh the data

2. Using code to bulk add multiple rows of data in an iOS project sample

code example:

Copy Code code as follows:

//
Main.m
01-Add multiple rows of data to the database
//
Created by Apple on 14-7-26.
Copyright (c) 2014 wendingding. All rights reserved.
//

#import <Foundation/Foundation.h>

int main (int argc, const char * argv[])
{

@autoreleasepool {
Nsarray *names=@[@ "Zhang Yi", @ "Zhang Yi", @ "John", @ "Zhang four"];
Nsmutablestring *sql=[nsmutablestring string];

for (int i=0; i<200; i++) {
int id=i+1;
The warning here is for unsigned type conversions
NSString *name=names[arc4random_uniform (Names.count)];
Name=[name stringbyappendingformat:@ "-%d", Arc4random_uniform (200)];
Generates random numbers with a range of 20-centered fluctuation 10
int Age=arc4random_uniform (20) +10;
[SQL appendformat:@ INSERT into t_student (id,name,age) VALUES (%d, '%@ ',%d); \ n ", Id,name,age];
}
Write SQL to File
[SQL writetofile:@ "/users/apple/desk/students.sql" Atomically:yes encoding:nsutf8stringencoding Error:nil];
NSLog (@ "\n%@", SQL);
}
return 0;
}

Function: Generate 200 corresponding SQL INSERT statements

Printing results are:

Using a text editor, open the generated SQL file.

You can copy these SQL statements to navicat for execution, or you can execute the entire file directly.

Create a table in the database:

Select Execute SQL file:

After the execution is complete, click Cancel.

Refresh the database to see the 200 inserted data

Second, pagination simple demo

Description

Use limit to accurately 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, and then take 8 records

Limit is often used to do paging queries, such as 5 data per page fixed, so you should take the data

1th page: Limit 0, 5

2nd page: Limit 5, 5

3rd page: Limit 10, 5

...

Nth page: Limit 5* (n-1), 5

The function of the following statement

SELECT * from t_student limit 7;

Equivalent to select * from T_student limit 0, 7; to take the first 7 records

Third, to supplement

1. About foreign KEY constraints (establish a link between two tables)

The first approach: you can create a new relational table, so that the previous two tables (Class table and student table to establish a corresponding link), but this approach is redundant, there is no need

The second approach: using FOREIGN KEY constraints

One-to-one, One-to-many, many-to-many relationships. When two tables are connected, how do I set a foreign key (in which table?) ）

2. About table joins

Subquery: Ask to inquire all types of dishes with Cantonese cuisine.

The results of the query are:

Connection query:

The results of the query are:

PS: Commonly used SQL statement collation
One, SQL statements

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

1. What is SQL

SQL (Structured Query Language): Structured Query Language

SQL is a language that defines and operates on data in a relational database

Concise SQL language, simple grammar, easy to learn and easy to use

2. What is SQL statement

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

In the process of running the program, you must use the SQL statement to manipulate the data in the database.

Characteristics of 3.SQL Statements

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

Each statement must be 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, etc.

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

II. Types of SQL statements

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

Include actions such as create and drop

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

2. Data operation statement (Dml:data manipulation Language)

Include INSERT, UPDATE, delete operations

The above 3 actions are used to add, modify, and delete data from 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 frequently used operation for DQL (and all SQL)

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

Third, the basic operation

1. Create a table

CREATE TABLE 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, ...);

Example

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

2. Field type

SQLite divides data into the following storage types:

Integer: Integer value

Real: Floating-point values

Text: literal string

BLOB: Binary data (such as files)

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

When you build a table, you declare what type or not, and that means that the creation statement can write:

CREATE table T_student (name, age);

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

3. Delete Table

Format

The drop table table name;

drop table if exists table 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 example above 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 example above will erase all the records in the T_student table.

7. Conditional statement

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

Common formats for conditional statements

where field = a value; Can not use two =

The Where field is a value; is equivalent to =

The Where field!= a value;

The Where field is not a value; Is isn't the equivalent of!=

Where field > a value;

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

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

Example

The T_student table is older than 10 and the name is not equal to Jack's record, The age is changed to 5

Update t_student Set age = 5 where age > Name!= ' Jack ';

Delete records in the T_student table that are less than or equal to 10 or older than 30

Delete from t_student where age <= > 30;

Guess the role of the following statement

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

T_student the name in the table to the record of Jack, the value of the score field is changed to the value of the age field

8.DQL statement

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. Alias

Format (both fields and tables can be aliased)

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 the name a nickname called MyName, an alias called Myage for Age

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

Give the T_student table an individual name called S, using 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, by age desc; Descending

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

You can also sort with more than one field

SELECT * FROM T_student with age ASC, height desc;

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

12.limit

Use limit to accurately 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, and then take 8 records

Limit is often used to do paging queries, such as 5 data per page fixed, so you should take the data

1th page: Limit 0, 5

2nd page: Limit 5, 5

3rd page: Limit 10, 5

...

Nth page: Limit 5* (n-1), 5

SELECT * from t_student limit 7; This statement acts as a select * from T_student limit 0, 7; takes the first 7 records

Iv. constraints

1. Simple constraints

When you build a table, you can set some constraints on a particular field, 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

(Suggestion: 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 defaults to 1

2. PRIMARY KEY constraint

(1) Simple description

If the name and age two fields are in the T_student table, and some records have the same values for the name and Time fields, then there is no way to distinguish between the data and the database records, which makes it inconvenient to manage the data

A good database programming specification should ensure the uniqueness of each record, thus increasing the 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?

The primary key (Primary key, short PK) is used to uniquely identify a record

For example, t_student can add an ID field as a primary key, equivalent to a person's identity card

A primary key can be a field or multiple fields

(3) Design principle of primary key

The primary key should be meaningless to the user

Never update a primary key

The primary key should not contain dynamically changing data

Primary keys should be automatically generated by the computer

(4) The 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);

ID of integer type as primary key of T_student table

Primary key Fields

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

The primary key field defaults to include NOT NULL and unique two constraints

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

The use of FOREIGN key constraints can be used to establish the relationship between tables

A foreign key is typically a field in a table that references 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 with the class_id field in the T_student table

4. Table Join query

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

Types of table joins

Inner joins: INNER JOIN or join (Displays records with full field values for both left and right tables)

Left OUTER join: Left OUTER JOIN (guarantees the integrity of the left-hand table data)

Example

Check 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 ';