初学MYSQL第三天

进阶2:条件查询

语法:
	select 查询列表 from 表名 where 筛选条件;

分类:

一、 按条件表达式筛选

	条件运算符:> < = != <> >= <=

案例1:查询工资>12000的员工信息

	SELECT * FROM employees WHERE salary>12000;

案例2:查询部门编号不等于90号的员工名和部门编号

	SELECT
				last_ name ,department id
	FROM 
				employees 
	WHERE
				department_id<>90;

二、按逻辑表达式筛选

	逻辑运算符:and or not

案例1:查询工资在10000到20000之间的员工名、工资以及奖金

	SELECT	
			last_ name，salary,
			 commission_ pct
	FROM
			employees
	WHERE
			salary>=10000 AND salary<=20000;

案例2:查询部门编号不是在90到110之间，或者工资高于15000的员工信息

SELECT *
FROM
		employees
WHERE
		department_ id<90 OR department id> 110 OR salary>15000;

三、模糊查询

		like
		between and	
		in
		is nu11

1.like

案例1:查询员工名中包含字符a的员工信息

SELECT  *
FROM
		employees
WHERE
		last_ name LIKE '%a%' ;/*‘%’代表通配符*/

案例2:查询员工名中第三个字符为n，第五个字符为l的员工名和工资

SELECT
		last_ name,
		salary
FROM
employees .
WHERE
last name LIKE '__n_l%' ;

案例3:查询员工名中第二个字符为_的员工名

SELECT
		last name .
FROM
		employees
WHERE
		last_ name LIKE '_\_%' ESCAPE '\';/*自定义转义字符‘\’*/

2.between and

案例1:查询员工编号在100到120之间的员工信息

SELECT *
FROM
		employees
WHERE
		employee_ id >= 100 AND employee_ id<=120;
或者写为
SELECT *
FROM
		employees
WHERE
		employee_ id BETWEEN 100 AND 120;

3.in

案例:查询员工的工种编号是IT_ PROG、 AD VB、 AD PREs中的一-个员工名和工种编号

SELECT
		last_ name,
		job_ id
FROM
		employees
WHERE
		job_ id IN( 'IT_ PROT' , 'AD_ _YP', 'AD_ PRES') ;

4、is null

案例1:查询没有奖金的员工名和奖金率

SELECT
		last_ name , 
		commission_ pct
FROM
		employees
WHERE
		commission_ pct IS NULL;