Shulou(Shulou.com)06/01 Report--

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.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.