1,算术运算
加:A+B
100 + a = 100 #此时a为0
减:A-B
乘:A*B
除:A/B —— A DIV B
50——整型变量 100 * 1 = 11
50.5——浮点型变量 100 * 1.0 = 100.0 #结果有小数点
除法默认结果带有小数点后四位 100/2=50.0000
余:A%B —— A MOD B
SELECT 12 % 3 , 12 % 5 , 12 MOD -5 , -12 % 5 , -12 % -5
结果正负与%前的数有关
举例:查询points位偶数的顾客
SELECT last_name,city,points
FROM customers
WHERE points % 2 = 0
2,比较运算符
= , <=> , <> != , < , <= , > , >=
A,等于
SELECT 1=2 , 1!=2 , 1='1' , 1='a' , 0='a' #字符存在隐式转换,如果转换数值不成功,则看做0
FROM dual;
'a'='b'输出结果为0
举例:
SELECT last_name,city,points
FROM customers
WHERE points = NULL #不会有任何结果,因为NULL参与了运算
B,安全等于
和=相同,但NULL<=>NULL是正确的
注意:SELECT 1<=>NULL , NULL<=>NULL
用法:查询phone中为NULL的字句
SELECT last_name,phone
FROM customers
WHERE phone <=> NULL;
3,IS NULL / IS NOT NULL / ISNULL
a,IS NULL
用法:查询phone中为NULL的字句
SELECT last_name,phone
FROM customers
WHERE phone IS NULL;
b,IS NOT NULL
用法:查询phone中不为NULL的字句
SELECT last_name,phone
FROM customers
WHERE phone IS NOT NULL;
4, LEAST() /GREATEST查询最大最小
举例:
SELECT LEAST(first_name,last_name),LEAST(LENGTH(last_name),LENGTH(last_name))比长短(first_name,last_name)
FROM customers
5,BETWEEN AND闭区间
用法:查询点数 在1000到2000以内的顾客
SELECT last_name,first_name,points
FROM customers
WHERE points BETWEEN 1000 AND 2000
查询结果包括边界值
6,IN(SET) / NOT IN (SET)
用法:查询city为Waltham,Orlando,Nashville的顾客
SELECT last_name,first_name,city
FROM customers
#WHERE city='Waltham' OR city='Orlando' OR city='Nashville'
WHERE city IN ('Waltham' ,'Orlando' ,'Nashville')
用法:查询city不是Waltham,Orlando,Nashville的顾客
SELECT last_name,first_name,city
FROM customers
#WHERE city!='Waltham' OR city!='Orlando' OR city!='Nashville'
WHERE city NOT IN ('Waltham' ,'Orlando' ,'Nashville')
7,LIKE模糊查询
用法:查询last_name中包含字母a的顾客
SELECT last_name,first_name,city
FROM customers
WHERE last_name LIKE '%a%'
%代表不确定个数的字符(0个,1个,或多个)
用法:查询last_name中以字母A开头的顾客
SELECT last_name,first_name,city
FROM customers
WHERE last_name LIKE 'A%'
用法:查询last_name中既有字母m又有字母a的顾客
SELECT last_name,first_name,city
FROM customers
WHERE last_name LIKE '%A%' AND last_name LIKE '%M%'
用法:查询last_name中第二个字母是a的顾客
SELECT last_name,first_name,city
FROM customers
WHERE last_name LIKE '_A%'
下划线“_”代表一个不确定的字符
用法:查询last_name中第二个字母是_第三个字母是a的顾客
SELECT last_name,first_name,city
FROM customers
WHERE last_name LIKE '_A%'
8,正则表达式REGEXP / RLIKE
举例:SELECT 'shkstart' REGEXP '^s' , 'shkstart' REGEXP 't$' , 'shkstart' REGEXP 'hk'
FROM DUAL
^s ——> 以s开头
t$ ——> 以t结尾
hk ——> 包含hk
9,逻辑运算符
A,NOT / !
举例:SELECT last_name,first_name,city
FROM customers
WHERE NOT points <=> NULL
B, AND / &&
举例:SELECT last_name,first_name,city
FROM customers
WHERE city ='Waltham' AND last_name = '_a%'
C, OR / ||
举例:SELECT last_name,first_name,city
FROM customers
WHERE city ='Waltham' OR city ='Orlando' OR city ='Nashville'
D, XOR 异或
举例:SELECT last_name,first_name,city
FROM customers
WHERE city ='Waltham' XOR last_name = '_a%'
一正一反,满足前面不满足后面,或者满足后面不满足前面
注意:AND优先级高于OR
10,位运算符#二进制比较
A,AND=&
SELECT 12 & 5
FROM DUAL
B,OR=|
SELECT 4 | 12 | 5
FROM DUAL
C,XOR=^
SELECT 13 ^ 12 ^ 5
FROM DUAL
4,~=按位取反
SELECT 10 & ~1
FROM DUAL