比較運算符

< > = !

# = <=> <> != < <= > >=
SELECT 1 =1, 1 != 2, 1 = '1', 1 = 'a', 0 = 'a' # 字符串存在隱式轉換，如果轉換數值不成功，則看做0
FROM DUAL;

SELECT 'a' = 'a', 'ab' = 'ab', 'a' = 'b' # 兩邊都是字符串的話，則按照ANSI的比較規(guī)則進行比較
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() 查詢數據大?。ㄗ值湫颍?/h3>

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優(yōu)先級高于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;

文章來源地址http://www.zghlxwxcb.cn/news/detail-795475.html

到了這里，關于Mysql運算符的文章就介紹完了。如果您還想了解更多內容，請在右上角搜索TOY模板網以前的文章或繼續(xù)瀏覽下面的相關文章，希望大家以后多多支持TOY模板網！