Chapter Sixth query data

Basic Query statement

The basic syntax for select:

Select attribute List

From table name and view list

[Where Condition expression 1]

[Group By property name 1 [having conditional expression 2]]

[Order BY property 2 [Asc|desc]]

single-table query

As shown in the following table:

Query all fields

Mysql> SELECT * from employee;

query-specific fields

Select NAME,SEX,HOMEADDR from employee;

query Specify Records

Where Condition expression

SELECT * from employee where d_id=2;

Query criteria:

Query criteria	Key words
Comparison	=,<,<=,>,>=,!=,!>,!<
Specified range	Between and, not between and
Specify the collection	In,not in
Matching characters	Like,not like
is empty	is Null,is NOT NULL
Multiple query criteria	And, or

query with in keyword

SELECT * FROM Employee where d_id in (2,3);

Note: If the element is a character, you must enclose the single quotation mark

range query with between and

SELECT * FROM employee where age between and 23;

character matching query with like

[Not] like ' string '

The string must be enclosed in single quotes.

"%" can be used to represent any length of string, b%k can represent Bk,bok,book

' _ ' can only represent a single character.

SELECT * FROM employee where name like ' li%y ';

query null value

SELECT * FROM employee where name is null;

Multi-condition query with and

SELECT * FROM employee where d_id = 1 and sex like ' male ';

SELECT * FROM employee where age<23 and sex like ' Men ';

Multi-condition query with or, on class

query results are not duplicated

Select distinct d_id from employee;

If d_id exists, the statement will display the different values for all of the fields

sort the results of a query

Order BY property name [Asc|desc]

SELECT * FROM employee-by-age;

NOTE: null defaults to Minimum value

grouped Queries

Group BY property name [having conditional expression][with Rollup]

A. Grouping by using the GROUP BY keyword alone

SELECT * FROM employee GROUP by sex;

As above, it makes little sense to display only one record per group, grouped by sex.

The B.group by keyword is used with the Group_concat () function

Select Sex,group_concat (name) from the employee group by sex;

C. GROUP BY keyword used with aggregate functions

Select Sex,count (Sex) from the employee group by sex;

D. Group BY keyword used with the having

Select Sex,count (Sex) from the employee group by sex has count (sex) >=2;

E. Multiple field groupings

SELECT * FROM employee GROUP by D_id,sex;

F. Group BY keyword used with rollup

Select Sex,count (Sex) from the employee GROUP by sex with rollup;

After using rollup, add a new record to show that the grouping is worth the sum

Mysql> Select Sex,group_concat (name) from the employee GROUP by sex with rollup;

limit the number of query results with limit

SELECT * from employee limit 2;

You can specify the starting position:

SELECT * FROM Employee limit 1, 2;

Specifies to start with the first article (default starting from 0th)

using aggregate function Queries

1.count () function

Total number of statistics recorded

Mysql> Select COUNT (*) from employee;

Count the number of records in different d_id

Mysql> Select D_id,count (*) from the employee group by D_ID;

2.sum () function

Sum of field values for some records in the following table

Mysql> Select Name,sum (grade) from student group by S_ID;

3.avg () function

Class on

4.min () function

Class on

3.max () function

Class on

Connection Query

The following two tables are available:

INNER JOIN query

Mysql> Select Num,name,employee.d_id,sex,d_name,function

-> from Employee,department

-> where employee.d_id = department.d_id;

Outer Joins

List of select attribute names

From table name 1 left| Right Join table Name 2

on table name 1. property name 1 = table Name 2. property Name 2;

The property Name list parameter represents the fields that you want to query, and they can come from different tables.

The table name 1 and table name 2 parameters represent the outer joins of the two tables.

"Left" and "right" specify whether to connect the query or the right-hand query.

"On" is followed by the join condition

A. Left connection query

Mysql> Select Num,name,employee.d_id,age,sex,d_name,function from employee left join department on Employee.d_id= department.d_id;

First remove the employee table data, and then remove the Department table data based on the join condition

B. Right connection query

Mysql> Select Num,name,employee.d_id,age,sex,d_name,function from employee right join department on Employee.d_id= department.d_id;

First take out the data of the Department table, and then remove the employee table data according to the connection condition

Compound conditional Join query

Mysql> Select Num,name,employee.d_id,sex,d_name,function from employee,department where employee.d_id = DEPARTMENT.D_ID and age<24;

Child Query subquery with in keyword

Mysql> SELECT * FROM Employee where d_id in (select d_id from department);