20150103--sql Connection Query + view-01

Review

Column Properties: comments and unique keys

Relationship: One-to-many, multi-pair, many-to-many

Paradigm: The way to standardize the design of data tables

FOREIGN key: FOREIGN KEY constraint (strict, empty, cascade)

Advanced data manipulation: adding or deleting changes to query operation

Full syntax

Select select option expression FROM clause WHERE clause GROUP BY clause HAVING clause ORDER BY clause limit clause GROUP BY grouping grouping syntax

Group BY is the grouping of data according to a condition.

Syntax: GROUP BY field

After grouping, the data is taken first.

Grouping is mainly used for data statistics. Statistical functions

(Group statistic function)

Max: Ask for maximum value

Min: Minimum value

Avg: Average

Count: The total number of records, if the count (*) is the statistic record count, if the count (field) is actually a statistical record number, if the field is changed to a value of NULL, then no statistics.

Sum: Sum

Count does not count as empty fields (in field statistics units)

Principle of grouping statistics

Group_concat: Connect all data from a field in a grouping statistic

Multi-field grouping

Group By field 1, Field 2 ....

Grouping sort

Syntax: Group BY field [Asc|desc]

Retrospective statistics

Note: It takes time to understand.

The system goes back up and down the layer, depending on the case, until the top level.

Syntax: Use with rollup after all the grouping fields;

HAVING clause

The HAVING clause, like the WHERE clause, is used for conditional judgment. Difference 1

Where is when the data is read from the disk into memory

Having is all the conditions before judging the grouping statistics

Difference principle

Difference 2

A field alias can be used in a HAVING clause, and where cannot use the

Difference 3

Having the ability to use statistical functions, but where cannot use

ORDER BY clause

Sort clauses to sort the corresponding fields. Sort depends on the proofing set.

Grammar

Order BY field [Asc|desc];

Multi-field sorting

Order BY field [Asc|desc], field 2 [Asc|desc];

Limit clause

The basic use of limit is used to restrict the amount of data accessed.

Limit standard usage syntax

Limit offset,length;

Gets the corresponding length record from the specified position (offset)

Application of Limit: Paging

What you must know about paging: page numbers, the length of data displayed per page

Length: Long

Offset: (page – 1) * Length Start position union query

Federated query: The results of multiple queries are combined vertically, without changing the number of fields, only changing the number of records. Grammar

SELECT statement

Union Union options

SELECT statement

Union option: Exactly the same as the Select option, the union option defaults to distinct (de-weight)

Attention

1. When using union, you must ensure that the number of fields between multiple query statements is consistent

2. When union is used, there is no concept of the type of data, only the concept of the same number of columns.

Union meaning

1. Merging data from different tables (too large to hold a single table), often for data statistics

2. Different forms of presentation of data on the same table.

Requirements: The students in the 1 classes of students according to the age of ascending order, 2 classes of students in descending order by age order by use

(SELECT statement ORDER BY clause)

Union

(SELECT statement ORDER BY clause);

Using order by in Union must match the limit

(SELECT statement ORDER BY clause limit clause)

Union

(SELECT statement ORDER BY clause limit clause);

Demand:

1. Get all students in Class 1

SELECT * FROM student where c_id = 1;

2. Get all the students in Class 1 and get the corresponding class information

3. Select * FROM student as S left JOIN Class C on s.c_id = c.id where c_id = 1; Connection Query

Connection query: Two tables of data, the field of stitching, the number of fields will certainly increase.

Connection queries are divided into several categories: inner connection, outer connection, cross connection, natural connection

Connection keyword: join

Left table: The table to the left of the Join keyword is the left table

Right table: The table to the right of the Join keyword is the right table cross join

SELECT * FROM table 1, table 2;

Cross-joins: crosses join

The result of cross connection is Cartesian product, so we should try to avoid the Cartesian product appearing. Internal connection

Inner joins: The data that appears in the left table also exists in the right table, so the record is saved and the record is not needed if it does not exist.

Grammar:

l_table [inner] join r_table on join condition

Intra-connection without connection conditions

Internal connection

Inner Connection principle

Condition matching for inner joins can be used Where,on can instead of

External connection

Outer joins: Take a table as the main table, hold all the records in the table to match in another table, if the match succeeds, keep all the records, if the match fails, then the fields with unmatched success are all empty.

Grammar:

Left (outer) connection: Left table is the primary table, the right table is a secondary table, and the l_table [out] join r_table the on join condition

Right (outer) connection: the right table is the main table, left table is the secondary table, and the l_table "out" join r_table on Join condition

Left connection

Right connection

Left join connecting to right

Requirements: access to students and corresponding class information, requires only 1 classes of all students

Natural connection

Natural connection: When connecting the table, you do not need to specify the connection conditions, the system automatically matches.

Natural connections include: natural inner Connection, natural outer connection

Grammar:

L_table Natural [left/right] join r_table;

Natural Inner Connection

After you modify the Name field of the student table

Natural connections after matching, only one field with the same name is preserved (left table is reserved)

Natural external connections

Inner and outer connections simulate natural connections: Use the same name field as the join condition, and merge the same name field.

Syntax: using (Field list)

Internal Connection Simulation

External connection Simulation

Using simulate multi-field natural joins

Note: In actual project development, requirements are usually met with internal and external connections, with little use of natural connections and cross-connections.

Multi-table connection: Exactly the same as two sheets

A join B on a. field = B. field join C on a. field = C. field ...

Demand:

1. Identify all 1 classes of students;

SELECT * FROM student where c_id = 1;

2. Identify all students in the PHP141115 class

Solution 1:select * From student S left joins Class C on s.c_id = c.id where c.name = ' PHP141115 ';

Solution 2:

Find php141115 corresponding class Id:select ID from class where name = ' PHP141115 ';

Find all the class students by ID: SELECT * from student where id =?;

20150103--sql Connection Query + view-01