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

Mysql database connection query

Connection is the main feature of relational database model. Join query is the most important query in relational database, including inner join, outer join and so on. Multiple table queries can be realized through join operation. When querying data, the join operation is used to query the information of different entities stored in multiple tables. When there are fields with the same meaning in two or more tables, you can use these fields to make join queries on different tables. The inner join query, outer join query and compound conditional join query between multiple tables are described below.

Internal join query

Inner join (inner join) uses the comparison operator to compare column data between tables, lists the data rows in these tables that match the join conditions, and combines them into new records. Create a new student table and class table, and realize the join query between the two tables. The primary key id in tb_class is the foreign key of tb_student, and the table structure is as follows:

The data in the table is as follows:

The join 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 JOIN tb_class ON tb_student.classID=tb_class.id

If in a join query, the two tables involved are the same table, this query is called self-join query. Self-join is a special inner join, which means that the joined tables are physically the same table, but can be logically divided into two tables. The following is the query for the tb_ student table. The query and the student with id 1 are students in the same class:

External join query

The join query queries the associated rows in multiple tables, and when joined within, only the rows in the query result set are returned that meet the query and join conditions. However, sometimes you need to include data in rows that are not associated, that is, the returned query result set contains not only rows that meet the join criteria, but also all data rows in the left table (left join), right table (right join), or two side tables. The outer connection is divided into left outer connection and right outer connection.

LEFT JOIN (left join): returns records that include all records in the left table and records with equal join fields in the right table

RIGHT JOIN (right join): returns records that include all records in the right table and equal join fields in the left table

LEFT JOIN left connection

The result of a left join includes all rows of the left table specified in the LEFT OUTER clause, not just the rows matched by the join column. If a row of the left table does not match in the right table, all select list columns of the right table in the associated result row are null. The following is the student table and class table, which looks up the names of all students' classes, including those who have not yet been assigned classes.

The left connection statement is: SELECT tb_student.name,tb_class.name FROM tb_student LEFT OUTER JOIN tb_class ON tb_student.classID=tb_class.id

RIGHT JOIN right connection

The right join is the reverse connection of the left connection, which will return all the rows of the right table. If a row of the right table does not have a match in the left table, the left table will return a null value. As shown in the following figure, there is no computer Class 5 student in the tb_student table, so the student name before Class 5 computer is empty.

Subquery

A subquery is a query in which a query statement is nested within another query statement. The subquery is first calculated in the SELECT clause, and the result of the subquery is used as the filter condition of another query. The query can be based on one or more tables. The operators commonly used in subqueries are ANY (SOME), ALL, IN, and EXISTS. Subqueries can be added to SELECT, UPDATE, and DELETE statements and can be nested at multiple levels. Comparison operators, such as "=" and "! =", can also be used in subqueries. This section describes how to nest subqueries in SELECT statements.

Subquery with ANY and SOME keywords

The ANY and SOME keywords are synonyms that either of these conditions are met, and they allow you to create an expression to compare the list of return values of a subquery and return a result as a condition for an outer query as long as any comparison condition in the inner subquery is met. The ANY keyword is followed by a comparison operator, indicating that TRUE is returned if compared to any value returned by the subquery as TRUE. The following is a list of all the scores that are higher than the minimum score of student number 1 (86):

The SQL query statement is: SELECT * FROM tb_score WHERE grade > ANY (SELECT grade FROM tb_score WHERE sID=1)

Subquery with ALL keyword

The keyword ALL is different from ANY and SOME. When you use ALL, you need to meet the conditions of the inner query at the same time. For example, if you change ANY to ALL in the above example, the result of the query is higher than that of the student with student number 1 (86 and 90), and the query result is empty.

Subquery with EXISTS keyword

The parameter after the EXISTS keyword is an arbitrary subquery, and the system operates on the subquery to determine whether it returns rows. If at least one row is returned, the result of EXISTS is TRUE, and the outer query statement will query; if the subquery does not return any rows, the result returned by EXISTS is FALSE, and the outer statement will not query.

Subquery with IN keyword

When the IN keyword runs a subquery, the inner query statement returns only one data column, and the values in this data column are provided to the outer query statement for comparison operations. The following two sentences query the students' Chinese scores and all the scores of the students in Class 2 respectively:

Inquire about students' language scores: SELECT sID,grade FROM tb_score WHERE cID IN (SELECT id FROM tb_course WHERE name=' Chinese')

Check all the scores of the students in Class 2: SELECT sID,cID,grade FROM tb_score WHERE sID IN (SELECT id FROM tb_student WHERE classID=2)

Merge query

Using the UNION keyword, you can give multiple SELECT statements and combine their results into a single result set. The number of columns and data types corresponding to the two tables must be the same when merging. Individual SELECT statements are separated by the UNION or UNION ALL keyword. When UNION executes without the keyword ALL, duplicate records are deleted and all returned rows are unique; the purpose of using the keyword ALL is not to delete duplicate rows or automatically sort the results.

If you check the scores of course 1 and more than 80 and student 4: SELECT sID,grade FROM tb_score WHERE grade > 80 AND cID=1 UNION SELECT sID,grade FROM tb_score WHERE 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

Regular expression

Regular expressions are usually used to retrieve or replace text content that matches a pattern, and matches special strings in the text that meet the requirements according to the specified matching pattern. Regular expressions can be used in places such as extracting a phone number from a text file, finding an article that repeatedly fades out or replacing some sensitive words entered by the user, and so on. The REGEXP keyword is used in MySQL to specify the string matching pattern for regular expressions, and a lists the common matches in the REGEXP operator as shown in the following table.

Option shows that the example match value instance ^ matches the start character of the text'^ b 'matches the string that begins with the letter b book,big,banana,bike$ matches the end character of the text' st$' matches the string test,resist,rest,persist that ends in st. Match any single character 'b.t' match any character between b and t one character bit,bat,but,bite* matches zero or more characters in front of it' fancin' matching character n is preceded by any character ffn,fan,faan,fit+ match preceding character 1 or more 'ba+' match begins with b followed by at least one aba,bay,bare Battle matches the text 'ha'happy,hacker,hackman [character set] that contains the specified string matches any character in the character set' [xz] 'matches x or zdizzy,zebra,x-ray,extra [^] matches any character' [^ abc] 'that is not in parentheses, matches any string desk,fox that does not contain a, b, and c The file string {n,} matches the previous string at least n times b {2} matches two or more bbb,bbb,bbbb strings {n department m} matches the previous string at least n times, at most m times. If naught 0, this parameter is optional b {2 bbb,bbb,bbbb 4} matches a minimum of 2 and a maximum of 4

Select the row with the first letter z in the information column of the student table: SELECT * FROM tb_student WHERE information REGEXP'^ z'

Other examples of regular expressions can be designed according to your own needs.

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.