Basic operation of database--adding and deleting, checking and querying

Table: tabstudentinfo

Id	Name	Gender	Class	Grade
Primary Key	NOT NULL	NOT NULL	NOT NULL	NOT NULL
Varchar (100)	Varchar (100)	Varchar (10)	Varchar (100)	Varchar (100)

Table: tabscoreinfo

Guid	Stuid	Chinese	Math	中文版	Other
Primary Key	NOT NULL	NOT NULL	NOT NULL	NOT NULL	NOT NULL
Varchar (100)	Varchar (100)	Int	Int	Int	Int

Note: Table names, field names, and keywords are case-insensitive in SQL statements.

Primary key: Meaning the primary key, the value of the field that is set as the primary key must be unique in all records, and cannot be empty.

Not Null: Indicates that the value of the field cannot be a null value.

1 , add data:

Keywords: insert

Statement structure: INSERT into table name (Field 1, Field 2, Field 3,...) VALUES (corresponding lot 1 value, corresponding lot 2 value, corresponding lot 3 value, ...)

Note: Fields marked as primary key and not NULL must have a value.

For example:

INSERT into tabstudentinfo values (' key ', ' Mike ', ' Male ', ' class1 ', ' Grade2 ')

Or

Insert into Tabstudentinfo (Id, Name, Gender, Class, Grade)

VALUES (' key ', ' Mike ', ' Male ', ' class1 ', ' Grade2 ')

2 , delete data:

Keyword:delete

Statement structure: Delete from table name where field name = field value

For example:

Delete from tabstudentinfo where Id = ' 123456 '

The value of the ID field in the Tabstudentinfo table will be deleted all records of 123456 .

Or

Delete from Tabstudentinfo//will delete all data in the Tabstudentinfo table.

3 , To Modify the data:

Keyword:update

Statement structure: Name of the Update table set the field name to update = the value of the field to update where field name = field value

For example:

Update Tabstudentinfo Set Class = ' Class1 ' where Id = ' 123456 '

The value of the Id field in the Tabstudentinfo table is updated to Class1 for all records that have a 123456 value of the class field

Or

Update Tabstudentinfo Set Class = ' Class1 ', Grade = ' grade1 ' where Id = ' 123456 '

The value of the Id field in the Tabstudentinfo table is updated to Class1 for all records that have a 123456 value of the class field

The value of the Grade field is updated to grade1

4 , find data:

Keywords:Select

Statement structure: SELECT * from table name where field name = field value

Or

SELECT * from table name where field 1 name = Field 1 value and/or field 2 name = value of field 2

Note: "SELECT *" means to find information for all fields and to find information for the specified field, list the field names and separate them with commas. And means, or represents, or

For example:

SELECT * from tabstudentinfo where Class = ' class1 '

Finds all records in the Tabstudentinfo table that have class equals Class1 .

Or

SELECT * from tabstudentinfo where Class = ' class1 ' and Grade = ' grade1 '

Finds all records in the Tabstudentinfo table that have class equals Class1 and grade equals grade1 .

Or

Select Name, Gender from tabstudentinfo where Class = ' class1 '

Finds information for the name and gender fields of all records in the Tabstudentinfo table that are equal to Class1 .

5 , the form of the query ( the most basic)

Statement structure: SELECT * from Form 1, Form 2 where form 1. Field name = Form 2. Field name

Or

SELECT * FROM Form 1 as Alias 1, Form 2 as Alias 2 where alias 1. Field name = Alias 2. Field name

Note: The point between the form name and the field name.

When the form name is too long, you can use the second syntax as a temporary alias for the form, and you can use aliases in the query criteria instead of the form name.

For example:

SELECT * from Tabstudentinfo, tabscoreinfo where tabstudentinfo.id = Tabscoreinfo.stuid

Or

SELECT * from Tabstudentinfo as t1,tabscoreinfo as T2 where T1. Id = T2. Stuid

Basic operation of database--adding and deleting, checking and querying