数据库 带条件查询 SQL

-- 1) 关系运算符 > < >= <= = !=
select * from student where sex='男'


-- 2) and  or  
select * from student where sex='男' and age>18; 
select * from student where sex='男' && age>18; 

-- 查询 tid=101号老师的学生 或者年龄>25
select * from student where tid=101 or age>25
select * from student where tid=101 || age>25

select * from student where age>=18 and age<=28
-- 3) between and   在18到28之间
select * from student where age between 18 and 28

--  4) in  not in  在 某个范围内的  
select * from student where sid in (2,3,6);
-- 等价于
select * from student where sid=2 or sid=3 or sid=6

select * from student where sid not in (2,3,6);


update student set tid=null where sid=2

select * from student 

-- 5) is null  is not null查询出tid为null的学生
select * from student where tid is null

select * from student where tid is not null

-- 6) like  not like  查询 模糊查找

insert into student values(null,'张三',default,8767,null,101);

insert into student values(null,'张四',default,8989,null,102);

insert into student values(null,'小张三',default,89898,null,102);

select * from student

--  查询姓张的所有学生
-- % 通配符  匹配任意字符任意多个 包括0个
select * from student where sname like '张%'
-- 查询名字中带张的
select * from student where sname like '%张%'

-- 查询两个字的名字 第二个字为三的
-- _通配符 匹配一个任意字符  
select * from student where sname like '_三'

select * from student where sname not  like '_三'

-- 查询第二个字为张的
select * from student where sname like '_张%'

-- 查询最后一个字是三或者四的
select * from student where sname like '%三' or sname like  '%四'