Oracle learns the next day

Common data types for Oracle databases
All Oracle data types are available in 26 categories

Char
Fixed-length string type length is invariant, for example: No char (10) If the value of the deposit is less than 10 characters, the other bits are also occupied
The default length is 1 max length is 2000 bytes GBK encoded 1000 man
Occupy more space, but the query efficiency is high

Varchar2
Edge length String type variable length For example: the length of the definition is 100 bytes, the value is 80 bytes, then the actual consumption is 80 bytes
VarChar is not recommended for use in Oracle, and it is highly recommended that you use VARCHAR2
You must specify a length of 1-4000 bytes for it to take into account the encoding problem
Oracle uses GBK encoding a Chinese account of two bytes, English, and the number of one byte
Less space to use, but less efficient to query

Number
Numeric type number (All_len,point_len)
First parameter: The number of digits of the entire number
The second argument: The decimal point, the right is a positive number, the left is a negative number, 0 is no decimal places
123.89number123.89
123.89number (3) 124
123.89number (6,2) 123.89
123.89number (6,1) 123.9
123.89number (4,2) exceeds precusion (valid bit is 5,5>4) out of range
123.89number (6,-2) 100
.01234number (4,5). 01234 (valid bit is 4)
.00012number (4,5). 00012
.000127number (4,5). 00013
.0000012number (2,7). 0000012
.00000123number (2,7). 0000012

Date
Functions (methods) for storing data in a database that need to be formatted
Default format: Dd-mm-yy
Select Sysdate from dual;
Search Result: 30月-September-15
To_date (' deposited value ', ' deposited in the format ');
2015-12-12 12:12:12YYYY-MM-DD HH24:MI:SS
To_date (' 2015-12-12 12:12:12 ', ' yyyy-mm-dd HH24:MI:SS ');

CREATE TABLE Test_date (name char (3), Test_time date default sysdate);

To_char ();
Select To_char (test_time, ' yyyy-mm-dd HH24:MI:SS ') Tochar_time from test_date where name= ' 123 ';

Timestamp

CLOB is used to store single-byte character data for storing extra-long text such as: novels, blogs

BLOBs are used to store binary data storage for video, image, audio

-------------------------------------------------------------------------------------------------------

Constraints
Using constraints when creating tables
is to impose a kneeling condition on the table to ensure that the database satisfies the business rules. can guarantee the integrity of the data
When DML or DDL operations are performed on a table, the database system refuses to do this if the operation causes the data in the table to violate the constraints or rules

When you define a constraint, the system automatically generates a name for the constraint if it is not given an explicit constraint name
Oracle strongly recommends that constraints be named when creating constraints

Classification of constraints:

Non-null constraint (NOT NULL)
Constraint field (column) cannot be a null value

Unique constraint (unique)
This column or the values of the columns defined in each row in the table cannot be identical or unique

PRIMARY KEY constraint (PRIMARY key)
Unique identity table for each piece of data (each row of records) cannot be null cannot be duplicated

FOREIGN KEY constraint (FOREIGN key)
Used to maintain the integrity of references between tables and primary tables (foreign KEY constraints are not recommended)

condition, check constraint (checked)
This constraint is met in each row of the column in the table.

Naming rules:
Constraint Name Short _ indicates _ column name
Non-null constraint nn_tablename_col1
Unique constraint Uk_tablename_col1
PRIMARY KEY constraint Pk_tablename_col1
FOREIGN KEY constraint Fk_tablename_col1
condition, check constraint ck_tablename_col1

CREATE TABLE C_test (
ID Number (6),
Name VARCHAR2 (30),
Gender Char,
Age Number (3),
Birthday date,
Constraint pk_c_test_id primary KEY (ID)
);
Query the constraint name of a table under a user (query under DBA role)
Select Constraint_name from dba_constraints where owner= ' SCOOT ' and table_name= ' EMP ';

Querying table constraints under the current user
Select Constraint_name,constraint_type,search_condition,r_constraint_name
From user_constraints where Table_name=upper (' emp ');

Delete a FOREIGN KEY constraint
ALTER TABLE TableName
Drop constraint fk_tablename_col1;
--------------------------------------------------------------------------------------
Table structure Operations:
Modify Table Name:
Rename TT to TTT;
To add a table field:
ALTER TABLE TTT add gender char default ' M ';
table field Rename:
ALTER TABLE TTT Rename column name to username;
To modify a table field:
ALTER TABLE TTT Modify (name VARCHAR2 () default ' Owen ');

Oracle functions
Operator:
Arithmetic operator +-*/
Select + A from dual;
Join Operator | |
Select ' Jack ' | | ' Love ' | | ' Rose ' from dual;
Comparison operators > = >= < <=! = between is null in
SELECT * from the TT where username like ' j% ';
Any value that is evaluated with NULL is NULL

ASCII (' a '); returns the corresponding encoded value according to the character
Chr (coded value); returns the corresponding character
concat (field name 1, field Name 2) connects two strings
Initcap (field name) returns a string and capitalizes the first letter of the string
Length (field name) returns the value of the field
Lower (field name) returns all lowercase of a string
Upper (field name) return field value all uppercase
Select Lower (Initcap (username)) Low,upper (username) up from TT;

LTrim (field name) clears the empty string to the left of the field value

RTrim (field name) clears the empty string to the right of the field value

Substr (the original string, starting with the first few characters, how many to intercept)
Select substr (' abcdefghijklmn ', 1,3) from dual;

Replace (the original string, the string to be replaced, the string to replace)
Select replace (' He love rose! ', ' he ', ' I ') from dual;

Trim (' character to be removed ' from ' target string ')
Select Trim (' s ' from ' strings ') from dual; Subtract all the characters that need to be removed from both sides.
Select Trim (Leading ' from ' strings ') from dual; Subtract all previous characters that need to be removed
Select trim (trailing ' from ' strings ') from dual; minus all the characters that need to be removed from the back.

ABS (field name) returns the absolute value of a number
Select ABS (123) Abs1,abs ( -1234) ABS2 from dual;

Floor (field name) removes the decimal digits and returns an integer (not rounded)
Select Floor (1234.567) from dual;

Ceil (field name) converts a decimal number to an integer (with decimals on + 1)

MoD (dividend, divisor)
Select mod (10,3) m1,mod (3,3) m2,mod (1,3) m3 from dual;

Power (original number, power)
Select Power (2,3) from dual;

Round (field name) rounding Decimal
Select Round (55.5) from dual;

Trunc (field name) directly intercepts the integer part (not rounded)
Select Trunc (55.5) from dual;

To_number ()

Show autocommit; display auto-commit status
Set autocommit on/off, turn on/off autocommit
commit; commit manually

Rollback, rollback (autocommit off), Undo previous action (uncommitted status)

----------------------------------------------------------------------------------------
Date Common functions
Last_day (date) returns the last day of the month on which date is
Select Last_day (sysdate) from dual;

Add_months (date,i) returns date plus I month Date value
I: Take positive integer decimal: Subtract an integer and then calculate a negative number: minus the date value of I month
Select Add_months (sysdate,1) from dual;

Months_between (Date,date_) returns two date intervals how many months
The result may be a negative number or a decimal number (you can carry it directly with the Ceil function)

Next_day (date, week) returns the date of the next week of the date data, such as 4, which is the date of the next Thursday
Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday
Select Next_day (sysdate,1) from dual;

least (); Take the minimum value
least (1,100,2,31);
Greatest (); take the maximum value
Greatest (1,100,2,31);

Extract (Year/month/day from date) extracts the data type specified by the parameter from the parameter date
Select Extract (year from sysdate) from dual;

Null and any value that the operation returns is null
SELECT * TableName where column1=null;//for null cannot be compared like this
SELECT * TableName where column1 is null;
SELECT * TableName where column1 is not null;
Null value function
NVL (col_null,col_not_null) converts a null value to a non-null value processing, keep the type of two arguments consistent
For example: Select SAL,COMM,SAL+NVL (comm,0) as salary from EMP;

NVL2 (col_null,col_is_null,col_is_not_null) turns a null value into an actual value, acting a bit like a three-mesh expression
If it is null, turn into col_is_null, if not null, turn into Col_is_not_null
For example: Select SAL,COMM,SAL+NVL2 (comm,0,1000) as salary from EMP;

Oracle learns the next day