MySQL Database connection query

MySQL Database connection query

• Connectivity is a key feature of the relational database model. Connection query is the most important query in relational database, including inner connection, outer connection and so on. Multiple table queries can be implemented through connection operations. When querying data, the connection operation queries the different entity information stored in multiple tables. When you have fields of the same meaning in two or more tables, you can use these fields to make a connection query to different tables. The inner JOIN query, the outer join query, and the compound conditional connection query between the multiple tables are described below.

Internal connection Query

An inner join (inner join) uses comparison operators to perform comparison operations on column data between tables, and lists the rows of data in those tables that match the join criteria, and the group synthesizes new records. Create a new student table, class table, and make connection queries between two tables. Where the primary key ID in Tb_class is the foreign key of tb_student, the table structure is as follows:

650) this.width=650; "Src=" http://images2015.cnblogs.com/blog/786913/201702/786913-20170202135051386-515362233. PNG "width=" 364 "height=" 135 "style=" margin:0px auto;padding:0px;border:0px; "/>

The data in the table is as follows:

650) this.width=650; "Src=" http://images2015.cnblogs.com/blog/786913/201702/786913-20170202140818558-976117808. PNG "style=" margin:0px;padding:0px;border:0px; "/> 650" this.width=650; "src=" Http://images2015.cnblogs.com/blog /786913/201702/786913-20170202140721901-1993620542.png "style=" margin:0px;padding:0px;border:0px; "/>

The connection query statement for the table is: SELECT tb_student.*,tb_class.name from Tb_student,tb_class WHERE tb_student.classid=tb_class.id; or SELECT tb_student.*,tb_class.name from Tb_student INNER joins Tb_class on Tb_student.classid=tb_class.id;

650) this.width=650; "Src=" http://images2015.cnblogs.com/blog/786913/201702/786913-20170202141313058-1919179447. PNG "style=" margin:0px auto;padding:0px;border:0px; "/>

If in a connection query, the two tables involved are the same table, which is called a self-join query, the self-join is a special inner join, it refers to the interconnected table is physically the same table, but can be logically divided into two tables. The following is a query for the Tb_student table, with a query and a student ID of 1 for the same class:

650) this.width=650; "Src=" http://images2015.cnblogs.com/blog/786913/201702/786913-20170202151507183-194872848. PNG "style=" margin:0px auto;padding:0px;border:0px; "/>

Outer JOIN query

The join query queries multiple tables for the associated rows, and when connected, returns only rows that meet the query criteria and join criteria in the query results collection. However, it is sometimes necessary to include data in rows that are not associated, that is, the returned query results collection contains not only rows that meet the join criteria, but also the left table (left connection), the right table (right connection), or all the data rows in the two edge table. The outer connection is divided into left outer connection and right outer connection;

1. Left join: Returns records that include all the records in the left table and the equivalent of the join fields in the right table

2. Right join: Returns records that include all records in the right table and join fields in the left table

• Left Join

The result of the left join includes all rows from the left table specified in the outer clause, not just the rows that match the join column. If a row in the left table does not have a matching row in the right table, all select list columns in the right table in the associated result row are null values. The Student and class tables are listed below, and the names of all students in the class are queried, including those who have not yet assigned the class.

650) this.width=650; "Src=" http://images2015.cnblogs.com/blog/786913/201702/786913-20170204153753167-1875641693. PNG "style=" margin:0px;padding:0px;border:0px; "/> 650" this.width=650; "src=" Http://images2015.cnblogs.com/blog /786913/201702/786913-20170204153845229-1221607466.png "style=" margin:0px;padding:0px;border:0px; "/>

Left join the following statement: SELECT Tb_student.name,tb_class.name from tb_student left OUTER JOIN tb_class on tb_student.classid=tb_class.id;

650) this.width=650; "Src=" http://images2015.cnblogs.com/blog/786913/201702/786913-20170204154117042-867865189. PNG "style=" margin:0px auto;padding:0px;border:0px; "/>

650) this.width=650; "Src=" http://images2015.cnblogs.com/blog/786913/201702/786913-20170204155022917-650726267. PNG "style=" margin:0px auto;padding:0px;border:0px; "/>

• Right Join

The right connection is a left-connected reverse connection that returns all rows of the right table, and if a row in the right table does not have a match in the left table, the left table will return a null value, as shown in the Tb_student table with no Computer Class 5 students, so the computer 5 class before the student name is empty.

650) this.width=650; "Src=" http://images2015.cnblogs.com/blog/786913/201702/786913-20170204154925604-1322672853. PNG "style=" margin:0px auto;padding:0px;border:0px; "/>

Sub-query

A subquery is a query that is nested inside another query statement, evaluates a subquery in the SELECT clause, and the subquery results as a filter for the outer layer of another query, which can be based on a single table or multiple tables. The operands commonly used in subqueries are any (SOME), all, in, EXISTS. Subqueries can be added to select, UPDATE, and DELETE statements, and can be nested in multiple layers. Comparison operators can also be used in subqueries, such as <, <=, >, >=, and! =. This section describes how to nest subqueries in a SELECT statement.

• Subqueries with any, some keyword

The any and some keywords are synonyms that satisfy any of these conditions, and they allow you to create an expression that compares the return value list of a subquery, and returns a result as a condition for the outer query as long as any of the comparison criteria in the inner subquery are satisfied. The Any keyword is followed by a comparison operator that returns true if any value returned from the subquery is more true. The following is a list of all scores with a minimum score of 1 students (86):

The SQL query statement is: SELECT * from Tb_score where grade> any (select grade from Tb_score where sid=1);

650) this.width=650; "Src=" http://images2015.cnblogs.com/blog/786913/201702/786913-20170204164949120-966209953. PNG "width=" 217 "height=" 252 "style=" margin:0px;padding:0px;border:0px; "/> 650" this.width=650; "src=" http:// Images2015.cnblogs.com/blog/786913/201702/786913-20170204165059073-998037364.png "width=" 479 "height=" 247 "style= "margin:0px;padding:0px;border:0px;"/>

• subquery with the ALL keyword

The keyword all is different from any and some, and when you use all, you need to satisfy the conditions of the inner query, for example, if you change any of the examples above to all, then the result of the query is higher than the student score of 1 (86,90), then you can see that the results are empty.

• Subquery with exists keyword

After the EXISTS keyword is an arbitrary subquery, the system queries the subquery to determine whether it returns a row, and if at least one row is returned, the result of exists is true, at which point the outer query statement will be queried, and if the subquery does not return any rows, The result of the exists return is false, at which point the outer statement will not be queried.

650) this.width=650; "Src=" http://images2015.cnblogs.com/blog/786913/201702/786913-20170204170823995-351881538. PNG "style=" margin:0px auto;padding:0px;border:0px; "/>

• Sub-query with in keyword

When the In keyword is queried, the inner query statement returns only one data column, and the value in the data column is provided to the outer query statement for comparison operations. The following two sentences are for each student's language score and all the results of the 2 classes:

Query the student's language score: Select Sid,grade from Tb_score where CID in (select ID from tb_course where name= ' language ');

650) this.width=650; "Src=" http://images2015.cnblogs.com/blog/786913/201702/786913-20170204171958917-642859880. PNG "style=" margin:0px auto;padding:0px;border:0px; "/>

Check all scores for Class 2 students: Select Sid,cid,grade from Tb_score where SID in (SELECT ID from tb_student where classid=2);

650) this.width=650; "Src=" http://images2015.cnblogs.com/blog/786913/201702/786913-20170204172622151-1808324286. PNG "style=" margin:0px auto;padding:0px;border:0px; "/>

Merging queries

Using the Union keyword, you can give multiple SELECT statements and combine their results into a single result set. The two tables must have the same number of columns and data types when merging. Each SELECT statement is delimited by using the Union or UNION ALL keyword. Union does not use the keyword all to delete duplicate records, all return rows are unique, and the use of the keyword all does not delete duplicate rows or automatically sort the results.

If you are searching for 1th courses and are more than 80 points and 4th students: Select Sid,grade from Tb_score WHERE grade>80 and cid=1 UNION SELECT sid,grade from Tb_score wher E sid=4; Select Sid,grade from Tb_score where grade>80 and cid=1 UNION all SELECT sid,grade from Tb_score where sid=4;

650) this.width=650; "Src=" http://images2015.cnblogs.com/blog/786913/201702/786913-20170207210944322-2017394359. PNG "style=" margin:0px auto;padding:0px;border:0px; "/>

650) this.width=650; "Src=" http://images2015.cnblogs.com/blog/786913/201702/786913-20170207211023213-909196573. PNG "style=" margin:0px auto;padding:0px;border:0px; "/>

Regular expressions

Regular expressions are often used to retrieve or replace text content that conforms to a pattern, matching a particular string in the text according to the specified matching pattern. For example, extracting a phone number from a text file, looking for some sensitive words that repeatedly fade out or replace user input in an article, and so on, can use regular expressions. MySQL uses the RegExp keyword to specify a string-matching pattern for regular expressions, and a The following table lists the common matching lists in the regexp operator.

Options	Description	Example	Matching value instances
^	start character of matching text	' ^b ' Matches a string beginning with the letter B	Book,big,banana,bike
$	The ending character of the matching text	' st$ ' Match the string ending with St
.	matches any single character	' b.t ' Matches any one character between B and T
*	match 0 or more in front of it Character	"f* n ' match character n preceded by any character F	fn,fan,faan,fit
+	match the preceding character 1 or more	' ba+ ' The match begins with b followed by at least one a	ba,bay,bare,battle
< string;		' ha '	happy, Hacker,hackman
[Character Set]	Match any one of the characters in the character set		dizzy, Zebra,x-ray,extra
[^]	match any that is not in parentheses Character	"[^ ABC] ' matches any string that does not contain a, B, and C	desk,fox,file
string {n,}	match the preceding string at least n times	b{2} Match two or more B
string {n,m}	Matches the preceding string at least n times, up to M times. If n=0, this parameter is an optional parameter	b{2,4} matches a minimum of 2, up to 4 b	bb,bbb,bbbb

Select the line with the first letter Z in the information column in the Student table: SELECT * from tb_student WHERE information REGEXP ' ^z ';

650) this.width=650; "Src=" http://images2015.cnblogs.com/blog/786913/201702/786913-20170208104459760-1413231370. PNG "style=" margin:0px auto;padding:0px;border:0px; "/>

Other regular Expressions For example, we can design according to their own needs.

MySQL Database connection query