查询⼀个时间范围内每天某个字段出现最⼤值、最⼩值的时间
按时间分组_mysql查询...
创建表
create table employee1(
`no` int comment "员⼯编号",
`name` varchar(20) comment "姓名",
`job` varchar(40) comment "职位",
`mgr` int comment "上司id",
`hiredate` date comment "雇佣时间",
`sal` double(10, 2) comment "⼯资",
`comm` double(10, 2) comment "奖⾦",
`deptno` int comment "部分id"
) charset=utf8;
插⼊数据
insert into employee values
(7369,"smith", "clerk", 7902, "1981-03-12", 800.00, NULL, 20),
(7499,"allen", "salesman", 7698, "1982-03-12", 1600.00, 300.00, 30),
(7521,"ward", "salesman", 7698, "1983-03-12", 1250.00, 500.00,30) ,
(7566,"jones" ,"manager",7839,"1981-03-12", 2975.00, NULL, 20),
(7654,"martin","salesman", 7698,"1981-03-12",1250.00, 1400.00, 30),
(7698,"blake", "manager", 7839,"1981-03-12",2850.00,NULL ,30),
(7782,"clark", "manager", 7839,"1985-03-12",2450.00,NULL,10),
(7788,"scott", "analyst" ,7566,"1981-03-12", 3000.00, NULL, 20),
(7839,"king", "president", NULL, "1981-03-12", 5000.00, NULL, 10),
(7844,"turner", "salesman", 7698, "1989-03-12", 1500.00, 0.00, 30) ,
(7876,"adams", "clerk", 7788, "1998-03-12", 1100.00, NULL, 20) ,
(7900,"james" ,"clerk", 7698, "1997-03-12", 950.00,NULL, 30),
(7902,"ford", "analyst",7566,"0000-00-00",3000.00,NULL, 20) ,
(7934,"miller", "clerk" ,7782, "1981-03-12", 1300.00,NULL, 10);
1、简单查询
#1、查询所有字段
select * from employee;
#2、避免重复数据
select job from employee;
select distinct job from employee;
#3、⼀年的薪资和 as ⽤法
select name,sal*12 from employee;
select name as "姓名",sal*12 as "年薪" from employee;
# 4、格式化显⽰
select concat(name,"的年薪为：",sal*12) "薪资" from employee;
2、条件查询
#1、单条件查询
select name from employee where job = "clerk";
#2、多条件查询
#查询⼯资⼤于800的所有⽂员
select name,job from employee where job ="clerk" and sal > 800;
#查询所有⽂员和销售
mysql删除重复的数据保留一条select name,job from employee where job = "clerk" or job = "salesman";
#查询⽂员以外的所有⼈员
select name,job from employee where job != "clerk";
#3、范围查(between and 包含)
select name,sal from employee where sal between 1000 and 2000;
等价于
select name,sal from employee where sal >=1000 and sal <=2000;
#4、is null 查询
#null 代表没有设置值，空字符串代表设置了值，但是值只是⼀个空字符串⽽已
#查询有奖⾦的员⼯
select name,comm from employee where comm is not null;
# 5 in 关键字查询所有销售⼈员和⽂员
select name,job from employyee where job in ("salesman","clerk");
select name,job from employyee where job ="salesman" or job = "clerk";
#查询⾮销售⼈员和⽂员
select name,job from employyee where job not in ("salesman","clerk");
select name,job from employyee where job !="salesman" and job != "clerk";
select name,job from employyee where not job ="salesman" and not job = "clerk";
# 6、带like 的关键字查询
like ⽀持的通配符：
1) "_" 表⽰匹配单个字符
2）"%" 表⽰可以匹配任意长度字符串
# 查询以A 开头的员⼯
select name from employee where name like "A%";
#包含A的员⼯
select name from employee where name like "%A%";
#以n结尾
select name from employee where name like "%n";
#第⼆个字符为m的员⼯
select name from employee where name like "_m%";
#不包含m的员⼯
select name from employee where name not like "%m%";
select name from employee where not name like "%m%";
3、排序与限制查询
# asc 升序；desc 降序默认：升序
select * from employee where condition order by field1[asc|desc],field2[asc|desc];
#限制记录查询 limit 数据分页中很常见
# offset_start 表⽰起始偏移量（从第⼏⾏开始） row_count 表⽰显⽰⾏数
select field1,field2,...fieldn from table name where condition limit offset_start,row_count;
# 将奖⾦为null的所有记录，从第⼆⾏开始，返回两⾏数据
select * from employee WHERE comm is null LIMIT 2,2;
4、统计函数与分组
#数据之所以可以分组，是因为表中相关字段存在重复值。使⽤ group by 是想把记录分为若⼲组，#然后分别调⽤聚集函数取做些统计⼯作
#count() 统计表中记录的条数
count(*) 统计所有记录，不管是null 还是⾮null值
count(field) 指定字段统计值，忽略null
#avg() 计算字段平均值
#sum() 字段求和
#max() 字段最⼤值
#min(）字段最⼩值
#注意
没有记录，count 返回 0，其他函数返回 null
#分组
#按照⼯种分组，单独计算平均⼯资
select job,avg(sal) from employee group by job;
#想要分组时还显⽰name,group_concat 显⽰每个分组中的字段和指定字段
#案例：
select job,group_concat(name),avg(sal) from employee group by job;
select deptno,GROUP_CONCAT(name, ":" ,sal) from employee GROUP BY deptno;
#多个字段分组
#先按照部门，在按照雇佣⽇期分组
SELECT deptno,hiredate from employee GROUP BY hiredate,deptno;
#having⼦句
where⼦句⽆法与聚合函数⼀起使⽤，where⼦句后，跟的是数据表⾥存在的字段
having ⼦句可以让我们筛选分组后的各组数据
语法为：
select column_name,aggregate_function(column_name)
from table_name
where column_name operate value
group by column_name
having aggregate_function(column_name) operator value;
5、多表设计
1、通过id 关联
优点：灵活，并发性⾼
缺点：数据完整性很难保证，需要⾃⼰写代码保证⼊库数据的完整性
2、通过外键关联
优点：数据⼀致性，数据删除后，⼀块删除，保证数据的可靠性
缺点：会影响性能
创建部门表，并插⼊数据
create table dept(
`deptno` int comment "部门编号",
`name` varchar(20) comment "部门名称",
`1oc` varchar(40) comment "所在城市"
) charset=utf8;
insert into dept values
(10,"accounting", "new_ york"),
(20, "research", "dalas") ,
(30,"sales", "beijing"),
(40,"financial", "shanghai");
6、连接
对于所有连接类型⽽⾔，就是将符合关键字 on 后条件的数据匹配为⼀条记录出现在结果集中1、内连接（innner join）保留关系中匹配数据
select field1,...fieldn form table1 inner join table2 [inner join tablen]
on join_condition
案例：select * from employee join dept on employee.deptno = dept.deptno;
2、外连接（outer join）
select field1,...fieldn from table [left|right|][outer] join table2 on join_condition
select * from employee left join dept using(deptno);#左连接，以左表为参考表
3、union
1）多个 select 语句中，第⼀个select 语句字段名被⽤于结果集中
2）select 字段数⼀致，跟数据类型⽆关
3）union 删除重复，union all 全部返回
select ...
UNION [all|distinct]
select ...
7、⼦查询
当⼀个查询是另⼀个查询的条件时，称之为⼦查询。
有经验的开发者会事先通过 select count(*) 查看表的记录数
#⼦查询会返回类型：单⾏单列、单⾏多列
单⾏单列
#查询雇员表中⼯资⽐smith 还要⾼的全部雇员的信息
select * from employee where sal > (select sal from employee where name = "smith");
单⾏多列
#查询雇员中⼯资和职位与Smith⼀样的全部雇员信息
select name,sal,job from employee where (sal,job) = (select sal,job from employee where name="smith");
#多⾏单列(in /any/all 关键字)
查询雇员表中的数据记录，这些数据部门编号必须在部门表中出现
select * from employee where deptno in (select deptno from dept)
#any 关键字
1. = any 和 in关键字⼀样
2. >any ⽐⼦查询中最⼩的还要⼤
3. <any ⽐⼦查询中最⼤数据⼩的数
#all 关键字
1. > all ⼤于所有
2. < all ⼩于所有
查询雇员姓名和⼯资，这些雇员的⼯资⾼于职位为manager的⼯资
select name,sal from employee where sal > all(select sal from employee where job = "manager");
多⾏多列
通过链接⾃查
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论