mysql command Daquan--dql (data query Language)

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)