(1) data record filtering:
SQL = " select * from datatable where field name = field value order by field name[desc]" SQL = " select * from datatable where field name like ' % field value % ' order by field name[desc]"
Sql="select top 10 * from data table where field names are sorted by field names [desc]" sql="select * from data table where field names in ('value 1',' value 2',' value 3')" sql="select * from data table where field names are at the value of 65433.
(2) Update data records:
Sql= "Update Data Table Setting Field Name = Field Value where Conditional Expression"
Sql= "Update data table setting field 1= value 1, field 2= value 2 ... field n= value n, where conditional expression"
(3) Delete data records:
Sql= "Delete where conditional expression from data table" sql= "Delete from data table" (delete all records in data table)
(4) Add data records:
SQL = "insert into datatable (field1,field 2, field 3 …) values (value 1, value 2, value 3 …)" SQL = "insert into target datatable select * from source datatable" (add the records in the source data table to the target data table).
(5) Statistical function of data record:
AVG (field name) returns the average value of table columns.
COUNT(*| field name) counts the number of rows of data or the number of rows of data with value in a column. MAX (field name) gets the maximum value of the table column. MIN (field name) gets the minimum value of the table column. SUM (field name) adds the values of data columns and refers to the above function:
2
Sql="select sum (field name) is used as the alias of the data table, in which the conditional expression "set RS = conn. EXECUTE (SQL).
Use RS ("alias") to get the total value of the system, and use other functions as above.
(5) the establishment and deletion of data table:
Create table data table name (field 1 type 1 (length), field 2, type 2 (length) ...) Example: create table tab01(name varchar (50), datetime default now ()) Drop table data table name (permanently delete a data table).
4. Recordset object method:
Rs.movenext moves the record pointer down one line from the current position.
Rs.moveprevious moves the recording pointer up one line from the current position.
Rs.movefirst moves the record pointer to the first row of the data table.
Rs.movelast moves the record pointer to the last row of the data table.
Rs.absoluteposition=N Move the record pointer to the nth row of the data table.
Rs.absolutepage=N Move the record pointer to the first line of the nth page.
Rs.pagesize=N set n records per page 2. Change tables.
Change table name
Add column column_name data type.
Note: Add a field (the syntax of the field will not be deleted. Change table name
Add primary key (column name)
Description: Change the definition of the table and set a field as the primary key. Change table name
Delete primary key (column_name) description: delete the definition of primary key.
Step 3 build an index
Index table _ name (column _ name) Description: Index the fields in the table to improve the query speed. Step 4 delete
Delete table name Delete index name
Second, the data form of data type smallint
16-bit integer.
three
Integer room
32-bit integer. decimal system
The exact value of p and a decimal integer with the size of s, where the exact value of p refers to all values with several digits and s refers to decimals.
How many digits are there after the dot? If not specified, the system will set p = 5；; S=0. Floating objects
32-bit real number. double；twofold
64-bit real number. character
A string of length n, where n cannot exceed 254. Variable character
A string with a variable length and a maximum length of n, where n cannot exceed 4000. Graph (n)
Same as char(n), but its unit is two characters and double bytes, and n cannot exceed 127. This form is for
In order to support two-character fonts, such as Chinese characters. Variable graph
A two-character string with variable length, with a maximum length of n, where n cannot exceed 2000. date
Contains the year, month and day. time
It includes hours, minutes and seconds. time stamp
It includes year, month, day, hour, minute, second and thousandth of a second.
Third, data manipulation DML (data manipulation language)
After the data is defined, the next step is the operation of the data. Data manipulation is nothing more than four modes: add data (insert), query data (query), change data (update) and delete data. Here's their grammar:
1, additional information:
Insert into table _ name (column 1, column 2, ...) Value (value 1, value 2, ...) Description:
1. If no column system is specified, the data will be filled in the order of the fields in the table. 2. The data format of the field must match the data filled in. 3. The table name can also be a scenic name.
INSERT INTO table_name (column 1, column 2, ...) Select columnx, columny, ... from another _ table.
Note: You can also populate data in other tables through subqueries.
four
2. Query information: basic query
Select column 1, column 2, ... from the table name.
Description: Lists all data in a specific field of the table name and selects *
From table name
Where the column 1 = xxx
[sum column & gtyyy][ or column3 & lt& gtZzz] III) cross connection.
Cross join has no WHERE clause. It returns the Cartesian product of all data rows of two connected tables and the numbers in the result set.
The number of data rows is equal to the number of data rows in the first table that meet the query criteria multiplied by the number of data rows in the second table that meet the query criteria.
For example, there are 6 books in the titles table and 8 publishers in the Publishers table, so the number of records retrieved by the following cross connection will be equal to 6*8=48 rows.
Select type, publishing name
Cross-connect publishers from titles and sort by type.
The UNION operator can combine the query result sets of two or more SELECT statements into one result set for display, that is, execute a joint query. The syntax format of UNION is: select_statement.
UNION[ALL]select statement
[UNION[ALL]select statement ][…n]
Where SELECTstatement is the select query statement to be combined.
The ALL option means that all rows are merged into the result set. If this item is not specified, only one row will be reserved for duplicate rows in the combined query result set.
In a joint query, the column heading of the query result is the column heading of the first query statement. Therefore, to define a column heading, it must be in the first query.
Definition in a sentence. When sorting the results of the union query, you must also use the column name, column title or column sequence number in the first query statement.
When using the UNION operator, you should ensure that there are the same number of expressions in the selection list of each joint query statement, and each query selects
Substitution expressions should have the same data type or can be automatically converted to the same data type. In automatic conversion, for numerical classes
Type, the system converts low-precision data types into high-precision data types.
In a UNION statement containing multiple queries, the execution order is from left to right, which can be changed by using parentheses.
five
Execution sequence. For example:
Query 1 union (query 2 union query 3)
Insert statement
Users can use the INSERT statement to insert a row of records into the specified table. For example, to insert the record of employee John Smith into the table of this example, you can use the following statements: insert into employees values ('Smith',' John',' 1980-06- 10',' Los Angles',16,45000.
With such an INSERT statement, the system will try to fill these values into the corresponding columns. These columns are arranged in the order defined when we created the table. In this example, the first value "Smith" will be filled in the first column LAST _ NAME and the second value "John" will be filled in the second column FIRST_NAME … and so on. We said that the system would "try" to fill in the values, and besides executing the rules, it would also perform type checking. If the types do not match (for example, a string is filled in a column of type number), the system will reject the operation and return an error message.
If SQL rejects the value of one column you fill in, the values of other columns in the statement will not be filled in. This is because SQL provides support for transactions. Transactions transfer a database from one consistency to another. If a part of a transaction fails, the whole transaction will fail and the system will revert (or roll back) to the state before the transaction.
Back to the original example of INSERT, please note that all integer decimals do not need to be enclosed in single quotes, but string and date type values should be distinguished by single quotes. Inserting commas between numbers to increase readability can lead to errors. Remember, commas are separators for elements in SQL.