MySQL Database Basics (v)--sql query

MySQL Database Basics (v)--sql query one, single-table query 1, query all fields

Use the asterisk "" wildcard character in a SELECT statement toquery all fields
Specify all fields in the SELECT statement
Select from TStudent ;

2. Query the specified field

Querying multiple fields
Select Sname,sex,email from Tstudent;

3. Query the specified record

The data is filtered by the WHERE clause in the SELECT statement, in the syntax format:
SELECT Field name 1, field name 2,..., field name n from table name where query condition
Select Sname,sex,email,class from tstudent where class= ' Java ';

4. Query with in keyword

The query satisfies the records in the specified range, uses the in operator, encloses all the search conditions in parentheses, and the search conditions are separated by commas, as long as a value within the criteria range is met.
Inquiry Wang Shi Shahe's students
SELECT * from Tstudent where left (sname,1) in (' King ', ' Liu ', ' Stone ');

5. Range query with between and

Queries a range of values that require two parameters, the start and end values of the range, and if the field values meet the specified range query criteria, the records are returned.
The following query criteria for students with numbers 100 to 150, including 100 and 150
Select from tstudent where convert (studentid,signed) between and 150
Equivalent to
Select from tstudent where convert (studentid,signed) >=100
and CONVERT (studentid,signed) <=150
Automatic conversion type
SELECT * from Tstudent where studentid between and 150

6. Character matching query with like

Percent semicolon wildcard '% ', matching characters of any length, even including 0 characters
Underline wildcard ' _ ', can only match any one character at a time
Find the student whose middle word is "zhi"
Select from tstudent where sname like ' log ';
Find students with the word "Chi" in their name
Select from tstudent where sname like '% log% ';

7. Query null value

Using the IS NULL clause in a SELECT statement allows you to query for a blank record of a field's contents.
Find a record with a null value for a mailbox
SELECT * from S where e-mail is null;

8. Multi-condition query with and

Connect two or more query conditions using and, and separate the multiple conditional expressions with an and.
select * from TStudent where sex=‘男‘ and Class=‘net‘ and studentid>20 and studentid<50;

9. Multi-condition query with OR

The OR operator, which means that only records that meet one of the criteria can be returned. Or you can connect two or more query conditions, and separate multiple conditional expressions with and.
SELECT * from tstudent where sname like '% log% ' or class= ' net ';

10, the query results are not duplicated

In the SELECT statement, you can use the Distince keyword to indicate that MySQL eliminates duplicate record values.
SELECT DISTINCT field name from table name;
There are several classes in a query
Select DISTINCT class from Tstudent;

11. Limit the number of query results with limit

The Limit keyword can return a record at the specified location.
LIMIT [position offset,] number of rows
Return to top 10 students
Select from tstudent limit 10;
Returning the 第11-20个 student, the offset is 10, which means to fetch 10 records starting from the 11th.
Select from Tstudent limit 10, 10;

12. Merge Query Results

Using the Union keyword, you can give multiple SELECT statements and combine their results into a single result set. When merging, two tables must correspond to the same number of columns and data types. Each SELECT statement is delimited by using the Union or UNION ALL keyword.
The column returned by the first SQL statement is required to return the same number of columns as the second one.
Select Studentid,sname from Tstudent where studentid<=10
Union
Select StudentID, sname from tstudent where sname like ' King% ';

13. Alias tables and fields

Alias a field
MySQL can specify a column alias, replace a field or an expression.
Column name [as] column alias
Select StudentID as study number, sname as name, sex as gender from tstudent
Select StudentID number, sname name, sex gender from tstudent
Alias a table
For ease of operation or when you need to use the same table multiple times, you can specify an alias for the table, representing the original name with an alias.
Table name [as] table alias
Select A.studentid number, a.sname name, A.sex sex from tstudent as A;
Select A.studentid number, a.sname name, A.sex sex from tstudent A;

Second, multi-table connection query 1, internal connection query

Inner joins (INNER join) Use comparison operators to match rows in two tables based on the values of the columns that are common to each table, and to list the data rows in the table that match the join criteria into a new record. In an inner join query, only records that meet the criteria can appear in the result relationship.
Statement 3: Implicit inner join, without inner join, forms a Cartesian product of two tables.
Select A.studentid, A.sname, B.mark from Tstudent A, Tscore b where a.studentid=b.studentid;
Statement 4: The displayed inner joins, commonly called Inner joins, have inner joins, and the resulting intermediate table is a Cartesian product of two tables after the on condition is filtered.
Select A.studentid, A.sname, B.mark from tstudent a inner jointscore b on A.studentid=b.studentid;
Select A.studentid,a.sname,c.subjectname,b.mark from Tstudent a join Tscore b on A.studentid=b.studentid join TSubject c o n B.subjectid=c.subjectid;

If a column is in only one table, you do not have to indicate which table is the column.
Select A.studentid,a.sname,subjectname,mark from Tstudent a joins Tscore B on A.studentid=b.studentid joins Tsubject C on B. Subjectid=c.subjectid;

2, External connection query

The outer joins are left-connected, right-connected, and fully connected.
An outer join returns to the query results collection not only rows that meet the join criteria, but also all data rows in the left table (left OUTER join or left join), right table (right outer join or RIGHT join), or two edge table (full outer join).
The outer join returns not only the data rows that meet the connection and query criteria, but also some rows that do not meet the criteria. The outer joins are divided into three categories: Left OUTER join (OUTER join), right outer join (OUTER join), and full outer join (fully OUTER join).
What all three have in common is that they return rows of data that meet the join criteria and query criteria (that is, inner joins). The different points are as follows:
The left OUTER join also returns data rows in the left table that do not meet the criteria for a join condition.
The right outer join also returns the data rows in the right table that do not meet the criteria for the join criteria.
The full outer join also returns rows of data in the left table that do not meet the criteria for the join criteria, and also returns rows in the right table that do not meet the criteria for a join condition. The full outer join is actually a mathematical collection of upper left outer joins and right outer joins (removing duplicates), i.e. "all outside = left outer UNION right outside".
Left connection
Contains all rows from the table on the left (regardless of whether there are rows in the right table that match them), and all rows in the right table.
The result set of the left connection is included? All rows of 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 set row are null values.
Select A.studentid, A.sname, B.mark from Tstudent a left joins Tscore B on A.studentid=b.studentid;

Right connection:
The right join contains all the rows from the right table, regardless of whether there are rows in the left table that match them, and all rows in the left table.
The right connection is the reverse connection of the left connection. All rows of the right table will be returned. If a row in the right table does not have a matching row in the left table, a null value will be returned for left table.????
Select A.studentid, A.sname, b.mark from Tscore b right joins tstudent a on A.studentid=b.studentid;

Full connection:
Full joins return all rows in the left and right tables. When a row does not have a matching row in another table, the selection list column for the other table contains a null value. If there are matching rows between the tables, the entire result set row contains the data values of the base table. MySQL does not support full-outer connections. You can find the results of a full-outer join by finding the collection by left and right outside.
Select A.studentid, A.sname, B.mark from Tstudent a LEFT join
Tscore B on A.studentid=b.studentid
Union
Select B.studentid, C.subjectname, b.mark from Tscore B right Join
Tsubject c on B.subjectid=c.subjectid;

3. Cross-Connect

The cross join returns all the rows in the left table, and each row in the left table is combined with all the rows in the right table. Cross joins are explicit and implicit, without an ON clause, and return a product of two tables, also called a Cartesian product.
The table or view in the FROM clause can be specified in any order by an inner join or full join, but the order of the table or view is important when you specify a table or view with a left or right outward connection.
Implicit cross-joins, no crosses join
Select A.studentid, A.sname, b.mark from tstudent A,tscore b where A.studentid < 4;
Show cross joins, with crosses join
Select A.studentid, A.sname, B.mark from Tstudent a cross join Tscore b where A.studentid < 4;

4, the principle of SQL query

First, single-table query: Filter the records in a table based on where conditions, form an intermediate table, and then select the appropriate column to return the final result based on the Select column of the SELECT.
Second, two-table connection query: The two-table quadrature (Cartesian product) with on conditions and connection type filtering to form an intermediate table; then the records of the intermediate table are filtered based on the Where condition and the query results are returned based on the column specified by select. Examples are as follows:
Select A.studentid, A.sname, B.mark from Tstudent a left joins Tscore B on A.studentid=b.studentid where A.studentid < 1 0;
Third, multi-table connection query: First and second table according to two table connection query, and then use the query results and the third table to make a connection query, and so on, until all the tables are connected, and eventually form an intermediate result table, and then filter the records of the intermediate table according to where conditions, and returns the result of the query based on the column specified by select.

5. Filter conditions

On condition: Filter Two connection table the Cartesian product forms the constraint condition of the intermediate table.
Where Condition: In a SELECT statement with an on condition, the constraint that filters the intermediate table. In a single-table query that does not have on, it is a constraint that restricts the return of records to physical tables or intermediate query results. In a two-table or multiple-table connection, a constraint that restricts the return result of the connection to form the final intermediate table.
It is inappropriate to move the where condition into on. The recommended practice is on only the connection operation, where only the records of the intermediate table are filtered.

6, the application of connection query scenario

The connection query is the core of the SQL query, and the connection type of the connection query is selected according to actual requirements. If you choose improperly, not only can not improve query efficiency, but will bring some logic errors or poor performance. The basis of two-table connection query selection method:
A, check two tables related columns equal data with the internal connection.
B, col_l is a subset of Col_r with right connection.
C, Col_r is a subset of col_l with a left connection.
E, Col_r, and col_l intersect each other but are not subsets of each other with full connections.
F, the differential operation when using a joint query.

Third, sorting the results of the query

In MySQL, you can sort the results of a query by using the ORDER BY clause in SELECT.

1, single-column sorting

ASC represents the results in the order of small to large, and? DESC. The results are listed in the order of the large and small. Default ascending ASC sort.
Select from TStudent order by birthday ASC;
Select from TStudent order by birthday desc;

2, multi-column sorting

You can specify the sort direction separately.
Select A.studentid,a.sname,subjectname,mark from Tstudent a joins Tscore B on A.studentid=b.studentid joins Tsubject C on B. Subjectid=c.subjectid where C. subJectID = ' 0001 ' ORDER by Mark Desc,a.studentid Desc;

Four, group query 1, group query Introduction

A group query is a grouping of data by one or more fields.
Grouping query formats
SELECT column
From table
[WHERE condition]
[GROUP by Group_by_expression]
[Having group_condition]//Filter condition is aggregate function, use having
[ORDER by column];
Aggregate functions can only appear in the select list, the HAVING clause, and the ORDER BY clause, and cannot appear in the WHERE clause. If you want to limit the grouping results, you can only use the HAVING clause.
When using the group by keyword, items that can be specified in the select list are limited, only the columns that are grouped are allowed in the SELECT statement, or an expression that returns a value for each grouping, such as an aggregate function that uses a column name as a parameter.
WHERE clause: Remove data from the data source that does not match the search criteria;
GROUP BY clauses: grouping, using statistical functions (aggregate functions) to calculate statistical values for each group;
HAVING clause: Remove the non-qualifying rows of data from each group in the divided group.

2. Using aggregate function query

COUNT () function
Select Class,count (*) from the TStudent group by class;
SUM () function
Query each student's total score
Select Concat (A.studentid, ", A.sname) Ss,sum (B.mark) from TStudent a joins Tscore B on a. StudentID =b. StudentID GROUP by SS;
AVG () function
Statistics of average classes per class
Select Class,avg (Mark) from TStudent a joins Tscore B on a. StudentID =b. StudentID Group by class;

3. Multi-field grouping

Statistics per class per branch average, need to be grouped by two columns class and Subjectname
Select Class,subjectname,avg (Mark) from Tstudent a joins Tscore B on A.studentid=b.studentid joins Tsubject C on B.subjectid =c.subjectid GROUP BY Class,subjectname;

4. Use having filter group

Query for students with a draw score greater than 80
Select Concat (A.studentid, ", A.sname) Ss,avg (B.mark) m from TStudent a joins Tscore B on a. StudentID =b. StudentID GROUP BY SS have m>80;

5. Use of GROUP by and order by

Find average points greater than 80 points, sorted by average.
Select Concat (A.studentid, ", A.sname) Ss,avg (B.mark) m from TStudent a joins Tscore B on a. StudentID =b. StudentID GROUP BY SS have m>80 order by M;

6. Use with ROLLUP in the GROUP BY clause

With the GROUP BY rollup clause, you can retrieve more group aggregation information, not only the aggregated information of each group, but also the aggregate information of the group.
Select Class,subjectname,avg (Mark) from Tstudent a joins Tscore B on A.studentid=b.studentid joins Tsubject C on B.subjectid =c.subjectid GROUP by Class,subjectname with rollup;
Can count the average grade per class, the average grade of each class can also be counted, all classes of the average grade can also be counted.

Sub-query 1, sub-query with in keyword

When the In keyword is queried, the inner query statement returns only one data column, and the values in the data column are provided to the outer query statement for comparison operations.
SELECT * from Tstudent where StudentID in (select distinct StudentID from Tscore where mark>98);

2. Sub-query with exists keyword

The argument after the EXISTS keyword is an arbitrary subquery that the system queries to determine whether the subquery 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.
Select from tstudent where studentid= ' 01001 ' and exists (select from Tscore where studentid= ' 01001 ');

3. Sub-query with any, some keyword

The any and some keywords are synonyms that satisfy any of these conditions, allow the creation of an expression to be compared to the list of return values of a subquery, and, as long as any one of the comparison criteria in the inner subquery is satisfied, returns a result as a condition for the outer query.
Select from tstudent where Studentid=any (select distinct StudentID from Tscore where mark>98)
Equivalent to
Select from Tstudent where Studentid=some (select distinct StudentID from Tscore where mark>98);
Equivalent to
Select from tstudent where StudentID in (select distinct StudentID from Tscore where mark>98);
Subqueries can also use other comparison operators, such as <, <=, =, >=, and! =, and so on.
The following SQL sentence query to find out the test results of more than 98 of the students StudentID, such as the results found there are three ' 00010 ', ' 00021 ', ' 00061 ', outside the query will be more than 00010 study number of students.
Select from Tstudent where Studentid>some (select distinct StudentID from Tscore where mark>98)
The following SQL sentence query to find out the test results of more than 98 of the students StudentID, such as the results found there are three ' 00010 ', ' 00021 ', ' 00061 ', outside the query will be more than 00061 study number of students.
select * from TStudent where studentid<some (select distinct studentid from TScore where mark>98);

4. Sub-query with all keyword

The all keyword differs from any and some, and all of the internal query conditions need to be met at the same time using all.
The following SQL sentence query to find out the test results of more than 98 of the students StudentID, such as the results found there are three ' 00010 ', ' 00021 ', ' 00061 ', outside the query will be more than 00010 study number of students.
select * from TStudent where studentid<all (select distinct studentid from TScore where mark>98)
The following SQL sentence query to find out the test results of more than 98 of the students StudentID, such as the results found there are three ' 00010 ', ' 00021 ', ' 00061 ', outside the query will be more than 00061 study number of students.
SELECT * from Tstudent where Studentid>all (select distinct StudentID from Tscore where mark>98);

Vi. using Regular expression queries

The function of a regular expression is to match text, comparing a pattern (regular expression) with a text string. MySQL provides preliminary support for regular expressions with a WHERE clause that allows you to specify that the data retrieved by select is filtered with a regular expression.
In a SQL query statement, the query condition is followed by regexp as a regular expression.

1. Query for records that begin with a specific character or string

The character ' ^ ' matches text that begins with a specific character or string.
SELECT * from tstudent where sname regexp ' ^ Liu Ping ';

2. Query for records ending with a specific character or string

The character ' $ ' matches text that ends with a specific character or string.
SELECT * from tstudent where cardid regexp ' 36$ ';

3, with the symbol "." To replace any one of the characters in the string

Character '. ' matches any one character.
SELECT * from tstudent where sname regexp '.

4. Use "*" and "+" to match multiple characters

The asterisk "matches the preceding character any number of times, including 0 times.
The plus ' + ' matches the preceding character at least once.
Find out xxx with 19 start, end with 6 students
Select from tstudent where Cardid regexp ' ^19. 6$ '
Find out that there are 123 students in the XXX number.
Select from tstudent where Cardid regexp '. 123+.';

5. Match the specified string

A regular expression can match a specified string, as long as the matching string is in the query text, such as to match multiple strings, using the delimiter between multiple strings ' | ' Separated.
SELECT * from tstudent where sname regexp ' Wu Yin | luo ';

6. Match any one of the specified characters

Square brackets "[]" specifies a character set that matches only one of these characters, which is the text you are looking for. Chinese characters are not supported.
Select from tstudent where email regexp ' [w-z] ';
Select from tstudent where Cardid regexp ' ^[1-3,7] ';

7. Match characters other than the specified character

"[^ charset]" matches any character that is not in the specified collection.
SELECT * from tstudent where Cardid regexp ' ^[^1-7] ';

8. Use {M} or {m,n} to specify the number of consecutive occurrences of a string

"String {N,}" indicates that at least n times the preceding character is matched. "String {n,m}" means that the preceding string matches not less than n times, not more than m times.
Look for students who appear in XXX in 138 and have 8 digit 0-9 digits behind.
SELECT * from tstudent where Cardid regexp ' 138[0-9]{15} ';

MySQL Database Basics (v)--sql query