数据库—运算符

#第四章运算符
#1.算数运算符: + - *  / div % mod
SELECT 100,100+0,100-0,100+50,100+50-30,100+35.5,100-35.5
FROM DUAL;
#在sql中,+没有连接的作用,就表示加法运算。此时,会将字符串转换为数值(隐式转换)
SELECT 100 + '1'    #在java中,结果是1001
FROM DUAL;
SELECT 100+'a' #此时将‘a’看作0来处理
FROM DUAL;
SELECT 100+NULL #null值参与运算,结果为null
FROM DUAL;
SELECT 100,100*1,100*1.0,100/1.0,100/2,
100+2*5/2,100/3,100 DIV 0,100/0  #分母如果为0,结果为null
FROM DUAL;
#取模运算
SELECT 12%3,12%5,12 MOD -5,-12 MOD 5,-12%-5
FROM DUAL;
#查询员工id为偶数的员工信息
SELECT employee_id,last_name,salary
FROM  employees
WHERE employee_id MOD 2 = 0;
#2比较运算符
#2.1 =, <=>, <>, !=, >=, <=

SELECT 1 = 2, 1!=2, 1 = '1', 1 = 'a' #字符串存在隐式转换,如果转换数值不成功看作0
FROM DUAL;
SELECT  'a' = 'a','ab' = 'ab','a' = 'b' #两边都是字符串的话,按照ANSI的比较规则进行比较
FROM DUAL; 
SELECT 1 = NULL,NULL = NULL    #只要有null参与判断,结果就为null
FROM DUAL;
SELECT last_name,salary,commission_pct
FROM employees
#where salary = 6000;
WHERE commission_pct = NULL; #此时执行不会有任何结果
#<=>:安全等于,可以对null进行判断,没有null参与的情况下和=效果一样
#练习:查询表中commission_pct为null的数据有哪些
SELECT last_name,salary,commission_pct
FROM employees
#where salary = 6000;
WHERE commission_pct <=> NULL;
SELECT 3<>2,'4'<>NULL,''!=NULL
FROM DUAL;
#2.
#1.is null /is not null / isnull
SELECT last_name,salary,commission_pct
FROM employees
WHERE commission_pct IS NULL;
#或
SELECT last_name,salary,commission_pct
FROM employees
WHERE ISNULL(commission_pct);
#不是null的值
SELECT last_name,salary,commission_pct
FROM employees
WHERE commission_pct IS NOT NULL;
SELECT last_name,salary,commission_pct
FROM employees
WHERE NOT commission_pct <=> NULL;
#(2).least()/greatest()
SELECT LEAST('g','b','t','m'),GREATEST('g','b','t','m')
FROM DUAL;
SELECT LEAST(first_name,last_name),LEAST(LENGTH(first_name),LENGTH(last_name))
FROM employees;
#(3)BETWEEN 条件下界一 AND 条件下届二(查询条件一和条件二范围内的数据,包含边界数据)
#查询工资在6000=8000的员工信息
SELECT employee_id,last_name,salary
FROM employees
WHERE salary BETWEEN 6000 AND 8000;
#交换6000和8000之后,查询不到数据
SELECT employee_id,last_name,salary
FROM employees
WHERE salary>=6000 && salary<=8000;
#查询工资不在6000到8000的员工信息
SELECT employee_id,last_name,salary
FROM employees
WHERE salary>6000 OR salary<8000;
SELECT employee_id,last_name,salary
FROM employees
WHERE NOT salary BETWEEN 6000 AND 8000;
#(4)in(set)/not in
#练习:查询部门为10,20,30部门的员工信息

SELECT last_name,salary,department_id
FROM employees
#where department_id = 10 or department_id = 20 or department_id = 30;
WHERE department_id IN (10,20,30);

#练习:查询工资不是6000,7000,8000的员工信息
SELECT last_name,salary,department_id
FROM employees
WHERE salary NOT IN(6000,7000,8000)
#(5)LIKE:模糊查询
#练习:查询last_name中包含字符'a'的员工信息
#%:代表不确定个数的字符(0个,1个,多个)
SELECT last_name,salary,department_id
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%' && last_name LIKE '%e%';
#写法二:
SELECT last_name
FROM employees
WHERE last_name LIKE '%a%e%' OR last_name LIKE '%e%a%';
#查询第二个字符是‘a’的员工信息
#_:代表一个不确定的字符
SELECT last_name
FROM employees
WHERE last_name LIKE '__a%';
#查询第二个字符是‘_’的员工信息
#需要使用转义字符
SELECT last_name
FROM employees
WHERE first_name LIKE '_\_a%'
#where first_name like '_$_a%' escape '$';
SELECT *
FROM employees;
#(6)regexp \ RLIKE:正则表达式
SELECT 'lanxiaodong' REGEXP '^l','lanxiaodong' REGEXP 'g$','lanxiaodong' REGEXP 'xiao'
FROM DUAL;
SELECT 'atguigu' REGEXP 'g..gu', 'atguigu' REGEXP '[ab]'
FROM DUAL;

给TA打赏
共{{data.count}}人
人已打赏
知识碎片

CSS的过渡属性

2021-11-28 17:11:07

知识碎片

多线程中wait()方法的使用

2021-11-28 23:21:14

0 条回复 A文章作者 M管理员
    暂无讨论,说说你的看法吧