Oracle Build table interpolation data and more

Management of Oracle's tables:
Naming rules for table names and columns
. Must start with a letter
. Length cannot exceed 30 characters
. Cannot use Oracle's reserved words
. Use only the following characters column_name-z,column_name-z,0-9,$, #等

Data types supported by Oracle

character class:
char fixed length, maximum 2000 bytes.
Example: char (10) If the content is ' little Jay ' then the first four bytes put ' Little Jay ', after adding 6 blanks complement
varchar2 () the maximum length of 4,000 characters.
Example: VARCHAR2 (10) ' Little Jay ', Oracle assigns four characters. This will save space.
Clob (character large object) character type large objects, Max 4G
Char Query the speed of the very fast wasted space, query more data, VARCHAR2 save space
Long stores variable-length character data up to 2GB long
Raw Storage of variable-length character data for unstructured data, up to the longest end of 2KB
Long raw storage of variable-length character data for unstructured data, longest tail 2GB
rowid binary data that stores the physical address of a column in a table, taking up a fixed 10 bytes
Urowid Store binary data representing any type of column address
bfile Storing unstructured binary data as a file outside of the database
Digital Type:
Number (n,m) where the default m represents an integer, range: 10 of 38 to 10 of the 38-time Square
can represent integers, or decimals, number (5,2) indicates that a decimal has 5 valid digits and a 2-bit decimal range: 999.99 to 999.99,number (5) Represents a 5-bit integer range of 99999 to -99999
Float Storage floating point number
Date Type:
Date contains date and time of day and seconds, Oracle default format: January-January -1999 timestamp This is an extension of the oracle9i for the date data type. Can be accurate to milliseconds.
Photo:
blob binary data can be stored picture/sound, 4G, generally speaking, in the real project is not to put pictures and sound really into the database, generally store pictures, video path, if security needs relatively high, then put into the database.
Build Table:
CREATE TABLE table_name (
column1_name type [constraint constraint_def] [primary key] [default Default_def],
column2_name type [constraint constraint_def] [references Table2_name (column2_name)] [default Default_def],
......
);
CREATE TABLE Tb_employee (
pk_employee_id Number (4) primary key,
ename VARCHAR2 (Ten),
Job Varchar2 (9),
Mgr Number (4),
hiredate Date,
sal Number (7,2),
Comm Number (7,2),
deptno Number (4)
);

CREATE TABLE Tb_department (
PK_DEPARTMENT_ID Number (4) primary key,
Dname VARCHAR2 (14),
Loc VARCHAR2 (13)
);

CREATE TABLE Tb_salgra (
PK_SALGRA_ID Number primary Key,
Losal number,
Hisal number
);

INSERT INTO Tb_department
VALUES (' ACCOUNTING ', ' NEW YORK ');
INSERT INTO Tb_department
VALUES ("DALLAS");
INSERT INTO Tb_department
VALUES (+, ' SALES ', ' CHICAGO ');
INSERT INTO Tb_department
VALUES (+, ' OPERATIONS ', ' BOSTON ');

INSERT INTO Tb_employee
values (7369, ' SMITH ', ' Clerk ', 7902,to_date (' 17-12-1980 ', ' dd-mm-yyyy '), 800,null,20);
INSERT INTO Tb_employee
values (7499, ' ALLEN ', ' salesman ', 7698,to_date (' 20-2-1981 ', ' dd-mm-yyyy '), 1600,300,30);
INSERT INTO Tb_employee
values (7521, ' WARD ', ' salesman ', 7698,to_date (' 22-2-1981 ', ' dd-mm-yyyy '), 1250,500,30);
INSERT INTO Tb_employee
values (7566, ' JONES ', ' MANAGER ', 7839,to_date (' 2-4-1981 ', ' dd-mm-yyyy '), 2975,null,20);
INSERT INTO Tb_employee
values (7654, ' MARTIN ', ' salesman ', 7698,to_date (' 28-9-1981 ', ' dd-mm-yyyy '), 1250,1400,30);
INSERT INTO Tb_employee
values (7698, ' BLAKE ', ' MANAGER ', 7839,to_date (' 1-5-1981 ', ' dd-mm-yyyy '), 2850,null,30);
INSERT INTO Tb_employee
values (7782, ' CLARK ', ' MANAGER ', 7839,to_date (' 9-6-1981 ', ' dd-mm-yyyy '), 2450,null,10);
INSERT INTO Tb_employee
values (7839, ' KING ', ' president ', null,to_date (' 17-11-1981 ', ' dd-mm-yyyy '), 5000,null,10);
INSERT INTO Tb_employee
values (7788, ' SCOTT ', ' ANALYST ', 7566, ' 1 September-April -1987 ', 3000.00,null,20);
INSERT INTO Tb_employee
values (7844, ' TURNER ', ' salesman ', 7698,to_date (' 8-9-1981 ', ' dd-mm-yyyy '), 1500,0,30);
INSERT INTO Tb_employee
values (7900, ' JAMES ', ' Clerk ', 7698,to_date (' 3-12-1981 ', ' dd-mm-yyyy '), 950,null,30);
INSERT INTO Tb_employee
values (7902, ' FORD ', ' ANALYST ', 7566,to_date (' 3-12-1981 ', ' dd-mm-yyyy '), 3000,null,20);
INSERT INTO Tb_employee

insert into Tb_salgra
values (1,700,1200);
insert into Tb_salgra
values (2,1201,1400);
insert into Tb_salgra
values (3,1401,2000);
insert into Tb_salgra
values (4,2001,3000);
insert into Tb_salgra
values (5,3001,9999);

SELECT * from Tb_department;
SELECT * from Tb_employee;
SELECT * from Tb_salgra;

drop table table_name;--Delete tables
Rename table_name to table_newname;--rename the table name or use
ALTER TABLE table_name Rename to Table_newname
or CREATE TABLE Table_newname as SELECT * from Table_name;drop table table_name;
ALTER TABLE table_name rename column column_name to column_newname;--modify field names in table
ALTER TABLE table_name ADD column_name newtype;--add field
ALTER TABLE table_name MODIFY COLUMN_NAME newtype;--Modify the type of the field
ALTER TABLE table_name drop column column_name;--Delete field
alter session set nls_date_format= ' YYYY-MM-DD ';--date set to the way the Chinese are accustomed
alter session Set time_zone;--change the time zone

Constraints are used to ensure that database data meets specific business rules.

in Oracle, constraints include: NOT NULL, Unique,primary key,foreign key, and check five, adding constraints
ALTER TABLE table_name MODIFY COLUMN_NAME not null;--non-empty
ALTER TABLE TABLE_NAME ADD constraint constraint_def unique (column_name);
ALTER TABLE TABLE_NAME ADD constraint Constraint_def primary key (column_name);
ALTER TABLE TABLE_NAME ADD constraint constraint_def foreign key (column_name) references R2 (column_name);
ALTER TABLE table_name ADD constraint constraint_def check (condition);
ALTER TABLE table_name DROP CONSTRAINT column_name constraint_def;--delete constraint
ALTER TABLE table_name MODIFY COLUMN_NAME null;--is empty
ALTER TABLE TABLE_NAME DROP CONSTRAINT PRIMARY key cascade;
prohibit constraint:
constraint disable;--When creating a new constraint
ALTER TABLE table_name disable constraint constraint_def;
Allow constraints:
ALTER TABLE TABLE_NAME enable constraint constraint_def;
To obtain constraint information:
select * from user_constraints;

DML (Data Manipulation language):

SELECT * FROM tb_employee;--query
INSERT INTO table_name values (); --Insert data (contents in table)
Delete from table_name where ...; --Delete tuples (contents in table), write log, recoverable
TRUNCATE TABLE table_name;--Delete tuples (contents in table), fast, no log, unrecoverable

UPDATE table_name set column_name where ...; --Update data (contents in table)
UPDATE table_name SET (A1,A2,A3) = (select (A1,A2,A3) from table_name where ...) where ...;

Paging:
RowNum
Classic example:
SELECT * FROM (select Column_name.*,rownum rn from (SELECT * from Tb_employee) column_name where rownum<=10) where RN&G t;=6;
Create a table from the result set, copying part of it:
CREATE TABLE Tb_mytable (pk_id,name) as select Pk_employee_id,ename from Tb_employee;

For row migration:
INSERT into R2 (A1,A2,A3) Select A1,a2,a3 from R1 where ...;

Common SQL Keywords:

distinct;
(=, <, <=, >, >=, <>);--arithmetic operation comparer
in;--contains tests
natural join;--equals =, join. On, INNER join: on
natural left outer join;--equals left outer join. on = right field (+)
Natural right outer join;--equals right outer join. On = left field (+)
Natural Full outer join;--equivalent natural left OUTER join UNION ALL Natural right outer join
Cross join;--crossover connection, also called Cartesian connection
Natural
any ;
All ;
question: How do i show the name, salary, and department number of the employee with a higher salary than all the employees in the Department 30?
Select Ename,sal,deptno from Tb_employee where Sal>all (select Sal from Tb_employee where deptno=30);
or select Ename,sal,deptno from Tb_employee where sal> (select Max (sal) from Tb_employee where deptno=30);--You can use min ( ) or the max () function override
like;--Matching Test
%: denotes 0 to more than one character; _: denotes any single character; the first character is S,like ' s% '; the third character is uppercase S,like ' __s% '; contains s,like '%s% '; with "AB%CD", like ' ab\%cd% ' escape ';
desc tb_employee;--View the structure of the table (describe (description) of the meaning)
order by;
Group By;--group by is used to group statistics on the results of a query
The having;--having clause is used to restrict grouping display results
||; --string Join operator
between and;--Range test, within a range
is null;--null value test
(and, or, not);--Compound condition Query

Oracle Build table interpolation data and more