mysql--simple query and conditional query

mysql--simple query and conditional query

Select statement when querying the basic statement of data from a data table in MySQL.
SELECT statement Basic syntax format:
Select query Content
From table name
where expression
Group By field name
Having an expression
Order BY Field name
Limit Record Count
Each SELECT statement consists of multiple clauses.

1. The From table name specifies the query from that table

2. Select query Content

Query all fields select * from table name;
* Wildcards represent all fields
Mysql> select * from test;
+------+------+------+
| ID | name | Age |
+------+------+------+
| 1 |    A | 4 |
| 2 |    B | 7 |
| 3 |    C | 5 |
| 4 |   D | 12 |
+------+------+------+
4 rows in Set (0.06 sec)

Query part field select Field name from table name;
Mysql> select name from test;
+------+
| name |
+------+
| A |
| B |
| C |
| D |
+------+
4 rows in Set (0.00 sec)

In MySQL tables, the data for each field can be treated as a variable.
Query the result of a field that is required to process data: The Select field is processed from the table name;
Mysql> select age-3 from test;
+-------+
| age-3 |
+-------+
| 1 |
| 4 |
| 2 |
| 9 |
+-------+
4 rows in Set (0.11 sec)

3. Where expression (query by criteria)
In the MySQL table query, often do not need to all the content to find out, but based on actual needs, query the required data
Select query content from table name where expression;

In a MySQL statement, a conditional expression is a query condition for a SELECT statement that can be connected using the relational operator in the WHERE clause
The data is selected as the query criteria by the next operand.
Relational operators:
= equals
<> Not equal to
! = does not equal
< less than
> Greater than
<= less than or equal to
>= greater than or equal to

For example, querying for information older than 5
Mysql> SELECT * from Test where age > 5;
+------+------+------+
| ID | name | Age |
+------+------+------+
| 2 |    B | 7 |
| 4 |   D | 12 |
+------+------+------+
2 rows in Set (0.04 sec)

Keyword query with in
Queries a record in a specified collection select query content from table name where condition in (specified content);
Mysql> SELECT * from Test where age in (5, 12);
+------+------+------+
| ID | name | Age |
+------+------+------+
| 3 |    C | 5 |
| 4 |   D | 12 |
+------+------+------+
2 rows in Set (0.00 sec)

With between and keyword query
Query for a record in a given range select query content from table name where condition between value 1 and value 2;
Mysql> SELECT * from Test where age between 5 and 12;
+------+------+------+
| ID | name | Age |
+------+------+------+
| 2 |    B | 7 |
| 3 |    C | 5 |
| 4 |   D | 12 |
+------+------+------+
3 rows in Set (0.07 sec)

Query for some null or non-empty records select query contents from table name where condition is (not) NULL;
Mysql> SELECT * FROM Test where-is NULL;
+------+------+------+
| ID | name | Age |
+------+------+------+
| 6 | F | NULL |
+------+------+------+
1 row in Set (0.00 sec)

Filter out duplicate values at query time: SELECT DISTINCT field name from table name; Field name indicates field to filter duplicate records
Mysql> Select Num from A;
+------+
| num |
+------+
| 5 |
| 10 |
| 15 |
| 10 |
| 15 |
| 5 |
| 10 |
+------+
7 Rows in Set (0.00 sec)

Mysql> SELECT Distinct num from A;
+------+
| num |
+------+
| 5 |
| 10 |
| 15 |
+------+
3 Rows in Set (0.00 sec)
When you use distinct to specify multiple fields, only those fields that are specified are the same values that are considered duplicates.

When querying data with a class of the same characteristics, you need to use a fuzzy query, which is the need for using the LIKE keyword
Select query content from table name where content (not) like ' matching string '
Percent semicolon wildcard%, which is any string that matches any length
Mysql> select name from name;
+------+
| name |
+------+
| 1112 |
| 1122 |
| 1222 |
| 2111 |
+------+
4 rows in Set (0.00 sec)

Mysql> select name from name where name is like ' 11% ';
+------+
| name |
+------+
| 1112 |
| 1122 |
+------+
2 rows in Set (0.00 sec)

The underscore wildcard _: indicates that any single character is matched, and if multiple characters need to be matched, you need to use multiple _
Mysql> select name from name where name is like ' 11__ ';
+------+
| name |
+------+
| 1112 |
| 1122 |
+------+
2 rows in Set (0.00 sec)

If you need to query for data with% or _, because% and _ are wildcard characters, you need to escape with \
\% means%,\_ means _

Sometimes when querying for more accurate query results, multiple constraints are required, then the and (&&) to join the condition
Mysql> Select cat_id, Cat_name, parent_id from category;
+--------+---------------------------+-----------+
| cat_id | Cat_name | parent_id |
+--------+---------------------------+-----------+
| 1 |         Phone type | 0 |
| 2 |         CDMA Mobile Phones | 1 |
| 3 |         GSM Mobile Phone | 1 |
| 4 |         3G Mobile Phone | 1 |
| 5 |         Dual-mode Mobile phones | 1 |
| 6 |         Mobile Accessories | 0 |
| 7 |         Charger | 6 |
| 8 |         Headphones | 6 |
| 9 |         Battery | 6 |
| 11 |         Card readers and Memory cards | 6 |
| 12 |         Prepaid Card | 0 |
| 13 |        PHS/fixed-line Prepaid Card | 12 |
| 14 |        Mobile Phone Recharge Card | 12 |
| 15 |        Unicom Mobile Prepaid Card | 12 |
+--------+---------------------------+-----------+
Rows in Set (0.00 sec)

Mysql> Select cat_id, Cat_name, parent_id from category
    where cat_id > 7 and Parent_i D = 6;
+--------+-----------------------+-----------+
| cat_id | cat_name               | parent_id |
+--------+-----------------------+-----------+
|      8 | Headphones                    |          6 |
|      9 | battery                    |         6 |
|     11 | card reader and memory card         |          6 |
+--------+-----------------------+-----------+
3 rows in Set (0.05 sec)

Sometimes when querying, you only need the data to satisfy some of the conditions, then you need to use or (| |) to join the condition
Ysql> Select cat_id, Cat_name, parent_id from category where cat_id = 3 or cat_id = 9;
+--------+-----------+-----------+
| cat_id | Cat_name | parent_id |
+--------+-----------+-----------+
| 3 |         GSM Mobile Phone | 1 |
| 9 |         Battery | 6 |
+--------+-----------+-----------+
2 rows in Set (0.02 sec)

At query time, and has a higher precedence than or

mysql--simple query and conditional query