MySQL - 1常见函数&查询汇总

记录一些必要的关键字与函数

1.DML(Data Manipulation Language) 数据操纵语言

1.1 select - 查询

1.1.1 基础查询

select 查询的东西(列表) from 来自的表名

基础查询可进行如下查询：

查询单个字段 select name from employees;
查询多个字段 select name, birth from employees;
查询表中所有字段 select * from employees;
查询常量值 select 100; select 'Jing';
查询表达式 select 100 / 2;
查询函数 select version()

注:

distinct关键字：去重，去除查询结果中的重复项
+符号：只有运算符的功能，无连接符的功能
1. 若两个字符均为数值型，进行加法运算
2. 若一方或均为字符型，尝试转换为数值型再运算
3. 只要有一方为null，结果就为null
concat() 函数：将查询字合并，可为合并项起别名
- eg. SELECT CONCAT(last_name, first_name) 姓名

1.1.2 条件查询

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

筛选条件分类：

条件表达式筛选：
- 条件运算符：<, >, =判断相等, <>(!=) 判断不等, >=, <=
- between and 相当于 >= and <=
- is null 与 is not null 查询值为或不为空 (不可用= != null)
- 安全等于 <=> 除判断值是否相等外还可与null进行比较
逻辑表达式筛选：
- 逻辑运算符：and-&&, or-||, not - !
- in 类似于多个or，in列表中的值类型必须兼容且不可使用通配符
模糊查询：like关键字,常与通配符搭配使用
- 常见通配符：% 表示任意多个字符、_表示任意单个字符
- eg. last_name LIKE '%a%' 表示姓中含字母a的筛选条件

1.1.3 排序查询

order by 排序列表 [desc/asc]
desc-降序， asc升序，默认asc
一般置于查询语句最后面(limit除外)

1.1.4 分组查询

分组函数做条件置于having子句中
优先使用分组前筛选
分组后也可添加排序置于分组查询最后 - group by 介于 where 与 order by

	数据源	位置	关键字
分组前筛选	原始表	group by子句前	where
分组后筛选	分组后的结果集	group by子句后	having

eg. 查询有奖金的工种中最高工资大于12000的工种id与最高工资

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

1.1.5 连接查询

又称多表查询，当查询字段来自于多个表时使用连接查询
连接查询本质上即为将笛卡尔乘积进行条件筛选

1- sql92标准 - 仅支持内连接

多表等值连接的结果为多表交集部分
n表连接,至少需要n-1个连接条件
多表的顺序无要求
一般需要为表起别名
可以搭配前面介绍的所有子句使用(排序,筛选,分组...)

代码案例:

#案例1.查询女神名和对应男神名
SELECT NAME,boyName
FROM beauty,boys
WHERE beauty.`boyfriend_id`=boys.`id`;

#案例2.查询员工名和对应部门名

SELECT last_name,department_name
FROM employees,departments
WHERE employees.`department_id`=departments.`department_id`;

#- 为表起别名
#案例:查询员工号,工种号,工种名
SELECT last_name,em.`job_id`,job_title
FROM employees AS em,jobs AS jo
WHERE em.`job_id`=jo.`job_id`;
#当查询的两个连接表含有共同的索引时,添加表名消除歧义
#通过表起别名的方法简化语句,提高语句简洁度
#注,起别名后查询字段只能用别名
#from中两表顺序可以变更

#- 筛选条件
#案例:查询有奖金的员工名,部门名
SELECT last_name,department_name,commission_pct
FROM employees AS em,departments AS de
WHERE em.`commission_pct` IS NOT NULL
	AND em.`department_id`=de.`department_id`;

#案例:查询城市名中第二个字符为o的部门名和城市名
SELECT city,department_name
FROM locations AS l,departments AS d
WHERE l.`location_id`=d.`location_id`
	AND city LIKE '_o%';
	
#- 添加分组
#案例:查询每个城市的部门个数
SELECT city,COUNT(*) AS 部门个数
FROM locations AS l,departments AS d
WHERE l.`location_id`=d.`location_id`
GROUP BY city;

#案例:查询有奖金的每个部门名和部门的领导编号以及该部门饿最低工资
SELECT department_name,d.manager_id,MIN(salary)
FROM departments AS d,employees AS e
WHERE e.`commission_pct` IS NOT NULL
	AND d.`department_id`=e.`department_id`
GROUP BY department_name,d.`manager_id`; #确保二者共组(只以department_name分组不合理,因其可能有多个领导编号)

#- 添加排序
#案例:查询每个工种的工种名和员工个数,按员工个数降序
SELECT job_title,COUNT(*) AS 员工个数
FROM employees AS e, jobs AS j
WHERE e.`job_id` = j.`job_id`
GROUP BY job_title
ORDER BY 员工个数 DESC;

#- 实现三(多)表来连接
#案例:查询员工名,部门名和其所在城市
SELECT last_name,department_name,city
FROM employees AS e,departments AS d,locations AS l
WHERE d.`location_id`=l.`location_id`
	AND e.`department_id`=d.`department_id`;

#2. 非等值连接
#案例1: 查询员工的工资和工资急别
SELECT salary,grade_level
FROM employees AS e,job_grades AS j
#where salary >= lowest_sal and salary <= highest_sal;
WHERE salary BETWEEN lowest_sal AND highest_sal
ORDER BY salary;


#3. 自连接
#自表连接自表(利用别名)
#案例: 查询员工名及其上级名称
#由员工的名称对应的manager_id找到employee_id再找到对应上级名
SELECT e.last_name,e.manager_id,m.employee_id,m.last_name
FROM employees AS e,employees AS m#巧用别名,强👍
WHERE e.`manager_id`=m.`employee_id`;

2- sql99标准

语法：

select ...
from 表一 别名[连接类型]
join 表二 别名
on 连接条件
[where 筛选条件]
[group by分组]
[having 组内筛选]
[order by排序列表]

内连接：inner 等值、非等值、自连接
外连接：
- 外连接查询结果为主表中的所有记录:
  1. 若从表有主表对应匹配值，则显式匹配值
  2. 若从表无匹配值，则显式nnull
  3. 外连接查询结果=内连接结果+主表有从表无的记录
1. 左外：left (outer) 左边为主表
2. 右外：right(outer) 右边为主表
3. 全外: (mysql不支持) full(outer) 内连接结果+表1有表2没有+表2有表1没有
交叉连接：cross

内连接代码：

#1.等值连接
/*
特点:
1.添加排序,分组,筛选
2.inner可省略
3.筛选条件可置于where后面,连接条件置于on后面,提高分离性便于阅读
4.inner join连接和sql92语法中的等值连接效果一样,都查询多表交集
*/
#案例1:查询员工名,部门名
SELECT last_name,department_name
FROM employees AS e
INNER JOIN departments AS d
ON e.`department_id`=d.`department_id`;
#案例2:查询名字中包含e的员工名和工种名
SELECT last_name,department_name
FROM employees AS e
INNER JOIN departments AS d
ON e.`department_id`=d.`department_id`
WHERE e.last_name LIKE '%e%';
#案例3.查询部门个数大于3的城市名和部门个数
SELECT city,COUNT(*) AS 部门个数
FROM locations AS l
INNER JOIN departments AS d
ON l.`location_id`=d.`location_id`
GROUP BY city
HAVING 部门个数>3;

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

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

#2. 非等值连接
#案例:查询员工工资级别
SELECT grade_level,COUNT(*)
FROM employees AS e
INNER JOIN job_grades AS jg
ON salary BETWEEN jg.`lowest_sal` AND jg.`highest_sal`
GROUP BY grade_level
ORDER BY grade_level DESC;

#3. 自连接
#案例:查询员工及其上级姓名
SELECT CONCAT(e.`last_name`,' ',e.`first_name`) AS employee,
	CONCAT(m.`last_name`,' ',m.`first_name`) AS manager
FROM employees AS e
INNER JOIN employees AS m
ON e.`manager_id`=m.`employee_id`
WHERE e.`last_name` LIKE '%k%' OR e.`first_name` LIKE '%k%';

外连接代码：

#左外连接
SELECT NAME,boyName
FROM beauty g
LEFT OUTER JOIN boys AS b
ON g.`boyfriend_id`=b.`id`
ORDER BY boyName;
#右外
SELECT NAME,boyName
FROM boys AS b
RIGHT OUTER JOIN beauty AS g
ON g.`boyfriend_id`=b.`id`
ORDER BY boyName;

#案例: 查询哪个部门没有员工
SELECT department_name
FROM departments AS d
LEFT OUTER JOIN employees AS e
ON d.`department_id`=e.`department_id`
WHERE e.`department_id` IS NULL;

#全外连接 (mysql不支持)

SELECT NAME,boyName
FROM beauty g
FULL OUTER JOIN boys b
ON g.`boyfriend_id`=b.`id`;

#Ⅲ. 交叉连接(笛卡尔乘积结果)
SELECT NAME,boyName
FROM beauty g
CROSS JOIN boys b
#ON g.`boyfriend_id`=b.`id`;

#案例:查询部门名为SAL或IT的员工信息
SELECT e.*
FROM employees AS e
RIGHT OUTER JOIN departments AS d
ON d.department_id=e.department_id
WHERE d.department_name IN('SAL','IT')
AND d.department_id IS NOT NULL;

1.1.6 子查询

(嵌套查询) 出现在其他语句中的select语句
1. select后面

按子查询出现位置分类：

select后：仅支持标量子查询
from后：仅支持表子查询
where或having后：
1. 标量子查询
2. 列子查询
3. 行子查询
exists后(相关子查询)：表子查询

按查询结果集行数不同分类：

标量子查询：结果集只有一行一列
列子查询：结果集只有一列多行
行子查询：结果集有一行多列
表子查询：结果集一般为多列多行

子查询解析&代码

#1. 标量子查询
#案例1: 谁的工资比Abel高?
SELECT *
FROM employees
WHERE salary>(
	SELECT salary
	FROM employees
	WHERE last_name='Abel'
);

#案例2: 查询job_id与141号员工相同,salary比143号员工多的员工 姓名, job_id和工资
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: 返回公司工资最少的员工信息
SELECT *
FROM employees
WHERE salary =(
	SELECT MIN(salary)
	FROM employees
);

#案例4:查询最低工资大于50号部门最低工资的部门id和其最低工资
SELECT department_id,MIN(salary)
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
HAVING MIN(salary) > (
	SELECT MIN(salary)
	FROM employees
	WHERE department_id = 50
);

#非法使用标量子查询

#2. 列子查询(多行子查询)
#案例1: 返回location_id是1400或1700的部门中所有的员工姓名

#内连接:
SELECT DISTINCT last_name
FROM employees AS e, departments AS d
WHERE d.`location_id` IN(1400, 1700)
	AND e.`department_id`=d.`department_id`;

#子查询
1.查询location_id是1400/1700的部门号
SELECT DISTINCT department_id
FROM departments
WHERE location_id IN(1400, 1700)

2.查询员工姓名,要求部门号是1列表中的某一个

SELECT last_name
FROM employees
WHERE department_id IN( #IN 与 = ANY类似
	SELECT DISTINCT department_id
	FROM departments
	WHERE location_id IN(1400, 1700)
)

#案例2: 返回其他部门中比job_id为'IT_PROG'部门任意工资低的员工的工号,姓名,job_id,salary
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE salary < (
	SELECT MAX(salary)
	FROM employees
	WHERE job_id IN('IT_PROG')
)
AND job_id NOT IN('IT_PROG')

#3. 行子查询(一行多列/多行多列)
#案例: 查询员工编号最小中工资最高的员工信息
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后面
#案例: 查询每个部门的员工个数

#方法1:分组(无法查出没有员工的department)
SELECT department_id, COUNT(*)
FROM employees e
GROUP BY department_id

#方法2:select子查询 只能是一行一列
SELECT *,(
	SELECT COUNT(*)
	FROM employees AS e
	WHERE e.department_id = d.department_id
) AS 个数
FROM departments d;

#案例: 查询员工号=102的部门名
SELECT department_name
FROM departments d, employees e
WHERE e.`employee_id`=102
	AND e.`department_id`=d.`department_id`;
	
#法2:
SELECT (
	SELECT department_name
	FROM departments d
	INNER JOIN employees e
	ON d.department_id=e.department_id
	WHERE e.employee_id=102
)AS 部门

#三. from后面
#将子查询结果充当一张表,要求必须起别名

#案例: 查询每个部门的平均工资的工资等级

SELECT avg_dep.*, grade_level
FROM(
	SELECT department_id, AVG(salary) AS sal
	FROM employees
	GROUP BY department_id
) AS avg_dep
INNER JOIN job_grades AS jo
ON avg_dep.sal BETWEEN lowest_sal AND highest_sal

#四. exists后面(相关子查询)
/*
exists(完整的查询语句),返回1(存在),0(不存在)
一般都可以代替
*/
SELECT EXISTS(SELECT employee_id FROM employees) AS isExit

#案例1:查询有员工的部门名
SELECT department_name
FROM departments d
#法一:exists
WHERE EXISTS(
	SELECT *
	FROM employees e
	WHERE e.department_id = d.department_id
)
#法二:in
WHERE d.department_id IN(
	SELECT department_id
	FROM employees
)

#案例2:查询没有女朋友的男神信息

#法一:in
SELECT boyName
FROM boys b
WHERE b.id NOT IN(
	SELECT boyfriend_id
	FROM beauty g
)

#法二:exists
SELECT boyName
FROM boys b
WHERE NOT EXISTS(
	SELECT boyfriend_id
	FROM beauty g
	WHERE b.id=g.boyfriend_id
)


#1.查询和Zlotkey相同部门的员工姓名和工资
SELECT last_name,salary
FROM employees
WHERE department_id=(
	SELECT department_id
	FROM employees
	WHERE last_name='Zlotkey'
)
#2.查询工资比公司平均工资高的员工的员工号,姓名和工资
SELECT employee_id,last_name,salary
FROM employees
WHERE salary>(
	SELECT AVG(salary)
	FROM employees
)
#3.查询各部门中工资比本部门平均工资高的员工的员工号,姓名,工资
#my step
SELECT department_id,last_name,salary
FROM employees e1
WHERE salary>(
	SELECT AVG(salary)
	FROM employees e2
	WHERE e1.department_id=e2.department_id
)
ORDER BY department_id

#step one:查询各部门平均工资
SELECT department_id,AVG(salary)
FROM employees
GROUP BY department_id

#step two:
SELECT e1.department_id,employee_id,last_name,salary
FROM employees e1
INNER JOIN(
	SELECT department_id,AVG(salary) ag
	FROM employees
	GROUP BY department_id
) a1
WHERE e1.department_id=a1.department_id
AND e1.salary>a1.ag
ORDER BY department_id
#4.查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名
SELECT employee_id,last_name
FROM employees
WHERE department_id IN(
	SELECT department_id
	FROM employees e
	WHERE e.last_name LIKE('%u%')
)

#5.查询在部门的location_id为1700的部门工作的员工的员工号
SELECT employee_id
FROM employees e
WHERE e.department_id IN(
	SELECT department_id
	FROM departments
	WHERE location_id=1700
)

#6.查询管理者是king的员工姓名和工资
#用in更普适(若结果表中包含超过一个值)
SELECT last_name,salary
FROM employees
WHERE manager_id IN(
	SELECT employee_id
	FROM employees e
	WHERE e.last_name='K_ing'
)

#7.查询工资最高的员工的姓名,要求first_name和last_name显示为一列,列明为姓,名
SELECT CONCAT(last_name,' ',first_name) AS 姓.名
FROM employees e
WHERE e.salary=(
	SELECT MAX(salary)
	FROM employees
)

1.1.7 联合查询

应用场景：当要查询的结果来自于多个表且表间无直接连接关系但查询信息一致时使用。
语句：union 将多条查询语句合并为一个结果(同结构)
特点：
1. 要求多条查询语句查询列数一致
2. 多条查询语句查询的每列类型与顺序最好一致
3. union关键字自动去重(union all不会)

#引入案例: 查询部门编号>90 || 邮箱号包含a的员工信息
SELECT *
FROM employees
WHERE department_id>90
UNION#合并查询
SELECT *
FROM employees
WHERE email LIKE '%a%'

1.1.8 分页查询

应用场景：当要显式的数据一页无法显式完全时，需分页提交sql请求
语句：limit offset,size; 限制范围
- (offset要显示条目的起始索引[从0开始],size要显示的条目个数)
特点：
1. limit语句置于查询语句最后
2. 表示size的公式：(显式页数为page，每页条目数为size)：
  - limit (page-1)*size, sizew

#案例:查询有奖金的,工资较高的前10名员工信息
SELECT *
FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY salary DESC
LIMIT 0,10;

1.1.9 查询语句执行顺序总结

	select 查询列表      ---7  #显示结果
	from 表             ---1 #先检索表
	[join...]	        ---2 #再检测连接顺序
	on 连接条件          ---3  #检测连接条件
	where ...           ---4  #检测筛选条件
	group by...         ---5  #分组
	having ...          ---6  #分组后筛选
	order by...         ---8  #排序
    limit offset,size;  ---最后 #分页

1.2 insert into - 插入

语法：
1. insert into 表名(列名...) values(值1,...);
2. INSERT INTO beauty SET id=14,NAME='三上悠亚',phone='13412412414'
要求:
1. 插入的值的类型与列的数据类型一致/兼容
2. 可以插入null(直接插null或不填列名)
3. 列的顺序可以调换
4. 列数和值个数必须一致
5. 可以省略列名，此时默认为所有列且复制顺序与表中列顺序一致
方式1与方式2比较：
1. 方式1支持插入多行，方式2不支持INSERT INTO beauty(id, NAME, sex, borndate, phone,photo,boyfriend_id) VALUES(13,'蔡徐坤','女','1990.4.23','1342412442',NULL,2), (13,'蔡徐坤','女','1990.4.23','1342412442',NULL,2), (13,'蔡徐坤','女','1990.4.23','1342412442',NULL,2),
2. 方式1支持子查询，方式2不支持INSERT INTO beauty(id,NAME,phone) SELECT 15,'宋茜','1412412412'

方式1示例：

INSERT INTO beauty(id, NAME, sex, borndate, phone,photo,boyfriend_id)
VALUES(13,'蔡徐坤','女','1990.4.23','1342412442',NULL,2);

方式2示例:

INSERT INTO beauty
SET id=14,NAME='三上悠亚',phone='13412412414'

1.3 update - 修改

语法：update 表名 set 列=新置,...(类型一致/兼容) where 筛选条件;
修改多表条记录：
1. sql92：
  
  update 表名 set 列=新置,...(类型一致/兼容) where 筛选条件;
2. sql99:
  
  update 表1 别名 inner|left|right join 表2 别名 on 连接条件 set 列=值,... where 筛选条件;

修改示例:

#案例1:修改beauty表中蔡徐坤性别改成男
UPDATE beauty
SET sex='男'
WHERE id=13
#案例2:修改boys鹿晗改为张飞,魅力值改为10
UPDATE boys
SET boyName='张飞',userCP=10
WHERE boyName LIKE'%鹿%'

#案例3:修改张无忌的女朋友手机号为114
UPDATE beauty g
INNER JOIN boys b
ON g.boyfriend_id=b.id
SET g.phone='144'
WHERE b.boyName='张无忌'

#案例4:修改没有男朋友的女神的男朋友编号都为张飞的编号(2)
UPDATE beauty g
LEFT JOIN boys b
ON g.boyfriend_id=b.id
SET g.boyfriend_id=(
	SELECT id
	FROM boys
	WHERE boyName='张飞'
)
WHERE b.id IS NULL

1.4 delete - 删除

1.4.1 delete

单表删除：delete from 表明 where 筛选条件
多表删除
1. sql92:
  
  delete (表1/表2)别名 from 表1 别名,表2 别名 where 连接条件 and 筛选条件;
2. sql99:
  
  delete (表1/表2)别名 from 表1 别名,表2 别名 where 连接条件 and 筛选条件;

delete语句示例:

#1.单表删除
#案例1:删除手机编号最后一位为9的女神
DELETE g
FROM beauty g
WHERE phone LIKE '%9'

#2.多表删除
#案例:删除张无忌的女朋友的信息
DELETE g
FROM beauty g
INNER JOIN boys b
ON g.boyfriend_id =(
	SELECT id
	FROM boys
	WHERE boyName='张无忌'
)

#案例:删除段誉以及其女朋友信息
DELETE g,b
FROM beauty g
INNER JOIN boys b
ON g.boyfriend_id=b.id
WHERE b.boyName='段誉'

1.4.2 truncate

语法：truncate table 表名；-删除整表
delete 与 truncate比较
1. delete可以加where条件,truncate不可
2. truncate删除效率高(整表)
3. 若要删除的表中有自增长列,
  若用delete删除后再插入数据,自增长列的值从断点开始
  若用truncate删除后再插入数据,自增长列值从1开始
4. truncate删除无返回值，delete删除返回影响行数
5. truncate不可回滚，delete可回滚

2. 一些常见函数

2.1 字符函数

length()：查询获取参数值字节个数(汉语字符根据字符集的不同所代表的字节数也有不同)
concat()：字符串拼接函数，一般用于拼接查询列表
upper(),lower()：将给定字符全部转为大/小写
substr()：截取给定位置字符，有四种重载
- 注：MySQL中索引从1开始而非0
instr(a, b)：返回子串(b)在大字符串(a)中的起始索引，若无则返回0
trim()：去除字符前后空格(默认)或其他指定字符或字符串(x from y)
- eg. SELECT TRIM('a' FROM 'aa我爱你aa非常的aaaa');
lpad / rpad (a, str, b) ：用指定字符实现左/右填充至指定长度
replace(a, str, b)：用b替换str中的a

2.2 数学函数

round(a, (b))：四舍五入, -保留指定(b)位数
cell / floor()：向上/向下取整 - 返回结果大于等于 / 小于等于该参数的最小整数
truncate()：截断，截取小数点后几位 (无取舍)
mod(a, b)：取余 : a - a / b * b

2.3 日期函数

now()：返回当前系统日期与实践
curdate()：返回当前系统日期
curtime()：返回当前实践
hour(time)：获取给定time的小时数
str_to_date(将日期格式字符转换成指定格式日期)
- %Y 四位年份
  %y 两位年份
  %M 英文月份
  %m 两位月份
  %c 一位(可能)月份
  %d 日
  %H 小时(24)
  %h 小时(12)
  %i 分钟
  %s 秒
- eg. SELECT *
  FROM employees
  WHERE hiredate = STR_TO_DATE('4-3 1992','%m-%d %Y');
data_format(time, str): 将日期time转换成字符str
- eg. SELECT DATE_FORMAT(NOW(),'%y年%M月%d日');

2.4 分组函数

sum(), avg(), max(), min(), count()

分组函数一般用作统计使用，一般用作分组判断依据
1. sum, avg一般用于处理数值型
2. max, min, count可处理任何数据类型
3. 可与distinct搭配实现去重
4. 与分组函数统一查询字段要求：查询所得字段列数相同

2.5 其他函数

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

posted @ 2021-09-30 17:16 rttrti 阅读(139) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

rttrti