The statement of realizing single table query by MySQL

The following brings you about the MySQL implementation of single-table query statements, if you are interested, let's take a look at this article, I believe that after reading MySQL to achieve single-table query statements to help you more or less.

First create a data table

# create tables 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 (15d2) not null, office int, dep_id int) # insert data mysql > insert into company.employee5 (name,sex,hire_date,post,job_description,salary,office,dep_id) values ('jack','male','20180202','instructor','teach',5000501100), (' tom','male','20180203','instructor','teach',5500501100), ('robin','male','20180202','instructor','teach',8000501100), (' alice','female') ('tianyun','male','20180202','hr','hrcc',600502101), (' harry','male','20180202','hr',NULL,6000502101), ('emma','female','20180206','sale','salecc',20000503102), (' christine','female','20180205','sale','salecc',2200503102), ('zhuzhu','male','20180205') 'sale',NULL,2200503102), (' gougou','male','20180205','sale','',2200503102) # View table structure mysql > desc employee5 +-+-- + | Field | Type | Null | Key | Default | Extra | +- -+ | id | int (11) | NO | PRI | NULL | auto_increment | | name | varchar (30) | | NO | | NULL | sex | enum ('male' | 'female') | NO | | male | | hire_date | date | NO | | NULL | | post | varchar (50) | NO | | NULL | | job_description | varchar (100) | YES | | NULL | Salary | double (15) | NO | | NULL | office | int (11) | YES | | NULL | | dep_id | int (11) | YES | | NULL | | +- +-+ query syntax SELECT field 1 Field 2. FROM table name WHERE condition GROUP BY field HAVING filter ORDER BY field LIMIT limit number View all data in the 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 | 2018 | 100 | 2 | tom | male | 2018-02-03 | instructor | teach | 5500.00 | 501 | 100 | 3 | robin | male | 2018-02 | instructor | teach | 8000.00 | 501 | 100 | 4 | alice | female | 2018-02-02 | instructor | teach | 7200.00 | | 5 | tianyun | male | 2018-02 | hr | hrcc | 600.00 | 502 | 101 | 6 | harry | male | 2018-02-02 | hr | NULL | 6000.00 | 6000.00 | 7 | emma | female | 2018-02-06 | sale | salecc | 20000.00 | 20000.00 | 102 | 8 | christine | | | female | 2018-02-05 | sale | salecc | 2200.00 | 503 | 2018 | 9 | zhuzhu | male | 2018-02-05 | sale | NULL | 2200.00 | 503 | 102 | 10 | gougou | male | 2018-02-05 | sale | 2200.00 | 503 | 102 | +-- +-+ 10 rows in set (0.00 sec) simple query simple query mysql > SELECT * FROM employee5 Mysql > SELECT name, salary, dep_id FROM employee5; deduplicates DISTINCTmysql > SELECT post FROM employee5;mysql > SELECT DISTINCT post FROM employee5; Note: DISTINCT cannot be partially used, usually only for a certain field. Query mysql > SELECT name, salary, salary*14 FROM employee5;mysql > SELECT name, salary, salary*14 AS Annual_salary FROM employee5;mysql > SELECT name, salary, salary*14 Annual_salary FROM employee5; definition display format CONCAT () function is used to concatenate the string mysql > SELECT CONCAT (name, 'annual salary:', salary*14) AS Annual_salary FROM employee5 Conditional query a, syntax select * from table name where condition b, comparison operator greater than less than less than equal to less than equal to not equal to >

= SELECT name,post FROM employee5 WHERE post='hr'; multi-condition query mysql > SELECT name,salary FROM employee5 WHERE post='hr' AND salary > 5000; keyword BETWEEN AND query mysql > SELECT name,salary FROM employee5 WHERE salary BETWEEN 5000 AND 15000 AND MySQL > SELECT name,salary FROM employee5 WHERE salary NOT BETWEEN 5000 AND 15000; keyword IS NULL query mysql > SELECT name,job_description FROM employee5 WHERE job_description IS NULL;mysql > SELECT name,job_description FROM employee5 WHERE job_description IS NOT NULL;mysql > SELECT name,job_description FROM employee5 WHERE job_description='' Keywords IN collection query mysql > SELECT name, salary FROM employee5 WHERE salary=4000 OR salary=5000 OR salary=6000 OR salary=9000; mysql > SELECT name, salary FROM employee5 WHERE salary IN (4000 SELECT name 5000 SELECT name, salary FROM employee5 WHERE salary NOT IN); keyword LIKE fuzzy query wildcard'% 'mysql > SELECT * FROM employee5 WHERE name LIKE' al%'; wildcard'_ 'mysql > SELECT * FROM employee5 WHERE name LIKE' al___'; query sort by single column mysql > SELECT * FROM employee5 ORDER BY salary Mysql > SELECT name, salary FROM employee5 ORDER BY salary ASC;mysql > SELECT name, salary FROM employee5 ORDER BY salary DESC; by multiple columns mysql > SELECT * FROM employee5 ORDER BY hire_date DESC,salary ASC;# first by entry time, then by salary mysql > SELECT * FROM employee5 ORDER BY hire_date DESC,salary DESC;# first by position, then by salary by mysql > SELECT * FROM employee5 ORDER BY post, salary DESC; paging query limitmysql > SELECT * FROM employee5 ORDER BY salary DESC LIMIT 5 / / the default initial location is 0mysql > SELECT * FROM employee5 ORDER BY salary DESC LIMIT 0meme 5 * MySQL > SELECT * FROM employee5 ORDER BY salary DESC LIMIT 3mem5. / / starting from Article 4, a total of 5 aggregate function queries an and count (*) are displayed to indicate the total number of rows. You can write * and column name b in parentheses, max (column) means to find the maximum value c of this column, min (column) means to find the minimum value d of this column, sun (column) means to find the sum of this column and e, avg (column) means to find the average value of this column mysql > SELECT COUNT (*) FROM employee5 Mysql > SELECT COUNT (*) FROM employee5 WHERE dep_id=101;mysql > SELECT MAX (salary) FROM employee5;mysql > SELECT MIN (salary) FROM employee5;mysql > SELECT AVG (salary) FROM employee5;mysql > SELECT SUM (salary) FROM employee5;mysql > SELECT SUM (salary) FROM employee5 WHERE dep_id=101; grouping query using GROUP BY keywords mysql > SELECT post FROM employee5 GROUP BY post; Note: if we group by post field, then the select query field can only be post. If you want to get other relevant information in the group, you need a function.

The GROUP BY keyword is used with the group_concat () function

# Group by id, and view the group members mysql > SELECT dep_id,GROUP_CONCAT (name) FROM employee5 GROUP BY dep_id;mysql > SELECT dep_id,GROUP_CONCAT (name) as emp_members FROM employee5 GROUP BY dep_id

GROUP BY is used with aggregate functions

# grouping according to dep_id, and calculating the total salary of members in the group mysql > SELECT dep_id,SUM (salary) FROM employee5 GROUP BY dep_id;# grouping according to dep_id, and calculating the average salary of members in the group mysql > SELECT dep_id,AVG (salary) FROM employee5 GROUP BY dep_id; regular expression query mysql > SELECT * FROM employee5 WHERE name REGEXP'^ mysql > SELECT * FROM employee5 WHERE salary REGEXP'[5] +. *' Mysql > SELECT * FROM employee5 WHERE salary REGEXP'[5] {2}. *'

Read the details of the above statement about MySQL to achieve single-table query, whether there is anything to gain. If you want to know more about it, you can continue to follow our industry information section.