First, the basic query
Querying all Columns
SELECT * from table name;
Querying a specified column
Select field 1, Field 2 from table name;
Second, the conditions of inquiry
The WHERE clause is given when querying to limit the results of the query to meet certain requirements
Operators and Keywords:
=,! =, <>, >=, <=,;, <
SELECT * FROM student where s_age>20;
Between A and b--between A and B (including A and b)
SELECT * FROM student where s_age between and 30;
In (value1, value2, Value3...valuen)-values in Value1, value2, Value3...valuen
SELECT * from student where s_id in (' 20170001 ', ' 20170003 ', ' 20170005 ');
is null--value is empty
SELECT * FROM student where s_name is null;
and/or/not--and/or/non-
SELECT * FROM student where s_id in (' 20170001 ', ' 20170003 ', ' 20170005 ') and s_age>20;
SELECT * FROM student where s_age>20 or s_age<9;
The combination of not and is Null has two syntaxes:
1. Select * FROM student where s_name are NOT null;
2. Select * FROM student where not s_name is null;
Third, fuzzy query
Keywords: like
Use underscore ' _ ' to represent any one character
Use '% ' to represent any 0 to more characters
Example: Query the name of a student with a total of five letters starting with S:
SELECT * FROM student where s_name like ' s____ '; Four underscores in the back
Name of student whose name contains S
SELECT * FROM student where s_name like '%s% ';
Iv. field control Query 1, remove duplicate records
Keyword:distinct
When querying the payroll SAL values in the Employee table EMP, when the same payroll record occurs, we need to
Select distinct Sal from EMP;
2. Query sum
Example: Querying employee information in an EMP and viewing employee salary Sal and bonus comm
SELECT *, Sal+comm from EMP;
Note: Only the two are numeric types to add, one of which is not a numeric type and an error, and any type with null addition is NULL
If there is a null value in the Bonus Comm field, the result will be wrong, then we need to change the null value to 0;
Syntax: SELECT *, Sal+ifnull (comm, 0) from EMP;
3. Alias the query result
SELECT *, Sal+ifnull (comm,0) as total from EMP;
Note: As can be omitted, but generally reserved
Five, sort
Keyword: ORDER BY
ASC Ascending; Desc Descending
Example: Querying all student records, sorted by age
SELECT * FROM student order by age; Default Ascending
SELECT * FROM Student ORDER BY age ASC;
Check all student records, sorted by age in descending order
SELECT * FROM Student ORDER by age desc;
When it comes to having multiple collations, sort by the order of the collation, such as: query all employees and arrange them in descending order, when the payroll is in ascending order by employee number
SELECT * from emp ORDER BY sal Desc, id ASC;
Vi. Aggregation functions
An aggregate function is a function used to do a longitudinal operation.
count (): counts the number of record rows for which the specified column is not null;
Max (): calculates the maximum value of the specified column, using string sorting operations if the specified column is a string type;
min (): calculates the minimum value of the specified column and, if the specified column is a string type, uses string sorting operations;
SUM (): calculates the value of the specified column and, if the specified column type is not a numeric type, evaluates to 0;
AVG (): calculates the average of the specified column and evaluates to 0 if the specified column type is not a numeric type.
Example: Statistics on the number of employees with payroll SAL over 3000
Select count (sal) from EMP where sal>3000;
Check the average salary:
Select AVG (SAL) from EMP;
Seven, group query
Sort by field, query results
Keyword:group by
Example: Query Employee Form EMP in department number DEPTNO and the sum of each department's wages
Select Deptno, sum (SAL) from the EMP group by DEPTNO;
Viii. qualifying result start and total rows
Keyword:limit
Example: Query employee information for the top three employees in the EMP
SELECT * from emp ORDER BY sal desc limit 0, 3;
Role: Classification
mysql command Daquan--dql (data query Language)