mysql 基础运算符范例

mysql 基础运算符范例

DESC employees;

SELECT * FROM employees;

SELECT last_name,salary*(1+IFNULL(commission_pct,0))*12 "ANUAL SALARY"

FROM employees;

SELECT DISTINCT job_id

FROM  employees;

SELECT last_name , salary

FROM employees

WHERE salary>12000;

SELECT last_name,department_id

FROM employees

WHERE employee_id=176;

SELECT 12%5,12%3,12 MOD -5 ,12%-5

FROM DUAL;

SELECT employee_id,last_name , salary

FROM employees

WHERE employee_id%2=0;

SELECT 1<>2, 2=2,0='a',2<3,4>2,4>=4

FROM  DUAL;

SELECT  1=NULL

FROM DUAL;

SELECT NULL=NULL

FROM DUAL;

SELECT NULL <=> NULL

FROM DUAL;

SELECT 'a'='b','ab'='ab'

FROM DUAL;

SELECT last_name,salary,department_id

FROM  employees

WHERE salary=6000;

#① IS NULL \ IS NOT NULL \ ISNULL

SELECT last_name,salary,commission_pct

FROM employees

WHERE commission_pct <=> NULL

SELECT last_name,salary,commission_pct

FROM employees

WHERE commission_pct IS NULL

SELECT last_name,salary,commission_pct

FROM employees

WHERE commission_pct IS NOT  NULL

#③ BETWEEN 条件下界1 AND 条件上界2  （查询条件1和条件2范围内的数据，包含边界）

SELECT last_name,salary,commission_pct,department_id

FROM employees

WHERE salary BETWEEN 6000 AND 8000;

SELECT last_name ,salary,commission_pct,department_id

FROM employees

WHERE salary>=6000 && salary <=8000;

#查询工资不在6000 到 8000的员工信息

SELECT last_name ,salary ,commission_pct,department_id

FROM employees

WHERE salary NOT BETWEEN 6000 AND 8000;

SELECT last_name ,salary,commission_pct,department_id

FROM employees

WHERE salary <6000 || salary > 8000;

#练习：查询部门为10,20,30部门的员工信息

SELECT last_name ,salary,commission_pct,department_id

FROM employees

WHERE department_id=10 OR department_id=20 OR department_id=30

#④ in (set)\ not in (set)

SELECT last_name,salary,commission_pct,department_id

FROM  employees

WHERE department_id IN (10,20,30);

#练习：查询工资不是6000,7000,8000的员工信息

SELECT last_name,salary

FROM employees

WHERE salary NOT IN (6000,7000,8000);

#⑤ LIKE :模糊查询

# % : 代表不确定个数的字符 （0个，1个，或多个）

#练习：查询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%';

# _ ：代表一个不确定的字符

#练习：查询第3个字符是'a'的员工信息

SELECT last_name ,salary,department_id

FROM employees

WHERE last_name LIKE '__a%';

#练习：查询第2个字符是_且第3个字符是'a'的员工信息

SELECT last_name

FROM employees

WHERE last_name LIKE '_\_a%';

#⑥ REGEXP \ RLIKE :正则表达式

SELECT last_name

FROM employees

WHERE last_name REGEXP '^Ki';

SELECT last_name

FROM employees

WHERE last_name RLIKE 'a$';

#3. 逻辑运算符： OR ||  AND && NOT ! XOR

#查找部门ids是10或者20的员工信息

SELECT last_name ,department_id

FROM employees

WHERE department_id=10 OR department_id=20;

SELECT last_name ,department_id

FROM employees

WHERE department_id=10 || department_id=20;

#查找部门里面ID 50 ,且薪资大于6000的员工信息

SELECT last_name ,department_id

FROM employees

WHERE department_id=50 AND salary >6000;

SELECT last_name ,department_id

FROM employees

WHERE department_id=50 && salary >6000;

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。