iOS Development Database Chapter---"SQLite common statements"

/* Simple constraint */
CREATE TABLE IF not EXISTS t_student (ID integer PRIMARY KEY autoincrement, name TEXT, age INTEGER);
CREATE TABLE IF not EXISTS t_student (ID integer PRIMARY KEY autoincrement, name TEXT NOT NULL, age INTEGER not NULL);
CREATE TABLE IF not EXISTS t_student (ID integer PRIMARY KEY autoincrement, name TEXT UNIQUE, age INTEGER);
CREATE TABLE IF not EXISTS t_student (ID integer PRIMARY KEY autoincrement, name TEXT, age INTEGER DEFAULT 1);

/* Paging */
SELECT * from t_student ORDER by ID ASC LIMIT 30, 10;

/* Sort */
SELECT * from T_student WHERE score > ORDER by age DESC;
SELECT * from T_student WHERE score < The ORDER by age ASC, score DESC;

/* Metering */
SELECT COUNT (*) from t_student WHERE age > 50;

/* Alias */
SELECT name as MyName, age as MyAge, score as Myscore from T_student;
SELECT name MyName, age MyAge, score myscore from T_student;
SELECT s.name myName, S.age myAge, S.score myscore from t_student s WHERE s.age > 50;

/* Query */
SELECT name, age, score from t_student;
SELECT * from T_student;

/* Modify the specified data */
UPDATE t_student SET name = ' MM ' WHERE age = 10;
UPDATE t_student SET name = ' WW ' WHERE age is 7;
UPDATE t_student SET name = ' Xxoo ' WHERE age < 20;
UPDATE t_student SET name = ' nnmm ' WHERE age < score > 10;

/* Delete Data */
DELETE from T_student;

/* Update Data */
UPDATE t_student SET name = ' LNJ ';

/* Insert Data */

INSERT into T_student (age, score, name) VALUES (' + ', ' + ', ' Jonathan ');
INSERT into T_student (name, age) VALUES (' Lee ', ' 28 ');
INSERT into T_student (score) VALUES (100);

/* Insert Data */
INSERT into T_student (name, age, score) VALUES (' Lee ', ' 28 ', 100);



/* Delete Table */
DROP TABLE IF EXISTS t_student;
DROP TABLE t_student;

/* CREATE TABLE */
CREATE TABLE IF not EXISTS t_student (id INTEGER, name TEXT, age, score REAL);
/* CREATE table and add primary key Master setting primary key active growth */
CREATE TABLE IF not EXISTS t_student (ID integer PRIMARY KEY autoincrement, name TEXT, age INTEGER, score REAL);
/* CREATE table and add primary key */
CREATE TABLE IF not EXISTS t_student (ID integer, name TEXT, age INTEGER, score REAL, PRIMARY KEY (id));

iOS Development Database Chapter---"SQLite common statements"