Syntax of form queries and priority of keyword execution
Form Query Syntax
SELECT DISTINCT Field 1,Field 2... FROM Table Name
WHERE condition
GROUP BY field
HAVING screen
ORDER BY field
LIMIT Limit number of barsPriority of keyword execution
from where group by select distinct having order by limit
1. Find the table:from
2. Take the constraint specified by where and fetch a record from the file/table
3. Group the extracted records into groups by, or as a group if there is no group by
4. Execute select (de-weighting)
5. having filters the grouped results
6. Sort results by condition: order by
7. Limit the number of displayed bars of results
Simple Query
company.employee
//Employee id int
//Name emp_name varchar
//Sex enum
//Age int
//Enrollment Date hire_date
//Post varchar
//Job description post_comment varchar
//Salary salary double
//Office int
//Department number depart_id int
#Create Table
create table employee(
id int not null unique auto_increment,
emp_name varchar(20) not null,
sex enum('male','female') not null default 'male', #Mostly male
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 house
depart_id int
);
#View table structure
mysql> desc employee;
+--------------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-----------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| emp_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, operation
insert into employee(emp_name,sex,age,hire_date,post,salary,office,depart_id) values
('egon','male',18,'20170301','Foreign Ambassador of Old Boy's Shahe Office',7300.33,401,1), #Below is the Department of Education
('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),#The following are the sales departments
('Ya Ya','female',38,'20101101','sale',2000.35,402,2),
('Dingding','female',18,'20110312','sale',1000.37,402,2),
('Stars','female',18,'20160513','sale',3000.29,402,2),
('sound made by a hen','female',28,'20170127','sale',4000.33,402,2),
('Zhang Ye','male',28,'20160311','operation',10000.13,403,3), #Here is the Operations Department
('Cheng Bite Gold','male',18,'19970312','operation',20000,403,3),
('Cheng Bite Silver','female',18,'20130311','operation',19000,403,3),
('Cheng Bite Copper','male',18,'20150411','operation',18000,403,3),
('Cheng Bite Iron','female',18,'20140512','operation',17000,403,3)
;
#ps: If you insert Chinese characters in a windows system and the result of select is blank, you can unify all character encoding to gbk
//Prepare tables and records
#Simple Query
SELECT id,emp_name,sex,age,hire_date,post,post_comment,salary,office,depart_id
FROM employee;
SELECT * FROM employee;
SELECT emp_name,salary FROM employee;
#Avoid duplicate DISTINCT
SELECT DISTINCT post FROM employee;
#Query by Four Operations
SELECT emp_name, salary*12 FROM employee;
SELECT emp_name, salary*12 AS Annual_salary FROM employee;
SELECT emp_name, salary*12 Annual_salary FROM employee;
#Define display format
CONCAT() Function used to connect strings
SELECT CONCAT('Full name: ',emp_name,' Annual salary: ', salary*12) AS Annual_salary
FROM employee;
CONCAT_WS() The first parameter is the delimiter
SELECT CONCAT_WS(':',emp_name,salary*12) AS Annual_salary
FROM employee;
//Combined with CASE statement:
SELECT
(
CASE
WHEN emp_name = 'jingliyang' THEN
emp_name
WHEN emp_name = 'alex' THEN
CONCAT(emp_name,'_BIGSB')
ELSE
concat(emp_name, 'SB')
END
) as new_name
FROM
employee;
Practice:
1 Find out the names and salaries of all employees in the format
<Name: egon> <Salary: 3000>
2 Identify all positions (remove duplicates)
3 Find out the names of all employees and their annual salary. The field name of the annual salary is annual_year
select concat('<Name:',emp_name,'> ','<salary:',salary,'>') from employee;
select distinct depart_id from employee;
select emp_name,salary*12 annual_salary from employee;where Constraint
where clauses can be used:
\1.Comparison operator: > < >= <= <>!=
\2. between 80 and 100 values between 80 and 100
\3. in(80,90,100) values are 80 or 90 or 100
\4. like 'e%'
Wildcards can be%or_,
%for any number of characters
_Represents a character
\5. Logical Operator: The logical operator and or not can be used directly under multiple conditions
#1:Single Conditional Query
SELECT emp_name FROM employee
WHERE post='sale';
#2:Multiple Conditional Query
SELECT emp_name,salary FROM employee
WHERE post='teacher' AND salary>10000;
#3:Keyword BETWEEN AND
SELECT emp_name,salary FROM employee
WHERE salary BETWEEN 10000 AND 20000;
SELECT emp_name,salary FROM employee
WHERE salary NOT BETWEEN 10000 AND 20000;
#4: Keyword IS NULL (IS is required to determine if a field is NULL and not equal sign)
SELECT emp_name,post_comment FROM employee
WHERE post_comment IS NULL;
SELECT emp_name,post_comment FROM employee
WHERE post_comment IS NOT NULL;
SELECT emp_name,post_comment FROM employee
WHERE post_comment=''; Be careful''Is an empty string, not null
ps:
//implement
update employee set post_comment='' where id=2;
//If you use the previous view, you will have a result.
#5:Key IN Collection Query
SELECT emp_name,salary FROM employee
WHERE salary=3000 OR salary=3500 OR salary=4000 OR salary=9000 ;
SELECT emp_name,salary FROM employee
WHERE salary IN (3000,3500,4000,9000) ;
SELECT emp_name,salary FROM employee
WHERE salary NOT IN (3000,3500,4000,9000) ;
#6:Keyword LIKE Fuzzy Query
//Wildcard'%'
SELECT * FROM employee
WHERE emp_name LIKE 'eg%';
//Wildcards''
SELECT * FROM employee
WHERE emp_name LIKE 'al__';
Practice:
1. Check the name and age of the employee whose position is teacher 2. Check the name and age of the employee whose job is a teacher who is older than 30 3. View the name, age and salary of the employee whose job is a teacher and whose salary ranges from 9000 to 10000 4. View employee information whose job description is not NULL 5. View the name, age, salary of an employee whose job is a teacher and whose salary is 10,000 or 9,000 or 30,000 6. Check the name, age, salary of an employee whose job is a teacher and whose salary is not 10,000 or 9,000 or 30,000 7. Check the name and salary of the employee whose job is a teacher and whose name starts with jin
select emp_name,age from employee where post = 'teacher'; select emp_name,age from employee where post='teacher' and age > 30; select emp_name,age,salary from employee where post='teacher' and salary between 9000 and 10000; select * from employee where post_comment is not null; select emp_name,age,salary from employee where post='teacher' and salary in (10000,9000,30000); select emp_name,age,salary from employee where post='teacher' and salary not in (10000,9000,30000); select emp_name,salary*12 from employee where post='teacher' and emp_name like 'jin%';
group by
Grouping using GROUP BY keywords alone
SELECT post FROM employee GROUP BY post;
Note: If we group by post field, then the field of the select query can only be post. To get other relevant information within the group, you need to use the function
GROUP BY keyword used with GROUP_CONCAT() function
SELECT post,GROUP_CONCAT(emp_name) FROM employee GROUP BY post; #Group by job and view group member names
SELECT post,GROUP_CONCAT(emp_name) as emp_members FROM employee GROUP BY post;
GROUP BY is used with aggregate functions
select post,count(id) as count from employee group by post; #Group by job and see how many people are in each group
Emphasize:
If we use the fields of unique ness as the basis for grouping, then each record is grouped into its own group, which makes no sense A field with the same value between multiple records is often used as a basis for grouping
Aggregate function
#Emphasis: Aggregate functions aggregate the contents of groups, and default groups if no groups exist
//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;
Practice:
1. Query job name and all employee names included in the job 2. Query job names and the number of employees included in each job 3. Query the number of male and female employees in the company 4. Query job names and average salaries for each job 5. Query job names and maximum salaries for each job 6. Query job names and minimum salaries for each job 7. Query average salaries of male and female employees
#Question 1: Grouping mysql> select post,group_concat(emp_name) from employee group by post; +-----------------------------------------+---------------------------------------------------------+ | post | group_concat(emp_name) | +-----------------------------------------+---------------------------------------------------------+ | operation | Zhang Ye,Cheng Bite Gold,Cheng Bite Silver,Cheng Bite Copper,Cheng Bite Iron | | sale | Crooked,Ya Ya,Dingding,Stars,sound made by a hen | | teacher | alex,wupeiqi,yuanhao,liwenzhou,jingliyang,jinxin,Jackie Chan | | Foreign Ambassador of Old Boy's Shahe Office | egon | +-----------------------------------------+---------------------------------------------------------+ #Title 2: mysql> select post,count(id) from employee group by post; +-----------------------------------------+-----------+ | post | count(id) | +-----------------------------------------+-----------+ | operation | 5 | | sale | 5 | | teacher | 7 | | Foreign Ambassador of Old Boy's Shahe Office | 1 | +-----------------------------------------+-----------+ #Title 3: mysql> select sex,count(id) from employee group by sex; +--------+-----------+ | sex | count(id) | +--------+-----------+ | male | 10 | | female | 8 | +--------+-----------+ #Title 4: mysql> select post,avg(salary) from employee group by post; +-----------------------------------------+---------------+ | post | avg(salary) | +-----------------------------------------+---------------+ | operation | 16800.026000 | | sale | 2600.294000 | | teacher | 151842.901429 | | Foreign Ambassador of Old Boy's Shahe Office | 7300.330000 | +-----------------------------------------+---------------+ #Title 5 mysql> select post,max(salary) from employee group by post; +-----------------------------------------+-------------+ | post | max(salary) | +-----------------------------------------+-------------+ | operation | 20000.00 | | sale | 4000.33 | | teacher | 1000000.31 | | Foreign Ambassador of Old Boy's Shahe Office | 7300.33 | +-----------------------------------------+-------------+ #Title 6 mysql> select post,min(salary) from employee group by post; +-----------------------------------------+-------------+ | post | min(salary) | +-----------------------------------------+-------------+ | operation | 10000.13 | | sale | 1000.37 | | teacher | 2100.00 | | Foreign Ambassador of Old Boy's Shahe Office | 7300.33 | +-----------------------------------------+-------------+ #Topic Seven mysql> select sex,avg(salary) from employee group by sex; +--------+---------------+ | sex | avg(salary) | +--------+---------------+ | male | 110920.077000 | | female | 7250.183750 | +--------+---------------+
HAVING Filtering
The difference between HAVING and WHERE is that!!!!!!
#!!!Execution priority ranges from high to low: where > group by > having #1. Where occurs before grouping group by, so there can be any field in Where, but aggregate functions should never be used. #2. Having occurs after grouping group by, so you can use grouped fields in Having, not other fields directly, and you can use aggregate functions
mysql> select @@sql_mode; +--------------------+ | @@sql_mode | +--------------------+ | ONLY_FULL_GROUP_BY | +--------------------+ row in set (0.00 sec) mysql> select * from emp where salary > 100000; +----+------+------+-----+------------+---------+--------------+------------+--------+-----------+ | id | emp_name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+------+------+-----+------------+---------+--------------+------------+--------+-----------+ | 2 | alex | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 | +----+------+------+-----+------------+---------+--------------+------------+--------+-----------+ row in set (0.00 sec) mysql> select post,group_concat(emp_name) from emp group by post having salary > 10000;#Error, cannot fetch salary field directly after grouping ERROR 1054 (42S22): Unknown column 'salary' in 'having clause' mysql> select post,group_concat(emp_name) from emp group by post having avg(salary) > 10000; +-----------+-------------------------------------------------------+ | post | group_concat(emp_name) | +-----------+-------------------------------------------------------+ | operation | Cheng Bite Iron,Cheng Bite Copper,Cheng Bite Silver,Cheng Bite Gold,Zhang Ye | | teacher | Jackie Chan,jinxin,jingliyang,liwenzhou,yuanhao,wupeiqi,alex | +-----------+-------------------------------------------------------+ rows in set (0.00 sec)
Practice:
1. Query the number of positions with less than 2 employees, the number of positions with employee names and numbers 3. Query the position names and average wages whose average salary is more than 10,000 4. Query the position names and average wages whose average salary is more than 10000 and less than 2000
#Question 1: mysql> select post,group_concat(emp_name),count(id) from employee group by post having count(id) < 2; +-----------------------------------------+--------------------+-----------+ | post | group_concat(emp_name) | count(id) | +-----------------------------------------+--------------------+-----------+ | Foreign Ambassador of Old Boy's Shahe Office | egon | 1 | +-----------------------------------------+--------------------+-----------+ #Title 2: mysql> select post,avg(salary) from employee group by post having avg(salary) > 10000; +-----------+---------------+ | post | avg(salary) | +-----------+---------------+ | operation | 16800.026000 | | teacher | 151842.901429 | +-----------+---------------+ #Title 3: mysql> select post,avg(salary) from employee group by post having avg(salary) > 10000 and avg(salary) <20000; +-----------+--------------+ | post | avg(salary) | +-----------+--------------+ | operation | 16800.026000 | +-----------+--------------+
ORDER BY Query Sorting
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 by age, then by salary if you are the same age
SELECT * from employee
ORDER BY age,
salary DESC;
Practice:
1. Query all employee information, first in ascending order by age, then in descending order by hire_date if age is the same 2. Query job names and average wages whose average salary is more than 10,000, and rank the results in ascending order of average salary 3. Query job names and average wages whose average salary is more than 10,000, and rank the results in descending order of average salary
#Title 1 mysql> select * from employee ORDER BY age asc,hire_date desc; #Title 2 mysql> select post,avg(salary) from employee group by post having avg(salary) > 10000 order by avg(salary) asc; +-----------+---------------+ | post | avg(salary) | +-----------+---------------+ | operation | 16800.026000 | | teacher | 151842.901429 | +-----------+---------------+ #Title 3 mysql> select post,avg(salary) from employee group by post having avg(salary) > 10000 order by avg(salary) desc; +-----------+---------------+ | post | avg(salary) | +-----------+---------------+ | teacher | 151842.901429 | | operation | 16800.026000 | +-----------+---------------+
LIMIT limits the number of records queried
Example:
SELECT * FROM employee ORDER BY salary DESC
LIMIT 3; #Default initial position is 0
SELECT * FROM employee ORDER BY salary DESC
LIMIT 0,5; #Starting from 0, the first item is queried first, then included, and then searched 5 items later
SELECT * FROM employee ORDER BY salary DESC
LIMIT 5,5; #Starting from the fifth, that is, first look up Article 6, then include it and look up Article 5
Practice:
1. Page breaks, with 5 entries per page
mysql> select * from employee limit 0,5; +----+-----------+------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+ | id | emp_name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+-----------+------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+ | 1 | egon | male | 18 | 2017-03-01 | Foreign Ambassador of Old Boy's Shahe Office | NULL | 7300.33 | 401 | 1 | | 2 | alex | male | 78 | 2015-03-02 | teacher | | 1000000.31 | 401 | 1 | | 3 | wupeiqi | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 | | 4 | yuanhao | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 | | 5 | liwenzhou | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 | +----+-----------+------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+ rows in set (0.00 sec) mysql> select * from employee limit 5,5; +----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+ | id | emp_name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+ | 6 | jingliyang | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 | | 7 | jinxin | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 | | 8 | Jackie Chan | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 401 | 1 | | 9 | Crooked | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 | | 10 | Ya Ya | female | 38 | 2010-11-01 | sale | NULL | 2000.35 | 402 | 2 | +----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+ rows in set (0.00 sec) mysql> select * from employee limit 10,5; +----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+ | id | emp_name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+ | 11 | Dingding | female | 18 | 2011-03-12 | sale | NULL | 1000.37 | 402 | 2 | | 12 | Stars | female | 18 | 2016-05-13 | sale | NULL | 3000.29 | 402 | 2 | | 13 | sound made by a hen | female | 28 | 2017-01-27 | sale | NULL | 4000.33 | 402 | 2 | | 14 | Zhang Ye | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 | | 15 | Cheng Bite Gold | male | 18 | 1997-03-12 | operation | NULL | 20000.00 | 403 | 3 | +----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+ rows in set (0.00 sec)
Query using regular expressions
SELECT * FROM employee WHERE emp_name REGEXP '^ale';
SELECT * FROM employee WHERE emp_name REGEXP 'on$';
SELECT * FROM employee WHERE emp_name REGEXP 'm{2}';
//Summary: How strings are matched
WHERE emp_name = 'egon';
WHERE emp_name LIKE 'yua%';
WHERE emp_name REGEXP 'on$';
Practice:
View employee information for all employees with a name starting with jin, n or g
select * from employee where emp_name regexp '^jin.*[gn]$';