三夏时光

@三夏时光

一期一会,世当珍惜!

文章 22
朋友 10
来访 22

0-我自己

1-大神们

三夏时光

  • MySQL

MySQL核心知识之DQL数据查询语言与项目查询实战(一)

简介:细讲对数据表中的数据进行各种查询,以及项目实战查询

备用数据库 MySQL:

/*创建部门表*/ 
CREATE TABLE dept( 
deptnu INT PRIMARY KEY comment '部门编号', 
dname VARCHAR(50) comment '部门名称', 
addr VARCHAR(50) comment '部门地址' 
); 
/* 某个公司的员工表 */ 
CREATE TABLE employee( 
empno INT PRIMARY KEY comment '雇员编号', 
ename VARCHAR(50) comment '雇员姓名', 
job VARCHAR(50) comment '雇员职位', 
mgr INT comment '雇员上级编号', 
hiredate DATE comment '雇佣日期', 
sal DECIMAL(7,2) comment '薪资', 
deptnu INT comment '部门编号' 
)ENGINE=MyISAM DEFAULT CHARSET=utf8;  
/*创建工资等级表*/ 
CREATE TABLE salgrade( 
grade INT PRIMARY KEY comment '等级', 
lowsal INT comment '最低薪资', 
higsal INT comment '最高薪资' 
); 
/*插入dept表数据*/ 
INSERT INTO dept VALUES (10, '研发部', '北京'); 
INSERT INTO dept VALUES (20, '工程部', '上海'); 
INSERT INTO dept VALUES (30, '销售部', '广州'); 
INSERT INTO dept VALUES (40, '财务部', '深圳'); 
/*插入emp表数据*/ 
INSERT INTO employee VALUES (1009, '唐僧', '董事长', NULL, '2010-11-17', 50000, 10); 
INSERT INTO employee VALUES (1004, '猪八戒', '经理', 1009, '2001-04-02', 29750, 20); 
INSERT INTO employee VALUES (1006, '猴子', '经理', 1009, '2011-05-01', 28500, 30); 
INSERT INTO employee VALUES (1007, '张飞', '经理', 1009, '2011-09-01', 24500,10); 
INSERT INTO employee VALUES (1008, '诸葛亮', '分析师', 1004, '2017-04-19', 30000, 20); 
INSERT INTO employee VALUES (1013, '林俊杰', '分析师', 1004, '2011-12-03', 30000, 20); 
INSERT INTO employee VALUES (1002, '牛魔王', '销售员', 1006, '2018-02-20', 16000, 30); 
INSERT INTO employee VALUES (1003, '程咬金', '销售员', 1006, '2017-02-22', 12500, 30); 
INSERT INTO employee VALUES (1005, '后裔', '销售员', 1006, '2011-09-28', 12500, 30); 
INSERT INTO employee VALUES (1010, '韩信', '销售员', 1006, '2018-09-08', 15000,30); 
INSERT INTO employee VALUES (1012, '安琪拉', '文员', 1006, '2011-12-03', 9500, 30); 
INSERT INTO employee VALUES (1014, '甄姬', '文员', 1007, '2019-01-23', 7500, 10); 
INSERT INTO employee VALUES (1011, '妲己', '文员', 1008, '2018-05-23', 11000, 20); 
INSERT INTO employee VALUES (1001, '小乔', '文员', 1013, '2018-12-17', 8000, 20); 
/*插入salgrade表数据*/ 
INSERT INTO salgrade VALUES (1, 7000, 12000); 
INSERT INTO salgrade VALUES (2, 12010, 14000); 
INSERT INTO salgrade VALUES (3, 14010, 20000); 
INSERT INTO salgrade VALUES (4, 20010, 30000); 
INSERT INTO salgrade VALUES (5, 30010, 99990);  

1、mysql查询子句之一where条件查询

简介:详解where条件下的各种查询

  • 简单查询
select * from employee;
select empno,ename,job as ename_job from employee;
  • 精确条件查询
select * from employee where ename='后裔';
select * from employee where sal != 50000;
select * from employee where sal <> 50000;
select * from employee where sal > 10000;
  • 模糊条件查询
show variables like '%aracter%';
select * from employee where ename like '林%';
select * from employee where ename like '张_'; 
  • 范围查询
select * from employee where sal between 10000 and 30000;
select * from employee where hiredate between '2011-01-01' and '2017-12-1'; 
  • 离散查询
select * from employee where ename in ('猴子','林俊杰','小红','小胡'); 
  • 清除重复值
 select distinct job from employee;  
  • 统计查询(聚合函数):
count(code)或者count(*)
select count(*) from employee;
select count(ename) from employee;

sum() 计算总和
select sum(sal) from employee;

max() 计算最大值
select * from employee where sal= (select max(sal) from employee);

avg() 计算平均值
select avg(sal) from employee;

min() 计算最低值
select * from employee where sal= (select min(sal) from employee);

concat函数:起到连接作用
select concat(ename,' 是 ',job) as aaaa from employee; 

2、mysql查询子句之二group by分组查询(分组)

简介:详解group by的用法以及应用场景

作用:把行 按 字段 分组。

语法:group by 列1,列2....列N。

适用场合:常用于统计场合,一般和聚合函数连用。

举例:
select deptnu,count(*) from employee group by deptnu;
select deptnu,job,count(*) from employee group by deptnu,job;
select job,count(*) from employee group by job;

3、mysql查询子句之三having条件查询(筛选)

简介:详解having的用法以及应用场景

作用:对查询的结果进行筛选操作。

语法:having 条件 或者 having 聚合函数 条件

适用场合:一般跟在 group by 之后

举例:
select job,count(*) from employee group by job having job ='文员';
select deptnu,job,count(*) from employee group by deptnu,job having count(*)>=2;
select deptnu,job,count(*) as 总数 from employee group by deptnu,job having 总数>=2;

4、mysql查询子句之四order by排序查询(排序)

简介:详解order by的用法以及应用场景

作用:对查询的结果进行排序操作。

语法:order by 字段1,字段2 ..... desc(正序)/ asc(默认倒序)。

适用场合:一般用在查询结果的排序 。

举例:
select * from employee order by sal;
select * from employee order by hiredate;
select deptnu,job,count(*) as 总数 from employee group by deptnu,job having 总数>=2 order by deptnu desc;
select deptnu,job,count(*) as 总数 from employee group by deptnu,job having 总数>=2 order by deptnu asc;
select deptnu,job,count(*) as 总数 from employee group by deptnu,job having 总数>=2 order by deptnu;

顺序:where ---- group by ----- having ------ order by

5、mysql查询子句之五limit限制查询(限制)

简介:详解limit的用法以及应用场景

作用:对查询结果起到限制条数的作用。

语法:limit n,m n:代表起始条数值,不写默认为0;m代表:取出的条数。

适用场合:数据量过多时,可以起到限制作用。

举例:
select * from employee limit 4,5;

Comments | NOTHING

  游客,你好 修改资料

*邮箱和昵称必须填写