数据库 -- 单表的数据查询

单表查询的语法及关键字执行的优先级

单表查询语法

select distinct 字段一,字段二,... from 表名
                                where 条件
                                group by field
                                having 筛选
                                order by field
                                limit 限制条数

 关键字执行的优先级

　　1. 找到表: from

　　2. 拿着where指定的约束条件,去文件 / 表中取出一条条记录

　　3. 将取出的一条条记录进行分组group by , 如果没有group by ,则整体作为一组

　　4. 执行select  (distinct --> 去重)

　　5.  将分组的结果进行having过滤

　　6. 将结果按条件排序: order by

　　7. 限制结果的显示条数

 

 建表和数据的准备:

#创建表
create table employee(
id int not null unique auto_increment,
emp_name varchar(20) not null,
sex enum('male','female') not null default 'male', #大部分是男的
age int(3) unsigned not null default 28,
hire_date date not null,
post varchar(50),
post_comment varchar(100),
salary double(15,2),
office int, #一个部门一个屋子
depart_id int
);


#插入记录
#三个部门：教学，销售，运营
insert into employee(emp_name,sex,age,hire_date,post,salary,office,depart_id) values
('egon','male',18,'20170301','联合国外交大使',7300.33,401,1), #以下是教学部
('alex','male',78,'20150302','teacher',1000000.31,401,1),
('wupeiqi','male',81,'20130305','teacher',8300,401,1),
('yuanhao','male',73,'20140701','teacher',3500,401,1),
('liwenzhou','male',28,'20121101','teacher',2100,401,1),
('jingliyang','female',18,'20110211','teacher',9000,401,1),
('jinxin','male',18,'19000301','teacher',30000,401,1),
('成龙','male',48,'20101111','teacher',10000,401,1),

('歪歪','female',48,'20150311','sale',3000.13,402,2),#以下是销售部门
('丫丫','female',38,'20101101','sale',2000.35,402,2),
('丁丁','female',18,'20110312','sale',1000.37,402,2),
('星星','female',18,'20160513','sale',3000.29,402,2),
('格格','female',28,'20170127','sale',4000.33,402,2),

('张野','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门
('程咬金','male',18,'19970312','operation',20000,403,3),
('程咬银','female',18,'20130311','operation',19000,403,3),
('程咬铜','male',18,'20150411','operation',18000,403,3),
('程咬铁','female',18,'20140512','operation',17000,403,3)
;

 

一 . 查询的语法:

1. select 语句:

• 基础命令:
  • select * from 表名;    查看表中的所有数据
  • select 字段名,字段名,... from 表名;    查看表中的某几列数据
  • insert into 已建好的表名(字段,...) select 字段,... from 另一张表名;    从另一张表中导入需要的数据
• 重命名字段
  • select 字段名 as 新名字 from 表名;
  • select 字段名 新名字 from 表名;     
  • 因此加不加 as 的效果一样
• 去重:
  • select distinct 字段 from 表名;
  • 当查询的字段中存在相同的数据时,只会查询到不重复的数据
• 定义显示格式:
  • 使用函数
    • concat()  用于连接字符串
      • select concat('要拼接的字符串',字段名,'要拼接的字符串',字段名) from 表名;    
    • concat ws()  第一个参数为分隔符
      • select concat ws('类似于 | : - 的分隔符',字段名,字段名) from 表名;    将两个或多个字段通过分隔符连接起来
  • 结合case语句    (判断逻辑,相当于if条件判断句)

    •  
      select (
      case
      when 字段名1 = 'aaa' then
          字段名                                    # 如果字段名1的内容为aaa时,就返回字段名原来的数据
      when 字段名1 = 'bbb' then
          concat(字段名1,'wahaha')         # 如果字段名1的内容为bbb时,就将字段名和wahaha拼接起来返回
      else
          concat(字段名1,'shuangwaiwai')  # 如果字段名1的内容不为上面两个时,就将字段名和shuangwaiwai拼接起来返回
      end
      )   as new_name    # 给这字段起一个新名字
      from 表名 

 2. 通过四则运算查询

• 从一张员工月薪表中查询员工的年薪
  • select name,salary*12 from 表名;    乘法运算

3. where 筛选出所有符合条件的行

• 比较运算符:  < , > , <= , >= , <> , !=   (后两个为不等于)
• 关键字is : 判断某个字段是否为null,不能用等号,要用is
  • where 字段名 is null;    筛选字段为null的数据
  • where 字段名 is not null;    筛选字段名不为null的数据
• 关键字between 值 and 值
  • where 字段名 between 90 and 100;    筛选字段值在90到100的数据
  • 不支持这种写法: 90 < 字段名> 100
• 关键字in查询
  • where 字段名 in(80,90,100);    筛选字段值为80或90或100的数据
• 关键字like查询
  • % : 通配符,表示可以匹配任意长度的任意内容
    • select * from 表名 where 字段名 like 'xx%';    查询字段名是以xx开头的数据
    • select * from 表名 where 字段名 like '%xx';    查询字段名是以xx结尾的数据
  • _ : 通配符,表示可以匹配一个字符长度的任意内容
    • select * from 表名 where 字段名 like '林__';    查询出字段名是以 '林'开头,并且字符长度是3的数据

4. 逻辑运算 (与 或 非)

• 在多个条件下可以直接使用逻辑运算符: 优先级:  not > and > or

 

二 . 分组聚合

1. 分组:  group by 字段名;    根据某个字段分组

　　- 根据谁分组,可以求出这个组的总人数,最大值,最小值,平均值,求和,但是这个求出来的值只是和分组字段对应,并不和其他任何字段对应,这个时候查出来的所有其他字段都不生效

单独使用group by关键字分组
    select post from employee group by post;
    注意：我们按照post字段分组，那么select查询的字段只能是post，想要获取组内的其他相关信息，需要借助函数

group by关键字和group_concat()函数一起使用
    select post,group_concat(emp_name) from employee group by post;#按照岗位分组，并查看组内成员名
    select post,group_concat(emp_name) as emp_members from employee group by post;

group by与聚合函数一起使用
    select post,count(id) as count from employee group by post;#按照岗位分组，并查看每个组有多少人

 

2. 聚合函数

• 强调: 聚合函数聚合的是组的内容,若是没有组,则默认表为一组
• count(字段名)  求个数
  • select count(*) from 表名;    查询表中有多少行数据
• max  求最大值
  • select 字段名,max(age) from 表名 group by 字段名; 根据字段名分组,然后查询每个分组中年龄最大的一个
• min  求最小值
  • select min(age) from 表名;    查询表中年龄最小的一个
• sum  求和
  • select sum(age) from 表名;    对表中age这一列的数据求和
• avg  求平均值
  • select avg(age) from 表名;    查询表中年龄的平均值

 

三. having 过滤语句

• 执行优先级: where > group by > having
• where发生在group by之前,ywhere中可以有任意字段,所以不能用分组聚合函数
• 适合去筛选符合条件的某一组数据,而不是某一行数据
• having发生在分组group by之后,因而having中可以使用分组的字段,无法直接获取到其他字段,可以通过聚合函数来获取

  mysql> select post,group_concat(emp_name) from emp group by post having salary > 10000;
  error 1054 (42s22): unknown column 'salary' in 'having clause' #错误，分组后无法直接取到salary字段
      
  # 通过分组聚合获取值:
  查询各岗位平均薪资大于10000且小于20000的岗位名、平均工资
  mysql> select post,avg(salary) from employee group by post having avg(salary) > 10000 and avg(salary) <20000;
  +-----------+--------------+
  | post      | avg(salary)  |
  +-----------+--------------+
  | operation | 16800.026000 |

   

四. order by 排列语句

• 默认是升序(从小到大)  --> asc
• 降序(从大到小) --> desc

  # 按多列排序:先按照age排序，如果年纪相同，则按照薪资排序
  select * from employee order by age,salary desc;
  
  # 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资升序排列
  mysql> select post,avg(salary) from employee group by post having avg(salary) > 10000 order by avg(salary) asc;
  +-----------+---------------+
  | post      | avg(salary)   |
  +-----------+---------------+
  | operation |  16800.026000 |
  | teacher   | 151842.901429 |
  +-----------+---------------+
  2 rows in set (0.00 sec)

   

五. limit 限制查询的记录条数

• limit m,n : 从 m+1 项开始,取n项,如果不写m,m默认为0
• 另一种写法: limit n offset m

  # 从第0开始，即先查询出第0+1条，然后包含这一条在内往后查3条
  select * from employee order by salary desc limit 3;
  
  # 从第5开始，即先查询出第6条，然后包含这一条在内往后查5条
  select * from employee order by salary desc limit 5,5;