MySQL学习笔记2【函数/约束/多表查询】

embedded/2025/1/11 20:45:40/

MySQL学习笔记

函数

字符串函数
函数功能
CONCAT(s1, s2, …, sn)字符串拼接，将s1, s2, …, sn拼接成一个字符串
LOWER(str)将字符串全部转为小写
UPPER(str)将字符串全部转为大写
LPAD(str, n, pad)左填充，用字符串pad对str的左边进行填充，达到n个字符串长度
RPAD(str, n, pad)右填充，用字符串pad对str的右边进行填充，达到n个字符串长度
TRIM(str)去掉字符串头部和尾部的空格
SUBSTRING(str, start, len)截取字符串，从start开始截取len长度的字符串，索引从1开始
REPLACE(column, source, replace)替换字符串
数值函数
函数功能
CEIL(x)向上取整
FLOOR(x)向下取整
MOD(x, y)返回x/y的模
RAND()返回0~1内的随机数
ROUND(x, y)对x的四舍五入，保留y位小数
日期函数
函数功能
CURDATE()返回当前日期
CURTIME()返回当前时间
NOW()返回当前日期和时间
YEAR(date)获取指定date的年份
MONTH(date)获取指定date的月份
DAY(date)获取指定date的日期
DATE_ADD(date, INTERVAL expr type)返回一个日期/时间值加上一个时间间隔expr后的时间值
DATEDIFF(date1, date2)返回起始时间date2和结束时间date1之间的天数

一个值得说的案例，查询员工入职天数，降序排序：

SELECT 
username, DATEDIFF(NOW(), entrydate) AS entrydays 
FROM users 
ORDER BY entrydays DESC;
流程控制函数

  1. IF(value, t, f)

    • 功能：评估 value 的真假。
    • 用法：如果 value 为真，返回 t；否则返回 f。
    • 示例：
      SELECT IF(age >= 18, 'Adult', 'Minor') AS age_group FROM users;

  2. IFNULL(value1, value2)

    • 功能：检查 value1 是否为空。
    • 用法：如果 value1 不为空，返回 value1；如果为空，返回 value2。
    • 示例：
      SELECT IFNULL(nickname, username) AS display_name FROM users;

  3. CASE WHEN [ val1 ] THEN [ res1 ] … ELSE [ default ] END

    • 功能：执行多个条件的检查。
    • 用法：如果 val1 为真，返回 res1，依此类推；如果都不满足，则返回 default。
    • 示例：
      SELECT CASE WHEN score >= 90 THEN 'A'WHEN score >= 80 THEN 'B'ELSE 'C'END AS grade
FROM scores;

  4. CASE [ expr ] WHEN [ val1 ] THEN [ res1 ] … ELSE [ default ] END

    • 功能：与第3个函数类似，但通过评估一个表达式来进行条件判断。
    • 用法：如果 expr 等于 val1，返回 res1；如果不匹配，返回 default。
    • 示例：
      SELECT CASE user_typeWHEN 'admin' THEN 'Administrator'WHEN 'user' THEN 'Regular User'ELSE 'Unknown'END AS user_role
FROM users;

约束

约束条件关键字说明
主键PRIMARY KEY唯一标识表中的每一行，主键列的值不能重复且不能为空。
自动增长AUTO_INCREMENT使字段在插入新行时自动增加（常用于主键）。
不为空NOT NULL指定字段不能接受空值。
唯一UNIQUE确保字段的所有值都是唯一的，允许空值，但不能重复。
逻辑条件（条件一定要加上括号）CHECK(约束条件)用于限制列中的值满足某些条件，必须使用括号以包围条件表达式。
默认值DEFAULT指定列的默认值，插入时如果不提供该列的值，则使用默认值。
  • 值得一提的是，如果想要同时插入多个数据，只要有一条数据不满足约束，这条语句全部执行失败，就算其他数据符合约束条件，也不会插入进数据。
  • 插入数据违反UNIQUE约束时，自增主键将会+1。
删除/更新行为

ON DELETE/UPDATE

行为说明
NO ACTION当在父表中删除或更新对应记录时，首先检查该记录是否有对应的外键。如果有，系统将不允许删除或更新（这与 RESTRICT 行为一致）。
RESTRICT与 NO ACTION 一样，删除或更新前会检查是否存在对应外键，如果存在，则不允许进行操作。
CASCADE当在父表中删除或更新对应记录时，如果该记录有对应的外键，系统会自动删除或更新子表中相关的记录。
SET NULL当在父表中删除或更新对应记录时，如果该记录有对应的外键，系统会将子表中该外键的值设置为 NULL（前提是该外键允许为 NULL）。
SET DEFAULT当父表的记录发生变更时，子表中的外键将被设为一个默认值（InnoDB 不支持此选项）。

多表查询

关系类型定义示例描述数据库表示示例
一对多一个表中的一条记录可以关联到另一个表中的多条记录，而另一表中的每条记录只能关联到一条记录。员工对应一个部门，部门有多个员工department 表与 emp 表，通过 dept_id 关联。
多对多一个表中的多条记录可以关联到另一个表中的多条记录。通常需要一个关联表来实现这种关系。一个学生可以选修多门课程，一门课程可以被多个学生选修。students 表与 courses 表，通过 student_courses 关联表。
一对一一个表中的一条记录只能关联到另一个表中的一条记录，反之亦然。每个用户有一条用户详情记录。users 表与 user_details 表，通过 user_id 关联。
  • 一对一可以用来对单表进行拆分，结构更加清晰。
1. 合并查询（笛卡尔积）

  • 定义：合并查询会显示两个表的所有组合结果，即笛卡尔积, 可以理解为19和09可以组合成多少个2位数

  • 示例：

    SELECT * FROM employee, dept;
2. 内连接查询

  • 定义：内连接只返回两张表交集的部分，即满足连接条件的记录。

  • 隐式内连接：

    • 通过逗号分隔表名和用 WHERE 子句指定连接条件：
    SELECT e.name, d.name FROM employee AS e, dept AS d WHERE e.dept = d.id;

  • 显式内连接：

    • 使用 JOIN 关键字来显示连接关系，语法更清晰：
    SELECT e.name, d.name FROM employee AS e INNER JOIN dept AS d ON e.dept = d.id;
3. 外连接查询

  • 左外连接（LEFT OUTER JOIN）：

    • 返回左表（employee）的所有数据，包含与右表（dept）交集部分的数据。
    SELECT e.*, d.name FROM employee AS e LEFT OUTER JOIN dept AS d ON e.dept = d.id;

  • 右外连接（RIGHT OUTER JOIN）：

    • 返回右表（dept）的所有数据，包含与左表（employee）交集部分的数据。
    SELECT d.name, e.* FROM employee AS e RIGHT OUTER JOIN dept AS d ON e.dept = d.id;

  • tips:

    • 左连接可以查询到没有部门的员工，右连接可以查询到没有员工的部门，此处可以做这道题练习一下
4. 自连接查询

  • 定义：当前表与自身进行连接查询，需要使用表别名。

  • 语法：

    SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ...;

  • 示例：

    • 查询员工及其所属领导的名字：
    SELECT a.name, b.name FROM employee AS a, employee AS b WHERE a.manager = b.id;
    • 查询没有领导的员工：
    SELECT a.name, b.name FROM employee AS a LEFT JOIN employee AS b ON a.manager = b.id;
5. 联合查询
SELECT 字段列表 FROM 表A
UNION [ALL]
SELECT 字段列表 FROM 表B;
注意事项

  1. 去重行为：

    • UNION：会去掉重复的记录，只返回唯一的结果集。
    • UNION ALL：包括所有结果，保留重复记录。

  2. 查询效率：

    • 在某些情况下，使用UNION可能会比OR效率高。这是因为对于UNION，数据库可以对每个查询的结果集进行合并，而避免了对整个数据表的扫描。
    • 使用OR条件可能会导致某些情况下索引(现在还不知道这个是什么, 姑且先记一下)失效，从而导致查询性能下降。

  3. 字段要求：

    • 使用UNION或UNION ALL时，所有的SELECT查询返回的字段数和数据类型必须一致。

  4. 执行顺序：

    • 如果查询的顺序很重要，可以使用ORDER BY对最终的结果集进行排序，但需要注意ORDER BY只能在最后一个查询之后使用。
使用示例
-- 查询两个表中用户的名字，去重
SELECT name FROM Customers
UNION
SELECT name FROM Employees;-- 查询两个表中用户的名字，包括重复
SELECT name FROM Customers
UNION ALL
SELECT name FROM Employees;
6.子查询

嵌套查询，也称为子查询，是一个查询嵌入在另一个查询内部。这种结构可以让我们在一个查询中利用另一个查询的结果。

子查询的分类

  1. 根据结果形式：

    • 标量子查询：返回单个值（如一个数字、一个字符串等）。
    • 列子查询：返回一列，可以是多行。
    • 行子查询：返回一行，包含多个列。
    • 表子查询：返回多行多列的结果。

  2. 根据位置：

    • WHERE之后：用于过滤主查询的结果。
    • FROM之后：作为数据源提供给主查询。
    • SELECT之后：用于计算和生成字段值。
常用的操作符
  • 标量子查询：常用操作符包括=, <, >, >=, <=。
  • 列子查询：常用操作符包括IN, NOT IN, ANY, SOME, ALL。
  • 行子查询：可以使用=, <, >, IN, NOT IN。
  • 表子查询：多用于IN操作符。
示例分析

  1. 标量子查询示例：

    SELECT * 
FROM employee 
WHERE entrydate > ( #子查询返回一个常量SELECT entrydate FROM employee WHERE name = 'xxx'
);

  2. 列子查询示例：

    SELECT * 
FROM employee 
WHERE dept 
IN (	#子查询返回很多列,如果dept存在于查询的结果之中,则算满足条件.SELECT id FROM dept WHERE name = '销售部' OR name = '市场部'
);

  3. 行子查询示例：

    SELECT * 
FROM employee
WHERE (salary, manager) = (   #查询一列,返回N行,如果这几行全部相等,那么则算满足条件,SELECT salary, manager FROM employee WHERE name = 'xxx'
);

  4. 表子查询示例：

    SELECT e.*, d.* 
FROM (  #查询一张表,从这张表中再进行条件过滤或者左连接SELECT * FROM employee WHERE entrydate > '2006-01-01'
) AS e
LEFT JOIN dept AS d 
ON e.dept = d.id;

​ TIPS:表字查询也可以放在WHERE '字段' IN后面, 和列子查询类似,只要满足其中一列全部相等,那么则算满足条件

值得一提的案例
SELECT d.id, 	#此处可以理解为先从FROM中查询到了id，再将id传入子查询中，通过count得出人数d.name, (SELECT COUNT(*) FROM emp e WHERE e.dept_id = d.id) AS '人数'
FROM dept d;

结语

该篇笔记主要涵盖了关于MySQL基本的函数/约束以及多表查询的相关内容，如果有什么问题，欢迎留言，希望对你也会有帮助。


http://www.ppmy.cn/embedded/153119.html

相关文章

如何使用Scala和Selenium爬取知乎视频并保存到本地

如何使用Scala和Selenium爬取知乎视频并保存到本地

一、环境准备 在开始之前&#xff0c;我们需要确保已经安装了以下环境和工具&#xff1a; Java开发环境&#xff1a;Selenium是基于Java开发的&#xff0c;因此需要先安装Java开发环境&#xff0c;可以从Oracle官网下载并安装JDK 11或更高版本。Scala开发环境&#xff1a;可以…
阅读更多...
【面试题】技术场景 4、负责项目时遇到的棘手问题及解决方法

【面试题】技术场景 4、负责项目时遇到的棘手问题及解决方法

工作经验一年以上程序员必问问题 面试题概述 问题为在负责项目时遇到的棘手问题及解决方法&#xff0c;主要考察开发经验与技术水平&#xff0c;回答不佳会影响面试印象。提供四个回答方向&#xff0c;准备其中一个方向即可。 1、设计模式应用方向 以登录为例&#xff0c;未…
阅读更多...
通过一个含多个包且引用外部jar包的项目实例感受Maven的便利性

通过一个含多个包且引用外部jar包的项目实例感受Maven的便利性

目录 1 引言2 手工构建3 基于Maven的构建4 总结 1 引言 最近在阅读一本Java Web的书籍1时&#xff0c;手工实现书上的一个含多个Packages的例子&#xff0c;手工进行编译、运行&#xff0c;最终实现了效果。但感觉到整个构建过程非常繁琐&#xff0c;不仅要手写各个源文件的编…
阅读更多...
利用 Python 爬虫获取 1688 关键字 API 接口

利用 Python 爬虫获取 1688 关键字 API 接口

在当今电商蓬勃发展的时代&#xff0c;掌握市场动态、洞察消费者需求已成为商家制胜的关键。而 1688 作为中国领先的 B2B 电商平台&#xff0c;汇聚了海量商品与交易数据&#xff0c;其关键字 API 接口更是蕴含着丰富的市场信息。本文将详细解读如何借助 Python 爬虫技术&#…
阅读更多...
人工智能知识分享第九天-机器学习_集成学习

人工智能知识分享第九天-机器学习_集成学习

集成学习 概念 集成学习是机器学习中的一种思想&#xff0c;它通过多个模型的组合形成一个精度更高的模型&#xff0c;参与组合的模型称为弱学习器&#xff08;基学习器&#xff09;。训练时&#xff0c;使用训练集依次训练出这些弱学习器&#xff0c;对未知的样本进行预测时…
阅读更多...
Luogu P4688 [Ynoi2016] 掉进兔子洞

Luogu P4688 [Ynoi2016] 掉进兔子洞

很bt的一个题。 题目描述 您正在打 galgame&#xff0c;然后突然发现您今天太颓了&#xff0c;于是想写个数据结构题练练手&#xff1a; 一个长为 n n n 的序列 a a a。 有 m m m 个询问&#xff0c;每次询问三个区间&#xff0c;把三个区间中同时出现的数一个一个删掉&…
阅读更多...
[Git] git reset --hard / git reset --soft

[Git] git reset --hard / git reset --soft

git reset --hard 功能&#xff1a;重置索引&#xff08;暂存区&#xff09;和工作目录到指定的提交状态。这意味着它会丢弃所有未提交的更改和已暂存的更改。 适用场景&#xff1a;当你想要完全放弃当前工作目录中的所有更改并回退到某个特定提交状态时&#xff0c;可以使用这…
阅读更多...
量子计算遇上人工智能：突破算力瓶颈的关键？

量子计算遇上人工智能：突破算力瓶颈的关键？

引言&#xff1a;量子计算遇上人工智能——突破算力瓶颈的关键&#xff1f; 在数字化时代的浪潮中&#xff0c;人工智能&#xff08;AI&#xff09;正以前所未有的速度改变着我们的生活&#xff0c;从语音助手到自动驾驶&#xff0c;从医学诊断到金融分析&#xff0c;无不彰显其…
阅读更多...
最新文章

Copyright @ 2022~2023