MySQL Tutorial Chapter 4th data Query

MySQL operator

These operators are used in SQL queries.

Arithmetic operators

+ Plus

-Minus

* Multiply

/DIV except

% MOD Take remainder

Comparison operators

= equals

<> = does not equal

<

<=

>

>=

Between specify range

In exists in the collection

Is null NULL

Is isn't null not empty

Like wildcard characters

Regexp rlike Regular Expression

Like what

Comparison operator, the returned result is really 1, false is 0

Operator Precedence

Highest------------------------------------------à lowest

！ () */div% mod-+ = <>= like between if then else not and OR

Single-Table Query

Query all columns for all rows

SELECT * FROM Tstudent

Querying a specified column

Select Studentid,sname,sex,cardid from Tstudent

Assigning Aliases to Columns

Select StudentID number, Sname name, sex sex, CardID ID number from tstudent

Specify query criteria

You can use the < > <= >=! = comparison operator in a query condition

1. Inquiry Online class students

Select StudentID number, Sname name, sex sex, CardID ID number, class class

From Tstudent where class= ' network and web development '

2. Query network class sex is a female student

Select StudentID number, Sname name, sex sex, CardID ID number, class class

From Tstudent where class= ' network and website development ' and sex= ' women '

3. Find a software test class or sex is a female student

Select StudentID number, Sname name, sex sex, CardID ID number, class class from tstudent where class= ' software test ' or sex= ' women '

4. Using a like fuzzy query

Use the character comparer like

% of 0 or more strings

_ Any single character

[] Any single character within the specified range or collection

[^] Any single character not within the specified range or collection

Find students whose names contain the word "Li"

Select StudentID number, Sname name, sex sex, CardID ID number, class class from tstudent where Sname like '% Mau '

Think: Find the last name of the classmate "Mao", write the SQL statement.

5. Find the student whose name is "Han Li" and "cloning"

Make a note of the two names of the above query results. Find a record of these two names.

Select StudentID number, Sname name, sex sex, CardID ID number, class class from Tstudent where Sname in (' Zhao Junmao ', ' Wei Qingmao ')

6. Find students born from 1975 to 1980

Using comparison operators = > < >= <= <>

Select StudentID number, Sname name, sex sex, CardID ID number, class class, Birthday birthday

From Tstudent where Birthday> ' 1980 ' and birthday< ' 1985 '

7. Using relational operators

The relational operator precedence is not and or, as in the following example, the parentheses result

Select StudentID number, Sname name, sex sex, CardID ID number, class class, Birthday birthday

From tstudent where birthday> ' 19820101 ' and birthday< ' 19841230 ' or sex= ' women '

Order of operations by using parentheses

Select StudentID number, Sname name, sex sex, CardID ID number, class class, Birthday birthday

From tstudent where birthday> ' 19820101 ' and (birthday< ' 19841230 ' or sex= ' women ')

8. Query for values within a certain range

Find a birthday student between 1985 and 1990

Select StudentID number, Sname name, sex sex, CardID ID number, class class, Birthday birthday

From Tstudent where Birthday between ' 19850000 ' and ' 19860000 '

Find students not born from 1985 to 1986

Select StudentID number, Sname name, sex sex, CardID ID number, class class, Birthday birthday

From Tstudent where Birthday not between ' 19850000 ' and ' 19860000 '

Try to use between instead of expressions represented by and and comparison operators

If you want to return rows that are not in the specified range, use not between. This reduces the speed of the data query.

9. Querying for null values

Insert into tstudent (studentid,sname) VALUES (' 19999 ', ' Zhang Yongxu ')

SELECT * from tstudent where studentid= ' 19999 '

You can see that no inserted column value is null

SELECT * from Tstudent where CardID is NULL

You're not going to write that.

SELECT * from tstudent where cardid= '

Querying non-empty rows in a specified column using is not NULL

SELECT * from tstudent where CardID are not NULL

Formatting result Sets

1. Sorting

Default is descending desc ascending asc Descending

SELECT * from Tstudent ORDER by CardID

SELECT * from Tstudent ORDER by CardID ASC

Sort by two columns first by class and then by number of students

SELECT * from Tstudent ORDER BY Class,studentid Desc

The following commands are sorted by column 7th and column 1th, and the output is the same as above

SELECT * from Tstudent ORDER BY 7,1 Desc

2. Eliminate duplicate rows

The following commands see how many classes

Select DISTINCT class from Tstudent

Multi-Table Query

Insert two pupils in the Tstudent table

INSERT into tstudent values (' 90006 ', ' Zhang Yong ', ' Male ', ' 132302198903044565 ', ' 19880203 ', ' [email protected] ', ' JAVA ', ' 20120803 ' );

INSERT into tstudent values (' 90007 ', ' by ', ' female ', ' 132302198905044565 ', ' 19880503 ', ' [email protected] ', ' JAVA ', ' 20120803 ' )

None of the two students have scored.

3. Join multiple tables using joins

Internal connection using the Inner keyword can be omitted.

Students with no grades are not listed.

The use of foreign key matching can be said to form a large table of multiple tables, you can see the number of large table records 4 students

Select a.*,b.*,c.* fractions from ' tstudent ' a inner joins ' Tscore ' B on a. ' StudentID ' =b. ' StudentID ' inner joins ' Tsubject ' C on B. ' Subjectid ' =c. ' Subjectid '

A large table from the above three tables finds a record of computer network courses with scores greater than 80 points

Select sname name, sex Gender, C. ' Subjectname ' subject, B. ' Mark ' score from ' tstudent ' a join ' Tscore ' B on a. ' StudentID ' =b. ' StudentID ' Joi n ' Tsubject ' C on B. ' Subjectid ' =c. ' Subjectid ' where c. ' Subjectname ' = ' computer network ' and B. ' Mark ' >80

You can see the output from three tables

4. Left connection

Make a connection using left has a connection using right

Select a.*,b.* from ' tstudent ' a ieft join ' Tscore ' B on a. ' StudentID ' =b. ' StudentID ' use the left connection to see no grades students also appear in the table

Thinking: Left Connection

5. Self-connect

Connect to yourself, such as finding students with duplicate names

Select A.studentid,a.sname,b.studentid,b.sname from Tstudent a join tstudent B on A.sname=b.sname where A.STUDENTID<&G T;b.studentid

6. Using sub-queries

With subqueries, the output can only come from one table and other tables as criteria for the query.

Why using subqueries---subqueries can break down a complex query into a series of logical steps so that complex query problems can be solved with a single statement.

Why using a connection without using a subquery---execution is similar, a subquery might require that the query optimizer perform additional operations, such as sorting, which will affect the processing strategy of the query.

Select Sname name from ' Tstudent ' where StudentID in (select StudentID from ' Tscore ' where mark>90)

The following SQL sentence query nested sub-query, query computer network, the output can only come from a table.

Select Sname name from the ' Tstudent ' where StudentID in (select StudentID from ' Tscore ' where mark>90 and Subjectid in (Sele CT subjectid from ' tsubject ' where subjectname= ' computer network '))

7. Use the ANY keyword

Create two tables

CREATE TABLE tb1 (num int not NULL);

CREATE TABLE TB2 (num int not NULL);

Inserting data

INSERT INTO TB1 VALUES (2), (5), (13), (25), (32);

INSERT INTO TB2 VALUES (6), (8), (20), (43), (70), (4);

Find TB1, provided that any value greater than TB2 can be

Select num from tb1 where num>any (select num from TB2)

8. Use the keyword All

Returns a value that TB2 all values are small

Select num from tb1 where num<all (select num from TB2)

9. Use the EXIST keyword

Returns the first record in the Tstudent table if the scores in the score table have records that are greater than 80 minutes

Of course, you can also use not exist

SELECT * from ' tstudent ' where EXISTS (select * from ' Tscore ' where mark>80) limit 1

Students with more than 100 points are found in the student records.

SELECT * from ' tstudent ' where EXISTS (select * from ' Tscore ' where mark>100) limit 1

10. Combine query results with Union

Finding data for TB1 and TB2 greater than 40

Select num from TB1 where num>40 Union select num from TB2 where num>40

Data grouping and summarization

A. Group by data Summary

If you use the aggregate function, a field of all records in the table is summarized and a single value is generated. If you want to generate multiple summary values, using both the aggregate function and the GROUP BY statement, you can combine the having and GROUP by clauses to make the result set contain only records that meet the criteria.

Calculate the average of "computer network" in each class

Select A.class class, Avg (b. ' Mark ') computer network average from ' tstudent ' a joins ' Tscore ' B on a. ' StudentID ' =b. ' StudentID ' join ' tsubject ' C on B. ' Subjectid ' =c. ' Subjectid ' where c. ' Subjectname ' = ' computer network ' GROUP by A. ' Class '

Group by must be used in conjunction with the aggregate function

12. Using the GROUP BY clause together with the HAVING clause

Having equal conditions

Find a class with an average of more than 80 points for computer network courses

Select A.class class, Avg (b. ' Mark ') computer network average from ' tstudent ' a joins ' Tscore ' B on a. ' StudentID ' =b. ' StudentID ' join ' tsubject ' C on B. ' Subjectid ' =c. ' Subjectid ' where c. ' Subjectname ' = ' computer network ' GROUP by A. ' Class ' has AVG (b. ' Mark ') >75

13. Study questions: To count the total scores of all subjects in each class.

14. Query the specified number of rows

Use limit to display the first 2 rows of data

Select Studentid,sname,sex,cardid from ' tstudent ' limit 2

Show 3 records from the 2nd record

Select Studentid,sname,sex,cardid from ' tstudent ' limit 2,3

15. Using aggregation functions to count rows

The Count function does not count NULL records

Select COUNT (*) from ' tstudent ';

Select COUNT (cardID) registered the number of students of the identity card from ' tstudent ', without registering the identity card without statistics.

16. Thinking: Statistics on the number of boys

Using regular expressions

The following reserved words can be used in a regular expression

Symbol	Description
^	The matched string begins with a string that follows
$	The matched string ends with the preceding string
.	Matches any character, including new lines
A *	Match any number of Aces (including empty strings)
A +	Match any number of a (not including empty strings)
A?	Match one or 0 a
De|abc	Match de or ABC
(ABC) *	Match any number of ABC (including Empty strings)
[A-DX]	Matches "A", "B", "C", "D" or "X"
[^A-DX]	Matches any character except "A", "B", "C", "D", "X". "[", "]" must be used in pairs

17. Querying for a specific character or a record at the beginning of a string

Select Sname,email from ' tstudent ' where email REGEXP ' ^ky '

18. Querying for records that end with a specific character or string

Select Sname,email from ' tstudent ' where Sname REGEXP ' Fu $ '

19. Use * and + to match multiple characters in a string

* match the preceding character any number of times, including 0 times, + match the preceding character at least once.

Select Sname,email from ' tstudent ' where email REGEXP ' ^tb* '

Select Sname,email from ' tstudent ' where email REGEXP ' ^tb+ '

20. Match the specified string

Select Sname,email from ' tstudent ' where email REGEXP ' BZ '

21. Match any one of the strings

Select Sname,email from ' tstudent ' where emai

MySQL Tutorial Chapter 4th data Query