mysql运算符

运算符

运算符包括算术运算符，比较运算符，逻辑运算符，位运算符
运算符的优先级

#运算符

#1，算术运算符： + - * div(/) %(mod)
SELECT 100,100+3,30*2
FROM DUAL;

SELECT 100+‘1’
FROM DUAL;

SELECT 100+‘Ab’
FROM DUAL;

SELECT 100+NULL
FROM DUAL;

SELECT 10/1.0
FROM DUAL;

SELECT 100 MOD 0
FROM DUAL;#分母如果为零结果为null

SELECT 12%3,12%5,23 MOD 5,23%-3,12%-5,12/3.5
FROM DUAL;

SELECT 10 /3.0
FROM DUAL;

#查询员工id为偶数的员工信息
SELECT *
FROM employees
WHERE employee_id%2=0

SELECT 1=2,1!=2,1=‘1’,1=‘A’,‘a’=‘a’,‘a’=‘ab’,1=NULL,NULL=NULL
FROM DUAL;

SELECT last_name,salary,commission_pct
FROM employees
#where salary=6000;
WHERE commission_pct <=> NULL;

SELECT last_name,salary,commission_pct
FROM employees
#where salary=6000;
WHERE commission_pct IS NOT NULL;

SELECT NULL IS NULL,NULL ISNULL,ISNULL(NULL),ISNULL(‘a’),1 IS NULL;

SELECT LEAST(1,0.99,3.1),LEAST(‘a’,‘c’,‘v’),LEAST(‘bc’,‘s’,‘z’),LEAST(1,2,NULL)

SELECT GREATEST(1,0.99,3.1),GREATEST(‘a’,‘c’,‘v’),GREATEST(‘bc’,‘s’,‘z’),GREATEST(1,2,‘a’,NULL)

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

SELECT employee_id,last_name,salary
FROM employees
#where salary between 6000 and 8000;
WHERE salary >=6000 && salary<=8000; #(用and或者用&&等价)
SELECT 11 BETWEEN 10 AND 12;

SELECT employee_id,last_name,salary
FROM employees
#where salary not between 6000 and 8000;
WHERE salary <6000 OR salary>8000; #(用and或者用&&等价)

#查询部门为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);

#模糊查询，查找last_name中包含字符’a’的员工信息
SELECT last_name
FROM employees
WHERE last_name LIKE’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%';

SELECT last_name
FROM employees
WHERE last_name LIKE’%a%e%‘OR last_name LIKE’%e%a%';

SELECT last_name
FROM employees
WHERE last_name LIKE’_a%e’;

SELECT last_name
FROM employees
WHERE last_name LIKE ‘__a%’;

SELECT last_name
FROM employees
WHERE last_name LIKE ‘__a%’;

SELECT last_name,salary,department_id
FROM employees
#where department_id=50 and salary>6000;
#where salary between 6000 and 8000;
#where salary not between 6000 and 10000;
#WHERE commission_pct IS NULL;
WHERE commission_pct IS NOT NULL;

SELECT 12&5,12|5,12^5
FROM DUAL;
SELECT 10&~1;

#1.选择工资不在5000到12000的员工姓名和工资
SELECT last_name,salary
FROM employees
WHERE salary NOT BETWEEN 5000 AND 12000;

#2.选择在20或50号部门工作的员工姓名和部门号
SELECT last_name,department_id
FROM employees
#where department_id=20 or department_id=50;
WHERE department_id IN(20,50);

#3.选择公司中没有管理者的员工姓名以及job_id
SELECT last_name,job_id
FROM employees
WHERE manager_id IS NULL;
#或 where manager_id <=>null;

#4.选择公司中没有奖金的员工姓名，工资和奖金级别
SELECT last_name,salary,commission_pct
FROM employees
WHERE commission_pct IS NOT NULL;
#或 where not manage_id<=>null;

#5.选择员工的姓名的第三个字母是a的员工姓名
SELECT last_name
FROM employees
WHERE last_name LIKE’__a%';

#6.选择姓名中有a和k的员工姓名
SELECT last_name
FROM employees
WHERE last_name LIKE’%a%k%‘OR’%k%a%';

#7.显示出表employees表中first_name以’e’结尾的员工信息
SELECT first_name,last_name
FROM employees
WHERE first_name LIKE’%e’;

#下边使用正则表达式 字母$表示以什么字母结尾SELECTfirs{t}_{n}ame,las{t}_{n}ameFROMemployeesWHEREfirs{t}_{n}ameREGEX{P}^{\prime }e$’;

SELECT first_name,last_name
FROM employees
WHERE first_name RLIKE ‘e$’;

#8.显示出表employees部门编号在80到100之间的姓名，工种
SELECT last_name,job_id
FROM employees
WHERE department_id BETWEEN 80 AND 100;

#9.显示出表employees表中manage_id是100,101，110的员工信息，工资，管理者id
SELECT last_name,salary,manager_id
FROM employees
WHERE manager_id IN(100,101,110);