mysql数据库入门教程（4）:查询讲解大全

SQL脚本导入

开始

在进行查询之前，必须得有写好的sql文件吧。
先导入写好的sql脚本

打开sqlyang客户端
右击root@localhost

选择执行sql脚本

选择文件，点击执行,最后点击完成

刷新，发现主页面多啦一个数据库

介绍

myemployees：员工
l里面有四张表departments，employees，jobs，locations

该资源链接：
myemployees所在地链接
这是官方给出的资源

1基础查询

语法

select 查询列表 from 表名;

查询列表可以是：表中的字段，常量值，表达式，函数。
查询结果的表格是一个虚拟表格。

DESC 表名;
显示表结构

1查询表中的单个字段

#基础查询
USE myemployees;#进入数据库
SELECT last_name FROM employees;#查询名字

可以在左边点击表格字段，右边即显示出来。

2查询表中的多个字段

#基础查询
USE myemployees;#进入数据库
SELECT last_name,salary,email FROM employees;#查询名字，运行，邮箱

3查询表中的所有字段

#基础查询
USE myemployees;#进入数据库
SELECT * FROM employees;#查询所有

4 查询常量

#基础查询
USE myemployees;
SELECT 100;
SELECT 'john';

5.查询表达式

#基础查询
USE myemployees;
SELECT 100%98;

6.查询函数

USE myemployees;

 SELECT VERSION();

查询函数即调用该函数，并返回返回值。

7.为字段起别名
方便我们理解
①便于理解
②如果要查询的字段有重名的情况，使用别名可以区分开来
#方式一：使用as

SELECT last_name AS 姓,first_name AS 名 FROM employees;

SELECT 100%98 AS 结果;
#方式二：使用空格

SELECT last_name 姓,first_name 名 FROM employees;

如果别名中有特殊符号，如空格，则应该把别名加上引号。否则报错
#案例：查询salary，显示结果为 out put
SELECT salary AS “out put” FROM employees;

8 去重

#案例：查询员工表中涉及到的所有的部门编号
SELECT DISTINCT department_id FROM employees;

在select 后面加上DISTINCT
DISTINCT:截然不同的

如果不加distinct ，会出现大量重复数据。

9 +号的使用

mysql中的+号：
仅仅只有一个功能：运算符

select 100+90; 两个操作数都为数值型，则做加法运算
select ‘123’+90;只要其中一方为字符型，试图将字符型数值转换成数值型
如果转换成功，则继续做加法运算
select ‘john’+90; 如果转换失败，则将字符型数值转换成0
select null+10; 只要其中一方为null，则结果肯定为null

10 concat拼接
#案例：查询员工名和姓连接成一个字段，并显示为 姓名

#基础查询
USE myemployees;

SELECT CONCAT(last_name,first_name) AS 姓名 FROM employees;

显示出表employees的全部列，各个列之间用逗号连接，列头显示成OUT_PUT

SELECT
	CONCAT(`first_name`,',',`last_name`,',',`job_id`,',',IFNULL(commission_pct,0)) AS out_put
FROM
	employees;

因为空值
IFNULL(commission_pct,0) 如果commission为空则为0，如果不为空就是原值

SELECT IFNULL(commission_pct,0) AS 奖金率,commission_pct FROM employees;	

2条件查询

语法：

select 查询列表 from 表名 where 筛选条件

分类：
一、按条件表达式筛选
简单条件运算符：> < = != <> >= <=
二、按逻辑表达式筛选
逻辑运算符：
作用：用于连接条件表达式
&& || !
and or not
&&和and：两个条件都为true，结果为true，反之为false
||或or： 只要有一个条件为true，结果为true，反之为false
!或not： 如果连接的条件本身为false，结果为true，反之为false
三、模糊查询
like
between and
in
is null

1、按条件表达式筛选
#案例1：查询工资>12000的员工信息

USE myemployees;
#案例1：查询工资>12000的员工信息
SELECT * FROM employees WHERE salary>12000;

#案例2：查询部门编号不等于90号的员工名和部门编号

USE myemployees;
#案例2：查询部门编号不等于90号的员工名和部门编号
SELECT last_name,department_id FROM employees WHERE department_id<>90;

2.按逻辑表达式筛选
#案例1：查询工资在10000到20000之间的员工名、工资以及奖金

USE myemployees;
#案例1：查询工资z在10000到20000之间的员工名、工资以及奖金
SELECT last_name,salary, commission_pct FROM employees WHERE salary>=10000 AND salary<=20000;

#案例2：查询部门编号不是在90到110之间，或者工资高于15000的员工信息

USE myemployees;
#案例2：查询部门编号不是在90到110之间，或者工资高于15000的员工信息
SELECT
	*
FROM
	employees
WHERE
	NOT(department_id>=90 AND  department_id<=110) OR salary>15000;

3、模糊查询

like
between and
in
is null|is not null

#1.like

/*
特点：
①一般和通配符搭配使用
通配符：
% 任意多个字符,包含0个字符
_ 任意单个字符
*、

like通常和通配符搭配
%通配符 任意多个字符，包含0个。
_任意单个字符

#案例1：查询员工名中包含字符a的员工信息

USE myemployees;

#案例1：查询员工名中包含字符a的员工信息
SELECT 
	*
FROM
	employees
WHERE
	last_name LIKE '%a%';

#案例2：查询员工名中第三个字符为n，第五个字符为l的员工名和工资

USE myemployees;

#案例2：查询员工名中第三个字符为n，第五个字符为l的员工名和工资
SELECT
	last_name,
	salary
FROM
	employees
WHERE
	last_name LIKE '__n_l%';

#特殊情况 案例3：查询员工名中第二个字符为_的员工名

ESCAPE ‘$’ ：指定 美元符号为转义符号

USE myemployees;

#案例3：查询员工名中第二个字符为_的员工名

SELECT
	last_name
FROM
	employees
WHERE
	last_name LIKE '_$_%' ESCAPE '$';

last_name LIKE ‘KaTeX parse error: Expected group after '_' at position 1: _̲%' ESCAPE '’;
还可以写成
last_name LIKE '_%’ ;
使用\转义

#2.between and

/*
①使用between and 可以提高语句的简洁度
②包含临界值
③两个临界值不要调换顺序
*/

#案例1：查询员工编号在100到120之间的员工信息

USE myemployees;

#案例1：查询员工编号在100到120之间的员工信息

SELECT
	*
FROM
	employees
WHERE
	employee_id BETWEEN 100 AND 120;

#3.in

/*
含义：判断某字段的值是否属于in列表中的某一项
特点：
①使用in提高语句简洁度
②in列表的值类型必须一致或兼容
③in列表中不支持通配符
*/

#案例：查询员工的工种编号是 IT_PROG、AD_VP、AD_PRES中的一个员工名和工种编号
原始方法

SELECT
	last_name,
	job_id
FROM
	employees
WHERE
	job_id = 'IT_PROT' OR job_id = 'AD_VP' OR JOB_ID ='AD_PRES';

in

SELECT
	last_name,
	job_id
FROM
	employees
WHERE
	job_id IN( 'IT_PROT' ,'AD_VP','AD_PRES');

#4、is null

/*
=或<>不能用于判断null值
is null或is not null 可以判断null值

#案例1：查询没有奖金的员工名和奖金率

SELECT
	last_name,
	commission_pct
FROM
	employees
WHERE
	commission_pct IS NULL;

#案例1：查询有奖金的员工名和奖金率

SELECT
	last_name,
	commission_pct
FROM
	employees
WHERE
	commission_pct IS NOT NULL;

#安全等于 <=>
可以判断空值

#案例1：查询没有奖金的员工名和奖金率

SELECT
	last_name,
	commission_pct
FROM
	employees
WHERE
	commission_pct <=>NULL;

#案例2：查询工资为12000的员工信息

#案例2：查询工资为12000的员工信息
SELECT
	last_name,
	salary
FROM
	employees

WHERE 
	salary <=> 12000;	

IS NULL:仅仅可以判断NULL值，可读性较高，建议使用
<=> :既可以判断NULL值，又可以判断普通的数值，可读性较低

3排序查询

/*
语法：
select 查询列表
from 表名
【where 筛选条件】
order by 排序的字段或表达式;

特点：
1、asc代表的是升序，可以省略
desc代表的是降序
2、order by子句可以支持 单个字段、别名、表达式、函数、多个字段
3、order by子句在查询语句的最后面，除了limit子句
*/

#1、按单个字段排序

USE myemployees;
#1、按单个字段排序
SELECT * FROM employees ORDER BY salary DESC;

#2、添加筛选条件再排序

USE myemployees;
#案例：查询部门编号>=90的员工信息，并按员工编号降序

SELECT *
FROM employees
WHERE department_id>=90
ORDER BY employee_id DESC;

#3、按表达式排序
salary12(1+IFNULL(commission_pct,0)) 年薪计算

USE myemployees;
#案例：查询员工信息 按年薪降序
SELECT *,salary*12*(1+IFNULL(commission_pct,0))
FROM employees
ORDER BY salary*12*(1+IFNULL(commission_pct,0)) DESC;

#4、按别名排序

#4、按别名排序
#案例：查询员工信息 按年薪升序

SELECT *,salary*12*(1+IFNULL(commission_pct,0)) 年薪
FROM employees
ORDER BY 年薪 ASC;

#5、按函数排序
#案例：查询员工名，并且按名字的长度降序

USE myemployees;
#案例：查询员工名，并且按名字的长度降序

SELECT LENGTH(last_name),last_name 
FROM employees
ORDER BY LENGTH(last_name) DESC;

#6、按多个字段排序
#案例：查询员工信息，要求先按工资降序，再按employee_id升序

USE myemployees;
#案例：查询员工信息，要求先按工资降序，再按employee_id升序
SELECT *
FROM employees
ORDER BY salary DESC,employee_id ASC;

4常见函数

在学习高级查询之前，先学习下常见函数

概念：类似于java的方法，将一组逻辑语句封装在方法体中，对外暴露方法名
好处：1、隐藏了实现细节 2、提高代码的重用性
调用：select 函数名(实参列表) 【from 表】;

分类：
1、单行函数
如 concat、length、ifnull等
2、分组函数
功能：做统计使用，又称为统计函数、聚合函数、组函数

单行函数

分类：字符函数,数学函数,日期函数,其他函数,控制函数

一.字符函数

length:获取字节个数(utf-8一个汉字代表3个字节,gbk为2个字节)
concat 拼接字符串
substr #截取字符
instr 返回子串第一次出现的索引,如果找不到返回0
trim 去前后空格
upper #转大写
lower #转小写
lpad 左填充，指定元素填充，指定总长度
rpad 右填充，指定元素填充，指定总长度
replace 替换

USE myemployees;
#1.length
SELECT LENGTH('余登武');
#2.concat 拼接字符串

SELECT CONCAT(last_name,'-',first_name) AS 姓名 FROM employees;

#3 upper lower
SELECT UPPER('yu');
SELECT LOWER('YU');
SELECT CONCAT(UPPER(last_name),LOWER(first_name)) AS 姓名 FROM employees;#姓大写，名小写，并拼接

#4。substr  截取字符
SELECT SUBSTR('杨幂李冰冰范爷',3); #索引从1开始，截取索引3后面的字符 ，即截取出李冰冰范爷
SELECT SUBSTR('杨幂李冰冰范爷',1,2); #索引从1开始，截取索引1后面长度为2的字符，即截取出杨幂 

#5.instr 返回子串第一次出现的索引，如果没找到，返回0
SELECT INSTR('杨不悔爱上了殷六侠爱', '爱') AS output; #结果4
#6.trim 去前后空格
SELECT LENGTH(TRIM('      余登武      ')) AS outout;#结果为9
SELECT TRIM('a' FROM 'aaaaa余aaa登aa武aaaaaaaaaaaa') AS output;#结果余aaa登aa武
 
#7.Lpad  左填充，指定元素填充，指定总长度
#Rpad 右填充 
SELECT LPAD('李倩',10,'*') AS output;#结果 ********李倩 
SELECT RPAD('李倩',10,'*') AS output;#结果 李倩********

# 8.replace 替换

SELECT REPLACE('总裁余喜欢高小姐','高小姐','李女士')AS output; #将高小姐替换为李女士

二，数学函数

round 四舍五入
ceil 向上取整,返回大于等于该数的最小整数
floor 向下取整，返回小于等于该参数的最大整数
truncate 截断，小数点后面保留几位
mod 取余

USE myemployees;
#1.round 四舍五入
SELECT ROUND(1.65);#结果2
SELECT ROUND(1.65,1);#结果1.7，第二个参数为小数点后面保留几位

#2 ceil 向上取整,返回大于等于该数的最小整数
SELECT CEIL(1.2);#结果2
#3.floor 向下取整，返回小于等于该参数的最大整数
SELECT FLOOR(1.2);#结果1

#4.truncate 截断  小数点后面保留几位
SELECT TRUNCATE(12.321,2);#结果12.32
#5.mod 取余
SELECT MOD(10,3); #结果1

三.其他函数

version 版本号
database 查看当前数据库
user 查看当前用户

#version 版本号
SELECT VERSION();
#查看当前数据库
SELECT DATABASE();
#查看当前用户
SELECT USER();

五. 流程控制函数if case

if 第一个参数为条件表达式，如果满足，返回第二个参数，不满足返回第三参数。

SELECT IF(10>5,'大','小');

结果大

case

mysql中
case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1;
when 常量2 then 要显示的值2或语句2;
…
else 要显示的值n或语句n;
end
*/

USE myemployees;
/*案例：查询员工的工资，要求

部门号=30，显示的工资为1.1倍
部门号=40，显示的工资为1.2倍
部门号=50，显示的工资为1.3倍
其他部门，显示的工资为原工资

*/

SELECT salary 原始工资,department_id,
CASE department_id
WHEN 30 THEN salary*1.1
WHEN 40 THEN salary*1.2
WHEN 50 THEN salary*1.3
ELSE salary
END AS 新工资
FROM employees;

mysql中：
case
when 条件1 then 要显示的值1或语句1
when 条件2 then 要显示的值2或语句2
。。。
else 要显示的值n或语句n
end
*/

USE myemployees;
/*
案例：查询员工的工资的情况
如果工资>20000,显示A级别
如果工资>15000,显示B级别
如果工资>10000，显示C级别
否则，显示D级别
*/

SELECT salary,
CASE 
WHEN salary>20000 THEN 'A'
WHEN salary>15000 THEN 'B'
WHEN salary>10000 THEN 'C'
ELSE 'D'
END AS 工资级别
FROM employees;

这里case后面没有语句

分组函数

/*
功能：用作统计使用，又称为聚合函数或统计函数或组函数
分类：
sum 求和、avg 平均值、max 最大值 、min 最小值 、count 计算个数
特点：
1、sum、avg一般用于处理数值型
max、min、count可以处理任何类型
max，min对字符排序是根据字的字母顺序
2、以上分组函数都忽略null值
3、可以和distinct搭配实现去重的运算

#1、简单 的使用
SELECT SUM(salary) FROM employees;
SELECT AVG(salary) FROM employees;
SELECT MIN(salary) FROM employees;
SELECT MAX(salary) FROM employees;
SELECT COUNT(salary) FROM employees;
#一起查询
SELECT SUM(salary) 和,AVG(salary) 平均,MAX(salary) 最高,MIN(salary) 最低,COUNT(salary) 个数
FROM employees;

SELECT SUM(salary) 和,ROUND(AVG(salary),2) 平均,MAX(salary) 最高,MIN(salary) 最低,COUNT(salary) 个数
FROM employees;

#和distinct搭配
SELECT SUM(DISTINCT salary),SUM(salary) FROM employees;

SELECT COUNT(DISTINCT salary),COUNT(salary) FROM employees;

#5、count函数的详细介绍

SELECT COUNT(salary) FROM employees;


SELECT COUNT(*) FROM employees;

SELECT COUNT(1) FROM employees;#1表示序号如表格里默认的行数

效率：
MYISAM存储引擎下  ，COUNT(*)的效率高
INNODB存储引擎下，COUNT(*)和COUNT(1)的效率差不多，比COUNT(字段)要高一些

#和分组函数一同查询的字段有限制  因为得到的行数不同，表格不好展示

SELECT AVG(salary),employee_id  FROM employees;

5分组查询

/*
语法：
select 查询列表
from 表
【where 筛选条件】
group by 分组的字段
【order by 排序的字段】;

特点：
1、和分组函数一同查询的字段必须是group by后出现的字段
2、筛选分为两类：分组前筛选和分组后筛选
针对的表 位置 连接的关键字
分组前筛选 原始表 group by前 where
分组后筛选 group by后的结果集 group by后 having

问题1：分组函数做筛选能不能放在where后面
答：不能
问题2：where——group by——having
一般来讲，能用分组前筛选的，尽量使用分组前筛选，提高效率
3、分组可以按单个字段也可以按多个字段
4、可以搭配着排序使用
*/

没有筛选

#案例1：查询每个工种的员工平均工资
SELECT AVG(salary),job_id
FROM employees
GROUP BY job_id;

可以实现分组前的筛选
如果根据原始表可以筛选，就是分组前筛选

#案例1：查询邮箱中包含a字符的 每个部门的最高工资

SELECT MAX(salary),department_id
FROM employees
WHERE email LIKE '%a%'
GROUP BY department_id;

#案例2：查询有奖金的每个领导手下员工的平均工资

SELECT AVG(salary),manager_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY manager_id;

添加分组后筛选
如果原始表不能筛选，就是分组后筛选。
分组后筛选的数据源是分组后的数据

USE myemployees;

#案例：查询哪个部门的员工个数>5


SELECT COUNT(*),department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*)>5;

#案例2：每个工种有奖金的员工的最高工资>12000的工种编号和最高工资

SELECT job_id,MAX(salary)
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING MAX(salary)>12000;

关于分组前和分组后的选择，建议先写select 和group by部分，最后写条件部分

添加排序

#案例：每个工种有奖金的员工的最高工资>6000的工种编号和最高工资,按最高工资升序

SELECT job_id,MAX(salary) m
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING m>6000
ORDER BY m ;

按多个字段分组

#案例：查询每个工种每个部门的最低工资,并按最低工资降序

SELECT MIN(salary),job_id,department_id
FROM employees
GROUP BY department_id,job_id
ORDER BY MIN(salary) DESC;

电气专业的计算机萌新，写博文不容易。如果你觉得本文对你有用，请点个赞支持下，谢谢。