1. The syntax of the
SELECT statement:
SELECT selection_list select which columns
FROM table_list where to select rows
What conditions must the WHERE primary_constraint row meet
GROUP BY grouping_columns how to group results
The second condition that the HAVING secondary_constraint row must meet
ORDER BY sorting_columns how to sort the results
LIMIT count result limit
Note: All keywords used must be given exactly in the above order.
For example, a HAVING clause must follow the GROUP BY clause and before the ORDER BY clause.
Except for the word "SELECT" and the column_list part that describes what you want to retrieve, everything in the grammar is optional. Some databases also require a FROM clause.
MySQL is different, it allows expressions to be evaluated without referencing any tables.
2. General query
The simplest form of SELECT is to retrieve everything from a table, the command is as follows:
mysql> SELECT * FROM pet;
Query specific rows:
You can select only specific rows from your table.
For example, if you want to verify your changes to Bowser’s date of birth, select Bowser’s records like this:
mysql> SELECT * FROM pet WHERE name = "Bowser";
Query specific columns
If you don't want to see the entire row of your table, name the columns you are interested in, separated by commas.
For example, if you want to know when your animal was born, select the name and birth columns:
mysql> SELECT name, birth FROM pet where owner="Gwen";
Perform expression evaluation
Most of the previous queries have produced output by retrieving values from the table. MySQL also allows the value of the output column to be calculated as the result of a formula. Expressions can be simple or complex. The following query evaluates the value of a simple expression (constant) and the value of a more complex expression involving several arithmetic operators and two function calls. For example, to calculate the number of days that Browser lives:
mysql> SELECT death-birth FROM pet WHERE name="Bowser";
MySQL allows evaluation of expressions without referencing any tables. So it can also be used like this:
mysql>select (2+3*4.5)/2.5;
3. Conditional query
You don't have to return all the rows for every query, you can select only specific rows from your table. For this you need to use WHERE or HAVING clause. The difference between the HAVING clause and the WHERE clause is that HAVING expresses the second condition. When used in conjunction with other clauses, it is obviously not possible to use HAVING in the WHERE clause. Therefore, this section introduces the use of WHERE clauses, and the use of HAVING clauses is similar. In addition, the WHERE clause can also achieve most of the functions of the HAVING clause.
To limit the set of records retrieved by the SELECT statement, you can use the WHERE clause, which gives the conditions for selecting rows. You can select rows by looking for column values that satisfy various conditions.
String comparison is usually irrelevant in size, so you can specify the names as "bowser", "BOWSER", etc., the query results will be the same.
You can specify conditions on any column, not just name. For example, if you want to know which animal was born after 1998, test the birth column:
mysql> SELECT * FROM pet WHERE birth >= "1998-1-1";
You can combine conditions, for example, to find female dogs:
mysql> SELECT * FROM pet WHERE species = "dog" AND sex = "f";
The above query uses the AND logical operator and there is also an OR operator:
mysql> SELECT * FROM pet WHERE species = "snake" OR species = "bird";
AND and OR can be mixed. If you do this, it is a good idea to use parentheses to indicate how the conditions should be grouped:
mysql> SELECT * FROM pet WHERE (species = "cat" AND sex = "m")
OR (species = "dog" AND sex = "f");
4. Query sorting
Use the ORDER BY clause to sort the results returned by the query by one or more columns. The syntax of the ORDER BY clause is:
ORDER BY column_name [ASC|DESC] [,…]
ASC means ascending order, which is the default value, and DESC is descending order. ORDER BY cannot be sorted by text and image data types. In addition, you can sort by expression.
For example, here are animal birthdays, sorted by date:
mysql> SELECT name, birth FROM pet ORDER BY birth;
To sort in reverse order, add the DESC (descending) keyword to the column name you are sorting:
mysql> SELECT name, birth FROM pet ORDER BY birth DESC;
You can sort on multiple columns. For example, to sort by animal type, then by birthday, first the youngest animal of the animal type, use the following query:
mysql> SELECT name, species, birth FROM pet ORDER BY species, birth DESC;
Note that the DESC keyword only applies to the column name (birth) immediately before it; the species values are still sorted in ascending order. Note that the output is first sorted by species, and then pets with the same species are sorted in descending order of birth.
5. Query grouping and row count
The GROUP BY clause returns grouped query results based on the given column name, which can be used to query columns with the same value. The syntax is:
GROUP BY col_name,...
You can group multiple columns.
E.g:
mysql>SELECT * FROM pet GROUP BY species;
When the query displays the results, if the grouped columns have duplicate values, only the top records are returned, and the returned recordset is sorted. This is not a good result. The use of the GROUP BY clause alone does not make much sense. The real function of this clause is to cooperate with various combined functions for row counting.
1) The COUNT() function counts the number of non-NULL results.
You can count the number of rows in the table like this:
mysql> select count(*) from pet;
Count the number of records where sex is not empty:
mysql> select count(sex) from pet;
Now used in conjunction with the GROUP BY clause.
For example: to know how many pets each owner has
mysql> SELECT owner, COUNT(*) FROM pet GROUP BY owner;
Another example, the number of each pet:
mysql> SELECT species,count(*) FROM pet GROUP BY species;
If you return a column value in addition to the count, you must use the GROUP BY statement, otherwise you cannot calculate the record. For example, in the above example, using GROUP BY to group all records for each owner, without it, all you get is an error message:
mysql> SELECT owner, COUNT(owner) FROM pet;
ERROR 1140 at line 1: Mixing of GROUP columns (MIN(),MAX(),COUNT()...)
with no GROUP columns is illegal if there is no GROUP BY clause
You can also group by multiple columns, for example:
Number of animals combined by type and gender:
mysql> SELECT species, sex, COUNT(*) FROM pet GROUP BY species, sex;
6. Query multiple tables
According to the previous method, proceed as follows:
1). Create a new table dept in the database asb, there are two elements in the table:
dept_id --> varchar(6)
dept_name --> varchar(10)
2). Insert the following new record in the table emp:
+--------+----------+---------+---------+--------- ---+---------+
| emp_id | emp_name | emp_age | emp_sal | emp_bir | emp_sex |
+--------+----------+---------+---------+--------- ---+---------+
100003 | Little Red | 30 | 8000 | 1976-11-11 | fmale |
+--------+----------+---------+---------+--------- ---+---------+
3). In the new table dept, enter the following records
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
| 100005 | MTD |
| 100001 | MTD |
| 100002 | MTD |
| 100003 | HR |
+---------+-----------+
Query the employee's name and department information in the two tables emp and dept
mysql> select emp.emp_name,dept.dept_name from emp,dept
-> where emp.emp_id=dept.dept_id;
The query results are as follows:
+----------+-----------+
| emp_name | dept_name |
+----------+-----------+
| Xiaotian | MTD |
| Red Maple | MTD |
| Cuckoo | MTD |
| Red | HR |
+----------+-----------+
4 rows in set (0.00 sec)
Note when querying multiple tables:
1). The FROM clause must give the full name of the queried table
2). When selecting the field, indicate the name of the table to which it belongs (eg emp_id in the emp table should be expressed as emp.emp_id)
3). The query conditions must be specified in the Where clause (eg, emp.emp_id and dept.dept_id are elements of the same meaning)
