Java:oracle (cascade Delete, left and right cross natural connection, subquery, all,any,in)

1. Cascade Delete:

-Cascade Delete: Layoffs, company closures
--Cascade Delete (Cascade), set to null (SETNULL), laissez-faire (no action)

　--Cascade: (Take one-to-many as an example) if you delete many of the parties, one party will not be affected, but if you delete a party, all of the corresponding data will be deleted.
SELECT * from the staff s inner joins department d on d.id = s.department_id;
Delete from the staff s where s.id = 1;
Delete from Department d where d.id = 2;

　　

　　--Set NULL: If one side is deleted, the corresponding data of the multiple side becomes null
SELECT * from the staff s full join Department d on d.id = s.department_id;
Delete from Department d where d.id = 10000;

　　--no cation: most commonly used
SELECT * from the staff s left join Department d on d.id = s.department_id;
Delete from Department d where d.id = 2;
SELECT * from the staff;

2. Natural connection between left and right cross:

--In the actual development work, the master needs to establish a foreign key column in the table, must not be in the key column, establish the primary foreign key relationship, the use of direct query can be

--Cross join,nature join,left join,right Join,inner join,full Join
--Multi-table query
SELECT * from teacher, classes;--1992 defined SQL standard, sql1992

　　

　　--Cross join: Crossover connection
SELECT * FROM teacher Cross join classes;--1999 year defined SQL standard, sql1999--select * from teacher, classes;

　　

　--Natural Join natural connection: equivalence is connected: there must be a primary foreign key relationship in both tables --there is a drawback: the same column name cannot exist in both tables two
--In a self-connection, no qualification can occur
--although you cannot add the qualification yourself, the natural join automatically connects the Dept_id=id.
--just need to understand it, do not need to remember, in the actual development, no one will let you write this
--There were two name columns in the table, so it was restricted
SELECT * FROM department S natural join staff D;-->select * from the staff S, Department d where d.id = t.s.department_id;

　

　--left join on: Left join: Connect to the right table with left table: department
Select S.name from department D left joins staff s on s.department_id = d.id;--here is equivalent to where s.department_id = D.id

　　

　　--Right join on: Connect to the left side, just opposite to the link on the right, and connect to the table on the left

SELECT * FROM Department D right JOIN staff s on s.department_id = D.id;

　　

　--INNER JOIN on: Inner connection
-On: The following condition must be the relationship of the primary foreign key of the two tables
-In real-world development, the internal connection uses the most
--In the actual project development, database Optimization (programmer level): SQL Optimization: 1. Use where more, less xxxx,xxx with multiple table queries (tables and tables are separated by commas)
SELECT * FROM Department D INNER JOIN staff s on s.department_id = d.id;--> SELECT * From the staff S, Deparment D where S. department_id = D.id

　　

　--Full join on: outer JOIN
--LEFT JOIN: A reference to the right, or null if there is no data for the linked table
--Right join: A reference to the left table, or null if there is no data for the associated
--Full join: There is no table as a benchmark, the corresponding data is displayed, if not the corresponding two parties can be empty
--full join is mainly used in statistics
SELECT * from the staff s full join Department d on d.id = s.department_id;

3. Sub-query:

--In a subquery in Oracle, ORDER by is not ordered, and all oder by sorts are placed in the final query result

--Information on teachers older than Zhangsan
--Information for teachers older than the average age
-Employee information under the same department, lower than Zhangsan salary
--All,any,in,union,union All

　　
-Information on teachers older than Zhangsan
--First step: Find out the age of Zhang San
Select ages from teacher where name= ' Zhangsan ';--Zhang San's age
--Find out all the teachers ' information and be older than Zhang San
SELECT * from teacher where age > ' Zhang San ';
SELECT * from teacher where is > (select age from teacher where name= ' Zhangsan ');

-Information for teachers older than the average age
--If the age shown is 0 years old, then the teacher does not fill in the actual age
--The first step: calculating the average age of all teachers
Select round (AVG (NVL (age,0))) from teacher;
--Second step: query all teacher information, and need to be greater than the average age of all teachers
SELECT * from teacher where ages > ' average age of all teachers ';
SELECT * from teacher where > (select Round (avg. NVL (age,0)) from teacher);

--Employee information in the same department, higher than Zhangsan salary
--The first step: to find out the wages of Zhangsan
Select salary from the staff where name= ' Zhangsan ';
--Step Two: Query the department ID of the Zhangsan
Select s.department_id from the staff s where s.name= ' Zhangsan ';
--The third step: to find out all the employees, and must be the same department with Zhangsan, and wages must be higher than Zhangsan
SELECT * from the staff where salary >
(select salary from the staff where name= ' Zhangsan ')
and staff.department_id =
(select s.department_id from the staff s where s.name= ' Zhangsan ');
　　

4.any,all,in
-Any: Compare any of the following: Comparable or size comparisons can be made; writing specification: operator any

--Check out and Zhangsan all employees in the same department
SELECT * from the staff s where s.department_id = any (select department_id from the staff where name= ' Zhangsan ');
SELECT * from the staff s where s.department_id > A (select department_id from the staff where name= ' Zhangsan ');
　　

　　

--all: Compare All
Select ID from Department f where id>1--query all department ID of department id>1, find out 3 data
SELECT * from staff s where s.department_id > All (SELECT ID from Department f where id>1);
--Any: one of them is compared: select ID from Department f where id>1, (2,3,4) Get data is starting from 2, get 3, get 4,id 4 get will overwrite the previous data, will only return a
　　

SELECT * from the staff s where s.department_id > A (select F.id from Department f where f.id > 0);
--Select ID from Department f where Id>1, will get 2,3,4
--select * from the staff s where s.department_id, is the ID 1,2,3,4,department_id must be queried after >
　　

　　-In: equivalent: Do not need operator when using in keyword, directly with in can, in equals =
SELECT * from the staff s where s.department_id in (select ID from Department f where id>1);

Practice:

1. Set up six tables, User_type,user,order,goods_order,goods,goods_type, first analyze what the table of this function is
user---users table <----membership Mechanism (bronze member, Gold Member, Diamond member)
id,username,password,address,user_type_id
User_type: Membership table (user type)
Id,typ_name,type _level
goods---commodity table
Id,goods_ name,price,goods_type_id
Goods_type: Product type
Id,type_name
orders---order form
id,goods_nums,total_price,user_id
Orders_ Goods relation table for orders and commodities
goods_id,order_id

2. Analyze the relationship between tables
User_type----User: One-to-many
Goods_type----Goods: one-to-many
User----Orders: one-to-many
Goods----Orders: Many-to-many
Goods and Orders tables have a relational table in the middle Goods_orders

--whether the inner join left JOIN or RIGHT join can only join one table per occurrence of a keyword, if you want to concatenate multiple tables multiple times using inner join on, the left join On,right join on
--In the case of using inner join, it is usually inner to omit

--Check out all order information
Select Tor.goods_nums goodsnums, Tor.total_price totoalprice, Tgs.goods_name goodsname, Tgs.price Price, Tgt.type_n Ame Goodstypename, Tur.username username, tur.address address, tut.type_name usertypename
From T_order Tor
INNER JOIN T_goods_order tgo on tgo.order_id = Tor.id
INNER JOIN t_goods TGS on tgs.id = tgo.goods_id
INNER JOIN T_goods_type tgt on tgt.id = tgs.goods_type_id
INNER JOIN T_user tur on tur.id = tor.user_id
INNER join T_user_type tut on tut.id = tur.user_type_id;
--List of various membership levels with order amounts greater than 1500
Select Tut.type_name from T_order Tor
INNER JOIN T_user tur on tur.id = tor.user_id
INNER join T_user_type tut on tut.id = tur.user_type_id
where Tor.total_price >;
--List the amount of all orders under the gold Membership level
Select Tor.total_price from T_order Tor
INNER JOIN T_user tur on tur.id = tor.user_id
INNER join T_user_type tut on tut.id = tur.user_type_id
where Tut.type_level < (select Tutp.type_level from T_user_type tutp where tutp.type_name= ' Gold Members ');
--List all member names with order amount above average order amount
Select Tur.username from T_order Tor
INNER JOIN T_user tur on tur.id = tor.user_id
where Tor.total_price > (select Round (avg (Total_price)) from T_order);
--List all customer types with at least one member
Select COUNT (1), tut.type_name from T_user_type tut
INNER JOIN T_user tur on tur.user_type_id = Tut.id
GROUP BY Tut.type_name
Having count (1) > 0;
--List the minimum order amount for each member level
Select min (tor.total_price), tut.type_name from T_order Tor
INNER JOIN T_user tur on tur.id = tor.user_id
INNER join T_user_type tut on tut.id = tur.user_type_id
GROUP by Tut.type_name;
--List the details of all membership levels and the number of orders under each level
Select Tut.type_name, Tut.type_level, COUNT (1) from T_order Tor
INNER JOIN T_user tur on tur.id = tor.user_id
INNER join T_user_type tut on tut.id = tur.user_type_id
GROUP by Tut.type_name, Tut.type_level
--List the number of members at each member level, the average order amount
Select COUNT (1), round (avg (tor.total_price)), tut.type_name from T_user_type tut
INNER JOIN T_user tur on tur.user_type_id = Tut.id
INNER join T_order tor on tor.user_id = Tur.id
GROUP BY Tut.type_name

Diy:

--Find the name of a non-member user who is more expensive than a diamond consumer

Select Tur.user_name,tut.type_name from T_order Tor
INNER JOIN T_user Tur on tur.id=tor.user_id
Inner join T_user_type tut on tut.id=tur.user_type_id
where tor.total_price> (select Tor.total_price from T_order tor Inner joins T_user Tur on tur.id=tor.user_id inner JOIN t _user_type tut on tut.id=tur.user_type_id where tut.type_name= ' Diamond Members ') and Tut.type_name= ' non-members ' GROUP by Tur.user_name,tu T.type_name

--Find the lowest member level name for average consumption
Create View Price_view as (select AVG (tor.total_price) Price, tur.user_name,tut.type_name from T_order Tor
INNER JOIN T_user Tur on tur.id=tor.user_id
Inner join T_user_type tut on tut.id=tur.user_type_id
Group BY Tur.user_name,tut.type_name)

Select Pvw.type_name from Price_view pvw where price= (select min (price) from Price_view) and pvw.type_name!= ' non-members '

--Find the name of the membership level with the highest average consumption
Select Pvw.type_name from Price_view pvw where price= (select Max (price) from Price_view) and pvw.type_name!= ' non-members '

Java:oracle (cascade Delete, left and right cross natural connection, subquery, all,any,in)