Mysql运算符

发布时间:2024年01月14日

比较运算符

< > = !

# = <=> <> != < <= > >=
SELECT 1 =1, 1 != 2, 1 = '1', 1 = 'a', 0 = 'a' # 字符串存在隐式转换,如果转换数值不成功,则看做0
FROM DUAL;

SELECT 'a' = 'a', 'ab' = 'ab', 'a' = 'b' # 两边都是字符串的话,则按照ANSI的比较规则进行比较
FROM DUAL;

SELECT 1 = NULL, NULL = NULL # 只要有null参与比较判断,结果就是null
FROM DUAL;

# <=>安全等于,为NULL而生
SELECT 1 <=> NULL, NULL <=> NULL # 只要有null参与比较判断,结果就是null
FROM DUAL;

IS NULL \ IS NOT NULL \ ISNULL

# 查询表中为null的数据
SELECT last_name,salary,commission_pct
FROM employees
WHERE commission_pct IS NULL;
# 查询表中为null的数据
SELECT last_name,salary,commission_pct
FROM employees
WHERE commission_pct IS NOT NULL;
# 查询表中并不为null的数据
SELECT last_name,salary,commission_pct
FROM employees
WHERE ISNULL(commission_pct);

LEAST() \ GREATEST() 查询数据大小(字典序)


SELECT LEAST('g','b','c','a'), GREATEST('g','b','c','a')
FROM DUAL;

SELECT LEAST(first_name,last_name), GREATEST(first_name,last_name)
FROM employees;

BETWEEN…AND…


# 查询工资在6000到8000的员工工资
SELECT employee_id,last_name,salary
FROM employees
WHERE salary BETWEEN 6000 AND 8000;

IN (SET) \ NOT IN (SET)

#查询部门为10,20,30的员工信息
SELECT last_name,salary,department_id
FROM employees
WHERE department_id IN(10,20,30);
#查询部门不为10,20,30的员工信息
SELECT last_name,salary,department_id
FROM employees
WHERE department_id NOT IN (10,20,30);

LIKE 模糊查询

# % : 代表不确定个数的字符
# 查询last_name中包含字符包含‘a’的员工信息
SELECT last_name
FROM employees
WHERE last_name LIKE '%a%';
# 查询last_name中包含字符以‘a’开头的员工信息
SELECT last_name
FROM employees
WHERE last_name LIKE 'a%';
# 查询last_name中包含字符包含‘a’且包含字符‘e’的员工信息
SELECT last_name
FROM employees
WHERE last_name LIKE '%a%' AND last_name LIKE '%e%';
# -:代表一个不确定的字符
# 查询第2个字符是‘a’的员工信息
SELECT last_name
FROM employees
WHERE last_name LIKE '_a%';
# 查询第2个字符是‘_’第3个字符是‘a’的员工信息
# 需要转义字符:\
SELECT last_name
FROM employees
WHERE last_name LIKE '_\_a%';

REGEXP \ RLIKE

# REGEXP \ RLIKE :正则表达式

逻辑运算符

逻辑运算符: OR (||)、AND (&&)、 NOT (!)、 XOR

# OR  AND (AND优先级高于OR)
SELECT last_name,salary,department_id
FROM employees
# WHERE department_id = 10 OR department_id = 20;
# WHERE department_id = 10 AND department_id = 20;
WHERE department_id = 50 AND salary > 6000;

# NOT
SELECT last_name,salary,department_id
FROM employees
# WHERE salary NOT BETWEEN 6000 AND 8000;
# WHERE commission_pct IS NOT NULL;
WHERE NOT commission_pct <=> NULL;

# XOR: 追求”异“
SELECT last_name,salary,department_id
FROM employees
WHERE department_id = 50 XOR salary > 6000;

位运算符

位运算符 & | ^ ~ >> <<


# & (都是1为1)  | (有1为1)  ^ (不同为1)
SELECT 12 & 5 ,12 | 5,12 ^ 5
FROM DUAL;

# ~ (逐位取反)
SELECT 10 & ~1
FROM DUAL;

# << (左移)  >> (右移)
SELECT 4 << 1, 8 >> 1
FROM DUAL;

排序

ORDER BY

# 使用对查询数据进行排序
# ASC (ascend) 正序
# DESC (descend) 逆序
SELECT employee_id,last_name,salary
FROM employees
ORDER BY salary ASC;#升序可以省略ASC

SELECT employee_id,last_name,salary
FROM employees
ORDER BY salary DESC;

# 可以使用列的别名进行排序 。别名只能在ORDER BY中使用,不能在WHERE中使用
SELECT employee_id,salary,salary * 12 annual_sal
FROM employees
ORDER BY annual_sal;

# WHERE需要在FROM之后,ORDER BY之前
SELECT employee_id,salary
FROM employees
WHERE department_id IN (50,60,70)
ORDER BY department_id DESC;

# 二级排序
SELECT employee_id,salary,department_id
FROM employees
ORDER BY department_id DESC, salary ASC;

分页

使用limit实现数据的分页显示

# 分页
SELECT employee_id,last_name
FROM employees
LIMIT 0,20;#LIMIT 偏移量,条目数

# WHERE...ORDER BY...LIMIT 声明顺序如下
SELECT employee_id,last_name,salary
FROM employees
WHERE salary > 6000
ORDER BY salary
LIMIT 0,20;

# 显示第32,33条数据
SELECT employee_id,last_name
FROM employees
LIMIT 31,2;
# mysql 8.0新特性:LIMIT...OFFSET...
SELECT employee_id,last_name
FROM employees
LIMIT 2 OFFSET 31;#LIMIT 条目数 OFFSET 偏移量

# 查询员工表中工资最高的员工信息
SELECT *
FROM employees
ORDER BY salary DESC
LIMIT 1;
文章来源:https://blog.csdn.net/weixin_54793960/article/details/135575362
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。