开发中常用的SQL语句

1.update更新时不能引用本身表

UPDATE student SET valid_flag = 0
WHERE id IN (
    SELECT id
    FROM (
        SELECT su.id
        FROM student su JOIN teacher t ON su.goto_id = t.id
        WHERE t.valid_flag = 0 AND su.valid_flag != 0
    ) AS subquery
)

2.备份MySQL

mysqldump -u admin -p 123456 > /root/backup_file.sql;

出现sock报错时

mysqldump -u admin -p 123456 --socket=/home/data/mysql/mysql.sock  > lwspace0927.sql

3.函数的使用

1. case,when的使用

select CASE   pid
			 WHEN 1 THEN '语文'
			 WHEN 2 THEN '数学'
			 WHEN 3 THEN '英语'
			 ELSE  '化学'	
			 END as 学科
sum(case when se.rent_flag=1 then se.total_area else 0 end) as rentTrueCount,

2. IF

SELECT IFNULL(SUM(c.total_amount), 0)

select if(1=2,1,2);--2

3.其它

不包含数字

 where  periods not  REGEXP '[^0-9.]'

4.拼接

LEFT() 函数用于截取日期字符串的前10个字符，即日期的年月日部分。然后使用 ’ —— ’ 字符串作为连接符进行拼接。

 SELECT 
        CONCAT(left('2000-01-01',10), ' —— ', left('2000-12-31',10) ) AS contractStartEndTime,
        CONCAT(left( '2023-01-01',10),' —— ', left('2023-12-31',10) ) AS rentStartEndTime

5. 处理时间

select CONCAT(YEAR(NOW()) - 1, '-01-31 23:59:59')
select year(now())--2023

相差天数

DATEDIFF(end_time, start_time) 

日期加n天

DATE_ADD(sc.rent_end_time, INTERVAL n DAY)

4.导出表结构注释等

SHOW FULL COLUMNS FROM table_name;

SELECT table_name, column_name, column_type, is_nullable, column_default, extra, column_comment
FROM information_schema.columns
WHERE table_schema = 'shequ-order';--table_schema库名字

5.冷门

1.EXISTS

WHERE EXISTS 是一种条件子句，用于在查询中检查一个子查询是否返回结果。
语法

SELECT * FROM TableName
WHERE EXISTS (Subquery);

WHERE EXISTS 子句会根据子查询的结果来判断主查询的每一行是否满足条件。如果子查询返回任何结果（即非空），则认为条件为真，相应的主查询结果将被返回。

SELECT * FROM Customers
WHERE EXISTS (SELECT * FROM Orders WHERE Customers.CustomerID = Orders.CustomerID);

上述查询将返回所有存在具有订单的客户信息。

2.GROUP_CONCAT

解释
是一个聚合函数，用于将多行结果按照指定的顺序连接为一个字符串。它将在每个分组中将指定的列的值连接起来，形成一个字符串，并用指定的分隔符进行分隔。
语法

GROUP_CONCAT([DISTINCT] expression [ORDER BY clause] [SEPARATOR 'separator'])

DISTINCT 是可选的，表示对结果进行去重处理。
expression 是要连接的列名或表达式。
ORDER BY clause 是可选的，表示按照指定的列进行排序。
SEPARATOR 是可选的，表示用于分隔连接的字符串，默认为逗号。
案例

select se.teacher_id,GROUP_CONCAT(se.id) from sp_student  se
where valid_flag =1
group by se.teacher_id

效果

6.with

使用 WITH 关键字可以创建一个临时的视图或子查询，也称为公共表表达式（CTE）。主要优点是提高了 SQL 查询的可读性和可维护性。

语法

WITH common_table_expression_name [(column_name1, column_name2, ...)]

AS

(SELECT statement)

SELECT statement using the common table expression

解释

第一个部分用于定义公共表表达式。在括号中的部分是一个标准的 SELECT 语句，用于生成共同表格。SELECT 语句返回的结果集将在第二个部分中使用。

第二个部分就像其他 SELECT 语句一样，但是使用了第一个部分中定义的公共表表达式。
案例

WITH Example AS (
  SELECT column1, column2 FROM table_name
)
SELECT * FROM Example WHERE column1 > 5;

总结

WITH 可以为查询提供临时表，供多次使用。

注意

使用 WITH 关键字创建的公共表表达式，只存在于定义它的查询中