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

2019-07-24 08:58:38来源:博客园 阅读 ()

新老客户大回馈,云服务器低至5折

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

单表查询语法

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;

     

 


原文链接:https://www.cnblogs.com/wenxin1120/p/11074003.html
如有疑问请与原作者联系

标签:

版权申明:本站文章部分自网络,如有侵权,请联系:west999com@outlook.com
特别注意:本站所有转载文章言论不代表本站观点,本站所提供的摄影照片,插画,设计作品,如需使用,请与原作者联系,版权归原作者所有

上一篇:干货|MySQL两千万数据大表优化过程,三种解决方案!

下一篇:深入了解MySQL的索引