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

This article mainly tells you about the simple method of realizing single-table query in MySQL. You can look up the relevant professional terms on the Internet or find some related books to supplement them. We will not dabble here, so let's go straight to the topic. I hope this article on the simple method of realizing single-table query in MySQL can bring you some practical help.

First, let's create the table and insert the data:

# 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','20180202') 'instructor','teach',7200501100), (' 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 # Group mysql > SELECT post FROM employee5 GROUP BY post using the GROUP BY keyword alone Note: if we group according to the post field, then the field queried by select can only be post. To obtain other relevant information in the group, you need the help of 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}. *'

MySQL to achieve single-table query simple method to tell you here, for other related issues you want to know can continue to pay attention to our industry information. Our section will capture some industry news and professional knowledge to share with you every day.

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.