The second bullet in the series of software implementation Interview articles, the interview summary of MySQL and Oracle joint query and aggregate function. If you look at mysql, can't you have some oracle? In front of it, many companies have done many written test questions, and now there are few written test questions. How much ink do you have in your stomach? An experienced interviewer basically knows about it when he asks. While there is still a little memory, we can share it completely.

Series of articles have been included in github warehouse:

https://github.com/cnwangk/SQL-study

preface

I'm the one who works hard and writes documents with my feet.

If you don't want to create a new sample by yourself, you also want to find a complete sample for test practice. The MySQL official website provides a sample database.

The official sakila and world databases and the download address of the official website have been provided, which can be downloaded for reference learning.

The sakila DB database contains three files:

• sakila-schema.sql: database table structure
• sakila-data.sql: database sample simulation data
• sakila.mwb: database physical model, which can be opened and viewed in MySQL workbench.

https://downloads.mysql.com/docs/sakila-db.zip

World DB database, table structure and data are included together:

https://downloads.mysql.com/docs/world-db.zip

Oracle11g comes with scott users after installation, which can be used to practice. The EMP and DEPT tables are mainly used. I think of the first demo example of CURD written in the ssh framework of Java that year, which is the two tables of Oracle, because they are related.

• EMP: employee list;
• DEPT: department table;

The second bullet in a series of articles on software implementation, which I wanted to write last year, has been until now, ha ha.

text

Worse than the pendulum, who is the strongest, he is stronger and stronger every time. Now I look back on those blogs I wrote before. Although I only sent them after my real practice and verification, I feel rotten. Although the document I wrote is very bad, it's better than before. In a year or two, you will find that the progress is considerable and the treasure house of knowledge is becoming richer and richer.

Think more and practice more. Don't just think about it, but move immediately. Practice and verify in person. Ask more why and think about the essence of things. It's better to see it ten thousand times than to practice it yourself.

My test environment is based on:

• Operating system: Windows10;
• Database: MySQL 8 0.28 and Oracle11g;
• Use the query tool: mysql8 0's own command line and Oracle's own SQLplus;
• Third party tools SQLyog and PLSQL Developer.

1, Joint query

Graphical joint query

Internal connection: the statistical content is the overlapping part of table1 and table2.

inner join on

Left outer connection: outer can be omitted. The statistical content is mainly table1.

left outer join on

Right outer connection: outer can also be omitted. The statistical content is mainly table2.

right outer join on

1. Joint query

1.1 example of joint query in MySQL

• inner join on
• right join on: right outer join
• left join on: left outer connection

The keyword of inner join query in MySQL: inner join on. It is only used as a demonstration, and the explain execution plan is not executed to judge the execution efficiency. Little suggestion. When testing these joint queries, you can see the differences between the three joint queries without too many filter conditions.

SELECT c.`ID`,c.`CountryCode`,cl.`CountryCode`,cl.`Language` 
FROM world.`city` c INNER JOIN world.`countrylanguage` cl 
ON c.`CountryCode`=cl.`CountryCode` WHERE c.`ID`>120 AND c.`ID` LIMIT 0,5; 

LEFT OUTER JOIN query keyword in MySQL: LEFT OUTER JOIN

SELECT c.`ID`,c.`Name`,c.`CountryCode`,cl.`IsOfficial`,cl.`CountryCode`,cl.`Language` 
FROM world.`city` c LEFT OUTER JOIN world.`countrylanguage` cl 
ON c.`CountryCode`=cl.`CountryCode` LIMIT 0,5;

RIGHT OUTER JOIN query keyword in MySQL: RIGHT OUTER JOIN

SELECT c.`ID`,c.`Name`,c.`CountryCode`,cl.`IsOfficial`,cl.`CountryCode`,cl.`Language` 
FROM world.`city` c RIGHT OUTER JOIN world.`countrylanguage` cl 
ON c.`CountryCode`=cl.`CountryCode` LIMIT 0,5;

1.2 example of joint query in Oracle

Mainly take the SCOTT user as an example to check the tables under the SCOTT user. In this way, you need to run SQL query as a dba administrator:

Owner represents the user name, so you can directly find the SCOTT user, TABLE_NAME: represents the table name.

select t.OWNER,t.TABLE_NAME,t.TABLESPACE_NAME from dba_tables t where t.OWNER='SCOTT'; 

The joint query in Oracle is also demonstrated by employee table (emp) and department table (dept).

Internal join in Oracle

Carry out association query according to department number, and carry out paging query. 5 pieces of data are displayed on each page:

select e.ename,e.empno,d.deptno,d.dname from scott.emp e 
inner join scott.dept d on e.deptno=d.deptno where rownum<=5; 

left outer join on

select e.ename,e.empno,d.deptno,d.dname from scott.emp e 
left outer join scott.dept d on e.deptno=d.deptno where rownum<=5; 

right outer join on

select e.ename,e.empno,d.deptno,d.dname from scott.emp e 
right outer join scott.dept d on e.deptno=d.deptno where rownum<=5;

full join on

select e.ename,e.empno,d.deptno,d.dname from scott.emp e 
full join scott.dept d on e.deptno=d.deptno where rownum<=5;

Combined query: union

select e.ename,e.empno from scott.emp e where rownum<=5 union select e.ename,e.empno from scott.emp e  
where e.ename like '%ARC%'; 

Combined query: union all

select e.ename,e.empno from scott.emp e where rownum<=5 union all select e.ename,e.empno from scott.emp e  
where e.ename like '%ARC%'; 

There is a difference between union and union all. The examples I listed are fuzzy matching, but the effect is not demonstrated. After using union all, the DBMS will not cancel duplicate rows.

Remove the following like conditions. The data counted by union is 14 lines and the data counted by union all is 19 lines. In fact, it is not difficult to understand that all is all.

2. Paging query

2.1 the MySQL paging query uses the limit keyword

tips: the CMD command window in Windows can use color a to call out the green font with black background, and color f0 is to quickly call up the black font with white background!

Eye care: R: 181 G: 230 B: 181

Example: the city table in the world database is used for demonstration paging query, and the data structure is displayed through desc, which is very common when cooperating with development for joint debugging:

mysql> desc world.city;

Query the first five data in the city table in the world database:

mysql> select * from city limit 0,5;

2.2. Oracle paging query uses rownum pseudo column

Similarly, use the desc keyword to query the emp table structure:

SQL> desc scott.emp;

Example of paging query: use rownum keyword to demonstrate paging query in Oracle.

Query the empno: number, ename: employee name and pseudo column rowid of emp (employee table) in scott user, and only query the first five data:

SQL> select t.rowid,t.empno,t.ename from scott.emp t where rownum <=5;

The first common method of Oracle paging query is to query the 6th ~ 11th data. Through nested sub query, the keywords rownum and where are used:

-- Statistics emp Total data entries
select count(*) from scott.emp;  
-- Query page 6~11 The data is queried through nested subqueries, and keywords are used rownum and where
select * from (select scott.emp.empno,rownum r from scott.emp Where rownum<=11)where r>=6;

The second common method of Oracle paging query is to sort order by first, and then paging query to query the 6th ~ 11th data:

-- Sort first
select * from  emp e order by e.empno Desc;
-- Page again
select * from (select e.*,rownum r_num from(select * from scott.emp e order by e.empno desc )e)b where b.r_num between 6 and 11;

2, Aggregate function

Most of the functions mentioned below are supported by standard SQL databases, but they should also be tested and verified according to the actual situation. Individuals mainly verify MySQL and Oracle.

Key point: when we want to migrate data, count and sum functions cannot avoid the need to manually calculate and compare the consistency of data before and after migration.

1. Common aggregate functions

Several aggregate functions are introduced:

• The count function is used to count the number of entries;
• sum function is used for summation;
• substr function is used to intercept;
• avg function is used to take the average value;
• The max function is used to remove the maximum value;
• The min function is used to remove the minimum value.

The following shows how to query the emp table of scott user in Oracle database by using multiple functions at the same time:

Query results: count counts the total number of employees, sum the total salary of all employees, avg counts the average salary of all employees, and substr intercepts the two digits after the decimal point.

-- count:Number of statistical items,sum:Sum,substr:intercept,avg:Take the average value
select count(*), sum(t.sal), substr(avg(t.sal), 0, 7) from scott.emp t;

Returns the average value avg, which is generally intercepted with the substr keyword, and retains two decimal places through calculation.

Average salary statistics of employees in a company:

-- avg:Take the average value
select avg(t.sal) from scott.emp t;
select substr(avg(t.sal), 0, 7) from scott.emp t;

Return the count of statistical rows

Count the total number of employees in a company:

-- Statistical function count:Statistics emp Number of table entries 14
select count(*) from scott.emp;

Returns the total number (sum). The sum function is generally used with the decode function. The black background above is tired after seeing it for a long time, so I specially changed a kind of eye color. The font color has not been specially changed. The font is a little bigger and looks more comfortable.

Calculate the total salary of all employees in a company:

-- Summation function sum Use of
select sum(t.sal) from scott.emp t;
-- coordination decode Function use
select sum(decode(ename, 'SMITH', sal, 0)) SMITH,sum(decode(ename, 'ALLEN', sal, 0)) ALLEN,
	   sum(decode(ename, 'WARD', sal, 0)) WARD,sum(decode(ename, 'JONES', sal, 0)) JONES,
	   sum(decode(ename, 'MARTIN', sal, 0)) MARTIN,sum(decode(ename, 'BLAKE', sal, 0)) BLAKE,
	   sum(decode(ename, 'CLARK', sal, 0)) CLARK,sum(decode(ename, 'SCOTT', sal, 0)) SCOTT,
	   sum(decode(ename, 'KING', sal, 0)) KING,sum(decode(ename, 'TURNER', sal, 0)) TURNER
from scott.emp;

tips: the count function is frequently used in work. You don't know how many records there are in a table. You need to make statistics before processing.

Returns the maximum value max

Check the highest paid employee:

-- max Use of functions
select max(t.sal) from scott.emp t;

Return min

Check the lowest paid employee:

select min(t.sal) from scott.emp t;

rownum pseudo column in Oracle

The last record of the company's employees is counted through rownum:

select t.sal from scott.emp t where rownum <=1;
select t.sal from scott.emp t where rownum <=1 order by t.sal desc;

Paging limit keyword in MySQL

The last three records are returned according to the actor (actor list) in sakila database as an example through the limit keyword, and the actor is used_ Sort by ID.

Note: limit belongs to the syntax after MySQL extends SQL92 and cannot be used in other databases. Oracle paging can be realized through rownum, which is also introduced above.

SELECT t.`first_name`,t.`actor_id` FROM sakila.`actor` t ORDER BY t.`actor_id` DESC LIMIT 0,3;

2. Key functions

• group by function;
• The having function is used to filter the content after grouping.

group by function

Use with the aggregate function sum to query the emp table under scott user in Oracle. Use group by to group, and then calculate the salary of employees in each department of the company:

SELECT t.deptno, SUM(t.sal) AS sals FROM scott.emp t GROUP BY t.deptno;

having function

Difference: the difference between having and where is that having filters the aggregated results conditionally, while where filters the records before aggregation. If logic allows, you should use where to filter records first as far as possible. Due to the reduction of the result set, the efficiency of aggregation is significantly improved. Finally, judge whether to filter again with having according to logic.

Used in conjunction with the aggregate function, the emp and dept tables under scott user in Oracle.

First group the Department names, and then use having to filter out the departments whose total salary is greater than 10000:

SELECT d.dname, SUM(e.sal) AS sals FROM scott.emp e
INNER JOIN scott.dept d ON e.deptno=d.deptno
WHERE e.deptno < 30 GROUP BY d.dname  HAVING SUM(e.sal) > 10000;

3, SQL core knowledge

Everything should depend on the actual working scene. Some personal understandings are just suggestions, and the final application needs to be combined with the actual application scenario. The software implementation has no high requirements for SQL functions, triggers and stored procedures, but it also needs to be able to use them basically. In some special scenarios, using these core knowledge of SQL will help to improve our work efficiency.

1. Function

FUNCTION keyword: FUNCTION

Creating a new function using a third-party client tool will automatically generate some templates:

DELIMITER $$  -- Declaration keyword DELIMITER
CREATE -- Keywords for creating functions create
    /*[DEFINER = { user | CURRENT_USER }]*/
    FUNCTION `study`.`stu_num`() -- Set function name
    RETURNS TYPE -- Type of return value
    /*LANGUAGE SQL
    | [NOT] DETERMINISTIC
    | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
    | SQL SECURITY { DEFINER | INVOKER }
    | COMMENT 'string'*/
    BEGIN -- Start business logic
    -- {Business logic area...}
    END$$ -- End flag
DELIMITER ;

2. Trigger

TRIGGER keyword: TRIGGER

When you create a new trigger using a third-party client tool, some templates will be automatically generated:

DELIMITER $$
CREATE	-- Key to create trigger create
    /*[DEFINER = { user | CURRENT_USER }]*/
    TRIGGER `study`.`stu_insert` BEFORE/AFTER INSERT/UPDATE/DELETE
    ON `study`.`<Table Name>`
    FOR EACH ROW BEGIN -- Use to for each loop
    -- {Business logic area...}
    END$$
DELIMITER ;

3. Stored procedure

Stored PROCEDURE keyword: PROCEDURE

The storage engine that supports complete transactions uses commit transactions as much as possible while ensuring the integrity and consistency of data. Using functions and stored procedures as a good example, it can be applied to quickly generate tens of millions of large data tables in MySQL for testing, and it can also be associated with testing performance. This is a better way to arouse the motivation of our study.

Creating a new stored procedure using a third-party client tool will automatically generate some templates:

DELIMITER $$
CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    PROCEDURE `study`.`insert_study`()
    /*LANGUAGE SQL
    | [NOT] DETERMINISTIC
    | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
    | SQL SECURITY { DEFINER | INVOKER }
    | COMMENT 'string'*/
    BEGIN
	-- {Business logic area...}
	COMMIT; -- Storage engine supporting complete transactions,Under the condition of ensuring the integrity and consistency of data,Use as much as possible commit Transaction commit
    END$$
DELIMITER ;

4. Typical example sakila database

This is a movie rental information management system database officially provided by MySQL with stored procedures, triggers and function examples. And the EER model is officially provided to facilitate understanding the association relationship between each table. You can use MySQL workbench to open sakila Mwb for reference learning. If you can read this document completely, you will find that I provided the download address of the official website of sakila database at the beginning.

sakila database view: actor_info, actor information view

Use the DESC keyword to view the view structure. This keyword is very practical. The view and table structure are very similar, with the actor in sakila_ Info view:

sakila database stored procedure: film_in_stock, movie inventory

An official example: create a stored procedure, declare three constant fields, assign values to the demonstration fields respectively, and finally copy and save the found records to p_film_count. Why do I say replication here? Because select is used Into keyword.

The main block of functions, triggers and stored procedures is in the area of BEGIN {business logic area...} END.

DELIMITER $$
USE `sakila`$$
DROP PROCEDURE IF EXISTS `film_in_stock`$$
CREATE DEFINER=`root`@`%` PROCEDURE `film_in_stock`(IN p_film_id INT, IN p_store_id INT, OUT p_film_count INT)
    READS SQL DATA
    SQL SECURITY INVOKER
BEGIN
     SELECT inventory_id
     FROM inventory
     WHERE film_id = p_film_id
     AND store_id = p_store_id
     AND inventory_in_stock(inventory_id);
     SELECT FOUND_ROWS() INTO p_film_count;
END$$
DELIMITER ;

As for functions, I won't list the examples officially provided by MySQL.

Give a little advice. It doesn't seem to work for you. You can ignore it: first, get familiar with the syntax quickly, interpret the official examples, and then run the verification. Finally, write some simple examples to achieve the purpose of skilled use. Don't just want to implement, but implement immediately and look at the problem with thinking. Ask more why and think about the essence.

4, You should also protect your eyes when reading documents

1. Common eye care

colour	RGB	Hexadecimal
Common eye care	R: 181 G: 230 B: 181	#B5E6B5
yellow	R: 250 G: 249 B: 222	#FAF9DE
brown	R: 250 G: 242 B: 226	#FFF2E2
red	R: 253 G: 230 B: 224	#FDE6E0
green	R: 227 G: 237 B: 205	#E3EDCD
Haitian blue	R: 220 G: 226 B: 241	#DCE2F1
purple	R: 233 G: 235 B: 154	#E9EBFE
ash	R: 234 G: 234 B: 239	#EAEAEF

summary

All the people who can see here are handsome boys and pretty girls. The above is a summary of common joint queries and aggregate functions in SQL. I hope it can be helpful to your work and study. I think it's OK. Just backhand one key three times. The update on the official account may be faster and is still improving. If I feel that the summary is not in place, I hope I can leave your valuable opinions. I will adjust and optimize it regularly in the article.