Chapter One Oracle Database Fundamentals

Chapter One Oracle Database Fundamentals

1. About Oracle--database management system
1.1: Database
1.2: Global database name
1.3: DB Instance
1.4: Table Space
1.5: Data file (extension:. dbf)
1.6: Control file (extension:. ctl)
1.7: Log file (extension:. log)
1.8: Pattern and Pattern objects
2. Connect to Oracle Database
2.1 Sql*plus Tools
2.2 PL/SQL Developer tool
3. Configuring the Oracle Database
3.1 Configuring the Listener (LISTENER)
3.2 Configuring a local network Service name (tnsname)
4. SQL language
4.1 Data Definition language (create, change, truncate, delete)
4.1.1//CREATE TABLE
CREATE TABLE STUDENT
(
Stuno CHAR (6) Not NULL,
Stuname VARCHAR2 () not NULL,
Stuage Number (3,0) is not NULL,
Stuid number (18,0),
Stuseat Number (2,0)
);
4.1.2//Delete all rows of the table without logging
TRUNCATE table<tablename>;

4.2 Data Manipulation language (insert, select, Update, delete)
4.2.1 Grammatical angle
4.2.1.1 Select rows with no Duplicates
Keywords: DISTINCT;
4.2.1.2 SQL commands with conditions and sorting
With conditions: WHERE
Sort: Order BY column name ASC or DESC
4.2.1.3 using column Aliases
Keywords: as or spaces
Example: Select Stuname as "name", Stuage as "age" from STUDENT;
4.2.1.4 Creating a new table with an existing table
Syntax: CREATE table<tablename2> as SELECT {* | column (s)} from <tablename1> [WHERE <condition>];
This command can copy data from an existing table to a new table, or you can copy only the selected columns or copy only the structure without copying the records
4.2.2 Skill Angle
4.2.2.1 View the number of rows in a table
Execute statement: SELECT COUNT (1) from STUDENT;--High efficiency
SELECT COUNT (*) from STUDENT;--Low efficiency
4.2.2.2 remove a column that does not have duplicate records
Example: SELECT stuname,stuage from STUDENT
GROUP by Stuname,stuage
Having (COUNT (Stuname | | stuage) &LT;2);
4.2.2.3 Delete a column of duplicate rows (keep a row of records)
//......
4.2.2.4 View the current user all data volume >100 table information
Example: SELECT table_name from user_all_tables a WHERE a.num_rows>1000000;
Where User_all_tables is the data view provided by the system.

4.3 Transaction Control Language (commit, save point, rollback)
4.3.1 commit: (Commit a transaction, save all modifications to the database)

4.3.2 ROLLBACK: (ROLLBACK TRANSACTION, cancel any modifications made to the database)

4.3.3 SavePoint: (Create a storage point in a transaction)
/
4.3.4 ROLLBACK to<savapoint_name>:(Roll back a transaction to a storage point)

4.4 Data Control Language-provide user Access control COMMAND + +

5. SQL operator
5.1 Arithmetic operators
Add: +
Minus:-
By: *
Except:/
5.2 Comparison Operators
equals: =
Not equal to:! =
Greater than:>
Less than:<
Greater than or equal to: >=
Less than equals: <=
Between two values: between ... and
Match the values in the list: in
Match character pattern: like
is empty: Is null
5.3 Logical operators
With: and
Or: OR
Non: not
5.4 Set-fit operator
Union (Union)--returns two queries all non-repeating rows
UNION ALL (combined)--merges all rows selected by two queries, including duplicate rows
INTERSECT (intersection)--returns rows for all two queries
Minus (minus set)--returns only the rows selected by the first query and not selected by the second query.
That is, the rows that appear in the second query result are excluded from the first query result
5.5 Connection operator
| |: Combine two or more strings into a single string, or combine a character with a numeric value

6 SQL Functions (single-line function, aggregate function, analytic function)
6.1 Single-line function
6.1.1 character functions
6.1.2 Date function
Sysdate-System Current date
Example: select Sysdate from dual;--->dual: Virtual table
6.1.3 Numeric functions
6.1.4 conversion function
6.1.1.1 To_char ()
Convert to String type
Example: SELECT to_char (sysdate, ' YYYY ' year "fmmm" month "FMDD" Day "HH24:MI:SS") from dual;
--Output system Current date
6.1.1.2 to_date ()
Convert to date type
Example: SELECT to_date (' 2012-01-12 ', ' YYYY-MM-DD ') from dual;
6.1.1.2 To_number ()
Convert to numeric type
Example: SELECT SQRT (To_number (')) from dual;
6.1.1 Other functions
6.1.1.1 NVL (EXP1,EXP2)--If the EXP1 bit is null, return EXP2, otherwise return EXP1;
6.1.1.2 NVL2 (EXP1,EXP2,EXP3)--If the EXP1 bit is null, return EXP2, otherwise return EXP3;
6.1.1.3 DECODE (value,if1,then1,if2,then2,... Else)-if value is IF1, the value of THEN1 is returned, and if value is IF2,
Returns the value of the Then2, .... Otherwise, return the value of else

6.2 Aggregation Functions
。。。
6.3 Analysis functions
Syntax: function name ([parameter]) over ([partition clause] [sort clause])
6.3.1 Row_number
Returns a unique value that, when the same data is encountered, increments by the order of the records in the recordset
6.3.2 Dense_rank
Returns a unique value that, when the same data is encountered, is ranked the same as the same data
6.3.3 RANK
Returns a unique value that, when the same data is encountered, is ranked the same as the same data, and the rank between the last same record and the next record rank is empty

Chapter One Oracle Database Fundamentals