SQL Basics in Oracle databases

oracle| Data | database first, some concepts of relational databases
　　
1, the value of the primary key can not be changed generally
　　
2. Foreign key: A field that points to another table or to the primary key or unique key of this table. The value of a foreign key must be the same as a primary key, or it will be empty.
　　
3. Database-like: tables, views, sequences, indexes, synonyms, programs (processes, functions, SQL and Pl/sql data) 4, SQL command category
　　
Data Retrieval Search: Select
Data Manipulationlanguage (DML) operation language: Insert,update,delete
Data definition Language (DDL) database definitions language: Create,alter,drop,rename,truncate
Transaction control transaction Controls: Commit,rollback,savepoint
Data Control Language (DCL): Grant,revoke
Execution of the DCL and DDL commands results in an implicit commit, and the uncommitted actions (including the DML command) are committed to write to the log file and written to the data file when appropriate.
　　
　 second, the syntax of SQL
　　
1) connection Number: | |
　　
2) Connect two characters
Eg:select game_card_type_id| | Name from Game_card_type;
　　
3 SELECT DISTINCT dept_id,title from emp: Unique for multiple fields
　　
4) Order by DESC (descending)
ORDER by ASC (Ascending)
　　
5 where column is (not) null
　　
6) Like ' _a% ' _ denotes a character. % representation of how many characters
Like '%x\_y% ' escape ': Display characters including x_y
　　
7 where Table1.column (+) =table2.column
Place the operator on the side of the join where there are no value to join.
　　
8) Join type:
Equijoin: Equality Query
Non_equijoin: Inequality Query
Self: Establish an association with yourself
Out Join:where A.column=b.column (+)
The operators that can be used are: ' = ', ' and ', cannot be ' or ', ' in '
　　
9 If the column used by the COUNT function contains a null value, the null row is ignored.
　　
(a) The difference between where and all
In: equals any number of subqueries
Any: Compared with each value of a subquery
Just one big (small) one.
All: Larger than all the values of the subquery (small)
The!=all function is the same as not
　　
　 Third, Sql*plus environment (you can define the initial parameters in Glogin.sql)
　　
1 The START command is used to execute a stored file, equal to the @
　　
2) The Save command is used to create a file
　　
3 The Edit command is used to invoke the editor to edit the contents of the saved file
　　
4 Change is a sql*plus edit command that converts the old paragraph to a new paragraph on the current line
　　
5) DEL command to delete one or more lines of text
　　
6 The spool command is used to store the query results in an existing file, noting that the difference between the save
　　
7 The Get command is used to write the contents of a file into a SQL block
　　
8 The SPOOL out command is used to send the result of the file to the system printer.
　　
9 Set Pause off/on: Set the page scrolling. Press ENTER to see the next page.
　　
PAGESIZE: Specifies the number of rows displayed per page,
　　
One) long: Sets the maximum width for long data display
　　
FEEDBACK: Sets the maximum value for a query to return records
　　
DESCRIBE: Used to display the structure of tables and views, synonyms, or specify the details of functions and procedures.
　　
Timing: You can see when the statement was executed
　　
Autotrace: You can see the execution plan for SQL, SYSDBA execution/home/oracle/product/9.2.0/sqlplus/admin/ Plustrce.sql script, and must assign the Plustrace role to the execution user. Execution user must run/home/oracle/product/9.2.0/rdbms/admin/utlxplan.sql
　　
16 in various data types, only the default display of the number data type is right-aligned, while Char, date, and VARCHAR2 are left-aligned
　　
QUIT is the Sql*plus command to end a sql*plus dialogue.
　　
) Ttitle:
Ttitle ' Selina ' The result of setting select is Selina
TTI: Shows the status of the Ttitle
TTI off/on
Btitle ' End ' sets the tail of the result
　　
) Column:
　　
A) Column name heading ' name ' Format A15
　　
b) Column ID justify left format 999999
　　
c) Column start_date format A9 null ' not hired '//when the field is empty it is displayed as not hired
　　
d) column: Show all column settings
　　
e) column ColumnName: Show settings for a field
　　
f) Clear column: Clears all column settings
　　
g) Column ColumnName clear: Clears the settings for a field
　　
h) The column above may be replaced by Col. Clear can be replaced with CLE
　　
13) Define Variables
　　
Input variable value by user, this variable can exist in the where, as the whole query statement variables. It can also be followed by the order. A variable to be a field. You can also place a variable as a field or an expression after a select.
　　
Use &&amp: If multiple places refer to this variable ... Input only once
　　
Sql> SELECT empno, ename, Job, &&column_name
From EMP
ORDER BY &column_name;
　　
Accept: Input variable value by user
Accept variable name datatype prompt ' tells the user to enter information: ' Hide
When quoting,:& change name
Define (undefined): Define variable values at the outset
　　
　 Four, function:
　　
1. Character function
　　
Lower: Turn the characters into lowercase
Upper: Turn the characters into uppercase
Initcap: Turn the first letter of the word into uppercase
Concat: Connection character concat (' good ', ' morning ') =goodmoring
SUBSTR (Column\expression, m[,n]) is used to intercept a string, starting at the first m position, and then intercepting the successive N-character portions thereafter, and starting at the end if the M-bit is negative.
Eg:substr (' string ', 1,3) =str
substr (' string ' -3,3) =ing
INSTR (' String ', ' R ') =3
Lpad (sal,10, ' * ') =*******sal
Length: is used to return the number of characters in an expression, and note that the return is.
NVL (expression1, expression2)
The NVL function is used to convert a null value to a real value, such as:
NVL (100/quantity, 0), if the quantity is a null value, the function returns a 0.
If two field types are different, they must be converted.
MIN (): Returns the minimum value ... If it is a character ... A
2. Mathematical function
　　
Round: Rounding
Round (2.566,2) =2.27
Round (45,-1) =50
Trunc: Truncated
Trunc (2.566,2) =2.56
Trunc (45,-1) =40
MoD
MoD (m,n): M-n*flood (m/n) File://flood is an integer
　　
3, Date function:
　　
A) Months_between (DATE1,DATE2): Calculates the number of months between date1 and Date2, can be a decimal can be negative
Months_between (' 01-sep-95 ', ' 11-jan-94 ') =1.9774194
　　
b) Add_months (date,n): Add n months for date, n can only be integers
　　
c) next_date (date, ' char '): Find the next week of date n
Next_date (' 01-sep-95 ', ' FRIDAY ') =08-sep-95
　　
d) last_day (date): Finds the last day of the date month.
　　
e) Rount (date): Rounding the dates
　　
f) Rount (25-may-95 ', ' MONTH ') =01-jun-95
　　
g) Rount (25-may-95 ', ' year ') =01-jan-95
　　
h) trunc (date): truncating dates
　　
i) trunc (25-may-95 ', ' MONTH ') =01-may-95
　　
j) trunc (25-may-95 ', ' year ') =01-jan-95
　　
K the difference between RR and yy in the date, the RR format is similar to a rounding operation, and the YY format is similar to the interception of the date
RR YY
1995 27-OCT-95 1995 1995
1995 27-OCT-17 2017 1917
2001 27-OCT-17 2017 2017
2001 27-OCT-95 1995 2095
Usage: Select To_char (sysdate, ' YY ') from dual;
Select To_char (to_date (' 95-11-27 ', ' rr-mm-dd '), ' YYYY-MM-DD ') from dual;
Select To_char (to_date (' 95-11-27 ', ' yy-mm-dd '), ' YYYY-MM-DD ') from dual;
　　
4. Conversion function
To_char:
To_char (date, ' FMT '): FM prefix used to remove the end of the null character or 0
To_char (Total, ' fm$999999 ')
　　
If you want to turn into a $0.25, it's written fm$9999990.99.
　　
You can convert dates to characters
To_char (log_time, ' mm/yy ')
To_char (lot_time, ' FMDD '; MM yyyy ')
　　
The specific format is as follows
HH24:MI:SS AM-----------15:24:32 pm
DD ' ' MONTH-----------of May
Ddspth------------------------Fourteenth
DDSP--------------------------Fourteen
Ddth---------------------------4th
YYYY-----------------------1978
MM-----------------------------12
MONTH-------------------------May
　　
5. Group function avg,count,max,min,stddev,sum,variance
　　
　 v. Data dictionary
　　
User tables: Created by the user, containing the user's content
　　
Data dictionary: Set up by the system, containing information about the database
　　
Prefix:
User_: Created by the user, displaying all objects owned by the user.
　　
All_: The name of the object that the user can access by an authorized user.
　　
Dba_: accessed by a person with DBA authority, displaying all objects of the database.
　　
v$: accessed by someone with DBA authority, displays data dictionary database server performance information. Typically, the DBA is used to display the statistics and dynamic performance tables for the system.
　　
Data dictionary
　　
The Dictionary data Dictionary provides a description of the data dictionary tables and views that users can access.
User_objects displays the objects owned by the user.
User_views displays the views that are owned by the user.
User_tables Displays the table owned by the user.
All_tables Displays the tables that the user can access.
All_views displays the views that users can access.
User_catalog displays all the tables, views, synonyms, and sequences owned by the user.
User_cons_columns displays the columns with constraints.
Dba_cons_columns displays all tables, views, and synonyms in the database.
User_tab_privs_made: This user assigns a table to another user to give permission
USER_TAB_PRIVS_RECD: A table that other users give permission to the user
User_col_privs_made: This user assigns a field to another user to give permission
USER_COL_PRIVS_RECD: A field that other users give the user permission to
Role_sys_privs: What system permissions are assigned to role
Role_tab_privs: What about the table permissions assigned to role
User_role_privs:role and user's corresponding table
　　
Commonly used tables
　　
User_objects: The User Object table (all objects that store users) stores the following types of data
Selina Sql>select distinct object_type from user_objects;
INDEX
Lob
PACKAGE
PACKAGE body
PROCEDURE
SEQUENCE
Synonym
TABLE
TRIGGER
VIEW
User_catalog: User Class table, which stores the following types of data
Selina Sql>select distinct table_type from User_catalog;
SEQUENCE
Synonym
TABLE
VIEW