51. Find the number of occurrences of commas in strings'10,A,B','cnt.

-- Except','Previous character length
SELECT LENGTH('10,A,B') AS length_str;
-- Except','Later character length
SELECT LENGTH(REPLACE('10,A,B', ',','')) AS length_only_str;
-- Number of commas
SELECT (LENGTH('10,A,B') - LENGTH(REPLACE('10,A,B', ',',''))) AS cnt;

Running time: 17ms

Occupied memory: 3408k

52. Get the first_name in Employees, and the query is arranged in ascending order according to the last two letters of the first_name.

-- Use substr(s,start,len)Function interception first_name The last two letters are sorted accordingly
SELECT DISTINCT first_name
FROM employees
ORDER BY SUBSTR(first_name, LENGTH(first_name)-1, 2);

Run time: 28ms

Occupied memory: 6628k

• In MySQL, you can also use RIGHT(s,n) functions

-- Use RIGHT(s,n)Function acquisition first_name The last two letters are sorted accordingly
SELECT DISTINCT first_name
FROM employees
ORDER BY RIGHT(first_name, 2);

53. According to dept_no, emp_no belongs to the same department is connected by comma, and the result gives dept_no and employees.

-- group_concat()Basic use of functions
SELECT dept_no, group_concat(emp_no) AS employees
FROM dept_emp
GROUP BY dept_no

Running time: 18ms

Occupied memory: 3300k

54. Find the average salary avg_salary of employees excluding the current maximum and minimum salary.

-- Choose the maximum and minimum salaries and exclude them
SELECT AVG(salary) AS avg_salary
FROM salaries AS s, (
    SELECT MAX(salary) AS max_sal, MIN(salary) AS min_sal  FROM salaries
) AS ms
WHERE to_date='9999-01-01' AND s.salary != ms.max_sal AND s.salary != ms.min_sal;

Running time: 18ms

Occupied memory: 3548k

-- The following methods are more rigorous 
-- Choosing the best value is currently selected rather than global 
-- But I can't get through it. OJ To examine...
SELECT AVG(salary) AS avg_salary
FROM salaries AS s, (
    SELECT MAX(salary) AS max_sal, MIN(salary) AS min_sal  
    FROM salaries
    WHERE to_date='9999-01-01'
) AS ms
WHERE to_date='9999-01-01' AND s.salary != ms.max_sal AND s.salary != ms.min_sal;

55. Page-by-page query employees table, one page per five rows, returning data on page 2

-- Use limit Implementing paging
-- The first page acts as 5 pages and the first page acts as 0 pages.,1,2,3,4
SELECT * FROM employees LIMIT (1-1)*5,5;
-- Page 2, 5,6,7,8,9
SELECT * FROM employees LIMIT (2-1)*5,5;

Running time: 27ms

Occupied memory: 4564k

Performance Analysis and Optimization of MySQL limit Usage and Paging Query

56. Get emp_no, dept_no and the corresponding bonus type btype and received for all employees, without assigning specific employees and without displaying them.

• Table structure of the invoked table

CREATE TABLE `dept_emp` ( 
    `emp_no` int(11) NOT NULL,
    `dept_no` char(4) NOT NULL,
    `from_date` date NOT NULL,
    `to_date` date NOT NULL,
    PRIMARY KEY (`emp_no`,`dept_no`));

CREATE TABLE `emp_bonus`(
    `emp_no` int NOT NULL,
    `recevied` datetime NOT NULL,
    `btype` smallint NOT NULL);

-- Because no assigned employees are not displayed, the employee sheet is only taken dept_emp That's fine.
-- Be careful emp_bonus Formerly established tables
SELECT de.emp_no, de.dept_no, eb.btype, eb.recevied
FROM dept_emp AS de
LEFT JOIN emp_bonus AS eb
ON de.emp_no=eb.emp_no;

Running time: 22ms

Occupied memory: 3368k

57. Use exists with keywords to find all information about employees who are not assigned to specific departments.

-- exists()It's like a function. 
-- input de.emp_no Does the output exist?'e.emp_no=de.emp_no'Equation Establishment Return T/F
SELECT *
FROM employees AS e
WHERE NOT EXISTS (
    SELECT * 
    FROM dept_emp AS de 
    WHERE e.emp_no=de.emp_no
);

Running time: 20ms

Occupied memory: 3320k

58. Get the row data in employees, and these rows also exist in emp_v. Note that you cannot use the intersect keyword.

• This is the precondition given by the title.

There are the following views:
create view emp_v as select * from employees where emp_no >10005;

• As with Question 47, the following solutions can be audited by OJ system

-- Most intuitive emp_v Just in employees Generated on the basis of
SELECT * FROM emp_v;

-- Use where
SELECT em.*
FROM employees AS em, emp_v AS ev 
WHERE em.emp_no=ev.emp_no;

-- Using Connections to Find Intersection
SELECT em.*
FROM employees AS em 
INNER JOIN emp_v AS ev 
ON em.emp_no=ev.emp_no;

59. Get information about employees with bonuses. emp_no, first_name, last_name, bonus type, salary and bonus amount are given.

• bonus type

  • btype is 10% of salary.
  • btype is 20% of its salary.
  • Other types are 30% of salary.

The current salary is to_date='9999-01-01'

-- Award-winning employee information in use btype When divided by 10.0 
-- Note that divide by 10.0，If you divide it by 10, the decimal digits of the result will be discarded.
SELECT e.emp_no, e.first_name, e.last_name, eb.btype, s.salary, (s.salary*eb.btype/10.0) AS bonus
FROM employees AS e 
INNER JOIN emp_bonus AS eb
ON e.emp_no=eb.emp_no
INNER JOIN salaries AS s 
ON eb.emp_no=s.emp_no AND s.to_date='9999-01-01';

Running time: 20ms

Occupied memory: 3408k

-- adopt case when Use btype
SELECT e.emp_no, e.first_name, e.last_name, eb.btype, s.salary, (
    CASE eb.btype
        WHEN 1 THEN s.salary*0.1
        WHEN 2 THEN s.salary*0.2
        ELSE s.salary*0.3
    END) AS bonus
FROM employees AS e 
INNER JOIN emp_bonus AS eb
ON e.emp_no=eb.emp_no
INNER JOIN salaries AS s 
ON eb.emp_no=s.emp_no AND s.to_date='9999-01-01';

Running time: 21ms

Occupied memory: 3300k

60. According to the sum of salary and running_total, running_total is the sum of salary of the first two employees, and so on.

The specific results are shown in Demo below.

Output format:

-- adopt group by Locate the current computed emp_no The grouping content is better than its emp_no Small employees then accumulate
SELECT s1.emp_no, s1.salary, SUM(s2.salary)
FROM salaries AS s1, salaries AS s2
WHERE s1.to_date='9999-01-01' AND s2.to_date='9999-01-01'
AND s2.emp_no <= s1.emp_no
GROUP BY s1.emp_no, s1.salary;

Run time: 29ms

Occupied memory: 3572k

-- Using subqueries is more intuitive
SELECT s1.emp_no, s1.salary, 
    (SELECT SUM(s2.salary) 
    FROM salaries AS s2 
    WHERE s2.emp_no <= s1.emp_no AND s2.to_date = '9999-01-01') AS running_total 
FROM salaries AS s1 
WHERE s1.to_date = '9999-01-01' 
ORDER BY s1.emp_no;

Running time: 21ms

Occupied memory: 3304k

• Note: Although there is no error in the actual environment, the above two methods are too slow to achieve the results before caching.

• The following is an efficient way to achieve 5 ms (+2 ms) by using the temporary variable @person

-- Use@persum Store the last accumulated total salary as a temporary variable
SELECT s.emp_no, s.salary, (@persum:=@persum + s.salary) AS running_total
FROM salaries AS s, (SELECT @persum:=0) AS ps
WHERE s.to_date='9999-01-01'
ORDER BY s.emp_no;

61. Give the first_name of odd rows in the employees table

-- Get odd-numbered first_name
SELECT e1.first_name 
FROM (
    -- Get each first_name And its ranking  
    SELECT e2.first_name, (
        -- For each first_name How many of the sorting numbers are counted in front of it?  
        SELECT COUNT(*) 
        FROM employees_sample AS e3 
        WHERE e3.first_name <= e2.first_name) AS rowid 
     FROM employees_sample AS e2) AS e1
WHERE e1.rowid % 2 = 1;

Running time: 17ms

Occupied memory: 3552k

-- stay where Direct judgment of whether the count is odd 
SELECT eo.first_name
FROM employees_sample AS eo
WHERE (
    SELECT COUNT(*)
    FROM employees_sample AS ei
    WHERE ei.first_name <= eo.first_name
) % 2 = 1;

• Use the COUNT() function to rank

Complete personal exercise code

My exercise SQL code has been uploaded to Github: https://github.com/slow bird of lsh/new code-sql-practice
For reference only~~~