MySQL Knowledge summary (ii)

Previous MySQL Knowledge summary (a) Portal: http://www.cnblogs.com/rosestudy/p/4820234.html

Iv. Fundamentals of relational operations

1. Select operation (Row Operations single table operation)
2. Projection (Column Operations single table operation)
3. Join Operations (multi-table operation)

• Equivalent joins: Common condition is that some column values of two tables are equal
• Natural joins: Requires two tables to have a common attribute (column), and the result set of the natural join operation is a new table that removes duplicate attributes after the equivalent connection is made on the common properties of the two tables involved

Five, MySQL database query

Choose Select Column

1. Select the specified column
2. Define column alias format: SELECT Column_name as Column_alias
3. Replace the data in the query results
4. Calculate column values
5. Eliminate duplicate row format in result set: SELECT DISTINCT | Distinctrow Column_name[,column_name ...]
6. Aggregate functions (commonly used are: count function, max/min function, Sum/avg function)

 Use Test Select name, school number  from student;            // The result of the execution is the information on the names and student numbers of all the students in the student table.

Select Count (*as' total number of students ' from student;         // Use the Count function to count the number of rows or total lines in the statistics group that meet the criteria

FROM clause

1. Referencing a table
2. Multiple table joins query data in different tables, you must make multiple tables in the FROM clause

• Fully connected
• Join connection

// Find the course name and course number selected by all students in the XSCJ database Select distinct KC. Course name, XS_KC. Course Number  from KC,XS_KC where KC. Course Number =xs_kc. Course number;

WHERE clause

GROUP BY clause

HAVING clause

ORDER BY clause

Limit clause

Union clause

Handler clause

MySQL Knowledge summary (ii)