Shulou(Shulou.com)05/31 Report--

Editor to share with you the example analysis of the table query operation in the MySQL database, I believe that most people do not know much about it, so share this article for your reference, I hope you will gain a lot after reading this article, let's go to know it!

1. Single table query

1.1. Query all fields

1.2. Query specified fields

1.3. Query the specified record

1.4.Query with IN keyword

1.5. range query with BETWEEN AND

1.6. character matching query with LIKE

1.7. Query null value

1.8. multi-conditional query with AND

1.9. Multi-conditional query with OR

1.10. Keyword DISTINCT (query results are not duplicated)

1.11. Sort the query results

1.12. Group query (GROUP BY)

1.13. Use LIMIT to limit the number of query results

Set function query

1.14. COUNT () function

1.15. SUM () function

1.16. AVG () function

1.17. MAX () function

1.18. MIN () function

Second, multi-table query

A little knowledge

Alias the table

Alias the field

Based on two tables

2.1. Common double table join query

2.2. Internal join query

2.3. External join query

2.3.1, left outer join query

2.3.2, right outer join query

2.4. Compound conditional join query

Subquery

2.5.Subquery with ANY and SOME keywords

2.6.Subquery with ALL keyword

2.7. subquery with EXISTS keyword

2.8.Subquery with IN keyword

2.9. Subquery with comparison operator

Merge result query

2.10. The use of UNION [ALL]

Third, query using regular expressions

3.1. Query records that begin with a specific character or string

3.2. Query records that end with a specific character or string

3.3. Use symbols. To replace any character in the string

3.4. Use "*" and "+" to match multiple characters

3.5. Match the specified string

3.6. Match any of the specified characters

3.7. Match characters other than the specified character

3.8. Specify the number of consecutive occurrences of a string by using {n,} or {ncentine m}

Fourth, comprehensive case exercise data sheet query operation

4.1. Build the environment

Omit

4.2. Query operation

Omit

4.3. do the following in the employee table that has been created

4.3.1. Calculate the age of all female employees (F)

4.3.2. Use LIMIT to query from Article 3 record to Article 6 record

4.3.3. Query the minimum wage of sales staff (SALSEMAN)

4.3.4. Query records whose names end with the letters N or S

4.3.5. Query the names and titles of employees working in BeiJing

4.3.6. Use left join to query employee and dept tables

4.3.7. Query the information of all employees who signed up from 2001 to 2005, query the employee information of department numbers 20 and 30, and use UNION to merge the two query results

4.3.8. Use LIKE to query records that contain the letter an in the employee's name

4.3.9. Use REGEXP to query the records of employees whose names contain any one of the letters T, C, or M.

If you want to do the problem directly, skip the explanation and go directly to the practice area.

The catalogue of this explanation is like this, so you can directly look at the parts you are interested in, instead of looking at the basics, and then you will realize the huge workload step by step.

1. Single table query

Create a query environment

CREATE TABLE fruits (

F_id CHAR (10) NOT NULL

S_id INT NOT NULL

F_name char (255) NOT NULL

F_price DECIMAL (8 dint 2) NOT NULL

PRIMARY KEY (f_id)

);

Explanation:

F_id: primary keys use characters of type CHAR to represent primary keys

S_id: this is actually the wholesaler's number, which represents which wholesaler the fruit came from. The purpose of writing this field is to facilitate the subsequent expansion of the table.

F_name: the name of the fruit

F_price: the price of fruit, using the DECIMAL data type, if you are not clear about this type, check out the above article on data types.

Add data.

INSERT INTO fruits (fanciiddimage, fallidiary, fanciname.fareprice) VALUES

('A1', 101, 'apple', 5.2)

('b1', 101,' blackberry', 10.2)

('bs1', 102,' orange', 11.2)

('bs2', 105,' melon', 8.2)

('T1', 102, 'banana', 10.3)

('T2', 102, 'grape', 5.3)

('o2', 103,' coconut', 9.2)

('c0', 101,' cherry', 3.2)

('a2', 103, 'apricot', 2.2)

('L2', 104, 'lemon', 6.4)

('b2', 104,' berry', 7.6)

('M1', 106, 'mango', 15.6)

('M2', 105, 'xbabay', 2.6)

('t4', 107,' xbababa', 3.6)

('m3', 105,' xxtt', 11.6)

('b5', 107,' xxxx', 3.6)

Blackberry: BlackBerry melon: melon grape: grape coconut: coconut cherry: cherry apricot: apricot berry: berry mango: mango the last few xbabay are written for testing, there is no practical significance.

Note: when copying my code to the cmd window, you should be careful that there are no spaces between the statements, otherwise an error will be reported. I am here to make you see it more clearly, so there is a space in front of each line.

1.1. Query all fields

SELECT * FROM fruits

Explanation: * represents all fields, that is, query the record degree below all fields from the table

1.2. Query specified fields

Query records for f_name and f_price fields

SELECT f_name, f_price FROM fruits

1.3. Query the specified record

Specify records: that is, query according to conditions, query the records that meet certain conditions, and use the WHERE keyword

SELECT * FROM fruits WHERE f_name = 'apple'; / / query all the information levels of the record named apple

SELECT * FROM fruits WHERE f_price > 15; / / query all fields of records with a price greater than 15

1.4.Query with IN keyword

IN keyword: IN (xx,yy,...) A value within the range of conditions is a match.

SELECT * FROM fruits WHERE f_name IN ('apple','orange')

SELECT * FROM fruits WHERE s_id IN (101105); / / records with 101or 105s_id

SELECT * FROM fruits WHERE s_id NOT IN (101105); / / records with s_id other than 101or 105s

1.5. range query with BETWEEN AND

BETWEEN... AND.: in. To. The values in the range are matches.

SELECT * FROM fruits WHERE f_price BETWEEN 5 AND 15; / / f_price is between 5 and 15, inclusive.

SELECT * FROM fruits WHERE f_price NOT BETWEEN 5 AND 15; / / f_price is not between 5 and 15.

1.6. character matching query with LIKE

LIKE: equivalent to fuzzy query, the wildcards used with LIKE are "%" and "_"

"%": the function is to match characters of any length.

"_": can only match any one character

SELECT * FROM fruits WHERE f_name LIKE'b% records; / / f_name all records that begin with the letter b

SELECT * FROM fruits WHERE f_name LIKE'b% yearly; / / f_name all records that begin with the letter b and end with the letter y

SELECT * FROM fruits WHERE f_name LIKE'_ yearly; / / there are four _ here, indicating that you want to query records that end with the letter y and are preceded by only four characters.

Summary:'% 'and' _ 'can be used anywhere, just remember that% can represent any character, and _ can only represent any character

1.7. Query null value

A null value does not mean an empty string "" or 0, which generally means that the data is unknown or that data is added later, that is, when adding data, its field defaults to NULL, that is, if no value is inserted into the field, it is NULL. You can look it up at this time.

SELECT * FROM table name WHERE field name IS NULL; / / query field name is a record of NULL

SELECT * FROM table name WHERE field name IS NOT NULL; / / query a record whose field name is not NULL

Since there is no suitable data here, I am not creating a table, adding data, and then testing this statement. It is very simple, and you should understand it by looking at it.

1.8. multi-conditional query with AND

AND: equivalent to "logic and", that is to say, a match can only be considered if the conditions are met at the same time.

SELECT * FROM fruits WHERE s_id = 101AND f_price > 5; / / both s_id = 101and f_price > 5 are considered to match.

1.9. Multi-conditional query with OR

OR: equivalent to "logical OR", that is, as long as one of the conditions is met, it is a match, which is similar to the effect of the IN keyword.

SELECT * FROM fruits WHERE s_id = 101OR f_price > 10; / / s_id = 101or f_price > 10. As long as one of the conditions is met, it is a match.

As you can see, the query records, f_price has less than 10, then it is sure that the f_price is not equal to 101, its f_price must be greater than 10, which shows the effect of OR. As long as one of the conditions is met, it is considered a match.

1.10. Keyword DISTINCT (query results are not duplicated)

SELECT s_id FROM fruits; / / query all s_id, there will be a lot of duplicate values.

Duplicate values can be eliminated by using DISTINCT

SELECT DISTINCT s_id FROM fruits

After the duplicate values are deleted, only seven records are left.

1.11. Sort the query results (ORDER BY)

See that the values output above are out of order, so you can sort them. Using the keyword ORDER BY, there are two values to choose between DESC descending and ASC ascending (default)

SELECT DISTINCT s_id FROM fruits ORDER BY ordered; / / the default is ascending order

SELECT DISTINCT s_id FROM fruits ORDER BY s_id DESC; / / use descending order, that is, sort from top to bottom

1.12. Group query (GROUP BY)

Group query a lot of people do not know what it means, at first I am also very bewildered, so it doesn't matter, let's have a look.

Group inquiry is to divide the same things into a group. In real life, for example, toilets are divided into men and women, which is also an application of grouping. Before there is a division of men's and women's toilets, everyone shares toilets, and then by dividing men and women into men and women, men and women are divided into one group, and women and women are divided into one group, thus divided into men's and women's toilets. This is what grouping means. When we query s_id above, we find a lot of duplicate values, so we can group them and divide the same values into a group.

Grouping SELECT s_id FROM fruits GROUP BY wholesalers; / / grouping s_id into groups has practical significance. Grouping according to wholesalers will put the fruit degree from wholesalers in this group.

Explanation: after grouping s_id, there are no duplicate values, because the degree of repetition is divided into a group. Now let's see how many values there are in each group.

SELECT s_id, COUNT (f_name), GROUP_CONCAT (f_name) FROM fruits GROUP BY s_id

Explanation:

COUNT (): this is a function to be explained below, which is used to calculate how many records there are.

GROUP_CONCAT (): displays the values of each field in the grouping

SELECT s_id, COUNT (f_name), GROUP_CONCAT (f_name), GROUP_CONCAT (f_price) FROM fruits GROUP BY spore;

After grouping, you can also perform conditional filtering, discard unwanted packets, and use the keyword HAVING

SELECT s_id count (f_name), GROUP_CONCAT (f_name) FROM fruits GROUP BY s_id HAVING COUNT (f_name) > 1 / he can pass s_id grouping, and then filter out the grouping information of fruit species greater than 1.

Summary:

Know the meaning of GROUP BY, and use HAVING to filter packets. Both HAVING and WHERE perform conditional filtering. The difference is that WHERE is filtered before grouping, while HAVING is conditional filtered after grouping.

1.13. Use LIMIT to limit the number of query results

Number of LIMIT [position offset] rows through LIMIT, you can select any number of rows in the database table, that is, instead of traversing from the first record, you can directly get the records from articles 5 to 10, or from articles 12 to 15. Take a look at the following example

SELECT * FROM fruits LIMIT 4; / / there is no write position offset. The default is 0, that is, starting from the first item, four pieces of data are taken back, that is, the data from the first piece of data to section 4.

SELECT * FROM fruits LIMIT 4 data 3; / / start with Article 5 data, and then take 3 pieces of data back, that is, from Article 5 to Article 8

Note: the first parameter of LIMIT is 0 by default, that is, the index of the first record is 0, starting from 0, and the second parameter means how many rows of records to fetch. These two are needed to determine the range of a fetch record.

Set function query

1.14. COUNT () function

This function has been useful above to count the total number of record rows contained in the data table, or to return the number of data rows contained in the column based on the query results.

COUNT (*): calculates the total number of rows in a table, regardless of whether a column has a numeric value or is null, because * represents all the data rows in the query table

COUNT (field name): calculates the total number of rows under this field name, ignoring rows with null values, that is, rows with null values.

SELECT COUNT (*) FROM fruits

SELECT COUNT (f_name) FROM fruits; / / query the number of rows under the f_name field name in the fruits table

There is no null value, so the calculated number of rows is the same as the total number of record rows.

1.15. SUM () function

SUM () is a summation function that returns the sum of the specified column values.

SELECT SUM (f_price) FROM fruits; / / this has no practical meaning, just testing the ability of the SUM () function to sum.

If anyone thinks this test is not accurate, you can add all the f_price manually. Then let's make a comparison. I believed it anyway.

1.16. AVG () function

By calculating the number of rows returned and the sum of each row of data, the AVG () function calculates the average of the specified column data (column data refers to the data under the field name, don't be confused about the columns and rows, just look at a table and figure out which is the column and which is the row). In popular terms, it is to divide the calculated sum by the total number of records to get an average.

SELECT AVG (f_price) FROM fruits;

It's the equivalent of 116 stroke, 16 = 7.25.

1.17. MAX () function

MAX () returns the maximum value in the specified column

SELECT MAX (f_price) FROM fruits

1.18. MIN () function

MIN () returns the minimum value in the query column

SELECT MIN (f_price) FROM fruits

Second, multi-table query

A little knowledge

Alias the table

Because you are querying two tables, it is a bit troublesome to write the table name every time, so use a simple alias to represent the table name

Format: table name AS alias

It will be used in the following example, so don't fail to recognize it.

Alias the field

The reason for aliasing field names is that some field names are intended to be displayed more clearly, such as

SELECT f_price AS 'Price' FROM fruits

Sentence execution order problem

1. Sql execution sequence,

(1) from

(2) on

(3) join

(4) where

(5) group by

(6) avg,sum....

(7) having

(8) select

(9) distinct

(10) order by

In other words, every time we execute a SQL statement, it starts with FROM.

Based on two tables

Set up the query environment, there is already a table, now add a suppliers table and the previous fruits table creation exercise, that is to say, let the s_id field value in fruits point to the primary key value of suppliers, and create a foreign key constraint relationship.

CREATE TABLE suppliers

(

S_id INT NOT NULL

S_name CHAR (50) NOT NULL

S_city CHAR (50) NULL

S_zip CHAR (10) NULL

S_call CHAR (50) NOT NULL

PRIMARY KEY (s_id)

)

In fact, there is no real foreign key constraint relationship here, just a simulation, so that the values in s_id in fruits can match the primary key values in suppliers. This relationship can be achieved by manually adding this kind of data. Anyway, it is dead data, and there is no need to add other data, so there is no need to establish a foreign key constraint relationship.

INSERT INTO suppliers (scuriddiary scurnamememe pr é cor citydiary sprints zipdir smiles call)

VALUES

(101, supplies, supplies, 180, 75, 400, 000, 400, 000)

(102, supplies, supplies, qingying, 400000, 44333)

(103, supplies supplies, 90046, Shanghai, 400000, 90046)

(104 sources Downs, Zhongshanzhong, 400000, 111111)

(105, supplies, supplies, Taiyuang.com, 400000, 22222,)

(106, supplies, supplies, Beijing, 400000, 45678)

(107 supplies Gems, 400000 Gems, 33332')

2.1. Common double table join query

Question: query fruit wholesaler number, wholesaler name, fruit name, fruit price

Analysis: if you look at the requirements, you will know that you need to query two tables. If you need to query two tables, then the relationship between the two tables must be a foreign key relationship, or similar to a foreign key relationship (similar to that is, the two tables are not really constrained by a foreign key, but their characteristics are the same as the two tables we created manually above, although there is no foreign key relationship. But its properties are the same as foreign key relationships.)

SELECT s.sdistribuidrect s.sroomnamerecoveryf.franknamerecoveryf.frankprice FROM fruits AS f, suppliers AS s WHERE f.s_id = s.s_id

Explanation: table aliases are used here, and the connection between the two tables here is that the s_id in fruits equals the s_id in suppliers. This everyone can understand, that is, the wholesaler's number is recorded in the fruit, and the detailed information of the corresponding wholesaler can be found in the suppliers table through this number. in this way, the two tables are linked.

Note: the first execution is FROM, so the table is aliased above, and can be used anywhere in the statement.

2.2. Internal join query

After knowing the basic join query of the above two tables, the inner join query is very simple, because the function of the inner join is the same as the above, and the only difference is the syntax.

Format: table name INNER JOIN table name ON connection condition

Question: query fruit wholesaler number, wholesaler name, fruit name, fruit price

SELECT s.s_id,s.s_name,f.f_name,f.f_price

FROM fruits AS f INNER JOIN suppliers AS s

ON f.s_id = s.s_id

I don't know if writing the sql sentence like this will make you see it more clearly.

There is also a special thing to know, and that is self-join query. What is self-join query? That is, the two tables involved are the same table.

Question: inquire about other types of fruits offered by fruit suppliers that supply fanciidfruit A1'?

SELECT f2.f_id,f2.f_name

FROM fruits AS f1 INNER JOIN fruits AS f2

ON f1.s_id = f2.s_id AND f1.f_id = 'a1'

Explanation: separate the fruits table as two identical tables, find the s_id of the favored s_id A1'in the F1 table, and then go to the f2 table to find the records that are equal to the s_id, and then query the results needed for the problem. There is another way, without internal join query, through the sub-query can also be done, the following will explain, here is the answer, then you can go back to look at this question.

SELECT f_id,f_name

FROM fruits

WHERE s_id = (SELECT s_id FROM fruits WHERE favored A1')

The effect is the same as the inner join, in fact, the principle is the same, or with the help of two tables, but this is easier for people to understand, you can use this to understand the above self-join query.

2.3. External join query

An inner join returns rows that meet the query criteria (join criteria), that is, the associated rows are returned.

In addition to returning the associated rows, the rows that will not be associated are also displayed.

Why do you need to display rows that are not unrelated? This depends on different business needs. For example, order and customers, customers may or may not have orders. Now we need to know the situation of orders issued by all customers, but we can not just query users with orders and leave users without orders without showing. This is not in line with our business needs. Some people say that since we know customers with orders, Through a single table query does not include these customers with orders, can not meet our requirements, this is OK, but very troublesome, how can it be displayed together and not so troublesome? In order to solve this problem, there is an external connection query.

2.3.1, left outer join query

Format: table name LEFT JOIN table name ON condition; returns records that include all records in the left table and equal join fields in the right table. In popular terms, in addition to displaying the associated rows, it will also display the row degrees of all records in the left table. Use an example to show the effect.

Since the records in fruits and suppliers we used above are designed, and no supplier does not provide fruit, now in order to reflect the effect of left outer connection, two records are added in suppliers, and there is no fruit information corresponding to these two records in fruits.

INSERT INTO suppliers VALUES (108, suppliers Turing, Jiangxie, 33333, 111111)

INSERT INTO suppliers VALUES (109, suppliers, Jiangxie, 22222, 44444)

SELECT s.s_id,s.s_name,f.f_id,f.f_name

FROM suppliers AS s LEFT JOIN fruits AS f

ON s.s_id = f.s_id

Explanation:

The suppliers table is on the left side of LEFT JOIN, so it shows all the records in it, and there are no related items for related items. This is what the left outer join means, showing all the records in the table on the left (as long as we follow the fields we need, that is, the fields selected after SELECT). If you put the suppliers table to the right of the LEFT JOIN, the records 108 and 109 will no longer be displayed. Come see

SELECT s.s_id,s.s_name,f.f_id,f.f_name

FROM fruits AS f LEFT JOIN suppliers AS s

ON s.s_id = f.s_id

2.3.2, right outer join query

Format: table name RIGHT JOIN table name ON condition returns records that include all records in the right table and records with equal join fields in the right table

In fact, it is similar to the left outer connection, that is, all the tables on the right are displayed.

SELECT s.s_id,s.s_name,f.f_id,f.f_name

FROM fruits AS f RIGHT JOIN suppliers AS s

The result of this statement is the same as the left outer connection above, but the position is changed, but the effect is the same as that of ON s.s_id.

Note:

LEFT JOIN and RIGHT JOIN are just one way to write, and there is another way to write LEFT OUTER JOIN and RIGHT OUTER JOIN.

Generally speaking, when writing such a complex query, the order of writing sql statements should be from FROM first.

2.4. Compound conditional join query

In the process of join query (inner join, outer join), filter conditions are added to limit the query results to make the query results more accurate. In popular terms, the conditions of join query are more refined.

Question 1: use INNER JOIN syntax in fruits and suppliers tables to query the supply information of suppliers with s_id 107in suppliers table?

SELECT s.s_id,s.s_name,f.f_id,f.f_name

FROM suppliers AS s INNER JOIN fruits AS f

ON s.s_id = f.s_id AND s.s_id = 107

Problem 2: use INNER JOIN syntax to make an inner join query between fruits table and suppliers table, and sort the query results

SELECT s.s_id,s.s_name,f.f_id,f.f_name

FROM suppliers AS s INNER JOIN fruits AS f

ON s.s_id = f.s_id

ORDER BY f.sroomid; / / A pair of f.s_id in ascending order. The default is ASC, so you don't have to write.

Sorting f.s_id is actually sorting s.s_id, and the effect is the same, because the condition is f.s_id=s.s_id.

Subquery

A subquery that uses the results of one table as a condition for another query, so that a nested query is called a subquery

2.5.Subquery with ANY and SOME keywords

Build an environment

CREATE TABLE tb11 (num1 INT NOT NULL)

CREATE TABLE tb12 (num2 INT NOT NULL)

INSERT INTO tb11 VALUES (1), (5), (13), (27)

INSERT INTO tb12 VALUES (6), (14), (11), (20)

The ANY keyword is followed by a comparison operator, indicating that if compared to any value returned by the subquery as TRUE, TRUE is returned. In general, TRUE is returned as long as any of the conditions are met.

SELECT num1 FROM tb11 WHERE num1 > ANY (SELECT num2 FROM tb12); / / in this case, the results of the query in the tb12 table are placed in the previous query statement as conditional parameters. As long as the num1 is greater than any number of its results, it is considered a match.

The use of the SOME keyword is the same as that of the ANY keyword, and I won't go into much detail here.

2.6.Subquery with ALL keyword

Using ALL means that all conditions need to be met at the same time.

SELECT num1 FROM tb11 WHERE num1 > ALL (SELECT num2 FROM tb12); / / num1 needs to be greater than all query results to be matched

2.7. subquery with EXISTS keyword

The parameter after the EXISTS keyword is any subquery, if the subquery returns record rows, it is TRUE, the outer query statement will query, if the subquery does not return any record rows, it will be FALSE, the outer query statement will not query.

SLEECT * FROM tb11 WHERE EXISTS (SELECT * FROM tb12 WHERE num2=3); / / query whether there are any records of num2=3 in tb12. If so, all records of tb11 will be queried. If not, no query will be made.

2.8.Subquery with IN keyword

This IN keyword works the same as the IN for the single table query above, but here the parameter in IN is a subquery statement.

SELECT s_id,f_id,f_name

FROM fruits

WHERE s_id IN (SELECT s_id FROM suppliers WHERE s_id = 107)

2.9. Subquery with comparison operator

In addition to using the keywords ALL, ANY, SOME, and so on, you can also use ordinary comparison operators. To make a comparison. For example, when we explained the inner join query above, we used the subquery statement, and we still used the = comparison operator, so we don't have to explain it here. We can take a look at it.

Merge result query

Using the UNION keyword, you can merge the results of the query into a result set, that is, join multiple SELECT statements through the UNION keyword. Note that merging the result set only increases the records in the table, not the fields in the table, but just the record rows together. The fields displayed should be the same, otherwise they cannot be merged.

2.10. The use of UNION [ALL]

UNION: do not use the keyword ALL, duplicate records will be deleted during execution, and all returned row degrees are unique

UNION ALL: no duplicate voyages are deleted or results are automatically sorted.

Format:

SELECT field name,... FROM table name

UNION [ALL]

SELECT field name,... FROM table name

Question 1: query the information of all fruits whose price is less than 9, query the information that s_id equals 101103all fruits, and use UNION to connect the query results.

SELECT s_id,f_name,f_price FROM fruits WHERE f_price

< 9 　　　UNION ALL 　　　SELECT s_id,f_name,f_price FROM fruits WHERE s_id = 101 OR s_id=103; 　　　解释：显示的字段都是s_id,f_name,f_price，只是将两个的记录行合并到一张表中。仅仅增加的是记录行，而显示的字段还是那三个，没有增加， 　　　 　　　使用UNION，而不用UNION ALL的话，重复的记录就会被删除掉。 　 三、使用正则表达式查询 　　　　　　这个非常简单，就是使用REGEXP关键字来指定正则表达式，画一张表格，就能将下面所有的度覆盖掉。 　　　 　　　　　　3.1、查询以特定字符或字符串开头的记录 　　　SELECT * FROM fruits WHERE f_name REGEXP '^b';　　　　//以b开头的记录 　　　　 　　　就讲解这一个，下面这些的形式跟这个一模一样，唯一的差别就在正则表达式不一样，一般使用这种模糊查询，使用MySQL中的'_'和'%'就已经足够了。 　　　　　　3.2、查询以特定字符或字符串结尾的记录 　　　　　　3.3、用符号"."来替代字符串中的任意一个字符 　　　　　　3.4、使用"*"和"+"来匹配多个字符 　　　　　　3.5、匹配指定字符串 　　　　　　3.6、匹配指定字符中的任意一个 　　　　　　3.7、匹配指定字符以外的字符 　　　　　　3.8、使用{n,}或者{n,m}来指定字符串连续出现的次数 四、综合案例 练习数据表查询操作 　　　　　　4.1、搭建环境 　　　　　　　　两张表： employee(员工)表和dept(部门)表。 　　　　　CREATE TABLE dept 　　　　　( 　　　　　　　d_no INT NOT NULL PRIMARY KEY AUTO_INCREMENT,　　　　//部门编号 　　　　　　　d_name VARCHAR(50),　　//部门名称 　　　　　　　d_location VARCHAR(100)//部门地址 　　　　　); 　　　　　 CREATE TABLE employee 　　　　　 ( 　　　　　　　e_no INT NOT NULL PRIMARY KEY,　　　　//员工编号 　　　　　　　e_name VARCHAR(100) NOT NULL,　　　　//员工姓名 　　　　　　　e_gender CHAR(2) NOT NULL,　　　　　　//员工性别 　　　　　　　dept_no INT NOT NULL,//部门编号 　　　　　　　e_job VARCHAR(100) NOT NULL,　　　　//职位 　　　　　　　e_salary SMALLINT NOT NULL,　　　　　//薪水 　　　　　　　hireDate DATE,　　　　//入职日期 　　　　　　　CONSTRAINT dno_fk FOREIGN KEY(dept_no) REFERENCES dept(d_no) 　　　　　 ); 表结构 　　　　 　　　　　　　　插入数据 　　　　INSERT INTO dept 　　　　VALUES 　　　　　　　(10,'ACCOUNTING','ShangHai'), 　　　　　　　(20,'RESEARCH','BeiJing'), 　　　　　　　(30,'SALES','ShenZhen'), 　　　　　　　(40,'OPERATIONS','FuJian'); 　　　单词解释：ACCOUNTING：会计部门　　RESEARCH:研发部　　SALES:销售部　　OPERATIONS:实践部 　　　　INSERT INTO employee 　　　　VALUES 　　　　　　　(1001, 'SMITH' , 'm' , 20 , 'CLERK' , 800 , '2005-11-12'), 　　　　　　 (1002, 'ALLEN' , 'f' , 30 , 'SALESMAN' , 1600, '2003-05-12'), 　　　　　　　(1003, 'WARD' , 'f' , 30 , 'SALESMAN' , 1250, '2003-05-12'), 　　　　　　 (1004, 'JONES' , 'm' , 20 , 'MANAGER' , 2975, '1998-05-18'), 　　　　　　　(1005, 'MARTIN' , 'm' , 30 , 'SALESMAN' , 1250, '2001-06-12'), 　　　　　　 (1006, 'BLAKE' , 'f' , 30 , 'MANAGER' , 2850, '1997-02-15'), 　　　　　　　(1007, 'CLARK' , 'm' , 10 , 'MANAGER' , 2450, '2002-09-12'), 　　　　　　 (1008, 'SCOTT' , 'm' , 20 , 'ANALYST' , 3000, '2003-05-12'), 　　　　　　　(1009, 'KING' , 'f' , 10 , 'PRESIDENT' , 5000, '1995-01-01'), 　　　　　　 (1010, 'TURNER' , 'f' , 30 , 'SALESMAN' , 1500, '1997-10-12'), 　　　　　　　(1011, 'ANAMS' , 'm' , 20 , 'CLERK' , 1100, '1999-10-15'), 　　　　　　 (1012, 'JAMES' , 'f' , 30 , 'CLERK' , 950, '2008-06-15'); 　　　单词解释：SALESMAN:销售员　　CLERK:普通职员　　MANAGER:经理　　PRESIDENT:董事长　　ANALYST:分析师 m:male 男性　　f:female 女性 　　　　 　　　　　　4.2、查询操作 　　　　 4.2.1、在employee表中，查询所有记录的e_no，e_name和e_salary字段值 　　　　　　　SELECT e_no,e_name,e_salary FROM employee; 　　　　 4.2.2、在employee表中，查询dept_no等于10和20的所有记录 　　　　　　　方式一：SELECT * FROM employee WHERE dept_no IN(10,20); 　　　　 　　　　　　　方式二：SELECT * FROM employee WHERE dept_no = 10 OR dept_no = 20; 　　　　 4.2.3、在employee表中，查询工资范围在800~2500之间的员工信息 　　　　　　　SELECT * FROM employee WHERE e_salary BETWEEN 800 AND 2500; 　　　　 4.2.4、在employee表中，查询部门编号为20的部门中的员工信息 　　　　　　　SELECT * FROM employee WHERE dept_no = 20; 　　　　 4.2.5、在employee表中，查询每个部门最高工资的员工信息 　　　　　分析：每个员工都可能处于不同的部门，要先找出每个部门中的所有员工，应该想到分组，将相同部门的员工分为一组。然后在使用MAX()函数比较最大的salary。注意不要MAX(GROUP_CONCAT(e_salary)), 这样写就会报错，GROUP_CONCAT(e_salary)是将分组中所有的e_salary显示出来，我们直接MAX(e_salary)就会将该分组中所有的e_salary进行比较，拿到最大的一份。 　　　　　　　SELECT e_no,e_name,MAX(e_salary) FROM employee GROUP BY dept_no; 　　　　　　 　　　　　　　我尝试过其他方法，但是最终还是要对在相同部门中的员工做比较，只有通过分组才能对相同部门中的员工做比较，如果不分组，那么将对全部的记录进行比较。 4.2.6、查询员工BLAKE所在部门和部门所在地 　　　　　分析：看下题目就应该知道是两张表，也就应该使用连接查询，找出是用内连接还是外连接，然后找出连接条件， 　　　方式一：SELECT d.d_no,d.d_name,d.d_location 　　　　　　　 FROM employee AS e,dept AS d 　　　　　　　 WHERE e.e_name = 'BLAKE' AND e.dept_no = d.d_no; 　　　　 　　　方式二：SELECT d.d_no,d.d_name,d.d_location 　　　　　　　 FROM employee AS e INNER JOIN dept AS d 　　　　　　　 ON e.e_name = 'BLAKE' AND e.dept_no = d.d_no; 　　　　 4.2.7、使用连接查询，查询所有员工的部门和部门信息 　　　　　分析：这个题跟上面哪个题差不多，使用内连接 　　　　　SELECT e.e_no,e.e_name,d.d_no,d.d_name,d.d_location 　　　　　FROM employee AS e INNER JOIN dept AS d 　　　　　ON e.dept_no = d.d_no; 　　　　 4.2.8、在employee中，计算每个部门各有多少员工 　　　　　分析：每个部门用分组 　　　　　SELECT COUNT(e.e_name) 　　　　　FROM employee AS e 　　　　　GROUP BY e.dept_no　　 　　　 　　　　　 　　　　　　注意：SUM()和COUNT()要分清楚，SUM()是计算数值总和的，COUNT()是计算总的记录行数的。 4.2.9、在employee表中，计算不同类型职员的总工资数 　　　　　分析：对员工职位类型进行分组 　　　　　SELECT e.e_job,SUM(e.e_salary) 　　　　　FROM employee AS e 　　　　　GROUP BY e.e_job; 　　　　　　 4.2.10、在employee表中，计算不同部门的平均工资 　　　　　分析：对部门进行分组 　　　　　 SELECT e.dept_no,AVG(e.e_salary) 　　　　　 FROM employee AS e 　　　　　 GROUP BY e.dept_no; 　　　　　　 4.2.11、在employee表中，查询工资低于1500的员工信息 　　　　　　SELECT * from employee WHERE e_salary < 1500; 　　　　　　　　 4.2.12、在employee表中，将查询记录先按部门编号由高到低排列，再按员工工资由高到低排列 　　　　　　SELECT * FROM employee ORDER BY dept_no DESC,e_salary DESC; 　　　　　　 4.2.13、在employee表中，查询员工姓名以字母A或S开头的员工信息 　　　　　　方式一：　SELECT * FROM employee WHERE e_name LIKE 'A%' OR e_name LIKE 'S%'; 　　　　　　 　　　　　　方式二：SELECT * FROM employee WHERE e_name REGEXP '^A' OR e_name REGEXP '^S'; 　　　　　　 4.2.14、在employee表中，查询到目前为止，工龄大于等于10年的员工信息 　　　　　　方式一：SELECT * FROM employee WHERE (YEAR(NOW()) - YEAR(hireDate)) >

10;

Method 2: SELECT * FROM employee WHERE (YEAR (CURDATE ())-YEAR (hireDate)) > 10;

Explanation: remember the data type we learned in the previous section, where CURDATE () represents YYYY-MM-DD and NOW () represents YYYY-MM-DD HH:MM:SS, so the two degrees can be used here, as long as it is intercepted as YEAR, and then subtracted, you can get a difference of several years.

4.3. do the following in the employee table that has been created

4.3.1. Calculate the age of all female employees (F) (time from entry to present)

SELECT e.etropnameree.estranggender.YEAR (CURDATE ())-YEAR (e.hireDate)

FROM employee AS e

WHERE e.e_gender ='f'

4.3.2. Use LIMIT to query from Article 3 record to Article 6 record

SELECT * FROM employee LIMIT 2 Jol 3;

4.3.3. Query the minimum wage of sales staff (SALSEMAN)

SELECT MIN (e_salary)

FROM employee

WHERE e_job = 'SALESMAN';

4.3.4. Query records whose names end with the letters N or S

Method 1:

SELECT * FROM employee WHERE e_name LIKE'% N' OR e_name LIKE'% slots;

Method 2:

SELECT * FROM employee WHERE e_name REGEXP 'Nice' OR e_name REGEXP 'Seven'

4.3.5. Query the names and titles of employees working in BeiJing

Method 1: SELECT esigname.job.xxx = (SELECT d_no FROM dept WHERE d_location = 'BeiJing')

Method 2: SELECT e.e_name, e.e_job FROM employee AS ejime dept AS d WHERE e.dept_no = d.d_no AND d.d_location = 'BeiJing'

Method 3: SELECT e.ebegname.e _ resume job FROM employee AS e INNER JOIN dept AS d ON e.dept_no = d.d_no AND d.d_location = 'BeiJing'

4.3.6. Use left join to query employee and dept tables

SELECT * FROM employee LEFT JOIN dept ON employee.dept_no = dept.d_no

4.3.7. Query the information of all employees who signed up from 2001 to 2005, query the employee information of department numbers 20 and 30, and use UNION to merge the two query results

SELECT * FROM employee WHERE YEAR (hireDate) BETWEEN 2001 AND 2005

UNION

SELECT * FROM employee WHERE dept_no IN (200.30)

4.3.8. Use LIKE to query records that contain the letter an in the employee's name

SELECT * FROM employee WHERE e_name LIKE'a%'OR e_name LIKE'% a'OR e_name LIKE'% a% salary;

4.3.9. Use REGEXP to query the records of employees whose names contain any one of the letters T, C, or M.

SELECT * FROM employee WHERE e_name REGEXP'[TCM]'

The above is all the contents of the article "sample Analysis of Table query Operations in MySQL Database". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.