Database Fundamentals 2

---restore content starts---

Database additions and deletions this is the largest database in the

Inster Delete Update Select

Create and delete tables in the database (CREATE table and drop table)

Commonly used keywords are where, order by, group by, and Having,limit

The 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 Inetger, score real);

If a t_student is not created, if there is no this table is created

CREATE table if not exists t_student (ID integer, name text, age Inetger, score Real)

SQLite divides the data into the following types of storage:
Integer: Integer value
Real: floating-point value
Text: literal string
BLOB: Binary data (such as files)

Delete a table

Format
drop table name;
drop table if exists name;

Example
drop table t_student;

Inserting data

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

Update data

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 previous example changes the name of all records in the T_student table to Jack,age to 20

Delete data

Delete from table name;

Example
Delete from t_student;

Attention
The above example deletes all the records in the T_student table

Conditional statements

where

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

Common formats for conditional statements
where 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 language
where field 1 = a value or field 2 = a value; or equivalent to the C language |

Example
Change the age of the T_student table to more than 10 and the name does not equal Jack's record to 5
Update 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 table
Delete from T_student where is age <= or age > 30;

Inquire

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

Alias from

Format (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;

Example
Select 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 table

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;

Sort

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 order BY age Desc; Descending
SELECT * from T_student order by age ASC; Ascending (default)

You can also sort by multiple fields
SELECT * from T_student ORDER BY age ASC, height desc;
Sort by age (ascending), and age equal by height (descending)

Take value

Use limit to precisely 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, then fetch 8 records

The limit is often used for paging queries, such as 5 data per page, so you should take the data
1th page: Limit 0, 5
2nd page: Limit 5, 5
3rd page: Limit 10, 5
...
Page N: Limit 5* (n-1), 5

Constraint ********************************

When you build a table, you can set some constraints on specific fields, 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
(recommendation: 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 the default is 1

FOREIGN KEY constraints

Using FOREIGN KEY constraints can be used to establish a table-to-table connection
The general case of a foreign key is a field in a table that refers to 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, constrain T Fk_student_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 table

If the specified ID is the primary key, the sequence number is added automatically in the table.

Database Fundamentals 2