DQL - Data Query Language
Queries do not modify database table records!
I. Basic Queries
1. Field (column) control
1) Query all columns
//Where "*" means querying all columns
SELECT * FROM Table name;
SELECT * FROM emp;2) Query specified columns
SELECT column 1 [, column 2,... Column N] FROM table name; SELECT empno, ename, sal, comm FROM table names;
3) Complete repetition of records only once
When the multiple rows in the query result are identical, only one row is displayed. This is rarely the case when querying all columns, but it is always possible to query only one column (or columns).
//Guarantee to inquire the salary of the employee's salary, if there is the same salary, only show once!
SELECT DISTINCT * | column1 [, column2, ... column N] FROM Table name;
SELECT DISTINCT sal FROM emp;4) column operation
Columns of type I can be added, subtracted, multiplied and divided
SELECT sal*1.5 FROM emp;
SELECT sal+comm FROM emp;II String Type Can Do Continuous Operations
SELECT CONCAT('$', sal) FROM emp;III Conversion NULL Value
Sometimes we need to convert NULL to other values, such as com+1000, if there is NULL value in com column, then NULL+1000 is NULL, and we want to calculate NULL current 0.
//IFNULL(comm, 0): If there is a NULL value in comm, it is treated as zero.
SELECT IFNULL(comm, 0)+1000 FROM emp;IV Aliases
As you may have noticed, when using column operations, the column names in the query result set are not very attractive, so we need to name the columns individually so that the aliases are displayed in the result set.
//Among them AS can be omitted.
SELECT IFNULL(comm, 0)+1000 AS bonus FROM emp;2. Conditional control
1) Conditional Query
Like the UPDATE and DELETE statements described earlier, the SELECT statement can also use the WHERE clause to control records.
SELECT empno,ename,sal,comm FROM emp WHERE sal > 10000 AND comm IS NOT NULL;
SELECT empno,ename,sal FROM emp WHERE sal BETWEEN 20000 AND 30000;
SELECT empno,ename,job FROM emp WHERE job IN ('manager', 'Chairman');2) Fuzzy Query
When you want to query an employee whose last name is Zhang and whose name is a total of two words, you can use fuzzy query.
Fuzzy queries require operators: LIKE, where matches an arbitrary character, note that only one character is matched, not more.
//The last name of the employee is Zhang, whose name is composed of two characters.
SELECT * FROM emp WHERE ename LIKE 'Zhang_';
//An employee whose name consists of three words
SELECT * FROM emp WHERE ename LIKE '___'; //If we want to inquire about the name sheet, we need to use "%" when the employee whose name can be several words. Among them% matches 0-N arbitrary characters.
SELECT * FROM emp WHERE ename LIKE 'Zhang%';//Never think that the above statement is an employee with A character in the middle of the query name, because% matches 0 to N characters, so the employee whose name begins with A and ends with A will also be queried.
SELECT * FROM emp WHERE ename LIKE '%A%';//This condition is the same and does not exist, but if the name of NULL query can not come out!
SELECT * FROM emp WHERE ename LIKE '%';Two, sort
1) ascending order
//Sort by sal, ascend! Among them, ASC can be omitted.
SELECT * FROM WHERE emp ORDER BY sal ASC;2) descending order
//Sort by comm, descend! DESC cannot be omitted
SELECT * FROM WHERE emp ORDER BY comm DESC;3) Use multiple columns as sorting criteria
//Use ascending sals, and descending comm if sals are the same
SELECT * FROM WHERE emp ORDER BY sal ASC, comm DESC;III. Aggregation Function
Aggregation functions are used to do longitudinal operations on a column.
1) COUNT
//Calculate the number of rows in the emp table that all columns are not NULL records
SELECT COUNT(*) FROM emp;// Number of rows that comm column is not NULL record in cloud computing emp table
SELECT COUNT(comm) FROM emp;2) MAX
// Search for maximum wage
SELECT MAX(sal) FROM emp;3) MIN
//Search for Minimum Wage
SELECT MIN(sal) FROM emp;4) SUM
//Search for salary package
SELECT SUM(sal) FROM emp;5) AVG
//Query Average Wage
SELECT AVG(sal) FROM emp;IV. Grouping Query
Group query is to group records using a column, and then query group information.
1) Check the records of all departments.
//Use deptno grouping to query Department numbers and the number of records per department
SELECT deptno, COUNT(*) FROM emp GROUP BY deptno;//Use job grouping to query the maximum salary for each job
SELECT job, MAX(SAL) FROM emp GROUP BY job;2) group conditions
//Group by department and query the number of records in each group. The condition is that the number of records is greater than 3
SELECT deptno, COUNT(*) FROM emp GROUP BY deptno HAVING COUNT(*) > 3;5. limit clause (dialect)
LIMIT is used to limit the number of initial rows and total rows of query results.
1) Query Start Behavior Line 5, a total of three rows of records
//Four of them represent starting at line 5, and three of them represent a total of three queries. That is, lines 5, 6 and 7.
SELECT * FROM emp LIMIT 4, 3;6. Query Template
select
from
where
group by
having
order by