时间：2021-07-01 10:21:17 帮助过：23人阅读
根据条件过滤原始表的数据，查询到想要的数据。
语法：
select 要查询的字段|表达式|常量值|函数 from 表 where 条件;
介绍：
使用WHERE子句，将不满足条件的行过滤掉，WHERE子句紧随FROM子句。
示例：
SELECT employee_id, last_name, job_id, department_id FROM employees WHERE department_id = 90 ;
介绍：
=：等于
>：大于
>=：大于、等于
<：小于
<=：小于、等于
<>：不等于 (也可以是!=)
示例：
SELECT last_name, salary FROM employees WHERE salary <= 3000;
介绍：
使用BETWEEN运算来显示在一个区间内的值。
特点：
1.使用between and可以提高语句的简洁度。
2.包含临界值。
3.两个临界值不要调换顺序。
示例：
SELECT last_name, salary FROM employees WHERE salary BETWEEN 2500 AND 3500;
介绍：
判断某字段的值是否属于in列表中的某一项。
特点：
1.使用in提高语句简洁度。
2.in列表的值类型必须一致或兼容。
3.in列表中不支持通配符。
示例：
SELECT employee_id, last_name, salary, manager_id FROM employees WHERE manager_id IN (100, 101, 201);
介绍：
1.使用LIKE运算选择类似的值。
2.一般和通配符搭配使用，选择条件可以包含字符或数字：
%代表零个或多个字符(任意个字符)
_代表一个字符
3.%和_可以同时使用。
示例：
SELECT first_name FROM employees WHERE first_name LIKE ‘S%‘; SELECT last_name FROM employees WHERE last_name LIKE ‘_o%‘; SELECT last_name FROM employees WHERE last_name LIKE CONCAT(‘%‘,‘o‘,‘%‘);
介绍：
=或<>不能用于判断null值，使用IS (NOT) NULL判断空值。
特点：
IS NULL：仅仅可以判断NULL值，可读性较高，建议使用。
安全等于<=>：既可以判断NULL值，又可以判断普通的数值，可读性较低。
示例：
SELECT last_name, manager_id FROM employees WHERE manager_id IS NULL;
介绍（&&）：
逻辑并，两个条件如果同时成立，结果为true，否则为false。
示例：
SELECT employee_id, last_name, job_id, salary FROM employees WHERE salary >=10000 AND job_id LIKE ‘%MAN%‘;
介绍（||）：
逻辑或，两个条件只要有一个成立，结果为true，否则为false。
示例：
SELECT employee_id, last_name, job_id, salary FROM employees WHERE salary >= 10000 OR job_id LIKE ‘%MAN%‘;
介绍：
逻辑否，如果条件成立，则not后为false，否则为true。
示例：
SELECT last_name, job_id FROM employees WHERE job_id NOT IN (‘IT_PROG‘, ‘ST_CLERK‘, ‘SA_REP‘);
#案例：查询工资>12000的员工信息 SELECT * FROM employees WHERE salary>12000; #案例：查询部门编号不等于90号的员工名和部门编号 SELECT last_name,department_id FROM employees WHERE department_id<>90; #案例：查询工资在10000到20000之间的员工名、工资以及奖金 SELECT last_name,salary,commission_pct FROM employees WHERE salary>=10000 AND salary<=20000; #案例：查询部门编号不是在90到110之间，或者工资高于15000的员工信息 SELECT * FROM employees WHERE NOT(department_id>=90 AND department_id<=110) OR salary>15000; #案例：查询员工名中包含字符a的员工信息 SELECT * FROM employees WHERE last_name LIKE ‘%a%‘; #案例：查询员工名中第三个字符为n，第五个字符为l的员工名和工资 SELECT last_name,salary FROM employees WHERE last_name LIKE ‘__n_l%‘; #案例：查询员工名中第二个字符为_的员工名 SELECT last_name FROM employees WHERE last_name LIKE ‘_$_%‘ ESCAPE ‘$‘; #案例：查询员工编号在100到120之间的员工信息 SELECT * FROM employees WHERE employee_id >= 100 AND employee_id<=120; SELECT * FROM employees WHERE employee_id BETWEEN 100 AND 120; #案例：查询员工的工种编号是IT_PROG、AD_VP、AD_PRES中的一个员工名和工种编号 SELECT last_name,job_id FROM employees WHERE job_id = ‘IT_PROT‘ OR job_id = ‘AD_VP‘ OR JOB_ID =‘AD_PRES‘; SELECT last_name,job_id FROM employees WHERE job_id IN( ‘IT_PROT‘ ,‘AD_VP‘,‘AD_PRES‘); #案例：查询没有奖金的员工名和奖金率 SELECT last_name,commission_pct FROM employees WHERE commission_pct IS NULL; #案例：查询有奖金的员工名和奖金率 SELECT last_name,commission_pct FROM employees WHERE commission_pct IS NOT NULL; #案例：查询没有奖金的员工名和奖金率 SELECT last_name,commission_pct FROM employees WHERE commission_pct <=>NULL; #案例：查询工资为12000的员工信息 SELECT last_name,salary FROM employees WHERE salary <=> 12000;
#员工表 CREATE TABLE `employees` ( `employee_id` int(6) NOT NULL AUTO_INCREMENT, `first_name` varchar(20) DEFAULT NULL, `last_name` varchar(25) DEFAULT NULL, `email` varchar(25) DEFAULT NULL, `phone_number` varchar(20) DEFAULT NULL, `job_id` varchar(10) DEFAULT NULL, `salary` double(10,2) DEFAULT NULL, `commission_pct` double(4,2) DEFAULT NULL, `manager_id` int(6) DEFAULT NULL, `department_id` int(4) DEFAULT NULL, `hiredate` datetime DEFAULT NULL, PRIMARY KEY (`employee_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; #员工数据 INSERT INTO employees VALUES (‘100‘, ‘Steven‘, ‘K_ing‘, ‘SKING‘, ‘515.123.4567‘, ‘AD_PRES‘, ‘24000.00‘, null, null, ‘90‘, ‘1992-04-03 00:00:00‘); INSERT INTO employees VALUES (‘101‘, ‘Neena‘, ‘Kochhar‘, ‘NKOCHHAR‘, ‘515.123.4568‘, ‘AD_VP‘, ‘17000.00‘, null, ‘100‘, ‘90‘, ‘1992-04-03 00:00:00‘); INSERT INTO employees VALUES (‘102‘, ‘Lex‘, ‘De Haan‘, ‘LDEHAAN‘, ‘515.123.4569‘, ‘AD_VP‘, ‘17000.00‘, null, ‘100‘, ‘90‘, ‘1992-04-03 00:00:00‘); INSERT INTO employees VALUES (‘103‘, ‘Alexander‘, ‘Hunold‘, ‘AHUNOLD‘, ‘590.423.4567‘, ‘IT_PROG‘, ‘9000.00‘, null, ‘102‘, ‘60‘, ‘1992-04-03 00:00:00‘); INSERT INTO employees VALUES (‘104‘, ‘Bruce‘, ‘Ernst‘, ‘BERNST‘, ‘590.423.4568‘, ‘IT_PROG‘, ‘6000.00‘, null, ‘103‘, ‘60‘, ‘1992-04-03 00:00:00‘); INSERT INTO employees VALUES (‘105‘, ‘David‘, ‘Austin‘, ‘DAUSTIN‘, ‘590.423.4569‘, ‘IT_PROG‘, ‘4800.00‘, null, ‘103‘, ‘60‘, ‘1998-03-03 00:00:00‘); INSERT INTO employees VALUES (‘106‘, ‘Valli‘, ‘Pataballa‘, ‘VPATABAL‘, ‘590.423.4560‘, ‘IT_PROG‘, ‘4800.00‘, null, ‘103‘, ‘60‘, ‘1998-03-03 00:00:00‘); INSERT INTO employees VALUES (‘107‘, ‘Diana‘, ‘Lorentz‘, ‘DLORENTZ‘, ‘590.423.5567‘, ‘IT_PROG‘, ‘4200.00‘, null, ‘103‘, ‘60‘, ‘1998-03-03 00:00:00‘); INSERT INTO employees VALUES (‘108‘, ‘Nancy‘, ‘Greenberg‘, ‘NGREENBE‘, ‘515.124.4569‘, ‘FI_MGR‘, ‘12000.00‘, null, ‘101‘, ‘100‘, ‘1998-03-03 00:00:00‘); INSERT INTO employees VALUES (‘109‘, ‘Daniel‘, ‘Faviet‘, ‘DFAVIET‘, ‘515.124.4169‘, ‘FI_ACCOUNT‘, ‘9000.00‘, null, ‘108‘, ‘100‘, ‘1998-03-03 00:00:00‘); INSERT INTO employees VALUES (‘110‘, ‘John‘, ‘Chen‘, ‘JCHEN‘, ‘515.124.4269‘, ‘FI_ACCOUNT‘, ‘8200.00‘, null, ‘108‘, ‘100‘, ‘2000-09-09 00:00:00‘); INSERT INTO employees VALUES (‘111‘, ‘Ismael‘, ‘Sciarra‘, ‘ISCIARRA‘, ‘515.124.4369‘, ‘FI_ACCOUNT‘, ‘7700.00‘, null, ‘108‘, ‘100‘, ‘2000-09-09 00:00:00‘); INSERT INTO employees VALUES (‘112‘, ‘Jose Manuel‘, ‘Urman‘, ‘JMURMAN‘, ‘515.124.4469‘, ‘FI_ACCOUNT‘, ‘7800.00‘, null, ‘108‘, ‘100‘, ‘2000-09-09 00:00:00‘); INSERT INTO employees VALUES (‘113‘, ‘Luis‘, ‘Popp‘, ‘LPOPP‘, ‘515.124.4567‘, ‘FI_ACCOUNT‘, ‘6900.00‘, null, ‘108‘, ‘100‘, ‘2000-09-09 00:00:00‘); INSERT INTO employees VALUES (‘114‘, ‘Den‘, ‘Raphaely‘, ‘DRAPHEAL‘, ‘515.127.4561‘, ‘PU_MAN‘, ‘11000.00‘, null, ‘100‘, ‘30‘, ‘2000-09-09 00:00:00‘); INSERT INTO employees VALUES (‘115‘, ‘Alexander‘, ‘Khoo‘, ‘AKHOO‘, ‘515.127.4562‘, ‘PU_CLERK‘, ‘3100.00‘, null, ‘114‘, ‘30‘, ‘2000-09-09 00:00:00‘); INSERT INTO employees VALUES (‘116‘, ‘Shelli‘, ‘Baida‘, ‘SBAIDA‘, ‘515.127.4563‘, ‘PU_CLERK‘, ‘2900.00‘, null, ‘114‘, ‘30‘, ‘2000-09-09 00:00:00‘); INSERT INTO employees VALUES (‘117‘, ‘Sigal‘, ‘Tobias‘, ‘STOBIAS‘, ‘515.127.4564‘, ‘PU_CLERK‘, ‘2800.00‘, null, ‘114‘, ‘30‘, ‘2000-09-09 00:00:00‘); INSERT INTO employees VALUES (‘118‘, ‘Guy‘, ‘Himuro‘, ‘GHIMURO‘, ‘515.127.4565‘, ‘PU_CLERK‘, ‘2600.00‘, null, ‘114‘, ‘30‘, ‘2000-09-09 00:00:00‘); INSERT INTO employees VALUES (‘119‘, ‘Karen‘, ‘Colmenares‘, ‘KCOLMENA‘, ‘515.127.4566‘, ‘PU_CLERK‘, ‘2500.00‘, null, ‘114‘, ‘30‘, ‘2000-09-09 00:00:00‘); INSERT INTO employees VALUES (‘120‘, ‘Matthew‘, ‘Weiss‘, ‘MWEISS‘, ‘650.123.1234‘, ‘ST_MAN‘, ‘8000.00‘, null, ‘100‘, ‘50‘, ‘2004-02-06 00:00:00‘); INSERT INTO employees VALUES (‘121‘, ‘Adam‘, ‘Fripp‘, ‘AFRIPP‘, ‘650.123.2234‘, ‘ST_MAN‘, ‘8200.00‘, null, ‘100‘, ‘50‘, ‘2004-02-06 00:00:00‘); INSERT INTO employees VALUES (‘122‘, ‘Payam‘, ‘Kaufling‘, ‘PKAUFLIN‘, ‘650.123.3234‘, ‘ST_MAN‘, ‘7900.00‘, null, ‘100‘, ‘50‘, ‘2004-02-06 00:00:00‘); INSERT INTO employees VALUES (‘123‘, ‘Shanta‘, ‘Vollman‘, ‘SVOLLMAN‘, ‘650.123.4234‘, ‘ST_MAN‘, ‘6500.00‘, null, ‘100‘, ‘50‘, ‘2004-02-06 00:00:00‘); INSERT INTO employees VALUES (‘124‘, ‘Kevin‘, ‘Mourgos‘, ‘KMOURGOS‘, ‘650.123.5234‘, ‘ST_MAN‘, ‘5800.00‘, null, ‘100‘, ‘50‘, ‘2004-02-06 00:00:00‘); INSERT INTO employees VALUES (‘125‘, ‘Julia‘, ‘Nayer‘, ‘JNAYER‘, ‘650.124.1214‘, ‘ST_CLERK‘, ‘3200.00‘, null, ‘120‘, ‘50‘, ‘2004-02-06 00:00:00‘); INSERT INTO employees VALUES (‘126‘, ‘Irene‘, ‘Mikkilineni‘, ‘IMIKKILI‘, ‘650.124.1224‘, ‘ST_CLERK‘, ‘2700.00‘, null, ‘120‘, ‘50‘, ‘2004-02-06 00:00:00‘); INSERT INTO employees VALUES (