MySQL Multi-table query

Multi-Table query:

Implementing a multi-table query is to implement table joins:

Cross join: (Cartesian product)

Example: SELECT * from Student,courses;

Natural connection: (inner connection)

Natural Connection Examples:

SELECT * from Students,course where student,courses where student.cid1=courses.cid;

Alias of table:

Select S.name,c.cname from student as s,course as C where s.cid1=c.cid

Outer connection: (Left outer connection, right outer connection)

Left outer connection: ... Left join ..... On ....

Example of LEFT outer connection: Select S.name,c.cname from student as s to join courses as C on S.CID1=C.CID; View student table in the person who studies the course, if there is a person who has chosen a course to show null

Right outer connection: ... Right join .... On ....

Example of the right outer connection: Select S.name,c.cname from student as S R join courses as C on s.cid1=c.cid; View Courses table in the course of a person who learns the display of what is learned, nothing shows null.

Note: When using an outer join, use on to specify the condition when the condition is not used.

Self-connect:

Example of self-connection: Select S.name as stu,c.name as teacher from students as s,student as C where S.sid=c.tid;

Sub-query:

Example: Find all students who are older than the average age:

Select name from student where > (select AVG (age) from students); Subqueries in subqueries can only be single values.

(2) using subqueries in In:

Example: Show the teacher's age and student's age as students:

Select name from student where-age-in (select-age from Tutors);

(3) Query results for the table again:

Show people who are older than or equal to 20 in the queried table:

Example: Select Name,age from (select Name,age from student) as T where t.age>=20;

Set up the results of two queries:

(select Name,age from Student), union (select Tname,age from tutors);

Union query: Union

Exercise: Pick out the course name of a course in the courses table that is not taught by students Cid2:

Mysql> Select Cid,cname from the courses where CID not in (select distinct CID2 from students where CID2 are not null);

Select a teacher who does not teach any courses:

Mysql>select tid,tname from tutors where TID isn't in (select TID from courses);

Pick out the course name of the same course that two or two students have studied in the Students table CID1:

Msyql> Select Cid,cname from Courses where CID in (select CID1 from students GROUP by CID1 have COUNT (CID1) >=2);

Show each teacher and the courses he teaches; The courses that are not taught by the teacher are kept null:

Mysql> Select B.tname, a.cname from tutors as B left joins courses as a on a.tid=b.tid;

Shows each teacher and the course he teaches, without the teacher's lessons showing the teacher empty:

Mysql> Select B.tname, a.cname from tutors as B right joins courses as a on a.tid=b.tid;

Show each student the course name of the CID1 course and the name of the teacher who taught the relevant course:

Mysql> Select Name,cname,tname from students,courses,tutors where students. Cid1=courses. CID and courses. Tid=tutors. TID;

View:

A view is a stored SELECT statement;

The query results based on the base table.

Creating a view: Create View

Help: Helping CREATE view

Create VIEW Example: Create View SCT as (select Name,cname,tname from students,courses,tutors where students. Cid1=courses. CID and courses. Tid=tutors. TID);

Show tables can see that the view is also shown as a table.

Note: In general, inserting data into a view is not allowed.

Show Table Status\g View the state of a table can distinguish between views and base tables.

Delete view: Drop View view_name

Help command to delete a view: Helping drop view

See how views are created commands

Show CREATE VIEW view_name

Show CREATE View View_name\g

Materialized view: Because the view we create is the result of a select query, when the result of a SELECT statement query is very large, two queries on the views cause the server cache consumption to be very large. So we can save this view to reduce the consumption of the cache. The advantage of this is to save the results of the query directly next time without a SELECT statement.  The downside is what to do when the base table is updated. Therefore, materialized views are recommended for tables that are not frequently updated and have a large amount of data, and are not recommended for those updates that are relatively quick to use. MySQL does not support materialized views!

Note: View the command that represents how to create (that is, the statement created): Show CREATE TABLE Tb_name

This article is from the "Wangconggao" blog, make sure to keep this source http://wangconggao.blog.51cto.com/3253868/1579684

MySQL Multi-table query