mysql-Database Single Table query

An introduction

The content of this section:

query syntax

The execution priority of the keyword

Simple query

Single-condition query: WHERE

Group query: GROUP BY

Having

Query sort: ORDER BY

Limit the number of records for a query: limit

Querying using Aggregate functions

Querying using regular expressions

Company.employee employee ID ID int name emp_name varchar sex s                Ex enum age int entry Date Hire_date Date post post              VarChar Job description post_comment varchar Salary Salary double office office INT department number depart_id int# creating table Create tables employee (ID int not NULL unique auto_increment,name varcha  R () not null,sex enum (' Male ', ' female ') is not null default ' male ', #大部分是男的age int (3) unsigned NOT NULL default 28,hire_date Date not null,post varchar (+), post_comment varchar (+), salary double (15,2), office int, #一个部门一个屋子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 | |+--------------+-----------------------+------+-----+---------+----------------+ #插入记录 # Three departments: teaching, Sales, operations insert INTO Employee (NAME,SEX,AGE,HIRE_DATE,POST,SALARY,OFFICE,DEPART_ID) VALUES (' Egon ', ' Male ', 18, ' 20170301', ' The old boy's diplomatic ambassador to the Shahe office ', 7300.33,401,1, #以下是教学部 (' Alex ', ' Male ', ' 20150302 ', ' teacher ', 1000000.31,401,1 '), (' Wupeiqi ', ' Male ', Bayi, ' 20130305 ', ' teacher ', 8300,401,1 ', (' Yuanhao ', ' Male ', ' the ' 20140701 ', ' the ', ' the ', ' the ', "Teacher ', 3500,401,1 '), (' Liwenzhou ', ' Male ', ' 20121101 ', ' teacher ', ' 2100,401,1 ', ' Jingliyang ', ' female ', ' 20110211 ', ' teacher ', 9000,401,1 ', (' jinxin ') , ' Male ', ' 19000301 ', ' teacher ', 30000,401, 1), (' Into the Dragon ', ' Male ', ', ' 20101111 ', ' teacher ', 10000,401, 1), (' Crooked ', ' female ', , ' 20150311 ', ' sale ', 3000.13,402,2), #以下是销售部门 (' Ya Ya ', ' female ', ' a ', ' 20101101 ', ' Sale ', 2000.35,402,2 '), (' Ding ', ' female ', (' 20110312 ', ' sale ', 1000.37,402,2), (' stars ', ' female ', ', ' 20160513 ', ' sale ', ' 3000.29,402,2 '), (' Princess ', ' female ', 28, ' 20170127 ', ' sale ', 4000.33,402,2), (' Ono ', ' Male ', ', ' 20160311 ', ' Operation ', 10000.13,403,3 '), #以下是运营部门 (' Cheng Bite gold ', ' male ', (' 19970312 ', ' Operation ', 20000,403, 3), (' Cheng Bites silver ', ' female ', ' 20130311 ', ' Operation ', 19000,403, 3), (' Cheng Bite copper ', ' male ', 18 , ' 20150411 ', ' Operation ', 18000,403, 3), (' Cheng Bite iron ', ' female ', ' 20140512 ', ' Operation ', 17000,403, 3);

Two query syntax

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

Implementation priority for three keywords (focus)

Focus: Keyword Execution priority fromwheregroup Byhavingselectdistinctorder Bylimit

1. Find the table: from

2. Filter table records by condition: where (filtered result is a record)

3. The results are grouped by condition: GROUP BY

4. Filter the results of the grouping again: having (and where in the same way can be filtered, the difference is that having is based on the results of grouping after filtering, filtering results are also a group)

5. Isolate the result: Select

6. Go to the heavy

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

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

See more: http://www.cnblogs.com/liluning/p/7490116.html

Four simple queries

#简单查询    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 Find out the names of all employees, salary, format    < name:egon>    < salary: 3000>2 Find all the positions (remove Duplicates) 3 Find out all the employee names, and their annual salary, the field name is Annual_year

Select Concat ('< name:', name,'> '    ,'< payroll:', salary,'>               from-from-employee;

Five single-condition queries: WHERE

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 10 or 20 or 30
4. Like ' egon% '
The pattern can be either% or _,
% means any number of characters
_ Represents a character
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__ ';

Small exercise:

1. Check the position is teacher's employee name, age 2. View posts are teacher and older than 30 years old employee name, age 3. Check the position is teacher and salary within 9000-1000 range of employees name, age, salary 4. View employee information for job description NOT NULL 5. Check the position is teacher and the salary is 10000 or 9000 or 30000 employee name, age, salary 6. Check the position is teacher and the salary is not 10000 or 9000 or 30000 employee name, age, salary 7. View the position is teacher and the name is Jin beginning employee name, annual salary

View Code

Six group queries: 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 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:

If we use unique fields as the basis for grouping, then each record becomes a group, and this grouping does not make sense for a field value between multiple records, which is usually used as the basis for grouping

!!! MySQL 5.7 Default only_full_group_by semantics introduction

Small exercise:

1. Check the job title and all employee names contained in the position 2. Check the job name and the number of employees included in each position 3. Check the number of male and female employees in the company 4. Check the job name and the average salary of each position 5. Check the job name and the maximum salary of each position 6. Check the job name and the minimum salary of each position 7. Average salary for male and male employees, average salary for female and female employees

View Code

Seven have

Having the same as where usage select * from employee where salary > 10000;select * from employee has salary > 10000; the difference is: Havi NG is filtered after grouping, and Wehre is the pre-grouping filter syntax: Having can be placed after group by, and where can only be placed before group by select POST,GROUP_CONCAT (name) from employee group By post have salary > 10000; #错误, cannot be taken directly to salary field after grouping select Post,group_concat (name) from employee GROUP by post have AV G (Salary) > 10000; execution priority: Having a lower execution priority than where select COUNT (ID) from employee where salary > 10000; #正确: Where executes first, after execution Selectselect count (ID) from the employee having salary > 10000; #错误: Select executes first, after execution has, all cannot be counted count# The order of two SQL above is # #: Find table Employee---> WHERE filter----> No grouping default group--->select perform view group ID number # # Find table Employee---> Use where to filter----> No grouping then the default set of--->select perform a view of the number of IDs in the group---->having is filtered based on the results from the previous step and cannot be taken from the group to the salary value

Small exercise:

1. The number of employees in each position is less than 2 of the job name, the position contains the employee name, the number of 3. The average salary of each post is more than 10000 of the job name, 4 salary. The job name and average salary of each position is more than 10000 and less than 20000.

View Code

Eight-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;
   

Small exercise:

1. Query all employee information, sorted in ascending order of age, and if age is the same, sort by hire_date in descending order of 2. The average salary of each job is more than 10000, and the result is 3 in ascending order of average salary. Find job names and average wages for each post with an averaged salary of more than 10000, with the result descending by average salary

View Code

Nine limit the number of records to query: limit

Example:    SELECT * FROM employee ORDER by salary DESC         LIMIT 3;                    #默认初始位置为0         SELECT * from the employee ORDER by salary DESC        LIMIT 0,5; #从第0开始, that is, the first one is queried, and then this one is included. 5    SELECT * FROM Employee ORDER by salary DESC        LIMIT 5,5; #从第5开始, that is, the 6th is queried first, and then included in the article 5

Small exercise:

1. Pagination display, 5 articles per page

View Code

Ten using aggregate function queries

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;

Xi. using Regular expression queries

SELECT * FROM employee WHERE name REGEXP ' ^ale '; SELECT * FROM employee WHERE name REGEXP ' on$ '; SELECT * FROM employee where name REGEXP ' m{2} '; Summary: How the string matches the 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

'^jin.*[gn]$'; 

mysql-Database Single Table query