What is SQL

• SQL (structured query language): structured query language
• SQL is a language for defining and operating data in relational database
• SQL language is simple and grammar is simple, easy to learn and use

Common relational database
PC side: Oracle, MySQL, SQL Server, Access, DB2, Sybase
Embedded \ mobile client: SQLite
sqlite lightweight database

• Less than 400K when fully configured, and less than 250K when optional function configuration is omitted
• Field (Col / Field): a field stores a value, which can store five types of data: integer, real, text, blob and null
• SQLite does not distinguish between accurate data types in essence when storing
• The main purpose of the database is to do data retrieval. Usually, the binary data that cannot be retrieved will not be saved in the database

Primary Key: Primary Key, a field that uniquely identifies a record. It has the following characteristics:

• Name: xxx_id
• Type: Integer
• Automatic growth
• The exact value is determined by the database and programmers don't care

Foreign Key: Foreign Key, which corresponds to the marks of other relational tables. Foreign keys can be used to establish a "relationship" with another table

• Convenient data maintenance
• Save storage space

Field type

• NULL: the value is NULL
• Integer: a signed integer that is stored as 1, 2, 3, 4, 6, or 8 bytes in size.
• REAL: floating point number, stored in the form of 8-byte exponent.
• TEXT: string, stored in database encoding (UTF-8, UTF-16BE or UTF-16-LE).
• Blob: BLOB data is stored in the form of input without any conversion. (binary data, such as files)
  ps: in relational databases, CLOB and BLOB types are used to store enlarged objects. BOLB represents binary large objects. This data type is used to save pictures, images, videos, etc. CLOB represents a large character object, which can store a large amount of character based data.

Characteristics of SQL statements
Case insensitive (for example, the database thinks user and user are the same)
Each statement must have a semicolon; ending

Common keywords in SQL:

• select, insert, update, delete, from, create, where, desc, order, by, group, table, alter, view, index and other keywords cannot be used to name tables and fields in the database

Types of SQL statements

Data Definition Language (DDL)

• Including create, drop and other operations
• Create a new table or delete a table (create table or drop table) in the database

Data Manipulation Language (DML)

• Including insert, update, delete and other operations
• The above three operations are used to add, modify and delete data in the table

Data query statement (DQL: Data Query Language)

• It can be used to query the data in the table
• The keyword select is the most used operation in DQL (and all SQL)
• Other keywords commonly used in DQL include where, order by, group by and having

Common SQL statements

• Create table
  //Create a table if it doesn't exist

  create table if not exists Table name (Field name 1 field type 1,Field name 2 field type 2, ...)；

//Create table

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

//Abbreviation

create table t_student(name, age);

In fact, SQLite is typeless, so it can be abbreviated as create table t_student(name, age);
In order to maintain good programming standards and facilitate the communication between programmers, it is best to add the specific type of each field when writing table building statements

• Delete table (drop)

//Delete the table, if any

drop table if exists t_student

//Delete table

drop table t_student;

• Insert data

insert into Table name (Field 1, Field 2, ...) values (Value of field 1, Value of field 2, ...) ;

• Update data

update Table name set Field 1 = Value of field 1, Field 2 = Value of field 2, ... ; 

//Update data

update t_student set name = 'jack', age = 20 ; 

Note: the above example will change t_ The name of all records in the student table is changed to jack and the age is changed to 20

• Delete data

	delete from Table name ;

//Delete table contents

	delete from t_student ;

The above example will change t_ Delete all records in the student table

• Conditional statement

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

where field = A value ; // You can't use two=
where field is A value ; // is equivalent to=
where field != A value ;
where field is not A value ; // is not equivalent to=
where field > A value ;
where Field 1 = A value and Field 2 > A value ; // and is equivalent to in C language&&
where Field 1 = A value or Field 2 = A value ; // or is equivalent to in C language||

Examples

//Will t_ For the records in the student table whose age is greater than 10 and whose name is not equal to jack, the age should be changed to 5
update t_student set age = 5 where age > 10 and name != 'jack' ;
//Delete t_ Records in student table with age less than or equal to 10 or older than 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' ;
//Will t_ For the record whose name is equal to jack in the student table, the value of the score field is changed to the value of the age field

• Query statement (select)

select Field 1, Field 2, ... from Table name ;
select * from Table name; // Query all fields
select name, age from t_student ;
select * from t_student ;
select * from t_student where age > 10 ;  //  Condition query

• alias

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 ;

Fields and tables can be aliased
Examples

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 t_ The student table has an alias called s, which is used to refer to the fields in the table

Common compound statements

• Calculate the number of records

select count (field) from Table name ;
select count ( * ) from Table name ;
Note: this statement is a compound statement
//Calculate age quantity
select count (age) from t_student ;
//Calculate the number with a score greater than or equal to 60
select count ( * ) from t_student where score >= 60;

• sort

The query results can be used order by Sort
select * from t_student order by field ;
select * from t_student order by age ;
The default is to sort in ascending order (from small to large) or descending order (from large to small)
select * from t_student order by age desc ; //Descending order
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 ;
//First sort by age (ascending order), and then sort by height (descending order) if the age is equal

• Limit limit search scope

use limit You can accurately control the number of query results, such as querying only 10 pieces of data at a time
select * from Table name limit Value 1, Value 2 ;
select * from t_student limit 4, 8 ;
//It can be understood as: skip the first 4 statements, and then take 8 records
limit It is often used for paging query. For example, five pieces of data are displayed on each page, so the data should be retrieved in this way
 Page 1: limit 0, 5
 Page 2: limit 5, 5
 Page 3: limit 10, 5
...
The first n Page: limit 5*(n-1), 5
select * from t_student limit 7 ;
//Equivalent to select * from t_student limit 0, 7 ;
//Indicates that the first 7 records are taken

constraint

• Simple constraint

When creating a table, you can set some constraints for specific fields. Common constraints are

not null : The value of the specified field cannot be null null
unique : The value of the specified field must be unique
default : Specifies the default value for the field

Suggestion: try to set strict constraints on fields to ensure the normalization of data)
Examples

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

Primary key constraint
What is a primary key?

• Primary Key (PK) is used to uniquely identify a record

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

• A primary key can be one field or multiple fields

  A good database programming specification should ensure the uniqueness of each record. Therefore, a primary key constraint is added, that is, each table must have a primary key to identify the uniqueness of the record

Design principle of primary key

• The primary key should be meaningless 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

Declaration of primary key
1. When creating a table, use primary key to declare a primary key

create table t_student (id integer primary key, name text, age integer) ;
//The id of integer type is used as t_ Primary key of student table

1. Fields of primary key

• As long as it is declared as primary key, it means it is a primary key field
• The primary key field contains not null and unique constraints by default

If you want self growth, you should add autoincrement

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

Foreign key constraint
What are foreign key constraints? effect?
Using foreign key constraints can be used to establish the relationship between tables
The primary key of a table refers to a field of another table

create table t_student (id integer primary key autoincrement, name text, age integer, class_id integer, constraint fk_t_student_class_id_t_class_id foreign key (class_id) references t_class (id)) ; 
//t_ There is one in the student table called FK_ t_student_ class_ id_ t_ class_ Foreign key for ID
//The function of this foreign key is to use t_class in student table_ id field reference t_ id field of class table

Table join query
What is a table join query?
You need to combine multiple tables to find the desired data.
Type of table connection
Inner join: inner join or join (shows records with complete field values in both the left and right tables)
Left outer join: left outer join (ensure the integrity of left table data)