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

This article mainly introduces SELECT single table, multi-table query upgrade and insert and delete, the things involved, learned from the theoretical knowledge, there are many books, literature for your reference, in terms of practical significance, accumulated years of practical experience can be shared with you.

SELECT: query

SELECT select-list FROM tb WHERE qualification looks up the corresponding row according to the standard qualification

Query statement type: qualification conditional field domain distinct unique, no duplicates

Simple query:

Multi-table query:

Subquery:

SELECT * FROM tb_name: query all the information of the tb_name table

SELECT field1,field2 FROM tb_name: the domain entry (field) set by the projection display. A field is a column.

SELECT [DISTINCT] * FROM tb_name WHERE qualification; selects unique, non-repeating entries that meet the criteria from the tb_ name table.

FROM clause: tables, multiple tables, other SELECT statements

WHERE clause: Boolean relation expressions =, >, SELECT Name,Age,Gender FROM students WHERE NOT Age > 20 AND NOT Gender='M'; # choose to query data whose age is not greater than 20 and whose gender is not male, it can also be written as the following conditions

Mysql > SELECT Name,Age,Gender FROM students WHERE NOT (Age > 20 OR Gender='M')

Special relationship: BETWEEN. AND... Somewhere in between.

LIKE''

%: any length, any character

_: any single character

Usage: SELECT Name FROM students WHERE Name LIKE'Y% data; find the data whose Name field begins with Y.

The SELECT Name FROM students WHERE Name LIKE'% ing%'; lookup Name field must include the field of ing

SELECT Name FROM students WHERE Name LIKE'Y characters are followed by at least 3 characters in the Name field.

REGEXP or RLIKE supports regular expressions:

Usage:SELECT Name,Age FROM students WHERE Name RLIKE'^ [XY]. * $'

Find data whose Name field line begins with X or Y

Search for IN discrete conditions:

Usage: SELECT Name,Age FROM students WHERE Age IN (18, 20, 25)

Find relevant data whose Age field is 18 or 20 or 25 years old

When there is a null value, compare: IS NULL, NOT NULL ORDER BY. With. Sort a field in ascending order

ORDER BY... With. Sort a field in descending order

Usage: SELECT Name,Age FROM students WHERE Name IS NULL

Find data with null Name field

SELECT Name,Age FROM students WHERE Name IS NULL

Find data for which the Name field is not null

Sort the data to be found in ascending or descending order: ORDER BY field_name {ASC | DESC}

ASC ascending sort (default) DESC descending sort

SELECT Name,Age FROM students WHERE CID IS NOT NULL ORDER BY Name

Find data whose CID fields are not empty and sort them in ascending order of field Name

SELECT Name,Age FROM students WHERE CID IS NOT NULL ORDER BY Name DESC

Find data whose CID fields are not empty and sort them in descending order of field Name

Field alias: AS

Usage: SELECT Name AS Student_Name FROM student

Mysql > SELECT Name FROM student

+-+

| | Name |

+-+

| | Li Lianjie |

| | Cheng Long |

| | Yang Guo |

| | Guo Jing |

+-+

4 rows in set (0.00 sec)

Mysql > SELECT Name AS Student_Name FROM student

+-+

| | Student_Name | AS alias is Student_Name |

+-+

| | Li Lianjie |

| | Cheng Long |

| | Yang Guo |

| | Guo Jing |

+-+

4 rows in set (0.00 sec)

LIMIT clause: how much is the offset of LIMIT [offset,] Count offset and how much does Count display?

Usage: SELECT Name AS Student_Name FROM student LIMIT 3

Search shows that the Name alias is Student_Name, and only the first three data are displayed

Usage: SELECT Name AS Student_Name FROM student LIMIT 2,2

Find and display Name alias as Student_Name, offset the first 2 data and do not display, display 2 data after the second data

Mysql > SELECT Name AS Student_Name FROM student LIMIT 3

+-+

| | Student_Name |

+-+

| | Li Lianjie |

| | Cheng Long |

| | Yang Guo |

+-+

3 rows in set (0.00 sec)

Mysql > SELECT Name AS Student_Name FROM student LIMIT 2

+-+

| | Student_Name |

+-+

| | Yang Guo |

| | Guo Jing |

+-+

2 rows in set (0.00 sec)

Aggregate operation: SUM () summation, MIN () minimum, MAX () maximum, AVG () average, the number of the same values in the COUNT () statistical field

Create a new table:

Mysql > CREATE TABLE class (ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT,Name CHAR (20) NOT NULL UNSIGNED, Age TINYINT NOT NULL)

Mysql > INSERT INTO class (Name,Age) VALUES ('Yang Guo',22), (' Guo Jing',46), ('Xiao Longnv',18), (' Huang Rong',40)

Mysql > DESC class

+-+ +

| | Field | Type | Null | Key | Default | Extra | |

+-+ +

| | ID | int (11) | NO | PRI | NULL | auto_increment |

| | Name | char (20) | NO | | NULL |

| | Age | tinyint (4) | NO | | NULL |

+-+ +

3 rows in set (0.00 sec)

Mysql > SELECT * FROM class

+-- +

| | ID | Name | Age | |

+-- +

| | 1 | Yang Guo | 22 | |

| | 2 | Guo Jing | 46 | |

| | 3 | Xiao Longnv | 18 |

| | 4 | Huang Rong | 40 | |

+-- +

4 rows in set (0.00 sec)

Mysql > SELECT SUM (Age) FROM class

+-+

| | SUM (Age) |

+-+

| | 126 |

+-+

1 row in set (0.02 sec)

Mysql > SELECT MAX (Age) FROM class

+-+

| | MAX (Age) |

+-+

| | 46 |

+-+

1 row in set (0.00 sec)

Mysql > SELECT MIN (Age) FROM class

+-+

| | MIN (Age) |

+-+

| | 18 |

+-+

1 row in set (0.00 sec)

Mysql > SELECT AVG (Age) FROM class

+-+

| | AVG (Age) |

+-+

| | 31.5000 |

+-+

1 row in set (0.00 sec)

Grouping: GROUP BY... HAVING qualification is based on. Group and satisfy the condition qualification

The conditional selection of GROUP BY needs to be filtered by HAVING instead of WHERE.

Mysql > SELECT Age,Gender FROM class1 GROUP BY Gender; # grouped by gender

+-+ +

| | Age | Gender |

+-+ +

| | 18 | F |

| | 22 | M |

+-+ +

2 rows in set (0.00 sec)

Mysql > SELECT AVG (Age), Gender FROM class1 GROUP BY Gender; # asks for the average age of male and female students.

+-+ +

| | AVG (Age) | Gender | |

+-+ +

| | 29.0000 | F | |

| | 34.0000 | M |

+-+ +

2 rows in set (0.00 sec)

Mysql > SELECT Name,AVG (Age) FROM class1 GROUP BY Gender HAVING Name RLIKE 'Y.girls'

# find the age flat value of the Gender group, and only display the average and name of the Name field that begins with Y.

+-+ +

| | Name | AVG (Age) |

+-+ +

| | Yang Guo | 34.0000 | |

+-+ +

1 row in set (0.00 sec)

The usage of SELECT is summarized:

Multi-table query:

Connection:

Cross join: Cartesian product (Usag:SELECT * FROM students,course; queries the contents of students and course tables)

Natural connection:

Usage: SELECT students.Name,courses.Course FROM students,courses WHERE students.CID1=courses.CID queries options for students table and courses table CID1=CID and displays the name

External connection:

Left outer join: left table LEFT JOIN right table ON condition

Right outer connection: left table RIGHT JOIN right table ON condition

Usage:

SELECT s.Name,c.Name FROM students AS s LEFT JOIN courses AS c ON s.CID1=c.CID

Show all the students in the table on the left and check the names of the courses they take. There is no direct display of NULL for elective courses.

SELECT s.Name,c.Name FROM students AS s RIGHT JOIN courses AS c ON s.CID1=c.CID

Look at all the elective courses in the table on the right, and see which students have taken the elective. The NULL of the elective course is not directly displayed.

Self-connect:

Make a join query for multiple fields of a table

Usage:

SELECT c.Name AS student,s.Name AS teacher FROM students AS c,students AS s WHERE

C.TID=s.SID

Look at the same fields of TID and SID in the students table and display their student and teacher names. AS aliases

Subquery:

Other subqueries are nested in the query statement. Subqueries are used in the comparison operation, and the subqueries can only return a single value.

Usage: SELECT Name FROM students WHERE Age > (SELECT AVG (Age) FROM students)

Query the names of students who are older than the average age

IN (): use subqueries

Usage:SELECT Name FROM students WHERE Age IN (SELECT Age FROM teacheers)

To query the name of a student whose age is the same as that of the teacher, IN indicates that the subquery can be a set of values.

Subqueries can also be inserted in FROM:

Usage:SELECT Name,Age FROM (SELECT Name,Age FROM students) AS t WHERE t.Age > = 20

Query other eligible data from the table of the structure of the SELECT query

Joint query:

UNION: join two tables into one table query display

Usage: (SELECT Name,Age FROM class) UNION (SELECT CID,Couse FROM courses)

Combine the CID and Couse fields in the courses table with the Name and Age fields in the class table to display as one table

Mysql > SELECT Name,Age FROM class

+-+ +

| | Name | Age |

+-+ +

| | Yang Guo | 22 | |

| | Guo Jing | 46 | |

| | Xiao Longnv | 18 | |

| | Huang Rong | 40 | |

+-+ +

4 rows in set (0.00 sec)

Mysql > SELECT CID,Couse FROM courses

+-+ +

| | CID | Couse |

+-+ +

| | 1 | physics |

| | 2 | english |

| | 3 | chemistry |

| | 4 | maths |

+-+ +

4 rows in set (0.00 sec)

Mysql > (SELECT Name,Age FROM class) UNION (SELECT CID,Couse FROM courses)

+-+ +

| | Name | Age |

+-+ +

| | Yang Guo | 22 | |

| | Guo Jing | 46 | |

| | Xiao Longnv | 18 | |

| | Huang Rong | 40 | |

| | 1 | physics |

| | 2 | english |

| | 3 | chemistry |

| | 4 | maths |

+-+ +

8 rows in set (0.00 sec)

Example:

1. Select the course names of the courses in the courses table that are not studied by the CID2 in the students table

Msyql > SELECT Cname FROM courses WHERE CID NOT IN (SELECT DISTINCT CID2 FROM students

WHERE CID2 IS NOT NULL); # SELECT DISTINCT CID2 FROM students WHERE CID2 IS NOT NULL finds the rows in the students table where CID2 is not empty and does not repeat, and then finds the rows in the courses table that CID is not looking for

The value of Cname in it

two。 Find out the course name of the same course that CID2 has two or more students in the students table.

Msyql > SELECT Cname FROM courses WHERE CID IN (SELECT CID2 FROM students GROUP

BY CID2 HAVING COUNT (CID2) > = 2); # find at least 2 students in a course and display the course name.

3. Show each course and its associated teachers, and courses that are not taught by teachers show their teachers as empty.

Msyql > SELECT t.TameCo.Cname FROM teachers AS t RIGHT JOIN courses AS c ON t.CID=c.TID

4. Show each teacher and the courses they teach, and the courses that are not taught remain null

Msyql > SELECT t.TameCo.Cname FROM teachers AS t LEFT JOIN courses AS c ON t.CID=c.TID

5. Display the name of each student's CID1 course and the name of the teacher who taught the relevant course

Msyql > SELECT Name,Cname,Tname FROM students,courses,teachers WHERE students.CID1=courses.CID AND courses.CID=teachers.CID

Read the above introduction of SELECT single table, multi-table query upgrade and insert and delete, hope to bring some help to everyone in practical application. Due to the limited space in this article, there will inevitably be deficiencies and areas that need to be supplemented. You can continue to pay attention to the industry information section and will update your industry news and knowledge regularly. If you need more professional answers, you can contact us on the official website for 24-hour pre-sales and after-sales to help you answer questions at any time.

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.