directory
I. Preface:
1. Why study databases? (Two concepts)
Relational database:
2.Oracle cognition (a cognition)
3. Three Nouns:
4. Four kinds of objects
5. Five categories
6.Oracle:
Activation:
Select usage details
1.select :
2. The SELECT statement performs arithmetic operations on all values of the specified column.
3. Differentiate the columns in the query
Use 4. | | enables multiple columns and special columns or the value of the string into a column for display
5. Null is worth replacing
6. Use a distinct keyword to display only one duplicate record in the display
7. Up sqlplus command
8.select id,last_name,first_name, salary, dept_id
from s_emp;
Third, sorting, conditional restrictions
1, Order by clause
2. Use of the Where clause
\
I. Preface:
1. Why study databases? (Two concepts)
Database concepts:
A database is a warehouse that organizes, stores and manages data according to data structure.
Database, in simple terms, is itself can be regarded as electronic file cabinet — the storage of electronic files, users can add, intercept, update, delete and other operations on the data in the files.
It stores data together in a way that can be shared by multiple users, features minimal redundancy, and is a collection of data separate from the application.
\
Data model is the storage mode of data in database and the foundation of database system
The data model has gone through:
1. Hierarchical model: Hierarchical model is one of the earliest models used in database system. Its data structure is a “directed tree”.
2. Network model: The network model uses the network structure to represent the connection between entities. Each node in the network represents a record type, and links are implemented using link Pointers. Mesh model can represent the relationship of multiple subordinate relationships, and also can represent the cross relationship between data, namely the horizontal relationship and vertical relationship between data, which is an extension of hierarchical model. The network model can easily represent all kinds of connections, but its structure is complex and its algorithm is difficult to be standardized
3. Relational: In the relational model, the basic data structure is two-dimensional table, and there is no link pointer like hierarchy or network. The relationships between records are represented by attributes of the same name in different relationships
Advantage:
Single data structure
Relationships are normalized and built on a strict theoretical basis
Simple concept, easy to operate.
Composition:
1. Single data structure — relationships
2. Set of relational operations
3. Relationship data integrity, accuracy and consistency.
六四屠杀** Relational database:
Relational databases can simply be understood as two-dimensional databases, with tables formatted like Excel, with rows and columns.
The so-called relational database refers to a database that uses a relational model to organize data. Relational model was first proposed by IBM researcher Dr. E.f. Codd in 1970. In the following decades, the concept of relational model has been fully developed and gradually become the mainstream model of database architecture. To put it simply, a relational model is a two-dimensional table model, and a relational database is a data organization composed of two-dimensional tables and their relationships.
There are many relational databases:
Mysql, sqlserver, oracle, db2,sybase, etc.
Some basic concepts in relational databases:
1. Relationships: A relationship corresponds to a two-dimensional table, and the two-dimensional table is the name of the relationship. Each table consists of rows and columns
2. Tuple: A row in a two-dimensional table is called a tuple. You can manipulate tuple data by using Structured Query Language (SQL)
3. Attribute: An attribute value (component) in a tuple
4. Codes: If there is an attribute in a relationship whose value can be used to uniquely identify a row, these attributes are called the key or code of the relationship
5. Primary key (primary code) : An attribute selected from several candidate codes that uniquely identifies a row of data.
6. Foreign key: used to logically associate data information in multiple tables.
2.Oracle cognition (a cognition)
Oracle is the name of a company (Oracle Corporation), one of the few in the world, is a very large international giant IT company, is the world’s largest enterprise software company, headquartered in Redwood Beach, California, the United States. It entered the Chinese market in 1989. In 2013, Oracle passed IBM to become the world’s second-largest software company after Microsoft
The best-known products are DB, Database,RDBMS, and relational database systems.
The world’s largest database supplier, Shanghai R&D center, wujiaochang, close to Fudan University. Oracle database software,
The world’s second largest ERP provider, large enterprise internal management system, including what human resources management and so on
\
Oracle was the first and most successful product of Oracle Corporation. The development of Oracle went through a long process, and in June 1997, the eighth version of Oracle was released. ORACLE8 supports object-oriented development and new multimedia applications, and this release also lays the foundation for supporting Internet, network computing, and more. At the same time, this version began to be able to handle large numbers of users and large amounts of data simultaneously.
\
ORACLE officially released ORACLE 8I in September 1998. “I” stands for Internet, and this release adds a number of features designed to support Internet. This release provides full Java support for database users. ORACLE 8I became the first database to fully integrate a native Java runtime environment.
\
And then we have 9i, 10g, g for grid. The biggest feature of this release is the addition of grid computing, then 11G, and now 12C, where C stands for Cloud, which is a very hot concept right now.
\
3. Three Nouns:
SQL: Structured query statement, operating oracle database language
Sqlplus: terminal provided by Oracle that can input SQL and display SQL execution results
Pl/SQL: A programmatic SLQ statement that adds certain logical operations to the SQL statement, for example, if for… , make it a SQL block, complete certain functions
\
4. Four kinds of objects
Table: A table consisting of rows and columns. A column is also called a field. Each row contains a complete piece of data in the table.
View: A partial or complete mapping of one or more tables, like a table looking into a mirror. The virtual image inside the mirror is the view
In addition to the table and View objects, the Oracle database supports the following four objects
Sequence of the sequence:
Index: improves data access efficiency
Synonym: synonym, which facilitates operations on objects
Program Unit: A program unit, an object for PL/SQL operations
5. Five categories
SQL:
Data Retrieval: Data retrieval
select
DML: Data manipulation language (row-level manipulation language) : It manipulates a column of data in a table
insert update delete
DDL: Data Definition Language (table level manipulation Language) : the content of operations is tables (objects)
create alter drop truncate rename
Transaction Control: Transaction control
commit rollback savepoint
DCL: Data control language
grant revoke
\
Delete, truncate difference:
Delete: Deletes one or more records from a table without returning the initialization status of the table
Truncate: Clears the table and returns the initial state of the table
\
6.Oracle:
六四屠杀六四屠杀Activation:
1. Win +R –> CMD —–> sqlplus “/as sysdba” // Log in as sysdba.
Win +R –> CMD —–> sqlplus username/password // log in as the specified username and password
win+R —> cmd —–> sqlplus
Enter the user name and password as prompted
Go to the installation directory and open the SQLPlus terminal.
****2. Create a user.
create user username identified by password
3. Grant permissions:
Grant resource, connect to user;
resource:
create trigger
create sequence
create type
create procedure
create cluster
create operator
create indextype
create table
connect:
create session
4. Switching users:
conn username/password
5. Import test data:
Because the default language environment of the data file is English, the current Windows system is Chinese, the time display in Chinese and English is inconsistent, the import will fail, you need to modify the language environment first
alter session set nls_date_language=english;
alter session set nls_language=english;
@ e:\oracle\summit2.sql
start e:/oracle/summit2.sql
****6. Query table structure:
desc table_name;
Select usage details
\
1.select :
Grammar:
select [distinct] {*,column [alias],… }
from table
\
Note: [] enclosed contents represent optional
* represents all columns
The DISTINCT keyword can only be followed by the SELECT keyword
\
The column to be queried is specified after the SELECT clause
The from clause is followed by the table to be queried
\
My name is first_name.last_name;
2. The SELECT statement performs arithmetic operations on all values of the specified column.
Grammar:
Select col_name operator number
from tb_name;
\
Note: The SELECT statement never modifies the raw data.
\
3. Differentiate the columns in the query
Grammar:
select old_column [as] new_column_name
from tb_name;
\
\
Use 4. | | enables multiple columns and special columns or the value of the string into a column for display
Grammar:
select col_name||’spe_char’||col_name
from tb_name
‘spe_char’: Use this syntax if the value of a column is to be displayed concatenated with a special string.
For example: select ‘the title of’ | | last_name | | ‘is’ | | the title as the message from s_emp;
\
\
5. Null is worth replacing
Grammar:
select nvl(col_name,change_value)
from tb_name;
\
Nvl2 (col_name, not null display value, null display value);
6. Use a distinct keyword to display only one duplicate record in the display
Grammar:
select distinct col_name,col_name…
from tb_name;
\
Note 1: A DISTINCT keyword can only be placed after a SELECT keyword
For example, select ID and distinct title
from s_emp;
This statement is syntactically incorrect!!!!!
Note 2: If there are multiple columns behind a distinct keyword, it indicates that the multiple columns are combined to be de-duplicated. That is, a duplicate record is considered only when the values of multiple columns are the same.
The test table:
id id2
1 2
1 3
2, 4
3, 4,
3, 4,
select distinct id,id2
from test;
The command output is as follows:
id id2
1 2
1 3
2, 4
3, 4,
\
\
7. Up sqlplus command
A: Appends content to the command line of the current operation
a test
C: Modify the content in the cli of the current operation
c/old_char/new_char
Clear buffer: command to clear the current cache
Del: Deletes the current operation line
Del line_num specifies the row to delete
I: insert the next line of the current operation command line
L: View the cache command
L line_num: Displays the specified command line
N text: replace the entire NTH line
! : Terminal command —— in Linux
! The clear: screen clearing / / /
$: the terminal command is followed
$CLS ———> Windows
/: Executes the cache SQL command
\
Save file_name: Save the cache command to file_name (append replace)
Get file_name: Extracts the contents of the file to SQLplus
Start and @file_name: Execute the SQL commands in the file
Edit file_name: Edit the file using the vi editor set by the terminal (notepad for Windows)
Spool file_name Saves the following SQL statement and the result of the SQL run to a file (append)
sql1
result1
sql2
result2
.
Spool off Disables the spool function
Exit, exit
\
8.select id,last_name,first_name, salary, dept_id
from s_emp;
\
The result is not pretty. Column makes our display interface look good.
\
colu last_name format a15;
colu first_name format a15;
\
Does Column change the data in the table? No, it just changes the display. Is it an SQL command? No, it’s a SQLplus command. Now let’s see what else it does.
\
COLUMN last_name HEADING ‘Employee|Name’ FORMAT A15
. Alias for the Employee for the last_name | Name, vertical bar represents a newline.
.a15 means fifteen bytes long, and a dash is one byte long
\
COLUMN salary JUSTIFY LEFT FORMAT $99,990.00
. Salary JUSTIFY LEFT: Just change column name to display LEFT
.format 99,990.00: Controls the display FORMAT by adding the preceding 99,990.00: “, “is the delimiter.0 or 9 represents the number (wildcard).0 represents the replacement alignment value.
\
COLUMN start_date FORMAT A8 NULL ‘Not hired’
. If the start_date value is empty, ‘Not Hired’ is displayed;
Format a8 or a10;
NULL ‘Not Hired ‘is a little different from NVL, which requires a type match
\
Column displays all column formatting Settings
Column last_name Displays Settings for the last_name column
Column last_name clear Deletes the formatting of the last_name column
Clear column Clears the formatting of all columns
\
Column The formatting of a Column that is not specific to a table.
\
ex:
1234 column 99.99 – > # # # # # # / / can’t display error, just shows # # # #
Column columName Displays restrictions on columns
\
Third, sorting, conditional restrictions
1, Order by clause
1) The Order by clause is in the whole
Position in select statement:
Always last
2) Order by
Column name, column alias, expression,
List after the select keyword
Order (column number);
3) Order by
That means sort by the first column,
If the first column has the same value, press again
The second column is sorted, like the first two columns
If all values are the same, sort by the third column…
4) ASC table ascending order, can be omitted.
DESC Table in descending order.
5) Null value is always the largest.
When sorting in ascending order, null values are placed last
When sorting in descending order, the null value comes first
\
\
2. Use of the Where clause
Grammar:
select col_name,…
from tb_name
Where col_name comparison operation expression
Logical operator
Col_name Comparison operation expression
1) Function: filter the data set returned by SQL statements;
2) Position: immediately after the from clause
3) Content: it consists of one or more qualification conditions, which consist of expressions, comparators and literals.
4) All strings and dates are enclosed in single quotes. Numeric values do not need single quotes.
Oracle has a specific format for dates, ‘DD-mon-yy’,
Otherwise as a string.
5) Several common operators:
1. Logical comparison operators
< >= <=! =
SQL comparison operators
-Serena: Between and what
In (list): in a list
Like: fuzzy query, which is used when the value is not an exact value
A wildcard is a symbol that can replace anything
% : wildcard 0 to multiple characters
_: If and only if one character is wildcard
Escape character:
The default is to use the escape symbol when you specify it. Only the following character can be escaped
Between 1 and 4: includes the start and end values. Limit the content to 1 through 4.
In (1,2,4): limit the content to 1,2,4.
3. Logical operators
Used when there are more than one condition
And: indicates logic
Or: or logic
Note: and logic is higher than or logic
Not: it is not logical