iOS Development Database-sql

Last Update:2015-10-06 Source: Internet

Author: User

Developer on Alibaba Coud: Build your first app with APIs, SDKs, and tutorials on the Alibaba Cloud. Read more ＞

one, SQL statementsIf you want to manipulate the data in the database while the program is running, you must first learn to use SQL statements1. What is SQLSQL (structured Query language): Structured Query LanguageSQL is a language for defining and manipulating data in a relational databaseSQL language concise, simple syntax, studious and useful2. What is a SQL statementThe sentence \ code written in the SQL language is the SQL statementIn the process of running the program, you must use SQL statements to manipulate (add and remove, CRUD) data in the databaseFeatures of the 3.SQL statementCase insensitive (E.G. The database thinks the user and user are the Same)Each statement must have a semicolon; EndThe commonly used keywords in 4.SQL areselect, insert, update, delete, from, create, where, desc, order, by, group, table, alter, view, index, and so onYou can not use keywords to name tables, fields in a databaseII. Types of SQL statements1. Data definition statements (ddl:data definition Language)Includes operations such as Create and dropCreate 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 moreThe above 3 actions are used to add, modify, and delete data in a table, respectively3. Data query Statement (dql:data query Language)can be used to query for data in a tableKeyword SELECT is the most used operation for DQL (and all sql)Other dql commonly used keywords are where,order by,group by and havingthree, the basic operation1. Create a tableCREATE 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, ...);ExampleCREATE TABLE t_student (id integer, name text, age integer, score real);2. Field typeSQLite divides the data into the following types of storage:Integer: integer valueReal: floating-point valueText: literal stringBlob: 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 typeIt 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 statement3. By deleting the tableFormatdrop table name;drop table if exists name;Exampledrop table t_student;4. Inserting Data (insert)FormatInsert into table name (field 1, field 2, ...) values (value of field 1, value of field 2, ...);ExampleInsert INTO t_student (name, age) values (' MJ ', 10);AttentionThe string contents in the database should be enclosed in single quotes5. Updating Data (update)FormatUpdate table name set field 1 = Value of field 1, Field 2 = value of field 2, ...;ExampleUpdate t_student Set name = ' Jack ', age = 20;AttentionThe previous example changes the name of all records in the T_student table to Jack,age to 206. Deleting Data (delete)FormatDelete from table name;ExampleDelete from t_student;AttentionThe above example deletes all the records in the T_student table7. Conditional StatementsIf you only want to update or delete some fixed records, you must add some conditions after the DML statementCommon formats for conditional statementswhere 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 languagewhere field 1 = a value or field 2 = a value; or equivalent to the C language | |ExampleChange the age of the T_student table to more than 10 and the name does not equal Jack's record to 5Update 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 tableDelete from T_student where is age <= or age > 30;Guess the effect of the following statementUpdate 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 field8.DQL statementsFormatSelect field 1, field 2, ... from table name;SELECT * FROM Table name; Query all fieldsExampleSelect name, age from t_student;SELECT * FROM t_student;SELECT * from t_student where age > 10; Conditional query9. AliasesFormat (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;ExampleSelect 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 table10. Calculate the number of recordsFormatSelect Count (field) from table name;Select Count (*) from table name;ExampleSelect Count (age) from t_student;Select Count (*) from T_student where score >= 60;11. SortingThe results of the query can be sorted with order bySELECT * 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; DescendingSELECT * from T_student ORDER by age asc; Ascending (default)You can also sort by multiple fieldsSELECT * from T_student order by age asc, height desc;Sort by age (ascending), and age equal by height (descending)12.limitUse limit to precisely control the number of query results, such as querying only 10 data at a timeFormatSELECT * from table name limit value 1, value 2;ExampleSELECT * from t_student limit 4, 8;Can be understood as: skip the first 4 statements, then fetch 8 recordsThe limit is often used for paging queries, such as 5 data per page, so you should take the data1th page: Limit 0, 52nd page: Limit 5, 53rd page: Limit 10, 5...Page n: Limit 5* (n-1), 5SELECT * FROM t_student limit 7; This statement is equivalent to select * from T_student limit 0, 7; to take the first 7 recordsIv. constraints1. Simple ConstraintsWhen you build a table, you can set some constraints on specific fields, and the common constraints areNot null: the value of the specified field cannot be nullUnique: the value of the specified field must be uniqueDefault: specify a value for the field( recommendation: as far as possible to set strict constraints on the field to ensure the normative data)ExampleCREATE TABLE t_student (id integer, Name text not null-unique, age integer NOT null default 1);The name field cannot be null, and the uniqueThe age field cannot be null, and the default is 12. PRIMARY KEY constraints(1) Brief descriptionIf 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 dataA good database programming specification should ensure uniqueness of each record, adding a PRIMARY KEY constraintIn 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 recordFor example, t_student can add an ID field as the primary key, which is equivalent to a Person's IDThe primary key can be a field or multiple fields(3) design principle of primary keyThe primary key should not be meaningful to the userNever update the primary keyThe primary key should not contain dynamically changing dataThe primary key should be automatically generated by the computer(4) declaration of the primary keyDeclare a primary key with primary key when creating a tableCREATE 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 tablePrimary key fieldAs long as primary key is declared, it is a primary key fieldThe primary key field contains not NULL and a unique two constraint by defaultDescription: If you want the primary key to grow automatically (must be an integer type), you should increase the AutoIncrementCREATE TABLE T_student (id integer PRIMARY key autoincrement, name text, age integer);3. FOREIGN KEY constraintsUsing FOREIGN KEY constraints can be used to establish a table-to-table connectionThe general case of a foreign key is a field in a table that refers to the primary key field of another tableCreate a new foreign keyCREATE 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 table4. Table Connection QueryTable Join query: You need to federate multiple tables to find the data you wantTypes of table joinsInner 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)ExampleFind all students in class 0316iOSSelect S.name,s.age from T_student s, t_class c where s.class_id = c.id and c.name = ' 0316iOS ';iOS Development Database-sql

This article is an English version of an article which is originally in the Chinese language on aliyun.com and is provided for information purposes only. This website makes no representation or warranty of any kind, either expressed or implied, as to the accuracy, completeness ownership or reliability of the article or any translations thereof. If you have any concerns or complaints relating to the article, please send an email, providing a detailed description of the concern or complaint, to info-contact@alibabacloud.com. A staff member will contact you within 5 working days. Once verified, infringing content will be removed immediately.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

Get Started for Free

Sales Support

1 on 1 presale consultation

Chat Contact Sales
After-Sales Support

24/7 Technical Support 6 Free Tickets per Quarter Faster Response

Open a Ticket
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.

Learn More

iOS Development Database-sql

Contact Us

A Free Trial That Lets You Build Big!

Sales Support

After-Sales Support