SQL statement details

SQL statement details

1. insert

1. Primary Key impact on data insertion
The primary key must be unique in the same table. If the specified Primary Key and existing
If the data is repeated, it will cause an exception that violates the primary key constraint.

2. Influence of Foreign keys on data insertion
The foreign key is a constraint pointing to the existing data in another table. Therefore, the foreign key value must exist in the target table. If you insert
If the input data does not exist in the target table, the foreign key constraint is violated.

Ii. update:

1. Impact of non-null constraints on data update
As "non-empty constraint" means, if a non-empty constraint is added to a field, we cannot
Values in fields updated to NULL.

2. Primary Key impact on data update
The primary key must be unique in the same table. If the specified Primary Key and existing
If the data is repeated, it will cause an exception that violates the primary key constraint.

3. Influence of Foreign keys on data update
The foreign key is a constraint pointing to the existing data in another table. Therefore, the foreign key value must exist in the target table. If more
If the new data does not exist in the target table, the foreign key constraint is violated.

Iii. Search

1. count (*) and count (field) are different

If a field is NULL, the count column does not include NULL fields.

Some common SQL statements

(1) Data Record Filtering:

SQL = "select * from data table where field name = Field Value order by field name"
SQL = "select * from data table where field name like '% Field Value %' order by field name"
SQL = "select top 10 * from data table where field name order by field name"
SQL = "select * from data table where field name in ('value 1', 'value 2', 'value 3 ')"
SQL = "select * from data table where field name between value 1 and value 2"

(2) update data records:

SQL = "update data table set field name = field value where condition expression"
SQL = "update data table set field 1 = value 1, Field 2 = value 2 ...... Field n = value n where condition expression"

(3) Delete data records:

SQL = "delete from data table where condition expression"
SQL = "delete from data table" (delete all data table Records)

(4) add data records:

SQL = "insert into data table (Field 1, Field 2, Field 3 ...) Valuess (value 1, value 2, value 3 ...) "
SQL = "insert into target data table select * from source data table" (add records of source data table to target data table)

(5) statistical functions of data records:

AVG (field name) returns the average value of a table column
COUNT (* | field name) statistics on the number of data rows or the number of data rows with values in a column
MAX (field name) obtains the maximum value of a table column.
MIN (field name) obtains the minimum value of a table column.
SUM (field name) adds the values in the data column

The method for referencing the above functions:

SQL = "select sum (field name) as Alias from data table where condition expression"
Set rs = conn. excute (SQL)

Use rs ("alias") to obtain the calculation value. Use the same method for other functions.

(5) Create and delete data tables:

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)

(6) method of record set object:
Rs. movenext moves the record pointer down a row from the current position
Rs. moveprevious transfers the record pointer from the current position to a row up
Rs. movefirst move 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 row of page N
Rs. pagesize = N set N records per page
Rs. pagecount the total number of pages returned Based on pagesize settings
Rs. recordcount total number of returned records
Rs. bof returns whether the record pointer exceeds the first end of the data table. true table... the remaining full text>

Find some classic SQL statements.

1. Connect to the database
(Run SQL * Plus and use SCOTT/TIGER)

2. Simple Query
SQL> select table_name
From user_tables;
The running result is as follows:
TABLE_NAME

SALGRADE
BONUS
EMP
DEPT

SQL> select * from emp;

3. Format user results
SQL> select ename, sal
From emp
Where ename like 'a % ';
Try again. If you replace 'a % 'with 'a %' in the preceding statement to see what results will appear.
If you just replace the select statement with the SELECT statement, you can see what effect it will have.
SQL> column sal format $9,999.99
SQL> select ename, sal
From emp
Where ename like 'a % ';
Note the differences between the observed results and those just now.
If you replace $ with $, you can see what the result will be.

SQL> describe user_objects
SQL> select object_type, object_name
From user_objects
Order by object_type, object_name;
SQL> column object_name format a30
SQL> run
Observe the differences between the two running results.

SQL> set pause on
SQL> select *
From emp;
SQL> set pause off

4. Set the user page size
SQL> set pagesize 10
SQL> select rownum, object_name
From all_objects
Where rownum <20;
Check the number of records in each batch. Why does this happen?
SQL> show linesize
SQL> set linesize 200
SQL> select * from emp;

5. FEEDBACK (output a row at the end of the query to indicate the number of records returned in the query)
SQL> show feedback
SQL> select empno, ename, job
From emp
Where rownum <7;
SQL> set feedback off
SQL> select empno, ename, job
From emp
Where rownum <7;
Observe the differences between the two running results.
SQL> set feedback 3
SQL> select empno, ename, job
From emp
Where rownum <3;
SQL> select empno, ename, job
From emp
Where rownum <4;
Let's take a look at the differences in the results and carefully understand the role of FEEDBACK.

6. Change the display output
SQL> create table t (x number );
SQL> insert into t values... the remaining full text>