For simplicity, we will reuse the student information table previously created under the database xiaohaizi_ Info and student transcript_ Score, you may have forgotten what these two tables look like. Let's review the structure of the two tables first:
Structure of students' basic information table
CREATE TABLE student_info (
number INT PRIMARY KEY,
name VARCHAR(5),
sex ENUM('male', 'female'),
id_number CHAR(18),
department VARCHAR(30),
major VARCHAR(30),
enrollment_time DATE,
UNIQUE KEY (id_number)
);
Structure of student transcript
CREATE TABLE student_score (
number INT,
subject VARCHAR(30),
score TINYINT,
PRIMARY KEY (number, subject),
CONSTRAINT FOREIGN KEY(number) REFERENCES student_info(number)
);
Fill in data for table
We insert some data into these two tables:
mysql> INSERT INTO student_info(number, name, sex, id_number, department, major, enrollment_time) VALUES
-> (20180101, 'Du Ziteng', 'male', '158177199901044792', 'school of computing', 'Computer science and Engineering', '2018-09-01'),
-> (20180102, 'Du Qiyan', 'female', '151008199801178529', 'school of computing', 'Computer science and Engineering', '2018-09-01'),
-> (20180103, 'Fan Tong', 'male', '17156319980116959X', 'school of computing', 'software engineering', '2018-09-01'),
-> (20180104, 'Shi Zhenxiang', 'female', '141992199701078600', 'school of computing', 'software engineering', '2018-09-01'),
-> (20180105, 'Fan Jian', 'male', '181048199308156368', 'Academy of Astronautics', 'Aircraft design', '2018-09-01'),
-> (20180106, 'Zhu Yiqun', 'male', '197995199501078445', 'Academy of Astronautics', 'Electronic information', '2018-09-01');
Query OK, 6 rows affected (0.01 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> INSERT INTO student_score (number, subject, score) VALUES
-> (20180101, 'Postpartum care of sows', 78),
-> (20180101, 'On Saddam's war preparation', 88),
-> (20180102, 'Postpartum care of sows', 100),
-> (20180102, 'On Saddam's war preparation', 98),
-> (20180103, 'Postpartum care of sows', 59),
-> (20180103, 'On Saddam's war preparation', 61),
-> (20180104, 'Postpartum care of sows', 55),
-> (20180104, 'On Saddam's war preparation', 46);
Query OK, 8 rows affected (0.00 sec)
Records: 8 Duplicates: 0 Warnings: 0
mysql>
Now the data in these two tables are as follows:
student_info table
| number | name | sex | id_number | department | major | enrollment_time |
|---|---|---|---|---|---|---|
| 20180101 | Du Ziteng | male | 158177199901044792 | school of computing | Computer science and Engineering | 2018-09-01 |
| 20180102 | Du Qiyan | female | 151008199801178529 | school of computing | Computer science and Engineering | 2018-09-01 |
| 20180103 | Fan Tong | male | 17156319980116959X | school of computing | software engineering | 2018-09-01 |
| 20180104 | Shi Zhenxiang | female | 141992199701078600 | school of computing | software engineering | 2018-09-01 |
| 20180105 | Fan Jian | male | 181048200008156368 | Academy of Astronautics | Aircraft design | 2018-09-01 |
| 20180106 | Zhu Yiqun | male | 197995199801078445 | Academy of Astronautics | Electronic information | 2018-09-01 |
student_score table
| number | subject | score |
|---|---|---|
| 20180101 | Postpartum care of sows | 78 |
| 20180101 | On Saddam's war preparation | 88 |
| 20180102 | Postpartum care of sows | 100 |
| 20180102 | On Saddam's war preparation | 98 |
| 20180103 | Postpartum care of sows | 59 |
| 20180103 | On Saddam's war preparation | 61 |
| 20180104 | Postpartum care of sows | 55 |
| 20180104 | On Saddam's war preparation | 46 |
Well, the filling of the table has been completed ~ finally we can start to query the data!
Query single column
The general format for viewing the data of a column in a table is as follows:
SELECT Listing FROM Table name;
That is, just put the column names to be queried behind the word SELECT, such as viewing student_ The data of the number column in the info table can be written as follows:
mysql> SELECT number FROM student_info; +----------+ | number | +----------+ | 20180104 | | 20180102 | | 20180101 | | 20180103 | | 20180105 | | 20180106 | +----------+ 6 rows in set (0.00 sec) mysql>
You can see that the number column of all records is displayed in the query structure. We sometimes call this result a result set.
Tip: you may find that the data in the result set is not sorted according to the size of our number column value. We will talk about how to specify the sorting method of the data in the result set later. Don't be impatient.
Alias of the column
We can also redefine an alias for the column in the result set. The command format is as follows:
SELECT Listing [AS] Alias of the column FROM Table name;
We see that AS is bracketed, which means it is optional. Without AS, the column name and column alias should be separated by white space characters. For example, if we want to list an alias for number, we can use one of the following two methods:
-
Mode 1
SELECT number AS Student number FROM student_info;
-
Mode 2:
SELECT number Student number FROM student_info;
Let's do the following:
mysql> SELECT number AS Student number FROM student_info; +----------+ | Student number | +----------+ | 20180104 | | 20180102 | | 20180101 | | 20180103 | | 20180105 | | 20180106 | +----------+ 6 rows in set (0.00 sec) mysql>
The column name displayed in the result set in the black box is no longer number, but the alias student number we just defined. However, it should be noted that the alias is only displayed in the result set of this query, and the column names in the real table will not be changed. In the next query, you can also take other aliases for the number column, such as:
mysql> SELECT number xuehao FROM student_info; +----------+ | xuehao | +----------+ | 20180104 | | 20180102 | | 20180101 | | 20180103 | | 20180105 | | 20180106 | +----------+ 6 rows in set (0.00 sec) mysql>
The output column name is another alias xuehao.
Query multiple columns
If you want to query the data of multiple columns, you can write multiple column names after SELECT and separate them with commas:
SELECT Column name 1, Column name 2, ... Listing n FROM Table name;
The multiple columns following the SELECT statement are collectively referred to as the query list. It should be noted that the column names in the query list can be placed in any order, and the result set will be displayed in the order of the column names we specify. For example, we query students_ Multiple columns in Info:
mysql> SELECT number, name, id_number, major FROM student_info; +----------+-----------+--------------------+--------------------------+ | number | name | id_number | major | +----------+-----------+--------------------+--------------------------+ | 20180101 | Du Ziteng | 158177199901044792 | Computer science and Engineering | | 20180102 | Du Qiyan | 151008199801178529 | Computer science and Engineering | | 20180103 | Fan Tong | 17156319980116959X | software engineering | | 20180104 | Shi Zhenxiang | 141992199701078600 | software engineering | | 20180105 | Fan Jian | 181048199308156368 | Aircraft design | | 20180106 | Zhu Yiqun | 197995199501078445 | Electronic information | +----------+-----------+--------------------+--------------------------+ 6 rows in set (0.00 sec) mysql>
The query list in this example is number, name and id_number, major, so the order of columns in the result set is displayed in this order. Of course, we can also use aliases to output these data:
mysql> SELECT number AS Student number, name AS full name, id_number AS ID number, major AS major FROM student_info; +----------+-----------+--------------------+--------------------------+ | Student number | full name | ID number | major | +----------+-----------+--------------------+--------------------------+ | 20180101 | Du Ziteng | 158177199901044792 | Computer science and Engineering | | 20180102 | Du Qiyan | 151008199801178529 | Computer science and Engineering | | 20180103 | Fan Tong | 17156319980116959X | software engineering | | 20180104 | Shi Zhenxiang | 141992199701078600 | software engineering | | 20180105 | Fan Jian | 181048199308156368 | Aircraft design | | 20180106 | Zhu Yiqun | 197995199501078445 | Electronic information | +----------+-----------+--------------------+--------------------------+ 6 rows in set (0.00 sec) mysql>
If you like, the same column can be repeated in the query list (although this is usually useless), such as:
mysql> SELECT number, number, number FROM student_info; +----------+----------+----------+ | number | number | number | +----------+----------+----------+ | 20180104 | 20180104 | 20180104 | | 20180102 | 20180102 | 20180102 | | 20180101 | 20180101 | 20180101 | | 20180103 | 20180103 | 20180103 | | 20180105 | 20180105 | 20180105 | | 20180106 | 20180106 | 20180106 | +----------+----------+----------+ 6 rows in set (0.00 sec) mysql>
Query all columns
If you need to find all the columns in the record, MySQL also provides an easy way. As we introduced earlier, you can directly use the asterisk * to represent the things to be queried, like this:
SELECT * FROM Table name;
We've seen this order before, so we don't talk much. However, it should be noted that unless you really need each column in the table, it is generally best not to use the asterisk * to query all columns. Although the asterisk * looks very convenient and does not explicitly list the required columns, querying unnecessary columns usually reduces performance.
De duplication of query results
Remove duplicate results in a single column
Sometimes when we query the data of a column, there will be some duplicate results. For example, we query student_ College information in info table:
mysql> SELECT department FROM student_info; +-----------------+ | department | +-----------------+ | school of computing | | school of computing | | school of computing | | school of computing | | Academy of Astronautics | | Academy of Astronautics | +-----------------+ 6 rows in set (0.00 sec)
Because there are 6 records in the table, 6 results are returned to us. But in fact, many of them are duplicate results. If we want to remove duplicate results, we can put DISTINCT in front of the queried column. That's it:
SELECT DISTINCT Listing FROM Table name;
We reprocess the college information:
mysql> SELECT DISTINCT department FROM student_info; +-----------------+ | department | +-----------------+ | school of computing | | Academy of Astronautics | +-----------------+ 2 rows in set (0.00 sec)
When you see the result set, there is only non repetitive information left.
Remove duplicate results from multiple columns
In the case of querying multiple columns, the repetition of two results means that the values in each column of the two results are the same. For example, query college and professional information:
mysql> SELECT department, major FROM student_info; +-----------------+--------------------------+ | department | major | +-----------------+--------------------------+ | school of computing | Computer science and Engineering | | school of computing | Computer science and Engineering | | school of computing | software engineering | | school of computing | software engineering | | Academy of Astronautics | Aircraft design | | Academy of Astronautics | Electronic information | +-----------------+--------------------------+ 6 rows in set (0.00 sec)
In the query result, the department and major columns in the records in rows 1 and 2 are the same, so these two records are duplicate. Similarly, rows 3 and 4 are also duplicate. If we want to duplicate the results of multi column query, we can directly put DISTINCT at the front of the queried column:
SELECT DISTINCT Column name 1, Column name 2, ... Listing n FROM Table name;
For example:
mysql> SELECT DISTINCT department, major FROM student_info; +-----------------+--------------------------+ | department | major | +-----------------+--------------------------+ | school of computing | Computer science and Engineering | | school of computing | software engineering | | Academy of Astronautics | Aircraft design | | Academy of Astronautics | Electronic information | +-----------------+--------------------------+ 4 rows in set (0.00 sec) mysql>
Limit the number of query results
Sometimes there are many query results, which may burst the screen when displayed. Therefore, MySQL provides us with a way to limit the number of records in the result set, that is, use this syntax at the end of the query statement:
LIMIT Start line, Limit the number of entries;
The start row refers to the row from which we want to start the query. The limit number is the maximum number of records in the result set.
Tip: in life, we usually start counting from 1, but in the computer, we start counting from 0, so the first record we usually say is the first record in the computer. For example ` student_ The six records in the info ` table are successively represented in the computer as: Article 0, Article 1, Article 2, Article 3, Article 4 and Article 5.
For example, let's check student_info table, starting from record 0, can query up to 2 records, which can be written as follows:
mysql> SELECT number, name, id_number, major FROM student_info LIMIT 0, 2; +----------+-----------+--------------------+--------------------------+ | number | name | id_number | major | +----------+-----------+--------------------+--------------------------+ | 20180101 | Du Ziteng | 158177199901044792 | Computer science and Engineering | | 20180102 | Du Qiyan | 151008199801178529 | Computer science and Engineering | +----------+-----------+--------------------+--------------------------+ 2 rows in set (0.00 sec) mysql>
If the specified start row is larger than the number of rows in the result, the query result has nothing:
mysql> SELECT number, name, id_number, major FROM student_info LIMIT 6, 2; Empty set (0.00 sec) mysql>
If the number of query results does not exceed the limit, all of them can be explicitly displayed:
mysql> SELECT number, name, id_number, major FROM student_info LIMIT 4, 3; +----------+-----------+--------------------+-----------------+ | number | name | id_number | major | +----------+-----------+--------------------+-----------------+ | 20180105 | Fan Jian | 181048199308156368 | Aircraft design | | 20180106 | Zhu Yiqun | 197995199501078445 | Electronic information | +----------+-----------+--------------------+-----------------+ 2 rows in set (0.00 sec) mysql>
There are two records starting from item 4, and the limit is 3, so both records can be displayed in the result set.
Use default start line
There can also be only one parameter after LIMIT, which represents the LIMIT of the number of rows. That is, we can not specify the start line. The default start line is line 0. For example, we can write:
mysql> SELECT number, name, id_number, major FROM student_info LIMIT 3; +----------+-----------+--------------------+--------------------------+ | number | name | id_number | major | +----------+-----------+--------------------+--------------------------+ | 20180101 | Du Ziteng | 158177199901044792 | Computer science and Engineering | | 20180102 | Du Qiyan | 151008199801178529 | Computer science and Engineering | | 20180103 | Fan Tong | 17156319980116959X | software engineering | +----------+-----------+--------------------+--------------------------+ 3 rows in set (0.00 sec) mysql>
The query result shows 3 records starting from 0.
Sort query results
When we query the number column before, the records obtained are not ordered. Why? In fact, MySQL will return data to us by default according to the order of the underlying storage of these data, but these data may be updated or deleted. If we do not specify the order of sorting the returned results, we can think that the order of records in the results is uncertain. In other words, if we want the records in the returned results to be sorted according to a specific rule, we must explicitly specify the sorting rule.
Sort by the value of a single column
We can use the following syntax to specify that the records of the returned results are sorted according to the value of a column:
ORDER BY Listing ASC|DESC
ASC and DESC refer to the sort direction. ASC refers to sorting from small to large according to the value of the specified column, which is also called ascending order. DESC refers to sorting from large to small according to the value of the specified column, which is also called descending order. The | in the middle indicates that only one of these two methods can be selected. This time we use student_ The score table tests:
mysql> SELECT * FROM student_score ORDER BY score ASC; +----------+-----------------------------+-------+ | number | subject | score | +----------+-----------------------------+-------+ | 20180104 | On Saddam's war preparation | 46 | | 20180104 | Postpartum care of sows | 55 | | 20180103 | Postpartum care of sows | 59 | | 20180103 | On Saddam's war preparation | 61 | | 20180101 | Postpartum care of sows | 78 | | 20180101 | On Saddam's war preparation | 88 | | 20180102 | On Saddam's war preparation | 98 | | 20180102 | Postpartum care of sows | 100 | +----------+-----------------------------+-------+ 8 rows in set (0.01 sec) mysql>
You can see that the output records are sorted from small to large. If the sorting direction in the ORDER BY statement is omitted, the default sorting is from small to large, that is, the semantics of ORDER BY column name and ORDER BY column name ASC are the same. Let's try:
mysql> SELECT * FROM student_score ORDER BY score; +----------+-----------------------------+-------+ | number | subject | score | +----------+-----------------------------+-------+ | 20180104 | On Saddam's war preparation | 46 | | 20180104 | Postpartum care of sows | 55 | | 20180103 | Postpartum care of sows | 59 | | 20180103 | On Saddam's war preparation | 61 | | 20180101 | Postpartum care of sows | 78 | | 20180101 | On Saddam's war preparation | 88 | | 20180102 | On Saddam's war preparation | 98 | | 20180102 | Postpartum care of sows | 100 | +----------+-----------------------------+-------+ 8 rows in set (0.01 sec)
Let's look at the sorting from large to small:
mysql> SELECT * FROM student_score ORDER BY score DESC; +----------+-----------------------------+-------+ | number | subject | score | +----------+-----------------------------+-------+ | 20180102 | Postpartum care of sows | 100 | | 20180102 | On Saddam's war preparation | 98 | | 20180101 | On Saddam's war preparation | 88 | | 20180101 | Postpartum care of sows | 78 | | 20180103 | On Saddam's war preparation | 61 | | 20180103 | Postpartum care of sows | 59 | | 20180104 | Postpartum care of sows | 55 | | 20180104 | On Saddam's war preparation | 46 | +----------+-----------------------------+-------+ 8 rows in set (0.00 sec) mysql>
Sort by the values of multiple columns
We can also specify multiple sorted columns at the same time. Multiple sorted columns are separated by commas. That's it:
ORDER BY Column 1 ASC|DESC, Column 2 ASC|DESC ...
For example, we want students_ The query results of score are sorted according to subjuect first, and then sorted according to the order of score value from large to small, which can be written as follows:
mysql> SELECT * FROM student_score ORDER BY subject, score DESC; +----------+-----------------------------+-------+ | number | subject | score | +----------+-----------------------------+-------+ | 20180102 | Postpartum care of sows | 100 | | 20180101 | Postpartum care of sows | 78 | | 20180103 | Postpartum care of sows | 59 | | 20180104 | Postpartum care of sows | 55 | | 20180102 | On Saddam's war preparation | 98 | | 20180101 | On Saddam's war preparation | 88 | | 20180103 | On Saddam's war preparation | 61 | | 20180104 | On Saddam's war preparation | 46 | +----------+-----------------------------+-------+ 8 rows in set (0.00 sec) mysql>
Again, if the sorting direction is not specified, ASC is used by default, that is, the ascending rule from small to large.
Tip: it's easy to understand the sorting of numbers, but how to sort strings? Which is bigger and which is smaller in uppercase A and lowercase a? This problem involves the encoding method used by strings and string sorting rules. We will introduce them in detail later. Now you only need to know the sorting syntax.
We can also use the ORDER BY statement in combination with the LIMIT statement, but The ORDER BY statement must be placed before the LIMIT statement, for example:
mysql> SELECT * FROM student_score ORDER BY score LIMIT 1; +----------+-----------------------------+-------+ | number | subject | score | +----------+-----------------------------+-------+ | 20180104 | On Saddam's war preparation | 46 | +----------+-----------------------------+-------+ 1 row in set (0.00 sec) mysql>
So we can find the record with the lowest score.