前言

本篇博客是学习mysql时的一些笔记，主要是SQL语言的学习，例表来源于尚硅谷，在B站上有视频资料可以学习。

进阶1-基础查询

  • 语法：

    SELECT
	查询列表
FROM
	表名;

    类似于System.out.println(“”);

  • 特点：

    查询列表可以是：字段、常量、表达式、函数

查单个字段

SELECT
	last_name 
FROM
	employees;

查多个字段

SELECT 
	last_name,
	first_name 
FROM 
	employees;

查询所有字段

SELECT 
	* 
FROM 
	employees;

查询常量

SELECT 1;
SELECT 'flag';

查询表达式

SELECT 100%98;

查询函数

SELECT VERSION();

起别名

SELECT 100%98 AS solution;
SELECT last_name AS "姓",first_name AS 名 FROM employees;
SELECT last_name 姓,first_name 名 FROM employees;
SELECT employees_id AS "out put" FROM employees;

去重

SELECT DISTINCT employees_id FROM employees;

+的作用

  • java中的+

    1、运算符：两个数据都为数值时
    2、链接符：只要有一个为字符串

  • mysql中的+

    仅为运算符

    1、当两个操作数都为数值时，做常规的加法。
    SELECT 1+2;
    2、只要其中一个为字符型，试图转换字符为数值，失败时将字符型转为0。
    SELECT ‘a’+1;
    3、只要存在一个null，结果为null。
    SELECT null+1+2+3;

拼接

SELECT
CONCAT('a','b') AS "name";

进阶2-条件查询-where

  • 语法：

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

  • 分类：

    1、条件运算符：> 、< 、 = 、 != 、 <> 、 >= 、 <=

    2、逻辑运算符：&& 、 || 、 ！ 、 and 、 or 、 not

    3、模糊查询：like 、 between and 、 in 、 is null

按条件表达式筛选

SELECT
	last_name
FROM
	employees
WHERE
	id>1;

按逻辑表达式筛选

SELECT
	last_name
FROM
	employees
WHERE
	employees_id>1 
AND
	employees_id<5;

模糊筛选

like

特点：一般和通配符搭配使用

通配符：

%：任意匹配多个字符，包含0个字符

_：任意单个字符

#查询员工名字中第二个字符为a且第3个字符为_的员工信息
SELECT
	*
FROM
	employees
WHERE
	last_name LIKE '_a\_%';

escape：将字符定义为转义前缀，未规定时默认为\

#查询员工名字中第二个字符为a且第3个字符为_的员工信息
SELECT
	*
FROM
	employees
WHERE
	last_name LIKE '_a$_%' ESCAPE '$';

between and

特点：包含临界值，左小右大

#查询员工编号在100到120之间的员工信息
SELECT
	*
FROM
	employees
WHERE
	employees_id BETWEEN 100 AND 120;

in

特点：in列表的值的类型必须一致，不支持通配符

#查询员工的工种编号是IT_PROG、AD_VIP、AD_PRES中的一个的员工信息
SELECT
	*
FROM
	employees
WHERE
	job_id IN ('IT_PROG','AD_VIP','AD_PRES');	#job_id = 'IT_PROG' OR job_id = 'AD_VIP' OR job_id = 'AD_PRES';

is null

特点：=或!=不能匹配null，null可以搭配is或者安全等于<=>

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

进阶3-排序查询-order by

  • 语法
SELECT
	查询列表
FROM
	表
WHERE
	筛选条件
ORDER BY 
	排序列表 ASC|DESC;
  • 特点
  1. asc代表升序，desc代表降序，如果不写默认升序
  2. order by支持单个字段、多个字段、表达式、函数、别名
  3. order by子句一般放在最后面，只有limit放在他后面

进阶4-常见函数

字符函数

length

获取参数值的字节个数

注意，在不同编码中，中文所占字节长度不同

SELECT LENGTH('nayst');
SELECT LENGTH('nayst的小站') AS out_put;

concat

拼接字符串

SELECT
	CONCAT(last_name,'_',firstname) AS 姓名 
FROM 
	employees;

upper、lower

将字符串转为小写（小写）

SELECT UPPER('nayst');

substr

截取从指定索引处后指定长度的字符，未规定时默认截取后面所有字符

SELECT SUBSTR('nayst is a handsome man', 12) AS out_put;
SELECT SUBSTR('nayst is a handsome man', 12, 8) AS out_put;

instr

返回子串第一次出现的索引，找不到返回0

SELECT INSTR('nayst is a handsome man','an') AS out_put;

trim

删除字符串前后的指定内容，未规定时默认删除空格

SELECT TRIM('aa' FROM 'aaaaaaanaystaaa') AS out_put;

lpad、rpad

用指定字符从左边（右边）开始填充至指定长度

注意，当指定长度小于字符长度时会产生截断

SELECT LPAD('nayst',10,'-') AS out_put;
SELECT LPAD('nayst',3,'-') AS out_put;

replace

替换指定字符

SELECT REPLACE('nayst is a handsome man','handsome','nice') AS out_put;

数学函数

round

对指定数值的绝对值四舍五入，可以规定四舍五入到第几位，未规定时默认整数位

SELECT ROUND(-1.567,2);

ceil、floor

向上（下）取整，返回该>=（<=）该参数的最小整数

SELECT CEIL(-1.02) out_put;

truncate

从小数点后指定位数截断，即保留指定位数的小数

SELECT TRUNCATE(1.12345,3);

mod

取余，等价于%

遇到负数时可以参考这个公式：MOD(a,b)=a-a/b*b

SELECT MOD(10,3);

日期函数

now

返回当前日期+时间

SELECT NOW();

curdate

只返回当前的日期

SELECT CURDATE();

curtime

只返回当前的时间

SELECT CURTIME();

获取指定的部分

获取日期中的年、月、日、小时、分钟、秒

SELECT YEAR(NOW()) 年;
SELECT YEAR('2021-7-11') 年;
SELECT YEAR(hiredate) 年 FROM employees;
SELECT MONTH(NOW()) 月;
SELECT MONTHNAME(NOW()) 'month';

流程控制函数

if

SELECT IF(10<5,'true','false') out_put;

case

/*查询员工的工资
部门号=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;

分组函数

功能：用作统计使用，又称聚合函数或统计函数

分类：sum 求和、avg 平均值、max 最大值、min 最小值、count 计算个数

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;

特点：

  1. sum、avg一般用于处理数值型

    max、min、count可以处理任何类型

  2. 以上分组都可以忽略null值

  3. 可以和distinct搭配实现去重的运算

  4. count(*)、count(1)可以统计行数

  5. 和分组函数一同查询的字段要求是group by后的字段

其他函数

SELECT VERSION();
SELECT DATABASE();
SELECT USER();

进阶5-分组查询-group by

  • 语法

    SELECT
	分组函数,列（要求出现在group by的后面）
FROM
	表 
[WHERE 
 	筛选条件]
GROUP BY
	分组的列表 [ORDER BY子句]

  • 注意：查询列表必须特殊，要求是分组函数和group by后出现的字段

  • 特点：

    分组查询中的筛选条件分为两类

    数据源 位置 关键字
    查询前筛选 原始表 group by子句的前面 where
    查询后筛选 分组后的结果集 group by子句的后面 having
    1. 分组函数做条件肯定是放在having子句中
    2. 能用分组前筛选的就优先考虑使用分组前筛选

简单的分组查询

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

查询前筛选

#查询邮箱中包含a字符的，每个部门的平均工资
SELECT
	AVG(salary),
	department_id
FROM
	employees
WHERE
	email LIKE '%a%'
GROUP BY
	department_id;

查询后筛选

#查询哪个部门的员工个数>2
#1.查询每个部门的员工个数
SELECT
	COUNT(*),
	department_id
FROM
	employees
GROUP BY
	department_id;
#2.根据1的结果筛选哪个部门员工个数>2
SELECT
	COUNT(*),
	department_id
FROM
	employees
G BY
	department_id
HAVING
	COUNT(*)>2;

按函数分组查询

#按员工姓名长度分组，查询每一组的员工个数，筛选员工个数>5的有哪些
#1.查询每个长度的员工个数
SELECT
	COUNT(*),
	LENGTH(last_name) len_name
FROM
	employees
GROUP BY
	LENGTH(last_name);
#2.添加筛选条件
SELECT
	COUNT(*) c,
	LENGTH(last_name) len_name
FROM
	employees
GROUP BY
	len_name
HAVING
	c>5;

按多个字段分组

#查询每个部门每个工种的员工的平均工资
SELECT
	AVG(salary),
	department_id,
	job_id
FROM
	employees
GROUP BY
	department_id,
	job_id;

添加排序

#查询每个部门每个工种的员工的平均工资，并按平均工资降序排序
SELECT
	AVG(salary),
	department_id,
	job_id
FROM
	employees
GROUP BY
	department_id,
	job_id;
ORDER BY
	AVG(salary) DESC;

进阶6-连接查询-join

  • 含义：又称多表查询，当查询的字段来自于多个表时，就会用到连接查询

  • 分类：

    • 按年代分类

      sql92标准

      sql99标准（推荐）

    • 按功能分类

      内连接：

      等值连接

      非等值连接

      自连接

      外连接：

      左外连接

      右外连接

      全外连接

      交叉连接

sql92语法

等值连接

  • 特点：需要查询的内容需要访问其他表
    • 可以添加筛选
    • 可以添加分组
    • 可以添加排序
#查询女神名和对应的男神名
SELECT 
	NAME,boyName
FROM
	boys,beauty
WHERE
	beauty.boyfriend_id = boys.id;

为表起别名

#查询员工名、工种名、工种号
SELECT
	e.last_name,
	e.job_id,
	j.job_id,
	j.job_title
FROM
	employees AS e,jobs AS j	#AS可以省略
WHERE
	e.`job_id` = j.`job_id`;

非等值连接

#查询员工的工资和工资筛选级别为A
SELECT
	salary,
	grade_level
FROM
	employees e,
	job_grades g
WHERE
	salary BETWEEN g.`lowest_sal` AND g.`highest_sal`
AND
	g.`grade_level` = 'A';

自连接

  • 特点：类似于等值连接，需要查询的内容需要再次访问当前表
#查询员工名和对应上级的名称
SELECT
	e.last_name,
	m.last_name
FROM
	employees e,
	employees m
WHERE
	e.`manager_id` = m.`employee_id`;

sql99语法（推荐）

  • 语法
SELECT
	查询列表
FROM
	表1 别名 [连接类型]
JOIN
	表2 别名
ON
	连接条件
[WHERE]
[GROUP BY]
[HAVING]
[ORDER BY]

内连接-inner

  • 特点：查询的内容在各自表中都有

等值连接

#查询员工名、部门名
SELECT
	last_name,
	department_name
FROM
	employees e
[INNER] JOIN
	departments d
ON
	e.`department_id` = d.`department_id`;

非等值连接

#查询员工每个工资级别的>20的个数，并按工资级别降序
SELECT
	COUNT(*),
	grade_level
FROM
	employees e
JOIN
	job_grade g
GROUP BY
	grade_level
HAVING
	COUNT(*)>20
ORDER BY
	grade_level DESC;

自连接

#查询员工名字和上级的名字
SELECT
	e.last_name,
	m.last_name
FROM
	employees e
JOIN
	employees m
ON
	e.`manager_id` = m.`employees_id`;

外连接-outer

  • 特点：查询一个表中有，另一个表中没有的记录

    • 外连接的查询结果为主表中的所有记录

      如果从表中没有和它匹配的则显示null

      外连接查询结果 = 内连接结果 + 主表有儿从表中没有的记录

      全外查询结果=内连接结果+表1中有但表2中没有+表2中有但表1中没有的

    • 左外连接：left join左边的是主表

      右外连接：right join右边的是主表

      全外连接：full

    • 左外和右外交换两个表的顺序，可以实现同样的效果

左外连接

#查询男朋友不在男神表的女神名
SELECT
	b.name,
	bo.*
FROM
	beatuy b
LEFT OUTER JOIN
	boys bo
ON
	b.`boyfriend_id` = bo.`id`
WHERE
	bo.`id` IS NULL;

右外连接

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

全外连接

#全外连接
SELECT
	b.*,
	bo.*
FROM
	beauty b
FULL OUTER JOIN
	boys bo
ON
	b.`boyfriend_id` = bo.id;

交叉连接-cross

SELECT
	b.*,
	bo.*
FROM
	beauty b
CROSS JOIN
	boys bo;

进阶7-子查询

  • 含义：出现在其他语句中的select语句，称为子查询或内查询

    外部的查询语句，称为主查询或外查询

  • 分类：

    • 按子查询出现的位置

      where或having后面

      select后面：只支持标量子查询

      from后面：支持表子查询

      exists后面（相关子查询）

    • 按结果集的行列数不同

      标量子查询（结果集只有一行一列）

      列子查询（结果集只有一行多列）

      行子查询（结果集有一行多列）

      表子查询（结果集一般为多行多列）

where后面

标量子查询

  • 查询谁的工资比Abel高

1.查询Abel的工资

SELECT
	salary
FROM
	employees
WHERE
	last_name = 'Abel';

2.查询满足salary>1结果的员工信息

SELECT
	*
FROM
	employees
WHERE
	salary > ( 
        SELECT 
        	salary 
        FROM 
        	employees 
        WHERE 
        	last_name = 'Abel' );

列子查询

in

  • 返回location_id是1400或1700的部门中的所有员工姓名

1.查询location_id是1400或1700的部门编号


SELECT DISTINCT
	departmen_id
FROM
	departments
WHERE
	location_id IN (1400,1700)

2.查询部门编号是1中某一个的员工姓名

SELECT
	last_name
FROM
	employees
WHERE
	department_id IN (
        SELECT DISTINCT
			departmen_id
		FROM
			departments
		WHERE
			location_id IN (1400,1700));

any/all

  • 查询其他部门中比job_id为’IT_PROG’部门任意一个员工的工资低的员工的员工号、姓名、job_id 以及salary

1.查询job_id为’IT_PROG’部门的工资

SELECT DISTINCT
	salary
FROM
	employees
WHERE
	job_id = 'IT_PROG';

2.查询salary<1的员工的员工号、姓名、job_id 以及salary

SELECT
	last_name,
	employees_id,
	job_id,
	salary
FROM
	employees
WHERE
	salary < ANY(
    	SELECT DISTINCT
			salary
		FROM
			employees
		WHERE
			job_id = 'IT_PROG'
)AND
	job_id <> 'IT_PROG';

行子查询

  • 查询员工编号最小并且工资最高的员工信息

1.查询最小的员工编号

SELECT
	MIN(employee_id)
FROM
	employees;

2.查询最高工资

SELECT
	MAX(salary)
FROM
	employees;

3.查询员工信息

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

select后面

  • 查询每个部门的员工个数
SELECT
	d.*,(
    SELECT
    	COUNT(*)
    FROM 
        employees e
    WHERE
        e.department_id = d.`department_id`
	)
FROM
	department d;
  • 查询员工号=102的部门名
SELECT
	(
    SELECT
    	department_name
    FROM
    	departments d
    INNER JOIN
    	employees e
    ON
    	d.department_id = e.department_id
    WHERE
    	e.employees_id = 102)

from后面

  • 查询每个部门平均工资的工资等级

1.查询每个部门的平均工资

SELECT
	AVG(salary),department_id
FROM
	employees
GROUP BY
	department_id

2.连接1的结果集和job_grades表，筛选条件平均工资 between lowest_sal and highest_sal

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

exists后面（相关子查询）

  • 查询有员工的部门名
SELECT
	department_name
FROM
	departments
WHERE EXISTS (
	SELECT
		*
	FROM
		employees e
	WHERE
    	d.`department_id` = e.`department_id`
);

进阶8-分页查询-limit

  • 语法
SELECT
	查询列表
FROM
	表
【...
】
limit
	offset,size;
#offset：要显示的条目的起始索引（从0开始）(从0开始时可以省略)
#size：要显示的条目个数
  • 查询前五条员工信息
SELECT * FROM employees limit 0,5;
#SELECT * FROM employees limit 5;
  • 查询11-25条信息
SELECT * FROM employees limit 11,15;

进阶9-联合查询-union

  • 含义：将多条查询语句的查询结果合并

  • 语法：

    查询语句1
UNION
查询语句2
UNION
...

  • 应用场景：要查询的结果来自多个表，且表之间没有连接关系，但查询信息一致时

  • 特点

  1. 要求多条查询语句的查询列数是一致的
  2. 要求多条查询语句的查询的每一列的类型和顺序最好一致
  3. union关键字默认去重，如果不需要去重使用union all
  • 查询中国用户中年男性的信息以及外国用户中年男性的用户信息
SELECT id,cname FROM t_ca WHERE csex='男'
UNION
SELECT t_id,tname FROM t_ua WHERE tGender='male';

参考资料

本博客使用的数据库（提取码k6kq）

课程学习