SQL Basics Section

Oracle Basic data types:
Class 4:
Character Type: string
CHAR (max.), nchar (max 1000, Unicode supported)---> Fixed length
VARCHAR2 (Max 4000), NVARCHAR2 (Max 2000, Unicode supported)---> variable length
Number Type
Include Integers and decimals
Number (valid digit, total number of digits);
FLOAT ()---> Store binary type data, 1-126-bit 0.30103
Date Type: Time
Date: Value range: January 1, 4712 A.D.---A.D. December 31, 9999, can be directly accurate to the second
Timestamp (timestamp: More accurate data, can be accurate to milliseconds)
Other types: Large text, file
BLOB: Max 4G, storing data in binary form
CLOB: Max 4G, stored as a string

Oracle Operators and expressions

Expressions are similar to Java, can be constants, can be variables, or they can be a field in a table

Arithmetic operators (+-*/)
Comparison operators (> >= < <= = <>/!=)
logical operators (and or not)
String Connector (| |)

Copying tables
Delete data from a table
Query with conditions
Export data for a table
Paste an Excel table into Oracle
Export query results to Excel
Creation of sequences

Common keywords
INSERT INTO
Delete
Update
Select---try not to write *
where
Inch
Like (%, _)
ORDER BY
Group by--(Group by enhancement)
Having
Case...when ...
Set
Distinct
Between...and (closed interval, that is, including the preceding number, also including the following number)
All

Common functions
To_char
To_date
MoD
Avg
Sum
Count
Decode
Substr
Max
Min
Trunc
Ceil
Floor
Nvl
Nvl2
Lower
Upper
Concat
? Wm_concat

There are four types of SQL statements:
Data definition Language (DDL), Data Manipulation language (DML), Data Control Language (DCL), and Object Control Language (TCL).

Data Definition Language (DDL)
DDL gives us the ability to create or delete tables. You can also define indexes (keys), specify links between tables, and impose constraints between tables.
Create database-Creating new databases
ALTER DATABASE-Modify databases
CREATE table-Creates a new table
ALTER TABLE-Change (change) database table
drop table-Delete tables
Create index-Creating indexes (search key)
Drop INDEX-Delete indexes

Data manipulation Language (DML)
For users to implement the data append, delete, update, query and other operations.
SELECT-Get data from a database table
Update-updating data in a database table
Delete-Deletes data from the database table
INSERT into-inserts data into a database table

Data Control Language (DCL)
Grant-Authorization
REVOKE-Revoke Authorization
Deny-Deny authorization

Transaction Control Language (TCL)
SavePoint-Set Save point
ROLLBACK-Rollback
Commit-Commit

Oracle Database constraints:
Defining rules
Defines the value to be entered, or the value in the range

Role: ensure integrity and ensure accuracy

1, non-null constraint
When recording a message, if the user name and password are not recorded, then this record is not meaningful, when the table is created by default can be empty
2, PRIMARY KEY constraint
Ensure the uniqueness of the data for each row in the table
Non-empty, unique
There can be only one primary key in a table, but this primary key can be composed of multiple fields (federated primary key, composite primary key)
You can write a key that is set as the primary key when building a table, primary key
Or write a pre-table constraint setting
Constraint primary Key Name primary key (field name 1, field name 2)

3, FOREIGN KEY constraint
The only one that involves a field constraint in a two table
When inserting data, the field from the associated relationship in the table is either an association of a primary table primary key, or a null value

4, UNIQUE constraint (unique)
Guaranteed Uniqueness of field values
Difference from primary key: A unique constraint can be a null value, and a unique constraint can have more than one
Creating a UNIQUE constraint with a statement can only write multiple statements, similar to insert into
5, CHECK constraints
Effect: Make the values in a table meaningful
such as defining a range of values
Checks (Sno>0 and sno<1000)

SQL Basics Section