One 、 Single table query method

• Single table query statement

''' SELECT  Field 1, Field 2... FROM  Table name  WHERE  Conditions  GROUP BY field HAVING  Screening  ORDER BY field LIMIT  Limit the number  '''                  

• Keyword execution priority

''' #  The point in the point ： Keyword execution priority  from where group by having select distinct order by limit '''

1. Find the watch :from

2. Hold where Specified constraints , Go to the file / Take out one record in the table

3. Group the extracted records group by, without group by, As a group

4. Group the results into having Filter

5. perform select

6. duplicate removal

7. Sort the results by condition ：order by

8. Limit the number of displayed results

1.1 select

company.employee
     staff id      id                  int             
     full name         emp_name            varchar
     Gender         sex                 enum
     Age         age                 int
     Date of entry      hire_date           date
     Position         post                varchar
     Job description      post_comment        varchar
     salary         salary              double
     The office        office              int
     Department number      depart_id           int



# Create table 
create table employee(
id int not null unique auto_increment,
name varchar(20) not null,
sex enum('male','female') not null default 'male', # Most of them are men 
age int(3) unsigned not null default 28,
hire_date date not null,
post varchar(50),
post_comment varchar(100),
salary double(15,2),
office int, # One department, one room 
depart_id int
);


# View table structure 
mysql> desc employee;
+--------------+-----------------------+------+-----+---------+----------------+
| Field        | Type                  | Null | Key | Default | Extra          |
+--------------+-----------------------+------+-----+---------+----------------+
| id           | int(11)               | NO   | PRI | NULL    | auto_increment |
| name         | varchar(20)           | NO   |     | NULL    |                |
| sex          | enum('male','female') | NO   |     | male    |                |
| age          | int(3) unsigned       | NO   |     | 28      |                |
| hire_date    | date                  | NO   |     | NULL    |                |
| post         | varchar(50)           | YES  |     | NULL    |                |
| post_comment | varchar(100)          | YES  |     | NULL    |                |
| salary       | double(15,2)          | YES  |     | NULL    |                |
| office       | int(11)               | YES  |     | NULL    |                |
| depart_id    | int(11)               | YES  |     | NULL    |                |
+--------------+-----------------------+------+-----+---------+----------------+

# insert record 
# Three departments ： teaching , sales , operating 
insert into employee(name,sex,age,hire_date,post,salary,office,depart_id) values
('egon','male',18,'20170301',' The old boy's ambassador to Shahe Office ',7300.33,401,1), # Here is the teaching department 
('alex','male',78,'20150302','teacher',1000000.31,401,1),
('wupeiqi','male',81,'20130305','teacher',8300,401,1),
('yuanhao','male',73,'20140701','teacher',3500,401,1),
('liwenzhou','male',28,'20121101','teacher',2100,401,1),
('jingliyang','female',18,'20110211','teacher',9000,401,1),
('jinxin','male',18,'19000301','teacher',30000,401,1),
(' Jackie Chan ','male',48,'20101111','teacher',10000,401,1),

(' Crooked ','female',48,'20150311','sale',3000.13,402,2),# Here is the sales department 
(' Yaya ','female',38,'20101101','sale',2000.35,402,2),
(' Butylene ','female',18,'20110312','sale',1000.37,402,2),
(' The stars ','female',18,'20160513','sale',3000.29,402,2),
(' Greg ','female',28,'20170127','sale',4000.33,402,2),

(' Zhang Ye ','male',28,'20160311','operation',10000.13,403,3), # Here's the operations department 
(' Cheng Yaojin ','male',18,'19970312','operation',20000,403,3),
(' Cheng Yaoyin ','female',18,'20130311','operation',19000,403,3),
(' Cheng Yaotong ','male',18,'20150411','operation',18000,403,3),
(' Cheng yaotie ','female',18,'20140512','operation',17000,403,3)
;

#ps： If in windows In the system , Insert Chinese characters ,select The result is blank , All character codes can be set to gbk

• Function demonstration

# Simple query 
    SELECT id,name,sex,age,hire_date,post,post_comment,salary,office,depart_id 
    FROM employee;

    SELECT * FROM employee;

    SELECT name,salary FROM employee;

# Avoid repetition DISTINCT
    SELECT DISTINCT post FROM employee;    

# Query through four operations 
    SELECT name, salary*12 FROM employee;
    SELECT name, salary*12 AS Annual_salary FROM employee;
    SELECT name, salary*12 Annual_salary FROM employee;

# Define the display format 
   CONCAT()  Function to connect strings 
   SELECT CONCAT(' full name : ',name,'  Annual salary : ', salary*12)  AS Annual_salary 
   FROM employee;
   
   CONCAT_WS()  The first parameter is the separator 
   SELECT CONCAT_WS(':',name,salary*12)  AS Annual_salary 
   FROM employee;

    combination CASE sentence ：
   SELECT
       (
           CASE
           WHEN NAME = 'egon' THEN
               NAME
           WHEN NAME = 'alex' THEN
               CONCAT(name,'_BIGSB')
           ELSE
               concat(NAME, 'SB')
           END
       ) as new_name
   FROM
       emp;

1.2 where

where Can be used in sentences ：

1. Comparison operator ：> < >= <= <> !=
2. between 80 and 100 Values in 10 To 20 Between
3. in(80,90,100) The value is 10 or 20 or 30
4. like ‘egon%’
   pattern It can be % or _,
   % Any number of characters
   _ Represents a character
5. Logical operators ： Logical operators can be used directly in multiple conditions and or not

#1: Single condition query 
    SELECT name FROM employee
        WHERE post='sale';
        
#2: Multiconditional query 
    SELECT name,salary FROM employee
        WHERE post='teacher' AND salary>10000;

#3: keyword BETWEEN AND
    SELECT name,salary FROM employee 
        WHERE salary BETWEEN 10000 AND 20000;

    SELECT name,salary FROM employee 
        WHERE salary NOT BETWEEN 10000 AND 20000;
    
#4: keyword IS NULL( Determine whether a field is NULL You can't use the equal sign , Need to use IS)
    SELECT name,post_comment FROM employee 
        WHERE post_comment IS NULL;

    SELECT name,post_comment FROM employee 
        WHERE post_comment IS NOT NULL;
        
    SELECT name,post_comment FROM employee 
        WHERE post_comment='';  Be careful '' Is an empty string , No null
    ps：
         perform 
        update employee set post_comment='' where id=2;
         Use the previous one to check , There will be results 

#5: keyword IN Set query 
    SELECT name,salary FROM employee 
        WHERE salary=3000 OR salary=3500 OR salary=4000 OR salary=9000 ;
    
    SELECT name,salary FROM employee 
        WHERE salary IN (3000,3500,4000,9000) ;

    SELECT name,salary FROM employee 
        WHERE salary NOT IN (3000,3500,4000,9000) ;

#6: keyword LIKE Fuzzy query 
     wildcard ’%’
    SELECT * FROM employee 
            WHERE name LIKE 'eg%';

     wildcard ’_’
    SELECT * FROM employee 
            WHERE name LIKE 'al__';

1.3 GROUP BY

'''  Use alone GROUP BY Keyword grouping  SELECT post FROM employee GROUP BY post;  Be careful ： We are in accordance with the post Field grouping , that select The query field can only be post, Want to get other relevant information in the group , With the help of functions  GROUP BY Key words and GROUP_CONCAT() Functions together  SELECT post,GROUP_CONCAT(name) FROM employee GROUP BY post;# Group by position , And look at the names of the members in the group  SELECT post,GROUP_CONCAT(name) as emp_members FROM employee GROUP BY post; GROUP BY Use with aggregate functions  select post,count(id) as count from employee group by post;# Group by position , And see how many people in each group  '''

1.4 Aggregate functions

# emphasize ： Aggregate functions aggregate the contents of groups , If there is no group , The default group is 

 Example ：
    SELECT COUNT(*) FROM employee;
    SELECT COUNT(*) FROM employee WHERE depart_id=1;
    SELECT MAX(salary) FROM employee;
    SELECT MIN(salary) FROM employee;
    SELECT AVG(salary) FROM employee;
    SELECT SUM(salary) FROM employee;
    SELECT SUM(salary) FROM employee WHERE depart_id=3;

1.5 having

#！！！ Execution priority from high to low ：where > group by > having 
#1. Where  In groups group by Before , thus Where You can have any field in , But never use aggregate functions .

#2. Having In groups group by after , thus Having You can use grouped fields , Can't get to other fields directly , You can use aggregate functions 
select * from emp having salary > 100000;

1.6 ORDER BY

 Sort by single column 
    SELECT * FROM employee ORDER BY salary;
    SELECT * FROM employee ORDER BY salary ASC;
    SELECT * FROM employee ORDER BY salary DESC;

 Sort by multiple columns : First according to age Sort , If you are the same age , Then sort by salary 
    SELECT * from employee
        ORDER BY age,
        salary DESC;
select post,avg(salary) from employee group by post having avg(salary) > 10000 order by avg(salary) asc;

1.7 LIMIT

 Example ：
    SELECT * FROM employee ORDER BY salary DESC 
        LIMIT 3;                    # The default initial position is 0 
    
    SELECT * FROM employee ORDER BY salary DESC
        LIMIT 0,5; # From 0 Start , That is, first find out the first , Then include this one to check later 5 strip 

    SELECT * FROM employee ORDER BY salary DESC
        LIMIT 5,5; # From 5 Start , That is to find out the 6 strip , Then include this one to check later 5 strip 

1.8 Regular

SELECT * FROM employee WHERE name REGEXP '^ale';

SELECT * FROM employee WHERE name REGEXP 'on$';

SELECT * FROM employee WHERE name REGEXP 'm{2}';


 Summary ： How to match strings 
WHERE name = 'egon';
WHERE name LIKE 'yua%';
WHERE name REGEXP 'on$';

#  Check that the names of all employees are jin start ,n perhaps g Employee information of the result 
select * from employee where name regexp '^jin.*[gn]$';

Two 、 Multi-table query

# a key ： External link Syntax 

SELECT  Field list 
    FROM  surface 1 INNER|LEFT|RIGHT JOIN  surface 2
    ON  surface 1. Field  =  surface 2. Field ;

2.1 Cross connect ： No matching conditions apply . Generate Cartesian product

select * from employee,department;

2.2 Internal connection ： Connect only matching lines

select employee.id,employee.name,employee.age,employee.sex,department.name from employee inner join department on employee.dep_id=department.id;

2.3 Left link of outer link ： All records in the left table will be displayed first

# Subject to the left table , That is, find out all the employee information , Including, of course, employees who don't have a department 
# Nature is ： On the basis of internal connection, add the result of left side and right side 
select employee.id,employee.name,department.name as depart_name from employee left join department on employee.dep_id=department.id;

2.4 Right connection of external link ： All records in the right table will be displayed first

# Subject to the right table , That is to find out all the Department information , Including departments without employees 
# Nature is ： On the basis of the inner connection, add the result whether there is left or not on the right side 
select employee.id,employee.name,department.name as depart_name from employee right join department on employee.dep_id=department.id;

2.5 Full outer join ： Show all records of the left and right tables

 Full outer join ： On the basis of inner connection, add the results of left side with right side without and right side with left side without 
# Be careful ：mysql External connection not supported  full JOIN
# emphasize ：mysql You can use this method to indirectly realize the total external connection 
select * from employee left join department on employee.dep_id = department.id
union
select * from employee right join department on employee.dep_id = department.id;
# View results 
+------+------------+--------+------+--------+------+--------------+
| id   | name       | sex    | age  | dep_id | id   | name         |
+------+------------+--------+------+--------+------+--------------+
|    1 | egon       | male   |   18 |    200 |  200 |  technology          |
|    5 | liwenzhou  | male   |   18 |    200 |  200 |  technology          |
|    2 | alex       | female |   48 |    201 |  201 |  human resources      |
|    3 | wupeiqi    | male   |   38 |    201 |  201 |  human resources      |
|    4 | yuanhao    | female |   28 |    202 |  202 |  sales          |
|    6 | jingliyang | female |   18 |    204 | NULL | NULL         |
| NULL | NULL       | NULL   | NULL |   NULL |  203 |  operating          |
+------+------------+--------+------+--------+------+--------------+

# Be careful  union And union all The difference between ：union Will remove the same record 

2.6 Qualified connection query

# Example 1： Internal connection mode query employee and department surface , also employee In the table age Field value must be greater than 25, That is to find out that the age is older than 25 Year old employees and their departments 
select employee.name,department.name from employee inner join department
    on employee.dep_id = department.id
    where age > 25;

# Example 2： Internal connection mode query employee and department surface , And take age Display fields in ascending order 
select employee.id,employee.name,employee.age,department.name from employee,department
    where employee.dep_id = department.id
    and age > 25
    order by age asc;

2.7 Subquery

#1： Subquery is to nest one query statement into another .
#2： Query results of inner query statements , Query conditions can be provided for outer query statements .
#3： Subqueries can contain ：IN、NOT IN、ANY、ALL、EXISTS  and  NOT EXISTS Other key words 
#4： You can also include comparison operators ：= 、 !=、> 、< etc. 

2.7.1 belt IN Subqueries for keywords

# The average age of inquiry is 25 Name of department over the age of 
select id,name from department
    where id in 
        (select dep_id from employee group by dep_id having avg(age) > 25);

# Check the name of the technical staff 
select name from employee
    where dep_id in 
        (select id from department where name=' technology ');

# Insufficient view 1 Person's department name ( The subquery gets the Department of someone id)
select name from department where id not in (select distinct dep_id from employee);

2.7.2 belt ANY Subqueries for keywords

# stay  SQL  in  ANY  and  SOME  Synonyms ,SOME  The usage and function of and  ANY  As like as two peas .

# ANY  and  IN  Operator differences 1
ANY  Must be used with other comparison operators , and ANY The comparison operator must be placed in  ANY  Before keywords , The values to be compared need to match any value in the subquery , This is the same.  ANY  What it means in English 

 for example ： Use  IN  And use  ANY Operators give consistent results 
select * from employee where salary = any (
select max(salary) from employee group by depart_id);

select * from employee where salary in (
select max(salary) from employee group by depart_id);

 Conclusion ： in other words “=ANY” Equivalent to  IN  Operator , and “<>ANY” Is equivalent to  NOT IN  Operator 

# ANY and  IN  Operator differences 2
ANY  Operators cannot match fixed sets , Like the following  SQL  The statement is wrong 

SELECT
*
FROM
T_Book
WHERE
FYearPublished < ANY (2001, 2003, 2005) 

2.7.3 belt ALL Subqueries for keywords

# all Same as any similar , It's just all It means all ,any Represents any 
 Find out those employees whose salary is higher than the average salary of all departments =》 A dog coin capitalist whose salary is above the average of all departments 
select * from employee where salary > all (
select avg(salary) from employee group by depart_id);

 Find out those employees whose salary is lower than the average salary of all departments =》 The working masses of the proletariat whose wages are below the average of all departments 
select * from employee where salary < all (
select avg(salary) from employee group by depart_id);

 Find out those employees whose salary is lower than the average salary of any department =》 Employees whose salary is below the average of any department 
select * from employee where salary < any ( select avg(salary) from employee group by depart_id); 

 Find out those employees whose salary is higher than the average salary of any department =》 Employees whose salary is above the average of any department 
select * from employee where salary > any (
select avg(salary) from employee group by depart_id); 

2.7.4 A subquery with a comparison operator

# Comparison operator ：=、!=、>、>=、<、<=、<>
# Query the name and age of employees older than the average age of all 
mysql> select name,age from emp where age > (select avg(age) from emp);
+---------+------+
| name | age |
+---------+------+
| alex | 48 |
| wupeiqi | 38 |
+---------+------+
2 rows in set (0.00 sec)


# Query the names of employees older than the average age in the Department 、 Age 
select t1.name,t1.age from emp t1
inner join 
(select dep_id,avg(age) avg_age from emp group by dep_id) t2
on t1.dep_id = t2.dep_id
where t1.age > t2.avg_age; 

2.7.5 belt EXISTS Subqueries for keywords

EXISTS Close key word indicates existence . In the use of EXISTS When a keyword , Inner query statement does not return the query record .
 It returns a true or false value .True or False
 When to return to True when , Outer query statement will query ; When the return value is False when , Outer query statement does not query 
#department Presence in table dept_id=203,Ture
mysql> select * from employee
    ->     where exists
    ->         (select id from department where id=200);
+----+------------+--------+------+--------+
| id | name       | sex    | age  | dep_id |
+----+------------+--------+------+--------+
|  1 | egon       | male   |   18 |    200 |
|  2 | alex       | female |   48 |    201 |
|  3 | wupeiqi    | male   |   38 |    201 |
|  4 | yuanhao    | female |   28 |    202 |
|  5 | liwenzhou  | male   |   18 |    200 |
|  6 | jingliyang | female |   18 |    204 |
+----+------------+--------+------+--------+

#department Presence in table dept_id=205,False
mysql> select * from employee
    ->     where exists
    ->         (select id from department where id=204);
Empty set (0.00 sec)

2.7.6 in And exists

！！！！！！ When in and exists When comparing query efficiency ,in The efficiency of query is faster than exists The query efficiency of ！！！！！！

==============================exists==============================
# exists
exists The following are usually sub queries , The following subqueries are called related subqueries （ It is related to the subject sentence ）, When the subquery returns the number of rows ,exists Conditional return true,
 Otherwise return to false,exists It does not return the value of the list ,exists Only care about whether the data in brackets can be found , Is there such a record .

#  example 
 Find out which classes have students in them 
select * from class where exists (select * from stu where stu.cid=class.id)

# exists The execution principle of is ：
1、 Execute external queries in turn ： namely select * from class 
2、 Then execute a subquery for each row returned by the external query ： namely (select * from stu where stu.cid=class.cid)
3、 If the sub query returns rows , be exists Conditions established , If the condition is true, the record retrieved from the external query will be output 

==============================in==============================
# in
in Followed by subqueries ,in() The following subquery   Is to return the result set 

#  example 
 Query boys of the same age as all girls 
select * from stu where sex=' male ' and age in(select age from stu where sex=' Woman ')

# in The execution principle of is ：
in() Execution sequence and exists() Dissimilarity ,in() The subquery of will produce the result set first ,
 Then the main query goes to the result set to find the list of fields that meet the requirements . Satisfactory output , On the contrary, it does not output .

2.7.7 not in And not exists

!!!!!!not exists The efficiency of query is far higher than not in Query efficiency .!!!!!!

==============================not in==============================
not in() The execution order of subqueries is ：
 In order to prove not in establish , I can't find , You need to query the table one by one , The result set of the subquery is returned only when it meets the requirements , If not, continue to query the next record , Until the records in the table are queried , It can only be proved by querying all records , Index is not used .
                
==============================not exists==============================
not exists：
 If there are few records in the main query table , There are many records in the subquery table , And there's an index .
 for example : Query those classes that have no students 
select * from class

where not exists

(select * from student where student.cid = class.cid)

not exists The order of execution is ：
 Query in the table , It is queried according to the index , If it exists, return true, If it doesn't exist, return false, Not every record will be queried . 

Related connection （ Notes come from the classification of video courses ）：
[1]: https://www.bilibili.com/video/BV1QE41147hU?p=17
[2]: https://www.cnblogs.com/Dominic-Ji/p/11093137.html
[3]: https://zhuanlan.zhihu.com/p/114200361
[4]: https://www.cnblogs.com/linhaifeng/articles/7267596.html