编辑代码

-- DQL 查询语言中的条件查询 关键词 where 跟的是条件；（单个条件/多个条件），+比较运算符&逻辑运算符；
create database info;
use info;
create table worker(
    id int,
    name varchar(10),
    age int,
    gender char(1),
    workcity varchar(10),
    entrytime date
);
insert into worker values (1,'张三',28,'F','上海','2020-08-20');
insert into worker values(2,'李四',24,'F','上海','2021-06-24');
insert into worker values(3,'王二麻',27,'M','北京','2020-07-22');
insert into worker values(4,'王五',29,'M','深圳','2022-01-10');
insert into worker values(5,'赵六',32,'F','深圳','2019-07-07');
insert into worker values(6,'钱九',31,'M','北京','2018-04-19');
insert into worker values(7,'孙八',30,'F','深圳','2021-09-17');
insert into worker values(8,'何十',34,'M','北京','2022-03-28');
insert into worker values(9,'九九',26,'M','深圳','2023-08-19');
insert into worker values(10,'零七',24,'F','上海','2021-06-24');
insert into worker values(11,'可可',27,'M',null,'2024-07-21');
-- select * from worker;
-- 操作1 where 后面跟比较运算符'=', 语法是 select * from 表名 where 字段=值;
-- 该条代码执行的是查询年龄是27和32的员工信息；
select * from worker where age = 27;
select * from worker where age =32;
-- 操作2 where 后面跟比较运算符'<', 语法是 select * from 表名 where 字段</<=/>/>=值;
select * from worker where age < 30;
select * from worker where age > 30;
select * from worker where age <= 30;
select * from worker where age >= 30;
-- 操作3 查询空值/非空值的信息，null，语法是 select * from 表名 where 字段名 is null;
select * from worker where workcity is null;
-- 补充：如果表格中有空值且为char类型，插入数据时，不需要加 '';
select * from worker where workcity is not null;
-- 操作4 where 后面跟逻辑运算符 比如不等于，!=  语法是 select * from 表名 where 字段 != 值;
select * from worker where age != 30;
-- 补充：不等于除了!= 还可以用<>; !
select * from worker where age <> 27;
-- 操作5 where 后面跟多个并列条件，and 和 &&;
select * from worker where age = 27 and workcity='北京';
select * from worker where age < 30 && workcity='深圳';
-- 操作6 where 后面跟between (最小值) and（最大值） 执行的是查询在什么之间的数据；
select * from worker where entrytime between '2020-12-01'and '2022-12-01';
-- 操作7 where 后面跟or 意思是或条件查询，2种语法 where 字段=值 or字段=值; 
-- where 字段 in（值1，值2，值3）；in代表的是满足列表中其一的条件即可；
select * from worker where age = 24 or age = 27 or age = 32;
select * from worker where age in (28,29,30,31,32);
-- 操作8 where 后面跟like %/_ 查询,下划线代表单个字符，%代表任意字符；
select * from worker where name like '__';
-- 上行执行的命令是查询名字是2个字符的员工信息；
select * from worker where name like '张%';
-- 上行执行的命令是查询名字是姓张的员工信息；
select * from worker where name like '%五';
-- 上行执行的命令是查询名字结尾是五的员工信息，不管前面的信息；