sql中的子句

where 返回数据进行限制

sql">root@mysqldb 10:52:  [d1]> SELECT * FROM cars_price WHERE name = 'ADI';
+------+------+--------+-------+------+
| name | num  | colour | price | type |
+------+------+--------+-------+------+
| ADI  |   22 | red    |    13 | sdfx |
+------+------+--------+-------+------+
1 row in set (0.00 sec)

like 进行模糊匹配查询

sql">root@mysqldb 10:54:  [d1]> SELECT * FROM cars_price WHERE name like "%CH%";
+---------+------+--------+-------+------+
| name    | num  | colour | price | type |
+---------+------+--------+-------+------+
| CHANGAN |   23 | white  |    15 | zdfs |
| BCHI    |   23 | white  |    15 | zdfs |
+---------+------+--------+-------+------+
2 rows in set (0.01 sec)

ORDER BY 按照某一个字段进行顺序排序

sql">root@mysqldb 10:54:  [d1]> SELECT * FROM cars_price ORDER BY price;
+---------+------+--------+-------+------+
| name    | num  | colour | price | type |
+---------+------+--------+-------+------+
| LKE     |   20 | white  |    11 | aa   |
| WLAI    |   21 | green  |    12 | acd  |
| ADI     |   22 | red    |    13 | sdfx |
| JILI    |   24 | black  |    14 | zdzx |
| CHANGAN |   23 | white  |    15 | zdfs |
| BCHI    |   23 | white  |    15 | zdfs |
+---------+------+--------+-------+------+
6 rows in set (0.00 sec)

ORDER BY … DESC 倒叙排序

sql">root@mysqldb 10:56:  [d1]> SELECT * FROM cars_price ORDER BY price DESC;
+---------+------+--------+-------+------+
| name    | num  | colour | price | type |
+---------+------+--------+-------+------+
| CHANGAN |   23 | white  |    15 | zdfs |
| BCHI    |   23 | white  |    15 | zdfs |
| JILI    |   24 | black  |    14 | zdzx |
| ADI     |   22 | red    |    13 | sdfx |
| WLAI    |   21 | green  |    12 | acd  |
| LKE     |   20 | white  |    11 | aa   |
+---------+------+--------+-------+------+
6 rows in set (0.00 sec)

group by

sql">root@mysqldb 11:05:  [d1]> select * from cars_price group by price;
+---------+------+--------+-------+------+
| name    | num  | colour | price | type |
+---------+------+--------+-------+------+
| LKE     |   20 | white  |    11 | aa   |
| WLAI    |   21 | green  |    12 | acd  |
| ADI     |   22 | red    |    13 | sdfx |
| JILI    |   24 | black  |    14 | zdzx |
| CHANGAN |   23 | white  |    15 | zdfs |
+---------+------+--------+-------+------+
5 rows in set (0.00 sec)

having HAVING子句用于在查询结果中进行筛选和聚合。通常与GROUP BY 子句一起使用，用于在分组后的结果中应用条件过滤。

sql">root@mysqldb 11:21:  [d1]> select name, num, price, SUM(price) as total_price FROM cars_price;
+---------+------+-------+-------------+
| name    | num  | price | total_price |
+---------+------+-------+-------------+
| CHANGAN |   23 |    15 |          80 |
+---------+------+-------+-------------+
1 row in set (0.00 sec)root@mysqldb 11:22:  [d1]> select name, num, price, SUM(price) as total_price FROM cars_price GROUP BY num having total_price > 20; 
+---------+------+-------+-------------+
| name    | num  | price | total_price |
+---------+------+-------+-------------+
| CHANGAN |   23 |    15 |          30 |
+---------+------+-------+-------------+
1 row in set (0.00 sec)