SQL Review II (data query language)

1.1 Data Query Language

DQL is the data query language, the database executes the DQL statement does not change the data, but lets the database send the result set to the client.

Grammar:

SELECT selection_list/* The name of the column to query */

From table_list/* Table name to query */

WHERE Condition/* Line Condition */

GROUP BY Grouping_columns/* Group results */

Having condition * * After grouping the row condition */

ORDER by Sorting_columns/* Group results */

Limit Offset_start, row_count/* Result limit */

1. Basic Query

1.1 Querying all Columns

Select *  from Stu;

1.2 Querying the specified column

Select sid,sname form Stu;

2. Conditional query

2.1 Article Query Introduction

A conditional query is a WHERE clause is given at query time, and the condition is qualified in the WHERE clause:

=,!=,<>,<,<=,>,>=;

Beteween......and ...

In (set)

is null;

and;

Or

Not

2.2 Query gender for female, and age 50 of records

Select *  from where gender= and age>;

2.3 Inquiry number is s_1001, or the name is Lisi Records

*  from where sid='s_1001 ' or name= ' liSi ';

2.4 The records of the inquiry number s_1001,s_1002,s_1003

Select *  from where inch ('s_1001','s_1002','s_1003  ');

2.5 Inquiry number is not a record of s_1001,s_1002,s_1003

Select *  from where  not inch ('s_1001','s_1002','s_1003 ');

2.6 Querying for records with a null age

Select *  from where  is null;

2.7 Check the student record of age 20 to 40

Select *  from where age> , age<, or select *  from where between  -  and ;

2.8 Query Gender non-male student records

Select *  from where gender! ='male'; or select*  fromwhere gender<>'male';
Or

SELECT * from Stu where not gender= ' male ';

2.9 Querying a student record whose name is not null

Select *  from where  is  not NULL ; or Select *  from where  not  is null;

3. Fuzzy query

Fuzzy queries need to use the keyword like.

3.1 Check the student record with name 5 letters

Select *  from where like ' _____ ';

A fuzzy query must use the LIKE keyword. Where "_" matches any one letter, 5 "_" represents 5 arbitrary letters.

3.2 Query name consists of 5 letters, and the 5th letter is "I" Student record

Select *  from where  like ' ____i ';

3.3 Querying student records with names beginning with "Z"

Select *  from where  like ' z% ';

Where "%" matches any letter of 0~n.

3.4 Check the student record of the 2nd letter "I" in the name

Select *  from where  like ' _i% ';

3.5 Student records with the letter "a" in their name

Select *  from where  like ' %a% ';

4 Field control Query

4.1 Removing duplicate records (with DISTINCT keyword)

Select distinct  from EMP;

4.2 Viewing the sum of the employee's monthly salary and commission

Because the types of Sal and comm Two columns are numeric types, you can do the add operation. If there is a field in Sal or comm that is not a numeric type, an error occurs.

Select ename,sal+ from EMP;

The Comm column has many records with a value of NULL, because anything with null adds a result or null, so the settlement result may appear null. The following function ifnull is used to convert null to value 0:

Select ename,sal+ifnull (comm,0 from EMP;

4.3 Adding aliases to column names

In the query above the column named Sal+ifnull (comm,0), which is very ugly, now we give this column an alias, for total:

Select ename, sal+ifnull (comm,0 as from emp; or Select ename, Sal+ifnull (comm,0 from EMP;

When you alias a column, you can omit the AS keyword:

5 sort

5.1 Query All student records, sorted by age ascending

Select *  from where Order  by ASC  Select*from thewhereorder by age;

The default is sorted in ascending order.

5.2 Querying all student records, sorted by age in descending order

Select *  from where Order  by desc;

5.3 Query all employees, in descending order of monthly salary, if the monthly salary is the same, in ascending order by number

Select *  from Order  by desc ASC;

Select *  from Order  by desc ASC;

6 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,

Min (): Calculates the minimum value for the specified column.

SUM (): Calculates the value of the specified column and, if the specified column type is not a numeric value, evaluates to 0;

AVG (): Calculates the average of the specified column if the type of the specified column is not a numeric value, then the result is 0;

6.1 COUNT

Count () can be used when vertical statistics are required.

Query the number of records in the EMP table:

Select Count (*)   as  from EMP;

• To inquire about the number of commissions in the EMP table:

Select Count  as  from EMP;

Note that because the Comm column is given in the count () function, only the number of non-null rows in the Comm column is counted.

• To inquire about the number of people earning more than 2500 in the EMP table:

Select Count (*as fromwhere sal>2500;

• Number of people with a monthly salary and commission of more than $2500:

Select Count (*)   as  from where sal+ifnull (comm,0)>2500;

• Check the number of people with a commission and the number of leaders:

Select Count (comm),count from EMP;

6.2 Sum and AVG

Use the sum () function when vertical summation is required.

• Check the monthly salary of all employees and:

Select sum  from EMP;

• Check all employees monthly and, and all employee commissions and:

Select sum (SAL),sum from EMP;

• Check all employees ' monthly salary + Commission and:

Select sum (Sal+ifnull (comm,0 from EMP;

• To count all employees ' average salary:

Select avg  from EMP, or Select sum (SAL) / Count  from EMP;

6.3 Max and Min

• Check the maximum wage and minimum wage:

Select Max (SAL),min from EMP;

7 Group queries

When grouping queries are required, you need to use the GROUP BY clause, such as querying the payroll for each department.

7.1 Group Queries

• Query the department number of each department and the salary of each department and:

Select deptno,sumfromgroup by Deptno;

• Query the department number for each department and the number of people in each department:

Select deptno,count(*fromgroup by Deptno;

• Query the department number for each department and the number of people who pay more than 1500 per department:

Select deptno,count(*fromwhere sal>1500  Group by Deptno;

7.2 HAVING clause

• Query the department number with the sum of the wages greater than 9000 and the salary and:

Select deptno,sumfromgroupby havesum(sal)  >9000;

Note that where is the condition that is recorded before grouping, and if a row of records does not meet the conditions of the WHERE clause, the row record does not participate in grouping, and having is a constraint on the data after grouping.

8 LIMIT

Limit is used to limit the starting row of the query result and the total number of rows.

8.1 Querying 5 rows of records starting from 0.

Select *  from 0,5;

Note that the starting line starts at 0, which is the first line!

8.2 Querying 10 rows of records starting from 3.

Select *  from 3,ten;

SQL Review II (data query language)