This article assumes that the reader has already understood the database connection technology, so only the syntax of the SQL query command is discussed.
The surface structure is as follows (MS Access 2000):
Table name: UserTable
----------------------
Field name field type
----------------------
UserID automatic count (long integer)
Username text
Usersex text
Createdate Date / Time (Default is now ())
----------------------
First, use the select command to extract records
1, get all records in the table
SELECT command, the statement is as follows:
"SELECT * homeTable"
2, get the Userid field record in the table
"SELECT Userid from UserTable"
3, get the userid, usersex field record in the table
"SELECT Userid, UserSex from UserTable"
4, get the record of the userSex value "male" in the table
"SELECT from utertable where usersex = 'male'"
5. Take the record of the "Zhang" word in the table in the table
"SELECT from utertable where username like '%%" "
6, get all the records in the table, press the Createdate field value descending order
"Select from userTable Order By CreateDate DESC"
Description:
1) The above command can be used in combination. For example, obtain the value of the "Hao" word in the table in the table, and press the time descending order of the Createdate value, then the command is as follows:
"SELECT Userid from UserTable Where Username Like '% Ho] ORDER BY CREATEDATE DESC"
2) In use, it should be noted that if the field type is text, then in the conditional discrimination, the single quotes should be applied to the condition word, which is suitable for any SQL query command.
3) "%" in the condition word is wildcard.
4) When the discrimination condition is more than one, it should be connected in "and" or "or".
Second, insert a new record with the insert into command
1, insert a new record
"INSERT INTO UserTable (UserName, Usersex) VALUES ('Zhang Haoan', 'Men')" Description:
Here, it is important to note that the type of the value of the inserted record should comply with the field type in the table, otherwise it will be wrong. In fact, the use of single quotes is just a convenient method, but it is not standardized, because if it is inserted in the record itself contains single quotes, there is an error (although this is not often happened, it may exist). So we'd better use a custom function to implement single quotes. Methods as below
Function SQLSTR (DATA)
SQLSTR = "'" & repeate (data, "'", "'') &" '"
END FUNCTION
In command:
DIM Name, SEX
Name = "Zhang Haoan"
SEX = "Men"
"INSERT INTO UserTable (" & SQLSTR (Name) & "," & Sqlstr (SEX) & ")
In this way, no matter whether it contains single quotes or double quotes in the value.
Note, please note that this rule only applies to fields of type text. If it is other type, it is generally not required to add any symbols.
Third, update records with Update
1. Update all the fields of all records
"Update UserTable Set Userid = 1, Usersex = 'Male', Username = 'Xu Yong', CreateDate =" & CDATE (CREATEDATE)
Description:
The values in the formula can be used in operation, such as userid = userid 1, etc.
2. Update the value of the username field value "Xu Yong" record value
"Update UserTable Set Userid = 1, username = 'Xu Wei' Where username = 'Xu Yong'"
Description:
Methods are asgens when needed to query. Also pay attention to single quotes.
Fourth, delete records with Delete
1. Delete all records
"Delete from userTable"
2. Delete records of specific conditions, such as deleting a record of UserId as "20"
"Delete from utertable where userid = 20"
3, delete the combination condition record, such as deleting usersex as "female", UserName contains "Zhang" record
"Delete from userTable where usersex = 'and username like'% Zhang% '"
Description:
Pay attention to single quotes.
