[Oracle] SQL * PLUS command usage

I. DataBase
Save data to present data in the form of tables
 
Ii. SQL
Structur query language
Structured Query Language is an object used to operate relational databases.
DDL (Data definition language), used to create or Delete tables, and modify table constraints.
Create table, alter table, and drop table add and delete operations on the table structure.
DML (Data manipulation language), insert records into the table, modify records
Insert, update, delete, merge
Transaction, transaction control language, composed of DML statements, commit;, rollback;
Select query statement
Dcl authorization statement grant
 
Iii. Oracle
DBMS Database Management System
Available in Oracle, AS, and Application Server
DBA Database Administrator

Iv. Related Operations
1. sqlplus database access commands (local access/remote access) and database connection commands are the database operation environment
Sqlplus user name/Password
2. show user: displays the user Name of the current user.
You can directly connect the user name/password to change your identity. This is the sqlplus command.
Available in sqlplus! You can switch between shell and sqlplus ,! You can use shell commands in sqlplus.
In fact, sqlplus runs a sub-process to execute shell commands.
3. There are two types of tables in the Oracle database: User tables (User-operated tables) and system tables (tables maintained by the database system, also called data dictionaries)
DDL operations on user tables start with DML operations on system tables!

5. Basic syntax
1. select query statement
Select table_name from user_tables; (query system table)
The preceding query statement is used to query the names of all tables owned by a user.

Projection operation. Only the information of the selected field is displayed.
Select Operation to view specific information in the field.
Join Operation, multi-Table query, query information in multiple tables through table connection

(1) select table_name from user_tables; (query system table)
The preceding query statement is used to query the names of all tables owned by a user.

(2) The buffer of sqlplus caches the last SQL statement. You can use "/" to execute the last SQL statement. You can also use
Edit command to edit the last SQL statement.
L command (list) (sqlplus command) can display the last command in buffer.

Sqlplus command can be abbreviated

(3) DESC [Table name]
This is a sqlplus command. Note that it is not an SQL statement. This command is used to view the table structure. Descript
[Field name] [field type], which is the table structure displayed after the DESC command is used.

(4) select [Table field name 1], [Table field name 2],... from table name;
Select * from table name; query information of all fields in the table

(5) The SQL statement, table name, and field name are case-insensitive.
The SQL statement ends with ";". If ";" is not added, the system does not execute this SQL statement and prompts you.
In Oracle, the characters are left-aligned and the values are right-aligned.

(6) mathematical expressions can be used in select statements.
Select [expression (this table field name must be included)], [...],... from table name;
The first multiplication, division, and addition and subtraction of the Operation priority, and the left-to-right operation of the same level. The brackets change the priority.

(7) alias
Select [field name or expression] ["alias"], [...] ["..."],... from table name;
You can add a space "alias" after the field name or expression to give the column or the alias of the expression result.
The alias must be enclosed in double quotation marks.

(8) String concatenation | symbol
Select target field name | "" | target field name from table name;

Note: The character string in Oracle must contain '..'
The alias must contain spaces or be case sensitive.

Exercise:
Write an SQL statement by yourself. The execution result is select * from ...;
Where... is the name of each system table
That is, add "select * from" before the table name of each system table, and then add ";"
Select 'select * from' | table_name | ';' from user_tables;

2. handle errors

(1 )! Oerr ora [Error Code]. The system displays the cause of the error and how to modify it. If the command is incorrectly entered, you can use edit or ed to modify the input error.
It is actually the last SQL statement in the cache file editing.
You can also use (change) c/error field/correct field to modify the replacement operation.
Only used on Linux
! It is equivalent to a host. The connection is not disconnected, but a switchover is performed to execute the shell command.
(2) edit command to edit the last SQL statement.

3. sqlplus settings

Set pause on press enter to respond, split-screen display, only valid in this session
Set pause off Disable Split screen display.
Set pause "..." sets the prompt information displayed on the split screen.
Set pause on first outputs prompt information, press enter to respond, split screen display
Set head off leading output off
Set feed off
Set echo off write-back disabled
Spool file name. Write the specified file to SQL
Spool off to disable writing.

4. SQL script

That is, a file with SQL statements in the file can be run in sqlplus.
Introduce SQL scripts
Sqlplus user name/password @ SQL script (Note: after entering the user name and password, add a space and then write @ SQL script)
Write "exit" in the last line of the script. After running the script, return to the shell.

5,
NULL values in Oracle are processed as Infinity. In fact, null values are not stored at all, but are regarded as Infinity.

In Oracle, the Control Handler NVL (field name, value) replaces the null value with the specified value. If it is not null, the original value is returned.
Example: select (salary * 12) * (NVL (commission_pct, 0)/100 + 1) salary, first_name from s_emp;

Distinct keyword, remove duplicate rows (this keyword will trigger the sorting Operation)
Example: select distinct dept_id, title from s_emp;
The combination of dept_id and title is not unique.
Note: distinct. After the keyword is entered, the fields before "from" will be duplicated.

6. column command --- sqlplus command

Column command column format definition

Column target column name to check whether the format of this class is defined

Column target column name format a... sets the column width.
Column last_name heading 'employee | name' formAT A15
Set question Header
'|' Is a line break.

Column salary justify left format $99,990.00
Define Numeric Display format
Note: If the display format is not met, the data will be displayed "#"

Column salary justify left format $00,000.00
$00,928.00 is displayed, and 0 is used for filling.

Column clear (clear column format definition)

Note: Only the sqlplus command can be abbreviated, And the sqlplus command cannot end with a plus sign.

6. Select Operation

1. order

Sort clause ASC (default, ascending) DESC (descending)
Order by target column name (alias) sorting order (if the sorting order is not specified, the sorting order is ascending by default)

Example: select first_name from s_emp order by first_name;
Select first_name from s_emp order by first_name desc;

Note: Values in ascending order are at the end of the result, and values in descending order are at the beginning of the result.
 
2. where clause

The where clause is used after select... from... to select the desired (qualified) record.

Where is followed by the expressions XXX = XXX, XXX between X and X, XXX in (X, X, X)
Like '...' wildcard Query

Between... and... indicates that the result is between them. between and is a closed interval,
It is equivalent to... <=... and...> = ....
! =, <>, ^ =, All three operators are not equal to, & lt ;=, >=, =, and can be used.
... In (va1, val2 ,...)
Like '...' string wildcard query. '%' indicates multiple characters, '_' indicates one character.
Note: escape usage: like's \ _ % 'escape '\'
... And... indicates that only two conditions are met at the same time.
... Or... indicates that only one of the conditions can be satisfied.
All... is required to meet the conditions.
Not..., it can be reversed with the above conditions.
The null value will affect not in, that is, it is not equal to any value, but the null value is an exception.
... Is null is used to determine whether the value is null.

Note: strings in Oracle are case sensitive.

(1) Pay attention to the data type. The number type is directly written, and the character is '...'. The default format of Date can be '...', only alias
"Included.
(2) select an appropriate operator

VII. Single Row Functions

1. Character Functions

Characters are case sensitive.
Lower-case lower (field name) --- The parameter can be a String constant or a field name
Convert to uppercase upper (field name)
Upper-case initcap (field name)
String concatenation Concat (Field 1, Field 2)
Substring truncation substr (field name, start position, number of characters)
A dual table is used for Function Testing and computation. It has only one record.
String concatenation Concat (...,....)
Returns the substring substr (..., starting position, number of characters)
You can use "-" to extract data from the right to the left. You can extract data from the left to the friends.
Example: Select substr (first_name,-) sub from s_emp; (take the last two)
Select substr (first_name, 2, 2) sub from s_emp; (take the first two)

2. Numeric Functions

Round (data, number of digits after the decimal point)
You can use a negative number to indicate the first digit after the decimal point, that is, the reserved digits, and-1 to indicate the digits (to ten digits ).
Example: Select round (15.36, 1) from dual;
Trunc (data, reserved digits (digits after decimal point)
Example: Select trunc (123.456, 1) from dual;
 
3. Date Functions
Date Format,
Century information in full date format, year, month, day, hour, minute, second.
Default Date Format: Day-month-year dd-mon-rr
Modify the date format of the current session. The date is output according to the specified format.
Alter session set nls_date_format = 'yyyy mm dd hh24: mi: ss ';

Returns the current date sysdate.
Example: select sysdate from dual;
Select sysdate + 1 from dual; get tomorrow's date, plus 1, unit: Day

Date is format-sensitive
Calculate the number of months of months_between (date1, date2)
Add_months (date, number of months) of the specified number of months, the number of months can be negative, and the negative value is minus the corresponding number of months.
Next_day (date, FriDay) on the first FriDay starting from date)
Returns the last_day (date) of the last month)
Trunc (date, 'year, month, day, hour, second ')
Example: select next_day (sysdate, 2) from dual;
Example: select trunc (add_months (sysdate, 1), 'month') from dual;
ROUND ('25-MAY-95 ', 'month') 01-JUN-95
Round ('25-may-95 ', 'Year') 01-Jan-95
Trunc ('25-may-95 ', 'month') 01-may-95
Trunc ('25-may-95 ', 'Year') 01-Jan-95
 
Exercise:
Returns the date of the first day of the next month.
Select round (last_day (sysdate), 'month') from dual;
Select add_months (trunc (sysdate, 'month'), 1 );
 
4. conversion functions between different data types

Convert a date to a character tochar (date, 'date format ')
The date format must be in a valid format. The format is case sensitive 'yyyy mm dd hh24: MI: ss ',
'Year' (the year of the competition), 'mm' (the month in number) 'month' (the month of the competition), 'day' (the day of the week ), 'ddspth' (full spelling of dates) 'yy mm dd'
Example: Select to_char (sysdate, 'yyyy mm dd hh24: MI: ss') from dual;

Convert the character to the number to_number ('...')
 
Convert a number to the character to_char (number, 'fmt'). FMT is a number format.

Convert string to date to_date ('...', 'date format ')
Example: Select to_char (to_date ('1970 11 03', 'yyyy mm dd'), 'dd-month-yy') from dual;