MYSQL-MOSH第三章-5-复合连接条件20240705
使用多个条件连接两个表格
就是join内连接的条件里加个“AND”
例题：
表1:order_items，主键：order_id和product_id
表二：order_item_notes，主键：note_id
SELECT *
FROM order_items AS oi
JOIN order_item_notes AS oin
ON oi.order_id = oin.order_id
AND oi.product_id = oin.product_id注：运行后无对应匹配条件数据。。。
MYSQL-MOSH第三章-6-隐式连接语法20240706
隐式连接即内连接的条件改为WHERE，连接的两表放在FROM里
SELECT *
FROM orders o
JOIN customers c
ON o.customer_id = c.customer_id;
-- 隐式链接语法
-- 不建议用，忘记WHERE-会得到交叉连接的结果，就是正常10条数据，交叉连接为100条数据
SELECT *
FROM orders o,customers c
WHERE o.customer_id = c.customer_idMYSQL-MOSH第三章-7-外连接20240706
内连接取不到customer_id没下单的顾客;但是想要显示没下单的顾客并订单显示NULL，用外连接
外连接：LEFT（左连接）、RIGHT（右链接）
左、右是指在JOIN左右两边哪个表中的数据更全些，下图中JOIN右边的c表中customer_id是最全的，所有右表的数据都会被返回，不管条件正确还是错误
注：LEFT OUTER JOIN/RIGHT OUTER JOIN，这个OUTER关键词可有可无，与内连接INNER JOIN中的INNER关键词一样
MYSQL-MOSH第三章-8-多表外连接20240707
最好避免使用右链接，因为当连接多表时，同时存在左、右连接会使情况变复杂，降低可读性，尽量使用左连接（LEFT JOIN）
例题：
连接了o/c/sh/os四个表
MYSQL-MOSH第三章-9-自外连接20240707
自连接取不到空值NULL，可以用LEFT JOIN取自己表的空值
（20240707我不能像老师一样简单的描述出来，淦）
MYSQL-MOSH第三章-10-USING子句20240707
连接中的ON条件内两表列名相同，即可使用USING
例：
两行效果一致，且USING更具可读性
USING关键字只能在不同表中的列名完全一致的情况下使用
MYSQL-MOSH第三章-11-自然连接20240707
不建议使用，因为有时候会出现出乎意料的结果
数据库引擎会自己看着办，基于共同的列连接，我们无法控制具体结果
SELECT
*
FROM orders o
NATURAL JOIN customers c MYSQL-MOSH第三章-12-交叉连接20240707
交叉连接，也被称为笛卡尔积，是SQL中一种特殊的连接类型。它返回两个表之间的所有可能的匹配组合。简而言之，它将一个表的每一行与另一个表的每一行组合在一起，产生的结果是两个表中每行之间的所有可能的组合。
-- 交叉连接显式语法如下
SELECT
c.first_name AS customer,
p.name AS product
FROM customers c
CROSS JOIN products p
order by c.first_name
-- 交叉连接隐式语法如下
SELECT
c.first_name AS customer,
p.name AS product
FROM customers c,products p
order by c.first_name
MYSQL-MOSH第三章-13-unios联合20240707
目的：合并多条查询的数据（不同表格也可以）
注意，查询返回的列的数量一定要一样，否则就会得到错误提示
检索后的列名由第一段查询写的列名决定
例题：
小于2000分是青铜（Bronze）,2000到3000分是白银（Silver），大于3000分是黄金（Gold）
SELECT
customer_id,
first_name,
points,
'Bronze' AS type
FROM customers
WHERE points < 2000
UNION
SELECT
customer_id,
first_name,
points,
'Silver' AS type
FROM customers
WHERE points BETWEEN 2000 AND 3000
UNION
SELECT
customer_id,
first_name,
points,
'Gold' AS type
FROM customers
WHERE points > 3000
ORDER BY first_nameMYSQL-MOSH第四章-1-列属性[插入，更新和删除数据]20240708
列的数据类型
PK：主键的缩写，黄钥匙
标识
NN：not null,非空值
AI：自动递增的意思，通常被用在主键列
Default/Expression：默认值，图中birth_date生日和电话phone的默认值为空值，积分points的默认值为0
VARCHAR和CHAR的区别：前者是可变长度，后者是固定长度（不足的长度，MYSQL会差插入剩余的空格符填满该列）
一个汉字占2个字节。在ASCII码中，一个英文字母（不分大小写）占一个字节的空间，一个中文汉字占两个字节的空间2。
MYSQL-MOSH第四章-2-插入单行20240708
INSERT INTO
-- 在指定数据表插入
INSERT INTO customers
-- 子句；下方为表中各列注释
-- 列名：customer_id，数据类型：INT,主键，可自动递增；列属性有自动递增时，输入指定值或'DEFAULT'默认生成;200或DEFAULT
-- 列名：first_name和last_name,数据类型：VACHAR(50);SQL中字符串和日期值要带引号；'John','Smith',
-- 列名：birth_data，数据类型：DATE；可空值；'1990-01-01'或NULL
-- 列名：phone,数据类型：VACHAR(50);可空值；NULL
-- 列名：address/city，数据类型：VACHAR(50)，不可为空；'address','city'
-- 列名：state，数据类型：CHAR(2)，不可为空；'CA'
-- 列名：points积分，数据类型：INT；默认值为0；200或DEFAULT
VALUES (
DEFAULT,
'John',
'Smith',
'1990-01-01',
NULL,
'address',
'city',
'CA',
DEFAULT
)
-- 另一种写法
-- 在表名称后加入想要插入值的列
-- 这样写就不需要DEFAULT、NULL
INSERT INTO customers（
first_name,
last_name,
birth_date,
address,
city,
state)
VALUES (
'John',
'Smith',
'1990-01-01',
'address',
'city',
'CA')MYSQL-MOSH第四章-3-插入多行20240708
-- 一次性插入多行
-- values子句中一个括号就是一行，多个括号就是多行
-- 自动递增的行，删除后再添加，序号从删除的序号后加（添加1、2、3行后删除，再次添加的序号为4）
INSERT INTO shippers (shipper_id,name)
VALUES (6,'Shipper1'),
(7,'Shipper2'),
(8,'Shipper3')MYSQL-MOSH第四章-4-插入分层行
往多表里插入数据
例：
orders表，简称o
order_items表，简称oi
题目解释：o表是订单表，我下了一个订单，里面买了黄瓜+番茄；oi是订单项目表，记录我买的黄瓜的id、数量和单价
INSERT INTO orders (customer_id,order_date,status)
VALUES (1,'2019-01-02',1);
-- MySQL和其他数据库引擎自带内置功能
-- last_insert_id（最近插入id）能返回插入新行时MySQL生成的那个id
-- 上面插入的id通过这个语句查出，并加入到第二个表里
SELECT last_insert_id()
-- 完整语句为
INSERT INTO orders (customer_id,order_date,status)
VALUES (1,'2019-01-02',1);
INSERT INTO order_items
VALUES
(LAST_INSERT_ID(),1,1,2.95),
(LAST_INSERT_ID(),2,1,3.95)MYSQL-MOSH第四章-5-创建表复制
-- 创建新表语句:CREATE TABLE AS
-- 将表orders复制为表orders_archived
-- 复制后的表orders_archived没有标记主键和自动递增列
-- 这个方式创建新表时，MySQL会忽略这些属性
-- 原表内的所有数据也会被复制到新表中
CREATE TABLE orders_archived AS
SELECT * FROM orders删除数据表内所有数据，操作：
找到对应表-右击-选择【Truncate Table...】（英译中：截断表...）-确定
-- 从表orders中选择部分数据，插入到表orders_archived中
-- 条件为表order中数据早于20191月1日
INSERT INTO orders_archived
SELECT *
FROM orders
WHERE order_date < '2019-01-01'再次执行查询，会得到错误，因为已经有个这个名字的表了
删除表，操作：
找到对应表-右击-选择【Drop Table】（英译中：放置表）-确定
MYSQL-MOSH第四章-6-更新单行
-- 更新指定表格名称
UPDATE invoices
-- 更新指定列名数据，用逗号隔开
SET payment_total = 10,payment_date = '2019-03-01'
-- 更新的条件
WHERE invoice_id = 1MYSQL-MOSH第四章-7-更新多行
在MySQL工作台执行更新多行数据语句，会得到错误提示，因为默认状况相爱，MySQL工作台会在安全更新模式下运行，它只会让你更新一条记录；
如果你用别的MySQL客户端，或用应用程序代码写这段语句，都不会出现问题
解决操作：编辑-偏好-SQL Editor-最下方【Safe Updates (rejects UPDATEs and DELETEs with no restrictions)】，取消勾选
UPDATE invoices
SET payment_total = invoice_total * 0.5,
payment_date = due_date
-- 更新所有客户3和客户4的发票
-- 所有WHERE子句中的运算符，都可以用在这里
WHERE client_id IN (3,4)MYSQL-MOSH第四章-8-在Updates中使用子查询
-- 在WHERE条件中使用SELECT查询，第5-8行
UPDATE invoices
SET payment_total = invoice_total * 0.5,
payment_date = due_date
WHERE client_id =
(SELECT client_id
FROM clients
WHERE name = 'Myworks')-- 因为子查询返回了多条记录，所以WHERE条件中把=改成IN运算符
UPDATE invoices
SET payment_total = invoice_total * 0.5,
payment_date = due_date
WHERE client_id =
(SELECT client_id
FROM clients
WHERE state IN ('CA','NY'))注：在执行UPDATE语句前，先执行查询，看看会更新哪些记录
MYSQL-MOSH第四章-9-删除行
-- 删除指定数据表
DELETE FROM invoices
-- 不写WHERE条件，这delete语句会删除表中号全部数据
WHERE invoice_id = 1-- 删除指定数据表
DELETE FROM invoices
-- 不写WHERE条件，这delete语句会删除表中号全部数据
-- 子查询出的条件与WHERE查询出的条件列一致，都是client_id，否则报错1241
WHERE client_id = (
SELECT client_id
FROM clients
WHERE name = 'Myworks'
)
--删除数据（避免这么写，删除表全部数据）
DELETE FROM `student`
--删除指定数据
DELETE FROM `student` WHERE id = 1;
<--删除连贯数据,可以用between and-->
delete from `t_emp` where id Between 23 and 99;
<--删除散列数据，可以用in-->
delete from `t_emp` where id in(3,5,6,8);
--TRUNCATE的作用：完全清空一条数据库表，表的结构和索引约束不会变！
--清空 student 表
TRUNCATE `student`
--------------------------------------------------------------------------------
DELETE和TRUNCATE区别
相同点：都能删除数据，都不会删除表结构
不同：
TRUNCATE 重新设置 自增列 计数器会归零
TRUNCATE 不会影响事务
DELETE和TRUNCATE区别详解：
https://blog.csdn.net/JJJikerUPUP/article/details/90677449
MYSQL-MOSH第四章-10-恢复数据库
在本节课前我们添加/更新/删除了一些数据
MySQL工作台-文件-打开SQL脚本-create-databases.sql-运行该脚本即可（20240709这个脚本应该是他建库的脚本，该操作相当于重新建立数据库，达到恢复的效果）
MYSQL-MOSH第五章-1-聚合函数
聚合函数只运行非空值，如果列中有控制，它不会被算在函数里；若想得到表格中所有记录条目，不管是否空值，要用count(*)
① sum() – 分组求和
② count() – 分组求总数
③ min() – 分组求最小值
④ max() – 分组求最大值
⑤ avg() --分组求均值
例题：
SELECT
-- 引号里内容作为数据内容
'First half of 2019上半年' AS date_range日期范围,
sum(invoice_total) AS total_sales总销售额,
SUM(payment_total) AS total_payments总付款,
SUM(invoice_total - payment_total) AS what_we_expect待付款
FROM invoices
WHERE invoice_date
BETWEEN '2019-01-01' AND '2019-06-30'
-- 用UNION连接两个查询语句
UNION
SELECT
'Second half of 2019下半年' AS date_range,
sum(invoice_total) AS total_sales,
SUM(payment_total) AS total_payments,
SUM(invoice_total - payment_total) AS what_we_expect
FROM invoices
WHERE invoice_date
BETWEEN '2019-07-01' AND '2019-12-31'
-- 用UNION连接两个查询语句
UNION
SELECT
'Total2019整年' AS date_range,
sum(invoice_total) AS total_sales,
SUM(payment_total) AS total_payments,
SUM(invoice_total - payment_total) AS what_we_expect
FROM invoices
WHERE invoice_date
BETWEEN '2019-01-01' AND '2019-12-31'
MYSQL-MOSH第五章-2-GROUP BY子句
-- 利用一列来数据分组
SELECT
client_id,
SUM(invoice_total) AS total_sales
FROM invoices
GROUP BY client_id
ORDER BY total_sales DESC-- 利用多列来数据分组
-- 连接invoices表和clients表alter
-- 多列数据分组，查看表c中的列state和列city的总销售
SELECT
c.state,
c.city,
SUM(invoice_total) AS total_sales
FROM invoices i
JOIN clients c
USING(client_id)
GROUP BY state,city
MYSQL-MOSH第五章-3-HAVING子句
SELECT
client_id,
SUM(invoice_total) AS total_sales
FROM invoices
-- SQL执行顺序到WHERE这里还没有对client_id进行分组，
-- 所以用WHERE会报错[Eror Code: 1054. Unknown column total_sales in 'where clause"]
WHERE total_sales > 500
GROUP BY client_idSELECT
client_id,
SUM(invoice_total) AS total_sales
FROM invoices
-- SQL执行顺序到WHERE这里还没有对client_id进行分组，所以用WHERE会报错[Eror Code: 1054. Unknown column total_sales in 'where clause"]
-- WHERE子句，可以在分组前筛选数据
GROUP BY client_id
-- HAVING子句，在分组后筛选数据；这里用到的列，一定是select子句中存在的
HAVING total_sales > 500 MYSQL-MOSH第五章-4-ROLLUP运算符
汇总数据运算符：with rollup
rollup运算符只能应用于聚合值的列
当多列分组，并运用rollup运算符时，会得到每个组及整个结果集的汇总值
rollup运算符很有用，在现实世界中应用广泛，
然后这仅在MySQL里有,他不是一个标准的SQL语言，所以无法再SQL server或oracle中执行这个查询
用rollup运算符时，不能在group by子句中使用列别名
MYSQL-MOSH第六章-1-介绍
再次创建数据库(回复数据库)
MYSQL-MOSH第六章-2-子查询
USE sql_store;
-- 当MySQL试图执行这条查询，首先会评估我们的内查询或子查询
-- 所以这里会先获取生菜（product_id为3）的单价，然后把结果传给我们的外查询
-- 子查询返回单一的值
SELECT *
FROM products
WHERE unit_price > (
SELECT unit_price
FROM products
WHERE product_id = 3
)
也可以在FROM子句或SELECT子句中编写子查询
MYSQL-MOSH第六章-3-IN运算符
USE sql_store;
-- 子查询返回了一个列表的值
-- distinct去除重复值
SELECT *
FROM products
WHERE product_id NOT IN (
SELECT distinct product_id
FROM sql_store.order_items
)
MYSQL-MOSH第六章-4子查询VS连接
-- 子查询的方式
SELECT *
FROM clients
WHERE client_id NOT IN(
SELECT DISTINCT client_id
FROM invoices
)
-- 连接的方式
-- 选择子查询或连接的方式，主要看两点：一是表现，二是可读性
SELECT *
FROM clients
LEFT JOIN invoices USING (client_id)
WHERE invoice_id IS NULL
例题：
-- 子查询方式查找订购生菜的顾客（生菜paoduct_id=3）
SELECT
customer_id,
first_name,
last_name
FROM customers
WHERE customer_id IN (
SELECT customer_id
FROM orders
WHERE order_id IN (
SELECT order_id
FROM sql_store.order_items
WHERE product_id = 3
)
);
-- 连接方式查找订购生菜的顾客（生菜paoduct_id=3）
SELECT
DISTINCT c.customer_id,
c.first_name,
c.last_name
FROM customers c
LEFT JOIN orders o USING (customer_id)
LEFT JOIN order_items oi USING (order_id)
WHERE oi.product_id = 3MYSQL-MOSH第六章-5-ALL关键字
-- 找出大于客户3最大发票的发票
USE sql_invoicing;
SELECT *
FROM invoices
WHERE invoice_total > (
SELECT MAX(invoice_total)
FROM invoices
WHERE client_id = 3
)
-- 另一种方法，使用ALL关键字
-- 子查询出得出多个值，因为客户3有多张发票
-- 子查询前面加上ALL关键字
USE sql_invoicing;
SELECT *
FROM invoices
WHERE invoice_total > ALL (
SELECT invoice_total
FROM invoices
WHERE client_id = 3
)
总结：部分子查询出多个值，用最大值或最小值比较时，在子查询中取消MAX/MIN，在子查询的括号前加上ALL
ALL关键字括号内是多个值
-- 查询invoice_total比括号里数值大的数据
SELECT *
FROM invoices
WHERE invoice_total > ALL (100,150,200)MYSQL-MOSH第六章-6-ANY关键字
MySQL里还有ANY或SOME关键字
-- 查询发票数大于2的客户
-- 方案一
SELECT *
FROM clients
WHERE client_id IN (
SELECT
client_id
FROM invoices
GROUP BY client_id
HAVING COUNT(*) >= 2
)
-- 方案二
-- IN 和 = ANY 等效/相同效果
-- 如果客户id等于这段查询返回的值里的任何一个，这个客户就会被返回到最终结果里
SELECT *
FROM clients
WHERE client_id = ANY (
SELECT
client_id
FROM invoices
GROUP BY client_id
HAVING COUNT(*) >= 2
)MYSQL-MOSH第六章-7-相关子查询
-- 子查询没有外查询的引用，也没有和外查询有相关性，所以MySQL只会执行一次这段子查询
-- 这里子查询返回了一系列客户id，他们会被赋值到外查询或主查询的WHERE子句中
SELECT *
FROM clients
WHERE client_id IN (
SELECT
client_id
FROM invoices
GROUP BY client_id
HAVING COUNT(*) >= 2
)-- 选择工资超过部门平均的员工
-- 求出每个部门office_id的平均工资
SELECT *
FROM employees e
WHERE salary > (
SELECT AVG(salary)
FROM employees
-- 这里的外和内查询都用到了员工表，为了获取在同一部门的员工，给office_id加了一个表名的前缀
-- 所以MySQL执行这段查询的时候，逻辑是：首先来到员工表，对每位员工执行子查询。计算同一部门员工的平均工资
-- 然后如果这名员工的工资高于平均值，这名员工就会被返回在最终结果里；然后轮到第二条记录第二条记录也会计算同一部门员工的平均工资
-- 这种叫做相关子查询，因为这段子查询和外查询有相关性（子查询的WHERE子句里引用了外查询里出现的别名 e表）
WHERE office_id = e.office_id
)相比较：使用相关子查询时，这段查询会在主查询的每一行层面执行，所以相关子查询经常执行得很慢。数据越多，查询更费力，也会占用更多的存储
例题
-- 练习： 获取高于客户平均值的发票
-- 题目解读：每个客户有多张发票，对每位客户都要找到平均发票额（AVG(invoice_total)），然后比较
USE sql_invoicing;
SELECT *
FROM invoices i
WHERE invoice_total > (
SELECT AVG(invoice_total)
FROM invoices
WHERE client_id = i.client_id
)MYSQL-MOSH第六章-8-EXISTS(存在)运算符
-- 例题：找到有发票的客户
-- 子查询的方式
-- 这里的子查询返回结果是一个有4个客户id的列表
-- 如果子查询返回结果是成千上万个客户id的列表，这样就会生成一张非常大的列表，会妨碍最佳性能
-- 对于这种情况，使用EXISTS运算符会能够提升效率
-- 如果我们在IN运算符后写的子查询生成了很大的结果集，使用EXISTS运算符能更有效率，因为我们使用EXISTS运算符时，子查询并没有真的把结果集返回给外查询
SELECT *
FROM clients
WHERE client_id IN (
SELECT client_id
FROM invoices
WHERE payment_date IS NULL
)
-- JOIN连接的方式
SELECT
DISTINCT client_id,
name,
address,
city,
state,
phone
FROM clients
JOIN invoices
USING(client_id )
WHERE payment_date IS NULL
-- EXISTS的方式
-- 当我们使用EXISTS运算符，子查询并没有给外查询返回一个结果,他会返回一个指令，说明这个子查询是否有符合这个搜索条件的行
SELECT *
FROM clients c
WHERE EXISTS (
SELECT client_id
FROM invoices
WHERE client_id = c.client_id
)
练习
-- 练习：找到从没被订购的产品
SELECT *
FROM products p
WHERE NOT EXISTS(
SELECT product_id
FROM order_items oi
WHERE oi.product_id = p.product_id
)MYSQL-MOSH第六章-9-SE LECT子句中的子查询
-- 例题：求发票id，发票金额，所有人的发票平均额，成员与平均额的相差额
SELECT
invoice_id,
invoice_total,
-- 这里为什么不直接使用AVG，直接使用AVG只能出现一行，子语句要每行都有平均值
-- AVG是集合函数必须用到GROUP BY
(SELECT AVG(invoice_total)
FROM invoices) AS invoice_average,
-- 把发票平均转化为一段子查询
invoice_total - (SELECT invoice_average) AS difference
FROM invoices
练习：
-- 练习：客户id，名字，每位客户的成交量（每位客户开出发票的合计），平均成交量（所有发票总计的平均），这两列的差值
SELECT
client_id,
name,
(SELECT SUM(invoice_total)
FROM invoices
WHERE client_id = c.client_id) AS total_sales,
(SELECT AVG(invoice_total) FROM invoices) AS average,
(SELECT total_sales - average) AS difference
FROM clients cMYSQL-MOSH第六章-10-FROM子句中的子查询
可以在选择语句的FROM子句中写子查询，但仅限于简单的查询
当在FROM子句中使用子查询时，必须给子查询一个别名，不管会不会使用到别名
-- 练习：客户id，名字，每位客户的成交量（每位客户开出发票的合计），平均成交量（所有发票总计的平均），这两列的差值
SELECT *
FROM (
SELECT
client_id,
name,
(SELECT SUM(invoice_total)
FROM invoices
WHERE client_id = c.client_id) AS total_sales,
(SELECT AVG(invoice_total) FROM invoices) AS average,
(SELECT total_sales - average) AS difference
FROM clients c
-- 当在FROM子句中使用子查询时，必须给子查询一个别名，不管会不会使用到别名
) AS sales_summary在选择语句的FROM子句中写子查询，会让主查询变得复杂，一个更好的解决方案就是使用视图，所以我们可以使用这段查询，作为视图存储在数据库中
MYSQL-MOSH第七章-1-数值函数
-- 内置函数，用以应对数值、日期时间和字符串值
-- 处理数值数据的函数
-- ROUND函数（轮函数）：四舍五入数字
SELECT ROUND(5.73)
-- 得到6
-- ROUND函数还有第二个可选参数，可以用来指定四舍五入的精确度
SELECT ROUND(5.7345,1)
-- 得到5.7；1指的是保留一位小数
-- TRUNCATE函数：用来截断数字
SELECT TRUNCATE(5.7354,2)
-- 得到5.73；2指的是保留2位小数，移除其他位数
-- CEILING函数：返回大于或等于这个数字的最小整数
SELECT CEILING(5.7354)
-- 得到6
-- FLOOR函数（地板函数）：返回小于或等于这个数字的最大整数
SELECT FLOOR(5.7354)
-- 得到5
-- ABS函数：用来计算绝对值
SELECT ABS(5.7354)
-- 得到5.7354
SELECT ABS(-5.7354)
-- 得到5.7354
-- RAND函数：用来生成0-1区间的随机浮点数
SELECT RAND(5.7354)
-- 每次调用这个函数，得到新的介于0-1的随机值如果想看这些数值函数的完整名单：搜索：MySQL numeric functions
https://www.mysqltutorial.org/mysql-math-functions/
MYSQL-MOSH第七章-2-字符串函数
-- 处理字符串值的函数
-- LENGTH函数，得到字符串中的字符数
SELECT LENGTH('sky')
-- 得到3，因为sky有3个字符
-- UPPER函数/LOWER函数，用以将字符串转化为大写和小写字母
SELECT UPPER('sky')
-- 得到SKY
SELECT LOWER('Sky')
-- 得到sky
-- 删除字符串中不需要的空格
-- LTRIM(left trim左修整的简写)，移除字符串左侧的空白字符或其他预定义字符
SELECT LTRIM(' sky')
-- 得到sky
-- RTRIM(right trim右修整的简写)，移除字符串右侧的空白字符或其他预定义字符
SELECT RTRIM(' sky ')
-- 得到' sky'
-- TRIM，删除所有前导或尾随空格
SELECT TRIM(' sky ')
-- 得到sky
-- LEFT函数，返回字符串左侧的几个字符
SELECT LEFT('Kindergarten',4)
-- 得到Kind；4表示返回左数四位字符
-- RIGHT函数，与LEFT函数相对，返回字符串右侧的几个字符
SELECT RIGHT('Kindergarten',4)
-- 得到'rten'；4表示返回右数四位字符
-- SUBSTR（SUB STRING）函数，字符截取函数,可以得到一个字符串中任何位置的字符
SELECT SUBSTR('Kindergarten',3,5)
-- 第二个参数3表示起始位置，第三个参数5是长度
-- 当调用这个函数的时候，MySQL会去到第三个点位，就是从‘n’开始，选取5个字符
-- 得到'nderg'
-- 第三个函数是可选的，如果不填，就会返回从起始位置算到字符串最后的所有字符
SELECT SUBSTR('Kindergarten',3)
-- 得到'ndergarten'
-- LOCATE，返回第一个字符或一串字符匹配位置
SELECT LOCATE('n','Kindergarten')
-- 第一个参数是要搜索的字符串(假设想搜索'Kindergarten'里的'n')
-- 得到'3'，因为'Kindergarten'第一个'n'的位置是3，无论大小写字母与，结果都是一样的
-- 如果搜索字符串里没有的字符会怎样
SELECT LOCATE('q','Kindergarten')
-- 得到'0'
-- 大多数编程语言中，如果搜索字符串中没有的字符，会得到'-1'，MySQL会得到'0'
-- 还可以搜索一串字符，比如'garten',
SELECT LOCATE('garten','Kindergarten')
-- 他会返回字符串中garten第一次出现的位置，得到'7'
-- REPLACE，3个参数；替换一个字符或一串字符
SELECT REPLACE('Kindergarten','garten','garden')
-- 第一个参数是字符串'Kindergarten'；想把替换有‘t’的'garten'；替换成有‘d’的'garden'
-- 得到'Kindergarden'
-- CONCAT，用来串联/合并两个字符串
SELECT CONCAT('first','last')
-- 得到'firstlast'
USE sql_store;
-- CONCAT函数，合并/串联多个字符串，第二个参数为空格，得到顾客的全名
SELECT CONCAT(first_name,' ',last_name) AS full_name
FROM customers;
如果想看这些字符串函数的完整名单：搜索：MySQL string functions
MySQL参考手册：MySQL :: MySQL 8.0 Reference Manual :: 14.8 String Functions and Operators
MYSQL-MOSH第七章-3-MySQL中的日期函数
-- 日期函数和时间函数
-- NOW函数，调用当前的日期和时间
SELECT NOW()
-- 返回电脑上的当前日期和时间,即年-月-日-时-分-秒
-- CURDATE，当前日期（current date）的缩写
SELECT CURDATE()
-- 只返回当前日期，不返回时间，即年-月-日
-- CURTIME，返回当前时间
SELECT CURTIME()
-- 只返回当前时间，不返回日期，即时-分-秒
-- YEAR函数，用以获取当前日期时间并提取年份，返回整数值
SELECT YEAR(NOW())
-- 返回'2024'，即返回当前日期时间的年份，今天是2024年
-- MONTH函数，用以获取当前日期时间并提取月，返回整数值
SELECT MONTH(NOW())
-- 返回'7'，即返回当前日期时间的月份，今天是7月
-- DAY函数，用以获取当前日期时间并提取日，返回整数值
SELECT DAY(NOW())
-- 返回'16'，即返回当前日期时间的几号，今天是16号
-- HOUR函数，用以获取当前日期时间并提取当前时间，返回整数值
SELECT HOUR(NOW())
-- 返回'11'，即返回当前日期时间的几点钟，现在是11点钟
-- MINUTE函数，用以获取当前日期时间并提取当前时间，返回整数值
SELECT MINUTE(NOW())
-- 返回'15'，即返回当前日期时间的第几分钟，现在是11点的15分钟
-- SECOND函数，用以获取当前日期时间并提取当前时间，返回整数值
SELECT SECOND(NOW())
-- 返回'8'，即返回当前日期时间的第几秒钟，现在是11点15分的第8秒
-- DAYNAME函数，获取字符串格式的星期数，返回字符串
SELECT DAYNAME(NOW())
-- 返回'Tuesday'，今天是周二
-- MONTHNAME函数，获取字符串格式的月份，返回字符串
SELECT MONTHNAME(NOW())
-- 返回'July'，今天是7月
-- EXTRACT函数，这个函数是标准SQL语言的一部分，如果想把代码录入到其他DBMS,最好别用该函数
-- 要调用它时，首先制定单位，如年/月/日期/秒
-- 当调用该函数时，输入想获取的单位，然后是FROM关键字，再是时间日期值
SELECT EXTRACT(YEAR FROM NOW())
-- 返回'2024'，即返回当前日期时间的年份，今天是2024年
SELECT EXTRACT(MONTH FROM NOW())
-- 返回''7'，即返回当前日期时间的月份，今天是7月
SELECT EXTRACT(DAY FROM NOW())
-- 返回'16'，即返回当前日期时间的几号，今天是16号
MYSQL-MOSH第七章-4-格式化日期和时间
-- 格式化日期和时间的函数
-- '2019-03-11'格式化为2019年3月11日
-- DATE_FORMAT,要用到两个参数，一个日起值和一个格式字符串(包含了格式化日期的几个构成成分的特殊代码)
-- %y表示两位的年份,%Y表示四位的年份
SELECT DATE_FORMAT(NOW(),'%y')
-- 返回'24'
SELECT DATE_FORMAT(NOW(),'%Y')
-- 返回'2024'
-- %m表示两位的月份
SELECT DATE_FORMAT(NOW(),'%m')
-- 返回'07'
-- %M表示月份名称
SELECT DATE_FORMAT(NOW(),'%M')
-- 返回'July'
-- %d表示日期
SELECT DATE_FORMAT(NOW(),'%d')
-- 返回'16'
-- %d表示日期
SELECT DATE_FORMAT(NOW(),'%D')
-- 返回'16th'
-- 日期函数
SELECT DATE_FORMAT(NOW(),'%M %d %Y')
-- 返回'July 16 2024'
-- 时间函数
-- 和日期函数一样，大小写代表不同，详情可见官网内的说明符文档（链接：https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html）
-- %H是24小时制，%h是12小时制
SELECT TIME_FORMAT(NOW(),'%H:%i %p')
-- 返回'11:54 AM'下表中显示的说明符可用于格式字符串。格式化前需要字符 说明符字符。说明符适用于其他函数 以及：STR_TO_DATE（）、TIME_FORMAT（）、UNIX_TIMESTAMP（）。%
月份和日期说明符的范围以零开头，因为 MySQL允许存储不完整日期的事实 如。'2014-00-00'
用于日和月名称和缩写的语言是 由 lc_time_names 系统变量的值控制 （第 12.16 节 “MySQL 服务器区域设置支持”）。
对于 、 、 和 说明符， 有关信息，请参阅 WEEK（） 函数的描述 关于模式值。该模式会影响周编号的方式 发生。
如果想看这些日期和时间函数的完整名单：搜索：MySQL date format string
MySQL参考手册：MySQL :: MySQL 8.0 Reference Manual :: 14.7 Date and Time Functions
MYSQL-MOSH第七章-5-计算日期和时间
-- 计算日期和时间
-- DATE_ADD函数，给日期时间添加日期成分
-- 假设想在当前日期时间上添加一天，第一个参数是当前日期时间，第二个参数是一段表达式
SELECT DATE_ADD(NOW(),INTERVAL 1 DAY)
-- 返回'2024-07-17 17:17:04'，即 返回了明天的同一时间
-- 也可以在当前日期时间添加一年
SELECT DATE_ADD(NOW(),INTERVAL 1 YEAR)
-- 返回'2025-07-16 17:18:45'，即 返回了明年的同一时间
-- 想要得到过去的时间，可以添加一个负值
SELECT DATE_ADD(NOW(),INTERVAL -1 YEAR)
-- 或者使用DATE_SUB函数
SELECT DATE_SUB(NOW(),INTERVAL 1 YEAR)
-- 两个都返回'2023-07-16 17:21:58'
-- DATEDIFF函数,计算两个日期的间隔
-- 该函数只返回天数的间隔，不返回小时分钟的间隔
-- 注意先写日期较后的日子，会得到正数；否则会得到负数
SELECT DATEDIFF('2019-01-05 09:00','2019-01-01 15:00')
-- 返回'4'
-- TIME_TO_SEC函数，计算时间间隔，他会返回从零点计算的秒数
SELECT TIME_TO_SEC('09:00')
-- 返回'32400'
-- 可以给第二个时间值调用TIME_TO_SEC函数，然后用第一个值减去它
SELECT TIME_TO_SEC('09:00') - TIME_TO_SEC('09:02')
-- 返回'-120'
MYSQL-MOSH第七章-6-IFNULL和COALESCE函数
例题：
USE sql_store;
SELECT *
FROM orders
-- 部分发货人id(shipper_id)为空值NULL
-- 需要把空值替换成其他
返回：
SELECT
order_id,
-- 如果shipper_id是空值，这个函数会返回'Not assigne未分配'
IFNULL(shipper_id,'Not assigne未分配') AS shipper
FROM orders返回
还有一个类似但更为巧妙的函数，COALESCE函数
串联串联v成-- COALESCE（合并）函数，可以放多个参数COALESCE(f1,f2,f3...fn)
-- 假设发货人id是空值，想要返回另一个列的值；如果另一个列的值也是空值，则返回'Not assigne未分配'
SELECT
order_id,
comments,
shipper_id，
COALESCE(shipper_id, comments, 'Not assigne未分配') AS shipper
FROM orders返回
IFNULL与COALESCE函数的区别：
IFNULL函数里，我们可以用其他内容替换空值
IFNULL(shipper_id,'Not assigne未分配') AS shipper
-- 第一个参数shipper_id不是空值时，返回第一个参数shipper_id的值
-- 第一个参数shipper_id是空值时，返回第二个参数的值COALESCE函数里，我们提供一堆值，这个函数会返回这堆值中第一个非空值
COALESCE(shipper_id, comments, 'Not assigne未分配') AS shipper
-- 第一个参数shipper_id不是空值时，返回第一个参数shipper_id的值
-- 第一个参数shipper_id是空值时，再看第二个参数是否空值；
-- 第二个参数不是空值时，返回第二个参数的值
-- 第二个参数是空值时，再看第三个参数是否空值
-- 以此类推MYSQL-MOSH第七章-7-IF函数
-- 会需要测试条件，并根据条件是否成立，返回不同值
-- 把订单分成两组：今年的订单为活跃，不是今年的为不活跃
SELECT
order_id,
order_date,
-- 调用IF函数，输入第一个参数：一个测试用的表达式，若表达式判定为真，函数会返回第二个参数的值；否则返回第三个参数的值
IF(
YEAR(order_date) = YEAR(NOW()),
'活跃',
'不活跃') AScategory
FROM ordersMYSQL-MOSH第七章-8-CASE运算符
-- IF函数可以测试一个表达式，并根据表达式判定结果返回不同值
-- 测试多个表达式， 用CASE表达式
-- 在有多个测试表达式且想要针对每个测试表达式返回不同值的时候，可以使用CASE运算符
SELECT
order_id,
order_date,
-- 打上CASE,紧跟一个或多个WHEN子句，每个WHEN子句都有一个测试表达式。
-- WHEN的表达式判定为真，输入在THEN的值就会被返回
CASE
WHEN YEAR(order_date) = YEAR(NOW()) THEN '活跃'
-- 可以继续写另一个WHEN子句，包含不同的测试表达式
WHEN YEAR(order_date) = YEAR(NOW()) - 1 THEN '去年'
WHEN YEAR(order_date) < YEAR(NOW()) - 1 THEN '归档'
-- 可以选择性加上ELSE子句，如果上述条件没有一个是真的，就会返回ELSE的值
ELSE '未来'
-- 最后要用END关键字关闭CASE语句块
END AS categor
FROM orders
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
