(vii) MySQL data operation dql: Single table Query

(1) Single-table query 1) Environment preparation

mysql> CREATE TABLE company.employee5(id int primary key AUTO_INCREMENT not null,name varchar(30) not null,sex enum(‘male‘,‘female‘) default ‘male‘ not null,hire_date date not null,post varchar(50) not null,job_description varchar(100),salary double(15,2) not null,office int,dep_id int);

insert intocompany.employee5(name,sex,hire_date,post,job_description,salary,office,dep_id) values(‘jack‘,‘male‘,‘20180202‘,‘instructor‘,‘teach‘,5000,501,100),(‘tom‘,‘male‘,‘20180203‘,‘instructor‘,‘teach‘,5500,501,100),(‘robin‘,‘male‘,‘20180202‘,‘instructor‘,‘teach‘,8000,501,100),(‘alice‘,‘female‘,‘20180202‘,‘instructor‘,‘teach‘,7200,501,100),(‘tianyun‘,‘male‘,‘20180202‘,‘hr‘,‘hrcc‘,600,502,101),(‘harry‘,‘male‘,‘20180202‘,‘hr‘,NULL,6000,502,101),(‘emma‘,‘female‘,‘20180206‘,‘sale‘,‘salecc‘,20000,503,102),(‘christine‘,‘female‘,‘20180205‘,‘sale‘,‘salecc‘,2200,503,102),(‘zhuzhu‘,‘male‘,‘20180205‘,‘sale‘,NULL,2200,503,102),(‘gougou‘,‘male‘,‘20180205‘,‘sale‘,‘‘,2200,503,102);

2) Simple Query

Grammar

select 字段1,字段2 from 表名;select * from 表名;

Querying data for a specified field in a table

mysql> select name,salary,post from employee5;+-----------+----------+------------+| name      | salary   | post       |+-----------+----------+------------+| jack      |  5000.00 | instructor || tom       |  5500.00 | instructor || robin     |  8000.00 | instructor || alice     |  7200.00 | instructor || tianyun   |   600.00 | hr         || harry     |  6000.00 | hr         || emma      | 20000.00 | sale       || christine |  2200.00 | sale       || zhuzhu    |  2200.00 | sale       || gougou    |  2200.00 | sale       |+-----------+----------+------------+

Querying all data in a table

Mysql> SELECT * from employee5;+----+-----------+--------+------------+------------+-----------------+--------- -+--------+--------+| ID | name | sex | hire_date | Post | job_description | Salary | Office |  dep_id |+----+-----------+--------+------------+------------+-----------------+----------+--------+--------+| 1 | Jack | Male | 2018-02-02 | Instructor |  Teach |    5000.00 |    501 |  100 | | 2 | Tom | Male | 2018-02-03 | Instructor |  Teach |    5500.00 |    501 |  100 | | 3 | Robin | Male | 2018-02-02 | Instructor |  Teach |    8000.00 |    501 |  100 | | 4 | Alice | Female | 2018-02-02 | Instructor |  Teach |    7200.00 |    501 |  100 | | 5 | Tianyun | Male | 2018-02-02 | HR |   HRCC |    600.00 |    502 |  101 | | 6 | Harry | Male | 2018-02-02 | HR |  NULL |    6000.00 |    502 |  101 | | 7 | Emma | Female | 2018-02-06 | Sale |      Salecc    |    20000.00 |    503 |  102 | | 8 | Christine | Female | 2018-02-05 | Sale |  SALECC |    2200.00 |    503 |  102 | | 9 | ZhuZhu | Male | 2018-02-05 | Sale |  NULL |    2200.00 |    503 | 102 | | 10 | Gougou | Male | 2018-02-05 |                 Sale |  |    2200.00 |    503 | 102 |+----+-----------+--------+------------+------------+-----------------+----------+--------+--------+

3) Avoid distinct, usually only for one field

Grammar

select distinct 字段 from 表名;

Query the department in the employee table, and go back to the Department field

mysql> select distinct post from employee5;+------------+| post       |+------------+| instructor || hr         || sale       |+------------+3 rows in set (0.00 sec)mysql> select post from employee5;+------------+| post       |+------------+| instructor || instructor || instructor || instructor || hr         || hr         || sale       || sale       || sale       || sale       |+------------+10 rows in set (0.00 sec)

4) through arithmetic query,

Grammar:
Select field 1, Field 2, field number from table name;
Select field 1, Field 2, Field 2 number as new field name from table name
Select field 1, Field 2, field-Number new field name from table name
14 annual salary According to monthly water

  mysql> Select name,salary,salary*14 from Employee5;   \ \ Create a new annual salary 14 salary field (salary*14) mysql> Select Name,salary,salary*14 as annual_salary from Employee5;   \ \ Give salary*14 this setting an alias mysql> select Name,salary,salary*14 annual_salary from Employee5; \\salary*14 set alias (annual_salary)  

Mysql> select name,salary,salary*14 from Employee5; +-----------+----------+-----------+| name | Salary | salary*14 |+-----------+----------+-----------+|  Jack |  5000.00 | 70000.00 | |  Tom |  5500.00 | 77000.00 | |  Robin | 8000.00 | 112000.00 | |  Alice | 7200.00 | 100800.00 | |   Tianyun |   600.00 | 8400.00 | |  Harry |  6000.00 | 84000.00 | | Emma | 20000.00 | 280000.00 | |  Christine |  2200.00 | 30800.00 | |  ZhuZhu |  2200.00 | 30800.00 | |  Gougou |  2200.00 | 30800.00 |+-----------+----------+-----------+10 rows in Set (0.00 sec) mysql> Select Name,salary,salary*14 as Annual_ Salary from employee5;+-----------+----------+---------------+| name | Salary | Annual_salary |+-----------+----------+---------------+|  Jack |      5000.00 | 70000.00 | |  Tom |      5500.00 | 77000.00 | |  Robin |     8000.00 | 112000.00 | |  Alice |     7200.00 | 100800.00 | |   Tianyun |       600.00 | 8400.00 | |  Harry | 6000.00 |      84000.00 | | Emma |     20000.00 | 280000.00 | |  Christine |      2200.00 | 30800.00 | |  ZhuZhu |      2200.00 | 30800.00 | |  Gougou |      2200.00 | 30800.00 |+-----------+----------+---------------+10 rows in Set (0.00 sec) mysql> Select name,salary,salary*14 Annual_salary from employee5;+-----------+----------+---------------+| name | Salary | Annual_salary |+-----------+----------+---------------+|  Jack |      5000.00 | 70000.00 | |  Tom |      5500.00 | 77000.00 | |  Robin |     8000.00 | 112000.00 | |  Alice |     7200.00 | 100800.00 | |   Tianyun |       600.00 | 8400.00 | |  Harry |      6000.00 | 84000.00 | | Emma |     20000.00 | 280000.00 | |  Christine |      2200.00 | 30800.00 | |  ZhuZhu |      2200.00 | 30800.00 | |  Gougou |      2200.00 | 30800.00 |+-----------+----------+---------------+10 rows in Set (0.00 sec)

5) define display format: Contcat () function for connection string

mysql>  select concat(name,‘annual salary:‘,salary*14) as annual_salary from employee5;+---------------------------------+| annual_salary                   |+---------------------------------+| jackannual salary:70000.00      || tomannual salary:77000.00       || robinannual salary:112000.00    || aliceannual salary:100800.00    || tianyunannual salary:8400.00    || harryannual salary:84000.00     || emmaannual salary:280000.00     || christineannual salary:30800.00 || zhuzhuannual salary:30800.00    || gougouannual salary:30800.00    |+---------------------------------+

(2) Single condition inquiry

Grammar:
Select field 1, Field 2 from table name where condition;

1) Single Condition query

mysql> select name,salary from employee5 where name=‘jack‘;+------+---------+| name | salary  |+------+---------+| jack | 5000.00 |+------+---------+

2) Multi-criteria query: query for names with wages greater than 5000 and less than 20000

Grammar:
Select Select Field 1, Field 2 from table name where Condition 1 and condition 2;
Select Select Field 1, Field 2 from table name where Condition 1 or condition 2;

mysql> select name,salary from employee5 where salary > 5000 and salary <20000;+-------+---------+| name  | salary  |+-------+---------+| tom   | 5500.00 || robin | 8000.00 || alice | 7200.00 || harry | 6000.00 |+-------+---------+

3) keyword between and

Query for names with wages greater than or equal to 5000 and less than or equal to 20000

mysql> select name,salary from employee5 where salary between 5000 and 20000;mysql> select name,salary from employee5 where salary not  between 5000 and 20000;

4) keyword is null

The query field is an empty value

mysql> select name,job_description from employee5 where job_description is null;

5) keyword in collection query

Query wages are 5000 and 6000 of those who are, and who are not

mysql> select name,salary from employee5 where salary=5000 or salary=6000;mysql> select name,salary from employee5 where salary in(5000,6000);mysql> select name,salary from employee5 where salary not in(5000,6000);

6) Fuzzy query, keyword: like

Wildcard:%, matches any length character

mysql> select * from employee5 where name like ‘al%‘;

Wildcard: _ matches any single character

mysql> select * from employee5 where name like ‘al_‘;

(3) Query sort 1) Single-column sorting

按照薪水排序mysql> select * from employee5 order by salary;   //升序按照薪水降序mysql> select * from employee5 order by salary desc; //降序

2) Sort by multiple columns

Sort by first entry time, then by salary

mysql> select * from employee5 order by hire_date ,salary asc;

(4) Limit the number of records for a query

mysql> select * from employee5 order by salary limit 5;    \\取前5条数据，默认初始位置是0mysql> select * from employee5 order by salary limit 4,5;  \\从第3条数据开始，取5条数据

(5) Querying with aggregate functions

COUNT (*) Statistics row data
Max (field) Fetch field maximum value
Min (field): Take the Minimum field value
AVG (field): Average
Sum (field): Fetch field and

mysql> select count(*) from employee5;mysql> select count(*) from employee5 where post=‘hr‘;

Find all the information about the person with the highest salary

mysql> select * from employee5 where salary = (select max(salary) from employee5);  //子查询

(6) Group query 1) group BY and Group_concat () functions are used together

Group BY Department ID to count people in the same department (splicing)

mysql> select dep_id,group_concat(name) from employee5  group by dep_id;

Group BY department, print out the name of each person in the department (splicing)

mysql> select post,group_concat(name) from employee5  group by post;

2) GROUP BY and aggregate functions used together

Group according to department, take the biggest salary of each department

mysql> select post,max(salary) from employee5 group by post;

(7) match with regular

mysql> select name,salary from employee5 where name regexp ‘m{2}‘;mysql> select name,salary from employee5 where name regexp ‘yun$‘;mysql> select name,salary from employee5 where name regexp ‘^m‘;

(vii) MySQL data operation dql: Single table Query