SQL基础操作

基础查询

查询所有列

SELECT * FROM table_name;

*表示通配符，*表示选择表中的所有列
table_name表示需要查询的表的名称

查询多列

SELECT column1,column2,... FROM table_name;

这条语句会返回table_name表中的column1,column2,…列的所有行

使用别名

SELECT first_name AS name,last_name AS surname FROM table_name AS tn;

这条语句会返回表中的first_name,last_name列，并将它们分别重命名为name和surname。同时table_name被重命名为tn

查询结果的过滤

SELECT column1,column2,... FROM table_name WHERE condition;

WHERE子句可以根据条件过滤结果
condition:限制条件

查询结果的排序

SELECT column1,column2,... FROM table_name ORDER BY column1 [ASC|DESC],column2 [ASC|DESC],...;

ORDER BY子句可以根据指定的列对查询结果进行排序。
column1 [ASC|DESC]:按照column1[升序|降序]进行排序，若不加ASC或者DESC则默认升序。

查询结果的去重

SELECT DISTINCT column1,column2,... FROM table_name;

使用DISTINCT关键字可以去除查询结果中的重复行。
对column1,colum2,…列中的重复行进行去除。

查询结果的限制

SELECT column1,column2,... FROM table_name LIMIT n;

在某些数据库系统中，使用LIMIT子句限制查询结果的行数。
限制输出n行

条件查询

多列排序

ORDER BY column1 ASC,column2 DESC;

ASC:表示按升序排序（默认值）
DESC：表示按降序排序
column1：指定要排序的列名

同时也可以在ORDER BY 子句中使用列的别名进行排序。

例如

SELECT column1,column2 AS co FROM user_profile ORDER BY co DESC;

基础操作符

操作符用于WHERE子句中指定条件，以过滤查询结果。

1. 比较运算符（=、<>或!=、>、<、>=、<=）
2. 逻辑运算符：AND、OR、NOT
3. IN操作符：

   IN操作符用于指定多个可能的值，返回布尔值。

SELECT column1,column2 FROM table_name WHERE column1 IN (value1,value2,...);

#查询部门id为101或者103的员工
SELECT * FROM employees WHERE department_id IN (101,103)

4. BETWEEN操作符
   BETWEEN操作符用于指定一个范围，返回布尔值。

SELECT column1,column2 FROM table_name WHERE column1 BETWEEN value1 AND value2;

#查询工资在50000到70000之间的员工
SELECT * FROM employees WHERE salary BETWEEN 50000 AND 70000;

5. LIKE操作符
   LIKE操作符用于模式匹配，返回布尔值。

SELECT column1,column2 FROM table_name WHERE column1 LIKE pattern;

#查询名字以字母"A"开头的员工
SELECT * FROM employees WHERE first_name LIKE "A%";

%:匹配任意数量的字符，包括零字符
_:匹配单个字符

高级操作符

IS NULL和IS NOT NULL,用于检查一个列是否为NULL或不为NULL。

# 查询工资为NULL的员工
SELECT * FROM table_name WHERE salary IS NULL;

# 查询工资不为NULL的员工
SELECT * FROM table_name WHERE salary IS NOT NULL;

EXISTS操作符用于检查子查询是否返回任何行。如果子查询返回至少一行，则EXISTS条件为TRUE。

SELECT column1,column2 FROM table_name WHERE EXISTS (SELECT 1 FROM another_table WHERE condition);

# 查询所有工资不低于表中其他员工工资的员工
SELECT * FROM user_profile el WHERE NOT EXISTS (
    SELECT 1
    FROM user_profile e2
    WHERE e2.device_id >e1.device_id
);
# 对于每个e1，子查询查找是否存在工资比e1更高的员工(e2.salary>e1.salary)
# 如果不存在，说明e1的工资是最高的

ALL和ANY操作符用于比较一个值与子查询返回的值集，ALL：与子查询返回的所有值进行比较，ANY：与子查询返回的任意一个值进行比较。

SELECT column1,column2 FROM table_name WHERE column1>ALL(SELECT column1 FROM another_table WHERE condition);

# 查询工资高于所有部门平均工资的员工
SELECT * FROM employees e WHERE salary > ALL (
    SELECT AVG(salary)
    FROM employees
    GROUP BY department_id
);

正则表达式

常见的正则表达式

字符类

字符类用于匹配特定的字符集集合

• .：匹配任何单个字符(除换行符外):a.b匹配a后面跟着任何字符，再跟着b,如a1b,a-b
• [abc]:匹配方括号内的任意一个字符：[abc]匹配a、b或c
• [^abc]:匹配不在方括号内的任意一个字符：[^abc]匹配除a、b和c之外的任何字符
• [a-z]:匹配指定范围内的任意一个字符：[a-z]匹配任何小写字母
• [A-Z]:匹配任何大写字母
• [0-9]:匹配任何数字

预定义字符类

预定义字符类是一些常用的字符类的简写形式

• \d:匹配任何数字，等价于[0-9]:\d匹配任何单个数字
• \D匹配任何非数字字符，等价于[^0-9]:匹配任何非数字字符
• \s:匹配任何空白字符，包括空格、制表符、换行符等。\s 匹配任何空白字符。
• \S：匹配任何非空白字符。\S 匹配任何非空白字符。
• \w：匹配任何字母数字字符，等价于 [a-zA-Z0-9_]。\w 匹配任何字母数字字符或下划线。
• \W：匹配任何非字母数字字符。\W 匹配任何非字母数字字符。

边界匹配

边界匹配用于匹配字符串的开始或结束位置。

• ^：匹配字符串的开始位置。^abc 匹配以 abc 开头的字符串。
• $：匹配字符串的结束位置。abc$ 匹配以 abc 结尾的字符串。
• \b：匹配单词边界。\bword\b 匹配完整的单词 word，而不是 word 的子字符串。

量词

量词用于指定前面的字符或字符类可以出现的次数。

• *：匹配前面的字符或字符类 0 次或多次。a* 匹配 0 个或多个 a，如 a、aa、aaa 或空字符串。
• +：匹配前面的字符或字符类 1 次或多次。a+ 匹配 1 个或多个 a，如 a、aa、aaa，但不匹配空字符串。
• ?：匹配前面的字符或字符类 0 次或 1 次。a? 匹配 0 个或 1 个 a，如 a 或空字符串。
• {n}：匹配前面的字符或字符类恰好 n 次。a{3} 匹配恰好 3 个 a，如 aaa。
• {n,}：匹配前面的字符或字符类至少 n 次。a{3,} 匹配 3 个或更多 a，如 aaa、aaaa。
• {n,m}：匹配前面的字符或字符类至少 n 次，但不超过 m 次。a{2,4} 匹配 2 到 4 个 a，如 aa、aaa、aaaa。

分组和捕获

分组和捕获用于将正则表达式的一部分分组，并捕获匹配的内容。

• ()：分组和捕获。(abc) 匹配 abc，并捕获匹配的内容。
• (?:...)：非捕获分组。(?:abc) 匹配 abc，但不捕获匹配的内容。

选择

选择用于匹配多个模式中的一个。

• |：选择。abc|def 匹配 abc 或 def。

贪婪和非贪婪

量词的贪婪和非贪婪模式决定了匹配的长度。

• 贪婪模式：默认情况下，量词是贪婪的，尽可能多地匹配字符。a+ 匹配尽可能多的 a。
• 非贪婪模式：在量词后加 ?，使其变为非贪婪模式，尽可能少地匹配字符。a+? 匹配尽可能少的 a。

断言

断言用于指定匹配的条件，但不消耗字符。

• 前瞻断言：
  • 正前瞻：(?=...)，匹配后面跟着指定模式的字符。abc(?=def) 匹配 abc，但只有在其后面跟着 def 时才匹配。
  • 负前瞻：(?!...)，匹配后面不跟着指定模式的字符。abc(?!def) 匹配 abc，但只有在其后面不跟着 def 时才匹配。
• 后顾断言：
  • 正后顾：(?<=...)，匹配前面跟着指定模式的字符。(?<=abc)def 匹配 def，但只有在其前面跟着 abc 时才匹配。
  • 负后顾：(?<!...)，匹配前面不跟着指定模式的字符。(?<!abc)def 匹配 def，但只有在其前面不跟着 abc 时才匹配。

使用语法

在Mysql中，可以使用REGEXP或RLIKE操作符来使用正则表达式

# REGEXP
SELECT column1,column2 FROM table_name WHERE column1 REGEXP 'pattern';

# RLIKE
SELECT column1,column2 FROM table_name WHERE column_name RLIKE 'pattern';

使用LIKE和正则表达式的区别

• LIKE操作符：
  • 适用于简单的匹配模式，支持%和_两个通配符
  • 性能较好，适用于大多数简单的文本匹配需求
• 正则表达式
  • 适用于复杂的模式匹配，支持更广泛的模式匹配功能
  • 性能可能稍差，但在处理复杂模式时非常强大

高级查询

计算函数

分组查询

多表查询

子查询

链接查询

组合查询

必会的常用函数

条件函数

日期函数

文本函数

窗口函数

聚合函数与窗口函数的结合使用

数学函数