数据库（续）

一、DQL查询表中数据

数据查询语言（Data Query Language，DQL）：DQL主要用于数据的查询，其基本结构是使用SELECT子句，FROM子句和WHERE子句的组合来查询一条或多条数据。

DQL是实际开发过程中使用最多的。

1. 数据准备

# 创建员工表 
表名 emp 
表中字段： 
eid 员工id，int 
ename 姓名，varchar(20) 
sex 性别，char(1) 
salary 薪资，double(7,1)
hire_date 入职时间，date
dept_name 部门名称，varchar(20)

# 创建bjsxt02数据库
create database bjsxt02;

# 选择bjsxt02
use bjsxt02;

# 创建员工表
create table emp(
	eid int,
	ename varchar(20),
	sex char(1),
	salary double(7,1),
	hire_date date,
	dept_name varchar(20)
);

# 添加测试数据 
insert into emp VALUES(1,'孙悟空','男',7200,'2013-02-04','教学部'); 
insert into emp VALUES(2,'猪八戒','男',3600,'2010-12-02','教学部');
insert into emp VALUES(3,'唐僧','男',9000,'2008-08-08','教学部'); 
insert into emp VALUES(4,'白骨精','女',5000,'2015-10-07','市场部'); 
insert into emp VALUES(5,'蜘蛛精','女',5000,'2011-03-14','市场部'); 
insert into emp VALUES(6,'玉兔精','女',200,'2000-03-14','市场部'); 
insert into emp VALUES(7,'林黛玉','女',10000,'2019-10-07','财务部');
insert into emp VALUES(8,'黄蓉','女',3500,'2011-09-14','财务部'); 
insert into emp VALUES(9,'吴承恩','男',20000,'2000-03-14',null); 
insert into emp VALUES(10,'孙悟饭','男', 10,'2020-03-14','财务部');
insert into emp VALUES(11,'兔八哥','女', 300,'2010-03-14','财务部');
insert into emp VALUES(12,'Tom','男', null,'2010-03-14','财务部');

2. 简单查询

查询不会对数据库中的数据进行修改, 只是一种显示数据的方式。

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


-- 将查询的字段全部列出
select eid ,ename, sex, salary, hire_date, dept_name from emp;

-- 查询所有的字段可以使用 * , * 代表所有的字段
select * from emp;

查询所有的员工信息, 将字段名显示为中文
select 
	eid as '编号',      -- as可以省略
	ename as '姓名',
	sex as '性别',
	salary as '薪资',
	hire_date as '入职日期',
	dept_name as '部门名称'
from emp;

查询有多少个部门
-- 查询所有的部门(出现重复部门)
select dept_name from emp;

-- 使用去重查询 关键字 distinct
select distinct dept_name from emp;

将所有的员工薪资加1000显示
-- 支持算术运算符 + - * / % 的运算
select eid, ename, salary, salary+1000 from emp;

3. 条件查询

• 如果查询语句中没有设置条件, 就会查询所有的行信息
• 在实际应用中, 通常会指定查询的条件, 对记录进行过滤

* -- 语法格式：
select 列名 from 表名 where 条件;

3.1 比较运算符

> < <= >= = <> !=	大于 小于 小于等于 大于等于 等于 不等于
运算符	说明
between…and…	显示在某一区间的值
in(集合)	集合表示多个值，使用逗号分隔，例如name in (悟空,八戒); in中的每个数据都会作为一次条件，只要满足条件就会显示
like ‘%张%’	模糊查询
is null	查询某一列为null的值，注意，不能写 = null

3.2 逻辑运算符

运算符	说明
and &&	多个条件同时成立
or ||	多个条件任一成立
not	取反

案例1：精确查询

#1 查询员工姓名为黄蓉的员工信息 
select * from emp where ename = '黄蓉';

#2 查询薪水价格为5000的员工信息
select * from emp where salary = 5000;

#3 查询薪水价格不是5000的所有员工信息 
select * from emp where salary != 5000; 
select * from emp where salary <> 5000; 

#4 查询薪水价格大于6000元的所有员工信息 
select * from emp where salary > 6000; 

#5 查询薪水价格在5000到10000之间的员工信息 
select * from emp where salary >= 5000 and salary <= 10000; 
select * from emp where salary between 5000 and 10000; 

#6 查询薪水价格是3600或7200或者20000的员工信息
select * from emp where salary = 3600 or salary = 7200 or salary = 20000;
select * from emp where salary in (3600, 7200, 20000);

案例2:

模糊查询
模糊查询，通配符

通配符	说明
%	表示匹配任意多个字符
_	表示匹配任意一个字符

#1 查询含有'八'字的员工信息
select * from emp where ename like '%八%';
 
#2 查询以'孙'字开头的员工信息 
select * from emp where ename like '孙%';

#3 查询第二个字为'兔'的员工信息 
select * from emp where ename like '_兔%';

#4 查询没有部门的员工信息 
select * from emp where dept_name is null;

#5 查询有部门的员工信息
select * from emp where dept_name is not null;

4. 排序

通过order by子语句, 可以将查询出的结果进行排序(排序只是显示效果, 并不会影响真实的数据）。

-- 语法格式：
select 字段名 from 表名 [where 条件] order by 字段名[asc | desc];
-- asc  升序(默认)
-- desc 降序

4.1 单列排序

只按照某一个字段进行排序。

案例:

​ 查询所有的员工信息, 使用salary进行排序。

-- 升序排序(默认 asc)
select * from emp order by salary;

-- 降序排序(desc)
select * from emp order by salary desc;

4.2 组合排序

同时对多个字段进行排序, 如果第一个字段相同, 就按照第二个字段排序, 以此类推。

案例:

​ 查询所有的员工信息, 使用salary升序排列,salary相同按照入职日期降序排序。

-- 组合排序
select * from emp order by salary, hire_date desc;

5. 函数

MySQL中提供了大量函数来简化用户对数据库的操作，比如字符串的处理、日期的运算、数值的运算等等。使用函数可以大大提高SELECT语句操作数据库的能力，同时也给数据的转换和处理提供了方便。

函数只是对查询结果中的数据进行处理，不会改变数据的值。

单行函数：:

单行函数是指对每一条记录的值进行计算，并得到相应的计算结果，然后返回给用户，也就是说，每条记录作为一个输入参数，经过函数计算得到每条记录的计算结果。

常用的单行函数主要包括字符串函数、数值函数、日期与时间函数、流程函数以及其他函数。
使用单行函数, 是对行中字段的操作, 操作多少行, 返回多少行数据。

多行函数：:

我们之前做的查询都是横向查询，它们都是根据条件一行一行的进行判断，而使用多行函数查询是纵向查询，它是对某一列的值进行计算，然后返回一个单一的值。

• 多行函数会忽略null空值。
• 多行函数也称为分组函数, 聚合函数。

使用多行函数，是对某一列的值进行计算，然后返回一个单一的值

5.1 单行函数

大小写处理函数

函数	描述	实例
LOWER(s)|LCASE(s)	将字符串 s 转换为小写	将字符串 OLDLU转换为小写：SELECT LOWER("OLDLU"); -- oldlu
UPPER(s)|UCASE(s)	将字符串s转换为大写	将字符串 oldlu转换为大写：SELECT UPPER("oldlu"); -- OLDLU

字符处理函数

函数	描述	实例
LENGTH(s)	返回字符串 s 的长度	返回字符串oldlu的字符数SELECT LENGTH("oldlu"); -- 5;
CONCAT(s1,s2…sn)	字符串 s1,s2 等多个字符串合并为一个字符串	合并多个字符串SELECT CONCAT("sxt ", "teacher ", "oldlu"); -- sxt teacher oldlu;
LPAD(s1,len,s2)	在字符串 s1 的开始处填充字符串 s2，使字符串长度达到 len	将字符串 x 填充到 oldlu字符串的开始处：SELECT LPAD('oldlu',8,'x'); -- xxxoldlu
LTRIM(s)	去掉字符串 s 开始处的空格	去掉字符串 oldlu开始处的空格：SELECT LTRIM(" oldlu") ;-- oldlu
REPLACE(s,s1,s2)	将字符串 s2 替代字符串 s 中的字符串 s1	将字符串 oldlu 中的字符 o 替换为字符 O：SELECT REPLACE('oldlu','o','O'); -- Oldlu
REVERSE(s)	将字符串s的顺序反过来	将字符串 abc 的顺序反过来：SELECT REVERSE('abc'); -- cba
RPAD(s1,len,s2)	在字符串 s1 的结尾处添加字符串 s2，使字符串的长度达到 len	将字符串 xx填充到 oldlu字符串的结尾处：SELECT RPAD('oldlu',8,'x'); -- oldluxxx
RTRIM(s)	去掉字符串 s 结尾处的空格	去掉字符串 oldlu 的末尾空格：SELECT RTRIM("oldlu "); -- oldlu
SUBSTR(s, start, length)	从字符串 s 的 start 位置截取长度为 length 的子字符串	从字符串 OLDLU中的第 2 个位置截取 3个 字符：SELECT SUBSTR("OLDLU", 2, 3); -- LDL
SUBSTRING(s, start, length)	从字符串 s 的 start 位置截取长度为 length 的子字符串	从字符串 OLDLU中的第 2 个位置截取 3个 字符：SELECT SUBSTRING("OLDLU", 2, 3); --LDL
TRIM(s)	去掉字符串 s 开始和结尾处的空格	去掉字符串 oldlu 的首尾空格：SELECT TRIM(' oldlu ');-- oldlu

#1 查询emp表所有数据, 将eid, ename, sex显示格式为 编号: x 姓名: xx 性别: x
select concat('编号:', eid), concat('姓名:', ename), concat('性别:', sex) from emp;

#2 查询emp表所有数据, 将ename第二个字符都换为 某
select eid, insert(ename, 2, 1, 某'), sex from emp;

#3 查询emp表所有数据, 显示ename的长度
select eid, ename, length(ename), sex from emp;

#4 查询emp表所有数据, 将 ename有英文的改为都是大写
select eid, ename, upper(ename), sex from emp;

#5 查询emp表所有数据, 将 ename有英文的改为都是小写
select eid, ename, lower(ename), sex from emp;

#6 查询emp表所有数据, ename只显示姓
select eid, ename, substring(ename, 1, 1), sex from emp;

5.1.2 数值函数

函数	描述	实例
ABS(num)	返回num的绝对值	SELECT ABS(-1) -- 返回1
CEIL(num)	返回大于num的最小整数（向上取整）	SELECT CEIL(1.5) – 返回2
FLOOR(num)	返回小于num的最大整数（向下取整）	SELECT FLOOR(1.5) -- 返回1
MOD(num1, num2)	返回num1/num2的余数（取模）	SELECT MOD(5,2) -- 1
PI()	返回圆周率的值	SELECT MOD(5,2) -- 1
POW(num,n)/POWER(num, n)	返回num的n次方	SELECT POW(2,3) – 8
RAND(num)	返回0~1之间的随机数	SELECT RAND() --0.93099315644334
ROUND(num, n)	返回x四舍五入后的值，该值保留到小数点后n位	SELECT ROUND(1.23456,3) --1.235
TRUNCATE(num, n)	返回num被舍去至小数点后n位的值（与 ROUND 最大的区别是不会进行四舍五入）	SELECT TRUNCATE(1.23456,3) -- 1.234

MySQL提供了一张虚拟表，该表名为“dual”，是MySQL为了满足用“SELECT ••• from•••”的习惯而增设的一张虚拟表。 在使用dual表时，如果没有where子句，则可以省略“from dual”, 没有实际意义, 查询的字段不属于任何表的时候, 就可以使用dual这张虚拟表。

select abs(-1), ceil(3.2), floor(3.7), round(3.5), mod(10,3), pi(), pow(2,5), sqrt(25) from dual;
select abs(-1), ceil(3.2), floor(3.7), round(3.5), mod(10,3), pi(), pow(2,5), sqrt(25);

5.1.3 日期与时间函数

函数	描述	实例
CURDATE()	返回当前日期	SELECT CURDATE(); – 2022-07-20
CURTIME()	返回当前时间	SELECT CURTIME(); -> 19:59:02
NOW()	返回当前日期和时间	SELECT NOW() -> 2022-07-20 20:57:43
SYSDATE()	返回该函数执行时的日期和时间	SELECT SYSDATE() -> 2022-07-20 20:57:43
DAYOFYEAR(date)	返回日期date为一年中的第几天	SELECT DAYOFYEAR('2011-11-11 11:11:11') ->315
WEEK(date)/WEEKOFYEAR(date)	返回日期date为一年中的第几周	SELECT DAYOFWEEK('2011-11-11 11:11:11') ->6
DATEDIFF(date1, date2)	返回起始日期date1与结束日期date2之间的间隔天数	SELECT DATEDIFF('2001-01-01','2001-02-02') -> -32
DATE()	从日期或日期时间表达式中提取日期值	SELECT DATE("2017-06-15"); -> 2017-06-15
DAY(d)	返回日期值 d 的日期部分	SELECT DAY("2017-06-15"); -> 15
MONTH(d)	返回日期d中的月份值，1 到 12	SELECT MONTH('2011-11-11 11:11:11') ->11
YEAR(d)	返回年份	SELECT YEAR("2017-06-15"); -> 2017

select curdate(), curtime(), now(), sysdate();  
select curdate(), curtime(), now(), sleep(2), sysdate();  

5.1.4 流程控制函数

间隔类型	描述
IF(condition, t, f)	如果条件condition为真，则返回t，否则返回f
IFNULL(value1, value2)	如果value1不为null，则返回value1，否则返回value2
NULLIF(value1, value2)	如果value1等于value2，则返回null，否则返回value1
CASE WHEN [condition1] THEN result1 [WHEN [condition2] THEN result2 …] [ELSE result] END	如果条件condition1为真，则返回result1，···，否则返回result

#1 查询emp表所有数据, 薪资 >= 10000 高工资  其他 低工资
select eid, ename, salary, if(salary >= 10000, '高工资', '低工资') from emp;

#2 查询emp表所有数据, 计算出员工的年薪 薪资*12 加年终奖(每人30k)
select eid, ename, salary, salary * 12+30000 '年薪' from emp;  -- 需要考虑null
select eid, ename, salary, ifnull(salary, 0) * 12+30000 '年薪' from emp;

#3 查询emp表所有数据, 薪资 >=3000 加把劲  >=5000 加油哦  >=9000 坚持住 >= 15000 优秀  其他 不及格
select eid, ename, salary, 
	case
		when salary >= 15000 then '优秀'
		when salary >= 9000 then '坚持住'
		when salary >= 5000 then '加油哦'
		when salary >= 3000 then '加把劲'
		else '努力奋斗吧骚年'
	end
from emp;

5.1.5 其它函数

函数	描述
DATABASE()	返回当前数据库名
VERSION()	返回当前MySQL的版本号
USER()	返回当前登录的用户名
INET_ATON(IP)	返回IP地址的数字表示
INET_NTOA	返回数字代表的IP地址
PASSWORD(str)	实现对字符串str的加密操作
FORMAT(num, n)	实现对数字num的格式化操作，保留n位小数
CONVERT(data, type)	实现将数据data转换成type类型的操作

select database(),user(),version(),inet_aton("192.168.10.1"),inet_ntoa(3232238081);

5.2 多行函数

• 多行函数会忽略null空值。
• 多行函数也称为分组函数, 聚合函数。

多行函数	作用
count(字段)	统计指定列不为null的记录行数
sum(字段)	计算指定列的数值和
max(字段)	计算指定列的最大值
min(字段)	计算指定列的最小值
avg(字段)	计算指定列的平均值

#1 查询员工的总数 
-- 使用某个字段查询，聚合函数会忽略null, 需要注意为null的字段
select count(eid) from emp;  
-- 所有字段匹配查询
select count(*) form emp; 
-- 增加一列
select 1 from emp;	
-- 效率更高推荐使用		  
select count(1) from emp;

#2 查看员工总薪水、最高薪水、最小薪水、薪水的平均值, 显示为总薪水, ... 
select 
	sum(salary) '总薪水',
	max(salary) '最高薪水',
	min(salary) '最小薪水',
	avg(salary) '平均薪水'
from emp;

#3 查询薪水大于4000员工的个数 
select count(1) from emp where salary > 4000;

#4 查询部门为'教学部'的所有员工的个数 
select count(1) from emp where dept_name = '教学部';

#5 查询部门为'市场部'所有员工的平均薪水
select avg(salary) from emp where dept_name = '市场部';

#6 查询部门的个数
select dept_name from emp;  -- 9个
select count(dept_name) from emp; -- 8个
-- 部门去重之后， 统计个数
select count(distinct dept_name) from emp; -- 3个

6. 分组

分组查询指的是使用group by语句, 对查询的信息进行分组, 相同数据作为一组。

语法格式：

select 分组字段/聚合函数 from 表名 group by 分组字段 [having 条件];

需求1: 通过性别分组.

# 按照性别分组查询
select * from emp group by sex;    -- 能查到结果, 但是没有意义
select sex from emp group by sex;  -- 正确操作

注意事项:

• 分组时可以查询要分组的字段, 或者使用聚合函数进行统计操作
• 查询其他字段没有意义

案例1：

#1 查询每个部门的名称
select dept_name from emp group by dept_name;

#2 查询每个部门名称及部门的平均薪资 
select dept_name, avg(salary) from emp group by dept_name;
 
#3查询每个部门名称及部门的平均薪资, 部门名称不能为空
select dept_name, avg(salary) from emp where dept_name is not null group by dept_name;

案例2：

#4 查询每个部门名称及部门的平均薪资 , 只显示平均工资在4000以上的 
select dept_name, avg(salary) from emp where avg(salary) > 4000 group by dept_name;
-- Invalid use of group function  报错

分析:

1. 需要在分组后, 对数据进行过滤, where的作用是在分组前过滤
2. select语句的执行顺序
   from – where – group by – having –- select – order by
3. 分组操作中的having子语句, 适用于对分组后的数据进行过滤的, 作用类似于where

#4 查询每个部门名称及部门的平均薪资, 只显示平均工资在4000以上的 
select dept_name, avg(salary) from emp group by dept_name having avg(salary) > 4000;

where和having的区别。

过滤方式	特点
where	分组之前的过滤后边不能写多行函数
having	分组之后的过滤后边可以写多行函数

案例3：

#1 统计每个部门中的最小工资, 列出最小工资小于4000的部门名称及最小薪资
-- 每个部门的最小工资
select dept_name, min(salary)  from emp group by dept_name; 
select dept_name, min(salary)  from emp group by dept_name having min(salary) < 4000;

#2 统计平均工资大于6000的部门名称 
-- 每个部门的平均工资
select dept_name, max(salary)  from emp group by dept_name;
select dept_name, max(salary)  from emp group by dept_name having max(salary) > 6000;

#3 统计人数小于4个人部门的平均工资
-- 每个部门的平均工资和人数
select dept_name, avg(salary), count(1) from emp group by dept_name;  
select dept_name, avg(salary), count(1) from emp group by dept_name having count(1) < 4;

#4 统计每个部门最高工资, 排除最高工资小于10000的部门
-- 每个部门的最高工资
select dept_name, max(salary) from emp group by dept_name
select dept_name, max(salary) from emp group by dept_name having max(salary) >= 10000;

7. limit关键字

作用:

• limit是限制的意思, 限制返回的查询结果的函数(通过limit函数，控制查询返回多少行数据)
• limit 语法是 MySql的方言, 用来完成分页

语法格式：

select 字段1, 字段2 ... from 表名 limit offset, length;

参数说明

1. offset 起始行数, 从0开始, 如果省略则默认从0开始, 0代表MySQL中第一条数据
2. length 返回的行数

#1 查询emp表中的前5条数据
select * from emp limit 5;   -- 不指定从哪行还是, 默认从0开始
select * from emp limit 0, 5;

#2 查询emp表中 从第4条开始, 查询6条
select * from emp limit 3, 6; -- 从0开始, 所以第四条数据为3

希望本篇博客文章能帮助你更好地理解数据库的基本概念和基本操作语法。如有任何问题或疑惑，请随时提问。