MySQL Database learning "seventh" single-table query

Create a table first

#创建表create table employee (ID int NOT NULL unique auto_increment,name varchar (a) not null,sex enum (' Male ', ' female ') not Nu ll default ' male ', #大部分是男的age int (3) unsigned NOT NULL default 28,hire_date date not null,post varchar (), post_comment VA Rchar, Salary double (15,2), office int, #一个部门一个屋子depart_id int), #查看表结构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 | |+--------------+-----------------------+------+-----+---------+----------------+ #插入记录 # Three departments: teaching, Sales, operations insert INTO Employee (NAME,SEX,AGE,HIRE_DATE,POST,SALARY,OFFICE,DEPART_ID) VALUES (' Egon ', ' Male ', ' 20170301 ', ' teacher ', 7300.33,401,1), #以下是教学部 (' Alex ', ' Male ', ', ' 20150302 ', ' teacher ', 1000000.31,401,1), (' Wupeiqi ', ' Male ', 81, ' 20130305 ') , ' teacher ', 8300,401,1), (' Yuanhao ', ' Male ', "the", ' 20140701 ', ' teacher ', 3500,401,1), (' Liwenzhou ', ' Male ', 28, ' 20121101 ') , ' teacher ', 2100,401,1), (' Jingliyang ', ' female ', ', ' 20110211 ', ' teacher ', 9000,401,1), (' jinxin ', ' Male ', 18, ' 19000301 ', ' teacher ', 30000,401, 1), (' Into Dragons ', ' Male ', ' 20101111 ', ' teacher ', 10000,401,1), (' Crooked ', ' female ', ' 20150311 ', ' sale ', ' 3000.13,402,2 '), #以下是销售部门 (' Ya Ya ', ' female ', ', ' 20101101 ', ' sale ', 2000.35,402,2), (' Ding ', ' female ', +, ' 20110312 ', ' sale ', 1000.37,402,2), (' stars ', ' female ', ' 20160513 ', ' sale ', 3000.29,402,2), (' Princess ', ' female ', ' I ', ' 20170127 ', ' sale ', 4000.33,402,2), (' Zhang Ye ', ' Male ', ' 20160311 ', ' Operation ', 10000.13,403,3), #以下是运营部门 (' Cheng Bite gold ', ' Male ', ', ' 19970312 ', ' Operation ', 20000,403, 3), (' Cheng Bite silver ', ' female ', 18, ' 20130311 ', ' Operation ', 19000,403, 3), (' Process bite copper ', ' male ', ' ', ' 20150411 ', ' Operation ', 18000,403, 3), (' Cheng Bite iron ', ' female ', 18, ' 20140512 ', ' Operation ', 17000,403, 3);

#一点小知识复习

1. Note:
SELECT * FROM t1 where Condition group by Group Field
1. Groups can only query the group field, to see the rest of the use of aggregate functions
2. Classification of aggregation functions: Count,min,max,avg,group_concat,sum and so on.
3. Fuzzy matching: Use the LIKE keyword.
SELECT * from t1 where name is like '%eg% '; #% denotes any character
SELECT * from t1 where name is like ' d__l '; #一个下划线表示一个字符, two underscores represent two characters
4. Copy table: CREATE TABLE t2 select * from T1;
CREATE TABLE T2 SELECT * from T1 where 1=2;

First, query syntax

SELECT Field 1, Field 2 ... From table name                  WHERE condition                  GROUP by field have                  filter                  ORDER by field limit                  number of bars

Second, 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, #避免重复DISTINCT    Select DISTINCT post from employee;    #通过四则运算查询    SELECT name, salary*12 from employee;    SELECT name, salary*12 as annual_salary from employee;    Select name, salary*12 annual_salary from employee, #定义显示格式   CONCAT () function for connection string   SELECT CONCAT (' name: ', Name, '  Annual salary: ', salary*12)  As annual_salary from    employee;      Concat_ws () The first parameter is a delimiter of   SELECT concat_ws (': ', name,salary*12) as  annual_salary from    employee;

Small exercise:

1 Identify all employees by name, salary, format    < name:egon>    < salary: 3000>select concat (' < name: ', Name, ' > ',  ' < salary: ', Salary, ' > ') from employee;2 to isolate all positions (remove Duplicates) SELECT distinct depart_id from employee;3 to find out all employee names, and their annual salary, annual salary of the field named Annual Select NAME,SALARY*12 annual salary from employee;

Third, where constraints

The WHERE clause can be used:

1. Comparison operators:> < >= <= <>! =
2. Between and 100 values between 10 and 20
3. In (80,90,100) value is 80 or 90 or 100
4. Like ' eg% '
Can be% or _,
% means any number of characters
_ Represents a character

Like ' E__n ':
5. Logical operators: logical operators and or not can be used directly in multiple conditions

#1: Single-condition query SELECT name from employee WHERE post= ' sale '; #2: Multi-criteria Query SELECT name,salary from employee WHERE post= ' teacher ' and salary>10000; #3: keyword between and select n    Ame,salary from employee WHERE salary between 10000 and 20000;    SELECT Name,salary from the employee WHERE salary not between 10000 and 20000;    #4: The keyword is null (determines whether a field is null, cannot be used as an equals sign, need to be) SELECT name,post_comment from employee WHERE post_comment is null;            SELECT name,post_comment from employee WHERE post_comment are not NULL; SELECT name,post_comment from employee WHERE post_comment= ';        Note ' is an empty string, not a null PS: Perform update employee set post_comment= ' where id=2; And then check it out, there's going to be a result. # #: keyword in collection query SELECT name,salary from employee WHERE salary=3000 or salary=3500 or salary=4000 or S        alary=9000;    SELECT Name,salary from the 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 the employee WHERE name like ' al__ ';

Iv. having a filter

The having and where syntax is the same.

SELECT * FROM employee where id>15;    SELECT * FROM employee have id>15;   

But having and where is not the same place lies in the following points!!!

#！！！ Execution priority high to Low: where > Group by > Aggregate function > Having >order by

1.where and having the difference                                                                                     1. Where is a constraint declaration that uses where to constrain data from the database, where it works before the result is returned                                             (the table is found first, the data is fetched from the table (file) according to where constraints, and the aggregate function 2 is not used in where                                                   .) Having is a filter declaration that filters the results of a query after the query returns a result set                                                          (the table is found, the data is fetched from the table (file) according to where constraints, and then the group by group,                                                      if there is no group By then all records are set as a whole, then the aggregate function is executed, then the result of the aggregation is filtered using having, and                                           the aggregation function can be used in the having.                                                                               the priority of the 3.where is higher                                                                          than the having priority 4.having can be placed after group by, and where can only be placed before group by.                                               

Verify the difference:

1. View the employee's id>15 how many select count (ID) from employee where id>15; #正确, analysis: Where executes first, after performing aggregate count (ID),                                            Then select out Result select COUNT (ID) from employee have id>15; #报错, Analysis: Perform the aggregate count (ID) first, after performing a having filter,                                            #无法对id进行id >15 Filter # above two SQL order is 1: Find table Employee---> Where to filter----> No grouping is the default set of execution aggregation count (ID)--->select execution view number of IDs in Group 2: Table Employee---> No grouping The default group performs an aggregate count (ID)---->having Based on the results of the previous aggregation (at this point only the count (ID) field) is id>15 filtered, it is clear that the ID field cannot be obtained at all

#having Example
1------have-----------2 select Depart_id,count (ID) from the Employee GROUP by DEPART_ID;3 Select Depart_id,count (ID) from E Mployee GROUP BY depart_id have depart_id = 3;4 select Depart_id,count (ID) from the employee group by DEPART_ID have count (ID) >7;5 select max (salary) maximum wage from employee where ID>2 GROUP by DEPART_ID have count (ID) >3;6 SELECT * from E Mployee where id>7; #查看所有id >7 's Employee information

Small exercise:

1. Inquire about the number of employees who are less than 2 in each position, including the name of the employee, the number of select POST,GROUP_CONCAT (name) employee name, count (ID) number from the employee GROUP by Post has C Ount (ID) <2;2. The job name and average salary for each job is more than 10000, select Post,avg (Salary) from the employee GROUP by Post have AVG (Salary) >10000;3. Check job name and average salary of each post with a value of more than 10000 and less than 20000, select Post,avg (Salary) from the  employee GROUP by Post have  avg (Salary) between 10000 and 20000;

Cluster Query GROUP BY

Major premise: You can group by any field, but after the group is divided, can only see the group of that field, to take the other field information in the group, you need to use the function

Use the group BY keyword to group    Select post from the employee group by post alone;    Note: We group by post field, then the field of the select query can only be post, want to get other related information within the group, need to use the function GROUP by keyword and group_concat () function with      select Post,group_ Concat (name) from  the employee group by post; #按照岗位分组 and view the group member name      select  post,group_concat (name) as Emp_members From the employee group by post; Group BY is used with the aggregation function    select Post,count (ID) as count from the employee group by post; #按照岗位分组, and see how many people are in each group

Emphasize:

Group: Generally the same number of words can be divided into a group (must be a duplicate field)
Small exercise:

1. Search for job name and all employee names included in the post select Post,group_concat (name) from employee group by post;
2. Query the job name and the number of employees in each position select Post,count (ID) from the employee group by post;
3. Query the number of male and female employees in the company Select Sex,count (ID) from the employee group by sex;
4. Check the job name and the average salary for each position select Post,max (Salary) from the employee group by post;
5. Search for the job name and the highest salary for each position select Post,max (Salary) from employee group by post;
6. Check the job name and the minimum salary for each position select Post,min (Salary) from the employee group by post;
7. Query the average salary of male and female employees, and the average salary of female employees and employees select SEX,AVG (Salary) from employee group by sex;

VI. implementation priorities for keywords (focus)

Focus: Keyword Execution priority fromwheregroup Byhavingselectdistinctorder Bylimit

1. Find the table: from

2. Take the where specified constraints, go to the File/table to remove a record

3. Group BY, if no group by, is grouped by the records taken out.

4. If there is an aggregation function, the group is aggregated

5. Filter the results of 4: having

6. Isolate the result: Select

7. Go to the heavy

8. Sort the results of 6 by criteria: ORDER BY

9. Limit the results of 7 to show the number of bars

Vii. Query sort ORDER BY

Sort by single column    SELECT * FROM employee ORDER by salary;    SELECT * FROM employee ORDER by salary ASC;    SELECT * from the employee order by salary DESC; Sort by multiple columns: Sort by the age first, and if you are older, sort by salary    SELECT * FROM employee        ORDER by Age,
   salary DESC;

===========order By==========1.select * From the employee order by salary; #如果不指定, the default is ascending 2.select * from the employee ORDER by Salar Y Asc;3.select * from the employee ORDER by salary desc; #先按照年龄升序, when the age of the same too many, the size is not clear, in accordance with the wages in descending 4.select * from the employee ORDER by ASC, Salary desc;

Small example:

1. Query all employee information, sorted in ascending order of age, if age is the same, sort by hire_date in descending order select * Form employee ORDER by Age,hire_date Desc;2. Check the job name and average salary of the job with the median pay of more than 10000, the result is sorted by average salary ascending select post, avg (salary) from employee GROUP by Post have AVG (Salary) >10000; 3. Inquire about the job name and average salary of the job with the median pay of more than 10000, the result is the average salary descending order select Post, AVG (Salary) from the employee GROUP by Post have AVG (Salary) >10000 Desc

Viii. using aggregate functions to query

First from Find table

Then use the WHERE condition constraint to take the record out of the table

Group BY is then grouped by default, without grouping

And then do the aggregation

The final Select results

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) Form employee WHERE depart_id=3;

Ix. additions to where (using regular expression queries)

1.select * FROM employee where name RegExp ' ^ale ';  #匹配以ale开头的员工信息2. Select * FROM employee where name RegExp ' on$ '; #匹配以on结尾的员工信息3. Select * FROM employee where name RegExp ' n{1,2} '; #匹配name里面包含1到2个n的员工信息小结: Match the string in the way where name = ' Egon '; where name like ' yua% '; where name regexp ' on$ ';

Small exercise:

View employee information for all employees whose name is Jin start, n or G results select * from employee where name RegExp ' ^jin.*[ng]$ ';

X. Limit the number of records to query: limit

=========limit: Limit printing of several =========1.select * from employee limit 3; #打印前三条2. This means: Where to start, take a few back (such operations are generally used for paging) SELECT * FROM Employee Limit 0,3;select * FROM employee limit 3,4;select * FROM employee limit 6,3;select * from employee limit 9,3;3.se Lect * FROM employee ORDER BY id DESC limit 3; #查看后三条

Little Practice

1. Pagination display, 5 articles per page

SELECT * from-employee limit 0,5;select * From-employee limit 5,5;select * From employee limit 10, 5;

MySQL Database learning "seventh" single-table query