后台-插件-广告管理-内容页广告位一(手机)

您现在的位置是:首页 > 开发类 > 数据库数据库

学习笔记:MYSQL查询

2021-04-18 18:35:35数据库人已围观

简介前言:之前花费两天晚上看了一遍Mysql必知必会,没想到后面效果太差。不如跟着网课视频敲一遍和完成练习题目(练习题没写注释就不记录了),再记下笔记。
一、基本的查询select语句

前言:之前花费两天晚上看了一遍Mysql必知必会,没想到后面效果太差。不如跟着网课视频敲一遍和完成练习题目(练习题没写注释就不记录了),再记下笔记。

一、基本的查询select语句
语法: select 查询列表 from 表名;
查询列表可以是表中的字段、常量值、表达式、函数,查询结果是一个虚拟的表格。

SELECT last_name FROM employees;

#2.查询表中的多个字段
SELECT first_name,last_name,phone_number,email FROM employees;

#3.查询表中的所有字段
SELECT * FROM employees;

#4.查询常量
SELECT 10;

#5.查询表达式
SELECT 1010*2;

#6.查询函数
SELECT VERSION( );

#7.起别名
SELECT salary AS `收入` FROM employees;
SELECT first_name `名` FROM employees;

#8.去重
SELECT DISTINCT salary AS `工资样本` FROM employees;

#9.‘+’加号:仅有运算符的作用,没有拼接字符的作用
SELECT 1010 + 2023;

#10.使用concat连接字段
SELECT CONCAT(first_name,last_name) FROM employees;

二、条件查询
语法:select 查询列表 from 表名
where 筛选条件;

分类:
1.按条件表达式筛选
运算符: > < = != <> >= <=

2.按逻辑表达式筛选
运算符: || && !
也可以使用 or and not

3.模糊查询
like
between and
in
is null

#按条件表达式查询
#1. 查询工资>12000的员工信息
SELECT * FROM employees WHERE salary >12000;

#2. 查询部门编号不等于90号的员工名和部门编号
SELECT last_name,department_id FROM employees WHERE department_id !=90;

#按逻辑表达式查询
#3.查询工资在10000~20000的员工名、工资、奖金
SELECT last_name, salary,commission_pct FROM employees WHERE salary >10000 AND salary <20000;

#4.查询部门编号不是在90~100,或者工资高于15000的员工信息
SELECT * FROM employees WHERE department_id <90 OR department_id >110 OR salary >15000;
SELECT * FROM employees WHERE NOT(department_id BETWEEN 90 AND 110) OR salary >15000;

#like查询
#5.查询员工名中有a字符的员工信息
SELECT * FROM employees WHERE last_name LIKE '%a%';

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

#7.查询员工名第二个字符为_的员工信息
SELECT * FROM employees WHERE last_name LIKE '_\_%';
SELECT * FROM employees WHERE last_name LIKE '_$_%' ESCAPE '$';

#between and 两个临界值不能调换顺序
#8.查询员工编号在100~120的员工信息
SELECT * FROM employees WHERE employee_id BETWEEN 100 AND 120;

#in
#9.查询员工工种是 IT_PROG,AD_VP,AD_PRSE中的一个的员工名和工种
SELECT last_name, job_id FROM employees WHERE job_id IN('IT_PROG','AD_VP','AD_PRSE');

#is null
#10. 查询没有奖金的员工名和奖金率
SELECT last_name,commission_pct FROM employees WHERE commission_pct IS NULL;

#安全等于
SELECT last_name, commission_pct FROM employees WHERE commission_pct <=> NULL;

#11. 查询有奖金的员工名和奖金率
SELECT last_name,commission_pct FROM employees WHERE commission_pct IS NOT NULL;

三、排序查询
语法:
select 查询列表 from 表名
where 筛选条件
order by 排序列表 ase | desc;

order by子句一般放在查询语句最后面,limit子句除外
order by子句支持单个字段、多个字段、表达式、别名、函数

#1.查询员工信息,要求按工资从低到高排序
SELECT * FROM employees 
ORDER BY salary ASC;

#2.查询部门编号>90的员工信息,按入职时间的先后排序
SELECT * FROM employees
WHERE department_id >90
ORDER BY hiredate ASC;

#3.按年薪的高低显示员工的信息和年薪
SELECT * ,salary*12 AS '年薪' FROM employees
ORDER BY salary*12 ASC;

#4.按姓名的长度显示员工姓名和薪水
SELECT CONCAT(first_name,last_name) AS '姓名',salary FROM employees
ORDER BY LENGTH(CONCAT(first_name,last_name)) ASC;

#5.查询员工信息,先按工资升序排序,再按员工编号降序排序
SELECT * FROM employees
ORDER BY salary ASC,employee_id DESC;

四、常见函数
语法:
select 函数名(实参列表)from 表名;
分类:

  1. 单行函数
    比如:concat、length、ifnull
  2. 分组函数
#(一)字符函数
#1.length 获取参数值的字节个数
SELECT LENGTH('中文');
SELECT LENGTH('English');

#2.concat 拼接字符串
SELECT CONCAT(first_name,' ',last_name) FROM employees;

#3.upper、lower
#将姓大写、名小写,再拼接
SELECT CONCAT(UPPER(first_name),' ',LOWER(last_name)) AS '姓名' FROM employees;

#4.substr、substring 索引从一开始
SELECT SUBSTR('一二三四五',1,4);

#将名中的首字符大写,其余字符小写,再拼接起来
SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),'_',LOWER(SUBSTR(last_name,2))) FROM employees;

#5.instr 返回子串第一次出现的索引,没有则返回0
SELECT INSTR('五岳泰山','泰山');

#6.trim
SELECT TRIM('    23232  ');
SELECT TRIM('o' FROM 'oooo中文oooo');

#7. lpad 用指定的字符左填充到指定的长度
SELECT LPAD('中文',9,'*');

#8. Rpad 用指定的字符右填充到指定的长度
SELECT RPAD('英文',10,'o');

#9. replace 替换
SELECT REPLACE('中文****','*','o');

##(二)数学函数
#1.round 四舍五入
SELECT ROUND(1.4);

#2.ceil 向上取整,返回>=该参数的最小整数
SELECT CEIL(1.4);

#3.floor 向下取整,返回<=该参数的最大整数
SELECT FLOOR(1.5);

#4.truncate 截断,保留几位小数
SELECT TRUNCATE(1.8888,0);

#5.mod 取余
SELECT MOD(34,8);

##(三)日期函数
#1.now 返回当前系统日期和时间
SELECT NOW();

#2.curdate 返回当前系统日期,不包括时间
SELECT CURDATE();

#3.curtime 返回时间,不包括日期
SELECT CURTIME()

#4.获取指定部分,年、月、日、小时、分钟、秒
SELECT YEAR(NOW());

SELECT MONTH(NOW());

SELECT MONTHNAME(NOW());

SELECT MINUTE(NOW());

#5.str_to_date 将字符通过指定的格式转换为日期
SELECT STR_TO_DATE('2021-4-13','%Y-%c-%d') out_put;

#查询入职日期是1992-4-3的员工
SELECT * FROM employees WHERE hiredate = STR_TO_DATE('4-3 1992','%c-%d %Y');

#date_format 将日期转换成字符
SELECT DATE_FORMAT(NOW(),'%Y年%c月%d日');

#查询有奖金的员工名和入职日期(xx年xx月xx日)
SELECT CONCAT(first_name,' ',last_name),DATE_FORMAT(hiredate,'%Y年%c月%d日') 入职日期 FROM employees
WHERE commission_pct IS NOT NULL;

##(四)其它函数
#1.显示mysql的版本号
SELECT VERSION();

#2.显示当前打开的数据库
SELECT DATABASE();

#3.显示当前登录的用户
SELECT USER();

##(五)流程控制函数
#if if else的效果
SELECT IF(35>10,'大','小');

SELECT CONCAT(first_name,' ',last_name),IF(commission_pct,'有奖金','没奖金') FROM employees;

#case 要判断的字符或表达式
#when 常量 then 要显示的值或语句
#when 常量 then 要显示的值或语句
#...
#else 要显示的值或语句
#end

#查询员工的工资,要求:
#部门编号为30,显示的工资为1.1倍
#部门编号为40,显示的工资为1.2倍
#部门编号为50,显示的工资为1.3倍
#其它部门,显示的工资为1倍

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;

#case 
#when 条件1 then 要显示的值或表达式
#when 条件2 then 要显示的值或表达式
#when 条件3 then 要显示的值或表达式
#else 要显示的值或表达式
#end

#查询员工的工资情况
/*如果工资大于20000,显示等级A
如果工资大于15000,显示等级B
如果工资大于10000,显示等级C
否则显示等级D
*/
SELECT salary,
CASE salary
WHEN salary>20000 THEN '等级A'
WHEN salary>15000 THEN '等级B'
WHEN salary>10000 THEN '等级C'
ELSE '等级D'
END AS 工资等级
FROM employees;



#分组函数
/*功能:用作统计使用,又称为聚合函数、统计函数或组函数
分类:
sum 求和、avg 平均值、max 最大值、count 计算个数
*/

#1.简单的使用
SELECT SUM(salary) FROM employees;
SELECT AVG(salary) FROM employees;
SELECT MAX(salary) FROM employees;
SELECT MIN(salary) FROM employees;
SELECT COUNT(salary) FROM employees;

#2.支持哪些参数类型 sum、 avg一般用于处理数值型 max、min、count可以处理任何类型
SELECT SUM(last_name) FROM employees;
SELECT MAX(last_name) FROM employees;
SELECT COUNT(last_name) FROM employees;

#3.是否忽略null值 下面的分组函数都忽略null值
SELECT MAX(commission_pct),SUM(commission_pct),AVG(commission_pct) FROM employees;
SELECT COUNT(employee_id),COUNT(commission_pct) FROM employees;

#4.和distinct搭配使用
SELECT SUM(DISTINCT(salary)) FROM employees;
SELECT SUM(salary) FROM employees;

#5.count 函数的详细介绍
/*效率:
MYISAM引擎下,count(*)效率高
INNODB引擎下,count(*)和count(1)效率差不多,比count(字段)效率高
*/
SELECT COUNT(salary) FROM employees;
SELECT COUNT(*) FROM employees;
SELECT COUNT(1) FROM employees;

#6.和分组函数一同查询的字段的限制:要求是group by后面的字段
SELECT SUM(salary),employee_id FROM employees;

五、分组查询
语法:
select 分组函数、列(要求是出现在group by后面的字段)
from 表名
where 筛选条件
group by 分组的列表
order by 子句

注意:查询列表必须特殊,要求是分组函数和group by后面的字段

分组查询中的筛选条件分为两类:
分组前筛选 数据源是原始表 位置在group by前面 关键字是where
分组后筛选 分组后的结果集 在group by后面 having
分组函数作为筛选条件的,肯定放在having子句中。

group by子句支持单个字段分组、多个字段分组、表达式或函数,也可以添加排序

#分组查询
#1.查询每个工种的最高工资
SELECT MAX(salary),job_id FROM employees
GROUP BY job_id;

#2.查询每个位置上的部门个数
SELECT COUNT(*),location_id FROM departments
GROUP BY location_id;

#3.添加筛选条件:查询邮箱中包含a字符的,每个部门的平均工资
SELECT AVG(salary),department_id FROM employees
WHERE email LIKE '%a%'
GROUP BY department_id;

#4.查询有奖金的每个领导手下员工的最高工资
SELECT MAX(salary),manager_id FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY manager_id;

#添加复杂的筛选条件
#5.查询哪个部门的员工个数>2
#查询每个部门的员工个数
SELECT COUNT(*),department_id FROM employees GROUP BY department_id;
#根据上面的结果筛选,查询哪个部门的员工个数>2
SELECT COUNT(*),department_id FROM employees GROUP BY department_id
HAVING COUNT(*) >2;

#6.查询每个工种有奖金员工的最高工资>12000的工种编号和最高工资
SELECT MAX(salary),job_id FROM employees
GROUP BY job_id
HAVING MAX(salary) >12000;

#7.查询领导编号>102的每个领导手下的最低工资>5000的领导编号是哪个,以及其最低工资
SELECT manager_id,MIN(salary) FROM employees
WHERE manager_id >102
GROUP BY manager_id
HAVING MIN(salary) >5000;

#按表达式或函数分组
#8.按员工名字的长度,查询每一组员工个数,筛选员工个数>5的有哪些
SELECT COUNT(*),LENGTH(last_name) FROM employees
GROUP BY LENGTH(last_name)
HAVING COUNT(*) >5;

#按多个字段分组
#9.查询每个部门每个工种的员工的平均工资
SELECT AVG(salary),department_id,job_id FROM employees
GROUP BY department_id,job_id;

#添加排序
#10.查询每个部门每个工种的员工的平均工资,按平均工资的高低排序
SELECT AVG(salary),department_id,job_id FROM employees
GROUP BY department_id,job_id
HAVING AVG(salary) >10000
ORDER BY AVG(salary) ASC;

六、多表查询
按标准分类:
sql92:仅支持内连接, sql99

按功能分类:
内连接 自连接 外连接

sql99语法:

select 查询列表
from 表名 连接类型
join 表名
on 连接条件
where 筛选条件
group by 分组条件
having 筛选条件
order by 筛选条件

分类:
内连接: inner
外连接: 左外连接 left outer, 右外连接 right outer, 全外 full outer
交叉连接:cross join

(1)内连接:
语法:select 查询列表
from 表名
inner join 表名
on 连接条件

分类:等值连接 非等值连接 自连接

(2)外连接:
应用场景:用于查询一个表中有,另外一个表中没有的记录。

特点:
外连接的查询结果为主表中的所有记录,
如果从表中有与它匹配的,则显示匹配的值,
从表中没有与它匹配的,则显示null值

外连接查询结果=内连接结果+主表中有而从表中没有的记录
左外连接,left join左边的是主表
右外连接,right join右边的是主表
全外连接=内连接结果+主表中有而从表中没有的记录+从表中有而主表中没有的记录

##自连接

#一、内连接
#等值连接
#1.查询员工名、部门名
SELECT last_name,department_name
FROM employees e
INNER JOIN departments d
ON e.department_id = d.department_id;

#2.查询员工名包含e的员工名和工种名(添加筛选)
SELECT last_name,job_title 
FROM employees e
INNER JOIN jobs j
ON e.`job_id`= j.`job_id`
WHERE e.last_name LIKE '%e%';

#3.查询部门个数>3的城市名和部门个数(添加分组和筛选)
SELECT COUNT(*),city
FROM departments d
INNER JOIN locations l
ON d.location_id  = l.location_id
GROUP BY city
HAVING COUNT(*) >3;

#4.查询那个部门的员工个数>3的部门名和员工个数,并按个数降序(添加排序)
SELECT COUNT(*),department_name
FROM employees e
INNER JOIN departments d
ON e.`department_id` = d.`department_id`
GROUP BY department_name
HAVING COUNT(*) >3
ORDER BY COUNT(*) DESC;

#5.查询员工名、部门名、工种名,并按部门名降序
SELECT last_name,department_name,job_title
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
INNER JOIN jobs j ON e.job_id = j.job_id
ORDER BY department_name DESC;

##非等值连接
#6.查询员工的工资级别
SELECT last_name,salary,grade_level FROM employees e
INNER JOIN job_grades j ON e.salary BETWEEN j.lowest_sal AND j.highest_sal;

#7.查询工资级别的个数>20的个数,并按工资级别降序
SELECT COUNT(*),grade_level FROM employees e
INNER JOIN job_grades j ON e.salary BETWEEN j.lowest_sal AND j.highest_sal
GROUP BY j.grade_level
HAVING COUNT(*) >20
ORDER BY  j.grade_level DESC;

##自连接
#8.查询员工的名字、上级的名字
SELECT e.last_name 员工名,m.last_name  上级 FROM employees e
INNER JOIN employees m ON e.manager_id = m.employee_id;

#9.查询员工的名字中包含字符k、上级的名字
SELECT e.last_name 员工名,m.last_name 上级 FROM employees e
INNER JOIN employees m ON e.manager_id = m.employee_id
WHERE e.last_name LIKE '%k%';

#二、外连接
#左外连接
#1.查询没有男朋友的女神
SELECT be.name,b.boyname FROM beauty be
LEFT OUTER JOIN boys b ON be.boyfriend_id = b.id
WHERE b.id IS NULL;

#2.查询哪个部门没有员工
SELECT department_name,employee_id FROM departments d
LEFT OUTER JOIN employees e ON d.department_id = e.department_id;
WHERE d.department_id IS NULL;

#右外连接
SELECT department_name FROM employees e
RIGHT OUTER JOIN departments d ON d.department_id = e.department_id;

#全外 mysql中不支持
SELECT be.*,bo.* FROM beauty be
FULL OUTER JOIN boys bo ON be.boyfriend_id = bo.id;

#交叉连接
SELECT be.*,bo.* FROM beauty be
CROSS  JOIN boys bo ;

七、子查询
含义:出现在其他语句中的select语句,称为子查询或内查询
外部的查询语句,称为主查询或外查询

分类:
按子查询出现的位置:
select 后面:仅支持标量子查询;
from后面: 支持表子查询;
where或having后面:标量子查询或列子查询、行子查询;
exist后面:相关子查询

按结果集的行列数不同划分:
标量子查询(结果集为一行一列)
行子查询(一行多列)
列子查询(一列多行)
表子查询(多行多列)

#一、where或having后面
/*
支持标量子查询或列子查询、行子查询
特点:
子查询一般放在括号内
子查询一般放在条件的右侧
标量子查询:一般搭配单行操作符使用(> < =)
列子查询:一般搭配着多行操作符使用(in any/some or)
*/

##标量子查询
#1.谁的工资比Abel高?
SELECT salary FROM employees
WHERE last_name = 'Abel';

SELECT last_name,salary FROM employees
WHERE salary >(
	SELECT salary FROM employees
WHERE last_name = 'Abel');
 
#2.查询job_id 与141号员工相同,salary比143号多的员工姓名、job_id和工资
SELECT job_id FROM employees
WHERE employee_id = 141;

SELECT salary FROM employees
WHERE employee_id = 143;

SELECT last_name,job_id,salary FROM employees
WHERE job_id = (
		SELECT job_id FROM employees
		WHERE employee_id = 141) 
AND salary > (
		SELECT salary FROM employees
		WHERE employee_id = 143);

#3.返回工资最少的员工的姓名、job_id和salary
SELECT MIN(salary) FROM employees;

SELECT last_name,job_id,salary FROM employees
WHERE salary = (SELECT MIN(salary) FROM employees);

#having
##4.查询最低工资大于50号部门的最低工资的部门id和其最低工资
SELECT MIN(salary),department_id FROM employees
GROUP BY department_id
HAVING department_id = 50;

SELECT MIN(salary),department_id FROM employees
GROUP BY department_id
HAVING MIN(salary) > (
	SELECT MIN(salary) FROM employees
	GROUP BY department_id
	HAVING department_id = 50);

#行子查询
#5.查询员工编号最小且工资最高的员工信息
SELECT MIN(employee_id) FROM employees;

SELECT MAX(salary) FROM employees;

SELECT * FROM employees
WHERE employee_id = (SELECT MIN(employee_id) FROM employees)
AND salary = (SELECT MAX(salary) FROM employees);

SELECT * FROM employees
WHERE (employee_id,salary) =(SELECT MIN(employee_id), MAX(salary) FROM employees);

#二、select后面:仅支持标量子查询)
#6.查询每个部门的员工个数
SELECT COUNT(*),department_name FROM departments d
LEFT OUTER JOIN employees e ON d.department_id = e.department_id
GROUP BY department_name;

SELECT d.*,(
	SELECT COUNT(*) FROM employees e
	WHERE e.department_id = d.department_id)
FROM departments d;

#7.查询员工号位为102的部门名
SELECT department_name FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
WHERE employee_id = 102;

#三、from后面:列子查询,将子查询的结果当做一张表,要求必须取别名
#8.查询每个部门的平均工资的工资等级
/* 可能错误 */
SELECT AVG(salary),department_id,grade_level FROM employees e
INNER JOIN job_grades ON salary BETWEEN lowest_sal AND highest_sal
GROUP BY department_id;

SELECT ag.*, grade_level 
FROM (
	SELECT AVG(salary) s,department_id FROM employees
	GROUP BY department_id) ag
INNER JOIN job_grades ON ag.s BETWEEN lowest_sal AND highest_sal;

#四、exist后面
/*语法:
exist(完整的查询语句)
结果:1或0
*/
#9. 查询有员工的部门名
SELECT department_name FROM departments d
WHERE EXISTS(
	SELECT * FROM employees e
	WHERE e.department_id = d.department_id);

SELECT department_name FROM departments d
INNER JOIN employees e ON d.department_id = e.department_id;

八、分页查询
要显示的数据,一页显示不全,需要分页提交sql请求
语法:
select 查询列表
from 表名
inner join 表名 on 连接条件
where 筛选条件
group by 分组条件
having 分组后的筛选
order by 排序的字段
limit offset,size

offset 要显示条目的起始索引(起始索引从零开始)
size 要显示的条目数

注意:limit放在查询语句的最后
公式:要显示的页数 limit (page-1)*size,size

#分页查询
#查询前五条员工信息
SELECT * FROM employees LIMIT 0,5;

#2.查询第11-25条员工信息
SELECT * FROM employees LIMIT 10,15;

#3.有奖金的员工信息,并且将工资较高的前10条显示出来
SELECT * FROM employees WHERE commission_pct IS NOT NULL 
ORDER BY salary
LIMIT 0,10;

九、联合查询
union 将多条查询语句的结果合并成一个结果
语法:
查询语句1
union
查询语句2
union
...
应用场景:查询的结果来自于多个表,且多个表没有直接的连接关系,但查询的信息一致时
特点: 要求多个查询语句的查询列表是一致的
多个查询语句的查询的每一列的类型和顺序最好是一致的
union关键字默认去重,如果使用union all可以包含重复项

#联合查询
#查询部门编号>90或邮箱包含a的员工信息
SELECT * FROM employees WHERE department_id > 90
UNION
SELECT * FROM employees WHERE email LIKE '%a%';

文章来源:查看

Tags:学习笔记   查询   MYSQL

很赞哦! ()

后台-插件-广告管理-内容页广告位二(手机)

相关文章

后台-插件-广告管理-内容页广告位三(手机)

随机图文

后台-插件-广告管理-内容页广告位四(手机)

文章评论

留言与评论(共有 0 条评论)
   
验证码:

本栏推荐

站点信息

  • 文章统计13614篇文章
  • 浏览统计468次浏览
  • 评论统计1个评论
  • 标签管理标签云
  • 统计数据:统计代码
  • 微信公众号:扫描二维码,关注我们