数据库单表查询

发表于 | 分类于

单表查询语法

1
2
3
4
5
6
SELECT 字段1,字段2... FROM 表名
                  WHERE 条件
                  GROUP BY field
                  HAVING 筛选
                  ORDER BY field
                  LIMIT 限制条数

关键字的执行优先级

1
2
3
4
5
6
7
8
FROM
WHERE
GROUP BY
HAVING
SELECT 
DISTINCT 
ORDER BY 
LIMIT
  1. 找到表：FROM
  2. 拿着WHERE指定的约束条件，去表中取出一条条记录
  3. 将拿到的记录进行分组GROUP BY ，没有GROUP BY 整体当成一组
  4. 将分组的结果进行having过滤
  5. 执行SELECT
  6. 去重DISTINCT
  7. 将结果按条件进行排序ORDER BY
  8. 限制结果的显示条数

简单查询

1
2
3
4
5
6
7
8
9
10
11
company.employee
    员工id      id                  int             
    姓名        emp_name            varchar
    性别        sex                 enum
    年龄        age                 int
    入职日期     hire_date           date
    岗位        post                varchar
    职位描述     post_comment        varchar
    薪水        salary              double
    办公室       office              int
    部门编号     depart_id           int

简单查询

1
2
3
4
5
6
SELECT id,name,sex,age,hire_date,post,post_comment,salary,office,depart_id 
    FROM employee;

SELECT * FROM employee;

SELECT name,salary FROM employee;

避免重复DISTINCT

1
SELECT DISTINCT post FROM employee;

通过四则运算

1
2
3
SELECT name, salary*12 FROM employee;
SELECT name, salary*12 AS Annual_salary FROM employee;
SELECT name, salary*12 Annual_salary FROM employee;

定义显示格式

1
2
3
4
5
6
7
#CONCAT() 函数用于连接字符串
SELECT CONCAT('姓名: ',name,'  年薪: ', salary*12)  AS Annual_salary 
   FROM employee;
   
#CONCAT_WS() 第一个参数为分隔符
SELECT CONCAT_WS(':',name,salary*12)  AS Annual_salary 
   FROM employee;

WHERE约束

where字句中可以使用：

  1. 比较运算符：> < >= <= <> !=
  2. between 80 and 100 值在10到20之间
  3. in(80,90,100) 值是10或20或30
  4. like ‘egon%’
    pattern可以是%或，
    %表示任意多字符     表示一个字符
  5. 逻辑运算符：在多个条件直接可以使用逻辑运算符 and or not
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
#1:单条件查询
    SELECT name FROM employee
        WHERE post='sale';
        
#2:多条件查询
    SELECT name,salary FROM employee
        WHERE post='teacher' AND salary>10000;

#3:关键字BETWEEN AND
    SELECT name,salary FROM employee 
        WHERE salary BETWEEN 10000 AND 20000;

    SELECT name,salary FROM employee 
        WHERE salary NOT BETWEEN 10000 AND 20000;
    
#4:关键字IS NULL(判断某个字段是否为NULL不能用等号，需要用IS)
    SELECT name,post_comment FROM employee 
        WHERE post_comment IS NULL;

    SELECT name,post_comment FROM employee 
        WHERE post_comment IS NOT NULL;
        
    SELECT name,post_comment FROM employee 
        WHERE post_comment=''; #注意''是空字符串，不是null
    #ps：
        #执行
        update employee set post_comment='' where id=2;
        #再用上条查看，就会有结果了

#5:关键字IN集合查询
    SELECT name,salary FROM employee 
        WHERE salary=3000 OR salary=3500 OR salary=4000 OR salary=9000 ;
    
    SELECT name,salary FROM employee 
        WHERE salary IN (3000,3500,4000,9000) ;

    SELECT name,salary FROM employee 
        WHERE salary NOT IN (3000,3500,4000,9000) ;

#6:关键字LIKE模糊查询
    #通配符’%’
    SELECT * FROM employee 
            WHERE name LIKE 'eg%';

    #通配符’_’
    SELECT * FROM employee 
            WHERE name LIKE 'al__';

分组查询GROUP BY

  1. 分组发生在where之后，即分组是基于where之后得到的记录而进行的
  2. 分组：将所有记录按照某个相同字段进行分类
  3. 可按照任意字段分组，分组后只能看到该字段的结果，想看组内信息，需要借助聚合函数
1
2
3
4
5
ALTER TABLE employee RENAME emp;

SELECT * FROM emp GROUP BY post;

SELECT post,count(id) FROM emp GROUP BY post;

GROUP BY + GROUP_CONCAT()

1
2
3
4
5
6
7
8
9
10
SELECT post,group_concat(name) FROM emp GROUP BY post;

#+----------------------------+-------------------------------------------------------+
#| post                       | group_concat(name)                                    |
#+----------------------------+-------------------------------------------------------+
#| operation                  | 张野,程咬金,程咬银,程咬铜,程咬铁                          |
#| sale                       | 歪歪,丫丫,丁丁,星星,格格                                 |
#| teacher                    | alex,wupeiqi,yuanhao,liwenzhou,jingliyang,jinxin,成龙  |
#| 老男孩驻沙河办事处外交大使    | egon                                                   |
#+----------------------------+-------------------------------------------------------+

GROUP BY + 聚合函数

1
2
# 按岗位分组，查看每个组有多少人
SELECT post,count(id) count FROM emp GROUP BY post;

聚合函数

聚合函数聚合的事组的内容，若没有分组，则默认为一组

1
2
3
4
5
6
7
SELECT COUNT(*) FROM employee;
SELECT COUNT(*) FROM employee WHERE depart_id=1;
SELECT MAX(salary) FROM employee;
SELECT MIN(salary) FROM employee;
SELECT AVG(salary) FROM employee;
SELECT SUM(salary) FROM employee;
SELECT SUM(salary) FROM employee WHERE depart_id=3;

HAVING 过滤

HAVING与WHERE不一样的地方在于：
！！！执行优先级从高到低：where > group by > having

  1. Where 发生在分组group by之前，因而Where中可以有任意字段，但是绝对不能使用聚合函数；
  2. Having发生在分组group by之后，因而Having中可以使用分组的字段，无法直接取到其他字段,可以使用聚合函数。
1
2
#查询各岗位的员工个数小于2的岗位名称、岗位内包含员工名字、个数
SELECT post,group_concat(name),count(id) FROM emp GROUP BY post HAVING count(id)<2;

查询排序 ORDER BY

1
2
3
4
5
6
7
8
9
#按单列排序 默认升序
SELECT * FROM employee ORDER BY salary;
SELECT * FROM employee ORDER BY salary ASC;
SELECT * FROM employee ORDER BY salary DESC;

#按多列排序:先按照age排序，如果年纪相同，则按照薪资排序
SELECT * from employee
        ORDER BY age,
        salary DESC;

限制查询记录数 LIMIT

1
2
3
4
5
6
7
8
SELECT * FROM employee ORDER BY salary DESC 
    LIMIT 3;                    #默认初始位置为0 

SELECT * FROM employee ORDER BY salary DESC
    LIMIT 0,5; #从第0开始，即先查询出第一条，然后包含这一条在内往后查5条

SELECT * FROM employee ORDER BY salary DESC
    LIMIT 5,5; #从第5开始，即先查询出第6条，然后包含这一条在内往后查5条

正则表达式查询

1
2
3
4
5
SELECT * FROM employee WHERE name REGEXP '^ale';

SELECT * FROM employee WHERE name REGEXP 'on$';

SELECT * FROM employee WHERE name REGEXP 'm{2}';