Shulou(Shulou.com)06/01 Report--

Chapter 1 Review of the basis of SQL

Omitting field names in INSERT statements is rare and risky. Don't do this unless you have a special reason. Don't make your SQL statements too fragile, because you can't predict when the structure of the table will change. For example, if someone rebuilds the table in the future, and the sort order of the fields in the table changes, and your INSERT statement happens to pass the field type and other constraint checks, you write the data to the wrong field in the table without an error prompt, it will be a long-term disaster. Therefore, it is strongly recommended that you specify a field name when writing an INSERT statement.

View the description of the table:

Desc table name

If you delete all rows with the DELETE statement, Oracle logs the deletion in its entirety so that we have a chance to roll back the data if it is a mistake. But it should be to keep a diary, so it takes a certain amount of time, and sometimes it can't reach the speed we want.

As a supplement, Oracle provides a TRUNCATE (truncation) statement to delete all data in the table. You can truncate the HR.EMPLOYEES table with the following statement:

Truncate table hr.employee

TRUNCATE statements do not have WHERE clauses or other restrictions. TRUNCATE statements are processed according to DDL (Data Definition Language, data definition language), so it also implies some database operations, such as implicitly committing other open transactions.

Non-standard handling of empty strings in Oracel

SQL standard practice is to use empty or zero-length strings to represent null values, while Oracle is not the case. We do not need to delve into the historical and regional reasons, but we must remember the regulations and usage of NULL by Oracle. For almost all other databases, SQL treats the empty string as a separate, known value.

People with programming background will find some similar rules that a 0-length string has only one string Terminator (\ 0) as its content. On the contrary, an encrypted string has no definite state. Even a Terminator. We cannot interchange strings of length 0 with unknown states, similar to how Oracle treats NULL.

The knack for using null values in SQL is also a daily lesson, which is often bewildering. The result of each SQL expression is one of three values: true, false, or empty. This is true in all kinds of comparisons, logical operations, and judgments. But in order to avoid occasional forgetting, we have to emphasize again and again: NULL is not equal to any value, any value is not equal to NULL, and NULL is not equal to NULL.

Oracel defaults to the binary sort method.

Sometimes we want to see two fields with the same value lined up together, ignoring case. You can do this with this NLS environment variable:

Alert session set NLS_SORT = 'BINARY_CI'

Oracle supports both case-sensitive and case-insensitive sorting. There is an environment variable NLS_SORT for case-sensitive and case-insensitive control. The default sort operation is case-sensitive, or NLS_SORT=BINARY. If we want to sort in a case-insensitive manner, we need to set the environment variable NLS_SORT to BINARY_CI.

As the name implies, NLS_SORT affects only the sort results, not other case operations. Even if NLS_SORT is set to BINARY_CI, the operation of comparing data in a case-insensitive manner continues the default way of Oracle.

Tip: the more traditional way is not to use the NLS environment variable, but to use the UPPER and LOWER functions to convert the field names and text to uppercase or lowercase and then compare. The disadvantage of this approach is that after using the function, the standard index cannot be used, the optimizer does not work, and the way to deal with it is to use function-based index (function-based index).

By setting the environment variable of the session, guide Oracle to compare with natural language. Although the table is full of Simth in this form rather than full lowercase, NLS_COMP environment variable can control the way Oracle compares characters. It can be set to LINGUISTIC and can be compared in a natural language way, which accords with human thinking habits.

Alert session set NLS_COMP = 'LINGUISTIC'; select first_name,last_name from hr.employee where last_name =' simth'

-

William SMITH

Lindsey Smith

The function of the MERGE statement is to insert new data into a table. Whether the record exists or not is judged by the PRIMARY key. If the primary key does not exist in the table, insert the row. If the primary key matches an existing row in the table, the row is updated with other details of the match key.

In the example, suppose you want to add information about some countries in the NEW_COUNTRIES table to the HR.COUNTIES table:

Merge into hr.countries c using (select country_id,country_name from hr.new_countries) nc on (c.country_id=nc.country_id) when matched then update set c.country_name = nc.country_name when not matched then insert (c. Classybaby _ name.) values (nc.country_id,nc.country_name)

The ON clause is followed by two other clauses. When the key values match, the contents of the "WHEN MATCHED CLAUSE" clause are executed, and when the key values do not match, the "WHEN NOT MATCHED" clause is executed, which inserts the new record into the target table.

Both the match and not-match clauses can contain more filters, and when the delete condition is met, the record can be deleted:

Merge into hr.countries c using (select country_id,country_name,region_id from hr.new_countries) nc on (c.country_id = nc.country_id) when matched then update set c.country_name = nc.country_name, c.region_id = nc.region_id delete where nc.region_id = 4 when not matched then insert (c. C.region_id) values (nc.country_id,nc.country_name,nc.region_id) where (nc.region_id! = 4)

In this modified version, except for rows with a new value of 4 for REGION_ID, all rows matching HR.CONTRIES based on COUNTRY_ID will modify the value of COUNTR_NAME, while rows with a REGION_ID of 4 will eventually be deleted. There are no matching rows, except for rows with a value of 4 for REGION_ID, all rows will be inserted into HR.COUNTRIES, while rows with a value of 4 for REGION_ID will be ignored.

Remember to like O (∩ _ ∩) O after reading it!

2015-03-17 23:40

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.