preface
This section is more important. It conducts query operations on data table data, among which some complex queries such as INNER JOIN, LEFT JOIN and RIGHT JOIN may be unfamiliar to everyone. Through this section, you can know how these basic complex queries are implemented, but the advice still needs to be more hands-on to knock, although you understand what is the inner join, but from understanding to learning, is a completely different feeling. Summary of a MYSQL knowledge map to share with you:
A, single table query
Creating 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,2) NOT NULL, PRIMARY KEY(f_id));Copy the codeExplanation:
F_id: The primary key uses CHAR characters to represent the primary key
S_id: this is actually the number of the wholesaler, that is, the wholesaler from which the fruit comes. The purpose of writing this field is to facilitate the later expansion of the table.
F_name: name of the fruit
F_price: the price of the fruit, using the DECIMAL data type. If you don’t know this type, check out the previous article on data types.
Add data.
INSERT INTO fruits(f_id,s_id,f_name,f_price) 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);
The last few Xbabay’s were written for testing purposes, not practical.
Note: When copying my code into the CMD window, make sure that there are no Spaces between the statements, otherwise you will get an error. I add Spaces at the beginning of each line to make it easier for you to see.
1.1. Query all fields
SELECT * FROM fruits;
* represents all fields, that is, all fields from the table below the degree of the query
1.2. Query a specified field
SELECT f_name, f_price FROM fruits;Copy the code
1.3. Query the specified record
Specify records: that is, according to the conditions to query, will meet certain conditions to query out, using the WHERE keyword
SELECT * FROM fruits WHERE f_name = ‘apple’; // Query all information levels of the record named apple
1.4 query with IN keyword
IN key words: 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(101, 105); S_id is 101 or 105
SELECT * FROM fruits WHERE s_id NOT IN(101,105); //s_id Is not a record of 101 or 105
1.5 range query with BETWEEN AND
BETWEEN … AND … : in the… To… The value within the range is the match,
SELECT * FROM fruits WHERE f_price BETWEEN 5 AND 15; F_price is between 5 and 15, including 5 and 15.
1.6. Query character matching with LIKE
LIKE: fuzzy query. The wildcards used with LIKE are “%” and “_”.
“%” : can match characters of any length.
“_” : matches only one character
SELECT * FROM fruits WHERE f_name LIKE ‘b%’; //f_name All records starting with the letter B
SELECT * FROM fruits WHERE f_name LIKE ‘b%y’; //f_name All records starting with b and ending with y
SELECT * FROM fruits WHERE f_name LIKE ‘___y’; // Query for records ending with y and preceded by only four characters
1.7. Query null values
A NULL value is not an empty string “” or 0. It usually means that the data is unknown or will be added later. That is, when data is added, the default value on the field is NULL. You can query it at this point.
SELECT * FROM table_name WHERE table_name IS NULL; // Query the record whose field name is NULL
SELECT * FROM table_name WHERE table_name IS NOT NULL; // Query for records whose field names are not NULL
Create table, add data, and test this statement
Select * from ‘AND’
AND: is the equivalent of “logical AND”, which means that both conditions are met to be a match
SELECT * FROM fruits WHERE s_id = 105 AND f_price > 5; // A match is performed only when s_id = 101 and f_price >5 are met.
1.9 multi-condition query with OR
OR: equivalent to “logical OR”, that is, if one of the conditions is met, the match is considered, similar to the effect of the IN keyword
SELECT * FROM fruits WHERE s_id = 105 OR f_price > 10; //s_id =101 or f_price >10, if either condition is met, the match is considered
If s_id is not 101, its f_price must be greater than 10. As long as one of these conditions is met, it’s a match.
1.10. Keyword DISTINCT(The query result is not repeated)
The SELECT s_id FROM fruits; // Select all s_id from s_id.
SELECT DISTINCT s_id FROM fruits;
When the duplicate values are removed, there are only seven records left.
1.11, ORDER BY query results
See above output values are not in order, can sort them. Using the keyword ORDER BY, there are two values to choose from: DESC descending and ASC ascending (default)
SELECT DISTINCT s_id FROM fruits ORDER BY s_id; // The default is ascending,
SELECT DISTINCT s_id FROM fruits ORDER BY s_id DESC; // Use descending order, that is, from the top to the bottom
1.12, GROUP BY
Group query a lot of people do not know what the meaning, I was also very confused at the beginning, so it does not matter, let’s have a look.
Grouping query is the same thing in a group assigned, put two real life example, toilets for men and women, this is also a group of application, before has not points men’s and women’s toilet, Shared toilet, all degrees behind by gender, man with man is divided into a group, a woman and a woman was divided into a group, the toilet was divided into men and women. S_id = s_id; s_id = s_id; s_id = s_id;
SELECT s_id FROM fruits GROUP BY s_id; S_id is grouped by wholesaler. Fruit from 101 wholesalers will be placed in group 101
After grouping s_id, there are no duplicates, because the degree of duplicates is grouped into one 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 that counts the number of records,
GROUP_CONCAT(): displays the values of each field in the group
SELECT s_id, COUNT(f_name), GROUP_CONCAT(f_name), GROUP_CONCAT(f_price) FROM fruits GROUP BY s_id;
SELECT s_id,COUNT(f_name),GROUP_CONCAT(f_name) FROM fruits GROUP BY s_id HAVING COUNT(f_name) > 1; // He can group by s_id, and then filter out the group information of fruit species greater than 1.
Conclusion:
Knowing the meaning of GROUP BY, I can use HAVING to filter groups. HAVING and WHERE are both conditionally filtered. The difference is that WHERE is filtered before the GROUP while HAVING is filtered after the GROUP.
1.13. Use LIMIT to LIMIT the number of query results
LIMIT allows you to select any number of rows in a database table, so you can retrieve records 5 through 10, or 12 through 15, without iterating from the first record. Look at the following example
SELECT * FROM fruits LIMIT 4; // There is no write position offset. The default value is 0, which means that the first data is fetched from the first data point to the fourth data point.
SELECT * FROM fruits LIMIT 4,3; // Select * from ‘5’ and select * from ‘8’
Note: the first argument to LIMIT defaults to 0, that is, the index of the first record is 0, starting from 0, and the second argument means how many rows to fetch, which is needed to determine the range of a fetch record
Set function query
1.14. COUNT() function
This function is used to count the total number of rows in a table, or to return the number of rows in a column based on the query result.
COUNT(): Counts the total number of rows in a table, regardless of whether a column has a value or a null value, because it represents all rows in the table
COUNT(field name): COUNT the total number of rows under the field name, ignoring rows with NULL values.
SELECT COUNT(*) FROM fruits;
1.15. SUM() function
SUM() is a summation function that returns the SUM of the specified column values
SELECT SUM (f_price) FROM fruits; SUM() = SUM()
If anyone thinks this test is wrong, they can add all f_prices manually. And then compare it. I believe it anyway.
1.16 AVG() function
AVG () function by counting the number of rows returned and every line of figures and, in the average of the specified column data (column data refers to the data field name and don’t be confused columns and rows, not sure which is listed on the table to clear which is the line), popular point, will be computed in all divided by the total number of an average,
SELECT AVG(f_price) FROM fruits;
That’s the same thing as 116/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;
Two, multi table query
The little knowledge
Alias the table
Since you are querying two tables, it is a bit cumbersome to write the table name each time, so a simple alias is used to represent the table name
Format: table name AS alias
You’ll see it in the examples below, so don’t be surprised
SELECT f_price AS ‘price’ FROM fruits; SELECT f_price AS ‘price’ FROM fruits;
Statement execution order problem
First, 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
That is, every time we execute an SQL statement, we start FROM.
Based on two tables
Create a foreign key constraint by adding a Suppliers table with which FRUITS s_id field points to the primary key of SUPPLIERS. In order to create a foreign key constraint, the s_id field of FRUITS points to the primary key of Suppliers.
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));Copy the codeIt’s not really a foreign key constraint, it’s just a simulation, so that the s_id in FRUITS matches the primary key in Suppliers, and that’s achieved by manually adding this data, it’s dead anyway, there’s no more data to add, so you don’t have to create a foreign key constraint, so be clear here
INSERT INTO suppliers(s_id,s_name,s_city,s_zip,s_call)
VALUES
(101,’Supplies A’,’Tianjin’,’400000′,’18075′),
(102,’Supplies B’,’Chongqing’,’400000′,’44333′),
(103,’Supplies C’,’Shanghai’,’400000′,’90046′),
(104,’Supplies D’,’Zhongshan’,’400000′,’11111′),
(105,’Supplies E’,’Taiyuang’,’400000′,’22222′),
(106,’Supplies F’,’Beijing’,’400000′,’45678′),
(107,’Supplies G’,’Zhengzhou’,’400000′,’33332′);
2.1 ordinary double table join query
Query the wholesaler number of fruit, wholesaler name, fruit name, fruit price
Analysis: Look at the requirements, know to query two tables, if you need to query two tables, so the relationship between the two tables must be a foreign key relationships, or similar to the foreign key relationships (similar to that is to say two tables and no real foreign key constraints, but its characteristics and foreign keys are the same as the above we manually create two tables, although there is no set up foreign key relationships, But the properties are the same as foreign key relationships.
SELECT s.s_id,s.s_name,f.f_name,f.f_price FROM fruits AS f, suppliers AS s WHERE f.s_id = s.s_id;
Note: the first execution is FROM, so the above alias for the table 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, the only difference is the syntax is not the same
INNER JOIN Table name ON Connection condition
Query the wholesaler number of fruit, 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 statement like this makes it a little bit clearer
One more thing you need to know in particular is self-join queries. What are self-join queries? The two tables involved are the same table.
Question: Query other fruit types supplied by fruit supplier f_id=’a1′?
SELECT f2.f_id,f2.f_name FROM fruits AS F1 INNER JOIN fruits AS f2Copy the code
Separate fruits as two identical tables, find s_id in F1 where f_id=’ A1 ‘, and then find s_id in F2 where s_id =’a1’, and you will find the answer to the question. There’s another way to do it, instead of using an inline query, you can do it with a subquery, and I’ll talk about that, but I’ll give you the answer first, and then you can come back and look at the problem.
SELECT f_id,f_name
FROM fruits
WHERE s_id = (SELECT s_id FROM fruits WHERE f_id=’a1′);
The effect and internal join is the same, in fact, the principle is the same, or with the help of two tables, but this is easier to understand, you can understand the above self-join query through this.
2.3. External connection query
An inner join returns rows that meet the query condition (join condition), that is, the associated row is returned.
In addition to returning associated rows, the outer join will also display unassociated rows.
Why would you want to show rows that are not unrelated? This will according to the different needs of the business, for example, order and customers, customers can order can’t order, now you need to know all the orders from the customers, and we can’t only query the order of the user, the user aside and didn’t order does not show, this is not consistent with our business needs, it is said, Since we know the customers with orders, can’t we meet our requirements by querying the single table without including these customers with orders? This is ok, but it is very troublesome. How can we display them together and not so troublesome? To solve this problem, there is such a thing as outer join query.
2.3.1 Left outer connection Query
LEFT JOIN table name ON condition; Return all records in the left table that are equal to the join fields in the right table. In layman’s terms, all records in the left table are displayed with row dimensions in addition to associated rows. Use an example to show the effect.
Since the records of 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 to the suppliers, and fruits corresponding to these two records are not available.
INSERT INTO suppliers VALUES(108,’suppliers T’,’Jiangxi’,’33333′,’11111′);
INSERT INTO suppliers VALUES(109,’suppliers Y’,’Jiangxi’,’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 the LEFT JOIN, so it shows all the records in it, with and without associated items. This is what a left outer join means, showing all the entries in the left side of the table (provided that we use the desired field, which is the field selected after SELECT). If I place the Suppliers table to the right of the LEFT JOIN, 108 and 109 will not be displayed. Take a look at
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 connection query
Format: table name RIGHT JOIN Table name ON Conditional Returns all records in the RIGHT table that are equal to the JOIN field in the RIGHT table
In fact, it is similar to the left outer join, which is to display all the tables on the right
SELECT s.s_id,s.s_name,f.f_id,f.f_name
FROM fruits AS f RIGHT JOIN suppliers AS s
ON s.s_id = f.s_id; // The result of this statement is the same as the left outer join above, but the position is changed, but the effect is the same.
Note:
LEFT OUTER JOIN RIGHT OUTER JOIN LEFT OUTER JOIN RIGHT OUTER JOIN
When writing complex queries like this, you would write SQL statements FROM FROM first
2.4. Compound conditional join query
In the process of join query (internal join, external join), filter conditions are added to limit the results of query and make the results of query more accurate. Generally speaking, it is to refine the conditions of join query.
Question 1: Use INNER JOIN in fruits and Suppliers to query the supply information of the supplier whose S_id is 107 in the 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;
Question 2: In the FRUITS and Suppliers tables, use the INNER JOIN syntax to perform the INNER JOIN query 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
The ORDER BY f.s _id. // increase f._id. The default is ASC, so don’t write it.
The subquery
Subqueries, in which the results from one table are queried to serve as criteria for another query, are called subqueries
2.5. subquery with ANY and SOME keywords
Set up the 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, followed by a comparison operator, returns TRUE if compared to ANY value returned by the subquery, or, more generally, TRUE if ANY of the conditions are met.
SELECT num1 FROM tb11 WHERE num1 > ANY(SELECT num2 FROM tb12); // The result of the query in the TB12 table is used as a conditional parameter in the previous query statement. A match is counted as long as num1 is greater than any of the resulting numbers.
The use of SOME is the same as the use of ANY, so I won’t go into details here.
2.6 Subquery with ALL keyword
When ALL is used, ALL conditions must be met.
SELECT num1 FROM tb11 WHERE num1 > ALL(SELECT num2 FROM tb12); Num1 must be greater than all the query results to be a match
2.7. A subquery containing the EXISTS keyword
The parameter after the EXISTS keyword is any subquery. This parameter is TRUE if the subquery returns a row, and the outer query is queried. If the subquery does not return any row, this parameter is FALSE and the outer query is not queried.
SLEECT * FROM tb11 WHERE EXISTS(SELECT * FROM tb12 WHERE num2 = 3); // select * from tb12 where num2=3
2.8, Subquery with IN keyword
The IN keyword performs the same function as the single table query above, except that the argument IN is a subquery.
SELECT s_id,f_id,f_name
FROM fruits
WHERE s_id IN(SELECT s_id FROM suppliers WHERE s_id = 107);
2.9. Subqueries with comparison operators
In addition to the keywords ALL, ANY, SOME, and so on, you can use plain comparison operators. To compare. For example, when we explained inner join queries above, we used subqueries and the = comparison operator. Without further explanation, we can look at merge result queries above
Using the UNION keyword, you can merge the query results into a single result set. That is, you can use the UNION keyword to join multiple SELECT statements together. Note that when you merge a result set, you only add records to the table. The fields displayed should be the same or they cannot be merged.
2.10. The use of UNION[ALL
UNION: do not use the keyword ALL. Duplicate records are deleted when executed. ALL rows returned are unique.
UNION ALL: does not delete duplicates and does not automatically sort results.
Format:
SELECT name,… FROM the name of the table
UNION[ALL]
SELECT name,… FROM the name of the table
Query information of all fruits whose price is less than 9. Query information of 101 fruits with s_id = 103
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;
The fields displayed are s_id,f_name,f_price, and just merge the two rows into one table. Only the record row is added, and the display of the same three fields, no increment,
3. Use regular expression query
This is as simple as using the REGEXP keyword to specify the regular expression and drawing a table that covers all of the following degrees.
Query records that start with a specific character or string
SELECT * FROM fruits WHERE f_name REGEXP ‘^b’; // Records that begin with b
Query records that end with a specific character or string
3.3. Replace any character in the string with the “.” symbol
3.4 use “*” and “+” to match multiple characters
3.5. Matches the specified string
3.6. Matches any of the specified characters
3.7. Matches a character other than the specified character
Use {n,} or {n,m} to specify the number of consecutive occurrences of the string
Fourth, comprehensive case practice data table query operation
4.1. Setting up the environment
There are two tables: employee table and DEPT table.
CREATE TABLE dept (d_no INT NOT NULL PRIMARY KEY AUTO_INCREMENT,// Department number D_name VARCHAR(50),// Department name d_location VARCHAR(100)// department address); CREATE TABLE employee (e_no INT NOT NULL PRIMARY KEY, e_name VARCHAR(100) NOT NULL,// Employee name e_gender CHAR(2) NOT NULL,// employee gender dept_no INT NOT NULL,// Department id e_job VARCHAR(100) NOT NULL,// position e_salary SMALLINT NOT CONSTRAINT dno_fk FOREIGN KEY(dept_no) The REFERENCES dept (d_no)); Table structureCopy the code
Insert data
INSERT INTO dept VALUES (10,'ACCOUNTING','ShangHai'), (20, 'RESEARCH', 'BeiJing'), (30 'SALES', 'give it'), (40, 'OPERATIONS', 'FuJian'); Explanation: ACCOUNTING: -Penny: Accounting RESEARCH SALES OPERATIONS INSERT INTO employee VALUES SMITH (1001, ' ', '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'), CLARK (1007, ' ', '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'). TURNER (1010, ' ', '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 :femaleCopy the code
4.2. Query operation
Select * from employee where e_NO, E_name, e_salary
SELECT e_no,e_name,e_salary FROM employee;
Select * from EMPLOYEE where depT_no = 10 and dept_no = 20
SELECT * FROM employee WHERE dept_no IN(10,20);
Select * from EMPLOYEE where salary ranges from 800 to 2500
SELECT * FROM employee WHERE e_salary BETWEEN 800 AND 2500;
In the Employee table, query the employee information in department 20
SELECT * FROM employee WHERE dept_no = 20;
4.2.5 In the Employee table, query the employee with the highest salary in each department
Analysis: Each employee may be in a different department. To find out all the employees in each department, we should think of groups and divide the employees in the same department into groups. Then use the MAX() function to compare the maximum salary. GROUP_CONCAT(e_salary), GROUP_CONCAT(e_salary), GROUP_CONCAT(e_salary), GROUP_CONCAT(e_salary), GROUP_CONCAT(e_salary) We simply MAX(e_salary) and compare all e_salary in the group to get the largest one.
SELECT e_no,e_name,MAX(e_salary) FROM employee GROUP BY dept_no;
I tried other methods, but eventually I had to compare people in the same department. Only by grouping can I compare people in the same department. If not grouping, then all records will be compared.
4.2.6 Query the department and location of employee BLAKE
Select join (*), join (*), join (*), join (*), join (*);
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;
Method 2: 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 Use connection query to query departments and department information of all employees
A. join B. join C. join D. join
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 In Employee, calculate the number of employees in each department
Analysis: Use groups for each department
SELECT COUNT(e.e_name)
FROM employee AS e
GROUP BY e.dept_no
Note: SUM() and COUNT() are separate, SUM() is the total number of rows, COUNT() is the total number of rows.
4.2.9 In the Employee table, calculate the total salary of different types of employees
Analysis: Group employees by job type
SELECT e.e_job,SUM(e.e_salary)
FROM employee AS e
GROUP BY e.e_job;
4.2.10 In the Employee table, calculate the average salary of different departments
Analysis: Group departments
SELECT e.dept_no,AVG(e.e_salary)
FROM employee AS e
GROUP BY e.dept_no;
Select * from EMPLOYEE where salary is lower than 1500
SELECT * from employee WHERE e_salary < 1500;
4.2.12 In the Employee table, query records are arranged in descending order of department number and then in descending order of employee salary
SELECT * FROM employee ORDER BY dept_no DESC,e_salary DESC;
4.2.13 In the EMPLOYEE table, query information about employees whose names start with A or S
SELECT * FROM employee WHERE e_name LIKE ‘A%’ OR e_name LIKE ‘S%’;
4.2.14 In the EMPLOYEE table, select the employees whose working experience is longer than or equal to 10 years
SELECT * FROM employee WHERE (YEAR(NOW()) – YEAR(hireDate)) > 10;
4.3 Perform the following operations in the employee table
4.3.1 Calculate the age of all female employees (F) (from entry to present)
SELECT e.e_name,e.e_gender,YEAR(CURDATE()) – YEAR(e.hireDate)
FROM employee AS e
WHERE e.e_gender = ‘f’;
4.3.2 Use LIMIT query from the third record to the sixth record
SELECT * FROM employee LIMIT 2,3;
4.3.3 Query the minimum salary of SALSEMAN
SELECT MIN(e_salary)
FROM employee
WHERE e_job = ‘SALESMAN’;
4.3.4. Query records whose names end with letter N or S
A:
SELECT * FROM employee WHERE e_name LIKE ‘%N’ OR e_name LIKE ‘%S’;
SELECT * FROM employee WHERE e_name REGEXP ‘N ‘ORenameREGEXP’S’ OR e_name REGEXP ‘ORenameREGEXP’S ‘;
4.3.5 Query the name and position of employees working in BeiJing
SELECT name,e_job FROM employee WHERE dept_no = (SELECT d_no FROM dept WHERE d_location = ‘BeiJing’);
SELECT e._name, e.job FROM employee AS e INNER JOIN dept AS D ON e.dept_no = D.dept_no AND D.dep_location = ‘BeiJing’;
Select * from EMPLOYEE and DEPT
SELECT * FROM employee LEFT JOIN dept ON employee.dept_no = dept.d_no;
4.3.7 Query the information of all employees who entered the company from 2001 to 2005, query the information of employees whose department numbers are 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(20,30);
4.3.8 Use LIKE to query employee name records containing letter A
SELECT * FROM employee WHERE e_name LIKE ‘a%’ OR e_name LIKE ‘%a’ OR e_name LIKE ‘%a%’;
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 last
I have compiled a MySQL database document Java systematic information (including Java core knowledge, Spring series of family, interview topics and 21 years of the latest Internet real questions, e-books, etc.) friends who need to pay attention to the public number [procedure Yuan small wan] can be obtained.