Basic SQL query language tutorial (3)

Basic SQL query language tutorial (3)

4. create table statement
The syntax of the create table statement is:

Create Table (field1 type [(size)] [not null] [index1]
[, Field2 type [(size)] [not null] [index2] [,...]
[, Constraint multifieldindex [,...])

Table
Name of the newly created table
Field1 type [(size)] [not null] [index1]
Field1 is the field name, type is the field data type, size is the field width, the following table details
Describes the value and description of type.

Type Jet data type description
Bit yes/no logical type
Byte numberic-byte number
Counter automatic number
Currency number
Datetime date/time date, time
Double numberic-double precision Floating Point Number
Long numberic-long integer
Longbinary OLE object type
Longtext memo remarks type
Short numberic-integer
Single numberic-single precision Floating Point Number
Text text

Not null
The value of this field cannot be blank.
Index1
Define field constraints

Example 7: Create a student data table
We still use the aforementioned db4.mdb file to create a new project, add the DaO definition library, and then add the following code to the load event of form1:
Private sub form_load ()
Dim astr as string
Dim dbadd as database

Screen. mousepointer = vbhourglass
Set dbadd = dbengine. workspaces (0). opendatabase ("C:/db4.mdb ")
Astr = "create table tbl_students "&_
"(Stdid counter primary key ,"&_
"Stdname text (12) not null ,"&_
"Stdage short ,"&_
"Stdbir datetime ,"&_
"Stdsex bit )"
Debug. Print astr
Dbadd. Execute astr

Screen. mousepointer = vbdefault
Msgbox "database created successfully ."
Dbadd. Close
Set dbadd = nothing
End sub
Run the program. The message box indicating that the database is successfully created is displayed. use access to open db4.mdb. You can see that the table tbl_students has been added to the database.
. In the above example, we add a table named tbl_students and add five fields: stdid: Student ID, automatic ID type, and act as the primary key;
Stdname: Specifies the Student name. It must be 12 characters in length and cannot be blank. stdage: Age of the student, integer type; stdbir: Date of birth and date type;
Stdsex: Gender, logical type.
To delete a table in the database, you only need to use the drop TABLE statement to delete the table. The example is as follows:
Drop table tblname
Tblname indicates the table name.

V. insert into statement
The insert into statement inserts data into a table. This function can insert data from an existing table into a new table or custom values into a new table.
The function syntax for inserting data in an existing table is as follows:

Insert into target [(field1 [, field2 [,...])] [In externaldatabase]
Select [source.] sfield1 [, sfield2 [,...]
From tableexpression

Target
Name of the table to add data
Field1, field2
Field name of the data to be added
Externaldatabase
Additional database name
Source
Name of the table for copying data
Sfield1 sfield2
Name of the field for copying data

The syntax for inserting new data is as follows:
Insert into target [(field1 [, field2 [,...])]
Values (value1 [, value2 [,...])

Value1, value2
The inserted value. value1 is inserted into field1 and value2 is inserted into field2.

Example 8: Insert new data and other data into the table
In the previous example, we have added a table named DB2 and a table named tbl_students to C:/db4.mdb. DB2 contains three students.
Tbl_students does not contain records. Now we need to attach the Student name value in the student record in the DB2 table to the stdname field of the tbl_students table.
. The following is an example:
Create a new project, add the DaO definition library, and add the following code to the form1 form_load event:

Private sub form_load ()
Dim astr as string
Dim dbadd as database

Screen. mousepointer = vbhourglass
Set dbadd = dbengine. workspaces (0). opendatabase ("C:/db4.mdb ")
Astr = "insert into [tbl_students] ([stdname])" & _
"Select distinct db2. student from DB2"
Debug. Print astr
Dbadd. Execute astr

Screen. mousepointer = vbdefault
Msgbox "data added successfully ."
Dbadd. Close
Set dbadd = nothing
End sub

Run the program. A message box is displayed. Open db4.mdb and you can see that the student name in the DB2 table has been added to tbl_students.
Changing astr to the following command string adds a new record:
Astr = "insert into [tbl_students] ([stdname], [stdage], [stdbir], [stdsex])" & _
"Values ('Li Xiang ', 15, #1985-10-10 #, 1 )"

Vi. Delete statements
The statement syntax is as follows:

Delete from table
Where criteria

Table
Name of the table to delete the record
Criteria
Deletion Conditions

The delete statement deletes the level that meets the criteria of criteria in the table. For example, the following statement deletes records with the Count field greater than 10 in the table TBL:
Dbftext. Execute ("delete from [TBL] Where [TBL. Count]> 10 ")

VII. alter table statement
The alter table statement modifies the database structure. It can add or delete a column to or from a table. The function syntax is as follows:

Alter table table {Add {Column field type [(size)] [not null] [constraint Index] |
Constraint multifieldindex} |
Drop {Column field I constraint indexname }}

The alter table statement contains two substatements: Add column or drop column. The add column statement adds columns to the table,
Drop column: delete a column in the table. In addition, the constraint sub-statement is used to create a data index. Other elements in the syntax are described as follows:

Table
Name of the table to change the structure
Field
Name of the column to be added/deleted
Type (size)
Data type and length of columns added
Index
Index name
Multifieldindex
Multi-field index name

Example 9: Add a column to tbl_students
Create a new project, add the DaO definition library, and add the following code to the form1 form_load event:

Private sub form_load ()
Dim astr as string
Dim dbadd as database

Screen. mousepointer = vbhourglass
Set dbadd = dbengine. workspaces (0). opendatabase ("C:/db4.mdb ")
Astr = "alter table tbl_students add column stdphone text (15 )"
Debug. Print astr
Dbadd. Execute astr

Screen. mousepointer = vbdefault
Msgbox "column added successfully ."
Dbadd. Close
Set dbadd = nothing
End sub
When you run the program, a message box is displayed. Open the tbl_students table in db4.mdb and you will see an additional stdphone column.
It is of the text type and the length is 15 bytes.

VIII. Update statement
The update statement executes a lot of updates on the data in the database. The statement syntax is as follows:

Update table
Set newvalue
Where criteria

Table
Name of the database whose data content you want to change
Newvalue
Change the old record value to the new value expression ,.
Criteria
An expression that SQL uses to monitor which record values will be changed.

Example 10: Change the phone number in the tbl_student table
Assume that the phone numbers in the student database need to be changed in batches because of the phone number upgrading, the update statement can be very convenient.
First, use access to open db4.mdb and write the phone number in the stdphone field of the tbl_students table. Then create a new project in VB,
Add the DaO definition library and add the following code to the form1 form_load event:
Private sub form_load ()
Dim astr as string
Dim dbadd as database

Screen. mousepointer = vbhourglass
Set dbadd = dbengine. workspaces (0). opendatabase ("C:/db4.mdb ")
Astr = "Update tbl_students set [stdphone] = '6' + [stdphone]" & _
"Where [stdphone] <> ''"
Debug. Print astr
Dbadd. Execute astr

Screen. mousepointer = vbdefault
Msgbox "records changed successfully ."
Dbadd. Close
Set dbadd = nothing
End sub
When you run the program, a message indicating that the change was successful is displayed. Open the tbl_students table in db4.mdb and you can see the original record in the stdphone field.
Previously added 6. Without a phone record, there is no change

Www.applevb.com