Orcale SQL statement Development

Available where comparison conditions are::
Equal to: =, <, <=,>, >=, <>
Including: In, not in
Value range: Between and not.
Matching Test: Like, not like
Null test: Is null, is
Boolean links: And, or, not

3. wildcard characters: In the WHERE clause, the wildcard is used with the like condition. In ORACLE:
% (Percent) is used to indicate any number of characters, or there may be no characters at all.
_ (Underline) indicates the exact unknown character.
? (Question mark) is used to indicate the exact unknown characters.
# (Well number) is used to indicate the exact Arabic number, ranging from 0 to 9.
[A-d] (square brackets) is used to indicate the character range, from A to D.

Chapter 3: conformity in the WHERE clause

1. punctuation marks
Spaces in names: Avoid them as much as possible, and use underscores instead of spaces.
Separated by commas.
Single quotes: in Oracle, only single quotes should be used to enclose text, characters, and dates. numbers cannot be enclosed by single quotes (including single double quotes.
Double quotation marks: in Oracle, single double quotation marks have different meanings. Double quotation marks are used to enclose column aliases that contain specific characters or spaces. Double quotation marks are also used to place text in the date format.
Select first_name as "first name"
From l_employees
Order by "first name"
# Font size: Use Access # font size to enclose numbers.
Semicolon: Used to end an SQL statement.
Reserved Words: Avoid using them.
Marker: in Oracle, the marker can also be written as two adjacent single quotes. In order to find all vendor names with marker in the middle of the Supplier name, you can write the code as follows:
Select * From l_suppliers where supplier_name like '% ''%'
Blank rows: Oracle generally does not allow any blank rows in SQL statements. You can set an option in sqlplus to allow blank rows in SQL statements.
& Symbol: in Oracle, & symbol is often used to indicate a variable. For example, & Fox is a variable, a little different & fox. Every time & Fox appears in an oracle script, you are required to provide a value for it. To use & Fox, you only need to provide the variable value when & Fox appears for the first time. If you want to use & as a common symbol, you should disable this feature. To disable this feature, run the following command: set define off, which is a sqlplus command, not an SQL command. Sqlplus sets the environment in which SQL runs in Oracle.
Double vertical bars: Oracle uses double vertical bars to represent string connection functions.
Asterisk: Select * indicates that all columns are selected. Count (*) indicates that all rows are calculated. When a wildcard is used, it indicates 0 or any number of characters.
Forward slash: Used to terminate an SQL statement in Oracle. More accurately, it indicates "running SQL code in the buffer zone ". A forward slash is also used as a separator.
Multi-line comment :/*......*/.
Not equal to: there are multiple expressions :! =, ^ =, <>, Not xxx = YYY, not (xxx = YYY)

Chapter 4: Save the result

1. Save the results in the new table or view.

1.1 create a new table from the results of the SELECT statement
ORACLE:
Create Table _ book
Select * From t_book B
Where B. Number like '123'

Access:
Select * From t_book B
Into _ book
From book
Where B. Number like '123'

1.2 create a new view from the SELECT statement results
ORACLE:
Create view v_book
Select * From t_book B
Where B. Number like '123'

2. Common SQL statements

2.1 one view can be built on another view
Create view vv_book
Select * From v_book B

2.2 commit and rollback commands

(Square brackets contain wildcards) indicate the character itself, without the nature of the wildcard.

 

3.6 add several new rows to the table containing the SELECT statement

Insert into t_book
Select B. ID, B. Kind, B. bookname
From book B
Where B. kind! = 11

Insert into t_book (ID, bookname)
Select B. ID, B. bookname
From book B
Where B. kind! = 11

3.7. Change the data that already exists in the table
Update t_book t
Set T. bookname = 'hahaha'
Where T. ID = 19
[Note]: The WHERE clause is very important. If it does not exist, all columns will be updated.

3.8 Delete rows from the table
Delete from t_book
Where id = 23;

4. in Oracle, you can run the following command:
Set autocommit on | off to set the automatic release of the commit command.

Chapter 5: Modifying data through views

1. Use the view with check Option
You can change the data through the view, even though the new or modified rows do not appear in the final view. Sometimes we don't want to make these changes. You can use with check option to define the view to prevent these changes. This can be done in Oracle and most other types of SQL products. Leizhimin 51cto technical blog

Select first_name, last_name, Age, Sex
From l_employees
Where dept_code = 'shp'
With check option;

2. Use of sqlplus

3. Data Dictionary Overview
A data dictionary is a set of tables that contain all information about the database structure. It includes the names of all tables, columns, primary keys, view names, select statements defining these views, and so on. Data dictionaries are sometimes used as System directories. Most SQL products have data dictionaries.
These tables are created and maintained by the database system itself. They contain all the information required by the database system to support its own processing and understanding.

Orcle data dictionary: Table and view information
Dictionary columns of the obtained information data dictionary table
Table Name: user_tables or all_tables table_name
View name: user_views or all_views view_name
View definition user_views or all_views text
User_tab_column or all_tab_column column_name
Table Primary keys user_constraints and user_cons_columns or all_constraints and all_cons_columns leizhimin 51cto technical blog

Leizhimin 51cto technical blog

3.1. How to find the name of a table
Select * From table_name

Chapter 6: Create your own table

1. Create a table

-- Create the l_employees table [1]
Drop table l_employees cascade constraints; -- preventive deletion [2]
Create Table l_employees (
Employee_id number (3 ),
First_name, varchar2 (5 ),
Last_name, varchar2 (10 ),
Dept_code, varchar2 (3 ),
Manager_id number (3 ))

Strorage (initial 2 K next 2 k pctincrease 0) -- Optional [3]
Tablespace & users; -- Optional [4] leizhimin 51cto technical blog

Alter table l_employees -- Optional [5]
Add constraint pk_l_employees
Primary Key (employee_id)
Using index -- Optional [6]
Strorage (initial 2 K next 2 k pctincrease 0) -- Optional
Tablespace & indx; -- Optional [7]

Leizhimin 51cto technical blog

Comment on table l_employees is 'employee table'; -- Optional [8]
Comment on column l_employees.employee_id is 'employee number'; -- Optional [9]
Comment on column l_employees.first_name is 'surname '; -- Optional
Comment on column l_employees.last_name is 'name'; -- Optional
Comment on column l_employees.dept_code is 'department codeny'; -- Optional
Comment on column l_employees.manager_id is 'manager codeny'; -- Optional

-- The table creation process is complete. The following describes how to insert data.
Insert into l_employees values (201, 'Jim ', 'fking', 'a32', 451); -- Optional [10]
......
Insert into l_employees values (999, 'Aix ', 'Tom', 'b51 ', 222 );

Leizhimin 51cto technical blog

Analyze table l_employees compute statistics; -- Optional [11]

Note:
-- [1]: A Comment on the SQL script. In Oracle and most other SQL products, the Comment line usually starts with two dashes and is followed by a space.
-- [2]: This is a preventive deletion. The phrase cascade constraints determines that the table will be deleted under all conditions. Without this phrase, the table will not be deleted under specific conditions.
-- [3]: The storage statement tells Oracle how much disk space is allocated to the table. This is usually handled by DBA and cannot be processed by application programmers. If this row is omitted here, the database uses the default value. In this storage statement, the initial 2 k parameter tells Oracle to allocate 2 kb of disk space to the table during initial table creation. The next parameter tells Oracle to allocate another 2 kb disk space to the table when the initially allocated disk space is full. The pctincrease 0 parameter tells Oracle that a 2 kb disk space is allocated for each table that fills up the disk space in the future. The solution is to allocate an average disk space. For example, pctincrease 50 increases each consecutive allocation by 50%. Therefore, the next allocation is 3kb, followed by 4.5kb. This parameter is sometimes used for rapidly growing tables.
-- [4]: The tablespace clause tells Oracle what table space should be used to create a new table. Here, a new table is created using the user tablespace, or the table space assigned to the & users variable. A tablespace is the place where the table is stored. It is a disk space area with a name. This is a DBA concept. The actual application product database usually has many disk drives. The space on these disk drives is divided into tablespaces, so it is easier to manage them. Generally, an Oracle database has at least four tablespaces: system, users, indx, and temp. The system tablespace is used by the data dictionary and should not be used elsewhere. The users tablespace is used to save most tables. The indx tablespace is used to store most indexes. The temp tablespace is used as the region for sorting. DBA can create other tablespaces. To view the table space name, you can use the user ID with DBA permission and enter the following command: Select tablespace name from dba_tablespace;
-- [5]: alter table command to make the employee_id column the primary key of the table
-- [6]: After a primary key is created, the index of the primary key is automatically created. The using index field sets the tablespace and disk space for the index.
-- [7]: Create the primary key index in the index tablespace, or assign the & indx variable to create the primary key index.
-- [8]: add comments to the table.
-- [9]: add comments to the table fields.
-- [10]: insert data.
-- [11]: after creating a new table and loading data in it, run the analyze table command. You should also run this command after adding a series of data to any expected table. This command puts information about the table (such as the table size and other features) into the data dictionary.

2. Update a table

Add a primary key to a table: a primary key is a type of constraints that restrict data that can be entered into the table. A table can only have one primary key. The primary key cannot be null. A primary key may consist of several columns. There is no need to publish a commit command after the alter table command. Changes made using the alter table command will immediately become permanent changes. In fact, you never need to use a commit command after the "Data Definition Language (DDL)" command. The DDL command creates a database object or changes the structure of an object. Only the "data modification language (DML)" command only requires the commit command. These DML Commands include insert, update, and delete, which change the data in the table.

Leizhimin 51cto technical blog

Alter table l_foods -- Comment [1]
Add constraint pk_l_foods -- Comment [2]
Primary Key (supplier_id, product_code); -- Comment [3]

Leizhimin 51cto technical blog

Note:
-- [1]: Use this command to change the l_foods table.
-- [7]: Name the constraint.
-- [7]: The word primary key indicates that this is a primary key constraint. The list of columns can contain columns that form primary keys. This list can contain any number of columns or even all columns in the table, but it is usually limited to only one or two columns.

Leizhimin 51cto technical blog

Change the primary key of a table: A table can only have one primary key. You must delete the original primary key before creating a new primary key.

Leizhimin 51cto technical blog

Alter table l_foods
Drop constraint pk_l_foods; -- pk_l_foods is the name of the constraint.

Leizhimin 51cto technical blog

Alter table l_foods
Add constraint pk_l_foods
Primary Key (menu_item );

Leizhimin 51cto technical blog

Add a new column to the table: the new column is always the last column of the table.

Leizhimin 51cto technical blog

Alter table l_foods
Add supply_date date; -- supply_date (supply date) is a newly added column with the Data Type of date.

Leizhimin 51cto technical blog

Extended Column Length: You can change the length of a text or numeric column, including changing the precision of a number, but the data type cannot be changed. All dates have the same data type. Therefore, it is meaningless to change the data type of the date column.

Leizhimin 51cto technical blog

Alter table l_foods
Modify food_name varchar2 (24 );

Leizhimin 51cto technical blog

Alter table l_foods
Modify price mumber (7, 2 );

Delete a column from the table:
Alter table l_foods
Drop column price_increase;

3. Duplicate rows

3.1. How to delete duplicate rows (Table A): Create a new table by removing duplicate query results.
Drop table A1;
Create Table A1
Select distinct *
From;

3.2. How to differentiate duplicate rows: Add a number column to the table to differentiate duplicate rows in the table. Set this column as the first column of the table.
Dorp table A1;
Create Table A1
Select rownum as row_id, name, price;

 

4. Load a large amount of data from a file

 

4.1 load a large amount of data from the file: The insert statement is a good tool to add a single row or an appropriate number of rows to the table. If you want to add a large number of rows to a table, you can put the data in a flat file, which is easier to operate. The so-called flat file is a common file without a special structure. You can use Notepad and other text editors to create a flat file. Data in flat files are separated by the "tab" key. To enter null, leave the data blank. The import principle is to use oracle's System Tool sqlldr.exe. The following is a batch processing script for importing a flat file (the "^" symbol in the batch processing indicates that a long command is divided into several lines, which is a continuation symbol in the batch processing file ).
Sqlldr.exe ^ -- Comment [1]
Control = 'C:/temp/load_file.ctl '^ -- Comment [2]
Log = 'C:/temp/log.txt '^ -- Comment [3]
Bad = 'C:/temp/bad.txt '^ -- Comment [4]
Rows = 50 -- Comment [5]
-- [1]: used for row continuation in the BAT file, which indicates that a row is a continuation of the current row. The computer will regard the entire BAT file as a single line of code. If ^ is not used, all parameters must be written in a single row.
-- [2]: The control file contains commands for data loading and data loading. It is an input file.
-- [3]: the log file is an output file and contains messages from loading.
-- [4]: The bad file is an output file that contains all rejected data.
-- [5]: This tells the loader to load 50 rows each time a commit is executed.

4.2. data loaded with delimiters in Oracle: It is very troublesome to specify the exact location of each field when loading data through a flat file. A simpler method is to load data with delimiters in Oracle. Data with delimiters is usually more convenient, because you do not need to arrange data perfectly in columns. When preparing a data file, you must first select characters that do not exist in the data as the delimiter. Here, we use a comma, which is placed between each two fields, marking the end of one field and the start of another field. Data is usually stacked together, and there is no blank between fields.

5. Analyze table command in Oracle: This Command tells the number of rows and other information of the table in the data dictionary. The optimizer uses this information to optimize the processing of select statements. Without this information in the data dictionary, the processing will not be particularly effective.
Analyze table a_organ compute statistics
-- Tells the number of rows in the_organ table of the data dictionary, and puts other data about the table into the dictionary.