MySQL 中表的数据操作常用命令总结_sal (7,2)

---

前言

查询：用户根据不同的方式从数据库表中获取自己所需要的数据，是数据库操作中非常重要且非常频繁的。

1.创建新库 test3

create database test3;
1

2.在 test3 库中创建 emp(雇员表)
emp(雇员编号 empno
雇员名称 ename
雇员的职位 job
雇员领导 mgr
雇员入职日期 hiredate
雇员月薪 sal
雇员的津贴 comm
雇员部门编号 deptno)

-- 创建雇员表
create table emp(
	empno int(4) primary key,
	ename varchar(10),
	job varchar(9),
	mgr int(4),
	hiredate date,
	sal decimal(7,2),
	comm decimal(7,2),
	deptno int(2)
);
1
2
3
4
5
6
7
8
9
10
11

3.给表插入数据

-- 为 emp 员工表同时插入多条数据
insert into emp values (7369, 'Smith', 'clerk', 7902, '1980-12-17', 800, null, 20), 
												(7499, 'Allen', 'salesman', 7698, '1981-02-20', 1600, 300, 30), 
												(7521, 'Ward', 'salesman', 7698, '1981-02-22', 1250, 500, 30), 
												(7566, 'Jones', 'manager', 7839, '1981-04-02', 2975, null, 20), 
												(7654, 'Maritn', 'salesman', 7698, '1981-09-28', 1250, 1400, 30), 
												(7698, 'Blake', 'manager', 7839, '1981-05-01', 2850, null, 30), 
												(7782, 'Clark', 'manager', 7839, '1981-06-09', 2450, null, 10), 
												(7788, 'Scott', 'analyst', 7566, '1987-04-19', 3000, null, 20), 
												(7839, 'King', 'president', null, '1981-11-17', 5000, null, 10), 
												(7844, 'Turner', 'salesman', 7698, '1981-09-08', 1500, 0, 30), 
												(7876, 'Adams', 'clerk', 7788, '1987-05-23', 1100, null, 20), 
												(7900, 'James', 'clerk', 7698, '1981-12-03', 950, null, 30), 
												(7902, 'Ford', 'analyst', 7566, '1981-12-03', 3000, null, 20), 
												(7934, 'Miller', 'clerk', 7782, '1982-01-23', 1300, null, 10);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

一、单表查询

1.查询所有字段

语法：select 字段名1,字段名2... from 表名;

例如：查询雇员表中雇员姓名、编号、职位、领导号、入职时间、薪资、奖金、部门编号

-- 查询雇员表中雇员姓名、编号、职位、领导号、入职时间、薪资、奖金、部门编号
select ename,empno,job,mgr,hiredate,sal,comm,deptno from emp;
1
2

运行结果：

2.使用通配符*查询所有字段

语法：select * from 表名;

例如：查询雇员表中所有信息

-- 使用通配符 * 查看 emp 员工表
select * from emp;
1
2

运行结果：

3.查询指定字段

语法：select 字段名1,字段名2... from 表名;

例如：查询雇员表中雇员姓名、职位、部门号、薪资

-- 查询雇员表中雇员姓名、职位、部门号、薪资
select ename,job,deptno,sal from emp;
1
2

运行结果：

4.去重 distinct 的使用
语法：select distinct 字段名1,字段名2 ...from 表名;
例如：查询雇员的部门号

-- 查询雇员的部门号
select deptno from emp;
1
2

运行结果：

我们只需要查询雇员在哪几个部门号中，需要进行去重处理：

-- 查询雇员的部门号(去重)
select distinct deptno from emp;
1
2

运行结果：

distinct 使用到多个字段上
例如：查询雇员部门号及职位

• 去重前

-- 查询雇员部门号及职位（去重前）
select deptno,job from emp;
1
2

运行结果：

• 去重后

-- 查询雇员部门号及职位（去重后）
select distinct deptno,job from emp;
1
2

运行结果：

注意：distinct使用到多个字段上，只有多个字段的值都相同时才去重

5.查询中使用算术运算符

+ - * /(div) %(mod)
1

例如：查询雇员的年薪

-- 查询雇员的年薪
select sal*12 from emp;
1
2

运行结果：

6.给字段起别名

语法：select 字段名1 [as] 别名,字段名2 .....from 表名;

例如：查询雇员的年薪，别名为 yearsal

-- 查询雇员的年薪，别名为 yearsal
select sal*12 as yearsal from emp;
1
2

运行结果：

注意：如果别名中有空格特殊符号，需要使用单引号将别名包裹起来

例如：查询雇员的年薪，别名为 yearsal&年薪；或者 yearsal 年薪

-- 查询雇员的年薪，别名为 yearsal&年薪；或者 yearsal 年薪
select sal*12 'yearsal&年薪' from emp;
select sal*12 'yearsal 年薪' from emp;
1
2
3

运行结果：

二、对查询结果排序

• 升序默认的排序方式 asc
• 降序 desc

1.在单个字段中排序

语法：select 查询内容 from 表名 order by 字段 asc|desc;

例如：查询雇员信息按雇员薪资升序排序

-- 查询雇员信息按雇员薪资升序排序，默认排序为升序，asc 可省略
select * from emp order by sal asc;
or
select * from emp order by sal;
1
2
3
4

运行结果：

2.在多个字段中使用排序
语法：select 查询内容 from 表名 order by 字段名1 asc|desc,字段名2 asc|desc...;

例如：查询雇员信息，先按照雇员薪资升序排序，如果薪资相同则按入职日期降序排序

-- 查询雇员信息，先按照雇员薪资升序排序，如果薪资相同则按入职日期降序排序
select * from emp order by sal asc, hiredate desc;
1
2

运行结果：

注意：排序可以使用别名

例如：查询雇员的年薪，给年薪起别名 yearsal 按照年薪降序排序

-- 查询雇员的年薪，给年薪起别名 yearsal 按照年薪降序排序
select sal*12 yearsal from emp order by yearsal desc;
1
2

运行结果：

三、条件查询

条件查询：是用户按照一定条件查询，查询满足条件的部分记录

语法：select 查询内容 from 表 where 条件;

1.比较运算符的使用

= > <  >=  <= !=  <>
1

例如：

• 查询部门号为 20 的雇员信息

-- 查询部门号为 20 的雇员信息
select * from emp where deptno=20;
1
2

运行结果：

• 查询薪资大于等于 3000 的雇员信息

-- 查询薪资大于等于 3000 的雇员信息
select * from emp where sal>=3000;
1
2

运行结果：

- 查询雇员的姓名为 smith 的雇员信息
1

-- 查询雇员的姓名为 smith 的雇员信息
select * from emp where ename='smith';
1
2

运行结果：

注意：MySQL 默认的是不区分大小写的，如果需要区分大小写，则可以使用 binary 关键字

-- 查询雇员的姓名为 smith 的雇员信息(binary 的使用)
select * from emp where binary ename='smith';
1
2

运行结果：

select * from emp where binary ename='Smith';
1

运行结果：

2.[not]between and 的使用
可以查询某区间范围的记录

例如：

• 查询薪资在 1200-3000 之间的雇员信息

-- 查询薪资在 1200-3000 之间的雇员信息
select * from emp where sal between 1200 and 3000;
1
2

运行结果：

• 查询薪资不在 1200-3000 之间的雇员姓名、薪资

-- 查询薪资不在 1200-3000 之间的雇员姓名、薪资
select ename,sal from emp where sal not between 1200 and 3000;
1
2

运行结果：

3.[not] in
表示在指定集合中

语法：select 查询内容 from 表名 where 字段名 in(值1,值2,....);

例如：

• 查询雇员姓名是 smith 、scott、king 的雇员信息

-- 查询雇员姓名是 smith 、scott、king 的雇员信息
select * from emp where ename in ('smith','scott','king');
1
2

运行结果：

• 查询雇员姓名不是 smith 、scott、king 的雇员信息

-- 查询雇员姓名不是 smith 、scott、king 的雇员信息
select * from emp where ename not in ('smith','scott','king');
1
2

运行结果：

4.is null 或者 is not null
判断某个字段是否为空

例如：

• 查询雇员中，有津贴的雇员信息

-- 查询雇员中，有津贴的雇员信息
select * from emp where comm is not null;
1
2

运行结果：

• 查询雇员中，没有津贴的雇员信息

-- 查询雇员中，没有津贴的雇员信息
select * from emp where comm is null;
1
2

运行结果：

5.模糊查询
可以查询以XX开头 以XX结尾 包含XX 第几个是X

语法：

select 查询内容 from 表名  where 字段名 like '匹配字符%|_';
%:指任意长度的字符(可以有也可以没有)
_:匹配一个字符
1
2
3

例如：

• 查询雇员中雇员姓名以 S 开头的雇员信息

-- 查询雇员中雇员姓名以 S 开头的雇员信息
select * from emp where ename like 'S%';
1
2

运行结果：

• 查询雇员中雇员姓名以 S 结尾的雇员信息

-- 查询雇员中雇员姓名以 S 结尾的雇员信息
select * from emp where ename like '%S'；
1
2

运行结果：

• 查询雇员中雇员姓名包含 S 的雇员信息

-- 查询雇员中雇员姓名包含 S 的雇员信息
select * from emp where ename like '%S%';
1
2

运行结果：

• 查询雇员姓名第二个字符是 L 的雇员信息

-- 查询雇员姓名第二个字符是 L 的雇员信息
select * from emp where ename like '_L%';
1
2

运行结果：

6.条件中的逻辑运算符
1.and 指并且。在查询时候只有满足所有查询条件的记录才会被查询出来

语法：select 查询内容 from 表名 where 条件1 and 条件2 and....;

例如：查询雇员表中部门号为 20 ，职位为 clerk 的雇员姓名、部门编号、职位

-- 查询雇员表中部门号为 20 ，职位为 clerk 的雇员姓名、部门编号、职位
select ename,deptno,job from emp where deptno=20 and job='clerk';
1
2

运行结果：

2.or 指或者。在查询时候只要满足任意一个条件的记录就能被查询出来

语法：select 查询内容 from 表名 where 条件1 or 条件2 or....;

例如：查询雇员表中部门号为 20 ，或者职位为 clerk 的雇员姓名、部门编号、职位

-- 查询雇员表中部门号为 20 ，或者职位为 clerk 的雇员姓名、部门编号、职位
select ename,deptno,job from emp where deptno=20 or job='clerk';
1
2

运行结果：

四、分页查询

语法: select 查询内容 from 表名 where 条件 order by 字段名 asc|desc limit a,b;

注意：
1.limit 从哪条记录开始，每页显示的条数且默认第一条的值 0
2.如果 limit 与 order by 连用时候，先排序再分页

-- 从第 0 条记录开始，每页显示 3 条数据
select * from emp order by sal limit 0,3;
1
2

运行结果：

五、函数的使用

1.单行函数

a.字符函数

（1）concat() 拼接字符串

例如：查询雇员的信息，以指定格式输出雇员姓名：XX，薪资：XX，职位：XX，入职日期：XX，年薪：XX

-- 查询雇员的信息，以指定格式输出雇员姓名：XX，薪资：XX，职位：XX，入职日期：XX，年薪：XX
select concat('雇员姓名：',ename,'，薪资：',sal,'，职位：',job,'，入职日期：',hiredate,'，年薪：',sal*12) from emp;
1
2

运行结果：

（2）length() 计算字符串的长度

例如：查询雇员中雇员姓名长度是 5 的雇员信息

-- 查询雇员中雇员姓名长度是 5 的雇员信息
select * from emp where length(ename)=5;
1
2

运行结果：

（3）lower() upper() 转换大小写

例如：查询雇员姓名，大写的雇员姓名，小写的雇员姓名

-- 查询雇员姓名，大写的雇员姓名，小写的雇员姓名
select ename,upper(ename),lower(ename) from emp;
1
2

运行结果：

（4）replace() 在指定的字符串中，将某子串替换为新的字符串

语法：replace(目标字符串,查找的子串,新字符串)

例如：将 helloword 字符串中的 hello 替换为 hi

-- 将 helloword 字符串中的 hello 替换为 hi
select replace('helloword','hello','hi') from dual;
1
2

运行结果：

（5）substring() 截取子串
substring(目标字符串,开始位置,长度)

注意：开始索引是从 1 开始，不是 0

例如：获取雇员姓名的前四个字符

-- 获取雇员姓名的前四个字符
select substring(ename,1,4) from emp;
1
2

运行结果：

b.数值函数

（1）abs()

作用：取绝对值

例如：select abs(1),abs(-1);

运行结果：

（2）pi()

作用：获取圆周率

例如：select pi();

运行结果：

（3）mod()

作用：取模

例如：select mod(3,2);

运行结果：

（4）pow()

作用：求一个数的 n 次方

例如：select pow(3,2);

运行结果：

（5）ceil()，floor()

• ceil()：向上取整

• floor()：向下取整

例如：select ceil(5.43),floor(5.7);

运行结果：

（6）round()

• round(num)：返回四舍五入的整数

• round(num,n)：返回四舍五入 n 位小数

例如：select round(5.4),round(6.8),round(5.34,1),round(6.789,2);

运行结果：

（7）truncate()
truncate(num,n)，其中 n 的取值可以是 0,1,2… 如果 n 取值为 0 代表截取整数。

例如：select truncate(5.3,0),truncate(5.67,1),truncate(5.789,2);

运行结果：

（8）rand()

作用：获取浮点类型的随机数，范围 0-1.0 其中包括 0 但不包括 1

例如：select rand(),rand(),rand();

运行结果：

c.日期时间函数

（1）now() 获取当前日期和时间，包括年月日、时分秒

例如：select now();

运行结果：

（2）curdate() 获取当前日期，只包括年月日

例如：select curdate();

运行结果：

（3）curtime() 获取当前时间，只包括时分秒

例如：select curtime();

运行结果：

（4）sysdate() 获取函数执行时的日期和时间，now() 获取 SQL 执行时的日期和时间

例如：select sysdate(),now();

运行结果：

（5）dayofyear() 获取某个日期是所在年份的第几天，week() 获取某个日期是所在年份的第几周

例如：select dayofyear(now()),week(now());

运行结果：

（6）datediff() 计算两个日期之间的时间间隔

例如：计算 2019-1-1 距离现在时间间隔

select datediff('2021-1-1',now());
1

运行结果：

（7）date_add()，date_sub() 实现日期的加减运算
date_add(日期,时间间隔类型关键字interval 时间间隔类型对应的表达式 时间间隔类型)

例如：

• day_hour 1_12 代表 1 天 12 小时
• year_month 2_1 代表 2 年 1 个月

select date_add(now(),interval '2_1' year_month);
1

运行结果：

select date_sub(now(),interval 10 day);
1

运行结果：

d.流程控制函数

（1）if(条件,t,f) 如果条件成立返回 t，否则返回 f

例如：

select if(1>2,'1大于2','1小于2');
1

运行结果：

查询雇员的薪资，如果薪资>=3000 输入’高薪’ 否则’低薪’

select sal,if(sal>=3000,'高薪','低薪') '薪资水平' from emp;
1

运行结果：

（2）ifnull(值1,值2) 如果值 1 不为空则返回值1，否则返回值2

例如：查询雇员的年薪

select (sal+ifnull(comm,0))*12 from emp;
1

（3）nullif(值1,值2) 如果值1等于值2返回 null，否则返回值1

例如：select nullif(1,2),nullif(1,1);

运行结果：

(4) case 值

when 值1 then 结果1
when 值2 then 结果2
…
else 其他结果
end

例如:

select  case 1 when 1 then '结果是1'
		       when 2 then '结果是2'
		       else '其他结果'
               end
1
2
3
4

运行结果：

(5) case
when 条件 then 结果1
when 条件 then 结果2
…
else 其他结果
end

例如：查询雇员薪资，如果薪资>=3000返回 ‘高薪’ 否则 ‘低薪’

select sal,case when sal>=3000 
	then '高薪'
	else '低薪'
	end  '薪资水平'
    from emp;
1
2
3
4
5

运行结果：

2、多行函数

1.多行函数：操作一组数据(多行记录)，返回一个结果，也叫分组函数。

2.多行函数大多使用于统计

例如：

• 统计各部门中雇员的人数
• 统计各部门中最高最低薪资是多少

3.多行函数主要有:

（1）count() 统计表中记录的数目

count() 的使用：
a.统计表中记录的总数目count(*)

例如：查询雇员表中有多少条记录

select count(*) from emp;
1

运行结果：

b.count(exp) 统计 exp 值非空的记录数目

例如：查询雇员表中有多少位雇员有津贴

select count(comm) from emp;
1

运行结果：

c.count(distinct(exp)) 返回表达式 exp 的值不重复且非空的总记录数目

例如：

• 统计雇员表中有多少位雇员是领导

select count(distinct(mgr)) from emp;--统计的是除董事长外的领导人数
1

运行结果：

• 统计雇员表中所有领导
  ifnull(值1,值2) 如果值1不为 null 返回值1，否则返回值2

select count(distinct(ifnull(mgr,1))) from emp;
1

运行结果：

（2）sum()

a.sum(exp) 返回表达式值得总和

例如：select sum(sal) from emp;

运行结果：

b.sum(distinct(exp)) 返回不重复的表达式 exp 的总和

例如：select sum(sal),sum(distinct(sal)) from emp;

运行结果：

（3）avg()

a.avg(exp)：返回表达式值得平均值

例如：select avg(sal) from emp;

运行结果：

b.avg(distinct(exp))：返回不重复的表达式 exp 的平均值

例如：select avg(distinct(sal)) from emp;

运行结果：

（4）max()，min()

max(exp)：返回表达式值得最大值
min(exp)：返回表达式值得最小值

例如：select min(sal),max(sal) from emp;

运行结果：

六、分组统计

1.语法：

select 查询内容 from 表名
[where 条件]
[group by 分组字段名1,分组字段名2...]
[order by 字段名 asc|desc]
[limit]
1
2
3
4
5

2.使用举例

a.求每个部门的人数

select deptno,count(*) from emp group by deptno;
1

运行结果：

b.求每个部门的平均工资

select deptno,avg(sal) from emp group by deptno;
1

运行结果：

c.求每个部门中最高工资和人数

select deptno,max(sal),count(*) from emp group by deptno;
1

运行结果：

d.求每个岗位的人数

select job,count(*) from emp group by job;
1

e.显示每个部门不同岗位的人数

select deptno,job,count(*) from emp group by deptno,job;
1

3.注意

a.查询字段，如果没有在多行函数中包含，则必须是分组字段
select ename,job,sum(sal) from emp group by job;

b.如果没有 group by ,查询字段不能与多行函数一起查询

select sal,empno from emp; 合法

select sum(sal),empno from emp; 不合法

c.不允许在 where 条件中使用多行函数

七、having 子句

1.语法：

select 查询内容
   from 表名
   [where 条件]
   [group by 分组字段]
   [having 条件]
   [order by]

select from --where过滤---group by--having过滤
1
2
3
4
5
6
7
8

2.使用举例

a.每个部门不同岗位的人数，且人数大于2

select count() from emp where count()>2 group by deptno,job；错误

select deptno,job,count(*) from emp group by deptno,job having count(*)>2;
1

运行结果：

b.在 emp 表中列出工资最小值小于 2000 的职位
–查询每个职位的最低薪资

select job,min(sal) from emp group by job;
1

运行结果：

select job,min(sal) from emp group by job having min(sal)<2000;
1

运行结果：

c.列出平均工资大于1200 的部门和职位搭配组合
–求每个部门不同职位的平均工资

select avg(sal) from emp group by deptno,job;
1

运行结果：

select avg(sal) from emp group by deptno,job having avg(sal)>1200;
1

运行结果：