A little knowledge of Oracle Database

--Query statement: SELECT Column name/wildcard/column alias/expression from table name (decoration/restriction statement)
SELECT * from tab;
Select Tname columns specified from tab;--
Select Tabtype from tab;
Select Tname "T-m" from tab;--new column alias
Select 3+4 from dual;--virtual table (only one row)
Select 3+4 as "he" from dual;
--employees: Employee table
Desc EMPLOYEES;--DESC: Shows the structure of a table (column name, strong constraint, column data type)
SELECT * FROM Employees;
--null:1. Participate in the operation, the whole is null
--NVL function: NVL (column name, value)
Select (1+commission_pct) *salary*12 as "annual salary" from employees;
--null value is used to replace NULL operation
Select (1+NVL (commission_pct,0)) *salary*12 as "annual salary" from employees;
Select Commission_pct, (1+NVL (commission_pct,0)) *salary*12 as "annual salary" from EMPLOYEES;
--nvl2 function: Nvl2 (column name, value 1, value 2) is null when display value 2, NOT NULL, display value 1
Select COMMISSION_PCT,NVL2 (commission_pct, ' Commission ', ' no Commission ') from EMPLOYEES;
--Remove duplicate rows within the collection: DISTINCT
SELECT DISTINCT first_name from EMPLOYEES;
--Double row query (column 1 column 2 cannot be duplicated)
--order by (sort, default from small to large ASC (desc opposite)) (sorted column names can no longer appear in the statement)
Select Employee_id,salary from Employees;
Select Employee_id,last_name,salary from Employees order by salary;
Select Employee_id,last_name,salary from Employees order by salary Desc;
Select Employee_id,last_name,salary as "Sal" from Employees Order BY "Sal" DESC;
--sequential number of each column in the SELECT statement, starting with 1
Select Employee_id,last_name,salary from Employees ORDER by 3 DESC;
--Conditional clauses: Finding information for a specific ID employee
--where conditions
SELECT * FROM Employees where employee_id = 100;
-Not equal to <>
SELECT * FROM Employees where employee_id <> 100;
--between value 1 and value 2: Gets data between two values.
SELECT * FROM employees where employee_id between and 107;
--in (value 1, value 2, value 3 ...): Get a specific set of data/meet any one
SELECT * FROM Employees where employee_id in (100,105,200);
--is null: Determines whether it is empty (is null/is not null)
SELECT * FROM Employees where commission_pct is Null;
--not in (): a specific value is not selected
SELECT * FROM employees where employee_id not in (100,200,107);
---logical operator and (satisfies all) or (satisfies a line) not (selected cannot be selected) operation with parentheses
SELECT * FROM Employees where employee_id = + employee_id = 200;
SELECT * FROM Employees where employee_id = or employee_id = 200;
SELECT * FROM employees where employee_id not in (200,100);
--fuzzy query:% instead of one or more characters (case sensitive)
--whether it contains
Select last_name from employees where last_name like '%m% ';
--In the first place
Select last_name from employees where last_name like ' M% ';
--Find the second-ranked
Select last_name from employees where last_name like ' _m% ';
--Find the second-lowest
Select last_name from employees where last_name like ' m_% ';
--reusability of substitution variable:& (filter) code
Select Last_name,salary from employees where salary >= ' & Salary ';
--The connection of strings | |
Select ' A ' | | ' B ' from dual;
Select First_name| | ' ' | | Last_Name from EMPLOYEES;
Select First_Name as "surname", last_name as "name", first_name| | ' ' | | Last_Name as "name" from EMPLOYEES;
--Script run (. sql): The path where the @+ file resides
@e:\\a.sql;
******************************************************
--Single-line function: Action on one row of records
--length: Seeking character length
Select Length (' Let the past all follow the Wind C ') as "length" from dual;
--The name length of the employee in the statistics staff table, changed to name long
--lower Upper Initcap
--Uppercase to lowercase
Select Lower (' ADCDEFG II ') from dual;
--Lowercase to uppercase
Select Upper (' ABCDEFG II ') from dual;
--initcap: initial capitalization, remaining lowercase (kanji ignored)
Select Initcap (' Shout CAJPPJPPDSIHOASD ') from dual;
--concat (","): Character connection
Select ' A ' | | ' B ' from dual;
Select Concat (' AA ', ' Kanji ') from dual;
--substr (', start,end): String intercept (' String ', ' intercept length ', ' intercept length ') (Intercept length: positive from left to right, negative to opposite)
Select SUBSTR (' ABCDEFG ', 2,3) from dual;
--instr (' Hellowword ', ' W ') (the position within the string to find the occurrence)
Select InStr (' Let the past be with the Wind C ', ' toward ') from dual;
--r/lpad (salary,10, ' * ') (padding left and right)
Select Lpad (salary,10, ' * ') from employee;
--replace (' Jack and Jue ', ' j ', ' BL ') (alternative)
Select replace (' Let the past all follow the Wind C ', ' toward ', '?? ') from dual;
--trim (' H ' from ' Helloword ') finds the given character and truncates it (only the beginning and end characters are truncated)
Select TRIM (' Wind ' from ' Let bygones be the Wind ') from dual;
--Left section
Select LTRIM (' Xyxyxyxyxylvxyxyxyxyxy ', ' XY ') from dual;
--Right cut
Select RTRIM (' Xyxyxyxyxylvxyxyxyxyxy ', ' XY ') from dual;
***************************************************************
--Numeric functions
--Rounding and truncation, taking surplus
--round: Rounding (positive, decimal). Negative number, rounding digit)
--15.2
Select Round (15.193,1) from dual;
--15
Select Round (15.193,0) from dual;
--15
Select Round (15.193) from dual;
20th
--trunc: Intercept
--15.1
Select Trunc (15.193,1) from dual;
--15
Select Trunc (15.193,0) from dual;
--15
Select Trunc (15.193) from dual;
--10
Select Trunc (15.193,-1) from dual;
--mod: Take remainder
--1
Select mod (16,3) from dual;
******************************************************************
--Date function sysdate
--Get the current time
Select Sysdate from dual;
--add_months: Add a given number to a given number of sun and moon
Select Add_months (sysdate,6) from dual;
--next_day: Calculate the number of months next week and four
Select Next_day (sysdate, ' Thursday ') from dual;
--last_day: The last day of the current month
Select Last_day (sysdate) from dual;
--Calculate date format data (numbers represent days)
Select sysdate+1 from dual;
--String representing date
Select Add_months (' 1 August-June -12 ', 6) +20 from dual;
--months_between (DATE1,DATE2): Number of months difference between two dates (before minus)
Select Months_between (sysdate, ' 2 June-April -16 ') from dual;
--round: Rounding of dates
--month: See days greater than 15, number of months plus one
Select round (To_date (' 2 June-June -17 '), ' month ') from dual;
--year: See the number of months greater than 6, the number of years plus one
Select round (to_date, ' year ') from dual;
--day:/dd by the week:/hh by the hour: see Minutes
Select Round (Sysdate, (' 1 June-June -17 12:00:59 ', ' dd-month-yy hh24:mi:ss '), ' DD ') from dual;
--trunc: Date interception (regardless of days/months)
Select Trunc (sysdate, ' month ') from dual;
Select Trunc (Sysdate, ' year ') from dual;
*************************************************************************
--Conversion function
--implicit conversion of data types
--string conversion to numeric type
Select salary from employees where salary > ' 12000 ';
--string conversion to date type
Select Hire_date from Employees where hire_date> ' January-May-05 ';
--to_char: Converts a numeric type to a string of the specified format
Select salary from Employees;
--0: Fill-in
Select To_char (Salary, ' l00,000 ') from employees;
Select To_number (' 2 ') from dual;
--to_char: The date type is converted to a string of the specified format
Select Last_name,hire_date from Employees;
Select Last_name,to_char (hire_date, ' Yyyy-mm-dd hh:mi:ss ') from employees;
--to_date: The date type is converted to a string of the specified format (hh24:24 hours) (Month:6 month/mm:06)
Select To_date (' 201 July-June -29 19:28:28 ', ' yyyy-month-dd hh24:mi:ss ') from dual;
***************************************************************************************
--Multi-table query
DESC employees;
DESC departments;
DESC locations;
Desc;
Select last_name from Employees;
Select Department_name from departments;
--Descartes Product
Select Last_name,department_name from Employees cross join departments;
--Equivalent connection (you can use the new name. Column name/Once you have modified the new name, you must use the new name)
Select Last_name,department_name from employees,departments where employees. employee_id = departments. department_id;
Select E.last_name,d.department_name from Employees e,departments D where e.employee_id = d.department_id;
--natural Join: Natural connections (all similar parts)
Select E.last_name,d.department_name from Employees E natural Join departments D;
--Create a table
CREATE TABLE "GRADES"
("LEV" VARCHAR2 (10),
"Low" number (6,0),
"HIGHT" Number (6,0)
);
INSERT into GRADES VALUES (' A ', 1000,2999);
INSERT into GRADES VALUES (' B ', 3000,5999);
INSERT into GRADES VALUES (' C ', 6000,9999);
INSERT into GRADES VALUES (' D ', 10000,14999);
INSERT into GRADES VALUES (' E ', 15000,24999);
INSERT into GRADES VALUES (' F ', 25000,40000);
Commit
DESC grades;
SELECT * from grades;
--Unequal price connection (value partitioning)
Select E.last_name,e.salary,f.lev from EMPLOYEES e,grades F where e.salary between F.low and F.hight;
--Self-connection of the table
--(normal situation)
Select Manager_id,last_name from EMPLOYEES where last_name = ' Jones ';
Select Last_Name from EMPLOYEES where employee_id = 123;
--(self-connected)
Select E1.last_name,e2.last_name from EMPLOYEES e1,employees E2 where E1. manager_id = E2. EMPLOYEE_ID and E1. last_name = ' Jones ';
--Morning ↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑
--the position of the left outer link (+) is on the right side of the equals sign
Select e.last_name,d.department_id, d.department_name,e.salary from Employees e,departments D where e.department_id = d. DEPARTMENT_ID (+);
Select e.last_name,d.department_id, d.department_name,e.salary from Employees E left OUTER joins departments D on (D.depart ment_id = e.department_id);
--Right outside link
Select e.last_name,d.department_id, d.department_name,e.salary from Employees e,departments D where e.department_id (+) = d.department_id;
Select e.last_name,d.department_id, d.department_name,e.salary from Employees e right OUTER JOIN departments D on (D.depar tment_id = e.department_id);
--Full connection
Select E.department_id,d.department_id,e.last_name,d.department_name from EMPLOYEES e full outer joins departments D on (E . department_id = d.department_id);
--nuion/union All
Select E.last_name,d.department_name from Employees e,departments D where e.department_id (+) = d.department_id
UNION ALL
Select E.last_name,d.department_name from Employees e,departments D where e.department_id = d.department_id (+);
--minus (the first query result minus the second query results left the second part of the non-repeating section)
Select E.last_name,d.department_name from Employees e,departments D where e.department_id = d.department_id (+)
Minus
Select E.last_name,d.department_name from Employees e,departments D where e.department_id (+) = d.department_id;
--intersect (Union Uniqueness) (intersection of two sets)
Select E.last_name,d.department_name from Employees e,departments D where e.department_id = d.department_id (+)
Intersect
Select E.last_name,d.department_name from Employees e,departments D where e.department_id (+) = d.department_id;
--Group functions
--count
Select COUNT (department_id) from EMPLOYEES;
Select COUNT (*) from EMPLOYEES where department_id = & department;
Select COUNT (commission_pct) from EMPLOYEES where department_id = & department;
--MAX (natural order)/min/sum/average (limit Chinese characters)
Select Max (Salary), min (Salary), sum (salary), round (AVG (Salary)) from EMPLOYEES;
Select Max (Salary), min (Salary), sum (salary), round (AVG (Salary)) from EMPLOYEES where department_id = 90;
--group by (grouping function) (having a conditional filter on a grouping function) (followed by a column name instead of a column alias)
--where> Group > Group function >having>order by
Select Department_id,round (AVG (Salary)) from EMPLOYEES GROUP by department_id ORDER by 2 desc;
Select Department_id,round (AVG (Salary)) from EMPLOYEES GROUP by DEPARTMENT_ID have round (avg (salary)) >9000;
*********************************************************************************************************
--General Query and sub-query
Select Last_name,salary from Employees where salary = 11000;
--Single-row subquery (multiline result) (conditional connector can be used)
--1. As a WHERE clause
Select last_name,salary,job_id from EMPLOYEES where job_id= (select job_id from EMPLOYEES where last_name= ' Abel ');
Select last_name,salary,job_id from EMPLOYEES where
Job_id= (select job_id from EMPLOYEES where last_name= ' Abel ')
and
Salary> (select salary from EMPLOYEES where last_name= ' Abel ');
--2. As FROM clause
Select Last_name,salary from EMPLOYEES where job_id = ' sa_rep ' and salary = 11000;
--back as a table, external and internal query (sub-query can be nested multiple layers (three layer can no longer))
Select Last_name,salary from
(select Last_name,salary from EMPLOYEES where job_id = (select job_id from EMPLOYEES where last_name= ' Abel '))
where salary = 11000;
--3. Using grouping functions in subqueries
Select Last_name| | first_name,salary,job_id from EMPLOYEES where salary= (select Max (Salary) from EMPLOYEES);
Using subqueries in--4.having
Select Department_id,min (Salary) from EMPLOYEES
GROUP by department_id have min (salary) >
(select min (Salary) from employees where department_id=50);
--The minimum wage is greater than the ID and minimum wage for all departments in department 100th
Select Department_id,min (Salary) from EMPLOYEES
GROUP by department_id have min (salary) >
(select min (Salary) from EMPLOYEES where department_id=100);
--Query the name, salary, and department of the employee who has the same name as the employee whose surname is * *.
--(in/= any) usage is the same (<>any is invalid operation, equivalent to NO)
Select Last_name,job_id,salary from EMPLOYEES where job_id = any
(select job_id from EMPLOYEES where last_name = ' King ');
--(>any/<any greater than the smallest, less than the largest)
Select Employee_id,last_name,job_id,salary from EMPLOYEES where salary <> any
(select salary from EMPLOYEES where job_id= ' It_prog ');
Select last_name,job_id from EMPLOYEES where job_id =
(select job_id from employees where last_name = ' Abel ');
Select last_name,job_id from EMPLOYEES where department_id =
(select department_id from EMPLOYEES where last_name = ' Abel ');
Select Last_name| | ' ' | | first_name,salary,department_id from Employees where salary<
(select AVG (Salary) from EMPLOYEES where department_id=100) and department_id<>100;
SELECT * from EMPLOYEES;
********************************************************************************************************
--Create a table
--create Table Name
--(
--Column 1 data type constraint (optional),
--Column 2 data type constraints
--);
--Create a table (TEST_OAEC) 3 columns: ID name age
CREATE TABLE Oaec_test
(
--Column level
--id Number (Ten) CONSTRAINT oaec_test_id_pk PRIMARY Key,
--Table level
ID Number (10),
CONSTRAINT OAEC_TEST_ID_PK primary KEY (ID),
Name Varchar2 (CONSTRAINT) oaec_test_name_nn NOT NULL,
Age Number (5) Check (age>0 and age<150),
Birthday Date
);
--birthday date default sysdate;
--Description Table
Desc oaec_test;
--Inserting data
Insert into Oaec_test (id,name,age) VALUES (1, ' suit ', DEFAULT);
--View
SELECT * from Oaec_test;
--Delete Table
drop table oaec_test;
--View constraints
SELECT * from User_constraints;
--View the constraints of the oaec_table table (constraint name and constraint type)
Select Constraint_name,constraint_type from user_constraints where table_name= ' oaec_test ';
--constraints
--not null (cannot be empty) (can only be written at the column level)
--unique (Unique)
--primary key (non-null and unique)
--foreign key (Foreign key column) (dependency.) Dependent primary key. The primary key has foreign keys, not necessarily, the primary key no foreign key must not have)
--check (custom constraint) For example: (Age>0 and age<150)
CREATE TABLE Friend
(
ID Number (10),
Phone Number (11),
Last_Name VARCHAR2 (25)
CONSTRAINT friend_last_name_nn NOT NULL,
Gender VARCHAR2 (10),
Age Number (3),
CONSTRAINT FRIEND_ID_PK PRIMARY KEY (ID),
CONSTRAINT friend_phone_uk UNIQUE (phone),
CONSTRAINT friend_gender_ck Check (Gender in (' Male ', ' female ')),
CONSTRAINT friend_age_ck Check (age>0 and age<150),
--Foreign key column, reference oaec_test ID primary key column (column level)
--TEST_ID Number (Ten) CONSTRAINT FRIEND_TEST_ID_FK REFERENCES oaec_test (ID)
--Table level
TEST_ID Number (10),
CONSTRAINT FRIEND_ID_FK FOREIGN Key (test_id) REFERENCES oaec_test (ID)
);
drop table oaec_test;
drop table friend;
drop table copy_friend;
SELECT * from Oaec_test;
SELECT * from FRIEND;
SELECT * from Copy_friend;
INSERT into Oaec_test (id,name) VALUES (1, ' flower ');
INSERT into Oaec_test (id,name) VALUES (2, ' flower 1 ');
INSERT into Oaec_test (id,name) VALUES (3, ' flower 1 ');
INSERT into Oaec_test (id,name) VALUES (4, ' Flower 1 ');
--insert into Oaec_test VALUES (3, ' Floral 1 ', 21,sysdate);
INSERT into FRIEND VALUES (1, 16345678910, ' flower ', ' male ', 22,null);
INSERT into FRIEND VALUES (2,12345678910, ' flower ', ' male ', 22, 1);
INSERT into FRIEND VALUES (3,16346678910, ' flower ', ' male ', 22,2);
INSERT into FRIEND VALUES (4,16345978910, ' flower ', ' male ', 22,2);
--Create a table with a subquery
--Create a table with only id,age
CREATE TABLE Copy_friend as
Select Id,age from FRIEND where age=22;
--Copy the entire source table
CREATE TABLE Copy_friend as
select * from friend;
--Create a table and add a column
ALTER TABLE Oaec_test Add (Salarys number (Ten) CONSTRAINT oaec_test_salarys_uk unique);
Desc oaec_test;
--Modify the columns after the table is created
ALTER TABLE Oaec_test MODIFY (name Number (10));
ALTER TABLE Oaec_test MODIFY (birthday date default sysdate);
--Add a constraint after creating a table
ALTER TABLE Oaec_test Add (CONSTRAINT oaec_test_birthday_uk unique (birthday));
--delete constraint after creating table (deleted by constraint name)
Alter table Oaec_test DROP CONSTRAINT oaec_test_birthday_uk;
--constraint invalidation (DISABLE)
--Delete columns in the table (the columns can be deleted even if they have data) (you can delete only one at a time and you must keep one column)
ALTER TABLE oaec_test drop column Salarys;
--Delete table (drop table) (cascade Delete is available when referenced)
DROP table oaec_test;
--(delete the constraint first and then delete the table)
DROP table Oaec_test CASCADE CONSTRAINTS;
*********************************************************************************************
--1. Inserting data into a table
--insert into table name (if the table name is not written, all columns of the table are to be inserted into the data)
--values (the inserted data and column one by one correspond)
SELECT * from tab;
--Create Test table
CREATE TABLE Tests
(
ID Number (10),
CONSTRAINT OAEC_TESTS_ID_PK primary KEY (ID),
Name Varchar2 (CONSTRAINT) oaec_tests_name_nn NOT NULL,
Age Number (5) Check (age>0 and age<150),
Birthday Date DEFAULT Sysdate
);
SELECT * from Tests;
INSERT into Tests (Id,name,age,birthday)
VALUES (1, ' suit ', 21,sysdate);
INSERT into Tests (Id,name,age,birthday)
VALUES (2, ' suit ', 21,to_date (' 2000-05-01 ', ' yyyy-mm-dd '));
INSERT into Tests (Id,name,age,birthday)
VALUES (4, ' suit ', 21,to_date (' 2000月-May -01 ', ' yyyy-mon-dd '));

CREATE TABLE Oaec_tests_copy as SELECT * from Oaec_test;
SELECT * from Oaec_tests_copy;
--delete data from a table
Delete oaec_tests_copy;
--2. Inserting data//inserting data with a subquery (the queried data is all inserted into the specified table/column for a pair) (normal insert can only insert one column)
INSERT INTO Oaec_tests_copy select * from Oaec_test;
Insert into Oaec_tests_copy (id,name) select Id,name from Oaec_test;
Insert into Oaec_tests_copy (id,name) Select Id,name from oaec_test where ID in (1,4);
--Update the data in the table (Update table name
--select Column name = new value
--where condition (determines the location of the data/does not write more than the line of all)
SELECT * from Oaec_test;
Update Oaec_test
Set id = 5 where id = 3;
Update oaec_test Set name = ' Faker ' WHERE name = ' The Greater Devil Faker ';
--3. Deleting data
--delete from table name
--where condition (determines where to delete the data);
Delete from Oaec_test;
Rollback
Delete from Oaec_test
where id = 0;
Delete from Oaec_test
Where NAME like '% floral 1% ';
--NOTE: Insert/delete/update do not violate constraints! Especially FOREIGN KEY constraints!
commit;--Submit
rollback;--rollback
SavePoint update_p;--Setting the rollback point
ROLLBACK to update_p;--rollback to the specified rollback point

A little knowledge of Oracle Database