MySQL 查询数据

快来打我* 2022-03-31 16:46 242阅读 0赞

1 查询所有字段

1.1 使用\*通配符查询所有字段

SELECT \* FROM 表名

1.2 在SELECT语句中指定所有字段

SELECT id,name from users；

2 查询指定字段

2.1 查询单个字段

SELECT 列名 FROM 表名；

2.2 查询多个字段

SELECT 列名1，列名2，列名3 ...列名n from 表名

2.3 查询指定记录

SELECT 字段名1，字段名2，...，字段名n FROM 表名 where 查询条件

2.4 带in关键字的查询

mysql> SELECT f_id from fruits where f_name in(apple, orange);
    
    ERROR 1054 (42S22): Unknown column 'apple' in 'where clause'
    
    mysql> SELECT f_id from fruits where f_name in('apple','orange');
    
    +------+
    
    | f_id |
    
    +------+
    
    | a1   |
    
    | bs1  |
    
    +------+
    
    2 rows in set (0.00 sec)

2.5 带between and的范围查询

2.6 带有like的字符匹配查询

可以和like一起匹配使用的通配符有 % 和 \_

2.6.1 百分号通配符 %  匹配任意长度的字符，甚至包括零字符   %可以放在任意位置

mysql> select f_id,s_id,f_name from fruits where f_name like '%c%';
    +------+------+------------+
    | f_id | s_id | f_name     |
    +------+------+------------+
    | b1   |  101 | blackberry |
    +------+------+------------+
    1 row in set (0.00 sec)

2.6.2 下划线通配符 \_ 一次只能匹配任意一个字符

select f_id, f_name from fruits where f_name like '____e';
    +------+--------+
    | f_id | f_name |
    +------+--------+
    | a1   | apple  |
    +------+--------+
    1 row in set (0.00 sec)

2.7 查询空值

select语句中使用 is NULL 子句，可以查询字段是否为空

mysql> select f_id, f_name from fruits where province is NULL;
    +------+--------+
    | f_id | f_name |
    +------+--------+
    | bs1  | orange |
    +------+--------+
    1 row in set (0.00 sec)

2.8 带and的多条件查询

使用and连接两个甚至多个查询条件，多个条件表达式之间用and分开

mysql> select * from fruits where f_price>6 and province is NOT NULL;
    
    +------+------+------------+---------+----------+
    
    | f_id | s_id | f_name     | f_price | province |
    
    +------+------+------------+---------+----------+
    
    | b1   |  101 | blackberry |   10.20 | ShanDong |
    
    +------+------+------------+---------+----------+
    
    1 row in set (0.00 sec)

2.9 带or的多条件查询

mysql> select * from fruits where f_name = 'apple' or province is NULL;
    +------+------+--------+---------+----------+
    | f_id | s_id | f_name | f_price | province |
    +------+------+--------+---------+----------+
    | a1   |  101 | apple  |    5.20 | HeNan    |
    | bs1  |  102 | orange |   11.20 | NULL     |
    +------+------+--------+---------+----------+
    2 rows in set (0.00 sec)

2.10 distinct 消除重复记录

2.11 对查询结果排序

2.11.1 单列排序

mysql> select * from fruits order by f_price;
    +------+------+------------+---------+----------+
    | f_id | s_id | f_name     | f_price | province |
    +------+------+------------+---------+----------+
    | a1   |  101 | apple      |    5.20 | HeNan    |
    | b1   |  101 | blackberry |   10.20 | ShanDong |
    | bs1  |  102 | orange     |   11.20 | NULL     |
    +------+------+------------+---------+----------+
    3 rows in set (0.00 sec)
    
    mysql> select * from fruits order by province;
    +------+------+------------+---------+----------+
    | f_id | s_id | f_name     | f_price | province |
    +------+------+------------+---------+----------+
    | bs1  |  102 | orange     |   11.20 | NULL     |
    | a1   |  101 | apple      |    5.20 | HeNan    |
    | b1   |  101 | blackberry |   10.20 | ShanDong |
    +------+------+------------+---------+----------+
    3 rows in set (0.00 sec)

2.11.2 多列排序

在对多列排序的时候，只有在第一列有相同值的情况下，才会对第二列进行排序。

mysql> select * from fruits;
    +------+------+------------+---------+----------+
    | f_id | s_id | f_name     | f_price | province |
    +------+------+------------+---------+----------+
    | a1   |  101 | apple      |    5.20 | HeNan    |
    | b1   |  101 | blackberry |   10.20 | ShanDong |
    | b11  |  101 | apple      |    4.00 | NULL     |
    | bs1  |  102 | orange     |   11.20 | NULL     |
    +------+------+------------+---------+----------+
    4 rows in set (0.00 sec)
    
    mysql> select * from fruits order by f_name;
    +------+------+------------+---------+----------+
    | f_id | s_id | f_name     | f_price | province |
    +------+------+------------+---------+----------+
    | a1   |  101 | apple      |    5.20 | HeNan    |
    | b11  |  101 | apple      |    4.00 | NULL     |
    | b1   |  101 | blackberry |   10.20 | ShanDong |
    | bs1  |  102 | orange     |   11.20 | NULL     |
    +------+------+------------+---------+----------+
    4 rows in set (0.00 sec)
    
    mysql> select * from fruits order by f_name,f_price;
    +------+------+------------+---------+----------+
    | f_id | s_id | f_name     | f_price | province |
    +------+------+------------+---------+----------+
    | b11  |  101 | apple      |    4.00 | NULL     |
    | a1   |  101 | apple      |    5.20 | HeNan    |
    | b1   |  101 | blackberry |   10.20 | ShanDong |
    | bs1  |  102 | orange     |   11.20 | NULL     |
    +------+------+------------+---------+----------+
    4 rows in set (0.00 sec)

2.11.3 指定排序方向

DESC 降序         ASC升序

2.12 分组查询

2.12.1 创建分组

group by关键字通常和集合函数一起使用，如MAX(),MIN(),COUNT(),SUM(),AVG().

mysql> select s_id,count(*) as total from fruits group by s_id;
    +------+-------+
    | s_id | total |
    +------+-------+
    |  101 |     3 |
    |  102 |     1 |
    +------+-------+
    2 rows in set (0.01 sec)

mysql> select s_id,count(*) as total, group_concat(f_name) from fruits group by s_id;
    +------+-------+------------------------+
    | s_id | total | group_concat(f_name)   |
    +------+-------+------------------------+
    |  101 |     3 | apple,blackberry,apple |
    |  102 |     1 | orange                 |
    +------+-------+------------------------+
    2 rows in set (0.00 sec)

2.12.2 使用having过滤分组

mysql> select s_id, count(*) as total from fruits group by s_id having count(*)>1;
    +------+-------+
    | s_id | total |
    +------+-------+
    |  101 |     3 |
    +------+-------+
    1 row in set (0.00 sec)

2.12.3 在group by子句中使用with rollup

使用with rollup关键自之后，最后一行会记录一条查询出的所有记录的总和，可以用来统计记录数量。

mysql> select s_id,count(*) as total from fruits group by s_id with rollup;
    +------+-------+
    | s_id | total |
    +------+-------+
    |  101 |     3 |
    |  102 |     1 |
    | NULL |     4 |
    +------+-------+
    3 rows in set (0.01 sec)

2.12.4 多字段分组

group by name1,name2