Shulou(Shulou.com)06/01 Report--

This article mainly explains how to use mysql join query, federated query, sub-query, the content is clear, interested friends can learn, I believe it will be helpful after reading.

Connection query:

Join query is a combination of multiple tables query, join query methods are internal join, external join, natural join, cross-join. Join queries allow you to view data in multiple tables at the same time. Internal join: a conditional join, in which multiple tables are connected according to the specified conditions, and the matching result is to keep records that match the matching results. Outer join: different from the inner join, no matter whether the match does not match, you can decide which table to keep according to the outer join mode. For example, if you keep the left table, if the left table cannot match the right table, keep the left table data, and then set the right table field data to null. Natural connection: conditional connection, automatically based on "field of the same name" (multiple fields with the same name are taken as conditions). Cross-join cross join: an unconditional join that connects each record to each record in another table (Cartesian product) so that the number of fields is equal to the sum of the original fields and the number of records is equal to the product of the previous table records. -- create table student (id int,name varchar (15), gender varchar (15), cid int); create table class (cid int,cname varchar (15)); drop table student,class;-- table data: insert into student values (1, "lilei", "male", 1), (2, "hanmeimei", "male", 2), (3, "jack", "male", 1), (4, "alice", "female", 4) -- A 4insert into class values (1, "linux"), (2, "python"), (3, "java"), (5, "html5") that is not found in class is specially created;-- A 5select * from student;select * from class that is not found in student is specially created here.

Inner join: take each record from the left table, match all the records in the right table, keep the successful records, and splice the two records. Syntax: select field list from left table [inner] join right table on left table. Field = table on the right. Field; when the on condition is not used, the result is the same as the cross connection-- inner connection-- select * from student inner join class;-the result is the same as the cross connection select * from student join class on student.cid = class.cid;select * from student inner join class on student.cid = class.cid Outer join: different from the inner join, the main table record is retained regardless of whether the match matches or not, the ways are left outer join, right outer join, left outer join is reserved left table, right outer join is reserved right table syntax: left outer join: select field list from left table left join right table on left table. Field = table on the right. Field; right outer connection: select field list from left table right join right table on left table. Field = table on the right. Field; select * from student left join class on student.cid = class.cid

Select * from student right join class on student.cid = class.cid; natural connection: automatically match the connection condition. The system uses the field name as the matching mode (the same name field as the condition, and multiple fields with the same name as the condition). It is similar to the internal connection, but does not provide the connection condition. Natural external connection: similar to external connection, but does not provide connection conditions. Syntax: natural internal join: select field list from table name natural join table name; natural external join: select field list from table name natural left\ right join table name; select * from student natural join class;select * from student natural left join class; cross-join: join each record with each record of another table syntax: select field list from table name cross join table name; select field list from table name, table name; select * from student cross join class;select * from student,class Add: in multiple tables, table aliases can be used to distinguish each table and to make it easy to use. Select * from student inner join class on student.cid = class.cid;-- the original result is select id,name,gender,c.cid,cname from student ass inner join class as c on s.cid = c. CidlyMurray-multiple joins can be made using table aliases. External connections can simulate natural connections, only need to on the connection conditions to the left table. Field = table on the right. Just change the field to "using field name". Federated query: a federated query is the splicing of multiple query results on a record. (equivalent to joining the query record results of other tables to the back of the first table) [because it is splicing, the number of fields of multiple query results must be the same] [splicing does not care about the data type, for example, the first field of the first table is int, but the varchar in the later table can still be spliced into the first column] syntax: select statement union select statement. ; select name,gender from studentunion select * from class;-- because there are only two fields in class, so the first one only selects two fields to supplement: union can be added after union, all option is not the same to remove duplicates, and distinct is deduplicated. Federated queries are generally used to display different data in different ways in the same table. (for example, if you want to query two subjects in a student's score sheet at the same time (assuming python and linux) union and order by use error reporting at the same time, if you want to sort a query result in a federated query, you need to enclose this select statement in parentheses. In addition, because of the stitching mechanism of federated queries, you need to add a limit clause after order by, and the number of limit can be a large value. ] if it is for the final federated query result, use order by in the last select statement [it is recommended to add parentheses to the last field, plus ordery by, where there is a field with the same name] subquery: a subquery is a query nested in the query statement. Subqueries can be divided into three categories according to where they appear: from subqueries: those that follow from; generally used in the case of "find out the two-dimensional table first and then process it". For example:-this is a meaningless example. For example, use select cid,cname from (select * from class where cname= "python") as c; where subquery: subquery is followed by where condition; generally used in the case of "query the specified condition first" such as: select * from student where cid= (select cid from class where cname= "python"); subquery: subquery in the exist statement This is an example of select * from class where exists (select * from student where cid=1) and cid=1; that does not output the corresponding course information if the student does not choose a cid=1 course. In fact, some people think that union is followed by a subquery, but here we do not regard these as subqueries, but only the above several very closely related to the "query" as subqueries. Add: in fact, you can also classify the subquery according to the results: scalar quantum query, the result of the subquery is one row and one column, which usually happens when the where subquery only queries one row and one column. Column subquery, the result of the subquery is multiple rows in one column, which usually occurs when the where subquery queries out multiple rows in one column. Row subquery, the result of the subquery is multiple columns (or multiple rows and multiple columns), which usually occurs when the where subquery produces multiple columns. Table subquery, the result of subquery is multi-row and multi-column. Generally, where subquery in from subquery sometimes uses some other keywords, such as any,all,some, but for =, = can basically achieve their function. After reading the above content, do you have a further understanding of how to use mysql connection query, federated query and subquery? if you want to learn more, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.