数据库系统-常用用法积累

数据库系统-常用用法积累

Delete

注意
1
删除重复邮件，保留最小的id email 来自leecode
1
2
3
4
5
6
7
8
9
10
11
# Write your MySQL query statement below
delete from Person where Person.id not in (
select t.id from (
select p2.id 
from Person p2
where p2.email in (select p1.email
from Person p1
group by p1.email
having count(*)=1)
) t
)
1
2
3
如果不要貌似多余的select t.id from ( 会报错

--MySQL 不允许在 DELETE 语句的 WHERE 子句中直接使用正在被更新的表。

image-20240516100028199

1
2
3
4
5
delete from Person where id not in (
	select a.id from (
		select min(id) from Person group by email a
	)
)
1
2
3
4
DELETE p1 FROM Person p1,
    Person p2
WHERE
    p1.Email = p2.Email AND p1.Id > p2.Id
1
2
3
p1是Person表的别名，这个DELETE语句会删除Person表中所有满足JOIN条件和WHERE条件的行

所以答案这样写的意思是，最终会在P1中delete，而不是在笛卡尔积的表里delete是吗？

日期

DATEDIFF()
1
2
3
4
5
6
7
8
9
10
11
12
13
14
DATEDIFF() 函数返回两个日期之间相差的时间。周、月数、年数可以除以7、30、365然后向上CEIL或者向下FLOOR取整粗略计算。

SELECT CEIL(DATEDIFF('2008-12-30', '2008-12-01') / 7) AS WeeksDiff;


select 
    b.Id 
from 
    weather a 
inner join 
    weather b 
where 
    DATEDIFF(b.recordDate,a.recordDate)=1 
and b.Temperature > a.Temperature;
TIMESTAMPDIFF() | NOW()

timestampdiff

1
2
3
4
5
6
7
8
9
10
11
12
13
14
timestampdiff(year,参数1,参数2) 

TIMESTAMPDIFF能干什么，可以计算相差天数、小时、分钟和秒，相比于datediff函数要灵活很多。格式是时间小的前，时间大的放在后面。 计算相差天数：

其中，year处可以是以下值之一：
MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
1
2
3
4
select w1.Id
from Weather as w1, Weather as w2
where TIMESTAMPDIFF(DAY, w2.RecordDate, w1.RecordDate) = 1 
AND w1.Temperature > w2.Temperature
1
2
3
4
5
6
SELECT TIMESTAMPDIFF(YEAR,'1999-11-02',NOW()) age;
24

now()
+---------------------+
| 2024-11-01 22:46:28

image-20241101225020808

DATE_FORMAT()
1
2
3
4
5
6
7
8
9
10
11
12
%b	缩写月名
%m	月，数值(00-12)
%d	月的天，数值(00-31)
%e	月的天，数值(0-31)
%Y	年，4 位
%y	年，2 位
%H	小时 (00-23)
%h	小时 (01-12)
%i	分钟，数值(00-59)
%p	AM 或 PM
%T	时间, 24-小时 (hh:mm:ss)
%f	微秒
1
2
3
4
5
--2018-12-18

DATE_FORMAT(trans_date, '%Y-%m')

--2018-12
DATE_ADD()
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
DATE_ADD() ：从日期增加指定的时间间隔，返回的是一个字符串

DATE_ADD(date,INTERVAL expr type)

date 参数是合法的日期表达式。
expr 是希望添加的时间间隔的数值（expr 是一个字符串，对于负值的间隔，可以以 - 开头）
type 是具体的数据类型，表示加上时间间隔的单位（可以是 MICROSECOND , SECOND , MINUTE , HOUR , DAY , WEEK , MONTH , QUARTER , YEAR 等）

二、使用
now() //now函数为获取当前时间

select date_add(now(), interval 1 day); - 加1天
select date_add(now(),INTERVAL -1 DAY);-当前日期向前推1天
select date_add(now(), interval 1 hour); -加1小时
select date_add(now(), interval 1 minute); - 加1分钟
select date_add(now(), interval 1 second); -加1秒
select date_add(now(), interval 1 microsecond);-加1毫秒
select date_add(now(), interval 1 week);-加1周
select date_add(now(), interval 1 month);-加1月
select date_add(now(), interval 1 quarter);-加1季
select date_add(now(), interval 1 year);-加1年
MySQL adddate(), addtime()函数，可以用date_add() 来替代。
1
2
3
4
5
6
7
8
9
10
select IFNULL(round(count(distinct(Result.player_id)) / count(distinct(Activity.player_id)), 2), 0) as fraction
from (
  select Activity.player_id as player_id
  from (
    select player_id, DATE_ADD(MIN(event_date), INTERVAL 1 DAY) as second_date
    from Activity
    group by player_id
  ) as Expected, Activity
  where Activity.event_date = Expected.second_date and Activity.player_id = Expected.player_id
) as Result, Activity
DATE_SUB()
1
2
3
4
5
6
7
8
9
10
用于从日期减去指定的时间间隔。它与 DATE_ADD() 函数具有相似的用法。

语法
SELECT DATE_SUB(date, INTERVAL expr type)
FROM table_name
其中：date 指代希望被操作的有效日期

expr 是希望添加的时间间隔

type 是具体的数据类型（可以是 MICROSECOND , SECOND , MINUTE , HOUR , DAY , WEEK , MONTH , QUARTER , YEAR 等）
CURDATE()，NOW()
1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> select curdate();
+------------+
| curdate()  |
+------------+
| 2024-11-01 |
+------------+


mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2024-11-01 22:46:28 |
+---------------------+
MONTH(),WEEK(),YEAR(),DAYOFMONTH()
1
2
3
4
MONTH提取日期中的月
YEAR提取日期中的年
WEEK计算日期当天是属于第几周
DAYOFMONTH提取日期中的天
DAYOFWEEK、DAYOFMONTH、DAYNAME
1
2
3
4
5
6
7
8
9
DAYOFWEEK函数接受1个参数，即DATE或DATETIME值。 它返回一个整数，范围从1到7，表示星期日到星期六。如果日期为NULL，零(0000-00-00)或无效，则DAYOFWEEK函数返回NULL。

DAYNAME返回中文的星期几

mysql> SELECT DAYNAME('2012-12-01'), DAYOFWEEK('2012-12-01');
+-----------------------+-------------------------+
| DAYNAME('2012-12-01') | DAYOFWEEK('2012-12-01') |
+-----------------------+-------------------------+
| 星期六                |                       7 |
1
DAYOFMONTH提取日期中的天

lag()、lead()

1
2
3
4
5
6
7
8
9
10
11
lag()和lead()这两个函数可以查询我们得到的结果集上下偏移相应行数的相应的结果。

lag()函数：
查询当前行向上偏移n行对应的结果
该函数有三个参数：第一个为待查询的参数列名，第二个为向上偏移的位数，第三个参数为超出最上面边界的默认值。

lead()函数：
查询当前行向下偏移n行对应的结果
该函数有三个参数：第一个为待查询的参数列名，第二个为向下偏移的位数，第三个参数为超出最下面边界的默认值。

第二个参数的默认值都为1，第三个参数没指定时取到超出范围时返回null
1
2
3
4
5
6
7
8
9
10
11
12
13
14
with people as
(
    select id, visit_date, people,
    Lag(people,2) over(order by id) as pprvPeople,
    Lag(people,1) over(order by id) as prvPeople,
    Lead(people,1) over(order by id) as nextPeople,
    Lead(people,2) over(order by id) as nnextPeople
    from stadium
)
select id, visit_date, people from people
where 
(people >= 100 and prvPeople>=100 and pprvPeople>=100) ||
(people >= 100 and nextPeople>=100 and nnextPeople>=100) ||
(people >= 100 and nextPeople>=100 and prvPeople>=100) 
1
当比如第一行取lag前一行，为null

数学

roud()
1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT T.request_at AS `Day`, 
	ROUND(
			SUM(
				IF(T.STATUS = 'completed',0,1)
			)
			/ 
			COUNT(T.STATUS),
			2
	) AS `Cancellation Rate`
FROM Trips AS T
JOIN Users AS U1 ON (T.client_id = U1.users_id AND U1.banned ='No')
JOIN Users AS U2 ON (T.driver_id = U2.users_id AND U2.banned ='No')
WHERE T.request_at BETWEEN '2013-10-01' AND '2013-10-03'
GROUP BY T.request_at
mod(M,N)
1
2
3
取余函数
mod(id,2)
id%2
ABS(p1.x_value - p2.x_value)
1
取绝对值
sqrt()开平方
1
sqrt(A)对A开平方根
FLOOR、CEIL
1
2
3
4
FLOOR()函数返回小于或等于给定数字的最大整数值

SELECT FLOOR(-3.56); -- 返回 -4
SELECT FLOOR(3.45); -- 返回 3
1
2
3
4
CEIL() 函数返回大于或等于给定数字的最小整数值。这个函数等同于 CEILING() 函数

SELECT CEIL(-6.43); -- 返回 -6
SELECT CEIL(25.75); -- 返回 26

limit()

1
2
3
4
5
6
7
limit  2 ==>从0索引开始读取2个

select * from table limit 2,1;                
//含义是跳过2条取出1条数据，limit后面是从第2条开始读，读取1条信息，即读取第3条数据
 
select * from table limit 2 offset 1;     
//含义是从下标1开始取出2条数据，limit后面跟的是2条数据，即读取第2,3条
一个参数
1
limit 10
两个参数
1
2
3
limit 0,2 第一个参数表示从第几行开始取，第二个表示取几行

--其实这个实现分页也是可以的
分页offset
1
2
3
4
5
6
7
8
9
例如，把结果集分页，每页3条记录。要获取第1页的记录，可以使用LIMIT 3 OFFSET 0：
SELECT * FROM student LIMIT 3 OFFSET 0;
上述查询LIMIT 3 OFFSET 0表示，对结果集从0号记录开始，最多取3条。注意SQL记录集的索引从0开始。

如果要查询第2页，那么我们只需要“跳过”头3条记录，也就是对结果集从3号记录开始查询，把OFFSET设定为3：
SELECT * FROM student LIMIT 3 OFFSET 3;

LIMIT总是设定为pageSize
OFFSET计算公式为pageSize * (pageIndex - 1)

IF

if()
1
IF(T.STATUS = 'completed',0,1)
IFNULL()、NULLIF()、ISNULL()
1
2
3
4
5
6
7
IFNULL()函数的使用
IFNULL(expr1,expr2)，如果expr1的值为null，则返回expr2的值，如果expr1的值不为null，则返回expr1的值。

 
SELECT IFNULL(NULL,'B'); -- 输出结果：B
 
SELECT IFNULL('HELLO','B'); -- 输出结果：HELLO
1
2
3
4
5
6
7
8
NULLIF()函数的使用
NULLIF(expr1,expr2)，如果expr1=expr2成立，那么返回值为null，否则返回值为expr1的值。

 
SELECT NULLIF('A','A'); -- 输出结果：null
 
SELECT NULLIF('A','B'); -- 输出结果：A

1
2
3
4
5
6
7
ISNULL()函数的使用

ISNULL(expr)，如果expr的值为null，则返回1，如果expr1的值不为null，则返回0。

SELECT ISNULL(NULL); -- 输出结果：1
 
SELECT ISNULL('HELLO'); -- 输出结果：0

=、<=>、NULL、is null、is not null

安全等于

1
MySQL提供了IS NULL和IS NOT NULL这两个操作符，它们专门用于检查字段是否为NULL。当我们使用IS NULL时，如果字段的值为NULL，查询将返回TRUE；如果字段的值不是NULL（包括字段中有值和字段为空），查询将返回FALSE。 同样地，IS NOT NULL用于检查字段值是否不是NULL。
1
2
普通的=，不能用于null的比较，两个式子中有一个或者两个null结果为null
安全等于<=>，可以用于包含null的比较，除了null<=>null等于1,null<=>1等于0
1
2
1<=>0	‘2’<=>2	2<=>2	‘b’<=>‘b’	(1+3)<=>(2+2)	NULL<=>NULL	NULL<=>‘1234’	NULL<=>123	‘456’<=>NULL
0	1	1	1	1	1	0	0	0
1
mysql中两个字符串比较按照字符串规则来，字符串和数字比较，字符串转换成数字比较

image-20240517153247499

image-20240517153306278

image-20240517153424876

1
安全等于可以用于比较null,同null为1，一个则为0。其它情况和=类似
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
输入： 
Customer 表:
+----+------+------------+
| id | name | referee_id |
+----+------+------------+
| 1  | Will | null       |
| 2  | Jane | null       |
| 3  | Alex | 2          |
| 4  | Bill | null       |
| 5  | Zack | 1          |
| 6  | Mark | 2          |
+----+------+------------+

找出那些 没有被 id = 2 的客户 推荐 的客户的姓名。


法一 
SELECT name
FROM customer
WHERE NOT referee_id <=> 2;

法二
SELECT name FROM customer WHERE referee_id != 2 OR referee_id IS NULL;

WITH语句

1
2
3
4
5
6
7
8
9
10
11
12
13
14
WITH BaseSalary AS (
    SELECT employee_id, salary * 12 AS annual_base_salary
    FROM employees
),
Bonus AS (
    SELECT employee_id, SUM(amount) AS total_bonus
    FROM bonuses
    GROUP BY employee_id
),
Commission AS (
    SELECT employee_id, SUM(amount) AS total_commission
    FROM commissions
    GROUP BY employee_id
)
1
2
3
4
5
6
WITH cte_name (column_name1, column_name2, ...) AS (
-- CTE查询定义
SELECT column1, column2,
FROM table_name
WHERE condition
)
1
在SQL查询中，经常会遇到需要重复使用的子查询。为了简化查询语句并提高可读性，SQL引入了WITH AS语法。通过使用WITH AS，我们可以创建临时表或视图，将子查询的结果保存起来，并在主查询中使用。本文将通过示例介绍SQL中WITH AS的特点，展示其在查询中的优势。
1
2
3
4
5
6
7
8
9
10
1. 简化复杂查询

WITH customer_orders (customer_id, total_amount) AS (
   SELECT customer_id, SUM(order_amount) AS total_amount
   FROM orders
   GROUP BY customer_id
)
SELECT customer_id, total_amount
FROM customer_orders
WHERE total_amount > 1000;
1
2
3
4
5
6
7
8
9
10
2. 提高查询性能：使用WITH AS可以避免在主查询中重复执行相同的子查询，从而提高查询性能。临时表的结果会被缓存，主查询只需要引用临时表即可，避免了重复计算子查询的开销。

WITH average_salary AS (
   SELECT AVG(salary) AS avg_salary
   FROM employees
)
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary > (SELECT avg_salary FROM average_salary)
ORDER BY salary DESC;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
with people as
(
    select id, visit_date, people,
    Lag(people,2) over(order by id) as pprvPeople,
    Lag(people,1) over(order by id) as prvPeople,
    Lead(people,1) over(order by id) as nextPeople,
    Lead(people,2) over(order by id) as nnextPeople
    from stadium
)
select id, visit_date, people from people
where 
(people >= 100 and prvPeople>=100 and pprvPeople>=100) ||
(people >= 100 and nextPeople>=100 and nnextPeople>=100) ||
(people >= 100 and nextPeople>=100 and prvPeople>=100)

group by

1
2
3
4
5
6
7
8
SELECT DATE_FORMAT(trans_date, '%Y-%m') AS month,
    country,
    COUNT(*) AS trans_count,
    COUNT(IF(state = 'approved', 1, NULL)) AS approved_count,
    SUM(amount) AS trans_total_amount,
    SUM(IF(state = 'approved', amount, 0)) AS approved_total_amount
FROM Transactions
GROUP BY month, country --这里的month是截取的，不能再group by后面截取，截取后也不能重命名
1
2
3
4
5
6
select e.student_id,student_name,count(*) attended_exams 
from Students s ,Examinations e
where s.student_id=e.student_id
group by student_id,student_name

-- 这里能select student_name,因为这里同分组下student_name相同
having
1
2
3
4
5
6
7
8
9
10
11
12
13
14
--leecode 3328

SELECT
    state,
    GROUP_CONCAT(city ORDER BY city SEPARATOR ', ') AS cities,
    COUNT(
        CASE
            WHEN LEFT(city, 1) = LEFT(state, 1) THEN 1
        END
    ) AS matching_letter_count
FROM cities
GROUP BY 1
HAVING COUNT(city) >= 3 AND matching_letter_count > 0
ORDER BY 3 DESC, 1;

变量

1
2
3
4
5
6
7
8
9
10
11
Queue 表
+-----------+-------------+--------+------+
| person_id | person_name | weight | turn |
+-----------+-------------+--------+------+
| 5         | Alice       | 250    | 1    |
| 4         | Bob         | 175    | 5    |
| 3         | Alex        | 350    | 2    |
| 6         | John Cena   | 400    | 3    |
| 1         | Winston     | 500    | 6    |
| 2         | Marie       | 200    | 4    |
+-----------+-------------+--------+------+
1
2
3
4
5
6
7
8
9
SELECT a.person_name
FROM (
	SELECT person_name, @pre := @pre + weight AS weight
	FROM Queue, (SELECT @pre := 0) tmp
	ORDER BY turn
) a
WHERE a.weight <= 1000
ORDER BY a.weight DESC
LIMIT 1

窗口函数

1
mysql8.0时开始支持

参考

1
通常将常用的窗口函数分为两大类：聚合窗口函数与专用窗口函数。
SUM 求和
AVG 求平均值
COUNT 求数量
MAX 求最大值
MIN 求最小值

函数名 分类 说明
RANK 排序函数 类似于排名，并列的结果序号可以重复，序号不连续
DENSE_RANK 排序函数 类似于排名，并列的结果序号可以重复，序号连续
ROW_NUMBER 排序函数 对该分组下的所有结果作一个排序，基于该分组给一个行数

1
2
3
[你要的操作] OVER ( PARTITION BY  <用于分组的列名>
                    ORDER BY <按序叠加的列名> 
                    ROWS <窗口滑动的数据范围> )
1
2
3
4
5
窗口函数([参数]) OVER (
  [PARTITION BY <分组列>] 
  [ORDER BY <排序列 ASC/DESC>]
  [ROWS BETWEEN 开始行 AND 结束行]
)
rows between and
1
2
3
4
5
6
7
8
<窗口滑动的数据范围> 用来限定[ 你要的操作] 所运用的数据的范围，具体有如下这些：

当前行 - current row
之前的行 - preceding
之后的行 - following
无界限 - unbounded
表示从前面的起点 - unbounded preceding
表示到后面的终点 - unbounded following
1
2
3
4
5
6
7
8
9
10
11
12
13
14
举例理解一下：

取当前行和前五行：ROWS between 5 preceding and current row --共6行
取当前行和后五行：ROWS between current row and 5 following --共6行
取前五行和后五行：ROWS between 5 preceding and 5 folowing --共11行

// 从当前行到最后的数据
sum(sales_volume) over(partition by id rows between current row and unbounded following) sum_sales

sum(sales_volume) over(partition by id rows between unbounded preceding and current row) sum_sales

sum(sales_volume) over(partition by id rows between current row and 2 following) sum_sales

sum(sales_volume) over(partition by id rows between 1 preceding and current row) sum_sales
range between and
1
2
3
4
5
6
7
8
9
10
11
range表示的是 具体的值，比这个值小n的行，比这个值大n的行
range between是以当前值为锚点进行计算

比如
range between 4 preceding AND 7 following
1
表示：如果当前值为10的话就取前后的值在6到17之间的数据。

sum(close) range between 100 preceding and 200 following
1
则通过字段差值来进行选择。如当前行的 close 字段值是 200，那么这个窗口大小的定义就会选择分区中 close 字段值落在 100 至 400 区间的记录（行）
1
前面的current row、following、preceding、unbounded也可以用。等同于前面的row between and
dense_rank() over
1
2
3
4
5
6
7
SELECT SId,totalscore,DENSE_RANK()over(ORDER BY totalscore DESC) r
FROM
(
SELECT SId,SUM(score) totalscore
FROM sc
GROUP BY SId
) t
avg(x) over()
1
2
3
4
5
SELECT
  sales.*,
  AVG( revenue ) OVER ( PARTITION BY category ) AS avg_revenue 
FROM
	sales

CASE

1
2
3
4
5
6
7
8
9
10
case语句格式一

CASE语句在执行时，将CASE后的表达式的值与各WHEN子句的表达式值比较，如果相等，则执行THEN后面的表达式或语句，然后跳出CASE语句；否则，返回ELSE后面的表达式。

CASE input_expression
    WHEN expression1 THEN result_expression1
    WHEN expression2 THEN result_expression2
    [...n]
    ELSE result_expression
    END
1
2
3
4
5
6
7
8
9
10
11
12
13
14
case语句格式二


CASE
    WHEN expression1 THEN result_expression1
    WHEN expression2 THEN result_expression2
    [...n]
    ELSE result_expression
    END


CASE后面没有表达式，多个WHEN子句中的表达式依次执行，如果表达式结果为真，则执行相应THEN关键字后面的表达式或语句，执行完毕之后跳出CASE语句。如果所有WHEN语句都为FALSE，则执行ELSE子句中的语句。

小tips：在交换数据和列转行时可以考虑使用case语句
1
2
3
4
5
6
7
case when ... then x else x end

\

case when then x
when then x else x end

1
case when sc.cid='01' then sc.score end

||

1
sql语句中“||”符号表示，连接符。比如'111'||'222'其结果就是'111222'。

行转列、列转行

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
行转列一般使用 IF 或 CASE WHEN 语句 + GROUP BY + 聚合函数。而列转行一般使用 UNION + 多个查询 的方法



SELECT 
  product_id,
  SUM(IF(store = 'store1', price, NULL)) 'store1',
  SUM(IF(store = 'store2', price, NULL)) 'store2',
  SUM(IF(store = 'store3', price, NULL)) 'store3' 
FROM
  Products1 
GROUP BY product_id ;


SELECT product_id, 'store1' store, store1 price FROM products WHERE store1 IS NOT NULL
UNION
SELECT product_id, 'store2' store, store2 price FROM products WHERE store2 IS NOT NULL
UNION
SELECT product_id, 'store3' store, store3 price FROM products WHERE store3 IS NOT NULL;

image-20240528130754538

image-20240528130802977

表连接

全外连接
1
2
mysql不支持全外连接
可以用left + right +union实现(这里需要去重)
笛卡尔积、CROSS JOIN
1
2
sql笛卡尔积
SQL中的笛卡尔积（Cartesian Product）是指两个表的所有可能的组合。当两个表没有关联条件时，数据库系统会对这两个表进行笛卡尔积操作，这会导致性能下降和资源浪费。
1
2
3
4
5
6
7
8
9
10
11
SELECT 
    *
FROM
    Students s
CROSS JOIN
    Subjects sub

和
from Students,Subjects

一样的，都是笛卡尔积

集合运算

union和union all
1
2
union会去重
union all不会去重

where

where (player_id, event_date) in select a,b
1
2
3
4
5
6
select player_id, device_id
from activity
where (player_id, event_date) in 
(select player_id, min(event_date)
from activity
group by player_id)

order by和limit一起使用注意事项

1
如果 order by 的列有相同的值时，MySQL 会随机选取这些行
1
2
3
4
当order by的列有重复值时，
select ... order by A和select ... order by A limit 6结果可能不一样

可以在order by A后面再加一个字段，让两个方法保持结果一致性

聚合函数

count(**)\count(*0)\count(1)
1
按照效率排序的话:count(字段)< count(主键id)< count(1)= count(*)，所以尽量使用count(*).

递归 recursive

1
题目1613 1270 3482
1
2
3
4
5
6
7
8
有两种递归字段n的声明写法，第一种是在with… as 中声明需要递归的字段，第二种是在sql语句中第一段的初始值内声明变量。

WITH RECURSIVE cte (n) AS
( select 初始值 from table
union all
select 递归内容 from cte where (终止条件)
)
这里注意 递归内容中选择的表格是cte,引用的是临时的递归表格。
1
2
3
4
5
6
7
8
1.生成数字序列
with recursive t(n) as
(
	select 1
	union all
	select n+1 from t where n<100

)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
2.树状或者层级结构

比如公司员工表，用一个员工表join另一个员工表可以得到上下级关系，再join...，但是又时不知道有多少个层级，就得用recursive了


with Recursive emp_path as(
	select emp_id,emp,name,emp_name path
	from employee
	where emp_id = 1
	union all
	select e.emp_id,e.emp_name,concat(m.path,'->',e.emp_name)
	from employee e
	join emp_path m on (m.emp_id=e.manager)
)
select * from emp_path;

image-20250703111238963

类型转换

cast
1
CAST(expression AS TYPE) 函数可以将任何类型的值转换为具有指定类型的值,利用该函数可以直接在数据库层处理部分因数据类型引起的问题
1
2
3
4
5
6
7
8
9
支持的TYPE类型	描述
BINARY	二进制型
CHAR	字符型
DATE	日期，格式为 ‘YYYY-MM-DD’
DATETIME	日期加具体的时间，格式为 ‘YYYY-MM-DD HH:MM:SS’
TIME	时间，格式为 ‘HH:MM:SS’
DECIMAL	float 型
SIGNED	int 型
UNSIGNED	无符号int
convert
1
2
3
CONVERT(value, type)
OR:
CONVERT(value USING charset)

字符串

字符串拼接
1
2
3
4
5
1.concat('xxx',num)
带分隔符拼接
2.CONCAT_WS('-', 'ID', 255); -- 结果：'ID-255'
3.强制类型转换：用 CAST() 或 CONVERT() 显式转换数字为字符串
SELECT CONCAT('ID:', CAST(100 AS CHAR));
1
2
3
4
5
6
7
避免用 + 运算符
MySQL 中 + 只做数学加法（非字符串拼接），会导致意外结果：
SELECT 'ID:' + 100; -- 结果：100（字符串被隐式转为数字0）

处理 NULL 值
如果拼接内容包含NULL，结果会变成NULL。解决方案：
SELECT CONCAT('文本', IFNULL(数字列, '')); -- 将NULL转为空字符串
substring_index substring
substring_index
1
按关键字进行读取
1
2
3
4
5
6
7
SUBSTRING_INDEX是MySQL中的一个非常实用的字符串截取函数。

它的基本用法是 SUBSTRING_INDEX(str, delim, count)

其中str是要截取的原始字符串，delim是作为分隔符的字符，而 count 指定了分隔符出现的次数。这个函数返回str中在第count次出现delim之前的子字符串。如果count是正数，它返回从左边开始到delim的所有内容；如果count是负数，它则返回从delim开始之后右边的所有内容。

如果分隔符不存在，函数会返回整个字符串
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- 获取第一个逗号前的字符串
SELECT SUBSTRING_INDEX('15,151,152,16', ',', 1);
-- 结果: '15'

-- 获取第二个逗号前的字符串
SELECT SUBSTRING_INDEX('15,151,152,16', ',', 2);
-- 结果: '15,151'

-- 获取最后一个逗号后的字符串
SELECT SUBSTRING_INDEX('15,151,152,16', ',', -1);
-- 结果: '16'

-- 获取倒数第二个逗号前的字符串中，最后一个逗号后的部分
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('15,151,152,16', ',', 2), ',', -1);
-- 结果: '151'

-- 获取倒数第二个逗号后的字符串中，第一个逗号前的部分
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('15,151,152,16', ',', -2), ',', 1);
-- 结果: '152'
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
--当不满足条件时，返回整个字符串

mysql> select substring_index('abc-d cda*+a dfji','|',1);
+--------------------------------------------+
| substring_index('abc-d cda*+a dfji','|',1) |
+--------------------------------------------+
| abc-d cda*+a dfji                          |
+--------------------------------------------+
1 row in set (0.01 sec)

mysql> select substring_index('abc-d cda*+a dfji','+',2);
+--------------------------------------------+
| substring_index('abc-d cda*+a dfji','+',2) |
+--------------------------------------------+
| abc-d cda*+a dfji                          |
+--------------------------------------------+
1 row in set (0.00 sec)
substring()
1
2
3
4
不是下标为0开始

--2018-12-18
substring(trans_date,1,7)
1
2
substring(str, pos)，即：substring(被截取字符串， 从第几位开始截取)
substring(str, pos, length)，即：substring(被截取字符串，从第几位开始截取，截取长度)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 当超出字符范围时，返回空字符''

mysql> select substring('abc-d cda*+a dfji',20);
+-----------------------------------+
| substring('abc-d cda*+a dfji',20) |
+-----------------------------------+
|                                   |
+-----------------------------------+
1 row in set (0.01 sec)

mysql> select substring('abc-d cda*+a dfji',20,2);
+-------------------------------------+
| substring('abc-d cda*+a dfji',20,2) |
+-------------------------------------+
|                                     |
+-------------------------------------+
instr
1
2
3
4
INSTR函数用于返回子字符串在字符串中第一次出现的位置。如果子字符串不存在，则返回0

SELECT INSTR('MySQL INSTR', 'SQL'); -- 返回 3
SELECT INSTR('MySQL INSTR', 'sql'); -- 返回 3（不区分大小写）
1
2
使用BINARY运算符可以使搜索区分大小写
SELECT INSTR('MySQL INSTR', BINARY 'sql'); --返回 0
1
2
INSTR函数也可以用于模糊查询，类似于LIKE运算符
SELECT productName FROM products WHERE INSTR(productName, 'Car') > 0;
left right
1
2
left(str,n) --从左起截n个
right(str,n) --从右起截n个
length
1
字符串字符数
TRIM
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
TRIM() 函数用于从字符串的开头和结尾删除指定字符，默认情况下删除空格

TRIM([BOTH | LEADING | TRAILING] [remstr] FROM str)


BOTH: 删除两侧的字符（默认）。
LEADING: 删除开头的字符。
TRAILING: 删除结尾的字符。
remstr: 要删除的字符集，默认为空格

删除两侧空格
SELECT TRIM(BOTH ' ' FROM ' Hello World ');
-- 输出: 'Hello World'

删除左侧空格
SELECT TRIM(LEADING ' ' FROM ' Hello World');
-- 输出: 'Hello World'

删除右侧空格
SELECT TRIM(TRAILING ' ' FROM 'Hello World ');
-- 输出: 'Hello World'

删除指定字符
SELECT TRIM(BOTH '*' FROM '**Hello*World**');
-- 输出: 'Hello*World'
UPPER LOWER
1
2
3
4
5
UPPER(expression)：将字符串表达式转换为大写。

LOWER(expression)：将字符串表达式转换为小写。

CONCAT(string1, string2, ...)：将两个或多个字符串连接成一个。
GROUP_CONCAT() CONCAT() CONCAT_WS()
contcat()
1
2
无连接符
concat(x,x,x..)
1
2
指定连接符
concat(x,'-',x,'-',x)
concat_ws()
1
2
3
CONCAT_WS()是 CONCAT With Separator 的缩写

仅需写一次分隔符即可拼接所有字符串
1
SELECT CONCAT_WS(' / ', firstname, phone, subject, qualification) AS Detail FROM kalacloud_student;
group_concat()
1
2
可以指定自定义的分隔符，不指定的情况下，默认是 ',' 
GROUP_CONCAT(DISTINCT product ORDER BY product ASC SEPARATOR ',')
例

1
2
3
4
5
SELECT sell_date,
       COUNT(DISTINCT product) num_sold,
       GROUP_CONCAT(DISTINCT product) products
FROM Activities
GROUP BY sell_date
1
2
3
4
5
6
7
select
     sell_date,
     count(distinct product) as num_sold,
     group_concat(distinct product order by product separator ',') as products
 from Activities 
 group by sell_date
 order by sell_date
正则表达式 REGEXP regexp_like
1
2
3
dna_sequence REGEXP 'xxx'

regexp_like(dna_sequence, 'xxx') 返回1包含，0不包含
1
2
3
4
5
REGEXP_LIKE(string, pattern, match_type)

string: 要检查的输入字符串。
pattern: 要匹配的正则表达式。
match_type: 可选参数，指定匹配方式，如 c 表示区分大小写，i 表示不区分大小写。
1
rlike和regexp一样的效果
REPLACE
1
2
3
replace(str,'a','')

用于将字符串中的指定字符全部替换成其它字符