MySQL Database Basics (i)

MySQL query and the use of In,all,some,any

Table1:

S1
2
10

table2:

S2
5
12
20

The In,any,some and all operators are used in the list query.

In :Within the specified item, with in (item 1, item 2, ...)Any :Used in conjunction with comparison operators, returns TRUE if any value returned by the subquery is greater than true after the comparison operator.SOME:The alias of any, less used.All :Used in conjunction with comparison operators to return TRUE if all values returned by the subquery are compared to true.

Details: In is the alias of any, and the same is the same, but the alias of not is not a <> any but <> SOME.

not in is an alias for <> all, the same

Special Cases

• If Table2 is an empty table, the result after any is FALSE;
• If the subquery returns an empty result such as the (null,null,null) column, the result after any is UNKNOWN.
• Some examples:

Select S1 from table1 WHERE S1 > No (select s2 from table2)

The result is: 10.

select S1 from table1 WHERE S1 > All (select S2 from table2)

No results are returned.

Note: For table2 empty tables, the following statement returns null:

Select S1 from table1 WHERE S1 > (select s2 from table2)

Select S1 from table1 WHERE S1 > All (SELECT MAX (S1) from table2)

Join in MySQL

Cross Joinis a flute Descartes product is the number of rows in a table multiplied by another table.
Left joinThe join column of the first table does not match in the second table, the value in the second table returns null
Right joinThe join column of the second table does not match in the first table, the value in the first table returns null
Full Joinreturns rows from two tables left Join+right join
INNER JOINreturns only matches for two table join columns

(1) connection

SELECT * FROM Table1,table2
Equivalent to
SELECT * FROM table1 cross join Table2
SELECT * FROM Table1,table2 where table1.row=table2.row

(2) Self-connected
SELECT * FROM Emploly E1, Emploly E2

Select E1.name,e2.name from employ E1,employ E2
where E1.name=e2.name

(3) Inner connection (inner join)

select stuname as ' name ', classname as ' class ' from student inner join class on Student.stuid=class.stuid 
 
 inner join ' table Name ' on condition--Join multiple tables 
 It is equivalent to: 
 Select Stuname as ' name ', classname as ' class ' 
 from Student,class 
 where Student.stuid=clas S.stuid 

 
 (4) outer joins: (outer join) 
 allows you to limit the rows in one table without restricting the rows in another table.  
 Note: Outer joins do not have to have foreign key constraints 
 1:left outer JOIN--the records in the left table cannot be used in the right-out join 
 all appear in the result set, and no corresponding records appear null 
 2:right outer The records in the right table of the join 
 all appear in the result set, with no corresponding record displayed in the coordinates null 
 3:full outer join|full Join--cannot use full out join 
 to return all records that match and do not match in two tables. 
 

Straight_join is the extension of MySQL to standard SQL, which specifies the order in which tables are loaded when querying multiple tables. In join table joins, you can also specify the order in which the tables are loaded, and this article only describes the application of Straight_join in join in table joins.

The MySQL straight_join syntax is as follows:

The Straight_join is actually exactly the same as the inner join INNER join, unlike when Straight_join is used, Table1 is loaded before table2.

If more tables are connected, the load order of Straight_join follows the rules from left to right. Finally, Straight_join cannot be applied to a LEFT join or right join.

NATURAL joins are also called natural connections, which are actually part of a join.

The MySQL NATURAL JOIN syntax is as follows:

... From table1 NATURAL 
 
   
    
   JOIN table2 
 
    ...

When using NATURAL JOIN , MySQL automatically records matches for fields with the same name in the table, and these same name field types can be different. Therefore, the NATURAL JOIN does not specify a match condition.

NATURAL join defaults to a INNER join that exactly matches the same name field, or you can use a LEFT join or right join. Some examples are as follows:

SELECT article.aid,article.title,user.username from article NATURAL JOIN user

Left SELECT

Article.aid,article.title,user.username from article NATURAL left JOIN user

Right SELECT

Article.aid,article.title,user.username from article NATURAL right JOIN user